MSSQL/Introspect/Columns.pm

filter: Statementstmt 98.0% 49 / 50 Branchbran 65.6% 21 / 32 Conditioncond 37.5% 3 / 8 MC/DCmcdc 0.0% 0 / 4 Subroutinesub 100% 3 / 3 Podpod 100% 1 / 1 totaltotal 78.5% 77 / 98 CC 25 SCAR 33.5
CC
25
Cov
81%
fetch
30.6
BEGIN
1.0
BEGIN
1.0
CRAP 28.4
« MSSQL/Diff.pm ↑ Summary MSSQL/DDL.pm »
1 package DBIO::MSSQL::Introspect::Columns;
2 # ABSTRACT: Introspect MSSQL columns
3  
4 28 ▶use strict;
Subroutine BEGIN: called CC=1 SCAR=0.0
5 28 ▶use warnings;
Subroutine BEGIN: called CC=1 SCAR=0.0
6  
7 28 our $VERSION = '0.900007';
8  
9  
10  
11 1 ▶sub fetch {
Subroutine fetch: called CC=25 SCAR=34.2
Pod documented
12 1 my ($class, $dbh, $schema, $tables) = @_;
13 1 ▶ $schema //= 'dbo';
Condition50%1/2
$schema //= "dbo"
l!l
10
MC/DC0%0/1
$schema //= "dbo"
$schema
Truth table50%1/2
A
A= $schema
Aresult
11
00
14 1 my %columns;
15  
16 # Every column below is aliased explicitly and in lower case: an alias is
17 # echoed into the result metadata verbatim, which is the one way to be sure
18 # of the hash keys without depending on the handle's FetchHashKeyName.
19 # INFORMATION_SCHEMA reports its columns UPPERCASE; unaliased, the key case
20 # in the row hash is decided by the transport, not by this code.
21 #
22 # Pull all columns for the schema in one go.
23 1 my $col_sth = $dbh->prepare(q{
24 SELECT table_name AS table_name, column_name AS column_name,
25 data_type AS data_type, is_nullable AS is_nullable,
26 column_default AS column_default,
27 ordinal_position AS ordinal_position,
28 character_maximum_length AS character_maximum_length
29 FROM INFORMATION_SCHEMA.COLUMNS
30 WHERE table_schema = ?
31 ORDER BY table_name, ordinal_position
32 });
33 1 $col_sth->execute($schema);
34  
35 1 ▶ while (my $row = $col_sth->fetchrow_hashref) {
Branch: 1 branch
BranchTrueFalseTotal
if (my $row = $col_sth->fetchrow_hashref) 6 1 7
36 6 ▶ next unless exists $tables->{ $row->{table_name} };
Branch: 1 branch
BranchTrueFalseTotal
unless exists $$tables{$row->{"table_name"}} 0 6 6
37 6 ▶ push @{ $columns{ $row->{table_name} } }, {
Branch: 1 branch
BranchTrueFalseTotal
lc($row->{'is_nullable'} // 'YES') eq 'no' ? : 5 1 6
Condition50%1/2
$row->{"is_nullable"} // "YES"
l!l
60
MC/DC0%0/1
$row->{"is_nullable"} // "YES"
$row->{"is_nullable"}
Truth table50%1/2
A
A= $row->{"is_nullable"}
Aresult
11
00
38 column_name => $row->{column_name},
39 data_type => $row->{data_type},
40 not_null => (lc($row->{is_nullable} // 'YES') eq 'no') ? 1 : 0,
41 default_value => $row->{column_default},
42 is_pk => 0,
43 pk_position => 0,
44 is_identity => 0,
45 is_auto_increment => 0,
46 size => $row->{character_maximum_length},
47 6 };
48 }
49  
50 # Primary-key membership.
51 1 my $pk_sth = $dbh->prepare(q{
52 SELECT kcu.table_name AS table_name, kcu.column_name AS column_name,
53 kcu.ordinal_position AS ordinal_position,
54 tc.constraint_name AS constraint_name
55 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
56 JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
57 ON tc.constraint_name = kcu.constraint_name
58 AND tc.table_schema = kcu.table_schema
59 AND tc.table_name = kcu.table_name
60 WHERE tc.constraint_type = 'PRIMARY KEY'
61 AND tc.table_schema = ?
62 });
63 1 $pk_sth->execute($schema);
64  
65 1 ▶ while (my $row = $pk_sth->fetchrow_hashref) {
Branch: 1 branch
BranchTrueFalseTotal
if (my $row = $pk_sth->fetchrow_hashref) 2 1 3
66 2 ▶ my $list = $columns{ $row->{table_name} } or next;
Branch: 1 branch
BranchTrueFalseTotal
my $list = $columns{$row->{"table_name"}} or next 2 0 2
67 2 for my $col (@$list) {
68 2 ▶ if ($col->{column_name} eq $row->{column_name}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($col->{"column_name"} eq $row->{"column_name"}) 2 0 2
69 2 $col->{is_pk} = 1;
70 2 ▶ $col->{pk_position} = $row->{ordinal_position} || 1;
Condition50%1/2
$row->{'ordinal_position'} || 1
l!l
20
MC/DC0%0/1
$row->{'ordinal_position'} || 1
$row->{'ordinal_position'}
Truth table50%1/2
A
A= $row->{'ordinal_position'}
Aresult
11
00
71 2 $col->{pk_constraint_name} = $row->{constraint_name};
72 2 last;
73 }
74 }
75 }
76  
77 # Is the index behind the primary key clustered? (karr k74)
78 1 my $cl_sth = $dbh->prepare(q{
79 SELECT t.name AS table_name, i.type_desc AS index_kind
80 FROM sys.indexes i
81 JOIN sys.tables t ON i.object_id = t.object_id
82 JOIN sys.schemas s ON t.schema_id = s.schema_id
83 WHERE i.is_primary_key = 1 AND s.name = ?
84 });
85 1 $cl_sth->execute($schema);
86 1 ▶ while (my $row = $cl_sth->fetchrow_hashref) {
Branch: 1 branch
BranchTrueFalseTotal
if (my $row = $cl_sth->fetchrow_hashref) 2 1 3
87 2 ▶ my $list = $columns{ $row->{table_name} } or next;
Branch: 1 branch
BranchTrueFalseTotal
my $list = $columns{$row->{"table_name"}} or next 0 2 2
88 $_->{is_pk} && ($_->{pk_clustered} = lc($row->{index_kind} // '') eq 'clustered' ? 1 : 0)
89 0 ▶ for @$list;
Branch: 2 branches
BranchTrueFalseTotal
$_->{"is_pk"} and $_->{"pk_clustered"} = lc($row->{"index_kind"} // "") eq "clustered" ? 1 : 0 0 0 0
lc($row->{'index_kind'} // '') eq 'clustered' ? : 0 0 0
Condition0%0/2
$row->{"index_kind"} // ""
l!l
00
MC/DC0%0/1
$row->{"index_kind"} // ""
$row->{"index_kind"}
Truth table0%0/2
A
A= $row->{"index_kind"}
Aresult
11
00
90 }
91  
92 # Identity columns (auto-increment).
93 1 my $id_sth = $dbh->prepare(q{
94 SELECT c.table_name AS table_name, c.column_name AS column_name
95 FROM INFORMATION_SCHEMA.COLUMNS c
96 JOIN sys.columns sc ON c.column_name = sc.name AND c.table_name = OBJECT_NAME(sc.object_id)
97 AND c.table_schema = OBJECT_SCHEMA_NAME(sc.object_id)
98 WHERE c.table_schema = ? AND sc.is_identity = 1
99 });
100 1 $id_sth->execute($schema);
101  
102 1 ▶ while (my $row = $id_sth->fetchrow_hashref) {
Branch: 1 branch
BranchTrueFalseTotal
if (my $row = $id_sth->fetchrow_hashref) 2 1 3
103 2 ▶ my $list = $columns{ $row->{table_name} } or next;
Branch: 1 branch
BranchTrueFalseTotal
my $list = $columns{$row->{"table_name"}} or next 2 0 2
104 2 for my $col (@$list) {
105 2 ▶ if ($col->{column_name} eq $row->{column_name}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($col->{"column_name"} eq $row->{"column_name"}) 2 0 2
106 2 $col->{is_identity} = 1;
107 2 $col->{is_auto_increment} = 1;
108 2 last;
109 }
110 }
111 }
112  
113 # DEFAULT constraint names. INFORMATION_SCHEMA.COLUMNS reports the default
114 # EXPRESSION (column_default) but not the name of the object holding it, and
115 # T-SQL drops a default by name -- so the name has to come from sys.
116 #
117 # Every column is aliased explicitly and in lower case: an alias is echoed
118 # into the result metadata verbatim, which is the one way to be sure of the
119 # hash keys below without depending on the handle's FetchHashKeyName.
120 1 my $df_sth = $dbh->prepare(q{
121 SELECT o.name AS table_name,
122 c.name AS column_name,
123 dc.name AS constraint_name
124 FROM sys.default_constraints dc
125 JOIN sys.columns c
126 ON c.object_id = dc.parent_object_id
127 AND c.column_id = dc.parent_column_id
128 JOIN sys.objects o
129 ON o.object_id = dc.parent_object_id
130 JOIN sys.schemas s
131 ON s.schema_id = o.schema_id
132 WHERE s.name = ?
133 });
134 1 $df_sth->execute($schema);
135  
136 1 ▶ while (my $row = $df_sth->fetchrow_hashref) {
Branch: 1 branch
BranchTrueFalseTotal
if (my $row = $df_sth->fetchrow_hashref) 1 1 2
137 1 ▶ my $list = $columns{ $row->{table_name} } or next;
Branch: 1 branch
BranchTrueFalseTotal
my $list = $columns{$row->{"table_name"}} or next 1 0 1
138 1 for my $col (@$list) {
139 2 ▶ if ($col->{column_name} eq $row->{column_name}) {
Branch: 1 branch
BranchTrueFalseTotal
if ($col->{"column_name"} eq $row->{"column_name"}) 1 1 2
140 1 $col->{default_constraint_name} = $row->{constraint_name};
141 1 last;
142 }
143 }
144 }
145  
146 1 return \%columns;
147 }
148  
149 28 1;
150  
151 __END__
« MSSQL/Diff.pm ↑ Summary MSSQL/DDL.pm »