Change Log

Unreleased

New Features:

• Schemas - Named subdrectories of databases. Every database has a schema named 'public' which is the default schema and uses the same directory as database. Schemas are now what hold tables and sequences, not databases.
• Sequences - From SQL standard. Named numeric values that you can control the start value, the min and max value, whather it can cycle etc.
• Identity columns - From SQL standard. Like sequences for table columns (or a more powerful AUTOINCREMENT). For backwards compatibility AUTOINCREMENT columns are now internally identities using same behavior it had before.
• Keys - Store primary key lookup table in memory.

Public API changes:

• Dropped all PHP 4 support.

• All classes moved into a FSQL namespace.

• All classes dropped their "fSQL" prefix in their names.

• Added select_schema($db_name, schema_name) method which registers the schema named $schema_name in the databased named $db_name to be the default schema for queries.

• Added fetch_single($results, $column = 0) method which takes in SELECT query results and returns a single column value from each row in the results. The $column parameter is optional; if none is given it return first column of each row. If the $column parameter is a number, it is index of the column to return each time. If it's a string, its the column name/alias of the desired row to return.

   $results = $fsql->query("SELECT sqrt(colA) FROM mytable");
   while(($sqrt = $fsql->fetch_single($results)) !== false) {
   	echo $sqrt."\r\n";
   }
  

• Added fetch_all($results) method to return the entire SELECT query result set at once in an array.

• All API methods now return false instead of null for errors and end of results for fetch methods

New Queries:

• ALTER SEQUENCE
• CREATE SEQUENCE
• CREATE SCHEMA
• DROP SEQUENCE
• DROP SCHEMA
• MERGE
• SHOW COLUMNS - From mySQL. Alias of DESCRIBE TABLE. SHOW COLUMNS allows FULL keyword to change query to "SHOW FULL COLUMNS" to get extra information.

New SQL Functions:

• AVG
• ANY
• CURRENT_CATALOG
• CURRENT_SCHEMA
• CURRVAL
• EVERY
• NEXTVAL
• SOME

SQL Syntax and Query Changes:

SELECT:

• Added GROUP BY and HAVING support for SELECT
• In SELECT, LEFT, RIGHT, and FULL joins support optional OUTER keyword before it.
• Can now ORDER BY SELECT column numbers rather than having to use names/aliases all the time.
• Added support for NULLS FIRST/NULLS LAST in ORDER BY.
• Added support for SQL Standard FETCH FIRST ROWS and the optional OFFSET before it. FETCH FIRST and LIMIT can't be used together.
• Added support for LIMIT length OFFSET offset form of LIMITs

INSERT:

• Added multi VALUE support to support inserting multiple rows in one query.
• Added "DEFAULT VALUES" clause to populate the row with its defaults.

UPDATE:

• Added IGNORE keyword support for UPDATE queries from mySQL (which fSQL already supported for INSERTs). If primary/unique key conflict occurs on an UPDATE, the UPDATE should fail. The IGNORE keyword will cause fSQL to ignore and not update any row that produces a unique/primary key violation.

TRUNCATE:

• Added optional [ CONTINUE | RESTART IDENTITY ] clause.

CREATE TABLE:

• CREATE TABLE ... LIKE tableName now supports INCLUDING/EXCLUDING IDENTITY and DEFAULTS

ALTER TABLE:

• Added ADD COLUMN
• Added DROP COLUMN
• Added SQL Standard "ALTER COLUMN" command/clause which takes the column name which can be followed by any of these:
  • SET DATA TYPE
  • SET DEFAULT
  • DROP DEFAULT
  • DROP IDENTTY
  • New identity values if the columns is an identity one. (ie. RESTART WITTH 50 CYCLE INCREMENT BY 3)
• Now supports running multiple actions in one query.

SHOW TABLES:

• Added FULL keyword support to SHOW TABLES.

Bug fixes:

• Fixed UPDATE to actually check if the row violates a unique/primary key before updating.
• Rewrote most of SELECT and WHERE functionality. Better table data loading, joins, and where as result.
• Fixed WHERE clause parsing to allow backticks (`) around column references in a WHERE clause.
• Fixed CREATE TABLE to allow backticks on CONSTRAINTS such as PRIMARY KEY definitions
• Fixed DESCRIBE/SHOW COLUMNS to specify the typename in the results
• Fixed bug with joins not loading second table into results.
• Fixed ON clause for JOINs to parse and load correctly
• Fixed SQL function CURDATE and CURTIME to run
• Fixed SQL functions MONTH and WEEK to be registered with the environment
• More locations that accept backtick (`) identifiers
• Fixed SELECT joining with an empty table
• Fixed SELECT query not always parsing correctly
• Fixed DROP DATABASE to actually delete the tables.
• Fixed SQL function CONCAT to work properly.
• Fixed INSERT not always padding missing data items with NULL.
• Fixed SET clauses not properly parsing if in = inside a string value.
• Fixed INSERT and UPDATE primary key duplicate values checking.

Other:

• Lots more error checking
• Removed all remaining uses of ereg functions deprecated in php 5.3
• Dropped PHP4 compatibility. All php5+ now.

fSQL v1.3.1

Improvements:

• The "AS" keyword is now optional when specifying alias in the SELECT expressions. Example: SELECT group g FROM user; is the same as: SELECT group AS g FROM user;
• Specifying an alias for a function call in a SELECT expression is now optional. If one is not specified, the column name will be the entire function call as a string. Example: $results = $fsql->query("SELECT sqrt(colA) FROM mytable"); while($row = $fsql->fetch_assoc($results)) { echo $row['sqrt(colA)']."\r\n"; }

Bug fixes:

• Fixed areas of query parsing to be more lenient on the amount of whitespace (specifically CREATE TABLE and SELECT).
• Fixed bug that caused calls to the supported "aggregate" or "grouping" functions (count, max, min, sum) to fail.
• Fixed parsing bug in the SELECT expression list.

fSQL v1.3

Improvements:

• Better error detection in define_db() if database path does not exist.
• Using NULL on an auto increment column during an INSERT now causes the increment instead of trying to INSERT NULL.
• Improved locking system.
• Added support for LOCK TABLES and UNLOCK TABLES queries. Does not support aliases yet. Ignores "LOCAL" and LOW PRIORITY" modifiers.
• Trying to modify a READ locked table will cause query() to return null and set the error message.

New SQL Queries:

• LOCK TABLES
• UNLOCK TABLES

SQL Syntax and Query Changes:

• Added support for "CONSTRAINT name PRIMARY KEY (column)" style syntax for keys in CREATE TABLE and ALTER TABLE.
• Renamed ALTER TABLE's "ALTER COLUMN" command to "CHANGE COLUMN" to match mySQL.
• Added another syntax for renaming tables "ALTER TABLE table1 RENAME [TO] table2".
• Added "INDEX" as an alias to "KEY" in constraint definitions.

Bug Fixes:

• Fixed many bugs involving reading and writing tables.
• Fixed bug in DELETE.
• Fixed bug in renaming tables.
• Fixed bugs in ALTER TABLE.
• Fixed few minor parser bugs.
• Fixed INSERT auto increment bug that occurs when the incremented column is not specified in query.
• Fixed a table's first auto increment value to be 1 not 0.
• Fixed the locking system to prevent the accidental downgrading of write locks to read locks and having to upgrade back again.

fSQL v1.2

New/Improved features:

• Improved API ** The old version's class (Database) has been replaced with fSQLEnvironment *** The old Database class's db_connect() has been replaced by fSQLEnviroment's define_db and select_db methods *** The old Database class's db_disconnect() has been replaced by fSQLEnviroment's close method ** New object-oriented backend to allow different backends in future ** More functions found in the mySQL API added to fSQLEnviroment: *** affected_rows() *** close() *** data_seek() *** insert_id() *** select_db()
• Ability to define multiple databases ** Databases now have names so they can be used in queries ** Able to select current database to use as default in queries
• A much smaller table output format to reduce the size of tables
• Improved table locking to prevent simultaneous writes
• Transaction support ** Autocommit is on by default but BEGIN or START TRANSACTION will create a transaction than ends on a COMMIT or ROLLBACK and returns to autocommit mode.
• Partially acknowledges column types ** fSQL now supports ENUM types ** fSQL knows whether the column is either an int, a float, a string, or an enum ** Date/Time types not added yet

New SQL queries:

• BEGIN
• COMMIT
• DROP DATABASE
• ROLLBACK
• SHOW DATABASES
• SHOW TABLES
• START TRANSACTION
• USE

SQL syntax changes:

• Allows the use of backtick quotes (`) around identifiers
• Allows a semi-colon to optionally appear at end of a query
• Ability to specifiy primary keys and unique indices.
• Ability to specify the database's name when using a table. ** This allows tables from other defined databases to be used in queries.
• Support for negative numbers
• Improved support for floating point numbers
• New supported SQL operators: ** LIKE ** NOT LIKE ** NOT REGEXP ** NOT RLIKE ** REGEXP ** RLIKE
• New supported SQL functions: ** DATABASE ** INSERT_ID ** ROW_COUNT ** SHA
• Added "CREATE TABLE table_name LIKE other_table" syntax
• Added IGNORE option on INSERT queries
• Added DISTINCTROW and ALL options to SELECT for mySQL compatibility
• Added ALTER COLUMN command for ALTER TABLE

Important bug fixes:

• If specified path to database does not exist, fSQL will attempt to create it
• Allows use of symbolic links in database paths
• Ability to use ' inbetween ' '
• Easier implicit conversion between numbers and strings where needed
• Fixed temporary tables
• Fixed setting columns to NULL
• Bug fixes in WHERE clauses
• Fixed SQL's LOG functions

• Improved reading of CREATE TABLE queries
  • Fixed many bugs related to old case-sensitivity of keywords
  • Fixed bugs involving calling SQL functions
  • Far more leniency in amount of whitespace used in queries