Tim,

In the upcoming EDB: what will be the SQL standard used: 92, 99, 2003 ?
Do the DBISAM SQL extensions also remain for EDB?

Regards, Harry

Harry,

<< In the upcoming EDB: what will be the SQL standard used: 92, 99, 2003 ?
>>

A subset of  the 2003 standard.  There are certain little things that are
different or are missing, such as only admins can grant privileges and roles
and you can't have them grant the GRANT privilege to anyone else.  Most of
the changes from the standard are due to trying to keep things simpler and
not to over-complicate things too much.  Much of the standard is geared
towards very large database installations with thousands of users, whereas
EDB will (most likely not be used in that manner.

<< Do the DBISAM SQL extensions also remain for EDB? >>

No, most of them have been removed in favor of more standardized methods.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>No, most of them have been removed in favor of more standardized methods.

Interesting bombshell - would you care to say more

Roy Lambert

Roy,

<< Interesting bombshell - would you care to say more >>

Well, for example the IF EXISTS clause is gone - if you want to find out if
a table, column, etc. exists, you just query for it:

IF EXISTS(SELECT * FROM Information.Tables WHERE Name='MyTable') THEN
  <<<< Do something >>>>>

Note: This has changed since my last posts describing the System database.
There is now an Information schema instead for every database that you can
query to find out the desired information.  For example, if you wanted to
query the Information schema for a particular database, you would use this:

SELECT * FROM Memory.Information.Tables WHERE Name='MyTable'

EDB uses the standard 3-part naming standard for table references:

Database.Schema.Table

However, since there are only two schemas in each database (at least
initially), the Information schema and the Default schema, if you don't
specify the Information schema then EDB allows you to skip the "Default"
name and go right to the table name like this:

SELECT * FROM Memory.MyTable WHERE.....

As for other things that have changed, they mostly revolve around feature
changes that don't require those clauses anymore such as joins in UPDATE and
DELETE statements, which aren't required anymore since EDB can do correlated
sub-queries.  My computer's motherboard took a dump yesterday, so I can't
post the SQL syntax diagrams right now because they were on my local drive,
but a new motherboard should be here by Thursday and then I can post the new
EDB SQL syntax so you can see it.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

> As for other things that have changed, they mostly revolve around feature
> changes that don't require those clauses anymore such as joins in UPDATE
> and DELETE statements, which aren't required anymore since EDB can do
> correlated sub-queries.

I just examined and try your UPDATE and DELETE SQL statement, and I think
that JOIN in UPDATE are very helpful for updating multiple columns like

UPDATE Table1 A SET A.Field1 = B.Field1, A.Field2 = B.Field2 and so on
FROM Table1 A INNER JOIN Table2 B ON A.JoinField = B.JoinField

Do you mean that it will be changed to 2003 standard like

UPDATE Table1 A
  SET A.Field1 = (SELECT Field1 FROM Table B WHERE B.JoinField =
A.JoinField),
  SET A.Field2 = (SELECT Field2 FROM Table B WHERE B.JoinField =
A.JoinField),
and so on

?

I think this is where the plus values of DBISAM, I hope I was wrong ...



Surjanto

Tim


An upgrade from DBISAM (any version) to ElevateDB is looking nastier and nastier. What are your plans on

1. Keeping DBISAM going as a product
2. Providing upgrade utilities?

Roy Lambert

Surjanto,

<< Do you mean that it will be changed to 2003 standard like

UPDATE Table1 A
  SET A.Field1 = (SELECT Field1 FROM Table B WHERE B.JoinField =
A.JoinField),
  SET A.Field2 = (SELECT Field2 FROM Table B WHERE B.JoinField =
A.JoinField),
and so on >>

Well, EDB can do the above syntax also, but what I meant was with respect to
a filtered UPDATE like this:

UPDATE Table1 A
SET A.Field1=100
WHERE A.Field2=(SELECT Field2 FROM Table2 B WHERE B.JoinField=A.JoinField)

Correlated sub-queries behave just like joins in EDB, hence they are just as
fast.

--
Tim Young
Elevate Software
www.elevatesoft.com

Roy,

<< An upgrade from DBISAM (any version) to ElevateDB is looking nastier and
nastier. >>

It's really not as bad as you make it.  Once you see the "getting started"
step-by-step info, you'll be shocked as to how similar it is.  It is *very*
simple to use, much simpler than DBISAM in terms of the number of component
properties, methods, etc.  You'll also appreciate some of the new features
very much.  For example, you could probably use this statement very much in
TMan:

CREATE TEXT INDEX <Name>
ON <TableName>
(<ColumnName> [COLLATE <CollationName>])
[DESCRIPTION <Description>]
[INDEXED WORD LENGTH <WordLength>]
[FILTER TYPE COLUMN <ColumnName>]
[WORD GENERATOR <WordGeneratorName>]

Notice the FILTER TYPE COLUMN.  It allows you to specify a column whose
contents are used at runtime to determine which text filter to use to filter
the contents of a CLOB column before it is indexed.  You could have text
filters set up like this:

Name          Type
------------------
UUEFilter    UUE
MimeFilter   Mime

etc. and they will be called as necessary using the type column for every
row as a guide.

<< 1. Keeping DBISAM going as a product >>

As I already mentioned, EDB is a new product and DBISAM will keep on as a
separate product.  The upgrade pricing is more of a discount as opposed to a
normal major upgrade.

<< 2. Providing upgrade utilities? >>

Do you mean in terms of the code and SQL ?  Apart from switching the
component types, I'm not sure what on earth we could do in an "automatic"
fashion that wouldn't involve some serious decision making on your part
also.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


>It's really not as bad as you make it. Once you see the "getting started"
>step-by-step info, you'll be shocked as to how similar it is. It is *very*
>simple to use, much simpler than DBISAM in terms of the number of component
>properties, methods, etc.

Hmmm, I'm sure you're right


>You'll also appreciate some of the new features
>very much. For example, you could probably use this statement very much in
>TMan:
>
>CREATE TEXT INDEX <Name>
>ON <TableName>
>(<ColumnName> [COLLATE <CollationName>])
>[DESCRIPTION <Description>]
>[INDEXED WORD LENGTH <WordLength>]
>[FILTER TYPE COLUMN <ColumnName>]
>[WORD GENERATOR <WordGeneratorName>]

Looks interesting. I'm guessing here about INDEXED WORD LENGTH I thought you were going to have a minimum and a maximum word length?

><< 1. Keeping DBISAM going as a product >>
>
>As I already mentioned, EDB is a new product and DBISAM will keep on as a
>separate product. The upgrade pricing is more of a discount as opposed to a
>normal major upgrade.

Good that's a fallback position at least.

><< 2. Providing upgrade utilities? >>
>
>Do you mean in terms of the code and SQL ? Apart from switching the
>component types, I'm not sure what on earth we could do in an "automatic"
>fashion that wouldn't involve some serious decision making on your part
>also.

What was going through my mind was something to spot all the sql statements that need changing, but as I thought about answering you here my mind came unhinged at trying to figure out what was an sql statement. Anything in a TDBISAMQuery obviously but I have them stored in stringlist containers and in text in the .pas files so eeeek.

Personally I think some of your extensions are better than the standard syntax and it would be worth a rethink about keeping them whatever you do behind the scenes.

Roy Lambert

Roy,

<< Looks interesting. I'm guessing here about INDEXED WORD LENGTH I thought
you were going to have a minimum and a maximum word length? >>

Actually, no, this is just the length of any indexed word, i.e. the engine
will simply truncate any word that is larger than this setting.  The actual
determination of which words to index based upon length is done by the word
generator.  The default word generator has a minimum word length of 3 and a
maximum of 30 and a fixed stop words list of 144 common noise words.  You
can change all of this by writing a custom word generator, which is simply
an external EDB module (DLL) that handles the word generation in a custom
fashion.

<< Good that's a fallback position at least. >>

Well, hopefully not too many will want to "fall back".

<< What was going through my mind was something to spot all the sql
statements that need changing, but as I thought about answering you here my
mind came unhinged at trying to figure out what was an sql statement.
Anything in a TDBISAMQuery obviously but I have them stored in stringlist
containers and in text in the .pas files so eeeek. >>

Yes, this is one of those things that just requires some human interaction.

<< Personally I think some of your extensions are better than the standard
syntax and it would be worth a rethink about keeping them whatever you do
behind the scenes. >>

If you're referring to things like IF EXISTS, then no, I don't agree at all.
It is much more flexible and extensible to be able to query the information
schema to see if something exists than it is to add IF [NOT] EXISTS to every
single SQL DDL statement in the product.  It is also much more powerful,
when combined with the ability in a procedure to make multiple branching
decisions based upon information in the information schema, such as "if the
table version is 2, then do this, else if the table version is 3 and the
number of columns is 4, then do this", etc.  You can very easily create one
procedure that upgrades the structures of all tables, etc. in an entire
database in one shot.  This type of expressive functionality was never
possible with DBISAM's SQL.

--
Tim Young
Elevate Software
www.elevatesoft.com