t/23json.t

   1 use warnings;
   2 use strict;
   3 use Test::More;
   4 use Test::Differences;
   5 use Test::SQL::Translator qw(maybe_plan);
   6 use SQL::Translator;
   7 use FindBin '$Bin';
   8
   9 BEGIN {
  10     maybe_plan(
  11         2,
  12         'SQL::Translator::Parser::SQLite',
  13         'SQL::Translator::Producer::JSON',
  14     );
  15 }
  16
  17 my $sqlt_version = $SQL::Translator::VERSION;
  18 my $json = <<JSON;
  19 {
  20    "schema" : {
  21       "procedures" : {},
  22       "tables" : {
  23          "person" : {
  24             "constraints" : [
  25                {
  26                   "deferrable" : 1,
  27                   "expression" : "",
  28                   "fields" : [
  29                      "person_id"
  30                   ],
  31                   "match_type" : "",
  32                   "name" : "",
  33                   "on_delete" : "",
  34                   "on_update" : "",
  35                   "options" : [],
  36                   "reference_fields" : [],
  37                   "reference_table" : "",
  38                   "type" : "PRIMARY KEY"
  39                },
  40                {
  41                   "deferrable" : 1,
  42                   "expression" : "",
  43                   "fields" : [
  44                      "name"
  45                   ],
  46                   "match_type" : "",
  47                   "name" : "u_name",
  48                   "on_delete" : "",
  49                   "on_update" : "",
  50                   "options" : [],
  51                   "reference_fields" : [],
  52                   "reference_table" : "",
  53                   "type" : "UNIQUE"
  54                }
  55             ],
  56             "fields" : {
  57                "age" : {
  58                   "data_type" : "integer",
  59                   "default_value" : null,
  60                   "is_nullable" : 1,
  61                   "is_primary_key" : 0,
  62                   "is_unique" : 0,
  63                   "name" : "age",
  64                   "order" : "3",
  65                   "size" : [
  66                      "0"
  67                   ]
  68                },
  69                "description" : {
  70                   "data_type" : "text",
  71                   "default_value" : null,
  72                   "is_nullable" : 1,
  73                   "is_primary_key" : 0,
  74                   "is_unique" : 0,
  75                   "name" : "description",
  76                   "order" : "6",
  77                   "size" : [
  78                      "0"
  79                   ]
  80                },
  81                "iq" : {
  82                   "data_type" : "tinyint",
  83                   "default_value" : "0",
  84                   "is_nullable" : 1,
  85                   "is_primary_key" : 0,
  86                   "is_unique" : 0,
  87                   "name" : "iq",
  88                   "order" : "5",
  89                   "size" : [
  90                      "0"
  91                   ]
  92                },
  93                "name" : {
  94                   "data_type" : "varchar",
  95                   "default_value" : null,
  96                   "is_nullable" : 0,
  97                   "is_primary_key" : 0,
  98                   "is_unique" : 1,
  99                   "name" : "name",
 100                   "order" : "2",
 101                   "size" : [
 102                      "20"
 103                   ]
 104                },
 105                "person_id" : {
 106                   "data_type" : "INTEGER",
 107                   "default_value" : null,
 108                   "is_auto_increment" : 1,
 109                   "is_nullable" : 0,
 110                   "is_primary_key" : 1,
 111                   "is_unique" : 0,
 112                   "name" : "person_id",
 113                   "order" : "1",
 114                   "size" : [
 115                      "0"
 116                   ]
 117                },
 118                "weight" : {
 119                   "data_type" : "double",
 120                   "default_value" : null,
 121                   "is_nullable" : 1,
 122                   "is_primary_key" : 0,
 123                   "is_unique" : 0,
 124                   "name" : "weight",
 125                   "order" : "4",
 126                   "size" : [
 127                      "11",
 128                      "2"
 129                   ]
 130                }
 131             },
 132             "indices" : [],
 133             "name" : "person",
 134             "options" : [],
 135             "order" : "1"
 136          },
 137          "pet" : {
 138             "constraints" : [
 139                {
 140                   "deferrable" : 1,
 141                   "expression" : "",
 142                   "fields" : [],
 143                   "match_type" : "",
 144                   "name" : "",
 145                   "on_delete" : "",
 146                   "on_update" : "",
 147                   "options" : [],
 148                   "reference_fields" : [],
 149                   "reference_table" : "",
 150                   "type" : "CHECK"
 151                },
 152                {
 153                   "deferrable" : 1,
 154                   "expression" : "",
 155                   "fields" : [
 156                      "pet_id",
 157                      "person_id"
 158                   ],
 159                   "match_type" : "",
 160                   "name" : "",
 161                   "on_delete" : "",
 162                   "on_update" : "",
 163                   "options" : [],
 164                   "reference_fields" : [],
 165                   "reference_table" : "",
 166                   "type" : "PRIMARY KEY"
 167                },
 168                {
 169                   "deferrable" : 1,
 170                   "expression" : "",
 171                   "fields" : [
 172                      "person_id"
 173                   ],
 174                   "match_type" : "",
 175                   "name" : "",
 176                   "on_delete" : "",
 177                   "on_update" : "",
 178                   "options" : [],
 179                   "reference_fields" : [
 180                      "person_id"
 181                   ],
 182                   "reference_table" : "person",
 183                   "type" : "FOREIGN KEY"
 184                }
 185             ],
 186             "fields" : {
 187                "age" : {
 188                   "data_type" : "int",
 189                   "default_value" : null,
 190                   "is_nullable" : 1,
 191                   "is_primary_key" : 0,
 192                   "is_unique" : 0,
 193                   "name" : "age",
 194                   "order" : "4",
 195                   "size" : [
 196                      "0"
 197                   ]
 198                },
 199                "name" : {
 200                   "data_type" : "varchar",
 201                   "default_value" : null,
 202                   "is_nullable" : 1,
 203                   "is_primary_key" : 0,
 204                   "is_unique" : 0,
 205                   "name" : "name",
 206                   "order" : "3",
 207                   "size" : [
 208                      "30"
 209                   ]
 210                },
 211                "person_id" : {
 212                   "data_type" : "int",
 213                   "default_value" : null,
 214                   "is_nullable" : 0,
 215                   "is_primary_key" : 1,
 216                   "is_unique" : 0,
 217                   "name" : "person_id",
 218                   "order" : "2",
 219                   "size" : [
 220                      "0"
 221                   ]
 222                },
 223                "pet_id" : {
 224                   "data_type" : "int",
 225                   "default_value" : null,
 226                   "is_nullable" : 0,
 227                   "is_primary_key" : 1,
 228                   "is_unique" : 0,
 229                   "name" : "pet_id",
 230                   "order" : "1",
 231                   "size" : [
 232                      "0"
 233                   ]
 234                }
 235             },
 236             "indices" : [],
 237             "name" : "pet",
 238             "options" : [],
 239             "order" : "2"
 240          }
 241       },
 242       "triggers" : {
 243          "pet_trig" : {
 244             "action" : {
 245                "for_each" : null,
 246                "steps" : [
 247                   "update pet set name=name"
 248                ],
 249                "when" : null
 250             },
 251             "database_events" : [
 252                "insert"
 253             ],
 254             "fields" : null,
 255             "name" : "pet_trig",
 256             "on_table" : "pet",
 257             "order" : "1",
 258             "perform_action_when" : "after"
 259          }
 260       },
 261       "views" : {
 262          "person_pet" : {
 263             "fields" : [],
 264             "name" : "person_pet",
 265             "order" : "1",
 266             "sql" : "select pr.person_id, pr.name as person_name, pt.name as pet_name\\n  from   person pr, pet pt\\n  where  person.person_id=pet.pet_id\\n"
 267          }
 268       }
 269    },
 270    "translator" : {
 271       "add_drop_table" : 0,
 272       "filename" : null,
 273       "no_comments" : 0,
 274       "parser_args" : {},
 275       "parser_type" : "SQL::Translator::Parser::SQLite",
 276       "producer_args" : {
 277          "canonical" : 1,
 278          "pretty" : 1
 279       },
 280       "producer_type" : "SQL::Translator::Producer::JSON",
 281       "show_warnings" : 0,
 282       "trace" : 0,
 283       "version" : "$sqlt_version"
 284    }
 285 }
 286 JSON
 287
 288 my $file = "$Bin/data/sqlite/create.sql";
 289 open my $fh, '<', $file or die "Can't read '$file': $!\n";
 290 local $/;
 291 my $data = <$fh>;
 292 my $tr = SQL::Translator->new(
 293     parser => 'SQLite',
 294     producer => 'JSON',
 295     producer_args => {
 296         canonical => 1,
 297         pretty => 1,
 298     },
 299     data => $data,
 300 );
 301
 302 my $out;
 303 ok( $out = $tr->translate, 'Translate SQLite to JSON' );
 304 eq_or_diff( $out, $json, 'JSON matches expected' );