Below is the script used to generate the database used for development and testing. The version of SQL Server used is Microsoft SQL Server 2012 Developer Edition with Service Pack 1 . It has not been tested with the Express Edition which is free but it may very well work with that version as well.

Before this project will run you will need an installed version of SQL Server with this database. You will also need to have a user ID for use by the program which will permit read and write access to the database.


/*
Deployment script for Twitter

This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


GO
:setvar DatabaseName "Twitter"
:setvar DefaultFilePrefix "Twitter"
:setvar DefaultDataPath "E:\MSSQL\Data\"
:setvar DefaultLogPath "E:\MSSQL\Logs\"

GO
:on error exit
GO
/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF;
*/
:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
    BEGIN
        PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
        SET NOEXEC ON;
    END


GO
USE [master];


GO

IF (DB_ID(N'$(DatabaseName)') IS NOT NULL)
BEGIN
    ALTER DATABASE [$(DatabaseName)]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [$(DatabaseName)];
END

GO
PRINT N'Creating $(DatabaseName)...'
GO
CREATE DATABASE [$(DatabaseName)]
    ON
    PRIMARY(NAME = [$(DatabaseName)], FILENAME = N'$(DefaultDataPath)$(DefaultFilePrefix)_Primary.mdf')
    LOG ON (NAME = [$(DatabaseName)_log], FILENAME = N'$(DefaultLogPath)$(DefaultFilePrefix)_Primary.ldf') COLLATE SQL_Latin1_General_CP1_CI_AS
GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET ANSI_NULLS ON,
                ANSI_PADDING ON,
                ANSI_WARNINGS ON,
                ARITHABORT ON,
                CONCAT_NULL_YIELDS_NULL ON,
                NUMERIC_ROUNDABORT OFF,
                QUOTED_IDENTIFIER ON,
                ANSI_NULL_DEFAULT ON,
                CURSOR_DEFAULT LOCAL,
                RECOVERY FULL,
                CURSOR_CLOSE_ON_COMMIT OFF,
                AUTO_CREATE_STATISTICS ON,
                AUTO_SHRINK OFF,
                AUTO_UPDATE_STATISTICS ON,
                RECURSIVE_TRIGGERS OFF
            WITH ROLLBACK IMMEDIATE;
        ALTER DATABASE [$(DatabaseName)]
            SET AUTO_CLOSE OFF
            WITH ROLLBACK IMMEDIATE;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET ALLOW_SNAPSHOT_ISOLATION OFF;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET READ_COMMITTED_SNAPSHOT OFF;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET AUTO_UPDATE_STATISTICS_ASYNC OFF,
                PAGE_VERIFY NONE,
                DATE_CORRELATION_OPTIMIZATION OFF,
                DISABLE_BROKER,
                PARAMETERIZATION SIMPLE,
                SUPPLEMENTAL_LOGGING OFF
            WITH ROLLBACK IMMEDIATE;
    END


GO
IF IS_SRVROLEMEMBER(N'sysadmin') = 1
    BEGIN
        IF EXISTS (SELECT 1
                   FROM   [master].[dbo].[sysdatabases]
                   WHERE  [name] = N'$(DatabaseName)')
            BEGIN
                EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)]
    SET TRUSTWORTHY OFF,
        DB_CHAINING OFF
    WITH ROLLBACK IMMEDIATE';
            END
    END
ELSE
    BEGIN
        PRINT N'The database settings cannot be modified. You must be a SysAdmin to apply these settings.';
    END


GO
IF IS_SRVROLEMEMBER(N'sysadmin') = 1
    BEGIN
        IF EXISTS (SELECT 1
                   FROM   [master].[dbo].[sysdatabases]
                   WHERE  [name] = N'$(DatabaseName)')
            BEGIN
                EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)]
    SET HONOR_BROKER_PRIORITY OFF
    WITH ROLLBACK IMMEDIATE';
            END
    END
ELSE
    BEGIN
        PRINT N'The database settings cannot be modified. You must be a SysAdmin to apply these settings.';
    END


GO
ALTER DATABASE [$(DatabaseName)]
    SET TARGET_RECOVERY_TIME = 0 SECONDS
    WITH ROLLBACK IMMEDIATE;


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET FILESTREAM(NON_TRANSACTED_ACCESS = OFF),
                CONTAINMENT = NONE
            WITH ROLLBACK IMMEDIATE;
    END


GO
USE [$(DatabaseName)];


GO
IF fulltextserviceproperty(N'IsFulltextInstalled') = 1
    EXECUTE sp_fulltext_database 'enable';


GO
PRINT N'Creating [Feed1]...';


GO
CREATE USER [Feed1] FOR LOGIN [Feed1];


GO
REVOKE CONNECT TO [Feed1];


GO
PRINT N'Creating <unnamed>...';


GO
EXECUTE sp_addrolemember @rolename = N'db_datareader', @membername = N'Feed1';


GO
PRINT N'Creating <unnamed>...';


GO
EXECUTE sp_addrolemember @rolename = N'db_datawriter', @membername = N'Feed1';


GO
PRINT N'Creating [dbo].[Accounts]...';


GO
CREATE TABLE [dbo].[Accounts] (
    [AccountID]    BIGINT         IDENTITY (1, 1) NOT NULL,
    [Account Name] NVARCHAR (500) NOT NULL,
    CONSTRAINT [PK_Accounts] PRIMARY KEY CLUSTERED ([AccountID] ASC)
);


GO
PRINT N'Creating [dbo].[Hashtag_Tweet]...';


GO
CREATE TABLE [dbo].[Hashtag_Tweet] (
    [Hashtag]  NVARCHAR (100) NOT NULL,
    [StatusID] BIGINT         NOT NULL
);


GO
PRINT N'Creating [dbo].[Hashtag_Tweet].[IX_Hashtag_Tweet]...';


GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Hashtag_Tweet]
    ON [dbo].[Hashtag_Tweet]([Hashtag] ASC, [StatusID] ASC) WITH (IGNORE_DUP_KEY = ON);


GO
PRINT N'Creating [dbo].[SearchTerms]...';


GO
CREATE TABLE [dbo].[SearchTerms] (
    [SearchID]   INT            IDENTITY (1, 1) NOT NULL,
    [AccountID]  BIGINT         NOT NULL,
    [Active]     BIT            NOT NULL,
    [SearchTerm] NVARCHAR (500) NOT NULL
);


GO
PRINT N'Creating [dbo].[Tweet]...';


GO
CREATE TABLE [dbo].[Tweet] (
    [StatusID]           BIGINT         NOT NULL,
    [UserID]             BIGINT         NOT NULL,
    [PossiblySensitive]  BIT            NULL,
    [RetweetCount]       INT            NULL,
    [Retweeted]          BIT            NULL,
    [Text]               NVARCHAR (500) NOT NULL,
    [CreatedAt]          DATETIME       NOT NULL,
    [ScreenNameResponse] NVARCHAR (500) NOT NULL,
    [UserIDResponse]     NCHAR (500)    NOT NULL
);


GO
PRINT N'Creating [dbo].[Tweet].[IX_TweetTest]...';


GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_TweetTest]
    ON [dbo].[Tweet]([StatusID] ASC) WITH (IGNORE_DUP_KEY = ON);


GO
PRINT N'Creating [dbo].[Tweet_URL]...';


GO
CREATE TABLE [dbo].[Tweet_URL] (
    [StatusID]    BIGINT         NOT NULL,
    [DisplayURL]  NVARCHAR (MAX) NOT NULL,
    [ExpandedURL] NVARCHAR (MAX) NOT NULL,
    [URL]         NVARCHAR (200) NOT NULL
);


GO
PRINT N'Creating [dbo].[Tweet_URL].[IX_Tweet_URL]...';


GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Tweet_URL]
    ON [dbo].[Tweet_URL]([StatusID] ASC, [URL] ASC) WITH (IGNORE_DUP_KEY = ON);


GO
PRINT N'Creating [dbo].[Tweet_User]...';


GO
CREATE TABLE [dbo].[Tweet_User] (
    [StatusID]           BIGINT         NOT NULL,
    [UserID]             BIGINT         NOT NULL,
    [CreatedAt]          DATETIME       NOT NULL,
    [Description]        NVARCHAR (500) NOT NULL,
    [FavoritesCount]     INT            NOT NULL,
    [FollowersCount]     INT            NOT NULL,
    [FriendsCount]       INT            NOT NULL,
    [GeoEnabled]         BIT            NOT NULL,
    [IncludeEntities]    BIT            NOT NULL,
    [IsTranslator]       BIT            NOT NULL,
    [Lang]               NVARCHAR (50)  NOT NULL,
    [LangResponse]       NVARCHAR (50)  NOT NULL,
    [ListedCount]        INT            NOT NULL,
    [Location]           NVARCHAR (500) NOT NULL,
    [Name]               NVARCHAR (500) NOT NULL,
    [Notifications]      BIT            NOT NULL,
    [Protected]          BIT            NOT NULL,
    [ScreenName]         NVARCHAR (500) NULL,
    [ScreenNameList]     NVARCHAR (500) NULL,
    [ScreenNameResponse] NVARCHAR (500) NOT NULL,
    [StatusesCount]      INT            NOT NULL,
    [Url]                NVARCHAR (MAX) NULL,
    [UserIDResponse]     NVARCHAR (200) NOT NULL,
    [Verified]           BIT            NOT NULL
);


GO
PRINT N'Creating [dbo].[Tweet_User].[IX_Tweet_User]...';


GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Tweet_User]
    ON [dbo].[Tweet_User]([StatusID] ASC) WITH (IGNORE_DUP_KEY = ON);


GO
PRINT N'Creating [dbo].[Tweet_UserMention]...';


GO
CREATE TABLE [dbo].[Tweet_UserMention] (
    [UserID]   BIGINT NOT NULL,
    [StatusID] BIGINT NOT NULL
);


GO
PRINT N'Creating [dbo].[Tweet_UserMention].[IX_Tweet_UserMention]...';


GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_Tweet_UserMention]
    ON [dbo].[Tweet_UserMention]([UserID] ASC, [StatusID] ASC) WITH (IGNORE_DUP_KEY = ON);


GO
PRINT N'Creating [dbo].[TweetSharp_Tweet]...';


GO
CREATE TABLE [dbo].[TweetSharp_Tweet] (
    [ID]                  BIGINT          NOT NULL,
    [InReplyToScreenName] NVARCHAR (2000) NULL,
    [InReplyToStatusId]   BIGINT          NULL,
    [InReplyToUserId]     BIGINT          NULL,
    [IsFavorited]         BIT             NOT NULL,
    [IsPossiblySensitive] BIT             NULL,
    [IsTruncated]         BIT             NOT NULL,
    [Language]            NVARCHAR (50)   NULL,
    [Location]            NVARCHAR (2000) NULL,
    [Place]               NVARCHAR (2000) NULL,
    [RawSource]           NVARCHAR (MAX)  NULL,
    [RetweetCount]        INT             NULL,
    [RetweetedStatus]     NVARCHAR (2000) NULL,
    [Source]              NVARCHAR (2000) NULL,
    [Text]                NVARCHAR (2000) NULL,
    [TextAsHtml]          NVARCHAR (2000) NULL,
    [TextDecoded]         NVARCHAR (2000) NULL,
    [User_ID]             BIGINT          NULL,
    [User_FollowersCount] BIGINT          NULL,
    [Users_RetweetCount]  BIGINT          NULL,
    [Author_ScreenName]   NVARCHAR (2000) NULL,
    [CreatedDate]         DATETIME        NOT NULL,
    [ST_Insert_Date]      DATETIME        NOT NULL,
    [ST_Ascension_No]     BIGINT          NOT NULL,
    CONSTRAINT [PK_TweetSharp_Tweet] PRIMARY KEY CLUSTERED ([ID] ASC)
);


GO
PRINT N'Creating DF_TweetSharp_Tweet_ST_Insert_Date...';


GO
ALTER TABLE [dbo].[TweetSharp_Tweet]
    ADD CONSTRAINT [DF_TweetSharp_Tweet_ST_Insert_Date] DEFAULT (sysutcdatetime()) FOR [ST_Insert_Date];


GO
PRINT N'Creating [dbo].[ST_Ascension_Number]...';


GO
CREATE SEQUENCE [dbo].[ST_Ascension_Number]
    AS BIGINT
    INCREMENT BY 1
    MINVALUE 1;


GO
PRINT N'Creating DF_TweetSharp_Tweet_ST_Ascension_No...';


GO
ALTER TABLE [dbo].[TweetSharp_Tweet]
    ADD CONSTRAINT [DF_TweetSharp_Tweet_ST_Ascension_No] DEFAULT (NEXT VALUE FOR [ST_Ascension_Number]) FOR [ST_Ascension_No];


GO
PRINT N'Creating FK_Hashtag_Tweet_Tweet...';


GO
ALTER TABLE [dbo].[Hashtag_Tweet]
    ADD CONSTRAINT [FK_Hashtag_Tweet_Tweet] FOREIGN KEY ([StatusID]) REFERENCES [dbo].[Tweet] ([StatusID]);


GO
PRINT N'Creating FK_SearchTerms_Accounts...';


GO
ALTER TABLE [dbo].[SearchTerms]
    ADD CONSTRAINT [FK_SearchTerms_Accounts] FOREIGN KEY ([AccountID]) REFERENCES [dbo].[Accounts] ([AccountID]);


GO
PRINT N'Creating FK_Tweet_URL_Tweet...';


GO
ALTER TABLE [dbo].[Tweet_URL]
    ADD CONSTRAINT [FK_Tweet_URL_Tweet] FOREIGN KEY ([StatusID]) REFERENCES [dbo].[Tweet] ([StatusID]);


GO
PRINT N'Creating FK_Tweet_User_Tweet...';


GO
ALTER TABLE [dbo].[Tweet_User]
    ADD CONSTRAINT [FK_Tweet_User_Tweet] FOREIGN KEY ([StatusID]) REFERENCES [dbo].[Tweet] ([StatusID]);


GO
PRINT N'Creating FK_Tweet_UserMention_Tweet...';


GO
ALTER TABLE [dbo].[Tweet_UserMention]
    ADD CONSTRAINT [FK_Tweet_UserMention_Tweet] FOREIGN KEY ([StatusID]) REFERENCES [dbo].[Tweet] ([StatusID]);


GO
DECLARE @VarDecimalSupported AS BIT;

SELECT @VarDecimalSupported = 0;

IF ((ServerProperty(N'EngineEdition') = 3)
    AND (((@@microsoftversion / power(2, 24) = 9)
          AND (@@microsoftversion & 0xffff >= 3024))
         OR ((@@microsoftversion / power(2, 24) = 10)
             AND (@@microsoftversion & 0xffff >= 1600))))
    SELECT @VarDecimalSupported = 1;

IF (@VarDecimalSupported > 0)
    BEGIN
        EXECUTE sp_db_vardecimal_storage_format N'$(DatabaseName)', 'ON';
    END


GO
PRINT N'Update complete.';


GO

Last edited Mar 6, 2014 at 4:31 AM by JonnyBoats, version 1