org.jooq.meta.derby.DerbyDatabase Maven / Gradle / Ivy
/*
* Licensed 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
*
* https://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.
*
* Other licenses:
* -----------------------------------------------------------------------------
* Commercial licenses for this work are available. These replace the above
* Apache-2.0 license and offer limited warranties, support, maintenance, and
* commercial database integrations.
*
* For more information, please visit: https://www.jooq.org/legal/licensing
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*/
package org.jooq.meta.derby;
import static java.util.stream.Collectors.mapping;
import static java.util.stream.Collectors.toList;
import static org.jooq.impl.DSL.case_;
import static org.jooq.impl.DSL.cast;
import static org.jooq.impl.DSL.condition;
import static org.jooq.impl.DSL.field;
import static org.jooq.impl.DSL.inline;
import static org.jooq.impl.DSL.noCondition;
import static org.jooq.impl.DSL.not;
import static org.jooq.impl.DSL.nullif;
import static org.jooq.impl.DSL.when;
import static org.jooq.impl.SQLDataType.BIGINT;
import static org.jooq.impl.SQLDataType.INTEGER;
import static org.jooq.impl.SQLDataType.NUMERIC;
import static org.jooq.impl.SQLDataType.VARCHAR;
import static org.jooq.meta.derby.sys.Tables.SYSCHECKS;
import static org.jooq.meta.derby.sys.Tables.SYSCOLUMNS;
import static org.jooq.meta.derby.sys.Tables.SYSCONGLOMERATES;
import static org.jooq.meta.derby.sys.Tables.SYSCONSTRAINTS;
import static org.jooq.meta.derby.sys.Tables.SYSKEYS;
import static org.jooq.meta.derby.sys.Tables.SYSSCHEMAS;
import static org.jooq.meta.derby.sys.Tables.SYSSEQUENCES;
import static org.jooq.meta.derby.sys.Tables.SYSTABLES;
import static org.jooq.meta.derby.sys.Tables.SYSTRIGGERS;
import static org.jooq.meta.derby.sys.Tables.SYSVIEWS;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.jooq.DSLContext;
import org.jooq.Field;
import org.jooq.Record;
import org.jooq.Record12;
import org.jooq.Record14;
import org.jooq.Record4;
import org.jooq.Record5;
import org.jooq.Record6;
import org.jooq.Result;
import org.jooq.ResultQuery;
import org.jooq.SQLDialect;
import org.jooq.SortOrder;
import org.jooq.TableOptions.TableType;
// ...
// ...
import org.jooq.exception.ControlFlowSignal;
import org.jooq.impl.DSL;
import org.jooq.meta.AbstractDatabase;
import org.jooq.meta.AbstractIndexDefinition;
import org.jooq.meta.ArrayDefinition;
import org.jooq.meta.CatalogDefinition;
import org.jooq.meta.ColumnDefinition;
import org.jooq.meta.DataTypeDefinition;
import org.jooq.meta.DefaultCheckConstraintDefinition;
import org.jooq.meta.DefaultDataTypeDefinition;
import org.jooq.meta.DefaultIndexColumnDefinition;
import org.jooq.meta.DefaultRelations;
import org.jooq.meta.DefaultSequenceDefinition;
import org.jooq.meta.DomainDefinition;
import org.jooq.meta.EnumDefinition;
import org.jooq.meta.IndexColumnDefinition;
import org.jooq.meta.IndexDefinition;
import org.jooq.meta.PackageDefinition;
import org.jooq.meta.ResultQueryDatabase;
import org.jooq.meta.RoutineDefinition;
import org.jooq.meta.SchemaDefinition;
import org.jooq.meta.SequenceDefinition;
import org.jooq.meta.TableDefinition;
import org.jooq.meta.UDTDefinition;
import org.jooq.meta.XMLSchemaCollectionDefinition;
import org.jooq.meta.derby.sys.tables.Systriggers;
/**
* @author Lukas Eder
*/
public class DerbyDatabase extends AbstractDatabase implements ResultQueryDatabase {
@Override
protected void loadPrimaryKeys(DefaultRelations relations) throws SQLException {
for (Record record : fetchKeys("P")) {
SchemaDefinition schema = getSchema(record.get(SYSSCHEMAS.SCHEMANAME));
String key = record.get(SYSCONSTRAINTS.CONSTRAINTNAME);
String tableName = record.get(SYSTABLES.TABLENAME);
String descriptor = record.get(SYSCONGLOMERATES.DESCRIPTOR, String.class);
TableDefinition table = getTable(schema, tableName);
if (table != null)
for (int index : decode(descriptor))
relations.addPrimaryKey(key, table, table.getColumn(index));
}
}
/**
* {@inheritDoc}
*/
@Override
protected void loadUniqueKeys(DefaultRelations relations) throws SQLException {
for (Record record : fetchKeys("U")) {
SchemaDefinition schema = getSchema(record.get(SYSSCHEMAS.SCHEMANAME));
String key = record.get(SYSCONSTRAINTS.CONSTRAINTNAME);
String tableName = record.get(SYSTABLES.TABLENAME);
String descriptor = record.get(SYSCONGLOMERATES.DESCRIPTOR, String.class);
TableDefinition table = getTable(schema, tableName);
if (table != null)
for (int index : decode(descriptor))
relations.addUniqueKey(key, table, table.getColumn(index));
}
}
@Override
public ResultQuery> primaryKeys(List schemas) {
return null;
}
@Override
public ResultQuery> uniqueKeys(List schemas) {
return null;
}
private Result> fetchKeys(String constraintType) {
return create().select(
SYSKEYS.sysconglomerates().systables().sysschemas().SCHEMANAME,
SYSKEYS.sysconglomerates().systables().TABLENAME,
SYSKEYS.sysconglomerates().systables().TABLEID,
SYSKEYS.sysconstraints().CONSTRAINTNAME,
SYSKEYS.sysconglomerates().DESCRIPTOR)
.from(SYSKEYS)
// [#6797] The casts are necessary if a non-standard collation is used
.where(SYSKEYS.sysconglomerates().systables().sysschemas().SCHEMANAME.cast(VARCHAR(32672)).in(getInputSchemata()))
.and(SYSKEYS.sysconstraints().TYPE.cast(VARCHAR(32672)).equal(constraintType))
.orderBy(
SYSKEYS.sysconglomerates().systables().sysschemas().SCHEMANAME,
SYSKEYS.sysconglomerates().systables().TABLENAME,
SYSKEYS.sysconstraints().CONSTRAINTNAME)
.fetch();
}
@Override
protected void loadForeignKeys(DefaultRelations relations) throws SQLException {
Field fkName = field("fc.constraintname", String.class);
Field fkTable = field("ft.tablename", String.class);
Field fkSchema = field("fs.schemaname", String.class);
Field fkDescriptor = field("fg.descriptor");
Field ukName = field("pc.constraintname", String.class);
Field ukTable = field("pt.tablename", String.class);
Field ukSchema = field("ps.schemaname", String.class);
for (Record record : create().select(
fkName,
fkTable,
fkSchema,
fkDescriptor,
ukName,
ukTable,
ukSchema)
.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.systables pt").on("pt.tableid = pc.tableid")
.join("sys.sysschemas ps").on("ps.schemaid = pt.schemaid")
// [#6797] The cast is necessary if a non-standard collation is used
.where("cast(fc.type as varchar(32672)) = 'F'")
.orderBy(fkSchema, fkTable, fkName)
) {
SchemaDefinition foreignKeySchema = getSchema(record.get(fkSchema));
SchemaDefinition uniqueKeySchema = getSchema(record.get(ukSchema));
String foreignKeyName = record.get(fkName);
String foreignKeyTableName = record.get(fkTable);
List foreignKeyIndexes = decode(record.get(fkDescriptor, String.class));
String uniqueKeyName = record.get(ukName);
String uniqueKeyTableName = record.get(ukTable);
TableDefinition foreignKeyTable = getTable(foreignKeySchema, foreignKeyTableName);
TableDefinition uniqueKeyTable = getTable(uniqueKeySchema, uniqueKeyTableName);
if (foreignKeyTable != null && uniqueKeyTable != null)
for (int i = 0; i < foreignKeyIndexes.size(); i++)
relations.addForeignKey(
foreignKeyName,
foreignKeyTable,
foreignKeyTable.getColumn(foreignKeyIndexes.get(i)),
uniqueKeyName,
uniqueKeyTable
);
}
}
/*
* Unfortunately the descriptor interface is not exposed publicly Hence, the
* toString() method is used and its results are parsed The results are
* something like UNIQUE BTREE (index1, index2, ... indexN)
*/
private List decode(String descriptor) {
List result = new ArrayList<>();
Pattern p = Pattern.compile(".*?\\((.*?)\\)");
Matcher m = p.matcher(descriptor);
while (m.find()) {
String[] split = m.group(1).split(",");
if (split != null)
for (String index : split)
result.add(Integer.parseInt(index.trim()) - 1);
}
return result;
}
@Override
protected void loadCheckConstraints(DefaultRelations relations) throws SQLException {
for (Record record : create()
.select(
SYSCHECKS.sysconstraints().systables().sysschemas().SCHEMANAME,
SYSCHECKS.sysconstraints().systables().TABLENAME,
SYSCHECKS.sysconstraints().CONSTRAINTNAME,
SYSCHECKS.CHECKDEFINITION)
.from(SYSCHECKS)
.where(SYSCHECKS.sysconstraints().systables().sysschemas().SCHEMANAME.in(getInputSchemata()))
) {
SchemaDefinition schema = getSchema(record.get(SYSCHECKS.sysconstraints().systables().sysschemas().SCHEMANAME));
TableDefinition table = getTable(schema, record.get(SYSCHECKS.sysconstraints().systables().TABLENAME));
if (table != null) {
relations.addCheckConstraint(table, new DefaultCheckConstraintDefinition(
schema,
table,
record.get(SYSCHECKS.sysconstraints().CONSTRAINTNAME),
record.get(SYSCHECKS.CHECKDEFINITION)
));
}
}
}
Map> lookupColumnByIndex;
private ColumnDefinition lookupColumnByIndex(TableDefinition table, int index) {
if (lookupColumnByIndex == null) {
lookupColumnByIndex = new HashMap<>();
for (Record r : create()
.select(
SYSTABLES.sysschemas().SCHEMANAME,
SYSTABLES.TABLENAME,
SYSCOLUMNS.COLUMNNAME,
SYSCOLUMNS.COLUMNNUMBER)
.from(SYSCOLUMNS)
.join(SYSTABLES).on(SYSCOLUMNS.REFERENCEID.eq(SYSTABLES.TABLEID))
.where(SYSTABLES.sysschemas().SCHEMANAME.in(getInputSchemata()))
.orderBy(
SYSTABLES.sysschemas().SCHEMANAME,
SYSTABLES.TABLENAME,
SYSCOLUMNS.COLUMNNUMBER)
) {
SchemaDefinition s = getSchema(r.get(SYSTABLES.sysschemas().SCHEMANAME));
if (s != null) {
TableDefinition t = getTable(s, r.get(SYSTABLES.TABLENAME));
if (t != null) {
ColumnDefinition c = t.getColumn(r.get(SYSCOLUMNS.COLUMNNAME));
// [#16237] ColumnDefinition can be null if it's hidden or excluded
lookupColumnByIndex.computeIfAbsent(t, x -> new ArrayList<>()).add(c);
}
}
}
}
List list = lookupColumnByIndex.get(table);
if (list == null)
return null;
if (list.size() <= index)
return null;
return list.get(index);
}
@Override
protected List getIndexes0() throws SQLException {
List result = new ArrayList<>();
indexLoop:
for (Record record : create()
.select(
SYSCONGLOMERATES.systables().sysschemas().SCHEMANAME,
SYSCONGLOMERATES.systables().TABLENAME,
SYSCONGLOMERATES.CONGLOMERATENAME,
SYSCONGLOMERATES.DESCRIPTOR)
.from(SYSCONGLOMERATES)
// [#6797] The cast is necessary if a non-standard collation is used
.where(SYSCONGLOMERATES.systables().sysschemas().SCHEMANAME.cast(VARCHAR(32672)).in(getInputSchemata()))
.and(SYSCONGLOMERATES.ISINDEX)
.and(getIncludeSystemIndexes()
? noCondition()
: not(condition(SYSCONGLOMERATES.ISCONSTRAINT)))
.orderBy(
SYSCONGLOMERATES.systables().sysschemas().SCHEMANAME,
SYSCONGLOMERATES.systables().TABLENAME,
SYSCONGLOMERATES.CONGLOMERATENAME)
) {
final SchemaDefinition tableSchema = getSchema(record.get(SYSCONGLOMERATES.systables().sysschemas().SCHEMANAME));
if (tableSchema == null)
continue indexLoop;
final String indexName = record.get(SYSCONGLOMERATES.CONGLOMERATENAME);
final String tableName = record.get(SYSCONGLOMERATES.systables().TABLENAME);
final TableDefinition table = getTable(tableSchema, tableName);
if (table == null)
continue indexLoop;
final String descriptor = record.get(SYSCONGLOMERATES.DESCRIPTOR);
if (descriptor == null)
continue indexLoop;
class SkipIndex extends ControlFlowSignal {}
try {
result.add(new AbstractIndexDefinition(tableSchema, indexName, table, descriptor.toUpperCase().contains("UNIQUE")) {
List indexColumns = new ArrayList<>();
{
List columnIndexes = decode(descriptor);
for (int i = 0; i < columnIndexes.size(); i++) {
ColumnDefinition column = lookupColumnByIndex(table, columnIndexes.get(i));
// [#16237] If column is hidden or excluded
if (column == null)
throw new SkipIndex();
indexColumns.add(new DefaultIndexColumnDefinition(
this,
column,
SortOrder.ASC,
i + 1
));
}
}
@Override
protected List getIndexColumns0() {
return indexColumns;
}
});
}
catch (SkipIndex ignore) {}
}
return result;
}
@Override
protected List getCatalogs0() throws SQLException {
List result = new ArrayList<>();
result.add(new CatalogDefinition(this, "", ""));
return result;
}
@Override
protected List getSchemata0() throws SQLException {
return
create().select(SYSSCHEMAS.SCHEMANAME)
.from(SYSSCHEMAS)
.collect(mapping(r -> new SchemaDefinition(this, r.value1(), ""), toList()));
}
@Override
public ResultQuery> sources(List schemas) {
return create()
.select(
inline(null, VARCHAR).cast(VARCHAR).as("catalog"),
SYSSCHEMAS.SCHEMANAME,
SYSTABLES.TABLENAME,
SYSVIEWS.VIEWDEFINITION)
.from(SYSTABLES)
// [#17280] Avoid implicit joining (i.e. aliasing) SYSSCHEMAS due to a bug in Derby:
// https://issues.apache.org/jira/browse/DERBY-7170
.join(SYSSCHEMAS)
.on(SYSTABLES.SCHEMAID.eq(SYSSCHEMAS.SCHEMAID))
.leftJoin(SYSVIEWS)
.on(SYSTABLES.TABLEID.eq(SYSVIEWS.TABLEID))
// [#6797] The cast is necessary if a non-standard collation is used
.where(SYSSCHEMAS.SCHEMANAME.cast(VARCHAR(32672)).in(schemas))
.orderBy(
SYSSCHEMAS.SCHEMANAME,
SYSTABLES.TABLENAME);
}
@Override
public ResultQuery> comments(List schemas) {
return null;
}
@Override
public ResultQuery> sequences(List schemas) {
return create().select(
inline(null, VARCHAR).cast(VARCHAR).as("catalog"),
SYSSCHEMAS.SCHEMANAME,
SYSSEQUENCES.SEQUENCENAME,
SYSSEQUENCES.SEQUENCEDATATYPE,
inline(null, INTEGER).cast(INTEGER).as("numeric_precision"),
inline(null, INTEGER).cast(INTEGER).as("numeric_scale"),
nullif(SYSSEQUENCES.STARTVALUE, inline(1L)).as(SYSSEQUENCES.STARTVALUE),
nullif(SYSSEQUENCES.INCREMENT, inline(1L)).as(SYSSEQUENCES.INCREMENT),
nullif(SYSSEQUENCES.MINIMUMVALUE, case_(cast(SYSSEQUENCES.SEQUENCEDATATYPE, VARCHAR))
.when(inline("SMALLINT"), inline((long) Short.MIN_VALUE))
.when(inline("INTEGER"), inline((long) Integer.MIN_VALUE))
.when(inline("BIGINT"), inline(Long.MIN_VALUE))
).coerce(NUMERIC).as(SYSSEQUENCES.MINIMUMVALUE),
nullif(SYSSEQUENCES.MAXIMUMVALUE, case_(cast(SYSSEQUENCES.SEQUENCEDATATYPE, VARCHAR))
.when(inline("SMALLINT"), inline((long) Short.MAX_VALUE))
.when(inline("INTEGER"), inline((long) Integer.MAX_VALUE))
.when(inline("BIGINT"), inline(Long.MAX_VALUE))
).coerce(NUMERIC).as(SYSSEQUENCES.MAXIMUMVALUE),
SYSSEQUENCES.CYCLEOPTION.eq(inline("Y")).as(SYSSEQUENCES.CYCLEOPTION),
inline(null, BIGINT).cast(BIGINT).as("cache")
)
.from(SYSSEQUENCES)
// [#17280] Avoid implicit joining (i.e. aliasing) SYSSCHEMAS due to a bug in Derby:
// https://issues.apache.org/jira/browse/DERBY-7170
.join(SYSSCHEMAS)
.on(SYSSEQUENCES.SCHEMAID.eq(SYSSCHEMAS.SCHEMAID))
// [#6797] The cast is necessary if a non-standard collation is used
.where(SYSSCHEMAS.SCHEMANAME.cast(VARCHAR(32672)).in(schemas))
.orderBy(
SYSSCHEMAS.SCHEMANAME,
SYSSEQUENCES.SEQUENCENAME);
}
@Override
protected List getSequences0() throws SQLException {
List result = new ArrayList<>();
for (Record record : sequences(getInputSchemata())) {
SchemaDefinition schema = getSchema(record.get(SYSSCHEMAS.SCHEMANAME));
DataTypeDefinition type = new DefaultDataTypeDefinition(
this,
schema,
record.get(SYSSEQUENCES.SEQUENCEDATATYPE)
);
result.add(new DefaultSequenceDefinition(
schema,
record.get(SYSSEQUENCES.SEQUENCENAME),
type,
null,
record.get(SYSSEQUENCES.STARTVALUE),
record.get(SYSSEQUENCES.INCREMENT),
record.get(SYSSEQUENCES.MINIMUMVALUE),
record.get(SYSSEQUENCES.MAXIMUMVALUE),
record.get(SYSSEQUENCES.CYCLEOPTION, Boolean.class),
null
));
}
return result;
}
@Override
public ResultQuery> enums(List schemas) {
return null;
}
@Override
protected List getTables0() throws SQLException {
List result = new ArrayList<>();
for (Record record : create().select(
SYSSCHEMAS.SCHEMANAME,
SYSTABLES.TABLENAME,
SYSTABLES.TABLEID,
when(SYSTABLES.TABLETYPE.eq(inline("V")), inline(TableType.VIEW.name()))
.else_(inline(TableType.TABLE.name())).as("table_type"))
.from(SYSTABLES)
// [#17280] Avoid implicit joining (i.e. aliasing) SYSSCHEMAS due to a bug in Derby:
// https://issues.apache.org/jira/browse/DERBY-7170
.join(SYSSCHEMAS)
.on(SYSTABLES.SCHEMAID.eq(SYSSCHEMAS.SCHEMAID))
// [#6797] The cast is necessary if a non-standard collation is used
.where(SYSSCHEMAS.SCHEMANAME.cast(VARCHAR(32672)).in(getInputSchemata()))
.orderBy(
SYSSCHEMAS.SCHEMANAME,
SYSTABLES.TABLENAME)) {
SchemaDefinition schema = getSchema(record.get(SYSSCHEMAS.SCHEMANAME));
String name = record.get(SYSTABLES.TABLENAME);
String id = record.get(SYSTABLES.TABLEID);
TableType tableType = record.get("table_type", TableType.class);
DerbyTableDefinition table = new DerbyTableDefinition(schema, name, id, tableType, null);
result.add(table);
}
return result;
}
@Override
protected List getEnums0() throws SQLException {
List result = new ArrayList<>();
return result;
}
@Override
protected List getDomains0() throws SQLException {
List result = new ArrayList<>();
return result;
}
@Override
protected List getXMLSchemaCollections0() throws SQLException {
List result = new ArrayList<>();
return result;
}
@Override
protected List getUDTs0() throws SQLException {
List result = new ArrayList<>();
return result;
}
@Override
protected List getArrays0() throws SQLException {
List result = new ArrayList<>();
return result;
}
@Override
protected List getRoutines0() throws SQLException {
List result = new ArrayList<>();
return result;
}
@Override
protected List getPackages0() throws SQLException {
List result = new ArrayList<>();
return result;
}
@Override
protected DSLContext create0() {
return DSL.using(getConnection(), SQLDialect.DERBY);
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy