/*Start of .sql script - the CREATE TABLE blocks have descriptions of each table*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Trips_Resort]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Trips] DROP CONSTRAINT FK_Trips_Resort
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Trips_TripTypes]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Trips] DROP CONSTRAINT FK_Trips_TripTypes
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_TripInfo_Trips]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TripInfo] DROP CONSTRAINT FK_TripInfo_Trips
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_TripsUsers_Trips]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TripsUsers] DROP CONSTRAINT FK_TripsUsers_Trips
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_TripInfo_Users]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TripInfo] DROP CONSTRAINT FK_TripInfo_Users
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_TripsUsers_Users]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TripsUsers] DROP CONSTRAINT FK_TripsUsers_Users
GO
/****** Object: Table [dbo].[Resort] Script Date: 9/24/2003 10:46:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Resort]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Resort]
GO
/****** Object: Table [dbo].[TripInfo] Script Date: 9/24/2003 10:46:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TripInfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TripInfo]
GO
/****** Object: Table [dbo].[TripTypes] Script Date: 9/24/2003 10:46:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TripTypes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TripTypes]
GO
/****** Object: Table [dbo].[Trips] Script Date: 9/24/2003 10:46:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Trips]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Trips]
GO
/****** Object: Table [dbo].[TripsUsers] Script Date: 9/24/2003 10:46:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TripsUsers]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TripsUsers]
GO
/****** Object: Table [dbo].[Users] Script Date: 9/24/2003 10:46:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Users]
GO
/****** Object: Table [dbo].[ZipDistances] Script Date: 9/24/2003 10:46:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ZipDistances]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ZipDistances]
GO
/****** Object: Table [dbo].[ZipMaster] Script Date: 9/24/2003 10:46:09 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ZipMaster]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ZipMaster]
GO
/****** Object: Table [dbo].[Resort] This table stores the names of the resorts assoc. with trips, and is a search param. Data is fixed******/
CREATE TABLE [dbo].[Resort] (
[ResortID] [tinyint] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[TripInfo] This table stores any text message the Users on a trip want to say to the other users on a trip******/
CREATE TABLE [dbo].[TripInfo] (
[TripID] [int] NOT NULL ,
[TimeStamp] [datetime] NOT NULL ,
[UserID] [int] NOT NULL ,
[Message] [varchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[TripTypes] 3 fixed rows ******/
CREATE TABLE [dbo].[TripTypes] (
[TripTypeID] [tinyint] IDENTITY (1, 1) NOT NULL ,
[TripTypeName] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[Trips] One record for each Trip created by a User ******/
CREATE TABLE [dbo].[Trips] (
[TripID] [int] IDENTITY (1, 1) NOT NULL ,
[TripDate] [smalldatetime] NOT NULL ,
[Description] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ResortID] [tinyint] NOT NULL ,
[MaxPassengers] [tinyint] NOT NULL ,
[TripTypeID] [tinyint] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[TripsUsers] One record for each user on a Trip(TripID & UserID is composite key)******/
CREATE TABLE [dbo].[TripsUsers] (
[TripID] [int] NOT NULL ,
[UserID] [int] NOT NULL ,
[IsDriver] [bit] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Users]One record for each User registered to use the web site******/
CREATE TABLE [dbo].[Users] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Password] [char] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Address] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ZipCode] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Email] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ReceiveEmailsFromSkiCarpool] [bit] NOT NULL ,
[Phone] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ExposePhone] [bit] NOT NULL ,
[Age] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ReceiveEmailsFromPartners] [bit] NOT NULL ,
[Gender] [bit] NOT NULL ,
[MemberSince] [smalldatetime] NULL ,
[EmailFormat] [bit] NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[ZipDistances] 865 x 865 rows of data - stores the distance from each zip code in Colorado to EVERY OTHER zip code in the state so that no calculations are ever done when Users search for trips******/
CREATE TABLE [dbo].[ZipDistances] (
[ZipCodeFrom] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ZipCodeTo] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Distance] [decimal](4, 1) NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[ZipMaster] ~865 rows - one for each zip code in Colorado ******/
CREATE TABLE [dbo].[ZipMaster] (
[Zip] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Name] [char] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Lat] [decimal](8, 6) NOT NULL ,
[Long] [decimal](9, 6) NOT NULL
) ON [PRIMARY]
GO
/*End of .sql script*/
Here's some sample data for tables:
Users:
1 billybob 1234 80001 skins@bwn.com 1 1 1 0 1
2 me mypwd 80002 me@me.com 1 1 1 1 1
3 you 3pwd 80003 you@me.com 1 1 1 1 1
Resorts:
1 Copper
2 Keystone
Trips:
46 1/1/2003 testtrip1 1 4 3
47 2/2/2003 testtrip2 2 4 3
48 2/2/2003 testtrip3 2 4 3
TripsUsers:
46 1 0
46 2 0
46 3 1
47 1 0
47 2 1
48 3 0
TripTypes:
1 Skiers Only
2 Boarders Only
3 Skiers and Boarders
ZipDistances:
80001 80001 0
80001 80002 21.9
80001 80003 22.5
ZipMaster and TripInfo tables should not need any sample data.
And here are sample parameters:
SELECT @csvResortIDs = '1,2'
SELECT @csvTripTypes = '1,2,3'
SELECT @ZipFrom = '80001'
SELECT @Distance = '700'
SELECT @StartDate = '1/1/2000'
SELECT @EndDate = '1/1/2004'
SELECT @NeedsA = 2
SELECT @ShowFull = 1
