Screenshot of phpLiteAdmin 1.9.3
Some minutes ago, I released the new version of phpLiteAdmin, a web management GUI for SQLite databases written in PHP. You can download it from our project site.
The new version addresses and mostly fixes lots of issues. Among these, one security issue has been fixed. Therefore, I’d recommend anybody using phpLiteAdmin to update.
A lot of work has gone into this release, fixing lots of bugs to make phpLiteAdmin more robust. For example, you can now have tables or columns containing special characters. The ALTER TABLE features have been partly rewritten so they now work a lot more reliable. And lots of other issues have been fixed. Thanks to anybody who reported bugs to the bug tracker.
If you still have any problems or suggestions, please let us know on our issue tracker.
At the moment I am working again on phpliteadmin, a php-based web GUI for database administration of sqlite databases. While debugging, I stumbled across a problem that only occurred with the PDO extension (not with SQLiteDatabase or SQLite3). I got the following error message while trying to drop a table:
HY000 / 6 / database table is locked
By the way, you can use PDO::errorInfo() to get these error messages. So as the error correctly explains, the table I tried to drop seemed to be in use. PDO documentation for PDO::query() also explains the problem (even though DROP TABLE statements are fired using PDO::exec()):
If you do not fetch all of the data in a result set before issuing your next call to PDO::query(), your call may fail. Call PDOStatement::closeCursor() to release the database resources associated with the PDOStatement object before issuing your next call to PDO::query().
So I hunted for the open cursor on a resultset of the table in question and could not find any. Finally, I found the SQL statement which still had an open cursor:
SELECT * FROM sqlite_master
Thinking about this, it is obvious: When querying sqlite_master, you request information about database tables. If one of the table gets altered or dropped, this might change the data listed in the resultset on which I still have a cursor.
Maybe this is a special case as usually you do not query sqlite_master a lot. But in case you do, this might be useful information.
To solve the problem, as the manual says, simply release the cursor using PDOStatement::closeCursor() before dropping/altering tables.
In case you use sqlite, a serverless, transactional SQL-complete database engine, you might also know phpliteadmin. It is a web-based DB admin tool just like phpMyAdmin is for MySQL. It is small and does not require complex installation just like sqlite. It is great to do most simple operations on a sqlite database, but in my opinion it still has some important limitations and bugs.
One thing I recently missed was the possibility to import CSV files into a SQLite database. The SQLite shell makes this very easy (if the file has the correct syntax). But this has also some limitations and you have to leave the phpLiteAdmin GUI and start a SQLite shell. So I thought it should be part of phpLiteAdmin. It already had some GUI options for CSV import, but the actual import implementation was missing, so I implemented CSV import myself. On the way testing the exported csv and importing it again I noticed some export bugs of phpliteadmin like this one and fixed them on the way.
So those are the changes I did to phpLiteAdmin:
- implemented CSV import with some features like:
- you can define Enclosure, Escaper, Null and first-row-field-description just like when exporting
- using PHP’s fgetcsv()
- can import files that were exported with phpliteadmin using the same settings!
- can even import files exported with phpliteadmin without my corrections and improvements (see below), although they are not “standard CSV”.
- removed line terminator option because:
- this was not implemented
- in my opinion anything else than a linebreak does not make sense, so useless feature
- Fix: Tables with indexes were exported multiple times
- Fix: Last column was terminated which is not usual in CSV
- NULLs are not enclosed anymore to allow distinction between “NULL”-text
- FIX: Removing CRLF was not implemented
- FIX: Tables that were not exported caused additional linebreaks in output
You can find anything related to these changes in phpLiteAdmin’s bug tracker.
You can also get my version with the improvements from the bugtracker or download it here.
I really hope this will make it into the next release of phpLiteAdmin, but unfortunately I did not get any feedback by the developers of phpLiteAdmin yet. Hopefully this is not getting a fork 😉
I hope you like these features and bugfixes. You can give me feedback here or in the phpliteadmin bug tracker.
I really think a good database management tool for SQLite is needed and phpLiteAdmin has the potential to become one. We will see.
You can also tell me if you use some other management tool for SQLite which you think is way better 😉
Update 2012/05/13: The phpliteadmin developers just announced that an official new version including these improvements will come up soon. Great news, I am looking forward to this new version!