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 core
  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                 init(new NativeSqlite3.open(path.to_cstring))
  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                 if not is_open then return
  46
  47                 is_open = false
  48
  49                 # close open statements
  50                 for stmt in open_statements do if stmt.is_open then
  51                         stmt.close
  52                 end
  53
  54                 native_connection.close
  55         end
  56
  57         # Prepare and return a `Statement`, return `null` on error
  58         fun prepare(sql: Text): nullable Statement
  59         do
  60                 var native_stmt = native_connection.prepare(sql.to_s)
  61                 if native_stmt == null then return null
  62
  63                 var stmt = new Statement(native_stmt)
  64                 open_statements.add stmt
  65                 return stmt
  66         end
  67
  68         # Execute the `sql` statement and return `true` on success
  69         fun execute(sql: Text): Bool
  70         do
  71                 var err = native_connection.exec(sql.to_s)
  72                 return err.is_ok
  73         end
  74
  75         # Create a table on the DB with a statement beginning with "CREATE TABLE ", followed by `rest`
  76         #
  77         # This method does not escape special characters.
  78         fun create_table(rest: Text): Bool do return execute("CREATE TABLE " + rest)
  79
  80         # Insert in the DB with a statement beginning with "INSERT ", followed by `rest`
  81         #
  82         # This method does not escape special characters.
  83         fun insert(rest: Text): Bool do return execute("INSERT " + rest)
  84
  85         # Replace in the DB with a statement beginning with "REPLACE", followed by `rest`
  86         #
  87         # This method does not escape special characters.
  88         fun replace(rest: Text): Bool do return execute("REPLACE " + rest)
  89
  90         # Select from the DB with a statement beginning with "SELECT ", followed by `rest`
  91         #
  92         # This method does not escape special characters.
  93         fun select(rest: Text): nullable Statement do return prepare("SELECT " + rest)
  94
  95         # TODO add more prefix here as needed
  96
  97         # The latest error message, or `null` if there is none
  98         fun error: nullable String
  99         do
 100                 if not native_connection.is_valid then
 101                         var err = sys.sqlite_open_error
 102                         if err == null then return null
 103                         return err.to_s
 104                 end
 105
 106                 var err = native_connection.error
 107                 if err.is_ok then return null
 108                 return err.to_s
 109         end
 110
 111         # Returns the id for the last successful insert on the current connection.
 112         fun last_insert_rowid: Int do return native_connection.last_insert_rowid
 113 end
 114
 115 # Prepared Sqlite3 statement
 116 #
 117 # Instances of this class are created from `Sqlite3DB::prepare` and
 118 # its shortcuts: `create_table`, `insert`, `replace` and `select`.
 119 # The results should be explored with an `iterator`,
 120 # and each call to `iterator` resets the request.
 121 # If `close_with_iterator` the iterator calls `close`
 122 # on this request upon finishing.
 123 class Statement
 124         private var native_statement: NativeStatement
 125
 126         # Is this statement usable?
 127         var is_open = true
 128
 129         # Should any `iterator` close this statement on `Iterator::finish`?
 130         #
 131         # If `true`, the default, any `StatementIterator` created by calls to
 132         # `iterator` invokes `close` on this request when finished iterating.
 133         # Otherwise, `close` must be called manually.
 134         var close_with_iterator = true is writable
 135
 136         # Close and finalize this statement
 137         fun close
 138         do
 139                 if not is_open then return
 140
 141                 is_open = false
 142                 native_statement.finalize
 143         end
 144
 145         # Reset this statement and return a `StatementIterator` to iterate over the result
 146         fun iterator: StatementIterator
 147         do
 148                 native_statement.reset
 149                 return new StatementIterator(self)
 150         end
 151 end
 152
 153 # A row from a `Statement`
 154 class StatementRow
 155         # Statement linked to `self`
 156         var statement: Statement
 157
 158         # Number of entries in this row
 159         #
 160         # require: `self.statement.is_open`
 161         fun length: Int
 162         do
 163                 assert statement_closed: statement.is_open
 164
 165                 return statement.native_statement.column_count
 166         end
 167
 168         # Returns the `i`th entry on this row
 169         fun [](i: Int): StatementEntry do return new StatementEntry(statement, i)
 170 end
 171
 172 # An entry on a `StatementRow`
 173 class StatementEntry
 174         # Statement linked to `self`
 175         var statement: Statement
 176
 177         private var index: Int
 178
 179         # Name of the column
 180         #
 181         # require: `self.statement.is_open`
 182         var name: String is lazy do
 183                 assert statement_closed: statement.is_open
 184
 185                 return statement.native_statement.column_name(index)
 186         end
 187
 188         # Get the value of this entry according to its Sqlite type
 189         #
 190         # require: `self.statement.is_open`
 191         fun value: nullable Sqlite3Data
 192         do
 193                 assert statement_closed: statement.is_open
 194
 195                 var data_type = statement.native_statement.column_type(index)
 196                 if data_type.is_integer then return to_i
 197                 if data_type.is_float then return to_f
 198                 if data_type.is_blob then return to_blob
 199                 if data_type.is_null then return null
 200                 if data_type.is_text then return to_s
 201                 abort
 202         end
 203
 204         # Get this entry as `Int`
 205         #
 206         # If the Sqlite type of this entry is not an integer, it will be `CAST` to
 207         # integer. If `null`, returns 0.
 208         #
 209         # require: `self.statement.is_open`
 210         fun to_i: Int
 211         do
 212                 assert statement_closed: statement.is_open
 213
 214                 return statement.native_statement.column_int(index)
 215         end
 216
 217         # Get this entry as `Float`
 218         #
 219         # If the Sqlite type of this entry is not a floating point, it will be `CAST`
 220         # to float. If `null`, returns 0.0.
 221         #
 222         # require: `self.statement.is_open`
 223         fun to_f: Float
 224         do
 225                 assert statement_closed: statement.is_open
 226
 227                 return statement.native_statement.column_double(index)
 228         end
 229
 230         # Get this entry as `String`
 231         #
 232         # If the Sqlite type of this entry is not text, it will be `CAST` to text.
 233         # If null, returns an empty string.
 234         #
 235         # require: `self.statement.is_open`
 236         redef fun to_s
 237         do
 238                 assert statement_closed: statement.is_open
 239
 240                 var native_string = statement.native_statement.column_text(index)
 241                 if native_string.address_is_null then return ""
 242                 return native_string.to_s_with_copy
 243         end
 244
 245         # Get this entry as `Blob`
 246         #
 247         # If the Sqlite type of this entry is not a blob, it will be `CAST` to text.
 248         # If null, returns a NULL pointer.
 249         #
 250         # require: `self.statement.is_open`
 251         fun to_blob: Blob
 252         do
 253                 assert statement_closed: statement.is_open
 254
 255                 # By spec, we must get the pointer before the byte count
 256                 var pointer = statement.native_statement.column_blob(index)
 257                 var length = statement.native_statement.column_bytes(index)
 258
 259                 return new Blob(pointer, length)
 260         end
 261 end
 262
 263 # Iterator over the rows of a statement result
 264 class StatementIterator
 265         super Iterator[StatementRow]
 266
 267         # Statement linked to `self`
 268         var statement: Statement
 269
 270         init
 271         do
 272                 self.item = new StatementRow(statement)
 273                 self.is_ok = statement.native_statement.step.is_row
 274         end
 275
 276         redef var item: StatementRow is noinit
 277
 278         redef var is_ok is noinit
 279
 280         # require: `self.statement.is_open`
 281         redef fun next
 282         do
 283                 assert statement_closed: statement.is_open
 284
 285                 var err = statement.native_statement.step
 286                 if err.is_row then
 287                         is_ok = true
 288                 else if err.is_done then
 289                         # Clean complete
 290                         is_ok = false
 291                 else
 292                         # error
 293                         # FIXME do something with the error?
 294                         is_ok = false
 295                 end
 296         end
 297
 298         redef fun finish do if statement.close_with_iterator then statement.close
 299 end
 300
 301 # A data type supported by Sqlite3
 302 interface Sqlite3Data end
 303
 304 redef universal Int super Sqlite3Data end
 305
 306 redef universal Float super Sqlite3Data end
 307
 308 redef class String
 309         super Sqlite3Data
 310
 311         # Return `self` between `'`s, escaping `\` and `'`
 312         #
 313         #     assert "'; DROP TABLE students".to_sql_string == "'''; DROP TABLE students'"
 314         fun to_sql_string: String
 315         do
 316                 return "'{self.replace('\\', "\\\\").replace('\'', "''")}'"
 317         end
 318 end
 319
 320 # A Sqlite3 blob
 321 class Blob
 322         super Sqlite3Data
 323
 324         # Pointer to the beginning of the blob
 325         var pointer: Pointer
 326
 327         # Size of the blob
 328         var length: Int
 329 end