home *** CD-ROM | disk | FTP | other *** search
/ Usenet 1994 October / usenetsourcesnewsgroupsinfomagicoctober1994disk2.iso / misc / volume37 / sybperl / part01 / eg / dbschema.pl
Perl Script  |  1993-05-04  |  9KB  |  378 lines
  1. #! /usr/local/bin/sybperl
  2. #
  3. #    @(#)dbschema.pl    1.3    6/24/92
  4. #
  5. #
  6. #    dbschema.pl    A script to extract a database structure from
  7. #            a Sybase database
  8. #
  9. #    Written by:    Michael Peppler (mpeppler@itf.ch)
  10. #    Last Modified:  24 June 1992
  11. #
  12. #    Usage:        dbschema.pl -d database -o script.name -t pattern -v
  13. #                where   database is self-explanatory (default: master)
  14. #                                   script.name is the output file (default: script.isql)
  15. #                                   pattern is the pattern of object names (in sysobjects)
  16. #                                           that we will look at (default: %)
  17. #
  18. #                -v turns on a verbose switch.
  19. #
  20.  
  21.  
  22. require 'sybperl.pl';
  23. require 'getopts.pl';
  24. require 'ctime.pl';
  25.  
  26. @nul = ('not null','null');
  27.  
  28. select(STDOUT); $| = 1;        # make unbuffered
  29.  
  30. do Getopts('d:t:o:v');
  31.  
  32. $opt_d = 'master' unless $opt_d;
  33. $opt_o = 'script.isql' unless $opt_o;
  34. $opt_t = '%' unless $opt_t;
  35.  
  36. open(SCRIPT, "> $opt_o") || die "Can't open $opt_o: $!\n";
  37. open(LOG, "> $opt_o.log") || die "Can't open $opt_o.log: $!\n";
  38.  
  39. #
  40. # NOTE: We login to Sybase with the default (Unix) user id.
  41. #       We should probably login as 'SA', and get the passwd
  42. #       from the user at run time.
  43. #
  44. $dbproc = &dblogin;
  45. &dbuse($dproc, $opt_d);
  46.  
  47. chop($date = &ctime(time));
  48.  
  49.  
  50. print "dbschema.pl on Database $opt_d\n";
  51.  
  52. print LOG "Error log from dbschema.pl on Database $opt_d on $date\n\n";
  53. print LOG "The following objects cannot be reliably created from the script in $opt_o.
  54. Please correct the script to remove any inconsistencies.\n\n";
  55.  
  56. print SCRIPT
  57.     "/* This Isql script was generated by dbschema.pl on $date.
  58. ** The indexes need to be checked: column names & index names
  59. ** might be truncated!
  60. */\n";
  61.  
  62. print SCRIPT "\nuse $opt_d\ngo\n"; # Change to the appropriate database
  63.  
  64.  
  65. # first, Add the appropriate user data types:
  66. #
  67.  
  68. print "Add user-defined data types...";
  69. print SCRIPT
  70.     "/* Add user-defined data types: */\n\n";
  71.  
  72. &dbcmd($dbproc, "select s.length, s.name, st.name,\n");
  73. &dbcmd($dbproc, "       object_name(s.tdefault),\n");
  74. &dbcmd($dbproc, "       object_name(s.domain)\n");
  75. &dbcmd($dbproc, "from   $opt_d.dbo.systypes s, $opt_d.dbo.systypes st\n");
  76. &dbcmd($dbproc, "where  st.type = s.type\n");
  77. &dbcmd($dbproc, "and s.usertype > 100 and st.usertype < 100 and st.usertype != 18\n");
  78. &dbsqlexec($dbproc);
  79. &dbresults($dbproc);
  80.  
  81.  
  82. while((@dat = &dbnextrow($dbproc)))
  83. {
  84.     print SCRIPT "sp_addtype $dat[1],";
  85.     if ($dat[2] =~ /char|binary/)
  86.     {
  87.         print SCRIPT "'$dat[2]($dat[0])'";
  88.     }
  89.     else
  90.     {
  91.         print SCRIPT "$dat[2]";
  92.     }
  93.     print SCRIPT "\ngo\n";
  94.                 # Now remeber the default & rule for later.
  95.     $urule{$dat[1]} = $dat[4] if $dat[4] !~ /NULL/;
  96.     $udflt{$dat[1]} = $dat[3] if $dat[3] !~ /NULL/;
  97. }
  98.  
  99. print "Done\n";
  100.  
  101. print "Create rules...";
  102. print SCRIPT
  103.     "\n/* Now we add the rules... */\n\n";
  104.  
  105. &getObj('Rule', 'R');
  106. print "Done\n";
  107.  
  108. print "Create defaults...";
  109. print SCRIPT
  110.     "\n/* Now we add the defaults... */\n\n";
  111.  
  112. &getObj('Default', 'D');
  113. print "Done\n";
  114.  
  115. print "Bind rules & defaults to user data types...";
  116. print SCRIPT "/* Bind rules & defaults to user data types... */\n\n";
  117.  
  118. while(($dat, $dflt)=each(%udflt))
  119. {
  120.     print SCRIPT "sp_bindefault $dflt, $dat\ngo\n";
  121. }
  122. while(($dat, $rule) = each(%urule))
  123. {
  124.     print SCRIPT "sp_bindrule $rule, $dat\ngo\n";
  125. }
  126. print "Done\n";
  127.  
  128. print "Create Tables & Indices...";
  129. print "\n" if $opt_v;
  130.  
  131. &dbcmd($dbproc, "select o.name,u.name, o.id\n");
  132. &dbcmd($dbproc, "from $opt_d.dbo.sysobjects o, $opt_d.dbo.sysusers u\n");
  133. &dbcmd($dbproc, "where o.type = 'U' and o.name like '$opt_t' and u.uid = o.uid\n");
  134. &dbcmd($dbproc, "order by o.name\n");
  135.  
  136. &dbsqlexec($dbproc);
  137. &dbresults($dbproc);
  138.  
  139. while((@dat = &dbnextrow($dbproc)))
  140. {
  141.     $_ = join('@', @dat);    # join the data together on a line
  142.     push(@tables,$_);        # and save it in a list
  143. }
  144.  
  145.  
  146. foreach (@tables)        # For each line in the list
  147. {
  148.     @tab = split(/@/, $_);
  149.  
  150.     print "Creating table $tab[0], owner $tab[1]\n" if $opt_v;
  151.  
  152.     print SCRIPT "/* Start of description of table $tab[1].$tab[0] */\n\n";
  153.  
  154.     &dbcmd($dbproc, "select Column_name = c.name, \n");
  155.     &dbcmd($dbproc, "       Type = t.name, \n");
  156.     &dbcmd($dbproc, "       Length = c.length, \n");
  157.     &dbcmd($dbproc, "       Nulls = convert(bit, (c.status & 8)),\n");
  158.     &dbcmd($dbproc, "       Default_name = object_name(c.cdefault),\n");
  159.     &dbcmd($dbproc, "       Rule_name = object_name(c.domain)\n");
  160.     &dbcmd($dbproc, "from   $opt_d.dbo.syscolumns c, $opt_d.dbo.systypes t\n");
  161.     &dbcmd($dbproc, "where  c.id = $tab[2]\n");
  162.     &dbcmd($dbproc, "and    c.usertype *= t.usertype\n");
  163.  
  164.     &dbsqlexec($dbproc);
  165.     &dbresults($dbproc);
  166.  
  167.     undef(%rule);
  168.     undef(%dflt);
  169.  
  170.     print SCRIPT "\n\nCREATE TABLE $opt_d.$tab[1].$tab[0]\n ("; 
  171.     $first = 1;
  172.     while((@field = &dbnextrow($dbproc)))
  173.     {
  174.         print SCRIPT ",\n" if !$first;        # add a , and a \n if not first field in table
  175.         
  176.         print SCRIPT "\t$field[0] \t$field[1]";
  177.         print SCRIPT "($field[2])" if $field[1] =~ /char|bin/;
  178.         print SCRIPT " $nul[$field[3]]";
  179.     
  180.     $rule{"$tab[0].$field[0]"} = $field[5] if ($field[5] !~ /NULL/ && $urule{$field[1]} ne $field[5]);
  181.     $dflt{"$tab[0].$field[0]"} = $field[4] if ($field[4] !~ /NULL/ && $udflt{$field[1]} ne $field[4]);;
  182.         $first = 0 if $first;
  183.         
  184.     }
  185.     print SCRIPT " )\n";
  186.  
  187. # now get the indexes...
  188. #
  189.  
  190.     print "Indexes for table $tab[1].$tab[0]\n" if $opt_v;
  191.     
  192.     &dbcmd($dbproc, "sp_helpindex '$tab[1].$tab[0]'\n");
  193.  
  194.     &dbsqlexec($dbproc);
  195.     &dbresults($dbproc);
  196.  
  197.     while((@field = &dbnextrow($dbproc)))
  198.     {
  199.         print SCRIPT "\nCREATE ";
  200.         print SCRIPT "unique " if $field[1] =~ /unique/;
  201.         print SCRIPT "clustered " if $field[1] =~ /^clust/;
  202.         print SCRIPT "index $field[0]\n";
  203.         @col = split(/,/,$field[2]);
  204.         print SCRIPT "on $opt_d.$tab[1].$tab[0] (";
  205.         $first = 1;
  206.         foreach (@col)
  207.         {
  208.             print SCRIPT ", " if !$first;
  209.             $first = 0;
  210.             print SCRIPT "$_";
  211.         }
  212.         print SCRIPT ")\n";
  213.     }
  214.  
  215.     &getPerms("$tab[1].$tab[0]");
  216.  
  217.     print SCRIPT "go\n";
  218.  
  219.     print "Bind rules & defaults to columns...\n" if $opt_v;
  220.     print SCRIPT "/* Bind rules & defaults to columns... */\n\n";
  221.  
  222.     if($tab[1] ne 'dbo' && (keys(%dflt) || keys(%rules)))
  223.     {
  224.     print SCRIPT "/* The owner of the table is $tab[1].
  225. ** I can't bind the rules/defaults to a table of which I am not the owner.
  226. ** The procedures below will have to be run manualy by user $tab[1].
  227. */";
  228.     print LOG "Defaults/Rules for $tab[1].$tab[0] could not be bound\n";
  229.     }
  230.  
  231.     while(($dat, $dflt)=each(%dflt))
  232.     {
  233.     print SCRIPT "/* " if $tab[1] ne 'dbo';
  234.     print SCRIPT "sp_bindefault $dflt, '$dat'";
  235.     if($tab[1] ne 'dbo')
  236.     {
  237.         print SCRIPT " */\n";
  238.     }
  239.     else
  240.     {
  241.         print SCRIPT "\ngo\n";
  242.     }
  243.     }
  244.     while(($dat, $rule) = each(%rule))
  245.     {
  246.     print SCRIPT "/* " if $tab[1] ne 'dbo';
  247.     print SCRIPT "sp_bindrule $rule, '$dat'";
  248.     if($tab[1] ne 'dbo')
  249.     {
  250.         print SCRIPT " */\n";
  251.     }
  252.     else
  253.     {
  254.         print SCRIPT "\ngo\n";
  255.     }
  256.     }
  257.     print SCRIPT "\n/* End of description of table $tab[1].$tab[0] */\n";
  258.  
  259. }
  260.  
  261. print "Done\n";
  262.  
  263.  
  264. #
  265. # Now create any views that might exist
  266. #
  267.  
  268. print "Create views...";
  269. print SCRIPT
  270.     "\n/* Now we add the views... */\n\n";
  271.  
  272. &getObj('View', 'V');
  273.  
  274. print "Done\n";
  275.  
  276. #
  277. # Now create any stored procs that might exist
  278. #
  279.  
  280. print "Create stored procs...";
  281. print SCRIPT
  282.     "\n/* Now we add the stored procedures... */\n\n";
  283. &getObj('Stored Proc', 'P');
  284.  
  285. print "Done\n";
  286.  
  287. #
  288. # Now create the triggers
  289. #
  290.  
  291. print "Create triggers...";
  292. print SCRIPT
  293.     "\n/* Now we add the triggers... */\n\n";
  294.  
  295. &getObj('Trigger', 'TR');
  296.  
  297.  
  298. print "Done\n";
  299.  
  300. print "\nLooks like I'm all done!\n";
  301. close(SCRIPT);
  302. close(LOG);
  303.  
  304. &dbexit;
  305.  
  306.  
  307. sub getPerms
  308. {
  309.     local($obj) = $_[0];
  310.     local($ret, @dat, $act, $cnt);
  311.  
  312.     &dbcmd($dbproc, "sp_helprotect '$obj'\n");
  313.     &dbsqlexec;
  314.  
  315.     $cnt = 0;
  316.     while(($ret = &dbresults) != $NO_MORE_RESULTS && $ret != $FAIL)
  317.     {
  318.     while(@dat = &dbnextrow)
  319.     {
  320.         $act = 'to';
  321.         $act = 'from' if $dat[0] =~ /Revoke/;
  322.         print SCRIPT "$dat[0] $dat[1] on $obj $act $dat[2]\n";
  323.         ++$cnt;
  324.     }
  325.     }
  326.     $cnt;
  327. }
  328.  
  329. sub getObj
  330. {
  331.     local($objname, $obj) = @_;
  332.     local(@dat, @items, @vi, $found);
  333.     
  334.     &dbcmd($dbproc, "select o.name, u.name, o.id\n");
  335.     &dbcmd($dbproc, "from $opt_d.dbo.sysobjects o, $opt_d.dbo.sysusers u\n");
  336.     &dbcmd($dbproc, "where o.type = '$obj' and o.name like '$opt_t' and u.uid = o.uid\n");
  337.     &dbcmd($dbproc, "order by o.name\n");
  338.  
  339.     &dbsqlexec($dbproc);
  340.     &dbresults($dbproc);
  341.  
  342.     while((@dat = &dbnextrow($dbproc)))
  343.     {                # 
  344.     $_ = join('@', @dat);    # join the data together on a line
  345.     push(@items, $_);    # and save it in a list
  346.     }
  347.  
  348.     foreach (@items)
  349.     {
  350.     @vi = split(/@/, $_);
  351.     $found = 0;
  352.  
  353.     &dbcmd($dbproc, "select text from syscomments where id = $vi[2]");
  354.     &dbsqlexec;
  355.     &dbresults;
  356.     
  357.     print SCRIPT
  358.         "/* $objname $vi[0], owner $vi[1] */\n";
  359.  
  360.     while(($text) = &dbnextrow)
  361.     {
  362.         if(!$found && $vi[1] ne 'dbo')
  363.         {
  364.         ++$found if($text =~ /$vi[1]/);
  365.         }
  366.         print SCRIPT $text;
  367.     }
  368.     print SCRIPT "\ngo\n";
  369.     if(!$found && $vi[1] ne 'dbo')
  370.     {
  371.         print "**Warning**\n$objname $vi[0] has owner $vi[1]\nbut this is not mentioned in the CREATE PROC statement!!\n";
  372.         print LOG "$objname $vi[0] (owner $vi[1])\n";
  373.     }
  374.     }
  375. }
  376.  
  377.  
  378.