sqlite syntax auto suggest

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

sqlite syntax auto suggest

Laurent Dhont
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite syntax auto suggest

Richard Hipp-3
On 11/28/19, Laurent Dhont <[hidden email]> 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
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite syntax auto suggest

Domingo Alvarez Duarte
Hello Richard !

I just looked at it and I have some doubts about the generated
parse.sql, as I see it there is this table:

====

CREATE TABLE symbol(
   id INTEGER PRIMARY KEY,
   name TEXT NOT NULL,
   isTerminal BOOLEAN NOT NULL,
   fallback INTEGER REFERENCES symbol
);

--and this entries

--...

INSERT INTO symbol(id,name,isTerminal,fallback)VALUES(2,'EXPLAIN',TRUE,59);

--...

INSERT INTO
symbol(id,name,isTerminal,fallback)VALUES(185,'explain',FALSE,NULL);

====

Wouldn't it be better to have:

====

CREATE TABLE symbol(
   id INTEGER PRIMARY KEY,
   name TEXT NOT NULL COLLATE NOCASE UNIQUE,
   isTerminal BOOLEAN NOT NULL,
   fallback INTEGER REFERENCES symbol
);

--and this entries

--...

INSERT INTO
symbol(id,name,isTerminal,fallback)VALUES(2,'EXPLAIN',TRUE,FALSE);

====

This way we can search for any combination of individual letter cases
and find a unique match ?

Cheers !

On 28/11/19 23:54, Richard Hipp wrote:

> On 11/28/19, Laurent Dhont <[hidden email]> 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.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite syntax auto suggest

Domingo Alvarez Duarte
In reply to this post by Richard Hipp-3
Hello Richard !

My bad after writing the last reply I found that "EXPLAIN" and "explain"
are different symbols there.

Could a small comment be generated at the begin of the generated
"parse.sql" ?

Something like: (dummy example)

====

---

-- This is a generated output from lemon parse generator

-- The symbol table can hold terminals and no terminals denoted by ...

-- ...

====

Cheers !

On 28/11/19 23:54, Richard Hipp wrote:

> On 11/28/19, Laurent Dhont <[hidden email]> 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.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite syntax auto suggest

Domingo Alvarez Duarte
In reply to this post by Richard Hipp-3
Hello Richard !

Again after rereading your reply and trying to use the "parse.sql" I
noticed that it doesn't have a "begin;../commit;" wrapper as it's
recommended to not having "fsync" calls on each insert.

Cheers !

On 28/11/19 23:54, Richard Hipp wrote:

> On 11/28/19, Laurent Dhont <[hidden email]> 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.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite syntax auto suggest

Domingo Alvarez Duarte
In reply to this post by Richard Hipp-3
Hello Richard !

Sorry by split my reply in so many small ones !

When trying to use the generate "parse.sql" I'm getting this:

====

sqlite3 parse.db < parse.sql
Error: near line 10: FOREIGN KEY constraint failed
Error: near line 11: FOREIGN KEY constraint failed
Error: near line 12: FOREIGN KEY constraint failed
Error: near line 13: FOREIGN KEY constraint failed

====

I have "foreign key" ON by default and added "BEGIN; ... COMMIT;" around
the generated SQL.

====

BEGIN;
CREATE TABLE symbol(
   id INTEGER PRIMARY KEY,
   name TEXT NOT NULL,
   isTerminal BOOLEAN NOT NULL,
   fallback INTEGER REFERENCES symbol
);
INSERT INTO symbol(id,name,isTerminal,fallback)VALUES(0,'$',TRUE,NULL);

...

INSERT INTO rule(ruleid,lhs)VALUES(384,261);
COMMIT;

====

Cheers !

On 28/11/19 23:54, Richard Hipp wrote:

> On 11/28/19, Laurent Dhont <[hidden email]> 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.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite syntax auto suggest

Laurent Dhont
In reply to this post by Richard Hipp-3
Dear Richard,


This has been very helpful, I have the parse.sql file. I feel very
flattered that you took the time to answer my question. My boss says you
are the one that should win a nobel prize.

This has been much appreciated!!


I completely understand if you won't answer anymore but I can try.

 From what I can understand:

  * the symbol table lists all keywords that can be typed in a query
    where [isTerminal] = 1.
  * The other rows where [isTerminal] = 0 are some kind of
    commands/placeholders. This I do not really know.
  * There is never a direct connection from an [isTerminal] = 1 row to
    an [isTerminal] = 0 row.
  * I think but I am not sure that [pos] means position. But maybe I am
    wrong because if I look at for example the FROM keyword I see [pos]
    = 0 with lowwercase 'from' and from what I understand you cannot use
    the FROM keyword in the first position.


Could you perhaps explain what pos means and what the [isTerminal] = 0
rows mean.

If you have not time for these things I completely understand, thanks
anyway!


Laurent

On 28.11.19 23:54, Richard Hipp wrote:

> On 11/28/19, Laurent Dhont<[hidden email]>  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.  Seehttps://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.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite syntax auto suggest

Richard Hipp-3
On 11/29/19, Laurent <[hidden email]> wrote:
>
> Could you perhaps explain what pos means and what the [isTerminal] = 0
> rows mean.
>

New check-in enhances the output to include a comment in the SQL
before the encoding of each production rule.  This should help make it
clear what the SQL is trying to represent.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite syntax auto suggest

Dominique Devienne
On Fri, Nov 29, 2019 at 2:10 PM Richard Hipp <[hidden email]> wrote:

> On 11/29/19, Laurent <[hidden email]> wrote:
> >
> > Could you perhaps explain what pos means and what the [isTerminal] =
> 0 rows mean.
>
> New check-in enhances the output to include a comment in the SQL
> before the encoding of each production rule.  This should help make it
> clear what the SQL is trying to represent.
>

Hi. Why not add a column and write it to the SQL insert?

Rather than as a comment in the SQL "dump"? The first thing I'd do with
that new output file,
would be to run it to generate the DB, then look at the DB in a graphical
client, to side and dice
the data. As it stands, those useful comments would be lost in that
scenario. My $0.02. --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite syntax auto suggest

Domingo Alvarez Duarte
Hello Dominique !

Here are two views that can bring that info to a query:

======

CREATE VIEW rule_list_view AS
SELECT
     a.ruleid,
     b.name,
     a.lhs
FROM rule AS a
LEFT JOIN symbol AS b ON a.lhs = b.id;

CREATE VIEW rulerhs_list_view AS
SELECT
     a.ruleid,
     b.name as symbol_name,
     c.name as rule_name,
     a.pos,
     a.sym
FROM rulerhs AS a
LEFT JOIN symbol AS b ON a.sym = b.id
LEFT JOIN rule_list_view AS c ON a.ruleid = c.ruleid;

======

Maybe others can contribute with other views to make the info there
easier to use !

Cheers !

On 29/11/19 14:56, Dominique Devienne wrote:

> On Fri, Nov 29, 2019 at 2:10 PM Richard Hipp <[hidden email]> wrote:
>
>> On 11/29/19, Laurent <[hidden email]> wrote:
>>> Could you perhaps explain what pos means and what the [isTerminal] =
>> 0 rows mean.
>>
>> New check-in enhances the output to include a comment in the SQL
>> before the encoding of each production rule.  This should help make it
>> clear what the SQL is trying to represent.
>>
> Hi. Why not add a column and write it to the SQL insert?
>
> Rather than as a comment in the SQL "dump"? The first thing I'd do with
> that new output file,
> would be to run it to generate the DB, then look at the DB in a graphical
> client, to side and dice
> the data. As it stands, those useful comments would be lost in that
> scenario. My $0.02. --DD
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: sqlite syntax auto suggest

Laurent Dhont
In reply to this post by Richard Hipp-3
Dear Richard & others,


Now it is clear but how do I let the user make for example an UPDATE
query without the OR.

I have UPDATE on position 0, then I want OR, OF, tableName. This works
but SET is only available on position 4-5. I want this also on position 2.


What I want to do is get a list of all words that can be used in the
next word the user is going to type.


I hope I am making sense.


Thanks!


Kind regards,


Laurent


On 29.11.19 14:09, Richard Hipp wrote:
> On 11/29/19, Laurent <[hidden email]> wrote:
>> Could you perhaps explain what pos means and what the [isTerminal] = 0
>> rows mean.
>>
> New check-in enhances the output to include a comment in the SQL
> before the encoding of each production rule.  This should help make it
> clear what the SQL is trying to represent.
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users