Re: [sqlite] sqlite syntax auto suggest
On 11/28/19, Laurent Dhont wrote: > is there an API to > get this information in a format that is not an image? By coincidence, I checked in a change two days ago that might be helpful. See https://www.sqlite.org/src/timeline?c=4dbd398d640852d4 for the specific check-in. If you now build SQLite from canonical sources, the file "parse.sql" will be left in the build directory. That file contains SQL text that initializes three SQL tables, the content of which describe the context-free language grammar that SQLite uses to parse its SQL input. There is no documentation of this, but if you have some familiarity with grammars and parsing and tools like Yacc/Bison or Lemon, then you should be able to figure it out. To be clear, I do not expect that the parse.sql file is directly usable by your application in its current form. But it is a machine-readable grammar description, that you can perhaps transform into a useful format using a script. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite syntax auto suggest
Hi all, I am trying to create a fully automated auto suggestion feature for my web application for sqlite. Currently this is going well, but I realized there are to many features in sqlite to hardcode this, so it will be near impossible and take a lot off time. The diagrams on the site of sqlite, for example the select statement https://sqlite.org/syntax/select-stmt.html, is there an API to get this information in a format that is not an image? I really want to make the auto suggestions very specific: A very basic SELECT statement without all the complicated stuff: SELECT column, column, ... FROM table_name WHERE clause, ... After "SELECT" is typed I only show columns in the auto suggestions, if a "," is typed I again show only column names, if there is no column I show only "FROM", after "FROM" I show only table_names, Please do not feel obligated to answer this question, if you do this is much appreciated! Thanks in advance. Kind regards, Laurent Dhont ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] built-in printf() not supporting \n or \t ?
On Thursday, 28 November, 2019 09:21, Richard Damon wrote: >\n and \t are not 'printf' features, but C string features, that \ is an >escape introducer for compiling a string, and if followed by a letter >like n or t it builds a string with the special value represented by >that function. The \n does NOT make it into the string itself, only the >code for a newline. You are not typing your line into a C compiler, but >the sqlite shell program, so it build strings differently. As far as I know the only place that the \ escape introducer is parsed in SQLite3 is in the arguments to the dot commands in the CLI. This is so, for example, one may use commands like: .seperator \t \n otherwise you would not be able to set the seperator characters to non-printable characters. Note that for that particular case the CLI is a "presentation" layer tool, so one would expect that behaviour. Otherwise, SQLite3 is not a "presentation" layer tool but rather a "data storage" layer tool and as such does not muck about with data that it is given or returns. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] built-in printf() not supporting \n or \t ?
On 11/28/19 10:24 AM, Dominique Devienne wrote:
> Obviously it's ugly to use concatenation and char() to format a string
> literal with tabs and newlines.
> Is there a better way? Why doesn't printf() support newlines and tabs like
> it's C cousin? --DD
>
> PS: Built-in printf() also doesn't support positional params, to "emulate"
> newline with printf( '%1$s1: %2$s%1$s2: %3$s%1$s' , char(10), 'one',
> 'two'), but that's not too readable either, in any case
>
> sqlite> select printf('\n1: %s\n2: %s\n', 'one', 'two');
> \n1: one\n2: two\n
> sqlite> select char(10)||printf('1: %s', 'one')||char(10)||printf('2: %s',
> 'two')||char(10);
>
> 1: one
> 2: two
>
> sqlite> select printf('\t1: %s\t2: %s\t', 'one', 'two');
> \t1: one\t2: two\t
> sqlite> select char(9)||printf('1: %s', 'one')||char(9)||printf('2: %s',
> 'two')||char(9);
> 1: one 2: two
> sqlite>
A couple of things to note:
\n and \t are not 'printf' features, but C string features, that \ is an
escape introducer for compiling a string, and if followed by a letter
like n or t it builds a string with the special value represented by
that function. The \n does NOT make it into the string itself, only the
code for a newline. You are not typing your line into a C compiler, but
the sqlite shell program, so it build strings differently.
ISO C also does not support positional arguments, that is an extension
that many compiler provide as I believe it is a common extension used in
linux.
You also don't NEED positional arguements, you could use
printf('%s1: %s%s2: %s', char(10), 'one', char(10), 'two')
There is a discusson of why SQLite uses its own printf here:
https://sqlite.org/printf.html
--
Richard Damon
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] built-in printf() not supporting \n or \t ?
Obviously it's ugly to use concatenation and char() to format a string
literal with tabs and newlines.
Is there a better way? Why doesn't printf() support newlines and tabs like
it's C cousin? --DD
PS: Built-in printf() also doesn't support positional params, to "emulate"
newline with printf( '%1$s1: %2$s%1$s2: %3$s%1$s' , char(10), 'one',
'two'), but that's not too readable either, in any case
sqlite> select printf('\n1: %s\n2: %s\n', 'one', 'two');
\n1: one\n2: two\n
sqlite> select char(10)||printf('1: %s', 'one')||char(10)||printf('2: %s',
'two')||char(10);
1: one
2: two
sqlite> select printf('\t1: %s\t2: %s\t', 'one', 'two');
\t1: one\t2: two\t
sqlite> select char(9)||printf('1: %s', 'one')||char(9)||printf('2: %s',
'two')||char(9);
1: one 2: two
sqlite>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] virtual tables vs. expression-based indexes
If your external data store can maintain an index on some expression, then
exposing that index as a computed field is the way to go with a virtual table.
Alternatively, you can expose the index as a separate virtual table with a
"foreign key" that references the original virtual table and join them together.
CREATE VIRTUAL TABLE my_data USING my_module;
-> declares (data_id INTEGER PRIMARY KEY, name TEXT);
CREATE VIRTUAL TABLE my_name_length USING my_module('name_length;length(name)');
-> declares (name_length INTEGER, data_id INTEGER);
SELECT d.* from my_data d JOIN my_name_length l ON (d.data_id = l.data_id)
WHERE l.name_length > 100;
xBestIndex for my_data should report 1 unique record for access via data_id,
and card(my_data) for full table scan
xBestIndex form y_name_length should report card(my_data)/card(unique
name_length) for access via name_length
This should allow SQLite to compute the correct query plan. Or you could just
CROSS JOIN it.
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Jens Alfke
Gesendet: Donnerstag, 28. November 2019 03:10
An: SQLite mailing list
Betreff: [EXTERNAL] [sqlite] virtual tables vs. expression-based indexes
I'm considering using a virtual table to query an external-to-SQLite data
store. However, I've carefully read about the xBestIndex method, and it appears
that virtual tables cannot have indexes on expressions; or rather that the
SQLite query engine can't make use of such indexes, only indexes on columns.
Consider for example a virtual table with a column named "text", and a query
with `WHERE length(text) > 100`. In my external data store I can create an
index on `length(text)`, but it doesn't look as though SQLite has any way of
asking me about it, so I assume it will just brute-force scan through every row.
The only workaround I can see is to add a virtual table column for every
possible expression that might be queried against — like "text_length" — but
the query interface in my project is open-ended enough that I can't delimit the
set of expressions that might need to be exposed this way. (It might be
feasible if I could alter the table on the fly to add columns as needed, but
the docs explicitly say I can't do that.)
—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
Gunter Hick | Software Engineer | Scientific Games International GmbH |
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43
1 80100 - 0
May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
