[dbsrgits/SQL-Translator.git] / t / 23json.t

use warnings;
use strict;
use Test::More;
use Test::Differences;
use Test::SQL::Translator qw(maybe_plan);
use SQL::Translator;
use FindBin '$Bin';

BEGIN {
    maybe_plan(
        2,
        'SQL::Translator::Parser::SQLite',
        'SQL::Translator::Producer::JSON',
    );
}

my $sqlt_version = $SQL::Translator::VERSION;
use JSON;
my $json = to_json(from_json(<<JSON), { canonical => 1, pretty => 1 });
{
   "schema" : {
      "procedures" : {},
      "tables" : {
         "person" : {
            "constraints" : [
               {
                  "deferrable" : 1,
                  "expression" : "",
                  "fields" : [
                     "person_id"
                  ],
                  "match_type" : "",
                  "name" : "",
                  "on_delete" : "",
                  "on_update" : "",
                  "options" : [],
                  "reference_fields" : [],
                  "reference_table" : "",
                  "type" : "PRIMARY KEY"
               },
               {
                  "deferrable" : 1,
                  "expression" : "",
                  "fields" : [
                     "name"
                  ],
                  "match_type" : "",
                  "name" : "u_name",
                  "on_delete" : "",
                  "on_update" : "",
                  "options" : [],
                  "reference_fields" : [],
                  "reference_table" : "",
                  "type" : "UNIQUE"
               }
            ],
            "fields" : {
               "age" : {
                  "data_type" : "integer",
                  "default_value" : null,
                  "is_nullable" : 1,
                  "is_primary_key" : 0,
                  "is_unique" : 0,
                  "name" : "age",
                  "order" : "3",
                  "size" : [
                     "0"
                  ]
               },
               "description" : {
                  "data_type" : "text",
                  "default_value" : null,
                  "is_nullable" : 1,
                  "is_primary_key" : 0,
                  "is_unique" : 0,
                  "name" : "description",
                  "order" : "6",
                  "size" : [
                     "0"
                  ]
               },
               "iq" : {
                  "data_type" : "tinyint",
                  "default_value" : "0",
                  "is_nullable" : 1,
                  "is_primary_key" : 0,
                  "is_unique" : 0,
                  "name" : "iq",
                  "order" : "5",
                  "size" : [
                     "0"
                  ]
               },
               "name" : {
                  "data_type" : "varchar",
                  "default_value" : null,
                  "is_nullable" : 0,
                  "is_primary_key" : 0,
                  "is_unique" : 1,
                  "name" : "name",
                  "order" : "2",
                  "size" : [
                     "20"
                  ]
               },
               "person_id" : {
                  "data_type" : "INTEGER",
                  "default_value" : null,
                  "is_auto_increment" : 1,
                  "is_nullable" : 0,
                  "is_primary_key" : 1,
                  "is_unique" : 0,
                  "name" : "person_id",
                  "order" : "1",
                  "size" : [
                     "0"
                  ]
               },
               "weight" : {
                  "data_type" : "double",
                  "default_value" : null,
                  "is_nullable" : 1,
                  "is_primary_key" : 0,
                  "is_unique" : 0,
                  "name" : "weight",
                  "order" : "4",
                  "size" : [
                     "11",
                     "2"
                  ]
               }
            },
            "indices" : [],
            "name" : "person",
            "options" : [],
            "order" : "1"
         },
         "pet" : {
            "constraints" : [
               {
                  "deferrable" : 1,
                  "expression" : "",
                  "fields" : [],
                  "match_type" : "",
                  "name" : "",
                  "on_delete" : "",
                  "on_update" : "",
                  "options" : [],
                  "reference_fields" : [],
                  "reference_table" : "",
                  "type" : "CHECK"
               },
               {
                  "deferrable" : 1,
                  "expression" : "",
                  "fields" : [
                     "pet_id",
                     "person_id"
                  ],
                  "match_type" : "",
                  "name" : "",
                  "on_delete" : "",
                  "on_update" : "",
                  "options" : [],
                  "reference_fields" : [],
                  "reference_table" : "",
                  "type" : "PRIMARY KEY"
               },
               {
                  "deferrable" : 1,
                  "expression" : "",
                  "fields" : [
                     "person_id"
                  ],
                  "match_type" : "",
                  "name" : "",
                  "on_delete" : "",
                  "on_update" : "",
                  "options" : [],
                  "reference_fields" : [
                     "person_id"
                  ],
                  "reference_table" : "person",
                  "type" : "FOREIGN KEY"
               }
            ],
            "fields" : {
               "age" : {
                  "data_type" : "int",
                  "default_value" : null,
                  "is_nullable" : 1,
                  "is_primary_key" : 0,
                  "is_unique" : 0,
                  "name" : "age",
                  "order" : "4",
                  "size" : [
                     "0"
                  ]
               },
               "name" : {
                  "data_type" : "varchar",
                  "default_value" : null,
                  "is_nullable" : 1,
                  "is_primary_key" : 0,
                  "is_unique" : 0,
                  "name" : "name",
                  "order" : "3",
                  "size" : [
                     "30"
                  ]
               },
               "person_id" : {
                  "data_type" : "int",
                  "default_value" : null,
                  "is_nullable" : 0,
                  "is_primary_key" : 1,
                  "is_unique" : 0,
                  "name" : "person_id",
                  "order" : "2",
                  "size" : [
                     "0"
                  ]
               },
               "pet_id" : {
                  "data_type" : "int",
                  "default_value" : null,
                  "is_nullable" : 0,
                  "is_primary_key" : 1,
                  "is_unique" : 0,
                  "name" : "pet_id",
                  "order" : "1",
                  "size" : [
                     "0"
                  ]
               }
            },
            "indices" : [],
            "name" : "pet",
            "options" : [],
            "order" : "2"
         }
      },
      "triggers" : {
         "pet_trig" : {
            "action" : {
               "for_each" : null,
               "steps" : [
                  "update pet set name=name"
               ],
               "when" : null
            },
            "database_events" : [
               "insert"
            ],
            "fields" : null,
            "name" : "pet_trig",
            "on_table" : "pet",
            "order" : "1",
            "perform_action_when" : "after"
         }
      },
      "views" : {
         "person_pet" : {
            "fields" : [],
            "name" : "person_pet",
            "order" : "1",
            "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"
         }
      }
   },
   "translator" : {
      "add_drop_table" : 0,
      "filename" : null,
      "no_comments" : 0,
      "parser_args" : {},
      "parser_type" : "SQL::Translator::Parser::SQLite",
      "producer_args" : {
         "canonical" : 1,
         "pretty" : 1
      },
      "producer_type" : "SQL::Translator::Producer::JSON",
      "show_warnings" : 0,
      "trace" : 0,
      "version" : "$sqlt_version"
   }
}
JSON

my $file = "$Bin/data/sqlite/create.sql";
open my $fh, '<', $file or die "Can't read '$file': $!\n";
local $/;
my $data = <$fh>;
my $tr = SQL::Translator->new(
    parser => 'SQLite',
    producer => 'JSON',
    producer_args => {
        canonical => 1,
        pretty => 1,
    },
    data => $data,
);

my $out;
ok( $out = $tr->translate, 'Translate SQLite to JSON' );
eq_or_diff( $out, $json, 'JSON matches expected' );
Commit	Line	Data
edc7ae17	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;
4f2693e3	18	use JSON;
4f2693e3	19	my $json = to_json(from_json(<<JSON), { canonical => 1, pretty => 1 });
edc7ae17	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' );