Projects:DBSourceManager/Architecture
| Languages: |
English | Italiano | Translate this article... |
Contents |
System overview
DBSourceManager is a java library that helps with database tasks related to development. Its most important feature is database independence. You will be able to focus in the development of database features and let DBSourceManager deal with the database implementations details.
DBSourceManager is based on DDLUtils and the database model used by DBSourceManager is an extension of the model used by DDLUtils. DDLUtils supports a large list of database engines but all the extensions created in DBSourceManager only work for Oracle and PostgreSQL.
Database model
DBSourceManager uses a common language based on XML to describe a database model. This language can describe all database structures needed. Internally DBSourceManager uses the Database java class to define a database model that is not related to any database engine.
The Database class
The Database class is defined in the package org.apache.ddlutils.model and in this package there are also all the classes used to describe a database model.
A Database instance contains a list of instances of the following classes: Table, Sequence, View, Function and Trigger.
A Table instance contains a list of instances of the following classes: Column, ForeignKey, Index, Unique and Check.
All the described classes have all the operations that allow to create from scratch, manipulate, and inspect the structure of a Database instance. The Database class has methods to add, remove, get and find any of its objects contained like Tables, Sequences etc.. It has methods to merge two Databases and more utility methods. Tables, Columns, Sequences and all the rest of the model classes have methods to access all its properties and objects.
The most important method of the Database class is the method initialize(). This method is called after a new Database instance has been created and all the model objects have been added. This method checks the consistency of the Database object, if the Database is consistent it just returns and if not it throws a ModelException. Some of the checks the initialize() method does are that Tables, Columns, Sequences must have a name, and there are not duplicated names, the Columns of an Index must be Columns that exist in the table, Triggers must reference an existing Table and the code must not be empty, etc.
If you create a Database instance from scratch and after you finish to add all the Tables, Sequences, Views etc. you must also call the initialize method to ensure the consistency of the Database created.
Read and write a database model
As described in the previous section a new Database instance can be created from scratch using java code, but the most common way to get a new Database instance is to use the utilities provided by DBSourceManager that allow to create a new Database instance from an XML file, a collection of XML files or an existing database connection.
To create a database from an XML file you use the class DatabaseIO that has a set of read() methods that return a Database object from an XML file, all these read() methods call the method initialize()” before return so if you need to read a collection of XML files as a single Database object you must call the method readplain() for each XML file, merge all Databases resulting and call the method initialize() of the resulting Database. In this class DatabaseIO there is a set of write() methods that store a Database instance in an XML file or serialize the Database to an stream in XML format. An utility method writeToDir() is used to store a Database instance to a collection of XML files in a directory, organizing the XML files in subdirectories for each table, sequence, view, function and trigger.
To create a Database instance from a database connection you must create a Platform object from a Datasource and call the method loadModelFromDatabase()” this method reads the system tables of the database engine and creates a Database instance from the Datasource this method only works for Oracle and PostgreSQL for the rest of database engines you can use the set of methods readModelFromDatabase(). These methods use the metadata provided by the JDBC connection and reads only the tables of the database and not sequences, views, functions or triggers.
Database model management
DBSourceManager offers utilities to construct and execute the SQL script that creates the database defined by a Database instance and that compares two Database instances, creates the SQL scripts that updates the first database to the second database, and executes it.
The interface in charge of these functionality is Platform, there is a base implementation of this interface PlatformImplBase and subclasses for each database engine supported.
To create a concrete instance of an object that implements Platform exist the factory PlatformFactory that has the set of methods createNewPlatformInstance that creates a new instance that implements Platform based on a DataSource, a JDBC URL string or a database engine name.
Create a database model
To create a database model you need to call one of the methods of Platform called createTables(). These methods take a Database instance, constructs the database creation script based on the parameters of the createTables() method and execute it.
If you need to separate this action you can use the methods getCreateTablesSQL() that return the SQL script that constructs the database and the method evaluateBatch() that executes an SQL script.
Update a database model
To update a database model you need to call one of the methods of Platform called alterTables(). These methods take two databases, construct the database update script and execute it.
If you need to separate this action you can use the methods getAlterTablesSQL() that return the SQL script that updates the database and the method evaluateBath() that executes an SQL script.
DBsourceManager to construct the SQL script that updates two databases uses the class ModelComparator this class has the method compare() that takes a source Database object, a target Database object and returns a list of changes. Each change is an object that implements the interface ModelChange. An example of classes that implements ModelChange are: AddCheckChange, ColumnDataTypeChange, RemoveColumnChange, RemoveViewChange, AddTriggerChange, etc. This list of changes is processed by the concrete instance of Platform to create the SQL scripts that updates the database.
Execute an SQL script
To execute an SQL script to construct a database or to update a database there is the method evaluateBatch(). This method executes an SQL script to a database connection. you can indicate if the an error occurs if the script must continue with the rest of SQL statements of the script of if the script must stop.
There is also the possibility to retry the execution of SQL statements if there is a comment before the SQL statement that indicates that: SCRIPT OPTIONS (FORCE = TRUE). All the SQL statements with this comment that fails are executed again at the end of the execution of the script while there are statements that succeed.
Data management
DBSourceManager provides utilities to import and export data. The java class in charge to export and import data is DatabaseDataIO.
To indicate the set of data to import or to export can be used a filter a filter is an instance of DatabaseFilter an instance of DatabaseFilter has two methods: getTableNames() that returns a list of table names to import and export and getTableFilter() that returns the SQL filter used to import and export data.
Export data
To export data call any of the methods writeDataToXML(). These methods create one XML file for each table to export according to the filter defined previously for DatabaseDataIO. By default the filter used contains all data of all tables.
Import data
To import data call any of the methods writeDataToDatabase(). These methods loads one XML file or a list of XML files and imports it to the database.
Before to proceed to the data import, all triggers and foreign references are disabled and enabled again after the import has finished. The filter used to import data is used also to assure that all the data that does not satisfy the filter and that is not in the data to import, is deleted. By default the filter used does not contain any table so, no data is deleted.
Subsystems
Database model creation
In order to support several database engines there exists concrete classes that implements the details of each database engine. The main interface is Platform and there is a base implementation PlatformImplBase that contains all the implementations details that are common to all database engines. Platform is the interface that performs all the database related task and is the facade interface for all database operations.
To help the Platform interface with his job, the base implementation PlatformImplBase contains one instance of PlatformInfo, this class stores information about what the database engine supports and what does not support: database features, structures, types, etc. Every PlatformImplBase subclass is responsible to set the properties of the PlatformInfo instance. PlatformImplInfo subclasses also contains an instance of SQLBuilder This class is responsible of creating all the SQL sentences and DDL sentences that creates, updates and modifies a database, and an instance of ModelLoader. This class is responsible to read a database model reading the database engine system tables. More information about ModelLoader is in the next section.
PL/SQL and SQL translator
Some classes of the database model contains PL/SQL code and SQL code. Views contains SQL code and functions and triggers contains PL/SQL code. The syntax of this code have differences between database engines and to achieve database independence the Oracle syntax has been chosen as the generic syntax for all database engines supported by DBSourceManger.
In order to use the Oracle syntax to create for example PostgreSQL procedures, DBSourceManager translates PL/SQL code and SQL code from Oracle syntax to PostgreSQL syntax. And to implement this translation a collection of utility classes based on regular expressions has been created to help in this task.
The main interface for translations is Translation. This interface has an unique method exec() that takes an String and returns another String that contains the code translated. For Oracle the Translation class that performs the PL/SQL translation and SQL translation is a simple class that just returns the parameter that the exec() method takes. For PostgreSQL a most complex class has been created.
The utility classes that implements the interface Translation are:
ReplacePatTranslation. This class performs a translation based on a regular syntax pattern and a replacement string.
ReplaceStrTranslation. This class subclass ReplacePatTranslation and performs a translation based on a search string and a replacement string.
RemoveTranslation. This class subclass ReplaceStrTranslation and performs a translation removing all appearances of a search string.
NullTranslation. A convenience translation that just returns the string to translate.
CombinedTranslation. A translation that contains a list of translations and executes all that translations in order.
ByLineTranslation. A translation based on another translation that is executed line by line.
Database model loader
As described as a method to create a Database instance based on a database connection a Platform instance implements the method loadModelFromDatabase(). The class in charge of performing this operation is an instance of the interface ModelLoader. ModelLoader has an abstract implementation ModelLoaderBase that implements the base algorithm and methods that loads a Database using the system tables of a database engine. And for each database engine exists a subclass that implements the concrete details of each database.
