ShowTable of Contents
A class to ease simple operations with the SQLite database.
For the ease of use it's assumed that the first column of a table is the primary key.
Note that local databases created by NotesDatabase or NotesView have a name prefixed with "yn".
So if you used a NotesDatabase("mydb"), then you have to prefix the name with "yn" when using the database with a YNDatabase object: Database("ynmydb").
Using YNDatabase to work with NotesView data
The NotesView class caches data of Domino views in a local SQLite database. You can use the YNDatabase class to have direct access to the view's column data.
You can access the local cache for a Domino view using the name of the NotesDatabase prefixed with "yn".
For example, a NotesDatabase that is used by this code:
var notesdb = new NotesDatabase("contacts");
will create a local SQLite database with the name "yncontacts". So you can work with it by using:
var db = new YNDatabase("yncontacts");
Using a NotesView class creates a table in the SQLite database. The table gets the name of the view prefixed with "view_" and is all lower case.
So if you used a NotesView like this:
var notesdb = new NotesDatabase("contacts");
var notesview = notesdb.getView("People");
notesview.update();
then you can access the table of the local cache of the view's data with:
var db = new YNDatabase("yncontacts");
var entries = db.getAllEntries("view_people");
Open database
var db = new YNDatabase("mydatabase");
Close database
You need to close the database if you don't work with it anymore.
db.close();
Check if a table exists
YNDatabase.tableExists(tablename)
With:
tablename: the name of the table to check.
Returns:
true if the table exists.
Example:
var db = new YNDatabase("mydatabase");
if (db.tableExists("mytable")) {
...
}
db.close();
Create a new table
YNDatabase.createTable(tablename, columns)
With:
tablename: the name of the table to create.
columns: an array of property objects with
- name: string, the name of the column
- type: string, a type like TEXT or VARCHAR(16). See SQLite documentation about more types.
- notNull: optional, boolean, set to true if the column must not be null.
Returns:
true if the operation was successful.
false if there was an exception.
Notes:
This method creates the table if it does not exist already.
The first column is automatically used as primary key.
Example:
var db = new YNDatabase("mydatabase");
var columns = [];
columns.push({name:'unid',type:'TEXT'});
columns.push({name:'lastname',type:'TEXT'});
columns.push({name:'firstname',type:'TEXT'});
db.createTable("persons", columns);
db.close();
Write data
YNDatabase.writeEntry(tablename, data)
With:
tablename: the name of the table to write to.
data: an array of column values for the new dataset.
Returns:
true if the operation was successful.
false if there was an exception.
Notes:
If there is already a dataset with the same primary key, it is being updated.
Otherwise a new dataset is being created.
Remember that it's assumed that the first column is the primary key.
Example:
var db = new YNDatabase("mydatabase");
db.writeEntry("persons", [1, "Garak", "Elim"]);
db.writeEntry("persons", [2, "Dax", "Jadzia"]);
db.close();
Get data
YNDatabase.getEntry(tablename, keys)
With:
tablename: the table to get data from
keys: an array of values matched against column values of the table's data. "*" matches all. If this parameter is a scalar, it's being matched against the primary key column.
Returns:
One entry matching the keys. If the keys match multiple entries, the first one is returned.
Null if no entry machted.
Since:
1.0 Beta 2.
YNDatabase.getAllEntries(tablename, sortBy, sortType)
YNDatabase.getAllEntriesByKey(tablename, keys, sortBy, sortType)
With:
tablename: the table to get data from.
keys: an array of values matched against column values of the table's data. "*" matches all. If this parameter is a scalar, it's being matched against the primary key column.
sortBy: (optional) an array of column names after which the entries should be sorted
sortType (optional) give 'desc' to sort the result descending (instead of ascending, which is the default)
Returns:
An array of entries, each entry is an array of column values.
Null if no entry matched.
Notes:
To simply get one entry by it's primary key, use YNDatabase.getAllEntriesByKey(tablename, primaryKey).
To skip the primary key in the search, use YNDatabase.getAllEntriesByKey(tablename, ['*', key1, key2...]).
Example:
var db = new YNDatabase("mydatabase");
var entries = db.getAllEntries("persons");
for (e in entries) {
YN.log("found entry: "+entries[e].join(", "));
}
var entries = db.getAllEntriesByKey('persons', ['*', 'dax', 'jadzia']);
var entries = db.getAllEntriesByKey('persons', 1);
db.close();
Delete entries
YNDatabase.deleteEntry(tablename, key)
With:
tablename: the table to delete data from.
key: one key that is matched against the first column of the table.
Notes:
This method deletes the database entry where the first column (the primary key) matches the key.
YNDatabase.deleteEntries(tablename, keys)
With:
tablename: the table to delete data from.
keys: an array of values matched against column values of the table's data. "*" matches all. If this parameter is a scalar, it's being matched against the primary key column.
Notes:
This method will delete all entries of the given table matching the given key(s).
Execute SQL
You can execute SQL just like with the standard Titanium database class:
var rows = db.execute(sql, parameters)
Use transactions to increase performance
If you need to write a lot of entries in a loop, performance can be improved by starting a transaction at the beginning of the loop and end the transaction after the loop.
Please note that if something breaks the loop before you end the transaction, all write operations are lost.
YNDatabase.beginTransaction()
Starts a transaction.
YNDatabase.endTransaction()
Ends a transaction and commits all write operations to disk. This commit is done automatically when you started a transaction and then close the database with the close() method.
Example:
var db = new YNDatabase("mydb");
db.beginTransaction();
for (var i = 0; i < 10000; i++) {
db.writeEntry(mytable, myvalue);
}
db.endTransaction();
db.close();
|