sqlogger data types

Posted on
Sun Mar 22, 2015 12:40 pm
kw123 offline
User avatar
Posts: 5599
Joined: May 12, 2013
Location: Dallas, TX

sqlogger data types

I am trying to convert my SQLite database to POSTGRE.
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

Posted on
Sun Mar 22, 2015 1:06 pm
RogueProeliator offline
User avatar
Posts: 1976
Joined: Nov 13, 2012
Location: Baton Rouge, LA

Re: sqlogger data types

One issue you are running into is that SQLite does not actually use types on columns... more like "hints." Thus it does not actually enforce type constraints on the column's values. That probably is a simplification of the underlying architecture, but that is the net result for us as developers -- thus you can get the empty string you saw in a floating-point typed column.

Check out this tool - pgloader as it might be an option, claiming to convert SQLite to Postgre. I have not used this but just coincidentally saw it mentioned the other day in a blog or news bite from one of my feeds.

Adam

Posted on
Mon Mar 23, 2015 12:07 pm
kw123 offline
User avatar
Posts: 5599
Joined: May 12, 2013
Location: Dallas, TX

Re: sqlogger data types

Almost there... SQLite seems to be really bad with types of columns.

sqlite .dumps creates:
Code: Select all
CREATE TABLE device_history_374645596 ( id INTEGER PRIMARY KEY, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, accumenergytimedelta INTEGER, accumenergytotal INTEGER, curenergylevel INTEGER, accumenergytimedelta_ui TEXT, accumenergytotal_ui TEXT, curenergylevel_ui TEXT);
INSERT INTO "device_history_374645596" VALUES(265545,'2014-01-30 06:00:43',694418,7079.339,773.086,'694418 seconds','7079.339 kWh','773.086 W');

it states that e.g. 7079.339 should be an integer.. that naturally does not work well when importing into postgres, (but rest seems to work ok)

is there any other way to get the types of the columns directly from INDIGO?

thanks

Karl

Page 1 of 1

Who is online

Users browsing this forum: No registered users and 1 guest