create database Squid
go

use Squid
go

if exists (select * from sysobjects where name = 'FeedCreation' and xtype = 'u')
drop table FeedCreation

if exists (select * from sysobjects where name = 'Snippet' and xtype = 'u')
drop table Snippet

if exists (select * from sysobjects where name = 'SnippetGroup' and xtype = 'u')
drop table SnippetGroup

if exists (select * from sysobjects where name = 'SnippetDated' and xtype = 'v')
drop view SnippetDated
go

create table dbo.SnippetGroup (
SnippetGroupId int primary key identity(1,1) not null,
SnippetGroupTitle varchar(400) Not null,
SnippetGroupModifiedDate datetime not null default(getdate()),
SnippetGroupCreationDate datetime not null default(getdate())
)
go

create table dbo.Snippet(
SnippetId int primary key identity(1,1) not null,
SnippetGroupId int null foreign key references SnippetGroup(SnippetGroupId),
SnippetTitle varchar(200) not null,
SnippetDescription text null,
SnippetExtra varchar(200) null,
SnippetOrder int not null constraint df_informationsnippet_snippetorder  default ((1)),
SnippetValidBegin datetime null,
SnippetValidEnd datetime null,
SnippetModifiedDate datetime not null default (getdate()),
SnippetCreationDate datetime not null default (getdate())
)
go

create table dbo.FeedCreation (
FeedCreationId int primary key identity(1,1) not null,
FeedCreationTitle varchar(200) not null,
FeedCreationDescription varchar(2000) not null,
FeedCreationStatement varchar(4000) not null,
FeedCreationDatabase varchar(200) null,
FeedGuid char(36) not null default (newid()),
FeedAccessViaGuidOnly bit not null default (0),
FeedCreationModifiedDate datetime not null default (getdate()),
FeedCreationCreationDate datetime not null default (getdate())
)
go

create view dbo.SnippetDated
as
select
SnippetId,
SnippetGroupId,
SnippetTitle,
SnippetDescription,
SnippetValidBegin,
SnippetValidEnd
from dbo.Snippet
where (getdate() > SnippetValidBegin) and (getdate() < SnippetValidEnd) or
(getdate() > SnippetValidBegin) and (SnippetValidEnd is null) or
(getdate() < SnippetValidEnd) and (SnippetValidBegin is null) or
(SnippetValidEnd is null) and (SnippetValidBegin is null)
go