Services to manipulate a Sqlite3 database

For more information, refer to the documentation of http://www.sqlite.org/docs.html

Introduced classes

class Blob

sqlite3 :: Blob

A Sqlite3 blob
class Sqlite3DB

sqlite3 :: Sqlite3DB

A connection to a Sqlite3 database
interface Sqlite3Data

sqlite3 :: Sqlite3Data

A data type supported by Sqlite3
class Statement

sqlite3 :: Statement

Prepared Sqlite3 statement
class StatementEntry

sqlite3 :: StatementEntry

An entry on a StatementRow
class StatementIterator

sqlite3 :: StatementIterator

Iterator over the rows of a statement result
class StatementRow

sqlite3 :: StatementRow

A row from a Statement

Redefined classes

redef enum Float

sqlite3 :: sqlite3 $ Float

Native floating point numbers.
redef enum Int

sqlite3 :: sqlite3 $ Int

Native integer numbers.
redef abstract class String

sqlite3 :: sqlite3 $ String

Immutable sequence of characters.
redef abstract class Text

sqlite3 :: sqlite3 $ Text

High-level abstraction for all text representations

All class definitions

class Blob

sqlite3 $ Blob

A Sqlite3 blob
redef enum Float

sqlite3 :: sqlite3 $ Float

Native floating point numbers.
redef enum Int

sqlite3 :: sqlite3 $ Int

Native integer numbers.
class Sqlite3DB

sqlite3 $ Sqlite3DB

A connection to a Sqlite3 database
interface Sqlite3Data

sqlite3 $ Sqlite3Data

A data type supported by Sqlite3
class Statement

sqlite3 $ Statement

Prepared Sqlite3 statement
class StatementEntry

sqlite3 $ StatementEntry

An entry on a StatementRow
class StatementIterator

sqlite3 $ StatementIterator

Iterator over the rows of a statement result
class StatementRow

sqlite3 $ StatementRow

A row from a Statement
redef abstract class String

sqlite3 :: sqlite3 $ String

Immutable sequence of characters.
redef abstract class Text

sqlite3 :: sqlite3 $ Text

High-level abstraction for all text representations
package_diagram sqlite3::sqlite3 sqlite3 sqlite3::native_sqlite3 native_sqlite3 sqlite3::sqlite3->sqlite3::native_sqlite3 core core sqlite3::native_sqlite3->core ...core ... ...core->core linux::data_store data_store linux::data_store->sqlite3::sqlite3 linux::ui ui linux::ui->linux::data_store linux::ui... ... linux::ui...->linux::ui

Ancestors

module abstract_collection

core :: abstract_collection

Abstract collection classes and services.
module abstract_text

core :: abstract_text

Abstract class for manipulation of sequences of characters
module array

core :: array

This module introduces the standard array structure.
module bitset

core :: bitset

Services to handle BitSet
module bytes

core :: bytes

Services for byte streams and arrays
module circular_array

core :: circular_array

Efficient data structure to access both end of the sequence.
module codec_base

core :: codec_base

Base for codecs to use with streams
module codecs

core :: codecs

Group module for all codec-related manipulations
module collection

core :: collection

This module define several collection classes.
module core

core :: core

Standard classes and methods used by default by Nit programs and libraries.
module environ

core :: environ

Access to the environment variables of the process
module error

core :: error

Standard error-management infrastructure.
module exec

core :: exec

Invocation and management of operating system sub-processes.
module file

core :: file

File manipulations (create, read, write, etc.)
module fixed_ints

core :: fixed_ints

Basic integers of fixed-precision
module fixed_ints_text

core :: fixed_ints_text

Text services to complement fixed_ints
module flat

core :: flat

All the array-based text representations
module gc

core :: gc

Access to the Nit internal garbage collection mechanism
module hash_collection

core :: hash_collection

Introduce HashMap and HashSet.
module iso8859_1

core :: iso8859_1

Codec for ISO8859-1 I/O
module kernel

core :: kernel

Most basic classes and methods.
module list

core :: list

This module handle double linked lists
module math

core :: math

Mathematical operations
module native

core :: native

Native structures for text and bytes
module numeric

core :: numeric

Advanced services for Numeric types
module protocol

core :: protocol

module queue

core :: queue

Queuing data structures and wrappers
module range

core :: range

Module for range of discrete objects.
module re

core :: re

Regular expression support for all services based on Pattern
module ropes

core :: ropes

Tree-based representation of a String.
module sorter

core :: sorter

This module contains classes used to compare things and sorts arrays.
module stream

core :: stream

Input and output streams of characters
module text

core :: text

All the classes and methods related to the manipulation of text entities
module time

core :: time

Management of time and dates
module union_find

core :: union_find

union–find algorithm using an efficient disjoint-set data structure
module utf8

core :: utf8

Codec for UTF-8 I/O

Parents

module native_sqlite3

sqlite3 :: native_sqlite3

Low-level Sqlite3 features

Children

module data_store

linux :: data_store

app::data_store implementation on GNU/Linux

Descendants

module a_star-m

a_star-m

module ui

linux :: ui

Implementation of the app.nit UI module for GNU/Linux
# Services to manipulate a Sqlite3 database
#
# For more information, refer to the documentation of http://www.sqlite.org/docs.html
module sqlite3

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

# 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
	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

# Iterator over the rows of a statement result
class StatementIterator
	super Iterator[StatementRow]

	# Statement linked to `self`
	var statement: Statement

	init
	do
		self.item = new StatementRow(statement)
		self.is_ok = statement.native_statement.step.is_row
	end

	redef var item: StatementRow is noinit

	redef var is_ok is noinit

	# 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
			# 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

	# Pointer to the beginning of the blob
	var pointer: Pointer

	# Size of the blob
	var length: Int
end
lib/sqlite3/sqlite3.nit:17,1--361,3