Subject Re: Upgrading from MySQL
Author rogerirwin2000
--- In, Helen Borrie <helebor@t...> wrote:
> 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)

Right...No more references to the Penguin OS then;-)

> You're moving from non-transactional desktop databases to
> client/server. You'll get to love generators.
Actually the MySQL I am using does have transactions. It does not
have sub-selects and most of all is a long way off Triggers/Stored

> 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:

Now this is the meat! I never thought of doing this. You should add
this to the 'migrating from MySQL FAQ' as this gives the impression
that we would need to modify the Insert query in all aour programs!

Excellent solution!

> >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
> transactions are in process, i.e. whilst users are logged in.
RTFM, I appreciate that having sub queries alleviates the need in
most cases **but** as I said, I have some records that get updated
continuously which simply do not need to be maintained if the
computer goes down. It seems such a shame to work the disks for
nothing, and of course RAM is much faster!

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

Hmmm...Interesting answer. Most modern databases open 'files' as a
means of obtaining an area of diskspace, but then use thier own
pointers within that space to organize tables, indexes etc. In some
cases they can simply take a partion and use a pointer within that

In some cases (Firebird and Access for example), the whole space is a
single block, as it also is in INNODB tables with MySQL. In other
cases (Such as FoxPro and MyISAM tables), tables and indexes are kept
in separate files. The difference is irrelevant, in any case these
files (or file) **could** be copied to a RAMDISK and used from there.
After a powercycle they get recopied (and thus reset to thier
original values).

Of course any layout optimisation that has been done to take
advantage of disk architecture would be wasted, but this is
irrelevant on a ramdisk (you would also want to disable the use of
cache as this is also a waste). Also, I am not sure how much disk
access **is** optimized on geometry, as these days the real geometry
is of ten different from the published ones!

Like I said my **apparent** problem would be to use tables that
reside in two different 'files', as these would be different