3.1 Architecture of Integra4 SQL Engine

The following diagram illustrates the internal architecture of Integra4 engine:


3.2 Important Features of Integra4 Engine

Integra4 has ANSI SQL92 intermediate level conformance and also some useful extensions. Given below is a list of important features:

ØConnection and user management

ØCatalogs and schema

ØTable creation
  • In any specified path
  • Domains
  • Unique constraint, NOT NULL
  • Check constraint
  • Referential constraint
  • Default value
  • Temporary table: Private for each session
ØData types
  • Char (1 - 256)
  • Varchar (1 - 32K)
  • Exact numeric: Decimal, Currency
  • Approximate numeric: Float, Double
  • Int, Smallint
  • Date
  • Time
  • Bit, Varbit
  • Binary
  • Logical
ØDrop table with drop behavior, RESTRICT or CASCADE

Ø Rename schema, table, column

Ø Create, Drop indexes

Ø Alter table
  • Add, drop, modify data type and default
  • Add, drop column constraint
  • NORESTRUCT option
  • Approximate numeric: Float, Double
  • Error rows capturing in a file
Ø Views
  • Create with CHECK
  • Updatable
  • Drop behaviour RESTRICT or CASCADE
Ø Transaction support
  • Commit, Rollback
  • READ ONLY, READ WRITE
  • IMPLICIT, EXPLICIT, OFF
  • Isolation levels
  • DDL statements in transactions
Ø Concurrency control: Row level and table level locks

ØSecurity
  • User name, Password at Database level
  • GRANT, REVOKE
  • Schema, table and column level control
ØBuilt-in variables: CURRENT_DATE, CURRENT_TIME, etc.

ØAdditional operators: CAST, COALESCE, MOD

Ø CASE construct

Ø String concatenation operator

Ø NULL value support

Ø User Defined Functions: Normal and aggregate

Built-in functions, uniformly available in SQL engine as well as forms and report tools:
  • Aggregate: AVG, MAX, MIN, SUM, COUNT
  • String: SUBSTRING, UPPER, LOWER, ASCII, ACHAR,

                      POSINSTR, LTRIM, RTRIM, REPLICATE, CONCAT, LENGTH,
  • REPLACE, INITCAP, BITTEST
  • Date and time: DAY, MONTH, YEAR, MDY,

                     WEEKDAY, DATEADD, DATEDIFF, DOW, LDOM, LWDOM, QUARTER, 

                      WEEK, HOUR, MINUTE, SECOND
  • Mathematical: ABS, CEILING, EXP, FLOOR, PI, POWER, RAND,

                      ROUND, SIGN, SQRT, TRUNC, MOD
Ø Outer join

INSERT INTO table FROM delimited file / SELECT ..

SELECT .. FROM table INTO delimited file

System tables access through SQL statements

Several pre-canned views for system information (InfoSchema) provides ANSI standard system catalog access

Ø Compiled query support

Stored procedures

Recovery features
  • Transaction recovery
  • Since data and indexes are kept in separate physical files, even when an index is corrupt, data will not be affected. Indexes can be reconstructed
  • Check and repair for tables

Optimizations Done In Integra4

                       Integra4 engine does extensive query optimisations.

                        It is possible for the user to obtain the query evaluation plan (QEP).

Performance Tuning

                       The following controls are available for users to improve database performance:

Placement control for tables:

                        Each Integra4 table is normally stored as one ObjecTrieve file consisting of 2 physical files,
                        one for data and one for index. They are normally created in the database directory. However,
                        it is possible to create different tables in different devices. Further it is even possible to keep the
                        data part and index part in different devices for better performance.

Ø Placing different tables in different directories reduces concurrency bottlenecks.

ObjecTrieve also supports the container file feature, using which several tables can be kept in a single pair of physical files. This ensures better disk space utilization for highly dynamic files.

System tables cache size

Ø Sort area size

Ø Physical file manager (ObjecTrieve) cache size

Ø IN list cache size

Ø Memory file size

                While Integra4's SQL gives fast access, in situations where very fast access is required,
                it is possible to access the tablesdirectly through ObjecTrieve, from C, C++ or Java
                 bypassing all the overheads of SQL.