Hi,

Still new to and evaluating ElevateDB so if these newbie questions are answered elsewhere, please point me to the answers.

What do I need to do to copy tables from one database to another, including indexes and constraints?  The ElevateDB Manager has a "Create copy of table" function, but I don't see any way to create the table copy into a different database here.

Also in the ElevateDB manager, the "Restore Database" section, I feel, has a fairly major shortcoming.  You can't restore tables that don't exist in the selected database unless you check the "Restore Catalog from Backup", and not only does this force a restore of ALL of the tables in the backed up database but it REMOVES all existing tables from that database.  If you don't check the  "Restore Catalog from Backup" and select just the table(s) you want, it comes up with "the table blahblah does not exist in the schema default"  Thats right it doesn't!  I want the restore function to completely restore (or create new if need be)  that table and only that table  (with all of the index definitions), and EleveteDB Manager just won't let me.  

Please don't tell me to cut and paste the SQL for the table and index create options from the old database to the new database since that is a BS solution to something that should be automatic and would require many, many cut and paste operations on say 10 tables with 5 indexes each since, as far as I can tell, you can't have multiple SQL statements in an ElevateDB TEDBQuery or the Manager (please correct me if I'm wrong on this)  

I thought of having two EDBTables, one attached to the source DB and one attached to the target.  That way, I could just open the source table, copy all of the fielddefs and indexdefs to the target and call TableTarget.CreateTable.  Unfortunately EDBTable does not implement the CreateTable procedure defined in the TTable superclass.

At this point I'm left with the prospect of having to programmatically create a query to do the table create, unless someone points me to one function or button in ElevateDB Manager I have overlooked and I can go "D'oh"

Thanks

Kurt Wagner

Kurt

First point is that I am unsure wether you need the capability as part of your design or run time activities. Since you only refer to EDBManager I'm guessing its design time so my answers are mainly focused that way.

>What do I need to do to copy tables from one database to another, including indexes and constraints? The ElevateDB Manager has a "Create copy of table" function, but I don't see any way to create the table copy into a different database here.

As things stand the best approach is to reverse engineer the database and cut'n'paste the parts you want. At run time you have "CREATE TABLE AS" which will copy the table (with or without data) based on a select statement, but currently doesn't include indices/functions etc.

>Also in the ElevateDB manager, the "Restore Database" section, I feel, has a fairly major shortcoming. You can't restore tables that don't exist in the selected database unless you check the "Restore Catalog from Backup", and not only does this force a restore of ALL of the tables in the backed up database but it REMOVES all existing tables from that database. If you don't check the "Restore Catalog from Backup" and select just the table(s) you want, it comes up with "the table blahblah does not exist in the schema default" Thats right it doesn't! I want the restore function to completely restore (or create new if need be) that table and only that table (with all of the index definitions), and EleveteDB Manager just won't let me.

This sounds like you want to use the restore functionality as a method of creating a table in a database when it didn't previously exist there. It won't.

ElevateDB is fundamentally different to DBISAM in that it has a separate catalog. With DBISAM (dBase, Paradox etc) all of the meta information (eg field definitions) is stored in the files that make up the tables; with ElevateDB this is stored separately in a catalog with only the data (including index data) stored in the table files. One effect of this is that it is no longer possible to just copy a table from somewhere else and have it be part of the database which is what you seem to want.

>Please don't tell me to cut and paste the SQL for the table and index create options from the old database to the new database since that is a BS solution to something that should be automatic and would require many, many cut and paste operations on say 10 tables with 5 indexes each since, as far as I can tell, you can't have multiple SQL statements in an ElevateDB TEDBQuery or the Manager (please correct me if I'm wrong on this)

1. OK I won't
2. You're right in that TEDBQuery doesn't support multiple SQL statements but TEDBScript does - it also has a full programming language associated with it.

>I thought of having two EDBTables, one attached to the source DB and one attached to the target. That way, I could just open the source table, copy all of the fielddefs and indexdefs to the target and call TableTarget.CreateTable. Unfortunately EDBTable does not implement the CreateTable procedure defined in the TTable superclass.

Probably because it doesn't inherit from TTable but rather TDataset. All table creation functions have been moved to SQL in ElevateDB

>At this point I'm left with the prospect of having to programmatically create a query to do the table create, unless someone points me to one function or button in ElevateDB Manager I have overlooked and I can go "D'oh"

I don't know of such a button, but I'm continually surprised at what Tim has built in. If this is functionality that you need a lot I'd suggest checking out the reverse engineering code in EDBManager and developing your own script (not a query) to read the catalog and extract the information you want and create the table. All the data you need will be in the Information database tables (they're all covered in in the OLH)

Roy Lambert [Team Elevate]

Ken,

Thanks for the quick and informative response.


> First point is that I am unsure wether you need the capability as part of your design or run time activities. Since you only refer to EDBManager I'm guessing its design time so my answers are mainly focused that way.

Generally true that it is just design time at this point, rearranging tables from various DBs to make more logical sense.  The ability to to so at run time definitely a plus.


>This sounds like you want to use the restore functionality as a method of creating a table in a database when it didn't previously exist there. It won't.

It sure would be great if it did


>ElevateDB is fundamentally different to DBISAM in that it has a separate catalog. With DBISAM (dBase, Paradox etc) all of the meta information (eg field definitions) is stored in the files that make up the tables; with ElevateDB this is stored separately in a catalog with only the data (including index data) stored in the table files. One effect of this is that it is no longer possible to just copy a table from somewhere else and have it be part of the database which is what you seem to want.

While that would be nice, I've accepted the fact that I can't just copy a table in from another folder and have it be recognized as part of the other database.  Being able to ask the EDBManager to do that for me, however, is another story.  Am I the first one who's ever asked for this?  If so, forget about me since there are manual workarounds (the reverse engineering script will work well enough, thanks for that tip).  I just thought it would be a common enough operation to have that function present in the manager.   


>I don't know of such a button, but I'm continually surprised at what Tim has built in. If this is functionality that you need a lot I'd suggest checking out the reverse engineering code in EDBManager and developing your own script (not a query) to read the catalog and extract the information you want and create the table. All the data you need will be in the Information database tables (they're all covered in in the OLH)

Thanks for the tips.  I got almost everything I needed for the create script from the open EDBTable, save for the NOT NULL and DEFAULT value status.  It would be nice if EDBTable published that info.

Thanks again

Kurt Wagner

Kurt

<<I got almost everything I needed for the create script from the open EDBTable, save for the NOT NULL and DEFAULT value status.  It would be nice if EDBTable published that info.>>

This is the wrong way to do it TDataset does not have and can not have all of the information that a table in ElevateDB can have. What you need to do is query the various tables in the Information database this has everything you need. Its a bit of a shift from using tables to using queries but when you're used to it then its a lot better, quicker and easier.

If you look in the extensions newsgroup you'll find a script I wrote to copy functions from the disk bound database to my in-memory database.

It wouldn't take a massive amount of effort to create a script to clone tables between databases, as long as they are within the same session.

If I were to do it I'd start with a CREATE TABLE AS to get the columns and then add the indices, triggers and constraints.

It might be possible to lift it straight from Tim's reverse engineering code - remember you have the source!

Roy Lambert [Team Elevate]

Kurt,

<< While that would be nice, I've accepted the fact that I can't just copy a
table in from another folder and have it be recognized as part of the other
database.  Being able to ask the EDBManager to do that for me, however, is
another story.  Am I the first one who's ever asked for this?  If so, forget
about me since there are manual workarounds (the reverse engineering script
will work well enough, thanks for that tip).  I just thought it would be a
common enough operation to have that function present in the manager. >>

Unfortunately, there's really no way to do so.  The table files themselves
only contain the data and indexes, and don't have any knowledge of a) what
table they are for (apart from their root name) or b) what the structure of
that table is.  The database catalog controls all of this, including whether
or not the table is considered to "exist".

<< Thanks for the tips.  I got almost everything I needed for the create
script from the open EDBTable, save for the NOT NULL and DEFAULT value
status.  It would be nice if EDBTable published that info. >>

If you include the edbreverse.pas unit in your application (in the
\utils\edbmgr\source installation subdirectory), you can just call one of
it's built-in functions to generate the code for you:

uses edbreverse;

function GetTableSQL(const TableName: string): string;
var
  TempReverseEngineer: TEDBReverseEngineer;
begin
  TempReverseEngineer:=TEDBReverseEngineer.Create;
  try
     with TempReverseEngineer do
        begin
        TargetDatabase:=MyEDBDatabase;
        Result:=GetTableSQL(TableName,True,True);
        end;
  finally
     FreeAndNil(TempReverseEngineer);
  end;

The GetTableSQL method looks like this:

function GetTableSQL(const TableName: TEDBString;
                                 IncludeConstraints: Boolean=False;
                                 IncludeForeignKeys: Boolean=False):
TEDBString;

The only other requirement is that the TEDBDatabase that you assign to the
TargetDatabase property must be opened prior to calling the GetTableSQL
method, or any other methods.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

Thanks for the reply. and the tips.  

>Unfortunately, there's really no way to do so.  The table files themselves
only contain the data and indexes, and don't have any knowledge of a) what
table they are for (apart from their root name) or b) what the structure of
that table is.  The database catalog controls all of this, including whether
or not the table is considered to "exist".


That is the point I was trying to make re using EDBManager.  While just copying via explorer would leave the catalog unknowing about the copied table, EDBManager has the catalogs in its hands and knows everything about all the databases.   I guess I'm just too much of a newbie to ElevateDB to understand, but if it is possible for EDBManager to make a copy of an existing table in the same database, why do you say there's really no way for EDBManager to create and copy this same table into a different DB?


Thanks

Kurt Wagner

Kurt

>That is the point I was trying to make re using EDBManager. While just copying via explorer would leave the catalog unknowing about the copied table, EDBManager has the catalogs in its hands and knows everything about all the databases. I guess I'm just too much of a newbie to ElevateDB to understand, but if it is possible for EDBManager to make a copy of an existing table in the same database, why do you say there's really no way for EDBManager to create and copy this same table into a different DB?

Obviously its theoretically possible BUT

1. The two databases have to be in the same session. ElevateDB can cross database boundaries but not session boundaries.

2. If there are constraints, RI or triggers on a table that can't be replicated in the target database then you've had it.

Roy Lambert [Team Elevate]

Kurt,

<< That is the point I was trying to make re using EDBManager.  While just
copying via explorer would leave the catalog unknowing about the copied
table, EDBManager has the catalogs in its hands and knows everything about
all the databases.   I guess I'm just too much of a newbie to ElevateDB to
understand, but if it is possible for EDBManager to make a copy of an
existing table in the same database, why do you say there's really no way
for EDBManager to create and copy this same table into a different DB?  >>

I wasn't really addressing that point, rather just the point about copying
table files and having the database "know" that they're there.

For the most part, copying tables between databases involves only a couple
of steps:

1) Select the desired table and hit Ctrl-Alt-S, which will bring forward the
SQL that can be used to create the table.  Copy that SQL using Ctrl-A and
Ctrl-C.

2) Hit Ctrl-N to start a new SQL window, and make sure that the current
database is the target database of the copy.  Paste in the SQL using Ctrl-V
and then execute it.

3) In the same SQL window, replace the CREATE TABLE with this statement:

INSERT INTO MyNewTable SELECT * FROM MyOtherDatabase.MyOtherTable

That will give you what you want, minus FK's, indexes, and triggers, all of
which are possibly dependent upon other objects in the configuration or
source database and are not conducive to being automatically copied without
knowing whether the dependencies have been met or not.   Dependency
information was just recently introduced, so I'm working through the EDB
Manager and updating its capabilities accordingly, starting with the
reverse-engeering.

--
Tim Young
Elevate Software
www.elevatesoft.com