generated from nashville-software-school/TabloidCLI
-
Notifications
You must be signed in to change notification settings - Fork 1
/
TabloidCLI.sql
133 lines (103 loc) · 4.52 KB
/
TabloidCLI.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
USE [master]
IF db_id('TabloidCLI') IS NULl
BEGIN
CREATE DATABASE [TabloidCLI]
END;
GO
use [TabloidCLI]
go
DROP TABLE IF EXISTS Journal;
DROP TABLE IF EXISTS BlogTag;
DROP TABLE IF EXISTS PostTag;
DROP TABLE IF EXISTS AuthorTag;
DROP TABLE IF EXISTS Tag;
DROP TABLE IF EXISTS Note;
DROP TABLE IF EXISTS Post;
DROP TABLE IF EXISTS Blog;
DROP TABLE IF EXISTS Author;
CREATE TABLE Author (
Id INTEGER NOT NULL PRIMARY KEY IDENTITY,
FirstName NVARCHAR(55) NOT NULL,
LastName NVARCHAR(55) NOT NULL,
Bio TEXT,
isActive BIT NOT NULL DEFAULT(1)
);
CREATE TABLE Blog (
Id INTEGER NOT NULL PRIMARY KEY IDENTITY,
Title NVARCHAR(55) NOT NULL,
URL NVARCHAR(2000) NOT NULL,
isActive BIT NOT NULL DEFAULT(1)
);
CREATE TABLE Post (
Id INTEGER NOT NULL PRIMARY KEY IDENTITY,
Title NVARCHAR(55) NOT NULL,
URL NVARCHAR(2000) NOT NULL,
PublishDateTime DATETIME NOT NULL,
AuthorId INTEGER NOT NULL,
BlogId INTEGER NOT NULL,
CONSTRAINT FK_Post_Author FOREIGN KEY(AuthorId) REFERENCES Author(Id),
CONSTRAINT FK_Post_Blog FOREIGN KEY(BlogId) REFERENCES Blog(Id),
isActive BIT NOT NULL DEFAULT(1)
);
CREATE TABLE Note (
Id INTEGER NOT NULL PRIMARY KEY IDENTITY,
Title NVARCHAR(55) NOT NULL,
Content TEXT NOT NULL,
CreateDateTime DATETIME NOT NULL,
PostId INTEGER NOT NULL,
CONSTRAINT FK_Note_Posti FOREIGN KEY(PostId) REFERENCES Post(Id),
isActive BIT NOT NULL DEFAULT(1)
);
CREATE TABLE Tag (
Id INTEGER NOT NULL PRIMARY KEY IDENTITY,
Name NVARCHAR(55) NOT NULL,
isActive BIT NOT NULL DEFAULT(1)
);
CREATE TABLE AuthorTag (
Id INTEGER NOT NULL PRIMARY KEY IDENTITY,
AuthorId INTEGER NOT NULL,
TagId INTEGER NOT NULL,
CONSTRAINT FK_AuthorTag_Author FOREIGN KEY(AuthorId) REFERENCES Author(Id),
CONSTRAINT FK_AuthorTag_Tag FOREIGN KEY(TagId) REFERENCES Tag(Id),
isActive BIT NOT NULL DEFAULT(1)
);
CREATE TABLE PostTag (
Id INTEGER NOT NULL PRIMARY KEY IDENTITY,
PostId INTEGER NOT NULL,
TagId INTEGER NOT NULL,
CONSTRAINT FK_PostTag_Post FOREIGN KEY(PostId) REFERENCES Post(Id),
CONSTRAINT FK_PostTag_Tag FOREIGN KEY(TagId) REFERENCES Tag(Id)
);
CREATE TABLE BlogTag (
Id INTEGER NOT NULL PRIMARY KEY IDENTITY,
BlogId INTEGER NOT NULL,
TagId INTEGER NOT NULL,
CONSTRAINT FK_BlogTag_Blog FOREIGN KEY(BlogId) REFERENCES Blog(Id),
CONSTRAINT FK_BlogTag_Tag FOREIGN KEY(TagId) REFERENCES Tag(Id)
);
CREATE TABLE Journal (
Id INTEGER NOT NULL PRIMARY KEY IDENTITY,
Title NVARCHAR(55) NOT NULL,
Content TEXT NOT NULL,
CreateDateTime DATETIME NOT NULL,
isActive BIT NOT NULL DEFAULT(1)
);
INSERT INTO Author ( FirstName, LastName, Bio) VALUES ( 'Scott', 'Hanselman', '.net advocate. Works at Micrsoft' );
INSERT INTO Author ( FirstName, LastName, Bio) VALUES ( 'Eric', 'Elliott', 'Opinionated javascript developer' );
INSERT INTO Author ( FirstName, LastName, Bio) VALUES ( 'Felienne', 'Hermans', ' associate professor at the Leiden Institute of Advanced Computer Science at Leiden University, where head the PERL group that researches programming education');
INSERT INTO Author ( FirstName, LastName, Bio) VALUES ( 'Jake', 'Archibald', 'Javascript dev, developer relations at Google' );
INSERT INTO Author ( FirstName, LastName, Bio) VALUES ( 'Julie', 'Lerman', 'Entity Framework expert' );
INSERT INTO Blog (Title, URL) VALUES ( 'The Data Farm', 'https://thedatafarm.com/blog/' );
INSERT INTO Blog (Title, URL) VALUES ( '.NET Blog', 'https://devblogs.microsoft.com/dotnet/' );
INSERT INTO Blog (Title, URL) VALUES ( 'felienne.com', 'https://www.felienne.com/' );
INSERT INTO Blog (Title, URL) VALUES ( 'NETFLIX Tech Blog', 'https://netflixtechblog.com/' );
INSERT INTO Blog (Title, URL) VALUES ( 'jakearchibald.com', 'https://jakearchibald.com/' );
INSERT INTO Blog (Title, URL) VALUES ( 'Develop Together', 'https://dev.to/' );
INSERT INTO Blog (Title, URL) VALUES ( 'Scott Hanselman Blog', 'https://www.hanselman.com/blog/' );
INSERT INTO Post ( Title, URL, PublishDateTime, AuthorId, BlogId ) VALUES ('Forms of notional machines', 'https://www.felienne.com/archives/6392', '2019-07-12', 3, 3);
--INSERT INTO Note ( Title, Content, CreateDateTime, PostId ) VALUES ();
INSERT INTO Tag ( Name ) VALUES ( 'nerdy' );
--INSERT INTO AuthorTag ( AuthorId, TagId) VALUES ( );
--INSERT INTO PostTag ( PostId, TagId ) VALUES ( );
--INSERT INTO BlogTag ( BlogId, TagId ) VALUES ( );
INSERT INTO Journal ( Title, Content, CreateDateTime ) VALUES ( 'My Big Day', 'I had a big day today. Would you believe I saw a dog????', '2020-04-30' ) ;