#! c:/perl/bin/perl.exe
#
#
use strict;

use Win32::AdminMisc;
use Math::BigInt;
use Data::Dumper;

use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
use Time::localtime;

use DBI;
use DBD::ODBC;

use VMware::VILib;
use VMware::VIRuntime;

use vars qw/$Storage $tables $dbh $DB/;
undef $_ foreach ([$Storage, $tables, $dbh, $DB]);
@{$tables->{SAN_Devices}}=qw/SAN_ID INT IDENTITY(1,1) PRIMARY KEY,
						SAN_Name NVARCHAR(255) not NULL unique,
						SAN_Type NVARCHAR(25),
						Notes text/;
										
@{$tables->{SAN_LUNs}}=qw/LUN_ID INT IDENTITY(1,1) PRIMARY KEY, 	
						LUN_Name NVARCHAR(255) not NULL,
						fkSAN_ID INT CONSTRAINT Relation1 FOREIGN KEY 
						REFERENCES dbo.SAN_Devices(SAN_ID) 
						ON DELETE CASCADE /;													
@{$tables->{SAN_WWN}}=qw/WWN_ID INT IDENTITY(1,1) PRIMARY KEY, 	
						WWN_Port NVARCHAR(255) not NULL,
						fkLUN_ID INT CONSTRAINT Relation2 FOREIGN KEY 
						REFERENCES dbo.SAN_LUNs(LUN_ID) 
						ON DELETE CASCADE /;
										

##</code><code>##

sub sql_prepare
{
	my ($tbl) = @_;
	
	print "\n\n\tPreparing Table: $tbl\n\n";
	
	$dbh->do("IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[$tbl]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) 
				CREATE TABLE [dbo].[$tbl] (@{$tables->{$tbl}})");
	
	my $temp = $dbh->selectcol_arrayref("SELECT constraint_name FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = '$tbl'");
	foreach my $rel (@$temp){
		
		next unless ($rel=~/Relation/);
		print "\n\t\tFORIEGN KEY: $rel\n";

		$dbh->do("ALTER TABLE $tbl DROP CONSTRAINT $rel");	}
	
	$dbh->do("IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[$tbl]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) 
				DROP TABLE [dbo].[$tbl]; 
				CREATE TABLE [dbo].[$tbl] (@{$tables->{$tbl}})");	}

print "\nPreparing DataBase tables:\n";
&sql_prepare('SAN_Devices');
&sql_prepare('SAN_LUNs');
&sql_prepare('SAN_WWN');


##</code><code>##

Preparing DataBase tables:

        Preparing Table: SAN_Devices


        Preparing Table: SAN_LUNs

                FORIEGN KEY: Relation1

        Preparing Table: SAN_WWN

                FORIEGN KEY: Relation2

##</code><code>##

Preparing CAPMAN DataBase tables:


        Preparing Table: SAN_Devices



        Preparing Table: SAN_LUNs



        Preparing Table: SAN_WWN


                FORIEGN KEY: Relation2