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 is a reference of the Posql API usage from PHP that handles the class methods, and the properties.
This document is not all of API implemented with Posql. If you want more documents, see the each classes in file "posql.php".
Posql does not use the global function space in version 2.17.
Creates the instance of Posql.
$posql = new Posql;
When the filename which becomes the database of the object is passed to the arguments in this point,
the database is newly created.
If the database has already existed, it is skipped only.
Refer to Posql (Constructor) for constructor's details.
If it excludes SQL grammar,
You can handle Posql only a few methods that
query(),
fetch(),
isError()
and
lastError()
.
query() executes arbitrary SQL statement.
fetch() fetches one row of the result set.
isError() examines whether the error occurred.
lastError() pops and gets the error message of immediately before.
$posql = new Posql('foo.db');
$sql = "CREATE TABLE foo (
id INTEGER PRIMARY KEY,
name VARCHAR(255) DEFAULT ''
)";
$posql->query($sql);
if ($posql->isError()) {
die($posql->lastError());
}
SQL statement can be executed like the above example.
Refer to manual Posql SQL Reference that bundled to the package for details of the supported SQL syntax in Posql if you want more informations.
Continuously Insert (INSERT) the data
$sql = "INSERT INTO foo (name) VALUES ('Hello!')";
$posql->query($sql);
if ($posq->isError()) {
die($posql->lastError());
}
One row data was inserted in table foo. And get (SELECT) it.
$sql = 'SELECT * FROM foo';
$stmt = $posql->query($sql);
if ($posq->isError()) {
die($posql->lastError());
}
print "<pre>";
while ($row = $stmt->fetch()) {
print_r($row);
}
print "</pre>";
In the example above, the data of each rows are displayed by acquiring data from table foo, and using fetch() to loop.
The explanation of each method is recorded as follows.
$db_path = 'foo.db'; $posql = new Posql($db_path);
The example above is 'foo.db' as a filename when creating it (afterwards, the extension ". php" is actually added.), and the database is opened.
The database is physically created as one file.
Connected time-out or the error might occur
if the target directory has no writing attribute (permission).
When the file is created as a database, the permission is set to 666 (rw-rw-rw-).
The header information and meta data are written into the database.
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". You can change the extension by using setExt() or getExt() methods if necessary.
If passed argument table as string of the tablename, and fields as an association array. Posql will execute CREATE TABLE command. Even when the table already exists, it does not become an error. This works as well as "IF NOT EXISTS".
$db_path = 'foo.db';
$table = 'foo';
$defaults = array(
'name' => '',
'data' => NULL
);
$posql = new Posql($db_path, $table, $defaults);
When the instance is created specifying the arguments like this, it is processed as follows.
When the database "foo.db" does not exist, it is processed as follows.
When the database "foo.db" does exist, it is processed as follows.
The return value is different according to each SQL commands.
Return the instance of the Posql_Statement object when inquiry that result like SELECT is arranged.
The Posql_Statement class is designed and modeled from the PDO (PDOStatement) class
that the bundle is being done by PHP5 over.
As for the statement, the operation compatible with PDO is possible.
Refer to
PHP manual (PDOStatement)
for details of PDO if you want more informations.
Interchangeability with PDO is not limited to various operations.
PDO::FETCH_* constant can be similarly handled in Posql_Statement.
Refer to
Posql_Statement class methods
for details of the statement.
$posql = new Posql; $sql = "SELECT 100 * 100, 'hoge' AS hoge"; $stmt = $posql->query($sql); print $stmt->fetchAllHTMLTable();
The example above executes the SELECT command which omitted the FROM clause. Refer to fetchAllHTMLTable explains as followings for details of the acquisition method of this example.
$posql = new Posql('foo.db');
$sql = "CREATE TABLE foo (id PRIMARY KEY, name)";
$result = $posql->query($sql);
var_dump($result);
The example above executes the CREATE TABLE command. In this case, the output return value becomes TRUE or FALSE. Posql basically will never output the error. Refer to isError(), lastError() or getErrors(). Explains as follows about the error.
$sql = 'hoge';
$result = $posql->query($sql);
if ($posql->isError()) {
die('Error!');
}
The example above issues an illegal SQL command and causes the error specifying it.
$sql = 'hogehoge';
$result = $posql->query($sql);
if ($posql->isError()) {
die($posql->lastError());
}
The example above aborts the script with the error message immediately before.
Error information on Posql is maintained in stackable. When having called it next, the message acquired by lastError() had already been annulled. If lastError is called again, empty string('') is returned.
$posql = new Posql;
$posql->query('hello!');
$posql->query('how are you?');
if ($posql->isError()) {
foreach ($posql->getErrors() as $error) {
echo $error, "<br/>\n";
}
die;
}
The example above aborts the script with all error messages.
Try to connection with argument path as filename of the database. If the file path does not exist, createDatabase() is called. If the database is exist, checks whether the database is available format of Posql database by using isDatabase(). When argument table and fields are passed, createTable() is called. If passed argument table as string of the tablename, and fields as an association array. Posql will execute CREATE TABLE command. Even when the table already exists, it does not become an error. Returns TRUE for success or returns FALSE for failure.
$posql = new Posql;
$result = $posql->open('/path/to/file.ext');
var_dump($result);
The example above "/path/to/file.ext" is opened as a database, or creates a new file as database.
Try to creating the database with argument path as name of database. If the target directory has no writing attribute (permissions), the error might be caused, because the database file is physically created. When the database was created, the header information and meta data are written into the database.
The created database becomes the target database as for a current afterwards. You can change the target database by using setPath(). Argument perms is a file attribute of the database (permission). Default attribute is 666 (rw-rw-rw-). You can change the permission by giving perms an arbitrary numerical value.
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". You can change the extension by using setExt() or getExt() methods if necessary.
Returns TRUE for success or returns FALSE for failure.
$posql = new Posql;
$result = $posql->createDatabase('new_db');
var_dump($result);
The example above newly creates 'new_db' as a Posql database. Physically, the extension '.php' is added after the 'new_db'. And the file 'new_db.php' is created.
Argument table is string as tablename. Argument fields is an association array that the key as field name and the value as DEFAULT value. And defines the table.
To specify PRIMARY KEY. Give the first column name to argument primary_key as PRIMARY KEY. A primary key which is a main key maintains the value as a reference to rowid which is explained as follows. PRIMARY KEY is a numeric type and it works as auto increment and UNIQUE restrictions.
Returns TRUE for success or returns FALSE for failure.
$db_path = 'foo.db';
$table_name = 'foo';
$defaults = array(
'id' => 0,
'name' => 'anonymous',
'text' => ''
);
$primary_key = 'id';
$posql = new Posql;
$posql->createDatabase($db_path);
if ($posql->isError()) {
die($posql->lastError());
}
$result = $posql->createTable($table_name, $defaults, $primary_key);
var_dump($result);
The example above creates the database 'foo.db', and creates the table named 'foo' on the database.
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 string DEFAULT '',
addr int(10) DEFAULT 0,
text string DEFAULT NULL
);
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() method.
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 dropTable() method is used to delete the table.
Registers the PHP function to Posql as UDF (User Defined Function). Registered function can be called from within your SQL statements. The UDF can be used in any SQL statement that can call functions, such as SELECT and UPDATE statements.
Note: UDF can be called in SQL-engines-mode only.
Refer to
getEngine() or
setEngine()
for SQL mode and PHP mode (expression-mode).
Argument funcname is passed by the character string as a function name used by SQL statement. Argument callback specifies the callback function to process the defined SQL function. You can register by specifying the same as sqlite_create_function() function.
Note: Callback functions should return a type understood by Posql (i.e. scalar type).
// The function is defined beforehand.
function posql_udf_crc32($value = null){
// The CRC32 function of interchangeable MySQL.
$result = null;
if ($value !== null) {
$result = sprintf('%u', crc32($value));
}
return $result;
}
// Register the UDF function as CRC32.
$posql = new Posql('foo.db');
$posql->setEngine('SQL');
$result = $posql->createFunction('crc32', 'posql_udf_crc32');
if (!$result || $posql->isError()) {
die($posql->lastError());
}
$sql = "SELECT CRC32('abc')";
$stmt = $posql->query($sql);
var_dump($stmt->fetchAll());
In the example above, the function which generates the CRC32 checksum of compatible MySQL is registered, and it is executed. It makes it to SQL-engines-mode by setEngine() beforehand.
Deletes physically the target database as a file. After dropDatabase() is executed, it cannot be restored by Posql.
When argument path is passed, path is set to the current database. When the arguments were omitted, a current database becomes the target of processing. You can change the current database by using getPath() or setPath() methods.
It stands by until the lock is released when the database is locking when argument force is FALSE. When force is TRUE, the database is deleted regardless of the state of the lock. The initial value of force is FALSE.
Returns TRUE for success or returns FALSE for failure.
$db_path = 'foo.db';
$posql = new Posql;
$posql->createDatabase($db_path);
if ($posql->isError()) {
die($posql->lastError());
}
$result = $posql->dropDatabase($db_path);
var_dump($result);
The example above is creates the database 'foo.db', and drops it as soon as.
Deletes physically the table that is given as argument table. Meta information and all the table data of the database are deleted physically and completely.
Returns TRUE for success or returns FALSE for failure.
$defaults = array(
'name' => '',
'text' => ''
);
$posql = new Posql('foo.db', 'foo', $defaults);
$data = array(
'name' => 'foo',
'text' => 'Hello!'
);
$affected_rows = $posql->insert('foo', $data);
var_dump($affected_rows);
rows of the argument is given by the association array like the example above. Moreover, the array that has two dimensions can be given as argument.
$defaults = array(
'name' => '',
'text' => ''
);
$posql = new Posql('foo.db', 'foo', $defaults);
$data = array(
array('name' => 'foo', 'text' => 'Hi!'),
array('name' => 'bar', 'text' => 'Hello!')
);
$affected_rows = $posql->insert('foo', $data);
var_dump($affected_rows);
The processing speed improves when a large amount of data is added instead of using the memory area by giving the argument like the example above.
And, the value of special column ctime and utime changes at the time of inserting. The value of time() at that time is set respectively.
Returns the numbers of affected rows.
Argument table as the target table. Argument expr as the expression (i.e. WHERE clause). Argument row becomes according new data of row. Updates the data of table according arguments if result of expr is true value (TRUE) on each rows.
$defaults = array(
'name' => '',
'text' => ''
);
$posql = new Posql('foo.db', 'foo', $defaults);
$data = array('name' => 'bar');
$expr = 'rowid = 1';
$affected_rows = $posql->update('foo', $data, $expr);
var_dump($affected_rows);
Argument expr is WHERE clause on SQL statement. The initial value of expr is TRUE (Everything is updated).
And, the value of special column utime changes at the time of updating. The value of time() at that time is set.
Returns the numbers of affected rows.
Argument table as the target table. Argument expr as the expression (i.e. WHERE clause). Deletes physically the records in the table, according arguments if result of expr is true value (TRUE) on each rows.
$defaults = array(
'name' => '',
'text' => ''
);
$posql = new Posql('foo.db', 'foo', $defaults);
$expr = 'rowid = 1';
$affected_rows = $posql->delete('foo', $expr);
var_dump($affected_rows);
Argument expr is WHERE clause on SQL statement. The initial value of expr is FALSE (Nothing is deleted).
Returns the numbers of affected rows.
replace is basically the same as insert(). As a difference: When a UNIQUE constraint violation occurs, the pre-existing rows that are causing the constraint violation are removed prior to inserting or updating the current row. Thus the insert or update always occurs. The command continues executing normally following REPLACE.
Returns the numbers of affected rows.
Argument table as the target table. Argument expr as the expression (i.e. WHERE clause). Scanning the rows of the table, and gets the rows if the result of expr is TRUE on each rows.
Returns the instance of Posql_Statement that maintains result sets.
select() method can not join the tables. If you want to join the tables, use multiSelect() method.
The arguments are able to give to the select() method as an associate array. It is useful to given the argument as an array because select() has a lot of arguments.
$args = array();
$args['select'] = '*';
$args['from'] = 'foo';
$args['where'] = 'rowid = 1';
$stmt = $posql->select($args);
while ($row = $stmt->fetch()) {
print_r($row);
}
The key can be given by the association array as a clause like the example above. Each 'clause' (e.g. 'from') or the argument name (e.g. table) etc. can be given as the key to the array.
The result of 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 columns. Any arbitrary expression can be used as a result. If columns is * then all columns of all tables are substituted for that one expression. The array which is the result set becomes like the order of the row of columns.
Argument expr as the WEHRE clause. When expr is specified, the result set is limited only to the rows if the expression expr was TRUE.
The argument group 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 argument having is similar to expr 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 argument order causes the output rows to be sorted. The argument to order 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 argument limit be set the upper bound of the number of lines returned as a result-set. When the value delimited by comma(,) is specified, the number of lines skipped from the head of the result set can be specified. LIMIT can be specified like "offset (,) limit". offset and limit should be numeric value.
You can omit arguments excluding table (the first argument).
Returns the instance of Posql_Statement that has result sets.
Some examples are given as follows. These examples are using the simple expedient for the sake of brevity. Refer to fetch(), fetchAll() or fetchAllHTMLTable() for details of the method of acquiring the result set. (It is assumed that there is a suitable row in 'foo.db' beforehand.)
$defaults = array(
'name' => '',
'text' => ''
);
$posql = new Posql('foo.db', 'foo', $defaults);
$stmt = $posql->select('foo');
print $stmt->fetchAllHTMLTable();
The example above is all rows are included in the result set without the conditional expression.
$defaults = array(
'name' => '',
'text' => ''
);
$posql = new Posql('foo.db', 'foo', $defaults);
$args = array();
$args['table'] = 'foo';
$args['cols'] = 'name, text';
$args['expr'] = "name = 'foo'";
$stmt = $posql->select($args);
print $stmt->fetchAllHTMLTable();
The example above is the result set which the column name limited to the value 'foo'.
$defaults = array(
'name' => '',
'text' => ''
);
$posql = new Posql('foo.db', 'foo', $defaults);
$args = array(
'from' => 'foo',
'col' => 'COUNT(*)'
);
$stmt = $posql->select($args);
print $stmt->fetchAllHTMLTable();
As for the example above, all the numbers of rows are included in the result set.
$defaults = array(
'name' => '',
'text' => ''
);
$posql = new Posql('foo.db', 'foo', $defaults);
$args = array(
'from' => 'foo',
'cols' => 'COUNT(*) AS cnt'
);
$stmt = $posql->select($args);
print $stmt->fetchAllHTMLTable();
the example above, all the numbers of rows that as named cnt are included in the result set.
$defaults = array(
'name' => '',
'text' => ''
);
$posql = new Posql('foo.db', 'foo', $defaults);
$args = array();
$args['from'] = 'foo';
$args['select'] = 'name, MAX(rowid) AS max_rowid';
$args['group'] = 'name';
$stmt = $posql->select($args);
print $stmt->fetchAllHTMLTable();
The example above makes to the group by the column name, and acquires the maximum value of rowid.
$defaults = array(
'name' => '',
'text' => ''
);
$posql = new Posql('foo.db', 'foo', $defaults);
$args = array();
$args['from'] = 'foo';
$args['order'] = 'utime DESC, ctime DESC';
$stmt = $posql->select($args);
print $stmt->fetchAllHTMLTable();
The example above acquires all rows. The result set is sorted by utime and ctime as descending order.
$defaults = array(
'name' => '',
'text' => ''
);
$posql = new Posql('foo.db', 'foo', $defaults);
$args = array();
$args['table'] = 'foo';
$args['order'] = 'rowid';
$args['limit'] = 1;
$args['offset'] = 2;
$result = $posql->select($args);
print $stmt->fetchAllHTMLTable();
The example above becomes the limited result set that is upper bound value 1 and offset 2.
Argument tables as two or more tables. Argument expr as the expression (i.e. WHERE clause). Scanning the rows of the multiple tables, and gets the rows if the result of expr is TRUE on each rows. And tables are joined on.
As well as select(), the arguments are able to give to the multiSelect() method as an associate array. It is useful to given the argument as an array because this method has a lot of arguments.
$args = array();
$args['select'] = '*';
$args['from'] = 'foo LEFT JOIN bar ON foo.col = bar.col';
$args['order'] = 'rowid DESC';
$stmt = $posql->multiSelect($args);
while ($row = $stmt->fetch()) {
print_r($row);
}
The difference with the select() method is to specify two or more tables for tables. Two or more tables are united by using the joinning operator (JOIN etc).
Returns the instance of Posql_Statement that has result sets.
$defaults = array(
'name' => '',
'text' => ''
);
$posql = new Posql('foo.db', 'foo', $defaults);
$data = array(
array('name' => 'foo', 'text' => 'Hi!'),
array('name' => 'bar', 'text' => 'Hello!')
);
$affected_rows = $posql->insert('foo', $data);
$posql->createTable('bar', $defaults);
if ($posql->isError()) {
die($posql->lastError());
}
$data = array(
'name' => 'bar',
'text' => 'hi, bar!'
);
$posql->insert('bar', $data);
if ($posql->isError()) {
die($posql->lastError());
}
$tables = 'foo LEFT JOIN bar USING(name)';
$stmt = $posql->multiSelect($tables);
print $stmt->fetchAllHTMLTable();
In the example above, it joins by using USING(name).
Argument table as the target table. Argument expr as the expression (i.e. WHERE clause). Scanning the rows of the table, and counts the numbers of rows if the result of expr is TRUE on each rows.
The count() method can be handled as a short cut of
"SELECT COUNT(*)".
Returns the integer that counted numbers of rows.
$defaults = array(
'name' => '',
'text' => ''
);
$posql = new Posql('foo.db', 'foo', $defaults);
$data = array(
array('name' => 'foo', 'text' => 'Hi!'),
array('name' => 'bar', 'text' => 'Hello!')
);
$affected_rows = $posql->insert('foo', $data);
printf('<p>affected_rows: %d</p>', $affected_rows);
$count = $posql->count('foo');
printf('<p>count: %u</p>', $count);
The example above gets all the numbers of rows of the table 'foo', and displays it.
Argument table as the target table. Gets an associate array that is describing informations of the table table in the current database. When argument table is omitted, get the describing informations on all tables.
When table is specified, the column name of the table and the value of the DEFAULT restriction on CREATE TABLE are included in the result set.
Returns the instance of Posql_Statement that has result sets.
$posql = new Posql('foo.db');
$stmt = $posql->describe();
var_dump($stmt->fetchAll());
The example above outputs each information on database 'foo.db'.
The queryf method is like merging the query() method and the sprintf() function. The first argument is a format string. The next argument is an argument of the format string (conversion specifications). And next argument is next conversion specifications argument. In a word, this method handles variable argument. If called this method by one argument, it works same operation as the call of the query() method.
Like the query() method, the return value changes according to commands. If the result set will be an array like SELECT, return the instance of Posql_Statement. If operating the records like INSERT, UPDATE etc, return the numbers of affected rows.
You can use enhanced conversion specifications that based original format of sprintf() function on queryf() method.
"base64_decode('encoded_string')".
This will be possible use without destroying all character strings.
Especially, it is useful for data like BLOB.
$posql = new Posql('foo.db');
$sql = "SELECT * FROM %s WHERE strlen(%s) == %1n OR %s = %q";
$stmt = $posql->queryf($sql, "foo", "bar", 123456789, "baz", "value");
var_dump($stmt->fetchAll());
// It is to be converted as follows.
// "SELECT * FROM foo WHERE strlen(bar) == 1 OR baz = 'value'";
The example above is 123456789 has been converted into 1 by '%1n'. And, value is quoted by the single quotation mark.
$posql = new Posql('foo.db');
$sql = "INSERT INTO foo (name, text) VALUES (%10n, %.10q)";
$ret = $posql->queryf($sql, "123456789123456789", str_repeat("abc", 100));
var_dump($ret);
// It is to be converted as follows.
// "INSERT INTO foo (name, text) VALUES (1234567891, 'abcabc...')";
As for the example above, 123456789123456789 (18 digits) has been converted into 10 digits by '%10n'.
And, str_repeat("abc", 100) (300 bytes)
is replaced to 'abcabc...'
that has been converted into under 10 bytes by '%.10q' even with the dots(.).
// example: handle binary data
$posql = new Posql('foo.db');
$posql->createTable('image', array('name' => '', 'type' => '', 'data' => ''));
$path = "http://example.com/hoge.gif";
$type = "image/gif";
$name = "hoge.gif";
$sql = "INSERT INTO image (name, type, data) VALUES(%q, %q, %B)";
$ret = $posql->queryf($sql, $name, $type, file_get_contents($path));
// it will be like convert below
// "INSERT ... VALUES('hoge.gif', 'image/gif', base64_decode('omit...'))";
The example above, BLOB is encoded with base64 by using '%B', and the string given to the parser is converted so that the decipherment is automatically done.
$posql = new Posql('foo.db');
$sql = "INSERT INTO foo (text) VALUES (%q)";
$ret = $posql->queryf($sql, "I'm feeling happy!");
var_dump($ret);
// to "INSERT INTO foo (text) VALUES ('I\'m feeling happy!')";
The example above is the string including a single quote(') is escaped, and the string is enclosed by the quotation mark.
The exec() method executes SQL statement which does not contain the result-set such as INSERT or UPDATE etc.
The query() method might be most in the case where the same results. This method ever returns the scalar type (numeric value) is different. This method was implemented for compatibility with PDO.
Prepares an SQL statement to be executed by the Posql_Statement::execute() method. The SQL statement can contain zero or more named (:name) or question mark (?) parameter markers for which real values will be substituted when the statement is executed.
This method has compatibility with PDO.
Returns the instance of Posql_Statement that maintains SQL statement.
$sql = 'SELECT name, colour, calories
FROM fruit
WHERE calories < :calories AND colour = :colour';
$stmt = $posql->prepare($sql);
$stmt->execute(array(':calories' => 150, ':colour' => 'red'));
$red = $stmt->fetchAll();
$stmt->execute(array('calories' => 175, 'colour' => 'yellow'));
$yellow = $stmt->fetchAll();
Example: Prepare an SQL statement with named parameters.
$sql = 'SELECT name, colour, calories
FROM fruit
WHERE calories < ? AND colour = ?';
$stmt = $posql->prepare($sql);
$stmt->execute(array(150, 'red'));
$red = $stmt->fetchAll();
$stmt->execute(array(175, 'yellow'));
$yellow = $stmt->fetchAll();
Example: Prepare an SQL statement with question mark parameters.
quote() places quotes around the input string (if required) and escapes special characters within the input string.
Argument string as the string to be quoted.
Returns a quoted string that is theoretically safe to pass into an SQL statement.
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.
When the script is exited, vacuum() is automatically called.
Returns TRUE for success or returns FALSE for failure.
beginTransaction() method starts the transaction block. If beginTransaction() was executed, the SQL statement will be process in a single transaction block after an explicit commit() or rollBack() to be executed.
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'.
Posql (Version 2.17) does not support nested transactions in. It is an error trying to start another transaction in the transaction.
createDatabase(), dropDatabase() 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.
Returns TRUE for success or returns FALSE for failure.
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() method to abort the transaction. The transaction status is not started even if you issue a commit() particular problem, but an error occurs.
Returns TRUE for success or returns FALSE for failure.
ROLLBACK ends the transaction begun by beginTransaction(), 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() method. The error occurs though there is no problem even if rollBack() is issued if the transaction is not begun.
Returns TRUE for success or returns FALSE for failure.
The getPager method is default built-in class for Pager. Get the object instance of Posql_Pager with settings.
Give the argument total_count as number of total items. Give the argument curpage as number of current page. Give the argument perpage as number of items per page. Give the argument range as number of page links for each window.
The number of pages starts from 1.
Return the Posql_Pager object instance.
The property of the Posql_Pager class is composed as follows.
The example of setting up Pager as follows.
<?php
$posql = new Posql('pager_sample');
$sql = "CREATE TABLE IF NOT EXISTS pages (
id INTEGER PRIMARY KEY,
msg TEXT
)";
$posql->exec($sql);
$stmt = $posql->query("SELECT COUNT(*) FROM pages");
$count = $stmt->fetchColumn(0);
if (!$count) {
// Insert 1000 records
$data = array();
for ($i = 1; $i <= 1000; $i++) {
$data[] = array(
'msg' => sprintf('current page is %d.', $i)
);
}
$count = $posql->insert('pages', $data);
}
$curpage = 1;
if (isset($_GET['page']) && is_numeric($_GET['page'])) {
$curpage = $_GET['page'];
}
// number of items per page
$perpage = 5;
// number of page links for each window
$range = 10;
// Create the Pager object
$pager = $posql->getPager($count, $curpage, $perpage, $range);
// Get the records
$sql = "SELECT * FROM pages LIMIT ? OFFSET ?";
$stmt = $posql->prepare($sql);
$stmt->execute(array($pager->limit, $pager->offset));
$stmt->setFetchMode('assoc');
?>
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<title>Posql Pager Sample</title>
<style>
* {
font-family: verdana;
}
.pager li {
display: block;
float: left;
margin: 5px;
padding: 0;
border: 1px solid #999;
}
.pager a {
display: block;
margin: 0;
padding: 4px 8px;
font-size: x-small;
text-decoration : none;
color: #333;
}
.pager a:hover {
color: #333;
background: #ccccff;
text-decoration: none;
}
</style>
</head>
<body>
<h1>Posql Pager Sample</h1>
<hr>
<?php while ($row = $stmt->fetch()): ?>
<div>
<?php printf('id:%d msg:%s', $row['id'], $row['msg']) ?>
</div>
<hr>
<?php endwhile; ?>
<?php /* Begin the Pager */ ?>
<ul class="pager">
<?php if ($pager->currentPage != 1): ?>
<li><a href="index.php?page=1"><<</a></li>
<?php endif; ?>
<?php if ($pager->prev): ?>
<li><a href="index.php?page=<?php echo $pager->prev ?>"><</a></li>
<?php endif; ?>
<?php foreach ($pager->pages as $page): ?>
<li><a href="index.php?page=<?php echo $page?>"><?php echo $page?></a></li>
<?php endforeach; ?>
<?php if ($pager->next): ?>
<li><a href="index.php?page=<?php echo $pager->next ?>">></a></li>
<?php endif; ?>
<?php if ($pager->currentPage != $pager->totalPages): ?>
<li><a href="index.php?page=<?php echo $pager->totalPages ?>">>></a></li>
<?php endif; ?>
</ul>
<?php /* End of Pager */ ?>
</body>
</html>
Return the value of rowid of the last row insert from this connection to the database. Return NULL if not inserted from this connection yet.
The value returned by this method is the same as getLastInsertId() which is explained as following. This is prepared for compatibility with PDO.
The Posql_Statement class enables the binding-mechanism and operating the result-set of SQL statement.
Posql_Statement was designed and modeled from the PDO class that bundled since PHP5.1. Refer to PHP manual (PDOStatement) for details of PDO if you want more informations. Interchangeability with PDO is not limited to various operations. PDO::FETCH_* constant can be similarly handled in Posql_Statement.
Fetches a row from a result set associated with a Posql_Statement object. The fetch_mode parameter determines how Posql returns the row. The fetch_mode can be given by the character string. (Example: 'ASSOC') Capital letters and small letters of the fetch_mode is not distinguished. Even PDO::FETCH_* constant can be given to fetch_mode.
Argument args_1 and args_2 specify the argument to give to each acquisition method.
The return value is different for each mode. FALSE returns for failure.
$stmt = $posql->prepare('SELECT name, colour FROM fruit');
$stmt->execute();
print 'FETCH_ASSOC:';
$result = $stmt->fetch('assoc'); // or PDO::FETCH_ASSOC
var_dump($result);
print 'FETCH_BOTH:';
$result = $stmt->fetch('both'); // or PDO::FETCH_BOTH
var_dump($result);
print 'FETCH_COLUMN';
$result = $stmt->fetch('column'); // or PDO::FETCH_COLUMN
var_dump($result);
print 'FETCH_OBJ';
$result = $stmt->fetch('obj'); // or PDO::FETCH_OBJ
var_dump($result);
The example above fetch the row in some different mode.
Argument fetch_mode is explained by the above-mentioned. The same fetch mode as the fetch() method can be specified. Even PDO::FETCH_* constant can be given to fetch_mode.
In Posql, 'HTMLTable' can be passed as an enhanced fetch mode. (Capital letters and small letters are not distinguished.)
Refer to fetchAllHTMLTable() for details of 'HTMLTable'. The result will be returned as well as this method.
The return value is different for each mode.
The empty array ( array() ) returns for failure.
$stmt = $posql->prepare('SELECT name, colour FROM fruit');
$stmt->execute();
$result = $stmt->fetchAll();
var_dump($result);
The example above displays all rows which remain in the result set.
Argument caption is HTML <caption> element as string. Argument attr is the attributes of <table> element. Even if the order is different, these two arguments are appropriately processed.
The string in the rows is escaped by escapeHTML() method as HTML entity.
Return the string that included all rows of the result set as HTML <table> element.
$sql = "SELECT 1, NULL, TRUE, FALSE, 'hoge', 'I''m feeling happy!' AS happy";
$stmt = $posql->prepare($sql);
$stmt->execute();
$result = $stmt->fetchAllHTMLTable($sql,
array(
'border' => 1,
'class' => 'my-class',
'style' => array(
'padding' => '5px',
'color' => '#333',
'background' => '#fff'
)
)
);
print $result;
The attribute can be set like the example above. The attributes adjusts even like 'border=1' as string.
Execute the prepared statement. If the prepared statement included parameter markers, pass an array of input-only parameter values.
The same operation as PDOStatement::execute() can be done as well as other methods.
Returns TRUE for success or returns FALSE for failure.
$sql = "SELECT * FROM foo WHERE name = ? OR rowid = ? ";
$stmt = $posql->prepare($sql);
$stmt->execute(array('foo', 1));
print $stmt->fetchAll('HTMLTable');
Example: Execute a prepared statement with question mark placeholders.
Binds a PHP variable to a corresponding named or question mark placeholder in the SQL statement that was use to prepare the statement. Unlike bindValue(), the variable is bound as a reference and will only be evaluated at the time that execute() is called.
The same operation as PDOStatement::bindParam() can be done as well as other methods.
Note: Arguments type and length are not implemented.
It is disregarded even if it specifies it.
Returns TRUE for success or returns FALSE for failure.
$calories = 150;
$colour = 'red';
$stmt = $posql->prepare('SELECT name, colour, calories
FROM fruit
WHERE calories < :calories AND colour = :colour');
$stmt->bindParam(':calories', $calories);
$stmt->bindParam(':colour', $colour);
$stmt->execute();
Example: Execute a prepared statement with named (:name) placeholders.
$calories = 150;
$colour = 'red';
$stmt = $posql->prepare('SELECT name, colour, calories
FROM fruit
WHERE calories < ? AND colour = ?');
$stmt->bindParam(1, $calories);
$stmt->bindParam(2, $colour);
$stmt->execute();
Example: Execute a prepared statement with question mark (?) placeholders.
Binds a value to a corresponding named (:name) or question mark (?) placeholder in the SQL statement that was use to prepare the statement.
The same operation as PDOStatement::bindValue() can be done as well as other methods.
Note: Arguments type and length are not implemented.
It is disregarded even if it specifies it.
Returns TRUE for success or returns FALSE for failure.
$calories = 150;
$colour = 'red';
$stmt = $posql->prepare('SELECT name, colour, calories
FROM fruit
WHERE calories < :calories AND colour = :colour');
$stmt->bindValue(':calories', $calories);
$stmt->bindValue(':colour', $colour);
$stmt->execute();
Example: Execute a prepared statement with named placeholders.
$calories = 150;
$colour = 'red';
$stmt = $posql->prepare('SELECT name, colour, calories
FROM fruit
WHERE calories < ? AND colour = ?');
$stmt->bindValue(1, $calories);
$stmt->bindValue(2, $colour);
$stmt->execute();
Example: Execute a prepared statement with question mark placeholders.
bindColumn() arranges to have a particular variable bound to a given column in the result-set from a query. Each call to fetch() or fetchAll() will update all the variables that are bound to columns.
Note: Since information about the columns is not always available to Posql until the statement is executed, portable applications should call this function after execute.
The same operation as PDOStatement::bindColumn() can be done as well as other methods.
Note: Arguments type and length are not implemented.
It is disregarded even if it specifies it.
Argument column is:
Number of the column (1-indexed) or name of the column in the result set.
If using the column name,
be aware that the name should match the case of the column.
Argument var is:
Name of the PHP variable to which the column will be bound.
Returns TRUE for success or returns FALSE for failure.
// Declare the variables beforehand.
$id = null;
$name = null;
$utime = null;
// Execute the prepare-statements
$posql = new Posql('foo.db');
$sql = "SELECT id, name, utime FROM foo";
$stmt = $posql->prepare($sql);
$stmt->execute();
// Bind with the columns index
$stmt->bindColumn(1, $id);
$stmt->bindColumn(2, $name);
// Bind with the columns name
$stmt->bindColumn('utime', $utime);
// Loop by fetch
while ($stmt->fetch()) {
print '[id] ' . $id;
print '[name] ' . $name;
print '[utime] ' . $utime;
print '<hr>';
}
Example: Binding result set output to PHP variables.
When fetch() or fetchAll() is called, the argument need not be given if the default fetch mode is set by the setFetchMode method. The fetch mode set by argument fetch_mode becomes default, and the setting is succeeded to the method of each fetch.
The fetch_mode parameter determines how Posql returns the row. The fetch_mode can be given by the character string. (Example: 'ASSOC') Capital letters and small letters of the fetch_mode is not distinguished. Even PDO::FETCH_* constant can be given to fetch_mode.
Argument args_1 and args_2 specify the argument to give to each acquisition method.
Returns TRUE for success or returns FALSE for failure.
class Posql_Fetch_Class_Sample {
function Posql_Fetch_Class_Sample($arg1 = null, $arg2 = null){
print ' new Posql_Fetch_Class_Sample(';
var_dump($arg1);
var_dump($arg2);
print ')';
}
}
$posql = new Posql;
$sql = "SELECT 1 c1, 2 c2, 3 c3
UNION
SELECT 4, 5, 6
UNION
SELECT 7, 8, 9
UNION
SELECT 10, 11, 12
UNION
SELECT 13, 14, 15";
$stmt = $posql->prepare($sql);
$stmt->execute();
print 'FETCH_ASSOC:';
$stmt->setFetchMode('assoc'); // or PDO::FETCH_ASSOC
$result = $stmt->fetch();
var_dump($result);
print 'FETCH_BOTH:';
$stmt->setFetchMode('both'); // or PDO::FETCH_BOTH
$result = $stmt->fetch();
var_dump($result);
print 'FETCH_COLUMN';
$stmt->setFetchMode('column'); // or PDO::FETCH_COLUMN
$result = $stmt->fetch();
var_dump($result);
print 'FETCH_OBJ';
$stmt->setFetchMode('obj'); // or PDO::FETCH_OBJ
$result = $stmt->fetch();
var_dump($result);
print 'FETCH_CLASS';
$stmt->setFetchMode('class', // or PDO::FETCH_CLASS
'Posql_Fetch_Class_Sample',
array('arg1_sample', 'arg2_sample'));
$result = $stmt->fetch();
var_dump($result);
The example above fetch the row in some different mode.
Specify argument column_key as 0-indexed number or name of the column. Fetches the first column if argument is omitted.
Returns a single column from the next row of a result set or FALSE if there are no more rows.
The fetchColumn method is the same as the fetch specifying fetch('column', column_key);.
The same operation as PDOStatement::fetchColumn() can be done as well as other methods.
Specify the argument class_name as name of the created class. Specify the argument class_args as arguments of array to given to the constructor.
The default class_name is stdClass.
Returns FALSE if there are no more rows.
The fetchObject method is the same as the fetch specifying
fetch('class', class_name, class_args);
or
fetch('object');.
The same operation as PDOStatement::fetchObject() can be done as well as other methods.
rowCount returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding statement object.
The same operation as PDOStatement::rowCount() can be done as well as other methods.
Returns the number of columns in the result set represented by the statement object.
The same operation as PDOStatement::columnCount() can be done as well as other methods.
Posql_Statement class is compatible with PDO. Moreover, a lot of methods of Posql_Statement class are implemented compatible with PEAR::MDB2. The methods such as getColumnNames(), fetchRow(), numRows(), numCols(), hasRows() and free() can be used though it is not explained here.
Refer to the class Posql_Statement in the source code if you want more detailed informations.
The Posql_Charset class is a library which handled the character set encoding. Uses the extensions mbstring and iconv libraries if available, or uses the original methods by pure PHP language.
The Posql_Charset class explains to define like Posql->pcharset->method() because it has property pcharset that as instance of Posql_Charset object.
Detects character encoding from string. It is detects whether it is the character-code when passed by the character string where argument encodings shows a single character-code. If argument encodings is omitted, or encodings is given as 'AUTO', it will be detected automatically. Capital letters and small letters of 'auto' are not distinguished. Encoding order may be specified by array or comma separated list string.
The detected character encoding or FALSE if the encoding cannot be detected from the given string.
The list of the character encoding which becomes a return value as follows.
All arguments are handled as string. Converts the character encoding of string string to to from optionally from.
If to is omitted, to is replaced to 'UTF-8'. The internal character encoding is detected by the automatic operation when from is omitted or 'auto' is passed, and argument string is converted into the encoding to. Capital letters and small letters of 'auto' are not distinguished.
If extension mbstring is available, it will be converted by mb_convert_encoding(). If extension iconv is available, it will be converted by iconv(). Returns FALSE if failed to convert string.
This method can treat the argument as well as mb_convert_encoding().
$posql = new Posql;
$encodings = array(
'UTF-8',
'UTF-16',
'UTF-32'
);
$string = 'aäeëiïoöuü';
foreach ($encodings as $charset) {
$string = $posql->pcharset->convert($string, $charset);
$encoding = $posql->pcharset->detectEncoding($string);
$result = array(
"to_encoding" => $charset,
"detect_encoding" => $encoding,
"converted_string" => $string
);
// All right, if "to_encoding" equaled "detect_encoding".
print "<pre>";
var_dump($result);
print "</pre>";
}
Posql_ECMA class implemented the JavaScript methods on PHP with compatibility. (ECMA-262 3rd edition).
Posql version 2.17 confirmed the validity on UTF-8.
Most of the source obtained the hints from SpiderMonkey.
Posql_ECMA is a library convenient to take the cooperation between PHP and JavaScript.
The Posql_ECMA class explains to define like Posql->ecma->method() because it has property ecma that as instance of Posql_ECMA object.
Refer Core JavaScript Reference if you want more informations.
Characters in a string are indexed from left to right. The index of the first character is 0. If the index you supply is out of range, JavaScript returns an empty string.
The following examples return the character string 'c'.
$posql = new Posql;
echo $posql->ecma->charAt('abcde', 2);
Unicode code points range from 0 to 1,114,111. The first 128 Unicode code points are a direct match of the ASCII character encoding. Note that charCodeAt will always return a value that is less than 65,536. charCodeAt returns NaN if the given index is not between 0 and 1 less than the length of the string.
Returns a string created by using the specified sequence of Unicode values.
The following examples return the character string 'ABC'.
$posql = new Posql; echo $posql->ecma->fromCharCode(65, 66, 67);
Returns the index within the string of the first occurrence of the specified value, starting the search at search, or -1 if the value is not found. The first index is 0.
$posql = new Posql;
echo $posql->ecma->indexOf('abcde', 'a'); // output: 0
echo $posql->ecma->indexOf('abcde', 'c'); // output: 2
echo $posql->ecma->indexOf('abcde', 'c', 2); // output: 2
Returns the index within the string of the last occurrence of the specified value, or -1 if not found. The string is searched backward, starting at offset.
$posql = new Posql;
echo $posql->ecma->lastIndexOf('foobarbaz', 'a'); // output: 7
echo $posql->ecma->lastIndexOf('foobarbaz', 'z'); // output: 8
echo $posql->ecma->lastIndexOf('foobarbaz', 'a', 5); // output: 4
echo $posql->ecma->lastIndexOf('foobarbaz', 'x'); // output: -1
length is implemented as property on JavaScript/ECMAScript. Note: Posql implemented as a method.
Returns the length of string as Unicode characters. Returns 0 length if string is empty.
$posql = new Posql;
echo $posql->ecma->length('abcdef'); // output: 6
slice extracts the text from one string and returns a new string. Changes to the text in one string do not affect the other string. slice extracts up to but not including end. slice($string, 1, 4) extracts the second character through the fourth character (characters indexed 1, 2, and 3). As a negative index, end indicates an offset from the end of the string. slice($string, 2, -1) extracts the third character through the second to last character in the string.
$posql = new Posql;
echo $posql->ecma->slice('abcdef', 1); // output: bcdef
echo $posql->ecma->slice('abcdef', -2); // output: ef
echo $posql->ecma->slice('abcdef', 2, 4); // output: cd
echo $posql->ecma->slice('abcdef', -2, 5); // output: e
The split method returns the new array. When found, separator is removed from the string and the substrings are returned in an array. Note: When the string is empty, split returns an array containing one empty string, rather than an empty array.
$posql = new Posql;
var_export($posql->ecma->split('foo bar baz', ' '));
// output:
// array ( 0 => 'foo', 1 => 'bar', 2 => 'baz', )
var_export($posql->ecma->split('foo bar baz', 'a'));
// output:
// array ( 0 => 'foo b', 1 => 'r b', 2 => 'z', )
var_export($posql->ecma->split('foo bar baz', ''));
// output:
// array ( 0 => 'f', 1 => 'o', 2 => 'o', 3 => ' ',
// 4 => 'b', 5 => 'a', 6 => 'r', 7 => ' ',
// 8 => 'b', 9 => 'a', 10 => 'z', )
start is a character index. The index of the first character is 0, and the index of the last character is 1 less than the length of the string. substr begins extracting characters at start and collects length characters (unless it reaches the end of the string first, in which case it will return fewer).
If start is positive and is greater than or equal to the length of the string, substr returns an empty string. If start is negative, substr uses it as a character index from the end of the string. If start is negative and abs(start) is larger than the length of the string, substr uses 0 as the start index.
If length is 0 or negative, substr returns an empty string. If length is omitted, start extracts characters to the end of the string.
$posql = new Posql; $string = 'abcdefghij'; var_dump($posql->ecma->substr($string, 1, 2)); // output: 'bc' var_dump($posql->ecma->substr($string, -2, 2)); // output: 'ij' var_dump($posql->ecma->substr($string, 1)); // output: 'bcdefghij' var_dump($posql->ecma->substr($string, -20, 2)); // output: 'ab' var_dump($posql->ecma->substr($string, 20, 2)); // output: ''
substring extracts characters from begin up to but not including end. In particular:
If begin is larger than end, then the effect of substring is as if the two arguments were swapped; for example, substring($string, 1, 0) == substring($string, 0, 1).
$posql = new Posql; $string = 'abcdefghij'; var_dump($posql->ecma->substring($string, 1)); // output: 'bcdefghij' var_dump($posql->ecma->substring($string, -2)); // output: 'abcdefghij' var_dump($posql->ecma->substring($string, 2, 5)); // output: 'cde' var_dump($posql->ecma->substring($string, 5, 2)); // output: 'cde' var_dump($posql->ecma->substring($string, -2, 5)); // output: 'abcde' var_dump($posql->ecma->substring($string, 0, 0)); // output: '' var_dump($posql->ecma->substring($string, 20, 2)); // output: 'cdefghij'
The toLowerCase method returns the value of the string converted to lowercase. toLowerCase does not affect the value of the string itself.
$posql = new Posql;
var_dump($posql->ecma->toLowerCase('ABCdefG')); // output: 'abcdefg'
The toUpperCase method returns the value of the string converted to uppercase. toUpperCase does not affect the value of the string itself.
$posql = new Posql;
var_dump($posql->ecma->toUpperCase('abcDEFg')); // output: 'ABCDEFG'
Returns the expression of the string by the specified argument value.
toString only returns the expression of the string. This method implemented for compatibility with JavaScript/ECMAScript.
The escape and unescape functions let you encode and decode strings. The escape function returns the hexadecimal encoding of an argument in the ISO Latin character set. The unescape function returns the ASCII string for the specified hexadecimal encoding value.
The escape and unescape functions do not work properly for non-ASCII characters and have been deprecated. These functions are implemented for compatibility.
The escape and unescape functions let you encode and decode strings. The escape function returns the hexadecimal encoding of an argument in the ISO Latin character set. The unescape function returns the ASCII string for the specified hexadecimal encoding value.
The escape and unescape functions do not work properly for non-ASCII characters and have been deprecated. These functions are implemented for compatibility.
Assumes that the URI is a complete URI, so does not encode reserved characters that have special meaning in the URI.
Note that encodeURI by itself cannot form proper HTTP GET and POST requests, such as for XMLHttpRequest, because '&', '+', and '=' are not encoded, which are treated as special characters in GET and POST requests. encodeURIComponent, however, does encode these characters. These behaviors are most likely not consistent across browsers.
Encodes a Uniform Resource Identifier (URI) component by replacing each instance of certain characters by one, two, or three escape sequences representing the UTF-8 encoding of the character.
encodeURIComponent escapes all characters except the following: alphabetic, decimal digits, - _ . ! ~ * ' ( )
Decodes a Uniform Resource Identifier (URI) previously created by encodeURI or by a similar routine.
Replaces each escape sequence in the encoded URI with the character that it represents. Does not decode escape sequences that could not have been introduced by encodeURI.
Replaces each escape sequence in the encoded URI component with the character that it represents.
Posql_Archive class is a library which handled the compression and archive.
Posql Version 2.17 implemented AlphamericHTML.
AlphamericHTML compresses the string by LZ77 algorithm, and the encoded string composes only the alphameric 63 based characters [0-9A-Za-z_] (AlphamericString).
The Posql_Archive class explains to define like Posql->archive->method() because it has property archive that as instance of Posql_Archive object.
Encodes the string s by the format of AlphamericHTML.
AlphamericHTML compresses the string by LZ77 algorithm, and the encoded string composes only the alphameric 63 based characters [0-9A-Za-z_] (AlphamericString).
Returns the encoded string.
Decodes the argument a by the format of AlphamericHTML.
Returns the decoded string.
In the example below, AlphamericHTML is understood for the character string to be compressed and to be converted into AlphamericString.
$posql = new Posql; $string = 'Hello,Hello,abcabcabcabcabcabc'; echo '<div>original string:</div>'; var_dump($string); // 'Hello,Hello,abcabcabcabcabcabc' $encode = $posql->archive->encodeAlphamericString($string); echo '<div>encodeAlphamericString:</div>'; var_dump($encode); // 'Y8Z5CCFXC_v4Z123_yD' $decode = $posql->archive->decodeAlphamericString($encode); echo '<div>decodeAlphamericString:</div>'; var_dump($decode); // 'Hello,Hello,abcabcabcabcabcabc' echo '<div>compare:</div>'; var_dump($string === $decode); // true
Posql (version 2.17) is never needs the file of external configurations (e.g. xml, ini, etc).
If you want to change the setting of something, use the methods of the explanation as follows.
Returns the all lower case string if argument tolower gave to TRUE. Otherwise (Default) return all upper case string.
Posql (Version 2.17) supporting the expression engines as follows.
Default expression mode is SQL-engines-mode.
The execution engine can change in the outside of SQL statement any timing.
Capital letters and small letters are not distinguished.
Returns the execution engine of immediately before as string. Returns FALSE for settings failure.
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. You can get the disabled functions by using this method.
Return the disabled functions of all as array.
Set the function prohibited by SQL statement in the PHP mode of Posql. One or more functions are given by the array. Or, function name func is given by the string.
If called this method with no argument, it will be clear all the disabled functions.
Returns the numbers of affected functions.
$posql = new Posql('foo.db');
$posql->setEngine('PHP');
$funcs = $posql->getDisableFunctions();
print '<pre>';
print "disable functions:\n";
print_r($funcs);
$affected = $posql->setDisableFunctions('hoge');
print "disable functions: After:\n";
$funcs = $posql->getDisableFunctions();
print_r($funcs);
$affected = $posql->setDisableFunctions();
print "affected disable functions:\n";
var_dump($affected);
$disable_funcs = array('eval', 'exit', 'fopen');
$affected = $posql->setDisableFunctions($disable_funcs);
print "After the disable functions:\n";
$funcs = $posql->getDisableFunctions();
print_r($funcs);
print "\n---------------\n";
print "Try calls fopen() function on expression:\n";
$stmt = $posql->query("SELECT fopen('somefile', 'r')");
print "results:\n";
var_dump($stmt->fetchAll());
print "errors: \n";
print $posql->lastError();
The example above is some settings test, try to execution the disabled function.
The default character set is UTF-8. This character-code is used by a part of function in SQL statement for the hint. Should be same as the character-code of the running script.
Should be same as the character-code of the running script.
This setting is a value to be able to describe SQL syntax even in the PHP mode without the sense of incompatibility. And, the validation for PHP is able to calling the function from the variable identifier.
This default setting value is TRUE (to convert).
SELECT ($func = 'unlink') AND $func('somefile');
When SQL statement is executed from the outside like the example above, the function might be executed in the PHP-engines-mode. When this setting is TRUE:
SELECT ($func == 'unlink') AND $func('somefile');
Because the example above changes into the comparison operator, the call of the function can be controlled in some degree.
SELECT ... WHERE rowid = 1
When comparing it by converting the equal sign, the example above might be useful.
SELECT ... WHERE rowid == 1
The defaulting value is 'php'.
Returns the extension of immediately before as string. Returns FALSE for settings failure.
Posql uses PHP functions that serialize() and unserialize() as encoding or decoding functions when the data is stored. Behavior can be changed by the setting of this property. The default encoding method is serialize() function.
The callback function given as an argument should be able to convert types other than the resource of PHP into string.
Returns the callback function of immediately before as string. Returns FALSE for settings failure.
Posql uses PHP functions that serialize() and unserialize() as encoding or decoding functions when the data is stored. Behavior can be changed by the setting of this property. The default decoding method is unserialize() function.
The callback function given as an argument should be able to convert types other than the resource of PHP into string.
Returns the callback function of immediately before as string. Returns FALSE for settings failure.
// This example might function as a compression database
function posql_extra_encode($value){
return gzcompress(serialize($value));
}
function posql_extra_decode($value){
return unserialize(gzuncompress($value));
}
// If the argument is given, it will be an error.
$posql = new Posql;
$posql->setEncoder('posql_extra_encode');
$posql->setDecoder('posql_extra_decode');
if ($posql->isError()) {
die($posql->lastError());
}
// Begin setting here.
$posql->open('foo.db');
$posql->exec("CREATE TABLE foo (col)");
if ($posql->isError()) {
die($posql->lastError());
}
$posql->exec("INSERT INTO foo (col) VALUES('abcabcabcabc')");
if ($posql->isError()) {
die($posql->lastError());
}
$stmt = $posql->query("SELECT * FROM foo");
print $stmt->fetchAllHTMLTable();
The example above: The data of each record will be compressed, and it can use as the compression database. The extension zlib is needed.
// This example might function as a compression database
// PHP5+ will be warned "Strict non-static"
class Posql_Extra_Serializer {
function encode($value){
$value = serialize($value);
if (extension_loaded('lzf')) {
$value = lzf_compress($value);
} else if (extension_loaded('zlib')) {
$value = gzcompress($value, 9);
} else if (extension_loaded('bz2')) {
$value = bzcompress($value);
}
return $value;
}
function decode($value){
if (extension_loaded('lzf')) {
$value = lzf_decompress($value);
} else if (extension_loaded('zlib')) {
$value = gzuncompress($value);
} else if (extension_loaded('bz2')) {
$value = bzdecompress($value);
}
$value = unserialize($value);
return $value;
}
}
// If the argument is given, it will be an error.
$posql = new Posql;
// assign the serializer function
$posql->setEncoder(array('Posql_Extra_Serializer', 'encode'));
$posql->setDecoder(array('Posql_Extra_Serializer', 'decode'));
if ($posql->isError()) {
die($posql->lastError());
}
// Begin setting here.
$posql->open('foo.db');
$posql->exec("CREATE TABLE foo (col)");
if ($posql->isError()) {
die($posql->lastError());
}
$posql->exec("INSERT INTO foo (col) VALUES('abcabcabcabc')");
if ($posql->isError()) {
die($posql->lastError());
}
$stmt = $posql->query("SELECT * FROM foo");
print $stmt->fetchAllHTMLTable();
The example above: The way of using the class methods.
This value influences a size of the data which can be stored in a single line and the maximum number etc. of rows. The defaulting value is 0x7FFFFF00 (2,147,483,392).
Give the argument max as numerical value that should be over 0. Valid types are int, float and the numerical string.
Returns the maximum value of immediately before as string. Returns FALSE for settings failure.
Posql generates a temporarily empty directory only while locking exclusion.
The directory for the lock is generated to the same place as the data base file,
and deleted instantaneously.
That is needing of 'Writing attribute' in the directory (There is a database file) of the object.
The naming convention of the directory name for the lock:
'.' + database_name + '.lock'
For example, if the database name is:
'my_database'
And, the directory name to lock as follows.
'.my_database.lock'
The collision of the name need not usually be considered.
The default timeout is 10 minutes. The directory for the lock is annulled when the timeout is passed, and processing is continued.
Argument is treated by the minutes. The default timeout is 10. The timeout should be larger than 1 in the numerical value.
Returns TRUE for success or returns FALSE for failure.
Posql bundled the query cache, this mechanism is storage as cache to the result-set of the SELECT statement. And it does not search tables when same query is requested then return the result-set from cache area. When the table is changed, cache is renewed to a new result set according to it.
A time-consuming statement might be speed up by using query cache since the second times. Instead, the capacity of the disk increases to need the cache storage area. Because query cache is preserved in a private area in the database, it is not necessary to operate it from the outside basically.
When TRUE is not used when query cache is used, FALSE is returned.
This default value is FALSE.
All query caches can be cleared by using the method of clearQueryCache().
Give argument as TRUE if the query cache to use. Give argument as FALSE if the query cache is not used.
Returns the number of upper bounds of query cache.
The upper bound value of default is 1024.
When new query cache is storages exceeding this value, it is deleted from the old item.
Give the argument as the numeric value of upper bounds of query cache.
refer to getUseQueryCache() for details of query cache.
clearQueryCache method clears all query cache physically.
Return number of affected rows.
The getMicrotime method returns the same value as microtime(true); in PHP5. This method composed for compatibility with PHP versions.
Returns current Unix timestamp with microseconds as float.
If the argument p is omitted, the target file will be the current database.
Return TRUE if the file is available format of Posql database. Otherwise return FALSE.
If you want to get the file path use getPath() method.
Returns the current database name (Not the filename).
Return the value of rowid of the last row insert from this connection to the database. Return NULL if not inserted from this connection yet.
Get the version of Posql that is running as database. Return the majors version (e.g. '2') when argument major is given as TRUE. Otherwise return full version (e.g. '2.17').
Return TRUE if argument tablename that as the table is exists, otherwise return FALSE.
Checks whether the class exists without calling __autoload().
Return TRUE if argument class that as the class is exists, otherwise return FALSE.
Escapes a string to validate expression in the SQL statement. Optionally, and escape characters to validate string as a LIKE pattern.
Escapes LIKE wildcards characters when specified TRUE on argument escape_wilcards.
Enclose string by single quotes when argument enclose is passed with TRUE.
Note that the possibility of appropriateness is high of the method of quote() usually.
Example of escape including LIKE wildcards character:
$posql = new Posql('math_db');
$posql->exec("DROP TABLE IF EXISTS math");
$posql->exec("CREATE TABLE IF NOT EXISTS math (
id integer,
expr varchar(255),
res integer,
primary key(id)
)");
for ($i = 1; $i <= 10; $i++) {
$expr = sprintf('%d %% %d / %d + %d', $i, $i, $i, $i);
$stmt = $posql->queryf('SELECT %s', $expr);
$res = $stmt->fetchColumn(0);
$stmt = $posql->prepare("INSERT INTO math (expr, res) VALUES(?, ?)");
$stmt->execute(array($expr, $res));
}
$search = $posql->escape('5 % 5', true);
$sql = "SELECT id, expr, res FROM math WHERE expr LIKE '" . $search . "%'";
$stmt = $posql->query($sql);
echo $stmt->fetchAllHTMLTable();
Returns the escaped string that has LIKE wildcards characters.
The LIKE wildcards are characters of '%' and '_'.
This method is a function only of the escape in the LIKE wildcards. Note: In almost case the quote() or escape() methods are possibility of appropriateness might be high.
Add the ENT_QUOTES option to the htmlspecialchars() function when html given by the argument is HTML or XML string, and execute it. As a result, convert HTML and XML special characters into the entities and return it. When html is array, it is recurrently processed. When argument charset is given, it is processed as a character-code. Using internal character-encoding property that is used as getCharset() and setCharset() methods will be substitutes if charset is omitted.
Interpret for argument templates as the template which uses one or more arguments for sorting, and sort mainly the key to array array.
$array = array(
'id' => 1,
'name' => 'foo',
'text' => 'hello!'
);
print "<br/><b>original:</b><br/><pre>\n";
print_r($array);
$template = array('name', 'id', 'text');
$posql = new Posql;
$posql->sortByTemplate($array, $template);
print "\n<b>after:</b><br/>\n";
print_r($array);
print "</pre>";
// output:
// array(
// [name] => foo
// [id] => 1
// [text] => hello!
// );
The example above is result will be like the comment at the end.
Return full path by given argument path as filename.
The return value becomes FALSE when it is examined whether the file actually exists in the file passing which becomes a return value when argument check_exists is passed with TRUE, and does not exist.
Appends given data as argument append_data into the file as argument filename with specified line number as argument line_number.
Concatenate by argument glue when argument append_data is given as array.
Returns the number of bytes written in the file.
This method is able to write for a large file which exceeds memory_limit.
1,100,one 2,200,two 3,300,three
Example of CSV file sample.csv on:
$posql = new Posql;
$filename = 'sample.csv';
$append_data = ',Append';
$line_number = 2;
$written_bytes = $posql->appendFileLine(
$filename,
$append_data,
$line_number
);
echo '<pre>', file_get_contents($filename), '</pre>';
The result is as follows.
1,100,one 2,200,two,Append 3,300,three
Inserts given data as argument insert_data into the file as argument filename with specified line number as argument line_number.
Concatenate by argument glue when argument insert_data is given as array.
Returns the number of bytes written in the file.
This method is able to write for a large file which exceeds memory_limit.
1,100,one 2,200,two 3,300,three
Example of CSV file sample.csv on:
$posql = new Posql;
$filename = 'sample.csv';
$insert_data = '2,150,Insert';
$line_number = 2;
$written_bytes = $posql->insertFileLine(
$filename,
$insert_data,
$line_number
);
echo '<pre>', file_get_contents($filename), '</pre>';
The result is as follows.
1,100,one 2,150,Insert 2,200,two 3,300,three
Replaces given data as argument replacement into the file as argument filename with specified line number as argument line_number.
Concatenate by argument glue when argument replacement is given as array.
Returns the number of bytes written in the file.
This method is able to write for a large file which exceeds memory_limit.
1,100,one 2,200,two 3,300,three
Example of CSV file sample.csv on:
$posql = new Posql;
$filename = 'sample.csv';
$replacement = '2,222,Replace';
$line_number = 2;
$written_bytes = $posql->replaceFileLine(
$filename,
$replacement,
$line_number
);
echo '<pre>', file_get_contents($filename), '</pre>';
The result is as follows.
1,100,one 2,222,Replace 3,300,three
The main documents and the manuals are included in the following links.