Re: [sqlite] SQLiteSpy 1.5.4 released
Hello C.Peachment, >It appears that VACUUM is not the only SQL command to cause >this behaviour. I had done a number of INSERT and UPDATE >commands before closing the database and attempting to use >it with PHP. The VACUUM command was not used. I am surprised to read this. I would be interested if you could reproduce this so I can see if SQLiteSpy is the evil or if it is an issue SQLite. If you can, please contact me via e-mail. >For the moment, my situation is not unique since other users of >PHP will face it too. The pragma legacy_file_format=on >instruction should solve the problem. Any possibility that it could >be set as a configuration parameter for SQLiteSpy to avoid the >need to issue the pragma command each time SQLiteSpy is >used? Sure, I will think about that. >I have searched the documentation for this pragma but without >success. Is this a case of "Star Wars : Use the source, Luke?" :-) Quite true. This has come up in the mailing list already, but maybe a SQLite documentation bug report would push it to success? Regards, Ralf
Re: [sqlite] SQLiteSpy 1.5.4 released
On Wed, 21 Jun 2006 09:24:35 +0200, Ralf Junker wrote: >>1. SQLiteSpy is able to read and work with database files >>formatted by versions of Sqlite earlier than 3.3.6 but it also >>appears to change the database format rather than leave >>it as it was found. >> >>I use php version 5.1.4 including Sqlite version 3.2.8. >>There is a database format change in later versions of >>Sqlite that means php is NOT able to read these later >>versions. >SQLiteSpy does not normally change the database format, just as SQLite does >not. However, starting with SQLite 3.3.0, the file format changed slightly. This is no problem until you run the VACUUM command, which I suppose you did? VACUUM causes SQLite (and therefore SQLiteSpy as well) to rewrite the database and to update it to the latest file format. >There are two solutions to your problem: >1. Don't run VACUUM from SQLite 3.3.0 or later ;=) >2. Issue > PRAGMA legacy_file_format=ON; > before running VACUUM. This instructs SQLite to use the > pre 3.3.0 file format and maintain compatability with > your 3.2.8 PHP version. >Btw: The problem applies to all software using SQLite 3.3.0 or later, even to >the SQLite command-line application. It appears that VACUUM is not the only SQL command to cause this behaviour. I had done a number of INSERT and UPDATE commands before closing the database and attempting to use it with PHP. The VACUUM command was not used. For the moment, my situation is not unique since other users of PHP will face it too. The pragma legacy_file_format=on instruction should solve the problem. Any possibility that it could be set as a configuration parameter for SQLiteSpy to avoid the need to issue the pragma command each time SQLiteSpy is used? I have searched the documentation for this pragma but without success. Is this a case of "Star Wars : Use the source, Luke?" :-)
Re: [sqlite] SQLiteSpy 1.5.4 released
Hello C.Peachment, >1. SQLiteSpy is able to read and work with database files >formatted by versions of Sqlite earlier than 3.3.6 but it also >appears to change the database format rather than leave >it as it was found. > >I use php version 5.1.4 including Sqlite version 3.2.8. >There is a database format change in later versions of >Sqlite that means php is NOT able to read these later >versions. SQLiteSpy does not normally change the database format, just as SQLite does not. However, starting with SQLite 3.3.0, the file format changed slightly. This is no problem until you run the VACUUM command, which I suppose you did? VACUUM causes SQLite (and therefore SQLiteSpy as well) to rewrite the database and to update it to the latest file format. There are two solutions to your problem: 1. Don't run VACUUM from SQLite 3.3.0 or later ;=) 2. Issue PRAGMA legacy_file_format=ON; before running VACUUM. This instructs SQLite to use the pre 3.3.0 file format and maintain compatability with your 3.2.8 PHP version. Btw: The problem applies to all software using SQLite 3.3.0 or later, even to the SQLite command-line application. >2. After executing some sql against a database, the result >set is displayed. If the database is closed after that, the result >set stays on the screen when it might be less confusing if it >disappeared as the database was closed. I felt it would be nice to keep the latest output in case a user wants to compare it with another databases still to be opened. But since some users are just as confused as you are, I will likely change SQLiteSpy to close all SQL statements and result grids if the database is closed. Regards, Ralf
Re: [sqlite] SQLiteSpy 1.5.4 released
On Tue, 20 Jun 2006 15:34:21 +0200, Ralf Junker wrote: >SQLiteSpy 1.5.4 is released. Changes include: >* Updated to SQLite3 3.3.6. >* The schema tree view did not automatically reflect if a table was emptied or >initial records were being added. >* The default text encoding for newly generated database files is now UTF-8. >* Fixed a nasty bug which could cause the syntax highlighting to freeze the >application. >SQLiteSpy is a Freeware SQLite3 database manager for Win32. It is available >for download from http://www.yunqa.de/delphi/sqlitespy/. Your program looks quite nice and seems to do what is needed. Thanks for developing it. Here are a couple of points I have discovered after a few minutes of use: 1. SQLiteSpy is able to read and work with database files formatted by versions of Sqlite earlier than 3.3.6 but it also appears to change the database format rather than leave it as it was found. I use php version 5.1.4 including Sqlite version 3.2.8. There is a database format change in later versions of Sqlite that means php is NOT able to read these later versions. My intention is to use SQLiteSpy as a data administration tool outside of php but that is not possible until their versions are synchronised. 2. After executing some sql against a database, the result set is displayed. If the database is closed after that, the result set stays on the screen when it might be less confusing if it disappeared as the database was closed. Regards, Chris
[sqlite] SQLiteSpy 1.5.4 released
SQLiteSpy 1.5.4 is released. Changes include: * Updated to SQLite3 3.3.6. * The schema tree view did not automatically reflect if a table was emptied or initial records were being added. * The default text encoding for newly generated database files is now UTF-8. * Fixed a nasty bug which could cause the syntax highlighting to freeze the application. SQLiteSpy is a Freeware SQLite3 database manager for Win32. It is available for download from http://www.yunqa.de/delphi/sqlitespy/. Regards, Ralf
