Posql is the database class by pure PHP language it use only all-in-one file as the database. It operates regardless of the version of PHP. It is aimed to be able to handle more readily than SQLite. Posql is a design which conforms to SQL-92. PosqlAdmin (database manager) is included in the package which is able to operating the database with GUI. You can be operated the Posql by using PEAR::DB or PEAR::MDB2, and CakePHP framework with DBO driver. Posql implemented prepared-statements and bind mechanism that compatible with the PDO class.
The PHP syntax can be used for the expression (i.e. WHERE clause etc.). It is also possible to evaluate it by SQL syntax by using the function and the grammar of the SQL-92 standard. The evaluation of the expression can switch by the evaluation engine switch function according to the PHP mode, SQL mode, and that time.
Posql does not use the global space dirty so as not to collide with other libraries and frameworks and be designed. Shall be defined only by a few individual classes, Posql* (Posql_Config, Posql_Utils, etc.) Defines a class of such.
This document handles the SQL-engines-mode.
Posql understand basic SQL language. However, a part of function is skipped or is handled as an error. Which part of SQL language does Posql support or it explains whether to do in this document.
This document is not all of SQL grammar implemented with Posql. If you want more documents, see the the source code "posql.php" in class "Posql_Parser".
Posql implements the following commands, or grammars.
Comments ::= SQL_Comment | PHP_Comment SQL_Comment ::= -- single_line PHP_Comment ::= // single_line | # single_line | /* multi_lines */
Comments are not SQL commands, SQL queries can be written in free. When these are to be parsed is simply ignored. Comments can contain text from anywhere can put a white space.
SQL comments are valid until the end.
Multiple-line comment may be something over the line. New SQL statement is a multi-line comments start with the end point.
SQL statement ::= { BEGIN | START } [TRANSACTION [transaction_name]]
Posql supported the transaction with a rollback and an atomic committing from Version 2.09. The transaction becomes either whether the processing of the data base between the blocks is 'Everything is executed' or 'Nothing is executed'.
BEGIN command to initialize the transaction and start the block. If BEGIN was executed, the SQL statement will be process in a single transaction block after an explicit COMMIT or ROLLBACK to be executed.
For compatibility with MySQL BEGIN can be treated by the alias named START.
Optional transaction_name is ignored. Posql (Version 2.17) does not support nested transactions in. It is an error trying to start another transaction in the transaction.
CREATE DATABASE, DROP DATABASE commands are exceptions, the transaction is not supported.
To execute multiple statements within a transaction block, which helps to ensure consistency of data, at the start and at the end of the transaction processing speed and a temporary impact on disk usage.
Posql (Version 2.17) never generate a temporary file. It thoroughly makes, and processes a temporary backup area, the update, and the deletion area in all one database (File) same as the transaction. Therefore, influence in the transaction to the decrease at the processing speed etc. especially.
When the database is operated, appropriate lock processing (exclusive operation) is automatically done. Unless there is a need for consistency, Posql on dealing with transaction does not need to do frequently.
SQL statement ::= { COMMIT | END } [TRANSACTION [transaction_name]]
COMMIT command, starting (BEGIN) and commit the transaction.
Confirmed as a permanent process for all transactions made within the current block, all databases are reflected in the actual process results and terminate the transaction.
Use ROLLBACK command to abort the transaction. The transaction status is not started even if you issue a COMMIT particular problem, but an error occurs.
END command is alias for COMMIT.
More discussion about Posql transactions are described in the BEGIN section.
SQL statement ::= CREATE DATABASE [IF NOT EXISTS] database_name database_name ::= physical_file_path
CREATE DATABASE command will create a new database. The database will be generated physically as the one file. Write permissions for the target directory (permissions) If you do not have the connection timeout or an error may be. File is generated as a database, their permissions are 666 (rw-rw-rw-) are set. The database is written to the header information and meta information.
The error does not occur even when the database which tries to be made exists when IF NOT EXISTS is put before the name of the database. In this case, the return value only becomes imitation (FALSE) and it returns.
When the database is created, Posql will try changing the extension of the database to ".php". This provides a database security is addressed in the case of direct access. The database is recommended to be put outside the document root of the server. But, it might be impossible according to the environment. In this case, it is desirable to adjust the extension to ".php".
SQL statement ::= CREATE TABLE [IF NOT EXISTS] table_name (column_definition [, column_definition]*) column_definition ::= column_name [data_type] [column_constraint]* data_type ::= type_name | type_name ( number ) | type_name ( number , number ) column_constraint ::= PRIMARY KEY | DEFAULT value
CREATE TABLE command creates a new table. Basically, the keyword "CREATE TABLE" after the name of the new table, and column definitions and constraints parentheses() are enclosed in follows.
IF NOT EXISTS can be given, even if the table already exists, you can prevent the occurrence of errors.
Definition of each column has a data type of the column after the column name, followed by optional constraints. But, Posql does not have any data type. If you type, it is running PHP will be dependent on the type. As pseudo-types, please refer the Types section. Therefore, it is no limited and the data that is stored in any data type column by column is available. No concept of maximum value. All columns will be variable.
DEFAULT constraint specifies a default value when you insert data. Default value, INSERT applies if the value of the column is omitted and the command executes.
PRIMARY KEY (primary key) can be specified as constraints in the first column. Internally holds the rowid will be described below as a reference value. PRIMARY KEY is a numeric type, unless intentionally changed to auto increment, UNIQUEM is treated as a constraint.
In a single INDEX, UNIQUE, CHECK and has not been implemented. Instead Posql in the following three columns are one special.
These special columns update when the record is changed or are added. And, whenever the column of the same name (e.g. rowid) is generated, the name space is overwrited on the Posql side. These three special columns are added at the end of the column when the table is made.
CREATE TABLE foo ( id INTEGER PRIMARY KEY, name VARCHAR(255) DEFAULT '', addr INT(11) DEFAULT 0, title TEXT );
When it executes above SQL, the column of foo is:
It is generated with such the order.
id works as a reference to rowid by specifying
PRIMARY KEY for the first column id.
The definition of the table can be referred to by using the DESCRIBE command.
In a certain table, the number of columns and the number of restrictions are never limited without permission. The size of the data which can be stored in a single line is about 2 GB by default. This numerical value influence the maximum number of rows. This value can be set by setMax() and getMax().
The DROP TABLE command is used to delete the table.
SQL statement ::= DELETE FROM table_name [WHERE expression]
DELETE command deletes the record from the table. The format, "DELETE FROM" command followed by the table name you want to delete a record.
If you omit the WHERE clause will delete all the rows of the table. WHERE clause is specified, the expression is TRUE will be removed and only lines.
SQL statement ::= { DESCRIBE | DESC } [table_name | DATABASE]
DESCRIBE command to get the informations from current database.
If table name is omitted, it will be get all the information of all tables. The explicit meaning, instead of the abbreviated name of the table after DESCRIBE can put the DATABASE.
The table name is specified, the result set column names of that table, CREATE TABLE when SQL, DEFAULT constraint contains the value.
SQL statement ::= DROP DATABASE [IF EXISTS] database_name database_name ::= physical_file_path
DROP DATABASE command physical deletes the database made by the CREATE DATABASE command. A physical deletion is to delete the database (File). After DROP DATABASE is executed, it cannot be restored by Posql.
Even when the data base does not exist, the error can be prevented from occurring by specifying IF EXISTS.
SQL statement ::= DROP TABLE [IF EXISTS] table_name
The DROP TABLE command deletes the table made by the CREATE TABLE command. When DROP TALBE is executed, meta information and all the table data of the data base are deleted physically and completely.
Even when the table of the object does not exist in specifying IF EXISTS, the occurrence of the error can be controlled.
Types ::= NULL | BOOLEAN | NUMBER | TEXT | BLOB
This section explains pseudotype that Posql handles in the expression and the function. Even if the above-mentioned type is specified by the CREATE TABLE command, it is ignored. A part of expression might keep necessary though there is no type in Posql. It is used by TYPEOF or CAST etc. functions.
These types are very vague. In a certain specific value, when information as the type is needed, it might be useful.
this section explains the evaluation type (expression) which does not explain a single command but becomes most other part commands. All operators are included in the expression. Though it describes even at the beginning, and this expression. This document explain the execution engine of Posql becomes for SQL-engines-mode to handle.
If execution type is PHP-engines-mode, it depends on PHP operating everything. It include the expression, the operator, and all functions. Please refer to the PHP manual for details of the operator and the priority level that PHP recognizes if you want more informations.
Expression ::= expression operator expression |
expression LIKE_operator expression [ESCAPE escape_string] |
operator expression |
( expression ) |
column_name |
table_name . column_name |
function ( expression [, expression ... ] | * ) |
expression ISNULL |
expression NOTNULL |
expression [NOT] BETWEEN expression AND expression |
expression [NOT] IN ( expression [, expression ... ] ) |
expression [NOT] IN ( SELECT query ) |
( SELECT query ) |
CASE [expression] { WHEN expression THEN expression }+ [ELSE expression] END
LIKE_operator ::= LIKE | NOT LIKE
The column name specifies either of the name defined by the CREATE TABLE statement.
Posql understands the following operators. The highest-precedence operators listed at the top.
() ~ ! NOT CASE, WHEN, THEN, ELSE, END LIKE IN BETWEEN * / % MOD DIV + - || << >> < <= >= <> = == != <=> IS & ^ | AND OR XOR ,
Any value of Posql can handle a part of the expression.
In the arithmetic operation, "numeric value" is handled as number.
There are an equal sign and two kinds of signs of inequality respectively.
The equal sign is expressed by = or == .
The sign of inequality is expressed by != or <> .
The "|| operator" is "concatenation of string".
Right and left two character strings which are the operands are concatenated.
"% operator" calculates the remainder which divides the left side on the right side.
The CASE expression can be handled compatible to the SQL-92 standard.
The CASE expression returns one of the expressions the idea by evaluating a series of condition.
The CASE expression has two types below.
In the simple CASE expression,
one expression is compared with a series of simplicity expression and the result is decided.
The searched CASE expression allows for values to be replace in the result set based on comparison values.
Either types can use the ELSE argument (It is possible to omit it).
CASE can be used by an arbitrary statement and the clause which can use an effective.
For instance,
it is possible to use it by clauses such as the statements such as
SELECT, UPDATE, DELETE, and SET, select_list, WHERE, and HAVING.
SELECT ProductNumber,
CASE ProductLine
WHEN 'R' THEN 'Road'
WHEN 'M' THEN 'Mountain'
WHEN 'T' THEN 'Touring'
WHEN 'S' THEN 'School'
ELSE 'Not for sale'
END AS Category
FROM Product
In Version 2.17, the nest of CASE expression (Put the CASE in the CASE) is not supported.
LIKE operators are compared by the wild-card.
The pattern is specified for a right operand.
In LIKE, two wild-card characters of the following can be used in the pattern.
It gives priority to it in the escape character to make it match to the literal of the wild-card character. When the ESCAPE character is not specified, the ESCAPE character is assumed to be backslash(\).
The ESCAPE argument is used to specify a different escape character.
SELECT '100% OFF' LIKE '100|%_OF_' ESCAPE '|';
The LIKE operator does not distinguish capital letters and small letters.
You can override an internal implementation of LIKE by using Posql->createFunction() API.
SELECT statements can appear in expressions as either the right-operand of the IN operator.
As a scalar quantity, or as the operand of an EXISTS, ANY, SOME and ALL operator.
As a scalar quantity or the operand of an IN operator,
the SELECT should have only a single column in its result-set.
SELECT with the EXISTS operator,
the columns in the result-set of the SELECT are ignored.
The expression returns TRUE if one or more rows exist.
Returns FALSE if the result set is empty.
If no terms in the SELECT expression refer to values in the containing query,
the expression is evaluated once prior to other processing.
If the SELECT expression does contain variables from the outer query,
then the SELECT is reevaluated every time it is needed.
When a SELECT is the right operand of the IN operator,
the IN operator returns TRUE if the SELECT result contains NULL
and if the left operand matches any of the values in the SELECT result.
When a SELECT appears within an expression but is not the right operand of an IN operator,
then the first row of the result of the SELECT becomes the value used in the expression.
If the SELECT yields more than one result row, all rows after the first are ignored.
If the SELECT yields no rows, then the value of the SELECT will be NULL.
Posql supports the nested sub-query.
Though it described even at previous section, This document explain the execution engine of Posql becomes for SQL-engines-mode to handle.
If execution type is PHP-engines-mode, it depends on PHP operating everything. It include the expression, the operator, and all functions. In the PHP mode, file I/O, output and a part of other programmed control functions cannot be used by default. This is a minimum validation for SQL statement. Refer to Posql->getDisableFunctions() and Posql->setDisableFunctions() that in "Posql API Reference #getDisableFunctions" API manual bundled in package for details of these functions if you want more informations.
The following functions are available in default in Posql. Moreover, there are a lot of functions which make the function that modeled from SQLite implemented functions. As for the function defined by SQL99, everything is almost implemented.
You can add, or override arbitrary function in the database engine by using Posql->createFunction() API. Refer to manual "Posql API Reference" that bundled to the package for details of API if you want more informations.
Posql supports Unicode in all functions. The character string of the string function is handled as Unicode. The character string is not a unit of the byte and is handled in one character.
The following functions that modeled from SQLite.
The following functions defined by SQL-99.
SELECT CONVERT('aäeëiï' USING 'UTF-8');
The example above converts string 'aäeëiï' into UTF-8.
SELECT SUBSTRING('ABCDEFG', 3, 4);
-> 'CDEF'
SELECT SUBSTRING('ABCDEFG', -5, 4);
-> 'CDEF'
Two example above is a specification method to delimit by usual comma(,).
SELECT SUBSTRING('ABCDEFG' FROM 5 FOR 2);
-> 'EF'
The example above is an example which uses the SQL99 grammar.
SELECT TRANSLATE('ABC123XYZ', 'ABC', 'DEF');
-> 'DEF123XYZ'
The example above is a specification method to delimit by usual comma(,).
SELECT TRANSLATE('ABC123XYZ', '123' USING 'xyz');
-> 'ABCxyzXYZ'
The example above is an example which uses like the SQL99 grammar.
SELECT TRIM(' bar ');
-> 'bar'
The example above becomes the same operation as a usual trim() function,
and removes the white space before and behind the character string.
SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx'); -> 'barxxx'The example above deletes 'x' from LEADING (forward).
SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx'); -> 'bar'The example above deletes 'x' from BOTH.
SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz'); -> 'barx'The example above deletes 'xyz' from TRAILING (rear side).
Other Functions
Aggregate functions
Posql supports the following aggregate functions.
SQL statement ::= INSERT [OR on_conflict_algorithm] INTO table_name [(column_name[, column_name ...])] VALUES (expression [, expression ...]) |
INSERT [OR on_conflict_algorithm] INTO table_name [(column_name[, column_name ...])] SELECT query
on_conflict_algorithm ::= REPLACE
The INSERT command inserts a single, new row in the specified table. It is supplemented in the order of the row of the column at CREATE TABLE when the arrangement of the column is omitted. It is necessary to agree to the number of columns for which the number of values is specified when the arrangement of the column is specified. When the column of the columns arrangement does not appear, the DEFAULT value will be set. When the DEFAULT value is not specified, NULL will be set.
The conflict algorithm can be specified after INSERT and OR. Supported algorithm is REPLACE only, on Posql version 2.17 now.
The example of INSERT is shown as follows.
INSERT INTO my_table (id, name, message) VALUES (1, 'foo', 'Hello');
The second form of the INSERT statement takes its data from a SELECT statement. A new entry is made in the table for every row of the SELECT result. The SELECT sub-query able to handle simple statement or compound statements.
INSERT INTO my_table SELECT sub_id, sub_name, NULL FROM sub_table;
SQL statement ::= ROLLBACK [TRANSACTION [transaction_name]]
The ROLLBACK command returns the database to the state before it is begun for a present transaction.
ROLLBACK ends the transaction begun by BEGIN, and it backs rolling, all updates done in the transaction block are abandoned, and the transaction will be end.
If the transaction to end with normally, use COMMIT command. The error occurs though there is no problem even if ROLLBACK is issued if the transaction is not begun.
A detailed explanation of transaction by Posql is described in the BEGIN section.
SQL statement ::= REPLACE INTO table_name [(column_name[, column_name ...])] VALUES (expression [, expression ...]) |
REPLACE INTO table_name [(column_name[, column_name ...])] SELECT query
The REPLACE command is an alias for the "INSERT OR REPLACE" variant of the INSERT command. This alias is provided for compatibility other SQL database engines (e.g. MySQL). See the INSERT command documentation for additional information.
SQL statement ::= SELECT [ALL | DISTINCT] select_list [FROM table_list]
[WHERE expression]
[GROUP BY expression [, expression ...]]
[HAVING expression]
[compound_operator SELECT query]*
[ORDER BY sort_definition]
[LIMIT [number | ALL] [ { OFFSET | , } number]]
select_list ::= result_column [, result_column]*
result_column ::= * | table_name . * | expression [[AS] alias_name]
table_list ::= table_name [join_operator table_name join_argument]*
table_name ::= table_name [[AS] alias_name] |
(SELECT query) [[AS] alias_name]
join_operator ::= , | [NATURAL] [LEFT | RIGHT | FULL] [OUTER | INNER | CROSS] JOIN
join_argument ::= [ON expression] | [USING (column_name [, column_name ...])]
sort_definition ::= column_name [sort_order] [, column_name [sort_order]]*
sort_order ::= ASC | DESC
compound_operator ::= {UNION | INTERSECT | EXCEPT} [ALL]
The SELECT statement is used to query the database. The result of a SELECT is zero or more rows of data where each row has a fixed number of columns. The number of columns in the result is specified by the expression list in between the SELECT and FROM keywords. Any arbitrary expression can be used as a result. If a result expression is * then all columns of all tables are substituted for that one expression. If the expression is the name of a table followed by .* then the result is all columns in that one table.
The DISTINCT keyword causes a subset of result rows to be returned, in which each result row is different. The default behavior is that all result rows be returned, which can be made explicit with the keyword ALL.
The query is executed against one or more tables specified after the FROM keyword. If multiple tables names are separated by commas, then the query is against the CROSS JOIN of the various tables. The full SQL-92 join syntax can also be used to specify joins. A sub-query in parentheses may be substituted for any table name in the FROM clause. The entire FROM clause may be omitted, in which case the result is a single row consisting of the values of the expression list.
The WHERE clause can be used to limit the number of rows over which the query operates.
The GROUP BY clause causes one or more rows of the result to be combined into a single row of output. This is especially useful when the result contains aggregate functions. The HAVING clause is similar to WHERE except that HAVING applies after grouping has occurred. The HAVING expression may refer to values, even aggregate functions, that are not in the result.
The ORDER BY clause causes the output rows to be sorted. The argument to ORDER BY is a list of result columns that are used as the key for the sort. ASC (ascending order) or DESC (descending order) can be specified by the option after the sorting type.
The LIMIT clause causes set the upper bound of the number of lines returned as a result-set. When you specify number LIMIT of limits, the line more than the specified number of lines is never returned (Might be fewer than specification as a result of the inquiry). LIMIT ALL is the same as the case to omit the LIMIT clause. The optional OFFSET following LIMIT specifies how many rows to skip at the beginning of the result set. In a compound query, the LIMIT clause may only appear on the final SELECT statement. The limit is applied to the entire query not to the individual SELECT statement to which it is attached. Note that if the OFFSET keyword is used in the LIMIT clause, then the limit is the first number and the offset is the second number. If a comma is used instead of the OFFSET keyword, then the offset is the first number and the limit is the second number. This seeming contradition is intentional - it maximizes compatibility with popular SQL database systems.
A compound SELECT is formed from two or more simple SELECT connected by one of the operators UNION, INTERSECT, or EXCEPT. In a compound SELECT, all the constituent SELECT must specify the same number of result columns. There may be only a single ORDER BY clause at the end of the compound SELECT. The UNION operators combine the results of the SELECT to the right and left into a single table. The difference is that in ommited ALL all result rows are distinct where in ALL there may be duplicates. The INTERSECT operator takes the intersection of the results of the left and right SELECT. EXCEPT takes the result of left SELECT after removing the results of the right SELECT. When three or more SELECTs are connected into a compound, they group from left to right.
SQL statement ::= UPDATE table_name SET assignment_expression [, assignment_expression]* [WHERE expression] assignment_expression ::= column_name = expression
The UPDATE statement is used to change the value of columns in selected rows of a table. Each assignment in an UPDATE specifies a column name to the left of the equals sign (=) and an arbitrary expression to the right. The expressions may use the values of other columns. All expressions are evaluated before any assignments are made. A WHERE clause can be used to restrict which rows are updated.
SQL statement ::= VACUUM
The VACUUM command is an enhanced feature in SQLite, it implements to a similar command that modeled from PostgreSQL, and the enhanced feature of Posql that modeled above. If VACUUM is executed, the current database will be optimized physically.
The following keywords are used by Posql. Most keywords are SQL-92, reserved words or it is listed assuming that it is possible to become a reserved word. Posql is not reserving any keywords, but there is a possibility of colliding as an identifier. Posql does not distinguish capital letters and small letters of the keywords.
ALL AND ANY AS ASC AUTO_INCREMENT BEGIN BETWEEN BY CASE CHECK COLLATE COMMIT CROSS DATABASE DEFAULT DELETE DESC DESCRIBE DISTINCT DROP ELSE END EXCEPT EXISTS FALSE FOR FOREIGN FROM FULL GROUP HAVING IN INDEX INNER INSERT INTERSECT INTO IS JOIN KEY LEFT LIKE LIMIT NATURAL NOT NULL OFFSET ON OR ORDER OUTER PRIMARY RIGHT ROLLBACK SELECT SET SOME START TABLE TRANSACTION TRUE UNION UNIQUE UPDATE USE USING VACUUM VALUES WHEN WHERE XOR
Special columns
rowid ctime utime
Special words
POSQL POSQL_*
The main documents and the manuals are included in the following links.