Release commit for 1.62

[dbsrgits/SQL-Translator.git] / t / 48xml-to-sqlite.t

#!/usr/bin/perl
use strict;

use FindBin qw/$Bin/;
use Test::More;
use Test::SQL::Translator;
use Test::Exception;
use Test::Differences;
use Data::Dumper;
use SQL::Translator;
use SQL::Translator::Schema::Constants;


BEGIN {
    maybe_plan(2, 'SQL::Translator::Parser::XML::SQLFairy',
              'SQL::Translator::Producer::SQLite');
}

my $xmlfile = "$Bin/data/xml/schema.xml";

my $sqlt;
$sqlt = SQL::Translator->new(
    quote_identifiers => 1,
    no_comments => 1,
    show_warnings  => 0,
    add_drop_table => 1,
);

die "Can't find test schema $xmlfile" unless -e $xmlfile;

my $sql = $sqlt->translate(
    from     => 'XML-SQLFairy',
    to       => 'SQLite',
    filename => $xmlfile,
) or die $sqlt->error;

eq_or_diff($sql, << "SQL");
BEGIN TRANSACTION;

DROP TABLE "Basic";

CREATE TABLE "Basic" (
  "id" INTEGER PRIMARY KEY NOT NULL,
  "title" varchar(100) NOT NULL DEFAULT 'hello',
  "description" text DEFAULT '',
  "email" varchar(500),
  "explicitnulldef" varchar,
  "explicitemptystring" varchar DEFAULT '',
  -- Hello emptytagdef
  "emptytagdef" varchar DEFAULT '',
  "another_id" int(10) DEFAULT 2,
  "timest" timestamp,
  FOREIGN KEY ("another_id") REFERENCES "Another"("id")
);

CREATE INDEX "titleindex" ON "Basic" ("title");

CREATE UNIQUE INDEX "emailuniqueindex" ON "Basic" ("email");

CREATE UNIQUE INDEX "very_long_index_name_on_title_field_which_should_be_truncated_for_various_rdbms" ON "Basic" ("title");

DROP TABLE "Another";

CREATE TABLE "Another" (
  "id" INTEGER PRIMARY KEY NOT NULL,
  "num" numeric(10,2)
);

DROP VIEW IF EXISTS "email_list";

CREATE VIEW "email_list" AS
    SELECT email FROM Basic WHERE (email IS NOT NULL);

DROP TRIGGER IF EXISTS "foo_trigger";

CREATE TRIGGER "foo_trigger" after insert on "Basic" BEGIN update modified=timestamp(); END;

DROP TRIGGER IF EXISTS "bar_trigger_insert";

CREATE TRIGGER "bar_trigger_insert" before insert on "Basic" BEGIN update modified2=timestamp(); END;

DROP TRIGGER IF EXISTS "bar_trigger_update";

CREATE TRIGGER "bar_trigger_update" before update on "Basic" BEGIN update modified2=timestamp(); END;

COMMIT;
SQL

# Test in list context
my @sql = $sqlt->translate(
    from     => 'XML-SQLFairy',
    to       => 'SQLite',
    filename => $xmlfile,
) or die $sqlt->error;

eq_or_diff(\@sql,
          [
          'BEGIN TRANSACTION',
          q<DROP TABLE "Basic">,
          q<CREATE TABLE "Basic" (
  "id" INTEGER PRIMARY KEY NOT NULL,
  "title" varchar(100) NOT NULL DEFAULT 'hello',
  "description" text DEFAULT '',
  "email" varchar(500),
  "explicitnulldef" varchar,
  "explicitemptystring" varchar DEFAULT '',
  -- Hello emptytagdef
  "emptytagdef" varchar DEFAULT '',
  "another_id" int(10) DEFAULT 2,
  "timest" timestamp,
  FOREIGN KEY ("another_id") REFERENCES "Another"("id")
)>,
          q<CREATE INDEX "titleindex" ON "Basic" ("title")>,
          q<CREATE UNIQUE INDEX "emailuniqueindex" ON "Basic" ("email")>,
          q<CREATE UNIQUE INDEX "very_long_index_name_on_title_field_which_should_be_truncated_for_various_rdbms" ON "Basic" ("title")>,
          q<DROP TABLE "Another">,
          q<CREATE TABLE "Another" (
  "id" INTEGER PRIMARY KEY NOT NULL,
  "num" numeric(10,2)
)>,
          q<DROP VIEW IF EXISTS "email_list">,
          q<CREATE VIEW "email_list" AS
    SELECT email FROM Basic WHERE (email IS NOT NULL)>,
          q<DROP TRIGGER IF EXISTS "foo_trigger">,
          q<CREATE TRIGGER "foo_trigger" after insert on "Basic" BEGIN update modified=timestamp(); END>,
          q<DROP TRIGGER IF EXISTS "bar_trigger_insert">,
          q<CREATE TRIGGER "bar_trigger_insert" before insert on "Basic" BEGIN update modified2=timestamp(); END>,
          q<DROP TRIGGER IF EXISTS "bar_trigger_update">,
          q<CREATE TRIGGER "bar_trigger_update" before update on "Basic" BEGIN update modified2=timestamp(); END>,
          'COMMIT',

          ], 'SQLite translate in list context matches');