Adding more detailed documentation of SQL EntityStore
Project: http://git-wip-us.apache.org/repos/asf/polygene-java/repo Commit: http://git-wip-us.apache.org/repos/asf/polygene-java/commit/0e399e07 Tree: http://git-wip-us.apache.org/repos/asf/polygene-java/tree/0e399e07 Diff: http://git-wip-us.apache.org/repos/asf/polygene-java/diff/0e399e07 Branch: refs/heads/develop Commit: 0e399e071511b3310f7fd925f2502d4d4c1a8130 Parents: 3705ce9 Author: niclas <[email protected]> Authored: Sun Oct 29 00:00:17 2017 +0800 Committer: niclas <[email protected]> Committed: Sun Oct 29 00:00:17 2017 +0800 ---------------------------------------------------------------------- extensions/entitystore-sql/dev-status.xml | 2 +- extensions/entitystore-sql/src/docs/es-sql.txt | 199 ++++++++++++++++++- .../polygene/entitystore/sql/SqlTable.java | 6 +- .../polygene/entitystore/sql/DocSupport.java | 55 +++++ manual/src/docs/userguide/extensions.txt | 2 +- 5 files changed, 257 insertions(+), 7 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/polygene-java/blob/0e399e07/extensions/entitystore-sql/dev-status.xml ---------------------------------------------------------------------- diff --git a/extensions/entitystore-sql/dev-status.xml b/extensions/entitystore-sql/dev-status.xml index c79727f..273df00 100644 --- a/extensions/entitystore-sql/dev-status.xml +++ b/extensions/entitystore-sql/dev-status.xml @@ -27,7 +27,7 @@ <codebase>beta</codebase> <!-- none, brief, good, complete --> - <documentation>brief</documentation> + <documentation>good</documentation> <!-- none, some, good, complete --> <unittests>good</unittests> http://git-wip-us.apache.org/repos/asf/polygene-java/blob/0e399e07/extensions/entitystore-sql/src/docs/es-sql.txt ---------------------------------------------------------------------- diff --git a/extensions/entitystore-sql/src/docs/es-sql.txt b/extensions/entitystore-sql/src/docs/es-sql.txt index bab38ef..ad3c733 100644 --- a/extensions/entitystore-sql/src/docs/es-sql.txt +++ b/extensions/entitystore-sql/src/docs/es-sql.txt @@ -18,7 +18,7 @@ /////////////////////////////////////////////////////////////// [[extension-es-sql,SQL EntityStore]] -= ORM EntityStore = += SQL EntityStore = [devstatus] -------------- @@ -49,7 +49,7 @@ Here are the available configuration properties: [snippet,java] ---- -source=extensions/entitystore-jooq/src/main/java/org/apache/polygene/entitystore/jooq/JooqEntityStoreConfiguration.java +source=extensions/entitystore-sql/src/main/java/org/apache/polygene/entitystore/sql/SqlEntityStoreConfiguration.java tag=config ---- @@ -59,3 +59,198 @@ As soon as you provide a `username`, authentication is set up. It is also strongly recommended that a connection pool is setup. <<library-sql-dbcp>> and <<library-bonecp>> are available in Polygene, but you can create your own. +== Table Layout == +The SQL Entity Store will create, if createIfMissing() is true, or expect that the following tables exist. + + +[width="80%",options="header", cols="4*<", title="TYPES Table", halign="left"] +|======= +|*Field* |*Type* |*Constraint* |*Description* +|_identity |VARCHAR |PRIMARY KEY |The Java classname of the Entity Type. +|_table_name |VARCHAR ||Chosen name of the Mixin table. This name is the simple name of the class name, unless that table already exists, in which case a sequence number (starting with 0) is added at the end of the name, e.g. Person_0. +|_created_at |TIMESTAMP ||The time of the creation into this table. +|_modified_at |TIMESTAMP ||The time of the last modification of this record. Should never change. +|======= + +Or in DDL (assuming default value for SqlEntityStoreConfiguration.typesTableName(); + +[source, bash] +---- +create table if not exists "TYPES"( + "_identity" varchar null, + "_table_name" varchar null, + "_created_at" timestamp null, + "_modified_at" timestamp null, + primary key ("_identity") +) +---- + + +[width="80%",options="header", cols="4*<", title="ENTITIES Table", halign="left"] +|======= +|*Field* |*Type* |*Constraint* |*Description* +|_identity |VARCHAR |PRIMARY KEY |The Identity of the entity. +|_created_at |TIMESTAMP ||The time of creation of the entity. +|_type |VARCHAR ||The identity of the type of entity. Linked to the TYPES table _identity field. +|_app_version |VARCHAR ||The Application.version() when the entity was last modified. +|_version |VARCHAR ||The MVCC version of the entity, to ensure consistency with optimistic locking. +|_modified_at |TIMESTAMP ||The time of latest change to the entity. +|_value_id |VARCHAR ||The identity of the values that are stored in the Mixin tables (their _identity field) +|======= + +[source, bash] +---- +create table if not exists "ENTITIES"( + "_identity" varchar null, + "_created_at" timestamp null, + "_type" varchar null, + "_app_version" varchar null, + "_version" varchar null, + "_modified_at" timestamp null, + "_value_id" varchar null, + primary key ("_identity") +) +---- + + + +Additionally, for each Mixin type, two tables will be created. One that contains one field per Property and +one field per Association. The second table will contain a fixed layout that holds all ManyAssociation and all +NamedAssociarion in a multi-field key-value table (see below). + +The primary Mixin table will be named as the Mixin type's simple class name, unless there is conflicts, in which +case a sequence number will be added to the table name. The TYPES table contains the mapping from Mixin type +(i.e. Java class name) to table name. + +For instance, the following types will end up creating 4 Mixin tables, plus the 4 association tables, + +[snippet,java] +---- +source=extensions/entitystore-sql/src/test/java/org/apache/polygene/entitystore/sql/DocSupport.java +tag=types +---- + +And the structures will be; + +[width="80%",options="header", cols="4*<", title="Person", halign="left"] +|======= +|*Field* |*Type* |*Constraint* |*Description* +|_identity |VARCHAR |PRIMARY KEY |The Identity of the current value, i.e. _value_id from ENTITIES table +|_created_at |TIMESTAMP ||The time of creation of the entity value. It will become possible to choose to keep old values for history/audit purposes, but not yet supported. This field is preparation for that. +|spouse |VARCHAR ||Association<Person> spouse(); +|======= + +And the corresponding DDL. + +[source, bash] +---- +create table "Person" ( + "_identity" varchar null, + "_created_at" timestamp null, + "spouse" varchar null, + primary key ("_identity") +) +---- + + +[width="80%",options="header", cols="4*<", title="HasName", halign="left"] +|======= +|*Field* |*Type* |*Constraint* |*Description* +|_identity |VARCHAR |PRIMARY KEY |The Identity of the current value, i.e. _value_id from ENTITIES table +|_created_at |TIMESTAMP ||The time of creation of the entity value. It will become possible to choose to keep old values for history/audit purposes, but not yet supported. This field is preparation for that. +|name |VARCHAR ||Property<String> name(); +|======= + +And the corresponding DDL. + +[source, bash] +---- +create table "HasName" ( + "_identity" varchar null, + "_created_at" timestamp null, + "name" varchar null, + primary key ("_identity") +) +---- + +[width="80%",options="header", cols="4*<", title="HasAddress", halign="left"] +|======= +|*Field* |*Type* |*Constraint* |*Description* +|_identity |VARCHAR |PRIMARY KEY |The Identity of the current value, i.e. _value_id from ENTITIES table +|_created_at |TIMESTAMP ||The time of creation of the entity value. It will become possible to choose to keep old values for history/audit purposes, but not yet supported. This field is preparation for that. +|street1 |VARCHAR ||Property<String> street1(); +|street2 |VARCHAR ||Property<String> street2(); +|zipCode |VARCHAR ||Property<String> zipCode(); +|country |VARCHAR ||Assocication<String> country(); which contains Identity of the Country entity referenced. +|======= + +And the corresponding DDL. + +[source, bash] +---- +create table "HasName" ( + "_identity" varchar null, + "_created_at" timestamp null, + "street1" varchar null, + "street2" varchar null, + "zipCode" varchar null, + "country" varchar null, + primary key ("_identity") +) +---- + +[width="80%",options="header", cols="4*<", title="Country", halign="left"] +|======= +|*Field* |*Type* |*Constraint* |*Description* +|_identity |VARCHAR |PRIMARY KEY |The Identity of the current value, i.e. _value_id from ENTITIES table +|_created_at |TIMESTAMP ||The time of creation of the entity value. It will become possible to choose to keep old values for history/audit purposes, but not yet supported. This field is preparation for that. +|======= + +And the corresponding DDL. + +[source, bash] +---- +create table "HasName" ( + "_identity" varchar null, + "_created_at" timestamp null, + primary key ("_identity") +) +---- + +NOTE: Country has no fields, as the HasName mixin type will be in its own table (above). But the _created_at field, +indicating latest change time, will be maintained. + + +And additionally four tables will be created, with identical structures. The names will be +Person_ASSOCS, HasName_ASSOCS, HasAddress_ASSOCS, Country_ASSOCS and the structure is; + +[width="80%",options="header", cols="4*<", title="*_ASSOCS", halign="left"] +|======= +|*Field* |*Type* |*Constraint* |*Description* +|_identity |VARCHAR ||The Identity of the current value, i.e. _value_id from ENTITIES table +|_created_at |TIMESTAMP ||The time of creation of the entity value. It will become possible to choose to keep old values for history/audit purposes, but not yet supported. This field is preparation for that. +|_name |VARCHAR ||The name of the ManyAssociation or NamedAssociation in the Mixin type. +|_index |VARCHAR ||For NamedAssociations this is the name of the association, i.e. the key. For ManyAssociation, this is the sequence number within the ordered list. +|_reference |VARCHAR ||The entity identity that the association is pointing to. +|======= + +[source, bash] +---- +create table if not exists "Person_ASSOCS"( + "_identity" varchar null, + "_created_at" timestamp null, + "_name" varchar null, + "_index" varchar null, + "_reference" varchar null +) +---- + +Additionally, one INDEX will be created for each _ASSOCS table. So the above four will have + +[source, bash] +---- +create index "IDX_Person_ASSOCS" on "Person_ASSOCS"("_identity") +create index "IDX_HasName_ASSOCS" on "HasName_ASSOCS"("_identity") +create index "IDX_HasAddress_ASSOCS" on "HasAddress_ASSOCS"("_identity") +create index "IDX_Country_ASSOCS" on "Country_ASSOCS"("_identity") +---- http://git-wip-us.apache.org/repos/asf/polygene-java/blob/0e399e07/extensions/entitystore-sql/src/main/java/org/apache/polygene/entitystore/sql/SqlTable.java ---------------------------------------------------------------------- diff --git a/extensions/entitystore-sql/src/main/java/org/apache/polygene/entitystore/sql/SqlTable.java b/extensions/entitystore-sql/src/main/java/org/apache/polygene/entitystore/sql/SqlTable.java index 1ff1135..0600d06 100644 --- a/extensions/entitystore-sql/src/main/java/org/apache/polygene/entitystore/sql/SqlTable.java +++ b/extensions/entitystore-sql/src/main/java/org/apache/polygene/entitystore/sql/SqlTable.java @@ -217,12 +217,12 @@ public interface SqlTable extends ServiceActivation dsl.createTableIfNotExists( dsl.tableNameOf( entitiesTableName ) ) .column( identityColumn ) + .column( createdColumn ) + .column( typeNameColumn ) .column( applicationVersionColumn ) - .column( valueIdentityColumn ) .column( versionColumn ) - .column( typeNameColumn ) .column( modifiedColumn ) - .column( createdColumn ) + .column( valueIdentityColumn ) .constraint( DSL.primaryKey( identityColumn ) ) .execute(); } ); http://git-wip-us.apache.org/repos/asf/polygene-java/blob/0e399e07/extensions/entitystore-sql/src/test/java/org/apache/polygene/entitystore/sql/DocSupport.java ---------------------------------------------------------------------- diff --git a/extensions/entitystore-sql/src/test/java/org/apache/polygene/entitystore/sql/DocSupport.java b/extensions/entitystore-sql/src/test/java/org/apache/polygene/entitystore/sql/DocSupport.java new file mode 100644 index 0000000..6e0cbd4 --- /dev/null +++ b/extensions/entitystore-sql/src/test/java/org/apache/polygene/entitystore/sql/DocSupport.java @@ -0,0 +1,55 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + * + * + */ +package org.apache.polygene.entitystore.sql; + +import org.apache.polygene.api.association.Association; +import org.apache.polygene.api.association.ManyAssociation; +import org.apache.polygene.api.common.Optional; +import org.apache.polygene.api.property.Property; + +public class DocSupport +{ + // START SNIPPET: types + public interface Person extends HasName, HasAddress + { + @Optional + Association<Person> spouse(); + ManyAssociation<Person> children(); + } + + public interface HasName + { + Property<String> name(); + } + + public interface HasAddress + { + Property<String> street1(); + Property<String> street2(); + Property<String> zipCode(); + Association<Country> country(); + } + + public interface Country extends HasName + { + } + // END SNIPPET: types + +} http://git-wip-us.apache.org/repos/asf/polygene-java/blob/0e399e07/manual/src/docs/userguide/extensions.txt ---------------------------------------------------------------------- diff --git a/manual/src/docs/userguide/extensions.txt b/manual/src/docs/userguide/extensions.txt index 82c692b..5fb4572 100644 --- a/manual/src/docs/userguide/extensions.txt +++ b/manual/src/docs/userguide/extensions.txt @@ -105,7 +105,7 @@ include::../../../../extensions/entitystore-riak/src/docs/es-riak.txt[] :leveloffset: 2 -include::../../../../extensions/entitystore-sql/src/docs/es-jooq.txt[] +include::../../../../extensions/entitystore-sql/src/docs/es-sql.txt[] :leveloffset: 2
