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_cstring)
  61                 if native_stmt.address_is_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_cstring)
  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.is_ok 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                 var cname = statement.native_statement.column_name(index)
 186                 assert not cname.address_is_null
 187                 return cname.to_s
 188         end
 189
 190         # Get the value of this entry according to its Sqlite type
 191         #
 192         # require: `self.statement.is_open`
 193         fun value: nullable Sqlite3Data
 194         do
 195                 assert statement_closed: statement.is_open
 196
 197                 var data_type = statement.native_statement.column_type(index)
 198                 if data_type.is_integer then return to_i
 199                 if data_type.is_float then return to_f
 200                 if data_type.is_blob then return to_blob
 201                 if data_type.is_null then return null
 202                 if data_type.is_text then return to_s
 203                 abort
 204         end
 205
 206         # Get this entry as `Int`
 207         #
 208         # If the Sqlite type of this entry is not an integer, it will be `CAST` to
 209         # integer. If `null`, returns 0.
 210         #
 211         # require: `self.statement.is_open`
 212         fun to_i: Int
 213         do
 214                 assert statement_closed: statement.is_open
 215
 216                 return statement.native_statement.column_int(index)
 217         end
 218
 219         # Get this entry as `Float`
 220         #
 221         # If the Sqlite type of this entry is not a floating point, it will be `CAST`
 222         # to float. If `null`, returns 0.0.
 223         #
 224         # require: `self.statement.is_open`
 225         fun to_f: Float
 226         do
 227                 assert statement_closed: statement.is_open
 228
 229                 return statement.native_statement.column_double(index)
 230         end
 231
 232         # Get this entry as `String`
 233         #
 234         # If the Sqlite type of this entry is not text, it will be `CAST` to text.
 235         # If null, returns an empty string.
 236         #
 237         # require: `self.statement.is_open`
 238         redef fun to_s
 239         do
 240                 assert statement_closed: statement.is_open
 241
 242                 var native_string = statement.native_statement.column_text(index)
 243                 if native_string.address_is_null then return ""
 244                 return native_string.to_s_with_copy
 245         end
 246
 247         # Get this entry as `Blob`
 248         #
 249         # If the Sqlite type of this entry is not a blob, it will be `CAST` to text.
 250         # If null, returns a NULL pointer.
 251         #
 252         # require: `self.statement.is_open`
 253         fun to_blob: Blob
 254         do
 255                 assert statement_closed: statement.is_open
 256
 257                 # By spec, we must get the pointer before the byte count
 258                 var pointer = statement.native_statement.column_blob(index)
 259                 var length = statement.native_statement.column_bytes(index)
 260
 261                 return new Blob(pointer, length)
 262         end
 263 end
 264
 265 # Iterator over the rows of a statement result
 266 class StatementIterator
 267         super Iterator[StatementRow]
 268
 269         # Statement linked to `self`
 270         var statement: Statement
 271
 272         init
 273         do
 274                 self.item = new StatementRow(statement)
 275                 self.is_ok = statement.native_statement.step.is_row
 276         end
 277
 278         redef var item: StatementRow is noinit
 279
 280         redef var is_ok is noinit
 281
 282         # require: `self.statement.is_open`
 283         redef fun next
 284         do
 285                 assert statement_closed: statement.is_open
 286
 287                 var err = statement.native_statement.step
 288                 if err.is_row then
 289                         is_ok = true
 290                 else if err.is_done then
 291                         # Clean complete
 292                         is_ok = false
 293                 else
 294                         # error
 295                         # FIXME do something with the error?
 296                         is_ok = false
 297                 end
 298         end
 299
 300         redef fun finish do if statement.close_with_iterator then statement.close
 301 end
 302
 303 # A data type supported by Sqlite3
 304 interface Sqlite3Data end
 305
 306 redef universal Int super Sqlite3Data end
 307
 308 redef universal Float super Sqlite3Data end
 309
 310 redef class String super Sqlite3Data end
 311
 312 redef class Text
 313
 314         # Return `self` between `'`s, escaping `\` and `'`
 315         #
 316         #     assert "'; DROP TABLE students".to_sql_string == "'''; DROP TABLE students'"
 317         fun to_sql_string: String
 318         do
 319                 return "'{self.replace('\\', "\\\\").replace('\'', "''")}'"
 320         end
 321
 322         # Format the date represented by `self` into an escaped string for SQLite
 323         #
 324         # `self` must be composed of 1 to 3 integers separated by '-'.
 325         # An incompatible format will result in an invalid date string.
 326         #
 327         #     assert "2016-5-1".to_sql_date_string == "'2016-05-01'"
 328         #     assert "2016".to_sql_date_string == "'2016-01-01'"
 329         fun to_sql_date_string: String
 330         do
 331                 var parts = self.split("-")
 332                 for i in [parts.length .. 3[ do parts[i] = "1"
 333
 334                 var year = parts[0].justify(4, 1.0, '0')
 335                 var month = parts[1].justify(2, 1.0, '0')
 336                 var day = parts[2].justify(2, 1.0, '0')
 337                 return "{year}-{month}-{day}".to_sql_string
 338         end
 339 end
 340
 341 # A Sqlite3 blob
 342 class Blob
 343         super Sqlite3Data
 344
 345         # Pointer to the beginning of the blob
 346         var pointer: Pointer
 347
 348         # Size of the blob
 349         var length: Int
 350 end