Apollo 9 Database Engine
The Apollo Engine is included with Apollo Embedded and Apollo Server
Overview
The Apollo database engine is the underlying low-level technology that is responsible for reading, writing, and updating the DBF/Xbase database files, indexes and memo files. It is included with Apollo Embedded and Apollo Server. The database engine consists of several small .DLL files that total under 1MB in size, and which may be freely deployed with your applications. These files do not require special registration or configuration on the end-user machine.
The engine files can be placed in the same directory as your Apollo applications (C:\YourApp) or can be copied to the C:\Windows\System32 directory to be shared by all of your Apollo applications. Included as part of the Apollo database engine is a SQL query processor that provides SQL-92 support for managing your DBF/Xbase data files.
Note: Developers should not use the Apollo database engine directly. Instead, use the Apollo Embedded components to develop your applications. These components wrap the Apollo database engine.
Features
- Native 32-bit and 64-bit support
- Small 1MB footprint
- Royalty free unlimited distribution
- High-speed SQL-92 query processor
- Included with Apollo Embedded and Apollo Server
- Fully supported by Apollo Embedded components (i.e. no need to call engine API directly)
- Single and multi-user support
- Table and row level locking support
- Database level encryption
- Field types: Character Numeric, Logical, Date, Memo, BLOB
- Direct low-level CRUD support (create, read, update and delete rows, tables, indexes)
- Built-in Mach SIx Query Optimizer similar to Microsoft RushMore Technology used in FoxPro and MS-Access, allows filters and non-SQL queries to execute efficiently
- Index Scoping lets you limit your indexed views in a flash
- Roll-Your-Own (RYO) Queries and Indexes lets you define high-speed ad-hoc queries with no fixed conditions
- Flip existing indexes between ascending and descending order
- Conditional Indexing allows indexes to selectively include data
- Low-level power using direct calls to the Apollo API functions
- Includes Fast Text Search (FTS) support which is a search engine that provides hyper-fast searches on text-based databases similar to the Find option in the Windows help system
- User-Defined Function (UDF) support in index/query expressions
SQL-92 Support
Apollo includes a SQL-92 compatible query processor that allows developers to access and manage DBF/Xbase database files using traditional SQL commands. Apollo provides support for CRUD operations (Create, Read, Update and Delete).
| SQL Command | Extra Commands and Clauses |
| SELECT |
Extracts data from one or more fields from one or more tables
Aliases (AS and IN) CASE DISTINCT FROM GROUP BY [HAVING] IS [NOT] NULL JOIN (INNER, LEFT, LEFT OUTER) LASTIDENTITY LIKE ORDER BY WHERE RIGHT [OUTER] JOIN and FULL [OUTER] JOIN are supported in 3.0 |
| INSERT | The INSERT statement is used to add or append records to tables. |
| UPDATE | The UPDATE statement is used to update, modify or change data in tables. |
| DELETE | The DELETE statement is used to remove rows in tables. |
| CREATE DATABASE |
This
command is used to create new DBF/Xbase database. COLLATE |
| CREATE TABLE |
Creates a new DBF/Xbase file. NULL [NOT NULL] IDENTITY DEFAULT CAPTION HIDDEN COMPRESSED ENCRYPTED READONLY Constraints: CHECK PRIMARY KEY FOREIGN KEY REFERENCES ON UPDATE [CASCADE] ON DELETE [CASCADE] |
| ALTER TABLE | Changes data tables DROP COLUMN ADD COLUMN |
| DROP TABLE | Deletes the specified table from the database. |
| CREATE INDEX |
Creates a new index for a given table. UNIQUE ASC | DESC CASE SENSITIVE |
| DROP INDEX | Delete the specified index from the database. |
| SQL Clauses | Notes |
| AVG | Returns the average of the values in a specified column or an expression. |
| COUNT | Counts the number of rows retrieved by a SELECT statement. |
| MAX | Calculates the largest value for a numeric column. |
| MIN | Calculate the smallest value for a numeric column. |
| STDEV | Computes the standard deviation. |
| SUM | Returns the sum of all values in a column. |
| SQL Clauses | Notes |
| ABS | Returns the absolute value of the argument. |
| ACRTAN | Calculates the arctangent of the given number. |
| COS | Returns the cosine of the angle X, in radians. |
| EXP | Returns the value of e raised to the power of X, where e is the base of the natural logarithms. |
| FRAC | Returns the fractional part of the argument X. |
| INT | Returns the Integer position of a Float value. |
| LN | Returns the natural logarithm (Ln(e) = 1) of the float-type expression X. |
| MINOF | Returns the lowest value of the n figures. |
| MAXOF | Returns the highest value of the n figures. |
| PI | Pi, approximated as 3.1415926535897932385. |
| POWER | Raises Base to any power. |
| ROUND | Rounds a float-type value to an integer-type value. |
| ROUNDDEC | Rounds a float-type value to a given precision (decimals). |
| SIN | Returns the sine of the argument. |
| SQR | Returns the sine of the argument. |
| SQRT | Returns the square root of the argument. |
| TRUNC | Truncates a float-type value to an integer-type value. |
| TRUNCDEC | Truncates a float-type value to a given precision (decimals). |
| SQL Clauses | Notes |
| IF | Evaluates a Boolean statement, returning one value for True and another for False. |
| SQL Clauses | Notes |
| NOW | Returns the current system date in floating point format. |
| DAY | Return the day for the given date |
| EXTRACT | Used to get Day, Month and Year values |
| MONTH | Return the month for the given date |
| YEAR | Return the year for the given date |
| HOUR | Return the hour for the given time |
| MIN | Return the minutes for the given time |
| MSEC | Return the milliseconds for the given time |
| SEC | Return the seconds for the given time |
| SQL Clauses | Notes |
| COPY | Returns a substring of a string. |
| EXTRACT | Formats the series of arguments in the list of arguments |
| FORMAT | Formats the series of arguments in the list of arguments |
| FORMATDATETIME | Formats the date-and-time value given by DateTime using a specified format |
| FORMATFLOAT | Formats the floating-point value given by Value using a specified format |
| LEFT | Returns the leftmost Count characters contained in a string. |
| LENGTH | Returns the number of characters actually used in a string. |
| LOWER | Returns a string converted to lowercase. |
| POS | Searches for a substring within a string. |
| RIGHT | Returns the rightmost Count characters contained in a string. |
| SUBSTRING | Extracts a substring from a table column or character literal |
| TRIM | Deletes the leading or trailing character, or both, from a table column. |
| UPPER | Returns a string converted to uppercase. |
Database File Format
Apollo supports industry standard CA-Clipper, FoxPro 2.6 and Apollo DBF/Xbase database files, which include the following file extension combinations DBF/NTX/DBT, DBF/CDX/FPT, DBF/IDX/FPT, DBF/NSX/SMT. Apollo supports both legacy DOS and Windows data storage and indexing rules, which allows Apollo to support data managed by legacy CA-Clipper applications.
File types
- CA-Clipper - DBF data, NTX indexes, DBT memo
- CA-Clipper (hybrid) - DBF data, NSX indexes, DBT memo
- FoxPro 2.6 - DBF data, CDX/IDX indexes, FPT memo
- Apollo/SIX RDD - DBF data, NSX indexes, SMT memo
Field types
- Character (String, Char, Byte)
- Numeric (Integer, Decimal, Float, Currency types of data)
- Logical (Boolean)
- Date (DateTime)
- Memo (String, Varchar, BLOB)
Specifications
| Database Files | Limit |
| Max records per table | 1 billion (1) |
| Max characters per record | 64k |
| Max fields per record | 1022 |
| Max tables open all tasks | 250 (2) |
| Index Files | Limit |
| Max characters per key (NTX) | 338 |
| Max characters per key (CDX/IDX/NSX) | 240 |
| Max length of index expression | 255 |
| Max length of conditional expression | 255 |
| Max tags in a .CDX index | 255 |
| Max tags in an .NSX index | 64 |
| Max open index files per table | 249 (2) |
| Max open index files per task | 249 (2) |
| Fields | Limit |
| Max characters per field: Character | 1023 (3) |
| Max characters per field: Numeric | 20 |
| Max characters per field: Logical | 1 (4) |
| Max characters per field: Date | 8 (4) |
| Max characters per field: memo ref in DBF | 10 (4) |
| Max characters per memo in DBT/FPT/SMT | 64k |
| Max BLOB size stored in a memo | 16MB |
| Max characters in a field name | 10 |
| Numeric precision | 16 |
Notes
(1) Actual file size is limited to
2GB
(2) Limited by memory and number of file handles available
(3) Up to
32K if ApolloGetRecord is used to read the entire record buffer
(4) Non
adjustable field size
Architecture
Apollo database engine supports the following scenarios with applications developed using the Apollo components , including the Apollo Server since it was developed using the Apollo VCL components.
Single User - Local Access
Single User - File Share or LAN Data Access
Multiple Users - File Share or LAN Data Access
Getting Started
Apollo is an EXCELLENT product which I use for EVERY application I write which requires a database, and nowadays that means all of them.
Apollo just got Hardcore!
I love your products. Applications I developed with
Apollo over the last decade have aced the test of time, with heavy daily use. I purchased the upgrade today.
Congratulations on winning Best Database Engine
Award!
