From Code to Community: Sponsoring The Perl and Raku Conference 2025 Learn more

VEESH / SQL-Translator-1.66 / t / 48xml-to-sqlite.t 

            

              
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133

              
#!/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'
);