Sqlite data fetch question

[Champak]

How exactly do I get data from cell of an unknown table? Do I use a * in place of the table name then cycle through the array for what I'm looking for? Or is there a faster/better way?

[nullschritt]

How exactly do I get data from cell of an unknown table? Do I use a * in place of the table name then cycle through the array for what I'm looking for? Or is there a faster/better way?

#include <SQLite.au3>
#include <SQLite.dll.au3>
#include <MsgBoxConstants.au3>
#include <array.au3>

Local $hQuery, $aRow, $sMsg
_SQLite_Startup()
ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF)
_SQLite_Open() ; open :memory: Database
_SQLite_Exec(-1, "CREATE TABLE aTest (a,b,c);") ; CREATE a Table
_SQLite_Exec(-1, "CREATE TABLE bTest (a,b,c);") ; CREATE a Table
_SQLite_Exec(-1, "CREATE TABLE cTest (a,b,c);") ; CREATE a Table
_SQLite_Exec(-1, "CREATE TABLE dTest (a,b,c);") ; CREATE a Table
_SQLite_Query(-1, 'SELECT name FROM sqlite_master WHERE type = "table"', $hQuery) ; the query

While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
    $sMsg &= $aRow[0]&"|"
WEnd
MsgBox($MB_SYSTEMMODAL, "SQLite", "Tables: " & $sMsg)
_SQLite_Close()
_SQLite_Shutdown()

[jchd]

Schema names (e.g. column or table names) can't be parametrized at the SQL level. So do as the example above shows, but better look at the schema first by using a database manager (like SQLite Expert).

[Champak]

No, I already know the names of all the tables in my database file, the problem is I don't know the name of the table something might be in for one of my query updates. So I need to find out what table "X" is in, then update "X" in that table. Or I guess my first question should be, do I need to find out what table "X" is in in order to update "X" cell? There are 6 tables in this database file all with the same structure just for reference.

[jchd]

What you describe badly look as a wrong DB design. There should never exist 6 tables having the exact same structure. The best proof is the problem you have.

Merge all 6 tables into a single one, eventually distinguishing their source with an extra column, if that matters in your context.

[nullschritt]

Yes you have to know the name of the table to modify a value in it. As stated above, merge all tables into one table, and if relevant, add an extra column to distinguish their source/reference.

In this case you would then need only the reference and value you want to update, to preform a query. 

[jchd]

It is like if you're Brit and you maintain 4 distinct phonebooks for people in Northern Ireland, Scotland, England and Whales.

That's simply not the way you or me would do it: we would eventually add a new field to store nation of residence. That way you can query "Tatcher" and Whales

or

"Tatcher" without residence precision.

[Champak]

Thanks, tou two are absolutely right now that I think of it. I didn't have to do it the way I did. Damn it why did you have to be right. Now I got to pull apart what I've done and restructure.

[jchd]

Smart move: the time you're going to spend doing so is not wasted. It's putting your application on the right tracks before entering production.

BTW your initial problem has a quick and dirty solution but I urge you not to go this route:

update table1 set column1 = 123 where columnX = "this";

update table2 set column1 = 123 where columnX = "this";

update table3 set column1 = 123 where columnX = "this";

update table4 set column1 = 123 where columnX = "this";

The engine will update only where conditions are met. Corollary: you don't need to be sure something exist to [try to] update it: if it isn't there, then nothing will change and no error will result.

[MHz]

I would have separate tables / phone books if it was deemed suitable and I have some scripts that work with that design. Tossing everything into one table is not a good design if it does not work well. The appropriate design takes thought and not a single rule to rule them all.

Here is an example of search through all tables for data in column a.

expandcollapse popup

#include <SQLite.au3>
;~ #include <SQLite.dll.au3> ; not needed if you already have the dll
#include <MsgBoxConstants.au3>
#include <array.au3>

Local $hQuery, $aRow, $sMsg, $sSql
_SQLite_Startup()
ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF)
_SQLite_Open() ; open :memory: Database
_SQLite_Exec(-1, "CREATE TABLE aTest (a,b,c);") ; CREATE a Table
_SQLite_Exec(-1, "INSERT INTO aTest VALUES ('1','2','3');") ; INSERT

_SQLite_Exec(-1, "CREATE TABLE bTest (a,b,c);") ; CREATE a Table
_SQLite_Exec(-1, "INSERT INTO aTest VALUES ('4','5','6');") ; INSERT

_SQLite_Exec(-1, "CREATE TABLE cTest (a,b,c);") ; CREATE a Table
_SQLite_Exec(-1, "INSERT INTO aTest VALUES ('7','8','9');") ; INSERT

_SQLite_Exec(-1, "CREATE TABLE dTest (a,b,c);") ; CREATE a Table
_SQLite_Exec(-1, "INSERT INTO aTest VALUES ('0','A','B');") ; INSERT

$aResult = _SearchDatabase('1')
_ArrayDisplay($aResult)

_SQLite_Close()
_SQLite_Shutdown()

; UNION ALL joins the tables vertically into one and the UDF also adds table name and rowid
;
; example:
;
; 1|2|3|aTest|1
; 4|5|6|bTest|1
; 7|8|9|cTest|1
; 0|A|B|dTest|1
;
; output:
;
; 1|2|3|aTest|1
;
; Now you have the values, table name and rowid of the match

Func _SearchDatabase($sString)
    ; search column "a" for data LIKE $sString 
    Local $sSql, $aTables, $aResult, $iState
    Local $tmp1, $tmp2
    If _SQLite_Master_GetTableNames($aTables) = $SQLITE_OK Then
        ; create the string to union all tables
        For $1 = 0 To UBound($aTables) -1
            $sSql &= "SELECT a, b, c, '" & $aTables[$1] & "' AS ""table"", rowid FROM " & $aTables[$1] & " UNION ALL "
        Next
        ; remove last " UNION ALL "
        $sSql = StringTrimRight($sSql, 11)
        If $sSql Then
            $sSql = "SELECT * FROM (" & $sSql & ") WHERE a LIKE '%" & $sString & "%' ORDER BY a;"
            $iState = _SQLite_GetTable2d(Default, $sSql, $aResult, $tmp1, $tmp2)
            If $iState <> $SQLITE_OK Then Return SetError(@error, 0, $iState)
            Return $aResult
        EndIf
    EndIf
    Return SetError(1, 0, 0)
EndFunc

Func _SQLite_Master_GetTableNames(ByRef $aTables)
    ; get table names from the master table
    Local $hQuery, $aRow, $sTables, $iState
    $iState = _SQLite_Query(Default, "SELECT name FROM sqlite_master WHERE type = 'table';", $hQuery)
    If $iState = $SQLITE_OK Then
        While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
            $sTables &= $aRow[0] & @CRLF
        WEnd
        $sTables = StringTrimRight($sTables, 2)
        $aTables = StringSplit($sTables, @CRLF, 3)
        Return $SQLITE_OK
    Else
        Return SetError(@error, 0, $iState)
    EndIf
EndFunc

[nullschritt]

 

I would have separate tables / phone books if it was deemed suitable and I have some scripts that work with that design. Tossing everything into one table is not a good design if it does not work well. The appropriate design takes thought and not a single rule to rule them all.

Here is an example of search through all tables for data in column a.

expandcollapse popup

#include <SQLite.au3>
;~ #include <SQLite.dll.au3> ; not needed if you already have the dll
#include <MsgBoxConstants.au3>
#include <array.au3>

Local $hQuery, $aRow, $sMsg, $sSql
_SQLite_Startup()
ConsoleWrite("_SQLite_LibVersion=" & _SQLite_LibVersion() & @CRLF)
_SQLite_Open() ; open :memory: Database
_SQLite_Exec(-1, "CREATE TABLE aTest (a,b,c);") ; CREATE a Table
_SQLite_Exec(-1, "INSERT INTO aTest VALUES ('1','2','3');") ; INSERT

_SQLite_Exec(-1, "CREATE TABLE bTest (a,b,c);") ; CREATE a Table
_SQLite_Exec(-1, "INSERT INTO aTest VALUES ('4','5','6');") ; INSERT

_SQLite_Exec(-1, "CREATE TABLE cTest (a,b,c);") ; CREATE a Table
_SQLite_Exec(-1, "INSERT INTO aTest VALUES ('7','8','9');") ; INSERT

_SQLite_Exec(-1, "CREATE TABLE dTest (a,b,c);") ; CREATE a Table
_SQLite_Exec(-1, "INSERT INTO aTest VALUES ('0','A','B');") ; INSERT

$aResult = _SearchDatabase('1')
_ArrayDisplay($aResult)

_SQLite_Close()
_SQLite_Shutdown()

; UNION ALL joins the tables vertically into one and the UDF also adds table name and rowid
;
; example:
;
; 1|2|3|aTest|1
; 4|5|6|bTest|1
; 7|8|9|cTest|1
; 0|A|B|dTest|1
;
; output:
;
; 1|2|3|aTest|1
;
; Now you have the values, table name and rowid of the match

Func _SearchDatabase($sString)
    ; search column "a" for data LIKE $sString 
    Local $sSql, $aTables, $aResult, $iState
    Local $tmp1, $tmp2
    If _SQLite_Master_GetTableNames($aTables) = $SQLITE_OK Then
        ; create the string to union all tables
        For $1 = 0 To UBound($aTables) -1
            $sSql &= "SELECT a, b, c, '" & $aTables[$1] & "' AS ""table"", rowid FROM " & $aTables[$1] & " UNION ALL "
        Next
        ; remove last " UNION ALL "
        $sSql = StringTrimRight($sSql, 11)
        If $sSql Then
            $sSql = "SELECT * FROM (" & $sSql & ") WHERE a LIKE '%" & $sString & "%' ORDER BY a;"
            $iState = _SQLite_GetTable2d(Default, $sSql, $aResult, $tmp1, $tmp2)
            If $iState <> $SQLITE_OK Then Return SetError(@error, 0, $iState)
            Return $aResult
        EndIf
    EndIf
    Return SetError(1, 0, 0)
EndFunc

Func _SQLite_Master_GetTableNames(ByRef $aTables)
    ; get table names from the master table
    Local $hQuery, $aRow, $sTables, $iState
    $iState = _SQLite_Query(Default, "SELECT name FROM sqlite_master WHERE type = 'table';", $hQuery)
    If $iState = $SQLITE_OK Then
        While _SQLite_FetchData($hQuery, $aRow) = $SQLITE_OK
            $sTables &= $aRow[0] & @CRLF
        WEnd
        $sTables = StringTrimRight($sTables, 2)
        $aTables = StringSplit($sTables, @CRLF, 3)
        Return $SQLITE_OK
    Else
        Return SetError(@error, 0, $iState)
    EndIf
EndFunc

I'm sorry but arn't you supposed to be an MPV? Please explain logically why you would ever need to use multiple tables for data with an identical structure.

The same result is achieved using one table with an identifier row, with the additional benefit of improved performance on sequential data reads/writes, and allowing the database engine to manipulate/search the in a more efficient/quicker way.

I'm disappointed to see an MPV suggesting illogical coding practices... 

[MHz]

Nullschritt,

For example, one table per massive amount of data collected from each internal drive on a PC. So you consider it logical to lump it all into one table. If you have to update data from one drive then you would need to do them all because you chose to create it all in 1 table. Dropping a whole table is easier then deleting by an identifier row. Searching a smaller table from all tables is quicker then searching one massive large table when needed. Good design that is suitable makes good results with timing and efficiency. Adding data from different scripts, from different sources into a single table is not what I would call logical coding practice.

There is little of additional benefit of improved performance storing all data into one single table.

I accept your disappointment as just a lack of understanding.

[nullschritt]

@MHz

The only valid point I see you've actually made is in reference to the time taken to search through the table/ (and this point is only valid if there is considerable difference between the size of each table, and if those tables are accessed in a dynamic order, as searching 4 tables for data takes just as long as searching 1 table with an equal amount of data, if not longer)[which does seem to be the case when storing/accessing harddrive data, I'll give you that much.].

And you can just as easily delete all results from a table based on an identifier row.

Though you have a point, it seems to be the exception and not the norm. And in this case, it's irrelevant, because as stated if he's searching 6 tables for data, it's going to take just as long as searching 1 table for the same data, as there is an EQUAL amount of data to go through regardless of if it's visually split into 6 individual loops or 1.

I'm simply saying that in this situation you shouldn't encourage such a coding practice.

[MHz]

nullschritt,

Your last post focuses on timings and nothing much more of anything else. It seems you still have a all in one table design or all tables should be treated as one. If you consider that as the norm then you seem to me quite limited in database design. Having multiple tables can be suitable as each table is isolated from the other tables. A join or union can tie data together from different tables. A number of SQL websites encourage smaller tables rather then all in one. I do not consider multiple tables as an exception. I consider your concept of norm is not everybodys understanding of norm.

If my example suits the use for Champak or use for anyone elses benefit for any reason, then it is within my liberal right to post information that could be suitable for a solution. I have little desire for anyone trying to tell me what I can post or not post based on their opinion of what they consider norm is.

[jchd]

There is no point in calling each other names.

While I agree with MHz that DB normalization pushed to the extreme is a dogmatic position which is not useful, there must be reasonable reasons to split a massive amount of data into two or more separate tables. One common reason is archiving past data, where querying split tables is a rare need and you know where to search when you have to, e.g. 2011 invoices details.

But splitting tables should never make routine usage harder. There are objective serious drawbacks to split tables. First, as in the OP, you don't know where to search and even if you can grab data as in your example with union all, you still don't know where it resides. Updating data is problematic: you have to query every table to locate it or update blindly all tables in the hope the where conditions are deterministic enough.

Then you can hardly use foreign keys. Triggers can reveal a nightmare. Self joins or joins accross tables can become accrobatic. Rowids are not enough or you have to reserve specific ranges but in this case you could as well merge everything. Overall SQL becomes much more complex and error-prone. The example you made in post #10 shows how cumbersome the simplest task become.

So while it's not an absolute no-no in all cases, there are enough clues that it isn't the natural design in 99% of the use cases. So it isn't bad advice to tell fresh DB users to keep away of such a design. If the context actually calls for split tables, a seasonned user will be well aware of the pro and contra and will know beforehand how to circumvent the issues associated with split tables.