Celesta User Manual

Demo project illustrating Celesta capabilities for Spring Boot is available at: https://github.com/inponomarev/celesta-demo/.

To create a Spring Boot project use the following Maven dependencies:

ru.curs:celesta-maven-plugin is used to generate the code of data access classes. Specify the path to the score folder in its settings:

Add database definition scripts in CelestaSQL language to the src/main/celestasql folder. For example,

mvn verify command generates OrderHeaderCursor.java and OrderLineCursor.java files with cursor classes in the target/generated-sources/celesta/…​ folder.

Those classes can be used to create services (see the more comprehensive demo):

Any method available in Spring Boot can be used to configure your project’s settings, including the application.yml file.

Celesta utilizes its own built-in JDBC connection pool if no other option is provided. If you want to configure a specific connection pool, or if you want to utilize the DataSource provided by the Spring framework, you can do so.

In standalone application you can use Celesta.createInstance method providing a DataSource in its parameter.

spring-boot-starter-celesta will utilize a DataSorce when it is provided by Spring.

For example, if you add the following dependency:

and the following configuration parameters to application.yml

Celesta will run with Hikari Connection Pool provided by Spring. Please note the following:

Celesta initialization goes in several stages, which is illustrated by the log output:

At this stage the application server connects to the database and checks the existence of the celesta.grains system table. If the table does not exist, it is created automatically (via CREATE command), but only in the following cases: 1) the database is completely empty; 2) force.dbinitialize parameter is set (to prevent "corruption" of existing non-empty databases if Celesta connects to them by mistake). If an error occurs during celesta.grains table existence check / creation, a fatal error is generated and the system does not launch.

Celesta features several methods to disable automatic migration:

Celesta system adds to the database not only user-defined tables and schemas, but also tables in its own celesta system schema, the structure of which is presented in a diagram below.

The structure (including the fields) of the Celesta system grain tables is unchangeable and access to data in them is provided by built-in ru.curs.celesta.syscursors package classes. At the same time, changing data in these tables is part of standard system configuration.

System tables are used to:

Purpose of tables:

This table is the vital Celesta system table as its contents manage the database structure synchronization with the score at the system’s launch. It contains the following fields:

As the table structure shows, table access rights are granted to roles and are not meant to be directly granted to users.

The roleid field contains the role identifier and grainid and tablename fields contain references to the tables. Flags are set in bit fields "r", "i", "m" and "d" if the rights are needed for reading, insertion, modification or deletion respectively.

Special system role names are available: reader and editor, as well as <grain ID>.reader and <grain ID>.editor.

The reader role grants the right to read all tables without any exceptions, the <grain ID>.reader (for example, "foo.reader") grants the right to read all tables in the respective grain.

The editor role grants full rights (to read, insert, modify and delete) to all tables. The <grain ID>.editor role (for example, "foo.editor") grants full rights to all tables in the respective grain.

Not only the access rights granting system is involved in any data modification via Celesta cursors, but also a modification logging system, which logs all changes to the celesta.log tables. In order to prevent the celesta.log table from being littered with potentially huge amounts of unnecessary data, the changes are logged only in the tables explicitly specified in the celesta.logsetup table. There is also an option to separately enable logging of entry insertions, modifications and deletions.

In order to enable logging data changes made via Celesta system, it is necessary to enter the relevant settings in the celesta.logsetup table. The "grainid" and "tablename" fields should contain a link to the table and flags are set in bit fields "i", "m" and "d" if it is needed to log insertion, modification and deletion respectively.

The celesta.log table consists of the following fields:

Celesta can operate in a profiling mode for troubleshooting, which is enabled by a setProfilemode(true) mode of a Celesta instance.

Information on all procedure calls in a profiling mode is entered in a calllog table consisting of the following fields:

Celesta SQL language is used to write scripts for defining grains. Script in CelestaSQL consists of the following statements

which must be separated with semicolons starting with CREATE GRAIN:

CelestaSQL supports standard single-string and multistring comments, as well as CelestaDoc comments:

Standard comments can be used in any part of the text and CelestaDoc comments can be used only right before the grain, table, field or index definition.

Identifiers are names of grains, tables, fields, indices, limitations and views. In Celesta, their syntax is subject to a number of stringent limitations.

Every grain definition script must start with CREATE SCHEMA. The following syntax is used (words GRAIN and SCHEMA are synonyms):

WITH NO AUTOUPDATE clause excludes the whole schema from the database autoupdate process, just like the similar table option. It can be used when the schema structure is managed not by Celesta, but by some external system.

Version indication is mandatory to rule out unintentional database automatic downgrade when an older grain version launches with a more recent database version.

Version consists of components separated with commas and may be as follows: 1.23,TITAN3.34. This reads: basic version 1.23, modified for the TITAN project – 3.34. Regular expression to check the component format is ([A-Z_]*)(\\.[0-9]). Every component of the version can be in bicomponent format only and either has no prefix or has one containing only Latin uppercase letters and an underscore. When the system determines a possible autoupgrade, all version tags are compared consecutively.

In this sense the following tags to the "1.23,TITAN3.34" tag are:

Versions are compared as floating point values.

The following syntax is used:

Main limitations and differences between this statement and similar statements in various RDBMS are:

The following syntax is used:

In other words, the CREATE TABLE statement can list the definitions of fields, primary keys and foreign keys in any order in the brackets and options can be listed after them. In practice, first definitions of all fields follow each other, then go composite primary keys (a single-field primary key can be defined in the field itself) and composite foreign keys (if single-field, they can be defined in the fields themselves).

Indices are used to speed up filtering by table field and are created with the following statement:

Names of indices within a grain must be unique. All indices in Celesta allow non-unique column values.

Views serve to grant read-only access to the data collected from one or several tables with a SELECT SQL query. Celesta creates a view object for every view and translates the SQL query in CelestaSQL into the respective SQL dialect.

Views are created using the following syntax statement:

SELECT statements in CelestaSQL can be chained with UNION ALL. As usual, it is required that each SELECT statement in UNION ALL chain returns the same number of columns of the matching types.

The names of the columns returned by UNION ALL expression are assigned from the respective columns of the first SELECT query. Columns' nullability is determined the following way: if any of the queries in UNION ALL chain can return null in a column, the column is identified as nullable.

This query key restrictions and differences from SQL queries in various RDBMS are:

Most functions not supported at the CelestaSQL level can be efficiently emulated with data access classes API.

Reference to a table (table_ref) has the following syntax:

It is not necessary to indicate the grain ID if the table is in the same grain as the current view.

View field defining term has the following syntax:

Common arithmetical operations with normal precedence are available for INT and REAL-typed expressions: unary minus has the highest precedence, next goes multiplication and division ("*", "/") and still next goes adding and substracting ("+", "-"). For VARCHAR expressions concatenation "||" is available, as well as UPPER and LOWER functions that convert text to upper- and lower-case, respectively. Operations with other types are not allowed.

References to fields may be single-component (if they unambiguously indicate a field of a certain table) or bicomponent, in this case the first component should be the alias of the table in the FROM statement, and if there is no explicit alias – table name.

<$param id> special ID type – a "$" symbol followed by ID – is used for references to function parameters.

At last, the condition logic expression used in JOIN …​ ON and WHERE statements has the following syntax:

It is important to note that the term inside a SUM statement must be a number.

GROUP BY statement is special in CelestaSQL because unlike the common SQL it is necessary to list all not aggregated columns in the selection.

Materialized views serve to grant read-only access to aggregated data collected from a single table and grouped using a GROUP BY expression. For its every materialized view Celesta creates a table in the database which is modified by database triggers on parent table modification.

Materialized views are created with the following syntax statement:

This query key restrictions and differences from SQL queries in various RDBMS are:

This statement determines functions or parametered views. They serve to grant read-only access to the data collected from one or several tables using a SELECT SQL query taking into account the given parameters.

Functions are created with the following statement:

Parameters are declared with the parameter name and type set and are separated with commas as follows:

To refer to a parameter in a function expression, a symbol $ must be put before the parameter name (for example, param1 is put in query expressions as $param1).

This query key restrictions and differences from SQL queries in various RDBMS are:

Otherwise, a parameterized view syntax is similar to common view.

Parameterized view example:

In order to start working with Celesta, a Celesta instance should be created. Normally, there must be a single Celesta object per application, this is why it must be stored as a singleton or managed by Spring framework.

If you are using spring-boot-starter-celesta, the Celesta instance is created automatically and is available as a bean.

If you want to create and instance of Celesta yourself, you should create Properties holding the Celesta settings and then utilize one of the following static methods on Celesta class:

To meet the requirements for granting access rights and action logging, all operations are performed on behalf of a specific user, anonymous operations are impossible. This is why Celesta code is executed in a certain context determined by a (CallContext) class instance.

Call context contain user ID, while links between user IDs and roles determine granting access to tables and allow to log changes made on user’s behalf.

Context calls are created at the controller level, where the ID of the user performing operations is assumed to be known (through a token sent to the query, digital signature or other means).

Context is created with a constructor

If there is no need to consider the user ID or grant rights to access the tables, a SystemCallContext subclass can be used, because its constructor does not require any parameters. In this case, a "system user" context with full access to all tables is created.

Any context created in this way goes through the following life cycle:

Developer does not usually need to manually activate and close contexts since Celesta framework performs it automatically.

Each method in the service layer using Celesta must have an argument of a ru.curs.celesta.CallContext type. Call context is intended to be an argument for cursor constructors, but it also has a number of public methods and properties available, feasible to be used in a service method:

For each table and view declared in CelestaSQL data access classes are generated.

Each instance of a data access class (which are also going to be called "cursors") at each moment holds information about a single database record (row) and every row’s field of the cursor has a corresponding object field. It is possible to move cursor across records taking into account filters and ordering. If a cursor is created from a table, it can also be used to insert, modify or delete data. View cursors only allow row navigation methods.

The UML diagram illustrates data access class hierarchy. Hierarchy is based on a BasicDataAccessor class. Each cursor class inherits from the BasicCursor class, Sequence class inherits from the BasicDataAccessor:

Below Cursor class methods are described, but the methods that are inherited from BasicCursor (and can be used when working with views and read-only tables) are marked with a symbol.

In most cases cursor filtering by field value can be implemented using setRange(…​) methods with two or three arguments. This method filters values by "field = value" condition or "field between value1 and value2" condition.

When simple comparison and a "between" condition is not enough, setFilter method allows to apply a more complex condition to values in one of the cursor fields. First setFilter method argument is the field and the second is the filter expression.

Correct filter expression can consist of:

Filter expression cannot be a null or an empty string, call setRange() method without any parameters to reset filter for the field. Spaces between literals and operators are ignored. The filter expression is translated directly without any optimization into a condition for a WHERE expression in SQL language.

The setFilter method allows to filter records with their fields taking values from a predefined set. For example,

filters records with "city code" equal to MSK or LON. A call

filters records with city code starting with a Latin "M".

But sometimes setFilter functionality is not enough: what if records have to be filtered by cities located in a certain region or country? Here is one of the ways to solve this problem: filter city catalogue by city.setRange(city.COLUMNS.country_id(), "RUS"), then upload the full list of these cities IDs from the database to the memory, group them in a single filter string separated by pipes and apply this filter to another cursor. Surely this is not an optimal solution if there are too many records matching the filter: it will result in excessive data transmission over the network and a overly long SQL query to the corresponding table.

In this case, a setIn method is used, allowing to set a filter with an auxiliary query for the chosen field set. It is available for Cursor and ViewCursor subclasses.

The general schema of the setIn method operation is the following:

Coupling is done with a FieldsLookup class, returned as a target cursor setIn method result. The setIn method receives the auxiliary cursor used to look for data intersection as a single argument. Preparing the target cursor, gathering pairs of columns and further setting the filter is done this way:

For this example, to access the a cursor records PostgreSQL will generate the following SQL expression:

Any number of auxiliary cursors can be attached to the target using the FieldsLookup class and method. Auxiliary cursors will not interfere with each other. An example for setting several auxiliary cursors is given below:

For this example, to access the a cursor records in PostgreSQL Celesta will generate the following SQL expression:

This filter has a set of restrictions, which, when violated, cause throwing the exceptions during the execution of FieldsLookup.add and BasicCursor.setIn methods:

The Sequence class allows to work with sequences. Unlike other access classes, during its code generation, a Sequence prefix is used instead of Cursor. Sequence class has a single nextValue method allowing to get the next sequence value as a long integer.

Below is an example of using a Sequence access class:

Working with data using data access classes not only allows to write universal RDBMS-agnostic code, but also to get around the issues of centralized permissions management and data changes logging.

Calling some of the methods requires users to have corresponding permissions for tables, set in system tables celesta.userroles and celesta.permissions, otherwise a PermissionDeniedException exception occurs with a message "There is no …​ permission for user …​ on object …​".

If tables change logging is set up in the celesta.logsetup table, calling certain methods will result in creating entries in the celesta.log table.

Other methods do not require table access rights and calling them is not logged. I.e. it is possible to define a cursor, set its filters and even count the number of entries meeting its conditions with a count() method without even a permission to read the table.

It is up to the developer to decide whether to use access permission management and logging mechanism or not. If these features are not used, the potential performance overhead is minimized.

BLOB fields allow to store huge amounts of information in table cells, even entire files with documents. Work with these fields using cursors is different from work with other field types.

BLOB class methods:

Examples of code for a BLOB field:

Often fields in relational databases can be assigned only a few values from a limited list. For example, the "state" field may have only "new", "processing", "finished" or "error" values and nothing else.

It is not feasible to create a separate reference tables or foreign keys since the list contains only a few values. Using integer values with specific "meaning" often helps to optimize table size and speed up processing. Like this:

Celesta supports simplified work with such fields.

To declare that the field is only allowed to have values from a certain list, an option property is set in an integer or text CelestaDoc field. For example:

When compiling data access class Celesta reads the option property and generates auxiliary code to facilitate using values from the list.

For example, two auxiliary classes are created automatically in the fooCursor class for our foo table:

Note: possible values for integer fields are numbered automatically and for text fields text values literally match their names. The solution developer may refer to possible values in the following manner:

When writing universal data processing procedures (like export/import procedures) the issue of dynamic access to data arises in case the table/view/sequence name is known not at the stage of code writing but during execution.

To create a data access class instance when only a metadata object is known, it is possible to use a static factory create method of the corresponding basic data access class, for example:

and so on. Cursor and ViewCursor classes also have an overloaded version of the create method allowing to create data access object instances with limited set of columns.

Dynamic access to table or view cursor fields in the BasicCursor class is performed using the following methods:

Let us consider the following scenario to illustrate the lost update notion.

Assume the application has a table listing clients and users allowed to edit that table using form-cards. Assume the events occur as follows:

This means the work of user B is lost!

This is the most common example, but in practice a developer might face lost updates in much more trivial cases not involving multiuser operation. Assume that a single table is modified by two cursors:

Assume that at a certain moment both cursors receive data of the same record as follows:

Assume the FooTable table entry has an "id = 1" and consists only of three fields:

And now both cursors perform entry modification:

As we see, bad code may cause lost updates even during single-user operation.

Generally, there are two approaches to prevent lost updates:

Naturally, every method has its drawbacks.

The main optimistic lock drawback is that the user cannot write the result of their work to the database if somebody else managed to update the entry before they did. Still, it rarely happens in practice and only the most sluggish users who take too long to edit entries suffer from it.

Main pessimistic lock drawback is that the user is expected to explicitly commit or cancel editing to unlock the entry. But in practice when the editing goes on for excessive periods of time it is impossible to guess if the user is going to properly end editing or some external intervention by administrators is needed to explicitly unlock the entry, otherwise other users will not be able to work with it.

In general, for systems like Celesta pessimistic lock drawbacks are much more severe, so Celesta uses optimistic lock to prevent lost updates.

By default, every table in Celesta is supplied with a INT NOT NULL type recversion system field.

This field is created automatically, so the developer should not include it in the CREATE TABLE script. The developer also cannot create their own field named recversion. Access to the field is provided by a getRecversion() method just like to any ordinary field.

When inserting a new record the recversion field assumes a default value 1 (one).

When updating the record a special database trigger checks if the new value matches the value in the database: the field is incremented if the values match and an error is generated if not:

In examples above Celesta will throw an exception and will not allow to save the record which causes lost update to the database.

Sometimes it is necessary to disable the lost update prevention, for example when the table is intended to be read and appended only or just read-only. In this case a WITH NO VERSION CHECK option must be used after the table definition in CelestaSQL language.

The easiest way to use it is to add a @CelestaTest annotation to the test class and use CallContext parameters in tests:

This means every test can receive an active CallContext as a parameter. This context is generated based on H2 database, to which a Celesta score is deployed and can be used to create cursors. When @BeforeEach-annotated methods are used, the same CallContext will be provided in the setup method and test method.

CelestaUnit has the following defaults:

Defaults can be changed by using @CelestaTest annotation parameters:

In some cases it might help to disable reference integrity check to simplify adding test data to tables linked by foreign keys to other tables.

A Celesta class instance is available with a getCelesta() method of a CallContext context variable sent as an argument to every data processing procedure.

The getScore() method provides the solution developer access to the system score built during parsing of CelestaSQL files. Score access is necessary to receive information on current database structure when executing business logic code, including metadata bound to database objects using CelestaDoc.

Score objects are not immutable, current score state can be saved to CelestaSQL files using a GrainSaver class.

Score mutability must be used to develop tools that can read and generate CelestaSQL, for example, for integration with database visual design tools. Changing the score during the Celesta run time is not allowed and may cause non-determinate system behaviour.

The score (Score) is divided into grains (Grain) consisting of tables (Table), indices (Index) and views (View).

A class diagram that describes the Score classes is presented below:

The ColumnMeta<V> interface is basic for table and view columns (V type parameter corresponds to the Java type stored in the column value), which allows to determine the Celesta column data type, column nullability and CelestaDoc bound to it. This interface is implemented by ViewColumnMeta<V> class to describe view fields and Column<V> to describe table fields.

The Column<V> class is abstract and has subclasses for six field types supported in Celesta:

Score modification is used only to develop CelestaSQL code generating tools.

Cursor is an object containing JDBC PreparedStatements and ResultSets necessary to execute cursor methods. These resources must be closed after use. Besides, the cursors and the objects constituting its inner state are not threadsafe, so the cursor must not be stored as a shared resource. Cursor closing, loosing a link to it and transaction completion must normally happen simultaneously.

Cursor life cycle is limited to CallContext life cycle it was created with. Calling close() on CallContext results in closing all cursors created in that context, so normally it is not necessary to explicitly close cursors in the code.

Still in some situations, when the cursor is created in an iteration, it must be closed explicitly.

CallContext does not allow to create more than 1023 cursors (otherwise it throws an exception). If a cursor can be created in an iteration, it must be closed (in the next point the code will be improved):

Opening and closing cursors in iterations results in multiple opening and closing JDBC PreparedStatements which is not optimal. The best solution is to design code where cursors are created in the beginning of the service method and then reused:

If the table being read has many columns, but only some of them are worked with, define the needed columns explicitly to speed it up. It can significantly reduce the amount of data transferred from the database.

Cursor methods can be divided in three categories:

Calling methods of the first category does not result in sending any queries to the database. For example, orderBy does not range entries at the moment the call happens, it only forms the cursor state to be used for forming SQL queries during the second and third category methods execution.

So the right pattern is to "prepare" the cursor as much as possible by setting all necessary limits using the first category methods before executing reading methods.

The get(…​) method extracts a single record from the database by its known primary key. It is a reliably fast (because the primary key always has an index) and the most demanded operation in practice. The get(…​) method does not account for existence of any filters set for the cursor, so in rare cases when it is necessary to check if a record with a known key is in the range set by filters, a navigate("=") method must be used.

If the primary key of an entry for deletion is known, do not read it from the database using get or find. Filling the primary key fields with correct values is enough for the delete() method.

Common mistake is to check the range for emptiness using the count() method. Counting all the records in the database only to determine if there are any at all is a bad idea.

If the search can be done in the database itself, better do that instead of sending the whole record set to the application to search using the application’s resources:

There are four data filtering methods in Celesta, ranged from the simplest to the most complex:

Do not use more complex filters if the simpler one suffice!

The setRange method is applicable in most practical tasks to filter a field by a single value or a of range values:

The setFilter method is used in rare cases when a field value range to be filtered needs to be set as a complex expression. A single field can have either a filter set by setRange or by setFilter, so calling these methods for the same field "replaces" the previously set filter.

setRange must be used whenever possible so Celesta can reuse JDBC PreparedStatement statements to greatly increase performance during the iteration:

Also setRange is preferred because its API allows to control types sent as arguments for the method.

The setComplexFilter method allows to add "a piece of SQL" to a WHERE expression setting the cursor entry set. After any setComplexFilter call all JDBC PreparedStatement statements must be recreated, so as with setFilter, it is not efficient to call it in an iteration. Main use for this method is to set conditions involving two or more fields, for example: a >= b. In other cases setRange/setFilter should be used.

The setIn method is instrumental in situations when the set of filtered values is determined dynamically from other data in the database.

A general point for all RDBMS. In Celesta, usually the following is true: if a setRange method is called for a cursor field, create an index for that field.

Querying data in iterations is a common pattern. It is possible to significantly reduce the number of queries to the database and increase speed by using the simplest caching: there is no need to call get for the cursor if the requested value of the primary key already matches the one requested earlier:

This idea can be developed in a very efficient pattern: it is possible to drastically reduce the number of queries for bar entries to the database by sorting the c cursor by the barId field:

Many cursor methods have two versions: with and without a try prefix. They act the same as far as processing speed is concerned, but from the perspective of code design (fail-fast approach) it is preferable to use methods without try if the returned Boolean value is not going to be used. See this explained in the documentation.

Two groups of methods exists to move between records in a cursor: navigation and iteration.

Navigation methods are tryFirst(), next(), previous() and navigate(…​) which allow to move to a different record according to specific rules.

Iteration methods are a tryFindSet() – nextInSet() pair and an iterator() method implementing the respective java.lang.Iterable interface method.

Navigation methods send a query to the database per each call so they should be utilized when only a single entry is needed.

Iteration methods send queries and open JDBC ResultSet used for further movement between records.

DBSchema is a convenient tool for database visual design. It is possible to fully simulate all Celesta information on the database structure (tables, fields, keys and indices) in DBSchema or to turn an existing Celesta score into a DBSchema diagram.

Everybody who need to create CelestaSQL scripts for an existing database are encouraged to use this technology. Other options (like uploading the SQL script from the database and its manual "cleaning") proved to be much more labor intensive.

DBSchema program with an installed Celesta support helps here. Here are the steps:

On the start of dbschemasync in convertation mode from DBSchema to score, the third command line option -adoc simultaneously generates diagrams in PlantUML format for each DBSchema digram. Diagram file names match names of DBSchema sheets with diagrams.