Support sqlite3

[dnmfarrell]

Hi,

Can you add sqlite3 support? I edited the output sql script to fit the sqlite3 syntax. Not sure how helpful this is:

pragma foreign_keys = on;

--
-- Table: "addresses"
--
CREATE TABLE "addresses" (
  "addresses_id"    integer NOT NULL,
  "users_id"        integer NOT NULL,
  "type"            text NOT NULL DEFAULT '',
  "archived"        integer NOT NULL DEFAULT 0,
  "first_name"      text NOT NULL DEFAULT '',
  "last_name"       text NOT NULL DEFAULT '',
  "company"         text NOT NULL DEFAULT '',
  "address"         text NOT NULL DEFAULT '',
  "address_2"       text NOT NULL DEFAULT '',
  "postal_code"     text NOT NULL DEFAULT '',
  "city"            text NOT NULL DEFAULT '',
  "phone"           text NOT NULL DEFAULT '',
  "states_id"       integer NULL,
  "country_iso_code" text NOT NULL,
  "created"         integer NOT NULL,
  "last_modified"   integer NOT NULL,
  PRIMARY KEY ("addresses_id"),
  CONSTRAINT "addresses_fk_country_iso_code" FOREIGN KEY ("country_iso_code") REFERENCES "countries" ("country_iso_code") ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT "addresses_fk_states_id" FOREIGN KEY ("states_id") REFERENCES "states" ("states_id") ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT "addresses_fk_users_id" FOREIGN KEY ("users_id") REFERENCES "users" ("users_id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "addresses_idx_country_iso_code" ON "addresses" ("country_iso_code");
CREATE INDEX "addresses_idx_states_id" ON "addresses" ("states_id");
CREATE INDEX "addresses_idx_users_id" ON "addresses" ("users_id");

--
-- Table: "attribute_values"
--
CREATE TABLE "attribute_values" (
  "attribute_values_id" integer NOT NULL ,
  "attributes_id"       integer NOT NULL,
  "value"               text NOT NULL,
  "title"               text NOT NULL DEFAULT '',
  "priority"            integer NOT NULL DEFAULT 0,
  PRIMARY KEY ("attribute_values_id"),
  CONSTRAINT "attribute_values_fk_attributes_id" FOREIGN KEY ("attributes_id") REFERENCES "attributes" ("attributes_id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "attribute_values_idx_attributes_id" ON "attribute_values" ("attributes_id");

--
-- Table: "attributes"
--
CREATE TABLE "attributes" (
  "attributes_id"   integer NOT NULL ,
  "name"            text NOT NULL,
  "type"            text NOT NULL DEFAULT '',
  "title"           text NOT NULL DEFAULT '',
  "dynamic"         integer NOT NULL DEFAULT 0,
  "priority"        integer NOT NULL DEFAULT 0,
  PRIMARY KEY ("attributes_id")
);

--
-- Table: "cart_products"
--
CREATE TABLE "cart_products" (
  "cart_products_id"    integer NOT NULL,
  "carts_id"            integer NOT NULL,
  "sku"                 text NOT NULL,
  "cart_position"       integer NOT NULL,
  "quantity"            integer NOT NULL DEFAULT 1,
  "created"             integer NOT NULL,
  "last_modified"       integer NOT NULL,
  PRIMARY KEY ("cart_products_id"),
  CONSTRAINT "cart_products_fk_carts_id" FOREIGN KEY ("carts_id") REFERENCES "carts" ("carts_id") ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT "cart_products_fk_sku" FOREIGN KEY ("sku") REFERENCES "products" ("sku") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "cart_products_idx_carts_id" ON "cart_products" ("carts_id");
CREATE INDEX "cart_products_idx_sku" ON "cart_products" ("sku");

--
-- Table: "carts"
--
CREATE TABLE "carts" (
  "carts_id"        integer NOT NULL,
  "name"            text NOT NULL DEFAULT '',
  "users_id"        integer NULL,
  "sessions_id"     text NULL,
  "created"         integer NOT NULL,
  "last_modified"   integer NOT NULL,
  "approved"        integer NULL,
  "status"          text NOT NULL DEFAULT '',
  PRIMARY KEY ("carts_id"),
  CONSTRAINT "carts_name_sessions_id" UNIQUE ("name", "sessions_id"),
  CONSTRAINT "carts_fk_sessions_id" FOREIGN KEY ("sessions_id") REFERENCES "sessions" ("sessions_id") ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT "carts_fk_users_id" FOREIGN KEY ("users_id") REFERENCES "users" ("users_id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "carts_idx_sessions_id" ON "carts" ("sessions_id");
CREATE INDEX "carts_idx_users_id" ON "carts" ("users_id");

--
-- Table: "countries"
--
CREATE TABLE "countries" (
  "country_iso_code"    text NOT NULL DEFAULT '',
  "scope"               text NOT NULL DEFAULT '',
  "name"                text NOT NULL DEFAULT '',
  "priority"            integer NOT NULL DEFAULT 0,
  "show_states"         integer NOT NULL DEFAULT 0,
  "active"              integer NOT NULL DEFAULT 1,
  PRIMARY KEY ("country_iso_code")
);

--
-- Table: "inventories"
--
CREATE TABLE "inventories" (
  "sku" text NOT NULL,
  "quantity" integer NOT NULL DEFAULT 0,
  PRIMARY KEY ("sku"),
  CONSTRAINT "inventories_fk_sku" FOREIGN KEY ("sku") REFERENCES "products" ("sku") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "inventories_idx_sku" ON inventories ("sku");

--
-- Table: "media_displays"
--
CREATE TABLE "media_displays" (
  "media_displays_id"   integer NOT NULL ,
  "media_types_id"      integer NOT NULL,
  "type"                text NOT NULL,
  "name"                text NULL,
  "path"                text NULL,
  "size"                text NULL,
  PRIMARY KEY ("media_displays_id"),
  CONSTRAINT "media_types_id_type_unique" UNIQUE ("media_types_id", "type"),
  CONSTRAINT "media_displays_fk_media_types_id" FOREIGN KEY ("media_types_id") REFERENCES "media_types" ("media_types_id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "media_displays_idx_media_types_id" ON "media_displays" ("media_types_id");

--
-- Table: "media_products"
--
CREATE TABLE "media_products" (
  "media_id"    integer NOT NULL,
  "sku"         text NOT NULL,
  PRIMARY KEY ("media_id", "sku"),
  CONSTRAINT "media_products_fk_media_id" FOREIGN KEY ("media_id") REFERENCES "medias" ("media_id") ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT "media_products_fk_sku" FOREIGN KEY ("sku") REFERENCES "products" ("sku") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "media_products_idx_media_id" ON "media_products" ("media_id");
CREATE INDEX "media_products_idx_sku" ON "media_products" ("sku");

--
-- Table: "media_types"
--
CREATE TABLE "media_types" (
  "media_types_id"  integer NOT NULL,
  "type"            text NOT NULL,
  PRIMARY KEY ("media_types_id"),
  CONSTRAINT "media_types_type" UNIQUE ("type")
);

--
-- Table: "merchandising_attributes"
--
CREATE TABLE "merchandising_attributes" (
  "merchandising_attributes_id" integer NOT NULL,
  "merchandising_products_id"   integer NOT NULL,
  "name"                        text NOT NULL,
  "value"                       text NOT NULL,
  PRIMARY KEY ("merchandising_attributes_id"),
  CONSTRAINT "merchandising_attributes_fk_merchandising_products_id" FOREIGN KEY ("merchandising_products_id") REFERENCES "merchandising_products" ("merchandising_products_id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "merchandising_attributes_idx_merchandising_products_id" ON "merchandising_attributes" ("merchandising_products_id");

--
-- Table: "merchandising_products"
--
CREATE TABLE "merchandising_products" (
  "merchandising_products_id"   integer NOT NULL,
  "sku"                         text NULL,
  "sku_related"                 text NULL,
  "type"                        text NOT NULL DEFAULT '',
  PRIMARY KEY ("merchandising_products_id"),
  CONSTRAINT "merchandising_products_fk_sku" FOREIGN KEY ("sku") REFERENCES "products" ("sku") ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT "merchandising_products_fk_sku_related" FOREIGN KEY ("sku_related") REFERENCES "products" ("sku") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "merchandising_products_idx_sku" ON "merchandising_products" ("sku");
CREATE INDEX "merchandising_products_idx_sku_related" ON "merchandising_products" ("sku_related");

--
-- Table: "message_types"
--
CREATE TABLE "message_types" (
  "message_types_id"    integer NOT NULL,
  "name"                text NOT NULL,
  "active"              integer NOT NULL DEFAULT 1,
  PRIMARY KEY ("message_types_id"),
  CONSTRAINT "message_types_name" UNIQUE ("name")
);

--
-- Table: "navigation_attribute_values"
--
CREATE TABLE "navigation_attribute_values" (
  "navigation_attribute_values_id"  integer NOT NULL,
  "navigation_attributes_id"        integer NOT NULL,
  "attribute_values_id"             integer NOT NULL,
  PRIMARY KEY ("navigation_attribute_values_id"),
  CONSTRAINT "navigation_attribute_values_fk_attribute_values_id" FOREIGN KEY ("attribute_values_id") REFERENCES "attribute_values" ("attribute_values_id") ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT "navigation_attribute_values_fk_navigation_attributes_id" FOREIGN KEY ("navigation_attributes_id") REFERENCES "navigation_attributes" ("navigation_attributes_id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "navigation_attribute_values_idx_attribute_values_id" ON "navigation_attribute_values" ("attribute_values_id");
CREATE INDEX "navigation_attribute_values_idx_navigation_attributes_id" ON "navigation_attribute_values" ("navigation_attributes_id");

--
-- Table: "navigation_attributes"
--
CREATE TABLE "navigation_attributes" (
  "navigation_attributes_id"    integer NOT NULL,
  "navigation_id"               integer NOT NULL,
  "attributes_id"               integer NOT NULL,
  PRIMARY KEY ("navigation_attributes_id"),
  CONSTRAINT "navigation_attributes_fk_attributes_id" FOREIGN KEY ("attributes_id") REFERENCES "attributes" ("attributes_id") ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT "navigation_attributes_fk_navigation_id" FOREIGN KEY ("navigation_id") REFERENCES "navigations" ("navigation_id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "navigation_attributes_idx_attributes_id" ON "navigation_attributes" ("attributes_id");
CREATE INDEX "navigation_attributes_idx_navigation_id" ON "navigation_attributes" ("navigation_id");

--
-- Table: "navigation_products"
--
CREATE TABLE "navigation_products" (
  "sku"             text NOT NULL,
  "navigation_id"   integer NOT NULL,
  "type"            text NOT NULL DEFAULT '',
  PRIMARY KEY ("sku", "navigation_id"),
  CONSTRAINT "navigation_products_fk_navigation_id" FOREIGN KEY ("navigation_id") REFERENCES "navigations" ("navigation_id") ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT "navigation_products_fk_sku" FOREIGN KEY ("sku") REFERENCES "products" ("sku") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "navigation_products_idx_navigation_id" ON "navigation_products" ("navigation_id");
CREATE INDEX "navigation_products_idx_sku" ON "navigation_products" ("sku");

--
-- Table: "navigations"
--
CREATE TABLE "navigations" (
  "navigation_id"   integer NOT NULL,
  "uri"             text NOT NULL DEFAULT '',
  "type"            text NOT NULL DEFAULT '',
  "scope"           text NOT NULL DEFAULT '',
  "name"            text NOT NULL DEFAULT '',
  "description"     text NOT NULL DEFAULT '',
  "alias"           integer NOT NULL DEFAULT 0,
  "parent_id"       integer NULL,
  "priority"        integer NOT NULL DEFAULT 0,
  "product_count"   integer NOT NULL DEFAULT 0,
  "created"         integer NOT NULL,
  "last_modified"   integer NOT NULL,
  "active"          integer NOT NULL DEFAULT 1,
  PRIMARY KEY ("navigation_id"),
  CONSTRAINT "navigations_uri" UNIQUE ("uri"),
  CONSTRAINT "navigations_fk_parent_id" FOREIGN KEY ("parent_id") REFERENCES "navigations" ("navigation_id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "navigations_idx_parent_id" ON "navigations" ("parent_id");

--
-- Table: "orderlines"
--
CREATE TABLE "orderlines" (
  "orderlines_id"       integer NOT NULL,
  "orders_id"           integer NOT NULL,
  "order_position"      integer NOT NULL DEFAULT 0,
  "sku"                 text NOT NULL,
  "name"                text NOT NULL DEFAULT '',
  "short_description"   text NOT NULL DEFAULT '',
  "description"         text NOT NULL,
  "weight"              real NOT NULL DEFAULT 0.0,
  "quantity"            integer NULL,
  "price"               real NOT NULL DEFAULT 0.0,
  "subtotal"            real NOT NULL DEFAULT 0.0,
  "shipping"            real NOT NULL DEFAULT 0.0,
  "handling"            real NOT NULL DEFAULT 0.0,
  "salestax"            real NOT NULL DEFAULT 0.0,
  "status"              text NOT NULL DEFAULT '',
  PRIMARY KEY ("orderlines_id"),
  CONSTRAINT "orderlines_fk_orders_id" FOREIGN KEY ("orders_id") REFERENCES "orders" ("orders_id") ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT "orderlines_fk_sku" FOREIGN KEY ("sku") REFERENCES "products" ("sku") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "orderlines_idx_orders_id" ON "orderlines" ("orders_id");
CREATE INDEX "orderlines_idx_sku" ON "orderlines" ("sku");

--
-- Table: "orderlines_shippings"
--
CREATE TABLE "orderlines_shippings" (
  "orderlines_id"   integer NOT NULL,
  "addresses_id"    integer NOT NULL,
  "shipments_id"    integer NOT NULL,
  PRIMARY KEY ("orderlines_id", "addresses_id"),
  CONSTRAINT "orderlines_shippings_fk_addresses_id" FOREIGN KEY ("addresses_id") REFERENCES "addresses" ("addresses_id") ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT "orderlines_shippings_fk_orderlines_id" FOREIGN KEY ("orderlines_id") REFERENCES "orderlines" ("orderlines_id") ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT "orderlines_shippings_fk_shipments_id" FOREIGN KEY ("shipments_id") REFERENCES "shipments" ("shipments_id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "orderlines_shippings_idx_addresses_id" ON "orderlines_shippings" ("addresses_id");
CREATE INDEX "orderlines_shippings_idx_orderlines_id" ON "orderlines_shippings" ("orderlines_id");
CREATE INDEX "orderlines_shippings_idx_shipments_id" ON "orderlines_shippings" ("shipments_id");

--
-- Table: "orders"
--
CREATE TABLE "orders" (
  "orders_id"               integer NOT NULL,
  "order_number"            text NOT NULL,
  "order_date"              integer NULL,
  "users_id"                integer NOT NULL,
  "email"                   text NOT NULL DEFAULT '',
  "shipping_addresses_id"   integer NOT NULL,
  "billing_addresses_id"    integer NOT NULL,
  "weight"                  real NOT NULL DEFAULT 0.0,
  "payment_method"          text NOT NULL DEFAULT '',
  "payment_number"          text NOT NULL DEFAULT '',
  "payment_status"          text NOT NULL DEFAULT '',
  "shipping_method"         text NOT NULL DEFAULT '',
  "subtotal"                real NOT NULL DEFAULT 0.0,
  "shipping"                real NOT NULL DEFAULT 0.0,
  "handling"                real NOT NULL DEFAULT 0.0,
  "salestax"                real NOT NULL DEFAULT 0.0,
  "total_cost"              real NOT NULL DEFAULT 0.0,
  "status"                  text NOT NULL DEFAULT '',
  PRIMARY KEY ("orders_id"),
  CONSTRAINT "orders_order_number" UNIQUE ("order_number"),
  CONSTRAINT "orders_fk_billing_addresses_id" FOREIGN KEY ("billing_addresses_id") REFERENCES "addresses" ("addresses_id") ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT "orders_fk_shipping_addresses_id" FOREIGN KEY ("shipping_addresses_id") REFERENCES "addresses" ("addresses_id") ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT "orders_fk_users_id" FOREIGN KEY ("users_id") REFERENCES "users" ("users_id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "orders_idx_billing_addresses_id" ON "orders" ("billing_addresses_id");
CREATE INDEX "orders_idx_shipping_addresses_id" ON "orders" ("shipping_addresses_id");
CREATE INDEX "orders_idx_users_id" ON "orders" ("users_id");

--
-- Table: "permissions"
--
CREATE TABLE "permissions" (
  "permissions_id"  integer NOT NULL,
  "roles_id"        integer NOT NULL,
  "perm"            text NOT NULL DEFAULT '',
  PRIMARY KEY ("permissions_id"),
  CONSTRAINT "permissions_fk_roles_id" FOREIGN KEY ("roles_id") REFERENCES "roles" ("roles_id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "permissions_idx_roles_id" ON "permissions" ("roles_id");

--
-- Table: "price_modifiers"
--
CREATE TABLE "price_modifiers" (
  "price_modifiers_id"  integer NOT NULL,
  "sku"                 text NOT NULL,
  "quantity"            integer NOT NULL DEFAULT 0,
  "roles_id"            integer NOT NULL,
  "price"               real NOT NULL DEFAULT 0.0,
  "start_date"          integer NULL,
  "end_date"            integer NULL,
  PRIMARY KEY ("price_modifiers_id"),
  CONSTRAINT "price_modifiers_fk_sku" FOREIGN KEY ("sku") REFERENCES "products" ("sku") ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT "price_modifiers_fk_roles_id" FOREIGN KEY ("roles_id") REFERENCES "roles" ("roles_id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "price_modifiers_idx_sku" ON "price_modifiers" ("sku");
CREATE INDEX "price_modifiers_idx_roles_id" ON "price_modifiers" ("roles_id");

--
-- Table: "product_attribute_values"
--
CREATE TABLE "product_attribute_values" (
  "product_attribute_values_id" integer NOT NULL,
  "product_attributes_id"       integer NOT NULL,
  "attribute_values_id"         integer NOT NULL,
  PRIMARY KEY ("product_attribute_values_id"),
  CONSTRAINT "product_attribute_values_fk_attribute_values_id" FOREIGN KEY ("attribute_values_id") REFERENCES "attribute_values" ("attribute_values_id") ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT "product_attribute_values_fk_product_attributes_id" FOREIGN KEY ("product_attributes_id") REFERENCES "product_attributes" ("product_attributes_id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "product_attribute_values_idx_attribute_values_id" ON "product_attribute_values" ("attribute_values_id");
CREATE INDEX "product_attribute_values_idx_product_attributes_id" ON "product_attribute_values" ("product_attributes_id");

--
-- Table: "product_attributes"
--
CREATE TABLE "product_attributes" (
  "product_attributes_id"   integer NOT NULL,
  "sku"                     text NOT NULL,
  "attributes_id"           integer NOT NULL,
  "canonical"               integer NOT NULL DEFAULT '1',
  PRIMARY KEY ("product_attributes_id"),
  CONSTRAINT "product_attributes_fk_attributes_id" FOREIGN KEY ("attributes_id") REFERENCES "attributes" ("attributes_id") ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT "product_attributes_fk_sku" FOREIGN KEY ("sku") REFERENCES "products" ("sku") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "product_attributes_idx_attributes_id" ON "product_attributes" ("attributes_id");
CREATE INDEX "product_attributes_idx_sku" ON "product_attributes" ("sku");

--
-- Table: "products"
--
CREATE TABLE "products" (
  "sku"                 text NOT NULL,
  "name"                text NOT NULL,
  "short_description"   text NOT NULL DEFAULT '',
  "description"         text NOT NULL,
  "price"               real NOT NULL DEFAULT 0.0,
  "uri"                 text NULL,
  "weight"              real NOT NULL DEFAULT 0.0,
  "priority"            integer NOT NULL DEFAULT 0,
  "gtin"                text NULL,
  "canonical_sku"       text NULL,
  "active"              integer NOT NULL DEFAULT 1,
  "inventory_exempt"    integer NOT NULL DEFAULT 0,
  "created"             integer NOT NULL,
  "last_modified"       integer NOT NULL,
  PRIMARY KEY ("sku"),
  CONSTRAINT "products_gtin" UNIQUE ("gtin"),
  CONSTRAINT "products_uri" UNIQUE ("uri"),
  CONSTRAINT "products_fk_canonical_sku" FOREIGN KEY ("canonical_sku") REFERENCES "products" ("sku")
);
CREATE INDEX "products_idx_canonical_sku" ON "products" ("canonical_sku");

--
-- Table: "roles"
--
CREATE TABLE "roles" (
  "roles_id"    integer NOT NULL,
  "name"        text NOT NULL,
  "label"       text NOT NULL,
  "description" text NOT NULL,
  PRIMARY KEY ("roles_id"),
  CONSTRAINT "roles_name" UNIQUE ("name")
);

--
-- Table: "sessions"
--
CREATE TABLE "sessions" (
  "sessions_id"     text NOT NULL,
  "session_data"    text NOT NULL,
  "created"         integer NOT NULL,
  "last_modified"   integer NOT NULL,
  PRIMARY KEY ("sessions_id")
);

--
-- Table: "settings"
--
CREATE TABLE "settings" (
  "settings_id" integer NOT NULL,
  "scope"       text NOT NULL,
  "site"        text NOT NULL DEFAULT '',
  "name"        text NOT NULL,
  "value"       text NOT NULL,
  "category"    text NOT NULL DEFAULT '',
  PRIMARY KEY ("settings_id")
);

--
-- Table: "shipment_carriers"
--
CREATE TABLE "shipment_carriers" (
  "shipment_carriers_id"    integer NOT NULL,
  "name"                    text NOT NULL DEFAULT '',
  "title"                   text NOT NULL DEFAULT '',
  "account_number"          text NOT NULL DEFAULT '',
  "active"                  integer NOT NULL DEFAULT 1,
  PRIMARY KEY ("shipment_carriers_id")
);

--
-- Table: "shipment_destinations"
--
CREATE TABLE "shipment_destinations" (
  "shipment_destinations_id"    integer NOT NULL,
  "zones_id"                    integer NOT NULL,
  "shipment_methods_id"         integer NOT NULL,
  "active"                      integer NOT NULL DEFAULT 1,
  PRIMARY KEY ("shipment_destinations_id"),
  CONSTRAINT "shipment_destinations_fk_shipment_methods_id" FOREIGN KEY ("shipment_methods_id") REFERENCES "shipment_methods" ("shipment_methods_id") ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT "shipment_destinations_fk_zones_id" FOREIGN KEY ("zones_id") REFERENCES "zones" ("zones_id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "shipment_destinations_idx_shipment_methods_id" ON "shipment_destinations" ("shipment_methods_id");
CREATE INDEX "shipment_destinations_idx_zones_id" ON "shipment_destinations" ("zones_id");

--
-- Table: "shipment_methods"
--
CREATE TABLE "shipment_methods" (
  "shipment_methods_id"     integer NOT NULL,
  "name"                    text NOT NULL DEFAULT '',
  "title"                   text NOT NULL DEFAULT '',
  "min_weight"              real NOT NULL DEFAULT 0.0,
  "max_weight"              real NOT NULL DEFAULT 0.0,
  "shipment_carriers_id"    integer NOT NULL,
  "active"                  integer NOT NULL DEFAULT 1,
  PRIMARY KEY ("shipment_methods_id"),
  CONSTRAINT "shipment_methods_fk_shipment_carriers_id" FOREIGN KEY ("shipment_carriers_id") REFERENCES "shipment_carriers" ("shipment_carriers_id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "shipment_methods_idx_shipment_carriers_id" ON "shipment_methods" ("shipment_carriers_id");

--
-- Table: "shipment_rates"
--
CREATE TABLE "shipment_rates" (
  "shipment_rates_id"   integer NOT NULL,
  "zones_id"            integer NOT NULL,
  "shipment_methods_id" integer NOT NULL,
  "min_weight"          real NOT NULL DEFAULT 0.0,
  "max_weight"          real NOT NULL DEFAULT 0.0,
  "price"               real NOT NULL DEFAULT 0.0,
  "created"             integer NOT NULL,
  "last_modified"       integer NOT NULL,
  PRIMARY KEY ("shipment_rates_id"),
  CONSTRAINT "shipment_rates_fk_shipment_methods_id" FOREIGN KEY ("shipment_methods_id") REFERENCES "shipment_methods" ("shipment_methods_id") ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT "shipment_rates_fk_zones_id" FOREIGN KEY ("zones_id") REFERENCES "zones" ("zones_id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "shipment_rates_idx_shipment_methods_id" ON "shipment_rates" ("shipment_methods_id");
CREATE INDEX "shipment_rates_idx_zones_id" ON "shipment_rates" ("zones_id");

--
-- Table: "shipments"
--
CREATE TABLE "shipments" (
  "shipments_id"            integer NOT NULL,
  "tracking_number"         text NOT NULL DEFAULT '',
  "shipment_carriers_id"    integer NOT NULL,
  "shipment_methods_id"     integer NOT NULL,
  "created"                 integer NOT NULL,
  "last_modified"           integer NOT NULL,
  PRIMARY KEY ("shipments_id"),
  CONSTRAINT "shipments_fk_shipment_carriers_id" FOREIGN KEY ("shipment_carriers_id") REFERENCES "shipment_carriers" ("shipment_carriers_id") ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT "shipments_fk_shipment_methods_id" FOREIGN KEY ("shipment_methods_id") REFERENCES "shipment_methods" ("shipment_methods_id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "shipments_idx_shipment_carriers_id" ON "shipments" ("shipment_carriers_id");
CREATE INDEX "shipments_idx_shipment_methods_id" ON "shipments" ("shipment_methods_id");

--
-- Table: "states"
--
CREATE TABLE "states" (
  "states_id"           integer NOT NULL,
  "scope"               text NOT NULL DEFAULT '',
  "country_iso_code"    text NOT NULL,
  "state_iso_code"      text NOT NULL DEFAULT '',
  "name"                text NOT NULL DEFAULT '',
  "priority"            integer NOT NULL DEFAULT 0,
  "active"              integer NOT NULL DEFAULT 1,
  PRIMARY KEY ("states_id"),
  CONSTRAINT "states_state_country" UNIQUE ("country_iso_code", "state_iso_code"),
  CONSTRAINT "states_fk_country_iso_code" FOREIGN KEY ("country_iso_code") REFERENCES "countries" ("country_iso_code") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "states_idx_country_iso_code" ON "states" ("country_iso_code");

--
-- Table: "taxes"
--
CREATE TABLE "taxes" (
  "taxes_id"            integer NOT NULL,
  "tax_name"            text NOT NULL,
  "description"         text NOT NULL,
  "percent"             real NOT NULL,
  "decimal_places"      integer NOT NULL DEFAULT 2,
  "rounding"            text NULL,
  "valid_from"          integer NOT NULL,
  "valid_to"            integer NULL,
  "country_iso_code"    text NULL,
  "states_id"           integer NULL,
  "created"             integer NOT NULL,
  "last_modified"       integer NOT NULL,
  PRIMARY KEY ("taxes_id"),
  CONSTRAINT "taxes_fk_country_iso_code" FOREIGN KEY ("country_iso_code") REFERENCES "countries" ("country_iso_code") ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT "taxes_fk_states_id" FOREIGN KEY ("states_id") REFERENCES "states" ("states_id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "taxes_idx_country_iso_code" ON "taxes" ("country_iso_code");
CREATE INDEX "taxes_idx_states_id" ON "taxes" ("states_id");
CREATE INDEX "taxes_idx_tax_name" ON "taxes" ("tax_name");
CREATE INDEX "taxes_idx_valid_from" ON "taxes" ("valid_from");
CREATE INDEX "taxes_idx_valid_to" ON "taxes" ("valid_to");

--
-- Table: "user_attribute_values"
--
CREATE TABLE "user_attribute_values" (
  "user_attribute_values_id"    integer NOT NULL,
  "user_attributes_id"          integer NOT NULL,
  "attribute_values_id"         integer NOT NULL,
  PRIMARY KEY ("user_attribute_values_id"),
  CONSTRAINT "user_attribute_values_fk_attribute_values_id" FOREIGN KEY ("attribute_values_id") REFERENCES "attribute_values" ("attribute_values_id") ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT "user_attribute_values_fk_user_attributes_id" FOREIGN KEY ("user_attributes_id") REFERENCES "user_attributes" ("user_attributes_id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "user_attribute_values_idx_attribute_values_id" ON "user_attribute_values" ("attribute_values_id");
CREATE INDEX "user_attribute_values_idx_user_attributes_id" ON "user_attribute_values" ("user_attributes_id");

--
-- Table: "user_attributes"
--
CREATE TABLE "user_attributes" (
  "user_attributes_id" integer NOT NULL ,
  "users_id" integer NOT NULL,
  "attributes_id" integer NOT NULL,
  PRIMARY KEY ("user_attributes_id"),
  CONSTRAINT "user_attributes_fk_attributes_id" FOREIGN KEY ("attributes_id") REFERENCES "attributes" ("attributes_id") ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT "user_attributes_fk_users_id" FOREIGN KEY ("users_id") REFERENCES "users" ("users_id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "user_attributes_idx_attributes_id" ON "user_attributes" ("attributes_id");
CREATE INDEX "user_attributes_idx_users_id" ON "user_attributes" ("users_id");

--
-- Table: "user_roles"
--
CREATE TABLE "user_roles" (
  "users_id" integer NOT NULL,
  "roles_id" integer NOT NULL,
  PRIMARY KEY ("users_id", "roles_id"),
  CONSTRAINT "user_roles_fk_roles_id" FOREIGN KEY ("roles_id") REFERENCES "roles" ("roles_id") ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT "user_roles_fk_users_id" FOREIGN KEY ("users_id") REFERENCES "users" ("users_id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "user_roles_idx_roles_id" ON "user_roles" ("roles_id");
CREATE INDEX "user_roles_idx_users_id" ON "user_roles" ("users_id");

--
-- Table: "users"
--
CREATE TABLE "users" (
  "users_id"        integer NOT NULL,
  "username"        text NOT NULL,
  "nickname"        text NULL,
  "email"           text NOT NULL DEFAULT '',
  "password"        text NOT NULL DEFAULT '',
  "first_name"      text NOT NULL DEFAULT '',
  "last_name"       text NOT NULL DEFAULT '',
  "last_login"      integer NULL,
  "fail_count"      integer NOT NULL DEFAULT 0,
  "created"         integer NOT NULL,
  "last_modified"   integer NOT NULL,
  "active"          integer NOT NULL DEFAULT 1,
  PRIMARY KEY ("users_id"),
  CONSTRAINT "users_nickname" UNIQUE ("nickname"),
  CONSTRAINT "users_username" UNIQUE ("username")
);

--
-- Table: "zone_countries"
--
CREATE TABLE "zone_countries" (
  "zones_id"            integer NOT NULL,
  "country_iso_code"    text NOT NULL,
  PRIMARY KEY ("zones_id", "country_iso_code"),
  CONSTRAINT "zone_countries_fk_country_iso_code" FOREIGN KEY ("country_iso_code") REFERENCES "countries" ("country_iso_code") ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT "zone_countries_fk_zones_id" FOREIGN KEY ("zones_id") REFERENCES "zones" ("zones_id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "zone_countries_idx_country_iso_code" ON "zone_countries" ("country_iso_code");
CREATE INDEX "zone_countries_idx_zones_id" ON "zone_countries" ("zones_id");

--
-- Table: "zone_states"
--
CREATE TABLE "zone_states" (
  "zones_id"    integer NOT NULL,
  "states_id"   integer NOT NULL,
  PRIMARY KEY ("zones_id", "states_id"),
  CONSTRAINT "zone_states_fk_states_id" FOREIGN KEY ("states_id") REFERENCES "states" ("states_id") ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT "zone_states_fk_zones_id" FOREIGN KEY ("zones_id") REFERENCES "zones" ("zones_id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "zone_states_idx_states_id" ON "zone_states" ("states_id");
CREATE INDEX "zone_states_idx_zones_id" ON "zone_states" ("zones_id");

--
-- Table: "zones"
--
CREATE TABLE "zones" (
  "zones_id"        integer NOT NULL,
  "zone"            text NOT NULL,
  "created"         integer NOT NULL,
  "last_modified"   integer NOT NULL,
  PRIMARY KEY ("zones_id"),
  CONSTRAINT "zones_zone" UNIQUE ("zone")
);

--
-- Table: "payment_orders"
--
CREATE TABLE "payment_orders" (
  "payment_orders_id"   integer NOT NULL,
  "payment_mode"        text NOT NULL DEFAULT '',
  "payment_action"      text NOT NULL DEFAULT '',
  "payment_id"          text NOT NULL DEFAULT '',
  "auth_code"           text NOT NULL DEFAULT '',
  "users_id"            integer NULL,
  "sessions_id"         text NULL,
  "orders_id"           integer NULL,
  "amount"              real NOT NULL DEFAULT 0.0,
  "status"              text NOT NULL DEFAULT '',
  "payment_sessions_id" text NOT NULL DEFAULT '',
  "payment_error_code"  text NOT NULL DEFAULT '',
  "payment_error_message" text NULL,
  "payment_fee"         real NOT NULL DEFAULT 0.0,
  "created"             integer NOT NULL,
  "last_modified"       integer NOT NULL,
  PRIMARY KEY ("payment_orders_id"),
  CONSTRAINT "payment_orders_fk_orders_id" FOREIGN KEY ("orders_id") REFERENCES "orders" ("orders_id") ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT "payment_orders_fk_sessions_id" FOREIGN KEY ("sessions_id") REFERENCES "sessions" ("sessions_id") ON DELETE SET NULL,
  CONSTRAINT "payment_orders_fk_users_id" FOREIGN KEY ("users_id") REFERENCES "users" ("users_id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "payment_orders_idx_orders_id" ON "payment_orders"("orders_id");
CREATE INDEX "payment_orders_idx_sessions_id" ON "payment_orders" ("sessions_id");
CREATE INDEX "payment_orders_idx_users_id" ON "payment_orders" ("users_id");

--
-- Table: "medias"
--
CREATE TABLE "medias" (
  "media_id"        integer NOT NULL,
  "file"            text NOT NULL DEFAULT '',
  "uri"             text NOT NULL DEFAULT '',
  "mime_type"       text NOT NULL DEFAULT '',
  "label"           text NOT NULL DEFAULT '',
  "author_users_id" integer NULL,
  "created"         integer NOT NULL,
  "last_modified"   integer NOT NULL,
  "active"          integer NOT NULL DEFAULT 1,
  "media_types_id"  integer NOT NULL,
  PRIMARY KEY ("media_id"),
  CONSTRAINT "media_id_media_types_id_unique" UNIQUE ("media_id", "media_types_id"),
  CONSTRAINT "medias_file" UNIQUE ("file"),
  CONSTRAINT "medias_fk_author_users_id" FOREIGN KEY ("author_users_id") REFERENCES "users" ("users_id"),
  CONSTRAINT "medias_fk_media_types_id" FOREIGN KEY ("media_types_id") REFERENCES "media_types" ("media_types_id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "medias_idx_author_users_id" ON "medias" ("author_users_id");
CREATE INDEX "medias_idx_media_types_id" ON "medias" ("media_types_id");

--
-- Table: "messages"
--
CREATE TABLE "messages" (
  "messages_id"         integer NOT NULL,
  "title"               text NOT NULL DEFAULT '',
  "message_types_id"    integer NOT NULL,
  "uri"                 text NULL,
  "content"             text NOT NULL,
  "author_users_id"     integer NULL,
  "rating"              real NOT NULL DEFAULT 0,
  "recommend"           integer NULL,
  "public"              integer NOT NULL DEFAULT 0,
  "approved"            integer NOT NULL DEFAULT 0,
  "approved_by_users_id" integer NULL,
  "created"             integer NOT NULL,
  "last_modified"       integer NOT NULL,
  PRIMARY KEY ("messages_id"),
  CONSTRAINT "messages_fk_approved_by_users_id" FOREIGN KEY ("approved_by_users_id") REFERENCES "users" ("users_id") ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT "messages_fk_author_users_id" FOREIGN KEY ("author_users_id") REFERENCES "users" ("users_id") ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT "messages_fk_message_types_id" FOREIGN KEY ("message_types_id") REFERENCES "message_types" ("message_types_id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "messages_idx_approved_by_users_id" ON "messages" ("approved_by_users_id");
CREATE INDEX "messages_idx_author_users_id" ON "messages" ("author_users_id");
CREATE INDEX "messages_idx_message_types_id" ON "messages" ("message_types_id");

--
-- Table: "order_comments"
--
CREATE TABLE "order_comments" (
  "messages_id" integer NOT NULL,
  "orders_id"   integer NOT NULL,
  PRIMARY KEY ("messages_id", "orders_id"),
  CONSTRAINT "order_comments_fk_messages_id" FOREIGN KEY ("messages_id") REFERENCES "messages" ("messages_id") ON DELETE CASCADE,
  CONSTRAINT "order_comments_fk_orders_id" FOREIGN KEY ("orders_id") REFERENCES "orders" ("orders_id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX "order_comments_idx_messages_id" ON "order_comments" ("messages_id");
CREATE INDEX "order_comments_idx_orders_id" ON "order_comments" ("orders_id");

--
-- Table: "product_reviews"
--
CREATE TABLE "product_reviews" (
  "messages_id" integer NOT NULL,
  "sku"         text NOT NULL,
  PRIMARY KEY ("messages_id", "sku"),
  CONSTRAINT "product_reviews_fk_messages_id" FOREIGN KEY ("messages_id") REFERENCES "messages" ("messages_id") ON DELETE CASCADE,
  CONSTRAINT "product_reviews_fk_sku" FOREIGN KEY ("sku") REFERENCES "products" ("sku") ON DELETE CASCADE
);
CREATE INDEX "product_reviews_idx_messages_id" ON "product_reviews" ("messages_id");
CREATE INDEX "product_reviews_idx_sku" ON "product_reviews" ("sku");

pragma foreign_keys = on;

[hexfusion]

@sillymoose welcome! By adding 'SQLite' to the interchange6-create-database script I was able to generate sqlite sql file. We do test againt sqlite so this should work but please let us know if you run into trouble.

https://github.com/interchange/interchange6-schema/blob/master/bin/interchange6-create-database

#! /usr/bin/env perl

use strict;
use warnings;

use Interchange6::Schema;

use Getopt::Long;

my ($mysql_version, $postgres_version, $clean);
GetOptions (
    "clean"  => \$clean,
    "mysql_version=s"   => \$mysql_version,
    "postgres_version=s"   => \$postgres_version,
) or die("Error in command line arguments\n");

my $dsn = shift;
my $schema = Interchange6::Schema->connect($dsn);

$schema->create_ddl_dir(['MySQL', 'PostgreSQL','SQLite'],
                        $Interchange6::Schema::VERSION,
                        './sql',
                        undef,
                        # needed for MySQL producer, otherwise booleans are broken
                        # https://github.com/interchange/interchange6-schema/issues/1
                        {
                producer_args => {
                    mysql_version => $mysql_version || 5,
                    postgres_version => $postgres_version || 9,
                },
                add_drop_table => $clean,
            },
                        );

[dnmfarrell]

Fantastic, thank you :)

[dnmfarrell]

Tested on local, works for me.

[hexfusion]

@sillymoose we aren't really happy with this solution please follow #143 for further improvements proposed for this script. But as you can see the sql files generated are usable atm.

[SysPete]

Closing issue since further activity will be tracked against issue #143