/* This file was generated by ODB, object-relational mapping (ORM)
 * compiler for C++.
 */

DROP TABLE IF EXISTS "package_other_repositories" CASCADE;

DROP TABLE IF EXISTS "package_build_config_bot_keys" CASCADE;

DROP TABLE IF EXISTS "package_build_config_auxiliaries" CASCADE;

DROP TABLE IF EXISTS "package_build_config_constraints" CASCADE;

DROP TABLE IF EXISTS "package_build_config_builds" CASCADE;

DROP TABLE IF EXISTS "package_build_configs" CASCADE;

DROP TABLE IF EXISTS "package_build_bot_keys" CASCADE;

DROP TABLE IF EXISTS "package_build_auxiliaries" CASCADE;

DROP TABLE IF EXISTS "package_build_constraints" CASCADE;

DROP TABLE IF EXISTS "package_builds" CASCADE;

DROP TABLE IF EXISTS "package_tests" CASCADE;

DROP TABLE IF EXISTS "package_requirement_alternative_requirements" CASCADE;

DROP TABLE IF EXISTS "package_requirement_alternatives" CASCADE;

DROP TABLE IF EXISTS "package_requirements" CASCADE;

DROP TABLE IF EXISTS "package_dependency_alternative_dependencies" CASCADE;

DROP TABLE IF EXISTS "package_dependency_alternatives" CASCADE;

DROP TABLE IF EXISTS "package_dependencies" CASCADE;

DROP TABLE IF EXISTS "package_keywords" CASCADE;

DROP TABLE IF EXISTS "package_topics" CASCADE;

DROP TABLE IF EXISTS "package_licenses" CASCADE;

DROP TABLE IF EXISTS "package_license_alternatives" CASCADE;

DROP TABLE IF EXISTS "package" CASCADE;

DROP TABLE IF EXISTS "public_key" CASCADE;

DROP TABLE IF EXISTS "repository_prerequisites" CASCADE;

DROP TABLE IF EXISTS "repository_complements" CASCADE;

DROP TABLE IF EXISTS "repository" CASCADE;

DROP TABLE IF EXISTS "tenant" CASCADE;

DROP TABLE IF EXISTS "schema_version";

CREATE TABLE "tenant" (
  "id" TEXT NOT NULL PRIMARY KEY,
  "private" BOOLEAN NOT NULL,
  "interactive" TEXT NULL,
  "creation_timestamp" BIGINT NOT NULL,
  "archived" BOOLEAN NOT NULL,
  "service_id" TEXT NULL,
  "service_type" TEXT NULL,
  "service_data" TEXT NULL,
  "loaded_timestamp" BIGINT NULL,
  "queued_timestamp" BIGINT NULL,
  "build_toolchain_name" TEXT NULL,
  "build_toolchain_version_epoch" INTEGER NULL,
  "build_toolchain_version_canonical_upstream" TEXT NULL,
  "build_toolchain_version_canonical_release" TEXT NULL,
  "build_toolchain_version_revision" INTEGER NULL,
  "build_toolchain_version_upstream" TEXT NULL,
  "build_toolchain_version_release" TEXT NULL);

CREATE UNIQUE INDEX "tenant_service_i"
  ON "tenant" (
    "service_id",
    "service_type");

CREATE INDEX "tenant_service_id_i"
  ON "tenant" ("service_id");

CREATE INDEX "tenant_loaded_timestamp_i"
  ON "tenant" ("loaded_timestamp");

CREATE TABLE "repository" (
  "tenant" TEXT NOT NULL,
  "canonical_name" TEXT NOT NULL,
  "location_url" TEXT NOT NULL,
  "location_type" TEXT NOT NULL,
  "display_name" TEXT NOT NULL,
  "priority" INTEGER NOT NULL,
  "interface_url" TEXT NULL,
  "email" TEXT NULL,
  "email_comment" TEXT NULL,
  "summary" TEXT NULL,
  "description" TEXT NULL,
  "cache_location_url" TEXT NOT NULL,
  "cache_location_type" TEXT NOT NULL,
  "certificate_fingerprint" TEXT NULL,
  "certificate_name" TEXT NULL,
  "certificate_organization" TEXT NULL,
  "certificate_email" TEXT NULL,
  "certificate_pem" TEXT NULL,
  "packages_timestamp" BIGINT NOT NULL,
  "repositories_timestamp" BIGINT NOT NULL,
  "internal" BOOLEAN NOT NULL,
  "buildable" BOOLEAN NOT NULL,
  PRIMARY KEY ("tenant",
               "canonical_name"),
  CONSTRAINT "tenant_fk"
    FOREIGN KEY ("tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED);

CREATE TABLE "repository_complements" (
  "repository_tenant" TEXT NOT NULL,
  "repository_canonical_name" TEXT NOT NULL,
  "index" BIGINT NOT NULL,
  "complement_tenant" TEXT NOT NULL,
  "complement_canonical_name" TEXT NOT NULL,
  CONSTRAINT "repository_tenant_fk"
    FOREIGN KEY ("repository_tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED,
  CONSTRAINT "repository_fk"
    FOREIGN KEY ("repository_tenant",
                 "repository_canonical_name")
    REFERENCES "repository" ("tenant",
                             "canonical_name")
    ON DELETE CASCADE,
  CONSTRAINT "complement_tenant_fk"
    FOREIGN KEY ("complement_tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED,
  CONSTRAINT "complement_fk"
    FOREIGN KEY ("complement_tenant",
                 "complement_canonical_name")
    REFERENCES "repository" ("tenant",
                             "canonical_name")
    INITIALLY DEFERRED);

CREATE INDEX "repository_complements_repository_i"
  ON "repository_complements" (
    "repository_tenant",
    "repository_canonical_name");

CREATE INDEX "repository_complements_index_i"
  ON "repository_complements" ("index");

CREATE TABLE "repository_prerequisites" (
  "repository_tenant" TEXT NOT NULL,
  "repository_canonical_name" TEXT NOT NULL,
  "index" BIGINT NOT NULL,
  "prerequisite_tenant" TEXT NOT NULL,
  "prerequisite_canonical_name" TEXT NOT NULL,
  CONSTRAINT "repository_tenant_fk"
    FOREIGN KEY ("repository_tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED,
  CONSTRAINT "repository_fk"
    FOREIGN KEY ("repository_tenant",
                 "repository_canonical_name")
    REFERENCES "repository" ("tenant",
                             "canonical_name")
    ON DELETE CASCADE,
  CONSTRAINT "prerequisite_tenant_fk"
    FOREIGN KEY ("prerequisite_tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED,
  CONSTRAINT "prerequisite_fk"
    FOREIGN KEY ("prerequisite_tenant",
                 "prerequisite_canonical_name")
    REFERENCES "repository" ("tenant",
                             "canonical_name")
    INITIALLY DEFERRED);

CREATE INDEX "repository_prerequisites_repository_i"
  ON "repository_prerequisites" (
    "repository_tenant",
    "repository_canonical_name");

CREATE INDEX "repository_prerequisites_index_i"
  ON "repository_prerequisites" ("index");

CREATE TABLE "public_key" (
  "tenant" TEXT NOT NULL,
  "fingerprint" TEXT NOT NULL,
  "data" TEXT NOT NULL,
  PRIMARY KEY ("tenant",
               "fingerprint"),
  CONSTRAINT "tenant_fk"
    FOREIGN KEY ("tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED);

CREATE TABLE "package" (
  "tenant" TEXT NOT NULL,
  "name" CITEXT NOT NULL,
  "version_epoch" INTEGER NOT NULL,
  "version_canonical_upstream" TEXT NOT NULL,
  "version_canonical_release" TEXT NOT NULL COLLATE "C",
  "version_revision" INTEGER NOT NULL,
  "version_upstream" TEXT NOT NULL,
  "version_release" TEXT NULL,
  "upstream_version" TEXT NULL,
  "project" CITEXT NOT NULL,
  "priority" INTEGER NOT NULL,
  "priority_comment" TEXT NOT NULL,
  "summary" TEXT NOT NULL,
  "description" TEXT NULL,
  "description_type" TEXT NULL,
  "package_description" TEXT NULL,
  "package_description_type" TEXT NULL,
  "changes" TEXT NULL,
  "changes_type" TEXT NULL,
  "url" TEXT NULL,
  "url_comment" TEXT NULL,
  "doc_url" TEXT NULL,
  "doc_url_comment" TEXT NULL,
  "src_url" TEXT NULL,
  "src_url_comment" TEXT NULL,
  "package_url" TEXT NULL,
  "package_url_comment" TEXT NULL,
  "email" TEXT NULL,
  "email_comment" TEXT NULL,
  "package_email" TEXT NULL,
  "package_email_comment" TEXT NULL,
  "build_email" TEXT NULL,
  "build_email_comment" TEXT NULL,
  "build_warning_email" TEXT NULL,
  "build_warning_email_comment" TEXT NULL,
  "build_error_email" TEXT NULL,
  "build_error_email_comment" TEXT NULL,
  "internal_repository_tenant" TEXT NULL,
  "internal_repository_canonical_name" TEXT NULL,
  "location" TEXT NULL,
  "fragment" TEXT NULL,
  "sha256sum" TEXT NULL,
  "buildable" BOOLEAN NOT NULL,
  "unbuildable_reason" TEXT NULL,
  "custom_bot" BOOLEAN NULL,
  "search_index" tsvector NULL,
  PRIMARY KEY ("tenant",
               "name",
               "version_epoch",
               "version_canonical_upstream",
               "version_canonical_release",
               "version_revision"),
  CONSTRAINT "tenant_fk"
    FOREIGN KEY ("tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED,
  CONSTRAINT "internal_repository_tenant_fk"
    FOREIGN KEY ("internal_repository_tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED,
  CONSTRAINT "internal_repository_fk"
    FOREIGN KEY ("internal_repository_tenant",
                 "internal_repository_canonical_name")
    REFERENCES "repository" ("tenant",
                             "canonical_name")
    INITIALLY DEFERRED);

CREATE INDEX "package_search_index_i"
  ON "package" USING GIN ("search_index");

CREATE TABLE "package_license_alternatives" (
  "tenant" TEXT NOT NULL,
  "name" CITEXT NOT NULL,
  "version_epoch" INTEGER NOT NULL,
  "version_canonical_upstream" TEXT NOT NULL,
  "version_canonical_release" TEXT NOT NULL COLLATE "C",
  "version_revision" INTEGER NOT NULL,
  "index" BIGINT NOT NULL,
  "comment" TEXT NOT NULL,
  CONSTRAINT "tenant_fk"
    FOREIGN KEY ("tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED,
  CONSTRAINT "object_id_fk"
    FOREIGN KEY ("tenant",
                 "name",
                 "version_epoch",
                 "version_canonical_upstream",
                 "version_canonical_release",
                 "version_revision")
    REFERENCES "package" ("tenant",
                          "name",
                          "version_epoch",
                          "version_canonical_upstream",
                          "version_canonical_release",
                          "version_revision")
    ON DELETE CASCADE);

CREATE INDEX "package_license_alternatives_object_id_i"
  ON "package_license_alternatives" (
    "tenant",
    "name",
    "version_epoch",
    "version_canonical_upstream",
    "version_canonical_release",
    "version_revision");

CREATE INDEX "package_license_alternatives_index_i"
  ON "package_license_alternatives" ("index");

CREATE TABLE "package_licenses" (
  "tenant" TEXT NOT NULL,
  "name" CITEXT NOT NULL,
  "version_epoch" INTEGER NOT NULL,
  "version_canonical_upstream" TEXT NOT NULL,
  "version_canonical_release" TEXT NOT NULL COLLATE "C",
  "version_revision" INTEGER NOT NULL,
  "alternative_index" BIGINT NOT NULL,
  "index" BIGINT NOT NULL,
  "license" TEXT NOT NULL,
  CONSTRAINT "tenant_fk"
    FOREIGN KEY ("tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED,
  CONSTRAINT "object_id_fk"
    FOREIGN KEY ("tenant",
                 "name",
                 "version_epoch",
                 "version_canonical_upstream",
                 "version_canonical_release",
                 "version_revision")
    REFERENCES "package" ("tenant",
                          "name",
                          "version_epoch",
                          "version_canonical_upstream",
                          "version_canonical_release",
                          "version_revision")
    ON DELETE CASCADE);

CREATE INDEX "package_licenses_object_id_i"
  ON "package_licenses" (
    "tenant",
    "name",
    "version_epoch",
    "version_canonical_upstream",
    "version_canonical_release",
    "version_revision");

CREATE TABLE "package_topics" (
  "tenant" TEXT NOT NULL,
  "name" CITEXT NOT NULL,
  "version_epoch" INTEGER NOT NULL,
  "version_canonical_upstream" TEXT NOT NULL,
  "version_canonical_release" TEXT NOT NULL COLLATE "C",
  "version_revision" INTEGER NOT NULL,
  "index" BIGINT NOT NULL,
  "topic" TEXT NOT NULL,
  CONSTRAINT "tenant_fk"
    FOREIGN KEY ("tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED,
  CONSTRAINT "object_id_fk"
    FOREIGN KEY ("tenant",
                 "name",
                 "version_epoch",
                 "version_canonical_upstream",
                 "version_canonical_release",
                 "version_revision")
    REFERENCES "package" ("tenant",
                          "name",
                          "version_epoch",
                          "version_canonical_upstream",
                          "version_canonical_release",
                          "version_revision")
    ON DELETE CASCADE);

CREATE INDEX "package_topics_object_id_i"
  ON "package_topics" (
    "tenant",
    "name",
    "version_epoch",
    "version_canonical_upstream",
    "version_canonical_release",
    "version_revision");

CREATE INDEX "package_topics_index_i"
  ON "package_topics" ("index");

CREATE TABLE "package_keywords" (
  "tenant" TEXT NOT NULL,
  "name" CITEXT NOT NULL,
  "version_epoch" INTEGER NOT NULL,
  "version_canonical_upstream" TEXT NOT NULL,
  "version_canonical_release" TEXT NOT NULL COLLATE "C",
  "version_revision" INTEGER NOT NULL,
  "index" BIGINT NOT NULL,
  "keyword" TEXT NOT NULL,
  CONSTRAINT "tenant_fk"
    FOREIGN KEY ("tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED,
  CONSTRAINT "object_id_fk"
    FOREIGN KEY ("tenant",
                 "name",
                 "version_epoch",
                 "version_canonical_upstream",
                 "version_canonical_release",
                 "version_revision")
    REFERENCES "package" ("tenant",
                          "name",
                          "version_epoch",
                          "version_canonical_upstream",
                          "version_canonical_release",
                          "version_revision")
    ON DELETE CASCADE);

CREATE INDEX "package_keywords_object_id_i"
  ON "package_keywords" (
    "tenant",
    "name",
    "version_epoch",
    "version_canonical_upstream",
    "version_canonical_release",
    "version_revision");

CREATE INDEX "package_keywords_index_i"
  ON "package_keywords" ("index");

CREATE TABLE "package_dependencies" (
  "tenant" TEXT NOT NULL,
  "name" CITEXT NOT NULL,
  "version_epoch" INTEGER NOT NULL,
  "version_canonical_upstream" TEXT NOT NULL,
  "version_canonical_release" TEXT NOT NULL COLLATE "C",
  "version_revision" INTEGER NOT NULL,
  "index" BIGINT NOT NULL,
  "buildtime" BOOLEAN NOT NULL,
  "comment" TEXT NOT NULL,
  CONSTRAINT "tenant_fk"
    FOREIGN KEY ("tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED,
  CONSTRAINT "object_id_fk"
    FOREIGN KEY ("tenant",
                 "name",
                 "version_epoch",
                 "version_canonical_upstream",
                 "version_canonical_release",
                 "version_revision")
    REFERENCES "package" ("tenant",
                          "name",
                          "version_epoch",
                          "version_canonical_upstream",
                          "version_canonical_release",
                          "version_revision")
    ON DELETE CASCADE);

CREATE INDEX "package_dependencies_object_id_i"
  ON "package_dependencies" (
    "tenant",
    "name",
    "version_epoch",
    "version_canonical_upstream",
    "version_canonical_release",
    "version_revision");

CREATE INDEX "package_dependencies_index_i"
  ON "package_dependencies" ("index");

CREATE TABLE "package_dependency_alternatives" (
  "tenant" TEXT NOT NULL,
  "name" CITEXT NOT NULL,
  "version_epoch" INTEGER NOT NULL,
  "version_canonical_upstream" TEXT NOT NULL,
  "version_canonical_release" TEXT NOT NULL COLLATE "C",
  "version_revision" INTEGER NOT NULL,
  "dependency_index" BIGINT NOT NULL,
  "index" BIGINT NOT NULL,
  "enable" TEXT NULL,
  "reflect" TEXT NULL,
  "prefer" TEXT NULL,
  "accept" TEXT NULL,
  "require" TEXT NULL,
  CONSTRAINT "tenant_fk"
    FOREIGN KEY ("tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED,
  CONSTRAINT "object_id_fk"
    FOREIGN KEY ("tenant",
                 "name",
                 "version_epoch",
                 "version_canonical_upstream",
                 "version_canonical_release",
                 "version_revision")
    REFERENCES "package" ("tenant",
                          "name",
                          "version_epoch",
                          "version_canonical_upstream",
                          "version_canonical_release",
                          "version_revision")
    ON DELETE CASCADE);

CREATE INDEX "package_dependency_alternatives_object_id_i"
  ON "package_dependency_alternatives" (
    "tenant",
    "name",
    "version_epoch",
    "version_canonical_upstream",
    "version_canonical_release",
    "version_revision");

CREATE TABLE "package_dependency_alternative_dependencies" (
  "tenant" TEXT NOT NULL,
  "name" CITEXT NOT NULL,
  "version_epoch" INTEGER NOT NULL,
  "version_canonical_upstream" TEXT NOT NULL,
  "version_canonical_release" TEXT NOT NULL COLLATE "C",
  "version_revision" INTEGER NOT NULL,
  "dependency_index" BIGINT NOT NULL,
  "alternative_index" BIGINT NOT NULL,
  "index" BIGINT NOT NULL,
  "dep_name" CITEXT NOT NULL,
  "dep_min_version_epoch" INTEGER NULL,
  "dep_min_version_canonical_upstream" TEXT NULL,
  "dep_min_version_canonical_release" TEXT NULL,
  "dep_min_version_revision" INTEGER NULL,
  "dep_min_version_upstream" TEXT NULL,
  "dep_min_version_release" TEXT NULL,
  "dep_max_version_epoch" INTEGER NULL,
  "dep_max_version_canonical_upstream" TEXT NULL,
  "dep_max_version_canonical_release" TEXT NULL,
  "dep_max_version_revision" INTEGER NULL,
  "dep_max_version_upstream" TEXT NULL,
  "dep_max_version_release" TEXT NULL,
  "dep_min_open" BOOLEAN NULL,
  "dep_max_open" BOOLEAN NULL,
  "dep_package_tenant" TEXT NULL,
  "dep_package_name" CITEXT NULL,
  "dep_package_version_epoch" INTEGER NULL,
  "dep_package_version_canonical_upstream" TEXT NULL,
  "dep_package_version_canonical_release" TEXT NULL COLLATE "C",
  "dep_package_version_revision" INTEGER NULL,
  CONSTRAINT "tenant_fk"
    FOREIGN KEY ("tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED,
  CONSTRAINT "object_id_fk"
    FOREIGN KEY ("tenant",
                 "name",
                 "version_epoch",
                 "version_canonical_upstream",
                 "version_canonical_release",
                 "version_revision")
    REFERENCES "package" ("tenant",
                          "name",
                          "version_epoch",
                          "version_canonical_upstream",
                          "version_canonical_release",
                          "version_revision")
    ON DELETE CASCADE,
  CONSTRAINT "dep_package_tenant_fk"
    FOREIGN KEY ("dep_package_tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED,
  CONSTRAINT "dep_package_fk"
    FOREIGN KEY ("dep_package_tenant",
                 "dep_package_name",
                 "dep_package_version_epoch",
                 "dep_package_version_canonical_upstream",
                 "dep_package_version_canonical_release",
                 "dep_package_version_revision")
    REFERENCES "package" ("tenant",
                          "name",
                          "version_epoch",
                          "version_canonical_upstream",
                          "version_canonical_release",
                          "version_revision")
    INITIALLY DEFERRED);

CREATE INDEX "package_dependency_alternative_dependencies_object_id_i"
  ON "package_dependency_alternative_dependencies" (
    "tenant",
    "name",
    "version_epoch",
    "version_canonical_upstream",
    "version_canonical_release",
    "version_revision");

CREATE TABLE "package_requirements" (
  "tenant" TEXT NOT NULL,
  "name" CITEXT NOT NULL,
  "version_epoch" INTEGER NOT NULL,
  "version_canonical_upstream" TEXT NOT NULL,
  "version_canonical_release" TEXT NOT NULL COLLATE "C",
  "version_revision" INTEGER NOT NULL,
  "index" BIGINT NOT NULL,
  "buildtime" BOOLEAN NOT NULL,
  "comment" TEXT NOT NULL,
  CONSTRAINT "tenant_fk"
    FOREIGN KEY ("tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED,
  CONSTRAINT "object_id_fk"
    FOREIGN KEY ("tenant",
                 "name",
                 "version_epoch",
                 "version_canonical_upstream",
                 "version_canonical_release",
                 "version_revision")
    REFERENCES "package" ("tenant",
                          "name",
                          "version_epoch",
                          "version_canonical_upstream",
                          "version_canonical_release",
                          "version_revision")
    ON DELETE CASCADE);

CREATE INDEX "package_requirements_object_id_i"
  ON "package_requirements" (
    "tenant",
    "name",
    "version_epoch",
    "version_canonical_upstream",
    "version_canonical_release",
    "version_revision");

CREATE INDEX "package_requirements_index_i"
  ON "package_requirements" ("index");

CREATE TABLE "package_requirement_alternatives" (
  "tenant" TEXT NOT NULL,
  "name" CITEXT NOT NULL,
  "version_epoch" INTEGER NOT NULL,
  "version_canonical_upstream" TEXT NOT NULL,
  "version_canonical_release" TEXT NOT NULL COLLATE "C",
  "version_revision" INTEGER NOT NULL,
  "requirement_index" BIGINT NOT NULL,
  "index" BIGINT NOT NULL,
  "enable" TEXT NULL,
  "reflect" TEXT NULL,
  CONSTRAINT "tenant_fk"
    FOREIGN KEY ("tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED,
  CONSTRAINT "object_id_fk"
    FOREIGN KEY ("tenant",
                 "name",
                 "version_epoch",
                 "version_canonical_upstream",
                 "version_canonical_release",
                 "version_revision")
    REFERENCES "package" ("tenant",
                          "name",
                          "version_epoch",
                          "version_canonical_upstream",
                          "version_canonical_release",
                          "version_revision")
    ON DELETE CASCADE);

CREATE INDEX "package_requirement_alternatives_object_id_i"
  ON "package_requirement_alternatives" (
    "tenant",
    "name",
    "version_epoch",
    "version_canonical_upstream",
    "version_canonical_release",
    "version_revision");

CREATE TABLE "package_requirement_alternative_requirements" (
  "tenant" TEXT NOT NULL,
  "name" CITEXT NOT NULL,
  "version_epoch" INTEGER NOT NULL,
  "version_canonical_upstream" TEXT NOT NULL,
  "version_canonical_release" TEXT NOT NULL COLLATE "C",
  "version_revision" INTEGER NOT NULL,
  "requirement_index" BIGINT NOT NULL,
  "alternative_index" BIGINT NOT NULL,
  "index" BIGINT NOT NULL,
  "id" TEXT NOT NULL,
  CONSTRAINT "tenant_fk"
    FOREIGN KEY ("tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED,
  CONSTRAINT "object_id_fk"
    FOREIGN KEY ("tenant",
                 "name",
                 "version_epoch",
                 "version_canonical_upstream",
                 "version_canonical_release",
                 "version_revision")
    REFERENCES "package" ("tenant",
                          "name",
                          "version_epoch",
                          "version_canonical_upstream",
                          "version_canonical_release",
                          "version_revision")
    ON DELETE CASCADE);

CREATE INDEX "package_requirement_alternative_requirements_object_id_i"
  ON "package_requirement_alternative_requirements" (
    "tenant",
    "name",
    "version_epoch",
    "version_canonical_upstream",
    "version_canonical_release",
    "version_revision");

CREATE TABLE "package_tests" (
  "tenant" TEXT NOT NULL,
  "name" CITEXT NOT NULL,
  "version_epoch" INTEGER NOT NULL,
  "version_canonical_upstream" TEXT NOT NULL,
  "version_canonical_release" TEXT NOT NULL COLLATE "C",
  "version_revision" INTEGER NOT NULL,
  "index" BIGINT NOT NULL,
  "test_name" CITEXT NOT NULL,
  "test_min_version_epoch" INTEGER NULL,
  "test_min_version_canonical_upstream" TEXT NULL,
  "test_min_version_canonical_release" TEXT NULL,
  "test_min_version_revision" INTEGER NULL,
  "test_min_version_upstream" TEXT NULL,
  "test_min_version_release" TEXT NULL,
  "test_max_version_epoch" INTEGER NULL,
  "test_max_version_canonical_upstream" TEXT NULL,
  "test_max_version_canonical_release" TEXT NULL,
  "test_max_version_revision" INTEGER NULL,
  "test_max_version_upstream" TEXT NULL,
  "test_max_version_release" TEXT NULL,
  "test_min_open" BOOLEAN NULL,
  "test_max_open" BOOLEAN NULL,
  "test_package_tenant" TEXT NULL,
  "test_package_name" CITEXT NULL,
  "test_package_version_epoch" INTEGER NULL,
  "test_package_version_canonical_upstream" TEXT NULL,
  "test_package_version_canonical_release" TEXT NULL COLLATE "C",
  "test_package_version_revision" INTEGER NULL,
  "test_type" TEXT NOT NULL,
  "test_buildtime" BOOLEAN NOT NULL,
  "test_enable" TEXT NULL,
  "test_reflect" TEXT NULL,
  CONSTRAINT "tenant_fk"
    FOREIGN KEY ("tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED,
  CONSTRAINT "object_id_fk"
    FOREIGN KEY ("tenant",
                 "name",
                 "version_epoch",
                 "version_canonical_upstream",
                 "version_canonical_release",
                 "version_revision")
    REFERENCES "package" ("tenant",
                          "name",
                          "version_epoch",
                          "version_canonical_upstream",
                          "version_canonical_release",
                          "version_revision")
    ON DELETE CASCADE,
  CONSTRAINT "test_package_tenant_fk"
    FOREIGN KEY ("test_package_tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED,
  CONSTRAINT "test_package_fk"
    FOREIGN KEY ("test_package_tenant",
                 "test_package_name",
                 "test_package_version_epoch",
                 "test_package_version_canonical_upstream",
                 "test_package_version_canonical_release",
                 "test_package_version_revision")
    REFERENCES "package" ("tenant",
                          "name",
                          "version_epoch",
                          "version_canonical_upstream",
                          "version_canonical_release",
                          "version_revision")
    INITIALLY DEFERRED);

CREATE INDEX "package_tests_object_id_i"
  ON "package_tests" (
    "tenant",
    "name",
    "version_epoch",
    "version_canonical_upstream",
    "version_canonical_release",
    "version_revision");

CREATE INDEX "package_tests_index_i"
  ON "package_tests" ("index");

CREATE TABLE "package_builds" (
  "tenant" TEXT NOT NULL,
  "name" CITEXT NOT NULL,
  "version_epoch" INTEGER NOT NULL,
  "version_canonical_upstream" TEXT NOT NULL,
  "version_canonical_release" TEXT NOT NULL COLLATE "C",
  "version_revision" INTEGER NOT NULL,
  "index" BIGINT NOT NULL,
  "expression" TEXT NOT NULL,
  "comment" TEXT NOT NULL,
  CONSTRAINT "tenant_fk"
    FOREIGN KEY ("tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED,
  CONSTRAINT "object_id_fk"
    FOREIGN KEY ("tenant",
                 "name",
                 "version_epoch",
                 "version_canonical_upstream",
                 "version_canonical_release",
                 "version_revision")
    REFERENCES "package" ("tenant",
                          "name",
                          "version_epoch",
                          "version_canonical_upstream",
                          "version_canonical_release",
                          "version_revision")
    ON DELETE CASCADE);

CREATE INDEX "package_builds_object_id_i"
  ON "package_builds" (
    "tenant",
    "name",
    "version_epoch",
    "version_canonical_upstream",
    "version_canonical_release",
    "version_revision");

CREATE INDEX "package_builds_index_i"
  ON "package_builds" ("index");

CREATE TABLE "package_build_constraints" (
  "tenant" TEXT NOT NULL,
  "name" CITEXT NOT NULL,
  "version_epoch" INTEGER NOT NULL,
  "version_canonical_upstream" TEXT NOT NULL,
  "version_canonical_release" TEXT NOT NULL COLLATE "C",
  "version_revision" INTEGER NOT NULL,
  "index" BIGINT NOT NULL,
  "exclusion" BOOLEAN NOT NULL,
  "config" TEXT NOT NULL,
  "target" TEXT NULL,
  "comment" TEXT NOT NULL,
  CONSTRAINT "tenant_fk"
    FOREIGN KEY ("tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED,
  CONSTRAINT "object_id_fk"
    FOREIGN KEY ("tenant",
                 "name",
                 "version_epoch",
                 "version_canonical_upstream",
                 "version_canonical_release",
                 "version_revision")
    REFERENCES "package" ("tenant",
                          "name",
                          "version_epoch",
                          "version_canonical_upstream",
                          "version_canonical_release",
                          "version_revision")
    ON DELETE CASCADE);

CREATE INDEX "package_build_constraints_object_id_i"
  ON "package_build_constraints" (
    "tenant",
    "name",
    "version_epoch",
    "version_canonical_upstream",
    "version_canonical_release",
    "version_revision");

CREATE INDEX "package_build_constraints_index_i"
  ON "package_build_constraints" ("index");

CREATE TABLE "package_build_auxiliaries" (
  "tenant" TEXT NOT NULL,
  "name" CITEXT NOT NULL,
  "version_epoch" INTEGER NOT NULL,
  "version_canonical_upstream" TEXT NOT NULL,
  "version_canonical_release" TEXT NOT NULL COLLATE "C",
  "version_revision" INTEGER NOT NULL,
  "index" BIGINT NOT NULL,
  "environment_name" TEXT NOT NULL,
  "config" TEXT NOT NULL,
  "comment" TEXT NOT NULL,
  CONSTRAINT "tenant_fk"
    FOREIGN KEY ("tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED,
  CONSTRAINT "object_id_fk"
    FOREIGN KEY ("tenant",
                 "name",
                 "version_epoch",
                 "version_canonical_upstream",
                 "version_canonical_release",
                 "version_revision")
    REFERENCES "package" ("tenant",
                          "name",
                          "version_epoch",
                          "version_canonical_upstream",
                          "version_canonical_release",
                          "version_revision")
    ON DELETE CASCADE);

CREATE INDEX "package_build_auxiliaries_object_id_i"
  ON "package_build_auxiliaries" (
    "tenant",
    "name",
    "version_epoch",
    "version_canonical_upstream",
    "version_canonical_release",
    "version_revision");

CREATE INDEX "package_build_auxiliaries_index_i"
  ON "package_build_auxiliaries" ("index");

CREATE TABLE "package_build_bot_keys" (
  "tenant" TEXT NOT NULL,
  "name" CITEXT NOT NULL,
  "version_epoch" INTEGER NOT NULL,
  "version_canonical_upstream" TEXT NOT NULL,
  "version_canonical_release" TEXT NOT NULL COLLATE "C",
  "version_revision" INTEGER NOT NULL,
  "index" BIGINT NOT NULL,
  "key_tenant" TEXT NOT NULL,
  "key_fingerprint" TEXT NOT NULL,
  CONSTRAINT "tenant_fk"
    FOREIGN KEY ("tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED,
  CONSTRAINT "object_id_fk"
    FOREIGN KEY ("tenant",
                 "name",
                 "version_epoch",
                 "version_canonical_upstream",
                 "version_canonical_release",
                 "version_revision")
    REFERENCES "package" ("tenant",
                          "name",
                          "version_epoch",
                          "version_canonical_upstream",
                          "version_canonical_release",
                          "version_revision")
    ON DELETE CASCADE,
  CONSTRAINT "key_tenant_fk"
    FOREIGN KEY ("key_tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED,
  CONSTRAINT "key_fk"
    FOREIGN KEY ("key_tenant",
                 "key_fingerprint")
    REFERENCES "public_key" ("tenant",
                             "fingerprint")
    INITIALLY DEFERRED);

CREATE INDEX "package_build_bot_keys_object_id_i"
  ON "package_build_bot_keys" (
    "tenant",
    "name",
    "version_epoch",
    "version_canonical_upstream",
    "version_canonical_release",
    "version_revision");

CREATE INDEX "package_build_bot_keys_index_i"
  ON "package_build_bot_keys" ("index");

CREATE TABLE "package_build_configs" (
  "tenant" TEXT NOT NULL,
  "name" CITEXT NOT NULL,
  "version_epoch" INTEGER NOT NULL,
  "version_canonical_upstream" TEXT NOT NULL,
  "version_canonical_release" TEXT NOT NULL COLLATE "C",
  "version_revision" INTEGER NOT NULL,
  "index" BIGINT NOT NULL,
  "config_name" TEXT NOT NULL,
  "config_arguments" TEXT NOT NULL,
  "config_comment" TEXT NOT NULL,
  "config_email" TEXT NULL,
  "config_email_comment" TEXT NULL,
  "config_warning_email" TEXT NULL,
  "config_warning_email_comment" TEXT NULL,
  "config_error_email" TEXT NULL,
  "config_error_email_comment" TEXT NULL,
  CONSTRAINT "tenant_fk"
    FOREIGN KEY ("tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED,
  CONSTRAINT "object_id_fk"
    FOREIGN KEY ("tenant",
                 "name",
                 "version_epoch",
                 "version_canonical_upstream",
                 "version_canonical_release",
                 "version_revision")
    REFERENCES "package" ("tenant",
                          "name",
                          "version_epoch",
                          "version_canonical_upstream",
                          "version_canonical_release",
                          "version_revision")
    ON DELETE CASCADE);

CREATE INDEX "package_build_configs_object_id_i"
  ON "package_build_configs" (
    "tenant",
    "name",
    "version_epoch",
    "version_canonical_upstream",
    "version_canonical_release",
    "version_revision");

CREATE INDEX "package_build_configs_index_i"
  ON "package_build_configs" ("index");

CREATE TABLE "package_build_config_builds" (
  "tenant" TEXT NOT NULL,
  "name" CITEXT NOT NULL,
  "version_epoch" INTEGER NOT NULL,
  "version_canonical_upstream" TEXT NOT NULL,
  "version_canonical_release" TEXT NOT NULL COLLATE "C",
  "version_revision" INTEGER NOT NULL,
  "config_index" BIGINT NOT NULL,
  "index" BIGINT NOT NULL,
  "expression" TEXT NOT NULL,
  "comment" TEXT NOT NULL,
  CONSTRAINT "tenant_fk"
    FOREIGN KEY ("tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED,
  CONSTRAINT "object_id_fk"
    FOREIGN KEY ("tenant",
                 "name",
                 "version_epoch",
                 "version_canonical_upstream",
                 "version_canonical_release",
                 "version_revision")
    REFERENCES "package" ("tenant",
                          "name",
                          "version_epoch",
                          "version_canonical_upstream",
                          "version_canonical_release",
                          "version_revision")
    ON DELETE CASCADE);

CREATE INDEX "package_build_config_builds_object_id_i"
  ON "package_build_config_builds" (
    "tenant",
    "name",
    "version_epoch",
    "version_canonical_upstream",
    "version_canonical_release",
    "version_revision");

CREATE TABLE "package_build_config_constraints" (
  "tenant" TEXT NOT NULL,
  "name" CITEXT NOT NULL,
  "version_epoch" INTEGER NOT NULL,
  "version_canonical_upstream" TEXT NOT NULL,
  "version_canonical_release" TEXT NOT NULL COLLATE "C",
  "version_revision" INTEGER NOT NULL,
  "config_index" BIGINT NOT NULL,
  "index" BIGINT NOT NULL,
  "exclusion" BOOLEAN NOT NULL,
  "config" TEXT NOT NULL,
  "target" TEXT NULL,
  "comment" TEXT NOT NULL,
  CONSTRAINT "tenant_fk"
    FOREIGN KEY ("tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED,
  CONSTRAINT "object_id_fk"
    FOREIGN KEY ("tenant",
                 "name",
                 "version_epoch",
                 "version_canonical_upstream",
                 "version_canonical_release",
                 "version_revision")
    REFERENCES "package" ("tenant",
                          "name",
                          "version_epoch",
                          "version_canonical_upstream",
                          "version_canonical_release",
                          "version_revision")
    ON DELETE CASCADE);

CREATE INDEX "package_build_config_constraints_object_id_i"
  ON "package_build_config_constraints" (
    "tenant",
    "name",
    "version_epoch",
    "version_canonical_upstream",
    "version_canonical_release",
    "version_revision");

CREATE TABLE "package_build_config_auxiliaries" (
  "tenant" TEXT NOT NULL,
  "name" CITEXT NOT NULL,
  "version_epoch" INTEGER NOT NULL,
  "version_canonical_upstream" TEXT NOT NULL,
  "version_canonical_release" TEXT NOT NULL COLLATE "C",
  "version_revision" INTEGER NOT NULL,
  "config_index" BIGINT NOT NULL,
  "index" BIGINT NOT NULL,
  "environment_name" TEXT NOT NULL,
  "config" TEXT NOT NULL,
  "comment" TEXT NOT NULL,
  CONSTRAINT "tenant_fk"
    FOREIGN KEY ("tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED,
  CONSTRAINT "object_id_fk"
    FOREIGN KEY ("tenant",
                 "name",
                 "version_epoch",
                 "version_canonical_upstream",
                 "version_canonical_release",
                 "version_revision")
    REFERENCES "package" ("tenant",
                          "name",
                          "version_epoch",
                          "version_canonical_upstream",
                          "version_canonical_release",
                          "version_revision")
    ON DELETE CASCADE);

CREATE INDEX "package_build_config_auxiliaries_object_id_i"
  ON "package_build_config_auxiliaries" (
    "tenant",
    "name",
    "version_epoch",
    "version_canonical_upstream",
    "version_canonical_release",
    "version_revision");

CREATE TABLE "package_build_config_bot_keys" (
  "tenant" TEXT NOT NULL,
  "name" CITEXT NOT NULL,
  "version_epoch" INTEGER NOT NULL,
  "version_canonical_upstream" TEXT NOT NULL,
  "version_canonical_release" TEXT NOT NULL COLLATE "C",
  "version_revision" INTEGER NOT NULL,
  "config_index" BIGINT NOT NULL,
  "index" BIGINT NOT NULL,
  "key_tenant" TEXT NOT NULL,
  "key_fingerprint" TEXT NOT NULL,
  CONSTRAINT "tenant_fk"
    FOREIGN KEY ("tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED,
  CONSTRAINT "object_id_fk"
    FOREIGN KEY ("tenant",
                 "name",
                 "version_epoch",
                 "version_canonical_upstream",
                 "version_canonical_release",
                 "version_revision")
    REFERENCES "package" ("tenant",
                          "name",
                          "version_epoch",
                          "version_canonical_upstream",
                          "version_canonical_release",
                          "version_revision")
    ON DELETE CASCADE,
  CONSTRAINT "key_tenant_fk"
    FOREIGN KEY ("key_tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED,
  CONSTRAINT "key_fk"
    FOREIGN KEY ("key_tenant",
                 "key_fingerprint")
    REFERENCES "public_key" ("tenant",
                             "fingerprint")
    INITIALLY DEFERRED);

CREATE INDEX "package_build_config_bot_keys_object_id_i"
  ON "package_build_config_bot_keys" (
    "tenant",
    "name",
    "version_epoch",
    "version_canonical_upstream",
    "version_canonical_release",
    "version_revision");

CREATE TABLE "package_other_repositories" (
  "tenant" TEXT NOT NULL,
  "name" CITEXT NOT NULL,
  "version_epoch" INTEGER NOT NULL,
  "version_canonical_upstream" TEXT NOT NULL,
  "version_canonical_release" TEXT NOT NULL COLLATE "C",
  "version_revision" INTEGER NOT NULL,
  "index" BIGINT NOT NULL,
  "repository_tenant" TEXT NOT NULL,
  "repository_canonical_name" TEXT NOT NULL,
  CONSTRAINT "tenant_fk"
    FOREIGN KEY ("tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED,
  CONSTRAINT "object_id_fk"
    FOREIGN KEY ("tenant",
                 "name",
                 "version_epoch",
                 "version_canonical_upstream",
                 "version_canonical_release",
                 "version_revision")
    REFERENCES "package" ("tenant",
                          "name",
                          "version_epoch",
                          "version_canonical_upstream",
                          "version_canonical_release",
                          "version_revision")
    ON DELETE CASCADE,
  CONSTRAINT "repository_tenant_fk"
    FOREIGN KEY ("repository_tenant")
    REFERENCES "tenant" ("id")
    INITIALLY DEFERRED,
  CONSTRAINT "repository_fk"
    FOREIGN KEY ("repository_tenant",
                 "repository_canonical_name")
    REFERENCES "repository" ("tenant",
                             "canonical_name")
    INITIALLY DEFERRED);

CREATE INDEX "package_other_repositories_object_id_i"
  ON "package_other_repositories" (
    "tenant",
    "name",
    "version_epoch",
    "version_canonical_upstream",
    "version_canonical_release",
    "version_revision");

CREATE INDEX "package_other_repositories_index_i"
  ON "package_other_repositories" ("index");

CREATE TABLE "schema_version" (
  "name" TEXT NOT NULL PRIMARY KEY,
  "version" BIGINT NOT NULL,
  "migration" BOOLEAN NOT NULL);

INSERT INTO "schema_version" (
  "name", "version", "migration")
  VALUES ('package', 34, FALSE);

