Contents

Column Types

When creating a table, there are multiple column types that you can choose from to customize that column. They can be any one of the following or more.

Name Example Description
auto_increment array('auto_increment' => [0|1]) This columns increments itself automatically when inserting. This column must be of type 'int'.
max array('max' => $maxLength) This column type limits the length of the field to $maxLength. If the column is of type integer, then the value will be checked against rather than the length
permanent array('permanent' => [0|1]) This column type, when set to 1, does not allow it to be editable until set to 1 again
type array('type' => [string|text|int|bool|date|enum]) see the data types section
enum_val array('enum_val' => array([$values]...)) This column type is only required when the column's type is set to enum. This is the list of possible values in the column. The default value is always the trailing value. If the value doesn't match any one of the enum_val's, then it will be replaced with the default value.
default array('default' => $defaultValue) This type allows txtSQL to know what the default value for a column is. txtSQL will use this value only when there is no value for a column given to insert
primary array('primary' => [0|1]) Tells txtSQL that this column is the primary column, when set to 1. There can only be one per table, and it must be of type 'int' and has to be 'auto_increment'


Data Types

When a column is created or altered, a column type can be given. One of the column types is 'type'. 'type' can be any one of the following.

Name Description
'string' Contains one line of text, which can contain anything
'text' Multiple lines of text, which can contain anything
'int' An integer ranging within the limit provided by the user
'bool' A boolean TRUE or FALSE
'enum' A list of possible values a field can have. An 'enum' column must be provided with 'enum_values', which is an array in the format of array([$possibleValue]...). The default value is always the trailing value. If the value doesn't match any one of the enum_val's, then it will be replaced with the default value.
'date' A 10 digit integer representing the UNIX timestamp of when the record was created
Note- The default data type is 'string'

Functions

These functions can be used in constructing where clauses. They are only available where txtSQL >= 2.2.2 RC2. Valid functions are listed below

Function Example Description
strUpper() array('strUpper($a) = $b') Converts $a to uppercase
strLower() array('strLower($a) = $b') Converts $a to lowercase
chop() array('chop($a) = $b') Strip whitespace from the end of a string
rtrim() array('rtrim($a) = $b') Strip whitespace from the end of a string
ltrim() array('ltrim($a) = $b') Strip whitespace from the beginning of a string
trim() array('trim($a) = $b') Strip whitespace from the beginning and end of a string
md5() array('md5($a) = $b') Calculates the md5 hash of a string
stripSlash() array('stripSlash($a) = $b') Un-quote string quoted with addslashes
strLength() array('stripSlash($a) = $b') Calculates the length of $a
strReverse() array('strReverse($a) = $b') Reverses $a
ucFirst() array('ucFirst($a) = $b') Makes $a's first character uppercase
ucwords() array('ucWords($a) = $b') Uppercase the first character of each word in a $a
bin2hex() array('bin2hex($a) = $b') Convert binary data into hexadecimal representation
entDecode() array('entDecode($a) = $b') Convert all HTML entities to their applicable characters
entEncode() array('entEncode($a) = $b') Convert all applicable characters to HTML entities
soundex() array('soundex($a) = $b') Calculate the soundex key of a string
ceil() array('ceil($a) = $b') Round fractions up
floor() array('floor($a) = $b') Round fractions down
round() array('round($a) = $b') Rounds a float
isNumeric()* array('isNumeric($a)') Finds whether a variable is a number or a numeric string
isString()* array('isString($a)') Finds whether a variable is a string
isFile()* array('isFile($a)') Tells whether the filename is a regular file
isDir()* array('isDir($a)') Tells whether the filename is a directory
*- These functions can be negated by placing a '!' in front of the function name. For example, array('!isdir($a)'). Also these functions can __NOT__ have an operator in it, they are 'unary functions'.

LIKE Clauses

LIKE clauses belong in an array and are in the following format.

     array('$column [!~|=~] $pattern')

where $column is the column name, and $pattern is a simplified regex pattern. The regex pattern can contain the following items

  • '$' - end of string
  • '^' - begginning of string
  • '[$a|$b|$c...]' - matches either $a, or $b, or $c and so on
  • '[^$a|$b|$c...]' - matches NOT either $a, or $b, or $c and so on
  • '%' - Wildcard, meaning it matches anything

    Limit Clauses

    Limit clauses can be used to specify the begginning offset and ending offset for a search in select(), update(), and delete(). But make note that in delete(), only the begginning offset is taken into account. A limit clause is an array in the following format

         array($startOffset, $endOffset)



    Logical Operators

    These operators can be used in joining two seperate where clauses, $a and $b

    Example Name Result
    array('$a', 'and', '$b') AND TRUE if both $a and $b are TRUE
    array('$a', 'or', '$b') OR TRUE if either $a or $b is TRUE
    array('$a', 'xor', '$b') XOR TRUE if either $a or $b is TRUE, but not both.


    OrderBy Clauses

    'Order By' clauses can be used in select(). It will sort the results that are found by select() by the specified $column, either ASCENDING or DESCENDING. An 'Order By' clause is an array in the following format

         array($column, [ASC|DESC])

    where $column is the column to sort the results by. If the $column is not in the result set, then txtSQL will issue an error.

    Primary Keys

    txtSQL has been designed to allow the use of primary keys. There are two ways to add them, and only one way to drop them.

    To add primary keys when creating a table, specify the column type 'primary' and set it equal to 1. The column must be 'int' (integer) and 'auto_increment'.
    The other way to add a primary key is to use the altertable() function.

    If you want to drop a primary key, you must also use the altertable() function.



    Relational Operators

    These operators can be used in constructing a where clause

    Example Name Result
    array('$a = $b') equal to TRUE if $a is equal to $b
    array('$a != $b') not equal to TRUE if $a is not equal to $b
    array('$a <> $b') not equal to TRUE if $a is not equal to $b
    array('$a < $b') less than TRUE if $a is less than $b
    array('$a <= '$b') less than or equal to TRUE if $a is less than or equal to $b
    array('$a > $b') greater than TRUE if $a is greater than $b
    array('$a >= $b') greater than or equal to TRUE if $a is greater than or equal to $b
    array('$a =~ $b') like TRUE if $a matches the pattern $b. Also see: LIKE clauses
    array('$a !~ $b') not like TRUE if $a does _NOT_ match the pattern $b.
    array('$a ? $b') instring (txtSQL >= 2.2.2 RC2) TRUE if $b is in $a
    array('$a !? $b') not instring (txtSQL >= 2.2.2 RC2) TRUE if $b is NOT in $a


    Where Clauses

    A where clause tells txtSQL what rows that you are looking for. Where clauses can be used in select(), delete() and update. A where clause is simply an array in the following format.

         array(['$column $op $value' [, $logicalOp]]...);

    where $column is the name of the column, $op is the operator to use, and $value is the value to check against. For more information on operators, see the relational operators section. For more information on logical operators, see the logical operators section.



    altertable()

    (void altertable ( array ('table' => $table, 'action' => $action, 'name' => $column, 'values' => $values [, 'after' => $afterColumn [, 'db' => $db]]) ))

    txtSQL >= 2.2.2 RC2
    This function will alter a txtSQL-$table's column defintions. It will only work with the specified column, which is $column. The $action can be either

  • insert- Inserts a new column, $column, and if specified, after the column $afterColumn

  • modify- Modifies an existing $column

  • drop- Drops an existing $column

  • rename col- Renames a $column. Expects array('name' => $newcolname) in the $values

  • rename table- Renames a $table. Expects array('name' => $newTableName) in the $values

  • addkey- Sets $column as the primary key ( must be integer and auto_increment ). Expects array('name' => $colName) in the $values

  • dropkey- Does opposite of 'addkey'

  • The $values element is an array containing information about the column, it must be in the following format

         array( [$colType => $value]... ) unless otherwise noted above


    Important- To achieve the same results as this function before txtSQL 2.2.2 RC2, use the execute() function
    Note- For more information on data types, see the data types section, or for more information the column types, see the column types section
    Note- If $tablename does not already exists, txtSQL will issue an error
    Note- If no $db is specified and no database is already selected, txtSQL will issue an error

    connect()

    (bool connect ( $user, $pass ))

    txtSQL >= 1.0.0
    This function connects you to the txtSQL service as $user. If the user does not exist, or the password is incorrect, then this function will return false. It also issues an error warning, which can be surpressed using strict(0). Once connected, you can disconnect using disconnect()

    createdb()

    (void createdb ( array( 'db' => $db ) ))

    txtSQL >= 2.0.0 Beta
    This function will create a new database inside of the current working directory, as long as it does not already exists.

    Note- This function will return false if the database already exists
    Caveat- In windows, filenames are not case-sensative, so 'DBNAME' is the same as 'dbname', which is why it will issue an error if you try to create a database 'test', when 'TEST' exists.

    createtable()

    (void createtable ( array ('table' => $tablename, 'columns' => $columns [, 'db' => $db]) ))

    txtSQL >= 2.2.2 RC2
    This function will create a txtSQL table with the name $tablename, as long as it does not already exist. The columns that will be in this table will be defined in $columns part of the array. $columns must be an array in the following format

         array([$columnName => array([$coltype => $value]...)]... )

    For more information on on column types, see the column types section

    Important- To achieve the same results as this function before txtSQL 2.2.2 RC2, use the execute() function
    Note- If $tablename already exists, txtSQL will issue an error
    Note- If no $db is specified and no database is already selected, txtSQL will issue an error

    db_exists()

    (bool db_exists ( $database ))

    txtSQL >= 2.2 Final
    This function will return TRUE if the $database exists, and FALSE if it does not

    delete()

    (int delete ( array( 'table' => $table, $where => $where, [, 'limit' => $limit [, 'db' => $db]] ) ))

    txtSQL >= 2.2.2 RC2
    This function will delete any rows that fit the $where clause, and returns the number of rows deleted from the table. This function will only delete rows as long as they are within the $limit. If no $limit is defined, then all rows that match will be deleted.

    Important- To achieve the same results as this function before txtSQL 2.2.2 RC2, use the execute() function
    Note- For more information on defining a where clause, see the how to create a where clause section, or for defining a limit clause, see how to create a limit clause section
    Note- If no $db is specified and no database is already selected, txtSQL will issue an error

    describe()

    (mixed describe ( array ('table' => $table [, 'db' => $db]) ))

    txtSQL >= 2.2.2 RC2
    This function will return information about a txtSQL $table. It will return information such as column type, permanent, maximum length etc., and will also return the enum values if the column is set to type 'enum'.

    Important- To achieve the same results as this function before txtSQL 2.2.2 RC2, use the execute() function
    Note- For more information on data types, see the data types section, or for more information the column types, see the column types section
    Note- If $tablename does not already exists, txtSQL will issue an error
    Note- If no $db is specified and no database is already selected, txtSQL will issue an error

    disconnect()

    (bool disconnect ( ))

    txtSQL >= 1.0.0
    This function allows for a connected user to disconnect from the txtSQL service. Although this is not mandatory, it exists for development purposes. If a user is not connected, it will return false, and issue a warning, which may be surpessed using strict(0).

    Note- This function will return false if not already connected

    dropdb()

    (void dropdb ( array( 'db' => $db ) ))

    txtSQL >= 2.0.0 Beta
    This function will PERMANENTLY drop a database ( so that means no undo-ing ), as long as it exists

    Note- This function will return false if the database doesn't already exists, or if the database specified is 'txtsql'

    droptable()

    (void droptable ( array ('table' => $table [, 'db' => $db]) ))

    txtSQL >= 2.2.2 RC2
    This function will drop a txtSQL $table from a $db. If no database is specified, then txtSQL will delete the table from the selected database

    Important- To achieve the same results as this function before txtSQL 2.2.2 RC2, use the execute() function
    Note- If $tablename does not already exists, txtSQL will issue an error
    Note- If no $db is specified and no database is already selected, txtSQL will issue an error

    empty_cache()

    (void empty_cache ())

    txtSQL >= 2.2.2 RC2
    When txtSQL first reads a table, it reads it and saves it to memory, because large tables consume time to read and parse. If you want to empty the cache (which only exists during run-time), use this function to erase it.

    error_dump()

    (void error_dump())

    txtSQL >= 1.0.0
    This function will print any and all error messagees that txtSQL issued during runtime.

    execute()

    (mixed execute ( $action [, $args] ))

    txtSQL >= 2.0.0 Beta
    This function will execute a valid txtSQL command and return that function's results. The list of valid $action's are 'select', 'insert', 'update', 'delete', 'show databases', 'create database', 'drop database', 'rename database', 'show tables', 'create table', 'drop table', 'alter table', and 'describe'.

    The $args is the same arguments as if you were using the function seperately. Click a function for more information on what is to be defined in the $args

    Note- For more information on defining a where clause, see the how to create a where clause section, or for defining a limit clause, see how to create a limit clause section
    Note- If no $db is specified and no database is already selected, txtSQL will issue an error

    getUsers()

    (mixed getUsers ())

    txtSQL >= 2.2.2 RC2
    This function returns an array filled with a list of currently registered txtSQL users.

    get_last_error()

    (string get_last_error())

    txtSQL >= 1.0.0
    This function will retrieve the last error message that txtSQL issued

    Note- To print the last warning rather than retrieving it, use last_error()

    grant_permissions()

    (void grant_permissions ( $action, $username, $password [, $newpass] ))

    txtSQL >= 2.0.0 Beta
    This function allows for the manipulation of $user; it can either add a user, drop, or edit a user.
    $action can be one of either add, edit, or drop.

  • If it is 'add', then $username will be the new user's name, and $password will be his/her password
  • If it is 'drop', then $username must be specified, and so does $password (password for this user).
  • If it is 'edit', then $username must be specified, along with $password, and $newpass. The new password would be $newpass.

    Note- If you are dropping, or editing a user, and the incorrect password is specified, OR when adding a user and the user already exists, then txtSQL will issue a warning and return false.
    Note- You can never drop the user 'root'
    Note- In txtSQL <= 2.2.0 Beta, setting an empty password would incorrectly save, do not set empty passwords before this.

    insert()

    (void insert( array( 'table' => $table, 'values' => $values [, 'db' => $db] ) ))

    txtSQL >= 2.2.2 RC2
    This function will insert a row of data, containing the $values given, into an existing txtSQL database. The $values must be an array in the following format

         array([$column => $value]...)

    where $column is the name of the column that txtSQL will insert into, and $value is the value for that corressponding column. If the column does not exist, txtSQL will issue an error.

    If a column is of type auto_increment, txtSQL will automatically increment that column. If a column is of type enum and the value being inserted does not exist as one of the enum values, the last value from the enum list is inserted.

    Important- To achieve the same results as this function before txtSQL 2.2.2 RC2, use the execute() function
    Note- If the value does not match the data type (i.e. a string being inserted into an integer column), txtSQL will automatically format the value
    Note- If a column is set to permanent, you will not be able to change this value
    Note- If no $db is specified and no database is already selected, txtSQL will issue an error

    isLocked()

    (bool isLocked ( $database ))

    txtSQL >= 2.2.0 Beta
    This function will return TRUE if $database has a file lock on it, and false otherwise. A file lock is basically a file created that tells txtSQL that it is locked.

    last_error()

    (void last_error())

    txtSQL >= 1.0.0
    This function will print the last error message that txtSQL issued

    Note- To retrieve the last error rather than print it, use get_last_error()

    last_insert_id()

    (int last_insert_id ( $table[, $db, [, $column]] ))

    txtSQL >= 2.2 Final
    This function will retrieve the last ID generated by a primary key in a $table. If $column is specified, then txtSQL will return the last ID generated by that column instead of the primary key; if this $column is not auto_increment, then txtSQL will issue an error.

    lockdb()

    (void lockDB ( $database ))

    txtSQL >= 2.2.0 Beta
    This function will return TRUE if $database was successfully locked and FALSE otherwise. A file lock is basically a file created that tells txtSQL that it is locked.

    query_count()

    (int query_count ( ))

    txtSQL >= 2.2 Final
    This function will return the number of queries that txtSQL evaluated thus far in script execution. This includes any unsuccessfull queries as well.

    renamedb()

    (bool renamedb ( array( $olddb, $newdb ) ))

    txtSQL >= 2.0.0 Beta
    This function will rename a txtSQL database, $dbname, to the given name, $newdb.

    Note- This function will return false if the database doesn't already exists, or if the new database name already exists

    select()

    (mixed select ( array( 'table' => $table [, 'select' => $selectCols[, 'db' => $db [, 'where' => $where [, 'limit' => $limit [, 'orderby' => $orderb[, $distinct = $column]]]]] ) ))

    txtSQL >= 2.2.2 RC2
    This function will retrieve data that matches the $where clause; if no $where clause is given, all rows match, and thus will be returned. If $selectCols is specified, then txtSQL will only select those columns. $selectCols should be an array with the following structure
         array ( col1, col2 ... )

    The search will look inside the selected database if no $db is defined, and will return rows according to the limit clause. If $orderby is specified, then the results will be sorted according to the orderby clause. If $distinct is set to TRUE, then the any repeated values for that $column will be deleted.

    Important- To achieve the same results as this function before txtSQL 2.2.2 RC2, use the execute() function
    Note- The DISTINCT feature was added in txtSQL 2.2 Final release
    Note- For more information on defining a where clause, see the 'where clause' section, or for defining a limit clause, see how to create a limit clause section
    Note- If no database is selected, and no $db is given, txtSQL will issue an error

    selectdb()

    (void selectdb ( $database ))

    txtSQL >= 2.2.0 Beta
    This function will return TRUE if $database was successfully selected and FALSE otherwise. The useful part of this function is the fact that if a database is 'selected' using selectdb(), then in queries such as select(), there is no need to specify a database.

    Note- This function will return FALSE if the database doesn't exist

    showdbs()

    (mixed showdbs ())

    txtSQL >= 2.0.0 Beta
    This function, when called, will return an array filled with a list of current txtSQL databases which are located inside of the current working directory.

    Note- This function will not return the database 'txtsql', as it is an internal working db

    showtables()

    (mixed showtables ( array (['db' => $db]) ))

    txtSQL >= 2.2.2 RC2
    This function returns a list of tables inside a txtSQL database. If no database is given, txtSQL will search the selected database.

    Important- To achieve the same results as this function before txtSQL 2.2.2 RC2, use the execute() function
    Note- If no $db is specified and no database is already selected, txtSQL will issue an error

    strict()

    (void strict ( $mode ))

    txtSQL >= 1.0.0
    When txtSQL encounters any type of error, it prints it to the webbrowser. If you want to surpress these error/warning messages, set $mode to FALSE or to turn it on, set $mode to TRUE.

    table_count()

    (int table_count ( $table [, $db]) ))

    txtSQL >= 2.0.0 Beta
    This function will return the number of records inside of a txtSQL table. It will look inside the $db if specified, and if not, txtSQL will look in the selected database.

    Note- If $tablename does not already exists, txtSQL will issue an error
    Note- If no $db is specified and no database is already selected, txtSQL will issue an error

    table_exists()

    (bool table_exists ( $table, $db ))

    txtSQL >= 2.2 Final
    This function will return TRUE if the $table exists inside of $database, and FALSE otherwise

    unlockdb()

    (void unlockdb ( $database ))

    txtSQL >= 2.2.0 Beta
    This function will return TRUE if $database was successfully unlocked and FALSE otherwise. A file lock is basically a file created that tells txtSQL that it is locked.

    update()

    (int update ( array( 'table' => $table, $where => $where, 'values' => $values [, 'limit' => $limit [, 'db' => $db]] ) ))

    txtSQL >= 2.2.2 RC2
    This function will update a row that matches the $where clause with the new information given in the $values, and returns the number of rows updated. The $values must be an array in the following format

         array([$column => $value]...)

    where $column is the name of the column that txtSQL will insert into, and $value is the value for that corressponding column. If the column does not exist, txtSQL will issue an error.

    If a $limit is defined, then txtSQL will stop updating rows and return the number of rows updated at the $limit. NOTE- This limit only needs one value (i.e. array(20) stops at 21 rows) unlike a regular limit clause.

    If a column is of type enum and the value being updated does not exist as one of the enum values, the last value from the enum list is inserted.

    Important- To achieve the same results as this function before txtSQL 2.2.2 RC2, use the execute() function
    Note- For more information on defining a where clause, see the how to create a where clause section
    Note- If the value does not match the data type (i.e. a string being updated in an integer column), txtSQL will automatically format the value
    Note- If a column is set to permanent, you will not be able to change this value
    Note- If no $db is specified and no database is already selected, txtSQL will issue an error

    version()

    (int version ())

    txtSQL >= 2.0.0 Beta
    This function returns an integer which represents the running txtSQL version