Export / Import tables

squirrel · 2017-05-30 09:35:13

I can use a comment / idea to point me in the right direction.

The need is to backup and restore both the structure and data of some tables - not an entire database. The exact structure of the tables will not be known to me, since this might include client-side customizations. I also do not know in advance whether the table will be on Sql Server or Mysql. So I thought of extracting and storing the data in a db neutral format and being able to either generate sql statements for the required platform as needed, or directly creating onto the required platform as needed. (yes I know there are 3rd party backup tools which can do this, but this request is only part of the main task)

The table structure I can get using TSQLDBConnectionProperties.GetFields and TSQLDBConnectionProperties.GetIndexes and save this to a file and the data I can get from a Select statement. (select statement will be custom, since some sensitive data must sometimes be excluded, ie leaving password fields empty). Both the structure and data can be stored as either json or sql. This leaves me with needing to be able to restore this custom backup. I've seen several areas dealing with batch inserts.

1) Using TSQLDBConnectionProperties.SQLCreate, I can generate the create statement without the indexes and then add the indexes one by one using SQLAddIndex. Is this correct or is there a more suitable way of storing and retrieving the table structure?
2) Which of the batch insert options are most suitable for such restores? My limitation here is that some of the tables have multi-field primary keys, so depending on the existence of a primary key called id is not an option.
3) Is there a way of generating standard create/insert sql scripts from these structures as a fallback? TSQLRestStorageExternal.JSONDecodedPrepareToSQL?

Any suggestions / ideas / guidance will be welcome.

Last edited by squirrel (2017-05-30 10:40:46)

ab · 2017-05-30 13:24:57

Why not just define an interface based service, with some DTO (including variants for schema-less data?

Don't recreate an ORM, but use the ORM for easy storage. Focus on your data, not on how to store it

squirrel · 2017-05-30 13:43:58

This is similar to a disaster recovery/offsite backup scenario. So there will likely only be old backup files to use, no running services. It is not intended to be used as a live transfer of data. Or am I understanding your suggestion incorrectly?

ab · 2017-05-31 07:13:03

IMHO defining a service is always the best option, because it will allow to use an interface, so will help making your code SOLID (see doc).

My suggestion is about logical design, not physical design.
Even if there is no remote service running, encapsulate your DB process within an interface is worth it.

Then, you could later on publish your class implementing the service as a mORMot interface-based REST service, if needed - with better performance and security over a network, by the way.

squirrel · 2017-05-31 07:52:48

I fully agree that there are advantages to a purely logical design.

However, the requirements of this task specify that a human readable file be created which can be stored offsite on a backup medium with no access to electricity. That leaves me with either a .sql or a .json file. Due to the custom nature of the specifications, just creating a sql dump file is not ideal. So I've decided to create a json file per table which contains the table's field definitions (GetFields), index defs (GetIndexes), and data (FetchAllAsJSON(false)).

Restoring the structure seems very doable (SqlCreate, SQLAddIndex). I'm not too sure about restoring the data.

ab · 2017-05-31 07:59:06

A SQlite3 file may have made better sense than plain text.
See https://www.sqlite.org/aff_short.html
and https://sqlite.org/formatchng.html

You have SQLite3 import/export available from SynDB units.
See the corresponding features in the SynDBExplorer sample.

mORMot Open Source

#1 2017-05-30 09:35:13

Export / Import tables

#2 2017-05-30 13:24:57

Re: Export / Import tables

#3 2017-05-30 13:43:58

Re: Export / Import tables

#4 2017-05-31 07:13:03

Re: Export / Import tables

#5 2017-05-31 07:52:48

Re: Export / Import tables

#6 2017-05-31 07:59:06

Re: Export / Import tables

Board footer