When and Why XPO Extends the Database Schema
- 5 minutes to read
eXpress Persistent Objects for .NET represent an intermediate layer between the application’s code and the database. It provides an interaction between them in the form of “insert-select-update-delete” queries to the database, additional functionalities such as optimistic concurrency, inheritance mapping and deferred object deletion, and support for simple many-to-many relationships introduced by XPO. In order to support functionalities that are not specifically associated with database operations, the database structure has to be extended by adding auxiliary database structure elements. The table below lists the XPO features and structure elements that they require.
| XPO feature | Auxiliary database structure elements |
|---|---|
| Simple many-to-many relation support | An intermediate table to store links between objects |
| Inheritance mapping | An XPObjectType table and an ObjectType column for each object table |
| Deferred object deletion | A GCRecord column for each object table |
| Optimistic concurrency | An OptimisticLockField column for each object table |
| Unique Identifiers | An auto-incremented integer Oid column |
Important
The database schema is not updated when the Session.AutoCreateOption is set to None or SchemaAlreadyExists (see AutoCreateOption).
Note
Note that while XPO can create database schema objects, it cannot modify them later. XPO cannot modify properties of tables, columns, indices, or foreign keys created according to persistent class definitions. In particular, XPO does not change the size and underlying type if the settings specified via the SizeAttribute and DbTypeAttribute attributes are changed. Also, XPO does not remove unused columns and tables if a persistent property or class was removed. Class/member renaming is not tracked and is treated as one class/member is deleted and a new one is added. Renaming a persistent class mapped to an existing database table is a serious change that requires adjusting the database structure and most likely data as well, according to the new class name. If you do not have any sensible data in the database yet, then the easiest solution is to drop your database and let XPO recreate it from scratch. Otherwise, you will have to manually modify your database structure or use SQL scripts to modify it according to the new data model structure. If you want to rename a class or a field in code only, and leave the mapped table or column name unchanged, you can use the PersistentAttribute with the PersistentAttribute.MapTo parameter to specify mapping to an existing table or column.
Simple Many-to-Many Relation Support
XPO creates a special intermediate table that contains “one_side_object_key-and-opposite_side_object_key” pairs. This table is linked to both tables by means of foreign keys. This table’s name is composed from the names of the properties that participate in the relationship and their owner tables’ names. You cannot customize the table name or its column names.
Inheritance Mapping and Object Polymorphism
XPO intrinsically supports object polymorphism (inheritance hierarchies with base persistent classes and their descendants). To accomplish this, XPO automatically creates a system table called XPObjectType to enumerate all known persistent object types. In addition, XPO adds a system column called ObjectType to all the tables mapped to base persistent objects used in a hierarchy. The ObjectType column values are initialized with XPObjectType table key values to associate table records with corresponding object types. If you are using the default mapping option (MapInheritanceType.OwnTable) for descendant classes, their tables store only properties that are declared in the classes. These tables have the same Oid key column values as the base table has. When retrieving descendant class data, XPO automatically links Oid key columns by a foreign key relationship between the key column of the base class table and those of the descendant tables.
Deferred Object Deletion
Deferred deletion, like object polymorphism, needs an additional column in a persistent class table. Deferred deletion means that XPO does not physically delete the record in the underlying data store whenever the corresponding persistent object is deleted. Instead, it marks the record as deleted.
If the XPObject or XPCustomObject is used as the base class for persistent objects, deferred deletion is enabled by default. Once you have classes with deferred deletion enabled, the tables that these classes are mapped to are extended with the GCRecord system column.
Whenever such a persistent object is marked for deletion, XPO generates a random integer value for it and writes it to GCRecord.
Note
The GCRecord columns are created and required only for those classes that implement the IXPObject interface (i.e., the persistent object model might simultaneously include objects with immediate and deferred deletion).
Optimistic Concurrency
When creating a persistent object by deriving from the XPBaseObject, XPCustomObject or XPObject class, an OptimisticLockingAttribute is automatically applied to it. This attribute specifies whether or not a session can lock a persistent object’s state (allowing optimistic locking to be enabled). To control object locking for objects that have the object locking option enabled, XPO adds a new system field (OptimisticLockField) to the object table’s structure during the database schema update.
For more information on optimistic concurrency, see Optimistic Concurrency Control.
Unique Identifiers
XPO expects each object’s state - stored in a table - to be uniquely identifiable. That is why each object should have a unique identifier. If your persistent objects are derived from the XPObject, then they already have an identifier. In this instance, XPO automatically adds an auto-incremented integer Oid column to the corresponding persistent object table.
You can also define your own key property using the KeyAttribute (see Adding Persistence to an Existing Hierarchy). The primary key constraint will be created automatically for every key column in each persistent object table.