Chalk me up as another user who has a large infrastructure around SQLite and just wants to be able to import those into Kexi. I took a different avenue to other folks (https://userbase.kde.org/Kexi/Tutorials/Importing_SQLite_database_into_Kexi), and my database is too big to convert via manual labor

Things of note in the following, along with the comments inline:
* Ubuntu 16.10, with latest kexi in the ubuntu repos [2.9.11]
* Requires sqlite 3.16.0+, since it uses pragma table-valued functions.
* Reading Kexi's source, f_fields.f_options and f_constraints are hard to infer. I mostly care about reading from an existing fully-written database [rather than using kexi's GUI to add data], I quietly ignore most of it
* Kexi doesn't use views to implement its "queries". The commented out section at the end does what I assume would be the right thing, but kexi doesn't like the syntax for a lot of the views in my database. If I just pretend that existing views are kexi "tables", the right thing happens.

Being said, here you go. This script takes my existing sqlite databases, and ends with a file that kexi is able to load and look at.

Code: Select all

BEGIN;

-- Great purge! This is useful during development of this script, but will be destructive to existing kexi databases
DROP TABLE IF EXISTS kexi__objectdata;
DROP TABLE IF EXISTS kexi__fields;
DROP TABLE IF EXISTS kexi__objects;
DROP TABLE IF EXISTS kexi__db;
DROP TABLE IF EXISTS kexi__blobs;
DROP TABLE IF EXISTS kexi__parts;
DROP TABLE IF EXISTS kexi__userdata;

CREATE TABLE kexi__objectdata (o_id Integer UNSIGNED NOT NULL, o_data CLOB, o_sub_id Text);
CREATE TABLE kexi__fields (t_id Integer UNSIGNED, f_type Byte UNSIGNED, f_name Text,
          f_length Integer, f_precision Integer, f_constraints Integer, f_options Integer,
          f_default Text, f_order Integer, f_caption Text, f_help CLOB);
CREATE TABLE kexi__objects (o_id INTEGER PRIMARY KEY, o_type Byte UNSIGNED, o_name Text,
          o_caption Text, o_desc CLOB);
CREATE TABLE kexi__db (db_property Text(32), db_value CLOB);
CREATE TABLE kexi__blobs (o_id INTEGER PRIMARY KEY, o_data BLOB, o_name Text, o_caption Text,
          o_mime Text NOT NULL, o_folder_id Integer UNSIGNED);
CREATE TABLE kexi__parts (p_id INTEGER PRIMARY KEY, p_name Text, p_mime Text, p_url Text);
CREATE TABLE kexi__userdata (d_user Text NOT NULL, o_id Integer UNSIGNED NOT NULL,
          d_sub_id Text NOT NULL, d_data CLOB);
 
INSERT INTO "kexi__parts" VALUES(1,'Tables','kexi/table','org.kexi-project.table');
INSERT INTO "kexi__parts" VALUES(2,'Queries','kexi/query','org.kexi-project.query');
INSERT INTO "kexi__parts" VALUES(3,'Forms','kexi/form','org.kexi-project.form');
INSERT INTO "kexi__parts" VALUES(4,'Reports','kexi/report','org.kexi-project.report');
INSERT INTO "kexi__parts" VALUES(5,'Scripts','kexi/script','org.kexi-project.script');
INSERT INTO "kexi__parts" VALUES(6,'Web pages','kexi/web','org.kexi-project.web');
INSERT INTO "kexi__parts" VALUES(7,'Macros','kexi/macro','org.kexi-project.macro');

INSERT INTO kexi__db ( db_property, db_value)
            VALUES
            ( 'kexidb_major_ver', 1) ,
            ( 'kexidb_minor_ver', 9) ,
            ( 'kexiproject_major_ver', 0) ,
            ( ' kexiproject_major_ver', 'Project major version') ,
            ( 'kexiproject_minor_ver', '1') ,
            ( ' kexiproject_minor_ver', 'Project minor version') ,
            ( 'project_caption', 'SUP2RTAM Kexi') ,
            ( ' project_caption', 'Project Caption') ,
            ( 'project_desc', 'SUP2RTAM Kexi') ,
            ( ' project_desc', 'Project Description') ;

-- Note that this is importing views as kexi "tables". Remove "OR type='view'" if you don't like that
INSERT INTO kexi__objects ( o_type, o_name, o_caption)
SELECT (SELECT p_id FROM kexi__parts WHERE p_name='Tables'), name, name
    FROM sqlite_master
    WHERE name NOT LIKE 'kexi__%'
        AND name NOT LIKE 'sqlite%'
        AND (type='table' OR type='view');

-- This CTE covers all the types I have in my database. If I were going to be rigorous about it,
--     I'd work through all of SQLite's recognised datatypes
INSERT INTO kexi__fields ( t_id, f_type, f_name, f_length, f_precision, f_constraints,
                        f_options, f_default, f_order, f_caption, f_help)
WITH kexi_type_lookup ( typename, typeid) AS
     ( SELECT 'INT%', 3
            UNION
            SELECT 'TEXT', 11
            UNION
            SELECT 'REAL', 10
            UNION
            SELECT 'DOUBLE', 10
            UNION
            SELECT 'NUMBER', 3
            UNION
            SELECT 'BOOL%', 5
     )
   SELECT    k_o.o_id AS t_id,
          COALESCE(ktl.typeid, 3) AS f_type, -- Default to integer, which is SQLite's regular behaviour
          pti.name AS f_name,
          0 AS f_length,
          0 AS f_precision,
          (CASE WHEN pti.pk THEN 119 ELSE 0 END) AS f_constraints, -- Dunno what this does, other than the pk one
          0 AS f_options, -- Dunno what these do
          pti.dflt_value AS f_default,
          1 AS f_order, -- Kexi turns out not to care
          pti.name AS f_caption,
          NULL AS f_help
     FROM kexi__objects k_o, pragma_table_info(k_o.o_name) pti
     LEFT JOIN kexi_type_lookup ktl ON pti.type LIKE ktl.typename
     WHERE k_o.o_type=(SELECT p_id FROM kexi__parts WHERE p_name='Tables');

-- This code would import all your "views" as kexi "queries", but kexi claims
--   that many of my queries are invalid, and doesn't recognise queries[views] that select
--   from other queries[views]
-- INSERT INTO kexi__objects ( o_type, o_name, o_caption)
-- SELECT (SELECT p_id FROM kexi__parts WHERE p_name='Queries'), name, name
--      FROM sqlite_master
--      WHERE name NOT LIKE 'kexi__%'
--           AND name NOT LIKE 'sqlite%'
--           AND type='view';
-- INSERT INTO kexi__objectdata(o_id, o_data, o_sub_id)
--   SELECT k_o.o_id, SUBSTR(sql, INSTR(sql, 'SELECT')), 'sql'
--      FROM kexi__objects k_o
--      INNER JOIN sqlite_master sm ON sm.name=k_o.o_name
--      WHERE sm.type='view';

COMMIT;

Afterthoughts:
* Kexi's schema:
- lacks FKs and other structures that would enforce correctness
- declares some columns as types that sqlite doesn't know about [CLOB] or doesn't care about [UNSIGNED]
- Doesn't use sqlite's autoincrement
* In the same vein, I can't seem to describe many of my constraints using kexi's lexicon [FKs, compound keys, CHECK constraints etc]. They're on the database and sqlite will enforce them. I'm quietly ignoring this.
* My broad endgame for something like this would be to inject kexi "Reports" as befits my specific application

Overall:
* It's awesome that there's finally something approaching Access.
* It clearly does a great job of solving a whole bunch of problems.
* Thank-you for creating this.

Cheers,
Gary

PS In case you're wondering: I have several models that drop SQLite databases as their output, that I then query to get model run results. Hence the databases would never be written to. I'm just trying to improve model usability by experimenting and finding good tools for users of my models to get output.

Thanks Gary, I see we can benefit from this analysis and check step by step what can be improved in Kexi schema and alike.

gbriggs wrote:* Kexi's schema:
- lacks FKs and other structures that would enforce correctness
- declares some columns as types that sqlite doesn't know about [CLOB] or doesn't care about [UNSIGNED]
- Doesn't use sqlite's autoincrement

Good observation, for my understanding:
- The schema originates from the SQLite 2.8 times when FKs were ignored, IIRC. FKs would be a good addition, not breaking backward compatibility unless someone depends on lack of FK while using the sqlite file outside of Kexi. Kexi's client itself (originating, again to the old SQLite times) enforces the integrity internally and this won't be removed so we would be OK - we would stay compatible.
- The use of UNSIGNED, as some other words in SQLite is a pure annotation.
- Regarding CLOB, consider this an annotation, SQLite recognizes this; from https://www.sqlite.org/datatype3.html: "If the declared type of the column contains any of the strings "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity. Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity."
- Kexi client uses the autoincrement feature by not setting the ID values. From https://www.sqlite.org/autoinc.html: "On an INSERT, if the ROWID or INTEGER PRIMARY KEY column is not explicitly given a value, then it will be filled automatically with an unused integer, usually one more than the largest ROWID currently in use. This is true regardless of whether or not the AUTOINCREMENT keyword is used." (so for SQLite AUTOINCREMENT would be redundant; if it's needed for some other db backends, it is added)

- The schema originates from the SQLite 2.8 times when FKs were ignored, IIRC. FKs would be a good addition, not breaking backward compatibility unless someone depends on lack of FK while using the sqlite file outside of Kexi. Kexi's code itself (originating, again to the old SQLite times) enforces the integrity internally and this won't be removed so we would be OK - we would stay compatible.

Yeah, and FKs still require enabling every single time you open a database connection. Adding them adds convenience, and like you say, wouldn't actually break anything [PRAGMAs are documented as being ignored-but-accepted if unknown, which plays positively into this]. Honestly, SQLite 2 is something not worth considering, it's now *decades* out of date, and trying to support it leads to misery.

- The use of UNSIGNED, as some other words in SQLite is a pure annotation.

Sure! If it were me, I'd want to do one of two things [note that this is a SQLite-specific discussion]:
a) If I actually cared that the values were unsigned, I'd add a CHECK constraint
b) If I didn't actually care, I'd leave it off *shrug*

- Regarding CLOB, consider this an annotation, SQLite recognizes this; from https://www.sqlite.org/datatype3.html: "If the declared type of the column contains any of the strings "CHAR", "CLOB", or "TEXT" then that column has TEXT affinity. Notice that the type VARCHAR contains the string "CHAR" and is thus assigned TEXT affinity."

So, the broad [strategic] question for kexi would be [and this applies to most of my thoughts about this]: do you want to drink the SQLite kool-aid wholesale? If you do, you can take advantage of some vendor-specific stuff, and your code would be clearer by using the SQLite types. If you don't, or you want to leave it open to porting your code more easily to other databases, then it's totally fine as-is.

- We're using the autoincrement feature by not setting the ID values. From https://www.sqlite.org/autoinc.html: "On an INSERT, if the ROWID or INTEGER PRIMARY KEY column is not explicitly given a value, then it will be filled automatically with an unused integer, usually one more than the largest ROWID currently in use. This is true regardless of whether or not the AUTOINCREMENT keyword is used." (so for SQLite AUTOINCREMENT would be redundant; if it's needed for some other db backends, it is added)

Ah, sorry. Upon further examination, I realise you're using that on kexi__{objects,blobs,parts} but not on kexi__{fields,db,objectdata,userdata}, because those don't actually have a pk column [they still have rowid, but that's hidden]. Which I'd note takes me back to a previous point; without a unique constraint on kexi__fields on {t_id,f_name}, you could easily end up in trouble; partially for correctness, and partially because addressing a specific row is hard [unless you're using the implicit rowid - implicit columns are something I'm not fond of, one of the few things I really don't take advantage of when drinking the sqlite kool-aid]

Thanks for the responses! I wasn't really expecting this, I was just throwing up something that other folks may find helpful, if they start googling for kexi sqlite conversion stuff. I really appreciate what you've built, here. If I had more free time right now, I'd just implement the import properly directly in kexi

Gary

Hi gbriggs:

How do you use the script?

Thanks.

bdika