Notes
SQL is Structured Query Language. Each query stands for one database operation. SDS supports only simple queries, it does not support multiple queries, stored procedures and triggers. All supported statements are described in this document.
Data in SQL must have its type. SDS has simplified data model, it has only 6 integrated types.
Statements consist of keywords, table/field identifiers, operators which are strictly ordered. Keywords are reserved SQL words (SELECT, CREATE, DROP, DELETE, INSERT, UPDATE, FROM, WHERE, INTO, SET, ORDER BY, LIMIT). They must stand on their predefined positions. SDS ignores keywords standing not on their positions (i.e. identifiers with reserved word names) but it is still bad style. So, use backticks (`identifier`) if identifier name matches some keyword. As you might suppose, keywords are case insensitive. But they are usually printed uppercase. Identifiers are table and field names. SDS field names are alpha_numeric values. SQL syntax like `table.field` is not supported. Table names in SDS are file names. It can be full or relative file path in quotes ("table path"), ticks ('table path') or backticks (`table path`).
SDS has 6 integrated data types:
VARCHAR/TEXT
Simplified string data type. It can be any ANSI string, length is not limited. You should put data of this type in quotes or ticks:
INSERT INTO `test.sds` (str1, str2) VALUES ("Some string", 'Some string also')
INT/INTEGER
Integer type. In fact, it is 4-byte integer type (range is -2147483648 .. 2147483647). No quotes needed:
INSERT INTO `test.sds` (int1, int2) VALUES (500, -4096)
FLOAT/REAL/DOUBLE
Double precision float. In fact, 8-byte float type (range is 5.0E-324 .. 1.7E308). No quotes needed:
INSERT INTO `test.sds` (real1, real2) VALUES (500.895, -4096.120)
DATE
Date in YYYY-MM-DD format, should be quoted:
INSERT INTO `test.sds` (dt1, dt2) VALUES ('2005-09-03', "1999-12-31")
Macro NOW stands for current date:
INSERT INTO `test.sds` (dt1) VALUES (NOW)
SELECT * FROM `rest.sds` WHERE dt < NOW
TIME
Time in HH:MM:SS format, should be quoted:
INSERT INTO `test.sds` (t1, t2) VALUES ('17:16:01', "23:00:00")
Macro NOW stands for current time:
INSERT INTO `test.sds` (t1) VALUES (NOW)
SELECT * FROM `rest.sds` WHERE t < NOW
DATETIME
Date and time in YYYY-MM-DD HH:MM:SS format, should be quoted:
INSERT INTO `test.sds` (dtm1, dtm2) VALUES ('2005-09-03 17:16:01', "1999-12-31 23:00:00")
Macro NOW stands for current date and time:
INSERT INTO `test.sds` (dtm1) VALUES (NOW)
SELECT * FROM `rest.sds` WHERE dtm < NOW
SDS supports following SQL operators in conditions:
= - equal,
< - less than,
> - greater than,
<= - less than or equal,
>= - greater than or equal,
<> - not equal
They are provided for all supported data types. There are also special operators for VARCHAR/TEXT:
REGEXP - regular expression match,
CASE_EQ - case insensitive equal,
CASE_NOT_EQ - case insensitive not equal,
CASE_LT - case insensitive less than,
CASE_GT - case insensitive greater than,
CASE_LT_OR_EQ - case insensitive less than or equal,
CASE_GT_OR_EQ - case insensitive greater than or equal,
NAT_LT - natural algorithm less than,
NAT_GT - natural algorithm greater than,
NAT_LT_OR_EQ - natural algorithm less than or equal,
NAT_GT_OR_EQ - natural algorithm greater than or equal,
NAT_CASE_LT - natural algorithm case insensitive less than,
NAT_CASE_GT - natural algorithm case insensitive greater than,
NAT_CASE_LT_OR_EQ - natural algorithm case insensitive less than or equal,
NAT_CASE_GT_OR_EQ - natural algorithm case insensitive greater than or equal
Example:
SELECT * FROM `test.sds` WHERE id < 100 OR name NAT_CASE_GT "John"
Note about REGEXP operator. It works like RegexpCheck in the Regexp.pas unit included with Powtils. Here is an example:
SELECT * FROM `test.sds` WHERE name REGEXP '/(foo|bar)123/i'
SDS supports simple logical conditions with AND, OR, XOR, AND NOT, OR NOT, XOR NOT operators. Nested conditions (with braces) are not supported. Here are some condition examples:
WHERE id = 500
WHERE id > 100 OR name = "john"
WHERE id >= 100 OR name REGEXP '/foo(bar)?/i' AND posts < 100
When building conditions remember about boolean arithmetic. AND is * when OR is +.
Syntax:
CREATE TABLE %TABLE_PATH%
(%COLUMN_NAME% %COLUMN_TYPE%, %COLUMN_NAME% %COLUMN_TYPE%, ...)
Creates new SDS table. %TABLE_PATH% is full or relative file path for a new table in quotes, ticks and backticks. %COLUMN_NAME% is field identifier, %COLUMN_TYPE% is one of supported data types. Example:
CREATE TABLE `test.sds` (id INT, login TEXT, posts INT, cash REAL, `date` DATETIME)
Very important SDS feature is that first field is
always same as UNSIGNED INT AUTO_INCREMENT PRIMARY KEY in MySQL. In SDS you give it name, put as INT but you should not care of its content because SDS automatically increments it by 1 for each new inserted row. Also notice that SDS does not support such SQL constructs as: NOT NULL, PRIMARY KEY, DEFAULT, etc.
Syntax:
DROP TABLE %TABLE_PATH%
Removes table from disk. %TABLE_PATH% is full or relative file path. Example:
DROP TABLE `test.sds`
Syntax:
INSERT INTO %TABLE_PATH%
(%COLUMN_NAME% , %COLUMN_NAME%, ...)
VALUES
(%INSERT_VALUE%, %INSERT_VALUE%, ...)
Inserts a new row into table. %TABLE_PATH% is full or relative path to table file in quotes, ticks or backticks. %COLUMN_NAME% is field identifier. %INSERT_VALUE% is value for insertion. Don't forget about ticks, quotes and NOW macro. Query example:
INSERT INTO `test.sds` (name, posts, `date`) VALUES ("John", 500, NOW)
Syntax:
DELETE FROM %TABLE_PATH%
WHERE %CONDITION%
Deletes all rows in %TABLE_PATH% which match the %CONDITION%. %TABLE_PATH% is full or relative path to table file in quotes, ticks or backticks. %CONDITION% is as described in
SDS Conditions. Example:
DELETE FROM `test.sds` WHERE name = "John" or posts < 10
Syntax:
UPDATE %TABLE_PATH%
SET %COLUMN_NAME% = %UPDATE_VALUE%, %COLUMN_NAME% = %UPDATE_VALUE%, ...
WHERE %CONDITION%
Updates specified fields of %TABLE_PATH% in rows that match %CONDITION%. %TABLE_PATH% is full or relative path to table file in quotes, ticks or backticks. %CONDITION% is as described in
SDS Conditions. %COLUMN_NAME% is field identifier, %UPDATE_VALUE% is new value. NOW macro is also supported for %UPDATE_VALUE%. Example:
UPDATE `test.sds` SET name = "John", posts = 400, `date` = NOW WHERE id = 2
Syntax:
SELECT %COLUMN_NAME%, %COLUMN_NAME%, ... || * || ALL || COUNT(*) MIN(%COLUMN_NAME%) || MAX(%COLUMN_NAME%)
FROM %TABLE_PATH%
WHERE %CONDITION%
ORDER BY %COLUMN_NAME ASC || DESC, %COLUMN_NAME ASC || DESC, ...
LIMIT %OFFSET%, %COUNT% || %COUNT%
This statement is used to select rows from table %TABLE_PATH%. %TABLE_PATH% is full or relative path to table file in quotes, ticks or backticks.
%COLUMN_NAME%, %COLUMN_NAME%, ...
This sequence is used when you need to select specified columns from each matching row. %COLUMN_NAME% is field identifier. Example:
SELECT name, `something_else`, posts FROM `test.sds`
* || ALL
Metasymbol * or ALL stand for all fields in matching row. Use it if you want to select all data of each row matching the select condition. Examples:
/* This will select whole table */
SELECT * FROM `test.sds`
/* Another example */
SELECT ALL FROM `test.sds` WHERE id = 1
COUNT(*)
This is special sequence for learning how many rows match the condition. In this SELECT mode SDS will not return any rowset, it returns only number of rows (SdsResultRows). Example:
SELECT COUNT(*) FROM `test.sds` WHERE posts > 1000
MIN()
This sequence is used to select one row with minimal value (the smallest in the table) of specified field. %COLUMN_NAME% is field identifier. Example:
SELECT MIN(cash) FROM `test.sds` WHERE `registered` < NOW
MAX()
This sequence is used to select one row with maximal value (the biggest in the table) of specified field. %COLUMN_NAME% is field identifier. Example:
SELECT MAX(cash) FROM `test.sds` WHERE `registered` < NOW
WHERE
This sequence specifies
selection condition.
ORDER BY
This sequence is used to specify order in selected rowset. SDS allows ascending (ASC) and descending (DESC) order by several fields. It uses fast and efficient Shell sorting algorithm which saves memory and CPU time. Example:
SELECT * FROM `test.sds` WHERE posts > 100 ORDER BY `date` DESC, name ASC
If no DESC or ASC is defined ascending sort is assumed.
LIMIT
This is MySQL-compliant rowset limit sequence. If two parameters are specified, first one is row offset and second is row count. If one parameter is specified it is row count and 0 offset is assumed. To understand offset and row count meaning lets look at the example:
SELECT * FROM `test.sds` WHERE cash > 50.20 LIMIT 5, 15
This query will select all column from rows with cash greater than 50.20 starting at 6th matching row and putting into result rowset not more than 15 rows (i.e. rows 6-15).