[Zope] coping database tables

[Spicklemire, Jerry]

Jacintha asked:

> Kindly let me know how to copy database tables from on Unix server to
> the other. We are using the same postgreSQL database on both the servers

Since you are copying between two instances of the same RDBMS, you 
will find it most effective to treat this as a non-Zope process. 
The specific steps involved can be found in the documentation for 
PostgreSQL and operating system.

However, it is also possible to perform the same operation using 
Zope as a form of middle-ware, that can interact directly with both 
RDBMS instances. In some cases this could be quite handy, though 
sub-optimal, in terms of efficiency. Most folks would perform a 
native export and import at both ends.

At any rate, since it is possible to do this with Zope, and you seem 
to prefer this approach, here's the basic idea.

Create ZSQL methods to extract all the data from each original table, 
and name them "sql_dump_table_name". They will look something like :

select * from table_name

Using the "advanced" tab set a cache value for this process somewhat 
higher than the default (0), so that the process is at least a little 
more efficient. A setting of 300 is equal to 5 minutes.

Next, create corresponding methods to insert each record into the 
new, identical tables where you need to replicate the data, and name 
them "sql_stuff_tablename".

They will look something like this:

insert into table_name (field_name1, field_name_2) 
data (<dtml-sqlvar name_1 type=nb>, <dtml-sqlvar var_name2 optional>)

In the "arguments" box you will need to list all the "var_names".

Of course this example should be adapted to represent the actual 
number of fields, and their names, and data types, whether the 
field can be "blank" (optional). See the ZSQL docs for details.

The names are arbitrary, but it helps in the example to keep things 
clear by identifying each piece.

You can execute this admittedly inefficient process like so:

<dtml-if sql_pg_dump_table_name>
 <dtml-in sql_pg_dump_table_name>
  <dtml-let var_name_1="_['field_name_1']"
            var_name_2="_['field_name_2']">
   <dtml-call sql_pg_stuff_tablename>
  </dtml-let>
 </dtml-in>
 Process complete!
</dtml-if>

Hopefully your tables are small (not many records), but even if they 
are, this process may time-out, depending on your browser settings. 
If your browser displays a "page could not be displayed" message, the 
process could very well still be running, even though the browser has 
"given up" and stopped waiting for the display data. You'll need to 
look at the target database to see how things have progressed.

With a little more work you could create lists of table/fieldnames, 
and set up a process that will extract - insert your entire database, 
without hard-coding each ZSQL method. The inefficiencies would be, 
compounded, but it could still be a convenient way to replicate data
between two unrelated RDBMS systems, with little prior knowledge of 
either.

This basic concept could also be used to create a Web based GUI for 
ad hoc data management, for mapping an existing Gadfly dataset onto 
an unknown target during a Zope based application installation.

Good luck!
Jerry S.