--------------------------------------------------------------
--
-- This installation script creates the MyComics database
-- and the Comics table. It also inserts several records into
-- the Comics table.
--
--------------------------------------------------------------

CREATE DATABASE MyComics
GO

USE MyComics
GO

CREATE TABLE [dbo].[Comics] (
	[ComicID] [int] IDENTITY (1000, 1) NOT NULL ,
	[CoverImage] [image] NULL ,
	[Title] [varchar] (32) NOT NULL ,
	[Number] [smallint] NOT NULL ,
	[Publisher] [varchar] (16)  NULL ,
	[Year] [smallint] NULL ,
	[Grade] [decimal] (3, 1) NULL ,
	[CGC] [bit] NULL ,
	[BookValue] [money] NULL ,
	[Comment] [varchar] (512) NULL,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Comics] ADD 
	CONSTRAINT [PK_Comics] PRIMARY KEY  CLUSTERED 
	(
		[ComicID]
	)  ON [PRIMARY] 
GO
delete comics
GO

INSERT INTO Comics (CoverImage, Title, Number, Publisher, Year, Grade, CGC, BookValue, Comment) VALUES (0x[BLOB], 'Bugslayer Comics', 100, 'Microsoft', 2000, 8.5, 0, 45.0000, 'Landmark 100th issue featuring notorious arch-villains from the past.')

INSERT INTO Comics (CoverImage, Title, Number, Publisher, Year, Grade, CGC, BookValue, Comment) VALUES (0x[BLOB], 'Bugslayer Comics', 108, 'Microsoft', 2000, 9.0, 0, 15.0000, 'First appearance of Dr. Spaghetti.')

INSERT INTO Comics (CoverImage, Title, Number, Publisher, Year, Grade, CGC, BookValue, Comment) VALUES (0x[BLOB], 'Bugslayer Comics', 111, 'Microsoft', 2001, 8.5, 1, 10.0000, 'Second appearance of Dr. Spaghetti.')

INSERT INTO Comics (CoverImage, Title, Number, Publisher, Year, Grade, CGC, BookValue, Comment) VALUES (0x[BLOB], 'Bugslayer Comics', 112, 'Microsoft', 2001, 9.0, 0, 12.0000, 'Insufficient unit testing causes a crisis on Earth One.')

INSERT INTO Comics (CoverImage, Title, Number, Publisher, Year, Grade, CGC, BookValue, Comment) VALUES (0x[BLOB], '.NET Man', 1, 'Microsoft', 2003, 9.0, 0, 175.0000, 'Landmark first issue featuring the origin of .NET Man.')

INSERT INTO Comics (CoverImage, Title, Number, Publisher, Year, Grade, CGC, BookValue, Comment) VALUES (0x[BLOB], '.NET Man', 2, 'Microsoft', 2003, 9.0, 0, 100.0000, '.NET Man battles the wizards of unmanaged code.')

INSERT INTO Comics (CoverImage, Title, Number, Publisher, Year, Grade, CGC, BookValue, Comment) VALUES (0x[BLOB], '.NET Man', 3, 'Microsoft', 2003, 9.0, 0, 65.0000, '.NET Man finds himself in a bind when the Garbage Collector jams.')

INSERT INTO Comics (CoverImage, Title, Number, Publisher, Year, Grade, CGC, BookValue, Comment) VALUES (0x[BLOB], '.NET Man', 4, 'Microsoft', 2003, 9.0, 0, 50.0000, 'First appearance of the evil Dr. RamCram.')

INSERT INTO Comics (CoverImage, Title, Number, Publisher, Year, Grade, CGC, BookValue, Comment) VALUES (0x[BLOB], '.NET Man', 5, 'Microsoft', 2003, 9.0, 1, 50.0000, 'The world learns .NET Man''s secret identity.')

INSERT INTO Comics (CoverImage, Title, Number, Publisher, Year, Grade, CGC, BookValue, Comment) VALUES (0x[BLOB], 'Captain Code', 1, 'Microsoft', 2003, 9.4, 1, 100.0000, 'Captain Code saves the day by implementing a fast reader-writer lock with just 3 lines of code.')

INSERT INTO Comics (CoverImage, Title, Number, Publisher, Year, Grade, CGC, BookValue, Comment) VALUES (0x[BLOB], 'Captain Code', 2, 'Microsoft', 2003, 9.2, 1, 65.0000, 'Captain Code brings order to the Internet by introducing Search.')

INSERT INTO Comics (CoverImage, Title, Number, Publisher, Year, Grade, CGC, BookValue, Comment) VALUES (0x[BLOB], 'Captain Code', 3, 'Microsoft', 2003, 9.0, 0, 55.0000, 'First appearance of Captain Code''s arch-nemesis Dr. CodeGen.')

INSERT INTO Comics (CoverImage, Title, Number, Publisher, Year, Grade, CGC, BookValue, Comment) VALUES (0x[BLOB], 'Captain Code', 4, 'Microsoft', 2003, 9.2, 0, 60.0000, 'Captain Code saves the planet by engaging Dr. CodeGen in a code duel.')

INSERT INTO Comics (CoverImage, Title, Number, Publisher, Year, Grade, CGC, BookValue, Comment) VALUES (0x[BLOB], 'Captain Code', 5, 'Microsoft', 2004, 9.0, 0, 40.0000, 'Captain Code travels to Mars in search of a missing dev lead.')

INSERT INTO Comics (CoverImage, Title, Number, Publisher, Year, Grade, CGC, BookValue, Comment) VALUES (0x[BLOB], 'Captain Code', 6, 'Microsoft', 2004, 9.0, 0, 35.0000, 'The origin of Captain Code, part 1.')

INSERT INTO Comics (CoverImage, Title, Number, Publisher, Year, Grade, CGC, BookValue, Comment) VALUES (0x[BLOB], 'Captain Code', 7, 'Microsoft', 2004, 9.2, 0, 35.0000, 'The origin of Captain Code, part 2.')

GO

CREATE TABLE [dbo].[SiteMap] (
	[Title] [varchar] (32) NOT NULL ,
	[Url]   [varchar] (512) NOT NULL,
	[Roles] [varchar] (512) NULL
) ON [PRIMARY]
GO

delete SiteMap
GO

INSERT INTO [dbo].[SiteMap] (Title, Url) VALUES ('Favorites', 'Favorites.aspx')
INSERT INTO [dbo].[SiteMap] (Title, Url, Roles) VALUES ('Admin', 'Secure/Admin.aspx', 'Administrators')
INSERT INTO [dbo].[SiteMap] (Title, Url) VALUES ('Hot Picks', 'HotPicks.aspx')
INSERT INTO [dbo].[SiteMap] (Title, Url) VALUES ('Events', 'Events.aspx')
INSERT INTO [dbo].[SiteMap] (Title, Url) VALUES ('Forums', 'Forums.aspx')
INSERT INTO [dbo].[SiteMap] (Title, Url) VALUES ('FAQ', 'FAQ.aspx')
GO