SONAR-2366 Upgrade embedded database to Derby 10.7.1.1 + add DatabaseTestCase to sonar-testing-harness

2 files changed, 721 insertions, 0 deletions

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<String> 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);
+