Well, sort of; ENTITIES table has a "_value_id", that is primary key in each Mixin table and the associated ASSOCS table. Then the ASSOCS table's "_reference" column contains the Many/Named Associated EntityReference.
Hope that explains it well enough. On Mon, Jun 12, 2017 at 9:34 PM, Kent Sølvsten <[email protected]> wrote: > Hi Niclas. > > Looks good. > > How do you handle the keys in named associations? > > If an entry in the ASSOCS_ table is pointing at a specific entity, does > that mean, that for each relation there will be 2 entries in the table, one > for each side? > > /Kent > > On Sun, Jun 11, 2017 at 5:41 AM, Niclas Hedhman <[email protected]> > wrote: > > > Gang, > > > > This is just a heads-up and request for feedback.... I am very close to > > finish the first pass at the JOOQ Entity Store, which uses JOOQ to handle > > SQL details across SQL dialects. > > > > The intent is that an "enterprisey" developer would go, "Yeah, that could > > work..." rather than the current "Use SQL as Key/Value store" approach > that > > we did initially. > > > > The structure is as follows (JOOQ generated queries); > > > > The TYPES table keeps mapping between Mixin types and table names. In > > general, the Class.getSimpleName() is used as table name, but if there > is a > > conflict, then a "_1", "_2" and so on is added. > > > > create table if not exists "POLYGENE"."TYPES"( > > "_identity" varchar null, > > "_table_name" varchar null, > > "_created_at" timestamp null, > > "_modified_at" timestamp null > > ) > > > > The ENTITIES table is containing the meta data about the entities. This > is > > effectively the built-in data in EntityState. The "_value_id" field is > the > > foreign key into the generated mixin tables. This is done this way to > > support the "Identity+Value" view of entities that we may have later, and > > that history can be preserved (but not fully implemented yet), and the > > "_deleted_at" field is there for this same reason, and also not used > > initially. > > > > create table if not exists "POLYGENE"."ENTITIES"( > > "_identity" varchar null, > > "_app_version" varchar null, > > "_value_id" varchar null, > > "_version" varchar null, > > "_type" varchar null, > > "_modified_at" timestamp null, > > "_created_at" timestamp null, > > "_deleted_at" timestamp null > > ) > > > > Then for each Mixin type, two tables are created. The first one is to > store > > Properties and Associations. The second is to store the ManyAssociations > > and NamedAssociations. I chose a single table, to reduce the amount of > > tables that needs to be managed, but perhaps it should be one table per > > many-to-many relation in the model. > > > > The primary table for the ES testcase (TestEntity) looks like; > > > > create table "POLYGENE"."TestEntity"( > > "_identity" varchar null, > > "_created_at" timestamp null, > > "instantValue" timestamp with time zone null, > > "bigIntegerValue" varchar null, > > "bigDecimalValue" varchar null, > > "dateTimeValue" timestamp with time zone null, > > "localDateTimeValue" timestamp null, > > "localDateValue" date null, > > "localTimeValue" time null, > > "duractionValue" varchar null, > > "periodValue" varchar null, > > "valueProperty" varchar null, > > "unsetName" varchar null, > > "emptyName" varchar null, > > "name" varchar null, > > "booleanValue" boolean null, > > "intValue" integer null, > > "longValue" bigint null, > > "floatValue" float null, > > "doubleValue" double null, > > "association" varchar null, > > "unsetAssociation" varchar null > > ) > > > > and the assocs table; > > > > create table if not exists "POLYGENE"."TestEntity_ASSOCS"( > > "_identity" varchar null, > > "_name" varchar null, > > "_index" varchar null, > > "_reference" varchar null > > ) > > > > The "_name" is the name of the association in the mixin, say "children" > for > > > > ManyAssociation<Person> children(); > > > > and the "_index" is the position inside the ManyAssociation or the > key/name > > of NamedAssociations. > > > > For both of these tables, the "_identity" is the "_value_id" in the > > ENTITIES table above. > > > > > > Composite get() results in 2 queries, one for properties/assocs and one > for > > the _ASSOCS tables. > > > > select * > > from "POLYGENE"."ENTITIES" > > left outer join "POLYGENE"."TestEntity" on "_value_id" = > > "TestEntity"."_identity" > > where "ENTITIES"."_identity" = 'ca22d692-5461-4756-8316- > 5a214d055aa5' > > > > select * > > from "POLYGENE"."ENTITIES" > > join "POLYGENE"."TestEntity_ASSOCS" on "_value_id" = > > "TestEntity_ASSOCS"."_identity" > > where "ENTITIES"."_identity" = 'ca22d692-5461-4756-8316- > 5a214d055aa5' > > > > In each of those queries, there is a "join" line per Mixin type, so > highly > > composed > > > > The idea is that ValueComposites are still going to be JSON serialized > into > > fields and leave it in the hands of developers to decide whether to go > with > > more entities or use values. This serialization is not yet implemented > and > > the main part that is not working yet. > > Other things still outstanding; > > * Primary Key constraints > > * Caching of TYPES, to reduce queries > > * Caching of queries (there is only 2 queries per primaryType) > > * Design improvements, not totally happy with the implementation. > > * History support > > * ALTER TABLE and Migration (big one) > > * Allow more JOOQ config to be specified > > > > Interestingly enough, the current design (without history) preserves > state > > of sub-types, which I think is no longer supported in other ES > > implementations. This is just a side-effect of how the storage is > > structured. > > > > So, this is on the brink of operational (6 of 8 tests in > > AbstractEntityStoreTest passes), and before digging into the > serialization > > of Values, I would like to check with you guys if this sounds reasonable, > > and if there are any ways to make this even more tempting. > > > > I am for instance seeking to change the "entitystore-sql" to another name > > (for instance sqlkv) or even remove it completely, and this store take > the > > "entitystore-sql" name. Otherwise, I think too many people will discard > > Polygene after seeing "SQL ES" key/value storage pattern. > > > > Personally, I think that this is fairly clean and something I think > > SQL-happy people would consider an alternative to Hibernate. > > > > > > I am committing/pushing this to branch "es-jooq" branch shortly, if you > > want to take a closer look and feel free to help on implementation. > > > > > > P.S. I am also keen on improving/expanding the ES test suite. It is > > currently a bit rudimentary, and I think we should try to improve that. > > > > > > Cheers > > -- > > Niclas Hedhman, Software Developer > > http://polygene.apache.org - New Energy for Java > > > -- Niclas Hedhman, Software Developer http://polygene.apache.org - New Energy for Java
