GNOME Bugzilla – Bug 340759
Support non-postgres database servers
Last modified: 2009-02-22 20:05:52 UTC
We already use libgda, so half the work is done. But there's still a lot of generated SQL, some of which would need to be special-cased for particular SQL dialects. If it's complicated then it's probably not portable. Wherever possible we should use libgda features instead so that we don't have to worry about the details. libgda 2.0 might be able to help. To see examples of the generated SQL, uncomment the std::cout lines in Base_DB::query_execute() and DbTreeModel::refresh_from_database() then play around with an example. 1. The SQL-generation code in Base_DB::postgres_change_column_extras() is particularly postgres-specific, because it depends on how constraints are specified and how they are named by default. I'd like to avoid ever using a constraint name. I just want to say "this field is a primary key" and "this field is unique". 2. Box_DB_Table_Definition::postgres_change_column_type() is also very postgres-specific because it contains lots of special data conversions, discovered through trial and error. 3. The syntax for aliased left outer joins in GlomUtils::build_sql_select_with_where_clause() might also need some adjustment for some other database servers. 4. We access the internal postgres system tables to discover and change users and groups, and we have to parse the rather eccentric format used there. This is obviously not portable. See Base_DB::get_table_privileges(). Maybe API could be added to libgda.
Points 1, 2 and 4 will be solved when we have support for "genaral DDL queries" in a provider independant way. This is on my TODO list next (or sooner if someone works on the subject during SOC). Point 3 might already be handled by libgda's GdaQuery object.
What are "general DDL queries"? Where is this TODO list?
"general DDL queries" are all the queries which are not SELECT, UPDATE INSERT or DELETE queries. I'm talking about tables creation, field modifications, access control, etc for which there are many differences between each DBMS dialect.
Hi! Does this bugs include support for sqlite3 as well? If not, I'd like to express my desire to see Glom working with & supporting sqlite3 databases. It would make the application accessible to even wider audience of users by making setup & admin simpler. Sincerely, Gour
As of version 1.9.103, some DDL queries are implemented in libgda (create and destroy database, create, rename and destroy table, add and remove column in a table for now) and works for PostgreSQL, MySQL and SQLite. Work is being done on Oracle. So yes, once Glom uses the upcoming stable libgda/libgnomedb libraries, there will be support for SQLite3 as well. Vivien
(In reply to comment #5) > As of version 1.9.103, some DDL queries are implemented in libgda (create and > destroy database, create, rename and destroy table, add and remove column in a > table for now) and works for PostgreSQL, MySQL and SQLite. Work is being done > on Oracle. > Hey, that's great! Thank you for bringing good news. > So yes, once Glom uses the upcoming stable libgda/libgnomedb libraries, there > will be support for SQLite3 as well. I hope it will be soon ;) Sincerely, Gour p.s. btw, I could not build libgda-beta1 (it fails due to no rule for raw.api in gnome-sharp).
> So yes, once Glom uses the upcoming stable libgda/libgnomedb libraries, there > will be support for SQLite3 as well. Woah there. When we use the newer libgda, it will be _easier_ to support sqllite, so a future version of Glom would _maybe_ supoort sqllite. However, for Glom-without-a-separate-server, I still plan to embed postgres. sqllite can't do multiple simultaneous users, and it would be a pain to move data from one server type to another when people decide (as they often will when deploying a mature Glom system) to put their data on a separate server.
(In reply to comment #7) > Woah there. When we use the newer libgda, it will be _easier_ to support > sqllite, so a future version of Glom would _maybe_ supoort sqllite. Thank Murray, for giving us at least some hope... > However, for Glom-without-a-separate-server, I still plan to embed postgres. > sqllite can't do multiple simultaneous users, and it would be a pain to move > data from one server type to another when people decide (as they often will > when deploying a mature Glom system) to put their data on a separate server. Why don't you think that there are many single-user desktop machines who just need simple database application environment, and those users are perfectly well with sqlite. I'm sure that you are aware that there are lot of sqlite users and Glom with sqlite support would bring Kexi-like application for gtk(mm), although much more powerful. Why don't you put some poll regarding sqlite support? otoh, I appreciate your choice as main developer to develop your application in any way you like. Sincerely, Gour
> and those users are perfectly well with sqlite. From my reply above: "sqllite can't do multiple simultaneous users and it would be a pain to move data from one server type to another when people decide (as they often will when deploying a mature Glom system) to put their data on a separate server. " So let me spell it out once last time. _Many_ people _will_ want these extra features, and the use of sqllite (though possibly a short term gain) will make that difficult. > Why don't you put some poll regarding sqlite support? Why dont you have a poll about whether you should do that work and whether you should deal with the complaints and support that result from it? Then you'll wonder whether a poll is the best way to design software.
(In reply to comment #9) > From my reply above: > "sqllite can't do multiple simultaneous users and it would be a pain to move > data from one server type to another when people decide (as they often will > when deploying a mature Glom system) to put their data on a separate server. > " Some users would, some would not decide to move their data to another type of server simply because stand-alone desktop database application is all what they want/need. > So let me spell it out once last time. _Many_ people _will_ want these extra > features, and the use of sqllite (though possibly a short term gain) will make > that difficult. Then I don't understand why you wrote "a future version of Glom would _maybe_ supoort sqllite" is there is no reason to support it? > Why dont you have a poll about whether you should do that work and whether you > should deal with the complaints and support that result from it? Then you'll > wonder whether a poll is the best way to design software. Because I do not develop Glom, neither can/will provide support for it, I will shut up. Since Glom is an open-source project, I just responded to Robin's post (on your blog) who asked for the same feature. Seing that you are pretty determined that sqlite support in Glom does not make sense, I'm giving up on it and will go 'shooping' to some other place. Thank you for your replies. Sincerely, Gour
> Then I don't understand why you wrote "a future version of Glom would _maybe_ > supoort sqllite" is there is no reason to support it? Possible reasons it would not be supported: - Nobody did the work to imlement it. - Nobody wanted to support bugs in that feature. - The described lack of features might make it an undesirable feature. > Some users would, some would not decide to move their data to another type of server simply because stand-alone desktop database application is all what they want/need. The first group (people who need more than sqllite can give) would be disadvantaged by the demands of the second group (people who demand sqllite), as described. But the second group will not need sqllite anyway if embedded postgres is implemented. You are demanding "sqllite support" when what you really need is an embedded server so you don't have to think about how it's implemented, regardless of whether that's sqllite or postgres. But I have made that clear by now and I don't wish to repeat myself any more.
By the way, "embedding" (well, starting our own process) of Postgres is now mostly implemented, apart from a few cosmetic improvements still to do. So you no longer need to configure a database server manually, and you can copy the entire database data around as files. For many people that was the main reason for wanting sqllite support. However, use of an external database server is still an option, and libgda2 might still allow us to support other databse server engines in future.
This task can be made easier by restricting it to the new client-only build at first.
But wouldn't a client-only build require a document that uses a non-postgres server, as it can't create new documents? And to create such a document, to be used with the client-only build, a non-client-only build would be required. Or maybe I simply don't get how you would use such a client-only build.
Yeah, I guess you are right. But I suggest that you just implement the simple stuff, without worrying about things like related records.
Created attachment 122877 [details] [review] Refactor Glom::ConnectionPool This patch makes ConnectionPool not rely on postgresql anymore. Instead, it allows different backends to be used for the actual database operations. It adds two backends for postgresql central hosting and self hosting, and adjusts the other glom code for this change. The next thing to do is to actually implement backends for other databases (such as sqlite) add UI to use them and fix upcoming problems. Can I commit this to trunk, or maybe in a branch? I could also develop everything locally until sqlite support is working, but I'd prefer to also have "intermediate versions" in version control.
That's an interesting idea. Yes, I trust you to commit to svn trunk. Please do. You might want to just use a ConnectionPoolBackend class instead of an inner class, to keep the code separated and clean.
I committed this patch to trunk, with ConnectionPool::Backend renamed to ConnectionPoolBackend.
I think we can close this now, and open individual bugs for the sqllite backend when necessary.