Package org.sandev.tools.JDBC

The JDBC package holds generic SQL utilities that work with relational databases adhering to the SQL-92 standard.

See:
Description

Class Summary

THD_JDBCPersister	A Persister implementation that communicates with a database via JDBC.
THD_QueryProcessor	A SandQueryMessage processor for standard SQL.
THD_ReinitConverter	A destructive implementation of the Converter interface that drops any bad tables and recreates them, DELETING ALL EXISTING DATA to avoid referential integrity problems.
THD_SchemaVerifier	Write table verification code to make sure the struct definitions we are working with match what is in the database.
THD_TableKeyCounter	A UniqueIDManager that finds the lowest unused uniqueID across all tables in the database, and then keeps track of the next available uniqueID via a counter kept in memory.
THD_UpdateProcessor	A SandUpdateMessage processor for standard SQL.

Exception Summary

SandSQLException

Provide a standard Exception ctor signature around a SQLException.

Package org.sandev.tools.JDBC Description

The JDBC package holds generic SQL utilities that work with relational databases adhering to the SQL-92 standard. In most cases these utilities can be used directly by a Persister implementation for basic setup.

This package does not make use of specific database extensions, and avoids doing work that is not generally applicable. It makes every effort to depend only on java.sql, so that the classes can be used under a variety of circumstances.

General assumptions and approach:

Inheritance is handled through separate tables for the parent and child, with the rows being joined on the uniqueID. We do not currently squish @abstract superclasses and classes together, since inheritance paths can change during initial development. This is a typical point of optimization in a customized production Persister implementation.

The uniqueID is the primary key. Management of primary keys is the responsibility of the Persister implementation and is not covered here in the general SQL utilities. Since primary key management can be tied to transaction management, this is also left to the Persister.

Non-reference arrays are converted to CSV and stored as a single VARCHAR. This emphasizes transaction simplicity over query processing. This may not be appropriate in a production environment, in which case this behavior needs to be overridden.

Query processing is subject to the limitations of the underlying database. Depending on the struct definitions and the depth of specification in the query message, it is possible to cause more joins and/or subselects than the database can handle. In that case the query will fail.

Processing of Match Expressions is limited to syntactic manipulation. If the resulting expression fails, then the query fails.

Drilldown matching is implemented via subselects using aliased table names.

Example primary key implementation:

For structs that do not inherit, and which do not contain any arrays of references, the uniqueID is declared as an IDENTITY column. This enables add operations to occur within a single database transaction, since a primary key does not need to be retrieved first. Using a struct model that takes advantage of this situation is strongly suggested for high volume persistence.

Note that the optimized add operation relies on none of the struct references being updated simulataneously. For example if you are adding a new Order, which references User, don't be adding the User as part of the same transaction. The AggregateUpdate processing will complain and the update will fail.

Steps for processing a transaction:

• For all add updates in the transaction that do not utilize an IDENTITY uniqueID, retrieve a new uniqueID value using the tableID counters.
• Each table that has a non-identity uniqueID has a corresponding row in the IDCounters table, which holds the nextID. The value is retrieved and then incremented, without any possibility of the same ID value being used twice.
• On failure for any reason, any newly retrieved ID values are simply lost. They are not re-used.
• Temporary ID references are replaced. Any ID value below 10,000 is understood as temporary and will be replaced with the actual ID preserving the reference relationships. If a reference cannot be resolved, then the transaction fails.
• If any add update has an IDENTITY uniqueID, and references the uniqueID of another add update within the same transaction, then processing fails stating that the two operations may not be done simultaneously.

There are potentially more efficient ways of resolving and retrieving non-identity primary key values. This is another point of optimization in a customized Persister implementation, tailored for what is supported by a specific database.

Alternative primary key implementation:

If only one DataManager node will be deployed, then the Persister implementation may allocate new primary keys using in-memory counters. The counters are initialized at startup from the current maximum value used in the tables. This is both fast and efficient, however it is clearly unsafe to use this approach for deployments with more than one DataManager node.

One way to adapt this approach to multiple DataManager nodes is to have each DataManager reserve a chunk of IDs for use. By reserving the chunk in a transaction, and keeping track of these allocations in the database, each DataManager node can allocate IDs from its in-memory pool without risk of collision.

• Pros:
  1. For non-IDENTITY tables, you need to reserve an ID when inserting. Doing this in chunks is more efficient.
  2. Standardized insert code. You don't need different insert statement constructions depending on the table having an IDENTITY primary key.
• Cons:
  1. Even though the cost of allocating these chunks is amortized over the transactions, this is not as fast as inserting into an IDENTITY-keyed row. For large numbers of inserts (such as high volume transactions), chunking incurs extra overhead.
  2. An IDENTITY column is guaranteed to be steadily increasing, if you chunk IDs, this ordering is lost.
  3. Gaps in IDs can make analysis more difficult.