The code below defines a simple database:
<?xml version="1.0"?>
<!DOCTYPE database SYSTEM "litesql.dtd">
<database name="PersonDatabase">
<object name="Person">
<field name="name" type="string"/>
</object>
</database>
The database file is created when PersonDatabase is constructed. First argument of constructor is requested backend. Other possibilities are "postgresql" and "mysql". Refer to class documentation for details.
PersonDatabase db("sqlite3", "database=person.db");
The database has to be created before any Persistent - classes can be used.
db.create();
The database can be dropped using drop() - method.
db.drop();
Suppose the database schema has changed along with new features of the program. Few data fields has been added to Person-class and completely new Persistent-class House has been defined. To upgrade database on disk, use needsUpgrade() and upgrade() - methods.
if (db.needsUpgrade())
db.upgrade();
If new fields has been added to classes, the upgrade operation may be quite costly. All backends do not support table modifications so records has to be copied forth and back.
Upgrade routine will try to preserve data. It will drop fields that are not in new schema and insert NULL-values to new fields.
A simple Person database:
<?xml version="1.0"?>
<!DOCTYPE database SYSTEM "litesql.dtd">
<database name="PersonDatabase">
<object name="Person">
<field name="name" type="string"/>
<field name="age" type="integer"/>
</object>
</database>
Following code demonstrates how to insert Persons to database:
PersonDatabase db("sqlite3", "database=person.db"); // assumes the database has been created
Person person(db); // construct Person, does not write anything to database
person.name = "Bob"; // assign values to fields
person.age = 20;
person.update(); // writes a new record to database
person.age = 21; // Bob got just older
person.update(); // updates old record
person.id = 100; // force internal identifier (id) to 100
person.update(); // updates old record
Note: if internal identifier (id-field) is changed, relations will not "follow" the object and will not be deleted either. If the object is not replaced with another object, relations should be manually dropped using delRelations-method.
Following code demonstrates how to delete Persons from database:
Person person = select<Person>(db).one(); // any Person will do
person.del(); // person.onDelete() gets called before data is deleted
template <class T> select(const Database& db, const Expr& filter=Expr());
Template function select returns DataSource which can be used to retrieve Persistent-objects or to create more complex queries.
select-function has two parameters: Database and expression (Expr) which can be used to limit the result set. DataSource DataSource is basically a wrapper for selection query and is used to access objects of type T.
DataSource has following methods:
* idQuery(): returns SelectQuery which returns id-number of Persistent objects
* objectQuery(): returns SelectQuery which returns data rows of Persistent objects
* cursor(): returns Cursor<T> which will return Persistent objects one by one
* one(): returns first Persistent object in the result set. Note: throws NotFound - if the result set is empty
* all(): returns the whole result set as Persistent objects in vector
* count(): returns number of object in the result set
* orderBy(FieldType field, bool ascending=true): result set can be ordered with this method.
* orderByRelation(FieldType id, FieldType field, bool ascending=true): orders the result set by external relation
An example of select and selectOne with database:
PersonDatabase db("sqlite3", "database=person.db");
vector<Person> = select<Person>(db).all();
Person bob = select<Person>(db, Person::Name == "Bob").one();
SelectQuery-class can be used when creating SQL SELECT-statements. Refer to documentation of SelectQuery for a list of methods. Methods can be combined:
Records recs = db.query(SelectQuery().result("id_")
.source("Person_")
.where(Person::Name == "Bob"));
Filters for select are created using Expr-API. The simplest form of a filter expression is . Example:
Person::Name == "Bob"
These expression can be combined using parenthesis and connectives:
() && ||
Operators that can be used in expressions are
== > < >= <= ! != .in .like
Persistent's (static) field information can be accessed using static FieldType-objects The name of the FieldType-object is almost the same as the field. Capitalize name of the field to get name of the FieldType-object.
// Person's name
Person::Name
template <class T> intersect(const DataSource<T>& ds1,
const DataSource<T>& ds2);
template <class T> union_(const DataSource<T>& ds1,
const DataSource<T>& ds2);
template <class T> except(const DataSource<T>& ds1,
const DataSource<T>& ds2);
Two DataSources of same type can be used to create a new DataSource which can be used to access intersection, union or difference of the sources.
Notes: -'union' is a reserved word of C so union_ is the name of set operation. -see MySQL specific notes about using these set operations with MySQL
// intersection of Bob's and Bill's friends
DataSource<Person> commonFriends =
intersect(bob.friends().get(), bill.friends().get());
// persons that are Bob's friends and/or Bill's friends
DataSource<Person> allFriends =
union_(bob.friends().get(), bill.friends().get());
// persons that are Bob's friends but not Bill's friends
DataSource<Person> allFriends =
except(bob.friends().get(), bill.friends().get());
A simple database with Person-class and friends-relation.
<?xml version="1.0"?>
<!DOCTYPE database SYSTEM "http://litesql.sourceforge.net/litesql.dtd">
<database name="TestDatabase" namespace="test">
<object name="Person">
<field name="name" type="string"/>
</object>
<relation name="FriendsRelation">
<relate object="Person" handle="friends"/>
<relate object="Person"/>
</relation>
</database>
Usually relations are accessed using a relation handle that is attached to a persistent object. Sometimes, it may be convenient to access relation using static methods of Relation-class.
Methods:
- link : link objects
- unlink : remove a link between objects
- get : get a DataSource of objects
- del : drop links using an expression
- ( getTYPEn : non-template versions for relations with duplicate types )
Relation handle is attached to persistent object and it provides convenient access to relation. It can be used to link, unlink or select objects related to relation handle's owner.
RelationHandle-class' methods:
- link : link owner to another object
- unlink : unlink owner from another object
- del : drop links using an expression
- get : get a DataSource of objects (generated for n-ary relations)
- get : get a DataSource of known type (generated for 2-ended relations, only one type)
- ( getTYPEn : non-template versions for relations with three or more duplicate types )
A linking example:
Person bill(db), bob(db);
bill.name = "Bill";
bill.update();
bob.name = "Bob";
bob.update();
// both objects must be stored in database before they can be linked
bill.friends().link(bob);
// following statement would throw an exception because they are already friends
// (friends is bidirectional relation)
bob.friends().link(bill);
A fetching example:
Person bob = bill.friends().get(Person::Name == "Bob").one();
vector<Person> billsFriends = bill.friends().get().all();
An unlinking example:
// Bill and Bob are no longer friends
bill.friends().unlink(bob);
~~~~~~~~~~~~~~~Relation-Examples
Same examples as above converted to static methods of FriendsRelation.
A linking example:
~~~~~~~~~~~~~~~{.cpp}
Person bill(db), bob(db);
bill.name = "Bill";
bill.update();
bob.name = "Bob";
bob.update();
// both objects must be stored in database before they can be linked
FriendsRelation::link(db, bill, bob);
A fetching example:
Person bob = FriendsRelation::getPerson2(db,
Person::Name == "Bob",
FriendsRelation::Person1==bill.id).one();
vector<Person> billsFriends =
FriendsRelation::getPerson2(db, Expr(),
FriendsRelation::Person1==bill.id).all();
An unlinking example:
// Bill and Bob are no longer friends
Friendsrelation::unlink(bill, bob);
In order to support virtual methods a Persistent object must be upcasted to correct type before calling virtual method.
upcast will return an auto_ptr which holds a pointer to an object of correct type.
upcastCopy is similar to upcast. It does not select an object of correct type from database but just constructs it in memory. Note that constructed object does not have all of its fields. This method is suitable when virtual method to be called does not need any special fields.
An example of upcast:
Playable pl = select<Playable>(db).one();
pl.upcast()->play();
An another example of upcast:
Playable pl = select<Playable>(db).one();
auto_ptr<Playable> uc = pl.upcast();
cout << "Playing " << uc->name << endl;
uc->play();
An example of upcastCopy:
Playable pl = select<Playable>(db).one();
pl.upcastCopy()->makeLogEntry(log);
Often it is not necessary to fetch all objects to memory and then process them.
Memory usage can be minimized using template class Cursor which returns one object at a time.
Creating a cursor(two ways):
Cursor<Record> cur1 = db.cursor(SelectQuery().result("id_").source("Person_"));
Cursor<Person> cur2 = select<Person>(db).cursor();
Iterating through the result set:
for (;cur.rowsLeft(); cur++)
cout << (*cur).name << endl;
The remaining contents of the result set can be retrieved with dump-method:
vector<Person> rest = cur.dump();
If you prefer to access database using raw queries, use plain Database-class and query-method.
escapeSQL-function can be used to escape illegal characters such as '. NULL is a special word and it has to be escaped by the user. 'NULL' maps to NULL and ''NULL'' to 'NULL' etc.
Use Database::groupInsert - if you want to create rows with new integer as the first field.
A query example:
Database db("sqlite3", "database=test.db");
db.query("CREATE TABLE custom(value INTEGER);");
db.query("INSERT INTO custom VALUES (1)");
Records recs = db.query("SELECT * from custom");
for (Records::iterator i = recs.begin(); i != recs.end(); i++)
cout << (*i)[0] << endl;