From 1dfdccb8118aeaa3cd844ac8de2a672c93312166 Mon Sep 17 00:00:00 2001 From: mokhan Date: Sat, 21 Feb 2009 21:44:27 +0000 Subject: git-svn-id: http://svn.xp-dev.com/svn/mokhan-sait@2 da190166-9cfc-4ee1-ae03-434a172be219 --- slips/build/sql/Data/authorize.sql.template | 4 + slips/build/sql/Data/boat.sql.template | 5 + slips/build/sql/Data/customer.sql.template | 4 + slips/build/sql/Data/dock.sql.template | 6 + slips/build/sql/Data/lease.sql.template | 4 + slips/build/sql/Data/leasetype.sql.template | 7 ++ slips/build/sql/Data/location.sql.template | 5 + slips/build/sql/Data/slip.sql.template | 157 ++++++++++++++++++++++++++ slips/build/sql/ddl/database.sql.template | 15 +++ slips/build/sql/ddl/security.sql.template | 40 +++++++ slips/build/sql/ddl/tables.sql.template | 166 ++++++++++++++++++++++++++++ slips/build/sql/original.sql.template | Bin 0 -> 38056 bytes 12 files changed, 413 insertions(+) create mode 100644 slips/build/sql/Data/authorize.sql.template create mode 100644 slips/build/sql/Data/boat.sql.template create mode 100644 slips/build/sql/Data/customer.sql.template create mode 100644 slips/build/sql/Data/dock.sql.template create mode 100644 slips/build/sql/Data/lease.sql.template create mode 100644 slips/build/sql/Data/leasetype.sql.template create mode 100644 slips/build/sql/Data/location.sql.template create mode 100644 slips/build/sql/Data/slip.sql.template create mode 100644 slips/build/sql/ddl/database.sql.template create mode 100644 slips/build/sql/ddl/security.sql.template create mode 100644 slips/build/sql/ddl/tables.sql.template create mode 100644 slips/build/sql/original.sql.template (limited to 'slips/build/sql') diff --git a/slips/build/sql/Data/authorize.sql.template b/slips/build/sql/Data/authorize.sql.template new file mode 100644 index 0000000..f1af498 --- /dev/null +++ b/slips/build/sql/Data/authorize.sql.template @@ -0,0 +1,4 @@ +use [@initial.catalog@]; +GO +INSERT INTO Authorize(UserName,Password,CustomerID) VALUES('jdoe','p@ssword',1000) +GO diff --git a/slips/build/sql/Data/boat.sql.template b/slips/build/sql/Data/boat.sql.template new file mode 100644 index 0000000..0387b5c --- /dev/null +++ b/slips/build/sql/Data/boat.sql.template @@ -0,0 +1,5 @@ +use [@initial.catalog@]; +GO +INSERT INTO Boat(RegistrationNumber,Manufacturer,ModelYear,Length,CustomerID) +VALUES('AR8373-223-2','Yamaha',2005,18,1000) +GO \ No newline at end of file diff --git a/slips/build/sql/Data/customer.sql.template b/slips/build/sql/Data/customer.sql.template new file mode 100644 index 0000000..f069fa5 --- /dev/null +++ b/slips/build/sql/Data/customer.sql.template @@ -0,0 +1,4 @@ +use [@initial.catalog@]; +GO +INSERT INTO Customer(FirstName,LastName,Phone,City) VALUES('John','Doe','555-545-1212','Phoenix'); +GO \ No newline at end of file diff --git a/slips/build/sql/Data/dock.sql.template b/slips/build/sql/Data/dock.sql.template new file mode 100644 index 0000000..55a4964 --- /dev/null +++ b/slips/build/sql/Data/dock.sql.template @@ -0,0 +1,6 @@ +use [@initial.catalog@]; +GO +INSERT INTO Dock(DockName, LocationId, WaterService, ElectricalService) VALUES('Dock A', 1, 1, 1) +INSERT INTO Dock(DockName, LocationId, WaterService, ElectricalService) VALUES('Dock B', 1, 1, 0) +INSERT INTO Dock(DockName, LocationId, WaterService, ElectricalService) VALUES('Dock C', 1, 0, 1) +GO \ No newline at end of file diff --git a/slips/build/sql/Data/lease.sql.template b/slips/build/sql/Data/lease.sql.template new file mode 100644 index 0000000..c85a53d --- /dev/null +++ b/slips/build/sql/Data/lease.sql.template @@ -0,0 +1,4 @@ +use [@initial.catalog@]; +GO +INSERT INTO Lease(StartDate,EndDate,SlipID,CustomerID,LeaseTypeID) VALUES('09-12-2007','09-12-2008',1030,1000,4) +GO \ No newline at end of file diff --git a/slips/build/sql/Data/leasetype.sql.template b/slips/build/sql/Data/leasetype.sql.template new file mode 100644 index 0000000..b3c85b3 --- /dev/null +++ b/slips/build/sql/Data/leasetype.sql.template @@ -0,0 +1,7 @@ +use [@initial.catalog@]; +GO +INSERT INTO LeaseType(LeaseTypeName, StandardRateAmount) VALUES('Daily', 25.50) +INSERT INTO LeaseType(LeaseTypeName, StandardRateAmount) VALUES('Weekly', 145.50) +INSERT INTO LeaseType(LeaseTypeName, StandardRateAmount) VALUES('Monthly', 500.00) +INSERT INTO LeaseType(LeaseTypeName, StandardRateAmount) VALUES('Yearly', 5000.00) +GO diff --git a/slips/build/sql/Data/location.sql.template b/slips/build/sql/Data/location.sql.template new file mode 100644 index 0000000..1e17c16 --- /dev/null +++ b/slips/build/sql/Data/location.sql.template @@ -0,0 +1,5 @@ +use [@initial.catalog@]; +GO +INSERT INTO Location(LocationName) VALUES('Inland Lake') +INSERT INTO Location(LocationName) VALUES('Sab Diego') +GO diff --git a/slips/build/sql/Data/slip.sql.template b/slips/build/sql/Data/slip.sql.template new file mode 100644 index 0000000..8b70a03 --- /dev/null +++ b/slips/build/sql/Data/slip.sql.template @@ -0,0 +1,157 @@ +use [@initial.catalog@]; +GO +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,16,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,16,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,16,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,16,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,16,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,16,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,16,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,16,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,16,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,16,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,18,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,18,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,18,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,18,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,18,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,18,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,18,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,18,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,18,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,18,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,20,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,20,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,20,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,20,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,20,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,20,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,20,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,20,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,20,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,20,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,22,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,22,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,22,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,22,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,22,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,22,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,22,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,22,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,22,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,22,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,24,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,24,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,24,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,24,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,24,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,24,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,24,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,24,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,24,1) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,24,1) + +-- Dock B slips +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,16,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,16,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,16,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,16,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,16,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,16,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,16,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,16,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,16,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,16,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,18,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,18,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,18,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,18,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,18,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,18,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,18,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,18,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,18,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,18,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,20,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,20,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,20,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,20,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,20,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,20,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,20,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,20,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,20,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(8,20,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,22,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,22,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,22,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,22,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,22,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,22,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,22,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,22,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,22,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,22,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,24,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,24,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,24,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,24,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,24,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,24,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,24,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,24,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,24,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,24,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,26,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,26,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,26,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,26,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,26,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,26,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,26,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,26,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,26,2) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,26,2) + +-- Dock C slips +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,22,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,22,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,22,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,22,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,22,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,22,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,22,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,22,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,22,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,22,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,22,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,22,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,22,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,22,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,24,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,24,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,24,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,24,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,24,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,24,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,24,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,24,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,24,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,24,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,24,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,24,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,24,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,24,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,24,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(10,24,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(12,28,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(12,28,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(12,28,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(12,28,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(12,28,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(12,28,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(12,28,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(12,28,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(12,28,3) +INSERT INTO SLIP(SlipWidth, SlipLength, DockId) VALUES(12,28,3) +GO diff --git a/slips/build/sql/ddl/database.sql.template b/slips/build/sql/ddl/database.sql.template new file mode 100644 index 0000000..dc102f2 --- /dev/null +++ b/slips/build/sql/ddl/database.sql.template @@ -0,0 +1,15 @@ +/**************************************** PART 1 CREATE DATABASE ******************************/ + +USE [master] +GO + +IF EXISTS(SELECT * FROM Sysdatabases WHERE NAME LIKE '@initial.catalog@') + DROP DATABASE [@initial.catalog@] + GO + +/****** Object: Database [InlandMarina] Script Date: 11/12/2007 08:39:11 ******/ +CREATE DATABASE [@initial.catalog@] ON PRIMARY +( NAME = N'@initial.catalog@', FILENAME = N'@database.path@\@initial.catalog@.mdf' , SIZE = 2240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) + LOG ON +( NAME = N'@initial.catalog@_log', FILENAME = N'@database.path@\@initial.catalog@.ldf' , SIZE = 768KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) +GO \ No newline at end of file diff --git a/slips/build/sql/ddl/security.sql.template b/slips/build/sql/ddl/security.sql.template new file mode 100644 index 0000000..27c5e22 --- /dev/null +++ b/slips/build/sql/ddl/security.sql.template @@ -0,0 +1,40 @@ +use [@initial.catalog@] +GO +IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'@asp.net.account@') +CREATE LOGIN [@asp.net.account@] FROM WINDOWS WITH DEFAULT_DATABASE=[@initial.catalog@] +GO + +if not exists (select * from dbo.sysusers where name = N'@asp.net.account@' and uid < 16382) + EXEC sp_grantdbaccess N'@asp.net.account@', N'@asp.net.account@' +GO + +if not exists (select * from dbo.sysusers where name = N'WebUser' and uid > 16399) + EXEC sp_addrole N'WebUser' +GO + +exec sp_addrolemember N'WebUser', N'@asp.net.account@' +GO + +use [@initial.catalog@] +GO + +GRANT SELECT ON Dock TO WebUser; +GRANT SELECT ON LeaseType TO WebUser; +GRANT SELECT ON Location TO WebUser; +GRANT SELECT ON Slip TO WebUser; +GRANT SELECT ON Lease TO WebUser; +GRANT SELECT ON Customer TO WebUser; +GRANT SELECT ON Authorize TO WebUser; +GRANT SELECT ON Boat TO WebUser; + +GRANT UPDATE ON Customer TO WebUser; +GRANT UPDATE ON Authorize TO WebUser; + +GRANT INSERT ON Authorize TO WebUser; +GRANT INSERT ON Boat TO WebUser; +GRANT INSERT ON Customer TO WebUser; +GRANT INSERT ON Lease TO WebUser; + +GRANT DELETE ON Boat TO WebUser; +GRANT DELETE ON Lease TO WebUser; +GO diff --git a/slips/build/sql/ddl/tables.sql.template b/slips/build/sql/ddl/tables.sql.template new file mode 100644 index 0000000..db741d6 --- /dev/null +++ b/slips/build/sql/ddl/tables.sql.template @@ -0,0 +1,166 @@ +USE [@initial.catalog@] +Go + +/****************************** CREATE LOCATION TABLE ***************************************/ + +CREATE TABLE [dbo].[Location]( + [LocationId] [int] IDENTITY(1,1) NOT NULL, + [LocationName] [varchar](50) NOT NULL, + CONSTRAINT [PK_Location] PRIMARY KEY CLUSTERED +( + [LocationId] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] +) ON [PRIMARY] + +GO + +/******************************** CREATE DOCK TABLE ****************************************/ + +CREATE TABLE [dbo].[Dock]( + [DockID] [int] IDENTITY(1,1) NOT NULL, + [DockName] [varchar](50) NOT NULL, + [LocationId] [int] NOT NULL, + [WaterService] [bit] NOT NULL, + [ElectricalService] [bit] NOT NULL, + CONSTRAINT [Dock_PK] PRIMARY KEY CLUSTERED +( + [DockID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] +) ON [PRIMARY] + +GO + +ALTER TABLE [dbo].[Dock] WITH CHECK ADD CONSTRAINT [FK_Dock_Location] FOREIGN KEY([LocationId]) +REFERENCES [dbo].[Location] ([LocationId]) +GO +ALTER TABLE [dbo].[Dock] CHECK CONSTRAINT [FK_Dock_Location] + + +/******************************** CREATE SLIP TABLE ****************************************/ + +CREATE TABLE [dbo].[Slip]( + [SlipID] [int] IDENTITY(1000,1) NOT NULL, + [SlipWidth] [int] NOT NULL, + [SlipLength] [int] NOT NULL, + [DockID] [int] NOT NULL, + CONSTRAINT [Slip_PK] PRIMARY KEY CLUSTERED +( + [SlipID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] +) ON [PRIMARY] + +GO +ALTER TABLE [dbo].[Slip] WITH CHECK ADD CONSTRAINT [Dock_Slip_FK1] FOREIGN KEY([DockID]) +REFERENCES [dbo].[Dock] ([DockID]) +GO +ALTER TABLE [dbo].[Slip] CHECK CONSTRAINT [Dock_Slip_FK1] + + +/******************************** CREATE LeaseType TABLE **********************************/ + +CREATE TABLE [dbo].[LeaseType]( + [LeaseTypeID] [int] IDENTITY(1,1) NOT NULL, + [LeaseTypeName] [varchar](50) NOT NULL, + [StandardRateAmount] [decimal](18, 2) NOT NULL, + CONSTRAINT [PK_LeaseType] PRIMARY KEY CLUSTERED +( + [LeaseTypeID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] +) ON [PRIMARY] + +GO + + +/******************************** CREATE CUSTOMER TABLE ***********************************/ + +CREATE TABLE [dbo].[Customer]( + [CustomerID] [int] IDENTITY(1000,1) NOT NULL, + [FirstName] [varchar](30) NOT NULL, + [LastName] [varchar](30) NOT NULL, + [Phone] [varchar](14) NOT NULL, + [City] [varchar](30) NOT NULL, + CONSTRAINT [Customer_PK] PRIMARY KEY CLUSTERED +( + [CustomerID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] +) ON [PRIMARY] + +GO + +/******************************** CREATE BOAT TABLE ***************************************/ + +CREATE TABLE [dbo].[Boat]( + [BoatID] [int] IDENTITY(1000,1) NOT NULL, + [RegistrationNumber] [varchar](20) NOT NULL, + [Manufacturer] [varchar](50) NOT NULL, + [ModelYear] [int] NOT NULL, + [Length] [int] NOT NULL, + [CustomerID] [int] NOT NULL, + CONSTRAINT [Boat_PK] PRIMARY KEY CLUSTERED +( + [BoatID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] +) ON [PRIMARY] + +GO +SET ANSI_PADDING OFF +GO +ALTER TABLE [dbo].[Boat] WITH CHECK ADD CONSTRAINT [Customer_Boat_FK1] FOREIGN KEY([CustomerID]) +REFERENCES [dbo].[Customer] ([CustomerID]) +GO +ALTER TABLE [dbo].[Boat] CHECK CONSTRAINT [Customer_Boat_FK1] + + +/******************************** CREATE AUTHORIZE TABLE **********************************/ + +CREATE TABLE [dbo].[Authorize]( + [AuthID] [int] IDENTITY(1,1) NOT NULL, + [UserName] [varchar](50) NOT NULL, + [Password] [varchar](14) NOT NULL, + [CustomerID] [int] NOT NULL, + CONSTRAINT [PK_Authorize] PRIMARY KEY CLUSTERED +( + [AuthID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] +) ON [PRIMARY] + +GO +SET ANSI_PADDING OFF +GO +ALTER TABLE [dbo].[Authorize] WITH CHECK ADD CONSTRAINT [FK_Authorize_Customer] FOREIGN KEY([CustomerID]) +REFERENCES [dbo].[Customer] ([CustomerID]) +GO +ALTER TABLE [dbo].[Authorize] CHECK CONSTRAINT [FK_Authorize_Customer] + + +/******************************** CREATE LEASE TABLE *************************************/ + +CREATE TABLE [dbo].[Lease]( + [LeaseID] [int] IDENTITY(1000,1) NOT NULL, + [StartDate] [datetime] NOT NULL, + [EndDate] [datetime] NOT NULL, + [SlipID] [int] NOT NULL, + [CustomerID] [int] NOT NULL, + [LeaseTypeID] [int] NOT NULL, + CONSTRAINT [PK_Lease] PRIMARY KEY CLUSTERED +( + [LeaseID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] +) ON [PRIMARY] + +GO +ALTER TABLE [dbo].[Lease] WITH CHECK ADD CONSTRAINT [FK_Lease_Customer] FOREIGN KEY([CustomerID]) +REFERENCES [dbo].[Customer] ([CustomerID]) +GO +ALTER TABLE [dbo].[Lease] CHECK CONSTRAINT [FK_Lease_Customer] +GO +ALTER TABLE [dbo].[Lease] WITH CHECK ADD CONSTRAINT [FK_Lease_LeaseType] FOREIGN KEY([LeaseTypeID]) +REFERENCES [dbo].[LeaseType] ([LeaseTypeID]) +GO +ALTER TABLE [dbo].[Lease] CHECK CONSTRAINT [FK_Lease_LeaseType] +GO +ALTER TABLE [dbo].[Lease] WITH CHECK ADD CONSTRAINT [FK_Lease_Slip] FOREIGN KEY([SlipID]) +REFERENCES [dbo].[Slip] ([SlipID]) +GO +ALTER TABLE [dbo].[Lease] CHECK CONSTRAINT [FK_Lease_Slip] +GO \ No newline at end of file diff --git a/slips/build/sql/original.sql.template b/slips/build/sql/original.sql.template new file mode 100644 index 0000000..3a58e01 Binary files /dev/null and b/slips/build/sql/original.sql.template differ -- cgit v1.2.3