Kexi/Tutorials/Importing SQLite database into Kexi

Note

This tutorial works with Kexi 2.x or newer. Author Jarosław Staniek (talk).

It has been written as for supporting this help request on the Kexi forum. Any comments are welcome in that thread.

Note: There is alternative approach: https://forum.kde.org/viewtopic.php?f=220&t=139243

Introduction

This tutorial shows how to take an input file oaklandweather.sdb which is a native standard SQLite3 file containing one table with data and create database project that can be used with Kexi.

The input SQLite3 file is available here (compressed to 600KB using xz, contains publicly available data).

Steps

1. In Konsole terminal you can use sqlite3 console app to show schema (Sqliteman GUI app would not show the schema):

sqlite3 oaklandweather.sdb

2. Then you can type:

.schema

3. This shows you schema for one table archive:

CREATE TABLE archive (
   dateTime INTEGER NOT NULL UNIQUE PRIMARY KEY,
   usUnits INTEGER NOT NULL,
   interval INTEGER NOT NULL,
   barometer REAL,
   pressure REAL,
   altimeter REAL,
   inTemp REAL,
   outTemp REAL,
   inHumidity REAL,
   outHumidity REAL,
   windSpeed REAL,
   windDir REAL,
   windGust REAL,
   windGustDir REAL,
   rainRate REAL,
   rain REAL,
   dewpoint REAL,
   windchill REAL,
   heatindex REAL,
   ET REAL,
   radiation REAL,
   UV REAL,
   extraTemp1 REAL,
   extraTemp2 REAL,
   extraTemp3 REAL,
   soilTemp1 REAL,
   soilTemp2 REAL,
   soilTemp3 REAL,
   soilTemp4 REAL,
   leafTemp1 REAL,
   leafTemp2 REAL,
   extraHumid1 REAL,
   extraHumid2 REAL,
   soilMoist1 REAL,
   soilMoist2 REAL,
   soilMoist3 REAL,
   soilMoist4 REAL,
   leafWet1 REAL,
   leafWet2 REAL,
   rxCheckPercent REAL,
   txBatteryStatus REAL,
   consBatteryVoltage REAL,
   hail REAL,
   hailRate REAL,
   heatingTemp REAL,
   heatingVoltage REAL,
   supplyVoltage REAL,
   referenceVoltage REAL,
   windBatteryStatus REAL,
   rainBatteryStatus REAL,
   outTempBatteryStatus REAL,
   inTempBatteryStatus REAL
);

4. This table needs to be manually re-created using Kexi to make it match the original table as much as possible. Unfortunately -- because this is a big table.

To do so, you can create a new blank database file project using the Kexi app. Give it a name such as oaklandweather so Kexi will pick a name oaklandweather.kexi. Do not close the Kexi project.

5. In the oaklandweather.kexi project, start designing a new table.

• Type a name for every column presented by the schema in the step 3.
• Use the Property Editor on the right hand to give each column in this table proper data type.
  • For INTEGER types, eg. dateTime INTEGER as seen on the schema, select Integer Number in the Data Type column of the Table Designer.
  • For REAL types, eg. barometer REAL as seen on the schema, select Floating Point Number in the Data Type column of the Table Designer.
• In addition, set the dateTime column as the primary key for the table in the Property Editor.
• Also, usUnits and interval columns have NOT NULL specifier assigned. To do the same in Kexi, set Required property to Yes for these columns in the Property Editor.

Note

A hint: on Linux you can copy column names a bit faster by double clicking on the name in the table schema above, double clicking on a subsequent empty cell in the Field Caption column of the Table designer, and then pressing the middle mouse button to paste the previously made selection.

Note

It is possible to create the table in Kexi using text editor and a SQL command but it would be reserved for more advanced users. Given there is enough interest instructions can be published.

6. Save the table's design with the Archive caption. Physical table's name will be set by Kexi as archive what matches the table name in the oaklandweather.sdb file.

Note that after saving the design you can again open it for designing. The only remark is that upon saving the altered table design you would loose any data in the table, if there is any, so it is better to make sure the table design is perfect before any data is inserted into the table.

Make sure there are all the 52 columns have been inserted in the archive table design and that all are in correct order and have proper type. Any difference can make the resulting archive table inaccessible for Kexi.

When you are done, close the Kexi app.

The Kexi file without data is available here for reference.

7. So far the oaklandweather.kexi file has no data in it. Now you may be ready to execute the final command to import the data. In the Konsole terminal enter to the directory where the oaklandweather.sdb and oaklandweather.kexi files exist or add proper paths to them. Then type (or paste) the command:

sqlite3 oaklandweather.sdb .dump | egrep -e "^(BEGIN|INSERT|ANALYZE|COMMIT)" | sqlite3 oaklandweather.kexi

This works as follows:

• Reports the oaklandweather.kexi file to a so-called SQL dump (a text representation) using the sqlite3 console app.
• Removes from this dump any lines that do not begin with BEGIN, INSERT, ANALYZE or COMMIT. This is needed because we are only interested in data of the table, not in creating a new table.
• Directs the resulting output to a sqlite3 console app again, which receives all the commands from the output and applies them to the oaklandweather.kexi database file. The last command applied is COMMIT.

8. Done! You can open the oaklandweather.kexi database project file in Kexi and open the 'archive' table in it to see all the data.

The resulting Kexi file is available here (compressed to 650KB using xz).