I find it odd (annoying?) that if I want to create a memory table, I have to create another database for my application and specify create "In Memory" instead of "On Disk". My application then uses 2 databases and has 2 TEDBDatabase components.

My Delphi database utility routines that work on disk based tables, now have to be passed the TEDBDatabase component so it finds either the disk based database or the memory database.

This makes for a lot more coding in Delphi and a greater chance to slip up (default to the wrong database).

1) What's the reason for having to create another database for memory tables? Why can't memory tables be created in the same disk based database? That's the way MySQL does it, and I think DBISAM does it that way too, as well as other 3rd party databases.

2) I assume since I defined the memory database under the same EDBMgr session that has my disk based database, I can use the same EDBSession1 component for both the memory database and disk based database. Correct? So a transaction would roll back changes performed to tables in either database?

TIA

Barry

Barry


>I find it odd (annoying?) that if I want to create a memory table, I have to create another database for my application and specify create "In Memory" instead of "On Disk". My application then uses 2 databases and has 2 TEDBDatabase components.
>
>My Delphi database utility routines that work on disk based tables, now have to be passed the TEDBDatabase component so it finds either the disk based database or the memory database.
>
>This makes for a lot more coding in Delphi and a greater chance to slip up (default to the wrong database).

You could use temporary tables instead. I've stuck with memory tables because they clean up after themselves in the event of a crash (I don't get many but just in case). Temporary tables clean up if the app closes properly.

>1) What's the reason for having to create another database for memory tables? Why can't memory tables be created in the same disk based database? That's the way MySQL does it, and I think DBISAM does it that way too, as well as other 3rd party databases.

Whilst I don't know I think there are three possible reasons:

1. In DBISAM Tim was asked for this facility because someone wanted to use different directories rather than different tablenames to separate things. That way he could keep table names the same. My memory may be wrong though or I may simply not be aware, and there may have been hundreds of very valid requests for the feature.

2. It probably made it easier for development since there's no magic destination and all the code needs to do is say point at this disk system not that disk system

3. By doing it that way all commands/syntax are the same across memory and disk based databases

It may also be that it aided in compliance with the SQL spec. Dunno.

>2) I assume since I defined the memory database under the same EDBMgr session that has my disk based database, I can use the same EDBSession1 component for both the memory database and disk based database. Correct? So a transaction would roll back changes performed to tables in either database?

You should be right but I've not tried rolling a mixed database back, however, I certainly use one session, one disk based and one memory based database.

Roy Lambert [Team Elevate]

Roy,

Thanks for the reply. They are all good reasons, but there may be a better solution.

MySQL has several different table engines: MyISAM, InnoDb, Memory, Berkley and you could intermix tables of different engines in the same database. Their solution was to define a default engine type, like MyISAM so if you don't specify an engine type when creating a table, the table uses the default engine type.

The same thing could have been accomplished with EDB. If the developer wanted a Memory database, he would set the default engine to Memory. All tables would be implicitly created in memory. But disk based tables could also be created if it was explicitly defined in the "Create Table ... Engine=Disk" statement.

Same when defining a disk based database. Default the engine type to "Disk", but allow the developer to explicitly specify "Create table ... Engine=Memory" when needed.

I think this is the best of both worlds.

Barry

Barry

>MySQL has several different table engines: MyISAM, InnoDb, Memory, Berkley and you could intermix tables of different engines in the same database. Their solution was to define a default engine type, like MyISAM so if you don't specify an engine type when creating a table, the table uses the default engine type.

1. That sounds scary. Do you really mean different engines? What would happen if you tried to open a table with a different engine? Does using different engines increase code size?

2. If you want in ElevateDB then at run time you can set the properties for Session and Database (the defaults) and these will be used if the session and database for a table or query aren't explicitly set. Table creation does have to be carried out within the context of its session/database though.

>The same thing could have been accomplished with EDB. If the developer wanted a Memory database, he would set the default engine to Memory. All tables would be implicitly created in memory. But disk based tables could also be created if it was explicitly defined in the "Create Table ... Engine=Disk" statement.

>Same when defining a disk based database. Default the engine type to "Disk", but allow the developer to explicitly specify "Create table ... Engine=Memory" when needed.

The difference there seems to be that you can specify in the SQL statement rather than the components. Is that right?

>I think this is the best of both worlds.

Well it might be if you believe that SQL is the answer to everything I don't and the thought of having to look through potentially hundreds of SQL snippits to find out where something is created rather than looking at a few components in the IDE makes me shudder.

I've never used MySQL so I don't know just what it does. What you're describing may be absolutely brilliant but from the description so far I'm not convinced.

Roy

Roy Lambert wrote:

>>MySQL has several different table engines: MyISAM, InnoDb, Memory, Berkley and you could intermix tables of different engines in the same database. Their solution was to define a default engine type, like MyISAM so if you don't specify an engine type when creating a table, the table uses the default engine type.<<

>1. That sounds scary. Do you really mean different engines? <
Yup. Different engine and different table storage/file structure. 3rd party developers can develop their own engine and plug it into MySQL. Of course the table engine must use the standard MySQL SQL DDL and syntax. It is like a plug and play table engine. Choose the right one for the type of data you are storing.

>What would happen if you tried to open a table with a different engine?<
You can't. The table engine type is stored in the table definition. You use the same SQL to access the table. MySQL knows what engine type it is. Once you create the table with "Create Table ... Engine=InnoDb" and specify the Engine Type, SQL handles the rest. The user doesn't do anything differently. All tables are accessed in the same fashion with the same code. You can also change the engine type with a simple "Alter Table Table1 engine=MyISAM" and it restructures it to the new engine complete with data.

>Does using different engines increase code size?<
No. Same code is used. The engine type is transparent to the program code and SQL.

>The difference there seems to be that you can specify in the SQL statement rather than the components. Is that right?<
Correct. It means no code changes in Delphi to access memory tables or disk tables.

>Well it might be if you believe that SQL is the answer to everything <
Of course it is. If there was a cereal named SQL, I'd be eating it every morning instead of "Alphabits". The cereal would have SQL reserved words in it so I could form SQL statements when working on code over breakfast. LOL

>I don't and the thought of having to look through potentially hundreds of SQL snippits to find out where something is created rather than looking at a few components in the IDE makes me shudder.<
No need to look at SQL snippets. The memory tables are created in the same database. I prefix memory tables names with "mem_" so I know what it is.

>I've never used MySQL so I don't know just what it does. What you're describing may be absolutely brilliant but from the description so far I'm not convinced.<
That's ok. It is just another way of doing things. I've done it that way for years and never had a problem with mixing memory tables and disk based tables in the same database.

I should have my Delphi database routines updated today to handle different databases so I can use memory tables. I just don't like adding more code to my app if I didn't have to, which is why I originally posted here. I thought there had to be an easier way than to segregate memory tables to a separate database. Oh, well. Back to coding.

Barry

Barry

>>>MySQL has several different table engines: MyISAM, InnoDb, Memory, Berkley and you could intermix tables of different engines in the same database. Their solution was to define a default engine type, like MyISAM so if you don't specify an engine type when creating a table, the table uses the default engine type.<<
>
>>1. That sounds scary. Do you really mean different engines? <
>Yup. Different engine and different table storage/file structure. 3rd party developers can develop their own engine and plug it into MySQL. Of course the table engine must use the standard MySQL SQL DDL and syntax. It is like a plug and play table engine. Choose the right one for the type of data you are storing.

Definitely scary. I'm feeling very confused - a database engine (MySQL) allows other DB engines in it - wow.


>>What would happen if you tried to open a table with a different engine?<
>You can't. The table engine type is stored in the table definition. You use the same SQL to access the table. MySQL knows what engine type it is. Once you create the table with "Create Table ... Engine=InnoDb" and specify the Engine Type, SQL handles the rest. The user doesn't do anything differently. All tables are accessed in the same fashion with the same code. You can also change the engine type with a simple "Alter Table Table1 engine=MyISAM" and it restructures it to the new engine complete with data.
>
>>Does using different engines increase code size?<
>No. Same code is used. The engine type is transparent to the program code and SQL.

Nope don't believe it. Either (even as DLLs) there must be more code or you're carrying the baggage of all the engines all the time.

>>The difference there seems to be that you can specify in the SQL statement rather than the components. Is that right?<
>Correct. It means no code changes in Delphi to access memory tables or disk tables.
>
>>Well it might be if you believe that SQL is the answer to everything <
>Of course it is. If there was a cereal named SQL, I'd be eating it every morning instead of "Alphabits". The cereal would have SQL reserved words in it so I could form SQL statements when working on code over breakfast. LOL

OK. I now understand that you're mentally unstable <vbg>

>>I don't and the thought of having to look through potentially hundreds of SQL snippits to find out where something is created rather than looking at a few components in the IDE makes me shudder.<
>No need to look at SQL snippets. The memory tables are created in the same database. I prefix memory tables names with "mem_" so I know what it is.

We have a difference in our definition of the word database. I can understand your definition (I think). You're including any and all tables regardless of location. In ElevateDB terms think Session = Database and Datebase = location <cue mad cackling>

Roy

Barry,

<<
I find it odd (annoying?) that if I want to create a memory table, I have to create another database for my application and specify create "In Memory" instead of "On Disk". My application then uses 2 databases and has 2 TEDBDatabase components.
>>
I believe this is a limitation of the EDB architecture at this point. As i think Roy suggested you can use temporary tables but those are per-session so depending on your need that might work better for you.
Otherwise Tim can advise if he has something planned for this (nothing on roadmap that i can see).

<<
1) What's the reason for having to create another database for memory tables? Why can't memory tables be created in the same disk based database? That's the way MySQL does it, and I think DBISAM does it that way too, as well as other 3rd party databases.
>>
No, DBISAM has a separate database as well (it's called Memory and you can have only one while in EDB you can have as many as you want) so its technically same as DBISAM but more flexible (multiple in memory DBs).

<<
2) I assume since I defined the memory database under the same EDBMgr session that has my disk based database, I can use the same EDBSession1 component for both the memory database and disk based database. Correct? So a transaction would roll back changes performed to tables in either database?
>>
Yes to the same session.
No i believe to the transaction - transactions are database based and not session. You could start separate transactions on both databases and then commit/rollback together.

Raul

Raul

>I find it odd (annoying?) that if I want to create a memory table, I have to create another database for my application and specify create "In Memory" instead of "On Disk". My application then uses 2 databases and has 2 TEDBDatabase components.
>>>
>I believe this is a limitation of the EDB architecture at this point. As i think Roy suggested you can use temporary tables but those are per-session so depending on your need that might work better for you.

Same for memory tables


>No, DBISAM has a separate database as well (it's called Memory and you can have only one while in EDB you can have as many as you want) so its technically same as DBISAM but more flexible (multiple in memory DBs).

I would tend to disagree here. More does not necessarily mean more flexible. I, personally, found the DBISAM syntax for using memory tables more flexible than ElevateDB's in a strange easier to use way which meant I made more use of them. ie flexible in application rather than features.

Roy

Roy,

<< I would tend to disagree here. More does not necessarily mean more
flexible. I, personally, found the DBISAM syntax for using memory tables
more flexible than ElevateDB's in a strange easier to use way which meant I
made more use of them. ie flexible in application rather than features. >>

I don't quite understand this statement, and I've seen others voice the
same.  The *only* difference we're talking about here is a:

MySession.Execute('CREATE DATABASE Memory IN MEMORY');

at application startup.  That's it   No more, no less, and the application
will work *exactly* the same as with DBISAM (4.x).

Tim Young
Elevate Software
www.elevatesoft.com

Barry,

<< 1) What's the reason for having to create another database for memory
tables? Why can't memory tables be created in the same disk based database?
That's the way MySQL does it, and I think DBISAM does it that way too, as
well as other 3rd party databases. >>

No, DBISAM does things just like EDB, except that the "Memory" database is
hard-coded.  If you want to mix temporary tables with normal tables in the
same database, then you should just use CREATE TEMPORARY TABLE to create
your temporary tables.  EDB creates them with special flags that instruct
Windows to keep the table files cached in memory as much as possible, so you
get about the same performance that you would get with in-memory tables.

<< 2) I assume since I defined the memory database under the same EDBMgr
session that has my disk based database, I can use the same EDBSession1
component for both the memory database and disk based database. Correct? So
a transaction would roll back changes performed to tables in either
database? >>

No, transactions are per-database, not per-session.  However, temporary
tables (mentioned above) can be mixed with normal tables in transactions.

If you have any other questions, please let me know.

Tim Young
Elevate Software
www.elevatesoft.com