From 91b9063c7b7e13eac1c7d5e11c64cfc9f79c04f7 Mon Sep 17 00:00:00 2001 From: simonbrandhof Date: Mon, 18 Apr 2011 16:56:47 +0200 Subject: [PATCH] SONAR-2366 Upgrade embedded database to Derby 10.7.1.1 + add DatabaseTestCase to sonar-testing-harness --- pom.xml | 2 +- .../org/sonar/jpa/entity/SchemaMigration.java | 9 + sonar-testing-harness/pom.xml | 4 + .../test/persistence/DatabaseTestCase.java | 238 +++++++++ .../org/sonar/test/persistence/sonar-test.ddl | 483 ++++++++++++++++++ 5 files changed, 735 insertions(+), 1 deletion(-) create mode 100644 sonar-testing-harness/src/main/java/org/sonar/test/persistence/DatabaseTestCase.java create mode 100644 sonar-testing-harness/src/main/resources/org/sonar/test/persistence/sonar-test.ddl diff --git a/pom.xml b/pom.xml index f59780f810f..039b0bace7f 100644 --- a/pom.xml +++ b/pom.xml @@ -85,7 +85,7 @@ - 10.6.1.0 + 10.7.1.1 6.1.24 sonar-deprecated,sonar-gwt-api,sonar-core-gwt UTF-8 diff --git a/sonar-core/src/main/java/org/sonar/jpa/entity/SchemaMigration.java b/sonar-core/src/main/java/org/sonar/jpa/entity/SchemaMigration.java index 47b1c4d567d..574765e5114 100644 --- a/sonar-core/src/main/java/org/sonar/jpa/entity/SchemaMigration.java +++ b/sonar-core/src/main/java/org/sonar/jpa/entity/SchemaMigration.java @@ -31,6 +31,15 @@ import javax.persistence.*; public class SchemaMigration { public final static int VERSION_UNKNOWN = -1; + + /* + + IMPORTANT : HOW TO ADD A DATABASE MIGRATION : + - set the following constant LAST_VERSION + - add the activerecord migration script into sonar-server/src/main/webapp/WEB-INF/db/migrate + - complete the Derby DDL file used for unit tests : sonar-testing-harness/src/main/resources/org/sonar/test/persistence/sonar-test.ddl + + */ public static final int LAST_VERSION = 190; public final static String TABLE_NAME = "schema_migrations"; diff --git a/sonar-testing-harness/pom.xml b/sonar-testing-harness/pom.xml index 8082f501f26..82b195c922e 100644 --- a/sonar-testing-harness/pom.xml +++ b/sonar-testing-harness/pom.xml @@ -36,6 +36,10 @@ sonar-plugin-api ${project.version} + + org.apache.derby + derby + org.dbunit dbunit diff --git a/sonar-testing-harness/src/main/java/org/sonar/test/persistence/DatabaseTestCase.java b/sonar-testing-harness/src/main/java/org/sonar/test/persistence/DatabaseTestCase.java new file mode 100644 index 00000000000..9bd6aa688c3 --- /dev/null +++ b/sonar-testing-harness/src/main/java/org/sonar/test/persistence/DatabaseTestCase.java @@ -0,0 +1,238 @@ +/* + * Sonar, open source software quality management tool. + * Copyright (C) 2008-2011 SonarSource + * mailto:contact AT sonarsource DOT com + * + * Sonar is free software; you can redistribute it and/or + * modify it under the terms of the GNU Lesser General Public + * License as published by the Free Software Foundation; either + * version 3 of the License, or (at your option) any later version. + * + * Sonar is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU + * Lesser General Public License for more details. + * + * You should have received a copy of the GNU Lesser General Public + * License along with Sonar; if not, write to the Free Software + * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02 + */ +package org.sonar.test.persistence; + +import org.apache.commons.io.IOUtils; +import org.apache.commons.lang.StringUtils; +import org.apache.derby.jdbc.EmbeddedDriver; +import org.dbunit.Assertion; +import org.dbunit.DatabaseUnitException; +import org.dbunit.IDatabaseTester; +import org.dbunit.JdbcDatabaseTester; +import org.dbunit.dataset.CompositeDataSet; +import org.dbunit.dataset.DataSetException; +import org.dbunit.dataset.IDataSet; +import org.dbunit.dataset.ReplacementDataSet; +import org.dbunit.dataset.xml.FlatXmlDataSet; +import org.dbunit.operation.DatabaseOperation; +import org.junit.After; +import org.junit.AfterClass; +import org.junit.Assert; +import org.junit.BeforeClass; + +import java.io.InputStream; +import java.sql.*; +import java.util.List; + +import static org.junit.Assert.fail; + +public abstract class DatabaseTestCase { + + private static IDatabaseTester databaseTester = null; + private static final String JDBC_URL = "jdbc:derby:memory:sonar"; + private Connection connection = null; + + + @BeforeClass + public static void startDatabase() throws Exception { + EmbeddedDriver driver = new EmbeddedDriver(); + DriverManager.registerDriver(driver); + databaseTester = new JdbcDatabaseTester(driver.getClass().getName(), JDBC_URL + ";create=true"); + createDatabase(); + } + + private static void createDatabase() throws Exception { + Connection c = databaseTester.getConnection().getConnection(); + Statement st = c.createStatement(); + for (String ddl : loadDdlStatements()) { + st.executeUpdate(ddl); + c.commit(); + } + st.close(); + c.close(); + } + + private static String[] loadDdlStatements() throws Exception { + InputStream in = DatabaseTestCase.class.getResourceAsStream("/org/sonar/test/persistence/sonar-test.ddl"); + List lines = IOUtils.readLines(in); + StringBuilder ddl = new StringBuilder(); + for (String line : lines) { + if (StringUtils.isNotBlank(line) && !StringUtils.startsWith(StringUtils.trimToEmpty(line), "#")) { + ddl.append(line).append(" "); + } + } + + in.close(); + return StringUtils.split(StringUtils.trim(ddl.toString()), ";"); + } + + @AfterClass + public static void stopDatabase() throws Exception { + try { + DriverManager.getConnection(JDBC_URL + ";shutdown=true"); + databaseTester.onTearDown(); + } catch (Exception e) { + // silently fail + } + } + + public static IDatabaseTester getDatabaseTester() { + return databaseTester; + } + + protected final Connection getConnection() { + try { + if (connection == null) { + connection = getDatabaseTester().getConnection().getConnection(); + } + + } catch (Exception e) { + throw new RuntimeException(e); + } + return connection; + } + + @After + public final void truncateTables() throws SQLException { + ResultSet rs = getConnection().getMetaData().getTables(null, "APP", null, null); + Statement st = getConnection().createStatement(); + while (rs.next()) { + String tableName = rs.getString(3); + // truncate command is implemented since derby 10.7 + st.executeUpdate("TRUNCATE TABLE " + tableName); + } + st.close(); + rs.close(); + getConnection().commit(); + } + + @After + public final void closeConnection() { + if (connection != null) { + try { + connection.close(); + connection = null; + } catch (SQLException e) { + throw new RuntimeException(e); + } + } + } + + + protected final void setupData(String... testNames) { + InputStream[] streams = new InputStream[testNames.length]; + try { + for (int i = 0; i < testNames.length; i++) { + String className = getClass().getName(); + className = String.format("/%s/%s.xml", className.replace(".", "/"), testNames[i]); + streams[i] = getClass().getResourceAsStream(className); + if (streams[i] == null) { + throw new RuntimeException("Test not found :" + className); + } + } + + setupData(streams); + + } finally { + for (InputStream stream : streams) { + IOUtils.closeQuietly(stream); + } + } + } + + private void setupData(InputStream... dataSetStream) { + try { + IDataSet[] dataSets = new IDataSet[dataSetStream.length]; + for (int i = 0; i < dataSetStream.length; i++) { + ReplacementDataSet dataSet = new ReplacementDataSet(new FlatXmlDataSet(dataSetStream[i])); + dataSet.addReplacementObject("[null]", null); + dataSet.addReplacementObject("true", 1); + dataSet.addReplacementObject("false", 0); + dataSets[i] = dataSet; + } + CompositeDataSet compositeDataSet = new CompositeDataSet(dataSets); + DatabaseOperation.CLEAN_INSERT.execute(getDatabaseTester().getConnection(), compositeDataSet); + + } catch (Exception e) { + throw new RuntimeException("Could not setup DBUnit data", e); + } + } + + protected final void assertTables(String testName, String... tables) { + try { + IDataSet dataSet = getCurrentDataSet(); + IDataSet expectedDataSet = getExpectedData(testName); + for (String table : tables) { + Assertion.assertEquals(expectedDataSet.getTable(table), dataSet.getTable(table)); + } + } catch (DataSetException e) { + throw translateException("Error while checking results", e); + } catch (DatabaseUnitException e) { + fail(e.getMessage()); + } + } + + protected final void assertEmptyTables(String... emptyTables) { + for (String table : emptyTables) { + try { + Assert.assertEquals(0, getCurrentDataSet().getTable(table).getRowCount()); + } catch (DataSetException e) { + throw translateException("Error while checking results", e); + } + } + } + + private IDataSet getExpectedData(String testName) { + String className = getClass().getName(); + className = String.format("/%s/%s-result.xml", className.replace(".", "/"), testName); + + InputStream in = getClass().getResourceAsStream(className); + try { + return getData(in); + } finally { + IOUtils.closeQuietly(in); + } + } + + private IDataSet getData(InputStream stream) { + try { + ReplacementDataSet dataSet = new ReplacementDataSet(new FlatXmlDataSet(stream)); + dataSet.addReplacementObject("[null]", null); + return dataSet; + } catch (Exception e) { + throw translateException("Could not read the dataset stream", e); + } + } + + private IDataSet getCurrentDataSet() { + try { + return databaseTester.getConnection().createDataSet(); + } catch (Exception e) { + throw translateException("Could not create the current dataset", e); + } + } + + private static RuntimeException translateException(String msg, Exception cause) { + RuntimeException runtimeException = new RuntimeException(String.format("%s: [%s] %s", msg, cause.getClass().getName(), cause.getMessage())); + runtimeException.setStackTrace(cause.getStackTrace()); + return runtimeException; + } + +} diff --git a/sonar-testing-harness/src/main/resources/org/sonar/test/persistence/sonar-test.ddl b/sonar-testing-harness/src/main/resources/org/sonar/test/persistence/sonar-test.ddl new file mode 100644 index 00000000000..eab1ca343af --- /dev/null +++ b/sonar-testing-harness/src/main/resources/org/sonar/test/persistence/sonar-test.ddl @@ -0,0 +1,483 @@ +create table ACTIVE_DASHBOARDS ( + ID INTEGER not null, + DASHBOARD_ID INTEGER not null, + USER_ID INTEGER, + ORDER_INDEX INTEGER, + primary key (id) +); +CREATE INDEX ACTIVE_DASHBOARDS_DASHBOARDID ON ACTIVE_DASHBOARDS (DASHBOARD_ID); +CREATE INDEX ACTIVE_DASHBOARDS_USERID ON ACTIVE_DASHBOARDS (USER_ID); + +create table ACTIVE_FILTERS ( + ID INTEGER not null, + FILTER_ID INTEGER, + USER_ID INTEGER, + ORDER_INDEX INTEGER, + primary key (id) +); + +create table ACTIVE_RULES ( + ID INTEGER not null, + PROFILE_ID INTEGER not null, + RULE_ID INTEGER not null, + FAILURE_LEVEL INTEGER not null, + INHERITANCE VARCHAR(10), + primary key (id) +); + +create table ACTIVE_RULE_PARAMETERS ( + ID INTEGER not null, + ACTIVE_RULE_ID INTEGER not null, + RULES_PARAMETER_ID INTEGER not null, + VALUE VARCHAR(4000), + primary key (id) +); + +create table ALERTS ( + ID INTEGER not null, + PROFILE_ID INTEGER, + METRIC_ID INTEGER, + OPERATOR VARCHAR(3), + VALUE_ERROR VARCHAR(64), + VALUE_WARNING VARCHAR(64), + primary key (id) +); + +create table ASYNC_MEASURE_SNAPSHOTS ( + ID INTEGER not null, + PROJECT_MEASURE_ID INTEGER, + MEASURE_DATE TIMESTAMP, + SNAPSHOT_ID INTEGER, + SNAPSHOT_DATE TIMESTAMP, + METRIC_ID INTEGER, + PROJECT_ID INTEGER, + primary key (id) +); +CREATE INDEX ASYNC_M_S_MEASURE_ID ON ASYNC_MEASURE_SNAPSHOTS (PROJECT_MEASURE_ID); +CREATE INDEX ASYNC_M_S_PROJECT_METRIC ON ASYNC_MEASURE_SNAPSHOTS (PROJECT_ID, METRIC_ID); +CREATE INDEX ASYNC_M_S_SNAPSHOT_ID ON ASYNC_MEASURE_SNAPSHOTS (SNAPSHOT_ID); + +create table CHARACTERISTICS ( + ID INTEGER not null, + QUALITY_MODEL_ID INTEGER, + KEE VARCHAR(100), + NAME VARCHAR(100), + RULE_ID INTEGER, + DEPTH INTEGER, + CHARACTERISTIC_ORDER INTEGER, + DESCRIPTION VARCHAR(4000), + ENABLED SMALLINT, + primary key (id) +); + +create table CHARACTERISTIC_EDGES ( + CHILD_ID INTEGER, + PARENT_ID INTEGER +); + +create table CHARACTERISTIC_PROPERTIES ( + ID INTEGER not null, + CHARACTERISTIC_ID INTEGER, + KEE VARCHAR(100), + VALUE DECIMAL(30, 20), + TEXT_VALUE VARCHAR(4000), + primary key (id) +); + +create table CRITERIA ( + ID INTEGER not null, + FILTER_ID INTEGER, + FAMILY VARCHAR(100), + KEE VARCHAR(100), + OPERATOR VARCHAR(20), + VALUE DECIMAL(30, 20), + TEXT_VALUE VARCHAR(256), + VARIATION SMALLINT, + primary key (id) +); + +create table DASHBOARDS ( + ID INTEGER not null, + USER_ID INTEGER, + NAME VARCHAR(256), + DESCRIPTION VARCHAR(1000), + COLUMN_LAYOUT VARCHAR(20), + SHARED SMALLINT, + CREATED_AT TIMESTAMP, + UPDATED_AT TIMESTAMP, + primary key (id) +); + +create table DEPENDENCIES ( + ID INTEGER not null, + FROM_SNAPSHOT_ID INTEGER, + FROM_RESOURCE_ID INTEGER, + TO_SNAPSHOT_ID INTEGER, + TO_RESOURCE_ID INTEGER, + DEP_USAGE VARCHAR(30), + DEP_WEIGHT INTEGER, + PROJECT_SNAPSHOT_ID INTEGER, + PARENT_DEPENDENCY_ID BIGINT, + FROM_SCOPE VARCHAR(3), + TO_SCOPE VARCHAR(3), + primary key (id) +); +CREATE INDEX DEPS_FROM_SID ON DEPENDENCIES (FROM_SNAPSHOT_ID); +CREATE INDEX DEPS_TO_SID ON DEPENDENCIES (TO_SNAPSHOT_ID); + +create table EVENTS ( + ID INTEGER not null, + NAME VARCHAR(50), + RESOURCE_ID INTEGER, + SNAPSHOT_ID INTEGER, + CATEGORY VARCHAR(50), + EVENT_DATE TIMESTAMP, + CREATED_AT TIMESTAMP, + DESCRIPTION VARCHAR(3072), + DATA VARCHAR(4000), + primary key (id) +); +CREATE INDEX EVENTS_RESOURCE_ID ON EVENTS (RESOURCE_ID); +CREATE INDEX EVENTS_SNAPSHOT_ID ON EVENTS (SNAPSHOT_ID); + +create table FILTERS ( + ID INTEGER not null, + NAME VARCHAR(100), + USER_ID INTEGER, + SHARED SMALLINT, + FAVOURITES SMALLINT, + RESOURCE_ID INTEGER, + DEFAULT_VIEW VARCHAR(20), + PAGE_SIZE INTEGER, + PERIOD_INDEX INTEGER, + primary key (id) +); + +create table FILTER_COLUMNS ( + ID INTEGER not null, + FILTER_ID INTEGER, + FAMILY VARCHAR(100), + KEE VARCHAR(100), + SORT_DIRECTION VARCHAR(5), + ORDER_INDEX INTEGER, + VARIATION SMALLINT, + primary key (id) +); + +create table GROUPS ( + ID INTEGER not null, + NAME VARCHAR(40), + DESCRIPTION VARCHAR(200), + CREATED_AT TIMESTAMP, + UPDATED_AT TIMESTAMP, + primary key (id) +); + +create table GROUPS_USERS ( + USER_ID INTEGER, + GROUP_ID INTEGER +); +CREATE INDEX INDEX_GROUPS_USERS_ON_GROUP_ID ON GROUPS_USERS (GROUP_ID); +CREATE INDEX INDEX_GROUPS_USERS_ON_USER_ID ON GROUPS_USERS (USER_ID); + +create table GROUP_ROLES ( + ID INTEGER not null, + GROUP_ID INTEGER, + RESOURCE_ID INTEGER, + ROLE VARCHAR(64) not null, + primary key (id) +); +CREATE INDEX GROUP_ROLES_GROUP ON GROUP_ROLES (GROUP_ID); +CREATE INDEX GROUP_ROLES_RESOURCE ON GROUP_ROLES (RESOURCE_ID); + +create table MEASURE_DATA ( + ID INTEGER not null, + MEASURE_ID INTEGER, + SNAPSHOT_ID INTEGER, + DATA BLOB, + primary key (id) +); +CREATE INDEX MEASURE_DATA_MEASURE_ID ON MEASURE_DATA (MEASURE_ID); +CREATE INDEX M_DATA_SID ON MEASURE_DATA (SNAPSHOT_ID); + +create table METRICS ( + ID INTEGER not null, + NAME VARCHAR(64) not null, + DESCRIPTION VARCHAR(255), + DIRECTION INTEGER not null, + DOMAIN VARCHAR(64), + SHORT_NAME VARCHAR(64), + QUALITATIVE SMALLINT, + VAL_TYPE VARCHAR(8), + USER_MANAGED SMALLINT, + ENABLED SMALLINT, + ORIGIN VARCHAR(3), + WORST_VALUE DECIMAL(30, 20), + BEST_VALUE DECIMAL(30, 20), + OPTIMIZED_BEST_VALUE SMALLINT, + HIDDEN SMALLINT, + primary key (id) +); +CREATE UNIQUE INDEX METRICS_UNIQUE_NAME ON METRICS (NAME); + +create table PARAMETERS ( + ID INTEGER not null, + PARAM_KEY VARCHAR(100) not null, + VALUE DECIMAL(30, 20) not null, + VALUE2 DECIMAL(30, 20), + primary key (id) +); + +create table PLUGINS ( + ID INTEGER not null, + PLUGIN_KEY VARCHAR(100), + VERSION VARCHAR(100), + NAME VARCHAR(100), + DESCRIPTION VARCHAR(3000), + ORGANIZATION VARCHAR(100), + ORGANIZATION_URL VARCHAR(500), + LICENSE VARCHAR(50), + INSTALLATION_DATE TIMESTAMP, + PLUGIN_CLASS VARCHAR(100), + HOMEPAGE VARCHAR(500), + CORE SMALLINT, + CHILD_FIRST_CLASSLOADER SMALLINT, + BASE_PLUGIN VARCHAR(100), + primary key (id) +); + +create table PLUGIN_FILES ( + ID INTEGER not null, + PLUGIN_ID INTEGER, + FILENAME VARCHAR(100), + primary key (id) +); + +create table PROJECTS ( + ID INTEGER not null, + NAME VARCHAR(256), + DESCRIPTION VARCHAR(2000), + ENABLED SMALLINT not null, + SCOPE VARCHAR(3), + QUALIFIER VARCHAR(3), + KEE VARCHAR(400), + ROOT_ID INTEGER, + PROFILE_ID INTEGER, + LANGUAGE VARCHAR(5), + COPY_RESOURCE_ID INTEGER, + LONG_NAME VARCHAR(256), + primary key (id) +); +CREATE INDEX PROJECTS_KEE ON PROJECTS (KEE); + +create table PROJECT_LINKS ( + ID INTEGER not null, + PROJECT_ID INTEGER not null, + LINK_TYPE VARCHAR(20), + NAME VARCHAR(128), + HREF VARCHAR(2048) not null, + primary key (id) +); + +create table PROJECT_MEASURES ( + ID INTEGER not null, + VALUE DECIMAL(30, 20), + METRIC_ID INTEGER not null, + SNAPSHOT_ID INTEGER, + RULE_ID INTEGER, + RULES_CATEGORY_ID INTEGER, + TEXT_VALUE VARCHAR(96), + TENDENCY INTEGER, + MEASURE_DATE TIMESTAMP, + PROJECT_ID INTEGER, + ALERT_STATUS VARCHAR(5), + ALERT_TEXT VARCHAR(4000), + URL VARCHAR(2000), + DESCRIPTION VARCHAR(4000), + RULE_PRIORITY INTEGER, + CHARACTERISTIC_ID INTEGER, + VARIATION_VALUE_1 DECIMAL(30, 20), + VARIATION_VALUE_2 DECIMAL(30, 20), + VARIATION_VALUE_3 DECIMAL(30, 20), + VARIATION_VALUE_4 DECIMAL(30, 20), + VARIATION_VALUE_5 DECIMAL(30, 20), + primary key (id) +); +CREATE INDEX MEASURES_SID_METRIC ON PROJECT_MEASURES (SNAPSHOT_ID, METRIC_ID); + +create table PROPERTIES ( + ID INTEGER not null, + PROP_KEY VARCHAR(512), + RESOURCE_ID INTEGER, + TEXT_VALUE CLOB, + USER_ID INTEGER, + primary key (id) +); +CREATE INDEX PROPERTIES_KEY ON PROPERTIES (PROP_KEY); + +create table QUALITY_MODELS ( + ID INTEGER not null, + NAME VARCHAR(100), + primary key (id) +); + +create table RULES ( + ID INTEGER not null, + NAME VARCHAR(192) not null, + PLUGIN_RULE_KEY VARCHAR(200) not null, + PLUGIN_NAME VARCHAR(255) not null, + DESCRIPTION CLOB, + PRIORITY INTEGER, + ENABLED SMALLINT, + CARDINALITY VARCHAR(10), + PARENT_ID INTEGER, + PLUGIN_CONFIG_KEY VARCHAR(500), + primary key (id) +); + +create table RULES_CATEGORIES ( + ID INTEGER not null, + NAME VARCHAR(255) not null, + DESCRIPTION VARCHAR(1000) not null, + primary key (id) +); + +create table RULES_PARAMETERS ( + ID INTEGER not null, + RULE_ID INTEGER not null, + NAME VARCHAR(128) not null, + PARAM_TYPE VARCHAR(512) not null, + DEFAULT_VALUE VARCHAR(4000), + DESCRIPTION VARCHAR(4000), + primary key (id) +); +CREATE INDEX ALTERED_RULES_PARAMETERS_RULE_ID ON RULES_PARAMETERS (RULE_ID); + +create table RULES_PROFILES ( + ID INTEGER not null, + NAME VARCHAR(100) not null, + DEFAULT_PROFILE SMALLINT, + PROVIDED SMALLINT, + LANGUAGE VARCHAR(16), + PARENT_NAME VARCHAR(100), + ENABLED SMALLINT, + primary key (id) +); + +create table RULE_FAILURES ( + ID INTEGER not null, + SNAPSHOT_ID INTEGER not null, + RULE_ID INTEGER not null, + FAILURE_LEVEL INTEGER not null, + MESSAGE VARCHAR(4000), + LINE INTEGER, + COST DECIMAL(30, 20), + CREATED_AT TIMESTAMP, + CHECKSUM VARCHAR(1000), + PERMANENT_ID INTEGER, + SWITCHED_OFF SMALLINT, + primary key (id) +); +CREATE INDEX RF_PERMANENT_ID ON RULE_FAILURES (PERMANENT_ID); +CREATE INDEX RULE_FAILURE_RULE_ID ON RULE_FAILURES (RULE_ID); +CREATE INDEX RULE_FAILURE_SNAPSHOT_ID ON RULE_FAILURES (SNAPSHOT_ID); + +create table SCHEMA_MIGRATIONS ( + VERSION VARCHAR(256) not null +); +CREATE UNIQUE INDEX UNIQUE_SCHEMA_MIGRATIONS ON SCHEMA_MIGRATIONS (VERSION); + +create table SNAPSHOTS ( + ID INTEGER not null, + CREATED_AT TIMESTAMP, + PROJECT_ID INTEGER not null, + PARENT_SNAPSHOT_ID INTEGER, + STATUS VARCHAR(4) not null, + ISLAST SMALLINT not null, + SCOPE VARCHAR(3), + QUALIFIER VARCHAR(3), + ROOT_SNAPSHOT_ID INTEGER, + VERSION VARCHAR(60), + PATH VARCHAR(96), + DEPTH INTEGER, + ROOT_PROJECT_ID INTEGER, + PERIOD1_MODE VARCHAR(100), + PERIOD1_PARAM VARCHAR(100), + PERIOD1_DATE TIMESTAMP, + PERIOD2_MODE VARCHAR(100), + PERIOD2_PARAM VARCHAR(100), + PERIOD2_DATE TIMESTAMP, + PERIOD3_MODE VARCHAR(100), + PERIOD3_PARAM VARCHAR(100), + PERIOD3_DATE TIMESTAMP, + PERIOD4_MODE VARCHAR(100), + PERIOD4_PARAM VARCHAR(100), + PERIOD4_DATE TIMESTAMP, + PERIOD5_MODE VARCHAR(100), + PERIOD5_PARAM VARCHAR(100), + PERIOD5_DATE TIMESTAMP, + primary key (id) +); +CREATE INDEX SNAPSHOTS_PARENT ON SNAPSHOTS (PARENT_SNAPSHOT_ID); +CREATE INDEX SNAPSHOTS_QUALIFIER ON SNAPSHOTS (QUALIFIER); +CREATE INDEX SNAPSHOTS_ROOT ON SNAPSHOTS (ROOT_SNAPSHOT_ID); +CREATE INDEX SNAPSHOT_PROJECT_ID ON SNAPSHOTS (PROJECT_ID); + +create table SNAPSHOT_SOURCES ( + ID INTEGER not null, + SNAPSHOT_ID INTEGER not null, + DATA CLOB, + primary key (id) +); +CREATE INDEX SNAP_SOURCES_SNAPSHOT_ID ON SNAPSHOT_SOURCES (SNAPSHOT_ID); + +create table USERS ( + ID INTEGER not null, + LOGIN VARCHAR(40), + NAME VARCHAR(200), + EMAIL VARCHAR(100), + CRYPTED_PASSWORD VARCHAR(40), + SALT VARCHAR(40), + CREATED_AT TIMESTAMP, + UPDATED_AT TIMESTAMP, + REMEMBER_TOKEN VARCHAR(500), + REMEMBER_TOKEN_EXPIRES_AT TIMESTAMP, + primary key (id) +); + +create table USER_ROLES ( + ID INTEGER not null, + USER_ID INTEGER, + RESOURCE_ID INTEGER, + ROLE VARCHAR(64) not null, + primary key (id) +); +CREATE INDEX USER_ROLES_RESOURCE ON USER_ROLES (RESOURCE_ID); +CREATE INDEX USER_ROLES_USER ON USER_ROLES (USER_ID); + +create table WIDGETS ( + ID INTEGER not null, + DASHBOARD_ID INTEGER not null, + WIDGET_KEY VARCHAR(256) not null, + NAME VARCHAR(256), + DESCRIPTION VARCHAR(1000), + COLUMN_INDEX INTEGER, + ROW_INDEX INTEGER, + CONFIGURED SMALLINT, + CREATED_AT TIMESTAMP, + UPDATED_AT TIMESTAMP, + primary key (id) +); +CREATE INDEX WIDGETS_DASHBOARDS ON WIDGETS (DASHBOARD_ID); +CREATE INDEX WIDGETS_WIDGETKEY ON WIDGETS (WIDGET_KEY); + +create table WIDGET_PROPERTIES ( + ID INTEGER not null, + WIDGET_ID INTEGER not null, + KEE VARCHAR(100), + TEXT_VALUE VARCHAR(4000), + VALUE_TYPE VARCHAR(20), + primary key (id) +); +CREATE INDEX WIDGET_PROPERTIES_WIDGETS ON WIDGET_PROPERTIES (WIDGET_ID); + -- 2.39.5