This is the initial version of the VS Code highlighting extension. Here I try to structure the TextMate grammar file in the BNF way, but I've made simplifications here and there to produce a basic version for a start. Instructions on quick running, development and installation can be found in README.md.

👋 Hello! I'm having great fun playing around with Logica. Thanks for the project.

I run into something a bit strange and was wondering if it is a bug.

I have a fact and rule like so:

Tuple(namespace: "document", id: "1", rel: "writer", user: "id", userId: "badger", user_namespace: "", user_object_id: "");

List(namespace:, id:, rel:, user:, userId:, user_namespace:, user_object_id:) :-
    Tuple(namespace:, id:, rel:, user:, userId:, user_namespace:, user_object_id:);

and if I try to run this as is, it returns a parsing error:

Parsing:
List(namespace:, id:, rel:, user:, userId:, user_namespace:, user_object_id:) :-
    Tuple(namespace:, id:, rel:, user:, userId:, user_namespace:, user_object_id:)

[ Error ] Could not parse expression of a value.

If I parametrize userId (with an x) as in the following:

Tuple(namespace: "document", id: "1", rel: "writer", user: "id", userId: "badger", user_namespace: "", user_object_id: "");

List(namespace:, id:, rel:, user:, userId: x, user_namespace:, user_object_id:) :-
    Tuple(namespace:, id:, rel:, user:, userId: x, user_namespace:, user_object_id:);

then it works fine:

(env) $ logica zanzibar.l run List
+-----------+----+--------+--------+----------+----------------+----------------+
| namespace | id | rel    | user   | userId   | user_namespace | user_object_id |
+-----------+----+--------+--------+----------+----------------+----------------+
| document  | 1  | writer | id     | badger   |                |                |
+-----------+----+--------+--------+----------+----------------+----------------+

Similarly, if I don't parametrize, but use snake case user_id then it also works fine.

So, should I always just stick with snake_case?

Thanks for any insights 🙌

In bigquery and firebase data.

SELECT user_pseudo_id,
 event_timestamp ,
CASE event_name
  WHEN 'screen_view' THEN 
         event_name || (SELECT param.value.string_value FROM UNNEST(event_params) AS param WHERE 
        param.key="firebase_screen_class") 
else event_name
END as name
 FROM `bq.x.x` 

order by event_timestamp

I can't find example for document In Logica tutorial.ipynb .

Can we support raw sql in logica? So we can write complicate sql in logica.

Hi, I'm interested in using DuckDB(https://duckdb.org/) as a data source for Logica and I was wondering what would be the steps to integrate it. It's like SQLite for analytics and is a subset of the PostgreSQL syntax.

We could use sqlalchemy but 1) I don't know how to proceed after and 2) I think the ORM design of sqlalchemy may make it slow if we are returning a lot of data(just an hypothesis based on https://github.com/duckdb/duckdb/issues/305)

So the question is two fold.

1. If we use the sqlalchemy interface, the setup code would be something like this.

# Install Logica.
!pip install logica
!pip install duckdb
!pip install duckdb-engine



# Connect to the database.
from logica import colab_logica
from sqlalchemy import create_engine

engine = create_engine('duckdb:///:memory:');
connection = engine.connect();
colab_logica.SetDbConnection(connection)

but how do I use it after that?

2. If I don't want to use the sqlachemy interface, how would I go about doing that? The general usage is:

import duckdb
con = duckdb.connect(database=':memory:', read_only=False) #can also use a file like sqlite
# create a table
con.execute("CREATE TABLE items(item VARCHAR, value DECIMAL(10,2), count INTEGER)")
# insert two items into the table
con.execute("INSERT INTO items VALUES ('jeans', 20.0, 1), ('hammer', 42.2, 2)")

# retrieve the items again
con.execute("SELECT * FROM items")
print(con.fetchall())
# [('jeans', 20.0, 1), ('hammer', 42.2, 2)]

In version 1.3.14, the import resolves relative to the current directory. We are trying to deploy Logica files embedded with our Python package, so they can be anywhere on sys.path. Would it be possible to change the parser so that a list of directories (like sys.path) could be provided for resolving import?

Whoever wrote the article here does not seem to understand databases much. https://opensource.googleblog.com/2021/04/logica-organizing-your-data-queries.html

SELECT 2 AS x

UNION ALL

SELECT 3 AS x

UNION ALL

SELECT 5 AS x;

Would never happen. You'd have a table of ints.

Version 1.3.14.15.92

Imports in an imported file cannot be found if they are relative to a path in import_root. They can only be found if they are relative to the directory Logica is initially called from.

To reproduce using integration_tests/import_root_test.l change the imports in integration_tests/import_tests/canada.l to be relative to the import_root used for the test:

import usa.ProductUSA;
import usa.SoftwareEngineer;

The test fails to find usa.l event though usa.l is on the import_root given to the test.

I've now fixed some problems left in the last PR and the highlighting is now error-free in terms of user experience (at least from my side). (I still didn't strictly follow the BNF in constructing the syntax because I realized it's not necessary for now as long as the highlighting gets its job done.) I haven't packaged and published this new version yet in case I need to make further modifications. If you notice anything wrong with the highlighting, please tell me. Thanks!

Since BigQuery's TIMESTAMP_SUB() has two arguments, I assume that TimestampSub() also has two arguments. However, the following predicate will result in an error.

A(t: TimestampSub(CurrentDate("Asia/Tokyo"), SqlExpr("INTERVAL 9 HOUR",{})));

Compiling:
A(t: TimestampSub(CurrentDate("Asia/Tokyo"), SqlExpr("INTERVAL 9 HOUR",{})))

[ Error ] Built-in function TimestampSub takes (3, 3) arguments, but 2 arguments were given.

hi, I have a question, does it work in big guary and will it work in the future? I have not figured it out yet to test it, but if it really works in the google cloud console, I will definitely start testing it in the near future

I'm trying to generate a set ordered by a another value, but I'm really struggling to pull it off. I'm not sure how to work around BigQuery's limitations, after trying quite a few attempts. Without doing some clever work around for BigQuery not supporting ARRAY_AGG(DISTINCT ... ORDER BY ...), the only other way I see is JOINing another select after the GROUP BY is preformed, but I'm also struggling to figure out how to pull that off in Logica as well. Anyways, love the project! Overall it's been really cool! 🚀

Code

Here's the most straight forward attempt

%%logica Test

@Engine("bigquery");

OrderedSet(a) = SqlExpr("ARRAY_AGG(DISTINCT {value} ORDER BY {arg})", {value: a.value, arg: a.arg});

TestOrderedSet(g:, ids_by_t? Array= t -> id) distinct :-
  Data(g:, id:, t:);

Data(g: 1, id: 1, t: 5);
Data(g: 1, id: 2, t: 4);
Data(g: 1, id: 2, t: 4);
Data(g: 1, id: 3, t: 3);
Data(g: 1, id: 3, t: 3);
Data(g: 1, id: 4, t: 2);
Data(g: 1, id: 5, t: 1);

Data(g: 2, id: 10, t: 1);
Data(g: 2, id: 10, t: 1);
Data(g: 2, id: 20, t: 2);
Data(g: 2, id: 20, t: 0);
Data(g: 2, id: 30, t: 3);

Expected(g: 1, ids_by_t: [5, 4, 3, 2, 1]);
Expected(g: 2, ids_by_t: [20, 10, 30]);
Expected(g: 3, ids_by_t: [20, 10, 30]);

Test(g:, actual:, expected:) :-
  TestOrderedSet(g:, ids_by_t: actual),
  Expected(g:, ids_by_t: expected);

Output

And the error 😢

BadRequest: 400 An aggregate function that has both DISTINCT and ORDER BY arguments can only ORDER BY expressions that are arguments to the function at [77:39]

                   -----Query Job SQL Follows-----                   

    |    .    |    .    |    .    |    .    |    .    |    .    |
   1:WITH t_2_Data AS (SELECT * FROM (
   2:  
   3:    SELECT
   4:      1 AS g,
   5:      1 AS id,
   6:      5 AS t
   7:   UNION ALL
   8:  
   9:    SELECT
  10:      1 AS g,
  11:      2 AS id,
  12:      4 AS t
  13:   UNION ALL
  14:  
  15:    SELECT
  16:      1 AS g,
  17:      2 AS id,
  18:      4 AS t
  19:   UNION ALL
  20:  
  21:    SELECT
  22:      1 AS g,
  23:      3 AS id,
  24:      3 AS t
  25:   UNION ALL
  26:  
  27:    SELECT
  28:      1 AS g,
  29:      3 AS id,
  30:      3 AS t
  31:   UNION ALL
  32:  
  33:    SELECT
  34:      1 AS g,
  35:      4 AS id,
  36:      2 AS t
  37:   UNION ALL
  38:  
  39:    SELECT
  40:      1 AS g,
  41:      5 AS id,
  42:      1 AS t
  43:   UNION ALL
  44:  
  45:    SELECT
  46:      2 AS g,
  47:      10 AS id,
  48:      1 AS t
  49:   UNION ALL
  50:  
  51:    SELECT
  52:      2 AS g,
  53:      10 AS id,
  54:      1 AS t
  55:   UNION ALL
  56:  
  57:    SELECT
  58:      2 AS g,
  59:      20 AS id,
  60:      2 AS t
  61:   UNION ALL
  62:  
  63:    SELECT
  64:      2 AS g,
  65:      20 AS id,
  66:      0 AS t
  67:   UNION ALL
  68:  
  69:    SELECT
  70:      2 AS g,
  71:      30 AS id,
  72:      3 AS t
  73:  
  74:) AS UNUSED_TABLE_NAME  ),
  75:t_0_TestOrderedSet AS (SELECT
  76:  Data.g AS g,
  77:  ARRAY_AGG(DISTINCT Data.id ORDER BY Data.t) AS ids_by_t
  78:FROM
  79:  t_2_Data AS Data
  80:GROUP BY g),
  81:t_3_Expected AS (SELECT * FROM (
  82:  
  83:    SELECT
  84:      1 AS g,
  85:      ARRAY[5, 4, 3, 2, 1] AS ids_by_t
  86:   UNION ALL
  87:  
  88:    SELECT
  89:      2 AS g,
  90:      ARRAY[20, 10, 30] AS ids_by_t
  91:   UNION ALL
  92:  
  93:    SELECT
  94:      3 AS g,
  95:      ARRAY[20, 10, 30] AS ids_by_t
  96:  
  97:) AS UNUSED_TABLE_NAME  )
  98:SELECT
  99:  TestOrderedSet.g AS g,
 100:  TestOrderedSet.ids_by_t AS actual,
 101:  Expected.ids_by_t AS expected
 102:FROM
 103:  t_0_TestOrderedSet AS TestOrderedSet, t_3_Expected AS Expected
 104:WHERE
 105:  (Expected.g = TestOrderedSet.g)
    |    .    |    .    |    .    |    .    |    .    |    .    |

Generate SQL

And the generated SQL for the lazy

WITH t_2_Data AS (SELECT * FROM (
  
    SELECT
      1 AS g,
      1 AS id,
      5 AS t
   UNION ALL
  
    SELECT
      1 AS g,
      2 AS id,
      4 AS t
   UNION ALL
  
    SELECT
      1 AS g,
      2 AS id,
      4 AS t
   UNION ALL
  
    SELECT
      1 AS g,
      3 AS id,
      3 AS t
   UNION ALL
  
    SELECT
      1 AS g,
      3 AS id,
      3 AS t
   UNION ALL
  
    SELECT
      1 AS g,
      4 AS id,
      2 AS t
   UNION ALL
  
    SELECT
      1 AS g,
      5 AS id,
      1 AS t
   UNION ALL
  
    SELECT
      2 AS g,
      10 AS id,
      1 AS t
   UNION ALL
  
    SELECT
      2 AS g,
      10 AS id,
      1 AS t
   UNION ALL
  
    SELECT
      2 AS g,
      20 AS id,
      2 AS t
   UNION ALL
  
    SELECT
      2 AS g,
      20 AS id,
      0 AS t
   UNION ALL
  
    SELECT
      2 AS g,
      30 AS id,
      3 AS t
  
) AS UNUSED_TABLE_NAME  ),
t_0_TestOrderedSet AS (SELECT
  Data.g AS g,
  ARRAY_AGG(DISTINCT Data.id ORDER BY Data.t) AS ids_by_t
FROM
  t_2_Data AS Data
GROUP BY g),
t_3_Expected AS (SELECT * FROM (
  
    SELECT
      1 AS g,
      ARRAY[5, 4, 3, 2, 1] AS ids_by_t
   UNION ALL
  
    SELECT
      2 AS g,
      ARRAY[20, 10, 30] AS ids_by_t
   UNION ALL
  
    SELECT
      3 AS g,
      ARRAY[20, 10, 30] AS ids_by_t
  
) AS UNUSED_TABLE_NAME  )
SELECT
  TestOrderedSet.g AS g,
  TestOrderedSet.ids_by_t AS actual,
  Expected.ids_by_t AS expected
FROM
  t_0_TestOrderedSet AS TestOrderedSet, t_3_Expected AS Expected
WHERE
  (Expected.g = TestOrderedSet.g);

Code:

%%logica Engineers
@Engine("sqlite");
Employee(name: "Alice", role: "Product Manager");
Employee(name: "Bob", role: "Engineer");
Employee(name: "Caroline", role: "Engineer");
Employee(name: "David", role: "Data Scientist");
Employee(name: "Eve", role: "Data Scientist");
Engineers(..r) :- Employee(..r), r.role == "Engineer";

Error:

OperationalError: no such column: Employee

I know that at this stage meaningful error messages may be a tall order but if it could perhaps print out the SQL statement it should be fairly easy to see what went wrong.

Also, sincerely thank you for this project: it is inspired.

It will take time to implement type inference. In the meanwhile we could have a simpler check: when a predicate is called (including databases) and arguments are used, verify that the predicate has corresponding columns.

We would like to infer types of arguments of the predicates, including reading types of database columns. Then we can display a native error for the user, rather then compiling an SQL and having user debug SQL error.

hi, do logica have subset function? I want to compare one set to another, and return a boolean value showing whether the two sets are equal or subset? eg: SUBSET({ }, { }) = TRUE SUBSET({ 1, 2, 3 }, { }) = TRUE SUBSET({ 1, 2 }, { 1, 2 }) = TRUE SUBSET({ 1, 2, 3 }, { 1, 2 }) = TRUE SUBSET({ 1, 3, 5 }, { 1, 2 }) = FALSE many thanks!