At 12:55 PM 14/03/2003 +0000, you wrote:

>Like many people I am looking to use Phoenix

Firebird! (the phoenix is the logo)

> as a high powered
>replacemnt for MySQL. By and large this is easy as {{{Phoenix}}}
>functionality tends to superseed that of MySQL. But I would like to
>make a couple of observations:
>
>1) MySQL users (and MSACCESS and many other db users) are used to
>having an auto-increment column to generate a unique index number.
>Phoenix uses 'generators for this, and I would agree that this is an
>improved technique. BUT, it does make porting a pain! Suppose Phoenix
>added an auto-increment column type that was an alias for an integer
>field whose default value is set to a generator name called <table-
>name>_DefGen. Perhaps it is possible to do something similar?

You're moving from non-transactional desktop databases to
client/server. You'll get to love generators.

Porting is pretty easy. Just make the autoinc column a numeric(18,0) and
pump the existing integers into it. Creating the generators in bulk is not
much of a pain if you do them in a DDL script (the recommended way to build
metadata in C/S). They are not linked to their columns by any sort of
dependency so you do this with a copy-and-paste editor after you've pumped
your data.

You can do similarly with the trigger you write to make the column
auto-incrementing. Just create a template for the trigger and swap in the
table, column and generator names:

set term ^;
create trigger bi_atable for atable
active before insert position 0
as
begin
if (new.idcol is null) then
new.idcol = gen_id (gen_id_atable, 1);
end ^
set term ; ^

Once you have all the ducks lined up up, you can write a little script to
set all of the generators to the MAX() value of the columns they are going
to populate.

set generator gen_id_table to 99999;
....

>2) MySQL has 'heap' table types, which are tables with reduced
>functionality which are stored in memory. They are probably needed
>less in Phoenix as MySQL users mostly use them for temporary tables
>to overcome the lack of a sub-select capability. **BUT** I do have
>real applications for these tables, that is data that is frequently
>modified but does not need to be persistent. Is there any way to
>vreate tables in memory?

That's not how you do it in a multi-user database with remote
clients. It's a very bad idea to be creating metadata whilst multiple
transactions are in process, i.e. whilst users are logged in.

Create the structures in your metadata and use a unique key when inserting
the sets of rows required by your app. Then have your app operate on just
the set it created. The tables can be "house-cleaned" as part of regular
maintenance.

>I suppose one could use afile on a ramdisk,
>but in that case it would not be possible to form queries which span
>tables on both disk based files and ramdisk based files, or am I
>wrong here?

Wrong. Firebird doesn't have physical files to store tables as MySQL,
Access, etc. do. All data are stored in pages, which are distributed
around the disk structure of the database in more or less random
order. The filesystem of your operating system knows nothing about tables,
indexes, etc. at all.

You'll have little or no need for temporary structures in Firebird, as you
will find when you get acquainted with stored procedures and views...it's
quite another way to look at database management.

heLen