DB framework

The DB framework works a lot like most object-relational mapping technologies but works at a slightly higher level using code-processing. Classes marked with the @DBTypeSettings annotation define a data model with fields, or bean-style properties with getX and setX methods. All of these are converted to bindable properties with getX/setX methods that have additional code in both the getX and setX method for persistence.

It's also possible to start with a SQL DDL definition in a file with the .scsql suffix. In this case, the 'create table' statement is converted into the StrataCode class, and columns used for properties.

Either way, the data model can be customized with layers, mixing both StrataCode and SQL definitions to form the complete data model.

Getting started

To get started with Postgresql, extend the jdbc.pgsql, and jdbc.schemaManager layer for automatic schema management from a new layer. Mark any types in this layer that need a table with @DBTypeSettings.

By default, the type name is converted to a SQL name from CamelCase to underscorename. For example MyClass is converted to myclass.

Any properties in MyClass that are not transient, read-only, or have a computed value are added columns to the my_class table.

Use the @DBPropertySettings annotation on properties to change the column name, add an index, assign a database default value, and more.

Associations are automatically detected when there is a property whose type or component type has a table of its own. For a single-valued association, add a property that refers to a class with @DBTypeSettings. Set @DBPropertySettings(onDemand=false) for it to be loaded in the same query with it's reference.

For multi-valued associations, define a List property where the type of the elements is another class with @DBTypeSettings.

Associations can use @DBPropertySettings(reverseProperty="reversePropName") to mark one side of a bi-directional relationship. Both sides of the relationship are marked as reversible and both properties will generate data binding events so that changes made in one direction can be automatically reflected in the other immediately.

JSON properties

If a persistent property is a type that's not mapped with @DBTypeSettings using it's own table, it will be serialized as a JSON value object. These types should have a zero arg constructor. All non-transient bean properties are serialized into a JSON column for that property.

If the property is a List of persistent types, by default it becomes a multi-table. But when the list is likely to be small, it's more efficient to set columnType="jsonb" so that it stores a list of references to the other table.

A List of strings or other primitive types is stored as a JSON array column.

Byte array

For a Java property of type byte[] DB uses the postgresql bytea column type.

Defining types with scsql

It's also possible to define a class with sql create table statements. First extend the layer db.scsql from the project layer to support for source files in the scsql format.

Create a file MyClass.scsql in the project layer and add to it a 'create table' statement to create my_class. The columns in that table are automatically added as properties in MyClass. It should have an id column called 'id'.

If a column has a 'references' constraint, that's used to define an association property to a table defined in another scsql file or an sc file using @DBTypeSettings.

If the table name matches the pattern:

it is either an auxiliary or multi table. An auxiliary table has a primary key that matches the primary table and adds properties to the MyClass just like it.

A multi table references the id of the primary table but does not have a unique constraint (it's not a primary key). The other columns in the multi table define the component type of the List property created.

If those columns reference another table, the property is defined as an association.

SQL Functions created in the scsql of the name myclassmyfunctionname are turned into static methods on MyClass with the name myFunctionName.

Indexes and sequences are parsed and added as the schemaSQL associated with that type. Any SQL construct that doesn't correspond with a class in the system can be passed through in this way.

When properties are fetched - onDemand, selectGroup

The @DBPropertySettings(onDemand=true/false) annotation can be used for both associations and regular properties. For associations, the default is true - load the associated property the first time it's accessed. For onDemand=false, a single-valued association is loaded using a join in the same query as the reference. The multi-valued property is loaded in separate query right after the main one.

For non-association properties, set onDemand=true to lazily load this property the first time it's accessed. Internally, it's put into a selecftGroup using the property name.

Use @DBPropertySettings(selectGroup="groupName") for fine-grained control over the grouping of when properties are loaded. Properties in the same select group with the same cardinality are loaded in the same query. By default, the selectGroup is the name of the table the property's column is in.

Modifying DB types with layers

A layer can modify a previous type, even if that type was defined with an scsql file. In this case, core aspects of a system can be custom coded in DDL and extensions can be made from application code. For example, adding properties a persistent type in a new layer adds a new column to the schema. If the table for the column was defined in scsql, the new column modifies the original sql to add the column. Perhaps more commonly, new properties are added to new tables but because DB uses a full parser/generator for the SQL code, it's very flexible in terms of how the SQL can be used in the application.

Use the @SchemaSQL annotation to attach SQL commands to be included with this type. Use the value attribute for inline SQL, or use the file attribute to specify the file name of a file with the .ddl suffix. Use this to add SQL commands like indexes or stored procedures. The stored procedures added in this way will generate a Java method with a matching type signature to call it.

Data sources

Layer definition files define data sources by defining a layer component that extends DBDataSource. It has a provider property set to "postgresql", to define the variant of SQL to use. It specifies the jndiName for defining the JDBC DataSource, the dbName, userName, password, serverName, and port. All of these can be overridden in subsequent layers.

The system stores a defaultDataSource used by any @DBTypeSettings annotations that do not refer to a specific one.

After the main code-processing phase, all @DBTypeSettings types are used to generate schema files for each dataSource. The SQL commands are sorted using the 'references' constraints to produce DDL that both creates the schema from scratch and drops the schema. The system also tracks the currently deployed version of the schema for each dataSource, for each buildLayer. When this deployed schema exists, it also generates the appropriate 'alter' script to upgrade the currently deployed schema to the new one. For example, if a new column has been added, an alter table statement that adds the column is generated and placed in this file.

Schema manager

When the jdbc.schemaManager layer is included, it takes schema management one step further. After the code processing step, if the schema has changed since the last run the schemaManager wizard is enabled. The application is run as normal (so that it can connect to the database and start core-services required for the wizard) but an API call can be used for the application or test suite to wait until the schema is ready (DBTypeDescriptor.waitForSchemaReady()).

The wizard at this point displays the set of schema changes from the last build - including new types, or changes to existing types. If a table, index, column, constraint, etc. has changed it shows the detailed changes. If those changes will lose data or add constraints, a warning is displayed.

The wizard lets you see the new schema, the old deployed schema, as well as the alter script it can run if you choose the update command.

Once the schema wizard quits, it marks the schema as ready and the application can run as normal.

For scripting purposes, use the -schema:update or -schema:accept options to update or accept the schema without running the wizard.

The schema manager keeps track of the schema history in it's own set of tables stored next to the application tables - dbschema* Inside, it stores the new DDL, the alter script if any that was applied, plus other metadata.

The schema manager also uses the database's metadata to validate the existence of tables, and columns expected as an extra check.

It's important to review the alter scripts as not all changes made to the schema are optimal. It can add or remove unique, and not null constraints or alter a column type if the data type cast is allowed by Postgresql. For more complex column changes, it's alter script will drop and re-add the column which may not be what you want.

Define a simple persistent class

@DBTypeSettings
class Product {
   String name;
}

Create an instance:

Product prod = new Product();
prod.name = "foobar";
prod.dbInsert(false);
System.out.println("My db assigned id is: " + prod.id);

Update:

prod.name = "bazinga";
prod.dbUpdate();

Delete:

Product prod = Product.dbTypeDesc.findById(37);
prod.dbDelete(false);

Enumerated types

Java enums can be converted to SQL columns in one of two ways. If the enum itself has the @DBTypeSettings, it's converted to a Postgres enumerated type in the database. The column for those properties then use that enum type as the column type.

If the enum is not marked with @DBTypeSettings, the enum property is converted to an integer column using the enum's "ordinal" value (a zero based integer using the order of the enum in the file).

Transactions

By default database update operations are performed immediately using autoCommit. To perform them in a transaction, first call DBTransaction.getOrCreate() and set autoCommit = false before the operations begins.

It's also possible to queue up inserts and updates by setting flags in DBTypeDescriptor.

Threadsafe apis

One of the goals of the DB layer is to make it easier to write efficient persistence code. Caching can be enabled when it's needed without having to rearchitect without losing consistency. This same model paves the way for efficient, low-latency real time applications, better stats and monitoring and more.

DB uses the "highlander principle" (from the old TV show) in that "there can be only one" instance for a given entity in a given process at a time. A central cache is used for any lookups, so subsequent queries for the same entity will update that entity rather than create a new copy, leaving the old one out of date. Changes made to that instance in a transaction are kept in a separate thread-local store visible only to that transaction until they are committed. Changes that come from these commits or that repopulate from the database become visible to other threads right away.

Although the API itself is threadsafe, it won't stop two threads from getting inconsistent views of data across a commit. Instead, it works more like the 'read committed' isolation level where two reads in a transaction might see different values if another thread happens at the same time.

Locking can be implemented at the 'scope' level to eliminate common race conditions at a higher level, for example to prevent two requests from the same session from running at the same time.

To explicitly uncache an instance, call the DynUtil.dispose method (that also removes data binding listeners and removes the instance from any views in the management UI). For objects stored in the ScopeContext, this happens automatically.

TODO: note - we currently don't have a time-expiration or other means of removing items from the cache. If they are stored in a component, they'll be cleaned up when the component is cleaned up (e.g. the session is expired). We could use a weak cache to store the instances but not sure how to make the getById lookup fast in that case.

Right now frameworks should provide safe management of the caches by ensuring components are owned by a scope context and the context expires at the right time. We could add other mechanisms like registering instances during the transaction and removing them from the cache after they are used and the transaction or thread completes. Or add other lifecycles like session that are periodically flushed if not used in a given time period (e.g. "product catalog scope" that defines a specific lifecycle for cached products and categories).

Metadata apis

The information that maps classes to tables is stored in the DB metadata classes: DBTypeDescriptor, TableDescriptor, and DBPropertyDescriptor. During the code processing phase, metadata is produced from the DB annotations, and scsql files encountered. The metadata is initialized in generated code in the persistent classes and so it's visible and available as part of the runtime api as well.

DBTypeDescriptor also provides access to an API useful for lower-level access to the DB framework - findById, findAll, findBy(list of property names), waitForSchemaReady() and more.

SQL manipulation APIs

The DB framework is not a lot of code, but the code is flexible. SQL commands are parsed and generate a SQLFileModel object that defines a list of SQLCommand, CreateTable, DropTable, etc. This API works both ways - as a way to extra info from the SQL model as well as to make incremental changes, or generate a new SQL model from scratch. There are apis to generate a new SQLFileModel that alters one set of commands to the other.