jsonsql-0.1.0.1: NOTES

String literals

mysql
- http://dev.mysql.com/doc/refman/5.0/en/string-literals.html
postgres
- http://www.postgresql.org/docs/8.2/static/sql-syntax-lexical.html
sqlite3
- https://www.sqlite.org/lang_expr.html

------------------------------------------------------------------------
Postgres


A string constant in SQL is an arbitrary sequence of characters bounded by
single quotes ('), for example 'This is a string'. To include a single-quote
character within a string constant, write two adjacent single quotes, e.g.
'Dianne''s horse'. Note that this is not the same as a double-quote character
(").

Two string constants that are only separated by whitespace with at least one
newline are concatenated and effectively treated as if the string had been
written as one constant. For example:

SELECT 'foo'
'bar';
is equivalent to

SELECT 'foobar';

but

SELECT 'foo'      'bar';

is not valid syntax. (This slightly bizarre behavior is specified by SQL;
PostgreSQL is following the standard.)

PostgreSQL also accepts "escape" string constants, which are an extension to
the SQL standard. An escape string constant is specified by writing the letter
E (upper or lower case) just before the opening single quote, e.g. E'foo'.
(When continuing an escape string constant across lines, write E only before
the first opening quote.) Within an escape string, a backslash character (\)
begins a C-like backslash escape sequence, in which the combination of
backslash and following character(s) represents a special byte value. \b is a
backspace, \f is a form feed, \n is a newline, \r is a carriage return, \t is a
tab. Also supported are \digits, where digits represents an octal byte value,
and \xhexdigits, where hexdigits represents a hexadecimal byte value. (It is
your responsibility that the byte sequences you create are valid characters in
the server character set encoding.) Any other character following a backslash
is taken literally. Thus, to include a backslash character, write two
backslashes (\\). Also, a single quote can be included in an escape string by
writing \', in addition to the normal way of ''.



MySQL

A string is a sequence of bytes or characters, enclosed within either single
quote (“'”) or double quote (“"”) characters. Examples:

'a string'
"another string"

Quoted strings placed next to each other are concatenated to a single string.
The following lines are equivalent:

'a string'
'a' ' ' 'string'

If the ANSI_QUOTES SQL mode is enabled, string literals can be quoted only
within single quotation marks because a string quoted within double quotation
marks is interpreted as an identifier.

Within a string, certain sequences have special meaning unless the
NO_BACKSLASH_ESCAPES SQL mode is enabled. Each of these sequences begins with a
backslash (“\”), known as the escape character. MySQL recognizes the escape
sequences shown in Table 9.1, “Special Character Escape Sequences”. For all
other escape sequences, backslash is ignored. That is, the escaped character is
interpreted as if it was not escaped. For example, “\x” is just “x”. These
sequences are case sensitive. For example, “\b” is interpreted as a backspace,
but “\B” is interpreted as “B”. Escape processing is done according to the
character set indicated by the character_set_connection system variable. This
is true even for strings that are preceded by an introducer that indicates a
different character set, as discussed in Section 10.1.3.5, “Character String
Literal Character Set and Collation”.

Table 9.1 Special Character Escape Sequences

Escape Sequence	Character Represented by Sequence
\0	An ASCII NUL (0x00) character.
\'	A single quote (“'”) character.
\"	A double quote (“"”) character.
\b	A backspace character.
\n	A newline (linefeed) character.
\r	A carriage return character.
\t	A tab character.
\Z	ASCII 26 (Control+Z). See note following the table.
\\	A backslash (“\”) character.
\%	A “%” character. See note following the table.
\_	A “_” character. See note following the table.



Sqlite3:

A string constant is formed by enclosing the string in single quotes ('). A
single quote within the string can be encoded by putting two single quotes in a
row - as in Pascal. C-style escapes using the backslash character are not
supported because they are not standard SQL.

BLOB literals are string literals containing hexadecimal data and preceded by a
single "x" or "X" character. Example: X'53514C697465'

A literal value can also be the token "NULL".



Real World Haskell

A second problem involves escaping characters. What if you wanted to insert the
string "I don't like 1"? SQL uses the single quote character to show the end of
the field. Most SQL databases would require you to write this as 'I don''t like
1'. But rules for other special characters such as backslashes differ between
databases. Rather than trying to code this yourself, HDBC can handle it all for
you. Let's look at an example. 4 comments




http://book.realworldhaskell.org/read/efficient-file-processing-regular-expressions-and-file-name-matching.html

-- file: ch08/GlobRegex.hs
globToRegex' :: String -> String
globToRegex' "" = ""

globToRegex' ('*':cs) = ".*" ++ globToRegex' cs

globToRegex' ('?':cs) = '.' : globToRegex' cs

globToRegex' ('[':'!':c:cs) = "[^" ++ c : charClass cs
globToRegex' ('[':c:cs)     = '['  :  c : charClass cs
globToRegex' ('[':_)        = error "unterminated character class"

globToRegex' (c:cs) = escape c ++ globToRegex' cs


-- file: ch08/GlobRegex.hs
escape :: Char -> String
escape c | c `elem` regexChars = '\\' : [c]
         | otherwise = [c]
             where regexChars = "\\+()^$.{}]|"

-- file: ch08/GlobRegex.hs
charClass :: String -> String
charClass (']':cs) = ']' : globToRegex' cs
charClass (c:cs)   = c : charClass cs
charClass []       = error "unterminated character class"


Dates?