Kexi/Vejledninger/Import af SQLite-database til Kexi

Note

Denne vejledning virker med Kexi 2.x og nyere. Forfatter Jarosław Staniek (diskussion).

Dette er skrevet for at hjælpe med følgende forespørgsel på Kexis forum. Kommentarer er velkomne i denne tråd.

Introduktion

Denne vejledning viser, hvordan man tager en inputfil, oaklandweather.sdb, som er en standard SQLite3-fil og indeholder én datatabel og opretter et databaseprojekt, som kan bruges i Kexi.

SQLite3-inputfilen findes her (komprimeret til 600KB ved brug af xz, indeholder offentligt tilgængelige data).

Skridt

1. I terminalen Konsole kan du bruge sqalite3-konsolprogrammet til at vise skemaet (den grafiske brugerflade Sqliteman viser ikke skemaet):

sqlite3 oaklandweather.sdb

2. Du kan nu skrive:

.schema

3. Dette viser dig skemaet for én tabel, 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. Denne tabel skal genskabes manuelt i Kexi for at få den til at matche den oprindelige tabel så meget som muligt. Desværre — for det er en stor tabel.

For at gøre dette oprettes en ny tom database-projektfil i Kexi. Giv den et navn som fx oaklandweather, sådan at Kexi vil vælge et navn, oaklandweather.kexi. Luk ikke Kexi-projektet.

5. Gå i gang med at designe en ny tabel i projektet oaklandweather.kexi.

Skriv et navn for hver kolonne, som vises i skemaet fra skridt 3.
Brug Egenskabseditoren i højre side til at give hver kolonne i denne tabel den rette datatype.
- For typen INTEGER, fx dateTime INTEGER som vist i skemaet vælges Heltal i kolonnen Datatype i Tabeldesigneren.
- For typen REAL, fx barometer REAL som vist i skemaet vælges Decimaltal i kolonnen Datatype i Tabeldesigneren.
Angiv desuden kolonnen dateTime som primær nøgle for tabellen i Egenskabseditoren.

Note

Et tip: under Linux kan du kopiere kolonnenavne lidt hurtigere ved at dobbeltklikke på navnet i tabelskemaet herover, dobbeltklikke på en efterfølgende tom celle i kolonnen Feltpåskrift i Taneldesigneren og klikke med den midterste museknap for at indsætte den markerede tekst.

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 [kexi-project.org/download/tests/oaklandweather-empty.kexi 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-caled 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).