Develop a backup tool for Cosyan

[jonathanvx]

We need a logical backup tool -> that outputs CREATE TABLE and INSERT statement (while disabling foreign keys)

[gsvigruha]

Can you elaborate a bit please? Is the idea here to transform existing tables into create and insert statements? I already have a backup tool which zips the whole meta and data files into a zip: https://github.com/gsvigruha/cosyan/blob/master/src/test/java/com/cosyan/db/tools/BackupManagerTest.java How would this be different functionally?

[jonathanvx]

Need to break this down to a few tasks:

• Get the metadata of the database tables
• Convert the metadata to CREATE TABLE commands
• Have command to disable foreign key checking (to enable loading of data without going through them)
• Go through all tables and generate INSERT statements for their contents

Addition: consider making certain columns in the CREATE TABLE statement as 'personal data' to then obfuscate when dumping data to INSERT statements.

[gsvigruha]

Thanks, the task is clear! But what I'm curious about is what is the advantage of converting into CREATE and INSERT statements and loading it back, as opposed to just zipping and backing up the DB contents as it is? You can restore from those as well. Is there an additional use case I'm missing?

[jonathanvx]

There are two types of database backups: physical and logical. Physical is backing up the actual blocks or files and Logical is SQL statements. You should normally do both. The logical is usually slower to create and slower to restore, but it offers an insurance policy if there was a block on your physical backup that got corrupted. Also, it is usually compatible across different databases and is helpful when you need to restore specific data like a small table or part of a large table - where as with a physical backup, you would need to do a full restore to a separate machine and extract just that table.

Its also useful for moving data into development environment, testing, staging.. etc. Baring in mind that those environments usually cannot have sensitive customer data to be compliant with some laws.

[gsvigruha]

Ok got it, thanks for the thorough explanation! Yeah we should do what you suggest for the logical backup then.

Creating INSERT from the table records should be fairly straightforward. Creating CREATE statements from the table metadata is a bit more complex since we have additional constructs. We might want to switch a few off optionally when backing up:

1. Columns
2. Foreign keys
3. Reference subtables
4. Rules (complex constraints)

[gsvigruha]

@jonathanvx would you like to give this a try? i'm happy point you to places to start in the codebase if you want?

[gsvigruha]

You can use this as a baseline: https://github.com/gsvigruha/cosyan/blob/master/src/main/java/com/cosyan/db/lang/sql/CSVStatements.java#L131 The CREATE statement would be generated from the tableMeta (it has the column names and type info): https://github.com/gsvigruha/cosyan/blob/master/src/main/java/com/cosyan/db/lang/sql/CSVStatements.java#L140

Parser needs to be extended as well: https://github.com/gsvigruha/cosyan/blob/master/src/main/java/com/cosyan/db/lang/sql/Parser.java#L173