See javadoc: smtyp for what types DASemantics can handle.

The semantic.DA can automatically generate a 6 digital length, radix 64 integer used for primary key.
It's saved in database as a string, but it's logically an integer.

Semantics.DA doesn't depending on database for implementing this. There is a table named 'oz_autoseq' in the testing database. All sequence number are saved here.

javadoc:

smtyp.autInc

Resulved Value

The auto generated integer is stored in the semantext. To get the resulved value after commit, at server side with java:

To use the resulvedVal while building sql (it's still unknown when referencing it):

Notes about Mysql Collate
Mysql has a special character setting, COLLATE, for different charset. As we frequently handling Chinese, we found it's the only stable way to use the UTF8 charset. Semantic.DA hasen't tested for other charsets.

Also, you need be careful for the COLLATE settings. The default for mysql's UTF8 charset is utf8_general_ci or utf8mb4_general_ci, both of them doesn't distinguish upper case and and lower case. We've tested this on mysql 5.6 and mysq 5.7.

The 'auto-key' semantics will generate a base64 character string representing a integer in radix 64, so the upper and lower cases matter. Make sure all columns handled by 'auto-key' semantics is utf8_bin or utf8mb4_bin. Here is an example:

        CREATE TABLE `a_domain` (
            `domainId` varchar(40) COLLATE utf8_bin NOT NULL,
            `parentId` varchar(40) ) COLLATE utf8_bin DEFAULT NULL,
            `domainName` varchar(50) COLLATE utf8_general_ci DEFAULT NULL,
            `domainValue` varchar(50) CHARACTER SET utf8 DEFAULT NULL,
            `sort` int(11) DEFAULT NULL,
            `others` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '命令  0正常 1火警 2异常 3屏蔽',
            `fullpath` varchar(80) COLLATE utf8_bin DEFAULT NULL,
            `stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (`domainId`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

In the domain table, domainId is generated throught 'auto-key' semantics handler, and parentId is a reference to the column, so both are using 'utf8_bin'.
The 'other' field is used for some character flags, it's possible for letters, so it's also utf8_bin.
The fullpath is generated from sort and domainId, concatenated to parent's fullpath, it's also using utf8_bin.
Reason for keeping using utf8_general_ci is that it count Chinese characters correctly, no matter how many bytes a character is.

See Mysql 8.0 Manual: Chapter 10 Character Sets, Collations, Unicode for more details.

The semantic.DA can automatically generate tree node's deep first fullpath for a tree table.

A tree table with fullpath is usually designed like this:

            parent  nodeId  node-name   sibling-sort  fullpath
            null    1       root        1             1 1
            1       2       child 1     1             1 0/1 2
            1       3       child 2     2             1 0/2 3

There are a few pros if a tree table has a fullpath column.

One of it is finding a sub-tree is extemely simple and performance optimized because all nodes in a sub-tree have the same prefixes. No recursive query is needed, which is not supported by all DBMS, and time cosuming.

The other benefit of fullpath is essential to seamntic-*. If the query result set is ordered by fullpath, the resultset will be correctly been converted in tree structure in one iteration.

The semantic-jserv/SemanticTree depends on this, which will convert a general query result to pre-configured tree structure, suitable for client binding.

Javadoc

DASemantics.smtype.fkCateIns

About

FkCateIns is an FK field referencing multiple parent table, typically used for special data table like managing attached files.

If you save all uploaded file in a separate folder, then you may want a special table remembering all the file URI, and for what parent table it's used for. The jserv.sample project come with a table named a_attaches is designed for this:

CREATE TABLE a_attaches (
        attId TEXT NOT NULL, -- attachement ID
        attName TEXT,
        uri TEXT,            -- the resource identity
        busiTbl TEXT,        -- used for e.g. a_users
        busiId TEXT,         -- the user's id, e.g. admin
        oper TEXT,
        optime DATETIME,
        CONSTRAINT a_attaches_PK PRIMARY KEY (attId) );

Also, the semantics-sys.xml configured a semantics fk-ins-cate:

        <s>
            <id>att.parent</id>
            <smtc>fk-ins-cate</smtc>
            <tabl>a_attaches</tabl>
            <pk>attId</pk>
            <!-- 0 business cate (table name); 1 merged child fk; 2 parent table, 3 parent referee [, ...]  -->
            <args>
                busiTbl busiId a_users userId,
                busiTbl busiId b_tasks taskId
            </args>
        </s>

This semantics makes semantics.DA/io.odysz.semantics.DASemantics.ShFkInsCates will replace a_attaches.busiId with auto generated parent key for a_users.

Tip:
A common error for using this semantics is that update parent, then delete and insert new attachment records. This won't resulve parent pk (not generated so can't find when updating parent table). You probably will get an error message like this:

To avoid this assertion error, you can eigther insert attachment after inserted a new parent record, or provide a parent reference for the semantics. For the later method, see the jclient.java test case, io.odysz.jclient.SemantiClientTest#testUpload().

On Event

Insert

Let seamntic.DA update a FK after back referenced record is generated, like the cross referencing.

Why Post Updating?

If a parent record has an FK to a child, the FK can not resulved when inserting parent records. It can only updated when child's PK generated. Post FK can be used to update parent FK aftermath.

Why both Post Fk and Resulving are needed?