ricomariani / cg-sql-author Goto Github PK

CG-SQL Author's Cut: CG/SQL is a compiler that converts a SQL Stored Procedure like language into C for SQLite. SQLite has no stored procedures of its own. CG/CQL can also generate other useful artifacts for testing and schema maintenance.

Home Page: https://ricomariani.github.io/CG-SQL-author/

License: Other

HTML 0.07% Shell 1.65% Makefile 0.12% C 84.58% Python 0.64% Lex 0.94% Yacc 2.35% Lua 0.80% Objective-C 0.09% Java 0.12% TSQL 7.98% PLpgSQL 0.60% C++ 0.03% Awk 0.02%

cg-sql-author's People

Contributors

Stargazers

Watchers

Forkers

timch326 grifx

cg-sql-author's Issues

Hydrates complex nested structures from flat rows

I was thinking about using a special convention with special chars to automatically hydrate flat rows to nested structures.

Is it a bad idea or harder than it looks to solve?

unquoted:

❯ echo 'SELECT "it works" AS té€$t☺😊' | sqlite3 :memory: -box
┌──────────┐
│ té€$t☺😊  │
├──────────┤
│ it works │
└──────────┘

backtick:

echo 'SELECT "it works" AS `~$.123ABCabc$%^&*().{}[]:,.-~?`;' | sqlite3 :memory: -box

┌────────────────────────────────┐
│ ~$.123ABCabc$%^&*().{}[]:,.-~? │
├────────────────────────────────┤
│ it works                       │
└────────────────────────────────┘

Grammar

select_expr ::= expr opt_as_alias | name '.' '*' | column_calculation
opt_as_alias ::= /* nil */ | as_alias
as_alias ::= "AS" name | name
name ::= "ID" | "TEXT" | "TRIGGER" | "ROWID" | "REPLACE" | "KEY" | "VIRTUAL" | "TYPE" | "HIDDEN" | "PRIVATE" | "FIRST" | "LAST"

ID: $ => /[_A-Za-z][A-Za-z0-9_]*/

Thanks

Query Plans issues

A few issues related to query plan generation:

Using `CAST` in queries can lead to CAST enforcement errors in query plan file

CREATE PROC a_proc() BEGIN
  declare v long not null; -- this is a long for whatever reason
  set v := 1;
  select cast(v as int) foo;
END;

If we have something like the above, running the query plan would fail:

go-qp.sql:52:1: error: in call : CQL0013: cannot assign/copy possibly null expression to not null target 'v'
go-qp.sql:55:1: error: in str : CQL0069: name not found 'C'
go-qp.sql:229:1: error: in call_stmt : CQL0213: procedure had errors, can't call 'populate_query_plan_1'

Using shared fragment arguments inferred to be not null

cg_query_plan.c variable replacement logic isn't picking up the inferred not null typing of a variable.

Code to reproduce:

@attribute(cql:shared_fragment)
CREATE PROC frag(v integer not null) BEGIN
  select v val;
END;

CREATE PROC use_frag(v integer) BEGIN
  set v := 1; -- v is now not null
  with
    (call frag(v))
  select * from frag;
END;

Error Result:

go-qp.sql:52:1: error: in call : CQL0013: cannot assign/copy possibly null expression to not null target 'v'
go-qp.sql:55:1: error: in str : CQL0069: name not found 'C'
go-qp.sql:229:1: error: in call_stmt : CQL0213: procedure had errors, can't call 'populate_query_plan_1'

Making proc call in shared cte argument

It seems like cg_query_plan isn't replacing the call to a_proc() with a dummy value the same way as variables would get replaced.

Code to reproduce:

CREATE PROC a_proc(out v int not null) BEGIN
  set v := 1;
END;

CREATE PROC use_frag() BEGIN
  with
    (call frag(a_proc()))
  select * from frag;
END;

Error Result:

go-qp.sql:52:1: error: in str : CQL0094: function not yet implemented 'a_proc'
go-qp.sql:55:1: error: in str : CQL0069: name not found 'C'
go-qp.sql:229:1: error: in call_stmt : CQL0213: procedure had errors, can't call 'populate_query_plan_1'

No way to support databases with tables that have exotic names

e.g. a database with a table named [x y]and a column named [a b] is fundamentally not usable by CQL today.

The idea here to add basic support for this:

Introduce `x y` as a valid quoted name format
support this in sql statements where a sql name can be used
- DONE insert, update, select, delete forms
- DONE create table (table and column names and constraints)
- DONE create index
- DONE create view
- DONE create trigger
- DONE constraint names including references
- DONE drop [all the above]
- DONE alter table add column

These names leak into the algebra... these areas have to be enlightened.

DONE
- allow `x y` in dot expressions e.g. `x y`.a, `x y`.`a b`
- auto-rename the exotic names into something that C can support where they appear in structs
- when producing output for sqlite use [x y].[a b] etc.
- support this in * and T.* expansion
- support declare C cursor LIKE `abc def` and other shape references
- support general names in expansions such as FROM C
- support general names in shape narrowing e.g.LIKE C(-`x y`)
- support field names like `x y` in cursors, so C.`x y` has to work
- enlighted gen_sql to the presence of this new kind of identifier and format it appropriately
- because of proc foo(LIKE table_name) locals can have exotic names
- since locals have to be supported then extend LET and SET so that they work on exotic names too
- support decoding the names in JSON output
- support query plan generating queries that use these in the table output
- support adding and removing exotic tables and columns in schema upgrader
- support these names in backed columns and backing columns
- support quoted names in arg bundles X like `foo bar` where `foo bar` might have `a column`
- support autotest creating such tables as needed
- support typed columns with exotic names (a column integer, another column real)
- support quoted id in misc attributes
- support quoted names in generated exports output
- support cursor diff rewrite (turns out it's best to use the C field name)
- support cursor format rewrite (turns out it's best to use the C field name)
- support dynamic cursors (give the cursor call the nice name in the name argument) (turns out it's best to use the C field name)
- support these names in blob storage (this might be a no-op)
- support fetch cursor USING form (a no-op, this falls out once INSERT works)
- support these names in the incremental @recreate update case, column pattern matching might need to be generalized (this might be a no-op) (OSS version of CQL doesn't support rebuild so this is a no-op, this was a Meta internal only feature)
PENDING
nothing

Playing with the naked grammar

I'm working to achieve a LALR(1)/LEX to try grammars online with wasm based on https://github.com/BenHanson/gram_grep and I've got the CG-SQL grammar working, view it here https://mingodad.github.io/parsertl-playground/playground/ select Cql parser from the examples, you can edit the Grammar or the Input source and press Parse to see a parser tree.

I hope it can be a nice tool to experiment with LALR(1)/LEX grammars with instant feedback !

Chained property access not working

DECLARE PROC printf NO CHECK;

@echo c, ""
  "typedef struct {} Custom;"
  ""
  "static void custom_object_finalize(void *_Nonnull data) {"
  "  Custom *_Nonnull self = data;"
  "  free(self);"
  "}"
  ""
  "cql_object_ref _Nonnull create_event() {"
  "  Custom *_Nonnull self = calloc(1, sizeof(Custom));"
  ""
  "  return _cql_generic_object_create(self, custom_object_finalize);"
  "}"
  ""
  "cql_object_ref _Nonnull create_event_invitees() {"
  "  Custom *_Nonnull self = calloc(1, sizeof(Custom));"
  ""
  "  return _cql_generic_object_create(self, custom_object_finalize);"
  "}"
; 

declare function create_event() create object<event> not null;
declare function create_event_invitees() create object<event_invitees> not null;

proc get_object_event_invitees(event_ object<event>, out value object<event_invitees> not null)
begin
  value := create_event_invitees();
end;

proc get_from_object_event_invitees(invitees object<event_invitees>, field text not null, out value text not null)
begin
  value := field;
end;

CREATE PROC entrypoint ()
BEGIN
  let event := create_event();
  
  -- Nested object access not working:
  -- call printf("Processing: %s \n", event.invitees.firstName);
  
  -- Assigning to a temporary variable does not work either:
  -- let invitees := create_event_invitees();
  -- call printf("Processing: %s \n", invitees.firstName);

  -- Direct calls work:
  let invitees := create_event_invitees();
  call printf("Processing: %s \n", invitees.firstName);
  call printf("Processing: %s \n", create_event_invitees().firstName);
END;

JSON emits duplicates

In case of duplicate declarations like so:

declare proc foo(x int!);
declare proc foo(x int!);

There will be two copies of foo in the output. That's wrong.

using LIKE in interior places might not validate correctly

I don't want to lose this but it occurs to me that maybe this doesn't work right:

[[shared_fragment]]
proc foo()
begin
   select * from (
      with 
         my_arg LIKE foo
         select * from my_arg
   );
end;

The issue being that the LIKE form is not at the top level. When validation happens I don't think we'll find it properly to ensure that it matches. I think we'll give errors if you try to something like

(call foo() using X as my_arg)

The easiest way to fix this is to only allow the LIKE form when it appears at the top level in the canonical location, which is always sufficent anyway.

e.g.

[[shared_fragment]]
proc foo()
begin
   with
   my_arg LIKE foo
   select * from (
         select * from my_arg
   );
end;

Which is silly but it's only to make the point that the LIKE could have gone along with any WITH and that complicates everything for no value.

Anyway I need to look into this.

Query plan fails if the same table name is declared twice in the input stream

It's hopeless if the table occurs more than once with different definitions. The input code could create a table and drop the table the create a table with a different name. The query plan code does not mimic the control flow of the input at all (that would be incomputable) it just creates the composite schema. The only hope we'd have is to rename the second instance of the table.

But we can give a meaningful error and we can also admit the case where the same table is created twice with the same schema which does happen.

Real code tends not to do any of the shenanigans but demos often do.

@include is presently allowed everywhere, this is a bad idea

let a :=
@include "stuff;"

I want to limit it to:

complete statements only
only at the top level

e.g. not this

proc foo()
begin
   @include "body"
end;

pieces like this are better done with macros.

Having seen how badly #include could be abused I want to have at least some rules on the built in @include. I want it to behave a lot more like an import than a textual include even though that's what it is right now. import is the direction and that would mean we could get rid of --generate-exports entirely.

DECLARE xx TYPE INTEGER;
DECLARE xx TYPE INTEGER;

This gives an error, it shouldn't.

Missing documentation

vault_sensitive is barely mentioned other than errors
blob_storage has a blog but nothing else
backed table feature has an intro article and nothing else

undeclared result set type for child result set types marked with private

declare proc printf no check;

[[private]]
proc create_tables()
begin
  create table parent_table(
    id integer primary key,
    u text not null,
    v text not null
  );

  create table child_table(
    id integer not null references parent_table(id),
    seq integer not null,
    a integer not null,
    b integer not null,
    primary key (id, seq)
  );
end;


[[private]]
proc parent(u_ text)
begin
  select * from parent_table where u = u_;
end;

[[private]] -- this is the problem 
proc child(u_ text)
begin
  select T1.id, T2.seq, T2.a, T2.b 
  from parent_table T1
  join child_table T2 on T1.id = T2.id
  where u = u_;
end;

-- join together parent and child using 'id'
-- example x_, y_ arguments for illustration only
[[private]]
proc parent_child(u_ text)
begin
  out union call parent(u_) join call child(u_) using (id);
end;

[[private]]
proc insert_data()
begin
   insert into parent_table values
      (1, 'foo', 'goo'),
      (2, 'foo', 'stew'),
      (3, 'you', 'new'),
      (4, 'who', 'moo');

  insert into child_table values
      (1, 1, 100, 10),
      (1, 2, 200, 20),
      (2, 1, 300, 30),
      (2, 2, 400, 40),
      (3, 1, 500, 50),
      (4, 1, 600, 60),
      (4, 2, 700, 70);
end;

proc go()
begin
    call create_tables();
    call insert_data();
    declare C cursor for call parent_child('foo');
    loop fetch C
    begin
      call printf("id: %d, u:%s, v:%s\n", C.id, C.u, C.v);
      declare D cursor for C.child1;
    end;
end;

--dot doesn't escape double quotes

There is an issue with the way we generate the dot output.

Example:

strb [label = "'The dot AST output does not escape double quotes "x".'" shape=plaintext]

How to reproduce

<<'EOF' | sources/out/cql --dot --hide_builtins | dot
DECLARE PROC printf NO CHECK;

CREATE PROC entrypoint ()
BEGIN
  call printf("The dot AST output does not escape double quotes \"x\".");
END;
EOF

Line causing this issue: sources/cql.y:2377

CG-SQL-author/sources/cql.y

Line 2382 in 3a39e27

 cql_output("\n %s%lx [label = \"%s\" shape=plaintext]", node->type, id, ((struct str_ast_node*)node)->value); 


digraph parse {
    stmt_list0 [label = "stmt_list" shape=plaintext]
    stmt_list0 -> declare_proc_no_check_stmt1;
    declare_proc_no_check_stmt1 [label = "declare_proc_no_check_stmt" shape=plaintext]
    declare_proc_no_check_stmt1 -> str2;
    str2 [label = "printf" shape=plaintext]
    _1 [label = "⏚" shape=plaintext]
    declare_proc_no_check_stmt1 -> _1;
 stmt_list0 -> stmt_list3;
    stmt_list3 [label = "stmt_list" shape=plaintext]
    stmt_list3 -> create_proc_stmt4;
    create_proc_stmt4 [label = "create_proc_stmt" shape=plaintext]
    create_proc_stmt4 -> str5;
    str5 [label = "entrypoint" shape=plaintext]
 create_proc_stmt4 -> proc_params_stmts6;
    proc_params_stmts6 [label = "proc_params_stmts" shape=plaintext]
    _6 [label = "⏚" shape=plaintext]
    proc_params_stmts6 -> _6;
 proc_params_stmts6 -> stmt_list7;
    stmt_list7 [label = "stmt_list" shape=plaintext]
    stmt_list7 -> call_stmt8;
    call_stmt8 [label = "call_stmt" shape=plaintext]
    call_stmt8 -> str9;
    str9 [label = "printf" shape=plaintext]
 call_stmt8 -> arg_lista;
    arg_lista [label = "arg_list" shape=plaintext]
    arg_lista -> strb;
    strb [label = "'The dot AST output does not escape double quotes "x".'" shape=plaintext]
    _a [label = "⏚" shape=plaintext]
    arg_lista -> _a;
    _7 [label = "⏚" shape=plaintext]
    stmt_list7 -> _7;
    _3 [label = "⏚" shape=plaintext]
    stmt_list3 -> _3;
}

Error: <stdin>: syntax error in line 29 near '"'

There should be a builtin cql_throw to throw the value of my choice

As mentioned in discussion #83.

Ideally I should be able to pass an enum value into it (even if I have to cast it as an int), so I can have all my possible error conditions in an enum.