Digest for jooq-user@googlegroups.com - 2 updates in 1 topic

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Digest for jooq-user@googlegroups.com - 2 updates in 1 topic

http://groups.google.com/group/jooq-user/topics mailing list
Manuel Rossetti <[hidden email]>: Nov 30 05:07PM -0600

I am running jooq 3.10.1
 
Here is a code snippet with your SELECT statement and its output. As you
will see, the ALTER from the DDL print out do not have the names of the
constraints. But, as you have noted, with your select statement, clearly
the names of the constraints are in the DB system tables. We agree that
the generated java code has the constraint names. *However, the SQL
generated from the DSLContext ddl() method is not capturing the constraint
names.*
 
******* code *****
List<Schema> schemas = create.meta().getSchemas();
Queries ddl = null;
for (Schema s : schemas) {
if (s.getName().equals("APP")) {
ddl = create.ddl(s);
break;
}
}
 
for (Query query : ddl.queries()) {
System.out.println(query);
}
 
String sql = "SELECT
fc.constraintname,ft.tablename,fs.schemaname,fg.descriptor,pc.constraintname,ps.schemaname
FROM sys.sysconstraints fc JOIN sys.sysforeignkeys f ON f.constraintid =
fc.constraintid JOIN sys.sysconglomerates fg ON fg.conglomerateid =
f.conglomerateid JOIN sys.systables ft ON ft.tableid = fg.tableid JOIN
sys.sysschemas fs ON ft.schemaid = fs.schemaid JOIN sys.sysconstraints pc
ON pc.constraintid = f.keyconstraintid JOIN sys.sysschemas ps ON
pc.schemaid = ps.schemaid WHERE cast(fc.type as varchar(32672)) = 'F'";
System.out.println(create.resultQuery(sql).fetch().format());
****** end code ****
 
 
------------ Output from the code -----------
create schema "APP"
create table "APP"."ARC"(
"ID" integer not null,
"EDGE" integer,
"PRODUCT" integer,
"PRIORITY" integer,
constraint "SQL171130162547050"
primary key ("ID")
)
create table "APP"."DEMAND"(
"ID" integer not null,
"TERMINAL" integer,
"PRODUCT" integer,
"PERIOD" integer,
"AMOUNT" integer,
constraint "SQL171130162546990"
primary key ("ID")
)
create table "APP"."EDGE"(
"ID" integer not null,
"ORIGIN" integer,
"DESTINATION" integer,
"VEHICLE" integer,
"TRANSITTIME" double,
constraint "SQL171130162547031"
primary key ("ID")
)
create table "APP"."ERQ"(
"ID" integer not null,
"TERMINAL" integer,
"PRODUCT" integer,
"MODE" integer,
"AMOUNT" integer,
constraint "SQL171130162547010"
primary key ("ID")
)
create table "APP"."INGREDIENT"(
"ID" integer not null,
"RECIPE" integer,
"PRODUCT" integer,
"AMOUNT" integer,
constraint "SQL171130162546980"
primary key ("ID")
)
create table "APP"."MODE"(
"ID" integer not null,
"CODE" varchar(1),
"NAME" varchar(11),
constraint "SQL171130162546870"
primary key ("ID")
)
create table "APP"."OPERATION"(
"ID" integer not null,
"CODE" varchar(1),
"NAME" varchar(10),
constraint "SQL171130162546920"
primary key ("ID")
)
create table "APP"."PIPELINE"(
"ROUTE" integer not null,
"CAPACITY" integer,
"MAXBATCH" integer,
"MINBATCH" integer,
"MAXRATE" integer,
"MINRATE" integer,
"MININTERVAL" integer,
constraint "SQL171130162546960"
primary key ("ROUTE")
)
create table "APP"."PRODUCT"(
"ID" integer not null,
"NAME" varchar(4),
"ADDITIVE" boolean not null,
"DESCRIPTION" varchar(255),
constraint "SQL171130162546930"
primary key ("ID")
)
create table "APP"."RECIPE"(
"ID" integer not null,
"PRODUCT" integer,
"DESCRIPTION" varchar(255),
constraint "SQL171130162546971"
primary key ("ID")
)
create table "APP"."RESOURCE"(
"ID" integer not null,
"TERMINAL" integer,
"MODE" integer,
"NAME" varchar(30),
"THROUGHPUT" integer,
"SPACES" integer,
"VEHICLETIME" double,
"PUMPRATE" integer,
constraint "SQL171130162547000"
primary key ("ID")
)
create table "APP"."RESOURCEOPERATION"(
"ID" integer not null,
"TERMINALOPERATION1" integer,
"TERMINALOPERATION2" integer,
constraint "SQL171130162547030"
primary key ("ID")
)
create table "APP"."ROUTE"(
"ID" integer not null,
"NAME" varchar(30),
"CIRCULAR" boolean not null,
constraint "SQL171130162546950"
primary key ("ID")
)
create table "APP"."ROUTEARC"(
"ID" integer not null,
"ROUTE" integer,
"ARC" integer,
"STOP" integer,
"LOADING" varchar(255),
constraint "SQL171130162547060"
primary key ("ID")
)
create table "APP"."SCHEDULEDRECEIPT"(
"ID" integer not null,
"ARC" integer,
"PERIOD" integer,
"AMOUNT" integer,
constraint "SQL171130162547070"
primary key ("ID")
)
create table "APP"."SKU"(
"ID" integer not null,
"TERMINAL" integer,
"PRODUCT" integer,
"INVENTORY" integer,
"MAXAUTHORIZED" integer,
"MAXFILL" integer,
"CONTROLLIMIT" integer,
constraint "SQL171130162547001"
primary key ("ID")
)
create table "APP"."TERMINAL"(
"ID" integer not null,
"NAME" varchar(30),
"LOCATION" varchar(30),
"TYPE" varchar(10),
"OWNER" varchar(4),
"MAXHOURS" integer,
"INJECTION" boolean not null,
constraint "SQL171130162546940"
primary key ("ID")
)
create table "APP"."TERMINALOPERATION"(
"ID" integer not null,
"RESOURCE" integer,
"OPERATION" integer,
"PRODUCT" integer,
"SPACES" integer,
constraint "SQL171130162547020"
primary key ("ID")
)
create table "APP"."VEHICLETYPE"(
"ID" integer not null,
"NAME" varchar(20),
"MODE" integer,
"SIZE" integer,
"FLEET" integer,
"TANKS" integer,
constraint "SQL171130162546970"
primary key ("ID")
)
alter table "APP"."ARC"
add constraint
foreign key ("EDGE")
references "EDGE" ("ID")
alter table "APP"."ARC"
add constraint
foreign key ("PRODUCT")
references "PRODUCT" ("ID")
alter table "APP"."DEMAND"
add constraint
foreign key ("PRODUCT")
references "PRODUCT" ("ID")
alter table "APP"."DEMAND"
add constraint
foreign key ("TERMINAL")
references "TERMINAL" ("ID")
alter table "APP"."EDGE"
add constraint
foreign key ("DESTINATION")
references "RESOURCE" ("ID")
alter table "APP"."EDGE"
add constraint
foreign key ("ORIGIN")
references "RESOURCE" ("ID")
alter table "APP"."EDGE"
add constraint
foreign key ("VEHICLE")
references "VEHICLETYPE" ("ID")
alter table "APP"."ERQ"
add constraint
foreign key ("MODE")
references "MODE" ("ID")
alter table "APP"."ERQ"
add constraint
foreign key ("PRODUCT")
references "PRODUCT" ("ID")
alter table "APP"."ERQ"
add constraint
foreign key ("TERMINAL")
references "TERMINAL" ("ID")
alter table "APP"."INGREDIENT"
add constraint
foreign key ("PRODUCT")
references "PRODUCT" ("ID")
alter table "APP"."INGREDIENT"
add constraint
foreign key ("RECIPE")
references "RECIPE" ("ID")
alter table "APP"."PIPELINE"
add constraint
foreign key ("ROUTE")
references "ROUTE" ("ID")
alter table "APP"."RECIPE"
add constraint
foreign key ("PRODUCT")
references "PRODUCT" ("ID")
alter table "APP"."RESOURCE"
add constraint
foreign key ("MODE")
references "MODE" ("ID")
alter table "APP"."RESOURCE"
add constraint
foreign key ("TERMINAL")
references "TERMINAL" ("ID")
alter table "APP"."RESOURCEOPERATION"
add constraint
foreign key ("TERMINALOPERATION2")
references "TERMINALOPERATION" ("ID")
alter table "APP"."RESOURCEOPERATION"
add constraint
foreign key ("TERMINALOPERATION1")
references "TERMINALOPERATION" ("ID")
alter table "APP"."ROUTEARC"
add constraint
foreign key ("ARC")
references "ARC" ("ID")
alter table "APP"."ROUTEARC"
add constraint
foreign key ("ROUTE")
references "ROUTE" ("ID")
alter table "APP"."SCHEDULEDRECEIPT"
add constraint
foreign key ("ARC")
references "ARC" ("ID")
alter table "APP"."SKU"
add constraint
foreign key ("PRODUCT")
references "PRODUCT" ("ID")
alter table "APP"."SKU"
add constraint
foreign key ("TERMINAL")
references "TERMINAL" ("ID")
alter table "APP"."TERMINALOPERATION"
add constraint
foreign key ("OPERATION")
references "OPERATION" ("ID")
alter table "APP"."TERMINALOPERATION"
add constraint
foreign key ("PRODUCT")
references "PRODUCT" ("ID")
alter table "APP"."TERMINALOPERATION"
add constraint
foreign key ("RESOURCE")
references "RESOURCE" ("ID")
alter table "APP"."VEHICLETYPE"
add constraint
foreign key ("MODE")
references "MODE" ("ID")
+-----------------------------------+-----------------+----------+----------+------------------+----------+
|CONSTRAINTNAME |TABLENAME
|SCHEMANAME|DESCRIPTOR|CONSTRAINTNAME |SCHEMANAME|
+-----------------------------------+-----------------+----------+----------+------------------+----------+
|MODEVEHICLETYPE |VEHICLETYPE |APP |BTREE (3)
|SQL171130162546870|APP |
|MODERESOURCE |RESOURCE |APP |BTREE (3)
|SQL171130162546870|APP |
|MODEERQ |ERQ |APP |BTREE (4)
|SQL171130162546870|APP |
|OPERATIONTERMINALOPERATION |TERMINALOPERATION|APP |BTREE (3)
|SQL171130162546920|APP |
|PRODUCTRECIPE |RECIPE |APP |BTREE (2)
|SQL171130162546930|APP |
|PRODUCTINGREDIENT |INGREDIENT |APP |BTREE (3)
|SQL171130162546930|APP |
|PRODUCTDEMAND |DEMAND |APP |BTREE (3)
|SQL171130162546930|APP |
|PRODUCTSKU |SKU |APP |BTREE (3)
|SQL171130162546930|APP |
|PRODUCTERQ |ERQ |APP |BTREE (3)
|SQL171130162546930|APP |
|PRODUCTARC |ARC |APP |BTREE (3)
|SQL171130162546930|APP |
|PRODUCTTERMINALOPERATION |TERMINALOPERATION|APP |BTREE (4)
|SQL171130162546930|APP |
|TERMINALDEMAND |DEMAND |APP |BTREE (2)
|SQL171130162546940|APP |
|TERMINALSKU |SKU |APP |BTREE (2)
|SQL171130162546940|APP |
|TERMINALRESOURCE |RESOURCE |APP |BTREE (2)
|SQL171130162546940|APP |
|TERMINALERQ |ERQ |APP |BTREE (2)
|SQL171130162546940|APP |
|PIPELINEROUTE |PIPELINE |APP |BTREE (1)
|SQL171130162546950|APP |
|ROUTEROUTEARC |ROUTEARC |APP |BTREE (2)
|SQL171130162546950|APP |
|VEHICLETYPEEDGE |EDGE |APP |BTREE (4)
|SQL171130162546970|APP |
|RECIPEINGREDIENT |INGREDIENT |APP |BTREE (2)
|SQL171130162546971|APP |
|ORIGINEDGE |EDGE |APP |BTREE (2)
|SQL171130162547000|APP |
|DESTINATIONEDGE |EDGE |APP |BTREE (3)
|SQL171130162547000|APP |
|RESOURCETERMINALOPERATION |TERMINALOPERATION|APP |BTREE (2)
|SQL171130162547000|APP |
|TERMINALOPERATIONRESOURCEOPERATION |RESOURCEOPERATION|APP |BTREE (2)
|SQL171130162547020|APP |
|TERMINALOPERATIONRESOURCEOPERATION1|RESOURCEOPERATION|APP |BTREE (3)
|SQL171130162547020|APP |
|EDGEARC |ARC |APP |BTREE (2)
|SQL171130162547031|APP |
|ARCSHIPMENT |SCHEDULEDRECEIPT |APP |BTREE (2)
|SQL171130162547050|APP |
|ARCROUTEARC |ROUTEARC |APP |BTREE (3)
|SQL171130162547050|APP |
+-----------------------------------+-----------------+----------+----------+------------------+----------+
 
 
Manuel Rossetti <[hidden email]>: Nov 30 05:20PM -0600

I also noticed that you are using Tables.ARC to reference the table. I
believe that this is the difference. I am using the meta data from the
schema.
 
So, it seems like there is an inconsistency here. In my use case, I do not
want to name specific table names. I want to produce the DDL for all tables
in the schema.
----------------
 
DSLContext create = DSL.using(c, SQLDialect.DERBY);
Meta meta = create.meta();
List<Table<?>> tables = meta.getTables();
System.out.println(create.ddl(tables.get(0))); // 0 happens to
be ARC table
 
System.out.println(create.ddl(Tables.ARC));
 
Output:
 
create table "APP"."ARC"(
"ID" integer not null,
"EDGE" integer,
"PRODUCT" integer,
"PRIORITY" integer,
constraint "SQL171130162547050"
primary key ("ID"),
constraint
foreign key ("EDGE")
references "APP"."EDGE" ("ID"),
constraint
foreign key ("PRODUCT")
references "APP"."PRODUCT" ("ID")
);
 
create table "APP"."ARC"(
"ID" int generated by default as identity not null,
"EDGE" int default 0,
"PRODUCT" int default 0,
"PRIORITY" int default 1,
constraint "SQL171125153158620"
primary key ("ID"),
constraint "EDGEARC"
foreign key ("EDGE")
references "APP"."EDGE" ("ID"),
constraint "PRODUCTARC"
foreign key ("PRODUCT")
references "APP"."PRODUCT" ("ID")
);
 
On Thu, Nov 30, 2017 at 5:07 PM, Manuel Rossetti <[hidden email]>
wrote:
 
You received this digest because you're subscribed to updates for this group. You can change your settings on the group membership page.
To unsubscribe from this group and stop receiving emails from it send an email to [hidden email].