Package smacs.load.schema

Module Builder Code Schema detection code Here we draw from the experiences of the jdbv package.

See:
Description

Interface Summary

DbSchemaColumn	Dbviz Column operations.
DbSchemaConstraint	base for internal representation of schema constraints.
DbSchemaFieldMapping	used for a DbSchemaForeignKeyField.
DbSchemaForeignKey	model for ForeinKey definitions.
DbSchemaForeignKeyField	part of a DbSchemaForeignKey It knows the local source and foreign target of the field and has a mapping list.
DbSchemaList	schema lookup.
DbSchemaSimpleNode	method access to DbSchema nodes.
DbSchemaTable	DbSchema Table operations.
DbSchemaTableList	DbSchema TableList operations.
DbSchemaTableQName	fully qualified name of a schema table.
DbSchemaTableStore	DbSchema TableList holder.
DbSchemaUniqueFields	wrap it.

Class Summary

DbSchemaColumnNode	part of a DbSchemaTableNode.
DbSchemaFieldMappingNode	used for a DbSchemaForeignKeyField.
DbSchemaForeignKeyFieldNode	part of a DbSchemaForeignKey It knows the local source and foreign target of the field and has a mapping list.
DbSchemaForeignKeyNode	store a ForeignKey definition.
DbSchemaScanSqlScript	convert parsertree to internal representation.
DbSchemaScriptNode	base implementation of DbSchema nodes.
DbSchemaTableListNode	DbSchema TableList operations.
DbSchemaTableNode	internal representation of a database table.
DbSchemaTableQNameNode	fully qualified name of a schema table.
DbSchemaUniqueFieldsNode	wrap it.

Exception Summary

ImportException

ParserException for the import stage.

Package smacs.load.schema Description

Module Builder Code

Schema detection code

Here we draw from the experiences of the jdbv package. For the gui code we had already written a converter that scans the sql tree parsed from a ddl sql file. Here we adapt the code to get our own schema description along with the foreign key constraints and later possibly more.

For licensing reasons we can not simply overtake the code from jdbv - instead we have to build our own object tree to be filled from the sql tree. The actual importer (ScriptToDbviz) was my code anyway, it's about the helper Objects Schema/ Table/ Column. In our implementation (as always) the schema description is held in a tree fashion that can be copied/ selected/ cutout/ walked/ transformed with xml operators.

Since java is incapable of managing renamed types we need to choose a prefix for each declaration type. For obvious reasons we use the term "Dbviz" - other candidates have been "Ddl" (for database description language) and "Dbdef" (database definitions) but we can also use a backronym of "Dbviz" (database view information seriez) which contrasts the original meaning (database visualisation).

after it's done:

Folding the importer to smacs, we do now strictly follow the lines of matching schema transformations as expressed earlier in sql scripts. That includes to have multiple schema in a single database as it can be expressed in postgres/oracle and other of those projects targetting `heavy weight` amounts of data.

In such a project it turns out that there is a difference between the `database schema` for the whole database, and a `table schema` for a group of tables. It has the essential effect that two tables may have the same name as long as they belong into a different `table schema`. See also

http://postgresql.com/docs/7.4/static/ddl-schemas.html

There are two things out there to be both backward compatible and to make writing sql easier - first is the "public" table schema that is implicitly assumed for any table name if it is not prefixed with a table schema name. In our implementation we do make the relation of table==null .equiv. table.equals("public").

Secondly there is the search_path thing for looking up tables without a schema attribution - now it is not anymore the case that the "public" schema is found first but the first table name present in a table schema listed in the search_path. The "public" schema is attached to the end of the search_path implicitly, so if there is no other table of that name then the public one will be found always at last.

After all, lookup of tables (without table schema attribute) should be bound to a search_path while the creation of tables (without a schema attribute) goes to "public". In the implementation, we build the schema hierarchy as consisting of schema-&;table-&;column where schema is the database schema but each table is given an (xml) attribute "schema" when it is confined to be non-public. So really we have schema-&;table["schema"]-&;column.

In the end, the schema mapping process might needs us to remap tables from one table schema to the other and that might include to remap the database "public" schema to something different in the temporary database. Essentially we treat the schema attributes as a volatile thing that can change in between trying to match the processing pipeline. (And in the same event we keep close to dbviz for visualising the tables in a gui).

about the module implementation

A SMACS module is essentially an sql script containing the SelIns transformations plus one to many scripts containing the schema definition that the SelIns should chew on. From a module it is the task of the schema mapping application compiler to system to create a program text that can do the actual schema mapping under the constraints of the given table schemas.

That sounds about simple but the actual logic is a little harder since schematic heterogenity might need to build up longer switch series in the mapping and some transformations require the mapping into a call to user-defined function or a selection operator on an internal table. Not to speak of building up some internal tables on the fly, and to destroy them when the work is done.