lib/sqlite3/sqlite3.nit

   1 # This file is part of NIT ( http://www.nitlanguage.org ).
   2 #
   3 # Copyright 201 Alexis Laferrière <alexis.laf@xymus.net>
   4 #
   5 # Licensed under the Apache License, Version 2.0 (the "License");
   6 # you may not use this file except in compliance with the License.
   7 # You may obtain a copy of the License at
   8 #
   9 #     http://www.apache.org/licenses/LICENSE-2.0
  10 #
  11 # Unless required by applicable law or agreed to in writing, software
  12 # distributed under the License is distributed on an "AS IS" BASIS,
  13 # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  14 # See the License for the specific language governing permissions and
  15 # limitations under the License.
  16
  17 # Services to manipulate a Sqlite3 database
  18 #
  19 # For more information, refer to the documentation of http://www.sqlite.org/docs.html
  20 module sqlite3
  21
  22 private import native_sqlite3
  23 import standard
  24
  25 # A connection to a Sqlite3 database
  26 class Sqlite3DB
  27         private var native_connection: NativeSqlite3
  28
  29         # Is this connection to the DB open?
  30         var is_open = false
  31
  32         # All `Statement` opened from this connection that must be closed with this connection
  33         private var open_statements = new Array[Statement]
  34
  35         # Open a connection to the database file at `path`
  36         init open(path: Text)
  37         do
  38                 native_connection = new NativeSqlite3.open(path.to_s)
  39                 if native_connection.is_valid then is_open = true
  40         end
  41
  42         # Close this connection to the DB and all open statements
  43         fun close
  44         do
  45                 is_open = false
  46
  47                 # close open statements
  48                 for stmt in open_statements do if stmt.is_open then
  49                         stmt.close
  50                 end
  51
  52                 native_connection.close
  53         end
  54
  55         # Prepare and return a `Statement`, return `null` on error
  56         fun prepare(sql: Text): nullable Statement
  57         do
  58                 var native_stmt = native_connection.prepare(sql.to_s)
  59                 if native_stmt == null then return null
  60
  61                 var stmt = new Statement(native_stmt)
  62                 open_statements.add stmt
  63                 return stmt
  64         end
  65
  66         # Execute the `sql` statement and return `true` on success
  67         fun execute(sql: Text): Bool
  68         do
  69                 var err = native_connection.exec(sql.to_s)
  70                 return err.is_ok
  71         end
  72
  73         # Create a table on the DB with a statement beginning with "CREATE TABLE ", followed by `rest`
  74         #
  75         # This method does not escape special characters.
  76         fun create_table(rest: Text): Bool do return execute("CREATE TABLE " + rest)
  77
  78         # Insert in the DB with a statement beginning with "INSERT ", followed by `rest`
  79         #
  80         # This method does not escape special characters.
  81         fun insert(rest: Text): Bool do return execute("INSERT " + rest)
  82
  83         # Replace in the DB with a statement beginning with "REPLACE", followed by `rest`
  84         #
  85         # This method does not escape special characters.
  86         fun replace(rest: Text): Bool do return execute("REPLACE " + rest)
  87
  88         # Select from the DB with a statement beginning with "SELECT ", followed by `rest`
  89         #
  90         # This method does not escape special characters.
  91         fun select(rest: Text): nullable Statement do return prepare("SELECT " + rest)
  92
  93         # TODO add more prefix here as needed
  94
  95         # The latest error message, or `null` if there is none
  96         fun error: nullable String
  97         do
  98                 if not native_connection.is_valid then
  99                         var err = sys.sqlite_open_error
 100                         if err == null then return null
 101                         return err.to_s
 102                 end
 103
 104                 var err = native_connection.error
 105                 if err.is_ok then return null
 106                 return err.to_s
 107         end
 108 end
 109
 110 # A prepared Sqlite3 statement, created from `Sqlite3DB::prepare` or `Sqlite3DB::select`
 111 class Statement
 112         private var native_statement: NativeStatement
 113
 114         private init(ns: NativeStatement) do self.native_statement = ns
 115
 116         # Is this statement usable?
 117         var is_open = true
 118
 119         # Close and finalize this statement
 120         fun close
 121         do
 122                 is_open = false
 123                 native_statement.finalize
 124         end
 125
 126         # Reset this statement and return a `StatementIterator` to iterate over the result
 127         fun iterator: StatementIterator
 128         do
 129                 native_statement.reset
 130                 native_statement.step
 131                 return new StatementIterator(self)
 132         end
 133 end
 134
 135 class StatementRow
 136         # Statement linked to `self`
 137         var statement: Statement
 138
 139         private init(s: Statement) do self.statement = s
 140
 141         # Number of entries in this row
 142         #
 143         # require: `self.statement.is_open`
 144         fun length: Int
 145         do
 146                 assert statement_closed: statement.is_open
 147
 148                 return statement.native_statement.column_count
 149         end
 150
 151         # Returns the `i`th entry on this row
 152         fun [](i: Int): StatementEntry do return new StatementEntry(statement, i)
 153 end
 154
 155 # An entry on a `StatementRow`
 156 class StatementEntry
 157         # Statement linked to `self`
 158         var statement: Statement
 159
 160         private var index: Int
 161
 162         private init(s: Statement, i: Int)
 163         do
 164                 self.statement = s
 165                 self.index = i
 166         end
 167
 168         # Name of the column
 169         #
 170         # require: `self.statement.is_open`
 171         fun name: String is cached do
 172                 assert statement_closed: statement.is_open
 173
 174                 return statement.native_statement.column_name(index)
 175         end
 176
 177         # Get the value of this entry according to its Sqlite type
 178         #
 179         # require: `self.statement.is_open`
 180         fun value: nullable Sqlite3Data
 181         do
 182                 assert statement_closed: statement.is_open
 183
 184                 var data_type = statement.native_statement.column_type(index)
 185                 if data_type.is_integer then return to_i
 186                 if data_type.is_float then return to_f
 187                 if data_type.is_blob then return to_blob
 188                 if data_type.is_null then return null
 189                 if data_type.is_text then return to_s
 190                 abort
 191         end
 192
 193         # Get this entry as `Int`
 194         #
 195         # If the Sqlite type of this entry is not an integer, it will be `CAST` to
 196         # integer. If `null`, returns 0.
 197         #
 198         # require: `self.statement.is_open`
 199         fun to_i: Int
 200         do
 201                 assert statement_closed: statement.is_open
 202
 203                 return statement.native_statement.column_int(index)
 204         end
 205
 206         # Get this entry as `Float`
 207         #
 208         # If the Sqlite type of this entry is not a floating point, it will be `CAST`
 209         # to float. If `null`, returns 0.0.
 210         #
 211         # require: `self.statement.is_open`
 212         fun to_f: Float
 213         do
 214                 assert statement_closed: statement.is_open
 215
 216                 return statement.native_statement.column_double(index)
 217         end
 218
 219         # Get this entry as `String`
 220         #
 221         # If the Sqlite type of this entry is not text, it will be `CAST` to text.
 222         # If null, returns an empty string.
 223         #
 224         # require: `self.statement.is_open`
 225         redef fun to_s
 226         do
 227                 assert statement_closed: statement.is_open
 228
 229                 var native_string = statement.native_statement.column_text(index)
 230                 if native_string.address_is_null then return ""
 231                 return native_string.to_s
 232         end
 233
 234         # Get this entry as `Blob`
 235         #
 236         # If the Sqlite type of this entry is not a blob, it will be `CAST` to text.
 237         # If null, returns a NULL pointer.
 238         #
 239         # require: `self.statement.is_open`
 240         fun to_blob: Blob
 241         do
 242                 assert statement_closed: statement.is_open
 243
 244                 # By spec, we must get the pointer before the byte count
 245                 var pointer = statement.native_statement.column_blob(index)
 246                 var length = statement.native_statement.column_bytes(index)
 247
 248                 return new Blob(pointer, length)
 249         end
 250 end
 251
 252 # Iterator over the rows of a statement result
 253 class StatementIterator
 254         super Iterator[StatementRow]
 255
 256         # Statement linked to `self`
 257         var statement: Statement
 258
 259         private init(s: Statement)
 260         do
 261                 self.statement = s
 262                 self.item = new StatementRow(s)
 263         end
 264
 265         redef var item: StatementRow
 266
 267         redef var is_ok = true
 268
 269         # require: `self.statement.is_open`
 270         redef fun next
 271         do
 272                 assert statement_closed: statement.is_open
 273
 274                 var err = statement.native_statement.step
 275                 if err.is_row then
 276                         is_ok = true
 277                 else if err.is_done then
 278                         # Clean complete
 279                         is_ok = false
 280                 else
 281                         # error
 282                         # FIXME do something with the error?
 283                         is_ok = false
 284                 end
 285         end
 286 end
 287
 288 # A data type supported by Sqlite3
 289 interface Sqlite3Data end
 290
 291 redef universal Int super Sqlite3Data end
 292 redef universal Float super Sqlite3Data end
 293 redef class String super Sqlite3Data end
 294
 295 # A Sqlite3 blob
 296 class Blob
 297         super Sqlite3Data
 298
 299         private init(pointer: Pointer, length: Int)
 300         do
 301                 self.pointer = pointer
 302                 self.length = length
 303         end
 304
 305         var pointer: Pointer
 306         var length: Int
 307 end