Data Definition

Author: Eike Stepper

Net4j DB models database structure as metadata, then delegates physical DDL to the selected adapter. That separation gives an application one schema vocabulary for a standalone desired model, a catalog snapshot, or the managed schema owned by IDBDatabase. The model itself is not the database: adding a field to an ordinary schema changes Java metadata, while creating or evolving a managed schema requires a database operation.

Schema evolution also connects Article 1's managed connections and caches. A schema transaction updates the managed model through the adapter and then coordinates cache invalidation across the database's managed connections. This coordination does not make every vendor's DDL transactional or place all physical DDL under the cache-coordination lock. For the broader architecture, see Architecture and Core Concepts; for row-level SQL, see Data Manipulation.

1  Standalone and Managed Schema Models

DBUtil.createSchema(String) creates an independent, mutable metadata model. It is appropriate for defining an application's desired tables before opening a database. DBUtil.readSchema(IDBAdapter, Connection, String) obtains a catalog snapshot through an adapter. When DBUtil.openDatabase creates a managed database, its implementation reads a schema and locks that model; callers should use IDBDatabase.getSchema() for inspection and use an IDBSchemaTransaction working copy for edits.

A standalone schema can be copied, compared, exported, created, or dropped using the overload appropriate to the application. Those methods call the supplied adapter and may use the supplied connection, data source, or provider. They perform actual DDL only when an operation explicitly asks for creation or dropping. They do not create a coordinated managed database merely because the schema model is passed to them.

	DefineSalesSchema.java
IDBSchema sales = DBUtil.createSchema("SALES"); IDBTable customer = sales.addTable("CUSTOMER"); IDBField customerID = customer.addField("ID", DBType.INTEGER, true); customer.addField("NAME", DBType.VARCHAR, 160, true); customer.addField("EMAIL", DBType.VARCHAR, 254, false); customer.addIndex("PK_CUSTOMER", IDBIndex.Type.PRIMARY_KEY, customerID); customer.addIndex("UQ_CUSTOMER_EMAIL", IDBIndex.Type.UNIQUE, "EMAIL"); IDBTable orders = sales.addTable("ORDERS"); IDBField orderID = orders.addField("ID", DBType.BIGINT, true); IDBField orderCustomerID = orders.addField("CUSTOMER_ID", DBType.INTEGER, true); orders.addField("TOTAL", DBType.DECIMAL, 12, 2, true); orders.addField("STATUS", DBType.VARCHAR, 24, true); orders.addIndex("PK_ORDERS", IDBIndex.Type.PRIMARY_KEY, orderID); orders.addIndex("IX_ORDERS_CUSTOMER", IDBIndex.Type.NON_UNIQUE, orderCustomerID);

2  Tables, Fields, Indexes, and Types

A table owns ordered fields and indexes. A field records a logical DBType, precision, scale, and nullability. An index records its kind (primary key, unique, or non-unique) and ordered fields. The model checks structural relationships such as whether an index field belongs to its table; it does not prove that a target database accepts every type, name, precision, or index form. The adapter supplies type adaptation, identifier rules, maximum lengths, and vendor-specific DDL. SQL builders and DDL generators should use IDBAdapter.quoteIdentifier(String) for one identifier and IDBAdapter.quoteTableName(IDBTable) for a table, so schema-qualified names are quoted component by component. A schema element's toString() is a general representation and does not know which adapter will execute the SQL.

Use descriptive, stable names and model constraints explicitly. For example, CUSTOMER.EMAIL can have a unique index, while ORDERS.CUSTOMER_ID can have a non-unique index for common customer order lookups. A model-level index does not itself create or verify a physical index.

3  Creating, Reading, and Comparing Schemas

IDBSchema.create(IDBAdapter, DataSource) delegates creation to the adapter. The overloads accepting a JDBC connection or IDBConnectionProvider let the caller choose resource ownership. Creation returns the tables created; it is not a reconciliation operation and should not be treated as idempotent. To inspect an existing catalog, read it through the adapter. To determine differences, compare two schema models. Comparison is in-memory and emits a typed IDBSchemaDelta; it executes no SQL.

A delta can be inspected, visited under a policy, or applied to a schema model. Applying it to a model changes that model only. The adapter's updateSchema operation is what maps a delta to database-specific DDL. Review removals, type changes, and constraint changes before applying them to production data.

	InspectCurrentSchema.java
// Inputs: an adapter and an open JDBC connection for the catalog being inspected. IDBSchema actual = DBUtil.readSchema(adapter, connection, "SALES"); IDBSchema desired = DBUtil.createSchema("SALES"); IDBTable customer = desired.addTable("CUSTOMER"); IDBField customerID = customer.addField("ID", DBType.INTEGER, true); customer.addField("NAME", DBType.VARCHAR, 160, true); customer.addIndex("PK_CUSTOMER", IDBIndex.Type.PRIMARY_KEY, customerID); IDBSchemaDelta delta = desired.compare(actual); System.out.println("Tables differing: " + delta.getTableDeltaCount()); for (IDBTableDelta tableDelta : delta.getTableDeltasSortedByName()) {   System.out.println(tableDelta.getChangeKind() + " " + tableDelta.getName()); }

4  Schema Transactions and Working Copies

IDBDatabase.openSchemaTransaction() snapshots the current managed schema into a working copy. Edit the copy, inspect IDBSchemaTransaction.getSchemaDelta(), and commit the transaction to invoke the adapter's update operation. Closing an uncommitted transaction abandons the working copy. A transaction can instead be opened through IDBConnection.openSchemaTransaction(); this associates its schema update with that existing managed connection. In that form, schema transaction commit() commits the connection after the update/completion path. In the database-opened form, Net4j obtains a managed connection for the update and commits it through its connection-execution helper.

Neither form is an ordinary application transaction around arbitrary row updates. Do not assume that JDBC rollback can undo DDL: databases differ in whether DDL is transactional, whether it causes an implicit commit, and which alterations they support. Keep application data migration and schema changes as explicit deployment steps when they need ordering or recovery logic.

The shared base schema already contains CUSTOMER.EMAIL and ORDERS.STATUS. These examples evolve it by adding CUSTOMER.LOYALTY_CODE and ORDERS.PROMOTION_CODE fields.

	AddLoyaltyCodeColumn.java
// Input: an open database whose managed schema includes CUSTOMER. try (IDBSchemaTransaction transaction = database.openSchemaTransaction()) {   IDBTable customer = transaction.getWorkingCopy().getTable("CUSTOMER");   customer.addField("LOYALTY_CODE", DBType.VARCHAR, 32, false);   IDBSchemaDelta delta = transaction.getSchemaDelta();   System.out.println("Proposed schema delta has " + delta.getTableDeltaCount() + " table changes");   transaction.commit(); }

	AddPromotionCodeColumn.java
// Input: an open managed connection; close all its prepared statements before committing this schema change. try (IDBSchemaTransaction transaction = connection.openSchemaTransaction()) {   IDBTable orders = transaction.getWorkingCopy().getTable("ORDERS");   orders.addField("PROMOTION_CODE", DBType.VARCHAR, 32, false);   transaction.commit(); }

5  Ensure Policies and Migration Review

IDBSchemaTransaction.ensureSchema(IDBSchema) compares a desired model with the transaction's working copy, filters the delta under a policy, copies allowed changes, applies them to the working copy, and returns the accepted delta. The default policy allows additions and a schema-level change; removals and ordinary element changes are ignored. This is deliberately conservative. An explicit policy can allow or forbid combinations of delta type and change kind, but allowing a change does not make it safe for existing data or supported by every adapter.

Before commit, inspect the proposed delta and plan any required data backfill. Adding a non-null field to a populated table may fail unless the database or migration process supplies a value for existing rows. A structural delta does not describe data conversion, default-value rollout, or application compatibility across versions. In the base adapter, the delta is applied to managed in-memory metadata before adapter DDL is executed. The transaction completion path then coordinates invalidation of managed connection caches as explained in Coordinated Schema Access and Statement Caches.

6  Limitations and Failure Boundaries

Adapter support determines how table creation, field alterations, indexes, and drops are rendered. The physical database determines accepted syntax, DDL transaction behavior, locking, and preservation of data. Net4j DB's managed schema model is metadata, not a replacement for migration tooling or a database catalog. If an adapter operation fails after some DDL has run, the framework's JDBC rollback attempt cannot establish a portable claim that all physical changes were undone. Confirm the resulting catalog and data state using the target database's supported inspection tools before retrying a deployment.