SQL Databases

Dari supports MySQL, PostgreSQL and Oracle, all of which are implemented using the same underlying table structure and indexing strategy. Dari’s SQL database schema is implemented using multiple tables (see below). The primary table, Record, stores the raw information about the objects that are saved. Objects are serialized to JSON format when they are saved and stored in the data column of the Record table. Several other tables are used to implement field indexes.

Dari creates unique ids for every object by generating a UUID. The id and typeId fields in the Record table store these UUIDs in the most appropriate datatype for the underlying database. For MySQL and Oracle, this is binary(16). On PostgreSQL, the native UUID is used.

Because SQL databases generally provide fast primary key lookups, Brightspot projects typically use an SQL implementation as the primary database. However, SQL database performance is limited for text matching and compound-predicate queries. Therefore, Brightspot projects generally supplement the SQL implementation with the Solr database, which Dari uses for full-text searches.

Creating a SQL Database

Due to the subtle differences between database vendors, MySQL, PostgreSQL and Oracle each have their own DDL that is used to setup a Dari database. They can be found in the following locations:

MySQL: schema-12.sql

PostgreSQL: schema-12.sql

Oracle: 1-dari.sql, 2-schema.sql, 3-grants.sql

Indexing

Dari implements indexing using four primary index tables: RecordString, RecordNumber, RecordUuid, and RecordLocation.

When fields of an object are indexed, the field’s value along with its object id are stored in the appropriate index table.

Tables

Dari creates the following tables in an SQL-based database. Table version is indicated by appended integer (versions listed here might differ from the versions shown in your database).

Record
This table is the primary storage table. All objects are stored in this table as serialized JSON blobs.
RecordLocation3
This table stores spatial indexes. Supported on MySQL and PostgreSQL only.
RecordNumber3
This table stores number and timestamp indexes.
RecordRegion2
This table stores indexes for localized data.
RecordSearch
This table tracks indexed fields and methods.
RecordString4
This table stores string and enumeration indexes.
RecordUpdate
This table tracks when objects were last updated.
RecordUuid3
This table stores relationship indexes.
Symbol
This table stores symbols such as index names. It is referenced by the other index tables.
See also: