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