molecule

Fixing correct resolution in RPC calls when an attribute name collides with a reserved keyword of a database.

Molecule now lets you make an optional cardinality-one reference to another namespace with ?. Data is returned as an Option with a value or tuple of ref attribute value(s):

A.i(1).save.transact

// Optional card-one ref (SQL left join) - no ref values as None
A.i.B.?(B.i).query.get ==> List(
  (1, None),
)

A.i(2).B.i(3).s("b").save.transact

// Optional card-one ref (SQL left join) - values in optional tuple
A.i.B.?(B.i.s).query.get ==> List(
  (1, None),
  (2, Some((3, "b"))),
)

The query translates to the following SQL:

SELECT DISTINCT
  A.i, B.i, B.s
FROM A
  LEFT JOIN B ON A.b = B.id
WHERE
  A.i IS NOT NULL;

Nested optional refs

We can "nest" optional references, also in inserts:

A.i.B.?(B.s.i.C.?(C.s.i)).insert(List(
  (1, None),
  (2, Some(("b", 2, None))),
  (3, Some(("b", 3, Some(("c", 3))))),
)).transact

A.i.B.?(B.s.i.C.?(C.s.i)).query.get ==> List(
  (1, None),
  (2, Some(("b", 2, None))),
  (3, Some(("b", 3, Some(("c", 3)))))
)

Which translates to 2 left joins, A -> B and B -> C

SELECT DISTINCT
  A.i, B.s, B.i, C.s, C.i
FROM A
  LEFT JOIN B ON A.b = B.id
  LEFT JOIN C ON B.c = C.id
WHERE
  A.i IS NOT NULL;

Adjacent optional refs

Use "adjacent" optional ref to make multiple optional refs/left joins from the initial namespace (A). Notice how a new definition starts outside the previous optional ref parenthesis.

A.i
  .B.?(B.i.s)
  .C.?(C.s.i).insert(List(
    (1, None, None),
    (2, Some((20, "b")), None),
    (3, None, Some(("c", 300))),
    (4, Some((40, "b")), Some(("c", 400))),
  )).transact

A.i
  .B.?(B.i.s)
  .C.?(C.s.i).query.get ==> List(
    (1, None, None),
    (2, Some((20, "b")), None),
    (3, None, Some(("c", 300))),
    (4, Some((40, "b")), Some(("c", 400))),
  )

Which translates to 2 left joins, A -> B and A -> C

SELECT DISTINCT
  A.i, B.i, B.s, C.s, C.i
FROM A
  LEFT JOIN B ON A.b = B.id
  LEFT JOIN C ON A.c = C.id
WHERE
    A.i IS NOT NULL;

In Datomic optional ref data is pulled (dummy default "__none__" value used for consistent output arities)

Nested:

[:find  ?b 
        (
          pull ?id1 [
            {(:A/b :default "__none__") [
              (:B/s :default "__none__")
              (:B/i :default "__none__")
              {(:B/c :default "__none__") [
                (:C/s :default "__none__")
                (:C/i :default "__none__")
              ]}
              ]
            }
          ]
        )  
 :where [?a :A/i ?b]
        [(identity ?a) ?id1]]

Adjacent:

[:find  ?b 
        (
          pull ?id1 [
            {(:A/b :default "__none__") [
              (:B/i :default "__none__")
              (:B/s :default "__none__")
            ]}
          ]
        ) 
        (
          pull ?id2 [
            {(:A/c :default "__none__") [
              (:C/s :default "__none__")
              (:C/i :default "__none__")
            ]}
          ]
        )  
 :where [?a :A/i ?b]
        [(identity ?a) ?id1]
        [(identity ?a) ?id2]]

Molecule now fully supports Scala 3.

A bug in the sbt-molecule plugin missed to pack new Scala 3 .tasty files into the generated jar file with Molecule boilerplate code. Now that it does, everything works like a charm.

Make sure to add the latest version of sbt-molecule in your project/plugins.sbt :

addSbtPlugin("org.scalamolecule" % "sbt-molecule" % "1.8.1")

This is a big release with a lot of fundamental changes.

Letting go of MongDB

Realising that Molecule likely won't appeal to the schema-less use cases of Mongo. Besides, Mongo with it's two data models (embedded/referenced) is a nightmare to implement. Possible, but likely not worth it.

Some background examples of thoughts against Mongo:
https://www.reddit.com/r/PostgreSQL/comments/19bkn8b/doubt_regarding_postgresql_vs_mongodb/
https://www.reddit.com/r/programming/comments/15qtfvf/goodbye_mongodb/

And an older one:
https://news.ycombinator.com/item?id=6712703

Welcoming SQlite

SQlite is now implemented in Molecule.

General improvements

• Improved Map api and key/value handling.
• More powerful and clear update/upsert semantics allowing updating across relationships!
• Distinction between Seqs of all primitive types except Bytes that is saved in an Array for serialization etc.
• General focusing of expression api.
• Aggregation of collection types dropped to keep things simple and focused.
• RawQuery now return List[List[Any]] - dropping futile attempt at returning a typed result. Raw is raw.

MongoDB added with full implementation of the SPI.

When you want to interact natively with the host database you can use the Fallback API of Molecule. It consists of 3 simple methods:

• inspect - print the query/transaction String that any molecule will produce without querying/transacting.
• rawQuery - send raw query string in the host database query language and get rows back as List[List[Any]] (often typed).
• rawTransact - send raw transaction string in the host database query language to transact data. Returns TxReport.

Examples:

Person.name.age.isMember.query.inspect prints the molecule meta model elements and the query string produced:

========================================
QUERY:
AttrOneManString("Person", "name", V, Seq(), None, None, Nil, Nil, None, None, Seq(0, 5))
AttrOneManInt("Person", "age", V, Seq(), None, None, Nil, Nil, None, None, Seq(0, 6))
AttrOneManBoolean("Person", "isMember", V, Seq(), None, None, Nil, Nil, None, None, Seq(0, 10))

SELECT DISTINCT
  Person.name,
  Person.age,
  Person.isMember
FROM Person
WHERE
  Person.name     IS NOT NULL AND
  Person.age      IS NOT NULL AND
  Person.isMember IS NOT NULL;
----------------------------------------

An insert molecule like Person.name.age.insert(("Michael", 35), ("Eva", 13)).inspect prints

========================================
INSERT:
AttrOneManString("Person", "name", V, Seq(), None, None, Nil, Nil, None, None, Seq(0, 5))
AttrOneManInt("Person", "age", V, Seq(), None, None, Nil, Nil, None, None, Seq(0, 6))

INSERT INTO Person (
  name,
  age
) VALUES (?, ?)

(Michael,35)
(Eva,13)
----------------------------------------

And similar with save, update and delete...

To avoid copying a molecule to inspect it you can also add the i flag after the action so that you both inspect and execute:

Person.name.age.isMember.query.i.get
Person.name("Lis").age(38).isMember(false).save.i.transact
Person(lisId).age(39).update.i.transact
etc...

Inspecting molecules can often be a convenient starting point to build a specialised native query or transaction.

Make a native query with rawQuery

rawQuery("select name, age, isMember from Person") ==> List(
  List("Bob", 42, true)
)

Transact data natively with rawTransact

val txReport = rawTransact(
  "insert into Person(name, age, isMember) values ('Liz', 38, false)"
)

Hopefully this API will help for the corner cases where you need to access the host database with a specialised query or transaction.

See tests in the fallback folder of each database test suite for more examples.

The following java.time._ types are now transparently mapped to all databases:

• Duration
• Instant
• LocalDate
• LocalTime
• LocalDateTime
• OffsetTime
• OffsetDateTime
• ZonedDateTime

SPI implemented for MariaDB

SPI implemented for Mysql

Molecule SPI fully implemented for PostgreSQL.

Molecule is now fully implemented for the H2 database on both the JVM and JS platforms!

Composites and TxMetaData has been dropped - see comments here. This makes compilation much faster and simplifies the api to core functionality that will satisfy the majority of all query and transaction needs of most projects.

This is a complete re-coded library based on years of active development of the molecule-old library that only targeted the Datomic database.

This new library targets both Datomic and JDBC-compliant sql databases, and more are to be added. The Datomic implementation is quite complete while the JDBC implementation is still work-in-progress.