sqlite.inc

Powerbasic 源码 这是对Sqlite这种数据库的操作.

    ELSEIF tRS.RowNo>tRS.RowCount THEN
        tRS.IsEof = -1
    END IF
    tRS.FirstColNdx = tRS.RowNo * tRS.ColCount
END SUB
' --------------------------------------------------
' --------------------------------------------------
SUB sqlMoveNext( tRS AS sqlRecSetType )
    ' move next row
    INCR tRS.RowNo
    tRS.IsBof = 0
    tRS.IsEof = 0
    IF tRS.RowCount<1 THEN
        tRS.IsBof = -1
        tRS.IsEof = -1
    ELSEIF tRS.RowNo<1 THEN
        tRS.IsBof = -1
    ELSEIF tRS.RowNo>tRS.RowCount THEN
        tRS.IsEof = -1
    END IF
    tRS.FirstColNdx = tRS.RowNo * tRS.ColCount
END SUB
' --------------------------------------------------
' --------------------------------------------------
SUB sqlMovePrev( tRS AS sqlRecSetType )
    ' move backwards
    DECR tRS.RowNo
    tRS.IsBof = 0
    tRS.IsEof = 0
    IF tRS.RowCount<1 THEN
        tRS.IsBof = -1
        tRS.IsEof = -1
    ELSEIF tRS.RowNo<1 THEN
        tRS.IsBof = -1
    ELSEIF tRS.RowNo>tRS.RowCount THEN
        tRS.IsEof = -1
    END IF
    tRS.FirstColNdx = tRS.RowNo * tRS.ColCount
END SUB
' --------------------------------------------------
' --------------------------------------------------
SUB sqlMoveLast( tRS AS sqlRecSetType )
    'move to last row
    tRS.RowNo = tRS.RowCount
    tRS.IsBof = 0
    tRS.IsEof = 0
    IF tRS.RowCount<1 THEN
        tRS.IsBof = -1
        tRS.IsEof = -1
    ELSEIF tRS.RowNo<1 THEN
        tRS.IsBof = -1
    ELSEIF tRS.RowNo>tRS.RowCount THEN
        tRS.IsEof = -1
    END IF
    tRS.FirstColNdx = tRS.RowNo * tRS.ColCount
END SUB
' --------------------------------------------------
' --------------------------------------------------
FUNCTION sqlGet( tRS AS sqlRecSetType, BYVAL sColName AS STRING ) AS STRING
    LOCAL x, ndx AS LONG
    LOCAL pzField AS ASCIIZ PTR  'Field return from get_table (element in tRS.@lpTable array)
    '    get column value by column name
    x = sqlColNo(tRS, sColName)
    IF x<1 THEN EXIT FUNCTION
    ndx = tRS.FirstColNdx + x - 1
    pzField  = tRS.@pTable[ndx]
    FUNCTION = @pzField
END FUNCTION
' --------------------------------------------------
' --------------------------------------------------
FUNCTION sqlGetPZ( tRS AS sqlRecSetType, BYVAL sColName AS STRING ) AS LONG
    LOCAL x, ndx AS LONG
    LOCAL pzField AS ASCIIZ PTR  'Field return from get_table (element in tRS.@lpTable array)
    '
    '    return ASCIIZ PTR to column value
    '
    '    Fast way to load ListView
    '
    x         = sqlColNo(tRS, sColName)
    IF x<1 THEN EXIT FUNCTION
    ndx       = tRS.FirstColNdx + x - 1
    FUNCTION  = tRS.@pTable[ndx]
END FUNCTION
' --------------------------------------------------
' --------------------------------------------------
FUNCTION sqlGetAt( tRS AS sqlRecSetType, BYVAL lColNo AS LONG ) AS STRING
    LOCAL ndx AS LONG
    LOCAL pzField AS ASCIIZ PTR  'Field return from get_table (element in tRS.pTable array)
    '    get column value by column number
    '    first column = 1
    IF lColNo<1 OR lColNo>tRS.ColCount THEN EXIT FUNCTION
    ndx = tRS.FirstColNdx + lColNo - 1
    pzField = tRS.@pTable[ndx]
    FUNCTION = @pzField
END FUNCTION
' --------------------------------------------------
' --------------------------------------------------
FUNCTION sqlGetAtPZ( tRS AS sqlRecSetType, BYVAL lColNo AS LONG ) AS LONG
    LOCAL ndx AS LONG
    LOCAL pzField AS ASCIIZ PTR  'Field return from get_table (element in tRS.pTable array)
    '
    '    return ASCIIZ PTR to column value by column number
    '    first column = 1
    '
    '    Fast way to load ListView
    '
    IF lColNo<1 OR lColNo>tRS.ColCount THEN EXIT FUNCTION
    ndx       = tRS.FirstColNdx + lColNo - 1
    FUNCTION  = tRS.@pTable[ndx]
END FUNCTION
' --------------------------------------------------
' --------------------------------------------------
FUNCTION sqlColNo( tRS AS sqlRecSetType, BYVAL sColName AS STRING ) AS LONG
    ' get column number from record set column list
    ' return 0 = not found
    FUNCTION = sql_internal_StrArryPtr_ScanNoCase( BYVAL tRS.pCols, sColName )
END FUNCTION
' --------------------------------------------------
' --------------------------------------------------
FUNCTION sqlColName( tRS AS sqlRecSetType, BYVAL lColNo AS LONG ) AS STRING
    ' get column name from record set column list
    ' return "" = not found
    FUNCTION = sql_internal_StrArryPtr_Get( BYVAL tRS.pCols, lColNo )
END FUNCTION
' --------------------------------------------------
' --------------------------------------------------
FUNCTION sqlColTypeOf( BYVAL hDB AS LONG, BYVAL sTable AS STRING, BYVAL sColName AS STRING, BYVAL lRowID AS LONG ) AS STRING
    LOCAL sTypeOf AS STRING
    '    get current SQLite TYPE of column value
    '    REMEBER - SQLite can hold any value type in any column
    '    the same column - different row - can have a different TYPE
    '    The only return values are "null", "integer", "real", "text", and "blob".
    '
    '    the reason I added this - test for BLOBs
    '
    '    if a column (may or may not) have a blob in it - you will never know without testing
    '    the regular sqlGet() function in this LIB will return an empty string
    sqlSelectGet( hDB, "select typeof(["+sColName+"]) from ["+sTable+"] where rowid="+STR$(lRowID), sTypeOf )
    FUNCTION = sTypeOf
END FUNCTION
' --------------------------------------------------
' --------------------------------------------------
SUB sqlTableList( BYVAL hDB AS LONG, saTableList() AS STRING )
    LOCAL i AS LONG
    ' get Table List for database
    LOCAL rs AS sqlRecSetType
    sqlRecSetNew(rs, hDB, LOCAL)

    REDIM saTableList()
    IF ISFALSE hDB THEN GOTO HERE

    IF ISFALSE sqlSelect(rs, "select name from sqlite_master where type = 'table' order by name collate nocase") THEN EXIT SUB
    IF rs.RowCount<1 THEN GOTO HERE

    REDIM saTableList(1 TO rs.RowCount)
    FOR i=1 TO rs.RowCount
        sqlMoveTo rs, i
        saTableList(i) = sqlGetAt(rs, 1)
    NEXT i
    HERE:
    sqlFree rs
END SUB
' --------------------------------------------------
' --------------------------------------------------
FUNCTION sqlTableExist( BYVAL hDB AS LONG, BYVAL sTable AS STRING ) AS LONG
    LOCAL rs AS sqlRecSetType
    sqlRecSetNew(rs, hDB, LOCAL)
    ' see if sTable exist
    IF ISFALSE hDB THEN GOTO HERE
    IF ISFALSE sqlSelect(rs, "Select * From sqlite_master where name like '"+sTable+"' and type='table'") THEN EXIT FUNCTION
    IF ISTRUE rs.RowCount THEN
        FUNCTION = -1
    ELSE
        FUNCTION = 0
    END IF
    HERE:
    sqlFree rs
END FUNCTION
' --------------------------------------------------
' --------------------------------------------------
SUB sqlColList( BYVAL hDB AS LONG, BYVAL sTable AS STRING, saColList() AS STRING )
    LOCAL i AS LONG
    LOCAL rs AS sqlRecSetType
    sqlRecSetNew(rs, hDB, LOCAL)
    ' get column list for sTable
    REDIM saColList()
    IF ISFALSE hDB THEN GOTO HERE

    IF ISFALSE sqlSelect( rs, "PRAGMA table_info(["+sTable+"])") THEN GOTO HERE
    IF rs.RowCount<1 THEN GOTO HERE

    REDIM saColList(1 TO rs.RowCount)
    FOR i=1 TO rs.RowCount
        sqlMoveTo rs, i
        saColList(i) = sqlGetAt(rs, 2)
    NEXT i
    HERE:
    sqlFree rs
END SUB
' --------------------------------------------------
' --------------------------------------------------
FUNCTION sqlPrimKeyCol( BYVAL hDB AS LONG, BYVAL sTable AS STRING) AS STRING
    LOCAL rs AS sqlRecSetType
    sqlRecSetNew(rs, hDB, LOCAL)
    ' get primary key column
    ' unless you specified otherwise, it will be 'rowid'
    IF ISFALSE sqlSelect(rs, "select rowid from ["+sTable+"] limit 1") THEN GOTO HERE
    IF sqlRowCount(rs) THEN
        sqlMoveTo rs, 1
        FUNCTION = sqlColName(rs, 1)
    END IF
    HERE:
    sqlFree rs
END FUNCTION
' --------------------------------------------------
' --------------------------------------------------
SUB sqlIndexList( BYVAL hDB AS LONG, BYVAL sTable AS STRING, saIndexList() AS STRING )
    LOCAL i AS LONG
    LOCAL rs AS sqlRecSetType
    sqlRecSetNew(rs, hDB, LOCAL)
    '    get index list for sTable
    REDIM saIndexList()
    IF ISFALSE hDB THEN GOTO HERE

    IF ISFALSE sqlSelect( rs, "PRAGMA index_list(["+sTable+"])") THEN GOTO HERE
    IF rs.RowCount<1 THEN GOTO HERE

    REDIM saIndexList(1 TO rs.RowCount)
    FOR i=1 TO rs.RowCount
        sqlMoveTo rs, i
        saIndexList(i) = sqlGetAt(rs, 2)
    NEXT i
    HERE:
    sqlFree rs
END SUB
' --------------------------------------------------
' --------------------------------------------------
FUNCTION sqlGetColCreateSQL( BYVAL hDB AS LONG, BYVAL sTable AS STRING, BYVAL ColID AS LONG ) AS STRING
    LOCAL x AS LONG
    LOCAL s, sSql AS STRING
    LOCAL arry() AS STRING
    LOCAL rs AS sqlRecSetType

    '   get the creation SQL from
    '   SQLite Master for Column ID - starts at zero
    '   how the Column was actually created

    sqlRecSetNew(rs, hDB, LOCAL)
    IF ISFALSE hDB THEN GOTO HERE '>>>

    sSql = "select sql from sqlite_master where tbl_name='"+sTable+"'"
    IF sqlSelect(rs, sSql) THEN
        IF rs.ColCount AND rs.RowCount THEN
            sqlMoveFirst rs
            s = sqlGetAt(rs,1)
            REPLACE $CRLF WITH "" IN s
            REPLACE $CR WITH "" IN s
            REPLACE $LF WITH "" IN s
            REPLACE $TAB WITH "" IN s
            REPLACE "[" WITH "" IN s
            REPLACE "]" WITH "" IN s
            s = REMAIN$(s,"(")
            x = INSTR(-1, s, ")")
            s = LEFT$(s, x-1)
            s = TRIM$(s)
            REDIM arry(1 TO PARSECOUNT(s))
            PARSE s, arry()
            IF ColID>=0 AND ColID<=UBOUND(arry)-1 THEN
                FUNCTION = arry(ColID+1)
            END IF
        END IF
    END IF

    HERE:
    sqlFree rs
END FUNCTION
' --------------------------------------------------
' --------------------------------------------------
FUNCTION sqlFix( BYVAL sString AS STRING) AS STRING
    ' fix single quotes
    REPLACE "'" WITH "''" IN sString
    FUNCTION = sString
END FUNCTION
' --------------------------------------------------
' --------------------------------------------------
FUNCTION sqlDate( BYVAL sDate AS STRING ) AS STRING
    LOCAL d$, m$, y$
    '    change most dates to SQL date - 2006-01-25
    '
    '    basic date$ - ok
    '    11/1/05 - ok
    '    11,01,05 - ok
    '    11.1.2005 - ok
    '    1-23-05 - ok
    '    YYYYMMDD - ok
    '    1-23-99 won't fix = 2099-01-23
    IF sDate= ""  THEN EXIT FUNCTION
    REPLACE   "." WITH "," IN sDate
    REPLACE   "-" WITH "," IN sDate
    REPLACE   "/" WITH "," IN sDate
    REPLACE   "\" WITH "," IN sDate

    IF INSTR(sDate,",")=0 THEN
        ''' date is in YYYYMMDD format?
        FUNCTION = LEFT$(sDate,4) +"-"+ MID$(sDate,5,2) +"-"+ RIGHT$(sDate,2)
        EXIT FUNCTION
    END IF

    d$ = PARSE$(sDate,",",2)
    m$ = PARSE$(sDate,",",1)
    y$ = PARSE$(sDate,",",3)
    IF LEN(d$)=1 THEN d$ = "0"+d$
    IF LEN(m$)=1 THEN m$ = "0"+m$
    IF LEN(y$)=2 THEN y$ = "20"+y$

    FUNCTION = y$+"-"+m$+"-"+d$
END FUNCTION
' --------------------------------------------------
' --------------------------------------------------
FUNCTION sqlTimeStamp() AS STRING
    'current SQL Time Stamp = "2005-11-30 17:15:46"
    FUNCTION = sqlDate(DATE$) +" "+ TIME$
END FUNCTION
' --------------------------------------------------
' --------------------------------------------------
FUNCTION sqlBinToHex( BYVAL sBin AS STRING ) AS STRING
    LOCAL i, lAtHere, lLenBin AS LONG
    LOCAL sBlob, sOneChar, sHex AS STRING
    LOCAL bByte AS BYTE
    '
    '   see sqlBlobGet() & sqlBlobSet() for an easy wrapper around this
    '
    '   convert binary data into a HEX string
    '   INSERT using SQLites's X'FFFF' function
    '
    '   "insert into Tbl4 (AAA, BBB) values ('a1',X'"+sqlBinToHex(sIcon)+"')"
    '
    '   !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
    '   !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
    '   X'' - note the "X" and single quotes
    '   you have to insert it this way
    '   SQLite will convert and store the HEX value
    '   between the single quotes to binary
    '
    '   you have to use quote() to retrieve the binary value
    '   see sqlQuoteToBin() below
    '