Search question

StungStang · March 9, 2011

@Smoke

First of all thanks for your trick...i've switched the _SQLite_GetTable2d to _SQLite_FetchData now my code is that :

#include <ButtonConstants.au3>
#include <GUIConstantsEx.au3>
#include <ListViewConstants.au3>
#include <WindowsConstants.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <GuiListView.au3>
#include <ComboConstants.au3>


$Form1 = GUICreate("Try", 627, 464, 192, 124)
$Input1 = GUICtrlCreateInput("Input1", 8, 8, 121, 21)
$Search = GUICtrlCreateButton("Search", 136, 8, 75, 25)
$ListView = GUICtrlCreateListView("Name|Streaming|WebSite|Genre|Country", 0, 40, 626, 382)
Local $hListView = GUICtrlGetHandle($ListView)

GUISetState(@SW_SHOW)

_SQLite_Startup()
_SQLite_Open(@ScriptDir & "\Database.db")

If @error > 0 Then
    MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!")
    Exit
EndIf


While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $Search
            $Valor = GUICtrlRead ($Input1)
            _Search($Valor)
    EndSwitch
WEnd


Func _Search ($Value) ;Changed, thanks to SmOke_N
_GUICtrlListView_BeginUpdate($ListView)
_GUICtrlListView_DeleteAllItems($ListView)
Local $hQuery, $aRow, $aNames
_SQLite_Query (-1, "SELECT * FROM America WHERE Name LIKE '%" & $Value & "%' UNION SELECT * FROM Europe WHERE Name LIKE '%" & $Value & "%';", $hQuery)
While _SQLite_FetchData($hQuery, $aRow, False , False) = $SQLITE_OK
$Line = $aRow[0] & "|" & $aRow[1] & "|" & $aRow[2] & "|" & $aRow[3] & "|" & $aRow[4]
GuiCtrlCreateListViewItem($Line,$ListView)
WEnd
_SQLite_QueryFinalize($hQuery)
_GUICtrlListView_EndUpdate($ListView)
EndFunc

I've only 5 tables...i thinks is the best way to divided the resarch of radio by Continent...If i do that with only one table, as suggested by Kylomas, i've too much redundant data, with slowest search and bigger database dimension.

Anyone have an idea how to improve this db, and do the search without too much UNION, for a cleanest and faster search?

Hi!

SmOke_N · March 9, 2011

Guess you missed my point of being creative with AutoIt.

Example only:

Global $gs_query = _my_UnionTable_NameQueryAllData("radio", "Name", "America", "Europe", "Asia", "Whatever") 
ConsoleWrite($gs_query & @CRLF)

Func _my_UnionTable_NameQueryAllData($s_value, $s_colwhere, $s_table1, $s_table2 = "", $s_table3 = "", $s_table4 = "", $s_table5 = "")
    #forceref $s_table1, $s_table2, $s_table3, $s_table4, $s_table5

    Local $i_params = @NumParams

    Local $s_query = "SELECT * FROM " & $s_table1
    $s_query &= " WHERE " & $s_colwhere & " LIKE '%" & $s_value & "%'"

    If $i_params = 3 Then
        $s_query = "begin;" & $s_query & ";commit;"
        Return $s_query
    EndIf

    For $i = 4 To @NumParams
        $s_query &= " UNION SELECT * FROM " & Eval("s_table" & $i - 2)
        $s_query &= " WHERE " & $s_colwhere & " LIKE '" & $s_value & "'"
    Next

    $s_query = "begin;" & $s_query & ";commit;"

    Return $s_query
EndFunc

StungStang · March 9, 2011

@SmOke_N

I don't have understand nothing of your code =P

Why i have to do this autoit function?

Global $gs_query = _my_UnionTable_NameQueryAllData("radio", "Name", "America", "Europe", "Asia", "Whatever")

"Radio" is the string that i've to search?

What do that?

$s_query = "begin;" & $s_query & ";commit;"

What do "begin;" and ";commit;" command?

Your fucntion is not the same of that?

_SQLite_Query (-1, "SELECT * FROM America WHERE Name LIKE '%" & $Value & "%' UNION SELECT * FROM Europe WHERE Name LIKE '%" & $Value & "%';", $hQuery)

Thanks for your help and sorry for my too much question =P

SmOke_N · March 9, 2011

You said you didn't want to create long strings.

The function creates them for you.

If you want the data from all the tables you want to query, then you do what you need to.

Example Only:

#include <ButtonConstants.au3>
#include <GUIConstantsEx.au3>
#include <ListViewConstants.au3>
#include <WindowsConstants.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <GuiListView.au3>
#include <ComboConstants.au3>


$Form1 = GUICreate("Try", 627, 464, 192, 124)
$Input1 = GUICtrlCreateInput("Input1", 8, 8, 121, 21)
$Search = GUICtrlCreateButton("Search", 136, 8, 75, 25)
$ListView = GUICtrlCreateListView("Name|Streaming|WebSite|Genre|Country", 0, 40, 626, 382)
Local $hListView = GUICtrlGetHandle($ListView)

GUISetState(@SW_SHOW)

_SQLite_Startup()
_SQLite_Open(@ScriptDir & "\Database.db")

If @error > 0 Then
    MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!")
    Exit
EndIf


While 1
    $nMsg = GUIGetMsg()
    Switch $nMsg
        Case $GUI_EVENT_CLOSE
            Exit
        Case $Search
            $Valor = GUICtrlRead ($Input1)
            _Search($Valor)
    EndSwitch
WEnd


Func _Search ($Value) ;Changed, thanks to SmOke_N
    _GUICtrlListView_BeginUpdate($ListView)
    _GUICtrlListView_DeleteAllItems($ListView)
    
    Local $hQuery, $aRow, $aNames
    Local $s_query_string = _my_UnionTable_NameQueryAllData($Value, "Name", "America", "Europe"); there are 3 more parameters you can use for the other 3 tables
    _SQLite_Query (-1, $s_query_string, $hQuery)
    While _SQLite_FetchData($hQuery, $aRow, False , False) = $SQLITE_OK
        $Line = $aRow[0] & "|" & $aRow[1] & "|" & $aRow[2] & "|" & $aRow[3] & "|" & $aRow[4]
        GuiCtrlCreateListViewItem($Line,$ListView)
    WEnd
    
    _SQLite_QueryFinalize($hQuery)
    _GUICtrlListView_EndUpdate($ListView)
EndFunc

Func _my_UnionTable_NameQueryAllData($s_value, $s_colwhere, $s_table1, $s_table2 = "", $s_table3 = "", $s_table4 = "", $s_table5 = "")
    #forceref $s_table1, $s_table2, $s_table3, $s_table4, $s_table5

    Local $i_params = @NumParams

    Local $s_query = "SELECT * FROM " & $s_table1
    $s_query &= " WHERE " & $s_colwhere & " LIKE '%" & $s_value & "%'"

    If $i_params = 3 Then
        $s_query = "begin;" & $s_query & ";commit;"
        Return $s_query
    EndIf

    For $i = 4 To @NumParams
        $s_query &= " UNION SELECT * FROM " & Eval("s_table" & $i - 2)
        $s_query &= " WHERE " & $s_colwhere & " LIKE '" & $s_value & "'"
    Next

    $s_query = "begin;" & $s_query & ";commit;"

    Return $s_query
EndFunc

As far as "BEGIN;" AND "COMMIT;", you can google those with sqlite.

Xenobiologist · March 10, 2011

Hi,

just 2 cents from me again.

I would prefer creating two tables.

So, you got

Name|Streaming|WebSite|Genre|Country-ID

and a table Country

ID|Name

Mega

Edited March 10, 2011 by Xenobiologist

jchd · March 10, 2011

Just to add to what has been just posted, I even started some time ago a small model for the OP where I created separate tables for continents, countries, states and radios, all using foreign keys to link them up.

To StungStang, you really should google for an SQL primer and build from there. BTW did you get my PM? If so, what do you think?

StungStang · March 10, 2011

The really problem is how to set the database?

A structure like that :

1 Table for 5 Continent:

Table America
-->Name|Streaming|Web Site|Genre|Country

Or 1 Table for all entry

Table All Station
-->Name|Streaming|Web Site|Genre|Continent|Country

Or 2 Table, one for station data and 1 for ID Continent

Table Radio
-->Name|Streaming|Web Site|Genre|Country|ID continent

Table Continent
-->Continent|ID

Or what else?....

@jchd

I see your dll, but i thinks is unnecessary for my script (or not?)...It will be good for other progett

Thanks!

Xenobiologist · March 10, 2011

Hi,

I would do something like this:

DROP TABLE IF EXISTS "Country";
CREATE TABLE "Country" (
 "ID" NUMERIC PRIMARY KEY  NOT NULL, 
 "Name" VARCHAR
);
INSERT INTO "Country" VALUES(1,'USA');
INSERT INTO "Country" VALUES(2,'Germany');
INSERT INTO "Country" VALUES(3,'Italy');
INSERT INTO "Country" VALUES(4,'England');
DROP TABLE IF EXISTS "Radio";
CREATE TABLE "Radio" (
 "ID"  PRIMARY KEY  NOT NULL, 
 "Name" VARCHAR,
 "Streaming" VARCHAR,
 "Site" VARCHAR,
 "Genre" VARCHAR,
 "State" VARCHAR,
 "Country-ID" NUMERIC,
 FOREIGN KEY("Country-ID") REFERENCES "Country"("ID")
);
INSERT INTO "Radio" VALUES('MyRadio','mms://myradio.asx','www.myradio.com','Rock','California','1',1);
INSERT INTO "Radio" VALUES('MyPreRadio','mms://myrad2io.asx','www.myprefradio.com','Pop','Florida','2',2);
INSERT INTO "Radio" VALUES('MyExamle','mms://link.mp3','www.myexample.com','Dance','New York','3',3);
INSERT INTO "Radio" VALUES('MyExamle2','mms://link.mp32','www.myexample2.com','Trance','Chicago','2',4);

You can save this as bla.sql and import it with SQLite Manager (add-on) for firefox.

Mega

jchd · March 10, 2011

The general idea with DB design is to normalize the tables so that it minimizes the redundancy of information. It's not only a question of used space, but merely a question of integrity. Selecting a country within a continent is easier to get right than typing/validating the name of a continent and a country spelling. Same for states and other data. Furthermore, this allows a much more efficient indexing (also using much less space both on diskk and memory).

Using the foreign key mecanism further enhances integrity as it's now impossible to make bad mistakes, i.e. add a state in a non-existent country or register a radio in non-existent genre, a.s.o..

Even if all this seems a bit too much complex for a simple DB like yours, it's a good design practice and will give you a strong basis should your application become more complex over time, or should you have a more complex project next.

Edited March 10, 2011 by jchd

StungStang · March 10, 2011

You do a table for All radio station, another for the id of continent

It the same to that :

Table All Station
-->Name|Streaming|Web Site|Genre|Continent|Country

Where i can assign a number for continet, for example 1= America, 2= Europe, ecc...

I waithing for other soluction ,to compare other ideas

Hi!

Xenobiologist · March 10, 2011

Sorry, I do not get you.

Any question left? Did you see my SQl to create your DB structure?

StungStang · March 10, 2011

If i do your metod...with 2 table, one only for id...

In my script how i can convert the number "1" to USA, etc...?

For you this is the better way? =)

Sorry, but this database confusing me too much @_@

Hi!

SmOke_N · March 10, 2011

You need a good db manager tool, some of us use SQLite Expert here, here are some more:

http://www.sqlite.org/cvstrac/wiki?p=ManagementTools

You also need to learn how to use SQLite:

http://www.sqlite.org/docs.html

You might want to look specifically at functions you can use.

http://www.sqlite.org/lang_corefunc.html

Keep in mind, though we have a "User Defined Function" base for SQLite, it's not AutoIt, it's something we can access/manipulate/utilize in AutoIt.

For specific questions like you have that are based on DB structure and DB access, you should consort with a SQLite forum. Not only would your questions be answered faster more than likely, but you'd be asking the your questions in the right place.

SQLite Forum ( First I came too ):

http://sqliteforum.com/

SQLite IRC:

#sqlite freenode

Good luck.

Xenobiologist · March 11, 2011

Hi,

first of all, do what SmOke_N said.

Read a little bit about sqlite.

Back to your question.

If you have the db structure like it showed above, then it is still no problem to get your infos even with column CountryID by Name.

SELECT r.*, c.Name
FROM Radio  r, Country c
WHERE r.CountryID = c.ID
AND r.Name like '%l%';

I can sent you the database if you want.

Mega

Edited March 11, 2011 by Xenobiologist

FastFrench · March 11, 2011

A (last ?) word before you leave this forum for another with this topic...

If you really want to add "continent" information, then add this in the Country table instead of the Radio table (makes more sense, and less redundancy). You may even make a specific table for that, but that's not really a great idea as I suspect you won't have thousands of countries.

And for the 'genre' field, you should also better makea third table for this, constructed and linked with Radio table the same way as suggested by Xenobiologist for "Country" table,

Sign In

Search question

Recommended Posts

StungStang

SmOke_N

StungStang

SmOke_N

Xenobiologist

jchd

StungStang

Xenobiologist

jchd

StungStang

Xenobiologist

StungStang

SmOke_N

Xenobiologist

FastFrench

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members

Browse

AutoIt Resources

Release

Beta