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_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
 109         # Returns the id for the last successful insert on the current connection.
 110         fun last_insert_rowid: Int do return native_connection.last_insert_rowid
 111 end
 112
 113 # A prepared Sqlite3 statement, created from `Sqlite3DB::prepare` or `Sqlite3DB::select`
 114 class Statement
 115         private var native_statement: NativeStatement
 116
 117         # Is this statement usable?
 118         var is_open = true
 119
 120         # Close and finalize this statement
 121         fun close
 122         do
 123                 is_open = false
 124                 native_statement.finalize
 125         end
 126
 127         # Reset this statement and return a `StatementIterator` to iterate over the result
 128         fun iterator: StatementIterator
 129         do
 130                 native_statement.reset
 131                 return new StatementIterator(self)
 132         end
 133 end
 134
 135 # A row from a `Statement`
 136 class StatementRow
 137         # Statement linked to `self`
 138         var statement: Statement
 139
 140         # Number of entries in this row
 141         #
 142         # require: `self.statement.is_open`
 143         fun length: Int
 144         do
 145                 assert statement_closed: statement.is_open
 146
 147                 return statement.native_statement.column_count
 148         end
 149
 150         # Returns the `i`th entry on this row
 151         fun [](i: Int): StatementEntry do return new StatementEntry(statement, i)
 152 end
 153
 154 # An entry on a `StatementRow`
 155 class StatementEntry
 156         # Statement linked to `self`
 157         var statement: Statement
 158
 159         private var index: Int
 160
 161         # Name of the column
 162         #
 163         # require: `self.statement.is_open`
 164         var name: String is lazy do
 165                 assert statement_closed: statement.is_open
 166
 167                 return statement.native_statement.column_name(index)
 168         end
 169
 170         # Get the value of this entry according to its Sqlite type
 171         #
 172         # require: `self.statement.is_open`
 173         fun value: nullable Sqlite3Data
 174         do
 175                 assert statement_closed: statement.is_open
 176
 177                 var data_type = statement.native_statement.column_type(index)
 178                 if data_type.is_integer then return to_i
 179                 if data_type.is_float then return to_f
 180                 if data_type.is_blob then return to_blob
 181                 if data_type.is_null then return null
 182                 if data_type.is_text then return to_s
 183                 abort
 184         end
 185
 186         # Get this entry as `Int`
 187         #
 188         # If the Sqlite type of this entry is not an integer, it will be `CAST` to
 189         # integer. If `null`, returns 0.
 190         #
 191         # require: `self.statement.is_open`
 192         fun to_i: Int
 193         do
 194                 assert statement_closed: statement.is_open
 195
 196                 return statement.native_statement.column_int(index)
 197         end
 198
 199         # Get this entry as `Float`
 200         #
 201         # If the Sqlite type of this entry is not a floating point, it will be `CAST`
 202         # to float. If `null`, returns 0.0.
 203         #
 204         # require: `self.statement.is_open`
 205         fun to_f: Float
 206         do
 207                 assert statement_closed: statement.is_open
 208
 209                 return statement.native_statement.column_double(index)
 210         end
 211
 212         # Get this entry as `String`
 213         #
 214         # If the Sqlite type of this entry is not text, it will be `CAST` to text.
 215         # If null, returns an empty string.
 216         #
 217         # require: `self.statement.is_open`
 218         redef fun to_s
 219         do
 220                 assert statement_closed: statement.is_open
 221
 222                 var native_string = statement.native_statement.column_text(index)
 223                 if native_string.address_is_null then return ""
 224                 return native_string.to_s_with_copy
 225         end
 226
 227         # Get this entry as `Blob`
 228         #
 229         # If the Sqlite type of this entry is not a blob, it will be `CAST` to text.
 230         # If null, returns a NULL pointer.
 231         #
 232         # require: `self.statement.is_open`
 233         fun to_blob: Blob
 234         do
 235                 assert statement_closed: statement.is_open
 236
 237                 # By spec, we must get the pointer before the byte count
 238                 var pointer = statement.native_statement.column_blob(index)
 239                 var length = statement.native_statement.column_bytes(index)
 240
 241                 return new Blob(pointer, length)
 242         end
 243 end
 244
 245 # Iterator over the rows of a statement result
 246 class StatementIterator
 247         super Iterator[StatementRow]
 248
 249         # Statement linked to `self`
 250         var statement: Statement
 251
 252         init
 253         do
 254                 self.item = new StatementRow(statement)
 255                 self.is_ok = statement.native_statement.step.is_row
 256         end
 257
 258         redef var item: StatementRow is noinit
 259
 260         redef var is_ok is noinit
 261
 262         # require: `self.statement.is_open`
 263         redef fun next
 264         do
 265                 assert statement_closed: statement.is_open
 266
 267                 var err = statement.native_statement.step
 268                 if err.is_row then
 269                         is_ok = true
 270                 else if err.is_done then
 271                         # Clean complete
 272                         is_ok = false
 273                 else
 274                         # error
 275                         # FIXME do something with the error?
 276                         is_ok = false
 277                 end
 278         end
 279 end
 280
 281 # A data type supported by Sqlite3
 282 interface Sqlite3Data end
 283
 284 redef universal Int super Sqlite3Data end
 285 redef universal Float super Sqlite3Data end
 286 redef class String
 287         super Sqlite3Data
 288
 289         # Return `self` between `'`s and escaping any extra `'`
 290         #
 291         #     assert "'; DROP TABLE students".to_sql_string == "'''; DROP TABLE students'"
 292         fun to_sql_string: String
 293         do
 294                 return "'{self.replace('\'', "''")}'"
 295         end
 296 end
 297
 298 # A Sqlite3 blob
 299 class Blob
 300         super Sqlite3Data
 301
 302         # Pointer to the beginning of the blob
 303         var pointer: Pointer
 304
 305         # Size of the blob
 306         var length: Int
 307 end