-
Notifications
You must be signed in to change notification settings - Fork 5
Octave SQLITE Toolkit
The Octave SQLITE toolkit is a set of SQLITE routines for GNU Octave
The toolkit must be installed and then loaded to be used.
It can be installed in GNU Octave directly from the website, or can be installed in an off-line mode via a downloaded tarball.
The toolkit has a dependency on the SQLITE library (https://www.sqlite.org/), so it must be installed in order to successfully install the toolkit.
The toolkit must be then be loaded once per each GNU Octave session in order to use its functionality.
With an internet connection available, the octave sqlite package can be installed from the octave-sqlite website using the following command within GNU Octave:
pkg install https://github.com/gnu-octave/octave-sqlite/releases/download/v0.0.1/octave-sqlite-0.0.1.tar.gz
On Octave 7.2 and later, the package can be installed using the following command within GNU Octave:
pkg install -forge sqlite
The latest released version of the toolkit will be downloaded and installed.
With the toolkit package already downloaded, and in the current directory when running GNU Octave, the package can be installed using the following command within GNU Octave:
pkg install octave-sqlite-0.0.1.tar.gz
Regardless of the method of installing the toolkit, in order to use its functions, the toolkit must be loaded using the pkg load command:
pkg load sqlite
The toolkit must be loaded on each GNU Octave session.
Connection to the database is done using the sqlite function.
Example:
db = sqlite('testdb.db')
db =
sqlite with properties
Database: testdb.db
IsReadOnly: 0
AutoCommit: on
To quickly read data from a table, use the sqlread function
Example:
data = sqlread(db, "TestTable")
To close the database, use the close function
Example:
close(db)
The functions currently available in the toolkit are described below;
close (DB)
Close the sqlite connection represented by the object DB.
Inputs:
DB
Previously created octave_sqlite object
Outputs:
None
See also: sqlite.
YESNO = isopen (DB)
Return true if the sqlite connection is open.
Inputs:
DB
previously opened sqlite database.
Outputs:
YESNO
Boolean for if the database is still open.
See also: sqlite, close.
DB = sqlite (FILENAME)
DB = sqlite (FILENAME, MODE)
Create a sqlite database connection
Inputs:
'FILENAME'
Filename of the sqlite database
'MODE'
mode for the sqlite access 'readonly', 'connect' (default),
'create'.
Outputs:
'DB'
a opened SQLITE database as a octave_sqlite object
Object Properties:
The octave_sqlite object has the following properties:
Database
The name of the opened database file (readonly)
IsOpen
Boolean field for if the database is currently open (readonly)
IsReadOnly
Boolean field for if the database was opened in readonly mode.
(readonly)
AutoCommit
String flag field for whether database is in auto commit mode.
"on" (default) or "off".
Examples:
Open a sqlite database, failing if it doesnt exist.
db = sqlite("mytest.db");
Open a sqlite database, creating it if it does not exist.
db = sqlite("mytest.db", "create");
DATA = fetch (DB, SQLQUERY)
DATA = fetch (DB, SQLQUERY, PROPERTYNAME, PROPERTYVALUE ...)
Return rows of data after runnning a sql query on a sqlite database.
Inputs:
DB
currently open sqlite database.
SQLQUERY
String containing a valid select sqlquery.
PROPERTYNAME, PROPERTYVALUE
property name/value pairs where known properties are:
MaxRows
Integer value of max number of rows in the query
VariableNamingRule
String value 'preserve' (default) or 'modify' to flag
renaming of variable names (currently ignored)
Outputs:
DATA
a table containing the query result.
See also: sqlite, sqlread.
DATA = sqlread (DB, TABLENAME)
DATA = sqlread (DB, TABLENAME, PROPERTYNAME, PROPERTYVALUE ...)
Return rows of data from table TABLENAME in a sqlite database.
This function is the equivalent of running SELECT * FROM TABLE.
Inputs:
DB
currently open sqlite database.
TABLENAME
Name of a table with the database.
PROPERTYNAME, PROPERTYVALUE
property name/value pairs where known properties are:
MaxRows
Integer value of max number of rows in the query
VariableNamingRule
String value 'preserve' (default) or 'modify' to flag
renaming of variable names (currently ignored)
Outputs:
DATA
a table containing the query result.
See also: sqlite, fetch.
sqlwrite (DB, TABLENAME, DATA)
sqlwrite (DB, TABLENAME, DATA, PROPERTYNAME, PROPERTYVALUE ...)
Insert rows of data from TABLENAME.
If the table does not exist it will be created, using the ColumnType propery if available otherwise, the type of input data will be used to determine field types.
Inputs:
DB
Previously created octave_sqlite object
TABLENAME
Name of table to write data to
DATA
Table containing data to write to the database. Variables
names are expected to match the databse.
PROPERTYNAME, PROPERTYVALUE
property name/value pairs where known properties are:
ColumnType
Optional cell array of same size as the data that may be
used if the table is created as part of the rite
operation.
Outputs:
None
See also: sqlite, execute.
commit (DB)
Commit pending transactions of sqlite connection.
Inputs:
DB
Previously created octave_sqlite object
Outputs:
None
See also: sqlite, rollback.
execute (DB, SQLQUERY)
Execute non select sql query SQLQUERY on a sqlite database.
Inputs:
DB
Previously created octave_sqlite object
SQLQUERY
A valid non selecting SQL query string
Inputs:
None
See also: sqlite, fetch.
rollback (DB)
Rollback pending transactions of sqlite connection.
Inputs:
DB
Previously created octave_sqlite object
Outputs:
None
See also: sqlite, commit.
TABLE = dbtable()
TABLE = dbtable (VAR1, ... VARN)
TABLE = dbtable (... PROPERTYNAME, PROPERTYVALUE)
Basic implementation of a table type to avoid dependancies on other packages.
Inputs:
VAR1 ... VARN
Variables or data to be used as the column data of the table.
When the input is a variable, the variable name will be used
as the column name, otherwise the columns will be Var1 ...
VarN.
PROPERTYNAME, PROPERTYVALUE
Property name/value pairs, where known property names are:
VariableNames
a cell string matching the number of input columns with
the name to use for the
DimensionNames
a cell string matching of length 2 for using as dimesion
access. If not specified it will be "Rows" and
"Variables".
Outputs:
TABLE
a dbtable object
T = struct2dbtable (ASTRUCT)
Create a dbtable from a struct
Inputs::
ASTRUCT
A struct with same number of elements in each field
Outputs::
T
a dbtable of the ASTRUCT data