As an aficionado (in the meaning of devotee) of Open Source Software, I am tempted to present here, a php script for a Sqlite (& Sqlite3) Database Creation, Table Creation, Editor for SQlite file databases which can be embedded and used in USB Pendrives, or Offline/Online Computers, without installing the database files in Memory. SqLite (&SqLite3) seem to suit this purpose of going for file databases. In case of file databases, the data is stored in .sqlite files. Though the .sqlite files are not like flat files genre such as 'csv' (Comma Separated Values) files which can be opened and read/edited in text editors, yet, at least file databases have independent existence in the respective folders either on USB drives, or in Computer Hard-disk folders. Unlike other mainstream databases such as mysql, postgresql, which require database files to be stored in Computer Memories, file databases, especially sqlite databases can use pdo sqlite connections, and can not only be stored in USB pen drives, but also be conveniently backed up/copied among usb-to-usb, usb-to-computer, computer-to-usb etc. In the absence of suitable php scripts (I felt that there is a paucity of such non-commercial scripts), we may have to use packages such as sqlmanageers, and sqlite managers. For editing purpose, some extra proficiency in creating and executing sql statements will be needed if we have to make use of packages such as 'sqlite managers'.
First I shall give a screenshot of an SQLite Manager, which I could get, wherefrom I do not know, probably I might have obtained as default with my php sqlite3 installation, which I have tried to use for working with sqlite databases:
The php script which is presented here below does not require such 'sql' proficiencies, which sqlite managers need. First I copy and paste below, the php script used by me to design an sqlite3 database creator, table creator, rows inserter, rows updater, rows deleter, searcher for strings in columns of tables. After the php script, I shall explain some prerequisites for the script to work, some precautions, some pros and cons.
Some Pre-requisites
I have tested this script on Ubuntu (Linux) version 15.04 and 16.04. As php is platform-independent, it can be tested on Windows and probably Mac.
,br />Apache2 server (or its equivalent) is to be installed, and is to be configured to facilitate operations on http://localhost or http://127.1.1 .
An appropriate folder is to be identified for use as a sort of 'public_html' for read, write access of sub-folders and files. And this public_html folder should be such that even if it is exposed to Public, there should be no risk. That means sensitive files should not be stored on this folder. This precaution is very important not only for SqLite, but also for any other php scripts.
Our php script file, with some name such as phppdosqliteexp.php should also be stored in the same folder.
Configuring of php can be checked by using a phpinfo.php file, with three lines inside the file: <?php linebreak phpinfo(); linebreak ?> .
I have given database name as 'dyb1'. Readers can give their own database names. If that database does not exist in SqLite, it will create.
I have given table name as 'tyb1'. Readers can give their own names.
I have used total 11 columns in each table, as default, and standard for uniformity. Readers will have to change the php script, for changing the number of columns and column names.
Column names used by me in each table:
First column: The column name used by me in the above script is 'idpk'. It is an integer (number) column. pk signifies 'primary key'. I have also used the attribute 'AUTOINCREMENT', which means we need not fill up this column. The PHP itself will give id numbers automatically. We have to keep in mind, the distinction between the column idpk and row-numbers (record numbers) in a table. When a particular record with a particular idpk no. is deleted, it will not get replaced. That particular NO. will be missing in the Serial Order. When we search for a record, with that deleted particular idpk record, we can only get blank text areas. Deleted 'idpk' records cannot be revived (to the best of my experience. Readers may have a different experience).
Row Numbers on the other hand are rather liquid, like line numbers in a text editor. When a record is deleted, the next record will occupy the row. Hence, searching row-numbers will not be convenient. Searching with idpk column, or any other text string in columns such as f1,f2,f3,f4,f5 will show results, if there are rows which contain the search string. The flexibility is: We can search in any column of our choice, one-by-one.
text columns: f1, f2, f3, f4, f5.
Decimal columns suitable for mathematical calculations: d1, d2, d3, d4, d5.
To facilitate searches even in large tables, this php script opens with default search in, default database say dyb1, table say tyb1, default search in column f1, and search string as 'a' The letter 'a' has been taken as default search string because, there will be very few tables which do not use 'a' as value in their text columns. After, running the php script, if Readers want to search for any other search string, they can simply change the search string in the search box, type the new search string, keep the ok box above submit button as blank, and click the submit button. Search Results will be shown record-by-record (i.e. row-by-row). This is necessary because, we may want to edit that particular record and update it, or delete it altogether. If we wish to go to next record, we have to type n in the ok box, and click submit.
The ok box (text area) supports following letters:
s - for searching.
i - for inserting.
u - for updating.
d- for deleting.
n - n to go to next record
p - g to go to previous record.
g - First we should type the idpk number we want, in the idpk box. Then type g in the ok box. Then Click submit button.
Note: There seems to be some bugs, when the search results / records go beyond the last row, or past the first row. The java script 'alert' of 'past the first record', 'past the last record' seems to be annoying. This, I shall try to resolve and post a fresh php script, as soon as I rectify them.
I welcome suggestions, corrections, and criticism.
To come back, to continue adding, deleting, modifying.
No comments:
Post a Comment
ఘోరమైన విమర్శలకు కూడ స్వాగతం, జవాబులు ఇవ్వబడతాయి. Harsh Criticism is also welcome.