Tom Metro wrote:I use an Android app that stores its data in an SQLite3 database, and the data gets backed up to my desktop, where I sometimes have a need to
browse the data. Rather than cobbling together queries, I figured I'd revisit Kexi, which I last looked at briefly back when I was running Ubuntu 9.10 and I put it aside due to the obsolete version available in the repositories.

Now running Ubuntu 12.04 and Kexi 2.4.0, I figured I'd give it a spin again, and see if it might be a quick way to build a GUI browser.

For this use case, I'd ideally like to be able to create a Kexi project in a local project directory, where custom forms can be saved, and then somehow specify that the actual database is at some other path, won't contain any Kexi-specific metadata, and should be opened read-only.

This sounds a bit out of the ordinary, but if you substitute connecting to a database on a remote database server instead of using a local file, then the need to separate the project and metadata from the database would be a common use case. One can imagine lots of situations where someone might want to build a GUI to work with a database where the schema is dictated by other applications or under the control of other departments within a corporation.

The tutorials didn't hint at anything like that being possible. Is it beyond Kexi's current capabilities?

I moved on to "plan b" for a temporary solution by pulling the database into Kexi. I tried the two obvious things: 1. copying the database, renaming it to have a .sqlite3 extension, and opening it in Kexi, and 2. creating a new project and using the import feature.

For #1 it failed with the complaint that the file lacked the Kexi metadata. For #2 I get a "this type is not supported" error. Kexi can't import from a natively supported format?

The above experience seems to suggest that Kexi can't be used as a general purpose database browser (without custom designed forms). It requires that you create a project, design forms, and store metadata in the target database. Maybe this is OK, as there are other tools that do provide generic database browsing, but this may not meet user expectations. Seems it would be better to keep the data and metadata separate, and permit the user not to persistently save that metadata.

This is important use case and we all are aware of it. Feel free to file a wish at http://bugs.kde.org and perhaps vote for it, and share your further suggestions. This always helps. We will have design document on the topic so you'll be able to voice your opinion. Behaviour like you're explaining (treating metadata as supplemental) is developed within Predicate database library, successor of KexiDB but the deadline is not close because of limited funds.

Also planned is "linking" to external databases from the master project, much like MS Access can, which is in par with the demand of more integration features that you have properly explained.

For now Kexi requires its own metadata to be present in any project type of database (file based or server based), so it's not that Kexi connects to "foreign" server databases silently. It requires importing all the database too, no matter how inefficient it sounds when duplication is not welcome or database is large. It's by (initial) design and is going to be changed, as always at the cost of slightly higher complexity. We need you, database application developers, data integrators, analysts to collaborate on the use cases and to figh corner cases.

Regarding your question "Kexi can't import from a natively supported format", for now Kexi's native file format is SQLIte3+metadata. Some intelligence and detection would be needed to properly import the raw SQLite3 files. To me it looks like treating the metadata as supplemental would be better investment - then the import of this kind wouldn't be needed anymore. Everything (data, schema) would be accessible "in place" without copying.

There is possible workaround in case of any database type (file, server) if you have predefined (foreign) set of tables that were created by a non-Kexi tool(s):
1. Create empty Kexi database of given database type.
2. Recreate the tables in Kexi. It's doable if at least temporarily you can give up with features not supported by Kexi GUI, such as non-primary-key indices, complex foreign keys, triggers, etc. Also data types couldn't be very "advanced", e.g. Kexi currently does not understanda PostgreSQL's composite types. You need to recreate only these tables that you need to share between Kexi and the other tools. Other tables can be just deep-copied from your original database using your dedicated native database's query/admin (mysql admin or mysql command line tool or sqlite3 or sqliteman...).
3. The created tables should fit to the original ones, in the sense of "INSERT INTO ... SELECT ... FROM ..." query. If that's the case, both Kexi and the non-Kexi tool(s) would "see" the tables and be able to access the data (read/write).

Above workaround is doable using just Kexi GUI and is typically one-time effort. Optionally one can add the metadata quite easily using dedicated native database's query/admin tool, match data types of your columns with what Kexi defines as common denominator. Once metadata is there (in kexi__* tables), Kexi can open your database and tables. If you need more info on this, we have it more explained somewhere, I'll need to find where.

jstaniek wrote:For now Kexi requires its own metadata to be present in any project...

Thanks for the clarification. (I'm guessing enough people wonder about this that it would be worth mentioning in some of your top-level documents (feature list or FAQ).)

It's by (initial) design and is going to be changed...

Good to hear.

Regarding your question "Kexi can't import from a natively supported format", for now Kexi's native file format is SQLIte3+metadata. Some intelligence and detection would be needed to properly import the raw SQLite3 files.

I'm confused. It sounds like your answer blurs opening and importing. I get that opening an SQLite file lacking metadata would fail. That makes perfect sense. I only mentioned that use case for completeness. To indicate I tried that approach, seeing as the alternative didn't work.

What I'm not following is why importing doesn't work. By implication, you should be importing from non-Kexi databases, which won't have the metadata. In this case the error message was not about the lack of metadata, but "this type is not supported" error. Is that referring to the SQLite file format itself, or a coumn data type (the message is vague). I guess if it is a column type - which I hadn't considered before - that would make sense.

...data types couldn't be very "advanced", e.g. Kexi currently does not understanda PostgreSQL's composite types.

I didn't think this SQLite database would have any oddball data types, but who knows. A more specific error message that identifies the type, column, or even table, would be helpful.

An option to skip the unsupported column would also be useful.

This is important use case and we all are aware of it. Feel free to file a wish...

I might, though I usually file bugs and feature requests for projects I've started using regularly. So far my experiences with Kexi have stalled before getting a project started. Pretty much all use cases I would have for this tool would be working with databases designed for other applications (including my own), where adding Kexi metadata would be unacceptable, and importing/copying would be a show stopper.

But I don't want to sound discouraging. I really like the idea of Kexi and like what I've seen so far (at least in terms of feature list and screen shots). I'll keep it installed in case a need arrises that it works for, and keep an eye on future versions.

-Tom

tmetro wrote:Thanks for the clarification. (I'm guessing enough people wonder about this that it would be worth mentioning in some of your top-level documents (feature list or FAQ).)

I am sorry for this late reply.

Answer 2.5 of the FAQ covers it, do you think it could be improved?

tmetro wrote:I'm confused. It sounds like your answer blurs opening and importing. I get that opening an SQLite file lacking metadata would fail. That makes perfect sense. I only mentioned that use case for completeness. To indicate I tried that approach, seeing as the alternative didn't work.

What I'm not following is why importing doesn't work. By implication, you should be importing from non-Kexi databases, which won't have the metadata. In this case the error message was not about the lack of metadata, but "this type is not supported" error. Is that referring to the SQLite file format itself, or a coumn data type (the message is vague). I guess if it is a column type - which I hadn't considered before - that would make sense.

There is no import plugin from externally created (native, without Kexi metadata) SQLite file. If you think the messages need some improvement, feel free to propose it and the program will be fixed. Now, before 2.7, it's the best time for doing so.

tmetro wrote:I didn't think this SQLite database would have any oddball data types, but who knows. A more specific error message that identifies the type, column, or even table, would be helpful.

An option to skip the unsupported column would also be useful.

In case of SQLite, types most probably would not be a problem. The option makes sense indeed.

tmetro wrote:I might, though I usually file bugs and feature requests for projects I've started using regularly. So far my experiences with Kexi have stalled before getting a project started. Pretty much all use cases I would have for this tool would be working with databases designed for other applications (including my own), where adding Kexi metadata would be unacceptable, and importing/copying would be a show stopper.

Thanks Tom, your support is already encouraging and your use case is a common one so it make sense to make Kexi shine in this area.