From: Jess Robinson <castaway@desert-island.me.uk>
Date: Sun, 23 Jul 2006 13:57:42 +0000 (+0000)
Subject: Lots of Pg Producer tests, some other fixes
X-Git-Tag: v0.11008~418
X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits%2FSQL-Translator.git;a=commitdiff_plain;h=8c4efd11b1888e7b99cdc302dbc66ace5037453d

Lots of Pg Producer tests, some other fixes
---

diff --git a/t/34tt-base.t b/t/34tt-base.t
index 7170260..d104dbe 100644
--- a/t/34tt-base.t
+++ b/t/34tt-base.t
@@ -10,7 +10,9 @@ use Test::Exception;
 use Test::SQL::Translator qw(maybe_plan);
 
 BEGIN {
-    maybe_plan(4, 'Template', 'Test::Differences')
+    maybe_plan(4, 'Template', 
+               'Test::Differences', 
+               'SQL::Translator::Parser::XML::SQLFairy')
 }
 use Test::Differences;
 
diff --git a/t/38-mysql-producer.t b/t/38-mysql-producer.t
index f288fc4..74fdaee 100644
--- a/t/38-mysql-producer.t
+++ b/t/38-mysql-producer.t
@@ -37,11 +37,16 @@ schema:
     thing:
       name: thing
       extra:
-        mysql_table_type: InnoDB
         mysql_charset: latin1 
         mysql_collate: latin1_danish_ci 
       order: 1
       fields:
+        id:
+          name: id
+          data_type: unsigned int
+          is_primary_key: 1
+          is_auto_increment: 1
+          order: 0
         name:
           name: name
           data_type: varchar
@@ -62,23 +67,64 @@ schema:
             mysql_charset: utf8
             mysql_collate: utf8_general_ci
           order: 3
+    thing2:
+      name: thing2
+      extra:
+      order: 2
+      fields:
+        id:
+          name: id
+          data_type: int
+          is_primary_key: 0
+          order: 0
+          is_foreign_key: 1
+        foo:
+          name: foo
+          data_type: int
+          order: 1
+          is_not_null: 1
+      constraints:
+        - type: PRIMARY_KEY
+          fields:
+            - id
+            - foo
+        - reference_table: thing
+          type: FOREIGN_KEY
+          fields: foo
+          name: fk_thing
+
 EOSCHEMA
 
 my $mysql_out = <<EOSQL;
 SET foreign_key_checks=0;
 
 CREATE TABLE thing (
+  id unsigned int auto_increment,
   name varchar(32),
   swedish_name varchar(32) CHARACTER SET swe7,
-  description text CHARACTER SET utf8 COLLATE utf8_general_ci
+  description text CHARACTER SET utf8 COLLATE utf8_general_ci,
+  INDEX (id),
+  PRIMARY KEY (id)
 ) Type=InnoDB DEFAULT CHARACTER SET latin1 COLLATE latin1_danish_ci;
 
+CREATE TABLE thing2 (
+  id integer,
+  foo integer,
+  INDEX (id),
+  INDEX (foo),
+  PRIMARY KEY (id, foo),
+  CONSTRAINT thing2_fk_thing FOREIGN KEY (foo) REFERENCES thing (id)
+) Type=InnoDB;
+
+SET foreign_key_checks=1;
+
 EOSQL
 
     my $sqlt;
     $sqlt = SQL::Translator->new(
         show_warnings  => 1,
         no_comments    => 1,
+#        debug          => 1,
         from           => "YAML",
         to             => "MySQL",
     );
diff --git a/t/46xml-to-pg.t b/t/46xml-to-pg.t
new file mode 100644
index 0000000..0104bfe
--- /dev/null
+++ b/t/46xml-to-pg.t
@@ -0,0 +1,50 @@
+#!/usr/bin/perl
+use strict;
+
+use FindBin qw/$Bin/;
+use Test::More;
+use Test::SQL::Translator;
+use Test::Exception;
+use Data::Dumper;
+use SQL::Translator;
+use SQL::Translator::Schema::Constants;
+
+
+BEGIN {
+    maybe_plan(1, 'SQL::Translator::Parser::XML::SQLFairy',
+              'SQL::Translator::Producer::PostgreSQL');
+}
+
+my $xmlfile = "$Bin/data/xml/schema.xml";
+
+my $sqlt;
+$sqlt = SQL::Translator->new(
+    no_comments => 1,
+    show_warnings  => 1,
+    add_drop_table => 1,
+);
+
+die "Can't find test schema $xmlfile" unless -e $xmlfile;
+
+my $sql = $sqlt->translate(
+    from     => 'XML-SQLFairy',
+    to       => 'PostgreSQL',
+    filename => $xmlfile,
+) or die $sqlt->error;
+
+is($sql, << "SQL");
+DROP TABLE "Basic";
+CREATE TABLE "Basic" (
+  "id" serial NOT NULL,
+  "title" character varying(100) DEFAULT 'hello' NOT NULL,
+  "description" text DEFAULT '',
+  "email" character varying(255),
+  "explicitnulldef" character varying,
+  "explicitemptystring" character varying DEFAULT '',
+  -- Hello emptytagdef
+  "emptytagdef" character varying DEFAULT '',
+  PRIMARY KEY ("id"),
+  Constraint "emailuniqueindex" UNIQUE ("email")
+);
+CREATE INDEX "titleindex" on "Basic" ("title");
+SQL
diff --git a/t/47postgres-producer.t b/t/47postgres-producer.t
new file mode 100644
index 0000000..c96b5e2
--- /dev/null
+++ b/t/47postgres-producer.t
@@ -0,0 +1,67 @@
+#!/usr/bin/perl
+
+use strict;
+use warnings;
+
+use Test::More;
+use Test::Exception;
+use Test::SQL::Translator qw(maybe_plan);
+
+use Data::Dumper;
+use FindBin qw/$Bin/;
+
+# Testing 1,2,3,4...
+#=============================================================================
+
+BEGIN {
+    maybe_plan(4,
+        'SQL::Translator::Producer::PostgreSQL',
+        'Test::Differences',
+    )
+}
+use Test::Differences;
+use SQL::Translator;
+
+
+
+my $table = SQL::Translator::Schema::Table->new( name => 'mytable');
+
+my $field1 = SQL::Translator::Schema::Field->new( name => 'myfield',
+                                                  table => $table,
+                                                  data_type => 'VARCHAR',
+                                                  size => 10,
+                                                  default_value => undef,
+                                                  is_auto_increment => 0,
+                                                  is_nullable => 1,
+                                                  is_foreign_key => 0,
+                                                  is_unique => 0 );
+
+my $field1_sql = SQL::Translator::Producer::PostgreSQL::create_field($field1);
+
+is($field1_sql, 'myfield character varying(10)', 'Create field works');
+
+my $field2 = SQL::Translator::Schema::Field->new( name      => 'myfield',
+                                                  table => $table,
+                                                  data_type => 'VARCHAR',
+                                                  size      => 25,
+                                                  default_value => undef,
+                                                  is_auto_increment => 0,
+                                                  is_nullable => 0,
+                                                  is_foreign_key => 0,
+                                                  is_unique => 0 );
+
+my $alter_field = SQL::Translator::Producer::PostgreSQL::alter_field($field1,
+                                                                $field2);
+is($alter_field, qq[ALTER TABLE mytable ALTER COLUMN myfield SET NOT NULL;
+ALTER TABLE mytable ALTER COLUMN myfield TYPE character varying(25);],
+ 'Alter field works');
+
+$field1->name('field3');
+my $add_field = SQL::Translator::Producer::PostgreSQL::add_field($field1);
+
+is($add_field, 'ALTER TABLE mytable ADD COLUMN field3 character varying(10);', 'Add field works');
+
+my $drop_field = SQL::Translator::Producer::PostgreSQL::drop_field($field2);
+is($drop_field, 'ALTER TABLE mytable DROP COLUMN myfield;', 'Drop field works');
+
+
diff --git a/t/48xml-to-sqlite.t b/t/48xml-to-sqlite.t
new file mode 100644
index 0000000..bac7be5
--- /dev/null
+++ b/t/48xml-to-sqlite.t
@@ -0,0 +1,60 @@
+#!/usr/bin/perl
+use strict;
+
+use FindBin qw/$Bin/;
+use Test::More;
+use Test::SQL::Translator;
+use Test::Exception;
+use Data::Dumper;
+use SQL::Translator;
+use SQL::Translator::Schema::Constants;
+
+
+BEGIN {
+    maybe_plan(1, 'SQL::Translator::Parser::XML::SQLFairy',
+              'SQL::Translator::Producer::SQLite');
+}
+
+my $xmlfile = "$Bin/data/xml/schema.xml";
+
+my $sqlt;
+$sqlt = SQL::Translator->new(
+    no_comments => 1,
+    show_warnings  => 1,
+    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;
+
+# print ">>$sql<<\n";
+
+is($sql, << "SQL");
+BEGIN TRANSACTION;
+
+
+--
+-- Table: Basic
+--
+DROP TABLE Basic;
+CREATE TABLE Basic (
+  id INTEGER PRIMARY KEY NOT NULL,
+  title varchar(100) NOT NULL DEFAULT 'hello',
+  description text DEFAULT '',
+  email varchar(255),
+  explicitnulldef varchar,
+  explicitemptystring varchar DEFAULT '',
+  -- Hello emptytagdef
+  emptytagdef varchar DEFAULT ''
+);
+
+CREATE INDEX titleindex_Basic on Basic (title);
+CREATE UNIQUE INDEX emailuniqueindex_Basic on Basic (email);
+
+COMMIT;
+SQL
diff --git a/t/49xml-to-pg-samefield.t b/t/49xml-to-pg-samefield.t
new file mode 100644
index 0000000..4fb029d
--- /dev/null
+++ b/t/49xml-to-pg-samefield.t
@@ -0,0 +1,56 @@
+#!/usr/bin/perl
+use strict;
+
+use FindBin qw/$Bin/;
+use Test::More;
+use Test::SQL::Translator;
+use Test::Exception;
+use Data::Dumper;
+use SQL::Translator;
+use SQL::Translator::Schema::Constants;
+
+
+BEGIN {
+    maybe_plan(1, 'SQL::Translator::Parser::XML::SQLFairy',
+              'SQL::Translator::Producer::PostgreSQL');
+}
+
+my $xmlfile = "$Bin/data/xml/samefield.xml";
+
+my $sqlt;
+$sqlt = SQL::Translator->new(
+    no_comments => 1,
+    show_warnings  => 1,
+    add_drop_table => 1,
+);
+
+die "Can't find test schema $xmlfile" unless -e $xmlfile;
+
+my $sql = $sqlt->translate(
+    from     => 'XML-SQLFairy',
+    to       => 'PostgreSQL',
+    filename => $xmlfile,
+) or die $sqlt->error;
+
+is($sql, << "SQL");
+DROP TABLE "one";
+CREATE TABLE "one" (
+  "same" character varying(100) DEFAULT 'hello' NOT NULL
+);
+
+
+
+DROP TABLE "two";
+CREATE TABLE "two" (
+  "same" character varying(100) DEFAULT 'hello' NOT NULL
+);
+
+SQL
+
+### This doesnt work, cant add a field with a name thats already there, so how do we test dupe field names?!
+
+# my $table = $sqlt->schema->get_table('two');
+# $table->add_field(name => 'same');
+# print Dumper($table);
+# $sql = SQL::Translator::Producer::PostgreSQL::produce($sqlt);
+# print ">>$sql<<\n";
diff --git a/t/data/xml/samefield.xml b/t/data/xml/samefield.xml
new file mode 100644
index 0000000..3d75202
--- /dev/null
+++ b/t/data/xml/samefield.xml
@@ -0,0 +1,31 @@
+<?xml version="1.0" encoding="utf-8"?>
+<!-- 
+Created by SQL::Translator::Producer::SqlfXML
+Created on Fri Aug 15 15:08:18 2003
+
+ -->
+<schema xmlns="http://sqlfairy.sourceforge.net/sqlfairy.xml">
+    
+  <tables>
+      <table order="1" name="one">
+        <fields>
+          <field
+              name="same"
+              is_primary_key="0" is_foreign_key="0"
+              size="100" is_auto_increment="0" data_type="varchar"
+              order="1" default_value="hello" is_nullable="0" />
+        </fields>
+      </table>
+      <table order="2" name="two">
+        <fields>
+          <field
+              name="same"
+              is_primary_key="0" is_foreign_key="0"
+              size="100" is_auto_increment="0" data_type="varchar"
+              order="1" default_value="hello" is_nullable="0" />
+        </fields>
+      </table>
+  </tables>
+
+
+</schema>