Any documentation on the parity between sqlite and psql

[min-mwei]

I just started learning logica. I played one of the examples circulated in this forum. I noticed that the sqlite engine can compile while the Postgres engine generates a type error. I don't know if there is a feature gap or this script needs to be coded differently. Thanks.

==data.json==
[{"item": "apple", "price": 5, "amount": 2},
 {"item": "pear", "price": 2, "amount": 1},
{"item": "orange", "price": 3, "amount": 4}]
===sample.l==
@Engine("psql");
@Ground(Data);
Fruit() = ReadJson("data.json");
FruitInfo(item:, price:, amount:,
          revenue: price * amount) :-
  {item:, price:, amount:} in Fruit();
FruitInfoObj() List= {item:, price:, amount:, revenue:} :-
  FruitInfo(item:, price:, amount:, revenue:);

==python logica.py sample.l print FruitInfoObj==
Error
Compiling:
FruitInfoObj() List= {item:, price:, amount:, revenue:} :-
  FruitInfo(item:, price:, amount:, revenue:)

[ Error ] Record needs type in PostgreSQL: {item:, price:, amount:, revenue:} was inferred only an incomplete type {amount: Num, item: Any, price: Num, revenue: Num}.

[EvgSkv]

Hello @min-mwei , we are designing a better documentation, for now Tutorial is the best start.

But it doesn't go over all the engines. So please don't hesitate to ask questions in this forum, I'll do my best to answer them promptly.

As for JSON: JSON is native to SQLite, all complex types are implemented on JSON when SQLite is the engine. So it's very natural to read it. Postgres is more statically typed, so it's a little more work to operate with JSON. Usually easiest practice is to convert it to native psql types.

PostgreSQL needs to know types of objects and Logica can often infer those, but sometimes it can't, in that case use type hints, using ~ operator. Here is example of how to read the fruits data in psql.

FruitText() = txt :-
  txt = ReadFile("/content/data.json"),
  txt ~ Str;

FruitStrings() = strings :-
  strings = SqlExpr(
    "array(select jsonb_array_elements_text({txt}::jsonb))",
    {txt: FruitText()}),
  strings ~ [Str];

JsonField(record, field_name) = SqlExpr("{record}::json ->> {field_name}",
                                        {record:, field_name:});

Fruit(item: JsonField(line, "item"),
      price: ToInt64(JsonField(line, "price")),
      amount: ToInt64(JsonField(line, "price"))) :-
  line in FruitStrings();

FruitsInfo(fruits? List= item,
           total_cost? += price * amount) distinct :-
  Fruit(item:, price:, amount:);

CoLab: LINK

Note that there is no ReadJSON function when running on psql, we need to use ReadFile and convert to json manually, as in the example.

Thanks for trying out Logica, please reach out if you have any questions or comments! Evgeny.