You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hive.apache.org by jv...@apache.org on 2011/03/19 05:01:53 UTC
svn commit: r1083139 [1/3] - in /hive/trunk/metastore/scripts/upgrade:
derby/ mysql/
Author: jvs
Date: Sat Mar 19 04:01:52 2011
New Revision: 1083139
URL: http://svn.apache.org/viewvc?rev=1083139&view=rev
Log:
HIVE-2011. upgrade-0.6.0.mysql.sql script attempts to increase size of
PK COLUMNS.TYPE_NAME to 4000
(Carl Steinbach via jvs)
Added:
hive/trunk/metastore/scripts/upgrade/derby/001-HIVE-972.derby.sql
hive/trunk/metastore/scripts/upgrade/derby/002-HIVE-1068.derby.sql
hive/trunk/metastore/scripts/upgrade/derby/003-HIVE-675.derby.sql
hive/trunk/metastore/scripts/upgrade/derby/004-HIVE-1364.derby.sql
hive/trunk/metastore/scripts/upgrade/derby/005-HIVE-417.derby.sql
hive/trunk/metastore/scripts/upgrade/derby/006-HIVE-1823.derby.sql
hive/trunk/metastore/scripts/upgrade/derby/007-HIVE-78.derby.sql
hive/trunk/metastore/scripts/upgrade/derby/hive-schema-0.3.0.derby.sql
hive/trunk/metastore/scripts/upgrade/derby/hive-schema-0.4.0.derby.sql
hive/trunk/metastore/scripts/upgrade/derby/hive-schema-0.4.1.derby.sql
hive/trunk/metastore/scripts/upgrade/derby/hive-schema-0.5.0.derby.sql
hive/trunk/metastore/scripts/upgrade/derby/hive-schema-0.6.0.derby.sql
hive/trunk/metastore/scripts/upgrade/derby/hive-schema-0.7.0.derby.sql
hive/trunk/metastore/scripts/upgrade/derby/upgrade-0.5.0-to-0.6.0.derby.sql
hive/trunk/metastore/scripts/upgrade/derby/upgrade-0.6.0-to-0.7.0.derby.sql
hive/trunk/metastore/scripts/upgrade/mysql/001-HIVE-972.mysql.sql
hive/trunk/metastore/scripts/upgrade/mysql/002-HIVE-1068.mysql.sql
hive/trunk/metastore/scripts/upgrade/mysql/003-HIVE-675.mysql.sql
hive/trunk/metastore/scripts/upgrade/mysql/004-HIVE-1364.mysql.sql
hive/trunk/metastore/scripts/upgrade/mysql/005-HIVE-417.mysql.sql
hive/trunk/metastore/scripts/upgrade/mysql/006-HIVE-1823.mysql.sql
hive/trunk/metastore/scripts/upgrade/mysql/007-HIVE-78.mysql.sql
hive/trunk/metastore/scripts/upgrade/mysql/hive-schema-0.3.0.mysql.sql
hive/trunk/metastore/scripts/upgrade/mysql/hive-schema-0.4.0.mysql.sql
hive/trunk/metastore/scripts/upgrade/mysql/hive-schema-0.4.1.mysql.sql
hive/trunk/metastore/scripts/upgrade/mysql/hive-schema-0.5.0.mysql.sql
hive/trunk/metastore/scripts/upgrade/mysql/hive-schema-0.6.0.mysql.sql
hive/trunk/metastore/scripts/upgrade/mysql/hive-schema-0.7.0.mysql.sql
hive/trunk/metastore/scripts/upgrade/mysql/upgrade-0.5.0-to-0.6.0.mysql.sql
hive/trunk/metastore/scripts/upgrade/mysql/upgrade-0.6.0-to-0.7.0.mysql.sql
Removed:
hive/trunk/metastore/scripts/upgrade/derby/upgrade-0.6.0.derby.sql
hive/trunk/metastore/scripts/upgrade/derby/upgrade-0.7.0.derby.sql
hive/trunk/metastore/scripts/upgrade/mysql/upgrade-0.6.0.mysql.sql
hive/trunk/metastore/scripts/upgrade/mysql/upgrade-0.7.0.mysql.sql
Modified:
hive/trunk/metastore/scripts/upgrade/derby/README
hive/trunk/metastore/scripts/upgrade/mysql/README
Added: hive/trunk/metastore/scripts/upgrade/derby/001-HIVE-972.derby.sql
URL: http://svn.apache.org/viewvc/hive/trunk/metastore/scripts/upgrade/derby/001-HIVE-972.derby.sql?rev=1083139&view=auto
==============================================================================
--- hive/trunk/metastore/scripts/upgrade/derby/001-HIVE-972.derby.sql (added)
+++ hive/trunk/metastore/scripts/upgrade/derby/001-HIVE-972.derby.sql Sat Mar 19 04:01:52 2011
@@ -0,0 +1,3 @@
+-- HIVE-972: Support views
+ALTER TABLE "TBLS" ADD "VIEW_ORIGINAL_TEXT" LONG VARCHAR DEFAULT NULL;
+ALTER TABLE "TBLS" ADD "VIEW_EXPANDED_TEXT" LONG VARCHAR DEFAULT NULL;
Added: hive/trunk/metastore/scripts/upgrade/derby/002-HIVE-1068.derby.sql
URL: http://svn.apache.org/viewvc/hive/trunk/metastore/scripts/upgrade/derby/002-HIVE-1068.derby.sql?rev=1083139&view=auto
==============================================================================
--- hive/trunk/metastore/scripts/upgrade/derby/002-HIVE-1068.derby.sql (added)
+++ hive/trunk/metastore/scripts/upgrade/derby/002-HIVE-1068.derby.sql Sat Mar 19 04:01:52 2011
@@ -0,0 +1,2 @@
+-- HIVE-1068: CREATE VIEW followup: add a 'table type' enum attribute in metastore
+ALTER TABLE "TBLS" ADD COLUMN "TBL_TYPE" VARCHAR(128);
Added: hive/trunk/metastore/scripts/upgrade/derby/003-HIVE-675.derby.sql
URL: http://svn.apache.org/viewvc/hive/trunk/metastore/scripts/upgrade/derby/003-HIVE-675.derby.sql?rev=1083139&view=auto
==============================================================================
--- hive/trunk/metastore/scripts/upgrade/derby/003-HIVE-675.derby.sql (added)
+++ hive/trunk/metastore/scripts/upgrade/derby/003-HIVE-675.derby.sql Sat Mar 19 04:01:52 2011
@@ -0,0 +1,3 @@
+-- HIVE-675: Add database/schema support for Hive QL
+ALTER TABLE "DBS" ALTER "DESC" SET DATA TYPE VARCHAR(4000);
+ALTER TABLE "DBS" ADD "DB_LOCATION_URI" VARCHAR(4000) NOT NULL DEFAULT '';
Added: hive/trunk/metastore/scripts/upgrade/derby/004-HIVE-1364.derby.sql
URL: http://svn.apache.org/viewvc/hive/trunk/metastore/scripts/upgrade/derby/004-HIVE-1364.derby.sql?rev=1083139&view=auto
==============================================================================
--- hive/trunk/metastore/scripts/upgrade/derby/004-HIVE-1364.derby.sql (added)
+++ hive/trunk/metastore/scripts/upgrade/derby/004-HIVE-1364.derby.sql Sat Mar 19 04:01:52 2011
@@ -0,0 +1,15 @@
+-- HIVE-1364: Increase the maximum length of various metastore fields,
+-- and remove TYPE_NAME from COLUMNS primary key
+ALTER TABLE "TBLS" ALTER "OWNER" SET DATA TYPE VARCHAR(767);
+ALTER TABLE "COLUMNS" DROP PRIMARY KEY;
+ALTER TABLE "COLUMNS" ADD PRIMARY KEY ("SD_ID", "COLUMN_NAME");
+ALTER TABLE "COLUMNS" ALTER "TYPE_NAME" SET DATA TYPE VARCHAR(4000);
+ALTER TABLE "PARTITION_KEYS" ALTER "PKEY_COMMENT" SET DATA TYPE VARCHAR(4000);
+ALTER TABLE "SD_PARAMS" ALTER "PARAM_VALUE" SET DATA TYPE VARCHAR(4000);
+ALTER TABLE "SDS" ALTER "INPUT_FORMAT" SET DATA TYPE VARCHAR(4000);
+ALTER TABLE "SDS" ALTER "LOCATION" SET DATA TYPE VARCHAR(4000);
+ALTER TABLE "SDS" ALTER "OUTPUT_FORMAT" SET DATA TYPE VARCHAR(4000);
+ALTER TABLE "SERDE_PARAMS" ALTER "PARAM_VALUE" SET DATA TYPE VARCHAR(4000);
+ALTER TABLE "SERDES" ALTER "SLIB" SET DATA TYPE VARCHAR(4000);
+ALTER TABLE "TABLE_PARAMS" ALTER "PARAM_VALUE" SET DATA TYPE VARCHAR(4000);
+ALTER TABLE "PARTITION_PARAMS" ALTER "PARAM_VALUE" SET DATA TYPE VARCHAR(4000);
Added: hive/trunk/metastore/scripts/upgrade/derby/005-HIVE-417.derby.sql
URL: http://svn.apache.org/viewvc/hive/trunk/metastore/scripts/upgrade/derby/005-HIVE-417.derby.sql?rev=1083139&view=auto
==============================================================================
--- hive/trunk/metastore/scripts/upgrade/derby/005-HIVE-417.derby.sql (added)
+++ hive/trunk/metastore/scripts/upgrade/derby/005-HIVE-417.derby.sql Sat Mar 19 04:01:52 2011
@@ -0,0 +1,47 @@
+--
+-- HIVE-417 Implement Indexing in Hive
+--
+
+CREATE TABLE "IDXS" (
+ "INDEX_ID" BIGINT NOT NULL,
+ "CREATE_TIME" INTEGER NOT NULL,
+ "DEFERRED_REBUILD" CHAR(1) NOT NULL,
+ "INDEX_HANDLER_CLASS" VARCHAR(256),
+ "INDEX_NAME" VARCHAR(128),
+ "INDEX_TBL_ID" BIGINT,
+ "LAST_ACCESS_TIME" INTEGER NOT NULL,
+ "ORIG_TBL_ID" BIGINT,
+ "SD_ID" BIGINT);
+
+ALTER TABLE "IDXS" ADD CONSTRAINT "IDXS_FK1"
+ FOREIGN KEY ("SD_ID") REFERENCES "SDS" ("SD_ID")
+ ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "IDXS" ADD CONSTRAINT "IDXS_FK2"
+ FOREIGN KEY ("INDEX_TBL_ID") REFERENCES "TBLS" ("TBL_ID")
+ ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "IDXS" ADD CONSTRAINT "IDXS_FK3"
+ FOREIGN KEY ("ORIG_TBL_ID") REFERENCES "TBLS" ("TBL_ID")
+ ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "IDXS" ADD CONSTRAINT "IDXS_PK"
+ PRIMARY KEY ("INDEX_ID");
+
+ALTER TABLE "IDXS" ADD CONSTRAINT "DEFERRED_REBUILD_CHECK"
+ CHECK (DEFERRED_REBUILD IN ('Y','N'));
+
+
+CREATE TABLE "INDEX_PARAMS" (
+ "INDEX_ID" BIGINT NOT NULL,
+ "PARAM_KEY" VARCHAR(256) NOT NULL,
+ "PARAM_VALUE" VARCHAR(767));
+
+ALTER TABLE "INDEX_PARAMS" ADD CONSTRAINT "INDEX_PARAMS_FK1"
+ FOREIGN KEY ("INDEX_ID") REFERENCES "IDXS" ("INDEX_ID")
+ ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "INDEX_PARAMS" ADD CONSTRAINT "INDEX_PARAMS_PK"
+ PRIMARY KEY ("INDEX_ID", "PARAM_KEY");
+
+CREATE UNIQUE INDEX "UNIQUEINDEX" ON "IDXS" ("INDEX_NAME", "ORIG_TBL_ID");
Added: hive/trunk/metastore/scripts/upgrade/derby/006-HIVE-1823.derby.sql
URL: http://svn.apache.org/viewvc/hive/trunk/metastore/scripts/upgrade/derby/006-HIVE-1823.derby.sql?rev=1083139&view=auto
==============================================================================
--- hive/trunk/metastore/scripts/upgrade/derby/006-HIVE-1823.derby.sql (added)
+++ hive/trunk/metastore/scripts/upgrade/derby/006-HIVE-1823.derby.sql Sat Mar 19 04:01:52 2011
@@ -0,0 +1,14 @@
+--
+-- HIVE-1823 Upgrade the database thrift interface to allow parameters key-value pairs
+--
+CREATE TABLE "DATABASE_PARAMS" (
+ "DB_ID" BIGINT NOT NULL,
+ "PARAM_KEY" VARCHAR(180) NOT NULL,
+ "PARAM_VALUE" VARCHAR(4000));
+
+ALTER TABLE "DATABASE_PARAMS" ADD CONSTRAINT "DATABASE_PARAMS_FK1"
+ FOREIGN KEY ("DB_ID") REFERENCES "DBS" ("DB_ID")
+ ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "DATABASE_PARAMS" ADD CONSTRAINT "DATABASE_PARAMS_PK"
+ PRIMARY KEY ("DB_ID", "PARAM_KEY");
Added: hive/trunk/metastore/scripts/upgrade/derby/007-HIVE-78.derby.sql
URL: http://svn.apache.org/viewvc/hive/trunk/metastore/scripts/upgrade/derby/007-HIVE-78.derby.sql?rev=1083139&view=auto
==============================================================================
--- hive/trunk/metastore/scripts/upgrade/derby/007-HIVE-78.derby.sql (added)
+++ hive/trunk/metastore/scripts/upgrade/derby/007-HIVE-78.derby.sql Sat Mar 19 04:01:52 2011
@@ -0,0 +1,184 @@
+--
+-- HIVE-78 Authorization model for Hive
+--
+
+--
+-- ROLES
+--
+CREATE TABLE "ROLES" (
+ "ROLE_ID" BIGINT NOT NULL,
+ "CREATE_TIME" INTEGER NOT NULL,
+ "OWNER_NAME" VARCHAR(128),
+ "ROLE_NAME" VARCHAR(128));
+
+ALTER TABLE "ROLES" ADD CONSTRAINT "ROLES_PK"
+ PRIMARY KEY ("ROLE_ID");
+
+CREATE UNIQUE INDEX "ROLEENTITYINDEX" ON "ROLES" ("ROLE_NAME");
+
+CREATE TABLE "ROLE_MAP" (
+ "ROLE_GRANT_ID" BIGINT NOT NULL,
+ "ADD_TIME" INTEGER NOT NULL,
+ "GRANT_OPTION" SMALLINT NOT NULL,
+ "GRANTOR" VARCHAR(128),
+ "GRANTOR_TYPE" VARCHAR(128),
+ "PRINCIPAL_NAME" VARCHAR(128),
+ "PRINCIPAL_TYPE" VARCHAR(128),
+ "ROLE_ID" BIGINT);
+
+ALTER TABLE "ROLE_MAP" ADD CONSTRAINT "ROLE_MAP_FK1"
+ FOREIGN KEY ("ROLE_ID") REFERENCES "ROLES" ("ROLE_ID")
+ ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "ROLE_MAP" ADD CONSTRAINT "ROLE_MAP_PK"
+ PRIMARY KEY ("ROLE_GRANT_ID");
+
+CREATE UNIQUE INDEX "USERROLEMAPINDEX" ON "ROLE_MAP" (
+ "PRINCIPAL_NAME", "ROLE_ID", "GRANTOR", "GRANTOR_TYPE");
+
+--
+-- GLOBAL_PRIVS
+--
+CREATE TABLE "GLOBAL_PRIVS" (
+ "USER_GRANT_ID" BIGINT NOT NULL,
+ "CREATE_TIME" INTEGER NOT NULL,
+ "GRANT_OPTION" SMALLINT NOT NULL,
+ "GRANTOR" VARCHAR(128),
+ "GRANTOR_TYPE" VARCHAR(128),
+ "PRINCIPAL_NAME" VARCHAR(128),
+ "PRINCIPAL_TYPE" VARCHAR(128),
+ "USER_PRIV" VARCHAR(128));
+
+ALTER TABLE "GLOBAL_PRIVS" ADD CONSTRAINT "GLOBAL_PRIVS_PK"
+ PRIMARY KEY ("USER_GRANT_ID");
+
+CREATE UNIQUE INDEX "GLOBALPRIVILEGEINDEX" ON "GLOBAL_PRIVS" (
+ "PRINCIPAL_NAME", "PRINCIPAL_TYPE", "USER_PRIV",
+ "GRANTOR", "GRANTOR_TYPE");
+
+--
+-- DB_PRIVS
+--
+CREATE TABLE "DB_PRIVS" (
+ "DB_GRANT_ID" BIGINT NOT NULL,
+ "CREATE_TIME" INTEGER NOT NULL,
+ "DB_ID" BIGINT,
+ "GRANT_OPTION" SMALLINT NOT NULL,
+ "GRANTOR" VARCHAR(128),
+ "GRANTOR_TYPE" VARCHAR(128),
+ "PRINCIPAL_NAME" VARCHAR(128),
+ "PRINCIPAL_TYPE" VARCHAR(128),
+ "DB_PRIV" VARCHAR(128));
+
+ALTER TABLE "DB_PRIVS" ADD CONSTRAINT "DB_PRIVS_FK1"
+ FOREIGN KEY ("DB_ID") REFERENCES "DBS" ("DB_ID")
+ ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "DB_PRIVS" ADD CONSTRAINT "DB_PRIVS_PK"
+ PRIMARY KEY ("DB_GRANT_ID");
+
+CREATE UNIQUE INDEX "DBPRIVILEGEINDEX" ON "DB_PRIVS" (
+ "DB_ID", "PRINCIPAL_NAME", "PRINCIPAL_TYPE",
+ "DB_PRIV", "GRANTOR", "GRANTOR_TYPE");
+
+--
+-- TBL_PRIVS
+--
+CREATE TABLE "TBL_PRIVS" (
+ "TBL_GRANT_ID" BIGINT NOT NULL,
+ "CREATE_TIME" INTEGER NOT NULL,
+ "GRANT_OPTION" SMALLINT NOT NULL,
+ "GRANTOR" VARCHAR(128),
+ "GRANTOR_TYPE" VARCHAR(128),
+ "PRINCIPAL_NAME" VARCHAR(128),
+ "PRINCIPAL_TYPE" VARCHAR(128),
+ "TBL_PRIV" VARCHAR(128),
+ "TBL_ID" BIGINT);
+
+ALTER TABLE "TBL_PRIVS" ADD CONSTRAINT "TBL_PRIVS_FK1"
+ FOREIGN KEY ("TBL_ID") REFERENCES "TBLS" ("TBL_ID")
+ ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "TBL_PRIVS" ADD CONSTRAINT "TBL_PRIVS_PK"
+ PRIMARY KEY ("TBL_GRANT_ID");
+
+CREATE INDEX "TABLEPRIVILEGEINDEX" ON "TBL_PRIVS" (
+ "TBL_ID", "PRINCIPAL_NAME", "PRINCIPAL_TYPE",
+ "TBL_PRIV", "GRANTOR", "GRANTOR_TYPE");
+
+--
+-- TBL_COL_PRIVS
+--
+CREATE TABLE "TBL_COL_PRIVS" (
+ "TBL_COLUMN_GRANT_ID" BIGINT NOT NULL,
+ "COLUMN_NAME" VARCHAR(128),
+ "CREATE_TIME" INTEGER NOT NULL,
+ "GRANT_OPTION" SMALLINT NOT NULL,
+ "GRANTOR" VARCHAR(128),
+ "GRANTOR_TYPE" VARCHAR(128),
+ "PRINCIPAL_NAME" VARCHAR(128),
+ "PRINCIPAL_TYPE" VARCHAR(128),
+ "TBL_COL_PRIV" VARCHAR(128),
+ "TBL_ID" BIGINT);
+
+ALTER TABLE "TBL_COL_PRIVS" ADD CONSTRAINT "TBL_COL_PRIVS_FK1"
+ FOREIGN KEY ("TBL_ID") REFERENCES "TBLS" ("TBL_ID")
+ ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "TBL_COL_PRIVS" ADD CONSTRAINT "TBL_COL_PRIVS_PK"
+ PRIMARY KEY ("TBL_COLUMN_GRANT_ID");
+
+CREATE INDEX "TABLECOLUMNPRIVILEGEINDEX" ON "TBL_COL_PRIVS" (
+ "TBL_ID", "COLUMN_NAME", "PRINCIPAL_NAME", "PRINCIPAL_TYPE",
+ "TBL_COL_PRIV", "GRANTOR", "GRANTOR_TYPE");
+
+--
+-- PART_PRIVS
+--
+CREATE TABLE "PART_PRIVS" (
+ "PART_GRANT_ID" BIGINT NOT NULL,
+ "CREATE_TIME" INTEGER NOT NULL,
+ "GRANT_OPTION" SMALLINT NOT NULL,
+ "GRANTOR" VARCHAR(128),
+ "GRANTOR_TYPE" VARCHAR(128),
+ "PART_ID" BIGINT,
+ "PRINCIPAL_NAME" VARCHAR(128),
+ "PRINCIPAL_TYPE" VARCHAR(128),
+ "PART_PRIV" VARCHAR(128));
+
+ALTER TABLE "PART_PRIVS" ADD CONSTRAINT "PART_PRIVS_FK1"
+ FOREIGN KEY ("PART_ID") REFERENCES "PARTITIONS" ("PART_ID")
+ ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "PART_PRIVS" ADD CONSTRAINT "PART_PRIVS_PK"
+ PRIMARY KEY ("PART_GRANT_ID");
+
+CREATE INDEX "PARTPRIVILEGEINDEX" ON "PART_PRIVS" (
+ "PART_ID", "PRINCIPAL_NAME", "PRINCIPAL_TYPE",
+ "PART_PRIV", "GRANTOR", "GRANTOR_TYPE");
+
+--
+-- PART_COL_PRIVS
+--
+CREATE TABLE "PART_COL_PRIVS" (
+ "PART_COLUMN_GRANT_ID" BIGINT NOT NULL,
+ "COLUMN_NAME" VARCHAR(128),
+ "CREATE_TIME" INTEGER NOT NULL,
+ "GRANT_OPTION" SMALLINT NOT NULL,
+ "GRANTOR" VARCHAR(128),
+ "GRANTOR_TYPE" VARCHAR(128),
+ "PART_ID" BIGINT,
+ "PRINCIPAL_NAME" VARCHAR(128),
+ "PRINCIPAL_TYPE" VARCHAR(128),
+ "PART_COL_PRIV" VARCHAR(128));
+
+ALTER TABLE "PART_COL_PRIVS" ADD CONSTRAINT "PART_COL_PRIVS_FK1"
+ FOREIGN KEY ("PART_ID") REFERENCES "PARTITIONS" ("PART_ID")
+ ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "PART_COL_PRIVS" ADD CONSTRAINT "PART_COL_PRIVS_PK"
+ PRIMARY KEY ("PART_COLUMN_GRANT_ID");
+
+CREATE INDEX "PARTITIONCOLUMNPRIVILEGEINDEX" ON "PART_COL_PRIVS" (
+ "PART_ID", "COLUMN_NAME", "PRINCIPAL_NAME", "PRINCIPAL_TYPE",
+ "PART_COL_PRIV", "GRANTOR", "GRANTOR_TYPE");
Modified: hive/trunk/metastore/scripts/upgrade/derby/README
URL: http://svn.apache.org/viewvc/hive/trunk/metastore/scripts/upgrade/derby/README?rev=1083139&r1=1083138&r2=1083139&view=diff
==============================================================================
--- hive/trunk/metastore/scripts/upgrade/derby/README (original)
+++ hive/trunk/metastore/scripts/upgrade/derby/README Sat Mar 19 04:01:52 2011
@@ -1,17 +1,108 @@
+Hive MetaStore Upgrade HowTo
+============================
-1) Shutdown your metastore instance.
+This document describes how to upgrade the schema of a Derby backed
+Hive MetaStore instance from one release version of Hive to another
+release version of Hive. For example, by following the steps listed
+below it is possible to upgrade a Hive 0.5.0 MetaStore schema to a
+Hive 0.7.0 MetaStore schema. Before attempting this project we
+strongly recommend that you read through all of the steps in this
+document and familiarize yourself with the required tools.
-2) Perform a backup of your Derby metastore database. Probably
- the easiest way of doing this is to just create a copy of the
- Derby database "metastore_db" directory.
+MetaStore Upgrade Steps
+=======================
-3) Execute the upgrade script:
- % ij
- ij version 10.4
- ij> CONNECT 'jdbc:derby:/Users/bob/hive/metastore_db;databaseName=metastore_db';
- ij> RUN 'upgrade-0.6.0.derby.sql';
- ij> quit;
+1) Shutdown your MetaStore instance and restrict access to the
+ MetaStore's Derby database. It is very important that no one else
+ accesses or modifies the contents of database while you are
+ performing the schema upgrade.
+
+2) Create a backup of your Derby metastore database. This will allow
+ you to revert any changes made during the upgrade process if
+ something goes wrong. The easiest way of accomplishing this task is
+ by creating a copy of the directory containing your Derby database.
+
+3) Dump your MetaStore database schema to a file using Derby's dblook utility:
+
+ % dblook -d <derby_db_url> -z "APP" > my-schema-x.y.z.derby.sql
+
+ Note that "APP" is Derby's default schema for user-created catalog
+ objects.
+
+
+4) The schema upgrade scripts assume that the schema you are upgrading
+ closely matches the official schema for your particular version of
+ Hive. The files in this directory with names like
+ "hive-schema-x.y.z.derby.sql" contain dumps of the official schemas
+ corresponding to each of the released versions of Hive. You can
+ determine differences between your schema and the official schema
+ by comparing the contents of the official dump with the schema dump
+ you created in the previous step. Note that due to a bug in Derby
+ the order in which the DDL statements appear is non-deterministic,
+ so simply diffing the two dumps is unlikely to result in useable
+ results. A simple workaround for this problem is to compare sorted
+ versions of the two schema dumps, e.g:
+
+ % sort hive-schema-x.y.z.derby.sql > hive-schema-x.y.z.derby.sql.sorted
+ % sort my-schema-x.y.z.derby.sql > my-schema-x.y.z.derby.sql.sorted
+ % diff hive-schema-x.y.z.derby.sql.sorted my-schema-x.y.z.derby.sql.sorted
+
+ Some differences are acceptable and will not interfere
+ with the upgrade process, but others need to be resolved manually
+ or the upgrade scripts will fail to complete.
+
+ * Missing Tables: Hive's default configuration causes the MetaStore
+ to create schema elements only when they are needed. Some tables
+ may be missing from your MetaStore schema if you have not created
+ the corresponding Hive catalog objects, e.g. the PARTITIONS table
+ will probably not exist if you have not created any table
+ partitions in your MetaStore. You MUST create these missing tables
+ before running the upgrade scripts. The easiest way to do this is
+ by executing the official schema DDL script against your
+ schema. You should expect most of the DDL statements to fail since
+ the table/constraint/index already exist.
+
+ * Reversed Column Constraint Names in the Same Table: Tables with
+ multiple constraints may have the names of the constraints
+ reversed. For example, the PARTITIONS table contains two foreign
+ key constraints named PARTITIONS_FK1 and PARTITIONS_FK2 which
+ reference SDS.SD_ID and TBLS.TBL_ID respectively. However, in your
+ schema you may find that PARTITIONS_FK1 references TBLS.TBL_ID and
+ PARTITIONS_FK2 references SDS.SD_ID. Either version is acceptable
+ -- the only requirement is that these constraints actually exist.
+
+ * Differences in Column/Constraint Names: Your schema may contain
+ tables with columns named "IDX" or unique keys named
+ "UNIQUE<tab_name>". If you find either of these in your schema you
+ will need to change the names to "INTEGER_IDX" and
+ "UNIQUE_<tab_name>" before running the upgrade scripts. For more
+ background on this issue please refer to HIVE-1435.
+
+5) You are now ready to run the schema upgrade scripts. If you are
+ upgrading from Hive 0.5.0 to Hive 0.6.0 you need to run the
+ upgrade-0.5.0-to-0.6.0.derby.sql script, but if you are upgrading
+ from 0.5.0 to 0.7.0 you will need to run the 0.5.0 to 0.6.0 upgrade
+ script followed by the 0.6.0 to 0.7.0 upgrade script.
NOTE: You may need to install the Derby 'ij' utility.
Look here for installation instructions:
http://db.apache.org/derby/docs/10.4/getstart/
+
+ % ij
+ ij version 10.4
+ ij> CONNECT 'jdbc:derby:/Users/bob/hive/metastore_db';
+ ij> RUN 'upgrade-0.5.0-to-0.6.0.derby.sql';
+ ij> RUN 'upgrade-0.6.0-to-0.7.0.derby.sql';
+ ij> quit;
+
+ These scripts should run to completion without any errors. If you
+ do encounter errors you need to analyze the cause and attempt to
+ trace it back to one of the preceding steps.
+
+6) The final step of the upgrade process is validating your freshly
+ upgraded schema against the official schema for your particular
+ version of Hive. This is accomplished by repeating steps (3) and
+ (4), but this time comparing against the official version of the
+ upgraded schema, e.g. if you upgraded the schema to Hive 0.7.0 then
+ you will want to compare your schema dump against the contents of
+ hive-schema-0.7.0.derby.sql
Added: hive/trunk/metastore/scripts/upgrade/derby/hive-schema-0.3.0.derby.sql
URL: http://svn.apache.org/viewvc/hive/trunk/metastore/scripts/upgrade/derby/hive-schema-0.3.0.derby.sql?rev=1083139&view=auto
==============================================================================
--- hive/trunk/metastore/scripts/upgrade/derby/hive-schema-0.3.0.derby.sql (added)
+++ hive/trunk/metastore/scripts/upgrade/derby/hive-schema-0.3.0.derby.sql Sat Mar 19 04:01:52 2011
@@ -0,0 +1,134 @@
+-- Timestamp: 2011-03-18 02:53:32.694
+-- Source database is: /Users/carl/metastore/derby/mdb-0.3.0
+-- Connection URL is: jdbc:derby:/Users/carl/metastore/derby/mdb-0.3.0
+-- appendLogs: false
+
+-- ----------------------------------------------
+-- DDL Statements for functions
+-- ----------------------------------------------
+
+CREATE FUNCTION "APP"."NUCLEUS_ASCII" (C CHAR(1)) RETURNS INTEGER LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA CALLED ON NULL INPUT EXTERNAL NAME 'org.datanucleus.store.rdbms.adapter.DerbySQLFunction.ascii' ;
+
+CREATE FUNCTION "APP"."NUCLEUS_MATCHES" (TEXT VARCHAR(8000),PATTERN VARCHAR(8000)) RETURNS INTEGER LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA CALLED ON NULL INPUT EXTERNAL NAME 'org.datanucleus.store.rdbms.adapter.DerbySQLFunction.matches' ;
+
+-- ----------------------------------------------
+-- DDL Statements for tables
+-- ----------------------------------------------
+
+CREATE TABLE "APP"."PARTITION_KEYS" ("TBL_ID" BIGINT NOT NULL, "PKEY_COMMENT" VARCHAR(767), "PKEY_NAME" VARCHAR(128) NOT NULL, "PKEY_TYPE" VARCHAR(767) NOT NULL, "INTEGER_IDX" INTEGER NOT NULL);
+
+CREATE TABLE "APP"."TYPE_FIELDS" ("TYPE_NAME" BIGINT NOT NULL, "COMMENT" VARCHAR(256), "FIELD_NAME" VARCHAR(128) NOT NULL, "FIELD_TYPE" VARCHAR(128) NOT NULL, "INTEGER_IDX" INTEGER NOT NULL);
+
+CREATE TABLE "APP"."DBS" ("DB_ID" BIGINT NOT NULL, "DESC" VARCHAR(767), "NAME" VARCHAR(128));
+
+CREATE TABLE "APP"."TABLE_PARAMS" ("TBL_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(767));
+
+CREATE TABLE "APP"."SORT_COLS" ("SD_ID" BIGINT NOT NULL, "COLUMN_NAME" VARCHAR(128), "ORDER" INTEGER NOT NULL, "INTEGER_IDX" INTEGER NOT NULL);
+
+CREATE TABLE "APP"."BUCKETING_COLS" ("SD_ID" BIGINT NOT NULL, "BUCKET_COL_NAME" VARCHAR(256), "INTEGER_IDX" INTEGER NOT NULL);
+
+CREATE TABLE "APP"."SERDE_PARAMS" ("SERDE_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(767));
+
+CREATE TABLE "APP"."TYPES" ("TYPES_ID" BIGINT NOT NULL, "TYPE_NAME" VARCHAR(128), "TYPE1" VARCHAR(128), "TYPE2" VARCHAR(128));
+
+CREATE TABLE "APP"."SERDES" ("SERDE_ID" BIGINT NOT NULL, "NAME" VARCHAR(128), "SLIB" VARCHAR(767));
+
+CREATE TABLE "APP"."SD_PARAMS" ("SD_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(767));
+
+CREATE TABLE "APP"."SDS" ("SD_ID" BIGINT NOT NULL, "INPUT_FORMAT" VARCHAR(256), "IS_COMPRESSED" CHAR(1) NOT NULL, "LOCATION" VARCHAR(767), "NUM_BUCKETS" INTEGER NOT NULL, "OUTPUT_FORMAT" VARCHAR(256), "SERDE_ID" BIGINT);
+
+CREATE TABLE "APP"."PARTITIONS" ("PART_ID" BIGINT NOT NULL, "CREATE_TIME" INTEGER NOT NULL, "LAST_ACCESS_TIME" INTEGER NOT NULL, "PART_NAME" VARCHAR(767), "SD_ID" BIGINT, "TBL_ID" BIGINT);
+
+CREATE TABLE "APP"."PARTITION_KEY_VALS" ("PART_ID" BIGINT NOT NULL, "PART_KEY_VAL" VARCHAR(256), "INTEGER_IDX" INTEGER NOT NULL);
+
+CREATE TABLE "APP"."COLUMNS" ("SD_ID" BIGINT NOT NULL, "COMMENT" VARCHAR(256), "COLUMN_NAME" VARCHAR(128) NOT NULL, "TYPE_NAME" VARCHAR(128) NOT NULL, "INTEGER_IDX" INTEGER NOT NULL);
+
+CREATE TABLE "APP"."TBLS" ("TBL_ID" BIGINT NOT NULL, "CREATE_TIME" INTEGER NOT NULL, "DB_ID" BIGINT, "LAST_ACCESS_TIME" INTEGER NOT NULL, "OWNER" VARCHAR(128), "RETENTION" INTEGER NOT NULL, "SD_ID" BIGINT, "TBL_NAME" VARCHAR(128));
+
+CREATE TABLE "APP"."PARTITION_PARAMS" ("PART_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(767));
+
+-- ----------------------------------------------
+-- DDL Statements for indexes
+-- ----------------------------------------------
+
+CREATE UNIQUE INDEX "APP"."UNIQUEPARTITION" ON "APP"."PARTITIONS" ("PART_NAME", "TBL_ID");
+
+CREATE UNIQUE INDEX "APP"."UNIQUETABLE" ON "APP"."TBLS" ("TBL_NAME", "DB_ID");
+
+CREATE UNIQUE INDEX "APP"."UNIQUE_TYPE" ON "APP"."TYPES" ("TYPE_NAME");
+
+CREATE UNIQUE INDEX "APP"."UNIQUE_DATABASE" ON "APP"."DBS" ("NAME");
+
+-- ----------------------------------------------
+-- DDL Statements for keys
+-- ----------------------------------------------
+
+-- primary/unique
+ALTER TABLE "APP"."SERDES" ADD CONSTRAINT "SERDES_PK" PRIMARY KEY ("SERDE_ID");
+
+ALTER TABLE "APP"."PARTITION_PARAMS" ADD CONSTRAINT "PARTITION_PARAMS_PK" PRIMARY KEY ("PART_ID", "PARAM_KEY");
+
+ALTER TABLE "APP"."PARTITION_KEYS" ADD CONSTRAINT "PARTITION_KEY_PK" PRIMARY KEY ("TBL_ID", "PKEY_NAME");
+
+ALTER TABLE "APP"."PARTITION_KEY_VALS" ADD CONSTRAINT "PARTITION_KEY_VALS_PK" PRIMARY KEY ("PART_ID", "INTEGER_IDX");
+
+ALTER TABLE "APP"."PARTITIONS" ADD CONSTRAINT "PARTITIONS_PK" PRIMARY KEY ("PART_ID");
+
+ALTER TABLE "APP"."TYPE_FIELDS" ADD CONSTRAINT "TYPE_FIELDS_PK" PRIMARY KEY ("TYPE_NAME", "FIELD_NAME");
+
+ALTER TABLE "APP"."TABLE_PARAMS" ADD CONSTRAINT "TABLE_PARAMS_PK" PRIMARY KEY ("TBL_ID", "PARAM_KEY");
+
+ALTER TABLE "APP"."TBLS" ADD CONSTRAINT "TBLS_PK" PRIMARY KEY ("TBL_ID");
+
+ALTER TABLE "APP"."SD_PARAMS" ADD CONSTRAINT "SD_PARAMS_PK" PRIMARY KEY ("SD_ID", "PARAM_KEY");
+
+ALTER TABLE "APP"."SERDE_PARAMS" ADD CONSTRAINT "SERDE_PARAMS_PK" PRIMARY KEY ("SERDE_ID", "PARAM_KEY");
+
+ALTER TABLE "APP"."TYPES" ADD CONSTRAINT "TYPES_PK" PRIMARY KEY ("TYPES_ID");
+
+ALTER TABLE "APP"."SDS" ADD CONSTRAINT "SDS_PK" PRIMARY KEY ("SD_ID");
+
+ALTER TABLE "APP"."DBS" ADD CONSTRAINT "DBS_PK" PRIMARY KEY ("DB_ID");
+
+ALTER TABLE "APP"."BUCKETING_COLS" ADD CONSTRAINT "BUCKETING_COLS_PK" PRIMARY KEY ("SD_ID", "INTEGER_IDX");
+
+ALTER TABLE "APP"."COLUMNS" ADD CONSTRAINT "COLUMNS_PK" PRIMARY KEY ("SD_ID", "COLUMN_NAME", "TYPE_NAME");
+
+ALTER TABLE "APP"."SORT_COLS" ADD CONSTRAINT "SORT_COLS_PK" PRIMARY KEY ("SD_ID", "INTEGER_IDX");
+
+-- foreign
+ALTER TABLE "APP"."PARTITION_PARAMS" ADD CONSTRAINT "PARTITION_PARAMS_FK1" FOREIGN KEY ("PART_ID") REFERENCES "APP"."PARTITIONS" ("PART_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."PARTITION_KEYS" ADD CONSTRAINT "PARTITION_KEYS_FK1" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."PARTITION_KEY_VALS" ADD CONSTRAINT "PARTITION_KEY_VALS_FK1" FOREIGN KEY ("PART_ID") REFERENCES "APP"."PARTITIONS" ("PART_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."PARTITIONS" ADD CONSTRAINT "PARTITIONS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."PARTITIONS" ADD CONSTRAINT "PARTITIONS_FK2" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."TYPE_FIELDS" ADD CONSTRAINT "TYPE_FIELDS_FK1" FOREIGN KEY ("TYPE_NAME") REFERENCES "APP"."TYPES" ("TYPES_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."TABLE_PARAMS" ADD CONSTRAINT "TABLE_PARAMS_FK1" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."TBLS" ADD CONSTRAINT "TBLS_FK2" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."TBLS" ADD CONSTRAINT "TBLS_FK1" FOREIGN KEY ("DB_ID") REFERENCES "APP"."DBS" ("DB_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."SD_PARAMS" ADD CONSTRAINT "SD_PARAMS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."SERDE_PARAMS" ADD CONSTRAINT "SERDE_PARAMS_FK1" FOREIGN KEY ("SERDE_ID") REFERENCES "APP"."SERDES" ("SERDE_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."SDS" ADD CONSTRAINT "SDS_FK1" FOREIGN KEY ("SERDE_ID") REFERENCES "APP"."SERDES" ("SERDE_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."BUCKETING_COLS" ADD CONSTRAINT "BUCKETING_COLS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."COLUMNS" ADD CONSTRAINT "COLUMNS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."SORT_COLS" ADD CONSTRAINT "SORT_COLS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+-- ----------------------------------------------
+-- DDL Statements for checks
+-- ----------------------------------------------
+
+ALTER TABLE "APP"."SDS" ADD CONSTRAINT "SQL110318025331380" CHECK (IS_COMPRESSED IN ('Y','N'));
Added: hive/trunk/metastore/scripts/upgrade/derby/hive-schema-0.4.0.derby.sql
URL: http://svn.apache.org/viewvc/hive/trunk/metastore/scripts/upgrade/derby/hive-schema-0.4.0.derby.sql?rev=1083139&view=auto
==============================================================================
--- hive/trunk/metastore/scripts/upgrade/derby/hive-schema-0.4.0.derby.sql (added)
+++ hive/trunk/metastore/scripts/upgrade/derby/hive-schema-0.4.0.derby.sql Sat Mar 19 04:01:52 2011
@@ -0,0 +1,134 @@
+-- Timestamp: 2011-03-18 02:53:50.745
+-- Source database is: /Users/carl/metastore/derby/mdb-0.4.0
+-- Connection URL is: jdbc:derby:/Users/carl/metastore/derby/mdb-0.4.0
+-- appendLogs: false
+
+-- ----------------------------------------------
+-- DDL Statements for functions
+-- ----------------------------------------------
+
+CREATE FUNCTION "APP"."NUCLEUS_ASCII" (C CHAR(1)) RETURNS INTEGER LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA CALLED ON NULL INPUT EXTERNAL NAME 'org.datanucleus.store.rdbms.adapter.DerbySQLFunction.ascii' ;
+
+CREATE FUNCTION "APP"."NUCLEUS_MATCHES" (TEXT VARCHAR(8000),PATTERN VARCHAR(8000)) RETURNS INTEGER LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA CALLED ON NULL INPUT EXTERNAL NAME 'org.datanucleus.store.rdbms.adapter.DerbySQLFunction.matches' ;
+
+-- ----------------------------------------------
+-- DDL Statements for tables
+-- ----------------------------------------------
+
+CREATE TABLE "APP"."SORT_COLS" ("SD_ID" BIGINT NOT NULL, "COLUMN_NAME" VARCHAR(128), "ORDER" INTEGER NOT NULL, "INTEGER_IDX" INTEGER NOT NULL);
+
+CREATE TABLE "APP"."SERDES" ("SERDE_ID" BIGINT NOT NULL, "NAME" VARCHAR(128), "SLIB" VARCHAR(767));
+
+CREATE TABLE "APP"."TBLS" ("TBL_ID" BIGINT NOT NULL, "CREATE_TIME" INTEGER NOT NULL, "DB_ID" BIGINT, "LAST_ACCESS_TIME" INTEGER NOT NULL, "OWNER" VARCHAR(128), "RETENTION" INTEGER NOT NULL, "SD_ID" BIGINT, "TBL_NAME" VARCHAR(128));
+
+CREATE TABLE "APP"."TABLE_PARAMS" ("TBL_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(767));
+
+CREATE TABLE "APP"."SERDE_PARAMS" ("SERDE_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(767));
+
+CREATE TABLE "APP"."TYPE_FIELDS" ("TYPE_NAME" BIGINT NOT NULL, "COMMENT" VARCHAR(256), "FIELD_NAME" VARCHAR(128) NOT NULL, "FIELD_TYPE" VARCHAR(128) NOT NULL, "INTEGER_IDX" INTEGER NOT NULL);
+
+CREATE TABLE "APP"."PARTITION_PARAMS" ("PART_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(767));
+
+CREATE TABLE "APP"."PARTITION_KEY_VALS" ("PART_ID" BIGINT NOT NULL, "PART_KEY_VAL" VARCHAR(256), "INTEGER_IDX" INTEGER NOT NULL);
+
+CREATE TABLE "APP"."COLUMNS" ("SD_ID" BIGINT NOT NULL, "COMMENT" VARCHAR(256), "COLUMN_NAME" VARCHAR(128) NOT NULL, "TYPE_NAME" VARCHAR(128) NOT NULL, "INTEGER_IDX" INTEGER NOT NULL);
+
+CREATE TABLE "APP"."BUCKETING_COLS" ("SD_ID" BIGINT NOT NULL, "BUCKET_COL_NAME" VARCHAR(256), "INTEGER_IDX" INTEGER NOT NULL);
+
+CREATE TABLE "APP"."SD_PARAMS" ("SD_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(767));
+
+CREATE TABLE "APP"."PARTITIONS" ("PART_ID" BIGINT NOT NULL, "CREATE_TIME" INTEGER NOT NULL, "LAST_ACCESS_TIME" INTEGER NOT NULL, "PART_NAME" VARCHAR(767), "SD_ID" BIGINT, "TBL_ID" BIGINT);
+
+CREATE TABLE "APP"."DBS" ("DB_ID" BIGINT NOT NULL, "DESC" VARCHAR(767), "NAME" VARCHAR(128));
+
+CREATE TABLE "APP"."TYPES" ("TYPES_ID" BIGINT NOT NULL, "TYPE_NAME" VARCHAR(128), "TYPE1" VARCHAR(128), "TYPE2" VARCHAR(128));
+
+CREATE TABLE "APP"."PARTITION_KEYS" ("TBL_ID" BIGINT NOT NULL, "PKEY_COMMENT" VARCHAR(767), "PKEY_NAME" VARCHAR(128) NOT NULL, "PKEY_TYPE" VARCHAR(767) NOT NULL, "INTEGER_IDX" INTEGER NOT NULL);
+
+CREATE TABLE "APP"."SDS" ("SD_ID" BIGINT NOT NULL, "INPUT_FORMAT" VARCHAR(256), "IS_COMPRESSED" CHAR(1) NOT NULL, "LOCATION" VARCHAR(767), "NUM_BUCKETS" INTEGER NOT NULL, "OUTPUT_FORMAT" VARCHAR(256), "SERDE_ID" BIGINT);
+
+-- ----------------------------------------------
+-- DDL Statements for indexes
+-- ----------------------------------------------
+
+CREATE UNIQUE INDEX "APP"."UNIQUEPARTITION" ON "APP"."PARTITIONS" ("PART_NAME", "TBL_ID");
+
+CREATE UNIQUE INDEX "APP"."UNIQUETABLE" ON "APP"."TBLS" ("TBL_NAME", "DB_ID");
+
+CREATE UNIQUE INDEX "APP"."UNIQUE_TYPE" ON "APP"."TYPES" ("TYPE_NAME");
+
+CREATE UNIQUE INDEX "APP"."UNIQUE_DATABASE" ON "APP"."DBS" ("NAME");
+
+-- ----------------------------------------------
+-- DDL Statements for keys
+-- ----------------------------------------------
+
+-- primary/unique
+ALTER TABLE "APP"."SERDES" ADD CONSTRAINT "SERDES_PK" PRIMARY KEY ("SERDE_ID");
+
+ALTER TABLE "APP"."TABLE_PARAMS" ADD CONSTRAINT "TABLE_PARAMS_PK" PRIMARY KEY ("TBL_ID", "PARAM_KEY");
+
+ALTER TABLE "APP"."SORT_COLS" ADD CONSTRAINT "SORT_COLS_PK" PRIMARY KEY ("SD_ID", "INTEGER_IDX");
+
+ALTER TABLE "APP"."COLUMNS" ADD CONSTRAINT "COLUMNS_PK" PRIMARY KEY ("SD_ID", "COLUMN_NAME", "TYPE_NAME");
+
+ALTER TABLE "APP"."PARTITIONS" ADD CONSTRAINT "PARTITIONS_PK" PRIMARY KEY ("PART_ID");
+
+ALTER TABLE "APP"."TYPE_FIELDS" ADD CONSTRAINT "TYPE_FIELDS_PK" PRIMARY KEY ("TYPE_NAME", "FIELD_NAME");
+
+ALTER TABLE "APP"."SERDE_PARAMS" ADD CONSTRAINT "SERDE_PARAMS_PK" PRIMARY KEY ("SERDE_ID", "PARAM_KEY");
+
+ALTER TABLE "APP"."TBLS" ADD CONSTRAINT "TBLS_PK" PRIMARY KEY ("TBL_ID");
+
+ALTER TABLE "APP"."PARTITION_PARAMS" ADD CONSTRAINT "PARTITION_PARAMS_PK" PRIMARY KEY ("PART_ID", "PARAM_KEY");
+
+ALTER TABLE "APP"."BUCKETING_COLS" ADD CONSTRAINT "BUCKETING_COLS_PK" PRIMARY KEY ("SD_ID", "INTEGER_IDX");
+
+ALTER TABLE "APP"."TYPES" ADD CONSTRAINT "TYPES_PK" PRIMARY KEY ("TYPES_ID");
+
+ALTER TABLE "APP"."SDS" ADD CONSTRAINT "SDS_PK" PRIMARY KEY ("SD_ID");
+
+ALTER TABLE "APP"."DBS" ADD CONSTRAINT "DBS_PK" PRIMARY KEY ("DB_ID");
+
+ALTER TABLE "APP"."SD_PARAMS" ADD CONSTRAINT "SD_PARAMS_PK" PRIMARY KEY ("SD_ID", "PARAM_KEY");
+
+ALTER TABLE "APP"."PARTITION_KEYS" ADD CONSTRAINT "PARTITION_KEY_PK" PRIMARY KEY ("TBL_ID", "PKEY_NAME");
+
+ALTER TABLE "APP"."PARTITION_KEY_VALS" ADD CONSTRAINT "PARTITION_KEY_VALS_PK" PRIMARY KEY ("PART_ID", "INTEGER_IDX");
+
+-- foreign
+ALTER TABLE "APP"."TABLE_PARAMS" ADD CONSTRAINT "TABLE_PARAMS_FK1" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."SORT_COLS" ADD CONSTRAINT "SORT_COLS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."COLUMNS" ADD CONSTRAINT "COLUMNS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."PARTITIONS" ADD CONSTRAINT "PARTITIONS_FK1" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."PARTITIONS" ADD CONSTRAINT "PARTITIONS_FK2" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."TYPE_FIELDS" ADD CONSTRAINT "TYPE_FIELDS_FK1" FOREIGN KEY ("TYPE_NAME") REFERENCES "APP"."TYPES" ("TYPES_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."SERDE_PARAMS" ADD CONSTRAINT "SERDE_PARAMS_FK1" FOREIGN KEY ("SERDE_ID") REFERENCES "APP"."SERDES" ("SERDE_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."TBLS" ADD CONSTRAINT "TBLS_FK2" FOREIGN KEY ("DB_ID") REFERENCES "APP"."DBS" ("DB_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."TBLS" ADD CONSTRAINT "TBLS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."PARTITION_PARAMS" ADD CONSTRAINT "PARTITION_PARAMS_FK1" FOREIGN KEY ("PART_ID") REFERENCES "APP"."PARTITIONS" ("PART_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."BUCKETING_COLS" ADD CONSTRAINT "BUCKETING_COLS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."SDS" ADD CONSTRAINT "SDS_FK1" FOREIGN KEY ("SERDE_ID") REFERENCES "APP"."SERDES" ("SERDE_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."SD_PARAMS" ADD CONSTRAINT "SD_PARAMS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."PARTITION_KEYS" ADD CONSTRAINT "PARTITION_KEYS_FK1" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."PARTITION_KEY_VALS" ADD CONSTRAINT "PARTITION_KEY_VALS_FK1" FOREIGN KEY ("PART_ID") REFERENCES "APP"."PARTITIONS" ("PART_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+-- ----------------------------------------------
+-- DDL Statements for checks
+-- ----------------------------------------------
+
+ALTER TABLE "APP"."SDS" ADD CONSTRAINT "SQL110318025349520" CHECK (IS_COMPRESSED IN ('Y','N'));
Added: hive/trunk/metastore/scripts/upgrade/derby/hive-schema-0.4.1.derby.sql
URL: http://svn.apache.org/viewvc/hive/trunk/metastore/scripts/upgrade/derby/hive-schema-0.4.1.derby.sql?rev=1083139&view=auto
==============================================================================
--- hive/trunk/metastore/scripts/upgrade/derby/hive-schema-0.4.1.derby.sql (added)
+++ hive/trunk/metastore/scripts/upgrade/derby/hive-schema-0.4.1.derby.sql Sat Mar 19 04:01:52 2011
@@ -0,0 +1,134 @@
+-- Timestamp: 2011-03-18 02:54:08.361
+-- Source database is: /Users/carl/metastore/derby/mdb-0.4.1
+-- Connection URL is: jdbc:derby:/Users/carl/metastore/derby/mdb-0.4.1
+-- appendLogs: false
+
+-- ----------------------------------------------
+-- DDL Statements for functions
+-- ----------------------------------------------
+
+CREATE FUNCTION "APP"."NUCLEUS_ASCII" (C CHAR(1)) RETURNS INTEGER LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA CALLED ON NULL INPUT EXTERNAL NAME 'org.datanucleus.store.rdbms.adapter.DerbySQLFunction.ascii' ;
+
+CREATE FUNCTION "APP"."NUCLEUS_MATCHES" (TEXT VARCHAR(8000),PATTERN VARCHAR(8000)) RETURNS INTEGER LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA CALLED ON NULL INPUT EXTERNAL NAME 'org.datanucleus.store.rdbms.adapter.DerbySQLFunction.matches' ;
+
+-- ----------------------------------------------
+-- DDL Statements for tables
+-- ----------------------------------------------
+
+CREATE TABLE "APP"."DBS" ("DB_ID" BIGINT NOT NULL, "DESC" VARCHAR(767), "NAME" VARCHAR(128));
+
+CREATE TABLE "APP"."PARTITION_KEYS" ("TBL_ID" BIGINT NOT NULL, "PKEY_COMMENT" VARCHAR(767), "PKEY_NAME" VARCHAR(128) NOT NULL, "PKEY_TYPE" VARCHAR(767) NOT NULL, "INTEGER_IDX" INTEGER NOT NULL);
+
+CREATE TABLE "APP"."TBLS" ("TBL_ID" BIGINT NOT NULL, "CREATE_TIME" INTEGER NOT NULL, "DB_ID" BIGINT, "LAST_ACCESS_TIME" INTEGER NOT NULL, "OWNER" VARCHAR(128), "RETENTION" INTEGER NOT NULL, "SD_ID" BIGINT, "TBL_NAME" VARCHAR(128));
+
+CREATE TABLE "APP"."PARTITION_PARAMS" ("PART_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(767));
+
+CREATE TABLE "APP"."SERDES" ("SERDE_ID" BIGINT NOT NULL, "NAME" VARCHAR(128), "SLIB" VARCHAR(767));
+
+CREATE TABLE "APP"."SORT_COLS" ("SD_ID" BIGINT NOT NULL, "COLUMN_NAME" VARCHAR(128), "ORDER" INTEGER NOT NULL, "INTEGER_IDX" INTEGER NOT NULL);
+
+CREATE TABLE "APP"."BUCKETING_COLS" ("SD_ID" BIGINT NOT NULL, "BUCKET_COL_NAME" VARCHAR(256), "INTEGER_IDX" INTEGER NOT NULL);
+
+CREATE TABLE "APP"."TABLE_PARAMS" ("TBL_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(767));
+
+CREATE TABLE "APP"."TYPE_FIELDS" ("TYPE_NAME" BIGINT NOT NULL, "COMMENT" VARCHAR(256), "FIELD_NAME" VARCHAR(128) NOT NULL, "FIELD_TYPE" VARCHAR(128) NOT NULL, "INTEGER_IDX" INTEGER NOT NULL);
+
+CREATE TABLE "APP"."COLUMNS" ("SD_ID" BIGINT NOT NULL, "COMMENT" VARCHAR(256), "COLUMN_NAME" VARCHAR(128) NOT NULL, "TYPE_NAME" VARCHAR(128) NOT NULL, "INTEGER_IDX" INTEGER NOT NULL);
+
+CREATE TABLE "APP"."PARTITION_KEY_VALS" ("PART_ID" BIGINT NOT NULL, "PART_KEY_VAL" VARCHAR(256), "INTEGER_IDX" INTEGER NOT NULL);
+
+CREATE TABLE "APP"."PARTITIONS" ("PART_ID" BIGINT NOT NULL, "CREATE_TIME" INTEGER NOT NULL, "LAST_ACCESS_TIME" INTEGER NOT NULL, "PART_NAME" VARCHAR(767), "SD_ID" BIGINT, "TBL_ID" BIGINT);
+
+CREATE TABLE "APP"."SDS" ("SD_ID" BIGINT NOT NULL, "INPUT_FORMAT" VARCHAR(256), "IS_COMPRESSED" CHAR(1) NOT NULL, "LOCATION" VARCHAR(767), "NUM_BUCKETS" INTEGER NOT NULL, "OUTPUT_FORMAT" VARCHAR(256), "SERDE_ID" BIGINT);
+
+CREATE TABLE "APP"."TYPES" ("TYPES_ID" BIGINT NOT NULL, "TYPE_NAME" VARCHAR(128), "TYPE1" VARCHAR(128), "TYPE2" VARCHAR(128));
+
+CREATE TABLE "APP"."SD_PARAMS" ("SD_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(767));
+
+CREATE TABLE "APP"."SERDE_PARAMS" ("SERDE_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(767));
+
+-- ----------------------------------------------
+-- DDL Statements for indexes
+-- ----------------------------------------------
+
+CREATE UNIQUE INDEX "APP"."UNIQUEPARTITION" ON "APP"."PARTITIONS" ("PART_NAME", "TBL_ID");
+
+CREATE UNIQUE INDEX "APP"."UNIQUETABLE" ON "APP"."TBLS" ("TBL_NAME", "DB_ID");
+
+CREATE UNIQUE INDEX "APP"."UNIQUE_TYPE" ON "APP"."TYPES" ("TYPE_NAME");
+
+CREATE UNIQUE INDEX "APP"."UNIQUE_DATABASE" ON "APP"."DBS" ("NAME");
+
+-- ----------------------------------------------
+-- DDL Statements for keys
+-- ----------------------------------------------
+
+-- primary/unique
+ALTER TABLE "APP"."SERDES" ADD CONSTRAINT "SERDES_PK" PRIMARY KEY ("SERDE_ID");
+
+ALTER TABLE "APP"."BUCKETING_COLS" ADD CONSTRAINT "BUCKETING_COLS_PK" PRIMARY KEY ("SD_ID", "INTEGER_IDX");
+
+ALTER TABLE "APP"."TABLE_PARAMS" ADD CONSTRAINT "TABLE_PARAMS_PK" PRIMARY KEY ("TBL_ID", "PARAM_KEY");
+
+ALTER TABLE "APP"."SD_PARAMS" ADD CONSTRAINT "SD_PARAMS_PK" PRIMARY KEY ("SD_ID", "PARAM_KEY");
+
+ALTER TABLE "APP"."PARTITIONS" ADD CONSTRAINT "PARTITIONS_PK" PRIMARY KEY ("PART_ID");
+
+ALTER TABLE "APP"."SERDE_PARAMS" ADD CONSTRAINT "SERDE_PARAMS_PK" PRIMARY KEY ("SERDE_ID", "PARAM_KEY");
+
+ALTER TABLE "APP"."SORT_COLS" ADD CONSTRAINT "SORT_COLS_PK" PRIMARY KEY ("SD_ID", "INTEGER_IDX");
+
+ALTER TABLE "APP"."TBLS" ADD CONSTRAINT "TBLS_PK" PRIMARY KEY ("TBL_ID");
+
+ALTER TABLE "APP"."PARTITION_KEY_VALS" ADD CONSTRAINT "PARTITION_KEY_VALS_PK" PRIMARY KEY ("PART_ID", "INTEGER_IDX");
+
+ALTER TABLE "APP"."PARTITION_KEYS" ADD CONSTRAINT "PARTITION_KEY_PK" PRIMARY KEY ("TBL_ID", "PKEY_NAME");
+
+ALTER TABLE "APP"."TYPES" ADD CONSTRAINT "TYPES_PK" PRIMARY KEY ("TYPES_ID");
+
+ALTER TABLE "APP"."SDS" ADD CONSTRAINT "SDS_PK" PRIMARY KEY ("SD_ID");
+
+ALTER TABLE "APP"."DBS" ADD CONSTRAINT "DBS_PK" PRIMARY KEY ("DB_ID");
+
+ALTER TABLE "APP"."PARTITION_PARAMS" ADD CONSTRAINT "PARTITION_PARAMS_PK" PRIMARY KEY ("PART_ID", "PARAM_KEY");
+
+ALTER TABLE "APP"."TYPE_FIELDS" ADD CONSTRAINT "TYPE_FIELDS_PK" PRIMARY KEY ("TYPE_NAME", "FIELD_NAME");
+
+ALTER TABLE "APP"."COLUMNS" ADD CONSTRAINT "COLUMNS_PK" PRIMARY KEY ("SD_ID", "COLUMN_NAME", "TYPE_NAME");
+
+-- foreign
+ALTER TABLE "APP"."BUCKETING_COLS" ADD CONSTRAINT "BUCKETING_COLS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."TABLE_PARAMS" ADD CONSTRAINT "TABLE_PARAMS_FK1" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."SD_PARAMS" ADD CONSTRAINT "SD_PARAMS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."PARTITIONS" ADD CONSTRAINT "PARTITIONS_FK1" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."PARTITIONS" ADD CONSTRAINT "PARTITIONS_FK2" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."SERDE_PARAMS" ADD CONSTRAINT "SERDE_PARAMS_FK1" FOREIGN KEY ("SERDE_ID") REFERENCES "APP"."SERDES" ("SERDE_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."SORT_COLS" ADD CONSTRAINT "SORT_COLS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."TBLS" ADD CONSTRAINT "TBLS_FK2" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."TBLS" ADD CONSTRAINT "TBLS_FK1" FOREIGN KEY ("DB_ID") REFERENCES "APP"."DBS" ("DB_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."PARTITION_KEY_VALS" ADD CONSTRAINT "PARTITION_KEY_VALS_FK1" FOREIGN KEY ("PART_ID") REFERENCES "APP"."PARTITIONS" ("PART_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."PARTITION_KEYS" ADD CONSTRAINT "PARTITION_KEYS_FK1" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."SDS" ADD CONSTRAINT "SDS_FK1" FOREIGN KEY ("SERDE_ID") REFERENCES "APP"."SERDES" ("SERDE_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."PARTITION_PARAMS" ADD CONSTRAINT "PARTITION_PARAMS_FK1" FOREIGN KEY ("PART_ID") REFERENCES "APP"."PARTITIONS" ("PART_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."TYPE_FIELDS" ADD CONSTRAINT "TYPE_FIELDS_FK1" FOREIGN KEY ("TYPE_NAME") REFERENCES "APP"."TYPES" ("TYPES_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."COLUMNS" ADD CONSTRAINT "COLUMNS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+-- ----------------------------------------------
+-- DDL Statements for checks
+-- ----------------------------------------------
+
+ALTER TABLE "APP"."SDS" ADD CONSTRAINT "SQL110318025407120" CHECK (IS_COMPRESSED IN ('Y','N'));
Added: hive/trunk/metastore/scripts/upgrade/derby/hive-schema-0.5.0.derby.sql
URL: http://svn.apache.org/viewvc/hive/trunk/metastore/scripts/upgrade/derby/hive-schema-0.5.0.derby.sql?rev=1083139&view=auto
==============================================================================
--- hive/trunk/metastore/scripts/upgrade/derby/hive-schema-0.5.0.derby.sql (added)
+++ hive/trunk/metastore/scripts/upgrade/derby/hive-schema-0.5.0.derby.sql Sat Mar 19 04:01:52 2011
@@ -0,0 +1,134 @@
+-- Timestamp: 2011-03-18 02:54:30.078
+-- Source database is: /Users/carl/metastore/derby/mdb-0.5.0
+-- Connection URL is: jdbc:derby:/Users/carl/metastore/derby/mdb-0.5.0
+-- appendLogs: false
+
+-- ----------------------------------------------
+-- DDL Statements for functions
+-- ----------------------------------------------
+
+CREATE FUNCTION "APP"."NUCLEUS_ASCII" (C CHAR(1)) RETURNS INTEGER LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA CALLED ON NULL INPUT EXTERNAL NAME 'org.datanucleus.store.rdbms.adapter.DerbySQLFunction.ascii' ;
+
+CREATE FUNCTION "APP"."NUCLEUS_MATCHES" (TEXT VARCHAR(8000),PATTERN VARCHAR(8000)) RETURNS INTEGER LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA CALLED ON NULL INPUT EXTERNAL NAME 'org.datanucleus.store.rdbms.adapter.DerbySQLFunction.matches' ;
+
+-- ----------------------------------------------
+-- DDL Statements for tables
+-- ----------------------------------------------
+
+CREATE TABLE "APP"."TYPE_FIELDS" ("TYPE_NAME" BIGINT NOT NULL, "COMMENT" VARCHAR(256), "FIELD_NAME" VARCHAR(128) NOT NULL, "FIELD_TYPE" VARCHAR(128) NOT NULL, "INTEGER_IDX" INTEGER NOT NULL);
+
+CREATE TABLE "APP"."SERDES" ("SERDE_ID" BIGINT NOT NULL, "NAME" VARCHAR(128), "SLIB" VARCHAR(767));
+
+CREATE TABLE "APP"."TABLE_PARAMS" ("TBL_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(767));
+
+CREATE TABLE "APP"."PARTITION_PARAMS" ("PART_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(767));
+
+CREATE TABLE "APP"."TYPES" ("TYPES_ID" BIGINT NOT NULL, "TYPE_NAME" VARCHAR(128), "TYPE1" VARCHAR(128), "TYPE2" VARCHAR(128));
+
+CREATE TABLE "APP"."SERDE_PARAMS" ("SERDE_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(767));
+
+CREATE TABLE "APP"."SD_PARAMS" ("SD_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(767));
+
+CREATE TABLE "APP"."COLUMNS" ("SD_ID" BIGINT NOT NULL, "COMMENT" VARCHAR(256), "COLUMN_NAME" VARCHAR(128) NOT NULL, "TYPE_NAME" VARCHAR(128) NOT NULL, "INTEGER_IDX" INTEGER NOT NULL);
+
+CREATE TABLE "APP"."BUCKETING_COLS" ("SD_ID" BIGINT NOT NULL, "BUCKET_COL_NAME" VARCHAR(256), "INTEGER_IDX" INTEGER NOT NULL);
+
+CREATE TABLE "APP"."PARTITIONS" ("PART_ID" BIGINT NOT NULL, "CREATE_TIME" INTEGER NOT NULL, "LAST_ACCESS_TIME" INTEGER NOT NULL, "PART_NAME" VARCHAR(767), "SD_ID" BIGINT, "TBL_ID" BIGINT);
+
+CREATE TABLE "APP"."SORT_COLS" ("SD_ID" BIGINT NOT NULL, "COLUMN_NAME" VARCHAR(128), "ORDER" INTEGER NOT NULL, "INTEGER_IDX" INTEGER NOT NULL);
+
+CREATE TABLE "APP"."DBS" ("DB_ID" BIGINT NOT NULL, "DESC" VARCHAR(767), "NAME" VARCHAR(128));
+
+CREATE TABLE "APP"."PARTITION_KEY_VALS" ("PART_ID" BIGINT NOT NULL, "PART_KEY_VAL" VARCHAR(256), "INTEGER_IDX" INTEGER NOT NULL);
+
+CREATE TABLE "APP"."SDS" ("SD_ID" BIGINT NOT NULL, "INPUT_FORMAT" VARCHAR(256), "IS_COMPRESSED" CHAR(1) NOT NULL, "LOCATION" VARCHAR(767), "NUM_BUCKETS" INTEGER NOT NULL, "OUTPUT_FORMAT" VARCHAR(256), "SERDE_ID" BIGINT);
+
+CREATE TABLE "APP"."PARTITION_KEYS" ("TBL_ID" BIGINT NOT NULL, "PKEY_COMMENT" VARCHAR(767), "PKEY_NAME" VARCHAR(128) NOT NULL, "PKEY_TYPE" VARCHAR(767) NOT NULL, "INTEGER_IDX" INTEGER NOT NULL);
+
+CREATE TABLE "APP"."TBLS" ("TBL_ID" BIGINT NOT NULL, "CREATE_TIME" INTEGER NOT NULL, "DB_ID" BIGINT, "LAST_ACCESS_TIME" INTEGER NOT NULL, "OWNER" VARCHAR(128), "RETENTION" INTEGER NOT NULL, "SD_ID" BIGINT, "TBL_NAME" VARCHAR(128));
+
+-- ----------------------------------------------
+-- DDL Statements for indexes
+-- ----------------------------------------------
+
+CREATE UNIQUE INDEX "APP"."UNIQUEPARTITION" ON "APP"."PARTITIONS" ("PART_NAME", "TBL_ID");
+
+CREATE UNIQUE INDEX "APP"."UNIQUETABLE" ON "APP"."TBLS" ("TBL_NAME", "DB_ID");
+
+CREATE UNIQUE INDEX "APP"."UNIQUE_TYPE" ON "APP"."TYPES" ("TYPE_NAME");
+
+CREATE UNIQUE INDEX "APP"."UNIQUE_DATABASE" ON "APP"."DBS" ("NAME");
+
+-- ----------------------------------------------
+-- DDL Statements for keys
+-- ----------------------------------------------
+
+-- primary/unique
+ALTER TABLE "APP"."SERDES" ADD CONSTRAINT "SERDES_PK" PRIMARY KEY ("SERDE_ID");
+
+ALTER TABLE "APP"."BUCKETING_COLS" ADD CONSTRAINT "BUCKETING_COLS_PK" PRIMARY KEY ("SD_ID", "INTEGER_IDX");
+
+ALTER TABLE "APP"."SORT_COLS" ADD CONSTRAINT "SORT_COLS_PK" PRIMARY KEY ("SD_ID", "INTEGER_IDX");
+
+ALTER TABLE "APP"."SD_PARAMS" ADD CONSTRAINT "SD_PARAMS_PK" PRIMARY KEY ("SD_ID", "PARAM_KEY");
+
+ALTER TABLE "APP"."PARTITIONS" ADD CONSTRAINT "PARTITIONS_PK" PRIMARY KEY ("PART_ID");
+
+ALTER TABLE "APP"."TYPE_FIELDS" ADD CONSTRAINT "TYPE_FIELDS_PK" PRIMARY KEY ("TYPE_NAME", "FIELD_NAME");
+
+ALTER TABLE "APP"."PARTITION_KEY_VALS" ADD CONSTRAINT "PARTITION_KEY_VALS_PK" PRIMARY KEY ("PART_ID", "INTEGER_IDX");
+
+ALTER TABLE "APP"."TBLS" ADD CONSTRAINT "TBLS_PK" PRIMARY KEY ("TBL_ID");
+
+ALTER TABLE "APP"."SERDE_PARAMS" ADD CONSTRAINT "SERDE_PARAMS_PK" PRIMARY KEY ("SERDE_ID", "PARAM_KEY");
+
+ALTER TABLE "APP"."COLUMNS" ADD CONSTRAINT "COLUMNS_PK" PRIMARY KEY ("SD_ID", "COLUMN_NAME", "TYPE_NAME");
+
+ALTER TABLE "APP"."TYPES" ADD CONSTRAINT "TYPES_PK" PRIMARY KEY ("TYPES_ID");
+
+ALTER TABLE "APP"."SDS" ADD CONSTRAINT "SDS_PK" PRIMARY KEY ("SD_ID");
+
+ALTER TABLE "APP"."DBS" ADD CONSTRAINT "DBS_PK" PRIMARY KEY ("DB_ID");
+
+ALTER TABLE "APP"."PARTITION_KEYS" ADD CONSTRAINT "PARTITION_KEY_PK" PRIMARY KEY ("TBL_ID", "PKEY_NAME");
+
+ALTER TABLE "APP"."TABLE_PARAMS" ADD CONSTRAINT "TABLE_PARAMS_PK" PRIMARY KEY ("TBL_ID", "PARAM_KEY");
+
+ALTER TABLE "APP"."PARTITION_PARAMS" ADD CONSTRAINT "PARTITION_PARAMS_PK" PRIMARY KEY ("PART_ID", "PARAM_KEY");
+
+-- foreign
+ALTER TABLE "APP"."BUCKETING_COLS" ADD CONSTRAINT "BUCKETING_COLS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."SORT_COLS" ADD CONSTRAINT "SORT_COLS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."SD_PARAMS" ADD CONSTRAINT "SD_PARAMS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."PARTITIONS" ADD CONSTRAINT "PARTITIONS_FK1" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."PARTITIONS" ADD CONSTRAINT "PARTITIONS_FK2" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."TYPE_FIELDS" ADD CONSTRAINT "TYPE_FIELDS_FK1" FOREIGN KEY ("TYPE_NAME") REFERENCES "APP"."TYPES" ("TYPES_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."PARTITION_KEY_VALS" ADD CONSTRAINT "PARTITION_KEY_VALS_FK1" FOREIGN KEY ("PART_ID") REFERENCES "APP"."PARTITIONS" ("PART_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."TBLS" ADD CONSTRAINT "TBLS_FK2" FOREIGN KEY ("DB_ID") REFERENCES "APP"."DBS" ("DB_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."TBLS" ADD CONSTRAINT "TBLS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."SERDE_PARAMS" ADD CONSTRAINT "SERDE_PARAMS_FK1" FOREIGN KEY ("SERDE_ID") REFERENCES "APP"."SERDES" ("SERDE_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."COLUMNS" ADD CONSTRAINT "COLUMNS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."SDS" ADD CONSTRAINT "SDS_FK1" FOREIGN KEY ("SERDE_ID") REFERENCES "APP"."SERDES" ("SERDE_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."PARTITION_KEYS" ADD CONSTRAINT "PARTITION_KEYS_FK1" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."TABLE_PARAMS" ADD CONSTRAINT "TABLE_PARAMS_FK1" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."PARTITION_PARAMS" ADD CONSTRAINT "PARTITION_PARAMS_FK1" FOREIGN KEY ("PART_ID") REFERENCES "APP"."PARTITIONS" ("PART_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+-- ----------------------------------------------
+-- DDL Statements for checks
+-- ----------------------------------------------
+
+ALTER TABLE "APP"."SDS" ADD CONSTRAINT "SQL110318025428840" CHECK (IS_COMPRESSED IN ('Y','N'));
Added: hive/trunk/metastore/scripts/upgrade/derby/hive-schema-0.6.0.derby.sql
URL: http://svn.apache.org/viewvc/hive/trunk/metastore/scripts/upgrade/derby/hive-schema-0.6.0.derby.sql?rev=1083139&view=auto
==============================================================================
--- hive/trunk/metastore/scripts/upgrade/derby/hive-schema-0.6.0.derby.sql (added)
+++ hive/trunk/metastore/scripts/upgrade/derby/hive-schema-0.6.0.derby.sql Sat Mar 19 04:01:52 2011
@@ -0,0 +1,134 @@
+-- Timestamp: 2011-03-18 02:54:47.993
+-- Source database is: /Users/carl/metastore/derby/mdb-0.6.0
+-- Connection URL is: jdbc:derby:/Users/carl/metastore/derby/mdb-0.6.0
+-- appendLogs: false
+
+-- ----------------------------------------------
+-- DDL Statements for functions
+-- ----------------------------------------------
+
+CREATE FUNCTION "APP"."NUCLEUS_ASCII" (C CHAR(1)) RETURNS INTEGER LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA CALLED ON NULL INPUT EXTERNAL NAME 'org.datanucleus.store.rdbms.adapter.DerbySQLFunction.ascii' ;
+
+CREATE FUNCTION "APP"."NUCLEUS_MATCHES" (TEXT VARCHAR(8000),PATTERN VARCHAR(8000)) RETURNS INTEGER LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA CALLED ON NULL INPUT EXTERNAL NAME 'org.datanucleus.store.rdbms.adapter.DerbySQLFunction.matches' ;
+
+-- ----------------------------------------------
+-- DDL Statements for tables
+-- ----------------------------------------------
+
+CREATE TABLE "APP"."PARTITION_KEY_VALS" ("PART_ID" BIGINT NOT NULL, "PART_KEY_VAL" VARCHAR(256), "INTEGER_IDX" INTEGER NOT NULL);
+
+CREATE TABLE "APP"."TYPE_FIELDS" ("TYPE_NAME" BIGINT NOT NULL, "COMMENT" VARCHAR(256), "FIELD_NAME" VARCHAR(128) NOT NULL, "FIELD_TYPE" VARCHAR(767) NOT NULL, "INTEGER_IDX" INTEGER NOT NULL);
+
+CREATE TABLE "APP"."SD_PARAMS" ("SD_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(4000));
+
+CREATE TABLE "APP"."SERDE_PARAMS" ("SERDE_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(4000));
+
+CREATE TABLE "APP"."PARTITIONS" ("PART_ID" BIGINT NOT NULL, "CREATE_TIME" INTEGER NOT NULL, "LAST_ACCESS_TIME" INTEGER NOT NULL, "PART_NAME" VARCHAR(767), "SD_ID" BIGINT, "TBL_ID" BIGINT);
+
+CREATE TABLE "APP"."SDS" ("SD_ID" BIGINT NOT NULL, "INPUT_FORMAT" VARCHAR(4000), "IS_COMPRESSED" CHAR(1) NOT NULL, "LOCATION" VARCHAR(4000), "NUM_BUCKETS" INTEGER NOT NULL, "OUTPUT_FORMAT" VARCHAR(4000), "SERDE_ID" BIGINT);
+
+CREATE TABLE "APP"."TABLE_PARAMS" ("TBL_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(4000));
+
+CREATE TABLE "APP"."COLUMNS" ("SD_ID" BIGINT NOT NULL, "COMMENT" VARCHAR(256), "COLUMN_NAME" VARCHAR(128) NOT NULL, "TYPE_NAME" VARCHAR(4000) NOT NULL, "INTEGER_IDX" INTEGER NOT NULL);
+
+CREATE TABLE "APP"."PARTITION_KEYS" ("TBL_ID" BIGINT NOT NULL, "PKEY_COMMENT" VARCHAR(4000), "PKEY_NAME" VARCHAR(128) NOT NULL, "PKEY_TYPE" VARCHAR(767) NOT NULL, "INTEGER_IDX" INTEGER NOT NULL);
+
+CREATE TABLE "APP"."PARTITION_PARAMS" ("PART_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(4000));
+
+CREATE TABLE "APP"."TYPES" ("TYPES_ID" BIGINT NOT NULL, "TYPE_NAME" VARCHAR(128), "TYPE1" VARCHAR(767), "TYPE2" VARCHAR(767));
+
+CREATE TABLE "APP"."BUCKETING_COLS" ("SD_ID" BIGINT NOT NULL, "BUCKET_COL_NAME" VARCHAR(256), "INTEGER_IDX" INTEGER NOT NULL);
+
+CREATE TABLE "APP"."SERDES" ("SERDE_ID" BIGINT NOT NULL, "NAME" VARCHAR(128), "SLIB" VARCHAR(4000));
+
+CREATE TABLE "APP"."TBLS" ("TBL_ID" BIGINT NOT NULL, "CREATE_TIME" INTEGER NOT NULL, "DB_ID" BIGINT, "LAST_ACCESS_TIME" INTEGER NOT NULL, "OWNER" VARCHAR(767), "RETENTION" INTEGER NOT NULL, "SD_ID" BIGINT, "TBL_NAME" VARCHAR(128), "TBL_TYPE" VARCHAR(128), "VIEW_EXPANDED_TEXT" LONG VARCHAR, "VIEW_ORIGINAL_TEXT" LONG VARCHAR);
+
+CREATE TABLE "APP"."SORT_COLS" ("SD_ID" BIGINT NOT NULL, "COLUMN_NAME" VARCHAR(128), "ORDER" INTEGER NOT NULL, "INTEGER_IDX" INTEGER NOT NULL);
+
+CREATE TABLE "APP"."DBS" ("DB_ID" BIGINT NOT NULL, "DESC" VARCHAR(4000), "DB_LOCATION_URI" VARCHAR(4000) NOT NULL, "NAME" VARCHAR(128));
+
+-- ----------------------------------------------
+-- DDL Statements for indexes
+-- ----------------------------------------------
+
+CREATE UNIQUE INDEX "APP"."UNIQUEPARTITION" ON "APP"."PARTITIONS" ("PART_NAME", "TBL_ID");
+
+CREATE UNIQUE INDEX "APP"."UNIQUETABLE" ON "APP"."TBLS" ("TBL_NAME", "DB_ID");
+
+CREATE UNIQUE INDEX "APP"."UNIQUE_TYPE" ON "APP"."TYPES" ("TYPE_NAME");
+
+CREATE UNIQUE INDEX "APP"."UNIQUE_DATABASE" ON "APP"."DBS" ("NAME");
+
+-- ----------------------------------------------
+-- DDL Statements for keys
+-- ----------------------------------------------
+
+-- primary/unique
+ALTER TABLE "APP"."SERDES" ADD CONSTRAINT "SERDES_PK" PRIMARY KEY ("SERDE_ID");
+
+ALTER TABLE "APP"."COLUMNS" ADD CONSTRAINT "COLUMNS_PK" PRIMARY KEY ("SD_ID", "COLUMN_NAME");
+
+ALTER TABLE "APP"."PARTITION_KEY_VALS" ADD CONSTRAINT "PARTITION_KEY_VALS_PK" PRIMARY KEY ("PART_ID", "INTEGER_IDX");
+
+ALTER TABLE "APP"."BUCKETING_COLS" ADD CONSTRAINT "BUCKETING_COLS_PK" PRIMARY KEY ("SD_ID", "INTEGER_IDX");
+
+ALTER TABLE "APP"."PARTITIONS" ADD CONSTRAINT "PARTITIONS_PK" PRIMARY KEY ("PART_ID");
+
+ALTER TABLE "APP"."SORT_COLS" ADD CONSTRAINT "SORT_COLS_PK" PRIMARY KEY ("SD_ID", "INTEGER_IDX");
+
+ALTER TABLE "APP"."PARTITION_KEYS" ADD CONSTRAINT "PARTITION_KEY_PK" PRIMARY KEY ("TBL_ID", "PKEY_NAME");
+
+ALTER TABLE "APP"."TBLS" ADD CONSTRAINT "TBLS_PK" PRIMARY KEY ("TBL_ID");
+
+ALTER TABLE "APP"."SERDE_PARAMS" ADD CONSTRAINT "SERDE_PARAMS_PK" PRIMARY KEY ("SERDE_ID", "PARAM_KEY");
+
+ALTER TABLE "APP"."PARTITION_PARAMS" ADD CONSTRAINT "PARTITION_PARAMS_PK" PRIMARY KEY ("PART_ID", "PARAM_KEY");
+
+ALTER TABLE "APP"."TYPES" ADD CONSTRAINT "TYPES_PK" PRIMARY KEY ("TYPES_ID");
+
+ALTER TABLE "APP"."SDS" ADD CONSTRAINT "SDS_PK" PRIMARY KEY ("SD_ID");
+
+ALTER TABLE "APP"."DBS" ADD CONSTRAINT "DBS_PK" PRIMARY KEY ("DB_ID");
+
+ALTER TABLE "APP"."TYPE_FIELDS" ADD CONSTRAINT "TYPE_FIELDS_PK" PRIMARY KEY ("TYPE_NAME", "FIELD_NAME");
+
+ALTER TABLE "APP"."TABLE_PARAMS" ADD CONSTRAINT "TABLE_PARAMS_PK" PRIMARY KEY ("TBL_ID", "PARAM_KEY");
+
+ALTER TABLE "APP"."SD_PARAMS" ADD CONSTRAINT "SD_PARAMS_PK" PRIMARY KEY ("SD_ID", "PARAM_KEY");
+
+-- foreign
+ALTER TABLE "APP"."COLUMNS" ADD CONSTRAINT "COLUMNS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."PARTITION_KEY_VALS" ADD CONSTRAINT "PARTITION_KEY_VALS_FK1" FOREIGN KEY ("PART_ID") REFERENCES "APP"."PARTITIONS" ("PART_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."BUCKETING_COLS" ADD CONSTRAINT "BUCKETING_COLS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."PARTITIONS" ADD CONSTRAINT "PARTITIONS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."PARTITIONS" ADD CONSTRAINT "PARTITIONS_FK2" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."SORT_COLS" ADD CONSTRAINT "SORT_COLS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."PARTITION_KEYS" ADD CONSTRAINT "PARTITION_KEYS_FK1" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."TBLS" ADD CONSTRAINT "TBLS_FK2" FOREIGN KEY ("DB_ID") REFERENCES "APP"."DBS" ("DB_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."TBLS" ADD CONSTRAINT "TBLS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."SERDE_PARAMS" ADD CONSTRAINT "SERDE_PARAMS_FK1" FOREIGN KEY ("SERDE_ID") REFERENCES "APP"."SERDES" ("SERDE_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."PARTITION_PARAMS" ADD CONSTRAINT "PARTITION_PARAMS_FK1" FOREIGN KEY ("PART_ID") REFERENCES "APP"."PARTITIONS" ("PART_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."SDS" ADD CONSTRAINT "SDS_FK1" FOREIGN KEY ("SERDE_ID") REFERENCES "APP"."SERDES" ("SERDE_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."TYPE_FIELDS" ADD CONSTRAINT "TYPE_FIELDS_FK1" FOREIGN KEY ("TYPE_NAME") REFERENCES "APP"."TYPES" ("TYPES_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."TABLE_PARAMS" ADD CONSTRAINT "TABLE_PARAMS_FK1" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."SD_PARAMS" ADD CONSTRAINT "SD_PARAMS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+-- ----------------------------------------------
+-- DDL Statements for checks
+-- ----------------------------------------------
+
+ALTER TABLE "APP"."SDS" ADD CONSTRAINT "SQL110318025446770" CHECK (IS_COMPRESSED IN ('Y','N'));
Added: hive/trunk/metastore/scripts/upgrade/derby/hive-schema-0.7.0.derby.sql
URL: http://svn.apache.org/viewvc/hive/trunk/metastore/scripts/upgrade/derby/hive-schema-0.7.0.derby.sql?rev=1083139&view=auto
==============================================================================
--- hive/trunk/metastore/scripts/upgrade/derby/hive-schema-0.7.0.derby.sql (added)
+++ hive/trunk/metastore/scripts/upgrade/derby/hive-schema-0.7.0.derby.sql Sat Mar 19 04:01:52 2011
@@ -0,0 +1,221 @@
+-- Timestamp: 2011-03-18 02:55:07.049
+-- Source database is: /Users/carl/metastore/derby/mdb-0.7.0-rc0
+-- Connection URL is: jdbc:derby:/Users/carl/metastore/derby/mdb-0.7.0-rc0
+-- appendLogs: false
+
+-- ----------------------------------------------
+-- DDL Statements for functions
+-- ----------------------------------------------
+
+CREATE FUNCTION "APP"."NUCLEUS_ASCII" (C CHAR(1)) RETURNS INTEGER LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA CALLED ON NULL INPUT EXTERNAL NAME 'org.datanucleus.store.rdbms.adapter.DerbySQLFunction.ascii' ;
+
+CREATE FUNCTION "APP"."NUCLEUS_MATCHES" (TEXT VARCHAR(8000),PATTERN VARCHAR(8000)) RETURNS INTEGER LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA CALLED ON NULL INPUT EXTERNAL NAME 'org.datanucleus.store.rdbms.adapter.DerbySQLFunction.matches' ;
+
+-- ----------------------------------------------
+-- DDL Statements for tables
+-- ----------------------------------------------
+
+CREATE TABLE "APP"."SDS" ("SD_ID" BIGINT NOT NULL, "INPUT_FORMAT" VARCHAR(4000), "IS_COMPRESSED" CHAR(1) NOT NULL, "LOCATION" VARCHAR(4000), "NUM_BUCKETS" INTEGER NOT NULL, "OUTPUT_FORMAT" VARCHAR(4000), "SERDE_ID" BIGINT);
+
+CREATE TABLE "APP"."GLOBAL_PRIVS" ("USER_GRANT_ID" BIGINT NOT NULL, "CREATE_TIME" INTEGER NOT NULL, "GRANT_OPTION" SMALLINT NOT NULL, "GRANTOR" VARCHAR(128), "GRANTOR_TYPE" VARCHAR(128), "PRINCIPAL_NAME" VARCHAR(128), "PRINCIPAL_TYPE" VARCHAR(128), "USER_PRIV" VARCHAR(128));
+
+CREATE TABLE "APP"."INDEX_PARAMS" ("INDEX_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(4000));
+
+CREATE TABLE "APP"."TYPES" ("TYPES_ID" BIGINT NOT NULL, "TYPE_NAME" VARCHAR(128), "TYPE1" VARCHAR(767), "TYPE2" VARCHAR(767));
+
+CREATE TABLE "APP"."PART_COL_PRIVS" ("PART_COLUMN_GRANT_ID" BIGINT NOT NULL, "COLUMN_NAME" VARCHAR(128), "CREATE_TIME" INTEGER NOT NULL, "GRANT_OPTION" SMALLINT NOT NULL, "GRANTOR" VARCHAR(128), "GRANTOR_TYPE" VARCHAR(128), "PART_ID" BIGINT, "PRINCIPAL_NAME" VARCHAR(128), "PRINCIPAL_TYPE" VARCHAR(128), "PART_COL_PRIV" VARCHAR(128));
+
+CREATE TABLE "APP"."COLUMNS" ("SD_ID" BIGINT NOT NULL, "COMMENT" VARCHAR(256), "COLUMN_NAME" VARCHAR(128) NOT NULL, "TYPE_NAME" VARCHAR(4000) NOT NULL, "INTEGER_IDX" INTEGER NOT NULL);
+
+CREATE TABLE "APP"."BUCKETING_COLS" ("SD_ID" BIGINT NOT NULL, "BUCKET_COL_NAME" VARCHAR(256), "INTEGER_IDX" INTEGER NOT NULL);
+
+CREATE TABLE "APP"."TABLE_PARAMS" ("TBL_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(4000));
+
+CREATE TABLE "APP"."DATABASE_PARAMS" ("DB_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(180) NOT NULL, "PARAM_VALUE" VARCHAR(4000));
+
+CREATE TABLE "APP"."TBL_PRIVS" ("TBL_GRANT_ID" BIGINT NOT NULL, "CREATE_TIME" INTEGER NOT NULL, "GRANT_OPTION" SMALLINT NOT NULL, "GRANTOR" VARCHAR(128), "GRANTOR_TYPE" VARCHAR(128), "PRINCIPAL_NAME" VARCHAR(128), "PRINCIPAL_TYPE" VARCHAR(128), "TBL_PRIV" VARCHAR(128), "TBL_ID" BIGINT);
+
+CREATE TABLE "APP"."IDXS" ("INDEX_ID" BIGINT NOT NULL, "CREATE_TIME" INTEGER NOT NULL, "DEFERRED_REBUILD" CHAR(1) NOT NULL, "INDEX_HANDLER_CLASS" VARCHAR(4000), "INDEX_NAME" VARCHAR(128), "INDEX_TBL_ID" BIGINT, "LAST_ACCESS_TIME" INTEGER NOT NULL, "ORIG_TBL_ID" BIGINT, "SD_ID" BIGINT);
+
+CREATE TABLE "APP"."DB_PRIVS" ("DB_GRANT_ID" BIGINT NOT NULL, "CREATE_TIME" INTEGER NOT NULL, "DB_ID" BIGINT, "GRANT_OPTION" SMALLINT NOT NULL, "GRANTOR" VARCHAR(128), "GRANTOR_TYPE" VARCHAR(128), "PRINCIPAL_NAME" VARCHAR(128), "PRINCIPAL_TYPE" VARCHAR(128), "DB_PRIV" VARCHAR(128));
+
+CREATE TABLE "APP"."ROLES" ("ROLE_ID" BIGINT NOT NULL, "CREATE_TIME" INTEGER NOT NULL, "OWNER_NAME" VARCHAR(128), "ROLE_NAME" VARCHAR(128));
+
+CREATE TABLE "APP"."SD_PARAMS" ("SD_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(4000));
+
+CREATE TABLE "APP"."SERDES" ("SERDE_ID" BIGINT NOT NULL, "NAME" VARCHAR(128), "SLIB" VARCHAR(4000));
+
+CREATE TABLE "APP"."PART_PRIVS" ("PART_GRANT_ID" BIGINT NOT NULL, "CREATE_TIME" INTEGER NOT NULL, "GRANT_OPTION" SMALLINT NOT NULL, "GRANTOR" VARCHAR(128), "GRANTOR_TYPE" VARCHAR(128), "PART_ID" BIGINT, "PRINCIPAL_NAME" VARCHAR(128), "PRINCIPAL_TYPE" VARCHAR(128), "PART_PRIV" VARCHAR(128));
+
+CREATE TABLE "APP"."PARTITION_KEYS" ("TBL_ID" BIGINT NOT NULL, "PKEY_COMMENT" VARCHAR(4000), "PKEY_NAME" VARCHAR(128) NOT NULL, "PKEY_TYPE" VARCHAR(767) NOT NULL, "INTEGER_IDX" INTEGER NOT NULL);
+
+CREATE TABLE "APP"."ROLE_MAP" ("ROLE_GRANT_ID" BIGINT NOT NULL, "ADD_TIME" INTEGER NOT NULL, "GRANT_OPTION" SMALLINT NOT NULL, "GRANTOR" VARCHAR(128), "GRANTOR_TYPE" VARCHAR(128), "PRINCIPAL_NAME" VARCHAR(128), "PRINCIPAL_TYPE" VARCHAR(128), "ROLE_ID" BIGINT);
+
+CREATE TABLE "APP"."TBLS" ("TBL_ID" BIGINT NOT NULL, "CREATE_TIME" INTEGER NOT NULL, "DB_ID" BIGINT, "LAST_ACCESS_TIME" INTEGER NOT NULL, "OWNER" VARCHAR(767), "RETENTION" INTEGER NOT NULL, "SD_ID" BIGINT, "TBL_NAME" VARCHAR(128), "TBL_TYPE" VARCHAR(128), "VIEW_EXPANDED_TEXT" LONG VARCHAR, "VIEW_ORIGINAL_TEXT" LONG VARCHAR);
+
+CREATE TABLE "APP"."TYPE_FIELDS" ("TYPE_NAME" BIGINT NOT NULL, "COMMENT" VARCHAR(256), "FIELD_NAME" VARCHAR(128) NOT NULL, "FIELD_TYPE" VARCHAR(767) NOT NULL, "INTEGER_IDX" INTEGER NOT NULL);
+
+CREATE TABLE "APP"."PARTITION_PARAMS" ("PART_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(4000));
+
+CREATE TABLE "APP"."PARTITION_KEY_VALS" ("PART_ID" BIGINT NOT NULL, "PART_KEY_VAL" VARCHAR(256), "INTEGER_IDX" INTEGER NOT NULL);
+
+CREATE TABLE "APP"."SORT_COLS" ("SD_ID" BIGINT NOT NULL, "COLUMN_NAME" VARCHAR(128), "ORDER" INTEGER NOT NULL, "INTEGER_IDX" INTEGER NOT NULL);
+
+CREATE TABLE "APP"."SERDE_PARAMS" ("SERDE_ID" BIGINT NOT NULL, "PARAM_KEY" VARCHAR(256) NOT NULL, "PARAM_VALUE" VARCHAR(4000));
+
+CREATE TABLE "APP"."DBS" ("DB_ID" BIGINT NOT NULL, "DESC" VARCHAR(4000), "DB_LOCATION_URI" VARCHAR(4000) NOT NULL, "NAME" VARCHAR(128));
+
+CREATE TABLE "APP"."PARTITIONS" ("PART_ID" BIGINT NOT NULL, "CREATE_TIME" INTEGER NOT NULL, "LAST_ACCESS_TIME" INTEGER NOT NULL, "PART_NAME" VARCHAR(767), "SD_ID" BIGINT, "TBL_ID" BIGINT);
+
+CREATE TABLE "APP"."TBL_COL_PRIVS" ("TBL_COLUMN_GRANT_ID" BIGINT NOT NULL, "COLUMN_NAME" VARCHAR(128), "CREATE_TIME" INTEGER NOT NULL, "GRANT_OPTION" SMALLINT NOT NULL, "GRANTOR" VARCHAR(128), "GRANTOR_TYPE" VARCHAR(128), "PRINCIPAL_NAME" VARCHAR(128), "PRINCIPAL_TYPE" VARCHAR(128), "TBL_COL_PRIV" VARCHAR(128), "TBL_ID" BIGINT);
+
+-- ----------------------------------------------
+-- DDL Statements for indexes
+-- ----------------------------------------------
+
+CREATE UNIQUE INDEX "APP"."UNIQUE_TYPE" ON "APP"."TYPES" ("TYPE_NAME");
+
+CREATE INDEX "APP"."PARTITIONCOLUMNPRIVILEGEINDEX" ON "APP"."PART_COL_PRIVS" ("PART_ID", "COLUMN_NAME", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", "PART_COL_PRIV", "GRANTOR", "GRANTOR_TYPE");
+
+CREATE UNIQUE INDEX "APP"."USERROLEMAPINDEX" ON "APP"."ROLE_MAP" ("PRINCIPAL_NAME", "ROLE_ID", "GRANTOR", "GRANTOR_TYPE");
+
+CREATE INDEX "APP"."TABLEPRIVILEGEINDEX" ON "APP"."TBL_PRIVS" ("TBL_ID", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", "TBL_PRIV", "GRANTOR", "GRANTOR_TYPE");
+
+CREATE UNIQUE INDEX "APP"."UNIQUEPARTITION" ON "APP"."PARTITIONS" ("PART_NAME", "TBL_ID");
+
+CREATE UNIQUE INDEX "APP"."UNIQUETABLE" ON "APP"."TBLS" ("TBL_NAME", "DB_ID");
+
+CREATE INDEX "APP"."PARTPRIVILEGEINDEX" ON "APP"."PART_PRIVS" ("PART_ID", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", "PART_PRIV", "GRANTOR", "GRANTOR_TYPE");
+
+CREATE UNIQUE INDEX "APP"."UNIQUE_DATABASE" ON "APP"."DBS" ("NAME");
+
+CREATE UNIQUE INDEX "APP"."ROLEENTITYINDEX" ON "APP"."ROLES" ("ROLE_NAME");
+
+CREATE UNIQUE INDEX "APP"."UNIQUEINDEX" ON "APP"."IDXS" ("INDEX_NAME", "ORIG_TBL_ID");
+
+CREATE UNIQUE INDEX "APP"."DBPRIVILEGEINDEX" ON "APP"."DB_PRIVS" ("DB_ID", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", "DB_PRIV", "GRANTOR", "GRANTOR_TYPE");
+
+CREATE INDEX "APP"."TABLECOLUMNPRIVILEGEINDEX" ON "APP"."TBL_COL_PRIVS" ("TBL_ID", "COLUMN_NAME", "PRINCIPAL_NAME", "PRINCIPAL_TYPE", "TBL_COL_PRIV", "GRANTOR", "GRANTOR_TYPE");
+
+CREATE UNIQUE INDEX "APP"."GLOBALPRIVILEGEINDEX" ON "APP"."GLOBAL_PRIVS" ("PRINCIPAL_NAME", "PRINCIPAL_TYPE", "USER_PRIV", "GRANTOR", "GRANTOR_TYPE");
+
+-- ----------------------------------------------
+-- DDL Statements for keys
+-- ----------------------------------------------
+
+-- primary/unique
+ALTER TABLE "APP"."PARTITION_PARAMS" ADD CONSTRAINT "PARTITION_PARAMS_PK" PRIMARY KEY ("PART_ID", "PARAM_KEY");
+
+ALTER TABLE "APP"."TYPES" ADD CONSTRAINT "TYPES_PK" PRIMARY KEY ("TYPES_ID");
+
+ALTER TABLE "APP"."SORT_COLS" ADD CONSTRAINT "SORT_COLS_PK" PRIMARY KEY ("SD_ID", "INTEGER_IDX");
+
+ALTER TABLE "APP"."PART_COL_PRIVS" ADD CONSTRAINT "PART_COL_PRIVS_PK" PRIMARY KEY ("PART_COLUMN_GRANT_ID");
+
+ALTER TABLE "APP"."PARTITION_KEYS" ADD CONSTRAINT "PARTITION_KEY_PK" PRIMARY KEY ("TBL_ID", "PKEY_NAME");
+
+ALTER TABLE "APP"."BUCKETING_COLS" ADD CONSTRAINT "BUCKETING_COLS_PK" PRIMARY KEY ("SD_ID", "INTEGER_IDX");
+
+ALTER TABLE "APP"."ROLE_MAP" ADD CONSTRAINT "ROLE_MAP_PK" PRIMARY KEY ("ROLE_GRANT_ID");
+
+ALTER TABLE "APP"."TBL_PRIVS" ADD CONSTRAINT "TBL_PRIVS_PK" PRIMARY KEY ("TBL_GRANT_ID");
+
+ALTER TABLE "APP"."SD_PARAMS" ADD CONSTRAINT "SD_PARAMS_PK" PRIMARY KEY ("SD_ID", "PARAM_KEY");
+
+ALTER TABLE "APP"."PARTITIONS" ADD CONSTRAINT "PARTITIONS_PK" PRIMARY KEY ("PART_ID");
+
+ALTER TABLE "APP"."SERDES" ADD CONSTRAINT "SERDES_PK" PRIMARY KEY ("SERDE_ID");
+
+ALTER TABLE "APP"."SERDE_PARAMS" ADD CONSTRAINT "SERDE_PARAMS_PK" PRIMARY KEY ("SERDE_ID", "PARAM_KEY");
+
+ALTER TABLE "APP"."TABLE_PARAMS" ADD CONSTRAINT "TABLE_PARAMS_PK" PRIMARY KEY ("TBL_ID", "PARAM_KEY");
+
+ALTER TABLE "APP"."TBLS" ADD CONSTRAINT "TBLS_PK" PRIMARY KEY ("TBL_ID");
+
+ALTER TABLE "APP"."COLUMNS" ADD CONSTRAINT "COLUMNS_PK" PRIMARY KEY ("SD_ID", "COLUMN_NAME");
+
+ALTER TABLE "APP"."PART_PRIVS" ADD CONSTRAINT "PART_PRIVS_PK" PRIMARY KEY ("PART_GRANT_ID");
+
+ALTER TABLE "APP"."DBS" ADD CONSTRAINT "DBS_PK" PRIMARY KEY ("DB_ID");
+
+ALTER TABLE "APP"."INDEX_PARAMS" ADD CONSTRAINT "INDEX_PARAMS_PK" PRIMARY KEY ("INDEX_ID", "PARAM_KEY");
+
+ALTER TABLE "APP"."ROLES" ADD CONSTRAINT "ROLES_PK" PRIMARY KEY ("ROLE_ID");
+
+ALTER TABLE "APP"."IDXS" ADD CONSTRAINT "IDXS_PK" PRIMARY KEY ("INDEX_ID");
+
+ALTER TABLE "APP"."DATABASE_PARAMS" ADD CONSTRAINT "DATABASE_PARAMS_PK" PRIMARY KEY ("DB_ID", "PARAM_KEY");
+
+ALTER TABLE "APP"."DB_PRIVS" ADD CONSTRAINT "DB_PRIVS_PK" PRIMARY KEY ("DB_GRANT_ID");
+
+ALTER TABLE "APP"."PARTITION_KEY_VALS" ADD CONSTRAINT "PARTITION_KEY_VALS_PK" PRIMARY KEY ("PART_ID", "INTEGER_IDX");
+
+ALTER TABLE "APP"."SDS" ADD CONSTRAINT "SDS_PK" PRIMARY KEY ("SD_ID");
+
+ALTER TABLE "APP"."TBL_COL_PRIVS" ADD CONSTRAINT "TBL_COL_PRIVS_PK" PRIMARY KEY ("TBL_COLUMN_GRANT_ID");
+
+ALTER TABLE "APP"."GLOBAL_PRIVS" ADD CONSTRAINT "GLOBAL_PRIVS_PK" PRIMARY KEY ("USER_GRANT_ID");
+
+ALTER TABLE "APP"."TYPE_FIELDS" ADD CONSTRAINT "TYPE_FIELDS_PK" PRIMARY KEY ("TYPE_NAME", "FIELD_NAME");
+
+-- foreign
+ALTER TABLE "APP"."PARTITION_PARAMS" ADD CONSTRAINT "PARTITION_PARAMS_FK1" FOREIGN KEY ("PART_ID") REFERENCES "APP"."PARTITIONS" ("PART_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."SORT_COLS" ADD CONSTRAINT "SORT_COLS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."PART_COL_PRIVS" ADD CONSTRAINT "PART_COL_PRIVS_FK1" FOREIGN KEY ("PART_ID") REFERENCES "APP"."PARTITIONS" ("PART_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."PARTITION_KEYS" ADD CONSTRAINT "PARTITION_KEYS_FK1" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."BUCKETING_COLS" ADD CONSTRAINT "BUCKETING_COLS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."ROLE_MAP" ADD CONSTRAINT "ROLE_MAP_FK1" FOREIGN KEY ("ROLE_ID") REFERENCES "APP"."ROLES" ("ROLE_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."TBL_PRIVS" ADD CONSTRAINT "TBL_PRIVS_FK1" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."SD_PARAMS" ADD CONSTRAINT "SD_PARAMS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."PARTITIONS" ADD CONSTRAINT "PARTITIONS_FK1" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."PARTITIONS" ADD CONSTRAINT "PARTITIONS_FK2" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."SERDE_PARAMS" ADD CONSTRAINT "SERDE_PARAMS_FK1" FOREIGN KEY ("SERDE_ID") REFERENCES "APP"."SERDES" ("SERDE_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."TABLE_PARAMS" ADD CONSTRAINT "TABLE_PARAMS_FK1" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."TBLS" ADD CONSTRAINT "TBLS_FK2" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."TBLS" ADD CONSTRAINT "TBLS_FK1" FOREIGN KEY ("DB_ID") REFERENCES "APP"."DBS" ("DB_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."COLUMNS" ADD CONSTRAINT "COLUMNS_FK1" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."PART_PRIVS" ADD CONSTRAINT "PART_PRIVS_FK1" FOREIGN KEY ("PART_ID") REFERENCES "APP"."PARTITIONS" ("PART_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."INDEX_PARAMS" ADD CONSTRAINT "INDEX_PARAMS_FK1" FOREIGN KEY ("INDEX_ID") REFERENCES "APP"."IDXS" ("INDEX_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."IDXS" ADD CONSTRAINT "IDXS_FK1" FOREIGN KEY ("ORIG_TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."IDXS" ADD CONSTRAINT "IDXS_FK2" FOREIGN KEY ("SD_ID") REFERENCES "APP"."SDS" ("SD_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."IDXS" ADD CONSTRAINT "IDXS_FK3" FOREIGN KEY ("INDEX_TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."DATABASE_PARAMS" ADD CONSTRAINT "DATABASE_PARAMS_FK1" FOREIGN KEY ("DB_ID") REFERENCES "APP"."DBS" ("DB_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."DB_PRIVS" ADD CONSTRAINT "DB_PRIVS_FK1" FOREIGN KEY ("DB_ID") REFERENCES "APP"."DBS" ("DB_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."PARTITION_KEY_VALS" ADD CONSTRAINT "PARTITION_KEY_VALS_FK1" FOREIGN KEY ("PART_ID") REFERENCES "APP"."PARTITIONS" ("PART_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."SDS" ADD CONSTRAINT "SDS_FK1" FOREIGN KEY ("SERDE_ID") REFERENCES "APP"."SERDES" ("SERDE_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."TBL_COL_PRIVS" ADD CONSTRAINT "TBL_COL_PRIVS_FK1" FOREIGN KEY ("TBL_ID") REFERENCES "APP"."TBLS" ("TBL_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+ALTER TABLE "APP"."TYPE_FIELDS" ADD CONSTRAINT "TYPE_FIELDS_FK1" FOREIGN KEY ("TYPE_NAME") REFERENCES "APP"."TYPES" ("TYPES_ID") ON DELETE NO ACTION ON UPDATE NO ACTION;
+
+-- ----------------------------------------------
+-- DDL Statements for checks
+-- ----------------------------------------------
+
+ALTER TABLE "APP"."IDXS" ADD CONSTRAINT "SQL110318025504980" CHECK (DEFERRED_REBUILD IN ('Y','N'));
+
+ALTER TABLE "APP"."SDS" ADD CONSTRAINT "SQL110318025505550" CHECK (IS_COMPRESSED IN ('Y','N'));
+
Added: hive/trunk/metastore/scripts/upgrade/derby/upgrade-0.5.0-to-0.6.0.derby.sql
URL: http://svn.apache.org/viewvc/hive/trunk/metastore/scripts/upgrade/derby/upgrade-0.5.0-to-0.6.0.derby.sql?rev=1083139&view=auto
==============================================================================
--- hive/trunk/metastore/scripts/upgrade/derby/upgrade-0.5.0-to-0.6.0.derby.sql (added)
+++ hive/trunk/metastore/scripts/upgrade/derby/upgrade-0.5.0-to-0.6.0.derby.sql Sat Mar 19 04:01:52 2011
@@ -0,0 +1,5 @@
+-- Upgrade MetaStore schema from 0.5.0 to 0.6.0
+RUN '001-HIVE-972.derby.sql';
+RUN '002-HIVE-1068.derby.sql';
+RUN '003-HIVE-675.derby.sql';
+RUN '004-HIVE-1364.derby.sql';
Added: hive/trunk/metastore/scripts/upgrade/derby/upgrade-0.6.0-to-0.7.0.derby.sql
URL: http://svn.apache.org/viewvc/hive/trunk/metastore/scripts/upgrade/derby/upgrade-0.6.0-to-0.7.0.derby.sql?rev=1083139&view=auto
==============================================================================
--- hive/trunk/metastore/scripts/upgrade/derby/upgrade-0.6.0-to-0.7.0.derby.sql (added)
+++ hive/trunk/metastore/scripts/upgrade/derby/upgrade-0.6.0-to-0.7.0.derby.sql Sat Mar 19 04:01:52 2011
@@ -0,0 +1,4 @@
+-- Upgrade MetaStore schema from 0.6.0 to 0.7.0
+RUN '005-HIVE-417.derby.sql';
+RUN '006-HIVE-1823.derby.sql';
+RUN '007-HIVE-78.derby.sql';
Added: hive/trunk/metastore/scripts/upgrade/mysql/001-HIVE-972.mysql.sql
URL: http://svn.apache.org/viewvc/hive/trunk/metastore/scripts/upgrade/mysql/001-HIVE-972.mysql.sql?rev=1083139&view=auto
==============================================================================
--- hive/trunk/metastore/scripts/upgrade/mysql/001-HIVE-972.mysql.sql (added)
+++ hive/trunk/metastore/scripts/upgrade/mysql/001-HIVE-972.mysql.sql Sat Mar 19 04:01:52 2011
@@ -0,0 +1,3 @@
+SELECT '< HIVE-972: Support views >' AS ' ';
+ALTER TABLE `TBLS` ADD `VIEW_EXPANDED_TEXT` mediumtext;
+ALTER TABLE `TBLS` ADD `VIEW_ORIGINAL_TEXT` mediumtext;
Added: hive/trunk/metastore/scripts/upgrade/mysql/002-HIVE-1068.mysql.sql
URL: http://svn.apache.org/viewvc/hive/trunk/metastore/scripts/upgrade/mysql/002-HIVE-1068.mysql.sql?rev=1083139&view=auto
==============================================================================
--- hive/trunk/metastore/scripts/upgrade/mysql/002-HIVE-1068.mysql.sql (added)
+++ hive/trunk/metastore/scripts/upgrade/mysql/002-HIVE-1068.mysql.sql Sat Mar 19 04:01:52 2011
@@ -0,0 +1,2 @@
+SELECT '< HIVE-1068: CREATE VIEW followup: add a "table type" enum attribute in metastore >' AS ' ';
+ALTER TABLE `TBLS` ADD `TBL_TYPE` VARCHAR(128);
Added: hive/trunk/metastore/scripts/upgrade/mysql/003-HIVE-675.mysql.sql
URL: http://svn.apache.org/viewvc/hive/trunk/metastore/scripts/upgrade/mysql/003-HIVE-675.mysql.sql?rev=1083139&view=auto
==============================================================================
--- hive/trunk/metastore/scripts/upgrade/mysql/003-HIVE-675.mysql.sql (added)
+++ hive/trunk/metastore/scripts/upgrade/mysql/003-HIVE-675.mysql.sql Sat Mar 19 04:01:52 2011
@@ -0,0 +1,3 @@
+SELECT '< HIVE-675: Add database/schema support for Hive QL >' AS ' ';
+ALTER TABLE `DBS` MODIFY `DESC` VARCHAR(4000);
+ALTER TABLE `DBS` ADD `DB_LOCATION_URI` VARCHAR(4000) NOT NULL DEFAULT '';
Added: hive/trunk/metastore/scripts/upgrade/mysql/004-HIVE-1364.mysql.sql
URL: http://svn.apache.org/viewvc/hive/trunk/metastore/scripts/upgrade/mysql/004-HIVE-1364.mysql.sql?rev=1083139&view=auto
==============================================================================
--- hive/trunk/metastore/scripts/upgrade/mysql/004-HIVE-1364.mysql.sql (added)
+++ hive/trunk/metastore/scripts/upgrade/mysql/004-HIVE-1364.mysql.sql Sat Mar 19 04:01:52 2011
@@ -0,0 +1,14 @@
+SELECT '< HIVE-1364: Increase the maximum length of various metastore fields >' AS ' ';
+ALTER TABLE `TBLS` MODIFY `OWNER` VARCHAR(767);
+ALTER TABLE `COLUMNS` DROP PRIMARY KEY;
+ALTER TABLE `COLUMNS` ADD PRIMARY KEY (`SD_ID`, `COLUMN_NAME`);
+ALTER TABLE `COLUMNS` MODIFY `TYPE_NAME` VARCHAR(4000);
+ALTER TABLE `PARTITION_KEYS` MODIFY `PKEY_COMMENT` VARCHAR(4000);
+ALTER TABLE `SD_PARAMS` MODIFY `PARAM_VALUE` VARCHAR(4000);
+ALTER TABLE `SDS` MODIFY `INPUT_FORMAT` VARCHAR(4000);
+ALTER TABLE `SDS` MODIFY `LOCATION` VARCHAR(4000);
+ALTER TABLE `SDS` MODIFY `OUTPUT_FORMAT` VARCHAR(4000);
+ALTER TABLE `SERDE_PARAMS` MODIFY `PARAM_VALUE` VARCHAR(4000);
+ALTER TABLE `SERDES` MODIFY `SLIB` VARCHAR(4000);
+ALTER TABLE `TABLE_PARAMS` MODIFY `PARAM_VALUE` VARCHAR(4000);
+ALTER TABLE `PARTITION_PARAMS` MODIFY `PARAM_VALUE` VARCHAR(4000);
Added: hive/trunk/metastore/scripts/upgrade/mysql/005-HIVE-417.mysql.sql
URL: http://svn.apache.org/viewvc/hive/trunk/metastore/scripts/upgrade/mysql/005-HIVE-417.mysql.sql?rev=1083139&view=auto
==============================================================================
--- hive/trunk/metastore/scripts/upgrade/mysql/005-HIVE-417.mysql.sql (added)
+++ hive/trunk/metastore/scripts/upgrade/mysql/005-HIVE-417.mysql.sql Sat Mar 19 04:01:52 2011
@@ -0,0 +1,28 @@
+SELECT '< HIVE-417 Implement Indexing in Hive >' AS ' ';
+CREATE TABLE IF NOT EXISTS `IDXS` (
+ `INDEX_ID` bigint(20) NOT NULL,
+ `CREATE_TIME` int(11) NOT NULL,
+ `DEFERRED_REBUILD` bit(1) NOT NULL,
+ `INDEX_HANDLER_CLASS` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
+ `INDEX_NAME` varchar(128) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
+ `INDEX_TBL_ID` bigint(20) DEFAULT NULL,
+ `LAST_ACCESS_TIME` int(11) NOT NULL,
+ `ORIG_TBL_ID` bigint(20) DEFAULT NULL,
+ `SD_ID` bigint(20) DEFAULT NULL,
+ PRIMARY KEY (`INDEX_ID`),
+ UNIQUE KEY `UNIQUEINDEX` (`INDEX_NAME`,`ORIG_TBL_ID`),
+ KEY `IDXS_FK1` (`SD_ID`),
+ KEY `IDXS_FK2` (`INDEX_TBL_ID`),
+ KEY `IDXS_FK3` (`ORIG_TBL_ID`),
+ CONSTRAINT `IDXS_FK1` FOREIGN KEY (`SD_ID`) REFERENCES `SDS` (`SD_ID`),
+ CONSTRAINT `IDXS_FK2` FOREIGN KEY (`INDEX_TBL_ID`) REFERENCES `TBLS` (`TBL_ID`),
+ CONSTRAINT `IDXS_FK3` FOREIGN KEY (`ORIG_TBL_ID`) REFERENCES `TBLS` (`TBL_ID`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
+
+CREATE TABLE IF NOT EXISTS `INDEX_PARAMS` (
+ `INDEX_ID` bigint(20) NOT NULL,
+ `PARAM_KEY` varchar(256) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
+ `PARAM_VALUE` varchar(767) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
+ PRIMARY KEY (`INDEX_ID`,`PARAM_KEY`),
+ CONSTRAINT `INDEX_PARAMS_FK1` FOREIGN KEY (`INDEX_ID`) REFERENCES `IDXS` (`INDEX_ID`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Added: hive/trunk/metastore/scripts/upgrade/mysql/006-HIVE-1823.mysql.sql
URL: http://svn.apache.org/viewvc/hive/trunk/metastore/scripts/upgrade/mysql/006-HIVE-1823.mysql.sql?rev=1083139&view=auto
==============================================================================
--- hive/trunk/metastore/scripts/upgrade/mysql/006-HIVE-1823.mysql.sql (added)
+++ hive/trunk/metastore/scripts/upgrade/mysql/006-HIVE-1823.mysql.sql Sat Mar 19 04:01:52 2011
@@ -0,0 +1,8 @@
+SELECT '< HIVE-1823 Upgrade the database thrift interface to allow parameters key-value pairs >' AS ' ';
+CREATE TABLE IF NOT EXISTS `DATABASE_PARAMS` (
+ `DB_ID` bigint(20) NOT NULL,
+ `PARAM_KEY` varchar(180) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
+ `PARAM_VALUE` varchar(4000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
+ PRIMARY KEY (`DB_ID`,`PARAM_KEY`),
+ CONSTRAINT `DATABASE_PARAMS_FK1` FOREIGN KEY (`DB_ID`) REFERENCES `DBS` (`DB_ID`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1;