One of the issues I run into is:
the SQLite dump declares a field as REAL but some of the contents is "" (and not NULL)
at the POSTGRE import stage it complains that the type is wrong (which is correct) and bombs.
2 questions:
- I though everything stored in INDIGO is TEXT
- Any idea how to fix this .. without very complex "sed" commands (i.e. replace "" in data with NULL if type of field is REAL.. , and the same for INTEGER and BOOLEAN , and ..?)(-- would then probably do that in python..)
any advice is appreciated
another way I am trying to do this:
1. let indigo create the POSTGRE data base by selecting POSTGRE as the database (that will create empty tables for all devices and variables) then quickly switch back to SQLITE so that the tables are still empty
2. create the SQL DUMP file; remove the CREATE statements, just use the INSERT commands
same issue
Karl
ps naturally there are some other conversions needed like:
- Code: Select all
sqlite3 indigo_history.sqlite .dump | sed -e '/CREATE/d' |sed -e 's/INTEGER PRIMARY KEY AUTOINCREMENT/SERIAL PRIMARY KEY/' | sed -e 's/PRAGMA foreign_keys=OFF;//' | sed -e 's/unsigned big int/BIGINT/g' | sed -e 's/UNSIGNED BIG INT/BIGINT/g' | sed -e 's/BIG INT/BIGINT/g' | sed -e 's/UNSIGNED INT(10)/BIGINT/' | sed -e 's/BOOLEAN/SMALLINT/g' | sed -e 's/boolean/SMALLINT/g' | sed -e 's/UNSIGNED BIG INT/INTEGER/g' | sed -e 's/INT(3)/INT2/g' > postgreImportFile