Re: [sqlite] again on sqlite as a standard, was: SQLite and OOo
Le dimanche 25 Juillet 2004 21:28, [EMAIL PROTECTED] a écrit : > > Let me explain, I guess I did no express myself clear. > > There is quit some SQL-generating software (like MDA*-software, but also all kind > of GUI's for databases, and these kind of GUI's can be everywhere in many kinds > of software), and that software expects that the most fundamental SQL is > understood. > It expects the engine to be able to create, drop and alter tables/indices, and it > expects the engine to support common datatypes, and common select/join- > constructions. > > This software is not prepared for workarounds because an engine has an omision > like not understanding ALTER TABLE. > As far as I know is SQLite the only sql-engine which does not understand ALTER > TABLE. > > It causes me a lot of trouble. I am even writing a small converting-programm which > converts an SQLite-database to a Firebird database (inclusive data). Let the > Firebird-database be manipulated by the SQL-generating and SQL-manipulating > software (MDA), and convert it back to the SQLite database, then switch the > connectors back to SQLite and in that way fool the MDA so it thinks it had > changed the SQLite database, and I can go on with my work. > > I hope I expressed myself clear now. > That's exactly the same with OOo : the OOo database GUI doesn't allow the user to modify an existing table, simply because SQLite doesn't support ALTER TABLE. And it's the same with Kexi ... When I say that ALTER TABLE is the most wanted feature, I mean in an end user point of vue. For a developper there's always a solution ! But for a poor OOo user (they are millions) who trys to build his own small database with his office suite (or anything else), it's difficult to understand why he can't modify a table when he has made a mistake. Most of them don't know SQL and have never heard about ALTER TABLE. I've written a howto for OOo users to explain how they can use SQLite Databases with the ODBC driver. After the publication of this howto, I have recieved tons of mails from people who asked why they couldn't modify existing tables with SQLite, but can do it with MySQL and OOo. But these people are not on this list ... OOo supports a lot of different database systems and it can adapt itself to the particularities of each one. My goal is not at all to criticize SQLite developpers. I want to thank them to have developped such a performant and universal file based database. When I see that it's choosen by more and more developpers for their program, I know that it's a good system ! And i'm happy to be able to use it each day with OOo. Of course I dream to see it one day integrated in OOo, like it is already in many others systems (PHP for example). Best regards. Yves
Re: [sqlite] again on sqlite as a standard, was: SQLite and OOo
> The code is Cocoa-based, but could easily be adapted to other > languages. I could build a GUI environment to alter a table and to > generate the SQL statements as well, I don't see what the problem is. > As I said earlier, the issue with the workaround is that it has to > copy the entire table twice. Other than that, I believe it works > fine... When you write SQL-generating software yourself, I guess you can do whatever you want, but I am talking about already existing SQL-generating software, and very good software, but not prepared for SQLite-workarounds. Regards Bert
Re: [sqlite] again on sqlite as a standard, was: SQLite and OOo
> Bert Verhees wrote: > > > > The workaround for ALTER TABLE is not useable in SQL-code generating > > environments. > > Sorry for being dense, but can you elaborate on the above? Why can't > such code be generated? Let me explain, I guess I did no express myself clear. There is quit some SQL-generating software (like MDA*-software, but also all kind of GUI's for databases, and these kind of GUI's can be everywhere in many kinds of software), and that software expects that the most fundamental SQL is understood. It expects the engine to be able to create, drop and alter tables/indices, and it expects the engine to support common datatypes, and common select/join- constructions. This software is not prepared for workarounds because an engine has an omision like not understanding ALTER TABLE. As far as I know is SQLite the only sql-engine which does not understand ALTER TABLE. It causes me a lot of trouble. I am even writing a small converting-programm which converts an SQLite-database to a Firebird database (inclusive data). Let the Firebird-database be manipulated by the SQL-generating and SQL-manipulating software (MDA), and convert it back to the SQLite database, then switch the connectors back to SQLite and in that way fool the MDA so it thinks it had changed the SQLite database, and I can go on with my work. I hope I expressed myself clear now. Regards Bert Verhees (MDA is Model Driven Architecture, this is software which let you design software in UML and OCL (sometimes exaggerated called "executable UML"), and then creates and generates a database for you. When you change the UML-scheme, the software changes the database for you, and tries not to loose data. Here it calls ALTER TABLE, sometimes it does this in steps, because when done intelligent less or no data will fall out of their context. It also has the possiblity to check data, and meta-data in the database, and finds out if all works consistent. I makes heavily use of EXISTS when doing this. The SQL-used hereby is hard- coded in the MDA software and is only for parts to define by the user, which is understandable because that software cannot be prepared for all kind of syntax- contructions that are caused by user-defined settings.)
Re: [sqlite] again on sqlite as a standard, was: SQLite and OOo
> These newly added features were long requested as well, and although > ALTER TABLE is indeed an important feature too, there are probably > other issues that require more attention right now. > > In any case, there is an entry on the FAQ section that shows you how to > do something like ALTER TABLE. It's not ideal because it copies the > entire table twice, but it's better than nothing, I suppose: The workaround for ALTER TABLE is not useable in SQL-code generating environments. These kinds of environments gain in importance. Not only in Model Driven Architecture, but also, I think it is possible in Open Office there will be GUI's which generate SQL code. > > http://www.sqlite.org/faq.html#q13 > > Cheers, > > -- > Tito
Re: [sqlite] again on sqlite as a standard, was: SQLite and OOo
On 25 jul 2004, at 5:47, Yves Chaufour wrote: Why has it not been added for SQLite 3 ? It's perhaps the more wanted SQLite feature, and we can't understand why it isn't yet implemented. Perhaps because you haven't you seen the tremendous amount of work that has been put into version 3. The difference between v2 and v3 is quite dramatic: improved concurrency, more compact datafile, BLOB support, UTF8 & UTF16 support, manifest typing, etc... The "most wanted feature" you say? Arguably. Just check the archives. There are other issues that were discussed more deeply than ALTER TABLE. Concurrency, for example. These newly added features were long requested as well, and although ALTER TABLE is indeed an important feature too, there are probably other issues that require more attention right now. In any case, there is an entry on the FAQ section that shows you how to do something like ALTER TABLE. It's not ideal because it copies the entire table twice, but it's better than nothing, I suppose: http://www.sqlite.org/faq.html#q13 Cheers, -- Tito smime.p7s Description: S/MIME cryptographic signature
Re: [sqlite] again on sqlite as a standard, was: SQLite and OOo
Le samedi 24 Juillet 2004 23:29, D. Richard Hipp a écrit : > M. Fioretti wrote: > > > >>The main drawback of SQLite is that it doesn't support ALTER > >>TABLE. It's a big limitation for simple users when building a > >>database. If this fonctionnality (and a few others) could be add in > >>a near future, SQLite would probably become the first choice for > >>OOo. > >> > > > > ALTER TABLE will probably start appearing (in stages) over the > next several months. Rename table and rename column will appear > first. Then add column and drop column. Finally add and drop > constraints. > Why has it not been added for SQLite 3 ? It's perhaps the more wanted SQLite feature, and we can't understand why it isn't yet implemented. > > > > 2) > > > >>So I hope that SQLite developpers will be able to work together with > >>OOo developpers to write an embeded OOo driver for SQLite (not > >>ODBC), and then to store SQLite database in the future OOo base file > >>format. > > > > Storing an SQLite database as part of a larger file is problematic. > (1) The SQLite database section needs to be contiguous and it > needs to be growable. So at the very least, it would need to be > at the end of the larger file. (2) If stored as part of a larger > file, standard tools (such as sqlite3.exe) would not be able to > read it since they would not know where to look for the start of > the database. (3) SQLite needs a separate rollback journal file > in order to support ACID transactions. Perhaps these objections > could be overcome if I better understood the problem. > I and a few other independant OOo users have tested OOo with SQLite and HSQLDB. Our goal is that these test results help the OOo dba developpers to make the choice of the next OOo embedded database. Our tests show that SQLite has very good performance, and that HSQLDB is very very slow when making requests. This is perhaps because of the slow interface (via jni) between HSQLDB (java) and OOo (C++). SQLite doesn't have this drawback and is very fast with the ODBC driver. Despite of this reality, it seems that OOo dba developpers are going to choose HSQLDB (perhaps because Sun loves java ?). We think it would be a bad choice for OOo. SQLite is already a standard and is already used by many applications. SQLite must not loose the opportunity to be integrated in one of the biggest and more used open source application. We are not programmers and we don't know if it's possible to store a SQLite database in a OOo file. The first step would be certainly to developp a native OOo driver for SQLite. This would allow all OOo users to use external SQLite databases within OOo without the ODBC driver, and to make it in a much easier and integrated way than today. We hope such a driver will be part of OOo 2.0. In a second time it could be interessant to find a solution for OOo to store the SQLite database in its own file. Yves
Re: [sqlite] again on sqlite as a standard, was: SQLite and OOo
I was wondering if internal functions saving to and from an XML data file (an XML file dumper?) might be sufficient to export/import from SQLite standard datafile (I mean without having all the data in the same file, but having multiple files); in any case, export/import functions might be part of the SQLite library file or alternatively as part of the program that is using the library; You could perhaps have separate XML files for each table or whatever; Importation of an XML file would have to be within the constraints of the SQLite data structure in any case; On the other hand I am not sure why there is really a problem: it might be simpler just to add SQLite compatibility to any program like OpenOffice if they want to have SQLite as a standard, it seems to me that SQLite is appearing on most platforms anyway! Kevin O'Neill - Original Message - From: "D. Richard Hipp" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, July 24, 2004 11:29 PM Subject: Re: [sqlite] again on sqlite as a standard, was: SQLite and OOo > M. Fioretti wrote: > > > >>The main drawback of SQLite is that it doesn't support ALTER > >>TABLE. It's a big limitation for simple users when building a > >>database. If this fonctionnality (and a few others) could be add in > >>a near future, SQLite would probably become the first choice for > >>OOo. > >> > > > > ALTER TABLE will probably start appearing (in stages) over the > next several months. Rename table and rename column will appear > first. Then add column and drop column. Finally add and drop > constraints. > > > > > 2) > > > >>So I hope that SQLite developpers will be able to work together with > >>OOo developpers to write an embeded OOo driver for SQLite (not > >>ODBC), and then to store SQLite database in the future OOo base file > >>format. > > > > Storing an SQLite database as part of a larger file is problematic. > (1) The SQLite database section needs to be contiguous and it > needs to be growable. So at the very least, it would need to be > at the end of the larger file. (2) If stored as part of a larger > file, standard tools (such as sqlite3.exe) would not be able to > read it since they would not know where to look for the start of > the database. (3) SQLite needs a separate rollback journal file > in order to support ACID transactions. Perhaps these objections > could be overcome if I better understood the problem. > > > -- > D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 >
Re: [sqlite] again on sqlite as a standard, was: SQLite and OOo
On Sat, Jul 24, 2004 17:29:22 PM -0400, D. Richard Hipp ([EMAIL PROTECTED]) wrote: > ALTER TABLE will probably start appearing (in stages) over the > next several months. Duly noted, thanks. > Storing an SQLite database as part of a larger file is problematic. Well, even today, an OASIS text file (.sxw) is actually a zip file which contains a folder with the actual text, XML info, pictures... all in separate files. grab any sxw file you can find, unzip it and see what I mean. Maybe what we want is the same thing *plus* the SQLite file? Ciao, Marco F. -- Marco Fiorettimfioretti, at the server mclink.it Red Hat & Fedora for low memory http://www.rule-project.org/ A dream will always triumph over reality, once it is given the chance. Stanislaw Lem
Re: [sqlite] again on sqlite as a standard, was: SQLite and OOo
M. Fioretti wrote: The main drawback of SQLite is that it doesn't support ALTER TABLE. It's a big limitation for simple users when building a database. If this fonctionnality (and a few others) could be add in a near future, SQLite would probably become the first choice for OOo. ALTER TABLE will probably start appearing (in stages) over the next several months. Rename table and rename column will appear first. Then add column and drop column. Finally add and drop constraints. 2) So I hope that SQLite developpers will be able to work together with OOo developpers to write an embeded OOo driver for SQLite (not ODBC), and then to store SQLite database in the future OOo base file format. Storing an SQLite database as part of a larger file is problematic. (1) The SQLite database section needs to be contiguous and it needs to be growable. So at the very least, it would need to be at the end of the larger file. (2) If stored as part of a larger file, standard tools (such as sqlite3.exe) would not be able to read it since they would not know where to look for the start of the database. (3) SQLite needs a separate rollback journal file in order to support ACID transactions. Perhaps these objections could be overcome if I better understood the problem. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
