[Kexi] Importing a SQLite3 database

[Jarosław Staniek]

Ken Zalewski said the following, On 2007-03-26 12:18:

> I have a sqlite3 database, and I'm trying to import it into Kexi.  This
> does not seem to work.
> 
> I have tried doing this two ways:
> 
> 1. Tools -> Import Database...
> 
> This seems to be able to import MS Access DBs, but not SQLite2/3 DBs.
> The dialog lists *.mda, *.mdb, and *.mde files as all the supported
> files.  If I enter my sqlite3 database filename, I get an error message:
> This type is not supported.
> 
> How can a sqlite3 DB type not be supported, when that is the exact type
> that Kexi uses to create database files?

Ken,
First, thanks for your questions. Your points are valid and pretty well known. 
Below I try to find a workaround for your current Kexi version.

Please read the FAQ related to exporting/importing.
http://kexi-project.org/wiki/wikiview/index.php?KexiFAQ

> 2. File -> Import -> Table Data From File...
> 
> Since I was unable to import my sqlite3 database, I thought I would try
> importing the table data itself.  Again, this failed to work.  The table
> data importer expects a CSV file, and works per table, not for an entire
> database.  And the importer cannot read a sqlite3 database.

You can use the following command in your sqlite (or ksqlite) command line tool:

.mode MODE ?TABLE?     Set output mode where MODE is one of:
                          csv      Comma-separated values

See http://www.sqlite.org/sqlite.html.
You can also use this command in SQL script, then import CSV files one-by-one 
into Kexi.

> I am mystified as to why Kexi cannot handle already-existing databases
> that are in the native format that Kexi itself uses to store database
> projects.

SQLite files are not 'quite' native Kexi's format. It's the lower layer. The 
native format is the one associated with schema data, namely kexidb__* tables, 
but (at the time when I started to use SQLite) SQLite did not support this or 
any other strict metadata and it is largely typeless.

There is sqlite_master now and 
http://sqlite.org/capi3ref.html#sqlite3_column_decltype
All this will be used in the forthcoming import/link features for "native 
SQLite" files, even while you still will be able to write:

'CREATE TABLE foo (mycolumn green_monkey);' and I'll need to ASK you what 
green_monkey type is :)

> Ultimately, my goal was to create a front end to a sqlite3 database,
> which I was then hoping to port to PHP5.  I don't think Kexi can
> generate PHP code, so Kexi is probably not my tool anyway.  I will list
> that as a feature request.
> 
> Summary:
> 
> 1. Kexi must be able to import a SQLite2 or SQLite3 database.
> 
> 2. Even better, Kexi should be able to reference a SQLite database
> externally, so that the .kexi file would contain only the metadata, and
> the external SQLite file would contain the actual data.

Of course it is planned for 2.x, see A4.2: 
http://kexi-project.org/wiki/wikiview/index.php?KexiFAQ
The goal is to limit a need for improting in favour of linking wherever possible.

> 3. Kexi should be able to generate PHP5 code (using PDO) that will allow
> users to build web-based database front-ends to their SQLite databases.

Proposed as GSoC 2007 project (nobody applied for it this year) 
http://techbase.kde.org/Projects/Summer_of_Code/2007/Ideas#Alternative_Solution_for_Kexi_Forms_Using_PHP

Anyone is free to come and work with me on the subject.

Moreover, this one feature will be in development if succeeded in GSoC 2007: 
http://techbase.kde.org/Projects/Summer_of_Code/2007/Ideas#Kexi_Web_Forms is

PS: please just write to kexi at kde.org to reach all the developers and many users.

-- 
regards / pozdrawiam, Jaroslaw Staniek
  Sponsored by OpenOffice Polska (http://www.openoffice.com.pl/en) to work on
  Kexi & KOffice: http://www.kexi.pl/en, http://www.koffice.org
  KDE3 & KDE4 Libraries for MS Windows: http://kdelibs.com, http://www.kde.org