Tim,

I would love to see a DBISAM 5 wihout the statistic stuff enabling speed
beyond 1 million records.

Arnd

Tim Young [Elevate Software] wrote:

> Matthew,
>
> << Can I propose DBISAM 5 please? Seriously. But not straight away. I've
> been reading some of the blogs from Codegear on the Unicode Tiburon coming
> down the tracks and it struck me that while I have ElevateDB, I have a lot
> more experience with DBISAM (1, 3 & now 4). I'll be needing DBISAM for the
> Unicode compatibility, and for the Win64 support when that comes too. But it
> would be unreasonable to ask for this to be added to DBISAM 4 since that is
> in a cooling period for you. What I'd like therefore it to be able to pay
> for an upgrade to a version 5 of DBISAM, the only new features of which
> would be to support whatever CodeGear give us in the next year or two. It's
> only fair that you get financial reward for the effort needed. Those who
> don't want it can stick on v4. Those who want the full new features and a
> more advanced database will get it with ElevateDB. >>
>
> I've thought about it, mainly because some customers are having a heck of a
> time getting their heads around EDB.  However, every time I start looking
> into it further it starts to turn into EDB again very quickly.  Once you
> start having to put in Unicode support and the other types of architecture
> changes, you start getting into some serious amounts of work, most of which
> has already been done with EDB.  The real issue is that I don't think that
> replicating a large chunk of the work done for EDB in a DBISAM 5 is worth
> the effort.  I could be wrong, though.
>

Arnd Baranowski

>I would love to see a DBISAM 5 wihout the statistic stuff enabling speed
>beyond 1 million records.

It would be even better if we could enable statistics "on" or "off" when creating tables. Sometimes I need statistics for an accurate scrollbar position,
but in most cases I need only speed. Other wishes:

- a fast repair table;
- 2 levels of table encryption (weak and strong);
- Calculated Fields on Server Side;

Mauricio Campana Nonino
Nonino Software

Arnd,

<< I would love to see a DBISAM 5 wihout the statistic stuff enabling speed
beyond 1 million records. >>

You would basically have EDB's indexes at that point.

--
Tim Young
Elevate Software
www.elevatesoft.com

Mauricio,

<< - a fast repair table; >>

The repair table functionality in EDB is very fast, and it can be aborted,
if necessary.

<< - Calculated Fields on Server Side; >>

EDB has this with the COMPUTED columns.

--
Tim Young
Elevate Software
www.elevatesoft.com

Matthew,

<< Thanks for that. It seems to have moved a little closer to the MS SQL
Server style of doing things - that is, more complicated. >>

It's really not that complicated at all, it's just different.  For example,
getting most of the system information out of the system is as simple as
executing a query or opening a table and setting a filter, things that you
know very well:

http://www.elevatesoft.com/scripts/manual.dll?action=mancat&id=edb1sql&category=3

You can even retrieve a list of backups and session/lock information this
way.  And if you need to see if any changes have occurred, you just Refresh
the table or query.

Then there's all of the stuff that you don't see mentioned much anywhere:

- Being able to refresh insensitive (static) result sets when changes are
made to the source tables/views.
- The SELECT..RANGE clause and the power of incremental result set
population for large insensitive result sets.
- Being able to perform "lookups" in SELECT column expressions via
sub-queries.
- The dynamic SQL in the scripts/jobs/procedures/functions that allows for
very rich SQL programming such as cross-tab generation without having to
write a single line of SQL code.
- True INTERVAL support for date/time calculations.
- GENERATED and COMPUTED columns that can automatically generate their
content from expressions, and can also be indexed.

and much more.

Then there's all of the issues with DBISAM that are fixed:

- Being able to truly switch from local to remote with only one property
change.
- Being able to specify accent-sensitivity with collations.
- Being able to specify a collation per column.
- Having to use UPPER and LOWER for case-insensitivity in comparisons - EDB
is much more elegant in this respect.
- Better and faster filter and range processing.
- OnFilterRecord allows for incremental filtering *in sorted order* and
doesn't require the entire table to be scanned.
- Better space usage for columns with DECIMAL types.
- Being able to configure whether you see "row has changed" warnings or not,
and more accurate row change detection that includes BLOBs (unlike DBISAM).
- Better space usage for BLOBs that eliminates the potential for "row has
changed" warnings with BLOB reads.
- Better progress updating based upon time, not the size of the source
operation.
- True separation of the concept of indexes and constraints.
- Much faster text indexing with more features, including the ability to
filter the text being indexed based upon another column.
- Native procedures/functions that don't require modifying the server, yet
still are very simple to code as an event handler, just like with DBISAM.
- Much higher limits on the number of indexes allowed in a table.
- Faster indexes that don't slowly degrade in performance when the number of
rows starts hitting the millions.
- Index compression is dynamic and doesn't require a complete
compress/decompress cycle when reading and writing index pages.
- More complete client-side caching of rows for remote sessions, whereas
DBISAM could not navigate the row cache bi-directionally, EDB can.
- Complete expression support for default column values instead of just
simple values.

That's just off the top of my head.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

yes and it would be great because we could use DBISAM a little longer.
Moving our system from DBISAM to EDB will require several month (may be
up to a year) including lots of confirmations of our customers that
everything works as it should.

Arnd

Tim Young [Elevate Software] wrote:

> Arnd,
>
> << I would love to see a DBISAM 5 wihout the statistic stuff enabling speed
> beyond 1 million records. >>
>
> You would basically have EDB's indexes at that point.
>

Tim

>It's really not that complicated at all, it's just different. For example,
>getting most of the system information out of the system is as simple as
>executing a query or opening a table and setting a filter, things that you
>know very well:

This is where I have to really disagree with you. It is complicated, or at the very least more difficult, in several areas by comparison with DBISAM. I can illustrate that by this bit of sql:

DBISAM
SELECT INTO "Memory\XX12"
FALSE AS _Selected,
_fkContacts,
_fkCompanies,
_Surname,
_Forename,
(_Forename+'' ''+_Surname) AS _xContactName,
IF(_EMail <> '',_EMail,_HomeEMail) AS _xEMail,
_EMail,
_HomeEMail,
_JobTitle,
_Name AS _CompanyName,
_Left,
_NoEshots
FROM "Contacts T0
JOIN Career ON Career._fkContacts = _ID
JOIN Companies ON Companies._ID = Career._fkCompanies
ORDER BY _Surname, _Forename

becomes

ElevateDB
SCRIPT
BEGIN

DECLARE tblCursor CURSOR FOR tbls;
PREPARE tbls FROM 'SELECT * FROM Information.Tables WHERE TableName = ''XX12''';
OPEN tblCursor;
IF (ROWCOUNT(tblCursor) > 0) THEN
 EXECUTE IMMEDIATE 'DROP TABLE "XX12"';
END IF;

EXECUTE IMMEDIATE 'CREATE TABLE XX12 AS
SELECT
FALSE AS _Selected,
_fkContacts,
_fkCompanies,
_Surname,
_Forename,
(_Forename+'' ''+_Surname) AS _xContactName,
IF(_EMail <> '',_EMail,_HomeEMail) AS _xEMail,
_EMail,
_HomeEMail,
_JobTitle,
_Name AS _CompanyName,
_Left,
_NoEshots
FROM "DiskDir".Contacts T0
JOIN "DiskDir".Career ON Career._fkContacts = _ID
JOIN "DiskDir".Companies ON Companies._ID = Career._fkCompanies')
WITH DATA';

EXECUTE IMMEDIATE 'CREATE INDEX Sorted ON XX12 (_Surname,_Forename)';

I think they're both right, but have just been typed in. They both do the same job, and once set up and just run there's no difference but for me the ElevateDB version is more complex.

>You can even retrieve a list of backups and session/lock information this
>way. And if you need to see if any changes have occurred, you just Refresh
>the table or query.

Some of this is restricted to C/S isn't it?

>Then there's all of the stuff that you don't see mentioned much anywhere:
>
>- Being able to refresh insensitive (static) result sets when changes are
>made to the source tables/views.

Don't know how I'd do this.

>- The SELECT..RANGE clause and the power of incremental result set
>population for large insensitive result sets.

This one looks good if I ever get any that large

>- Being able to perform "lookups" in SELECT column expressions via
>sub-queries.

OK

>- The dynamic SQL in the scripts/jobs/procedures/functions that allows for
>very rich SQL programming such as cross-tab generation without having to
>write a single line of SQL code.

Can you give an example because what you've written doesn't make sense to me.

>- True INTERVAL support for date/time calculations.

Arrgh - I'm sure I'll get used to them

>- GENERATED and COMPUTED columns that can automatically generate their
>content from expressions, and can also be indexed.

Brill



>Then there's all of the issues with DBISAM that are fixed:
>
>- Being able to truly switch from local to remote with only one property
>change.
>- Being able to specify accent-sensitivity with collations.
>- Being able to specify a collation per column.

Don't care about the above.

>- Having to use UPPER and LOWER for case-insensitivity in comparisons - EDB
>is much more elegant in this respect.

On balance I agree with you.

>- Better and faster filter and range processing.

Hadn't noticed, but then I don't have mega sized tables.

>- OnFilterRecord allows for incremental filtering *in sorted order* and
>doesn't require the entire table to be scanned.

OnFilterRecord - wash your moth out

>- Better space usage for columns with DECIMAL types.

I'll take your word for it

>- Being able to configure whether you see "row has changed" warnings or not,
>and more accurate row change detection that includes BLOBs (unlike DBISAM).

And my 8708 eater has been retired!

>- Better space usage for BLOBs that eliminates the potential for "row has
>changed" warnings with BLOB reads.

Again I'll take your word for it

>- Better progress updating based upon time, not the size of the source
>operation.

Not sure it makes a great deal of difference - if I want to report each 1% step does it matter which way it works? This isn't knocking ElevateDB I'm genuinely interested.

>- True separation of the concept of indexes and constraints.

I think I can see a benefit, but I won't be using RI (unless there's an easy switch to turn it on and off) and I haven't looked at other uses yet.

>- Much faster text indexing with more features, including the ability to
>filter the text being indexed based upon another column.

Yes! I like this one very much, but as you know I'd have liked to keep the ability to use it even on non-indexed columns (and yes I know it would be deadly slow).

>- Native procedures/functions that don't require modifying the server, yet
>still are very simple to code as an event handler, just like with DBISAM.

OK

>- Much higher limits on the number of indexes allowed in a table.

I never hit the limits with DBISAM

>- Faster indexes that don't slowly degrade in performance when the number of
>rows starts hitting the millions.

Very nice for those with big tables (the braggarts)

>- Index compression is dynamic and doesn't require a complete
>compress/decompress cycle when reading and writing index pages.

Good - one less decision for me to make

>- More complete client-side caching of rows for remote sessions, whereas
>DBISAM could not navigate the row cache bi-directionally, EDB can.

I'm stuck in fileserver.

>- Complete expression support for default column values instead of just
>simple values.

Not really used yet but I think I will be

Roy,

<< This is where I have to really disagree with you. It is complicated, or
at the very least more difficult, in several areas by comparison with
DBISAM. I can illustrate that by this bit of sql: >>

I really don't care to re-hash these same issues again.  You have 3 major
issues with EDB:

1) NULLs
2) IF [NOT] EXISTS
3) INTO

and I'm well aware of them.  However, every single one is due to an issue
with *DBISAM*, not EDB.  DBISAM is using a non-standard implementation or
SQL syntax, and EDB is using the correct standard one.  The fact that they
are "complicated" is only because they are new and different ways of doing
the same thing.  I hardly consider 5-10 extra lines of SQL to be that much
more complex.  Also, you're showing a table-specific version of the generic
script that I worked up for you that will do an INTO for *any* query with a
few simple parameters.  IOW, 5-10 extra lines of SQL code for *every* INTO
in your application:

SCRIPT (IN SQLStatement VARCHAR, IN TableName VARCHAR, IN OrderBy VARCHAR)
BEGIN
  DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt;
  DECLARE ResultCursor SENSITIVE CURSOR WITH RETURN FOR ResultStmt;

  PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name=?';
  OPEN InfoCursor;

  IF (ROWCOUNT(InfoCursor) > 0) THEN
     EXECUTE IMMEDIATE 'DROP TABLE "'+TableName+'"';
  END IF;

  CLOSE InfoCursor;

  PREPARE ResultStmt FROM 'CREATE TABLE "'+TableName+'" AS '+SQLStatement+'
WITH DATA';
  EXECUTE ResultStmt;

  PREPARE ResultStmt FROM 'CREATE INDEX OrderBy ON "'+TableName+'"
('+OrderBy+')';
  EXECUTE ResultStmt;

  PREPARE ResultStmt FROM 'SELECT * FROM "'+TableName+'" ORDER BY
'+OrderBy;
  OPEN ResultCursor;
END

This could be expanded to automatically create temporary tables instead, or
to create them in a special in-memory database, etc.

<< Some of this is restricted to C/S isn't it? >>

Yes, the session and lock information is C/S only, but it hardly detracts
from the fact that it is still a feature that DBISAM doesn't have at all.

<< Don't know how I'd do this. >>

Call the TEDBQuery, TEDBStoredProc, or TEDBScript Refresh method.   That's
it.

<< This one looks good if I ever get any that large >>

It works with small tables, also.  Any usage at all buys you performance
improvements for those situations where you need incremental or paginated
views of the result set.

<< Can you give an example because what you've written doesn't make sense to
me. >>

The SQL scripts in DBISAM can't possibly do something as complicated as
cross-tab result set generation.  EDB can do it with a script and a few
simple parameters with no problem.

<< Arrgh - I'm sure I'll get used to them >>

Well, if you even attempt to argue that the way DBISAM handles it is better,
I'll know you're lying.  DBISAM's implementation of date/time
calculations and units of measure is sorely lacking.   You have days and
milliseconds, and that is it.  To get something as simple as the difference
between two times in minutes requires all sorts of embedded division
operations.

<< Don't care about the above. >>

I'm sure you don't, but a *lot* of DBISAM customers have issues with
accent-insensitivity (the default in DBISAM).

<< Hadn't noticed, but then I don't have mega sized tables. >>

<< OnFilterRecord - wash your moth out >>

You'd be surprised how many people use it, and the expectations are that it
works like EDB, not DBISAM.

<< I'll take your word for it >>

34 bytes (DBISAM) vs. 8 bytes (EDB).

<< Not sure it makes a great deal of difference - if I want to report each
1% step does it matter which way it works? This isn't knocking ElevateDB I'm
genuinely interested. >>

Sure, the difference is between whether the OnProgress is fired in order to
allow for application messages to be processed in the main thread or not,
which can affect the UI responsiveness during long operations.  DBISAM goes
away and does not come back until the next 1% (or whatever) is done
according to the ProgressSteps setting.  With EDB, you're guaranteed to get
a progress message every X number of milliseconds.

<< Good - one less decision for me to make >>

Exactly, I have had a lot of support inquiries with DBISAM regarding the
proper settings.

--
Tim Young
Elevate Software
www.elevatesoft.com

Roy,

The thing with the Null's is that several, such as MySQL and Oracle, a Null
is not the same thing as an empty string.  I think this is actually default
database behaviour.

I actually prefer having them different, although it does add some thought
to the code.

If I add a column and want to then go back through the old records that were
made prior to the column, I can then search for NULL and deal with them as
necessary.  Granted, I could just as easily search for an empty string...
however...   It may be a field that when empty, would be a correct value.

Lance





"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:F3117125-7027-4E5C-813A-2C354BDB60D6@news.elevatesoft.com...
> Matthew
>
>
> Documentation is pretty good. Its been beefed up on the differences with
> DBISAM which I think helps.
>
> SQL for the catalog (and the catalog itself) isn't bad especially now Tim
> has built the history function in so you can capture the sql output of
> using his ui for table creation etc. I switched to using sql for creating
> tables indices etc some time back so that helps. Debugging scripts is fun
> though.
>
> The biggest problems for me have been the "lost toys".
>
> First let me emphasise that I'm not passing any judgements either way
> about these issues. The fact that I'd like to introduce a blunt object to
> the standard setters heads is totally immaterial
>
> NULL = ''
> With ElevateDB null is no longer the same as an empty string. I'm altering
> any instances of flield.AsString := '' to field.Clear also setting up a
> before post trigger to ensure any where users delete the contents of a
> varchar or char field it gets set to null. Finally checking all my sql for
> = '' test and altering to = NULL. Its a lot of work and checking (not done
> yet) but with the exception of my query generator where I'll have to use
> both forms of test (= '' for string fields and IS NULL for all others)
> that's all it is.
>
> There are implications for calculations (I think V4 does it "correctly")
> and joining fields in sql. I posted a reference to Firebirds 51 page
> manual if you want to do further research.
>
> Truncating assignments to fit VARCHARs
> Since I rarely use sql for data input update and Delphi still trims fields
> this isn't a significant problem, as long as CodeGear don't change their
> code. At some point I may add tests for length of data before its applied
> to a varchar field but the effort seems enormous.
>
> Editable canned queries
> I'm switching to in-memory tables. Temporary tables would be another
> alternative but they appeared after I started the conversion and an
> in-memory table has the benefit of guaranteed cleanup even in the event of
> a crash.
>
> SELECT INTO
> Replaced by CREATE TABLE AS. The two main issues are that you will have to
> deal with ensuring the table to be created doesn't already exist (select
> into zapped it for you) and that the ORDER BY clause isn't permitted in
> the select clause so you have to create any ordering you want. The final
> bit is the difference in the way DBISAM and ElevateDB treat in-memory
> tables. With DBISAM you set the query's database to the disk based tables
> and specified memory for the destination in-memory table. With ElevateDB
> its the other way round you set the query's database to the memory
> database and specify the database for the disk based tables.
>
> TEXTSEARCH
> Almost replaced by CONTAINS. The lacking bit is that CONTAINS only works
> on indexed fields. I have it planned to write my own TEXTSEARCH
> replacement for non-indexed columns.
>
> Simple Scripts
> I'll be converting these to a mixture of edbscripts, pascal plus sql and
> simple looping round a ; separated list throwing it at a query which is
> what DBISAM does
>
> REDEFINE
> Almost replaced by ALTER. I'm hoping (but doubtful) Tim will deliver the
> missing ability to rename a column.
>
> IF EXISTS
> This is lost, gone forever, and much lamented. There are ways round it
> using scripts but to me they all feel clumsier.
>
> If I had to sum it up for me from my experience so far it would be DBISAM
> simple, well known and easy to use ElevateDB more powerful, faster (for me
> one of the drivers for changing is the speed improvement in full text
> indexing) but feels more clumsy and more work to achieve things. The
> latter will ease as ElevateDB becomes more familiar but my current belief
> is that for the things I'm used to using ElevateDB will in several cases
> take more typing than DBISAM. For others (and me in the future) the
> situation may be reversed.
>
> Overall I still think I made the right decision (to convert) but I've lost
> count of the times I've thought about just switching back. My advice to
> anyone converting an app would be - look at your code, decide which of the
> DBISAM goodies you have used and how they should be handled in DBISAM then
> write little test apps for test in EDBManager (by then Tim might have the
> debugger written .
>
> Finally before I started converting I wrote a little app with ElevateDB
> from scratch. After overcoming the "catalog, what is a catalog" problem
> and how to point at my tables (look at Tim's CD collector example) it was
> easy.
>
> Sorry for the long rambling post and I know its no dummy's guide but I
> hope it helps.
>
> Roy Lambert
>

Tim and others

I am DBISAM developer and I have some things to improve DBISAM but...

I understand completelly that maintain two database engines with different
features is a nightmare.

Nobody likes to change some lines of code (me too) but we have to understand
the situation.

We need to plan to change from DBISAM to ElevateDB as soon as possible even
if takes a year or so and stop to ask Tim to improve DBISAM.

Tim needs to concentrate his efforts in only one product. ElevateSoft has
based the main idea from DBISAM (fast, stable, embedded, etc) and agregate a
lot of new features. DBISAM is about 10 years and we must understand that
source code was changed and changed a lot since. The market is moving and
DBISAM can´t receive new features easyly. ElevateDB is being build to grow
much more than DBISAM.

As a developer I understand 100% that build another product is a way to
follow.

Eduardo