Managing the database

Managing the database is done through "DDL" commands. In SIRA_PRISE, these commands are nothing else than regular assignment commands to catalog relvars. Two particular consequences from this approach deserve to be spelled out explicitly :

Just like all regular assignments, they are set-level, and it is thus possible to define multiple relvars, multiple dbms files, multiple attributes, ... in one single assignment command.
Just as with regular assignment, multiple assignment to distinct target relvars is also possible, and in fact in certain cases it is even required, due to the database constraints that govern the catalog. Nevertheless, lots of combinations of catalog MA may not be supported (especially the ones that involve assignments which define or modify the physical design of some part of the database), most often because the corresponding side-effects cannot be applied to the system properly. Also remember that assignments must always be either to catalog relvars or else to 'user' relvars. It is e.g. not possible to define a new relvar and assign a non-empty value to it within the same statement.

Also note that, in contrast with assignments to "user" relvars, assignments to catalog relvars are always immediately committed. That is, all assignments to catalog relvars are treated as if they were made from a connection/transaction that has its 'autocommit' flag set to true.

The DDL commands will now be discussed one by one, and any relevant remarks given as appropriate. For all updatable catalog relvars, one section will be presented with the concerned relvar's name as the section title. In the sections, the heading of the relvar will be specified as well as that relvar's keys, unless the relvar is all-key (every attribute participates in the only key).

Defining Types

The first step in defining a new database is, of course, to define its logical design. This involves deciding on which data types will be used in the database to represent the information it is supposed to contain. A number of types are already provided by SIRA_PRISE, but it is unlikely that these will suffice to satisfy all your requirements. At times, you may want or need to define your own custom type. Three ways are available in SIRA_PRISE to achieve that :
- Java-backed types.
  When creating java-backed types, the first thing to do is of course to create an implementation class in java for your type. Rules for this implementation class are to be followed both for the class name and the package name :
  - The classname must be the word 'Dbms', followed by the capitalized name of the type, followed by the word "Implementation". "Capitalized name" means : first character uppercase, all other characters lowercase. E.g. the classname for the java type implementation corresponding to a type you want to name SphericAngle, would be DbmsSphericangleImplementation.
  - The package name must be either the system's type implementations package be.SIRAPRISE.typeimplementations, or else it may be a name of your choice, but in that case this package name must be included in the implementations search-path configuration setting.
  - The type implementation class must implement the TypeImplementation interface.
  - The type names cannot end with the word 'interval'. SIRA_PRISE automatically generates types with such names for all types that have a comparison operator GT. Also consider that the name of the corresponding intervaltype, that is automatically generated by SIRA_PRISE if the type has an ordering operator, must itself also obey the rules for typenames, in particular the maximum length for typenames. This means that the maximum length for names of types that have an ordering operator, is only 52, not 60. See the last paragraphs of this section for more information on interval types.
  Obviously, the compiled class must also be made available on the server's classpath. When that is done, you can register your type by adding a tuple to the JavaBackedType relvar.
- User-defined types
  SIRA_PRISE also offers the possibility of defining custom types without providing a java implementation for the type. Naming such a type is done by adding the required tuples to the UserDefinedType relvar and its "satellites".
- Constrained Types
  SIRA_PRISE also offers support for (a subset of) the inheritance model depicted in TTM. Using this feature is done by adding tuples to the the ConstrainedType relvar.
Some more things are to be said on the correspondance between any of the three foregoing "types of type", and interval types.

Whenever the system must load any type, it also tries to find an implementation for the ordering operator for that type (i.e. GT() ). If it does find such an implementation, then SIRA_PRISE automatically also defines an intervaltype, based on the ordered base type concerned and the ordering operator found for it. The name of this intervaltype will be the name of the ordered base type, suffixed with the word "Interval". E.g. the interval type corresponding to ordered base type INT will be named INTINTERVAL. The intervaltype will itself be a scalar, unordered type. For all such interval types, a whole host of useful operators are also automatically provided. These operators are discussed in the section on the interval operators.

The interval types themselves will have at least one possrep, which corresponds to the closed-open notation specifically. There is always a BEGIN component in this possrep, which denotes the interval's (inclusive) start point. Optionally, there is also an END component, which denotes the interval's (non-inclusive) end point. Interval values without an END component "stretch to infinity", so to speak, i.e. they include all the values of the underlying point type up to and including the very last. Interval values that include and END component only include the values of the underlying point type up to, but not including, this END component value. Intervals without a BEGIN component are not supported. So intervals cannot be made to "start at negative infinity", so to speak.

Some interval types will be "generated" with a second possrep, which corresponds to the closed-closed notation. This possrep has two components, FROM and TO (and these components are always present for any value of such interval types). The FROM component value is always equal to the BEGIN component value of the "closed-open" possrep, the TO component will denote the highest value of the underlying point type that is still included in the interval's range. This second "closed-closed" possrep is generated only if all of the following conditions are met :
- A (niladic) FIRSTxxx operator can be found, with xxx the name of the underlying point type
- A (niladic) LASTxxx operator can be found, with xxx the name of the underlying point type
- A (monadic) NEXT operator can be found that takes a single argument of the underlying point type, and returns a value of the underlying point type
- A (monadic) PRIOR operator can be found that takes a single argument of the underlying point type, and returns a value of the underlying point type
One more or less special interval value is the empty interval (which, in the approach adopted by SIRA_PRISE, is indeed a valid interval value). It can be recognised (if and when it appears anywhere - which it really shouldn't) by the fact that the END component is equal to the BEGIN component. BEGIN(1)END(1) and BEGIN(2)END(2) are thus all representations of the same -empty- interval value. The empty interval will never appear anywhere as a value of an attribute inside a relation, tuples with empty interval values are always automatically discarded from the relation.

Deleting a type will also delete the corresponding intervaltype based on the deleted type, if such an intervaltype exists. It is not possible to delete only the intervaltype based on some ordered base type, only the base type itself can be deleted, which will have the effect of removing both from SIRA_PRISE's type system.
Defining operators

If you want to be able to write expressions to do computations on values of the types you have defined, you will need some operators. SIRA_PRISE comes with a set of system-provided operators, but for operating on values of your own types, you will have to implement your own. Much as with JavaBackedType implementations, all that is needed, is to write a java class and install this in the classpath of the Java virtual machine that runs SIRA_PRISE. The details for creating such an operator implementation are specified in the javadoc.
Defining attributes

The next step in defining a logical design, is to define the attributes that will be used in it. In SIRA_PRISE, this must be done as a separate step, prior to defining/declaring relvars and their headings. This encourages uniform and consistent use of the same attribute names for the same "business information" : it helps avoid multiple distinct names popping up for the same kind of info, such as "customerid", "clientid", "custid", ...

Defining attributes ("business elements") is done by adding tuples to the Attribute relvar and its "satellites".
Defining the logical model - relvars

When the needed business elements have been defined as attributes, the next step is to define the (logical structure of the) relvars in the database. SIRA_PRISE supports two "kinds" of relvars :
- "Base" relvars. These relvars are the "basic information bricks" in the database, and are, in principle, user-updatable ("in principle", because constraints can be defined to rule out user updates). Base relvars are defined by adding tuples to the Relvar and RelvarAttribute relvars.
- "Virtual relvars". These relvars constitute read-only views on the "Base" relvars, these relvars are therefore not updatable, in the sense that they cannot directly be the target of a database update statement. Virtual relvars are defined by adding tuples to the Virtualrelvar relvar.
Defining the logical model - constraints

When the relvars in the database have been defined, the constraints that apply to them can also be defined. Although all in all, any constraint is really just a constraint, SIRA_PRISE offers several distinct ways to define them :
- Keys. Keys are constraints that define a uniqueness property within a single relvar, which can be either a base relvar or a virtual one. Keys are defined by adding tuples to the Key and KeyAttribute relvars.
- Tuple constraints. Tuple constraints are constraints stating a rule that must be adhered to by all the individual tuples in a relvar (i.e. deciding that the constraint is satisfied can be done by looking at the tuple in isolation, and at nothing else). Tuple constraints are defined by adding tuples to the TupleConstraint relvar.
- Database constraints. Database constraints are all the rules that say whether a database value is valid or not, and which are neither keys nor tuple constraints. (But note that there is no obligation for stating, say, tuple constraints as tuple constraints. They can equivalently be defined as database constraints without any harm, but this might be
- Assignment constraints. While the three foregoing "classes" (insofar as that term is appropriate) of constraint all apply to (i.e. constrain a) single database state, assignment constraints are constraints that can depend on more than just a single database state alone. Transition constraints, for example, which depend on both the pre-update and post-update state of the database, are supported in the form of assignment constraints. Constraints which depend on contextual information such as current date or identity of the user owning the transaction that is issuing an update, are also supported using this concept of assignment constraints. Iow, assignment constraints are, in a sense, the "broadest" category of constraints conceivable in a database context. Defining assignment constraints is done by adding tuples to the AssignmentConstraint relvar.
  The two "major purposes" for assignment constraints are : (a) to define transition constraints, and (b) to define update secruity rules. For more information on how to use assignment constraints for enforcing update security rules, please see "securing your databases with SIRA_PRISE".
All constraints in the system, regardless of which of the 4 foregoing "categories" they belong to, are uniquely identified by an ERRORCODE. That is, the same ERRORCODE attribute value cannot appear for both a KEY and a DATABASECONSTRAINT, or any such combination.

For all constraints in the system, it is possible to define a "tailored" constraint message text. This is the text to be displayed (returned to the client) by the system as an error message, whenever it finds that a constraint is violated. Using this feature is done by manipulating the ConstraintMessageText relvar.
Defining the logical model - automatic ('triggered') data actions

SIRA_PRISE supports the concept of update operations to some relvar being "triggered" ("fired", as it were) by the fact of an update operation being applied to some other relvar. Using this feature is done by adding tuples to the TriggeredDataAction relvar.
Defining the physical model - creating the resources

For a logical design to be implemented through some physical design, the physical resources used in that design must be created. This is done by manipulating the DbmsFile relvar, the StorageSpace relvar, and its "satellites".
Defining the physical model - defining a physical design

With a logical design completed and the needed physical resources created, you can go about defining the physical design of your database to SIRA_PRISE. This is done by manipulating the RecordType relvar and its "satellites", and the Index relvar and its "satellites".
Managing database statistics

SIRA_PRISE offers support for collecting and managing database statistics. Collecting database statistics is done using the appropriate Monitor Command. The statistics themselves are registered in the RelvarCardinality and AttributeValueDistribution relvars.

The system-provided operators

General Overview

Operator name and argument types	Comments
EQ(...,...)	Provided for all types
GT(...,...) LT(...,...) GE(...,...) LE(...,...)	Provided for types INT, LONG, FLOAT, DECIMAL, DATE and TIMEOFDAY.
XOR(boolean,boolean)
AND(boolean,boolean)
OR(boolean,boolean)
NOT(boolean)
ABS(...)	Returns the absolute value of its argument. Provided for the types INT, LONG, FLOAT.
MAX(...,...)	Returns the greater of its two arguments. Provided for the types INT, LONG, FLOAT, DATE and TIMEOFDAY.
MIN(...,...)	Returns the lower of its two arguments. Provided for the types INT, LONG, FLOAT, DATE and TIMEOFDAY.
PLUS(...,...)	Returns the sum of its two arguments. Provided for the types INT, LONG, FLOAT, DECIMAL and TIMEOFDAY.
SUB(...,...)	Returns the difference of its two arguments. Provided for the types INT, LONG, FLOAT and TIMEOFDAY.
MULT(...,...)	Returns the product of its two arguments. Provided for the types INT, LONG, and FLOAT.
DIV(...,...)	Returns the result of the division of the first argument by the second. Provided for the types INT, LONG, and FLOAT.
ROUND(float,float)	Out of the set of whole multiples of the second argument, returns the value that is closest to the first argument.
EXP(float,float)	Returns the result of the first argument raised to the power of the second.
EXP(float)	Returns the result of e raised to the power of the argument.
LN(float)	Returns the natural logarithm of the argument.
SQRT(float)	Returns the square root of the argument.
CEIL(...,...)	Returns the smallest number that is an exact multiple of the second argument and greater than or equal to the first. Provided for types INT and LONG.
FLOOR(...,...)	Returns the largest number that is an exact multiple of the second argument and smaller than or equal to the first. Provided for types INT and LONG.
MOD(...,...)	Returns the number c such that if c = mod(a,b), then a = (a / b) + c. Provided for types INT and LONG.
COS(angle)	Returns the cosine of the argument
COSEC(angle)	Returns the cosecant of the argument
COTAN(angle)	Returns the cotangent of the argument
SEC(angle)	Returns the secant of the argument
SIN(angle)	Returns the sine of the argument
TAN(angle)	Returns the tangent of the argument
ACOS(float)	Returns the arc cosine of the argument
ACOSEC(float)	Returns the arc cosecant of the argument
ACOTAN(float)	Returns the arc cotangent of the argument
ASEC(float)	Returns the arc secant of the argument
ASIN(float)	Returns the arc sine of the argument
ATAN(float)	Returns the arc tangent of the argument
LENGTH(longinterval)
LENGTH(intinterval)
LENGTH(string)	Returns the number of tokens that appear in the string argument.
CONCAT(string, string)	Returns the result of concatenating the two string arguments.
UPPERCASE(string)	Returns the result of converting the string argument to uppercase. Note that this can be dependent on locale of the machine where the server is running.
LOWERCASE(string)	Returns the result of converting the string argument to lowercase. Note that this can be dependent on locale of the machine where the server is running.
SUBSTR(string, start)	Returns the string that consists of the same series of tokens that appear in the string argument starting at the position designated by the integer argument start. The first position is position 1 (i.e. positions are not offsets). If the start value is less than 1, then the entire string argument is returned. If the start value is larger than the given string's actual length, then the empty string is returned.
SUBSTR(string, start, end)	Returns the string that consists of the same series of tokens that appear in the string argument starting at the position designated by the integer argument 'start', and before the position designated by the integer argument 'end' (i.e. the token at position 'end' is not included in the result). The first position is position 1 (i.e. positions are not offsets). If the 'start' value is outside the range [1-actual length of the string], then it is adjusted to fall within that range. After that, if the 'end' argument is outside the range [start-actual length of the string], then it is also adjusted to fall within that range. If, after adjustment, 'end' is equal to 'start', then the empty string is returned.
MATCHES(string, regex)	Returns true if the first argument "matches" the second, false otherwise. The second argument must be a valid Java Regular Expression.
BEGINSWITH(..., ...)	Returns true if the first argument "begins with" the second, false otherwise. Provided for types STRING and NAME.
ENDSWITH(..., ...)	Returns true if the first argument "ends with" the second, false otherwise. Provided for types STRING and NAME.
DATESHIFT(date,years,months,days)	Years, months and days are all integer arguments. Only one is allowed to be nonzero. Returns the date that is obtained by adding that nonzero value to the given date.
FIRST...()	Returns the lowest value of the type whose name follows 'FIRST' in the operator name. Provided for types INT, LONG and DATE.
LAST...()	Returns the lowest value of the type whose name follows 'FIRST' in the operator name. Provided for types INT, LONG and DATE.
NEXT(...)	Returns the value that is next-higher to the given argument. Provided for types INT, LONG and DATE.
PRIOR(...)	Returns the value that is next-lower to the given argument. Provided for types INT, LONG and DATE.
AFTER(anyintervaltype,anyintervaltype)	See following section for more detail on intervaltypes and their operators.
BEFORE(...,...)
BEGINS(...,...)
ENDS(...,...)
CONTAINS(...,...)
WITHIN(...,...)
ISEMPTYINTERVAL(...)
ISINFINITEINTERVAL(...)
MEETS(...,...)
MERGES(...,...)
OVERLAPS(...,...)
GAPBETWEEN(...,...)
SPAN(...,...)
IINTERSECT(...,...)
IUNION(...,...)
IMINUS(...,...)
CONTAINSPOINT(...,...)
STARTPOINTOF(...)
ENDPOINTOF(...)
SPLITS(...)
SPLITBY(...)

The interval operators

This section will mention, for each provided interval operator, its return type, its definition, its treatment of the empty interval, and remarks with respect to argument limitations, if any. The following abbreviated notation will be used to denote the arguments and their "components" :

i1 Denotes the first (or only) interval input argument, i2 denotes the second. b1 Denotes the begin() part of the first (or only) input argument, b2 the begin part of the second. e1 Denotes the end() part of the first (or only) input argument, e2 the end part of the second. If there actually is no end() part, then e1/e2 are assumed to represent some conceptual "value" of "infinity", which compares to any real value of the base type as being greater (and to itself as equal). The empty interval is denoted by o.

AFTER(i1,i2) returns a boolean (this applies to all operators up to and including MERGES(), and will not be repeated everywhere). The return value is true if b1>=e2, false otherwise. If either argument is o, false is returned.

BEFORE(i1,i2) returns true if b2>=e1, false otherwise. If either argument is o, false is returned.

BEGINS(i1,i2) returns true if b1=b2 and e1<e2. False if either argument is o.

ENDS(i1,i2) returns true if e1=e2 and b1>b2. False if either argument is o.

CONTAINS(i1,i2) returns true if b1<=b2 and e1>=e2. True if i2 = o. False if i1=o and i2<>o.

WITHIN(i1,i2) returns true if b1>=b2 and e1<=e2. True if i1 = o. False if i2=o and i1<>o.

MEETS(i1,i2) returns true if b1=e2 or e1=b2. True if either argument is o.

OVERLAPS(i1,i2) returns true if b1<e2 and b2<e1. False if either argument is o.

MERGES(i1,i2) = MEETS(i1,i2) or OVERLAPS(i1,i2).

ISEMPTYINTERVAL(i1) returns true if i1 is o, false otherwise.

ISINFINITEINTERVAL(i1) returns true if i1's representation has no end() part, false otherwise.

CONTAINSPOINT(i1,p) takes one interval value and a value p of the type on which the interval type is based, and returns the boolean value true if i1 <> o, b1<=p and e1>p, false otherwise.

STARTPOINTOF(i1) returns the begin() part of i1. An exception is raised if i1 is o. This operator can also be invoked as THE_BEGIN(i1).

ENDPOINTOF(i1) returns the end() part of i1. If ISINFINITEINTERVAL(i1) returns true, or if i1 is o, an exception is raised . Synonymous to THE_END(i1).

IUNION(i1,i2), IINTERSECT(i1,i2) and IMINUS(i1,i2) are the (hopefully) obvious interval computation operators. IUNION() and IMINUS() raise exceptions if the "result" is not one single interval, IINTERSECT() possibly returns o.

SPLITBY(i1,i2) returns true if b1<b2 and e1>e2 and i2<>o.

SPLITS(i1,i2) returns true if b2<b1 and e2>e1 and i1<>o.

GAPBETWEEN(i1,i2) returns the gap between two interval values, if neither i1 nor i2 are o and one of AFTER(i1,i2) or AFTER(i2,i1) are true. That is, GAPBETWEEN returns the interval from MIN(e1,e2) to MAX(b1,b2). Returns an empty interval if MERGES(i1,i2) is true.

SPAN(i1,i2) returns the interval from MIN(b1,b2) to MAX(e1,e2) if neither i1 not i2 are o. If any argument is o, returns the value of the other argument (possibly also o).

Possreps and THE_ operators

SIRA_PRISE supports the inclusion in a type definition of 'possible representations' (possreps for short). Each type obviously must have at least one such possrep, for otherwise the values of the type simply would not be externally representible. That possrep is the type's "default" possrep.

All possreps can have 'possrep components'. For each component of a possrep, SIRA_PRISE automatically defines a THE_ operator whose argument is a value of the possrep's type, and whose return value is the value for that particular component in that particular possrep's representation of the argument.

E.g. a date value can be represented as a single 10-character string, or it can be represented using three integer values denoting the day, month and year respectively. The latter gives rise to a possrep with three components named DAY, MONTH and YEAR respectively, and to the automatic availability of three operators THE_DAY, THE_MONTH and THE_YEAR respectively (the argument to each of these operators is a value of type DATE, of course). Values of type date can then be selected using either one of DATE(yyyy-mm-dd), DATE(ISO(yyyy-mm-dd)), or DATE(DAY(dd)MONTH(mm)YEAR(yyyy)).

The possreps (and thus, which THE_ operators are available) for the system-provided types are included in the type documentation.

Please observe that the component names of a possrep must be unique across all the possreps of the same type (otherwise there would have to be multiple distinct THE_ operators that share the same name and have the same argument type, making their signature completely equal, and thus rendering the operators indistinguishible from the engine's perspective). So it is therefore not possible to define, say, a possrep MDY for type DATE that also has a component named DAY, MONTH or YEAR.

Also observe that type STRING does not seem to have a possrep. If it had one, it should also have a component, and that component would (in practice) have to be of type STRING as well, breaking the rule that no type can have a component that is of the very same type. The absence of a possrep for type STRING obviously does not prohibit writing value selectors for the type. The absence of a possrep for type STRING only reflects the fact that STRING is the actual root type of the system, as far as "representation of values" is concerned.

Managing the database

Defining Types

Defining operators

Defining attributes

Defining the logical model - relvars

Defining the logical model - constraints

Defining the logical model - automatic ('triggered') data actions

Defining the physical model - creating the resources

Defining the physical model - defining a physical design

Managing database statistics

The system-provided operators

General Overview

The interval operators

Possreps and THE_ operators