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                 return new StatementIterator(self)
 131         end
 132 end
 133
 134 class StatementRow
 135         # Statement linked to `self`
 136         var statement: Statement
 137
 138         private init(s: Statement) do self.statement = s
 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         private init(s: Statement, i: Int)
 162         do
 163                 self.statement = s
 164                 self.index = i
 165         end
 166
 167         # Name of the column
 168         #
 169         # require: `self.statement.is_open`
 170         fun name: String is cached do
 171                 assert statement_closed: statement.is_open
 172
 173                 return statement.native_statement.column_name(index)
 174         end
 175
 176         # Get the value of this entry according to its Sqlite type
 177         #
 178         # require: `self.statement.is_open`
 179         fun value: nullable Sqlite3Data
 180         do
 181                 assert statement_closed: statement.is_open
 182
 183                 var data_type = statement.native_statement.column_type(index)
 184                 if data_type.is_integer then return to_i
 185                 if data_type.is_float then return to_f
 186                 if data_type.is_blob then return to_blob
 187                 if data_type.is_null then return null
 188                 if data_type.is_text then return to_s
 189                 abort
 190         end
 191
 192         # Get this entry as `Int`
 193         #
 194         # If the Sqlite type of this entry is not an integer, it will be `CAST` to
 195         # integer. If `null`, returns 0.
 196         #
 197         # require: `self.statement.is_open`
 198         fun to_i: Int
 199         do
 200                 assert statement_closed: statement.is_open
 201
 202                 return statement.native_statement.column_int(index)
 203         end
 204
 205         # Get this entry as `Float`
 206         #
 207         # If the Sqlite type of this entry is not a floating point, it will be `CAST`
 208         # to float. If `null`, returns 0.0.
 209         #
 210         # require: `self.statement.is_open`
 211         fun to_f: Float
 212         do
 213                 assert statement_closed: statement.is_open
 214
 215                 return statement.native_statement.column_double(index)
 216         end
 217
 218         # Get this entry as `String`
 219         #
 220         # If the Sqlite type of this entry is not text, it will be `CAST` to text.
 221         # If null, returns an empty string.
 222         #
 223         # require: `self.statement.is_open`
 224         redef fun to_s
 225         do
 226                 assert statement_closed: statement.is_open
 227
 228                 var native_string = statement.native_statement.column_text(index)
 229                 if native_string.address_is_null then return ""
 230                 return native_string.to_s_with_copy
 231         end
 232
 233         # Get this entry as `Blob`
 234         #
 235         # If the Sqlite type of this entry is not a blob, it will be `CAST` to text.
 236         # If null, returns a NULL pointer.
 237         #
 238         # require: `self.statement.is_open`
 239         fun to_blob: Blob
 240         do
 241                 assert statement_closed: statement.is_open
 242
 243                 # By spec, we must get the pointer before the byte count
 244                 var pointer = statement.native_statement.column_blob(index)
 245                 var length = statement.native_statement.column_bytes(index)
 246
 247                 return new Blob(pointer, length)
 248         end
 249 end
 250
 251 # Iterator over the rows of a statement result
 252 class StatementIterator
 253         super Iterator[StatementRow]
 254
 255         # Statement linked to `self`
 256         var statement: Statement
 257
 258         private init(s: Statement)
 259         do
 260                 self.statement = s
 261                 self.item = new StatementRow(s)
 262
 263                 self.is_ok = statement.native_statement.step.is_row
 264         end
 265
 266         redef var item: StatementRow
 267
 268         redef var is_ok: Bool
 269
 270         # require: `self.statement.is_open`
 271         redef fun next
 272         do
 273                 assert statement_closed: statement.is_open
 274
 275                 var err = statement.native_statement.step
 276                 if err.is_row then
 277                         is_ok = true
 278                 else if err.is_done then
 279                         # Clean complete
 280                         is_ok = false
 281                 else
 282                         # error
 283                         # FIXME do something with the error?
 284                         is_ok = false
 285                 end
 286         end
 287 end
 288
 289 # A data type supported by Sqlite3
 290 interface Sqlite3Data end
 291
 292 redef universal Int super Sqlite3Data end
 293 redef universal Float super Sqlite3Data end
 294 redef class String
 295         super Sqlite3Data
 296
 297         # Return `self` between `'`s and escaping any extra `'`
 298         #
 299         #     assert "'; DROP TABLE students".to_sql_string == "'''; DROP TABLE students'"
 300         fun to_sql_string: String
 301         do
 302                 return "'{self.replace('\'', "''")}'"
 303         end
 304 end
 305
 306 # A Sqlite3 blob
 307 class Blob
 308         super Sqlite3Data
 309
 310         private init(pointer: Pointer, length: Int)
 311         do
 312                 self.pointer = pointer
 313                 self.length = length
 314         end
 315
 316         var pointer: Pointer
 317         var length: Int
 318 end