About functions illegalSqlName, isReservedSqlName and isReservedSqliteName

[Alexey Pechnikov]

I did rewrite code with these functions as:

/*
if (illegalSqlName (*(text->titles + i))
|| isReservedSqlName (*(text->titles + i))
|| isReservedSqliteName (*(text->titles + i)))
sprintf (dummyName, "COL_%d", seed++);
else
strcpy (dummyName, *(text->titles + i));
*/
sprintf (dummyName, "\"%s\"", *(text->titles + i));

It's possible using any text string placed into double quotes as table
or column name.

As example:

$ head access.log -n 1
195.98.38.96 - - [1234732034] "GET /ajax/messages.adp HTTP/1.0" 200
169 "https://offline.mts.mobigroup.ru/extension/report_02/report.adp"
"Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR
1.1.4322; .NET CLR 2.0.50727; .NET CLR 3.0.04506.30)"

$ sqlite3 :memory:
sqlite> create virtual table test using VirtualText
(access.log,utf8,1,POINT,DOUBLEQUOTE,' ');
sqlite> .header on
sqlite> select * from test limit 1;
ROWNO|195.98.38.96|-|COL_0|[1234732034]|GET_/ajax/messages.adp_HTTP/
1.0|200|169|https://offline.mts.mobigroup.ru/extension/report_02/
report.adp|Mozilla/4.0_
(compatible;_MSIE_6.0;_Windows_NT_5.1;_SV1;_.NET_CLR_1.1.4322;_.NET_CLR_2.0.50727;_.NET_CLR_3.0.04506.30)
1|195.98.38.96|-|-|[1234732041]|GET /ajax/messages.adp HTTP/1.1|200|
167|https://offline.mts.mobigroup.ru/extension/report_01/report.adp|
Mozilla/5.0 (Windows; U; Windows NT 5.1; ru; rv:1.8.1) Gecko/20061010
Firefox/2.0

sqlite> select "GET_/ajax/messages.adp_HTTP/1.0|200|169|https://
offline.mts.mobigroup.ru/extension/report_02/report.adp" from test
limit 1;
"GET_/ajax/messages.adp_HTTP/1.0|200|169|https://
offline.mts.mobigroup.ru/extension/report_02/report.adp"
GET_/ajax/messages.adp_HTTP/1.0|200|169|https://
offline.mts.mobigroup.ru/extension/report_02/report.adp

sqlite> pragma table_info(test);
0|ROWNO|INTEGER|0||0
1|195.98.38.96|TEXT|0||0
2|-|TEXT|0||0
3|COL_0|TEXT|0||0
4|[1234732034]|TEXT|0||0
5|GET_/ajax/messages.adp_HTTP/1.0|TEXT|0||0
6|200|TEXT|0||0
7|169|TEXT|0||0
8|https://offline.mts.mobigroup.ru/extension/report_02/report.adp|TEXT|
0||0
9|Mozilla/4.0_
(compatible;_MSIE_6.0;_Windows_NT_5.1;_SV1;_.NET_CLR_1.1.4322;_.NET_CLR_2.0.50727;_.NET_CLR_3.0.04506.30)|
TEXT|0||0

[Alessandro Furieri]

Alexey,

you are perfectly right: standard SQL syntax allows to
mask any kind of column or table name, simply enclosing
it within double quotes.
This feature is supported by MySql, PostgreSQL and SQLite
as well.

But I fear that such a 'sophisticated' syntax may easily
confuse beginners, because it requires a careful name
decoration-qualification.
And may introduce some extra complexity for query
generators and other SW tools.

bye Sandro

[Alexey Pechnikov]

Usually the standart behaviour is much better then different
solutions. Beginners did know a few SQL by different article and books
and may be confused that your extension work is strange. And
professionals are confused more than beginners...

SQL is very popular declarative language and making it simpler but non-
standart is not needed I think.