Wed Jan 09, 2013 9:27 am
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.
Sat Jan 12, 2013 2:03 am
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).)
Good to hear.
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.
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.
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.
Thu Jan 24, 2013 10:10 pm
I am sorry for this late reply.
Answer 2.5 of the FAQ covers it, do you think it could be improved?
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.
In case of SQLite, types most probably would not be a problem. The option makes sense indeed.
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.