MSSQL/DDL.pm

filter: Statementstmt 97.8% 404 / 413 Branchbran 90.9% 251 / 276 Conditioncond 75.7% 144 / 190 MC/DCmcdc 57.8% 63 / 109 Subroutinesub 97.0% 33 / 34 Podpod 100% 1 / 1 totaltotal 87.5% 896 / 1023 CC 235 SCAR 33.5
CC
235
Cov
91%
install_ddl
63.1
_schemas_of
42.0
_mssql_column_type
38.6
CRAP 28.5
« MSSQL/Introspect/Columns.pm ↑ Summary MSSQL/Storage.pm »
1 package DBIO::MSSQL::DDL;
2 # ABSTRACT: Generate MSSQL DDL from DBIO Result classes
3  
4 66 ▶use strict;
Subroutine BEGIN: called CC=1 SCAR=0.0
5 66 ▶use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
6  
7 66 our $VERSION = '0.900007';
8  
9 66 ▶use Exporter 'import';
Subroutine BEGIN: called CC=1 SCAR=0.0
10 66 our @EXPORT_OK = qw(_mssql_column_type _quote_ident _quote_table _bare_table _schema_prefix _schemas_of _check_table_name _constraint_name _check_cascade_paths _check_constraint_names);
11  
12 66 ▶use Digest::SHA ();
Subroutine BEGIN: called CC=1 SCAR=0.0
13  
14 66 ▶use DBIO::Exception ();
Subroutine BEGIN: called CC=1 SCAR=0.0
15 66 ▶use DBIO::Schema::Type ();
Subroutine BEGIN: called CC=1 SCAR=0.0
16 66 ▶use DBIO::MSSQL::Adapter ();
Subroutine BEGIN: called CC=1 SCAR=0.0
17  
18 66 my $ADAPTER = DBIO::MSSQL::Adapter->new;
19  
20 # The reserved keywords of Transact-SQL (Microsoft's list). A name on it is
21 # bracketed, and so is a name that is not of the plain form. A capital needs
22 # no bracket: SQL Server keeps the case of a bare name exactly as written.
23 66 my %RESERVED = map { $_ => 1 } qw(
24 ADD ALL ALTER AND ANY AS ASC AUTHORIZATION BACKUP BEGIN BETWEEN BREAK
25 BROWSE BULK BY CASCADE CASE CHECK CHECKPOINT CLOSE CLUSTERED COALESCE
26 COLLATE COLUMN COMMIT COMPUTE CONSTRAINT CONTAINS CONTAINSTABLE
27 CONTINUE CONVERT CREATE CROSS CURRENT CURRENT_DATE CURRENT_TIME
28 CURRENT_TIMESTAMP CURRENT_USER CURSOR DATABASE DBCC DEALLOCATE DECLARE
29 DEFAULT DELETE DENY DESC DISK DISTINCT DISTRIBUTED DOUBLE DROP DUMP
30 ELSE END ERRLVL ESCAPE EXCEPT EXEC EXECUTE EXISTS EXIT EXTERNAL FETCH
31 FILE FILLFACTOR FOR FOREIGN FREETEXT FREETEXTTABLE FROM FULL FUNCTION
32 GOTO GRANT GROUP HAVING HOLDLOCK IDENTITY IDENTITY_INSERT IDENTITYCOL
33 IF IN INDEX INNER INSERT INTERSECT INTO IS JOIN KEY KILL LEFT LIKE
34 LINENO LOAD MERGE NATIONAL NOCHECK NONCLUSTERED NOT NULL NULLIF OF OFF
35 OFFSETS ON OPEN OPENDATASOURCE OPENQUERY OPENROWSET OPENXML OPTION OR
36 ORDER OUTER OVER PERCENT PIVOT PLAN PRECISION PRIMARY PRINT PROC
37 PROCEDURE PUBLIC RAISERROR READ READTEXT RECONFIGURE REFERENCES
38 REPLICATION RESTORE RESTRICT RETURN REVERT REVOKE RIGHT ROLLBACK
39 ROWCOUNT ROWGUIDCOL RULE SAVE SCHEMA SECURITYAUDIT SELECT
40 SEMANTICKEYPHRASETABLE SEMANTICSIMILARITYDETAILSTABLE
41 SEMANTICSIMILARITYTABLE SESSION_USER SET SETUSER SHUTDOWN SOME
42 STATISTICS SYSTEM_USER TABLE TABLESAMPLE TEXTSIZE THEN TO TOP TRAN
43 TRANSACTION TRIGGER TRUNCATE TRY_CONVERT TSEQUAL UNION UNIQUE UNPIVOT
44 UPDATE UPDATETEXT USE USER VALUES VARYING VIEW WAITFOR WHEN WHERE WHILE
45 WITH WITHIN WRITETEXT
46 );
47  
48 # The one way a name is written, in the DDL and in the diff ops: bare when it
49 # is a plain identifier that is no reserved word, else in brackets
50 # with a closing bracket doubled. A dot is part of the name.
51 3469 ▶sub _quote_ident {
Subroutine _quote_ident: called CC=3 SCAR=11.0
52 3469 my ($name) = @_;
53 3469 ▶ return $name if $name =~ /\A[a-z_][a-z0-9_]*\z/i && !$RESERVED{ uc $name };
Branch: 1 branch
BranchTrueFalseTotal
if $name =~ /\A[a-z_][a-z0-9_]*\z/i and not $RESERVED{uc $name} 3434 35 3469
Condition100%3/3
$name =~ /\A[a-z_][a-z0-9_]*\z/i and not $RESERVED{uc $name}
!ll&&!rl&&r
14213434
MC/DC100%2/2
$name =~ /\A[a-z_][a-z0-9_]*\z/i and not $RESERVED{uc $name}
$name =~ /\A[a-z_][a-z0-9_]*\z/i not $RESERVED{uc $name}
Truth table100%3/3
A and not(B)
A= $name =~ /\A[a-z_][a-z0-9_]*\z/iB= not $RESERVED{uc $name}
ABresult
0X0
100
111
54 35 (my $q = $name) =~ s/\]/]]/g;
55 35 return "[$q]";
56 }
57  
58 # A table or view name is "table" (in dbo) or "schema.table" (karr k72): both
59 # parts are quoted on their own, so [my schema].t is the table t of the schema
60 # "my schema", never the table "my schema.t" of dbo. The model keys of
61 # DBIO::MSSQL::Introspect are written the same way (a table of dbo bare).
62 890 ▶sub _quote_table {
Subroutine _quote_table: called CC=2 SCAR=6.9
63 890 my ($name) = @_;
64 890 ▶ return _quote_ident($name) unless $name =~ /\./;
Branch: 1 branch
BranchTrueFalseTotal
unless $name =~ /\./ 860 30 890
65 30 my ($schema, $table) = split /\./, $name, 2;
66 30 return _quote_ident($schema) . '.' . _quote_ident($table);
67 }
68  
69 # The table of a "schema.table" name; a bare name is itself. Constraint names
70 # are made from it: they live in the schema of their table.
71 554 ▶sub _bare_table {
Subroutine _bare_table: called CC=1 SCAR=0.0
72 554 my ($name) = @_;
73 554 return $name =~ s/\A[^.]*\.//r;
74 }
75  
76 # "schema." for a "schema.table" name, '' for a table of dbo: the prefix of a
77 # name in the namespace of that schema (constraints and objects share one).
78 565 ▶sub _schema_prefix {
Subroutine _schema_prefix: called CC=2 SCAR=6.9
79 565 my ($name) = @_;
80 565 ▶ return $name =~ /\A([^.]*\.)/ ? $1 : '';
Branch: 1 branch
BranchTrueFalseTotal
$name =~ /\A([^.]*\.)/ ? : 22 543 565
81 }
82  
83 # The name a source gives its table or view, as the driver writes it. A name
84 # with a dot means schema.table in SQL Server and is made in that schema
85 # (karr k72); dbo.t is the table t of dbo, written bare, as the introspect
86 # keys it. A name with another number of dots (a database-qualified one) or an
87 # empty part is refused: the driver cannot create a table in another database.
88 1673 ▶sub _check_table_name {
Subroutine _check_table_name: called CC=9 SCAR=22.1
89 1673 my ($name, $kind) = @_;
90 1673 ▶ return $name unless defined $name && !ref $name && $name =~ /\./;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $name and not ref $name and $name =~ /\./ 1638 35 1673
Condition50%3/6
defined $name and not ref $name and $name =~ /\./
!ll&&!rl&&r
0163835
defined $name and not ref $name
!ll&&!rl&&r
001673
MC/DC33%1/3
defined $name and not ref $name and $name =~ /\./
defined $name not ref $name $name =~ /\./
Truth table50%2/4
A and not(B) and C
A= defined $nameB= not ref $nameC= $name =~ /\./
ABCresult
0XX0
10X0
1100
1111
91 35 ▶ $kind //= 'table';
Condition100%2/2
$kind //= "table"
l!l
2312
MC/DC100%1/1
$kind //= "table"
$kind
Truth table100%2/2
A
A= $kind
Aresult
11
00
92 35 my @part = split /\./, $name, -1;
93 35 ▶ DBIO::Exception->throw("DBIO::MSSQL: $kind '$name' is refused: a name with a "
Branch: 1 branch
BranchTrueFalseTotal
unless @part == 2 and length $part[0] and length $part[1] 4 31 35
Condition100%6/6
@part == 2 and length $part[0] and length $part[1]
!ll&&!rl&&r
3131
@part == 2 and length $part[0]
!ll&&!rl&&r
2132
MC/DC100%3/3
@part == 2 and length $part[0] and length $part[1]
@part == 2 length $part[0] length $part[1]
Truth table100%4/4
A and B and C
A= @part == 2B= length $part[0]C= length $part[1]
ABCresult
0XX0
10X0
1100
1111
94 . "dot means schema.table in SQL Server, with exactly one dot and a name on "
95 . "both sides (a name qualified with a database cannot be created from "
96 . "here)")
97 unless @part == 2 && length $part[0] && length $part[1];
98 31 ▶ return lc $part[0] eq 'dbo' ? $part[1] : $name;
Branch: 1 branch
BranchTrueFalseTotal
lc $part[0] eq 'dbo' ? : 4 27 31
99 }
100  
101 # The schemas other than dbo that the tables and views of a schema class name
102 # (virtual views and inline SQL are no objects of the server), sorted. What
103 # the introspect has to read besides dbo, and what install_ddl creates.
104 0 ▶sub _schemas_of {
Subroutine _schemas_of: not called CC=6 SCAR=37.4
105 0 my ($schema) = @_;
106 0 my %seen;
107 0 for my $source_name ($schema->sources) {
108 0 my $source = $schema->source($source_name);
109 0 ▶ next if _is_virtual_view($source);
Branch: 1 branch
BranchTrueFalseTotal
if _is_virtual_view($source) 0 0 0
110 0 ▶ my $name = _resolve_table_name($source->name, _is_view($source) ? 'view' : 'table');
Branch: 1 branch
BranchTrueFalseTotal
_is_view($source) ? : 0 0 0
111 0 ▶ next unless defined $name && $name =~ /\A([^.]+)\./;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $name and $name =~ /\A([^.]+)\./ 0 0 0
Condition0%0/3
defined $name and $name =~ /\A([^.]+)\./
!ll&&!rl&&r
000
MC/DC0%0/2
defined $name and $name =~ /\A([^.]+)\./
defined $name $name =~ /\A([^.]+)\./
Truth table0%0/3
A and B
A= defined $nameB= $name =~ /\A([^.]+)\./
ABresult
0X0
100
111
112 0 $seen{$1} = 1;
113 }
114 0 return sort keys %seen;
115 }
116  
117 2685 ▶sub _is_view { $_[0]->isa('DBIO::ResultSource::View') }
Subroutine _is_view: called CC=1 SCAR=0.0
118  
119 # A virtual view is a subquery in the FROM clause, no object of the server:
120 # nothing is deployed for it and its name is nobody's business.
121 1120 ▶sub _is_virtual_view {
Subroutine _is_virtual_view: called CC=4 SCAR=13.9
122 1120 my ($s) = @_;
123 1120 ▶ return _is_view($s) && $s->can('is_virtual') && $s->is_virtual ? 1 : 0;
Branch: 1 branch
BranchTrueFalseTotal
_is_view($s) && $s->can('is_virtual') && $s->is_virtual ? : 20 1100 1120
Condition83%5/6
_is_view($s) and $s->can("is_virtual") and $s->is_virtual
!ll&&!rl&&r
10841620
_is_view($s) and $s->can("is_virtual")
!ll&&!rl&&r
1084036
MC/DC66%2/3
_is_view($s) and $s->can("is_virtual") and $s->is_virtual
_is_view($s) $s->can("is_virtual") $s->is_virtual
Truth table75%3/4
A and B and C
A= _is_view($s)B= $s->can("is_virtual")C= $s->is_virtual
ABCresult
0XX0
10X0
1100
1111
124 }
125  
126 # SQL Server keeps constraint names and object names (tables, views) in one
127 # namespace per schema: a constraint called like a table fails with Msg 2714,
128 # also when the name was chosen on purpose. Found before the server is asked
129 # (karr k71). $objects: { name => 1 } of the tables and views that are
130 # created; $constraints: [ { table, name, kind, label, columns } ], the label
131 # naming the declaration ("unique constraint", "relationship 'x'"). $objects is
132 # keyed by the names as the model keys them (schema.table, a table of dbo
133 # bare) and a constraint is judged against the objects of the schema of its
134 # table. The names are compared as written (a case insensitive collation would also see
135 # 'Artist' and 'artist' as one name).
136 176 ▶sub _check_constraint_names {
Subroutine _check_constraint_names: called CC=3 SCAR=11.0
137 176 my ($objects, $constraints) = @_;
138 176 for my $c (@$constraints) {
139 # the namespace is the schema's: the object of the same schema
140 494 ▶ next unless $objects->{ _schema_prefix($c->{table}) . $c->{name} };
Branch: 1 branch
BranchTrueFalseTotal
unless $$objects{_schema_prefix($c->{"table"}) . $c->{"name"}} 486 8 494
141 DBIO::Exception->throw(sprintf
142 "DBIO::MSSQL: table '%s': %s '%s' (%s) is refused: the name is also the "
143 . "name of a table or view of this deploy, and SQL Server keeps constraint "
144 . "and object names in one namespace (Msg 2714). Name the constraint "
145 . "differently",
146 8 $c->{table}, $c->{label}, $c->{name}, join(', ', @{ $c->{columns} }));
147 }
148 168 return;
149 }
150  
151 # The most characters of an identifier in SQL Server.
152 66 my $MAX_NAME = 128;
153  
154 # The name of a constraint this driver makes up: PREFIX_table_col1_col2. One
155 # function for install_ddl and everything else that names one, so install and
156 # diff agree. A name over 128 characters is cut and ends in 8 hex digits of the
157 # SHA-1 of the full name, so two long names that start alike stay apart.
158 544 ▶sub _constraint_name {
Subroutine _constraint_name: called CC=2 SCAR=6.9
159 544 my ($prefix, $table, @cols) = @_;
160 # named after the bare table: the constraint lives in the schema of the table
161 544 my $name = join '_', $prefix, _bare_table($table), @cols;
162 544 ▶ return $name if length $name <= $MAX_NAME;
Branch: 1 branch
BranchTrueFalseTotal
if length $name <= $MAX_NAME 536 8 544
163 8 my $hash = substr Digest::SHA::sha1_hex($name), 0, 8;
164 8 return substr($name, 0, $MAX_NAME - 9) . "_$hash";
165 }
166  
167  
168 # The char-family types whose Transact-SQL grammar has the unbounded form:
169 # varchar [ ( n | max ) ], nvarchar [ ( n | max ) ], varbinary [ ( n | max ) ].
170 # The fixed-length char [ ( n ) ], nchar [ ( n ) ] and binary [ ( n ) ] have no
171 # (max), which is why _mssql_column_type cannot honour a -1 on them (karr k28).
172 66 my %MAX_LENGTH_TYPE = map { $_ => 1 } qw(varchar nvarchar varbinary);
173  
174 # SQL standard spellings (normalised, see _normalize_type) and the SQL Server
175 # type each one is a synonym of.
176 66 my %TYPE_ALIAS = (
177 'character' => 'char',
178 'char varying' => 'varchar',
179 'character varying' => 'varchar',
180 'national char' => 'nchar',
181 'national character' => 'nchar',
182 'nchar varying' => 'nvarchar',
183 'national char varying' => 'nvarchar',
184 'national character varying' => 'nvarchar',
185 'binary varying' => 'varbinary',
186 );
187  
188 # Lower case, blanks folded to one, "(n)" or "(p,s)" taken off: the type name
189 # and the parameter apart.
190 1391 ▶sub _normalize_type {
Subroutine _normalize_type: called CC=2 SCAR=6.9
191 1391 my ($type) = @_;
192 1391 my $n = lc $type;
193 1391 $n =~ s/\A\s+|\s+\z//g;
194 1391 $n =~ s/\s+/ /g;
195 1391 my $param;
196 1391 ▶ $param = $1 if $n =~ s/\s*\(\s*([^)]*?)\s*\)\z//;
Branch: 1 branch
BranchTrueFalseTotal
if $n =~ s/\s*\(\s*([^)]*?)\s*\)\z// 40 1351 1391
197 1391 return ($n, $param);
198 }
199  
200  
201  
202 176 ▶sub install_ddl {
Subroutine install_ddl: called CC=61 SCAR=41.4
Pod documented
203 176 my ($class, $schema) = @_;
204  
205 176 my @stmts;
206 my %seen_table;
207 176 my @view_stmts;
208 176 my %seen_view;
209 176 my %seen_constraint;
210 176 my @constraints;
211 176 my %schemas;
212  
213 176 for my $source_name (_topo_sort_sources($schema)) {
214 366 my $source = $schema->source($source_name);
215 366 my $result_class = $source->result_class;
216 # Views: emit CREATE VIEW after all tables; skip virtual views.
217 366 ▶ if ($source->isa('DBIO::ResultSource::View')) {
Branch: 1 branch
BranchTrueFalseTotal
if ($source->isa("DBIO::ResultSource::View")) 11 355 366
218 11 ▶ next if $source->can('is_virtual') && $source->is_virtual;
Branch: 1 branch
BranchTrueFalseTotal
if $source->can("is_virtual") and $source->is_virtual 6 5 11
Condition66%2/3
$source->can("is_virtual") and $source->is_virtual
!ll&&!rl&&r
056
MC/DC50%1/2
$source->can("is_virtual") and $source->is_virtual
$source->can("is_virtual") $source->is_virtual
Truth table66%2/3
A and B
A= $source->can("is_virtual")B= $source->is_virtual
ABresult
0X0
100
111
219 5 my $vname = _resolve_table_name($source->name, 'view');
220 5 ▶ next if !defined $vname || $seen_view{$vname}++;
Branch: 1 branch
BranchTrueFalseTotal
if not defined $vname or $seen_view{$vname}++ 0 5 5
Condition33%1/3
not defined $vname or $seen_view{$vname}++
l!l&&r!l&&!r
005
MC/DC0%0/2
not defined $vname or $seen_view{$vname}++
not defined $vname $seen_view{$vname}++
Truth table33%1/3
not(A) or B
A= not defined $vnameB= $seen_view{$vname}++
ABresult
1X1
011
000
221 5 ▶ my $def = $source->can('view_definition') ? $source->view_definition : undef;
Branch: 1 branch
BranchTrueFalseTotal
$source->can('view_definition') ? : 5 0 5
222 5 ▶ next unless defined $def && length $def;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $def and length $def 0 5 5
Condition33%1/3
defined $def and length $def
!ll&&!rl&&r
005
MC/DC0%0/2
defined $def and length $def
defined $def length $def
Truth table33%1/3
A and B
A= defined $defB= length $def
ABresult
0X0
100
111
223 5 ▶ $schemas{$1} = 1 if $vname =~ /\A([^.]+)\./;
Branch: 1 branch
BranchTrueFalseTotal
if $vname =~ /\A([^.]+)\./ 1 4 5
224 5 push @view_stmts, [ $vname, $def ];
225 5 next;
226 }
227  
228 355 my $table_name = _resolve_table_name($source->name);
229  
230 # Virtual / view source whose name is inline SQL: skip here.
231 355 ▶ next unless defined $table_name;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $table_name 3 352 355
232  
233 # Multiple Result classes may share one physical table.
234 352 ▶ next if $seen_table{$table_name}++;
Branch: 1 branch
BranchTrueFalseTotal
if $seen_table{$table_name}++ 6 346 352
235 346 ▶ $schemas{$1} = 1 if $table_name =~ /\A([^.]+)\./;
Branch: 1 branch
BranchTrueFalseTotal
if $table_name =~ /\A([^.]+)\./ 5 341 346
236  
237 346 my @col_defs;
238 my %is_pk;
239 346 my @pk_cols = $source->primary_columns;
240 346 @is_pk{@pk_cols} = (1) x @pk_cols;
241  
242 346 for my $col_name ($source->columns) {
243 852 my $info = $source->column_info($col_name);
244 852 my $type = _mssql_column_type($info, $table_name, $col_name);
245  
246 850 my $def = sprintf ' %s %s', _quote_ident($col_name), $type;
247  
248 850 ▶ $def .= ' NOT NULL' if !$info->{is_nullable};
Branch: 1 branch
BranchTrueFalseTotal
unless $info->{"is_nullable"} 643 207 850
249  
250 850 ▶ if ($info->{is_auto_increment}) {
Branch: 2 branches
BranchTrueFalseTotal
if ($info->{'is_auto_increment'}) { } 85 765 850
elsif (defined $info->{'default_value'}) { } 102 663 765
251 85 $def .= ' IDENTITY(1,1)';
252 } elsif (defined $info->{default_value}) {
253 102 $def .= ' DEFAULT ' . _default_literal($info->{default_value}, $type, $table_name, $col_name);
254 }
255  
256 805 push @col_defs, $def;
257 }
258  
259 299 ▶ if (@pk_cols) {
Branch: 1 branch
BranchTrueFalseTotal
if (@pk_cols) 281 18 299
260 # Named: unnamed, the server gives the key a random name
261 # (PK__table__<hash>) in every database, and the diff has nothing to
262 # address a key it adds by. The same name, PK_<table>, is what
263 # DBIO::MSSQL::Diff writes (karr k74).
264 281 my $pk_name = _constraint_name('PK', $table_name);
265 281 push @constraints, { table => $table_name, name => $pk_name,
266 label => 'primary key', columns => \@pk_cols };
267 push @col_defs, sprintf ' CONSTRAINT %s PRIMARY KEY (%s)',
268 _quote_ident($pk_name),
269 281 join(', ', map { _quote_ident($_) } @pk_cols);
270 }
271  
272 # The columns that are unique already: the primary key and every unique
273 # constraint. A plain index over exactly these columns is a second one for
274 # the lookups the server's own index serves (see "Indexes").
275 299 my %covered;
276 299 ▶ $covered{ join "\0", @pk_cols } = 1 if @pk_cols;
Branch: 1 branch
BranchTrueFalseTotal
if @pk_cols 281 18 299
277  
278 299 ▶ if ($source->can('unique_constraints')) {
Branch: 1 branch
BranchTrueFalseTotal
if ($source->can("unique_constraints")) 299 0 299
279 299 my %uniques = $source->unique_constraints;
280 299 for my $uname (sort keys %uniques) {
281 352 ▶ next if $uname eq 'primary';
Branch: 1 branch
BranchTrueFalseTotal
if $uname eq "primary" 281 71 352
282 71 my $cols = $uniques{$uname};
283 # Named: unnamed, the server gives the constraint a random name
284 # (UQ__table__<hash>) in every database it is installed into, and the
285 # diff of two databases then sees two different indexes. The name is
286 # the declared one (core names an undeclared one <table>_<columns>).
287 # The name core makes up for an undeclared one, <table>_<columns>, is
288 # an object name that a table can have as well (the table user and
289 # unique(email) against a table user_email): it gets its own prefix,
290 # as the foreign keys have theirs. A name the declaration chose stays.
291 71 ▶ my $cname = $uname eq $source->name_unique_constraint($cols)
Branch: 1 branch
BranchTrueFalseTotal
$uname eq $source->name_unique_constraint($cols) ? : 40 31 71
292 ? _constraint_name('UQ', $table_name, @$cols)
293 : $uname;
294 71 my $ckey = _schema_prefix($table_name) . $cname;
295 DBIO::Exception->throw(sprintf
296 "DBIO::MSSQL: table '%s': unique constraint '%s' (%s) is refused: it "
297 . "has the name of one on another table, and SQL Server holds "
298 . "constraint names once per schema",
299 $table_name, $cname, join(', ', @$cols))
300 71 ▶ if $seen_constraint{$ckey} && $seen_constraint{$ckey} ne $table_name;
Branch: 1 branch
BranchTrueFalseTotal
if $seen_constraint{$ckey} and $seen_constraint{$ckey} ne $table_name 2 69 71
Condition66%2/3
$seen_constraint{$ckey} and $seen_constraint{$ckey} ne $table_name
!ll&&!rl&&r
6902
MC/DC50%1/2
$seen_constraint{$ckey} and $seen_constraint{$ckey} ne $table_name
$seen_constraint{$ckey} $seen_constraint{$ckey} ne $table_name
Truth table66%2/3
A and B
A= $seen_constraint{$ckey}B= $seen_constraint{$ckey} ne $table_name
ABresult
0X0
100
111
301 69 $seen_constraint{$ckey} = $table_name;
302 69 push @constraints, { table => $table_name, name => $cname,
303 label => 'unique constraint', columns => $cols };
304 69 $covered{ join "\0", @$cols } = 1;
305 push @col_defs, sprintf ' CONSTRAINT %s UNIQUE (%s)',
306 _quote_ident($cname),
307 69 join(', ', map { _quote_ident($_) } @$cols);
308 }
309 }
310  
311 297 push @stmts, sprintf "CREATE TABLE %s (\n%s\n);",
312 _quote_table($table_name), join(",\n", @col_defs);
313  
314 # Standalone indexes: the ones written by hand with mssql_indexes, and
315 # the canonical declarations of the result source (Cake idx, indices;
316 # ADR 0061 in core, karr k66). A canonical declaration of the same name
317 # wins over the hand-written one.
318 297 my %indexes;
319 297 ▶ if ($result_class->can('mssql_indexes')) {
Branch: 1 branch
BranchTrueFalseTotal
if ($result_class->can("mssql_indexes")) 15 282 297
320 15 my $hand = $result_class->mssql_indexes;
321 15 $indexes{$_} = $hand->{$_} for keys %$hand;
322 }
323 # The helper refuses an entry that carries only another driver's block
324 # and an unknown key in the own block (core k333); its message is passed
325 # through. An empty own block is the plain index.
326 297 for my $decl ($source->index_declarations_for('mssql', ['kind', 'where'])) {
327 # type (other than unique) and options have no counterpart here and
328 # are refused, never written as a plainer index (ADR 0061).
329 29 ▶ if (defined $decl->{type} && $decl->{type} !~ /^unique$/i) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $decl->{"type"} and not $decl->{"type"} =~ /^unique$/i) 2 27 29
Condition100%3/3
defined $decl->{"type"} and not $decl->{"type"} =~ /^unique$/i
!ll&&!rl&&r
2522
MC/DC100%2/2
defined $decl->{"type"} and not $decl->{"type"} =~ /^unique$/i
defined $decl->{"type"} not $decl->{"type"} =~ /^unique$/i
Truth table100%3/3
A and not(B)
A= defined $decl->{"type"}B= not $decl->{"type"} =~ /^unique$/i
ABresult
0X0
100
111
330 DBIO::Exception->throw(sprintf
331 "DBIO::MSSQL: table '%s': index '%s' (%s) is refused: type '%s' has "
332 . "no SQL Server counterpart and is not written as a plainer index",
333 2 ▶ $table_name, $decl->{name}, join(', ', @{ $decl->{columns} // [] }),
Condition50%1/2
$decl->{"columns"} // []
l!l
20
MC/DC0%0/1
$decl->{"columns"} // []
$decl->{"columns"}
Truth table50%1/2
A
A= $decl->{"columns"}
Aresult
11
00
334 2 $decl->{type});
335 }
336 27 ▶ if (defined $decl->{options}) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $decl->{"options"}) 2 25 27
337 DBIO::Exception->throw(sprintf
338 "DBIO::MSSQL: table '%s': index '%s' (%s) is refused: options are "
339 . "not supported by the SQL Server DDL and are not dropped silently",
340 2 ▶ $table_name, $decl->{name}, join(', ', @{ $decl->{columns} // [] }));
Condition50%1/2
$decl->{"columns"} // []
l!l
20
MC/DC0%0/1
$decl->{"columns"} // []
$decl->{"columns"}
Truth table50%1/2
A
A= $decl->{"columns"}
Aresult
11
00
341 }
342 25 ▶ my $own = ($decl->{driver} || {})->{mssql} || {};
Condition75%3/4
+($decl->{'driver'} || {})->{'mssql'} || {}
l!l
1510
$decl->{'driver'} || {}
l!l
250
MC/DC0%0/1
+($decl->{'driver'} || {})->{'mssql'} || {}
$decl->{'driver'}
Truth table50%1/2
A
A= $decl->{'driver'}
Aresult
11
00
343 $indexes{ $decl->{name} } = {
344 columns => $decl->{columns},
345 unique => $decl->{unique},
346 kind => $own->{kind},
347 25 ▶ (exists $own->{where} ? (where => $own->{where}) : ()),
Branch: 1 branch
BranchTrueFalseTotal
exists $own->{'where'} ? : 5 20 25
348 };
349 }
350 {
351 291 my $has_clustered = 0;
352 291 for my $idx_name (sort keys %indexes) {
353 36 my $idx = $indexes{$idx_name};
354 36 ▶ my $kind = $idx->{kind} // '';
Condition100%2/2
$idx->{"kind"} // ""
l!l
1224
MC/DC100%1/1
$idx->{"kind"} // ""
$idx->{"kind"}
Truth table100%2/2
A
A= $idx->{"kind"}
Aresult
11
00
355 DBIO::Exception->throw(sprintf
356 "DBIO::MSSQL: table '%s': index '%s' (%s) is refused: invalid kind "
357 . "'%s' -- expected 'clustered' or 'nonclustered'",
358 36 ▶ $table_name, $idx_name, join(', ', @{ $idx->{columns} // [] }), $kind)
Branch: 1 branch
BranchTrueFalseTotal
if length $kind and $kind ne "clustered" and $kind ne "nonclustered" 2 34 36
Condition87%7/8
length $kind and $kind ne "clustered" and $kind ne "nonclustered"
!ll&&!rl&&r
3222
length $kind and $kind ne "clustered"
!ll&&!rl&&r
2484
$idx->{"columns"} // []
l!l
20
MC/DC100%3/3
length $kind and $kind ne "clustered" and $kind ne "nonclustered"
length $kind $kind ne "clustered" $kind ne "nonclustered"
MC/DC0%0/1
$idx->{"columns"} // []
$idx->{"columns"}
Truth table100%4/4
A and B and C
A= length $kindB= $kind ne "clustered"C= $kind ne "nonclustered"
ABCresult
0XX0
10X0
1100
1111
Truth table50%1/2
A
A= $idx->{"columns"}
Aresult
11
00
359 if length $kind && $kind ne 'clustered' && $kind ne 'nonclustered';
360 # A filter (karr k76) is a predicate string; the server accepts it in
361 # a form of its own and refuses a filter on a clustered index (it
362 # would not hold every row), so that is refused here.
363 34 my $where = $idx->{where};
364 34 ▶ if (exists $idx->{where}) {
Branch: 1 branch
BranchTrueFalseTotal
if (exists $idx->{"where"}) 12 22 34
365 DBIO::Exception->throw(sprintf
366 "DBIO::MSSQL: table '%s': index '%s' (%s) is refused: where must "
367 . "be a non-empty SQL predicate string",
368 12 ▶ $table_name, $idx_name, join(', ', @{ $idx->{columns} // [] }))
Branch: 1 branch
BranchTrueFalseTotal
if not defined $where or ref $where or not $where =~ /\S/ 5 7 12
Condition75%6/8
not defined $where or ref $where or not $where =~ /\S/
l!l&&r!l&&!r
237
not defined $where or ref $where
l!l&&r!l&&!r
0210
$idx->{"columns"} // []
l!l
50
MC/DC66%2/3
not defined $where or ref $where or not $where =~ /\S/
not defined $where ref $where not $where =~ /\S/
MC/DC0%0/1
$idx->{"columns"} // []
$idx->{"columns"}
Truth table75%3/4
not(A) or B or not(C)
A= not defined $whereB= ref $whereC= not $where =~ /\S/
ABCresult
1XX1
01X1
0011
0000
Truth table50%1/2
A
A= $idx->{"columns"}
Aresult
11
00
369 if !defined $where || ref $where || $where !~ /\S/;
370 7 ▶ DBIO::Exception->throw(sprintf
Branch: 1 branch
BranchTrueFalseTotal
if $kind eq "clustered" 2 5 7
371 "DBIO::MSSQL: index '%s' on table '%s' is refused: a filter "
372 . "(where) on a clustered index (SQL Server allows a filter on "
373 . "nonclustered indexes only)", $idx_name, $table_name)
374 if $kind eq 'clustered';
375 5 $where =~ s/^\s+|\s+$//g;
376 }
377 # Same columns in the same order as the primary key or a unique
378 # constraint: the server keeps an index for those, a second one is a
379 # duplicate -- plain or declared unique. A CLUSTERED one is another
380 # object (it fixes the physical order, which the nonclustered
381 # constraint index does not), and a filtered one holds fewer rows
382 # (and may be unique where the constraint is not), so both are written.
383 next if $kind ne 'clustered' && !defined $where
384 27 ▶ && $covered{ join "\0", @{ $idx->{columns} // [] } };
Branch: 1 branch
BranchTrueFalseTotal
if $kind ne "clustered" and not defined $where and $covered{join "\0", @{$idx->{"columns"} // [];}} 4 23 27
Condition87%7/8
$kind ne "clustered" and not defined $where and $covered{join "\0", @{$idx->{"columns"} // [];}}
!ll&&!rl&&r
11124
$kind ne "clustered" and not defined $where
!ll&&!rl&&r
6516
$idx->{"columns"} // []
l!l
160
MC/DC100%3/3
$kind ne "clustered" and not defined $where and $covered{join "\0", @{$idx->{"columns"} // [];}}
$kind ne "clustered" not defined $where $covered{join "\0", @{$idx->{"columns"} // [];}}
MC/DC0%0/1
$idx->{"columns"} // []
$idx->{"columns"}
Truth table100%4/4
A and not(B) and C
A= $kind ne "clustered"B= not defined $whereC= $covered{join "\0", @{$idx->{"columns"} // [];}}
ABCresult
0XX0
10X0
1100
1111
Truth table50%1/2
A
A= $idx->{"columns"}
Aresult
11
00
385 23 ▶ my $unique = $idx->{unique} ? 'UNIQUE ' : '';
Branch: 1 branch
BranchTrueFalseTotal
$idx->{'unique'} ? : 5 18 23
386 23 ▶ if ($kind eq 'clustered') {
Branch: 1 branch
BranchTrueFalseTotal
if ($kind eq "clustered") 6 17 23
387 # A PRIMARY KEY is CLUSTERED unless written NONCLUSTERED, and a
388 # table has one clustered index: the server refuses the second
389 # (Msg 1902). Refused here, before the server, not rewritten into
390 # a NONCLUSTERED key (that would change the key type of existing
391 # users; a maintainer question).
392 6 ▶ DBIO::Exception->throw(sprintf
Branch: 1 branch
BranchTrueFalseTotal
if @pk_cols 1 5 6
393 "DBIO::MSSQL: index '%s' on table '%s' is refused: kind "
394 . "'clustered' collides with the clustered PRIMARY KEY of the "
395 . "table (SQL Server allows one clustered index per table, "
396 . "Msg 1902); declare the index nonclustered, or the table "
397 . "without a primary key", $idx_name, $table_name)
398 if @pk_cols;
399 5 ▶ DBIO::Exception->throw(sprintf
Branch: 1 branch
BranchTrueFalseTotal
if $has_clustered++ 1 4 5
400 "DBIO::MSSQL: index '%s' on table '%s' is refused: a second "
401 . "clustered index (SQL Server allows one per table, Msg 1902)",
402 $idx_name, $table_name)
403 if $has_clustered++;
404 }
405 21 ▶ my $kind_sql = $kind eq 'clustered' ? 'CLUSTERED ' : $kind eq 'nonclustered' ? 'NONCLUSTERED ' : '';
Branch: 2 branches
BranchTrueFalseTotal
$kind eq 'clustered' ? : 4 17 21
$kind eq 'nonclustered' ? : 2 15 17
406 my $columns = join ', ',
407 21 ▶ map { _quote_ident($_) } @{ $idx->{columns} // [] };
Condition50%1/2
$idx->{"columns"} // []
l!l
210
MC/DC0%0/1
$idx->{"columns"} // []
$idx->{"columns"}
Truth table50%1/2
A
A= $idx->{"columns"}
Aresult
11
00
408 21 my $sql = sprintf 'CREATE %s%sINDEX %s ON %s (%s)',
409 $unique, $kind_sql, _quote_ident($idx_name),
410 _quote_table($table_name), $columns;
411 21 ▶ $sql .= " WHERE $where" if defined $where;
Branch: 1 branch
BranchTrueFalseTotal
if defined $where 5 16 21
412 21 push @stmts, "$sql;";
413 }
414 }
415 }
416  
417 106 push @stmts, _foreign_key_stmts($schema, \%seen_table, \@constraints);
418  
419 90 _check_constraint_names({ %seen_table, %seen_view }, \@constraints);
420  
421 # A schema a table or view names is created before the first object, each
422 # CREATE SCHEMA a statement of its own: it has to be the first of its batch,
423 # and the statements run one by one. dbo is always there.
424 85 my @schema_stmts = map { sprintf 'CREATE SCHEMA %s;', _quote_ident($_) } sort keys %schemas;
425  
426 85 return join "
427  
428 ", @schema_stmts, @stmts, _order_view_statements(@view_stmts);
429 }
430  
431 # CREATE VIEW statements, a view after the views its text names (a word that is
432 # the bare name of another view of the deploy), otherwise in the order given.
433 # The text is all there is to go by here; the diff orders by what the server
434 # recorded (karr k82). A cycle is left to the server to refuse.
435 86 ▶sub _order_view_statements {
Subroutine _order_view_statements: called CC=2 SCAR=6.9
436 86 my (@views) = @_;
437 86 my %by = map { $_->[0] => $_ } @views;
438 86 my (@out, %state);
439 86 ▶ my $visit; $visit = sub {
Subroutine __ANON__: called CC=5 SCAR=16.1
440 8 my ($v) = @_;
441 8 ▶ return if $state{ $v->[0] }++;
Branch: 1 branch
BranchTrueFalseTotal
if $state{$v->[0]}++ 1 7 8
442 7 for my $other (@views) {
443 13 ▶ next if $other == $v;
Branch: 1 branch
BranchTrueFalseTotal
if $other == $v 7 6 13
444 6 (my $bare = _bare_table($other->[0])) =~ s/(\W)/\\$1/g;
445 6 ▶ $visit->($other) if $v->[1] =~ /(?<![\w\@#\$])\[?$bare\]?(?![\w\@#\$])/i;
Branch: 1 branch
BranchTrueFalseTotal
if $v->[1] =~ /(?<![\w\@#\$])\[?$bare\]?(?![\w\@#\$])/i 1 5 6
446 }
447 7 push @out, $v;
448 86 };
449 86 $visit->($_) for @views;
450 86 return map { sprintf 'CREATE VIEW %s AS %s;', _quote_table($_->[0]), $_->[1] } @out;
451 }
452  
453 # FOREIGN KEY constraints of the belongs_to relationships (karr k71, ADR 0060
454 # case family ddl-fk-*). Written as ALTER TABLE ... ADD CONSTRAINT after every
455 # CREATE TABLE, so the order of the tables does not matter. The name is
456 # FK_<table>_<columns>, the key of the constraint in the diff (ADR 0021); the
457 # actions are the on_delete / on_update the relationship declares, as declared
458 # (none: no clause, NO ACTION). A relationship with
459 # is_foreign_key_constraint => 0 writes none.
460 66 my %FK_ACTION = map { $_ => 1 } ('CASCADE', 'SET NULL', 'SET DEFAULT', 'NO ACTION');
461  
462 330 ▶sub _fk_action {
Subroutine _fk_action: called CC=2 SCAR=6.9
463 330 my ($event, $action, $rel_name, $table_name) = @_;
464 330 my $uc = _fk_action_word($event, $action, $rel_name, $table_name);
465 328 ▶ return defined $uc ? " $event $uc" : '';
Branch: 1 branch
BranchTrueFalseTotal
defined $uc ? : 69 259 328
466 }
467  
468 # The action as SQL Server writes it, or undef when none is declared.
469 628 ▶sub _fk_action_word {
Subroutine _fk_action_word: called CC=5 SCAR=16.1
470 628 my ($event, $action, $rel_name, $table_name) = @_;
471 628 ▶ return undef unless defined $action && length $action;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $action and length $action 496 132 628
Condition66%2/3
defined $action and length $action
!ll&&!rl&&r
4960132
MC/DC50%1/2
defined $action and length $action
defined $action length $action
Truth table66%2/3
A and B
A= defined $actionB= length $action
ABresult
0X0
100
111
472 132 my $uc = uc $action;
473 132 $uc =~ s/\s+/ /g;
474 # SQL Server has no RESTRICT; its NO ACTION is checked at the end of the
475 # statement, without deferral -- the same behaviour, so the same constraint
476 # (core's own test schema declares RESTRICT).
477 132 ▶ $uc = 'NO ACTION' if $uc eq 'RESTRICT';
Branch: 1 branch
BranchTrueFalseTotal
if $uc eq "RESTRICT" 8 124 132
478 DBIO::Exception->throw(sprintf
479 "DBIO::MSSQL: table '%s': relationship '%s' is refused: %s %s is not an "
480 . "action of SQL Server (CASCADE, SET NULL, SET DEFAULT, NO ACTION)",
481 $table_name, $rel_name, $event, $action)
482 132 ▶ unless $FK_ACTION{$uc};
Branch: 1 branch
BranchTrueFalseTotal
unless $FK_ACTION{$uc} 2 130 132
483 130 return $uc;
484 }
485  
486 185 ▶sub _fk_column_pairs {
Subroutine _fk_column_pairs: called CC=8 SCAR=21.6
487 185 my ($cond) = @_;
488 185 ▶ return unless ref $cond eq 'HASH';
Branch: 1 branch
BranchTrueFalseTotal
unless ref $cond eq "HASH" 12 173 185
489 173 my %self_to_foreign;
490 173 for my $key (keys %$cond) {
491 195 my $val = $cond->{$key};
492 195 ▶ return if ref $key || ref $val;
Branch: 1 branch
BranchTrueFalseTotal
if ref $key or ref $val 0 195 195
Condition33%1/3
ref $key or ref $val
l!l&&r!l&&!r
00195
MC/DC0%0/2
ref $key or ref $val
ref $key ref $val
Truth table33%1/3
A or B
A= ref $keyB= ref $val
ABresult
1X1
011
000
493 195 ▶ return unless $key =~ /^foreign\.([^.]+)$/;
Branch: 1 branch
BranchTrueFalseTotal
unless $key =~ /^foreign\.([^.]+)$/ 0 195 195
494 195 my $f_col = $1;
495 195 ▶ return unless $val =~ /^self\.([^.]+)$/;
Branch: 1 branch
BranchTrueFalseTotal
unless $val =~ /^self\.([^.]+)$/ 0 195 195
496 195 $self_to_foreign{$1} = $f_col;
497 }
498 173 ▶ return unless %self_to_foreign;
Branch: 1 branch
BranchTrueFalseTotal
unless %self_to_foreign 0 173 173
499 173 my @self_cols = sort keys %self_to_foreign;
500 173 return (\@self_cols, [ map { $self_to_foreign{$_} } @self_cols ]);
501 }
502  
503 # The order of the columns of a foreign key is the order of the key it
504 # references (karr k73). Measured: SQL Server takes the referenced list only
505 # in the exact order of a primary key, unique constraint or unique index of the
506 # target (Msg 1776 otherwise) and the pairing with the referencing columns as
507 # written. The primary key is asked first, then the unique constraints by name;
508 # the first whose column set equals the referenced columns gives the order.
509 # None: the order stays as _fk_column_pairs made it (sorted by the referencing
510 # names; the server decides). One column: nothing to order.
511 173 ▶sub _order_by_referenced_key {
Subroutine _order_by_referenced_key: called CC=8 SCAR=20.8
512 173 my ($foreign, $self_cols, $foreign_cols) = @_;
513 173 ▶ return ($self_cols, $foreign_cols) if !$foreign || @$foreign_cols < 2;
Branch: 1 branch
BranchTrueFalseTotal
if not $foreign or @$foreign_cols < 2 157 16 173
Condition100%3/3
not $foreign or @$foreign_cols < 2
l!l&&r!l&&!r
115616
MC/DC100%2/2
not $foreign or @$foreign_cols < 2
not $foreign @$foreign_cols < 2
Truth table100%3/3
not(A) or B
A= not $foreignB= @$foreign_cols < 2
ABresult
1X1
011
000
514 16 my %pair; @pair{@$foreign_cols} = @$self_cols;
515 16 my $want = join "\0", sort @$foreign_cols;
516 16 my @keys = ([ $foreign->primary_columns ]);
517 16 ▶ if ($foreign->can('unique_constraints')) {
Branch: 1 branch
BranchTrueFalseTotal
if ($foreign->can("unique_constraints")) 16 0 16
518 16 my %uniques = $foreign->unique_constraints;
519 16 push @keys, $uniques{$_} for grep { $_ ne 'primary' } sort keys %uniques;
520 }
521 16 for my $key (@keys) {
522 17 ▶ next unless @$key == @$foreign_cols && join("\0", sort @$key) eq $want;
Branch: 1 branch
BranchTrueFalseTotal
unless @$key == @$foreign_cols and join("\0", sort(@$key)) eq $want 2 15 17
Condition66%2/3
@$key == @$foreign_cols and join("\0", sort(@$key)) eq $want
!ll&&!rl&&r
2015
MC/DC50%1/2
@$key == @$foreign_cols and join("\0", sort(@$key)) eq $want
@$key == @$foreign_cols join("\0", sort(@$key)) eq $want
Truth table66%2/3
A and B
A= @$key == @$foreign_colsB= join("\0", sort(@$key)) eq $want
ABresult
0X0
100
111
523 15 return ([ map { $pair{$_} } @$key ], [ @$key ]);
524 }
525 1 return ($self_cols, $foreign_cols);
526 }
527  
528 106 ▶sub _foreign_key_stmts {
Subroutine _foreign_key_stmts: called CC=22 SCAR=30.9
529 106 my ($schema, $seen_table, $constraints) = @_;
530 106 my (@stmts, %fk_seen, @cascade);
531  
532 106 for my $source_name (sort $schema->sources) {
533 293 my $source = $schema->source($source_name);
534 293 ▶ next if _is_view($source);
Branch: 1 branch
BranchTrueFalseTotal
if _is_view($source) 11 282 293
535 282 my $table_name = _resolve_table_name($source->name);
536 282 ▶ next unless defined $table_name;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $table_name 3 279 282
537 # several Result classes of one table: the first one wrote it, and
538 # declares the keys
539 279 ▶ next if ($fk_seen{ "\0table" }{$table_name}++);
Branch: 1 branch
BranchTrueFalseTotal
if $fk_seen{"\0table"}{$table_name}++ 6 273 279
540  
541 273 for my $rel_name (sort $source->relationships) {
542 321 my $info = $source->relationship_info($rel_name);
543 321 ▶ my $attrs = $info->{attrs} || {};
Condition50%1/2
$info->{'attrs'} || {}
l!l
3210
MC/DC0%0/1
$info->{'attrs'} || {}
$info->{'attrs'}
Truth table50%1/2
A
A= $info->{'attrs'}
Aresult
11
00
544 321 ▶ next unless $attrs->{is_depends_on} && $attrs->{is_foreign_key_constraint};
Branch: 1 branch
BranchTrueFalseTotal
unless $attrs->{"is_depends_on"} and $attrs->{"is_foreign_key_constraint"} 136 185 321
Condition100%3/3
$attrs->{"is_depends_on"} and $attrs->{"is_foreign_key_constraint"}
!ll&&!rl&&r
1324185
MC/DC100%2/2
$attrs->{"is_depends_on"} and $attrs->{"is_foreign_key_constraint"}
$attrs->{"is_depends_on"} $attrs->{"is_foreign_key_constraint"}
Truth table100%3/3
A and B
A= $attrs->{"is_depends_on"}B= $attrs->{"is_foreign_key_constraint"}
ABresult
0X0
100
111
545  
546 185 my ($self_cols, $foreign_cols) = _fk_column_pairs($info->{cond});
547 185 ▶ next unless $self_cols;
Branch: 1 branch
BranchTrueFalseTotal
unless $self_cols 12 173 185
548  
549 173 my $foreign = eval { $source->related_source($rel_name) };
550 173 ($self_cols, $foreign_cols) = _order_by_referenced_key($foreign, $self_cols, $foreign_cols);
551 173 ▶ my $f_table = $foreign && !_is_virtual_view($foreign)
Branch: 2 branches
BranchTrueFalseTotal
$foreign && !_is_virtual_view($foreign) ? : 169 4 173
_is_view($foreign) ? : 3 166 169
Condition100%3/3
$foreign and not _is_virtual_view($foreign)
!ll&&!rl&&r
13169
MC/DC100%2/2
$foreign and not _is_virtual_view($foreign)
$foreign not _is_virtual_view($foreign)
Truth table100%3/3
A and not(B)
A= $foreignB= not _is_virtual_view($foreign)
ABresult
0X0
100
111
552 ? _resolve_table_name($foreign->name, _is_view($foreign) ? 'view' : 'table')
553 : undef;
554 # A view, or a source that is inline SQL, has no table to reference.
555 # Skipped, not refused, although the server would refuse a key to a
556 # view: belongs_to sets is_foreign_key_constraint => 1 itself when the
557 # class does not say (core, BelongsTo.pm), so a key to a view that
558 # nobody asked for cannot be told from one that was -- and the shared
559 # test schema of the family declares exactly that (Track to
560 # Year1999CDs). A class that wants none says is_foreign_key_constraint => 0.
561 173 ▶ next if $foreign && (_is_view($foreign) || !defined $f_table);
Branch: 1 branch
BranchTrueFalseTotal
if $foreign and _is_view($foreign) || !defined($f_table) 6 167 173
Condition83%5/6
$foreign and _is_view($foreign) || !defined($f_table)
!ll&&!rl&&r
11666
_is_view($foreign) or not defined $f_table
l!l&&r!l&&!r
60166
MC/DC66%2/3
$foreign and _is_view($foreign) || !defined($f_table)
$foreign _is_view($foreign) not defined $f_table
Truth table75%3/4
A and B or not(C)
A= $foreignB= _is_view($foreign)C= not defined $f_table
ABCresult
0XX0
1000
11X1
1011
562 DBIO::Exception->throw(sprintf
563 "DBIO::MSSQL: table '%s': relationship '%s' is refused: it is declared "
564 . "as a foreign key to %s, which is not part of this deploy -- "
565 . "register the class with the schema, or set "
566 . "is_foreign_key_constraint => 0",
567 $table_name, $rel_name, $info->{class})
568 167 ▶ unless defined $f_table && $seen_table->{$f_table};
Branch: 1 branch
BranchTrueFalseTotal
unless defined $f_table and $seen_table->{$f_table} 1 166 167
Condition66%2/3
defined $f_table and $seen_table->{$f_table}
!ll&&!rl&&r
10166
MC/DC50%1/2
defined $f_table and $seen_table->{$f_table}
defined $f_table $seen_table->{$f_table}
Truth table66%2/3
A and B
A= defined $f_tableB= $seen_table->{$f_table}
ABresult
0X0
100
111
569  
570 my $actions = _fk_action('ON DELETE', $attrs->{on_delete}, $rel_name, $table_name)
571 166 . _fk_action('ON UPDATE', $attrs->{on_update}, $rel_name, $table_name);
572  
573 164 my $fk_name = _constraint_name('FK', $table_name, @$self_cols);
574 164 my $fk_key = join "\0", $f_table, @$foreign_cols, $actions;
575 164 ▶ if (my $earlier = $fk_seen{$table_name}{$fk_name}) {
Branch: 1 branch
BranchTrueFalseTotal
if (my $earlier = $fk_seen{$table_name}{$fk_name}) 15 149 164
576 # the same key declared twice is one constraint
577 15 ▶ next if $earlier->[0] eq $fk_key;
Branch: 1 branch
BranchTrueFalseTotal
if $earlier->[0] eq $fk_key 13 2 15
578 2 DBIO::Exception->throw(sprintf
579 "DBIO::MSSQL: table '%s': relationships '%s' and '%s' are refused: "
580 . "they declare the foreign key on (%s) twice, as constraint %s, "
581 . "with another target or other actions; the server holds one "
582 . "constraint of a name -- declare one of them",
583 $table_name, $earlier->[1], $rel_name, join(', ', @$self_cols), $fk_name);
584 }
585 149 $fk_seen{$table_name}{$fk_name} = [ $fk_key, $rel_name ];
586 149 push @$constraints, { table => $table_name, name => $fk_name,
587 label => "foreign key of relationship '$rel_name'", columns => $self_cols };
588  
589 push @cascade, {
590 table => $table_name,
591 label => "relationship '$rel_name'",
592 from_cols => $self_cols,
593 to_table => $f_table,
594 to_cols => $foreign_cols,
595 on_delete => _fk_action_word('ON DELETE', $attrs->{on_delete}, $rel_name, $table_name) // 'NO ACTION',
596 149 ▶ on_update => _fk_action_word('ON UPDATE', $attrs->{on_update}, $rel_name, $table_name) // 'NO ACTION',
Condition100%4/4
_fk_action_word("ON DELETE", $attrs->{"on_delete"}, $rel_name, $table_name) // "NO ACTION"
l!l
49100
_fk_action_word("ON UPDATE", $attrs->{"on_update"}, $rel_name, $table_name) // "NO ACTION"
l!l
12137
MC/DC100%1/1
_fk_action_word("ON DELETE", $attrs->{"on_delete"}, $rel_name, $table_name) // "NO ACTION"
_fk_action_word("ON DELETE", $attrs->{"on_delete"}, $rel_name, $table_name)
MC/DC100%1/1
_fk_action_word("ON UPDATE", $attrs->{"on_update"}, $rel_name, $table_name) // "NO ACTION"
_fk_action_word("ON UPDATE", $attrs->{"on_update"}, $rel_name, $table_name)
Truth table100%2/2
A
A= _fk_action_word("ON DELETE", $attrs->{"on_delete"}, $rel_name, $table_name)
Aresult
11
00
Truth table100%2/2
A
A= _fk_action_word("ON UPDATE", $attrs->{"on_update"}, $rel_name, $table_name)
Aresult
11
00
597 };
598  
599 push @stmts, sprintf
600 'ALTER TABLE %s ADD CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s (%s)%s;',
601 _quote_table($table_name), _quote_ident($fk_name),
602 171 join(', ', map { _quote_ident($_) } @$self_cols),
603 _quote_table($f_table),
604 149 join(', ', map { _quote_ident($_) } @$foreign_cols),
605 $actions;
606 }
607 }
608 101 _check_cascade_paths(\@cascade);
609 90 return @stmts;
610 }
611  
612 # What SQL Server refuses with Msg 1785 ("may cause cycles or multiple cascade
613 # paths"), found before the server is asked. Measured on SQL Server 2022
614 # (t/95-ddl-refusals-names-live.t); NO ACTION is never refused.
615 #
616 # An FK whose action is CASCADE, SET NULL or SET DEFAULT hands an event of its
617 # parent on to its child: a DELETE of the parent either deletes the child
618 # (CASCADE) or updates the child's key columns (SET NULL / SET DEFAULT); an
619 # UPDATE of a referenced column updates the child's key columns. An update of
620 # columns passes on only through the FKs that reference one of those columns.
621 # From every table and each of the two events (delete, update) the chain of
622 # those hand-overs is followed, and refused is
623 #
624 # - a foreign key that references its own table with any action (SET NULL
625 # as well: measured), and a chain that comes back to a key it went
626 # through (a cycle);
627 # - a table reached by two different chains from one start (two keys from one
628 # table to the same parent with actions, or a diamond: also when one of the
629 # chains ends in SET NULL).
630 #
631 # (The table the chain starts on counts as reached once already.)
632 #
633 # Not refused, measured: two keys to one parent when one of them is NO ACTION;
634 # DELETE CASCADE on one key and UPDATE CASCADE on the other (other events); a
635 # cycle of SET NULL keys whose update reaches no referenced column; a diamond
636 # whose legs are SET NULL on columns nothing references.
637 #
638 # $fks: [ { table, label, from_cols, to_table, to_cols, on_delete, on_update } ]
639 # with the actions as words ('NO ACTION' for none). The label names the key in
640 # the message (the relationship, or the constraint of a diff).
641 120 ▶sub _check_cascade_paths {
Subroutine _check_cascade_paths: called CC=8 SCAR=20.8
642 120 my ($fks) = @_;
643 120 my %by_parent;
644 120 push @{ $by_parent{ $_->{to_table} } }, $_ for @$fks;
645  
646 12 ▶ my $refuse = sub {
Subroutine __ANON__: called CC=1 SCAR=0.0
647 12 my ($fk, $why) = @_;
648 DBIO::Exception->throw(sprintf
649 "DBIO::MSSQL: table '%s': foreign key (%s) to '%s' (%s) is refused: SQL "
650 . "Server rejects it with Msg 1785 (%s). Declare NO ACTION on one of the "
651 . "keys involved, or is_foreign_key_constraint => 0",
652 12 $fk->{table}, join(', ', @{ $fk->{from_cols} }), $fk->{to_table},
653 12 $fk->{label}, $why);
654 120 };
655  
656 # a key that references its own table with an action
657 120 for my $fk (@$fks) {
658 169 ▶ next unless $fk->{table} eq $fk->{to_table};
Branch: 1 branch
BranchTrueFalseTotal
unless $fk->{"table"} eq $fk->{"to_table"} 158 11 169
659 11 ▶ next if $fk->{on_delete} eq 'NO ACTION' && $fk->{on_update} eq 'NO ACTION';
Branch: 1 branch
BranchTrueFalseTotal
if $fk->{"on_delete"} eq "NO ACTION" and $fk->{"on_update"} eq "NO ACTION" 8 3 11
Condition100%3/3
$fk->{"on_delete"} eq "NO ACTION" and $fk->{"on_update"} eq "NO ACTION"
!ll&&!rl&&r
218
MC/DC100%2/2
$fk->{"on_delete"} eq "NO ACTION" and $fk->{"on_update"} eq "NO ACTION"
$fk->{"on_delete"} eq "NO ACTION" $fk->{"on_update"} eq "NO ACTION"
Truth table100%3/3
A and B
A= $fk->{"on_delete"} eq "NO ACTION"B= $fk->{"on_update"} eq "NO ACTION"
ABresult
0X0
100
111
660 3 $refuse->($fk, 'a table cannot cascade onto itself');
661 }
662  
663 117 for my $root (sort keys %by_parent) {
664 113 for my $event ('delete', 'update') {
665 # the table the event starts on counts as reached: a chain that comes
666 # back to it (a SET NULL key back to a table whose rows a CASCADE
667 # deletes) is refused too, measured
668 218 my %reached = ($root => 1);
669 218 my $walk;
670 267 ▶ $walk = sub {
Subroutine __ANON__: called CC=13 SCAR=25.7
671 267 my ($table, $ev, $cols, $path) = @_;
672 267 for my $fk (sort { $a->{table} cmp $b->{table}
673 191 ▶ || join(',', @{ $a->{from_cols} }) cmp join(',', @{ $b->{from_cols} }) }
Condition66%2/3
$DBIO::MSSQL::DDL::a->{'table'} cmp $DBIO::MSSQL::DDL::b->{'table'} || join(',', @{$DBIO::MSSQL::DDL::a->{'from_cols'};}) cmp join(',', @{$DBIO::MSSQL::DDL::b->{'from_cols'};})
l!l&&r!l&&!r
168230
MC/DC0%0/2
$DBIO::MSSQL::DDL::a->{'table'} cmp $DBIO::MSSQL::DDL::b->{'table'} || join(',', @{$DBIO::MSSQL::DDL::a->{'from_cols'};}) cmp join(',', @{$DBIO::MSSQL::DDL::b->{'from_cols'};})
$DBIO::MSSQL::DDL::a->{'table'} cmp $DBIO::MSSQL::DDL::b->{'table'} join(',', @{$DBIO::MSSQL::DDL::a->{'from_cols'};}) cmp join(',', @{$DBIO::MSSQL::DDL::b->{'from_cols'};})
Truth table66%2/3
A || B
A= $DBIO::MSSQL::DDL::a->{'table'} cmp $DBIO::MSSQL::DDL::b->{'table'}B= join(',', @{$DBIO::MSSQL::DDL::a->{'from_cols'};}) cmp join(',', @{$DBIO::MSSQL::DDL::b->{'from_cols'};})
ABresult
1X1
011
000
674 267 ▶ @{ $by_parent{$table} || [] }) {
Condition100%2/2
$by_parent{$table} || []
l!l
24027
MC/DC100%1/1
$by_parent{$table} || []
$by_parent{$table}
Truth table100%2/2
A
A= $by_parent{$table}
Aresult
11
00
675 359 my $action;
676 359 ▶ if ($ev eq 'delete') {
Branch: 1 branch
BranchTrueFalseTotal
if ($ev eq 'delete') { } 171 188 359
677 171 $action = $fk->{on_delete};
678 } else {
679 188 my %hit = map { $_ => 1 } @{ $fk->{to_cols} };
680 188 ▶ next if $cols && !grep { $hit{$_} } @$cols;
Branch: 1 branch
BranchTrueFalseTotal
if $cols and not grep({$hit{$_};} @$cols) 41 147 188
Condition66%2/3
$cols and not grep({$hit{$_};} @$cols)
!ll&&!rl&&r
147041
MC/DC50%1/2
$cols and not grep({$hit{$_};} @$cols)
$cols not grep({$hit{$_};} @$cols)
Truth table66%2/3
A and not(B)
A= $colsB= not grep({$hit{$_};} @$cols)
ABresult
0X0
100
111
681 147 $action = $fk->{on_update};
682 }
683 318 ▶ next if $action eq 'NO ACTION';
Branch: 1 branch
BranchTrueFalseTotal
if $action eq "NO ACTION" 260 58 318
684 $refuse->($fk, 'a cycle of cascading keys')
685 58 ▶ if grep { $_ == $fk } @$path;
Branch: 1 branch
BranchTrueFalseTotal
if grep {$_ == $fk;} @$path 0 58 58
686 $refuse->($fk, $fk->{table} eq $root
687 ? "the chain comes back to table '$root'"
688 : "more than one cascading path reaches table '$fk->{table}' "
689 . "from table '$root'")
690 58 ▶ if $reached{ $fk->{table} }++;
Branch: 2 branches
BranchTrueFalseTotal
if $reached{$fk->{"table"}}++ 9 49 58
$fk->{'table'} eq $root ? : 3 6 9
691 $walk->($fk->{table},
692 ($ev eq 'delete' && $action eq 'CASCADE') ? 'delete' : 'update',
693 49 ▶ $fk->{from_cols}, [ @$path, $fk ]);
Branch: 1 branch
BranchTrueFalseTotal
$ev eq 'delete' && $action eq 'CASCADE' ? : 27 22 49
Condition100%3/3
$ev eq "delete" and $action eq "CASCADE"
!ll&&!rl&&r
91327
MC/DC100%2/2
$ev eq "delete" and $action eq "CASCADE"
$ev eq "delete" $action eq "CASCADE"
Truth table100%3/3
A and B
A= $ev eq "delete"B= $action eq "CASCADE"
ABresult
0X0
100
111
694 }
695 218 };
696 218 $walk->($root, $event, undef, []);
697 }
698 }
699 108 return;
700 }
701  
702 # The default as SQL. A scalar reference is an expression and written as it
703 # is; a number is written bare; anything else is a string literal -- national
704 # (N'...') for a national type, the quote doubled. A digit string with a
705 # leading zero is a string, not the number it looks like.
706 #
707 # A string that is no number on a numeric or bit type (int, bigint, smallint,
708 # tinyint, decimal, numeric, float, real, money, smallmoney, bit) is refused
709 # (karr k71): written as a literal, the server converts at CREATE TABLE or
710 # not, and a default such as 'GETDATE()' meant as an expression fails only at
711 # the first INSERT. The same holds for date/time types (datetime, datetime2,
712 # smalldatetime, date, time, datetimeoffset), measured live in t/99: the server
713 # takes any string at CREATE TABLE and fails at the INSERT (Msg 22007/22018).
714 # A string there must begin like a date literal (2020-01-01, 20200101) or, for
715 # time and the datetime types, a time (hh:mm[:ss[.f]]); anything else is
716 # refused.
717 102 ▶sub _default_literal {
Subroutine _default_literal: called CC=21 SCAR=30.7
718 102 my ($dv, $type, $table_name, $col_name) = @_;
719 102 ▶ return $$dv if ref $dv eq 'SCALAR';
Branch: 1 branch
BranchTrueFalseTotal
if ref $dv eq "SCALAR" 13 89 102
720 # true and false are the booleans of the family; a bit column holds 1 and 0
721 89 ▶ return $dv =~ /true/i ? 1 : 0
Branch: 2 branches
BranchTrueFalseTotal
if $type =~ /\Abit\b/i and $dv =~ /\A\s*(?:true|false)\s*\z/i 18 71 89
$dv =~ /true/i ? : 9 9 18
Condition100%3/3
$type =~ /\Abit\b/i and $dv =~ /\A\s*(?:true|false)\s*\z/i
!ll&&!rl&&r
67418
MC/DC100%2/2
$type =~ /\Abit\b/i and $dv =~ /\A\s*(?:true|false)\s*\z/i
$type =~ /\Abit\b/i $dv =~ /\A\s*(?:true|false)\s*\z/i
Truth table100%3/3
A and B
A= $type =~ /\Abit\b/iB= $dv =~ /\A\s*(?:true|false)\s*\z/i
ABresult
0X0
100
111
722 if $type =~ /\Abit\b/i && $dv =~ /\A\s*(?:true|false)\s*\z/i;
723 71 ▶ return $dv if $dv =~ /\A[+-]?(?:0|[1-9][0-9]*)(?:\.[0-9]+)?\z/;
Branch: 1 branch
BranchTrueFalseTotal
if $dv =~ /\A[+-]?(?:0|[1-9][0-9]*)(?:\.[0-9]+)?\z/ 12 59 71
724 59 ▶ if ($type =~ /\A(?:int|integer|bigint|smallint|tinyint|bit)\b/i
Branch: 1 branch
BranchTrueFalseTotal
if ($type =~ /\A(?:int|integer|bigint|smallint|tinyint|bit)\b/i and $dv =~ /\A\s*[+-]?(?:[0-9]+\.?[0-9]*|\.[0-9]+)[eE][+-]?[0-9]+\s*\z/) 5 54 59
Condition100%3/3
$type =~ /\A(?:int|integer|bigint|smallint|tinyint|bit)\b/i and $dv =~ /\A\s*[+-]?(?:[0-9]+\.?[0-9]*|\.[0-9]+)[eE][+-]?[0-9]+\s*\z/
!ll&&!rl&&r
4685
MC/DC100%2/2
$type =~ /\A(?:int|integer|bigint|smallint|tinyint|bit)\b/i and $dv =~ /\A\s*[+-]?(?:[0-9]+\.?[0-9]*|\.[0-9]+)[eE][+-]?[0-9]+\s*\z/
$type =~ /\A(?:int|integer|bigint|smallint|tinyint|bit)\b/i $dv =~ /\A\s*[+-]?(?:[0-9]+\.?[0-9]*|\.[0-9]+)[eE][+-]?[0-9]+\s*\z/
Truth table100%3/3
A and B
A= $type =~ /\A(?:int|integer|bigint|smallint|tinyint|bit)\b/iB= $dv =~ /\A\s*[+-]?(?:[0-9]+\.?[0-9]*|\.[0-9]+)[eE][+-]?[0-9]+\s*\z/
ABresult
0X0
100
111
725 && $dv =~ /\A\s*[+-]?(?:[0-9]+\.?[0-9]*|\.[0-9]+)[eE][+-]?[0-9]+\s*\z/) {
726 5 ▶ DBIO::Exception->throw(sprintf
Condition50%2/4
$col_name // "?"
l!l
50
$table_name // "?"
l!l
50
MC/DC0%0/1
$col_name // "?"
$col_name
MC/DC0%0/1
$table_name // "?"
$table_name
Truth table50%1/2
A
A= $col_name
Aresult
11
00
Truth table50%1/2
A
A= $table_name
Aresult
11
00
727 "DBIO::MSSQL: default for column '%s' of table '%s' is refused: '%s' is "
728 . "written with an exponent, which the server does not take as an integer "
729 . "for column type %s; write the number out (%s)",
730 $col_name // '?', $table_name // '?', $dv, $type,
731 sprintf('%.0f', $dv));
732 }
733 54 ▶ if ($type =~ /\A(?:int|integer|bigint|smallint|tinyint|decimal|numeric|float|real|money|smallmoney|bit)\b/i
Branch: 1 branch
BranchTrueFalseTotal
if ($type =~ /\A(?:int|integer|bigint|smallint|tinyint|decimal|numeric|float|real|money|smallmoney|bit)\b/i and not $dv =~ /\A\s*[+-]?(?:[0-9]+\.?[0-9]*|\.[0-9]+)(?:[eE][+-]?[0-9]+)?\s*\z/) 14 40 54
Condition100%3/3
$type =~ /\A(?:int|integer|bigint|smallint|tinyint|decimal|numeric|float|real|money|smallmoney|bit)\b/i and not $dv =~ /\A\s*[+-]?(?:[0-9]+\.?[0-9]*|\.[0-9]+)(?:[eE][+-]?[0-9]+)?\s*\z/
!ll&&!rl&&r
39114
MC/DC100%2/2
$type =~ /\A(?:int|integer|bigint|smallint|tinyint|decimal|numeric|float|real|money|smallmoney|bit)\b/i and not $dv =~ /\A\s*[+-]?(?:[0-9]+\.?[0-9]*|\.[0-9]+)(?:[eE][+-]?[0-9]+)?\s*\z/
$type =~ /\A(?:int|integer|bigint|smallint|tinyint|decimal|numeric|float|real|money|smallmoney|bit)\b/i not $dv =~ /\A\s*[+-]?(?:[0-9]+\.?[0-9]*|\.[0-9]+)(?:[eE][+-]?[0-9]+)?\s*\z/
Truth table100%3/3
A and not(B)
A= $type =~ /\A(?:int|integer|bigint|smallint|tinyint|decimal|numeric|float|real|money|smallmoney|bit)\b/iB= not $dv =~ /\A\s*[+-]?(?:[0-9]+\.?[0-9]*|\.[0-9]+)(?:[eE][+-]?[0-9]+)?\s*\z/
ABresult
0X0
100
111
734 && $dv !~ /\A\s*[+-]?(?:[0-9]+\.?[0-9]*|\.[0-9]+)(?:[eE][+-]?[0-9]+)?\s*\z/) {
735 14 ▶ DBIO::Exception->throw(sprintf
Condition50%2/4
$col_name // "?"
l!l
140
$table_name // "?"
l!l
140
MC/DC0%0/1
$col_name // "?"
$col_name
MC/DC0%0/1
$table_name // "?"
$table_name
Truth table50%1/2
A
A= $col_name
Aresult
11
00
Truth table50%1/2
A
A= $table_name
Aresult
11
00
736 "DBIO::MSSQL: default for column '%s' of table '%s' is refused: the "
737 . "string '%s' is a literal, and column type %s is not a string type; "
738 . "an expression is written as a scalar reference (\\'GETDATE()')",
739 $col_name // '?', $table_name // '?', $dv, $type);
740 }
741 40 ▶ if ($type =~ /\A(?:datetime2|datetimeoffset|datetime|smalldatetime|date|time)\b/i) {
Branch: 1 branch
BranchTrueFalseTotal
if ($type =~ /\A(?:datetime2|datetimeoffset|datetime|smalldatetime|date|time)\b/i) 35 5 40
742 35 my $date = qr/[0-9]{4}-[0-9]{2}-[0-9]{2}|[0-9]{8}/;
743 35 my $time = qr/[0-9]{1,2}:[0-9]{2}(?::[0-9]{2}(?:\.[0-9]+)?)?/;
744 35 ▶ my $ok = $type =~ /\Adate\b/i ? $dv =~ /\A\s*$date(?:[T ]|\s*\z)/
Branch: 2 branches
BranchTrueFalseTotal
$type =~ /\Adate\b/i ? : 5 30 35
$type =~ /\Atime\b/i ? : 6 24 30
745 : $type =~ /\Atime\b/i ? $dv =~ /\A\s*$time/
746 : $dv =~ /\A\s*(?:$date(?:[T ]|\s*\z)|$time)/;
747 35 ▶ DBIO::Exception->throw(sprintf
Branch: 1 branch
BranchTrueFalseTotal
unless $ok 26 9 35
Condition50%2/4
$col_name // "?"
l!l
260
$table_name // "?"
l!l
260
MC/DC0%0/1
$col_name // "?"
$col_name
MC/DC0%0/1
$table_name // "?"
$table_name
Truth table50%1/2
A
A= $col_name
Aresult
11
00
Truth table50%1/2
A
A= $table_name
Aresult
11
00
748 "DBIO::MSSQL: default for column '%s' of table '%s' is refused: the "
749 . "string '%s' does not look like a date or time literal for column type "
750 . "%s, and the server would take it at CREATE TABLE and fail at the first "
751 . "INSERT; an expression is written as a scalar reference (\\'GETDATE()')",
752 $col_name // '?', $table_name // '?', $dv, $type) unless $ok;
753 }
754 14 (my $q = $dv) =~ s/'/''/g;
755 14 ▶ my $n = $type =~ /\A(?:nchar|nvarchar|ntext)\b/i ? 'N' : '';
Branch: 1 branch
BranchTrueFalseTotal
$type =~ /\A(?:nchar|nvarchar|ntext)\b/i ? : 4 10 14
756 14 return "$n'$q'";
757 }
758  
759 1742 ▶sub _resolve_table_name {
Subroutine _resolve_table_name: called CC=5 SCAR=16.2
760 1742 my ($name, $kind) = @_;
761 1742 ▶ return _check_table_name($name, $kind) unless ref $name;
Branch: 1 branch
BranchTrueFalseTotal
unless ref $name 1673 69 1742
762 69 ▶ return undef unless ref $name eq 'SCALAR';
Branch: 1 branch
BranchTrueFalseTotal
unless ref $name eq "SCALAR" 0 69 69
763 69 my $v = $$name;
764 69 ▶ return undef unless defined $v;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $v 0 69 69
765 69 ▶ return $v if $v =~ /\A\w+\z/;
Branch: 1 branch
BranchTrueFalseTotal
if $v =~ /\A\w+\z/ 57 12 69
766 12 return undef;
767 }
768  
769 177 ▶sub _topo_sort_sources {
Subroutine _topo_sort_sources: called CC=23 SCAR=31.6
770 177 my ($schema) = @_;
771  
772 177 my %deps;
773 my %by_table;
774 177 my @sources = sort $schema->sources;
775  
776 177 for my $name (@sources) {
777 372 my $s = $schema->source($name);
778 372 ▶ next if _is_virtual_view($s);
Branch: 1 branch
BranchTrueFalseTotal
if _is_virtual_view($s) 7 365 372
779 365 ▶ my $t = _resolve_table_name($s->name, _is_view($s) ? 'view' : 'table');
Branch: 1 branch
BranchTrueFalseTotal
_is_view($s) ? : 5 360 365
780 361 ▶ next unless defined $t;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $t 3 358 361
781 358 ▶ $by_table{$t} //= $name;
Condition66%2/3
$by_table{$t} //= $name
l!l&&r!l&&!r
63520
MC/DC0%0/2
$by_table{$t} //= $name
$by_table{$t} $name
Truth table66%2/3
A //= B
A= $by_table{$t}B= $name
ABresult
1X1
011
000
782 }
783  
784 173 for my $name (@sources) {
785 368 my $s = $schema->source($name);
786 368 ▶ next if _is_virtual_view($s);
Branch: 1 branch
BranchTrueFalseTotal
if _is_virtual_view($s) 7 361 368
787 361 ▶ next unless defined _resolve_table_name($s->name, _is_view($s) ? 'view' : 'table');
Branch: 2 branches
BranchTrueFalseTotal
unless defined _resolve_table_name($s->name, _is_view($s) ? "view" : "table") 3 358 361
_is_view($s) ? : 5 356 361
788 358 ▶ $deps{$name} ||= {};
Condition50%1/2
$deps{$name} ||= {}
l!l
0358
MC/DC0%0/1
$deps{$name} ||= {}
$deps{$name}
Truth table50%1/2
A
A= $deps{$name}
Aresult
11
00
789 358 for my $rel ($s->relationships) {
790 438 my $info = $s->relationship_info($rel);
791 next unless $info && $info->{attrs}
792 438 ▶ && $info->{attrs}{is_foreign_key_constraint};
Branch: 1 branch
BranchTrueFalseTotal
unless $info and $info->{"attrs"} and $info->{"attrs"}{"is_foreign_key_constraint"} 229 209 438
Condition50%3/6
$info and $info->{"attrs"} and $info->{"attrs"}{"is_foreign_key_constraint"}
!ll&&!rl&&r
0229209
$info and $info->{"attrs"}
!ll&&!rl&&r
00438
MC/DC33%1/3
$info and $info->{"attrs"} and $info->{"attrs"}{"is_foreign_key_constraint"}
$info $info->{"attrs"} $info->{"attrs"}{"is_foreign_key_constraint"}
Truth table50%2/4
A and B and C
A= $infoB= $info->{"attrs"}C= $info->{"attrs"}{"is_foreign_key_constraint"}
ABCresult
0XX0
10X0
1100
1111
793 209 my $foreign = $info->{class};
794 209 my $fs = eval { $schema->source($foreign) }
795 209 ▶ // eval { $schema->source($foreign =~ s/.*:://r) };
Condition66%2/3
eval { do { $schema->source($foreign) } } // eval { do { $schema->source($foreign =~ s/.*:://r) } }
l!l&&r!l&&!r
20801
MC/DC50%1/2
eval { do { $schema->source($foreign) } } // eval { do { $schema->source($foreign =~ s/.*:://r) } }
eval { do { $schema->source($foreign) } } eval { do { $schema->source($foreign =~ s/.*:://r) } }
Truth table66%2/3
A // B
A= eval { do { $schema->source($foreign) } }B= eval { do { $schema->source($foreign =~ s/.*:://r) } }
ABresult
1X1
011
000
796 209 ▶ next unless $fs && !_is_virtual_view($fs);
Branch: 1 branch
BranchTrueFalseTotal
unless $fs and not _is_virtual_view($fs) 4 205 209
Condition100%3/3
$fs and not _is_virtual_view($fs)
!ll&&!rl&&r
13205
MC/DC100%2/2
$fs and not _is_virtual_view($fs)
$fs not _is_virtual_view($fs)
Truth table100%3/3
A and not(B)
A= $fsB= not _is_virtual_view($fs)
ABresult
0X0
100
111
797 205 ▶ my $ft = _resolve_table_name($fs->name, _is_view($fs) ? 'view' : 'table');
Branch: 1 branch
BranchTrueFalseTotal
_is_view($fs) ? : 3 202 205
798 205 ▶ next unless defined $ft;
Branch: 1 branch
BranchTrueFalseTotal
unless defined $ft 0 205 205
799 205 my $owner = $by_table{$ft};
800 205 ▶ next unless $owner;
Branch: 1 branch
BranchTrueFalseTotal
unless $owner 0 205 205
801 205 ▶ next if $owner eq $name;
Branch: 1 branch
BranchTrueFalseTotal
if $owner eq $name 11 194 205
802 194 $deps{$name}{$owner} = 1;
803 }
804 }
805  
806 173 my @out;
807 my %visited;
808 173 my $visit;
809 513 ▶ $visit = sub {
Subroutine __ANON__: called CC=4 SCAR=13.9
810 513 my ($n) = @_;
811 513 ▶ return if $visited{$n}++;
Branch: 1 branch
BranchTrueFalseTotal
if $visited{$n}++ 145 368 513
812 368 ▶ for my $d (sort keys %{ $deps{$n} || {} }) {
Condition100%2/2
$deps{$n} || {}
l!l
35810
MC/DC100%1/1
$deps{$n} || {}
$deps{$n}
Truth table100%2/2
A
A= $deps{$n}
Aresult
11
00
813 145 $visit->($d);
814 }
815 368 push @out, $n;
816 173 };
817 173 $visit->($_) for @sources;
818 173 return @out;
819 }
820  
821 1391 ▶sub _mssql_column_type {
Subroutine _mssql_column_type: called CC=38 SCAR=36.5
822 1391 my ($info, $table, $column) = @_;
823 1391 ▶ my $type = $info->{data_type} // 'nvarchar';
Condition50%1/2
$info->{"data_type"} // "nvarchar"
l!l
13910
MC/DC0%0/1
$info->{"data_type"} // "nvarchar"
$info->{"data_type"}
Truth table50%1/2
A
A= $info->{"data_type"}
Aresult
11
00
824  
825 # What SQL Server cannot hold is refused here, loudly, rather than written
826 # as SQL the server rejects or silently as another column (karr k71). The
827 # one function serves install_ddl and the diff ops. $table and $column only
828 # name the place in the message.
829 1391 ▶ my $where = join ', ',
Branch: 2 branches
BranchTrueFalseTotal
defined $table ? : 937 454 1391
defined $column ? : 937 454 1391
830 (defined $table ? "table '$table'" : ()),
831 (defined $column ? "column '$column'" : ());
832 1391 ▶ $where = " ($where)" if length $where;
Branch: 1 branch
BranchTrueFalseTotal
if length $where 937 454 1391
833  
834 # The type as the guards below compare it: lower case, blanks folded, a
835 # "(n)" taken off. Without it 'ENUM', 'enum(a,b)', 'bit(8)' and the
836 # spellings of the character types slipped past an exact comparison.
837 1391 my ($base, $param) = _normalize_type($type);
838  
839 1391 ▶ if ($base eq 'enum') {
Branch: 1 branch
BranchTrueFalseTotal
if ($base eq "enum") 9 1382 1391
840 9 DBIO::Exception->throw("DBIO::MSSQL: type 'enum'$where is refused: SQL Server has no enum "
841 . "type. Declare a varchar column (with a CHECK constraint), or "
842 . "another type");
843 }
844 1382 ▶ if ($base eq 'bit' || $base eq 'bool' || $base eq 'boolean') {
Branch: 1 branch
BranchTrueFalseTotal
if ($base eq "bit" or $base eq "bool" or $base eq "boolean") 41 1341 1382
Condition100%6/6
$base eq "bit" or $base eq "bool" or $base eq "boolean"
l!l&&r!l&&!r
3291341
$base eq "bit" or $base eq "bool"
l!l&&r!l&&!r
2391350
MC/DC100%3/3
$base eq "bit" or $base eq "bool" or $base eq "boolean"
$base eq "bit" $base eq "bool" $base eq "boolean"
Truth table100%4/4
A or B or C
A= $base eq "bit"B= $base eq "bool"C= $base eq "boolean"
ABCresult
1XX1
01X1
0011
0000
845 41 ▶ my $n = $param // (ref $info->{size} eq 'ARRAY' ? $info->{size}[0] : $info->{size});
Branch: 1 branch
BranchTrueFalseTotal
ref $info->{'size'} eq 'ARRAY' ? : 0 38 38
Condition100%3/3
$param // (ref $info->{"size"} eq "ARRAY" ? $info->{"size"}[0] : $info->{"size"})
l!l&&r!l&&!r
3830
MC/DC100%2/2
$param // (ref $info->{"size"} eq "ARRAY" ? $info->{"size"}[0] : $info->{"size"})
$param (ref $info->{"size"} eq "ARRAY" ? $info->{"size"}[0] : $info->{"size"})
Truth table100%3/3
A // B
A= $paramB= (ref $info->{"size"} eq "ARRAY" ? $info->{"size"}[0] : $info->{"size"})
ABresult
1X1
011
000
846 41 ▶ DBIO::Exception->throw("DBIO::MSSQL: size '$n' on type '$base'$where is refused: bit has "
Branch: 1 branch
BranchTrueFalseTotal
if defined $n and $n ne 1 8 33 41
Condition100%3/3
defined $n and $n ne 1
!ll&&!rl&&r
3038
MC/DC100%2/2
defined $n and $n ne 1
defined $n $n ne 1
Truth table100%3/3
A and B
A= defined $nB= $n ne 1
ABresult
0X0
100
111
847 . "no length in SQL Server (a single 0/1 value). Declare it without "
848 . "a size, or use a binary or integer type for wider values")
849 if defined $n and $n ne '1';
850 33 $type = $base;
851 }
852  
853 # the SQL standard spellings of the character and binary types: the native
854 # type, and the length they were given stays
855 1374 ▶ if (my $native = $TYPE_ALIAS{$base}) {
Branch: 1 branch
BranchTrueFalseTotal
if (my $native = $TYPE_ALIAS{$base}) 35 1339 1374
856 35 $type = $native;
857 35 ▶ if (defined $param) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $param) 12 23 35
858 # a parameter is a length or max; anything else (a precision and scale,
859 # a word) was thrown away and the bare type, one character, written
860 12 ▶ DBIO::Exception->throw("DBIO::MSSQL: type '$info->{data_type}'$where is "
Branch: 1 branch
BranchTrueFalseTotal
unless $param =~ /\A(?:\d+|max)\z/i 2 10 12
861 . "refused: '$param' is neither a length nor max")
862 unless $param =~ /\A(?:\d+|max)\z/i;
863 10 ▶ my $plen = lc $param eq 'max' ? -1 : $param + 0;
Branch: 1 branch
BranchTrueFalseTotal
lc $param eq 'max' ? : 2 8 10
864 10 ▶ my $size = ref $info->{size} eq 'ARRAY' ? $info->{size}[0] : $info->{size};
Branch: 1 branch
BranchTrueFalseTotal
ref $info->{'size'} eq 'ARRAY' ? : 0 10 10
865 10 ▶ if (defined $size) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $size) { } 5 5 10
866 5 ▶ DBIO::Exception->throw("DBIO::MSSQL: type '$info->{data_type}'$where is "
Branch: 1 branch
BranchTrueFalseTotal
unless "$size" =~ /\A\s*-?\d+\s*\z/ and $size + 0 == $plen 2 3 5
Condition66%2/3
"$size" =~ /\A\s*-?\d+\s*\z/ and $size + 0 == $plen
!ll&&!rl&&r
023
MC/DC50%1/2
"$size" =~ /\A\s*-?\d+\s*\z/ and $size + 0 == $plen
"$size" =~ /\A\s*-?\d+\s*\z/ $size + 0 == $plen
Truth table66%2/3
A and B
A= "$size" =~ /\A\s*-?\d+\s*\z/B= $size + 0 == $plen
ABresult
0X0
100
111
867 . "refused: the type says $param and size says $size; declare one "
868 . "of them")
869 unless "$size" =~ /\A\s*-?\d+\s*\z/ && $size + 0 == $plen;
870 } else {
871 5 $info = { %$info, size => $plen };
872 }
873 }
874 }
875  
876 # Pre-parameterized types pass through (e.g. varchar(255), numeric(10,2)).
877 1370 ▶ return $type if $type =~ /\(.+\)$/;
Branch: 1 branch
BranchTrueFalseTotal
if $type =~ /\(.+\)$/ 22 1348 1370
878  
879 # Portable base types: delegate to the adapter (single source of truth,
880 # shared shape with DBIO::MySQL / DBIO::PostgreSQL). Introspected live
881 # types arrive as dialect names (int, nvarchar, datetime, ...) which are
882 # not base-type names, so they fall through to the alias map below.
883 1348 ▶ if (DBIO::Schema::Type::is_base_type(lc $type)) {
Branch: 1 branch
BranchTrueFalseTotal
if (DBIO::Schema::Type::is_base_type(lc $type)) 663 685 1348
884 663 my $canon = eval { DBIO::Schema::Type::canonical_column('_', $info) };
885 663 ▶ return $ADAPTER->to_native($canon) if $canon;
Branch: 1 branch
BranchTrueFalseTotal
if $canon 663 0 663
886 }
887  
888 # Dialect / alias names only (the eight base-type names are handled
889 # above). Maps legacy and cross-engine spellings to MSSQL natives.
890 685 my %type_map = (
891 # integers
892 tinyint => 'tinyint',
893 smallint => 'smallint',
894 int => 'int',
895 bigint => 'bigint',
896 serial => 'int',
897 bigserial => 'bigint',
898  
899 # floats / decimals
900 real => 'real',
901 float => 'float',
902 'double precision'=> 'float',
903 decimal => 'numeric',
904  
905 # strings
906 string => 'nvarchar',
907 varchar => 'nvarchar',
908 ntext => 'ntext',
909  
910 # booleans
911 bool => 'bit',
912  
913 # blobs
914 bytea => 'varbinary',
915 tinyblob => 'varbinary',
916 mediumblob => 'varbinary',
917 longblob => 'varbinary',
918 binary => 'binary',
919 varbinary => 'varbinary',
920  
921 # temporal
922 date => 'date',
923 time => 'time',
924 datetime => 'datetime',
925 timestamptz => 'datetimeoffset',
926 'timestamp with time zone' => 'datetimeoffset',
927 smalldatetime => 'smalldatetime',
928 );
929  
930 685 ▶ my $mapped = $type_map{ lc $type } // $type;
Condition66%2/3
$type_map{lc $type} // $type
l!l&&r!l&&!r
5291560
MC/DC0%0/2
$type_map{lc $type} // $type
$type_map{lc $type} $type
Truth table66%2/3
A // B
A= $type_map{lc $type}B= $type
ABresult
1X1
011
000
931  
932 # decimal/numeric: size is a [precision, scale] arrayref, not a scalar
933 # length, so it needs its own branch rather than the char-family regex
934 # below. Mirrors DBIO::MSSQL::Adapter::to_native's rendering for the
935 # 'numeric' base type (karr k17).
936 685 ▶ if ($mapped eq 'numeric') {
Branch: 1 branch
BranchTrueFalseTotal
if ($mapped eq "numeric") 3 682 685
937 3 my $size = $info->{size};
938 3 ▶ if (ref $size eq 'ARRAY') {
Branch: 1 branch
BranchTrueFalseTotal
if (ref $size eq "ARRAY") 2 1 3
939 2 my ($p, $s) = @$size;
940 2 ▶ return "numeric($p,$s)" if defined $p && defined $s;
Branch: 1 branch
BranchTrueFalseTotal
if defined $p and defined $s 1 1 2
Condition66%2/3
defined $p and defined $s
!ll&&!rl&&r
011
MC/DC50%1/2
defined $p and defined $s
defined $p defined $s
Truth table66%2/3
A and B
A= defined $pB= defined $s
ABresult
0X0
100
111
941 1 ▶ return "numeric($p)" if defined $p;
Branch: 1 branch
BranchTrueFalseTotal
if defined $p 1 0 1
942 }
943 1 return $mapped;
944 }
945  
946 # Append length to types that take one (char/varchar/binary families).
947 #
948 # Every type here takes exactly ONE scalar length in T-SQL -- char[(n)],
949 # varchar[(n|max)], nchar[(n)], nvarchar[(n|max)], binary[(n)],
950 # varbinary[(n|max)] -- so an arrayref size has one member that can mean
951 # anything: the width. Read it, rather than let the reference numify to its
952 # (always positive) address, pass the guard and stringify into the type name
953 # as nvarchar(ARRAY(0x...)). Same "first member is the width" reading the
954 # numeric branch above has had since karr k17; a size that names no width
955 # ([], [undef]) falls through exactly as the scalar form does (karr k27).
956 #
957 # The widths a caller can state are not one case (karr k28):
958 #
959 # n >= 1 a length. Emitted as given, and tested first so that the shapes
960 # this branch does not judge -- a non-ARRAY reference, a string
961 # that is not a number -- keep behaving exactly as they did before
962 # (form checking for column info is core's, karr k150).
963 # -1 the (max) marker, and the ordinary deploy-path value rather than
964 # an exotic import: INFORMATION_SCHEMA reports the unbounded
965 # variants as CHARACTER_MAXIMUM_LENGTH = -1 and
966 # DBIO::MSSQL::Introspect::Columns passes that straight through as
967 # `size`. It has to be rendered BACK as (max), because omitting the
968 # length does not mean "unbounded" in a T-SQL column definition --
969 # with n omitted it means LENGTH 1 (and 30 in CAST/CONVERT). The
970 # old fallthrough therefore recreated every introspected (max)
971 # column as a one-character column: valid SQL, wrong column, silent
972 # until the first INSERT truncates.
973 # 0, <-1 nothing at all. T-SQL lengths start at 1 and there is no second
974 # marker, so the caller has stated a width that cannot exist. There
975 # is no right rendering to pick -- bare would be the one-character
976 # column this ticket is about -- so refuse, the way
977 # DBIO::MSSQL::Adapter::to_native refuses a base type it has no
978 # native for.
979 #
980 # An ABSENT size (undef, missing, or an arrayref naming no width) keeps
981 # falling through to the bare type: nothing was prescribed, so nothing is
982 # emitted and the dialect default stands. Only a STATED width that cannot
983 # exist is refused -- declining to misrepresent an intent is not the same as
984 # demanding one.
985 #
986 # (max) is not available on every type in the regex, so the -1 is only
987 # honoured for the three that have it (%MAX_LENGTH_TYPE). SQL Server can
988 # never report -1 for char/nchar/binary -- a fixed-length column always
989 # reports its real n -- and neither rendering would be right if it did.
990 682 ▶ if ($mapped =~ /^(?:n?char|n?varchar|binary|varbinary)$/i) {
Branch: 1 branch
BranchTrueFalseTotal
if ($mapped =~ /^(?:n?char|n?varchar|binary|varbinary)$/i) 326 356 682
991 326 ▶ my $size = ref $info->{size} eq 'ARRAY' ? $info->{size}[0] : $info->{size};
Branch: 1 branch
BranchTrueFalseTotal
ref $info->{'size'} eq 'ARRAY' ? : 15 311 326
992 326 ▶ if (defined $size) {
Branch: 1 branch
BranchTrueFalseTotal
if (defined $size) 290 36 326
993 290 ▶ return "$mapped($size)" if $size > 0;
Branch: 1 branch
BranchTrueFalseTotal
if $size > 0 262 28 290
994 28 ▶ if ($size =~ /^\s*[-+]?\d+\s*$/) {
Branch: 1 branch
BranchTrueFalseTotal
if ($size =~ /^\s*[-+]?\d+\s*$/) 28 0 28
995 28 ▶ return "$mapped(max)" if $size == -1 && $MAX_LENGTH_TYPE{lc $mapped};
Branch: 1 branch
BranchTrueFalseTotal
if $size == -1 and $MAX_LENGTH_TYPE{lc $mapped} 19 9 28
Condition100%3/3
$size == -1 and $MAX_LENGTH_TYPE{lc $mapped}
!ll&&!rl&&r
6319
MC/DC100%2/2
$size == -1 and $MAX_LENGTH_TYPE{lc $mapped}
$size == -1 $MAX_LENGTH_TYPE{lc $mapped}
Truth table100%3/3
A and B
A= $size == -1B= $MAX_LENGTH_TYPE{lc $mapped}
ABresult
0X0
100
111
996 DBIO::Exception->throw("DBIO::MSSQL: size '$size' on type "
997 . "'$mapped'$where is refused: not a valid length"
998 9 ▶ . ($MAX_LENGTH_TYPE{lc $mapped}
Branch: 1 branch
BranchTrueFalseTotal
$MAX_LENGTH_TYPE{lc $mapped} ? : 6 3 9
999 ? ' (lengths start at 1; -1 is the (max) marker)'
1000 : " (lengths start at 1; $mapped has no (max) form)"));
1001 }
1002 }
1003 }
1004  
1005 392 return $mapped;
1006 }
1007  
1008 66 1;
1009  
1010 __END__
« MSSQL/Introspect/Columns.pm ↑ Summary MSSQL/Storage.pm »