A new technical article was posted a couple of days ago that discusses how to deploy ElevateDB applications, so please take a look and let me know if there's any information that's missing or that you would like discussed in addition to what is already covered. The following is a direct link:

Deploying ElevateDB Applications

I (finally) have frozen the 2.04 feature set, and only have a small bit of work yet, so I thought that I would take a moment to let everyone know what to expect, or not expect, in 2.04. The following is a list of the features along with any notes about what the feature does or about why it wasn't included in 2.04.

Major Features

---

Procedures and Functions at the Configuration Level (Not Implemented)

This feature had to be cancelled due to some issues with dependencies that violate some basic design principles in ElevateDB. ElevateDB is designed so that any database catalog to configuration dependency breakages can resolve themselves without causing any problems with opening the database catalog. For example, if a text index uses a custom word generator that cannot be found in the configuration because the configuration file was replaced or re-created without it, then ElevateDB will automatically tell the text index to use the default word generator. The same goes for ownership of objects, which in the case of a missing owner user, ElevateDB will assign the System user as the owner. The problem with procedures and functions at the configuration level is that they would cause a dependency between the database catalog and configuration that could not be automatically resolved, and would cause a database catalog open to fail. This would effectively "brick" the database catalog, to use MP3 player lingo, until a suitable configuration file was put in place. But, if this were to happen with a database catalog where there where thousands of calls to configuration-level procedures or functions, the result would be catastrophic.

Messaging Framework (Postponed)

The messaging framework has been postponed until later in the 2.04 development cycle due to time constraints. The current comms manager and sockets code is synchronous, and a lot of work still needs to happen to convert it to asynchronous code that works on multiple platforms and can handle the event-based design of such a framework.

Virtual Configurations (Implemented)

This feature allows you to specify that the configuration file should be handled entirely in the local process memory, and not written/read from disk. This feature is primarily designed to be used with local, single-user applications, but can also be used for the ElevateDB Server and multi-user, file-sharing applications. The only restriction is that in a multi-user, file-sharing application, this feature is an all-or-nothing proposition - all applications accessing a given database must use virtual configurations, or they must all use disk-based configurations. You cannot mix and match the two, and doing so can cause major issues.

Also, when using virtual configurations, you will have to recreate all necessary database, user/role, job, and store definitions every time the application is started, although the default users and roles will always be created for you.

Row Value Support (Implemented)

This feature allows the use of row value constructors in various scenarios that are outlined in the SQL statements below.

INSERT INTO InsertTest (ID, Description)
VALUES (100, 'Test 100'),
       (200, 'Test 200'),
       (300, 'Test 300')

UPDATE InsertTest SET (ID,Description)=(500,'Test 500')
WHERE (ID,Description)=(300,'Test 300')

UPDATE InsertTest SET (ID,Description)=
  (SELECT ID,'Updated' FROM UpdateTest WHERE ID=InsertTest.ID)

SELECT *
FROM orders
WHERE (custno,orderno)=(2156,1020)

SELECT * FROM orders
WHERE (orderno,1313) IN
  (SELECT OrderNo, Partno FROM Items WHERE OrderNo=Orders.OrderNo)

SELECT *
FROM rowvaluecust INNER JOIN rowvalueorders ON
(rowvaluecust.customerno,rowvaluecust.orderno) = (rowvalueorders.customerno,rowvalueorders.orderno)

Row value constructors can be optimized by the query optimizer using multi-column indexes. This feature reduce the number of indexes required for optimization of queries against a given table and will allow entire primary key and foreign keys to be used for optimization, as opposed to just the first column in the key.

Universal Triggers (Implemented)

This feature will allow you to define and use universal (ALL) triggers in addition to BEFORE, AFTER, and ERROR triggers, and a new OPERATION() function will indicate which operation (Insert, Update, or Delete) is currently-executing.

StandardNullBehavior Property (Implemented)

The engine and session/connection components of ElevateDB will include a property or connection string attribute that allows you to specify that you desire ANSI-SQL standard NULL behavior (the default), or non-ANSI-SQL NULL behavior where NULLs are comparable with normal operators (=,<>,>,<,BETWEEN, etc.) and NULL values are treated like an empty version of the containing value's data type. For example, with non-ANSI-SQL NULL behavior enabled, the following SELECT statement will return all rows where the State column is NULL:

SELECT * FROM Customer
WHERE State=NULL

This feature does not affect anything other than SQL comparisons, and does not affect how required (NOT NULL) columns work, how the IS NULL/IS NOT NULL operators work, or how primary, unique, and foreign key constraints work with respect to NULLs.

DISTINCT Clause in Aggregate Functions (Implemented)

This feature will allow for distinct COUNT, SUM, RUNSUM, AVG, STDDEV, and LIST operations. This is useful for queries like the following when you have INNER JOINs that cause outer rows to be duplicated, but you still need to have a unique count, sum, etc.:

SELECT COUNT(DISTINCT Customer.CustNo), SUM(Orders.ItemsTotal)
FROM Customer
INNER JOIN Orders ON Customer.CustNo=Orders.CustNo

Faster REPAIR TABLE and New VERIFY TABLE (Implemented)

This feature will allow the REPAIR TABLE and new VERIFY TABLE statements to operate much more quickly than they do currently. In addition, you will be able to specify what level of repair or verification should occur - structural only, or both structural and data contents. The logging and error reporting will be much more detailed than what currently exists.

User-Defined Functions in Column Expressions (Implemented)

This feature will allow the use of UDFs in column expressions such as computed/generated expressions and default expressions. You still will not be able to use sub-queries in these expressions, but you can use SQL statements and cursors in the UDFs themselves.

IMPORT/EXPORT Table ANSI/Unicode Formats (Implemented)

This feature will allow the use of ANSI or UNICODE specifiers in the IMPORT TABLE and EXPORT TABLE SQL statements so that you can import and export ANSI tables to/from Unicode, and vice-versa. For Unicode imports, the IMPORT TABLE statement is BOM-sensitive (Byte Order Mark), and can auto-identify an incoming text file as Unicode if it has a BOM prefix.

EMPTY TABLE Statement (Implemented)

This feature introduces a new EMPTY TABLE statement that will allow you to very quickly truncate a table so that it is completely empty. There will also be an optional clause that allows you to specify that any constraints should be ignored. By default, the constraints will be checked before the empty operation takes place.

BEGIN..FINALLY Statement (Implemented)

The SQL/PSM will include a FINALLY statement can designate a block of SQL/PSM code that is always executed, even if an exception occurs somewhere inside the BEGIN..FINALLY statement block.

Exclusive Engine File Access (Implemented)

The engine will be able to be configured so that it can open all files exclusively, and handle all locking internally. This is useful for situations where only one process needs access to a configuration/databases, as is the case with the ElevateDB Server. This mode will also be improved upon as things progress, and will present the fastest performance one can get from the engine.

Dynamic Column References in SQL/PSM Statements (Implemented)

This feature allows you to use two new functions to reference columns for INSERT, UPDATE, and FETCH SQL/PSM statements: COLUMNBYNAME() and COLUMNBYINDEX(). This will permit the use of column names that are passed in to a procedure/function to dynamically fetch and insert/update rows. This will be a breaking change.

Binary GUID Column Storage (Implemented)

Currently GUID columns are stored as CHAR() columns that are 40 characters in length. The new GUID storage in 2.04 will allow GUIDs to be stored in 16 bytes. Existing tables will continue to use the old storage, and only newly-created GUID columns in 2.04 will use the new storage.

CURRENT_COMPUTER() Function (Implemented)

This function will return the current computer name (if applicable to the OS).

Faster Highly-Selective WHERE Clauses with Sensitive Result Sets (Implemented)

ElevateDB 2.04 will generate and navigate sensitive result sets very quickly, even if the number of selected rows is very small and the size of the table is very large. As it is now, this will cause excess navigation and possible performance issues.

More Control Over Sessions in the ElevateDB Server (Implemented)

ElevateDB 2.04 will allow for quicker disconnection and removal of sessions on the ElevateDB Server, even if they are currently in the middle of executing a long operation.

Configuration and Database Catalog Changes

---

2.04 will include several configuration and catalog format changes, such as per-object XML storage for application-specific information, version numbers for views, procedures/functions, in addition to tables, password change date/times for users, and new registration/unregistration methods for external modules.

VCL Changes

---

TEDBEngine StoreActive Property (Implemented)

There is a new TEDBEngine StoreActive property that allows you to specify whether ElevateDB should store the design-time value of the TEDBEngine Active property at runtime, or whether it should always be turned off at runtime. This can help avoid issues where tables are left open at design-time, and cause embarrassing exceptions when the application is deployed.

.NET Data Provider Changes

---

New EDBEngine Class (Implemented)

There will be a new EDBEngine class in the .NET Data Provider namespace that will allow the configuration of the ElevateDB engine as a client or server under .NET, as well as all of the configuration options currently present in the VCL TEDBEngine component.

Miscellaneous Changes

---

These changes are minor, or in some cases, were pushed forward into 2.03, so they are already implemented even though they were originally scheduled for 2.04.

Checksums for Table Structures (Implemented)

This feature is in 2.03 and allows you to move table files between different databases and still have them work properly as long as a checksum of the basic table structure (columns and indexes) matches the same checksum that is calculated and stored in the database catalog.

Catalog Version in the Databases Table (Implemented)

There will be a CatalogVersion column in the Databases table that indicates the minor release number of the last instance of ElevateDB to write out the database catalog.

Parameters in Derived Views (Implemented)

This feature is in 2.03 and allows you to use parameters in derived tables.

As always, feel free to comment with any questions that you may have.

---

Tags: Technical Article, ElevateDB

Permanent Link

Comments (5)	You must be logged in to comment
Tom van der Vlugt said...	Reply
I've just received my license for ElevateDB VCL Standard. This is great stuff. The new SQL features you mentioned are very interesting. I believe that this engine will outperform other DB systems regarding SQL possibilities!
David Cornelius said...	Reply
It looks like Roy Lambert will finally get his wish on NULL strings!   I like the concept of "virtual configurations" and many other features I see.  I just wish I had waited to implement GUIDs in a major database upgrade a while back so I could take advantage of the binary GUID fields. Nice work, Tim!
Tim Young [Elevate Software] said...	Reply
Tom - I'll be also adding your suggestion about the constraint error messages since minor releases are where the catalog changes take place, and this is a good opportunity to put such a feature in place.  We've had quite a few customers asking for such a feature for a while now.
Tim Young [Elevate Software] said...	Reply
David - re: NULLs, close, but not quite what Roy had in mind.  It won't treat empty strings as non-NULL in row values for the Delphi components, only for SQL.  And, as noted, it doesn't affect the behavior of constraints, etc. As for the GUIDs, the new ALTER TABLE performance improvements that went in late in 2.03 may help you put another upgrade out with a little less issue.  The new ALTER TABLE is much smarter, and can crank through a *lot* of rows very quickly, especially if you're only altering one or two indexed (or key) columns in a table.
GReg said...	Reply
Tim, if it will be possible please consider extending CONTAINS functionality to allow fast text searching in all indexed fields. Example: SELECT * FROM Customer WHERE * CONTAINS 'Text'