/* Build all tables in the admin subsystem              */
/* Microsoft SQL Server - Scripting			*/
/* Server: ?????					*/
/* Database: admin					*/
/* Creation Date 12/11/98 10:17:17 AM 			*/


/****** Object:  Table dbo.databases    Script Date: 12/11/98 10:17:19 AM ******/
CREATE TABLE dbo.databases (
	name varchar (30) NOT NULL ,
	description varchar (255) NULL ,
	compareTableName varchar (30) NULL ,
	primaryServer varchar (30) NULL ,
	CONSTRAINT PK_databases PRIMARY KEY  CLUSTERED 
	(
		name
	) WITH  FILLFACTOR = 95
)
GO

/****** Object:  Table dbo.drivers    Script Date: 12/11/98 10:17:19 AM ******/
CREATE TABLE dbo.drivers (
	name varchar (30) NOT NULL ,
	description varchar (255) NULL ,
	distributedFlag varchar (5) NULL ,
	CONSTRAINT PK_drivers PRIMARY KEY  CLUSTERED 
	(
		name
	) WITH  FILLFACTOR = 95
)
GO

/****** Object:  Table dbo.in_use    Script Date: 12/11/98 10:17:19 AM ******/
CREATE TABLE dbo.in_use (
	dbName varchar (30) NOT NULL ,
	server varchar (30) NOT NULL ,
	process varchar (30) NULL ,
	start_time datetime NOT NULL CONSTRAINT DF_in_use_start_time DEFAULT (getdate()),
	CONSTRAINT PK_in_use PRIMARY KEY  CLUSTERED 
	(
		dbName,
		server
	) WITH  FILLFACTOR = 95
)
GO

/****** Object:  Table dbo.logspace    Script Date: 12/11/98 10:17:19 AM ******/
CREATE TABLE dbo.logspace (
	dbname char (30) NOT NULL ,
	logsize numeric(5, 2) NOT NULL ,
	spaceused numeric(10, 8) NOT NULL ,
	status bit NOT NULL ,
	date datetime NOT NULL CONSTRAINT DF_logspace_date DEFAULT (getdate()),
	CONSTRAINT PK_logspace PRIMARY KEY  CLUSTERED 
	(
		dbname,
		date
	) WITH  FILLFACTOR = 90
)
GO

/****** Object:  Table dbo.messenger    Script Date: 12/11/98 10:17:19 AM ******/
CREATE TABLE dbo.messenger (
	id int IDENTITY (1, 1) NOT NULL ,
	spid smallint NULL ,
	info varchar (100) NULL ,
	CONSTRAINT PK_messenger PRIMARY KEY  CLUSTERED 
	(
		id
	) WITH  FILLFACTOR = 90
)
GO

/****** Object:  Table dbo.nullreader    Script Date: 12/11/98 10:17:19 AM ******/
CREATE TABLE dbo.nullreader (
	text varchar (100) NULL 
)
GO

/****** Object:  Table dbo.persons    Script Date: 12/11/98 10:17:19 AM ******/
CREATE TABLE dbo.persons (
	firstName varchar (20) NOT NULL ,
	lastName varchar (20) NOT NULL ,
	role char (10) NULL ,
	homePhone varchar (15) NULL ,
	pager varchar (15) NULL ,
	emailAddress varchar (50) NULL ,
	CONSTRAINT PK_persons PRIMARY KEY  CLUSTERED 
	(
		lastName,
		firstName
	) WITH  FILLFACTOR = 95
)
GO

/****** Object:  Table dbo.PULISTtext    Script Date: 12/14/98 10:48:02 AM ******/
CREATE TABLE dbo.PULISTtext (
	text varchar (100) NOT NULL 
)
GO

/****** Object:  Table dbo.scripts    Script Date: 12/11/98 10:17:19 AM ******/
CREATE TABLE dbo.scripts (
	ID int IDENTITY (1, 1) NOT NULL ,
	name varchar (30) NOT NULL ,
	line varchar (100) NOT NULL ,
	CONSTRAINT PK_scripts PRIMARY KEY  CLUSTERED 
	(
		name,
		ID
	) WITH  FILLFACTOR = 90
)
GO

/****** Object:  Table dbo.semaphore    Script Date: 12/11/98 10:17:19 AM ******/
CREATE TABLE dbo.semaphore (
	name varchar (30) NOT NULL ,
	status tinyint NOT NULL ,
	description varchar (255) NULL ,
	pad1 char (255) NULL CONSTRAINT DF_pad1 DEFAULT ('used to prevent page lock contention'),
	pad2 char (255) NULL CONSTRAINT DF_pad2 DEFAULT ('five char pad columns of length 255'),
	pad3 char (255) NULL CONSTRAINT DF_pad3 DEFAULT ('will cause the row to use a complete page'),
	pad4 char (255) NULL CONSTRAINT DF_pad4 DEFAULT ('because records cannot share a 2k page'),
	pad5 char (255) NULL CONSTRAINT DF_pad5 DEFAULT ('defaults assure that the storage space for the column is allocated'),
	CONSTRAINT PK_semaphore PRIMARY KEY  CLUSTERED 
	(
		name
	) WITH  FILLFACTOR = 90
)
GO

/****** Object:  Table dbo.servers    Script Date: 12/11/98 10:17:20 AM ******/
CREATE TABLE dbo.servers (
	name varchar (30) NOT NULL ,
	description varchar (100) NULL ,
	IPaddr char (15) NULL ,
	dumpShare varchar (10) NULL ,
	dumpDrive char (1) NULL ,
	dumpFolder varchar (30) NULL ,
	loadShare varchar (10) NULL ,
	loadDrive char (1) NULL ,
	loadFolder varchar (30) NULL ,
	CONSTRAINT PK_servers PRIMARY KEY  CLUSTERED 
	(
		name
	) WITH  FILLFACTOR = 95,
	CONSTRAINT dumpDrive_not_blank CHECK (dumpDrive <> ' '),
	CONSTRAINT dumpFolder_not_blank CHECK (dumpFolder <> ' '),
	CONSTRAINT dumpShare_not_blank CHECK (dumpShare <> ' '),
	CONSTRAINT IPaddr_not_blank CHECK (IPaddr <> ' '),
	CONSTRAINT loadDrive_not_blank CHECK (loadDrive <> ' '),
	CONSTRAINT loadFolder_not_blank CHECK (loadFolder <> ' '),
	CONSTRAINT loadShare_not_blank CHECK (loadShare <> ' '),
	CONSTRAINT name_not_blank CHECK (name <> ' ')
)
GO

/****** Object:  Table dbo.subsystem    Script Date: 12/11/98 10:17:20 AM ******/
CREATE TABLE dbo.subsystem (
	name varchar (30) NOT NULL ,
	path varchar (255) NULL ,
	date datetime NULL ,
	CONSTRAINT PK_subsystems PRIMARY KEY  CLUSTERED 
	(
		name
	) WITH  FILLFACTOR = 95
)
GO

/****** Object:  Table dbo.test    Script Date: 12/11/98 10:17:20 AM ******/
CREATE TABLE dbo.test (
	text varchar (100) NOT NULL 
)
GO

/****** Object:  Table dbo.textreader    Script Date: 12/11/98 10:17:20 AM ******/
CREATE TABLE dbo.textreader (
	text varchar (100) NOT NULL 
)
GO

/****** Object:  Trigger dbo.server_instrig    Script Date: 12/11/98 10:17:20 AM ******/
CREATE TRIGGER server_instrig ON dbo.servers 
FOR INSERT
AS
DECLARE @testDrive CHAR(1),
        @found INT, 
        @myName VARCHAR(30),
        @IPaddr VARCHAR(15),
        @newServer VARCHAR(30),
        @dumpShare VARCHAR(10),
        @dumpDrive CHAR(1),
        @dumpFolder VARCHAR(30),
        @loadShare VARCHAR(10),
        @loadDrive CHAR(1),
        @loadFolder VARCHAR(30)
  
SELECT @myName = 'admin..server_instrig'
IF @@ROWCOUNT > 1
  BEGIN
    RAISERROR ("Multiple row operations not permitted for table servers (%s)",1,2,@myName) WITH SETERROR

    ROLLBACK TRANSaction 
  END
GO