Storable is the persistence engine included with Project Apollo. It can be used out side the context of the rest of Apollo, and vice versa, the rest of apollo (with a few exceptions) can be used without Storable. Storable is a mechanism to map Java Objects to a relational data store. This allows the sum of the Objects to be greater then the sum of the relational database store. Methods relating to business logic and other methods can be added to the Object as any other java object. However the object itself contains the know-how to put itself into a table in a relational database, and the object can be retrieved from that same store. This is all accomplished without using Java's serialization mechanism, and can be used on any standard java JVM. The aim of Storable is to store these objects in non-serialized form, using database primitives (serialization of other attributes is possible, see later description as to why this is bad). With all data elements stored in database primitives, complex natural joins can be done at the database level, and correlated to the object level for the programmer. This ability to offload complex data processing to the database gives an enormous speed boost to complex queries when compared to processing the same query locally over a set of objects.
There are two different ways to query the database to get your objects back. You can load a single object out of the database, by it's primary key. You can also load a set of objects out of the database using a set of conditions.
loadInstance
With the helper methods (described in Creating a Storable Object) added to your storable objects, you can load a database object using the object's primary key. You pass the primary key to loadInstance as a Object array, with the Primary key elements in the same sequence they are defined in the Storable class.
User u = User.loadInstance(MT, new Object[] {"joe"});
loadVectorInstance
With the helper methods added to your storable objects, you can load a set of database objects out of the database using the loadVectorInstance function. You may only load a single type of object out of the database using loadVectorInstance, however you may join data from other tables. You specify the set of conditions to use when loading objects out of the database using a WhereQuery object. If you are familure with SQL, you can think of the WhereQuery object representing the "where" part of the select clause.
Excerpts from User.java:
User u = User.loadInstance(MT, new Object[] {"joe"});
GregorianCalendar gc =
new GregorianCalendar();
gc.set(Calendar.MINUTE, 0);
gc.set(Calendar.HOUR, 0);
gc.set(Calendar.SECOND, 0);
Date startOfToday = gc.getTime();
WhereQuery wq = new WhereQuery();
wq.insertWhereClause(WhereQuery.AND, "lastLoginDate", User.class, startOfToday, WhereQuery.GREATER);Vector usersLoggedInToday = User.loadVectorInstance(MT, wq);
//Something using a join
wq = new WhereQuery();
wq.insertWhereClause(WhereQuery.AND, "strUserID", User.class, "strUserID", JobMembership.class);
wq.insertWhereClause(WhereQuery.AND, "jobID", JobRecord.class, "jobID", JobMembership.class);
wq.insertWhereClause(WhereQuery.AND, "jobID", JobRecord.class, "jobID", JobMembership.class);
wq.insertWhereClause(WhereQuery.AND, "resultTypeID", JobRecord.class, new Integer(JobManager.JOB_FAILED));
wq.insertWhereClause(WhereQuery.AND, "startDate", JobRecord.class, startOfToday, WhereQuery.GREATER);
//Vector of user Objects from users who had Jobs fail today
Vector usersWithFailedJobsToday = User.loadVectorInstance(MT, wq);
//Vector of those failed JobRecords
Vector failedJobRecordsToday = JobRecord.loadVectorInstance(MT, wq);If you need to write your own custom WhereQuery, you can use a class called RawWhereQuery to create a custom WhereQuery.
If you need to write your own custom WhereQuery, you can use a class called RawWhereQuery to create a custom WhereQuery.
Sometimes you need to just load a single piece of data out of the database, perhaps a Count, Max, Min, Average or some other function. You may even need to aggregate data using the SQL Group By clause. Using the loadFunction method you can access this data without the overhead of creating database objects. You need to create a StorableFunction object to describe the database function (or data) you which to query. There are a number of standard functions already predefined. Once you have the StorableFunction, you combine it with a WhereQuery (describing the set of data to operate over) like this:
StorableFunction sf = new StorableFunction(Storable.COUNT);
JobRecord.loadFunction(MT, sf, wq);Here's a couple examples of generating StorableFunctions for the data you want.
For example:
new StorableFunction(Storable.COUNT);
This creates a function which counts the number of matches from executing the WhereQuery (Count(*)).
new StorableFunction(Storable.MAX, User.class, "numLogins");
This creates a function which finds the Max of the numLogins column, over the WhereQuery's set of objects.
new StorableFunction("User.firstName, User.numLogins");
This creates a custom function fetching just the User.firstName, and User.numLogins fields. You'll see below we'll use this in a ridiculous example explaining Group By aggregations.
The loadFunction function will return the appropriate datatype. If your using count, it will return an Integer, if you're using MAX, MIN, AVERAGE, you'll probably get a Double or Float back. You'll have to be familure with the data in your database and the types of fields your querying to know what type you'll get back. If you request a custom function, you will get back a ResultSet matching your query. Here's an example from the demo site, from LaunchJobBroker.java. It counts the number of non-completed jobs a particular user has.
new WhereQuery();WhereQuery wq =
wq.insertWhereClause(WhereQuery.AND, "userID", JobMembership.class, user.getUserID());
wq.insertWhereClause(WhereQuery.AND, "jobID", JobMembership.class, "jobID", JobRecord.class);
wq.insertWhereClause(WhereQuery.AND, "statusID", JobRecord.class, new Integer(JobManager.JOB_FINISHED), WhereQuery.NOT_EQUAL);
wq.insertWhereClause(WhereQuery.AND, "statusID", JobRecord.class, new Integer(JobManager.JOB_FAILED), WhereQuery.NOT_EQUAL);
Integer countOfRunningJobs = (Integer) JobRecord.loadFunction(MT, new StorableFunction(Storable.COUNT), wq);
The loadFunction can also be used to do Group By aggregations of objects. Here's a rather silly example using the storable classes in the demo site. This query will count the number of logins for ever user who logged in more then 2 times, and group them by first name. So if Joe Smith logged in 5 times, and Joe Kislo logged in 3 times, and Bob BottleNoseDolphin logged in 5 times, you would get back
Joe 8
Bob 5
This example is not very useful, but Group By queries are extremely powerful. When using complex Group By's with several natural joins be sure to optimize your use of indexes.
wq =
new WhereQuery();
wq.insertWhereClause(WhereQuery.AND, "numLogins", User.class, new Integer(2), WhereQuery.GREATER_EQUAL);
java.sql.ResultSet rs = (java.sql.ResultSet) Storable.loadFunction(MT, new StorableFunction("User.firstName, User.numLogins"), new Class[] {User.class}, new String[] {"firstName"},wq);
Learning how to maximize the efficiency of your queries requires understanding the underlying relational database concepts. If you're already familure with writing queries using natural joins, group by's, you can probably skim this following document fairly quickly. Athenium has been nice enough to donate the worksheet I used when I taught a class explaining Storable at Athenium. The worksheet runs through a number of simple queries, and moves up to more complex queries. It also includes a few bonus topics I haven't included here, such as Limit queries, Ordering results, and atomic updates. The answer key includes answers for both the Python and the Java implementation of Storable. The Python implementation of Storable implements the same functionality as the Java version, but is sometimes a bit nicer to look at because of some Python syntactic gloss is made possible by Python's loosely typed language constructs. The Python implementation is owned by Athenium, and there has not been any interest expressed by the community for Athenium to Open Source it's Python implementation.
AtheniumLLC Natural Joins Worksheet
Creating a storable object class can be easily done using the web based MySQLAdmin program included with apollo. Simply go to create a table, define your table, and press the Make Storable button. More detail is given below in the MysqlAdmin Storable Generation section. Understanding how to create a Storable class from scratch is important; so you can understand the details and how to tweak Storable object's operation.
Storable objects must extend the Storable class. There are several special data structures and methods which must appear in a Storable class. By looking at the User.java example from the demo site, here's an explanation:
fieldType
This field describes the database schema. As you can see this is a double array, and is arranged into columns.
The first column is the name of the field in the database. Be sure not to use a keyword, as defined by your underlying database.
The second is the field type; this data is only used when Storable must create the table in the database. As you can see here we're using a StorableType to describe the schema using a database independent set of descriptors. For a list of the database independent column types, look here. You may substitute the StorableType object with a string with the raw column type for your underlying database. You should be warned, almost all databases I've used use very different column type definitions. So you if you use a non-database indepdenent type, you will most likely be breaking out the SQL books if you move your data to a different underlying database.
The third column is the Java class which will be instantiated to describe that field.
The fourth column is the null status for that column, True for NULL, False for NOT NULL. Here's an example from the demo site:final
static public Object[][] fieldType = {
{"userID", new StorableType(StorableType.VARCHAR, 50), String.class, False },
{"firstName", new StorableType(StorableType.VARCHAR, 50), String.class, False },
{"lastName", new StorableType(StorableType.VARCHAR, 50), String.class, False },
{"passwordField", new StorableType(StorableType.VARCHAR, 12), String.class, False },
{"lastLoginDate", new StorableType(StorableType.DOUBLE), Date.class, True },
{"numLogins", new StorableType(StorableType.INTEGER), Integer.class, False },
{"emailAddress", new StorableType(StorableType.VARCHAR, 75), String.class, False },
{"securityLevel", new StorableType(StorableType.INTEGER), Integer.class, False },
};
fieldPrimaryKey
This field describes the primary key for the database table. The primary key always uniquely identifies this particular object. With this key you can use the loadInstance method to load a particular instance of a Object. If there is no unique identifier for the object (such as a Message), using an autoincrement field is advised (EG: MessageID). The primary key can be multiple columns in width.
final static public String[] fieldPrimaryKey = { "userID" };
Full Constructor for Object
A full constructor is required for the object. All datafields appearing in the fieldType must appear in the constructor, and in the same order. Java polymorphizes on Field Type; so the field types in the constructor must exactly match those in the fieldType third column. Without a proper constructor, objects cannot be retrieved from the database. You may create your own constructors for your own use; however this full constructor is required for storable to function. It is advisable (for sanities sake) to keep your variables names in the java object the same as the names of the columns in the the database; but not required. It is required that in all constructors that you pass to the superclass the fieldType, and fieldPrimaryKey fields.
public User(String userID, String firstName, String lastName, String password, Date lastLoginDate, Integer numLogins,String emailAddress, Integer securityLevel) {
super(fieldType, fieldPrimaryKey);
super.testStorableConfiguration(this);getFieldValues()
This method returns, in the same sequence as the fieldType, all the data members being sent to the database. By using a method to return the values when Storable is storing an object in the database, it preserves the programmers ability to have data members be private. The order in which members are returned is critical.
public Object[] getFieldValues() {
return new Object[] { userID, firstName, lastName, password, lastLoginDate, numLogins, emailAddress, securityLevel };
}Getters/Setters
It's probably a good idea to use getters and setters to access object attributes. When setting the value of a database stored attribute you may wish to use the setUpdatedField(String) method. This method lets the Storable layer know that the attribute has been updated. When an object is stored into the database using the update() method, only updated attributes will be sent to the database for modification. Calling this method is not required; if this method is never called, Storable will update all attributes.
public
String getLastName() {
return this.lastName;
} public void setLastName(String lastName) {
this.lastName=lastName;
setUpdatedField("lastName");
}Helper Methods
There are several static methods which you should add to your Storable subclasses. These methods aid in automatically casting Objects to their appropriate type. If you use the MysqlAdmin Storable generator, these methods will be added automatically for you.
static public User loadInstance(ManagerTracker MT, Object[] theLoadKey) {
return (User) Storable.loadInstance(MT, User.class, theLoadKey);
} static public User[] loadArrayInstance(ManagerTracker MT, WhereStatement whereStatement) {
return (User[]) Storable.loadArrayInstance(MT, User.class, whereStatement);
} static public Vector loadVectorInstance(ManagerTracker MT, WhereStatement whereStatement) {
return Storable.loadVectorInstance(MT, User.class, whereStatement);
}
The MysqlAdmin utility included with the demo site is a handy tool for working with MySQL based databases. It includes the ability to define a table's schema, then create the table in the database. However when you're working with Storable classes, you don't need to create the table schema ahead of time in the database; the storable itself will create the table if necessary. You'll notice that there is a "Make Storable" button next to the "Create Table" button. After you have defined your database schema in the create table tool, you can hit the Make Storable button to create a storable class reflecting that database schema. You can then save the file as a java file, and start customizing the base Storable by adding your object methods. It's probably a good idea if you are writing a good sized application to place all your Storable objects in a different java package. If you do this; be sure to remember to save the Storable java file in the correct directory, and add a package line to the file.
When you have constructed a Storable subclass, you'll likely want to insert it into the database. The insert(ManagerTracker) method on the object will insert the object into the database. You should only call the insert method if the object does not already exist in the database. If you attempt to insert an object which already exists (by virtue of unique key/primary key) a DuplicateKeyException exception will be thrown. If you have a storable object which you have modified, you should call the Update(ManagerTracker) method to update the contents of that object to the database.
User u =
new User("joe", "Joe", "Kislo", "jk", null, new Integer(0), "[email protected]",new Integer(0));
u.insert(MT);
u.setStrFirstName("Bozo");
u.setStrLastName("Clown");
u.update(MT);
Here's some more examples from User.java.
When defining the schema of a particular Storable class you need to declare what type of column that data will be stored in on the database. It turns out that the keywords describing the column types differ greatly across relational databases. A database independent set of storable types has been created to allow you to declare columns, and have the DBAdapter convert them to the database's native keywords. Here are the types:
CHAR VARCHAR INTEGER DOUBLE FLOAT BLOB INTEGER_AUTOINCREMENT Here are the constructors for StorableType:
public
StorableType(int typeCode, int count) public StorableType(int typeCode)As you can see you can also declare a count with the type code, so (StorableType.CHAR, 25) would result in CHAR(25) on most databases. INTEGER_AUTOINCREMENT defines the column as a column which will be issued a unique integer for each inserted record. This is called a generator in Interbase, a "serial" or sequence column in Postgre, and an autoincrement column in MySQL. Storable will take care of creating generators, sequences, or whatever the underlying database requires for this to function. Your objects will not have this column set until the record is inserted into the database. Once the insert(MT) method is called, the attribute is set to the database value via the
protected void setAutoIncrement(long l)
method on your Storable class.
At some point each of these storable classes needs to be connected to a database. You do this by attaching each one of the Storable Classes you plan on using to the appropriate database adapter. You could attach some tables to the Interbase DBAdapter, and some to the MySQL DBAdapter. Storable will automatically query the correct database when you load objects. (You will, however, be unable to join across databases). You can also port your application to a different database platform by simply moving your data, and connecting different DBAdapters. You could even convert your data as easily as connecting the old DBAdapters, loading all objects to be converted into memory, and connecting the new DBAdapters, and reinserting. The DBAdapters represent the concrete implementation of the Storable functions for a specific database. The apollo properties file allows you to specify a Class which will connect all your Storable classes to the appropriate database adapters. Take a look at the DBConnectors Class from the demo site. You can see how each Storable class is bound to a MySQLAdapter, and the table name MySQL should use for that class is passed in.
DBConnectors.java
You've probably noticed that a ManagerTracker (MT) gets passed into most database functions. If you havn't read about apollo's ManagerTracker, you might want to now. Briefly, the ManagerTracker will hold all the pointers to the DBAdapters for storable. To access your databases, you must have a ManagerTracker available. That usually means passing a reference wherever you want database access. If you're writing a standalone application, it may be acceptable to a singleton ManagerTracker reference, making it effectively a global variable, which can be accessed from anywhere without a pointer. If you're writing a web application (or any application), you should really really think twice about making a global reference. I wrote a bit about that here.
Storable takes the same approach that JDBC does about Transactions, you the programmer will be responsible for making sure your transaction requests are compliant with the underlying database. For example, MySQL does not currently support transactions, however Interbase does. Ideally we would like code we write for Interbase to be directly moved to MySQL by simply changing in the Mysql DBAdapter. For the rest of storable this is true, however not with transactions. For this to be true, the MySQL Adapter would have to somehow implement database transactions in the DBAdapter layer; since the database itself does not support it. This is an unreasonable request for a database abstraction layer. So, if you write an application using the Interbase DBAdapter, then try to move to the MySQL Adapter, it will require you to deal with the fact that transactions are no longer available. This will probably mean dealing with the lack of rollback capability; which is most commonly used in the face of failure. Begin and commit transactional requests will be sent to the MySQL server by the MySQLAdapter, but will have no effect unless you're using the experimental BDB tables. It shouldn't be too much work to port transactional code between DBAdapters; but Storable will not give you transactional features which the underlying database does not support.
Transactions require the use of a TransactionTracker, and not a ManagerTracker. In the above examples such as:
User u = User.loadInstance(MT, new Object[] {"joe"});
We passed in the ManagerTracker to the Storable function. The ManagerTracker gave the Storable functions access to the DBAdapters. However the ManagerTracker is an object with a global context. It's the same object which you have in all of your classes; so it cannot possibly represent the current transaction. When you pass in the ManagerTracker to the storable functions, you will have no transactional context. The first database Connection available will be used, and sequential database requests may not use the same database connection.
A TransactionTracker is the same thing as a ManagerTracker, except that it's context is not global. When you create a TransactionTracker, it will only be accessible from your local context. When you pass a TransactionTracker into a Storable function, you will always be using the same database connection. This allows you to operate inside a transactional context.
TransactionTracker TT = new TrasactionTracker(MT);
Examples
Is it possible to have Storable track the most current version of a storable object, and update other memory references when a new version is created. This functionality is needed if you end up keeping multiple Storable instances in memory for an extended period of time. This is likely to happen if you store a Storable object in a WebSession. Since each Storable instance is a seperate instance, updating one copy of the same Storable object will not update any of the other instances. This may be a problem since a stale object may end up being using when it is fetched out of a Session.
To get around this problem you could optionally load the Storable object out of the database whenever you need it; but that would impose additional server time; and there would be no reason to store it in a session. With the synchronizable feature, you can simply call the synchronize(MT) method on the Storable object, and it will update itself to the newest version if it is out of date. If the Storable object is up to date, no database access will occour, and only a highspeed hashtable lookup will occour.
To use the Synchronizable feature, your storable class must extend Synchronizable. This will mean adding a setValues method to your Storable class. This method will be called by storable when it needs to reprogram an existing Storable instance's values to conform with the newest version in the database. If you have triggers in your constructor for the Storable object, they should be repeated in the setValue method.
Then simply call the synchronize(MT) method whenever you pull the Storable object out of the session, and you will always have the most recent version.
Storable ExceptionsStorable exceptions create a exception hierarchy staring with StorableException, which extends RuntimeException. This is very important, because RuntimeExceptions do not need to be declared. All storable functions run the possibility of throwing a StorableException. If it is not caught locally, it will travel up the stack until it is. The decision to extend RuntimeException instead of Exception was made to avoid requiring try catch clauses for every single database request. DuplicateKeyException occurs when you have violated a unique key integrity constraint on the database (probably the primary key). MalformedStorable exception is the general exception when something broke, and really shouldn't have.
Storable Multi-Threaded Operation
When not operating in a transactional context and issuing a loadVectorInstance storable command, your request may be processed using the Work Manager. If the work manager processes your request, your database request will be processed by a worker thread, and your DB query will immediately return a Proxy Object. This would allow you to maximize code efficiency by not blocking during a database call. With control returning immediately to your application after your request, you can continue application work until you actually require the database object you requested. When you try to access the database object your thread will be blocked until it's finished loading. This works on the very simple idea that there's no reason to sit blocked waiting for a database query to complete if there's something better to do. This can drastically improve throughput of database requests by maximizing CPU usage. Even when the database server resides on the same machine and there is only a single CPU, you will still be able to capitalize by using the CPU while the database server is blocked on I/O.
As you can imagine the parallel database queries will not be effective inside a transaction context, because a transactional context (on nearly all SQL servers) is tied directly to a single database connection. I don't believe you can have two Statements concurrently executing statements against the database server from a single JDBC connection. So, unless the database server has the ability of accessing a transactional context from different database connections, all database requests inside a transactional context need to be issued in serial order into that single database connection. I have seen some hooks in Interbase for the naming of transactions; which would allow the access of transactions across multiple connections. At this time I have not been able to get this facility in interbase to function as advertised.
Storable Implementation Notes
Storable object instances are unique, and are not static inside the JVM. Perhaps better explained; if you load the same object twice out of the database, you will have two instances of the identical objects, and not two pointers to the same instance. If you change an attribute on one, the other will not change. This is important to keep in mind if you store Storable objects inside a WebSession. If this disturbs you (about objects stored in a Session getting stale) go read the section about Storable Object Memory Synchronization.