reset_tables.sh will drop and recreate entire database

[abnDK]

In order to remove all constraints, sequences and other stuff being created with the creation of tables, it is better to drop the entire database and rebuild it. this should be optional and stated using a flag when running reset_tables.sh

Code should be placed in reset_database.sh and run from reset_tables.sh

First: run psql as -u abndk and -f drop_database.sql

drop_database.sql: DROP DATABASE personal_budget_2;

Then: run psql as -u abndk and -f create_database.sql

create_database.sql: CREATE DATABASE personal_budget_2;

After calling reset_database from reset_tables, add line to create tables using psql -f db_1-0.sql

[abnDK]

reset_tables now resets database.

Need to make it optional - but in what cases does it makes sense not to drop the entire thing and instead down migrate from x-x to 1-0 and trying to delete sequences etc? Is the any situations, where i wanna keep the data? Is this solely for testing? Can i preserve data in other ways?

How can i copy data to a copy-database. Reset entire database. Recreate database and insert data from copy-database? What kind of complexities will i run into with data traversing multiple versions when migrating up/down?

[abnDK]

possible changes when migrating:

• tables and columns can be renamed
• tables can be created/dropped
• columns can be created/dropped
• column datatype can be changed
• constraints can be created/dropped
• more?

[abnDK]

if i was to make a copy-database it should

• drop/create database with --copy
• be rewritten everytime it is used. Can hold latest backup version, but will be dropped/created whenever it has to be overwritten with new data.

[abnDK]

reset_tables now renames the old database to "_old" and creates a new database.

It is then possible to transfer data from the old version to new version.

Add function for transfering data from the old database to the new version.

[abnDK]

Analysis for transferring data between databases:

• get all table names of old database - what table and/or function can give us that?
• export data from table to file with COPY TO
• delete temp export files after use
• import data from file to table in new db.
• could this be done one table at the time in order to preserve space? (export - import - delete file)
• finally remove file with tablenames.

[abnDK]

if ./reset_tables.sh is called with 'data' in position 1, data will be transfered to new db

Find out how to order tables so tables who a referenced by other tables will be processed first, then FKEY tables after. Maybe look into pg_constraint's type column?

[abnDK]

if ./reset_tables.sh is called with 'data' in position 1, data will be transfered to new db

Find out how to order tables so tables who a referenced by other tables will be processed first, then FKEY tables after. Maybe look into pg_constraint's type column?

Design of a solution, that orders data export and import in a way, that processes parent tables before children tables (with fkey relation to parent tables)

• If a table has foreign key constraint, it comes after
• if table has no foreign key constraint, it will be imported first
• if table has both foreign key constraint and is referenced by other table it will be imported in the middle

table a -- table b -- table c

import order: a, b, c

table A -- table B -- table C ---- table D -- table E

import order: a, b, c, e, d

Solution will follow the order of generations: grandparents, then parents, then children and so forth.

[abnDK]

First part - getting parents and children relationship can be done with "psql -f foreign_key_constraints.sql"

• See 1b754f30f48e2fe89f5100b487157c434aba55a6

Still need to find a way to order the tables and then run the export/import of data from that list.

[abnDK]

Is done now with sort_tables.js etc.