SQOOP-1597: Sqoop2: Refactor DerbySchemaQuery into one for create/ update/ and then CRUD operarations
(Veena Basavaraj via Jarek Jarcec Cecho) Project: http://git-wip-us.apache.org/repos/asf/sqoop/repo Commit: http://git-wip-us.apache.org/repos/asf/sqoop/commit/3c7e3ea9 Tree: http://git-wip-us.apache.org/repos/asf/sqoop/tree/3c7e3ea9 Diff: http://git-wip-us.apache.org/repos/asf/sqoop/diff/3c7e3ea9 Branch: refs/heads/sqoop2 Commit: 3c7e3ea9f2b10c4dce27b148e5872aba3300de6e Parents: 520fc33 Author: Jarek Jarcec Cecho <[email protected]> Authored: Mon Oct 27 14:12:37 2014 -0700 Committer: Jarek Jarcec Cecho <[email protected]> Committed: Mon Oct 27 14:12:37 2014 -0700 ---------------------------------------------------------------------- .../derby/DerbyRepositoryHandler.java | 5 +- .../derby/DerbySchemaCreateQuery.java | 483 ++++++ ...erbySchemaInsertUpdateDeleteSelectQuery.java | 617 ++++++++ .../repository/derby/DerbySchemaQuery.java | 1434 ------------------ .../derby/DerbySchemaUpgradeQuery.java | 434 ++++++ .../sqoop/repository/derby/DerbyTestCase.java | 5 +- 6 files changed, 1542 insertions(+), 1436 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/sqoop/blob/3c7e3ea9/repository/repository-derby/src/main/java/org/apache/sqoop/repository/derby/DerbyRepositoryHandler.java ---------------------------------------------------------------------- diff --git a/repository/repository-derby/src/main/java/org/apache/sqoop/repository/derby/DerbyRepositoryHandler.java b/repository/repository-derby/src/main/java/org/apache/sqoop/repository/derby/DerbyRepositoryHandler.java index b4b5f3e..7f19c28 100644 --- a/repository/repository-derby/src/main/java/org/apache/sqoop/repository/derby/DerbyRepositoryHandler.java +++ b/repository/repository-derby/src/main/java/org/apache/sqoop/repository/derby/DerbyRepositoryHandler.java @@ -17,7 +17,10 @@ */ package org.apache.sqoop.repository.derby; -import static org.apache.sqoop.repository.derby.DerbySchemaQuery.*; +import static org.apache.sqoop.repository.derby.DerbySchemaUpgradeQuery.*; +import static org.apache.sqoop.repository.derby.DerbySchemaCreateQuery.*; +import static org.apache.sqoop.repository.derby.DerbySchemaInsertUpdateDeleteSelectQuery.*; + import java.net.URL; import java.sql.Connection; http://git-wip-us.apache.org/repos/asf/sqoop/blob/3c7e3ea9/repository/repository-derby/src/main/java/org/apache/sqoop/repository/derby/DerbySchemaCreateQuery.java ---------------------------------------------------------------------- diff --git a/repository/repository-derby/src/main/java/org/apache/sqoop/repository/derby/DerbySchemaCreateQuery.java b/repository/repository-derby/src/main/java/org/apache/sqoop/repository/derby/DerbySchemaCreateQuery.java new file mode 100644 index 0000000..5920c34 --- /dev/null +++ b/repository/repository-derby/src/main/java/org/apache/sqoop/repository/derby/DerbySchemaCreateQuery.java @@ -0,0 +1,483 @@ +/** + * 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.sqoop.repository.derby; + +import static org.apache.sqoop.repository.derby.DerbySchemaConstants.*; + +/** + * DDL queries that create the Sqoop repository schema in Derby database. These + * queries create the following tables: + * <p> + * <strong>SQ_SYSTEM</strong>: Store for various state information + * + * <pre> + * +----------------------------+ + * | SQ_SYSTEM | + * +----------------------------+ + * | SQM_ID: BIGINT PK | + * | SQM_KEY: VARCHAR(64) | + * | SQM_VALUE: VARCHAR(64) | + * +----------------------------+ + * </pre> + * + * </p> + * <p> + * <strong>SQ_DIRECTION</strong>: Directions. + * <pre> + * +---------------------------------------+ + * | SQ_DIRECTION | + * +---------------------------------------+ + * | SQD_ID: BIGINT PK AUTO-GEN | + * | SQD_NAME: VARCHAR(64) |"FROM"|"TO" + * +---------------------------------------+ + * </pre> + * </p> + * <p> + * <strong>SQ_CONFIGURABLE</strong>: Configurable registration. + * + * <pre> + * +-----------------------------+ + * | SQ_CONFIGURABLE | + * +-----------------------------+ + * | SQC_ID: BIGINT PK AUTO-GEN | + * | SQC_NAME: VARCHAR(64) | + * | SQC_CLASS: VARCHAR(255) | + * | SQC_TYPE: VARCHAR(32) |"CONNECTOR"|"DRIVER" + * | SQC_VERSION: VARCHAR(64) | + * +-----------------------------+ + * </pre> + * </p> + * <p> + * <strong>SQ_CONNECTOR_DIRECTIONS</strong>: Connector directions. + * <pre> + * +------------------------------+ + * | SQ_CONNECTOR_DIRECTIONS | + * +------------------------------+ + * | SQCD_ID: BIGINT PK AUTO-GEN | + * | SQCD_CONNECTOR: BIGINT |FK SQCD_CONNECTOR(SQC_ID) + * | SQCD_DIRECTION: BIGINT |FK SQCD_DIRECTION(SQD_ID) + * +------------------------------+ + * </pre> + * + * </p> + * <p> + * <strong>SQ_CONFIG</strong>: Config details. + * + * <pre> + * +-------------------------------------+ + * | SQ_CONFIG | + * +-------------------------------------+ + * | SQ_CFG_ID: BIGINT PK AUTO-GEN | + * | SQ_CFG_CONNECTOR: BIGINT |FK SQ_CFG_CONNECTOR(SQC_ID),NULL for driver + * | SQ_CFG_NAME: VARCHAR(64) | + * | SQ_CFG_TYPE: VARCHAR(32) |"LINK"|"JOB" + * | SQ_CFG_INDEX: SMALLINT | + * +-------------------------------------+ + * </pre> + * </p> + * <p> + * <strong>SQ_CONFIG_DIRECTIONS</strong>: Connector directions. + * <pre> + * +------------------------------+ + * | SQ_CONNECTOR_DIRECTIONS | + * +------------------------------+ + * | SQCD_ID: BIGINT PK AUTO-GEN | + * | SQCD_CONFIG: BIGINT |FK SQCD_CONFIG(SQ_CFG_ID) + * | SQCD_DIRECTION: BIGINT |FK SQCD_DIRECTION(SQD_ID) + * +------------------------------+ + * </pre> + * + * </p> + * <p> + * <strong>SQ_INPUT</strong>: Input details + * + * <pre> + * +----------------------------+ + * | SQ_INPUT | + * +----------------------------+ + * | SQI_ID: BIGINT PK AUTO-GEN | + * | SQI_NAME: VARCHAR(64) | + * | SQI_CONFIG: BIGINT |FK SQ_CONFIG(SQ_CFG_ID) + * | SQI_INDEX: SMALLINT | + * | SQI_TYPE: VARCHAR(32) |"STRING"|"MAP" + * | SQI_STRMASK: BOOLEAN | + * | SQI_STRLENGTH: SMALLINT | + * | SQI_ENUMVALS: VARCHAR(100) | + * +----------------------------+ + * </pre> + * + * </p> + * <p> + * <strong>SQ_LINK</strong>: Stored links + * + * <pre> + * +--------------------------------+ + * | SQ_LINK | + * +--------------------------------+ + * | SQ_LNK_ID: BIGINT PK AUTO-GEN | + * | SQ_LNK_NAME: VARCHAR(64) | + * | SQ_LNK_CONNECTOR: BIGINT | FK SQ_CONNECTOR(SQC_ID) + * | SQ_LNK_CREATION_USER: VARCHAR(32) | + * | SQ_LNK_CREATION_DATE: TIMESTAMP | + * | SQ_LNK_UPDATE_USER: VARCHAR(32) | + * | SQ_LNK_UPDATE_DATE: TIMESTAMP | + * | SQ_LNK_ENABLED: BOOLEAN | + * +--------------------------------+ + * </pre> + * + * </p> + * <p> + * <strong>SQ_JOB</strong>: Stored jobs + * + * <pre> + * +--------------------------------+ + * | SQ_JOB | + * +--------------------------------+ + * | SQB_ID: BIGINT PK AUTO-GEN | + * | SQB_NAME: VARCHAR(64) | + * | SQB_FROM_LINK: BIGINT |FK SQ_LINK(SQ_LNK_ID) + * | SQB_TO_LINK: BIGINT |FK SQ_LINK(SQ_LNK_ID) + * | SQB_CREATION_USER: VARCHAR(32) | + * | SQB_CREATION_DATE: TIMESTAMP | + * | SQB_UPDATE_USER: VARCHAR(32) | + * | SQB_UPDATE_DATE: TIMESTAMP | + * | SQB_ENABLED: BOOLEAN | + * +--------------------------------+ + * </pre> + * + * </p> + * <p> + * <strong>SQ_LINK_INPUT</strong>: N:M relationship link and input + * + * <pre> + * +----------------------------+ + * | SQ_LINK_INPUT | + * +----------------------------+ + * | SQ_LNKI_LINK: BIGINT PK | FK SQ_LINK(SQ_LNK_ID) + * | SQ_LNKI_INPUT: BIGINT PK | FK SQ_INPUT(SQI_ID) + * | SQ_LNKI_VALUE: LONG VARCHAR| + * +----------------------------+ + * </pre> + * + * </p> + * <p> + * <strong>SQ_JOB_INPUT</strong>: N:M relationship job and input + * + * <pre> + * +----------------------------+ + * | SQ_JOB_INPUT | + * +----------------------------+ + * | SQBI_JOB: BIGINT PK | FK SQ_JOB(SQB_ID) + * | SQBI_INPUT: BIGINT PK | FK SQ_INPUT(SQI_ID) + * | SQBI_VALUE: LONG VARCHAR | + * +----------------------------+ + * </pre> + * + * </p> + * <p> + * <strong>SQ_SUBMISSION</strong>: List of submissions + * + * <pre> + * +-----------------------------------+ + * | SQ_JOB_SUBMISSION | + * +-----------------------------------+ + * | SQS_ID: BIGINT PK | + * | SQS_JOB: BIGINT | FK SQ_JOB(SQB_ID) + * | SQS_STATUS: VARCHAR(20) | + * | SQS_CREATION_USER: VARCHAR(32) | + * | SQS_CREATION_DATE: TIMESTAMP | + * | SQS_UPDATE_USER: VARCHAR(32) | + * | SQS_UPDATE_DATE: TIMESTAMP | + * | SQS_EXTERNAL_ID: VARCHAR(50) | + * | SQS_EXTERNAL_LINK: VARCHAR(150) | + * | SQS_EXCEPTION: VARCHAR(150) | + * | SQS_EXCEPTION_TRACE: VARCHAR(750) | + * +-----------------------------------+ + * </pre> + * + * </p> + * <p> + * <strong>SQ_COUNTER_GROUP</strong>: List of counter groups + * + * <pre> + * +----------------------------+ + * | SQ_COUNTER_GROUP | + * +----------------------------+ + * | SQG_ID: BIGINT PK | + * | SQG_NAME: VARCHAR(75) | + * +----------------------------+ + * </pre> + * + * </p> + * <p> + * <strong>SQ_COUNTER</strong>: List of counters + * + * <pre> + * +----------------------------+ + * | SQ_COUNTER | + * +----------------------------+ + * | SQR_ID: BIGINT PK | + * | SQR_NAME: VARCHAR(75) | + * +----------------------------+ + * </pre> + * + * </p> + * <p> + * <strong>SQ_COUNTER_SUBMISSION</strong>: N:M Relationship + * + * <pre> + * +----------------------------+ + * | SQ_COUNTER_SUBMISSION | + * +----------------------------+ + * | SQRS_GROUP: BIGINT PK | FK SQ_COUNTER_GROUP(SQR_ID) + * | SQRS_COUNTER: BIGINT PK | FK SQ_COUNTER(SQR_ID) + * | SQRS_SUBMISSION: BIGINT PK | FK SQ_SUBMISSION(SQS_ID) + * | SQRS_VALUE: BIGINT | + * +----------------------------+ + * </pre> + * + * </p> + */ + +// NOTE: If you have signed yourself to modify the schema for the repository +// such as a rename, change in table relationships or constraints, embrace yourself! +// The following code is supposed to be a chronological order of how the +// repository schema evolved. So do not ever change the following +// code directly. Instead make sure the upgrade queries are written to reflect +// the renames and changes in the table relationships or constraints +// It would have been nicer and much cleaner +// if this was not code but sql scripts. Having it in code it is very +// easy and tempting to rename or make changes to it and easily miss +// the upgrade code. Not to mention, make sure to +// the update the tests to use the upgrade queries as well +// Make sure to add a lot of comments to the upgrade code if there is an +// ordering dependency to help future contributors to not lose their sleep over +// enhancing this code +public final class DerbySchemaCreateQuery { + + /**************************** DERBY CREATE SCHEMA queries ********************************/ + // DDL: Create schema + public static final String QUERY_CREATE_SCHEMA_SQOOP = + "CREATE SCHEMA " + SCHEMA_SQOOP; + + public static final String QUERY_SYSSCHEMA_SQOOP = + "SELECT SCHEMAID FROM SYS.SYSSCHEMAS WHERE SCHEMANAME = '" + + SCHEMA_SQOOP + "'"; + + // DDL: Create table SQ_SYSTEM + public static final String QUERY_CREATE_TABLE_SQ_SYSTEM = + "CREATE TABLE " + TABLE_SQ_SYSTEM + " (" + + COLUMN_SQM_ID + " BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) PRIMARY KEY, " + + COLUMN_SQM_KEY + " VARCHAR(64), " + + COLUMN_SQM_VALUE + " VARCHAR(64) " + + ")"; + + // DDL: Create table SQ_DIRECTION + public static final String QUERY_CREATE_TABLE_SQ_DIRECTION = + "CREATE TABLE " + TABLE_SQ_DIRECTION + " (" + + COLUMN_SQD_ID + " BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) PRIMARY KEY, " + + COLUMN_SQD_NAME + " VARCHAR(64)" + + ")"; + + // DDL: Create table SQ_CONNECTOR + @Deprecated // used only for upgrade + public static final String QUERY_CREATE_TABLE_SQ_CONNECTOR = + "CREATE TABLE " + TABLE_SQ_CONNECTOR + " (" + + COLUMN_SQC_ID + " BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) PRIMARY KEY, " + + COLUMN_SQC_NAME + " VARCHAR(64), " + + COLUMN_SQC_CLASS + " VARCHAR(255), " + + COLUMN_SQC_VERSION + " VARCHAR(64) " + + ")"; + + // DDL: Create table SQ_CONNECTOR_DIRECTIONS + public static final String QUERY_CREATE_TABLE_SQ_CONNECTOR_DIRECTIONS = + "CREATE TABLE " + TABLE_SQ_CONNECTOR_DIRECTIONS + " (" + + COLUMN_SQCD_ID + " BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) PRIMARY KEY, " + + COLUMN_SQCD_CONNECTOR + " BIGINT, " + + COLUMN_SQCD_DIRECTION + " BIGINT, " + + "CONSTRAINT " + CONSTRAINT_SQCD_SQC + " " + + "FOREIGN KEY (" + COLUMN_SQCD_CONNECTOR + ") " + + "REFERENCES " + TABLE_SQ_CONNECTOR + " (" + COLUMN_SQC_ID + "), " + + "CONSTRAINT " + CONSTRAINT_SQCD_SQD + " " + + "FOREIGN KEY (" + COLUMN_SQCD_DIRECTION + ") " + + "REFERENCES " + TABLE_SQ_DIRECTION + " (" + COLUMN_SQD_ID + ")" + + ")"; + + // DDL: Create table SQ_FORM + public static final String QUERY_CREATE_TABLE_SQ_FORM = + "CREATE TABLE " + TABLE_SQ_FORM + " (" + + COLUMN_SQF_ID + " BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) PRIMARY KEY, " + + COLUMN_SQF_CONNECTOR + " BIGINT, " + + COLUMN_SQF_OPERATION + " VARCHAR(32), " + + COLUMN_SQF_NAME + " VARCHAR(64), " + + COLUMN_SQF_TYPE + " VARCHAR(32), " + + COLUMN_SQF_INDEX + " SMALLINT, " + + "CONSTRAINT " + CONSTRAINT_SQF_SQC + " " + + "FOREIGN KEY (" + COLUMN_SQF_CONNECTOR + ") " + + "REFERENCES " + TABLE_SQ_CONNECTOR + " (" + COLUMN_SQC_ID + ")" + + ")"; + + // DDL: Create table SQ_CONFIG_DIRECTIONS ( same as SQ_FORM_DIRECTIONS) + // Note: that the form was renamed to config at one point and this code was added after the rename + // DDL: Create table SQ_CONFIG_DIRECTIONS + public static final String QUERY_CREATE_TABLE_SQ_CONFIG_DIRECTIONS = + "CREATE TABLE " + TABLE_SQ_CONFIG_DIRECTIONS + " (" + + COLUMN_SQ_CFG_DIR_ID + " BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) PRIMARY KEY, " + + COLUMN_SQ_CFG_DIR_CONFIG + " BIGINT, " + + COLUMN_SQ_CFG_DIR_DIRECTION + " BIGINT, " + + "CONSTRAINT " + CONSTRAINT_SQ_CFG_DIR_CONFIG + " " + + "FOREIGN KEY (" + COLUMN_SQ_CFG_DIR_CONFIG + ") " + + "REFERENCES " + TABLE_SQ_CONFIG + " (" + COLUMN_SQ_CFG_ID + "), " + + "CONSTRAINT " + CONSTRAINT_SQ_CFG_DIR_DIRECTION + " " + + "FOREIGN KEY (" + COLUMN_SQ_CFG_DIR_DIRECTION + ") " + + "REFERENCES " + TABLE_SQ_DIRECTION + " (" + COLUMN_SQD_ID + ")" + + ")"; + + + // DDL: Create table SQ_INPUT + public static final String QUERY_CREATE_TABLE_SQ_INPUT = + "CREATE TABLE " + TABLE_SQ_INPUT + " (" + + COLUMN_SQI_ID + " BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) PRIMARY KEY, " + + COLUMN_SQI_NAME + " VARCHAR(64), " + + COLUMN_SQI_FORM + " BIGINT, " + + COLUMN_SQI_INDEX + " SMALLINT, " + + COLUMN_SQI_TYPE + " VARCHAR(32), " + + COLUMN_SQI_STRMASK + " BOOLEAN, " + + COLUMN_SQI_STRLENGTH + " SMALLINT, " + + COLUMN_SQI_ENUMVALS + " VARCHAR(100)," + + "CONSTRAINT " + CONSTRAINT_SQI_SQF + " " + + "FOREIGN KEY (" + COLUMN_SQI_FORM + ") " + + "REFERENCES " + TABLE_SQ_FORM + " (" + COLUMN_SQF_ID + ")" + + ")"; + + // DDL: Create table SQ_CONNECTION + public static final String QUERY_CREATE_TABLE_SQ_CONNECTION = + "CREATE TABLE " + TABLE_SQ_CONNECTION + " (" + + COLUMN_SQN_ID + " BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) PRIMARY KEY, " + + COLUMN_SQN_CONNECTOR + " BIGINT, " + + COLUMN_SQN_NAME + " VARCHAR(32)," + + COLUMN_SQN_CREATION_DATE + " TIMESTAMP," + + COLUMN_SQN_UPDATE_DATE + " TIMESTAMP," + + "CONSTRAINT " + CONSTRAINT_SQN_SQC + " " + + "FOREIGN KEY(" + COLUMN_SQN_CONNECTOR + ") " + + " REFERENCES " + TABLE_SQ_CONNECTOR + " (" + COLUMN_SQC_ID + ")" + + ")"; +//DDL: Create table SQ_JOB + public static final String QUERY_CREATE_TABLE_SQ_JOB = + "CREATE TABLE " + TABLE_SQ_JOB + " (" + + COLUMN_SQB_ID + " BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) PRIMARY KEY, " + + COLUMN_SQB_CONNECTION + " BIGINT, " + + COLUMN_SQB_NAME + " VARCHAR(64), " + + COLUMN_SQB_TYPE + " VARCHAR(64)," + + COLUMN_SQB_CREATION_DATE + " TIMESTAMP," + + COLUMN_SQB_UPDATE_DATE + " TIMESTAMP," + + "CONSTRAINT " + CONSTRAINT_SQB_SQN + " " + + "FOREIGN KEY(" + COLUMN_SQB_CONNECTION + ") " + + "REFERENCES " + TABLE_SQ_CONNECTION + " (" + COLUMN_SQN_ID + ")" + + ")"; + + + // DDL: Create table SQ_CONNECTION_INPUT + public static final String QUERY_CREATE_TABLE_SQ_CONNECTION_INPUT = + "CREATE TABLE " + TABLE_SQ_CONNECTION_INPUT + " (" + + COLUMN_SQNI_CONNECTION + " BIGINT, " + + COLUMN_SQNI_INPUT + " BIGINT, " + + COLUMN_SQNI_VALUE + " LONG VARCHAR," + + "PRIMARY KEY (" + COLUMN_SQNI_CONNECTION + ", " + COLUMN_SQNI_INPUT + "), " + + "CONSTRAINT " + CONSTRAINT_SQNI_SQN + " " + + "FOREIGN KEY (" + COLUMN_SQNI_CONNECTION + ") " + + "REFERENCES " + TABLE_SQ_CONNECTION + " (" + COLUMN_SQN_ID + ")," + + "CONSTRAINT " + CONSTRAINT_SQNI_SQI + " " + + "FOREIGN KEY (" + COLUMN_SQNI_INPUT + ") " + + "REFERENCES " + TABLE_SQ_INPUT + " (" + COLUMN_SQI_ID + ")" + + ")"; + +// DDL: Create table SQ_JOB_INPUT + public static final String QUERY_CREATE_TABLE_SQ_JOB_INPUT = + "CREATE TABLE " + TABLE_SQ_JOB_INPUT + " (" + + COLUMN_SQBI_JOB + " BIGINT, " + + COLUMN_SQBI_INPUT + " BIGINT, " + + COLUMN_SQBI_VALUE + " LONG VARCHAR," + + " PRIMARY KEY (" + COLUMN_SQBI_JOB + ", " + COLUMN_SQBI_INPUT + "), " + + " CONSTRAINT " + CONSTRAINT_SQBI_SQB + " " + + "FOREIGN KEY (" + COLUMN_SQBI_JOB + ") " + + "REFERENCES " + TABLE_SQ_JOB + " (" + COLUMN_SQB_ID + "), " + + " CONSTRAINT " + CONSTRAINT_SQBI_SQI + " " + + "FOREIGN KEY (" + COLUMN_SQBI_INPUT + ") " + + "REFERENCES " + TABLE_SQ_INPUT + " (" + COLUMN_SQI_ID + ")" + + ")"; + + // DDL: Create table SQ_SUBMISSION + public static final String QUERY_CREATE_TABLE_SQ_SUBMISSION = + "CREATE TABLE " + TABLE_SQ_SUBMISSION + " (" + + COLUMN_SQS_ID + " BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), " + + COLUMN_SQS_JOB + " BIGINT, " + + COLUMN_SQS_STATUS + " VARCHAR(20), " + + COLUMN_SQS_CREATION_DATE + " TIMESTAMP," + + COLUMN_SQS_UPDATE_DATE + " TIMESTAMP," + + COLUMN_SQS_EXTERNAL_ID + " VARCHAR(50), " + + COLUMN_SQS_EXTERNAL_LINK + " VARCHAR(150), " + + COLUMN_SQS_EXCEPTION + " VARCHAR(150), " + + COLUMN_SQS_EXCEPTION_TRACE + " VARCHAR(750), " + + "PRIMARY KEY (" + COLUMN_SQS_ID + "), " + + "CONSTRAINT " + CONSTRAINT_SQS_SQB + " " + + "FOREIGN KEY (" + COLUMN_SQS_JOB + ") " + + "REFERENCES " + TABLE_SQ_JOB + "(" + COLUMN_SQB_ID + ") ON DELETE CASCADE" + + ")"; + + // DDL: Create table SQ_COUNTER_GROUP + public static final String QUERY_CREATE_TABLE_SQ_COUNTER_GROUP = + "CREATE TABLE " + TABLE_SQ_COUNTER_GROUP + " (" + + COLUMN_SQG_ID + " BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), " + + COLUMN_SQG_NAME + " VARCHAR(75), " + + "PRIMARY KEY (" + COLUMN_SQG_ID + ")," + + "UNIQUE ( " + COLUMN_SQG_NAME + ")" + + ")"; + + // DDL: Create table SQ_COUNTER + public static final String QUERY_CREATE_TABLE_SQ_COUNTER = + "CREATE TABLE " + TABLE_SQ_COUNTER + " (" + + COLUMN_SQR_ID + " BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), " + + COLUMN_SQR_NAME + " VARCHAR(75), " + + "PRIMARY KEY (" + COLUMN_SQR_ID + "), " + + "UNIQUE ( " + COLUMN_SQR_NAME + ")" + + ")"; + + // DDL: Create table SQ_COUNTER_SUBMISSION + public static final String QUERY_CREATE_TABLE_SQ_COUNTER_SUBMISSION = + "CREATE TABLE " + TABLE_SQ_COUNTER_SUBMISSION + " (" + + COLUMN_SQRS_GROUP + " BIGINT, " + + COLUMN_SQRS_COUNTER + " BIGINT, " + + COLUMN_SQRS_SUBMISSION + " BIGINT, " + + COLUMN_SQRS_VALUE + " BIGINT, " + + "PRIMARY KEY (" + COLUMN_SQRS_GROUP + ", " + COLUMN_SQRS_COUNTER + ", " + COLUMN_SQRS_SUBMISSION + "), " + + "CONSTRAINT " + CONSTRAINT_SQRS_SQG + " " + + "FOREIGN KEY (" + COLUMN_SQRS_GROUP + ") " + + "REFERENCES " + TABLE_SQ_COUNTER_GROUP + "(" + COLUMN_SQG_ID + "), " + + "CONSTRAINT " + CONSTRAINT_SQRS_SQR + " " + + "FOREIGN KEY (" + COLUMN_SQRS_COUNTER + ") " + + "REFERENCES " + TABLE_SQ_COUNTER + "(" + COLUMN_SQR_ID + "), " + + "CONSTRAINT " + CONSTRAINT_SQRS_SQS + " " + + "FOREIGN KEY (" + COLUMN_SQRS_SUBMISSION + ") " + + "REFERENCES " + TABLE_SQ_SUBMISSION + "(" + COLUMN_SQS_ID + ") ON DELETE CASCADE " + + ")"; + + private DerbySchemaCreateQuery() { + + } +} \ No newline at end of file http://git-wip-us.apache.org/repos/asf/sqoop/blob/3c7e3ea9/repository/repository-derby/src/main/java/org/apache/sqoop/repository/derby/DerbySchemaInsertUpdateDeleteSelectQuery.java ---------------------------------------------------------------------- diff --git a/repository/repository-derby/src/main/java/org/apache/sqoop/repository/derby/DerbySchemaInsertUpdateDeleteSelectQuery.java b/repository/repository-derby/src/main/java/org/apache/sqoop/repository/derby/DerbySchemaInsertUpdateDeleteSelectQuery.java new file mode 100644 index 0000000..02b11fc --- /dev/null +++ b/repository/repository-derby/src/main/java/org/apache/sqoop/repository/derby/DerbySchemaInsertUpdateDeleteSelectQuery.java @@ -0,0 +1,617 @@ +/** + * 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.sqoop.repository.derby; + +import static org.apache.sqoop.repository.derby.DerbySchemaConstants.*; + +/** + * Derby Repository Insert/ Update/ Delete / Select queries + * + */ +public final class DerbySchemaInsertUpdateDeleteSelectQuery { + +/******** SYSTEM TABLE**************/ + // DML: Get system key + public static final String STMT_SELECT_SYSTEM = + "SELECT " + + COLUMN_SQM_VALUE + + " FROM " + TABLE_SQ_SYSTEM + + " WHERE " + COLUMN_SQM_KEY + " = ?"; + + //DML: Get deprecated or the new repo version system key + public static final String STMT_SELECT_DEPRECATED_OR_NEW_SYSTEM_VERSION = + "SELECT " + + COLUMN_SQM_VALUE + " FROM " + TABLE_SQ_SYSTEM + + " WHERE ( " + COLUMN_SQM_KEY + " = ? )" + + " OR (" + COLUMN_SQM_KEY + " = ? )"; + + // DML: Remove system key + public static final String STMT_DELETE_SYSTEM = + "DELETE FROM " + TABLE_SQ_SYSTEM + + " WHERE " + COLUMN_SQM_KEY + " = ?"; + + // DML: Insert new system key + public static final String STMT_INSERT_SYSTEM = + "INSERT INTO " + TABLE_SQ_SYSTEM + "(" + + COLUMN_SQM_KEY + ", " + + COLUMN_SQM_VALUE + ") " + + "VALUES(?, ?)"; + + /*******DIRECTION TABLE **************/ + + public static final String STMT_SELECT_SQD_ID_BY_SQD_NAME = + "SELECT " + COLUMN_SQD_ID + " FROM " + TABLE_SQ_DIRECTION + + " WHERE " + COLUMN_SQD_NAME + "=?"; + + public static final String STMT_SELECT_SQD_NAME_BY_SQD_ID = + "SELECT " + COLUMN_SQD_NAME + " FROM " + TABLE_SQ_DIRECTION + + " WHERE " + COLUMN_SQD_ID + "=?"; + +/*********CONFIGURABLE TABLE ***************/ + //DML: Get configurable by given name + public static final String STMT_SELECT_FROM_CONFIGURABLE = + "SELECT " + + COLUMN_SQC_ID + ", " + + COLUMN_SQC_NAME + ", " + + COLUMN_SQC_CLASS + ", " + + COLUMN_SQC_VERSION + + " FROM " + TABLE_SQ_CONFIGURABLE + + " WHERE " + COLUMN_SQC_NAME + " = ?"; + + //DML: Get all configurables for a given type + public static final String STMT_SELECT_CONFIGURABLE_ALL_FOR_TYPE = + "SELECT " + + COLUMN_SQC_ID + ", " + + COLUMN_SQC_NAME + ", " + + COLUMN_SQC_CLASS + ", " + + COLUMN_SQC_VERSION + + " FROM " + TABLE_SQ_CONFIGURABLE + + " WHERE " + COLUMN_SQC_TYPE + " = ?"; + + // DML: Select all connectors + @Deprecated // used only for upgrade logic + public static final String STMT_SELECT_CONNECTOR_ALL = + "SELECT " + + COLUMN_SQC_ID + ", " + + COLUMN_SQC_NAME + ", " + + COLUMN_SQC_CLASS + ", " + + COLUMN_SQC_VERSION + + " FROM " + TABLE_SQ_CONNECTOR; + + //DML: Insert into configurable + public static final String STMT_INSERT_INTO_CONFIGURABLE = + "INSERT INTO " + TABLE_SQ_CONFIGURABLE + " (" + + COLUMN_SQC_NAME + ", " + + COLUMN_SQC_CLASS + ", " + + COLUMN_SQC_VERSION + ", " + + COLUMN_SQC_TYPE + + ") VALUES (?, ?, ?, ?)"; + + @Deprecated // used only in the upgrade path + public static final String STMT_INSERT_INTO_CONFIGURABLE_WITHOUT_SUPPORTED_DIRECTIONS = + "INSERT INTO " + TABLE_SQ_CONNECTOR+ " (" + + COLUMN_SQC_NAME + ", " + + COLUMN_SQC_CLASS + ", " + + COLUMN_SQC_VERSION + + ") VALUES (?, ?, ?)"; + //Delete all configs for a given configurable + public static final String STMT_DELETE_CONFIGS_FOR_CONFIGURABLE = + "DELETE FROM " + TABLE_SQ_CONFIG + + " WHERE " + COLUMN_SQ_CFG_CONFIGURABLE + " = ?"; + + //Delete all inputs for a given configurable + public static final String STMT_DELETE_INPUTS_FOR_CONFIGURABLE = + "DELETE FROM " + TABLE_SQ_INPUT + + " WHERE " + + COLUMN_SQI_CONFIG + + " IN (SELECT " + + COLUMN_SQ_CFG_ID + + " FROM " + TABLE_SQ_CONFIG + + " WHERE " + + COLUMN_SQ_CFG_CONFIGURABLE + " = ?)"; + + //Update the configurable + public static final String STMT_UPDATE_CONFIGURABLE = + "UPDATE " + TABLE_SQ_CONFIGURABLE + + " SET " + COLUMN_SQC_NAME + " = ?, " + + COLUMN_SQC_CLASS + " = ?, " + + COLUMN_SQC_VERSION + " = ?, " + + COLUMN_SQC_TYPE + " = ? " + + " WHERE " + COLUMN_SQC_ID + " = ?"; + + //DML: Insert new connection + @Deprecated // used only in upgrade path + public static final String STMT_INSERT_CONNECTION = + "INSERT INTO " + TABLE_SQ_CONNECTION + " (" + + COLUMN_SQN_NAME + ", " + + COLUMN_SQN_CONNECTOR + "," + + COLUMN_SQN_ENABLED + ", " + + COLUMN_SQN_CREATION_USER + ", " + + COLUMN_SQN_CREATION_DATE + ", " + + COLUMN_SQN_UPDATE_USER + ", " + COLUMN_SQN_UPDATE_DATE + + ") VALUES (?, ?, ?, ?, ?, ?, ?)"; + + + /**********CONFIG TABLE **************/ + //DML: Get all configs for a given configurable + public static final String STMT_SELECT_CONFIG_FOR_CONFIGURABLE = + "SELECT " + + COLUMN_SQ_CFG_ID + ", " + + COLUMN_SQ_CFG_CONFIGURABLE + ", " + + COLUMN_SQ_CFG_NAME + ", " + + COLUMN_SQ_CFG_TYPE + ", " + + COLUMN_SQ_CFG_INDEX + + " FROM " + TABLE_SQ_CONFIG + + " WHERE " + COLUMN_SQ_CFG_CONFIGURABLE + " = ? " + + " ORDER BY " + COLUMN_SQ_CFG_INDEX; + + + //DML: Insert into config + public static final String STMT_INSERT_INTO_CONFIG = + "INSERT INTO " + TABLE_SQ_CONFIG + " (" + + COLUMN_SQ_CFG_CONFIGURABLE + ", " + + COLUMN_SQ_CFG_NAME + ", " + + COLUMN_SQ_CFG_TYPE + ", " + + COLUMN_SQ_CFG_INDEX + + ") VALUES ( ?, ?, ?, ?)"; + + /********** INPUT TABLE **************/ + + // DML: Get inputs for a given config + public static final String STMT_SELECT_INPUT = + "SELECT " + + COLUMN_SQI_ID + ", " + + COLUMN_SQI_NAME + ", " + + COLUMN_SQI_CONFIG + ", " + + COLUMN_SQI_INDEX + ", " + + COLUMN_SQI_TYPE + ", " + + COLUMN_SQI_STRMASK + ", " + + COLUMN_SQI_STRLENGTH + ", " + + COLUMN_SQI_ENUMVALS + ", " + + "cast(null as varchar(100))" + + " FROM " + TABLE_SQ_INPUT + + " WHERE " + COLUMN_SQI_CONFIG + " = ?" + + " ORDER BY " + COLUMN_SQI_INDEX; + + // DML: Insert into config input + public static final String STMT_INSERT_INTO_INPUT = + "INSERT INTO " + TABLE_SQ_INPUT + " (" + + COLUMN_SQI_NAME + ", " + + COLUMN_SQI_CONFIG + ", " + + COLUMN_SQI_INDEX + ", " + + COLUMN_SQI_TYPE + ", " + + COLUMN_SQI_STRMASK + ", " + + COLUMN_SQI_STRLENGTH + ", " + + COLUMN_SQI_ENUMVALS + + ") VALUES (?, ?, ?, ?, ?, ?, ?)"; + + /**********LINK INPUT TABLE **************/ + + //DML: Get inputs and values for a given link + public static final String STMT_FETCH_LINK_INPUT = + "SELECT " + + COLUMN_SQI_ID + ", " + + COLUMN_SQI_NAME + ", " + + COLUMN_SQI_CONFIG + ", " + + COLUMN_SQI_INDEX + ", " + + COLUMN_SQI_TYPE + ", " + + COLUMN_SQI_STRMASK + ", " + + COLUMN_SQI_STRLENGTH + "," + + COLUMN_SQI_ENUMVALS + ", " + + COLUMN_SQ_LNKI_VALUE + + " FROM " + TABLE_SQ_INPUT + + " LEFT OUTER JOIN " + TABLE_SQ_LINK_INPUT + + " ON " + COLUMN_SQ_LNKI_INPUT + " = " + COLUMN_SQI_ID + + " AND " + COLUMN_SQ_LNKI_LINK + " = ?" + + " WHERE " + COLUMN_SQI_CONFIG + " = ?" + + " AND (" + COLUMN_SQ_LNKI_LINK + " = ?" + " OR " + COLUMN_SQ_LNKI_LINK + " IS NULL)" + + " ORDER BY " + COLUMN_SQI_INDEX; + + /**********JOB INPUT TABLE **************/ + + //DML: Fetch inputs and values for a given job + public static final String STMT_FETCH_JOB_INPUT = + "SELECT " + + COLUMN_SQI_ID + ", " + + COLUMN_SQI_NAME + ", " + + COLUMN_SQI_CONFIG + ", " + + COLUMN_SQI_INDEX + ", " + + COLUMN_SQI_TYPE + ", " + + COLUMN_SQI_STRMASK + ", " + + COLUMN_SQI_STRLENGTH + ", " + + COLUMN_SQI_ENUMVALS + ", " + + COLUMN_SQBI_VALUE + + " FROM " + TABLE_SQ_INPUT + + " LEFT OUTER JOIN " + TABLE_SQ_JOB_INPUT + + " ON " + COLUMN_SQBI_INPUT + " = " + COLUMN_SQI_ID + + " AND " + COLUMN_SQBI_JOB + " = ?" + + " WHERE " + COLUMN_SQI_CONFIG + " = ?" + + " AND (" + COLUMN_SQBI_JOB + " = ? OR " + COLUMN_SQBI_JOB + " IS NULL)" + + " ORDER BY " + COLUMN_SQI_INDEX; + + /**********LINK TABLE **************/ + + // DML: Insert new link + public static final String STMT_INSERT_LINK = + "INSERT INTO " + TABLE_SQ_LINK + " (" + + COLUMN_SQ_LNK_NAME + ", " + + COLUMN_SQ_LNK_CONFIGURABLE + ", " + + COLUMN_SQ_LNK_ENABLED + ", " + + COLUMN_SQ_LNK_CREATION_USER + ", " + + COLUMN_SQ_LNK_CREATION_DATE + ", " + + COLUMN_SQ_LNK_UPDATE_USER + ", " + + COLUMN_SQ_LNK_UPDATE_DATE + + ") VALUES (?, ?, ?, ?, ?, ?, ?)"; + + // DML: Insert new link inputs + public static final String STMT_INSERT_LINK_INPUT = + "INSERT INTO " + TABLE_SQ_LINK_INPUT + " (" + + COLUMN_SQ_LNKI_LINK + ", " + + COLUMN_SQ_LNKI_INPUT + ", " + + COLUMN_SQ_LNKI_VALUE + + ") VALUES (?, ?, ?)"; + + // DML: Update link + public static final String STMT_UPDATE_LINK = + "UPDATE " + TABLE_SQ_LINK + " SET " + + COLUMN_SQ_LNK_NAME + " = ?, " + + COLUMN_SQ_LNK_UPDATE_USER + " = ?, " + + COLUMN_SQ_LNK_UPDATE_DATE + " = ? " + + " WHERE " + COLUMN_SQ_LNK_ID + " = ?"; + + // DML: Enable or disable link + public static final String STMT_ENABLE_LINK = + "UPDATE " + TABLE_SQ_LINK + " SET " + + COLUMN_SQ_LNK_ENABLED + " = ? " + + " WHERE " + COLUMN_SQ_LNK_ID + " = ?"; + + // DML: Delete rows from link input table + public static final String STMT_DELETE_LINK_INPUT = + "DELETE FROM " + TABLE_SQ_LINK_INPUT + + " WHERE " + COLUMN_SQ_LNKI_LINK + " = ?"; + + // DML: Delete row from link table + public static final String STMT_DELETE_LINK = + "DELETE FROM " + TABLE_SQ_LINK + + " WHERE " + COLUMN_SQ_LNK_ID + " = ?"; + + // DML: Select one specific link + public static final String STMT_SELECT_LINK_SINGLE = + "SELECT " + + COLUMN_SQ_LNK_ID + ", " + + COLUMN_SQ_LNK_NAME + ", " + + COLUMN_SQ_LNK_CONFIGURABLE + ", " + + COLUMN_SQ_LNK_ENABLED + ", " + + COLUMN_SQ_LNK_CREATION_USER + ", " + + COLUMN_SQ_LNK_CREATION_DATE + ", " + + COLUMN_SQ_LNK_UPDATE_USER + ", " + + COLUMN_SQ_LNK_UPDATE_DATE + + " FROM " + TABLE_SQ_LINK + + " WHERE " + COLUMN_SQ_LNK_ID + " = ?"; + + // DML: Select all links + public static final String STMT_SELECT_LINK_ALL = + "SELECT " + + COLUMN_SQ_LNK_ID + ", " + + COLUMN_SQ_LNK_NAME + ", " + + COLUMN_SQ_LNK_CONFIGURABLE + ", " + + COLUMN_SQ_LNK_ENABLED + ", " + + COLUMN_SQ_LNK_CREATION_USER + ", " + + COLUMN_SQ_LNK_CREATION_DATE + ", " + + COLUMN_SQ_LNK_UPDATE_USER + ", " + + COLUMN_SQ_LNK_UPDATE_DATE + + " FROM " + TABLE_SQ_LINK; + + // DML: Select all links for a specific connector. + public static final String STMT_SELECT_LINK_FOR_CONNECTOR_CONFIGURABLE = + "SELECT " + + COLUMN_SQ_LNK_ID + ", " + + COLUMN_SQ_LNK_NAME + ", " + + COLUMN_SQ_LNK_CONFIGURABLE + ", " + + COLUMN_SQ_LNK_ENABLED + ", " + + COLUMN_SQ_LNK_CREATION_USER + ", " + + COLUMN_SQ_LNK_CREATION_DATE + ", " + + COLUMN_SQ_LNK_UPDATE_USER + ", " + + COLUMN_SQ_LNK_UPDATE_DATE + + " FROM " + TABLE_SQ_LINK + + " WHERE " + COLUMN_SQ_LNK_CONFIGURABLE + " = ?"; + + // DML: Check if given link exists + public static final String STMT_SELECT_LINK_CHECK_BY_ID = + "SELECT count(*) FROM " + TABLE_SQ_LINK + + " WHERE " + COLUMN_SQ_LNK_ID + " = ?"; + + /**********JOB TABLE **************/ + + // DML: Insert new job + public static final String STMT_INSERT_JOB = + "INSERT INTO " + TABLE_SQ_JOB + " (" + + COLUMN_SQB_NAME + ", " + + COLUMN_SQB_FROM_LINK + ", " + + COLUMN_SQB_TO_LINK + ", " + + COLUMN_SQB_ENABLED + ", " + + COLUMN_SQB_CREATION_USER + ", " + + COLUMN_SQB_CREATION_DATE + ", " + + COLUMN_SQB_UPDATE_USER + ", " + + COLUMN_SQB_UPDATE_DATE + + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?)"; + + // DML: Insert new job inputs + public static final String STMT_INSERT_JOB_INPUT = + "INSERT INTO " + TABLE_SQ_JOB_INPUT + " (" + + COLUMN_SQBI_JOB + ", " + + COLUMN_SQBI_INPUT + ", " + + COLUMN_SQBI_VALUE + + ") VALUES (?, ?, ?)"; + + public static final String STMT_UPDATE_JOB = + "UPDATE " + TABLE_SQ_JOB + " SET " + + COLUMN_SQB_NAME + " = ?, " + + COLUMN_SQB_UPDATE_USER + " = ?, " + + COLUMN_SQB_UPDATE_DATE + " = ? " + + " WHERE " + COLUMN_SQB_ID + " = ?"; + + // DML: Enable or disable job + public static final String STMT_ENABLE_JOB = + "UPDATE " + TABLE_SQ_JOB + " SET " + + COLUMN_SQB_ENABLED + " = ? " + + " WHERE " + COLUMN_SQB_ID + " = ?"; + + // DML: Delete rows from job input table + public static final String STMT_DELETE_JOB_INPUT = + "DELETE FROM " + TABLE_SQ_JOB_INPUT + + " WHERE " + COLUMN_SQBI_JOB + " = ?"; + + // DML: Delete row from job table + public static final String STMT_DELETE_JOB = + "DELETE FROM " + TABLE_SQ_JOB + + " WHERE " + COLUMN_SQB_ID + " = ?"; + + // DML: Check if given job exists + public static final String STMT_SELECT_JOB_CHECK_BY_ID = + "SELECT count(*) FROM " + TABLE_SQ_JOB + + " WHERE " + COLUMN_SQB_ID + " = ?"; + + // DML: Check if there are jobs for given link + public static final String STMT_SELECT_JOBS_FOR_LINK_CHECK = + "SELECT" + + " count(*)" + + " FROM " + TABLE_SQ_JOB + + " JOIN " + TABLE_SQ_LINK + + " ON " + COLUMN_SQB_FROM_LINK + " = " + COLUMN_SQ_LNK_ID + + " WHERE " + COLUMN_SQ_LNK_ID + " = ? "; + + //DML: Select all jobs + public static final String STMT_SELECT_JOB = + "SELECT " + + "FROM_CONNECTOR." + COLUMN_SQ_LNK_CONFIGURABLE + ", " + + "TO_CONNECTOR." + COLUMN_SQ_LNK_CONFIGURABLE + ", " + + "JOB." + COLUMN_SQB_ID + ", " + + "JOB." + COLUMN_SQB_NAME + ", " + + "JOB." + COLUMN_SQB_FROM_LINK + ", " + + "JOB." + COLUMN_SQB_TO_LINK + ", " + + "JOB." + COLUMN_SQB_ENABLED + ", " + + "JOB." + COLUMN_SQB_CREATION_USER + ", " + + "JOB." + COLUMN_SQB_CREATION_DATE + ", " + + "JOB." + COLUMN_SQB_UPDATE_USER + ", " + + "JOB." + COLUMN_SQB_UPDATE_DATE + + " FROM " + TABLE_SQ_JOB + " JOB" + + " LEFT JOIN " + TABLE_SQ_LINK + " FROM_CONNECTOR" + + " ON " + COLUMN_SQB_FROM_LINK + " = FROM_CONNECTOR." + COLUMN_SQ_LNK_ID + + " LEFT JOIN " + TABLE_SQ_LINK + " TO_CONNECTOR" + + " ON " + COLUMN_SQB_TO_LINK + " = TO_CONNECTOR." + COLUMN_SQ_LNK_ID; + + // DML: Select one specific job + public static final String STMT_SELECT_JOB_SINGLE_BY_ID = + STMT_SELECT_JOB + " WHERE " + COLUMN_SQB_ID + " = ?"; + + // DML: Select all jobs for a Connector + public static final String STMT_SELECT_ALL_JOBS_FOR_CONNECTOR_CONFIGURABLE = + STMT_SELECT_JOB + + " WHERE FROM_LINK." + COLUMN_SQ_LNK_CONFIGURABLE + " = ? OR TO_LINK." + + COLUMN_SQ_LNK_CONFIGURABLE + " = ?"; + + /**********SUBMISSION TABLE **************/ + + // DML: Insert new submission + public static final String STMT_INSERT_SUBMISSION = + "INSERT INTO " + TABLE_SQ_SUBMISSION + "(" + + COLUMN_SQS_JOB + ", " + + COLUMN_SQS_STATUS + ", " + + COLUMN_SQS_CREATION_USER + ", " + + COLUMN_SQS_CREATION_DATE + ", " + + COLUMN_SQS_UPDATE_USER + ", " + + COLUMN_SQS_UPDATE_DATE + ", " + + COLUMN_SQS_EXTERNAL_ID + ", " + + COLUMN_SQS_EXTERNAL_LINK + ", " + + COLUMN_SQS_EXCEPTION + ", " + + COLUMN_SQS_EXCEPTION_TRACE + ") " + + " VALUES(?, ?, ?, ?, ?, ?, ?, substr(?, 1, 150) , substr(?, 1, 150), substr(?, 1, 750))"; + + // DML: Update existing submission + public static final String STMT_UPDATE_SUBMISSION = + "UPDATE " + TABLE_SQ_SUBMISSION + " SET " + + COLUMN_SQS_STATUS + " = ?, " + + COLUMN_SQS_UPDATE_USER + " = ?, " + + COLUMN_SQS_UPDATE_DATE + " = ?, " + + COLUMN_SQS_EXCEPTION + " = ?, " + + COLUMN_SQS_EXCEPTION_TRACE + " = ?" + + " WHERE " + COLUMN_SQS_ID + " = ?"; + + // DML: Check if given submission exists + public static final String STMT_SELECT_SUBMISSION_CHECK = + "SELECT" + + " count(*)" + + " FROM " + TABLE_SQ_SUBMISSION + + " WHERE " + COLUMN_SQS_ID + " = ?"; + + // DML: Purge old entries + public static final String STMT_PURGE_SUBMISSIONS = + "DELETE FROM " + TABLE_SQ_SUBMISSION + + " WHERE " + COLUMN_SQS_UPDATE_DATE + " < ?"; + + // DML: Get unfinished + public static final String STMT_SELECT_SUBMISSION_UNFINISHED = + "SELECT " + + COLUMN_SQS_ID + ", " + + COLUMN_SQS_JOB + ", " + + COLUMN_SQS_STATUS + ", " + + COLUMN_SQS_CREATION_USER + ", " + + COLUMN_SQS_CREATION_DATE + ", " + + COLUMN_SQS_UPDATE_USER + ", " + + COLUMN_SQS_UPDATE_DATE + ", " + + COLUMN_SQS_EXTERNAL_ID + ", " + + COLUMN_SQS_EXTERNAL_LINK + ", " + + COLUMN_SQS_EXCEPTION + ", " + + COLUMN_SQS_EXCEPTION_TRACE + + " FROM " + TABLE_SQ_SUBMISSION + + " WHERE " + COLUMN_SQS_STATUS + " = ?"; + + // DML : Get all submissions + public static final String STMT_SELECT_SUBMISSIONS = + "SELECT " + + COLUMN_SQS_ID + ", " + + COLUMN_SQS_JOB + ", " + + COLUMN_SQS_STATUS + ", " + + COLUMN_SQS_CREATION_USER + ", " + + COLUMN_SQS_CREATION_DATE + ", " + + COLUMN_SQS_UPDATE_USER + ", " + + COLUMN_SQS_UPDATE_DATE + ", " + + COLUMN_SQS_EXTERNAL_ID + ", " + + COLUMN_SQS_EXTERNAL_LINK + ", " + + COLUMN_SQS_EXCEPTION + ", " + + COLUMN_SQS_EXCEPTION_TRACE + + " FROM " + TABLE_SQ_SUBMISSION + + " ORDER BY " + COLUMN_SQS_UPDATE_DATE + " DESC"; + + // DML: Get submissions for a job + public static final String STMT_SELECT_SUBMISSIONS_FOR_JOB = + "SELECT " + + COLUMN_SQS_ID + ", " + + COLUMN_SQS_JOB + ", " + + COLUMN_SQS_STATUS + ", " + + COLUMN_SQS_CREATION_USER + ", " + + COLUMN_SQS_CREATION_DATE + ", " + + COLUMN_SQS_UPDATE_USER + ", " + + COLUMN_SQS_UPDATE_DATE + ", " + + COLUMN_SQS_EXTERNAL_ID + ", " + + COLUMN_SQS_EXTERNAL_LINK + ", " + + COLUMN_SQS_EXCEPTION + ", " + + COLUMN_SQS_EXCEPTION_TRACE + + " FROM " + TABLE_SQ_SUBMISSION + + " WHERE " + COLUMN_SQS_JOB + " = ?" + + " ORDER BY " + COLUMN_SQS_UPDATE_DATE + " DESC"; + + // DML: Select counter group + public static final String STMT_SELECT_COUNTER_GROUP = + "SELECT " + + COLUMN_SQG_ID + ", " + + COLUMN_SQG_NAME + " " + + "FROM " + TABLE_SQ_COUNTER_GROUP + " " + + "WHERE " + COLUMN_SQG_NAME + " = substr(?, 1, 75)"; + + // DML: Insert new counter group + public static final String STMT_INSERT_COUNTER_GROUP = + "INSERT INTO " + TABLE_SQ_COUNTER_GROUP + " (" + + COLUMN_SQG_NAME + ") " + + "VALUES (substr(?, 1, 75))"; + + // DML: Select counter + public static final String STMT_SELECT_COUNTER = + "SELECT " + + COLUMN_SQR_ID + ", " + + COLUMN_SQR_NAME + " " + + "FROM " + TABLE_SQ_COUNTER + " " + + "WHERE " + COLUMN_SQR_NAME + " = substr(?, 1, 75)"; + + // DML: Insert new counter + public static final String STMT_INSERT_COUNTER = + "INSERT INTO " + TABLE_SQ_COUNTER + " (" + + COLUMN_SQR_NAME + ") " + + "VALUES (substr(?, 1, 75))"; + + // DML: Insert new counter submission + public static final String STMT_INSERT_COUNTER_SUBMISSION = + "INSERT INTO " + TABLE_SQ_COUNTER_SUBMISSION + " (" + + COLUMN_SQRS_GROUP + ", " + + COLUMN_SQRS_COUNTER + ", " + + COLUMN_SQRS_SUBMISSION + ", " + + COLUMN_SQRS_VALUE + ") " + + "VALUES (?, ?, ?, ?)"; + + // DML: Select counter submission + public static final String STMT_SELECT_COUNTER_SUBMISSION = + "SELECT " + + COLUMN_SQG_NAME + ", " + + COLUMN_SQR_NAME + ", " + + COLUMN_SQRS_VALUE + " " + + "FROM " + TABLE_SQ_COUNTER_SUBMISSION + " " + + "LEFT JOIN " + TABLE_SQ_COUNTER_GROUP + + " ON " + COLUMN_SQRS_GROUP + " = " + COLUMN_SQG_ID + " " + + "LEFT JOIN " + TABLE_SQ_COUNTER + + " ON " + COLUMN_SQRS_COUNTER + " = " + COLUMN_SQR_ID + " " + + "WHERE " + COLUMN_SQRS_SUBMISSION + " = ? "; + + // DML: Delete rows from counter submission table + public static final String STMT_DELETE_COUNTER_SUBMISSION = + "DELETE FROM " + TABLE_SQ_COUNTER_SUBMISSION + + " WHERE " + COLUMN_SQRS_SUBMISSION + " = ?"; + + /******* CONFIG and CONNECTOR DIRECTIONS ****/ + + public static final String STMT_INSERT_DIRECTION = "INSERT INTO " + TABLE_SQ_DIRECTION + " " + + "(" + COLUMN_SQD_NAME + ") VALUES (?)"; + + public static final String STMT_FETCH_CONFIG_DIRECTIONS = + "SELECT " + + COLUMN_SQ_CFG_ID + ", " + + COLUMN_SQ_CFG_DIRECTION + + " FROM " + TABLE_SQ_CONFIG; + + + public static final String STMT_INSERT_SQ_CONNECTOR_DIRECTIONS = + "INSERT INTO " + TABLE_SQ_CONNECTOR_DIRECTIONS + " " + + "(" + COLUMN_SQCD_CONNECTOR + ", " + COLUMN_SQCD_DIRECTION + ")" + + " VALUES (?, ?)"; + + public static final String STMT_INSERT_SQ_CONFIG_DIRECTIONS = + "INSERT INTO " + TABLE_SQ_CONFIG_DIRECTIONS + " " + + "(" + COLUMN_SQ_CFG_DIR_CONFIG + ", " + COLUMN_SQ_CFG_DIR_DIRECTION + ")" + + " VALUES (?, ?)"; + + public static final String STMT_SELECT_SQ_CONNECTOR_DIRECTIONS_ALL = + "SELECT " + COLUMN_SQCD_CONNECTOR + ", " + COLUMN_SQCD_DIRECTION + + " FROM " + TABLE_SQ_CONNECTOR_DIRECTIONS; + + public static final String STMT_SELECT_SQ_CONNECTOR_DIRECTIONS = + STMT_SELECT_SQ_CONNECTOR_DIRECTIONS_ALL + " WHERE " + + COLUMN_SQCD_CONNECTOR + " = ?"; + + public static final String STMT_SELECT_SQ_CONFIG_DIRECTIONS_ALL = + "SELECT " + COLUMN_SQ_CFG_DIR_CONFIG + ", " + COLUMN_SQ_CFG_DIR_DIRECTION + + " FROM " + TABLE_SQ_CONFIG_DIRECTIONS; + + public static final String STMT_SELECT_SQ_CONFIG_DIRECTIONS = + STMT_SELECT_SQ_CONFIG_DIRECTIONS_ALL + " WHERE " + + COLUMN_SQ_CFG_DIR_CONFIG + " = ?"; + + + private DerbySchemaInsertUpdateDeleteSelectQuery() { + // Disable explicit object creation + } +} \ No newline at end of file
