<< I do want/need feedback, I just wasn't prepared for it. >>

What I can offer is to give you some of my own design thoughts. I'm not
prepared to involve myself in any heated design discussion, and I've not the
energy to involve myself in any detailed QA activity on a new RDBMS product.

1) Name space / SQL identifiers / Database objects

Since you're introducing catalog support in EDB, I'd take advantage of the
standard 3-part name-spaces/identifiers in SQL:

- Catalog_name
- Schema_name
- Object_name

Example:

MyCatalog.MySchema.MyTable

I'd make the "Catalog" the physical representation of the database, such
that a statement like:

CREATE DATABASE TestDB PATH 'C`:\Data\TestDB';

would create the OS file (folder) representing the physical database (as it
does, only representing a schema as far as I can tell).

The catalog (database) can contain at minimum the INFORMATION schema, or IMO
even better the INFORMATION_SCHEMA, and any number of additional
user-defined schemas.

A user-defined schema may contain zero or any number of database objects,
such as tables, views, indexes, triggers, stored procedures and functions.

Database object definitions should be available in the context of a CREATE
SCHEMA statement and/or as singleton DDL statements.

2) SQL scripts

Multi-statement support is IMO essential for using a RDBMS efficiently, and
would probably be somehting I'd personally miss sorely if I were to use
ElevateDB.

3) SQL/PSM - computational completess

Likewise, I'd miss the option of using procedure language statements (PSM)
directly in the main execution context. The procedure language is what makes
SQL computational complete, like traditional host languages. The ability to
write any kind of SQL statements in multi-statement blocks and execute the
code directly in the main execution context is a powerful and flexible
feature.

4)

As for the SQL syntax/grammar, I think it's just fine that your intention is
to use standard SQL:2003 where appropriate and fill in with vendor
extensions when needed.

Ole Willy Tuv

Ole,

<< 1) Name space / SQL identifiers / Database objects

Since you're introducing catalog support in EDB, I'd take advantage of the
standard 3-part name-spaces/identifiers in SQL:

- Catalog_name
- Schema_name
- Object_name >>

We do allow the 3-part naming currently.  However, we restrict references to
other databases/catalogs to dynamic SQL or jobs.  IOW, you can't reference
another catalog from withinga stored catalog SQL object such as a view or
stored procedure.  Also, we allow for skipping the schema name for the
Default schema (see below).  So, this:

MyDatabase.MyTable

can be interpreted as being a reference to the Default schema for the
MyDatabase database/catalog.  We did this to make things easier on migrating
DBISAM users.

<< The catalog (database) can contain at minimum the INFORMATION schema, or
IMO even better the INFORMATION_SCHEMA, and any number of additional
user-defined schemas. >>

Currently we limit the schemas to two pre-defined schemas, Information and
Default.  My feelings were that adding the schemas layer would be a bit too
unnecessarily complicated for our customer base.

<< Likewise, I'd miss the option of using procedure language statements
(PSM) directly in the main execution context. >>

By "main execution context", do you mean via the TDBISAMQuery component ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< By "main execution context", do you mean via the TDBISAMQuery component ?
>>

Yes, and in similar SQL commands in other client data access libraries (e.g.
ADO.NET), as opposed to specifically call a stored procedure or user-defined
function.

Ole Willy Tuv

Tim,

<< Yes, and in similar SQL commands in other client data access libraries
(e.g. ADO.NET), as opposed to specifically call a stored procedure or
user-defined  function. >>

As an example, here's my script for creating my (infamous) "ATable" test
table with 100000 rows of data in another RDBMS:

drop table if exists ATable;
create table ATable
storage engine 'Variable'
default locale 1033
(
 ID autoinc,
 SField1 char varying(20),
 SField2 char varying(20),
 SField3 char varying(20),
 SField4 char varying(20),
 SField5 char varying(20),
 SField6 char varying(20),
 SField7 char varying(20),
 SField8 char varying(20),
 Stamp timestamp default current_timestamp,
 constraint pk_ATable primary key (ID)
);
create index ix_ATable_SField1 on ATable (SField1);

declare rows integer default 100000;
declare trans integer default 10000;
declare id, t, i integer;
set id = 0;
repeat
 if (rows-id) > trans then
   set t = trans;
 else
   set t = (rows-id);
 end if;
 set i = 0;
 start transaction;
 while i < trans do
   if (id = rows) then
     leave;
   end if;
   set id = id+1;
   insert into ATable values
   (
     default,
     'SField1 row ' || cast(id as varchar(10)),
     'SField2 row ' || cast(id as varchar(10)),
     'SField3 row ' || cast(id as varchar(10)),
     'SField4 row ' || cast(id as varchar(10)),
     'SField5 row ' || cast(id as varchar(10)),
     'SField6 row ' || cast(id as varchar(10)),
     'SField7 row ' || cast(id as varchar(10)),
     'SField8 row ' || cast(id as varchar(10)),
     current_timestamp
   );
   set i = i+1;
 end while;
 commit;
until (id=rows)
end repeat;

Ole

Tim,

<< By "main execution context", do you mean via the TDBISAMQuery component ?
>>

To be clear, I meant the excution context in the SQL-server part itself. How
the statement block is materialized to the SQL engine is less important,
whether the requesting SQL agent is a TEdbQuery (is that the correct
component name ? , an ADO.NET command or whatever client data access
component in question.

Ole Willy Tuv

Ole,

<< To be clear, I meant the excution context in the SQL-server part itself.
How the statement block is materialized to the SQL engine is less important,
whether the requesting SQL agent is a TEdbQuery (is that the correct
component name ? , an ADO.NET command or whatever client data access
component in question. >>

I understand.  This type of processing would have to occur on the server for
C/S access, and locally for local access.  It's a lot different than just a
series of SQL statements like with DBISAM.

--
Tim Young
Elevate Software
www.elevatesoft.com