/* Create tables, foreign keys, and indexes. Created: 2008-10-31 */ -- Drop tables - at first children then parents IF OBJECT_ID('SearchWord', 'U') IS NOT NULL DROP TABLE SearchWord GO IF OBJECT_ID('BookUpdate', 'U') IS NOT NULL DROP TABLE BookUpdate GO IF OBJECT_ID('Book', 'U') IS NOT NULL DROP TABLE Book GO IF OBJECT_ID('SyncKeyword', 'U') IS NOT NULL DROP TABLE SyncKeyword GO IF OBJECT_ID('PublisherSearchWord', 'U') IS NOT NULL DROP TABLE PublisherSearchWord GO IF OBJECT_ID('Publisher', 'U') IS NOT NULL DROP TABLE Publisher GO IF OBJECT_ID('CategorySearchWord', 'U') IS NOT NULL DROP TABLE CategorySearchWord GO IF OBJECT_ID('Category', 'U') IS NOT NULL DROP TABLE Category GO IF OBJECT_ID('UserKeyword', 'U') IS NOT NULL DROP TABLE UserKeyword GO IF OBJECT_ID('BookType', 'U') IS NOT NULL DROP TABLE BookType GO -- Create dictionaries (parents) /******************** Publisher *********************/ CREATE TABLE Publisher ( PubId INT IDENTITY(1,1) PRIMARY KEY, -- DisplayName of Publisher PubName NVARCHAR(250) NOT NULL DEFAULT '', -- Publisher name in a form kept in the Amazon database (used for lookups as a key) AmazonPublisher NVARCHAR(250) NOT NULL DEFAULT '' ) ON [PRIMARY] GO /******************** Category *********************/ CREATE TABLE Category ( CatId INT IDENTITY(1,1) PRIMARY KEY, -- DisplayName of Category CatName NVARCHAR(50) NOT NULL DEFAULT '', -- Description of the category provided by an administrator CatDesc NVARCHAR(250) NOT NULL DEFAULT '', -- Counter of "category popularity" CatCounter INT NOT NULL DEFAULT 0 ) ON [PRIMARY] GO /******************** BookType *********************/ CREATE TABLE BookType ( -- Book type id BookTypeId INT PRIMARY KEY, -- A short description (one or two words) of the book type BookTypeDesc VARCHAR(50) NOT NULL DEFAULT '' ) ON [PRIMARY] GO -- Create data tables /******************** SyncKeyword *********************/ CREATE TABLE SyncKeyword ( -- Id is used for sorting. It has to be inserted explicitly. SyncKeyId INT NOT NULL PRIMARY KEY, -- Keyword used to update catalog or find new books in Amazon, e.g. directx, quaternion, 3d SyncKey VARCHAR(50) NOT NULL DEFAULT '', -- Description of the keyword. SyncKeyDesc VARCHAR(100) NOT NULL DEFAULT '', -- The Amazon BrowseNodeId of a category related to the keyword. -- NOTE: BrowseNodeIds are locale-specific (Books Node Id: CA:927726, US:1000) AmazonBrowseNodeId INT NOT NULL DEFAULT 0, -- Default category for the sync keyword (optional) DefaultCatId INT NOT NULL CONSTRAINT FK_Category_SyncKeyword FOREIGN KEY REFERENCES Category(CatId) DEFAULT 0 ) ON [PRIMARY] GO /******************** Book Remarks: We do not store ASIN here as the TaraRara Catalog does not allow books without ISBN. For books with ISBN, ASIN is the same as the ISBN10. *********************/ CREATE TABLE Book ( BookId INT IDENTITY(1,1) PRIMARY KEY, -- Publisher of the Book PubId INT NOT NULL CONSTRAINT FK_Publisher_Book FOREIGN KEY REFERENCES Publisher(PubId), -- Category the Book belongs to CatId INT NOT NULL CONSTRAINT FK_Category_Book FOREIGN KEY REFERENCES Category(CatId), -- Full tile (together with subtitle) as in Amazon (should not be modified as it is used as a key in -- the case of a missing ISBN) Title NVARCHAR(250) NOT NULL, -- A comma-separated list of authors as in Amazon Authors NVARCHAR(520) NOT NULL, -- 10-digit ISBN (no spaces or separators) Isbn10 CHAR(10) NOT NULL, -- 13-digit ISBN (no spaces or separators). -- ISBN13 may be artificially created by prepending ASIN with "978" if a book does not have an ISBN. -- In such a case the IsAsin flag is 1 and ISBN13 should not be used to identify the book. Isbn13 CHAR(13) NOT NULL, -- Date of publication PubDate DATETIME NOT NULL, -- Some publication dates may miss the day part IsDayMissing BIT NOT NULL DEFAULT 0, -- Some publication dates (especially for old books) may miss the day and the month part IsMonthMissing BIT NOT NULL DEFAULT 0, -- Price of the Book Price MONEY NOT NULL, -- Average user rating (from 0.0 to 5.0) -- 2 (precision) - total number of decimal digits -- 1 (scale) - the number of decimal digits that can be stored to the right of the decimal point Rating DECIMAL(2,1) NOT NULL DEFAULT 0.0, -- Number of user ratings NumRatings INT NOT NULL DEFAULT 0, -- Number of pages NumPages INT NOT NULL DEFAULT 0, -- Hyperlink to the Book on the Amazon website AmazonBookLink VARCHAR(250) NOT NULL DEFAULT '', -- Hyperlink to the image of the Book on the Amazon website AmazonImageLink VARCHAR(250) NOT NULL DEFAULT '', -- DVD filepath to the Book (optional - for internal purposes) EBookPath VARCHAR(100) NOT NULL DEFAULT '', -- Keyword that was used to find the books in Amazon, e.g. directx, quaternion, 3d SyncKeyId INT NOT NULL CONSTRAINT FK_SyncKeyword_Book FOREIGN KEY REFERENCES SyncKeyword(SyncKeyId), -- BookType - default value is None BookTypeId INT NOT NULL CONSTRAINT FK_BookType_Book FOREIGN KEY REFERENCES BookType(BookTypeId) DEFAULT 0, -- Comment provided by an administrator during book evaluation Comment VARCHAR(250) NOT NULL DEFAULT '', -- The date and time the book was fetched from Amazon DateAdded DATETIME NOT NULL DEFAULT GETDATE(), -- The last date the book was updated. DateUpdated DATETIME NOT NULL DEFAULT GETDATE(), -- Indicates if the Isbn10 column contains ASIN or a real ISBN. IsAsin BIT NOT NULL DEFAULT 0, -- BookStatus: 1-New, 2-Private, 3-Public BookStatus INT NOT NULL DEFAULT 1 CHECK (BookStatus IN (1, 2, 3)) ) ON [PRIMARY] GO CREATE UNIQUE INDEX UX_Books_Isbn10 ON Book(Isbn10) -- used to request books from Amazon CREATE UNIQUE INDEX UX_Books_Isbn13 ON Book(Isbn13) -- just for informational purposes GO /******************** SearchWord *********************/ CREATE TABLE SearchWord ( WordId INT IDENTITY(1,1) PRIMARY KEY, -- Referenced Book BookId INT NOT NULL CONSTRAINT FK_Book_SearchWord FOREIGN KEY REFERENCES Book(BookId), -- A source field of the word (just for informational purposes): -- 1-Book.Title, 2-Book.Author, 3-Book.Isbn10/13, 10-Publisher.PubName, 20-Category.CatName SearchWordType INT NOT NULL CHECK (SearchWordType IN (1, 2, 3, 10, 20)), -- Actual text of the search word Word NVARCHAR(50) ) ON [PRIMARY] GO /******************** PublisherSearchWord *********************/ CREATE TABLE PublisherSearchWord ( WordId INT IDENTITY(1,1) PRIMARY KEY, -- Referenced Publisher PubId INT NOT NULL CONSTRAINT FK_Publisher_PublisherSearchWord FOREIGN KEY REFERENCES Publisher(PubId), -- Actual text of the search word Word NVARCHAR(50) ) ON [PRIMARY] GO /******************** CategorySearchWord *********************/ CREATE TABLE CategorySearchWord ( WordId INT IDENTITY(1,1) PRIMARY KEY, -- Referenced Publisher CatId INT NOT NULL CONSTRAINT FK_Category_CategorySearchWord FOREIGN KEY REFERENCES Category(CatId), -- Actual text of the search word Word NVARCHAR(50) ) ON [PRIMARY] GO /***************************** BookUpdate ******************************/ CREATE TABLE BookUpdate ( UpdateId INT IDENTITY(1,1) PRIMARY KEY, -- BookId from Book table BookId INT NOT NULL CONSTRAINT FK_Book_BookUpdate FOREIGN KEY REFERENCES Book(BookId), -- Schema name of the updated attribute. -- AttrName can contain any attribute from the Book table. -- Attributes of most interest are: PubDate, Price, Rating, and NumRatings. AttrName VARCHAR(50) NOT NULL DEFAULT '', -- Previous value of the updated attribute. -- NVARCHAR(520) is the type of Book.Authors OldValue NVARCHAR(520) NOT NULL DEFAULT '', -- Date/time the book's attribute has been updated DateUpdated DATETIME NOT NULL DEFAULT GETDATE() ) ON [PRIMARY] GO /***************************** UserKeyword ******************************/ CREATE TABLE UserKeyword ( UserKeywordId INT IDENTITY(1,1) PRIMARY KEY, -- Keywords as entered by a user UserKeywords NVARCHAR(1000) NOT NULL DEFAULT '', -- Keywords entered by a user after formatting (i.e. converted to SearchWords). They are used for searching. FormattedKeywords NVARCHAR(1000) NOT NULL DEFAULT '', -- Date/time a user issued the search DateEntered DATETIME NOT NULL DEFAULT GETDATE(), -- User's ASP.NET SessionId SessionId CHAR(20) NOT NULL ) ON [PRIMARY] GO /***************************** Create triggers ******************************/ -- IMPORTANT: Book_RecordBookUpdates assumes that only a single row is updated at a time. -- Remarks: We can't use the UPDATE() function as it indicates only that the column has been updated -- not if it has another value. -- Triggers are dropped together with tables. To drop a trigger explicitly use the following code: -- IF OBJECT_ID('Book_RecordBookUpdates', 'TR') IS NOT NULL DROP TRIGGER Book_RecordBookUpdates -- GO CREATE TRIGGER Book_RecordBookUpdates ON Book FOR UPDATE AS BEGIN SET NOCOUNT ON -- DateUpdated is used by all fields. DECLARE @DateUpdated DATETIME SELECT @DateUpdated=DateUpdated FROM Inserted -- No need to check if the publisher, title, or author changed to a different value as this is already tested -- in the business layer in order to determine search words. IF UPDATE(PubId) INSERT INTO BookUpdate(del.BookId, AttrName, OldValue, DateUpdated) SELECT BookId, 'PubName', pub.PubName, @DateUpdated FROM Deleted del JOIN Publisher pub ON pub.PubId=del.PubId IF UPDATE(Title) INSERT INTO BookUpdate(BookId, AttrName, OldValue, DateUpdated) SELECT BookId, 'Title', Title, @DateUpdated FROM Deleted IF UPDATE(Authors) INSERT INTO BookUpdate(BookId, AttrName, OldValue, DateUpdated) SELECT BookId, 'Authors', Authors, @DateUpdated FROM Deleted IF UPDATE(Isbn13) INSERT INTO BookUpdate(BookId, AttrName, OldValue, DateUpdated) SELECT BookId, 'Isbn13', Isbn13, @DateUpdated FROM Deleted DECLARE @NewPubDate DATETIME, @OldPubDate DATETIME SELECT @NewPubDate=PubDate FROM Inserted SELECT @OldPubDate=PubDate FROM Deleted IF @NewPubDate<>@OldPubDate INSERT INTO BookUpdate(BookId, AttrName, OldValue, DateUpdated) SELECT BookId, 'PubDate', CONVERT(NVARCHAR(520), PubDate, 101), @DateUpdated FROM Deleted -- use 'mm/dd/yyyy' format DECLARE @NewIsDayMissing BIT, @OldIsDayMissing BIT SELECT @NewIsDayMissing=IsDayMissing FROM Inserted SELECT @OldIsDayMissing=IsDayMissing FROM Deleted IF @NewIsDayMissing<>@OldIsDayMissing INSERT INTO BookUpdate(BookId, AttrName, OldValue, DateUpdated) SELECT BookId, 'IsDayMissing', IsDayMissing, @DateUpdated FROM Deleted DECLARE @NewIsMonthMissing BIT, @OldIsMonthMissing BIT SELECT @NewIsMonthMissing=IsMonthMissing FROM Inserted SELECT @OldIsMonthMissing=IsMonthMissing FROM Deleted IF @NewIsMonthMissing<>@OldIsMonthMissing INSERT INTO BookUpdate(BookId, AttrName, OldValue, DateUpdated) SELECT BookId, 'IsMonthMissing', IsMonthMissing, @DateUpdated FROM Deleted DECLARE @NewPrice MONEY, @OldPrice MONEY SELECT @NewPrice=Price FROM Inserted SELECT @OldPrice=Price FROM Deleted IF @NewPrice<>@OldPrice INSERT INTO BookUpdate(BookId, AttrName, OldValue, DateUpdated) SELECT BookId, 'Price', CONVERT(NVARCHAR(520), Price), @DateUpdated FROM Deleted -- default formatting 42342.34 DECLARE @NewRating DECIMAL(2,1), @OldRating DECIMAL(2,1) SELECT @NewRating=Rating FROM Inserted SELECT @OldRating=Rating FROM Deleted IF @NewRating<>@OldRating INSERT INTO BookUpdate(BookId, AttrName, OldValue, DateUpdated) SELECT BookId, 'Rating', CONVERT(NVARCHAR(520), Rating), @DateUpdated FROM Deleted DECLARE @NewNumRatings INT, @OldNumRatings INT SELECT @NewNumRatings=NumRatings FROM Inserted SELECT @OldNumRatings=NumRatings FROM Deleted IF @NewNumRatings<>@OldNumRatings INSERT INTO BookUpdate(BookId, AttrName, OldValue, DateUpdated) SELECT BookId, 'NumRatings', CONVERT(NVARCHAR(520), NumRatings), @DateUpdated FROM Deleted DECLARE @NewNumPages INT, @OldNumPages INT SELECT @NewNumPages=NumPages FROM Inserted SELECT @OldNumPages=NumPages FROM Deleted IF @NewNumPages<>@OldNumPages INSERT INTO BookUpdate(BookId, AttrName, OldValue, DateUpdated) SELECT BookId, 'NumPages', CONVERT(NVARCHAR(520), NumPages), @DateUpdated FROM Deleted DECLARE @NewAmazonBookLink VARCHAR(250), @OldAmazonBookLink VARCHAR(250) SELECT @NewAmazonBookLink=AmazonBookLink FROM Inserted SELECT @OldAmazonBookLink=AmazonBookLink FROM Deleted IF @NewAmazonBookLink<>@OldAmazonBookLink INSERT INTO BookUpdate(BookId, AttrName, OldValue, DateUpdated) SELECT BookId, 'AmazonBookLink', AmazonBookLink, @DateUpdated FROM Deleted DECLARE @NewAmazonImageLink VARCHAR(250), @OldAmazonImageLink VARCHAR(250) SELECT @NewAmazonImageLink=AmazonImageLink FROM Inserted SELECT @OldAmazonImageLink=AmazonImageLink FROM Deleted IF @NewAmazonImageLink<>@OldAmazonImageLink INSERT INTO BookUpdate(BookId, AttrName, OldValue, DateUpdated) SELECT BookId, 'AmazonImageLink', AmazonImageLink, @DateUpdated FROM Deleted DECLARE @NewIsAsin BIT, @OldIsAsin BIT SELECT @NewIsAsin=IsAsin FROM Inserted SELECT @OldIsAsin=IsAsin FROM Deleted IF @NewIsAsin<>@OldIsAsin INSERT INTO BookUpdate(BookId, AttrName, OldValue, DateUpdated) SELECT BookId, 'IsAsin', IsAsin, @DateUpdated FROM Deleted SET NOCOUNT OFF END GO /***************************** Insert book types ******************************/ SET NOCOUNT ON INSERT INTO BookType VALUES (2, 'Foreign') INSERT INTO BookType VALUES (3, 'Not Available') INSERT INTO BookType VALUES (4, 'Research Paper / Article') INSERT INTO BookType VALUES (5, 'Duplicate') INSERT INTO BookType VALUES (6, 'Out Of Print') INSERT INTO BookType VALUES (7, 'eBook') INSERT INTO BookType VALUES (8, 'Box Set') INSERT INTO BookType VALUES (9, 'Future Category') -- books that can't be classified INSERT INTO BookType VALUES (10, 'Investigate') INSERT INTO BookType VALUES (11, 'Lecture Notes / Lab Manual') SET NOCOUNT OFF /***************************** Insert book categories and SyncKeywords ******************************/ DECLARE @CatId INT -- XNA EXEC @CatId = tar_AddCategory 'XNA Studio', 'XNA', 'xna' INSERT INTO SyncKeyword VALUES (10, 'xna', 'XNA (key)', 0, @CatId) -- DirectX EXEC @CatId = tar_AddCategory 'DirectX', 'DirectX', 'directx' INSERT INTO SyncKeyword VALUES (20, 'directx', 'DirectX', 3922, @CatId) INSERT INTO SyncKeyword VALUES (21, '', 'DirectX (obj)', 3922, @CatId) INSERT INTO SyncKeyword VALUES (22, 'directx', 'DirectX (key)', 0, @CatId) -- Open GL EXEC @CatId = tar_AddCategory 'Open GL', 'Open GL', 'open gl opengl' INSERT INTO SyncKeyword VALUES (30, '', 'Open GL (obj)', 3935, @CatId) INSERT INTO SyncKeyword VALUES (31, 'opengl', 'Open GL (key)', 0, @CatId) -- C++ EXEC @CatId = tar_AddCategory 'C++', 'C++ programming language', 'c++ programming language' INSERT INTO SyncKeyword VALUES (400, 'c++ language', 'C++ Language', 3961, @CatId) INSERT INTO SyncKeyword VALUES (405, 'c++', 'C++ Language (obj)', 3961, @CatId) INSERT INTO SyncKeyword VALUES (410, 'c++', 'C++ Algorithms (obj)', 3957, @CatId) INSERT INTO SyncKeyword VALUES (420, 'c++ algorithms', 'C++ Algorithms (key)', 0, @CatId) INSERT INTO SyncKeyword VALUES (490, 'c++ programming language', 'C++ All', 3956, @CatId) -- all books on C/C++ INSERT INTO SyncKeyword VALUES (492, 'c++', 'C++ All (obj)', 3956, @CatId) -- all books on C/C++ -- Game Programming EXEC @CatId = tar_AddCategory 'Game Programming', 'Game programming', 'game games programming' INSERT INTO SyncKeyword VALUES (500, 'game programming', 'Game Programming', 15375251, @CatId) INSERT INTO SyncKeyword VALUES (501, '', 'Game Programming (obj)', 15375251, @CatId) /***************************** Insert [Not Specified] records ******************************/ -- Insert "dummy" category SET IDENTITY_INSERT Category ON INSERT INTO Category(CatId, CatName, CatDesc) VALUES(0, '[Not Specified]', 'No category specified') SET IDENTITY_INSERT Category OFF -- Insert "dummy" syncKey INSERT INTO SyncKeyword VALUES (0, '[Not Specified]', '[Not Specified]', 0, 0) -- Insert "dummy" bookType INSERT INTO BookType VALUES (0, '[Not Specified]')