We all know how boring text file handling is for saving information: (Online users, visit counters...)
even more if we have to insert, extract, update or manage that information in any way.
FileSQL is a group of functions that let us work with plain text files through queries the way we would launch them against a database such as mySQL. Therefore, we would be able to launch INSERT, UPDATE, DELETE or SELECT sentences to insert, update, delete or select information from our files. .
The idea is quite simple: a directory would be a database. Each file of the directory would be a table. In each text file there would be several data fields separated by the ":" character.
FileSQL functions are analog to their mySQL equivalent ones. This is: mysql_fetch_array would turn into file_fetch_array. Besides, we have file_query, file_num_rows, file_fetch_row, file_connect, file_fetch_assoc file_close, and a specific function to import phpmyadmin '.sql' files directly into fileSQL system.
You just have to include the fileSQL.php file in your script to make fileSQL work, and start managing your file data.
You must create a new directory with all the permissions (its name would be the one of the db). You can create all the text files you want in the directory. However, in every directory there must be a special file called "fields.fql" with the field definition:
Let's see it with an example:
Content of fields.fql:
# Example of field definition for fileSQL
# Format:
# file:field1:field2:field3 ...
# A '*' leading a field name indicates AUTO_INCREMENT
users.txt:nick:password:date
shop.txt:*id:product:price
This file says that we have 2 text files, noticias.txt and usuarios.txt, and then the fields separated with ":". The content of users.txt could be the following:
john:s3cr3tk3y:20050111
peter:n0sp00n:20041203
smith:r0s3bud:20050320
There should also be another file called shop.txt with the field order previously declared in fields.fql
<?php
include ("fileSQL.php");
// We 'connect' the directory
// the directory must have read and write permissions
$connection = file_connect("./files");
/*
Example of queries fileSQL can perform
$query = "INSERT INTO file.txt (field1,field2,field3) VALUES ('value1','value2','value3')";
$query = "INSERT INTO data.txt VALUES ('value1','value2','value3','value4')";
$query = "DELETE FROM data.txt WHERE field1 = 'value' AND field2 = 'value2'";
$query = "UPDATE data.txt set field1='value1', field2='value2', field3='value3' WHERE field1 LIKE 'A%' AND field2 <= 35";
$query = "SELECT field1,field2 from datafile.txt WHERE (field1 LIKE '%value%' OR (field2 < '3' AND field3 = 'value') ORDER BY RAND() LIMIT 10";
$query = "SELECT field3 FROM data.txt ORDER BY field1 DESC limit 2,3";
// queries with special functions
$query = "SELECT * from data.txt ORDER BY RAND()";
// queries with NOT LIKE, IN and NOT IN
$query = "DELETE FROM data.txt WHERE field1 NOT LIKE 't%' AND colors IN ('red','green','blue')";
$query = "DELETE FROM data.txt WHERE countries NOT IN ('Spain','France','Germany')";
// valid as of fileSQL 1.1beta*
$query = "SELECT AVG(price),MAX(price) from shop.txt ";
$query = "SELECT MIN(date) FROM file.txt WHERE id > 4 AND field2 LIKE 'p%'";
$query = "SELECT COUNT(*),SUM(field1) FROM data.txt";
$query = "SHOW TABLES";
$query = "SHOW COLUMNS FROM data.txt";
*/
/* To launch the query we use file_query */
$result_id = file_query($query,$connection); // the resource link ($connection) is mandatory
echo 'Records found: ' . file_num_rows($result_id); // file_num_rows returns the number of records.
echo file_result($result_id,0); // works just as mysql_result ( resource result, int row [, mixed field] )
/* Loop through the results
- file_fetch_array
- file_fetch_row
- file_fetch_assoc */
// i.e:
echo '<table>';
while ($data = file_fetch_row($result_id)) {
echo '<tr><td>'.$data[0].'</td><td>'.$data[1].'</td></tr>';
}
echo '</table>';
// or
echo '<table>';
while ($data = file_fetch_assoc($result_id)) {
echo '<tr><td>'.$data['field1'].'</td><td>'.$data['field2'].'</td></tr>';
}
echo '</table>';
file_close($connection);
?>
There are obviously many limitations. FileSQL cannot replace a complex database such as mySQL, for example.
- You can't arrange data by many fields at a time: ORDER BY campo1 ASC, campo2 DESC <- won't work
- You can't select in many tables: SELECT * from tabla1,tabla2 <- won't work
- The MySQL functions that work are: (since 1.1beta)
- RAND() -> ORDER BY RAND()
- RAND() -> ORDER BY RAND()
- MAX() -> SELECT MAX(field)...
- MIN() -> SELECT MIN(field)...
- SUM() -> SELECT SUM(field)...
- AVG() -> SELECT AVG(field)...
- COUNT() -> SELECT COUNT(field|*)...
- The conditions are very flexible and the may be as complex as wanted.
WHERE (campo1 ='valor' OR (campo1 like '%valor%' AND (campo3 != 'pepe' OR campo3 != 'luis))) ORDER... LIMIT...
Since 1.1beta the '_' wildcard can also be used
- The CREATE sentence is not finished, hasta ese momento, importSQL only works with an .sql with data, but it won't work with the structure of the tables (the table has to be previously defined).
- WARNING. FileSQL is case-insensitive by default: WHERE field1='john' won't fit in with JOHN (MySQL si). Use WHERE field1 LIKE 'john' instead.
- Mathematical operations do not work directly on the fields yet (they will, though. UPDATE tabla set field=field+1 <- won't work
- Other limitations that I can't think about now LOL
Credits
Queries syntax is very flexible (space, no space, with or without quotes, case-insensitive clauses), and the numer of possible queries are endles at this moment. Therefore, the debug works were very important to detect errors and problems in the queries (more will be found, please report them).
- Debug: spezial. (thanks for its exhaustive debug)
- Debug: Aeoris
- Debug: {Arias}
- Debug: Yosolito
- Debug: xlony
- Debug: BeTo
- Idea and programming: thessoro
EOF
fileSQL by thessoro
Bug reports to thessoro@gmail.com