Support > Repository > Business logic > Primary key
ja | en

I will explain how to set arbitrary format using a script.

Depending on the "status" item in the report model, the following rules shall apply:

New customer
N1404001 (1st digit: N, 2nd to 5th digit: year/month, 6th to 8th digit: 001 ... 999 serial number)
existing customer
E1404001 (1st digit: W, 2nd to 5th digit: year/month, 6th to 8th digit: 001 ... 999 serial number)

in this way,Sequential number prepared for each classificationTo do.

Figure 1 shows the registration of new customer's report, and Figure 2 shows an example of registering existing customer's report.Both serial numbers are 001 and are managed independently.

Figure 1 New customer's report, primary key is "N1404001"
Figure 2 Existing customer's report, primary key is "E 1404001"

Figure 3 shows the overall configuration of the "report" model.

Figure 3 Report model

Details about the primary key of the primary key item "ID" are not set.(Figure 4) This is implemented as a script.[To be described later]

Figure 4 Do not perform detailed setting of primary key

- When entering "ID" item, treat it as hidden item/Read only.With this, you can realize no input (automatic setting) although it is a primary key.

Figure 5 Hidden and read only settings

The initial value of the "status" model is shown in Figure 6."New" assign code 1, "existing" assign code 2 respectively.

Figure 6 Initial value of the status model

In order to prepare a sequential number for each class, we register their own order "report_seq 1" and "report _ seq 2" in the order table "seq" prepared by Wagby.Set the start number to "1".

In order to issue your own DDL, prepare $ (DEVHOME) /customize/webapp/WEB-INF/export/conf/initdb_ex.xml.(FIG. 7)

Please create export folder and export/conf folder manually.
Figure 7 Creating a new initdb_ex.xml

We will have the following contents.

Figure 8 Contents of initdb_ex.xml
<?xml version="1.0" encoding="UTF-8"?>
  <sql group="data"
        acceptcommand="sql sql-create-after" denycommand="all"
        tablename="seq">
INSERT INTO &quot;seq&quot; VALUES('report_seq1',1);
INSERT INTO &quot;seq&quot; VALUES('report_seq2',1);
  </sql>

After the build, it will be placed below.
wagbyapp/webapps/wagby/WEB-INF/export/conf/initdb_ex.xml
I used the sequence table "seq" here, but if your database has an ordering mechanism, it is a good idea to create a sequence with CREATE SEQUENCE.(Oracle, PostgreSQL, SQL Server 2012 provide order.)

In this way, initdb_ex.xml can write its own DDL in the sql element.When creating a database with Wagby, you can issue your own DDL, such as creating sequences and views.[Related Pages: Guide to Using Import and Export Function (R 7)> Setting File> Extension file initdb_ex.xml]
Whether double quotes enclose the table name depend on the database you use.

"Screen> scriptOpen ".Create script code for new registration.
This is called at the end of the new registration process, so it is a convenient point to set the value of the primary key.

Figure 9 Code for Primary Key Generation
var CommonService = Java.type("jp.jasminesoft.jfc.app.CommonService");

var seq;
var prefix;
if (report.status == 1) {    
    /* 順序の使い分け */
    /* CommonServiceはWagbyが提供するクラス */
    /* p はWagbyが提供するオブジェクト */
    seq = CommonService.getSeq(p, "report_seq1");
    prefix = "N";
} else if (report.status == 2) {
    seq = CommonService.getSeq(p, "report_seq2");
    prefix = "E";
}
/* デバッグ用 stdout.println("seq="+seq+",prefix="+prefix);*/
var pkey = prefix;
/* new Date() は JavaScript のオブジェクトを利用 */
var today = new Date();
/* String() は JavaScript のオブジェクトを利用 */
var year = String(today.getFullYear());
var month = String(today.getMonth()+1);
/* ExcelFunctionはWagbyが提供するクラス */
/* ゼロ詰めにし、年部分の右2桁を取得する */
pkey = pkey + ExcelFunction.RIGHT(ExcelFunction.PADDING("0",2,year),2);
/* ゼロ詰めにし、月部分の右2桁を取得する */
pkey = pkey + ExcelFunction.RIGHT(ExcelFunction.PADDING("0",2,month),2);
/* ゼロ詰めにし、順序部分の右3桁を取得する */
pkey = pkey + ExcelFunction.RIGHT(ExcelFunction.PADDING("0",3,String(seq)),3);
/* reportモデルのreportid項目が文字列型の主キー */
report.reportid = pkey;
  • RIGHT functionFetches N digits from the right of the string.
  • PADDING function, It fills the character string with specified characters and cuts out.
If you created the order with CREATE SEQUENCE, use CommonService.getSequence instead of CommonService.getSeq.