Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB Public Beta Tests » View Thread |
Messages 1 to 6 of 6 total |
To Tim: Design thoughts |
Tue, Dec 19 2006 3:43 PM | Permanent Link |
"Ole Willy Tuv" | << 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 |
Tue, Dec 19 2006 3:58 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Tue, Dec 19 2006 4:05 PM | Permanent Link |
"Ole Willy Tuv" | 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 |
Tue, Dec 19 2006 4:17 PM | Permanent Link |
"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 |
Tue, Dec 19 2006 7:10 PM | Permanent Link |
"Ole Willy Tuv" | 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 |
Wed, Dec 20 2006 8:34 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |