
Cosyan – Transactional RDBMS with multi-table constraint logic - gsvigruha
https://github.com/gsvigruha/cosyan
======
trurl
It isn't really clear to me how this differs from usual database integrity
constraints? However, I'm not familiar with what typical SQL databases
provide.

The database I work on has had sophisticated integrity declarative integrity
constraints for a decade now, so it seems surprising that this would be
considered something new.

~~~
gsvigruha
Interesting, which DB is that? The ones i checked only have constraints on one
table at a time (besides foreign keys).

The idea here is that constraints could span across multiple tables via chains
of foreign keys. It might not be a new idea but i haven't seen any
implementations yet (unless you count triggers).

~~~
trurl
We've had that for at least a decade in the LogicBlox database (which is based
upon Datalog and not SQL). So you could say write

transfer_table(id, user, src, dest, amnt), amnt > 10000.0 -> auth_table(id,
man), managed_by_table(user, man).

If you wanted to express that for a transfer involving more than 10,000
dollars that it must have been approved by the user's manager. Basically any
logical formula can be used as a constraint. It's also possible to express
basic temporal constraints like

+user_pass_table(user, pswd1), user_pass_table@prev(user, pswd2) -> pswd1 !=
pswd2.

So that says if the user's password has changed, and the user had a password
in the previous transaction, they cannot be the same password.

~~~
gsvigruha
Thanks! I will check Datalog out, i have never used it (though i know Prolog a
bit).

So the main difference is then that my version is SQL based: alter table
transactions add constraint c_amount check (amount > 10000 -> auth.name =
user.man.name) # or something similar, where you refer to other tables via a
chain of foreign keys.

Another extra feature is that you can use aggregators in constraints in case
of a one to many relationship:
[https://github.com/gsvigruha/cosyan/blob/master/src/main/res...](https://github.com/gsvigruha/cosyan/blob/master/src/main/resources/doc/rules/32_reverse_foreign_keys.md)

~~~
trurl
I'm not sure why you need to restrict constraints between tables to be based
on foreign keys. I guess maybe that is a limitation inherent to SQL?

We also allow constraints over aggregations. Though our syntax does require
giving the result of the aggregation a temporary "table" name so that it can
be used as part of the constraint.

~~~
gsvigruha
I dont need to, thats a design choice i made for the moment for various
reasons, one being to stay close to SQL concepts.

It seems like your DB is basically a Datalog version of what im trying to do
in SQL.

------
danharaj
I've wanted multi table foreign keys and join indices for Postgres. What
exactly is the design here?

~~~
gsvigruha
Probably the easiest if i link some of the doc to show some examples of usage:
[https://github.com/gsvigruha/cosyan/blob/master/src/main/res...](https://github.com/gsvigruha/cosyan/blob/master/src/main/resources/doc/rules/31_foreign_keys.md)
[https://github.com/gsvigruha/cosyan/blob/master/src/main/res...](https://github.com/gsvigruha/cosyan/blob/master/src/main/resources/doc/rules/32_reverse_foreign_keys.md)

------
paulryanrogers
Interesting concept. Triggers can get close to the same effect in a
traditional DB.

~~~
snaky
That's a shame we have triggers as the only tool for this job, because e.g.
CHECK constraint "cannot contain subqueries nor refer to variables other than
columns of the current row"[1]. Triggers are very low-level thing actually,
I'd consider them an assembler of RDBMS. So the questions like "Do the
triggers and constraints outlined below actually cover all the bases, or is it
still possible to add/modify data in such a way that the result would be
inconsistent?"[2] are raised too often (usually they are _not_ raised at all,
and that's even worse). And the obvious answer[3] is application-level
consistency checking - which is exactly me personally consider a shame for
current state of RDBMS, especially in the light of Codd' ideas (referenced
above), considering the more than half of century history of RDBMS industry.

[1] [https://www.postgresql.org/docs/devel/static/sql-
createtable...](https://www.postgresql.org/docs/devel/static/sql-
createtable.html)

[2] [https://stackoverflow.com/questions/27191677/how-to-
maintain...](https://stackoverflow.com/questions/27191677/how-to-maintain-
cross-table-consistency-with-triggers-in-postgresql)

[3] [https://www.postgresql.org/message-
id/m3iq57gcn9.fsf@passepa...](https://www.postgresql.org/message-
id/m3iq57gcn9.fsf@passepartout.tim-landscheidt.de)

------
alecbenzer
It's not clear to me why this would get you faster development cycles or
better performance than application-level checks? And I'm not really sure what
"dependency tracking" is supposed to mean?

~~~
gsvigruha
Yes you're totally right i need to write the readme properly. You get faster
development cycle by skipping the app layer altogether. It's a few sql
statements vs java code/release/deploy. Performance: you don't need multiple
queries for a constraint plus you can optimize from info within the DB.
Dependency tracking: no need to figure out yourself which table/operation can
break which constraint.

