Edward Guiness > DBIx-TableReferences-MSSQL-0.04 > DBIx::TableReferences::MSSQL

Download:
DBIx-TableReferences-MSSQL-0.04.tar.gz

Dependencies

Annotate this POD

Related Modules

SQL::Translator
more...
By perlmonks.org
View/Report Bugs
Module Version: 0.04   Source  

NAME ^

DBIx::TableReferences::MSSQL -- Perl extension for getting information about declared referential constraints in MS SQL Server 2000 databases.

SYNOPSIS ^

    use DBIx::TableReferences::MSSQL;

    $tr = DBIx::TableReferences::MSSQL->new( $dbh );

    my $table = 'sales';

    @reftables = $tr->reftables($table);

    print "'$table' references these tables: @reftables\n";

Output (assuming $dbh is connected to the pubs database)

    'sales' references these tables: dbo.stores dbo.titles

Want more information?

    $refdetails = $tr->references('sales');

    for $ref (@{$refdetails}) {
        
        # The owner.name of the referenced table
        print "$ref->{refowner}.$ref->{reftable}\n";
        
        # columns in the referential constraint
        @fkeys = @{$ref->{cols}};    # FK
        @rkeys = @{$ref->{refcols}}; # PK

        while ($fkey = shift @fkeys, $rkey = shift @rkeys) {
            print "\t$fkey -> $rkey\n"
        }
    }

Output (showing the columns involved in the referential constraints)

    dbo.stores
            stor_id -> stor_id
    dbo.titles
            title_id -> title_id

DESCRIPTION ^

DBIx::TableReferences::MSSQL aims to provide information about declared table relationships, aka table references, in MS SQL Server 2000 databases.

To say that table A references table B is a shortcut for saying table A has a foreign key that exists as a primary key in table B.

METHODS ^

new

    $tr = DBIx::TableReferences::MSSQL->new($dbh);

Instantiates and returns the object. Retrieves all table reference information from the database and stores it internally, ready for querying via tablerefs or references.

You must pass this method a valid DBI connection to a MS SQL Server 2000 database, shown here as $dbh.

tablerefs

    @tables = $tr->tablerefs('titles');
    print $_,"\n" for @tables;

Returns a list of tables that are referenced by the table 'dbo.titles'. If you do not specify the owner of the table, 'dbo' is assumed.

tablerefs is just a wrapper method that calls references in list context.

references

    @tables = $tr->references('titles'); # list context

Returns a list of table names exactly as though you had called $tr->tablerefs('titles').

    $refs = $tr->references('titles'); # scalar context

Returns a reference to an array of hashes with the following structure -

    $refs = [
                {
                    'owner'      => 'dbo',
                    'table'      => 'titles',
                    'cols'       => [
                                      'pub_id'
                                    ],
                    'refowner'   => 'dbo',
                    'reftable'   => 'publishers',
                    'refcols'    => [
                                      'pub_id'
                                    ],
                    'sql_add'    => 'ALTER TABLE [dbo].[titles] ADD CONSTRAINT ...',
                    'sql_drop'   => 'ALTER TABLE [dbo].[titles] DROP CONSTRAINT ...',
                    'constraint' => 'FK__titles__pub_id__619B8048'
                } 
            ];

EXAMPLES ^

This example prints all the referential constraints as DROP statements and then as ALTER statements.

    use strict;
    use warnings;
    use DBIx::TableReferences::MSSQL;
    use DBC; # my custom database connector

    my $dbh = DBC->connect({database => 'pubs'});

    my $tr = DBIx::TableReferences::MSSQL->new( $dbh );

    my $sql = "select user_name(uid), name from sysobjects where xtype='U'";
    my @tables = map {"$_->[0].$_->[1]"} @{$dbh->selectall_arrayref($sql)};

    for my $table (@tables) {

        my $r = $tr->references('titles');

        for my $ref (@{$tr->references($table)}) {
            print $ref->{sql_drop},"\n";
        }
    }

    for my $table (@tables) {
        for my $ref (@{$tr->references($table)}) {
            print $ref->{sql_add},"\n";
        }
    }

AUTHOR ^

Edward Guiness <EdwardG@cpan.org>

DEPENDENCIES ^

The structure of the MS SQL 2000 sysreferences table.

EXPORTS ^

None by default.

COPYRIGHT AND LICENSE ^

Copyright (C) 2004 by Edward Guiness

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself, either Perl version 5.8.3 or, at your option, any later version of Perl 5 you may have available.

syntax highlighting: