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                   "comments" : [
 108                     "field comment 1",
 109                     "field comment 2"
 110                   ],
 111                   "data_type" : "INTEGER",
 112                   "default_value" : null,
 113                   "is_auto_increment" : 1,
 114                   "is_nullable" : 0,
 115                   "is_primary_key" : 1,
 116                   "is_unique" : 0,
 117                   "name" : "person_id",
 118                   "order" : "1",
 119                   "size" : [
 120                      "0"
 121                   ]
 122                },
 123                "weight" : {
 124                   "data_type" : "double",
 125                   "default_value" : null,
 126                   "is_nullable" : 1,
 127                   "is_primary_key" : 0,
 128                   "is_unique" : 0,
 129                   "name" : "weight",
 130                   "order" : "4",
 131                   "size" : [
 132                      "11",
 133                      "2"
 134                   ]
 135                }
 136             },
 137             "indices" : [],
 138             "name" : "person",
 139             "options" : [],
 140             "order" : "1"
 141          },
 142          "pet" : {
 143             "constraints" : [
 144                {
 145                   "deferrable" : 1,
 146                   "expression" : "",
 147                   "fields" : [],
 148                   "match_type" : "",
 149                   "name" : "",
 150                   "on_delete" : "",
 151                   "on_update" : "",
 152                   "options" : [],
 153                   "reference_fields" : [],
 154                   "reference_table" : "",
 155                   "type" : "CHECK"
 156                },
 157                {
 158                   "deferrable" : 1,
 159                   "expression" : "",
 160                   "fields" : [
 161                      "pet_id",
 162                      "person_id"
 163                   ],
 164                   "match_type" : "",
 165                   "name" : "",
 166                   "on_delete" : "",
 167                   "on_update" : "",
 168                   "options" : [],
 169                   "reference_fields" : [],
 170                   "reference_table" : "",
 171                   "type" : "PRIMARY KEY"
 172                },
 173                {
 174                   "deferrable" : 1,
 175                   "expression" : "",
 176                   "fields" : [
 177                      "person_id"
 178                   ],
 179                   "match_type" : "",
 180                   "name" : "",
 181                   "on_delete" : "",
 182                   "on_update" : "",
 183                   "options" : [],
 184                   "reference_fields" : [
 185                      "person_id"
 186                   ],
 187                   "reference_table" : "person",
 188                   "type" : "FOREIGN KEY"
 189                }
 190             ],
 191             "fields" : {
 192                "age" : {
 193                   "data_type" : "int",
 194                   "default_value" : null,
 195                   "is_nullable" : 1,
 196                   "is_primary_key" : 0,
 197                   "is_unique" : 0,
 198                   "name" : "age",
 199                   "order" : "4",
 200                   "size" : [
 201                      "0"
 202                   ]
 203                },
 204                "name" : {
 205                   "data_type" : "varchar",
 206                   "default_value" : null,
 207                   "is_nullable" : 1,
 208                   "is_primary_key" : 0,
 209                   "is_unique" : 0,
 210                   "name" : "name",
 211                   "order" : "3",
 212                   "size" : [
 213                      "30"
 214                   ]
 215                },
 216                "person_id" : {
 217                   "data_type" : "int",
 218                   "default_value" : null,
 219                   "is_nullable" : 0,
 220                   "is_primary_key" : 1,
 221                   "is_unique" : 0,
 222                   "name" : "person_id",
 223                   "order" : "2",
 224                   "size" : [
 225                      "0"
 226                   ]
 227                },
 228                "pet_id" : {
 229                   "data_type" : "int",
 230                   "default_value" : null,
 231                   "is_nullable" : 0,
 232                   "is_primary_key" : 1,
 233                   "is_unique" : 0,
 234                   "name" : "pet_id",
 235                   "order" : "1",
 236                   "size" : [
 237                      "0"
 238                   ]
 239                }
 240             },
 241             "indices" : [],
 242             "name" : "pet",
 243             "options" : [],
 244             "order" : "2"
 245          }
 246       },
 247       "triggers" : {
 248          "pet_trig" : {
 249             "action" : {
 250                "for_each" : null,
 251                "steps" : [
 252                   "update pet set name=name"
 253                ],
 254                "when" : null
 255             },
 256             "database_events" : [
 257                "insert"
 258             ],
 259             "fields" : null,
 260             "name" : "pet_trig",
 261             "on_table" : "pet",
 262             "order" : "1",
 263             "perform_action_when" : "after",
 264             "scope": "row"
 265          }
 266       },
 267       "views" : {
 268          "person_pet" : {
 269             "fields" : [],
 270             "name" : "person_pet",
 271             "order" : "1",
 272             "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"
 273          }
 274       }
 275    },
 276    "translator" : {
 277       "add_drop_table" : 0,
 278       "filename" : null,
 279       "no_comments" : 0,
 280       "parser_args" : {},
 281       "parser_type" : "SQL::Translator::Parser::SQLite",
 282       "producer_args" : {
 283          "canonical" : 1,
 284          "pretty" : 1
 285       },
 286       "producer_type" : "SQL::Translator::Producer::JSON",
 287       "show_warnings" : 0,
 288       "trace" : 0,
 289       "version" : "$sqlt_version"
 290    }
 291 }
 292 JSON
 293
 294 my $file = "$Bin/data/sqlite/create.sql";
 295 open my $fh, '<', $file or die "Can't read '$file': $!\n";
 296 local $/;
 297 my $data = <$fh>;
 298 my $tr = SQL::Translator->new(
 299     parser => 'SQLite',
 300     producer => 'JSON',
 301     producer_args => {
 302         canonical => 1,
 303         pretty => 1,
 304     },
 305     data => $data,
 306 );
 307
 308 my $out;
 309 ok( $out = $tr->translate, 'Translate SQLite to JSON' );
 310 eq_or_diff( $out, $json, 'JSON matches expected' );