FirstSQL/J is an Object-Relational Database Management System (ORDBMS) written fully in Java. It provides intermediate level support to the SQL-92 standard and full Java object capabilities in the physical database using Java classes. SQL is the data access language and Java Database Connectivity (JDBC) is used as the client interface. In addition to the primitive data types, columns of the database can map to Java Objects. Moreover, stored procedures written in Java can be called by JDBC. Application developers can combine the their database and application code into a single jar file and execute on any device, embedded system, or server computing platform with a Java Virtual Machine.
The development of FirstSQL/J started in 2001. The initial goal of the DBMS is to have it written fully in Java so that it is platform independent and support other Java client applications. The first publicly available version, FirstSQL/J v1.0, was released in February 2002. The initial release came in two versions: the Enterprise version to be used by client/server applications and the professional version to be used by embedded desktop applications. FirstSQL/J has since been undergoing continuous development and support. In addition to the original Enterprise and Professional versions, versions to be used for embedded mobile systems and Android devices were released in October 2005 and October 2015, respectively.
In SQL-92, there is not a BEGIN statement to explicitly start a new transaction. A new transaction begins with any SQL statement if there is not an active transaction already. During each transaction, locks are acquired at the row level. The locks are held until the end of a transaction, which can happen if : * The client commits the transaction through the action of a COMMIT statement * The client aborts the transaction through the action of a ROLLBACK statement * The client requesting the transaction terminates abnormally * The DBMS terminates the transaction through deadlock detection and resolution * The DBMS terminates abnormally
Columns can map to Java Objects. Java classes, methods, and inheritance are fully supported in the physical database. User can define Java objects and treat these objects as data types. They can be managed using JDBC client APIs and accessed using SQL. Methods can be part of SQL statements and are executed first and treated as part of the expression tree of the query.
All four standard isolation levels supported by JDBC are supported by FirstSQL/J. The default transaction isolation level is Repeatable Read. The different isolation levels are implemented using different policies on how row-level locks are acquired.
A rolling log is maintained by the primary server to record the status of transactions in the form of SQL commands issued by the client. The number of concurrent transactions is assumed to be small. Replication servers are read-only servers which are copies of the primary server. When a transaction is committed, the primary server sends all changes to replication servers. A special type of replication server is called Standby Server. When a Standby Server detects that a primary server has failed, it switches to read-write mode to continue processing transactions.
All data access to the physical database is through SQL. However, the FirstSQL/J client interface support both SQL and Java (through JDBC). The client application may use Java code to make SQL queries or call Stored Procedure for retrieving data. The tool Ijdbc, a utility developed by Oracle and packaged in every FirstSQL/J distribution, allows SQL statements to be executed by through the command-line.
Main Memory mode and Disk mode are supported. The two modes cannot be used simultaneously. The physical database either reside in main memory or disk. Disk mode persists data at whenever a transaction is committed whereas Main Memory mode persists data only after a client terminates a session, which can involved many transactions.
Stored Procedures need to be written in Java. They can be called directly using SQL commands or by other Java code in the client application. DDL commands stores Java class into the catalog. Once the class is stored in the catalog, the class methods can be called as Stored Procedures.
The system architecture depends on the configuration being used. In FirstSQL/J Professional Edition, the client application and the DBMS run on the same Java Virtual Machine and thereby the same physical machine. All data is stored on the same machine as the DBMS. In FirstSQL/J Enterprise Edition, the client application and the DBMS engine run on different Java Virtual Machines and physical machines. The JVM running the DBMS engine is responsible for both execution and storage. In addition to a single primary server, there can be many replication servers. Replication servers are standalone read-only servers that mirror the primary server.
Database elements are stored in a tree structure. When constructing views, the tree structure is traversed to produce a set of matching tuples. This set of tuples is never materialized and needs to be recreated every time the view is needed. The supported clauses include GROUP, UNION, and WITH CHECK OPTION as specified in the SQL92 standard. Views can be created by interactive SQL tools called Ijdbc and IjdbcWin, provided as part of the database.