blib/lib/Sybase/Metadata.pm

filter: Statementstmt 66.6% 4 / 6 Branchbran n/a Conditioncond n/a MC/DCmcdc n/a Subroutinesub 100% 2 / 2 Podpod n/a totaltotal 75.0% 6 / 8 CC 1 SCAR 1.4
CC
1
Cov
67%
BEGIN
1.3
BEGIN
1.0
CRAP 1.1
↑ Summary
1 package Sybase::Metadata;
2 1 ▶use DBI;
Subroutine BEGIN: called CC=1 SCAR=0.0
3 0 ▶use DBD::Sybase;
Subroutine BEGIN: called CC=1 SCAR=2.6
4 use strict;
5  
6 # Module to perform get Sybase db and object info
7 # Meant to be used in conjunction with CGI, etc to display info
8 # Contains subroutines to search through metadata by object
9 # type or globally - for present database
10 # Designed to be read-only for version 1.0
11  
12 # Written by B. Michael O'Brien 8/2009
13  
14 # History:
15  
16 # Version 1 - 8/1/2009 Basic insight into metadata provided,
17 # basic search capabilities. More bells and whistles
18 # such as live data to be added later via another module.
19  
20 # Note that this will be constructed as a class using bless so that
21 # we may refer to its instance variables downstream.
22  
23 use Exporter;
24 our $VERSION = 1.00;
25 our @ISA = qw(Exporter);
26 our @EXPORT = qw($dbHandle
27 &new
28 &Initialize
29 &GetDatabases
30 &GetTables
31 &GetProcs
32 &GetViews
33 &GetTriggers
34 &GetRIs
35 &GetIndexes
36 &DescribeTable
37 &DescribeProc
38 &DescribeTrigger
39 &DescribeView
40 &GetUsers
41 &GetLogins
42 &GetGroups
43 &ExtractTableSQL
44 &ExtractViewSQL
45 &ExtractProcSQL
46 &ExtractTriggerSQL
47 &ExtractRISQL
48 &SearchProcNames
49 &SearchProcText
50 &SearchTriggerNames
51 &SearchTriggerText
52 &SearchColumns
53 &SearchTables
54 &SearchViewNames
55 &SearchViewText
56 &SearchIndexes
57 &SearchUsers
58 &SearchGroups
59 );
60  
61 # Global variables
62  
63 my $dbListSQL = '
64 select DBName = d.name,
65 DBID = d.dbid,
66 Owner = l.name,
67 CreateDate = d.crdate
68 from master.dbo.sysdatabases d, master.dbo.syslogins l
69 where d.suid = l.suid
70 order by d.name';
71  
72 my $getTablesSQL = '
73 select Name = o.name,
74 TableOID = o.id,
75 Owner = u.name,
76 CreateDate = o.crdate
77 from sysobjects o, sysusers u
78 where o.type = "U" and
79 o.uid = u.uid
80 order by o.name';
81
82 my $getProcsSQL = '
83 select Name = o.name,
84 ProcOID = o.id,
85 Owner = u.name,
86 CreateDate = o.crdate
87 from sysobjects o, sysusers u
88 where o.type = "P" and
89 o.uid = u.uid
90 order by o.name';
91  
92 my $getViewsSQL = '
93 select Name = o.name,
94 ViewOID = o.id,
95 Owner = u.name,
96 CreateDate = o.crdate
97 from sysobjects o, sysusers u
98 where o.type = "V" and
99 o.uid = u.uid
100 order by o.name';
101  
102 my $getTriggersSQL = '
103 select TriggerName = o1.name,
104 TriggerOID = o1.id,
105 TableName = o2.name,
106 TableOID = o2.id
107 from sysobjects o1, sysobjects o2
108 where o1.type = "TR" and
109 (o1.deltrig = o2.id or
110 o1.instrig = o2.id or
111 o1.updtrig = o2.id)';
112  
113 my $getRISQL = '
114 select Name = o1.name,
115 RIOID = o1.id,
116 FromTable = o2.name,
117 FromTableOID = o2.id,
118 ToTable = o3.name,
119 ToTableOID = o3.id
120 from sysobjects o1, sysobjects o2, sysreferences r, sysobjects o3
121 where o1.type = "RI" and
122 o1.id = r.constrid and
123 o2.id = r.tableid and
124 r.reftabid = o3.id';
125  
126 my $getIndexesSQL = '
127 select Name = i.name,
128 OnTable = o.name,
129 CreateDate = i.crdate
130 from sysindexes i, sysobjects o
131 where i.id = o.id and o.type = "U" and indid = 1';
132  
133 my $describeTableSQL = '
134 select Name = c.name,
135 Type = CASE
136 when t.name in ("char","varchar","binary","varbinary") then t.name + "("+convert(varchar(9),c.length)+")"
137 else t.name
138 END, -- case
139 NullType = CASE
140 when c.status = 0 then "NOT NULL"
141 else "NULL"
142 END -- case
143 from sysobjects o, syscolumns c, systypes t
144 where c.usertype = t.usertype and
145 c.id = o.id and
146 o.name = ?
147 order by c.number';
148  
149 my $getUsersSQL = '
150 select UserName = u1.name,
151 UserID = u1.uid,
152 GroupName = u2.name,
153 GroupID = u1.gid
154 from sysusers u1, sysusers u2
155 where u1.gid *= u2.uid and
156 ((u1.uid < @@mingroupid and u1.uid != 0)
157 or (u1.uid > @@maxgroupid))';
158  
159 my $getLoginsSQL = '
160 select LoginName = name,
161 LoginID = suid,
162 DefaultDB = dbname
163 from master.dbo.syslogins ';
164  
165 my $getGroupsSQL = '
166 select GroupName = name,
167 GroupID = gid
168 from sysusers u
169 where ((u.uid between @@mingroupid and @@maxgroupid) or u.uid = 0)
170 and not exists (select 1 from sysroles r where u.uid = r.lrid)';
171  
172 my $GetMembersSQL = '
173 select UserName = u2.name,
174 UserID = u2.uid
175 from sysusers u1, sysusers u2
176 where u1.name = ? and
177 u1.uid = u2.gid and
178 ((u2.uid < @@mingroupid and u2.uid != 0) or
179 (u2.uid > @@maxgroupid))';
180  
181 my $searchProcNamesSQL = '
182 select ProcName = name,
183 ProcOID = id
184 from sysobjects
185 where type = "P" and name like ?';
186  
187 my $searchProcTextSQL = '
188 select ProcName = o.name,
189 ProcOID = o.id,
190 Snippett = c.text
191 from sysobjects o, syscomments c
192 where o.id = c.id and
193 o.type = "P" and
194 c.text like ?';
195  
196 my $searchTrigNamesSQL = '
197 select TriggerName = o1.name,
198 TriggerOID = o1.id,
199 TableName = o2.name,
200 TableOID = o2.id
201 from sysobjects o1, sysobjects o2
202 where o1.name like ? and
203 o1.type = "TR" and
204 (o1.deltrig = o2.id or
205 o1.instrig = o2.id or
206 o1.updtrig = o2.id)';
207  
208 my $searchTrigTextSQL = '
209 select TriggerName = o1.name,
210 TriggerOID = o1.id,
211 TableName = o2.name,
212 TableOID = o2.id,
213 Snippett = c.text
214 from sysobjects o1, sysobjects o2, syscomments c
215 where o1.type = "TR" and
216 o1.id = c.id and
217 c.text like ? and
218 (o1.deltrig = o2.id or
219 o1.instrig = o2.id or
220 o1.updtrig = o2.id)';
221  
222 my $searchColumnsSQL = '
223 select ColumnName = c.name,
224 TableName = o.name,
225 TableOID = o.id
226 from sysobjects o, syscolumns c
227 where o.id = c.id and
228 o.type = "U" and
229 c.name like ?';
230  
231 my $searchTableNamesSQL = '
232 select TableName = name,
233 TableOID = id
234 from sysobjects
235 where type = "U" and
236 name like ?';
237  
238 my $searchViewNamesSQL = '
239 select ViewName = name,
240 ViewOID = id
241 from sysobjects
242 where type = "V" and
243 name like ?';
244  
245 my $searchViewTextSQL = '
246 select ViewName = o.name,
247 ViewOID = o.id,
248 Snippett = c.text
249 from sysobjects o, syscomments c
250 where o.type = "V" and
251 o.id = c.id and
252 c.text like ?';
253  
254 my $searchUsersSQL = '
255 select UserName = name,
256 UserID = uid
257 from sysusers
258 where name like ? and
259 (uid = 0 OR uid < @@mingroupid OR uid > @@maxgroupid)';
260  
261 my $searchGroupsSQL = '
262 select GroupName = name,
263 GroupID = uid
264 from sysusers
265 where name like ? and
266 uid between @@mingroupid and @@maxgroupid';
267  
268 my $getTrigMetadataSQL = '
269 select constrid = o1.id,
270 FromTable = o2.name,
271 ToTable = o3.name
272 from sysobjects o1, sysobjects o2, sysreferences r, sysobjects o3
273 where o1.type = "RI" and
274 o1.id = r.constrid and
275 o2.id = r.tableid and
276 r.reftabid = o3.id and
277 o1.name = ?';
278  
279 my $getForKeysSQL = '
280 select FoKey = c.name
281 from syscolumns c
282 where exists (select 1 from sysreferences r
283 where constrid = ? and
284 r.tableid = c.id and
285 (c.colid = r.fokey1 or
286 c.colid = r.fokey2 or
287 c.colid = r.fokey3 or
288 c.colid = r.fokey4 or
289 c.colid = r.fokey5 or
290 c.colid = r.fokey6 or
291 c.colid = r.fokey7 or
292 c.colid = r.fokey8 or
293 c.colid = r.fokey9 or
294 c.colid = r.fokey10 or
295 c.colid = r.fokey11 or
296 c.colid = r.fokey12 or
297 c.colid = r.fokey13 or
298 c.colid = r.fokey14 or
299 c.colid = r.fokey15 or
300 c.colid = r.fokey16) )';
301  
302 my $getRefKeysSQL = '
303 select RefKey = c.name
304 from syscolumns c
305 where exists (select 1 from sysreferences r
306 where constrid = ? and
307 r.tableid = c.id and
308 (c.colid = r.refkey1 or
309 c.colid = r.refkey2 or
310 c.colid = r.refkey3 or
311 c.colid = r.refkey4 or
312 c.colid = r.refkey5 or
313 c.colid = r.refkey6 or
314 c.colid = r.refkey7 or
315 c.colid = r.refkey8 or
316 c.colid = r.refkey9 or
317 c.colid = r.refkey10 or
318 c.colid = r.refkey11 or
319 c.colid = r.refkey12 or
320 c.colid = r.refkey13 or
321 c.colid = r.refkey14 or
322 c.colid = r.refkey15 or
323 c.colid = r.refkey16) )';
324  
325 my $searchIndexesSQL = '
326 select IndexName = i.name,
327 TableName = o.name,
328 TableOID = o.id
329 from sysindexes i, sysobjects o
330 where i.id = o.id and
331 o.type = "U" and
332 indid = 1 and
333 i.name like ?';
334  
335  
336 # Set DB Parameters (like initialization)
337  
338 # Methods
339  
340 ####################################################################
341 #
342 # new
343 # + Construct object and return handle
344 #
345 # + Input: None
346 #
347 # + Output: Object handle/pointer
348 #
349 ####################################################################
350 sub new {
351  
352 my $self = {};
353 bless $self;
354  
355 return $self;
356  
357 }
358  
359 ####################################################################
360 #
361 # Initialize
362 # + Initialize desired db connection and return global handle
363 #
364 # + Input: Pointer to hash of DB properties containing:
365 # - Server
366 # - User
367 # - Password
368 # - Database
369 #
370 # + Output: None but initializes db handle to be used internally
371 #
372 ####################################################################
373  
374 sub Initialize {
375  
376 # Initialize user parameters
377 my $self = shift;
378 my $dbPropsPtr = shift;
379 $self->{DBNAME} = $dbPropsPtr->{DATABASE};
380 $self->{SYBASE} = $ENV{SYBASE};
381 $self->{SYBASE_OCS} = $ENV{SYBASE_OCS};
382 $self->{USER} = $dbPropsPtr->{USER};
383 $self->{SERVER} = $dbPropsPtr->{SERVER};
384 $self->{PASSWORD} = $dbPropsPtr->{PASSWORD};
385 $self->{DBHANDLE} = DBI->connect("dbi:Sybase:$self->{SERVER}","$self->{USER}","$self->{PASSWORD}");
386  
387 ($self->{DBHANDLE}->do("use $self->{DBNAME}") != -2)
388 or warn "Cannot switch to $self->{DBNAME}\n";
389  
390 # Ensure Sybase environment variable is defined
391 if (! $self->{SYBASE} =~ /\w/) {
392 warn "You must define SYBASE environment variable\n";
393 return undef;
394 }
395  
396 }
397  
398  
399 ####################################################################
400 #
401 # GetDatabases
402 # + Get a list of all databases and their space usage info
403 # + Input : None
404 # + Output : Ref to array of hashes containing:
405 # DBName
406 # DBID
407 # Owner
408 # CreateDate
409 #
410 #
411 ####################################################################
412  
413 sub GetDatabases {
414  
415 my $self = shift;
416
417 # Based on constant SQL $dbListSQL
418 my $sth = $self->{DBHANDLE}->prepare($dbListSQL);
419  
420 $sth->execute();
421 my @resultsArray;
422 my $rowRef;
423 while ($rowRef = $sth->fetchrow_hashref())
424 {
425 push(@resultsArray,$rowRef);
426 }
427  
428 return \@resultsArray;
429  
430 }
431  
432  
433 ####################################################################
434 #
435 # GetTables
436 # + Get a list of tables in present database, Useful for drill
437 # down to table details
438 # + Input : None
439 # + Output : Ref to array of hashes containing:
440 # Name (name of table)
441 # TableOID
442 # Owner
443 # CreateDate
444 #
445 #
446 ####################################################################
447  
448 sub GetTables {
449  
450 my $self = shift;
451  
452 # Based on constant SQL $getTablesSQL
453 my $sth = $self->{DBHANDLE}->prepare($getTablesSQL);
454  
455 $sth->execute();
456 my @resultsArray;
457 my $rowRef;
458 while ($rowRef = $sth->fetchrow_hashref())
459 {
460 push(@resultsArray,$rowRef);
461 }
462  
463 return \@resultsArray;
464  
465 }
466  
467  
468 ####################################################################
469 #
470 # GetProcs
471 # + Get a list of tables in present database, Useful for drill
472 # down to table details
473 # + Input : None
474 # + Output : Ref to array of hashes containing:
475 # Name (name of proc)
476 # ProcOID
477 # Owner
478 # CreateDate
479 #
480 ####################################################################
481  
482 sub GetProcs {
483  
484 my $self = shift;
485  
486 # Based on constant SQL $getProcsSQL
487 my $sth = $self->{DBHANDLE}->prepare($getProcsSQL);
488  
489 $sth->execute();
490 my @resultsArray;
491 my $rowRef;
492 while ($rowRef = $sth->fetchrow_hashref())
493 {
494 push(@resultsArray,$rowRef);
495 }
496  
497 return \@resultsArray;
498  
499 }
500
501  
502 ####################################################################
503 #
504 # GetViews
505 # + Get a list of views in present database, Useful for drill
506 # down to view details
507 # + Input : None
508 # + Output : Ref to array of hashes containing:
509 # Name (name of view)
510 # ViewOID
511 # Owner
512 # CreateDate
513 #
514 ####################################################################
515  
516 sub GetViews {
517  
518 my $self = shift;
519  
520 # Based on constant SQL $getViewsSQL
521 my $sth = $self->{DBHANDLE}->prepare($getViewsSQL);
522  
523 $sth->execute();
524 my @resultsArray;
525 my $rowRef;
526 while ($rowRef = $sth->fetchrow_hashref())
527 {
528 push(@resultsArray,$rowRef);
529 }
530  
531 return \@resultsArray;
532  
533 }
534  
535
536 ####################################################################
537 #
538 # GetTriggers
539 # + Get a list of views in present database, Useful for drill
540 # down to view details
541 # + Input : None
542 # + Output : Ref to array of hashes containing:
543 # TriggerName
544 # TriggerOID
545 # TableName
546 # TableOID
547 #
548 ####################################################################
549  
550 sub GetTriggers {
551  
552 my $self = shift;
553  
554 # Based on constant SQL $getTriggersSQL
555 my $sth = $self->{DBHANDLE}->prepare($getTriggersSQL);
556  
557 $sth->execute();
558 my @resultsArray;
559 my $rowRef;
560 while ($rowRef = $sth->fetchrow_hashref())
561 {
562 push(@resultsArray,$rowRef);
563 }
564  
565 return \@resultsArray;
566  
567 }
568  
569  
570 ####################################################################
571 #
572 # GetRIs
573 # + Get a list of referential integrities in present database
574 # + Input : None
575 # + Output : Ref to array of hashes containing:
576 # Name (name of referential inegtrity)
577 # RIOID
578 # FromTable
579 # FromTableOID
580 # ToTable
581 # ToTableOID
582 #
583 ####################################################################
584  
585 sub GetRIs {
586  
587 my $self = shift;
588  
589 # Based on constant SQL $getRISQL
590 my $sth = $self->{DBHANDLE}->prepare($getRISQL);
591  
592 $sth->execute();
593 my @resultsArray;
594 my $rowRef;
595 while ($rowRef = $sth->fetchrow_hashref())
596 {
597 push(@resultsArray,$rowRef);
598 }
599  
600 return \@resultsArray;
601  
602 }
603
604  
605 ####################################################################
606 #
607 # GetIndexes
608 # + Get a list of indexes in present database
609 # + Input : None
610 # + Output : Ref to array of hashes containing:
611 # Name (name of index)
612 # OnTable
613 # CreateDate
614 #
615 ####################################################################
616  
617 sub GetIndexes {
618  
619 my $self = shift;
620  
621 # Based on constant SQL $getIndexesSQL
622 my $sth = $self->{DBHANDLE}->prepare($getIndexesSQL);
623  
624 $sth->execute();
625 my @resultsArray;
626 my $rowRef;
627 while ($rowRef = $sth->fetchrow_hashref())
628 {
629 push(@resultsArray,$rowRef);
630 }
631  
632 return \@resultsArray;
633  
634 }
635  
636  
637
638 ####################################################################
639 #
640 # DescribeTable
641 # + Get table details including column names, types, null/not null
642 # + Input : Table Name
643 # + Output : Ref to array of hashes containing:
644 # Name (of column)
645 # Type
646 # NullType (NULL/NOT NULL)
647 #
648 ####################################################################
649  
650 sub DescribeTable {
651  
652 my $self = shift;
653 my $tableName = shift;
654  
655 # Based on constant SQL $describeTableSQL
656 my $sth = $self->{DBHANDLE}->prepare($describeTableSQL);
657  
658 $sth->execute($tableName);
659 my @resultsArray;
660 my $rowRef;
661 while ($rowRef = $sth->fetchrow_hashref())
662 {
663 push(@resultsArray,$rowRef);
664 }
665  
666 # Remove DEFNCOPY text
667 my $junk = pop @resultsArray;
668  
669  
670 return \@resultsArray;
671  
672 }
673
674
675 ####################################################################
676 #
677 # DescribeProc
678 # + Get stored proc text excluding create statements, etc
679 # + Input : Proc Name
680 # + Output : Ref to array containing lines of text
681 #
682 ####################################################################
683  
684 sub DescribeProc {
685  
686 my $self = shift;
687 my $procName = shift;
688 my $tempFile = "$procName.$$";
689  
690 # Use defncopy to extract the info needed. Copy to a temporary file
691 # and then put the results in an array by reading the file!
692 my $defnCmd = "$self->{SYBASE}/$self->{SYBASE_OCS}/bin/defncopy -U $self->{USER} -S $self->{SERVER} -P $self->{PASSWORD} out $tempFile $self->{DBNAME} $procName";
693
694 system($defnCmd);
695  
696 # open result file for processing
697 open(FH,"<$tempFile") or die "Could not open $tempFile - check directory permisisons\n";
698 my @results = <FH>;
699  
700 close(FH);
701 # Remove temporary file
702 unlink($tempFile);
703  
704 # Remove DEFNCOPY text
705 my $junk = pop @results;
706  
707 return \@results;
708  
709 }
710  
711  
712 ####################################################################
713 #
714 # DescribeTrigger
715 # + Get trigger text excluding create statements, etc
716 # + Input : Trigger Name
717 # + Output : Ref to array containing lines of text
718 #
719 ####################################################################
720  
721 sub DescribeTrigger {
722  
723 my $self = shift;
724  
725 my $triggerName = shift;
726 my $tempFile = "$triggerName.$$";
727  
728 # Use defncopy to extract the info needed. Copy to a temporary file
729 # and then put the results in an array by reading the file!
730 my $defnCmd = "$self->{SYBASE}/$self->{SYBASE_OCS}/bin/defncopy -U $self->{USER} -S $self->{SERVER} -P $self->{PASSWORD} out $tempFile $self->{DBNAME} $triggerName";
731
732 system($defnCmd);
733  
734 # open result file for processing
735 open(FH,"<$tempFile") or die "Could not open $tempFile - check directory permisisons\n";
736 my @results = <FH>;
737  
738 close(FH);
739 # Remove temporary file
740 unlink($tempFile);
741  
742 # Remove DEFNCOPY text
743 my $junk = pop @results;
744  
745 return \@results;
746  
747 }
748
749
750 ####################################################################
751 #
752 # DescribeView
753 # + Get view text excluding create statements, etc
754 # + Input : ViewName
755 # + Output : Ref to array containing lines of text
756 #
757 ####################################################################
758  
759 sub DescribeView {
760  
761 my $self = shift;
762  
763 my $viewName = shift;
764 my $tempFile = "$viewName.$$";
765  
766 # Use defncopy to extract the info needed. Copy to a temporary file
767 # and then put the results in an array by reading the file!
768 my $defnCmd = "$self->{SYBASE}/$self->{SYBASE_OCS}/bin/defncopy -U $self->{USER} -S $self->{SERVER} -P $self->{PASSWORD} out $tempFile $self->{DBNAME} $viewName";
769
770 system($defnCmd);
771  
772 # open result file for processing
773 open(FH,"<$tempFile") or die "Could not open $tempFile - check directory permisisons\n";
774 my @results = <FH>;
775  
776 close(FH);
777 # Remove temporary file
778 unlink($tempFile);
779  
780 # Remove DEFNCOPY text
781 my $junk = pop @results;
782  
783  
784 return \@results;
785  
786 }
787
788
789  
790 ####################################################################
791 #
792 # GetUsers
793 # + Get names/groups of all users in this database
794 # + Input : None
795 # + Output : Ref to array of hashes containing:
796 # UserName
797 # UserID
798 # GroupName
799 # GroupID
800 #
801 #
802 ####################################################################
803  
804 sub GetUsers {
805  
806 my $self = shift;
807  
808 # Based on constant $getUsersSQL
809 my $sth = $self->{DBHANDLE}->prepare($getUsersSQL);
810  
811 $sth->execute();
812 my @resultsArray;
813 my $rowRef;
814 while ($rowRef = $sth->fetchrow_hashref())
815 {
816 push(@resultsArray,$rowRef);
817 }
818  
819 return \@resultsArray;
820  
821 }
822  
823
824 ####################################################################
825 #
826 # GetLogins
827 # + Get names of all server level logins
828 # + Input : None
829 # + Output : Ref to hash containing:
830 # LoginName
831 # LoginID
832 # DefaultDB
833 #
834 ####################################################################
835  
836 sub GetLogins {
837  
838 my $self = shift;
839  
840 # Based on constant $getLoginsSQL
841 my $sth = $self->{DBHANDLE}->prepare($getLoginsSQL);
842  
843 $sth->execute();
844 my @resultsArray;
845 my $rowRef;
846 while ($rowRef = $sth->fetchrow_hashref())
847 {
848 push(@resultsArray,$rowRef);
849 }
850  
851 return \@resultsArray;
852  
853 }
854
855  
856 ####################################################################
857 #
858 # GetGroups
859 # + Get names of all groups in present database
860 # + Input : None
861 # + Output : Ref to hash containing:
862 # GroupName
863 # GroupID
864 #
865 ####################################################################
866  
867 sub GetGroups {
868  
869 my $self = shift;
870  
871 # Based on constant $getGroupsSQL
872 my $sth = $self->{DBHANDLE}->prepare($getGroupsSQL);
873  
874 $sth->execute();
875 my @resultsArray;
876 my $rowRef;
877 while ($rowRef = $sth->fetchrow_hashref())
878 {
879 push(@resultsArray,$rowRef);
880 }
881  
882 return \@resultsArray;
883  
884 }
885  
886
887 ####################################################################
888 #
889 # GetGroupMembers
890 # + Get list of all members of a given group
891 # + Input : GroupName
892 # + Output : Ref to hash containing:
893 # UserName
894 # UserID
895 #
896 ####################################################################
897  
898 sub GetGroupMembers {
899  
900 my $self = shift;
901 my $groupName = shift;
902  
903 # Based on constant $GetMembersSQL
904 my $sth = $self->{DBHANDLE}->prepare($GetMembersSQL);
905  
906 $sth->execute($groupName);
907 my @resultsArray;
908 my $rowRef;
909 while ($rowRef = $sth->fetchrow_hashref())
910 {
911 push(@resultsArray,$rowRef);
912 }
913  
914 return \@resultsArray;
915  
916 }
917
918  
919  
920 ####################################################################
921 #
922 # ExtractTableSQL
923 # + Get entire stored proc with drop/create statements
924 # + Input : Table Name
925 # + Output : Ref to array containing text
926 #
927 ####################################################################
928  
929 sub ExtractTableSQL {
930  
931 my $self = shift;
932 my $tableName = shift;
933  
934 my $currSQL = "if not exists (select 1 from sysobjects where type = 'U' and name = '".$tableName."') \n";
935  
936 my @results = ($currSQL);
937 push(@results,"BEGIN \n");
938 $currSQL = " CREATE TABLE ".$tableName."( \n";
939 push(@results,$currSQL);
940 my $columnsPtr = $self->DescribeTable($tableName);
941  
942 my $columnPtr;
943  
944 foreach $columnPtr (@{ $columnsPtr }) {
945 my $currSQL = sprintf("%-30s %-20s %s,\n",$columnPtr->{Name},$columnPtr->{Type},$columnPtr->{NullType});
946 push(@results,$currSQL);
947 }
948  
949 push(@results,") \n");
950 push(@results,"END\n");
951  
952 return \@results;
953  
954 }
955  
956  
957 ####################################################################
958 #
959 # ExtractViewSQL
960 # + Get entire view with drop/create statements
961 # + Input : View Name or View OID
962 # + Output : Ref to array containing text
963 #
964 ####################################################################
965  
966 sub ExtractViewSQL {
967  
968 my $self = shift;
969 my $viewName = shift;
970  
971 my $currSQL = "if not exists (select 1 from sysobjects where type = 'V' and name = '".$viewName."') \n";
972 my @results = ($currSQL);
973 push(@results,"BEGIN \n");
974 my $viewDefPtr = $self->DescribeView($viewName);
975 push(@results,@{ $viewDefPtr });
976 push(@results,"END \n");
977  
978 return \@results;
979  
980 }
981
982
983 ####################################################################
984 #
985 # ExtractProcSQL
986 # + Get entire stored procedure with drop/create statements
987 # + Input : Proc Name or Proc OID
988 # + Output : Ref to array containing text
989 #
990 ####################################################################
991  
992 sub ExtractProcSQL {
993  
994 my $self = shift;
995 my $procName = shift;
996  
997 my $currSQL = "if not exists (select 1 from sysobjects where type = 'P' and name ='".$procName."') \n";
998 my @results = ($currSQL);
999 push(@results,"BEGIN \n");
1000 my $procDefPtr = $self->DescribeProc($procName);
1001 push(@results,@{ $procDefPtr });
1002 push(@results,"END \n");
1003  
1004 return \@results;
1005  
1006 }
1007  
1008  
1009 ####################################################################
1010 #
1011 # ExtractTriggerSQL
1012 # + Get entire trigger with drop/create statements
1013 # + Input : Trigger Name or Trigger OID
1014 # + Output : Ref to array containing text
1015 #
1016 ####################################################################
1017  
1018 sub ExtractTriggerSQL {
1019  
1020 my $self = shift;
1021 my $triggerName = shift;
1022  
1023 my $currSQL = "if not exists (select 1 from sysobjects where type = 'TR' and name = '".$triggerName."') \n";
1024 my @results = ($currSQL);
1025 push(@results,"BEGIN \n");
1026 my $triggerDefPtr = $self->DescribeTrigger($triggerName);
1027 push(@results,@{ $triggerDefPtr });
1028 push(@results,"END \n");
1029  
1030 return \@results;
1031  
1032 }
1033
1034
1035 ####################################################################
1036 #
1037 # ExtractRISQL
1038 # + Get entire referential integrity with drop/create statements
1039 # + Input : RI Name
1040 # + Output : Ref to array containing text
1041 #
1042 ####################################################################
1043  
1044 sub ExtractRISQL {
1045  
1046 my $self = shift;
1047 my $RIName = shift;
1048  
1049 my $currSQL = "if not exists (select 1 from sysobjects \n";
1050 my @results = ($currSQL);
1051 $currSQL = "where type = 'RI' and name = '".$RIName."') \n";
1052 push(@results,$currSQL);
1053 push(@results,"begin \n");
1054  
1055 # Get table names, etc
1056 # $getTrigMetadataSQL
1057
1058 my $sth = $self->{DBHANDLE}->prepare($getTrigMetadataSQL);
1059  
1060 $sth->execute($RIName);
1061 my $resultsRef = $sth->fetchrow_hashref;
1062  
1063 # Ensure first item exists
1064 # if (undef $resultsRef->[0]) {
1065 # warn "RI ".$RIName." not found in db";
1066 # return undef;
1067 # }
1068  
1069 my ($constrid,
1070 $table,
1071 $reftable) =
1072 ($resultsRef->{constrid},
1073 $resultsRef->{FromTable},
1074 $resultsRef->{ToTable});
1075  
1076 $currSQL = "alter table ".$table."\n";
1077 push(@results,$currSQL);
1078 $currSQL = "add constraint ".$RIName."\n";
1079 push(@results,$currSQL);
1080 $currSQL = "foreign key (";
1081  
1082 # Get foreign keys
1083 $sth = $self->{DBHANDLE}->prepare($getForKeysSQL);
1084  
1085 $sth->execute($constrid);
1086 $resultsRef = $sth->fetchall_arrayref({});
1087  
1088 foreach (@{ $resultsRef }) {
1089 $currSQL .= $_->{FoKey}.",";
1090 }
1091  
1092 # remove last comma
1093 chop $currSQL;
1094  
1095 $currSQL .= ") \n";
1096  
1097 push(@results,$currSQL);
1098  
1099 $currSQL = "references ".$reftable." (";
1100  
1101 # Get ref keys
1102 $sth = $self->{DBHANDLE}->prepare($getRefKeysSQL);
1103  
1104 $sth->execute($constrid);
1105 $resultsRef = $sth->fetchall_arrayref({});
1106 my @resultsArray = @{ $resultsRef };
1107 my $hashPtr;
1108  
1109 foreach $hashPtr (@resultsArray) {
1110 $currSQL .= $hashPtr->{RefKey}.",";
1111 }
1112  
1113 # remove last comma
1114 chop $currSQL;
1115  
1116 $currSQL .= ") \n";
1117  
1118 push(@results,$currSQL);
1119  
1120 push(@results,"end \n");
1121  
1122 return \@results;
1123
1124 }
1125
1126  
1127 ####################################################################
1128 #
1129 # SearchProcNames
1130 # + Search proc names for a given text pattern or sybase
1131 # regular expression. Will validate regular expression first.
1132 # + Input : Pattern (string with Sybase Reg Ex optional)
1133 # + Output : Ref To Array of hashes containing:
1134 # ProcName
1135 # ProcOID
1136 #
1137 ####################################################################
1138  
1139 sub SearchProcNames {
1140  
1141 my $self = shift;
1142 my $pattern = shift;
1143  
1144 # Based on constant $searchProcNamesSQL
1145 my $sth = $self->{DBHANDLE}->prepare($searchProcNamesSQL);
1146  
1147 $sth->execute($pattern);
1148 my @resultsArray;
1149 my $rowRef;
1150 while ($rowRef = $sth->fetchrow_hashref())
1151 {
1152 push(@resultsArray,$rowRef);
1153 }
1154  
1155 return \@resultsArray;
1156  
1157 }
1158  
1159
1160 ####################################################################
1161 #
1162 # SearchProcText
1163 # + Search proc text for a given text pattern or sybase
1164 # regular expression. Will validate regular expression first.
1165 # + Input : Pattern (string with Sybase Reg Ex optional)
1166 # + Output : Ref To Array of hashes containing:
1167 # ProcName
1168 # ProcOID
1169 # Snippett (text within proc containing pattern)
1170 #
1171 ####################################################################
1172  
1173 sub SearchProcText {
1174  
1175 my $self = shift;
1176 my $pattern = shift;
1177  
1178 # Based on constant $searchProcTextSQL
1179 my $sth = $self->{DBHANDLE}->prepare($searchProcTextSQL);
1180  
1181 $sth->execute($pattern);
1182 my @resultsArray;
1183 my $rowRef;
1184 while ($rowRef = $sth->fetchrow_hashref())
1185 {
1186 push(@resultsArray,$rowRef);
1187 }
1188  
1189 return \@resultsArray;
1190  
1191 }
1192
1193  
1194 ####################################################################
1195 #
1196 # SearchTriggerNames
1197 # + Search trigger names for a given text pattern or sybase
1198 # regular expression. Will validate regular expression first.
1199 # + Input : Pattern (string with Sybase Reg Ex optional)
1200 # + Output : Ref To Array of hashes containing:
1201 # TriggerName
1202 # TriggerOID
1203 # TableName
1204 # TableOID
1205 #
1206 ####################################################################
1207  
1208 sub SearchTriggerNames {
1209  
1210 my $self = shift;
1211 my $pattern = shift;
1212  
1213 # Based on constant $searchTrigNamesSQL
1214 my $sth = $self->{DBHANDLE}->prepare($searchTrigNamesSQL);
1215  
1216 $sth->execute($pattern);
1217 my @resultsArray;
1218 my $rowRef;
1219 while ($rowRef = $sth->fetchrow_hashref())
1220 {
1221 push(@resultsArray,$rowRef);
1222 }
1223  
1224 return \@resultsArray;
1225  
1226 }
1227  
1228  
1229 ####################################################################
1230 #
1231 # SearchTriggerText
1232 # + Search trigger text for a given text pattern or sybase
1233 # regular expression. Will validate regular expression first.
1234 # + Input : Pattern (string with Sybase Reg Ex optional)
1235 # + Output : Ref To Array of hashes containing:
1236 # TriggerName
1237 # TriggerOID
1238 # TableName
1239 # TableOID
1240 # Snippett (piece of code containing pattern)
1241 #
1242 ####################################################################
1243  
1244 sub SearchTriggerText {
1245  
1246 my $self = shift;
1247 my $pattern = shift;
1248  
1249 # Based on constant $searchTrigTextSQL
1250 my $sth = $self->{DBHANDLE}->prepare($searchTrigTextSQL);
1251  
1252 $sth->execute($pattern);
1253 my @resultsArray;
1254 my $rowRef;
1255 while ($rowRef = $sth->fetchrow_hashref())
1256 {
1257 push(@resultsArray,$rowRef);
1258 }
1259  
1260 return \@resultsArray;
1261  
1262 }
1263  
1264  
1265 ####################################################################
1266 #
1267 # SearchColumns
1268 # + Search column names for a given text pattern or sybase
1269 # regular expression. Will validate regular expression first.
1270 # + Input : Pattern (string with Sybase Reg Ex optional)
1271 # + Output : Ref To Array of hashes containing:
1272 # ColumnName
1273 # TableName
1274 # TableOID
1275 #
1276 ####################################################################
1277  
1278 sub SearchColumns {
1279  
1280 my $self = shift;
1281 my $pattern = shift;
1282  
1283 # Based on constant $searchColumnsSQL
1284 my $sth = $self->{DBHANDLE}->prepare($searchColumnsSQL);
1285  
1286 $sth->execute($pattern);
1287 my @resultsArray;
1288 my $rowRef;
1289 while ($rowRef = $sth->fetchrow_hashref())
1290 {
1291 push(@resultsArray,$rowRef);
1292 }
1293  
1294 return \@resultsArray;
1295  
1296 }
1297
1298  
1299 ####################################################################
1300 #
1301 # SearchTableNames
1302 # + Search table names for a given text pattern or sybase
1303 # regular expression. Will validate regular expression first.
1304 # + Input : Pattern (string with Sybase Reg Ex optional)
1305 # + Output : Ref To Array of hashes containing:
1306 # TableName
1307 # TableOID
1308 #
1309 ####################################################################
1310  
1311 sub SearchTableNames {
1312  
1313 my $self = shift;
1314 my $pattern = shift;
1315  
1316 # Based on constant $searchTableNamesSQL
1317 my $sth = $self->{DBHANDLE}->prepare($searchTableNamesSQL);
1318  
1319 $sth->execute($pattern);
1320 my @resultsArray;
1321 my $rowRef;
1322 while ($rowRef = $sth->fetchrow_hashref())
1323 {
1324 push(@resultsArray,$rowRef);
1325 }
1326  
1327 return \@resultsArray;
1328  
1329 }
1330
1331  
1332 ####################################################################
1333 #
1334 # SearchViewNames
1335 # + Search view names for a given text pattern or sybase
1336 # regular expression. Will validate regular expression first.
1337 # + Input : Pattern (string with Sybase Reg Ex optional)
1338 # + Output : Ref To Array of hashes containing:
1339 # ViewName
1340 # ViewOID
1341 #
1342 ####################################################################
1343  
1344 sub SearchViewNames {
1345  
1346 my $self = shift;
1347 my $pattern = shift;
1348  
1349 # Based on constant $searchViewNamesSQL
1350 my $sth = $self->{DBHANDLE}->prepare($searchViewNamesSQL);
1351  
1352 $sth->execute($pattern);
1353 my @resultsArray;
1354 my $rowRef;
1355 while ($rowRef = $sth->fetchrow_hashref())
1356 {
1357 push(@resultsArray,$rowRef);
1358 }
1359  
1360 return \@resultsArray;
1361  
1362 }
1363  
1364
1365 ####################################################################
1366 #
1367 # SearchViewText
1368 # + Search view names for a given text pattern or sybase
1369 # regular expression. Will validate regular expression first.
1370 # + Input : Pattern (string with Sybase Reg Ex optional)
1371 # + Output : Ref To Array of hashes containing:
1372 # ViewName
1373 # ViewOID
1374 # Snippett (bit of view containing pattern)
1375 #
1376 ####################################################################
1377  
1378 sub SearchViewText {
1379  
1380 my $self = shift;
1381 my $pattern = shift;
1382  
1383 # Based on constant $searchViewTextSQL
1384 my $sth = $self->{DBHANDLE}->prepare($searchViewTextSQL);
1385  
1386 $sth->execute($pattern);
1387 my @resultsArray;
1388 my $rowRef;
1389 while ($rowRef = $sth->fetchrow_hashref())
1390 {
1391 push(@resultsArray,$rowRef);
1392 }
1393  
1394 return \@resultsArray;
1395  
1396 }
1397
1398  
1399 ####################################################################
1400 #
1401 # SearchIndexNames
1402 # + Search index names for a given text pattern or sybase
1403 # regular expression. Will validate regular expression first.
1404 # + Input : Pattern (string with Sybase Reg Ex optional)
1405 # + Output : Ref To Array of hashes containing:
1406 # IndexName
1407 # TableName
1408 # TableOID
1409 #
1410 ####################################################################
1411  
1412 sub SearchIndexNames {
1413  
1414 my $self = shift;
1415 my $pattern = shift;
1416  
1417 # Based on constant $searchIndexesSQL
1418 my $sth = $self->{DBHANDLE}->prepare($searchIndexesSQL);
1419  
1420 $sth->execute($pattern);
1421 my @resultsArray;
1422 my $rowRef;
1423 while ($rowRef = $sth->fetchrow_hashref())
1424 {
1425 push(@resultsArray,$rowRef);
1426 }
1427  
1428 return \@resultsArray;
1429  
1430 }
1431  
1432
1433 ####################################################################
1434 #
1435 # SearchUsers
1436 # + Search user names for a given text pattern or sybase
1437 # regular expression. Will validate regular expression first.
1438 # + Input : Pattern (string with Sybase Reg Ex optional)
1439 # + Output : Ref To Array of hashes containing:
1440 # UserName
1441 # UserID
1442 #
1443 ####################################################################
1444  
1445 sub SearchUsers {
1446  
1447 my $self = shift;
1448 my $pattern = shift;
1449  
1450 # Based on constant $searchUsersSQL
1451 my $sth = $self->{DBHANDLE}->prepare($searchUsersSQL);
1452  
1453 $sth->execute($pattern);
1454 my @resultsArray;
1455 my $rowRef;
1456 while ($rowRef = $sth->fetchrow_hashref())
1457 {
1458 push(@resultsArray,$rowRef);
1459 }
1460  
1461 return \@resultsArray;
1462  
1463 }
1464  
1465
1466 ####################################################################
1467 #
1468 # SearchGroups
1469 # + Search group names for a given text pattern or sybase
1470 # regular expression. Will validate regular expression first.
1471 # + Input : Pattern (string with Sybase Reg Ex optional)
1472 # + Output : Ref To Array of hashes containing:
1473 # GroupName
1474 # GroupID
1475 #
1476 ####################################################################
1477  
1478 sub SearchGroups {
1479  
1480 my $self = shift;
1481 my $pattern = shift;
1482  
1483 # Based on constant $searchGroupsSQL
1484 my $sth = $self->{DBHANDLE}->prepare($searchGroupsSQL);
1485  
1486 $sth->execute($pattern);
1487 my @resultsArray;
1488 my $rowRef;
1489 while ($rowRef = $sth->fetchrow_hashref())
1490 {
1491 push(@resultsArray,$rowRef);
1492 }
1493  
1494 return \@resultsArray;
1495  
1496 }
1497
1498  
1499 ####################################################################
1500 #
1501 # CloseConnection
1502 # + Clean up and close DB handle
1503 # + Input: None needed
1504 #
1505 ####################################################################
1506  
1507 sub CloseConnection {
1508  
1509 my $self = shift;
1510  
1511 $self->{DBHANDLE}->disconnect;
1512  
1513 }
1514  
1515  
1516  
1517  
1518 1; #last line as required by perl modules
1519 __END__
↑ Summary