Announcement

Collapse
No announcement yet.

CGI and SQlite

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

  • CGI and SQlite

    Most ISPs offer MySQL.
    I'm happy with SQLite, but have no experience of it in the CGI context.
    Anyone out there using SQLite behind a CGI executable? Does it work OK?


    ------------------

  • #2
    I've used SQLite with PHP without issue. (If not familiar,
    PHP {www.php.net} is a CGI scripting language, and is
    possibly/probably the most popular.) However, because SQLite
    just provides you access to data (you do the CGI part on your
    own) you need to look at the strengths/weaknesses of SQLite
    for your purposes.

    Here's a quote from an article describing SQLite's strengths
    and weaknesses: (http://devzone.zend.com/node/view/id/760)
    Like every tool, SQLite has its strengths and weaknesses.
    While being an ideal solution for small and/or mostly-read
    applications, it is not well suited for large-scale
    applications performing frequent writes. This limitation
    is due to SQLite’s single file based architecture, which
    doesn’t allow multiplexing across servers, or the usage
    of database-wide locks on writes.
    Hope this helps.

    -John

    ------------------
    LOCAL MyEMail AS STRING , MySkype AS STRING
    MyEmail = STRREVERSE$("letnitj") & CHR$(64) & STRREVERSE$("liamg") & CHR$(46) & STRREVERSE$("moc")
    MySkype = STRREVERSE$("adirolftj")
    LOCAL MyEMail AS STRING
    MyEmail = STRREVERSE$("53pmohtj") & CHR$(64) & STRREVERSE$("liamg") & CHR$(46) & STRREVERSE$("moc")

    Comment


    • #3
      John,
      Interesting quote, which looks ambiguous but I think means that beacuse write locks are global, shared write access performance can be poor.
      SQLite' own comments about transactions:
      After a BEGIN IMMEDIATE, you are guaranteed that no other thread or process will be able to write to the database or do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE. Other processes can continue to read from the database, however. An exclusive transaction causes EXCLUSIVE locks to be acquired on all databases. After a BEGIN EXCLUSIVE, you are guaranteed that no other thread or process will be able to read or write the database until the transaction is complete.
      Sounds like a database-wide lock to me!

      Depends on whether the performance degradation hits you when you have 2, 20 or 200 users active.


      ------------------

      Comment


      • #4
        I have tried it and it did work in my CGI test environment.
        It works just as you'd expect, no surprises in my limited
        experience with it. It works just like it does in a local
        environment.

        FYI: I started a project using sqlLite but ended up moving
        to mySql for other reasons (not a problem with sqlLite)

        ------------------

        Comment


        • #5
          I've been toying with a client/server front end for SQLite, by basically
          cuttin' and pastin' code from these forums. Starting with a threaded
          server example from [email protected], then Stan Durham's excellent
          SQLite wrappers, and some other code gleaned from the forums.

          Here's the client (can't get much easier than this!). It was written as
          a CGI, to simply run a SQL query and return the results to the browser:

          An example URL for this sample, assmuing GetZip.exe is in your web server's
          cgi-bin directory, and sls.exe is running with a zipcode database (you can
          get a pre-built SQLite zipcode database here: http://www.eggheadcafe.com/articles/20050514.asp )

          http://localhost/cgi-bin/GetZip.exe?...02&maxrows=999

          Code:
          #COMPILE EXE "GetZip.exe"
          #DIM ALL
          
          #INCLUDE "Win32Api.inc"
          #INCLUDE "PBCGIBOTH.INC"
          
          $SERVER = "127.0.0.1"
          %IP_PORT = 8090
          
          SUB Alert (msg AS STRING)
              STDOUT "<?xml version='1.0' encoding='UTF-8' ?>" & $CRLF
              STDOUT "<result><error>" & msg & "</error></result>" & $CRLF
          END SUB
          
          FUNCTION getValue(arr() AS STRING, key AS STRING) AS STRING
              LOCAL i AS LONG
          
              '-- Find the key
              FOR i = 1 TO UBOUND(arr)
                  '-- Return the matching value
                  IF PARSE$(LCASE$(arr(i)),"=",1) = LCASE$(key) THEN
                      FUNCTION = PARSE$(arr(i),"=",2)
                      EXIT FUNCTION
                  END IF
              NEXT
          
              FUNCTION = ""
          
          END FUNCTION
          
          FUNCTION PBMAIN () AS LONG
          
              LOCAL nSocket AS LONG
              LOCAL sBuffer AS STRING
              LOCAL sPacket AS STRING
              LOCAL i             AS LONG
              LOCAL s             AS STRING
              LOCAL pc            AS LONG
              LOCAL cgi           AS STRING
              LOCAL cgi_arr()     AS STRING
              LOCAL cv            AS STRING
              LOCAL cnt           AS LONG
              LOCAL p             AS LONG
              LOCAL maxRows       AS LONG
          
              STDOUT "Content-type: text/xml" & $CRLF & $CRLF
              cgi = ReadCGI()
              IF cgi = "" THEN cgi = ENVIRON$("QUERY_STRING")
              cgi = TRIM$(cgi,ANY $CRLF)
              pc = PARSECOUNT(cgi,"&")
              REDIM cgi_arr(1 TO pc)
              PARSE cgi,cgi_arr(),"&"
          
              cv = getValue(cgi_arr(),"currentValue")
              IF cv = "" THEN
                  Alert "Invalid Request"
                  EXIT FUNCTION
              END IF
              maxRows = VAL(getValue(cgi_arr(),"maxRows"))
              IF maxRows = 0 THEN maxRows = 10
          
              TCP OPEN PORT %IP_PORT AT $SERVER AS nSocket TIMEOUT 15000
              IF ERR THEN
                  Alert "Error opening port: " + STR$(ERR)
                  EXIT FUNCTION
              END IF
          
              TCP PRINT nSocket, "SELECT * FROM zipcodes WHERE zip LIKE '" & cv & "%' LIMIT" & STR$(maxRows) & ";"
          
              ERRCLEAR
              DO
                  TCP RECV nSocket, 1024, sBuffer
                  STDOUT sBuffer
              LOOP WHILE ISTRUE LEN(sBuffer) AND ISFALSE ERR
          
              TCP CLOSE nSocket
          
          END FUNCTION
          And the server (needs work, but works OK for testing. Be sure to change
          the path to your database:

          Code:
          #COMPILE EXE "sls.exe"
          
          #INCLUDE "Win32Api.inc"
          #INCLUDE "Wsock32.inc"
          #INCLUDE "SQLite.inc"
          
          '************************ FULL path to SQLite database file ***********
          $DBFILE = "C:\Program Files\SQLite Zipcode\zipcodes.db3"  ' <<<--- CHANGE!!!!!!
          '**********************************************************************
          
          %IP_PORT = 8090
          
          '-- Cleanup some of the "bad" chars
          FUNCTION xmlClean (sInc AS STRING) AS STRING
          
              LOCAL sRet AS STRING
              LOCAL i AS LONG
              LOCAL ch AS INTEGER
          
              IF sInc = $NUL OR LEN(sInc) = 0 THEN
                  sRet = " "
              ELSE
                  sRet = TRIM$(sInc)
              END IF
          
              REPLACE "'" WITH "&apos;" IN sRet
              REPLACE "&" WITH "&" IN sRet
              REPLACE $DQ WITH """ IN sRet
          
              FUNCTION = sRet
          
          END FUNCTION
          
          SUB WriteSocket(BYVAL hSocket AS LONG, sText AS STRING)
          
              LOCAL p AS LONG
              LOCAL ch AS ASCIIZ * 1024
          
              FOR p = 1 TO LEN(sText) STEP 1024
                  ch = MID$(sText, p, 1024)
                  SSEND hSocket, ch, LEN(ch), 0
              NEXT
          
          END SUB
          
          '-- Run SQL query, write results to hSocket
          FUNCTION ExecQuery(hDB AS LONG, sSQL AS STRING, BYVAL hSocket AS LONG) AS LONG
          
              LOCAL hSQL AS LONG
              LOCAL i AS LONG
              LOCAL iResult AS LONG
              LOCAL iCount AS LONG
              LOCAL sErr AS STRING
              LOCAL sValue AS STRING
              LOCAL rs AS sqlRecSetType
          
          
              'initiate sqlRecSetType before using
              sqlRecSetNew(rs, hDB, LOCAL)
          
              WriteSocket hSocket, "<?xml version='1.0' encoding='UTF-8' ?>" & $CRLF
          
              hSQL = sqlSelect(rs, sSQL)
              IF hSQL = 0 THEN
                  WriteSocket hSocket, "<result><error>" & sqlErrMsg(hDB) & "</error></result>" & $CRLF
                  sqlFree rs
                  FUNCTION = 1
                  EXIT FUNCTION
              END IF
          
              WriteSocket hSocket, "<result>" & $CRLF
          
              '- loop through them
              iCount = 0
              IF sqlRowCount(rs) THEN
                  WriteSocket hSocket,  "<cols>" & $CRLF
          
                  FOR i = 1 TO sqlColCount(rs)
                      WriteSocket hSocket, "<col id='" & TRIM$(STR$(i)) & "' name='" & xmlClean(TRIM$(sqlColName(rs,i))) & "' />" & $CRLF
                  NEXT i
          
                  WriteSocket hSocket, "</cols>" & $CRLF
                  WriteSocket hSocket, "<rows>" & $CRLF
          
                  IF sqlRowCount(rs) THEN
                      sqlMoveFirst rs
                      WHILE ISFALSE sqlEOF(rs)
                          INCR iCount
          
                          WriteSocket hSocket, "<row id='" & TRIM$(STR$(iCount)) & "' >" & $CRLF
                          FOR i = 1 TO sqlColCount(rs)
                              WriteSocket hSocket, "<col id='" & TRIM$(STR$(i)) & "'>" & xmlClean(sqlGetAt(rs, i)) & "</col>" & $CRLF
                          NEXT i
          
                          WriteSocket hSocket,  "</row>" & $CRLF
                          sqlMoveNext rs
                      WEND
                  END IF
          
                  WriteSocket hSocket, "</rows>" & $CRLF
          
              END IF
          
              WriteSocket hSocket, "</result>" & $CRLF
          
              sqlFree rs
          
              FUNCTION = 0
          
          END FUNCTION
          
          
          FUNCTION ServiceChildWorkerThread( BYVAL hSocket AS LONG ) AS LONG
              '--------------------------------------------------------------------------------------------------------
              ' Author    :   [email protected]
              ' Purpose   :   Actual POP 3 Service Entry point
              ' Date      :   1/26/2003
              ' Updates   :   1/26/2003 - Service Created
              '               8/8/2004  - Major clean up of code
              '--------------------------------------------------------------------------------------------------------
              LOCAL ms_inBuffer           AS STRING
              LOCAL ms_command            AS STRING
          
              LOCAL sIp                   AS ASCIIZ PTR
              LOCAL ms_buffer             AS ASCIIZ * 1
              LOCAL ms_prompt             AS ASCIIZ * 16834
              LOCAL ml_result             AS LONG
              LOCAL sock_sa               AS sockaddr_in
              LOCAL iCount                AS LONG
              LOCAL ml_flags              AS LONG
          
              LOCAL hDB                   AS LONG
              LOCAL sQuery                AS STRING
              LOCAL ret                   AS LONG
          
              ml_flags = 0
          
          
              'the sock io settings
              cmd& = %FIONBIO
              argp& = 1
              n& = ioctlsocket( hSocket, cmd&, argp& )
          
              'we need to retrieve the remote name and ip address
              'to compare against our blacklist
              ml_result = getpeername(hSocket,sock_sa,SIZEOF(sock_sa))
              IF ml_result <> %SOCKET_ERROR THEN
                  sIp = inet_ntoa(sock_sa.sin_addr.s_addr)
                  IF sIp <> %NULL THEN
                      HOST NAME sock_sa.sin_addr.s_addr TO hostname$
                  END IF
              END IF
          
              '-- Open database
              sqlOpen($DBFILE, hDB)
              IF hDB = 0 THEN
                  closesocket hSocket
                  EXIT FUNCTION
              END IF
          
              DO
                  'collect the buffer
                  i% = RRECV( hSocket, ms_buffer, SIZEOF( ms_buffer ), 0 )
                  'tight loop so lets release the cpu cycle again
                  SLEEP 20
                  IF i% = 0 THEN ' OR i% = -1 THEN
                      'check to see that the socket didnt detach
                      closeSocket hSocket
                      IF hDB THEN
                          sqlClose hDB
                      END IF
                      EXIT FUNCTION
                   END IF
                  IF i% > 0 THEN
                      'add to the buffer
                      ms_inBuffer = ms_inBuffer & ms_buffer
                      'and clean out the old buffer
                      ms_buffer = ""
          
                      'if we have a ;crlf then this command is completed so exit the do
                      IF RIGHT$(ms_inBuffer, 3) = ";" & $CRLF THEN EXIT DO
                  END IF
              LOOP
          
              ret = ExecQuery(hDB, ms_inBuffer, hSocket)
          
              closesocket hSocket
          
              '-- Close the database
              IF hDB THEN
                  sqlClose hDB
              END IF
          
          
          END FUNCTION
          
          FUNCTION ListenThread(BYVAL Id AS LONG) AS LONG
          '--------------------------------------------------------------------------------------------------------
          ' Author    :   [email protected]
          ' Purpose   :   Service Entry point
          ' Date      :   1/26/2003
          ' Updates   :   1/26/2003 - Service Created
          '               1/26/2003 - Added WaitForMultipleObjects
          '--------------------------------------------------------------------------------------------------------
              DIM result      AS WORD
              DIM wDat        AS WSAdata
              DIM Sockin      AS Sockaddr_in
              DIM s           AS LONG
              DIM ns          AS LONG
              DIM pid         AS LONG
              DIM ms_buffer   AS ASCIIZ * 512
              DIM FF          AS INTEGER
              DIM wPort       AS LONG
              
              LOCAL hDB       AS LONG
          
              wPort = %IP_PORT
              result = WsaStartup( 257, wdat )                            'lets start up winsock
              s = socket( %PF_INET, %SOCK_STREAM, %IPPROTO_TCP )          'and allocate a socket handle
          
              IF s = - 1 THEN
                  EXIT FUNCTION
              END IF
          
              '--------------------------------------------------------------------------------------------------------
              'set the socket information
              '--------------------------------------------------------------------------------------------------------
              sockin.sin_family = %AF_INET
              sockin.sin_port = htons( wPort )
          '    sockin.sin_addr.s = Chr$( 0, 0, 0, 0 ) '''''htonl(%INADDR_ANY)
              sockin.sin_addr.s = CHR$(127,0,0,1)  '-- Limit to localhost only
              n& = bind( s, Sockin, SIZEOF( Sockin ))                     'bind to the socket
              IF n& = - 1 THEN                                            'if we couldnt bind to the socket lets exit
                  closesocket S                                           'process error here
                  wsaCleanup
                  EXIT FUNCTION
              END IF
          
              '--------------------------------------------------------------------------------------------------------
              'listen on the port
              '--------------------------------------------------------------------------------------------------------
              n& = listen( s, 5 )
          
              IF n& = -1 THEN
                  'this normally occurs if winsock is corrupt or installed incorrectly
                  closesocket S
                  wsaCleanup
                  EXIT FUNCTION
              END IF
          
              '-- Open database
              sqlOpen($DBFILE, hDB)
              
              '--------------------------------------------------------------------------------------------------------
              'Main listen loop
              '--------------------------------------------------------------------------------------------------------
              DO
          
                  SLEEP 20
                  ns = AACCEPT( s, Sockin, SIZEOF( Sockin ))              'this is a blocking call and will
                                                                          'return if a client is attempting to connect
                  IF ns = - 1 THEN
                      'some funky error occured here so close the
                      'socket and exit
                      closesocket S
                      wsaCleanup
                      EXIT FUNCTION
          
                  END IF
                  'if we reach here there is a user attempting to connect
                  THREAD CREATE ServiceChildWorkerThread( ns ) TO dummy&
                  THREAD CLOSE dummy& TO dummy&
              LOOP
          
              closesocket S
              wsaCleanup
              
              '-- Close the database
              IF hDB THEN
                  sqlClose hDB
              END IF
          
          
          END FUNCTION
          
          FUNCTION PBMAIN() AS LONG
          
              LOCAL hListenThread AS LONG
              LOCAL lResult AS LONG
          
              '-- Create listen thread
              THREAD CREATE ListenThread(0) TO hListenThread
          
              '-- Wait for thread to finish
              WaitForSingleObject hListenThread, %INFINITE
              
              '-- Cleanup
              THREAD CLOSE hListenThread TO lResult
          
          END FUNCTION

          Updated with Michael's suggestion. Note that the BBS messes up a couple of
          lines in XmlClean():

          REPLACE "'" WITH "#apos;" IN sRet
          REPLACE "&" WITH "#amp;" IN sRet
          REPLACE $DQ WITH "#quot;" IN sRet

          Replace the "#" with "&" to get the original code.

          ------------------


          --pdf



          [This message has been edited by Paul Franks (edited January 16, 2007).]
          --pdf

          Comment


          • #6
            Just a little 'finer point'....
            Code:
            DO 
               SLEEP 20 
               THREAD STATUS hListenThread TO lResult
            LOOP WHILE lResult <> 0
            This only works correctly if the thread function always returns zero.

            Better, since main thread of execution is doing nothing anyway...

            Code:
            WaitForSingleObject hListenThread, %INFINITE
            Besides, Windows 'waits' far more efficiently than does any polling code.

            Michael Mattias
            Tal Systems (retired)
            Port Washington WI USA
            [email protected]
            http://www.talsystems.com

            Comment


            • #7
              Originally posted by Michael Mattias:

              Better, since main thread of execution is doing nothing anyway...

              Code:
              WaitForSingleObject hListenThread, %INFINITE
              Thanks, Michael, I'll try that. Like I said, it needs work...


              ------------------

              --pdf
              --pdf

              Comment

              Working...
              X