# This file is part of NIT ( http://www.nitlanguage.org ).
#
-# Copyright 2013 Guillaume Auger <jeho@resist.ca>
-# Copyright 2013 Alexis Laferrière <alexis.laf@xymus.net>
+# Copyright 201 Alexis Laferrière <alexis.laf@xymus.net>
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# See the License for the specific language governing permissions and
# limitations under the License.
+# Services to manipulate a Sqlite3 database
+#
+# For more information, refer to the documentation of http://www.sqlite.org/docs.html
module sqlite3
-in "C header" `{
- #include "sqlite3.h"
-`}
-
-extern class Sqlite3Code `{int`}
- new ok `{ return SQLITE_OK; `} # 0 /* Successful result */
- fun is_ok: Bool `{ return recv == SQLITE_OK; `}
-
- # new `{ return SQLITE_ERROR; `} # 1 /* SQL error or missing database */
- # new `{ return SQLITE_INTERNAL; `} # 2 /* Internal logic error in SQLite */
- # new `{ return SQLITE_PERM; `} # 3 /* Access permission denied */
- # new `{ return SQLITE_ABORT; `} # 4 /* Callback routine requested an abort */
- # new `{ return SQLITE_BUSY; `} # 5 /* The database file is locked */
- # new `{ return SQLITE_LOCKED; `} # 6 /* A table in the database is locked */
- # new `{ return SQLITE_NOMEM; `} # 7 /* A malloc() failed */
- # new `{ return SQLITE_READONLY; `} # 8 /* Attempt to write a readonly database */
- # new `{ return SQLITE_INTERRUPT; `} # 9 /* Operation terminated by sqlite3_interrupt()*/
- # new `{ return SQLITE_IOERR; `} # 10 /* Some kind of disk I/O error occurred */
- # new `{ return SQLITE_CORRUPT; `} # 11 /* The database disk image is malformed */
- # new `{ return SQLITE_NOTFOUND; `} # 12 /* Unknown opcode in sqlite3_file_control() */
- # new `{ return SQLITE_FULL; `} # 13 /* Insertion failed because database is full */
- # new `{ return SQLITE_CANTOPEN; `} # 14 /* Unable to open the database file */
- # new `{ return SQLITE_PROTOCOL; `} # 15 /* Database lock protocol error */
- # new `{ return SQLITE_EMPTY; `} # 16 /* Database is empty */
- # new `{ return SQLITE_SCHEMA; `} # 17 /* The database schema changed */
- # new `{ return SQLITE_TOOBIG; `} # 18 /* String or BLOB exceeds size limit */
- # new `{ return SQLITE_CONSTRAINT; `} # 19 /* Abort due to constraint violation */
- # new `{ return SQLITE_MISMATCH; `} # 20 /* Data type mismatch */
- # new `{ return SQLITE_MISUSE; `} # 21 /* Library used incorrectly */
- # new `{ return SQLITE_NOLFS; `} # 22 /* Uses OS features not supported on host */
- # new `{ return SQLITE_AUTH; `} # 23 /* Authorization denied */
- # new `{ return SQLITE_FORMAT; `} # 24 /* Auxiliary database format error */
- # new `{ return SQLITE_RANGE; `} # 25 /* 2nd parameter to sqlite3_bind out of range */
- # new `{ return SQLITE_NOTADB; `} # 26 /* File opened that is not a database file */
- # new `{ return SQLITE_NOTICE; `} # 27 /* Notifications from sqlite3_log() */
- # new `{ return SQLITE_WARNING; `} # 28 /* Warnings from sqlite3_log() */
-
- new row `{ return SQLITE_ROW; `} # 100 /* sqlite3_step() has another row ready */
- fun is_row: Bool `{ return recv == SQLITE_ROW; `}
-
- new done `{ return SQLITE_DONE; `} # 101 /* sqlite3_step() has finished executing */
- fun is_done: Bool `{ return recv == SQLITE_DONE; `}
-
- redef fun to_s: String import NativeString::to_s `{
-#if SQLITE_VERSION_NUMBER >= 3007015
- char *err = (char *)sqlite3_errstr(recv);
-#else
- char *err = "sqlite3_errstr supported only by version >= 3.7.15";
-#endif
- if (err == NULL) err = "";
- return NativeString_to_s(err);
- `}
+private import native_sqlite3
+import core
+
+# A connection to a Sqlite3 database
+class Sqlite3DB
+ private var native_connection: NativeSqlite3
+
+ # Is this connection to the DB open?
+ var is_open = false
+
+ # All `Statement` opened from this connection that must be closed with this connection
+ private var open_statements = new Array[Statement]
+
+ # Open a connection to the database file at `path`
+ init open(path: Text)
+ do
+ init(new NativeSqlite3.open(path.to_cstring))
+ if native_connection.is_valid then is_open = true
+ end
+
+ # Close this connection to the DB and all open statements
+ fun close
+ do
+ if not is_open then return
+
+ is_open = false
+
+ # close open statements
+ for stmt in open_statements do if stmt.is_open then
+ stmt.close
+ end
+
+ native_connection.close
+ end
+
+ # Prepare and return a `Statement`, return `null` on error
+ fun prepare(sql: Text): nullable Statement
+ do
+ var native_stmt = native_connection.prepare(sql.to_cstring)
+ if native_stmt.address_is_null then return null
+
+ var stmt = new Statement(native_stmt)
+ open_statements.add stmt
+ return stmt
+ end
+
+ # Execute the `sql` statement and return `true` on success
+ fun execute(sql: Text): Bool
+ do
+ var err = native_connection.exec(sql.to_cstring)
+ return err.is_ok
+ end
+
+ # Create a table on the DB with a statement beginning with "CREATE TABLE ", followed by `rest`
+ #
+ # This method does not escape special characters.
+ fun create_table(rest: Text): Bool do return execute("CREATE TABLE " + rest)
+
+ # Insert in the DB with a statement beginning with "INSERT ", followed by `rest`
+ #
+ # This method does not escape special characters.
+ fun insert(rest: Text): Bool do return execute("INSERT " + rest)
+
+ # Replace in the DB with a statement beginning with "REPLACE", followed by `rest`
+ #
+ # This method does not escape special characters.
+ fun replace(rest: Text): Bool do return execute("REPLACE " + rest)
+
+ # Select from the DB with a statement beginning with "SELECT ", followed by `rest`
+ #
+ # This method does not escape special characters.
+ fun select(rest: Text): nullable Statement do return prepare("SELECT " + rest)
+
+ # TODO add more prefix here as needed
+
+ # The latest error message, or `null` if there is none
+ fun error: nullable String
+ do
+ if not native_connection.is_valid then
+ var err = sys.sqlite_open_error
+ if err.is_ok then return null
+ return err.to_s
+ end
+
+ var err = native_connection.error
+ if err.is_ok then return null
+ return err.to_s
+ end
+
+ # Returns the id for the last successful insert on the current connection.
+ fun last_insert_rowid: Int do return native_connection.last_insert_rowid
+end
+
+# Prepared Sqlite3 statement
+#
+# Instances of this class are created from `Sqlite3DB::prepare` and
+# its shortcuts: `create_table`, `insert`, `replace` and `select`.
+# The results should be explored with an `iterator`,
+# and each call to `iterator` resets the request.
+# If `close_with_iterator` the iterator calls `close`
+# on this request upon finishing.
+class Statement
+ private var native_statement: NativeStatement
+
+ # Is this statement usable?
+ var is_open = true
+
+ # Should any `iterator` close this statement on `Iterator::finish`?
+ #
+ # If `true`, the default, any `StatementIterator` created by calls to
+ # `iterator` invokes `close` on this request when finished iterating.
+ # Otherwise, `close` must be called manually.
+ var close_with_iterator = true is writable
+
+ # Close and finalize this statement
+ fun close
+ do
+ if not is_open then return
+
+ is_open = false
+ native_statement.finalize
+ end
+
+ # Reset this statement and return a `StatementIterator` to iterate over the result
+ fun iterator: StatementIterator
+ do
+ native_statement.reset
+ return new StatementIterator(self)
+ end
+end
+
+# A row from a `Statement`
+class StatementRow
+ # Statement linked to `self`
+ var statement: Statement
+
+ # Maps the column name to its value
+ fun map: Map[String, nullable Sqlite3Data]
+ do
+ var ret = new ArrayMap[String, nullable Sqlite3Data]
+ for i in [0 .. length[ do
+ var st = self[i]
+ ret[st.name] = st.value
+ end
+ return ret
+ end
+
+ # Number of entries in this row
+ #
+ # require: `self.statement.is_open`
+ fun length: Int
+ do
+ assert statement_closed: statement.is_open
+
+ return statement.native_statement.column_count
+ end
+
+ # Returns the `i`th entry on this row
+ fun [](i: Int): StatementEntry do return new StatementEntry(statement, i)
end
-extern class Statement `{sqlite3_stmt*`}
-
- fun step: Sqlite3Code `{
- return sqlite3_step(recv);
- `}
-
- fun column_name(i: Int) : String import NativeString::to_s `{
- const char * name = (sqlite3_column_name(recv, i));
- if(name == NULL){
- name = "";
- }
- char * ret = (char *) name;
- return NativeString_to_s(ret);
- `}
-
- fun column_bytes(i: Int) : Int `{
- return sqlite3_column_bytes(recv, i);
- `}
-
- fun column_double(i: Int) : Float `{
- return sqlite3_column_double(recv, i);
- `}
-
- fun column_int(i: Int) : Int `{
- return sqlite3_column_int(recv, i);
- `}
-
- fun column_text(i: Int) : String import NativeString::to_s `{
- char * ret = (char *) sqlite3_column_text(recv, i);
- if( ret == NULL ){
- ret = "";
- }
- return NativeString_to_s(ret);
- `}
-
- fun column_type(i: Int) : Int `{
- return sqlite3_column_type(recv, i);
- `}
-
- # fun column_blob(i : Int) : String `{
- # TODO
- # `}
-
- fun column_count: Int `{
- return sqlite3_column_count(recv);
- `}
+# An entry on a `StatementRow`
+class StatementEntry
+ # Statement linked to `self`
+ var statement: Statement
+
+ private var index: Int
+
+ # Name of the column
+ #
+ # require: `self.statement.is_open`
+ var name: String is lazy do
+ assert statement_closed: statement.is_open
+
+ var cname = statement.native_statement.column_name(index)
+ assert not cname.address_is_null
+ return cname.to_s
+ end
+
+ # Get the value of this entry according to its Sqlite type
+ #
+ # require: `self.statement.is_open`
+ fun value: nullable Sqlite3Data
+ do
+ assert statement_closed: statement.is_open
+
+ var data_type = statement.native_statement.column_type(index)
+ if data_type.is_integer then return to_i
+ if data_type.is_float then return to_f
+ if data_type.is_blob then return to_blob
+ if data_type.is_null then return null
+ if data_type.is_text then return to_s
+ abort
+ end
+
+ # Get this entry as `Int`
+ #
+ # If the Sqlite type of this entry is not an integer, it will be `CAST` to
+ # integer. If `null`, returns 0.
+ #
+ # require: `self.statement.is_open`
+ fun to_i: Int
+ do
+ assert statement_closed: statement.is_open
+
+ return statement.native_statement.column_int(index)
+ end
+
+ # Get this entry as `Float`
+ #
+ # If the Sqlite type of this entry is not a floating point, it will be `CAST`
+ # to float. If `null`, returns 0.0.
+ #
+ # require: `self.statement.is_open`
+ fun to_f: Float
+ do
+ assert statement_closed: statement.is_open
+
+ return statement.native_statement.column_double(index)
+ end
+
+ # Get this entry as `String`
+ #
+ # If the Sqlite type of this entry is not text, it will be `CAST` to text.
+ # If null, returns an empty string.
+ #
+ # require: `self.statement.is_open`
+ redef fun to_s
+ do
+ assert statement_closed: statement.is_open
+
+ var c_string = statement.native_statement.column_text(index)
+ if c_string.address_is_null then return ""
+ return c_string.to_s_with_copy
+ end
+
+ # Get this entry as `Blob`
+ #
+ # If the Sqlite type of this entry is not a blob, it will be `CAST` to text.
+ # If null, returns a NULL pointer.
+ #
+ # require: `self.statement.is_open`
+ fun to_blob: Blob
+ do
+ assert statement_closed: statement.is_open
+
+ # By spec, we must get the pointer before the byte count
+ var pointer = statement.native_statement.column_blob(index)
+ var length = statement.native_statement.column_bytes(index)
+
+ return new Blob(pointer, length)
+ end
end
-extern class Sqlite3 `{sqlite3 *`}
- new open(filename: String) import String::to_cstring `{
- sqlite3 *self;
- sqlite3_open(String_to_cstring(filename), &self);
- return self;
- `}
+# Iterator over the rows of a statement result
+class StatementIterator
+ super Iterator[StatementRow]
+
+ # Statement linked to `self`
+ var statement: Statement
- fun destroy do close
+ init
+ do
+ self.item = new StatementRow(statement)
+ self.is_ok = statement.native_statement.step.is_row
+ end
- fun close `{ sqlite3_close(recv); `}
+ redef var item: StatementRow is noinit
- fun exec(sql : String): Sqlite3Code import String::to_cstring `{
- return sqlite3_exec(recv, String_to_cstring(sql), 0, 0, 0);
- `}
+ redef var is_ok is noinit
- fun prepare(sql: String): nullable Statement import String::to_cstring, Statement as nullable `{
- sqlite3_stmt *stmt;
- int res = sqlite3_prepare_v2(recv, String_to_cstring(sql), -1, &stmt, 0);
- if (res == SQLITE_OK)
- return Statement_as_nullable(stmt);
+ # require: `self.statement.is_open`
+ redef fun next
+ do
+ assert statement_closed: statement.is_open
+
+ var err = statement.native_statement.step
+ if err.is_row then
+ is_ok = true
+ else if err.is_done then
+ # Clean complete
+ is_ok = false
else
- return null_Statement();
- `}
+ # error
+ # FIXME do something with the error?
+ is_ok = false
+ end
+ end
+
+ redef fun finish do if statement.close_with_iterator then statement.close
+end
+
+# A data type supported by Sqlite3
+interface Sqlite3Data end
+
+redef universal Int super Sqlite3Data end
+
+redef universal Float super Sqlite3Data end
+
+redef class String super Sqlite3Data end
+
+redef class Text
+
+ # Return `self` between `'`s, escaping `\` and `'`
+ #
+ # assert "'; DROP TABLE students".to_sql_string == "'''; DROP TABLE students'"
+ fun to_sql_string: String
+ do
+ return "'{self.replace('\\', "\\\\").replace('\'', "''")}'"
+ end
+
+ # Format the date represented by `self` into an escaped string for SQLite
+ #
+ # `self` must be composed of 1 to 3 integers separated by '-'.
+ # An incompatible format will result in an invalid date string.
+ #
+ # assert "2016-5-1".to_sql_date_string == "'2016-05-01'"
+ # assert "2016".to_sql_date_string == "'2016-01-01'"
+ fun to_sql_date_string: String
+ do
+ var parts = self.split("-")
+ for i in [parts.length .. 3[ do parts[i] = "1"
+
+ var year = parts[0].justify(4, 1.0, '0')
+ var month = parts[1].justify(2, 1.0, '0')
+ var day = parts[2].justify(2, 1.0, '0')
+ return "{year}-{month}-{day}".to_sql_string
+ end
+end
+
+# A Sqlite3 blob
+class Blob
+ super Sqlite3Data
- fun last_insert_rowid: Int `{
- return sqlite3_last_insert_rowid(recv);
- `}
+ # Pointer to the beginning of the blob
+ var pointer: Pointer
- fun error: Sqlite3Code `{
- return sqlite3_errcode(recv);
- `}
+ # Size of the blob
+ var length: Int
end
nitlanguage / nit.git / blobdiff