sbuberl / fsql

Flat-file SQL (fSQL) is a set of classes available in PHP that allows users without SQL database servers to select and manipulate flat-file data using SQL queries.

fSQL

Flat-file SQL (fSQL) is a set of classes available in PHP that allows users without SQL database servers to select and manipulate flat-file data using SQL queries.

fsql's Issues

Views

This is another old 1.4 branch feature that could get ported over. This is blocked by #9.

Composer Install

How do you install this via composer? I've tried composer require sbuberl/fSQL as well as composer require fsql/fsql and neither works.

New ALTER TABLE options

fSQL hasn't been able to add or delete a column since v1.1. Should really implement to those.

Also should add ALTER COLUMN ... SET DATA TYPE.

Better Date and Time support

The old v1.4 had better handling of dates and times both as a string and their timestamp value. Even added "WITH TIME ZONE" for times and timestamp.

Would be nice to port this over and probably upgrade it to use PHP's DateTime class internally.

"Customly parsed" SQL functions

Some SQL standard functions have special syntax to their parameters rather just comma separated. For example:

TRIM(LEADING 'x' FROM 'xxxbarxxx')
EXTRACT(YEAR_MONTH FROM '2009-07-02 01:02:03')

I did this before in the 1.4 rewrite branch. Just need to port them over.

Identity columns (and sequences?)

The SQL Standard has identity columns which are like sequences for the only the one column. They are much flexible than auto_increment because you can set start value, increment, min value, max value, and to cycle or not. And you can choose to not allow user to enter own values and values are auto generated.

Would be nice to have these. AUTO_INCREMENT would become the same internally as "GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1)"

Since all those fields are shared with sequences, should be easy to add CREATE SEQUENCE if I wanted.

Keys

Port key classes from old 1.4 branch so can use it with for collision checking for UPDATE and INSERT

Fix Insert and update collision detection for multiple column keys

Insert and update have collision detection that checks for duplicates in primary keys and unique columns and returns errors or ignores them. It is currently checking each column in the primary key for collisions individually rather an treating them them all one as one single value that will be used for detection.

Fix and re-enable MERGE

The MERGE statement was available in fSQL 1.0, but it was implemented incorrectly and commented out in 1.2. Fix to work as SQL standard expects and re-enable it.

Subqueries

Subqueries add a lot of power to any SQL language that fSQL just can't do. Been toying with the idea of seeing if I get them to work in fSQL without full rewrite of parser. We'll see.

Fix DELETE

DELETE using the WriteCursor is broken. It should not be using foreach() using the cursor because deleteRow advances the cursor and so does the foreach.

Delete row problem (1.3.1)

I do hope I am posting to the right place. The link from Flat-file SQL on sourceforge brought me here.
Also this is about version 1.3.1 and I see mention on this site to a 1.4... I have no idea if or how to make the 1.4 on this site, work... Anyway...

Lets see if I can explain this... If I create a database with three columns ID, fruit, and amount, and I populate it with entries the text get in my database is shown below...

10
0: 1;'Strawberry';22;
1: 2;'Apple';22;
2: 3;'Peach';22;
3: 4;'Plum';42;
4: 5;'Mango';42;
5: 6;'Cherry';22;
6: 7;'Orange';68;
7: 8;'Lemon';62;
8: 9;'Lime';50;
9: 10;'Banana';18;

The database works just fine.

Now lets say I use the delete command to remove Plum, Mango, Orange and Lemon. I am left with what is below and this is where the problem starts.

6
0: 1;'Strawberry';22;
1: 2;'Apple';22;
2: 3;'Peach';22;
5: 6;'Cherry';22;
8: 9;'Lime';50;
9: 10;'Banana';18;

There is two problems that can happen at this point... Well one that can happen and the second that never seems to fail to happen.

Problem 1, the one that always happens) Lets say I get a shipment of Pineapples, Oranges and Watermelons, 100 of each. I want to add them to the database, so I do.

The database ends up looking as follows:

7
0: 1;'Strawberry';22;
1: 2;'Apple';22;
2: 3;'Peach';22;
5: 6;'Cherry';22;
8: 9;'Lime';50;
9: 10;'Banana';18;
10: 7;'Pineapple';100;

Notice the Oranges and Watermelons are not added.

Now, nothing I do will allow me to add any more entries. This seems like a bug. Now I have tried repeating this, without my ID column so the database looks like:

10
0: 'Strawberry';22;
1: 'Apple';22;
2: 'Peach';22;
3: 'Plum';42;
4: 'Mango';42;
5: 'Cherry';22;
6: 'Orange';68;
7: 'Lemon';62;
8: 'Lime';50;
9: 'Banana';18;

But the problem still happens.

The only way I have found to solve the issue is to manually change the leading numbers... So,

6
0: 1;'Strawberry';22;
1: 2;'Apple';22;
2: 3;'Peach';22;
5: 6;'Cherry';22;
8: 9;'Lime';50;
9: 10;'Banana';18;

Becomes...

6
0: 1;'Strawberry';22;
1: 2;'Apple';22;
2: 3;'Peach';22;
3: 6;'Cherry';22;
4: 9;'Lime';50;
5: 10;'Banana';18;

When that is done the database works normally again, but it is not really feasible to manually edit the database each time a row is deleted when the database can have hundreds or more entries... Would you have any idea on how to fix this problem, or is it a intended design?

Oh and the second issue that shows up sometimes is that once a row has been deleted, any row after the one that was removed may or may not be able to be read and may or may not be able to be altered or updated.

Thank you for any assistance you might be able to provide.

Add full field info from SELECT results

Currently only the column names are kept by the result set. Should keep array/class similar to this to keep original tale column info, the data type, and more. Also need to update our fetch_field to be more like the mysqli one.

This would allow the additions of views, subqueries, and maybe more.

Replace preg_match_all in create table

There is a preg_match_all in create table that parses the column definitions and key definitions. But it is a row doesn't match, it is ignored. Switch to regular preg_match to parse each definition and report back errors. May have to add regex to allow Foreign keys definitions even if not supported so it is portable with other SQL DBs.

Refactor Environment

Environment is a mess. Last stable release this was all one php file (with less features).

In preparation for #10, first thing I want to do is separate the parsing and execution logic. The execution logic should go in classes in Statement classes. The parsing can stay in Environment for now. Right now, only all the Create queries and Insert have Statement classes.

While adding these Statement classes is a good time to add testing. Rather than adding unit tests for the Statement, add testing for the SQL command. See tests/SQL for example.

Once everything is in a Statement would to like pull out all the parsing logic in Environment into a new Parser class but not required.

Fix FROM table list split in SELECT

SELECT is unfortunately exploding on commas to separate the tables, this won't allow multi-column USINGs nor multiple param functions in ON.

Make API more like mySQLi

While I'm working on Prepared Statements which are modeled after mySQL Statements. My original API was based on the legacy mySQL driver. I want to update the API to be more like mySQLi where appropriate for a server-less database engine.

Implement multiples row INSERTs

fSQL can only INSERT one row at a time (unless you use INSERT...SELECT). Update it to support multiple rows in the VALUES clause:

INSERT INTO table1 (first,last) VALUES
   ('Fred','Smith'),
   ('John','Smith'),
   ('Robert','Smith');

Prepared Statements

This are pretty much a requirement nowadays especially for blocking SQL Injection

The old 1.4 branch add prepared statements but no placeholder support.

Schemas

Implemented these for old 1.4 branch. Basically work as subdirectories for db. Except a 'public' default one which is stored in database directory. All other objects would be child of schema instead of DB. Port this over soon.

Adds docs to website

The docs are on fsql.org are pretty lacking. Would like to add documentation for each query with the allowed syntax options, functionality, and maybe some examples. Plus more detail on both SQL functions and the the public API.

MERGE DELETE and more UPDATE clauses

While I'm refactoring and testing MERGE next, probably a good time to add more MERGE support. Currently, MERGE only allows one UPDATE clause and has no DELETE support.

TypeError on manipulating queries

hey

thanks for this great library.
i'e got it up and running and was able to create tables. but as i try to insert into a table i#ve got an error
Argument 1 passed to FSQL\Database\TableCursor::__construct() must be of the type array, null given

thats what my create and insert queries look like

$fsql->query("CREATE TABLE usergroup ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(255), desc VARCHAR(500), active INT, created DATE, PRIMARY KEY(id) )") or die($fsql->error());

$fsql->query( "INSERT INTO usergroup (name, desc, active, created) VALUES ('admin', 'Users with administrative Rights', 1, '2017-01-01')" ) or die($fsql->error());

would be awesome if you could point me to what i'm doing wrong.
i would really like to use the library and would contribute documentation in contrary.

thanks in advance
daniel