[Easymod-cvs] sql_parser/docs syntax_reference_bbcode.txt,NONE,1.1

[Markus P. <mar...@us...>]

Update of /cvsroot/easymod/sql_parser/docs
In directory sc8-pr-cvs1.sourceforge.net:/tmp/cvs-serv25873/docs

Added Files:
	syntax_reference_bbcode.txt 
Log Message:
Added Syntax Reference in BBCode format (new docs directory). Also fixed a typo in the language file.

--- NEW FILE: syntax_reference_bbcode.txt ---
[code]/**
 *
 * @package SQL Parser
 * @version $Id: syntax_reference_bbcode.txt,v 1.1 2005/09/27 19:13:23 markus_petrux Exp $
 * @copyright (c) 2005 phpBB Group
 * @license http://opensource.org/licenses/gpl-license.php GNU General Public License
 *
 */[/code]

[list][*][size=14][b]Introduction[/b][/size]

This document describes the syntax that the SQL Parser for EasyMOD accepts. While it is largely MySQL based, not all options available in the [url=http://dev.mysql.com/doc/mysql/en/index.html]MySQL syntax[/url] are supported. phpBB supports several SQL servers with its Database Abstraction Layer (DBAL) implementation, hence a minimal set of options available to perform DB operations needs to be defined. This is the main goal of the SQL Parser, that is provide support for as much options as possible while making sure all those options are portable.

[b]NOTE:[/b] These standards are under development and most importantly open to discussion. However, as being said, whatever is implemented should be portable to, at least, all SQL servers supported by phpBB.
[/list]
[list][*][size=14][b]Syntax Conventions[/b][/size]

This section describes the general rules applied to SQL statements and their components:

[list][*]No explicit rule is applied to case. However, keywords and identifiers are case insensitive for almost all SQL servers. While the parser accepts any lettercase, we recommend use of keywords written in uppercase and identifiers in lowercase, for readability, as well as to prevent possible portability related issues. All SQL statements generated by the SQL Parser will use keywords in uppercase and identifiers written as found in the input. Read the [i]Identifiers[/i] section for other rules applied to identifiers, though.

[*]Syntax Diagrams and Tables used in this document adhere the following conventions:
[code]CONVENTION          USED FOR
----------------    ----------------------------------------
UPPERCASE           SQL keywords.
lowercase           User supplied elements such as SQL identifiers or constants.
<label>             Name for a block of syntax. This convention is used to group
                    portions of lengthy blocks of syntax that can be used in more
                    than one place.
| (vertical bar)    Separating optional syntax items within brackets or braces
                    Only one of the specified items can be choosen.
[ ] (brackets)      Optional syntax items (brackets aren't part of the input).
{ } (braces)        Required syntax items (braces aren't part of the input).
[,...]              The preceding item can be repeated n number of times
                    (separated by commas).
[ ...]              The preceding item can be repeated n number of times
                    (separated by blanks).[/code]
[*]SQL statements must be terminated with a semicolon (;). Note the semicolon is required, even if only one statement is specified or for the last one.

[*]SQL statements can be splitted into several lines.

[*]Several forms of comments are allowed (described below).[/list]
[/list]
[list][*][size=14][b]Indentifiers[/b][/size]

Table, index and column names are user supplied identifiers. To minimize portability related issues, all user supplied identifiers are subject to the following rules:

[list][*][b]Backticked (quoted) identifiers:[/b] Some SQL servers allow to use non-ASCII characters, or even Reserved Keywords, when identifiers are backticked (MySQL) or double quoted (other SQL servers). However, even if backticked, a valid identifier for MySQL can be considered illegal on other SQL servers. Therefore, backticks are ignored and the rest of the rules enforced to minimize portability related issues.

[*][b]Reserved Keywords:[/b] Can't be used. To minimize portability related issues identifiers are checked against a huge list of reserved keywords extracted from the following sources:

* [url=http://dev.mysql.com/doc/mysql/en/reserved-words.html]MySQL Reserved Keywords[/url].
* [url=http://www.postgresql.org/docs/8.0/static/sql-keywords-appendix.html]PostgreSQL and Standard SQL 9x Reserved Keywords[/url].
* [url=http://msdn.microsoft.com/library/en-us/tsqlref/ts_ra-rz_9oj7.asp]Microsoft SQL Server Reserved Keywords[/url].

[*][b]Length:[/b] Up to 30 characters. This limit has been found from the minimum length allowed on selected SQL servers:

* [url=http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_03_6e9e.asp]128 characters in Microsoft SQL Server[/url].
* [url=http://dev.mysql.com/doc/mysql/en/legal-names.html]64 characters in MySQL[/url].
* [url=http://www.postgresql.org/docs/7.3/interactive/sql-syntax.html]63 characters in PostgreSQL 7.3 (based on NAMEDATALEN)[/url].
* [url=http://www.postgresql.org/docs/7.2/interactive/sql-syntax.html]31 characters in PostgreSQL 7.2 (based on NAMEDATALEN)[/url].

[*][b]Valid Characters:[/b] First character must be a letter (a-z or A-Z) in any lettercase or an underscore (_), from the second character on, also numbers (0-9) can be used.[/list]
Examples:
[code]forum_id                 -- Valid identifier!
`default`                -- Invalid, it's a Reserver Keyword!
default_id               -- Valid identifier!
default-id               -- Invalid, the hyphen (-) is NOT a valid character!
_default                 -- Valid identifier!
1st_flag                 -- Invalid, starts with a number!
first_flag               -- Valid identifier!
this_is_a_very_very_long_identifier   -- Invalid, 35 characters in length![/code]
[/list]
[list][*][size=14][b]Constants[/b][/size]

[list][*][b]String Constants:[/b]

[list][*]Standard SQL string constants are arbitrary sequences of characters delimited by single quotes.

ie. 'This is a standard SQL string constant'

[*]Double quotes are used in SQL to quote identifiers, except MySQL, which uses backticks for this purpose. MySQL (therefore this SQL parser too) also accepts double quotes to delimit string constants.

ie. "This is NOT standard, but still considered a string constant by MySQL, and so it is here"

[*]Quotes used as string delimiters may be escaped by themselves or by backslashes.

ie. 'Here''s an example' 'Here\'s another example'

[*]Character set specifiers and collations are not supported.[/list]
[*][b]Numeric Constants:[/b]

Numeric constants include Intergers and Floating-Point Numbers. Numeric constants may or may not be enclosed in quotation marks. However, the SQL parser will remove the quotes when possible to prevent the SQL server from performing redundant and unnecessary type conversions. It is not always possible for the parser to know if a numeric constant should be un-quoted (for instance, when setting a default value in an ALTER statement), therefore it is recommended to not quote numbers.

[list][*]Integer constants are represented by a sequence of numbers (0-9), optionally preceded by a hyphen (-) to indicate a negative value. Integers are accepted in the following format:

[code][+-]digits[/code]
Examples:
[code]123
0
-123[/code]
[*][url=http://babbage.cs.qc.edu/courses/cs341/IEEE-754references.html]Floating-Point[/url] numbers are inexact, variable precision numeric types that are represented using scientific notation, accepted in the following format:

[code][+-]digits[[.decimals][e[+-]exponent]][/code]
Examples:
[code]123
123.45
-123.45
123.45e+10
123.45e10
123.45e-10
123e10[/code]
[/list]
[*][b]Boolean Constants:[/b]

[list][*]Boolean values can be specified as 1 (true), 0 (false), TRUE or FALSE (in any lettercase).[/list]
[*][b]NULL Constants:[/b]

[list][*]The NULL value means "no data". It can be written in any lettercase.[/list]
[*][b]Hexadecimal Constants:[/b]

[list][*]Not supported, yet.[/list]
[*][b]Binary Constants:[/b]

[list][*]Not supported, yet.[/list]
[/list]
* [size=9]References about Constants: [url=http://dev.mysql.com/doc/mysql/en/literals.html]MySQL[/url], [url=http://www.postgresql.org/docs/8.0/interactive/sql-syntax.html#SQL-SYNTAX-CONSTANTS]PostgreSQL[/url], [url=http://msdn.microsoft.com/library/en-us/tsqlref/ts_ca-co_1n1v.asp]Microsoft SQL Server[/url].[/size]
[/list]

[list][*][size=14][b]Comments[/b][/size]

Simple SQL comments and C-style comments are allowed. Comments are removed from the input before parsing the SQL statements. Note comment markers (described below) inside of string constants should be (in fact, are) considered part of the constant.

[list][*][b]Simple SQL comments:[/b]

Simple SQL comments are introduced by two consecutive hyphens (--) or by a hash character (#). All input from (and including) any of these comment markers until the end of the line is ignored.

The hash (#), when found on the first character of a line, is used by the MOD Template to delimit MOD Actions (note hashes preceded only by whitespaces may still be considered MOD action delimiters). However, you can still use the hash in the middle of a line or at end of a SQL statement to add comments.

Examples:
[code]ALTER TABLE phpbb_users ADD user_flag1 SMALLINT;   # This is a SQL comment.
ALTER TABLE phpbb_users ADD user_flag2 SMALLINT;   -- This is a SQL comment.
#
# This is NOT a SQL comment!
# Note it will mark the end of the SQL action in the MOD Template![/code]

[*][b]C-style comments:[/b]

C-style comments start with /* and end with */ and may affect multiple lines.

Example:
[code]/*
   This is a multi line comment
*/
ALTER TABLE phpbb_users ADD user_flag1 SMALLINT;[/code]
[/list][/list]

[list][*][size=14][b]Data Types[/b][/size]

[list][*]The following data types are supported:
[code]DATA TYPE                   DESCRIPTION
----------------            ------------------------------
BOOL                        See BOOLEAN
BOOLEAN                     Logical boolean (true/false)
TINYINT[(D)]                Very small integer
SMALLINT[(D)]               Small integer
MEDIUMINT[(D)]              Medium-size integer
INT[(D)]                    See INTEGER
INTEGER[(D)]                Normal-size integer
BIGINT[(D)]                 Large integer
FLOAT[(L[,D])]              Single precision floating-point number
REAL[(L[,D])]               See FLOAT
DOUBLE[(L[,D])]             See DOUBLE PRECISION
DOUBLE PRECISION[(L[,D])]   Double precision floating-point number
DEC[(L[,D])]                See DECIMAL
DECIMAL[(L[,D])]            Exact fixed-point number
FIXED[(L[,D])]              See DECIMAL
NUMERIC[(L[,D])]            See DECIMAL
CHAR[(S)]                   Fixed-size character string
VARCHAR[(S)]                Variable-size character string
BINARY[(S)]                 Fixed-size string of binary data
VARBINARY[(S)]              Variable-size string of binary data
TINYTEXT                    Unlimited-size character string
TEXT                        Unlimited-size character string
MEDIUMTEXT                  Unlimited-size character string
LONGTEXT                    Unlimited-size character string
TINYBLOB                    Binary large Object
BLOB                        Binary large Object
MEDIUMBLOB                  Binary large Object
LONGBLOB                    Binary large Object[/code]
[i](D)[/i] is an optional argument used to specify display width for integer data types.
[i](S)[/i] is an optional argument used to specify field size, in chars or bytes depending on the data type.
[i]L[/i] in [i](L[,D])[/i] is the total number of digits (including decimals).
[i]D[/i] in [i](L[,D])[/i] is the total number of digits after the decimal point.
Note [i](L[,D])[/i] May also be specified for floating-point data types. However, it is not recommended since the meaning of these arguments may vary between SQL servers.
[/list]
[list][*]The following tables detail assumed storage sizes used for integer types:

The column "DW" means "Display Width", which is the maximum recommended value to use as the [i](D)[/i] optional argument for integer types. This value is used by the parser to decide if an unsigned integer should be promoted to a higher size type when converting SQL statements for SQL servers that have no support for the unsigned attribute, such as PostgreSQL, MSSQL, etc.[/list]
[code]TYPE        BYTES        MINIMUM UNSIGNED   DW        MAXIMUM UNSIGNED   DW
---------   -----   ---------------------   --   ---------------------   --
TINYINT       1                      -128    4                    +127    4
SMALLINT      2                    -32768    6                  +32767    6
MEDIUMINT     3                  -8388608    8                +8388607    8
INTEGER       4               -2147483648   11             +2147483647   11
BIGINT        8      -9223372036854775808   20    +9223372036854775807   20[/code]
[code]TYPE        BYTES          MAXIMUM SIGNED   DW
---------   -----   ---------------------   --
TINYINT       1                      +255    4
SMALLINT      2                    +65535    6
MEDIUMINT     3                 +16777215    9
INTEGER       4               +4294967295   11
BIGINT        8     +18446744073709551615   21[/code]

* [size=9]Data Type References: [url=http://dev.mysql.com/doc/mysql/en/column-types.html]MySQL[/url], [url=http://www.postgresql.org/docs/8.0/interactive/datatype.html]PostgreSQL[/url], [url=http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_7msw.asp]Microsoft SQL Server[/url].[/size]
[/list]

[list][*][size=14][b]SQL Reference[/b][/size]

[list][*][size=12][b]CREATE TABLE[/b][/size]

[list][*]Syntax:
[code]CREATE TABLE table_name ({<column_definition>|<index_definition>}[,...]);[/code]
[*]There must be, at least, one table definition element enclosed between parentheses. If more than one element is specified, they must be separated with commas.

[*]Table attributes outside the parentheses are SQL server dependent, therefore are NOT accepted here.

[*][i]<column_definition>[/i] and [i]<index_definition>[/i] are described below.

[*]For the [i]table_name[/i] syntax, please refer to the [b]Identifiers[/b] chapter.[/list]
* [size=9]CREATE TABLE References: [url=http://dev.mysql.com/doc/mysql/en/create-table.html]MySQL[/url], [url=http://www.postgresql.org/docs/8.0/interactive/sql-createtable.html]PostgreSQL[/url], [url=http://msdn.microsoft.com/library/en-us/tsqlref/ts_create2_8g9x.asp]Microsoft SQL Server[/url].[/size]
[/list]

[list][*][size=12][b]ALTER TABLE[/b][/size]

[list][*]Syntax:
[code]ALTER TABLE table_name ADD PRIMARY KEY (<index_key_definition>[,...]);

ALTER TABLE table_name ADD {INDEX|UNIQUE} [index_name] (<index_key_definition>[,...]);

ALTER TABLE table_name ADD [COLUMN] <column_definition>;

ALTER TABLE table_name ALTER [COLUMN] column_name {SET DEFAULT constant|DROP DEFAULT};

ALTER TABLE table_name CHANGE [COLUMN] old_col_name <column_definition>;

ALTER TABLE table_name MODIFY [COLUMN] <column_definition>;

ALTER TABLE table_name DROP [COLUMN] column_name;

ALTER TABLE table_name DROP PRIMARY KEY;

ALTER TABLE table_name DROP INDEX index_name;[/code]
[*]For the [i]table_name[/i], [i]index_name[/i] or [i]column_name[/i] syntax, please refer to the [b]Identifiers[/b] chapter.[/list]
* [size=9]ALTER TABLE References: [url=http://dev.mysql.com/doc/mysql/en/alter-table.html]MySQL[/url], [url=http://www.postgresql.org/docs/8.0/interactive/sql-altertable.html]PostgreSQL[/url], [url=http://msdn.microsoft.com/library/en-us/tsqlref/ts_aa-az_3ied.asp]Microsoft SQL Server[/url].[/size]
[/list]

[list][*][size=12][b]DROP TABLE[/b][/size]

[list][*]Syntax:
[code]DROP TABLE table_name;[/code]
[*]For the [i]table_name[/i] syntax, please refer to the [b]Identifiers[/b] chapter.[/list]
* [size=9]DROP TABLE References: [url=http://dev.mysql.com/doc/mysql/en/drop-table.html]MySQL[/url], [url=http://www.postgresql.org/docs/8.0/interactive/sql-droptable.html]PostgreSQL[/url], [url=http://msdn.microsoft.com/library/en-us/tsqlref/ts_de-dz_7uud.asp]Microsoft SQL Server[/url].[/size]
[/list]

[list][*][size=12][b]Syntax Elements[/b][/size]

[list][*][b]<column_definition>[/b]

[list][*]Syntax:
[code]column_name data_type [UNSIGNED] [ZEROFILL] [BINARY] [NULL|NOT NULL] [DEFAULT constant|NULL] [AUTO_INCREMENT][/code]
[*]The following column attributes are accepted in any order:

[list][*][b]UNSIGNED[/b]: Optional. For numeric types only.
[*][b]ZEROFILL[/b]: Optional. For numeric types only. Ignored on non-MySQL servers.
[*][b]BINARY[/b]: Optional. For CHAR or VARCHAR data types only.
[*][b]NULL or NOT NULL[/b]: Optional. NOT NULL is required for primary keys.
[*][b]DEFAULT[/b]: Optional.
[*][b]AUTO_INCREMENT[/b]: Optional, If used, column must be indexed.[/list]
[*]For the [i]column_name[/i] syntax, please refer to the [b]Identifiers[/b] chapter.[/list][/list]

[list][*][b]<index_definition>[/b]

[list][*]Syntax:
[code]PRIMARY KEY (<index_key_definition>[,...])
{KEY|INDEX} [index_name] (<index_key_definition>[,...])
UNIQUE [INDEX] [index_name] (<index_key_definition>[,...])[/code]
[*]For the [i]index_name[/i] syntax, please refer to the [b]Identifiers[/b] chapter.[/list][/list]

[list][*][b]<index_key_definition>[/b]

[list][*]Syntax:
[code]{column_name[(length)] [ASC|DESC]}[,...][/code]
[*]The [i]length[/i] attribute (next to the column name and enclosed between parentheses) is optional for CHAR, VARCHAR, BINARY and VARBINARY data types and required for TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT data types.

[*]The [i]order[/i] attribute is optional and defaults to ASC.

[*]For the [i]column_name[/i] syntax, please refer to the [b]Identifiers[/b] chapter.[/list]
[/list]
[/list]
[/list]