Michael L Brereton - 02 February 2008, http://www.ewesoft.com/
>>
Next: Advanced Database Techniques
Specifying Database Fields, Sorts and Indexes
Specifying Database Fields and Sorts Using a Data Object
Initializing and Opening a Database
Accessing Database Data Using a Data Object
Searching For Database Records
This chapter
deals with the storage of application data in a simple database that is
portable from the desktop to the mobile device.
Please note
at this point that the databases that we will be dealing with are not
desktop/server SQL compatible databases. Accessing those types of databases
will require an external API and implementation. Rather, the databases access
provided by Eve is of a proprietary type (which may be different on different
platforms) that is designed for the storage of simple data with an API that
allows for the synchronization of desktop and mobile data.
The data
stored in an Eve Database would be thought of as a single Table in a
standard relational database system. Each database contains a fixed number of Fields
(which can be thought of as Columns). Each field has a different
name, type and numeric ID. Individual entries in a database can be thought of
as the Rows in the table.
This
chapter deals with a platform independent API that will work across
platforms that support file systems, and those that do not. Specifically, this
API uses the following classes and interfaces, all of which are in the eve.database
package.
Database |
An
interface that specifies the functions provided by a platform specific
database. |
DatabaseEntry |
An interface
representing an individual unit of data stored within a Database. |
FoundEntries |
An
interface representing a collection of DatabaseEntry references that
represent some subset of the Database data, usually sorted by some criteria. |
EntriesView |
A subset
of references into a FoundEntries object, which can be searched, re-arranged
and modified without affecting the source FoundEntries. |
DatabaseManager |
Used to
create and open databases. |
DataValidator |
Used to
validate entries before being modified or stored. |
There are
other classes and interfaces in the package, and these will be explained in the
following chapter.
A Database
will store a list of Records (the rows of the table), represented by a DatabaseEntry
object when brought into memory for reading or editing. Each record consists of
a set of Fields (the columns of the table) each of which is assigned a
unique ID and name and has a specific type. The Fields that are stored
in a record are specified when a Database is created and can be altered after
the database is already in use (that is to say, fields may be added and
removed, but fields types cannot be changed).
A Database
can have multiple Sorting Criteria (referred to as Sorts)
associated with it. Each Sort is basically a named list of up to four fields
that are used to sort the records in the Database. For example a contact
database may have a Sort that sorts by the contact’s last name, and then first
names. This Sort would only use two fields and could have a name such as “By
Name”. It is also possible to provide more complex sort criteria using a custom
sorting class - but this will be covered in the advanced chapter.
A Database
can have multiple Indexes. Each index is a table of references to the
entries in the database, sorted by some criteria. The entries in an index are
updated and re-arranged when entries are added, edited or removed from the
database. Indexes should be used for large databases to avoid long sort times
each time the database is opened.
The records
within the Database are not sorted themselves. Sorting is only done when
the getFoundEntries() method is called on the Database. This method
returns a FoundEntries object that has references to entries in the
Database sorted by the sort criteria specified in the getEntries() call.
However if the sort criteria specified by the getEntries() call matches a saved
Index for the database, then this index is used to create the FoundEntries so
that a sort operation will not have to be performed.
Since a
Database is an interface it cannot be instantiated directly. To open a Database
you will use one of DatabaseManager.initializeDatabase() or DatabaseManager.openDatabase()
methods as described in the next section. For the moment we will not deal with
these methods, rather we will assume that we have already acquired an open
database that is ready to have the fields set.
This
section deals with specifying fields and sorts explicitly using the bottom
level methods. However there are some higher level methods, described in the
following section, that allow you to specify the fields and sorts of a Database
by using fields within a Java class. This simplifies programming significantly
and its use is highly encouraged. However before dealing with these high level
methods, the basic concepts of fields and sorts need to be understood and these
are explained in this section.
The bottom
level method for adding fields to the Database is the addField(String
fieldName, int fieldType) method. The fieldType must be one of the
following types (defined in the DatabaseTypes interface)
BOOLEAN |
Used to
store true/false values as represented by the Java boolean type. |
DECIMAL |
Used to
store arbitrarily large Decimal values as represented by the Ewe ewe.sys.Decimal
class (which uses a BigDecimal for its implementation). |
DOUBLE |
Used to
store a 64-bit double precision floating point number as represented by the
Java double type. |
INTEGER |
Used to
store a 32-bit integer number as represented by the Java int type. |
LONG |
Used to
store a 64-bit integer number as represented by the Java long type. |
STRING |
Used to
store an arbitrary length Unicode text string as represented by the Java String
class. |
DATE |
Used to
store a date value as represented by the Ewe ewe.sys.DayOfYear class. |
TIME |
Used to
store a time value as represented by the Ewe ewe.sys.TimeOfDay class. |
DATE_TIME |
Used to
store a date and time value as represented by the Ewe ewe.sys.Time
class. |
TIMESTAMP
|
Used to
store a timestamp value as represented by the Ewe ewe.database.TimeStamp
class. |
BYTE_ARRAY
|
Used to
store an array of bytes as represented by the Java byte[] type or ewe.util.ByteArray
class. |
JAVA_OBJECT |
Used to
store a Java object which is encoded somehow as a sequence of bytes. |
This method
returns a FieldID – an integer value unique to this field within this
Database. All further operations on the added field use the FieldID as the
field reference. You can always get the FieldID of a named field, or the name
and type of a FieldID using the following methods:
int findField(String fieldName) – Find the ID of the named field.
int [] getFields() – Get all the fields.
int getFieldType(int fieldID) – Find the type of the field.
String getFieldName(int fieldID) – Find the name of the field.
void setFieldHeader(int fieldID) – Set the header for the field.
String getFieldHeader(int fieldID) – Find the header for the field.
The Header
for a field is a long descriptive name for the field suitable for display as
the header in a table column or as a prompt in an input form. By default, the
header for a field is derived from the field name as follows:
By these
rules, a field name of last_name is converted to “Last Name”. A field
name of lastName will also be converted to “Last Name”. A field like customerDOB
is converted into “Customer DOB”. However by using setFieldHeader(int
fieldID, String header) you can always explicitly specify the header for a
field.
You can add
special predefined fields using the addSpecialField(int fieldID)
method. This adds one of the reserved XXXX_FIELD fields, such as the MODIFIED_FIELD
and OID_FIELD. Some of these fields are used for some forms of
synchronization and these are described in a later section.
The addSort(String
sortName, int options, int field1, int field2, int field3, int field4)
method is used to add a Sort that sorts using up to four fields. If less than
four fields are used in the sort, then the fields that are not being used
should be set as zero. For example if you are only sorting using two fields,
then field3 and field4 should be set to zero. Another method addSort(String
sortName, int options, int field) can be used to add a sort that sorts by
one field only.
The options
parameter is applied to all fields and consists of any of the following options
OR’ed together.
SORT_IGNORE_CASE – Ignore case when sorting Strings.
SORT_UNKNOWN_FIRST – If the value for a field is not set for a
particular entry then place that entry before others instead of after (the
default).
SORT_DATE_ONLY – When sorting Date/Time values, ignore the
time of day section and only compare the date.
SORT_TIME_ONLY – When sorting Date/Time values, ignore the
date section and only compare the time of day.
Again, the addSort()
methods return a unique ID identifying that Sort. You can use findSort(String
sortName) to locate the ID of a named Sort and you can use getSortName(int
id) to get the name of the Sort with the specified ID.
There are a
number of indexBy() methods provided. An index does not have to be
sorted by a sort created by addSort() but it usually is. Simply call the method
indexBy(Handle h, int sortID) to add an index for the sort. This index is
constantly maintained whenever the database is modified. Forgetting to index
your database can lead to poor performance especially on mobile devices for
large databases.
The
Database interface allows you to specify the fields in a Database using the
fields in a Java class. You simply define the class as being public with the
fields declared as public. For example:
//##################################################################
public class Contact {
//##################################################################
public String lastName = "";
public String firstName = "";
public String company = "";
public ewe.sys.DayOfYear dob = new ewe.sys.DayOfYear();
public boolean isColleague = false;
//
// This next field is not treated as a data field
// because it starts with a '_' character.
//
public String _sorts = "By Name$ix|lastName,firstName|By DOB$t|dob,lastName,firstName";
//##################################################################
}
//##################################################################
Now you can
set the fields in the database to match the fields in the class by simply
doing:
int []
ids = database.setFields(new Contact());
The return
value is an array of integers representing the unique ID assigned to each
field. From this point on, fields are referred to using the ID of the field.
Again, the header for a field is derived from the field name as
described above.
You can
also use the method setFields(Object data, String fields) to set the
fields. This will set only the fields specified in the fields
parameter, which must be a comma separated list of fields. For example we could
have done: setFields(new Contact(), "lastName,firstName,dob")
to only add the specified three fields.
We could
also use the method setFields(Object data, String fields, String headers)
which allows you to specify a field list and a list of headers to be assigned
to the fields.
You can
specify the sorts of a database using setSorts(Object data, String sorts).
The sorts parameter must be formatted as follows:
“<SortName>|<FieldList>|<SortName>|<FieldList>…”
Each FieldList
should be a comma separated list of fields to be included in the sort, in the
order of priority.
Each SortName
should be a unique name for the Sort (e.g. “By Name”, “By DOB”). A SortName
can end with a $ followed by any number of the following letters that
specify sort options:
i – indicating the SORT_IGNORE_CASE option.
t – indicating the SORT_DATE_ONLY option.
m – indicating the SORT_TIME_ONLY option.
u – indicating the SORT_UNKNOWN_FIRST option.
x – indicating that an index should be created for this sort.
An example
of a formatted Sort specifier is:
"By
Name$ix|lastName,firstName|By DOB$t|dob,lastName,firstName"
You can
also specify the sort order within the Object itself by declaring a public
String variable called "_sorts". Because this variable begins with
an ‘_’ character it is not treated as a data field and is not added in with the
other fields when setFields() is called. If you have the _sorts variable
defined then you can use the setSorts(Object data) method to set the
sorts for the database.
In our Contact
class defined above we could set up the fields and sorts for the database
using just four lines:
Contact c = new Contact();
database.setFields(c);
database.setSorts(c);
database.save();
The method DatabaseManager.initializeDatabase(DatabaseMaker
maker, String name, Object initializer) should be used whenever your
application first accesses the database when it is run. If the named database
already exists this method returns null. This lets you know that the database
had been created and initialized on a previous execution.
If initializeDatabase()
returns a Database object, this will be an uninitialized Database and you
should initialize it as necessary, save it and then close it. If
the initializer parameter is not null, then the fields and sorts of the
database will be taken from the initializer object as described in the previous
section before the database object is returned. If it is null then the database
will have no fields and no sorts set when it is returned.
The maker
parameter to this call can be null, in which case the default
DatabaseMaker will be used. Eve provides a default DatabaseMaker that will
provide a Database implementation for the running platform. You can change the
default DatabaseMaker if you whish using setDefaultDatabaseMaker().
Following
database initialization you should then call DatabaseManager.openDatabase(DatabaseMaker
maker, String name, String mode) to open the database for read-only
(“r”) or read-write (“rw”) mode.
An example
is given below – it is a method used to open a database that is used to store
contact information based on the Contact object given in the previous examples.
//===================================================================
public Database openMyDatabase(String name) throws IOException
//===================================================================
{
Database db = DatabaseManager.initializeDatabase(null,name,new Contact());
if (db != null){
//
// Do your extra initialization here.
//
db.save();
db.close();
}
return DatabaseManager.openDatabase(null,name,"rw");
}
As
mentioned before you retrieve the data within a database using the FoundEntries
object. In fact you also use this object to add, edit and delete records in the
database.
A
FoundEntries object does not contain the data stored in the database. Rather,
it contains a sorted list of references (32-bit integers) to the records in the
database. The record data is not brought into memory until one of the get()
or getData() methods are called, or until some sort of search is done.
The sort
of a FoundEntries cannot be changed and as data is added, edited or removed,
the sort state of the FoundEntries will always be maintained.
To get a
FoundEntries object you call one of the Database.getFoundEntries()
method, the simplest of which is getFoundEntries(Handle h, int sortID).
This returns a FoundEntries object that refers to all the records in the
database, sorted by the specified sortID (or unsorted if sortID is 0). The Handle
parameter is optional. If it is not null then another thread can monitor and
possibly abort the process using the stop() method, in which case the
getFoundEntries() method returns null.
You can
also call one of the getFoundEntries() methods which return a Handle.
These methods create a new thread to create the FoundEntries object and then
return immediately. The returned Handle is used to monitor and possible abort
the process. Once the Handle state reports Success you can get the
created FoundEntries using the returnValue of the Handle.
If you want
to add records to the database and are not interested in reading the records
that are already stored, then you can call Database.getEmptyEntries().
This returns a FoundEntries object for the database that contains no
references.
The number
of entries in a FoundEntries is retrieved using the size() method. The
entries within the FoundEntries are then accessed using indexes from 0 to
size()-1.
The getData(int
index) method can be used to retrieve the data in the record at the
specified index. Remember that the index specifies its order within the
FoundEntries, not its order within the database. This method also relies on a
default constructor being available for the data object sot that a new one can
be created and returned. You can also call the getData(int index, Object
data) method. This retrieves the data into the specified data object –
which must be of the same type as that used when the database was set up.
The delete(int
index) method is used to delete a record in the database. This method will
also remove the reference to the record from the FoundEntries object thereby
reducing its size by one.
The addData(Object
data) method is used to add a record to the database. The record is added
to the database and then a reference to the record is added to the FoundEntries
in the appropriate location as determined by the Sort used by the FoundEntries
– thereby preserving its sorted state. The index of the newly added record is
returned by this method.
You can
modify the data for a record by calling setData(int index, Object data).
This sets the record at the specified index to contain the data in the data
object. This method will also move the reference to the record to a new
location in order to keep the FoundEntries object sorted. The new index of the
data is returned by this method.
If you are
using a data object to represent your database records (a practice that is
highly encouraged) then the methods described above are all you need to access
and modify the records in a database for the majority of database usage, even
if you intend to synchronize your database across a remote connection. However
for certain advanced database operations – such as implementing a more advanced
synchronization system – you may have to deal with DatabaseEntry objects
directly. This will be discussed in another chapter on Advanced Database
Techniques.
An EntriesView
object is essentially a managed set of indexes into a specific FoundEntries
object. So it is essentially a view into a FoundEntries. However, unlike
the FoundEntries object, an EntriesView object can have its entries re-arranged
so that it no longer is sorted as per the FoundEntries criteria. However the
EntriesView can be very quickly re-sorted to match the FoundEntries since a
sort on the EntriesView is simply a sort on the numeric indexes which it
contains.
You can
call getEmptyView() on the FoundEntries to get a view of the
FoundEntries that contains no indexes. You can also call getFullView()
to get a full view of the FoundEntries.
You can use
the EntriesView object to add, modify and delete objects in the database, in
much the same way as you would use a FoundEntries object. However, in this
case, the EntriesView object does not attempt to maintain the sort order of the
view, although the sort order of the associated FoundEntries will always be
maintained. You can call sort() on the EntriesView at any time to sort
it according to the FoundEntries criteria.
Searching is done on an EntriesView object
using a binary chop search. This requires that the FoundEntries be in a fully
sorted state. That is a non-zero Sort must have been used when it was created.
To search for a set of records you would create an empty EntriesView for the
FoundEntries and then use one of the search() methods.
For
example:
FoundEntries fe = database.getFoundEntries(null, "IndexByName");
EntriesView ev = fe.getEmptyView();
ev = ev.search(null, "B*");
Now the
EntriesView ev contains all the records where the name field starts with
a 'B'.
There are a
number of search() methods. See the API for a complete list.
Filtering, on the other hand, does not
require that the FoundEntries be sorted, since it involves checking each record
in the FoundEntries one at a time. There are similarly a number of filter()
methods that you can use.
Once you
have an EntriesView that contain the data you are looking for, you can access
the data using the get() methods. You can also create a new FoundEntries
that contains only the data from the view by using the getSubSet()
method in the original FoundEntries. You can then resort that FoundEntries
using a different sort criteria if you wish.