1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
|
use strict;
use warnings;
use 5.010;
use Test::More;
use DBIx::Class::DeploymentHandler::DeployMethod::SQL::Translator;
sub make_dm {
my $storage_class = shift;
bless {
storage => bless({}, 'DBIx::Class::Storage::DBI::'.$storage_class),
@_,
}, 'DBIx::Class::DeploymentHandler::DeployMethod::SQL::Translator';
}
my $dm = make_dm('mysql');
is_deeply [ $dm->_split_sql_chunk( <<'END' ) ], [ 'BEGIN SELECT * FROM YADAH END' ];
BEGIN
-- stuff
SELECT * FROM YADAH
END;
END
is_deeply [ $dm->_split_sql_chunk( 'foo', ' ', 'bar' ) ], [qw(foo bar)];
$dm = make_dm('mysql', txn_prep => 1); # default, bw-comp.
is_deeply [ $dm->_split_sql_chunk( <<'END' ) ],
BEGIN;
-- stuff
DELIMITER $$
insert into door (color) VALUES ('#f00')$$
SELECT * FROM YADAH$$
DELIMITER ;
Commit;
END
[
q(insert into door (color) VALUES ('#f00')),
'SELECT * FROM YADAH',
];
$dm = make_dm('mysql', txn_prep => 0);
is_deeply [ $dm->_split_sql_chunk( <<'END' ) ],
BEGIN;
-- stuff
DELIMITER $$
insert into door (color) VALUES ('#000')$$
SELECT * FROM YADAH$$
DELIMITER ;
Commit;
END
[
'BEGIN',
q(insert into door (color) VALUES ('#000')),
'SELECT * FROM YADAH',
'Commit',
];
$dm = make_dm('mysql', txn_prep => 0);
is_deeply [ $dm->_split_sql_chunk( <<'END' ) ],
insert into door (color) VALUES ('#000');
CREATE TRIGGER upd_check BEFORE UPDATE ON account
FOR EACH ROW
BEGIN
IF NEW.amount < 0 THEN
SET NEW.amount = 0;
ELSEIF NEW.amount > 100 THEN
SET NEW.amount = 100;
END IF;
END;
SELECT * FROM YADAH;
END
[
q(insert into door (color) VALUES ('#000')),
'CREATE TRIGGER upd_check BEFORE UPDATE ON account FOR EACH ROW BEGIN IF NEW.amount < 0 THEN SET NEW.amount = 0; ELSEIF NEW.amount > 100 THEN SET NEW.amount = 100; END IF; END',
'SELECT * FROM YADAH',
];
$dm = make_dm('Pg');
is_deeply [ $dm->_split_sql_chunk( <<'END' ) ],
-- Add triggers to maintain sync between list_material_ratings table and list_materials table:;
CREATE FUNCTION add_rating() RETURNS trigger AS $add_rating$
BEGIN
IF NEW."type" = 'like' THEN
UPDATE "list_materials" SET "likes" = (SELECT COUNT(*) FROM "list_material_ratings" WHERE "list" = NEW."list" AND "material" = NEW."material" AND "type" = 'like') WHERE "list" = NEW."list" AND "material" = NEW."material";
END IF;
IF NEW."type" = 'dislike' THEN
UPDATE "list_materials" SET "dislikes" = (SELECT COUNT(*) FROM "list_material_ratings" WHERE "list" = NEW."list" AND "material" = NEW."material" AND "type" = 'dislike') WHERE "list" = NEW."list" AND "material" = NEW."material";
END IF;
RETURN NULL;
END;
$add_rating$ LANGUAGE plpgsql;
END
[ q{CREATE FUNCTION add_rating() RETURNS trigger AS $add_rating$ BEGIN IF NEW."type" = 'like' THEN UPDATE "list_materials" SET "likes" = (SELECT COUNT(*) FROM "list_material_ratings" WHERE "list" = NEW."list" AND "material" = NEW."material" AND "type" = 'like') WHERE "list" = NEW."list" AND "material" = NEW."material"; END IF; IF NEW."type" = 'dislike' THEN UPDATE "list_materials" SET "dislikes" = (SELECT COUNT(*) FROM "list_material_ratings" WHERE "list" = NEW."list" AND "material" = NEW."material" AND "type" = 'dislike') WHERE "list" = NEW."list" AND "material" = NEW."material"; END IF; RETURN NULL; END; $add_rating$ LANGUAGE plpgsql} ];
$dm = make_dm('Pg');
is_deeply [ $dm->_split_sql_chunk( <<'END' ) ],
CREATE TABLE "dbix_class_deploymenthandler_versions" ( "id" serial NOT NULL, "version" character varying(50) NOT NULL, "ddl" text, "upgrade_sql" text, PRIMARY KEY ("id"), CONSTRAINT "dbix_class_deploymenthandler_versions_version" UNIQUE ("version") )
END
[ q{CREATE TABLE "dbix_class_deploymenthandler_versions" ( "id" serial NOT NULL, "version" character varying(50) NOT NULL, "ddl" text, "upgrade_sql" text, PRIMARY KEY ("id"), CONSTRAINT "dbix_class_deploymenthandler_versions_version" UNIQUE ("version") )} ];
done_testing;
|
