''
''
''   SQLite3 class (Public Domain code - enjoy). 
''   Paul Squires of PlanetSquires Software (August 2015)
''   A simple class interface to the SQLite3 database DLL.
''
''   Compiler:  FreeBASIC 1.03 (32-bit) 
''

#Include Once "sqlite3.bi"


Type clsSqlite

   Private:
      m_dbHandle     As sqlite3 Ptr     ' Database connection handle
      m_LastError    As Long         ' Current error code

   Public:
      Declare Destructor
      Declare Function Version() As String
      Declare Function SourceID() As String
      Declare Function VersionNumber() As Long
      Declare Property dbHandle() As sqlite3 Ptr
      Declare Property dbHandle( nValue As sqlite3 Ptr ) 
      Declare Property LastError() As Long
      Declare Property LastError( nValue As Long ) 
      Declare Function OpenDatabase( DBName As String ) As Long
      Declare Function CloseDatabase() As Long
      Declare Function SqlExec( ByVal sql As String ) As Long
      Declare Function SafeSql( ByVal sql As String ) As String
      Declare Sub      StartTransaction() 
      Declare Sub      EndTransaction() 

End Type


Type clsSqliteQuery 

   Private:
      m_hStmt As sqlite3_stmt Ptr 
      m_LastError As Long
      
   Public:
      Declare Destructor
      Declare Function GetRow() As BOOLEAN
      Declare Function PrepareQuery( ByRef db As clsSqlite, ByRef sSQL As String ) As Long
      Declare Property hStmt() As sqlite3_stmt Ptr
      Declare Property hStmt( nValue As sqlite3_stmt Ptr ) 
      Declare Property LastError() As Long
      Declare Property LastError( nValue As Long ) 
      Declare Function ColumnCount() As Long
      Declare Function ColumnName( ByVal nCol As Long ) As String
      Declare Function ColumnType( ByVal nCol As Long ) As Long
      Declare Function ColumnDeclType( ByVal nCol As Long ) As String
      Declare Function GetText Overload ( ByVal nCol As Long ) As String
      Declare Function GetText Overload ( ByVal sColName As String ) As String
      Declare Function GetColIndex( ByVal sColName As String ) As Long
      Declare Function FinalizeQuery() As Long
      Declare Function ResetQuery() As Long
      Declare Function BindQuery Overload ( ByRef nPos As Long, ByRef nInteger As Long) As Long
      Declare Function BindQuery Overload ( ByRef nPos As Long, ByRef nInt64 As sqlite3_int64) As Long
      Declare Function BindQuery Overload ( ByRef nPos As Long, ByRef nDouble As Double) As Long
      Declare Function BindQuery Overload ( ByRef nPos As Long ) As Long    ' to bind a NULL
      Declare Function BindQuery Overload ( ByRef nPos As Long, ByVal sText As Const ZString Ptr) As Long
      Declare Function BindQueryBlob( ByRef nPos As Long, ByVal pBinary As Any Ptr, ByVal nLength As Long) As Long
End Type




''  ---------------------------------------------------------------------
''  clsSQLITE
''  ---------------------------------------------------------------------
''
''  Close any open database when object is destroyed
''
Destructor clsSqlite
   this.CloseDatabase
End Destructor


''
''  Returns the SQLite3 version string.
''
Function clsSqlite.Version() As String
   Dim psz As Const ZString Ptr
   psz = sqlite3_libversion
   If psz Then Function = *psz
End Function


''
''  Returns the SQLite3 version number.
''
Function clsSqlite.VersionNumber() As Long
   Function = sqlite3_libversion_number()
End Function


''
''  Returns the SQLite3 source ID string.
''
Function clsSqlite.SourceID() As String
      Dim psz As Const ZString Ptr
      psz = sqlite3_sourceid
      If psz Then Function = *psz
End Function


''
''  dbHandle (Property - handle to the open database)
''
Property clsSqlite.dbHandle() As sqlite3 Ptr
   Property = this.m_dbHandle
End Property

Property clsSqlite.dbHandle( nValue As sqlite3 Ptr )
   this.m_dbHandle = nValue
End Property


''
''  LastError (Property - error code from most recent sqlite function call)
''
Property clsSqlite.LastError() As Long
   Property = this.m_LastError
End Property

Property clsSqlite.LastError( nValue As Long )
   this.m_LastError = nValue
End Property


''
''  Opens database and returns error code (if any) 
''
Function clsSqlite.OpenDatabase( ByRef DBName As String ) As Long
   Dim rc As Long = sqlite3_open( DBName, @m_dbHandle)
   this.LastError = rc
   If (rc <> SQLITE_OK) Then
       this.CloseDatabase
   End If
   Function = rc
End Function 


''
''  Close active database 
''
Function clsSqlite.CloseDatabase() As Long
   Dim rc As Long = sqlite3_close(m_dbHandle)
   this.LastError = rc
   this.dbHandle = 0
   Function = rc
End Function 


''
''  Run zero or more semicolon separated SQL statements.
''
Function clsSqlite.SqlExec( ByVal sql As String ) As Long
   Dim errmsg As ZString Ptr   
   this.LastError = sqlite3_exec(this.dbHandle, sql, 0, 0, @errmsg)
   sqlite3_free(errmsg)      
   Function = this.LastError
End Function


''
''  Safely escape embedded quotes within an sql statement
''
Function clsSqlite.SafeSql( ByVal sql As String ) As String
   If Len(sql) = 0 Then Exit Function   ' prevents mprintf from returning "(NULL)" literal string
   Dim pzSql As ZString Ptr
   pzSql = sqlite3_mprintf("%q", sql)
   Function = *pzSql
   sqlite3_free(pzSql)  
End Function


''
''  Start/End Transaction (inserts much faster into database)
''
Sub clsSqlite.StartTransaction() 
   this.SqlExec( "BEGIN IMMEDIATE TRANSACTION;" )
End Sub

Sub clsSqlite.EndTransaction() 
   this.SqlExec( "COMMIT TRANSACTION;" )
End Sub




''  ---------------------------------------------------------------------
''  clsSQLITEQUERY
''  ---------------------------------------------------------------------
 
''
''  Close any open query conection when the object is destroyed
''
Destructor clsSqliteQuery
   sqlite3_finalize(this.hstmt)
End Destructor


''
''  Prepare statement to execute (mostly SELECT queries) and step through the result set
''
Function clsSqliteQuery.PrepareQuery( ByRef db As clsSqlite, ByRef sSQL As String ) As Long
   this.LastError = sqlite3_prepare_v2( db.dbHandle, sSQL, Len(sSQL), @m_hStmt, 0 )
   Function = this.LastError 
End Function


''
''  Get the next row in the data set. Return TRUE if valid row returned, FALSE if no more records.
''
Function clsSqliteQuery.GetRow() As BOOLEAN
   this.LastError = sqlite3_step(this.hStmt)
   If this.LastError = SQLITE_ROW Then 
      Function = True
   Else   
      Function = False
   End If   
End Function


''
''  hStmt (Property - handle to the compile statement)
''
Property clsSqliteQuery.hStmt() As sqlite3_stmt Ptr
   Property = this.m_hStmt
End Property

Property clsSqliteQuery.hStmt( nValue As sqlite3_stmt Ptr )
   this.m_hStmt = nValue
End Property


''
''  LastError (Property - error code from most recent sqlite function call)
''
Property clsSqliteQuery.LastError() As Long
   Property = this.m_LastError
End Property

Property clsSqliteQuery.LastError( nValue As Long )
   this.m_LastError = nValue
End Property


''
''  ColumnCount (Return the number of columns in the recordset)
''
Function clsSqliteQuery.ColumnCount() As Long
   Function = sqlite3_column_count( this.hStmt )
End Function


''
''  ColumnName (Return the column name based on ordinal position in recordset)
''
Function clsSqliteQuery.ColumnName( ByVal nCol As Long ) As String
   Function = *sqlite3_column_name( this.hStmt, nCol )
End Function


''
''  ColumnType (Return the column type based on ordinal position in recordset)
''
Function clsSqliteQuery.ColumnType( ByVal nCol As Long ) As Long
   Function = sqlite3_column_type( this.hStmt, nCol )
   ' SQLITE_INTEGER 1
   ' SQLITE_FLOAT   2 
   ' SQLITE_TEXT    3
   ' SQLITE_BLOB    4
   ' SQLITE_NULL    5
End Function


''
''  ColumnDeclType (Return the column declared type (from TABLE) based on ordinal position in recordset)
''
Function clsSqliteQuery.ColumnDeclType( ByVal nCol As Long ) As String
   Function = *sqlite3_column_decltype( this.hStmt, nCol )
End Function


''
''  GetText (Return the column text value based on ordinal position in recordset)
''
Function clsSqliteQuery.GetText Overload ( ByVal nCol As Long ) As String
   Function = *Cast(ZString Ptr, sqlite3_column_text( this.hStmt, nCol ))
End Function

''
''  GetText (Return the column text value based on column name in recordset)
''
Function clsSqliteQuery.GetText Overload ( ByVal sColName As String ) As String
   Dim nCol As Long = this.GetColIndex( sColName )
   Function = *Cast(ZString Ptr, sqlite3_column_text( this.hStmt, nCol ))
End Function


''
''  (Private) GetColIndex (Return column position based on column name)
''
Function clsSqliteQuery.GetColIndex( ByVal sColName As String ) As Long
   Dim NumCols As Long = this.ColumnCount 
   Dim i As Long 
   
   sColName = Ucase(sColName)
   
   For i = 0 To NumCols - 1
      If Ucase(this.ColumnName(i)) = sColName Then
         Function = i: Exit Function
      End If
   Next
         
End Function


''
''  Finalize a statement connection and release resources
''
Function clsSqliteQuery.FinalizeQuery() As Long
   this.m_LastError = sqlite3_finalize(this.hStmt)
   this.hStmt = 0
   Function = this.m_LastError
End Function


''
''  Reset a query connection so it can be reused without recompilation
''
Function clsSqliteQuery.ResetQuery() As Long
   this.m_LastError = sqlite3_reset(this.hStmt)
   Function = this.m_LastError
End Function


''
''  Bind Reset a query connection so it can be reused without recompilation
''
Function clsSqliteQuery.BindQuery Overload ( ByRef nPos As Long, ByRef nInteger As Long) As Long
   this.m_LastError = sqlite3_bind_int(this.hStmt, nPos, nInteger)
   Function = this.m_LastError
End Function

Function clsSqliteQuery.BindQuery Overload ( ByRef nPos As Long, ByRef nInt64 As sqlite3_int64) As Long
   this.m_LastError = sqlite3_bind_int64(this.hStmt, nPos, nInt64)
   Function = this.m_LastError
End Function

Function clsSqliteQuery.BindQuery Overload ( ByRef nPos As Long, ByRef nDouble As Double) As Long
   this.m_LastError = sqlite3_bind_double(this.hStmt, nPos, nDouble)
   Function = this.m_LastError
End Function

Function clsSqliteQuery.BindQuery Overload ( ByRef nPos As Long ) As Long
   this.m_LastError = sqlite3_bind_null(this.hStmt, nPos)
   Function = this.m_LastError
End Function

Function clsSqliteQuery.BindQuery Overload ( ByRef nPos As Long, ByVal sText As Const ZString Ptr) As Long
   this.m_LastError = sqlite3_bind_text(this.hStmt, nPos, sText, -1, SQLITE_TRANSIENT)
   Function = this.m_LastError
End Function

Function clsSqliteQuery.BindQueryBlob( ByRef nPos As Long, ByVal pBinary As Any Ptr, ByVal nLength As Long) As Long
   this.m_LastError = sqlite3_bind_blob(this.hStmt, nPos, pBinary, nLength, SQLITE_TRANSIENT)
   Function = this.m_LastError
End Function