On 21.9.2016 0:08, Jiri Jetmar wrote:
Independently of that that, things starts to be complicated in the SQL
world with large data when you are submitting e.g. a inner JOIN statement
in a transactional INSERT expression, where the tables are located  (the
data) on different nodes, simply because of a single node limit.

Well... there are ways to circumvent this, too. :)
Unfortunately, can't tell much more about that, as it is key concept of the company where I work right now. But very large and globally distributed RDBMS nodes, which don't lag under high stress, are very much so possible!

I would like to work on this task. Therefore smart ideas are highly welcome
! :-)

I am responsible for current SQL indexing code, and Paul is responsible for current SQL entitystore code. The indexing code is, unfortunately, quite spaghettified, and I would do lots of things probably very differently now, than how I did them several years ago.

I think one strategy to approach would be to re-write whole thing from scratch, but keeping the main principles: 1. Each property and association should be stored in separate table. This is required for the very dynamic nature of Zest queries. This implicates that the whole DB schema will be owned by this indexing service - it is not compatible with some external tool generating tables (unless tables are, of course, in this exact required format).

Example: if you have interface MyEntity { Property<String> myProp(); Association<SomeOtherEntity> myAsso(); },
    That would end up with 3 tables:
1.1. The table for implicit 'identity' property. Current indexing code has separate DB-specific entity primary key which is of type 'long' (64bit integer), since that makes foreign key references much faster and compacter. Not sure if that is good idea anymore. 1.2. The table for 'myProp' property with 2 columns: foreign key reference to identity table and actual column of type 'text' containing the contents of 'myProp' property. 1.3. The table for 'myAsso' association with 2 columns: foreign key reference to identity table of 'MyEntity' and foreign key reference to identity table of 'SomeOtherEntity'. Obviously this covers the 'ManyAssociation's as well, if they are still present in Zest.

2. The SQL query will then be created by translating Zest query objects into SQL query objects. You could also just build SQL strings, but it would be quite nasty and most likely very unmaintaineable task - current code uses java-sql-generator, which is my library on github, to create SQL statements using DSL, and not by concatenating strings. IIRC Zest AND-conditions are SQL INTERSECTIONs of single property/asso queries, Zest OR-conditions are SQL UNIONs of single property/asso queries, and Zest NOT-conditions are SQL NOTs of single property/asso queries.

3. Keeping DB schema in sync with Zest application schema.
This is very tough one - if someone adds/removes properties/associations, the database schema should be kept up-to-date. I remember having problems with this one - it also implies that you have to walk through the *whole* application structure in order to generate tables for all the properties/assos of all the entities visible to this indexing service.

Reply via email to