Announcement

Collapse
No announcement yet.

SQLite Journey

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQLite Journey

    Ok, after all the encouragement to look at SQLite, I spent time last night doing some reading on the topic.

    Thanks, Chris(H)j, for your post with helpful intro comments.

    This first example worked as expected, creating an empty db file.

    Code:
    'Compilable Example:
    #Compile Exe
    #Dim All
    %Unicode = 1
    #Include "Win32API.inc"
    #Include "sqlite3.inc"
    
    Function PBMain() As Long
       Local hSQL         As Dword              'handle to database
       sqlite3_open  "sql1.db", hSQL
       sqlite3_close hSQL
    End Function

  • #2
    This 2nd example appears to work. Just creates a table with 2 rows of data. I haven't gotten to the part yet of reading table data.

    Code:
    'Compilable Example:
    #Compile Exe
    #Dim All
    %Unicode = 1
    #Include "Win32API.inc"
    #Include "sqlite3.inc"
    
    Function PBMain() As Long
       Local hSQL         As Dword              'handle to database
       Local sqlZ         As StringZ * 200      'query string
       Local nRows, nCols As Long               'row/col in result of query
       Local iResult      As Long               'error # of result of query
       Local pResults     As Dword              'pointer to results pointer array
       Local pError       As StringZ Ptr        'pointer to error msg
    
       sqlite3_open  "sql.db", hSQL
       sqlZ = "begin transaction;" + _
             "create table Locations(City,State,Population);" + _
             "insert into Locations values ('Dallas','Texas',5000000);" + _
             "insert into Locations values ('OKC','Oklahoma',750000);" + _
             "commit transaction;"    'or  rollback transition;
       iResult = sqlite3_get_table (ByVal hSQL, sqlZ, pResults, nRows, nCols, VarPtr(pError))
       sqlite3_close hSQL
    End Function
    Jose's include file has this for declaration of the sqlite3_get_table function:

    Code:
    Declare Function sqlite3_get_table CDecl Import "SQLITE3.DLL" Alias "sqlite3_get_table" ( _
       ByVal hDbc As Dword _                                ' sqlite3*
     , ByRef szSql As AsciiZ _                              ' const char *zSql
     , ByRef pazResult As Dword _                           ' char ***pazResult
     , ByRef pnRows As Long _                               ' int *pnRow
     , ByRef pnColumns As Long _                            ' int *pnColumn
     , ByRef pzErrmsg As Dword _                            ' char **pzErrmsg
     ) As Long                                              ' int
    Looking through the forum I'm seeing a variety of ways that folks call the function, often with ByVal arguments other than the one specified as ByVal in Jose's includes. Not sure why that is.

    I've also seen a comment or two about %Unicode=1 being a problem with SQLite. Is that true? My apps are written with %Unicode=1 in them and having to remove it and adjust the remaining code would be a real pain.

    Note: The code above has a correction from an earlier version of this post.
    Last edited by Gary Beene; 4 Aug 2019, 03:19 PM. Reason: Code Error Correction

    Comment


    • #3
      Please accept the advice of someone who has spent the last 30+ years almost constantly working with databases large and small

      Start off on the right foot.
      Get into the habit of:
      1. Using relationships - normalise your data.
      2. In SQLite, don't rely on ROWID, create a specific primary key alias for it in every table and use that in all table relationships
      3. Specify default column types.

      Code:
      create table States(StatePK INTEGER PRINARY KEY, State TEXT NOT NULL, StateAbbr TEXT NOT NULL);
      create table Locations(CityPK INTEGER PRINARY KEY, City TEXT NOT NULL, StateFK INTEGER NOT NULL,Population INTEGER);

      Comment


      • #4
        > I've also seen a comment or two about %Unicode=1 being a problem with SQLite. Is that true?


        The only caveat about SQLITE and UNICODE characters is case sensitivity. The NOCASE constraint makes sort/search case insensitive but only works on the ASCII character set range. It's never caused me a problem.

        Comment


        • #5
          Hi Stuart!

          As someone who has never used a database in my PowerBASIC apps, I'm all ears. I appreciate the suggestions.

          My reading so far hasn't exposed me to any of the terminology you just suggested I use. But as I keep reading I'll watch out for it.

          Comment


          • #6
            If you can ignore all the advertising that wastes far to much real estate, this is a good primer:

            https://www.tutorialspoint.com/sqlite/index.htm

            Comment


            • #7
              Improved version below
              https://duckduckgo.com instead of google

              Comment


              • #8
                Inserts can be checked if they are successful after each insert or optionally after all inserts in a transaction.
                Sqlite has sqlite3_change and sqlite3_total_changes
                I created a couple of macros to make it easier.
                I'm sure there are macros posted on the bbs for many of the these things.

                Got rid of having to pass the pesky database handle ghDB
                Also demonstrated how "rollback" might be used.

                I like passing as few values as possible and if I need a result, just read a threaded variable.
                The number of rows and columns are always available this way (see gnrows,gncols).
                If an error occurs it is also available in gsErr$ without having to pass any values.

                Also note that the biggy, the results, are in a threaded array gsArray()
                ? JOIN$(gsArray(),$CR),,USING$("rows # cols #",gnrows,gncols)

                Text can be inserted without having to wrap strings with $SQ using binding.
                This allows easily inserting complete documents "as is".
                Wrapping strings can be a real pain when documents already have single quoted strings in them.


                Code:
                #INCLUDE "sqlite3.inc"
                THREADED ghDB,ger,gnrows,gncols AS DWORD
                THREADED gserr,gsArray()        AS STRING
                MACRO changecount= sqlite3_changes(ghDB)
                MACRO totalchangecount = sqlite3_total_changes(ghDB)
                MACRO sqliteopen(p1)=sqlite3_open(p1,ghDB)
                MACRO sqliteclose=sqlite3_close(ghDB)
                
                FUNCTION PBMAIN AS LONG
                
                 sqliteopen ("sql.db")
                 rs "drop table if exists Locations"
                 rs "create table if not exists Locations(City text,State text ,Population integer)"
                 rs "begin immediate"  'important: use "begin immediate" or "begin exclusive" on a LAN
                 rs "insert into Locations values ('Dallas','Texas',5000000)"
                 rs "insert into Locations values ('OKC','Oklahoma',750000)"
                 IF totalchangecount <>2 THEN rs "rollback" ELSE rs "end" 'assumes totalchangecount was 0 before beginning transaction
                
                 rs "select rowid,* from Locations"
                 ? JOIN$(gsArray(),$CR),,USING$("rows # cols #",gnrows,gncols)
                 sqliteclose
                
                END FUNCTION
                
                FUNCTION rs(s AS STRING) AS LONG
                 LOCAL r AS LONG, presults AS DWORD       ' pointer to SQLite's results set
                 LOCAL psz() AS ASCIZ PTR                 ' array of pointers to SQLite's result set
                 'LOCAL nrows, ncols AS LONG              ' # of rows and columns returned by SQLite
                 LOCAL pzer AS ASCIZ PTR                  ' ptr to SQLite's error msg
                 ger = SQLite3_Get_Table(BYVAL ghDB, BYVAL STRPTR(s), presults, gnRows,gnCols,BYVAL VARPTR(pzer))
                 IF ger THEN
                  gserr = "SQLite error in query! :" + @pzer + $CRLF + s
                  ? gserr,%MB_ICONERROR,"Ending Program"
                  sqlite3_close(ghDB)
                  END
                 ELSE
                  gserr=""
                  REDIM psz(0 TO gnrows*gncols-1) AT presults
                  IF LTRIM$(LEFT$(TRIM$(s),7)) <> "select " THEN sqlite3_free(pzer):EXIT FUNCTION
                  IF gnrows*gncols+gncols-1 < 0 THEN
                    ? "No columns or rows",,"Ending Program"
                    sqlite3_close(ghDB)
                    EXIT FUNCTION 'invalid sql got to here
                  END IF
                  REDIM gsArray(0 TO gnrows*gncols+gncols-1)'not sure about this
                  FOR r = 0 TO UBOUND(gsArray):gsArray(r) = @psz(r):NEXT
                 END IF
                 sqlite3_free_table(presults) ' free resources
                END FUNCTION
                https://duckduckgo.com instead of google

                Comment


                • #9
                  Insert 10 files into a table using the file name as a unique key and doing the insert with binding.
                  I haven't done binding without SQLitening, yet, but it will be very similar.
                  Code:
                  #INCLUDE "sqlitening.inc"
                  
                  FUNCTION PBMAIN () AS LONG
                  
                   LOCAL s,sFile,sBuffer,sArray() AS STRING, x AS LONG
                  
                   FOR x = 1 TO 10 'create 10 files
                    OPEN USING$("junk#",x) FOR OUTPUT AS #1
                    s = USING$("'quoted data' is 'no' problem with binding in junk#'",x)
                    PRINT #1,s;
                    CLOSE #1
                   NEXT
                  
                   slopen "junk.db3","C"
                   slexe  "drop table if exists t1"
                   slexe  "create table if not exists t1(filename text unique,document text)"
                  
                   slexe "begin" 'lock database only once for tremendous speed improvement
                  
                   FOR x = 1 TO 10  'read 10 files into SQLite table
                    sFile = USING$("junk#",x)
                    OPEN sFile FOR BINARY AS #1
                    GET$ #1,LOF(1),sBuffer 'get data and strop
                    slexebind "insert into t1 values(?,?)",slbuildbinddat(sfile,"T") + slbuildbinddat(sbuffer,"T")
                    CLOSE #1
                   NEXT
                  
                   slexe "end' 'unlock database
                  
                   slselAry  "select * from t1",sArray(),"Q9"  'display everything
                   ? JOIN$(sArray(),$CR),%MB_SYSTEMMODAL,"Bind Test"
                  
                  END FUNCTION
                  Click image for larger version  Name:	junk.png Views:	0 Size:	3.7 KB ID:	783589
                  https://duckduckgo.com instead of google

                  Comment


                  • #10
                    Originally posted by Stuart McLachlan View Post
                    ...
                    Get into the habit of:
                    1. Using relationships - normalise your data.
                    2. In SQLite, don't rely on ROWID, create a specific primary key alias for it in every table and use that in all table relationships
                    3. Specify default column types.
                    If you are using "databases" as in "repositories for stored data", I agree.

                    But if you are using some of the facilities of SQLite to do in-memory stuff, where the entire life cycle of your data is controlled by a single program, ROWID can be pretty useful, and untyped columns too.

                    BTW, here's a tutorial on normalising data for those who are not familiar with the term: https://www.studytonight.com/dbms/da...malization.php

                    Comment


                    • #11
                      Originally posted by Gary Beene View Post
                      Ok, after all the encouragement to look at SQLite, I spent time last night doing some reading on the topic.

                      Thanks, Chris(H)j, for your post with helpful intro comments.
                      Gary, it can't have been much more than a decade ago that I started posting SQLite examples, and here you are impetuously diving in! Are you feeling OK? :smile:

                      Comment


                      • #12
                        Chris,
                        Could you point me to something with binding?
                        I'd like to continue with post#8, but add binding to it.
                        It is so easy working with SQLite with everything in a single function.

                        I don't know which of your posts would be the best to borrow from.
                        I work with SQLitening server, but would really like to switch to SQLite for single-user and put everything into a single function to make it even easier.
                        With Threaded variables and MACROS it is possible.
                        Post #8 demonstrates just passing a statement. It puts the recordset into a threaded array.
                        https://duckduckgo.com instead of google

                        Comment


                        • #13
                          Chris(H) ... I wouldn't want to be accused of acting hastily!

                          My interest in SQL is for use in gbThreads to store/search threads and to avoid the need to distribute 50K thread files.

                          Comment


                          • #14
                            Gary,
                            That is why I'd like to add binding to post #8 so you don't have an excuse. Binding eliminates the unsightly wrapping of strings.
                            It is easy with SQLitening, but having trouble figuring it out with SQLite and adding to a single function that does everything.
                            Post #9 is my vision of reading your data into SQLite.
                            Note: I'd add any indexes after loading the data.
                            https://duckduckgo.com instead of google

                            Comment


                            • #15
                              Gary
                              All normalisation is...is a set of rules to reduce/eliminate data duplication so you don't accidentally change one instance and leave another as it was. Once you've "normalised" your data...you often backtrack on it and reintroduce duplication to make your app faster
                              i.e. it's a sensible step but not the end of the story.

                              Data elimination ideally means having your data specified only in one table or another and referring to it's records via codes so you only have to change the data in one place and all the codes stay the same. Hope that helps. This normally means ending up with more tables and indirection than SEEMS intuitively necessary.

                              Comment


                              • #16
                                Mike, here's a quick working example of SQLite native binding on a single column for building and querying a table:

                                Code:
                                '
                                ' run a simple SQLITE3 queries to populate and query a table using binding
                                ' Chris Holbrook Aug 2019
                                #compile exe
                                #dim all
                                #debug display
                                #include "WIN32API.INC"
                                #include "c:\users\chris\mine\chris\sqlite3.inc"
                                
                                function pbmain()
                                    local hDB as long           ' database handle
                                    local s as string           ' ephemeral
                                    local sSQL as string        ' SQL passed to SRs
                                    local sval as string        ' string value used by SRs
                                    local nval as long          ' long value used by SRs
                                    local i as long             ' ephemeral
                                    local nparm as long         ' bind parameter position
                                    local nsteps as long        ' row count returned by steps SR
                                    local lretval as long       ' VALUE RETURNED BY FUNCTIONS
                                    local pz  as asciz ptr      'ephemeral
                                    local pztail as asciz ptr   ' ptr to unprocessed part of SQL
                                    local pstmt as long         ' ptr to query VM
                                    local hWin as long          ' text window handle
                                    '
                                    txt.window("SQLite3 BINDING EXAMPLE by Chris Holbrook 11 Aug 2019", 100, 100, 24, 80) to hWin
                                    txt.print "OPEN MEMORY DATABASE"
                                    if sqlite3_open( ":memory:", hdb) <> %SQLITE_OK then
                                        txt.print "CAN'T OPEN MEMORY DB!"
                                        txt.waitkey$
                                        exit function
                                    end if
                                    ' create table
                                    sSQL = "CREATE TABLE members(ID INTEGER PRIMARY KEY, Name TEXT)":txt.print sSQL
                                    gosub runquery
                                    ' insert data using binding
                                    sSQL = "INSERT INTO members(Name) VALUES(?)":txt.print sSQL
                                    gosub prepare
                                    gosub explain
                                    nparm = 1
                                    sval = "Jones": gosub bindtext: gosub steps:txt.print sval
                                    sval = "Singh": gosub bindtext: gosub steps:txt.print sval
                                    sval = "Li": gosub bindtext: gosub steps:txt.print sval
                                    sqlite3_clear_bindings(pStmt)
                                    '  query using one bind parameter
                                    sSQL = "SELECT ID, name FROM members WHERE Name not like ?":txt.print sSQL
                                    gosub prepare
                                    '
                                    sval = "Singh": gosub bindtext: sqlite3_step(pStmt): txt.print sval
                                    ' read back each row in the result set
                                    gosub showem
                                    '
                                
                                    ' clean up
                                    sqlite3_finalize(pStmt) ' discard VM
                                    sqlite3_close(hDB)
                                    txt.waitkey$
                                    exit function
                                ''''''''''
                                showem:
                                    s = ""
                                    sqlite3_reset(pstmt)
                                    lretval = sqlite3_step(pStmt)
                                    do
                                        if lretval = %SQLITE_ERROR then
                                            sqlite3_close(hDB)
                                            txt.print "step failed(1): " + s + $crlf + _
                                                   "error " + str$(lretval)
                                            txt.waitkey$
                                            exit function
                                        end if
                                        pz = sqlite3_column_text(pStmt, 1)
                                        txt.print ">    " + using$("#, &", sqlite3_column_int(pStmt, 0), @pz)
                                        lretval = sqlite3_step(pStmt)
                                        if lretval = %SQLITE_DONE then exit loop
                                    wend
                                    txt.print "done!"
                                    txt.waitkey$
                                return
                                ''''''''''
                                runquery:
                                    pz = strptr(sSQL)
                                    lretval = sqlite3_exec(hDB, @pz, %NULL, %NULL, %NUll)
                                    if (lretval <> %SQLITE_OK) then
                                        sqlite3_close(hDB)
                                        txt.print "SQL failed: " + s + $crlf + _
                                               "error " + str$(lretval)
                                        txt.waitkey$
                                        exit function
                                    end if
                                return
                                ''''''''''
                                prepare:
                                    pz = strptr(sSQL)
                                    lretval = sqlite3_prepare_v2(hDB, @pz, -1, pStmt, pztail)
                                    if (lretval <> %SQLITE_OK) then
                                        txt.print using$("failed to prepare statement" + $crlf + "&" + $crlf + "error: ###", sSQL, lretval)
                                        sqlite3_close(hDB)
                                        txt.waitkey$
                                        exit function
                                    end if
                                return
                                '''''''''''
                                explain:
                                    pz = strptr(sSQL)
                                    txt.print using$("& " + $crlf + _
                                             "parameter count is ##", _
                                             @pz, sqlite3_bind_parameter_count(byval pStmt))
                                return
                                ''''''''''
                                bindtext:
                                    pz = strptr(sval)
                                    lretval = sqlite3_bind_text(pStmt, nparm, @pz, -1, %SQLITE_TRANSIENT)
                                    if lretval <> %SQLITE_OK then
                                        txt.print using$("failed to bind text" + $crlf + "&" + $crlf + "error: ###", sval, lretval)
                                        sqlite3_close(hDB)
                                        txt.waitkey$
                                        exit function
                                    end if
                                return
                                ''''''''''
                                bindlong:
                                    lretval = sqlite3_bind_int(pStmt, nparm, nval)
                                    if lretval <> %SQLITE_OK then
                                        txt.print using$("failed to bind long" + $crlf + "###" + $crlf + "error: ###", nval, lretval)
                                        sqlite3_close(hDB)
                                        txt.waitkey$
                                        exit function
                                    end if
                                return
                                '''''''''
                                steps:
                                    nsteps = 0
                                    do
                                        lretval = sqlite3_step(pStmt)
                                        select case as long lretval
                                            case %SQLITE_ERROR
                                                sqlite3_close(hDB)
                                                txt.print "step failed(2): " + s + $crlf + _
                                                       "error " + str$(lretval)
                                                txt.waitkey$
                                                exit function
                                            case %SQLITE_DONE
                                                sqlite3_reset(pstmt)
                                                exit loop
                                            case %SQLITE_ROW
                                                incr nsteps
                                                iterate loop
                                            case else
                                                txt.print using$( "step: unexpected result(#) from &", lretval, s)
                                                sqlite3_finalize(pStmt)
                                                sqlite3_close(hDB)
                                                txt.waitkey$
                                                exit function
                                        end select
                                    loop
                                return
                                end function

                                Comment


                                • #17
                                  Fantastic. It will fit right into my function!! Thank you.
                                  https://duckduckgo.com instead of google

                                  Comment


                                  • #18
                                    Very nice example.
                                    One quibble
                                    > sSQL = "CREATE TABLE members(ID INTEGER PRIMARY KEY, Name TEXT)"
                                    while NAME is not a SQLite keyword, it is a PB one. As a general rule it is good practice to avoid using any keywords for table/field etc names.

                                    Comment


                                    • #19
                                      Originally posted by Stuart McLachlan View Post
                                      One quibble
                                      Fair comment Stuart. I have a couple of quibbles myself, but am out of time to play with it for a day or two.

                                      Comment


                                      • #20
                                        I made an improved version of my code above and posted it to the Source Code Forum here: https://forum.powerbasic.com/forum/u...ts-and-binding

                                        Comment

                                        Working...
                                        X