I have always been thinking about how I can replace running MySQL with SQLite on my Mac for simple web applications. SQLite is basically a software that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. I think it should make developing and testing easier when a full-scale database is not required.
While administering SQLite locally is easy via the command-line or the SQLite Database browser, it’s not that straightforward to do it remotely. I could have installed phpLiteAdmin, which is based on phpMyAdmin, but then I thought it would be fun and educational for me to create one. Hence I started with a PHP framework, threw in a few AJAX libraries and plugins, and came up with P.S.A.
Version 1.0 supports the adding, updating and deleting of table data as well as the creating and dropping of tables. Only adding of column is allowed for altering table schema. What table is displayed for use depends on which user is logged in.
Logging into demo table,
- Click ‘Login’ link.
- Enter username/password as demo/demo.
- psa_demo table will be displayed.
- Click on ‘Add’ button to add a new row.
- Double-click on a field to edit.
- Select a row and click ‘Delete’ button to remove the row.
Logging in as administrator,
- Click ‘Login’ link.
- Enter username/password as admin/admin. (This account is disabled in my online version. Download the enabled version here.)
- Admin panel will be displayed.
- Click on table name in admin panel to edit/drop table.
- Click on add_new_table in admin panel to create a table.
- Click on psa_users in tables panel to add/remove a user or edit username, password and the table a user can access.
As SQLite is not a full-scale database and that PHP does not come with SQLite3 support, I have to google for a customised PDO driver and restrict the functionalities of DataTables. Sorting and searching have to be disabled as IgnitedDatatables generates SQL queries for MySQL but not SQLite3.
- Modify IgnitedDatatables to support SQLite sorting and searching functionalities.
- Any other user suggestions given in the comments below.
Any enhancements done by users are greatly welcomed and appreciated.