sgEjXpf7

1DROP VIEW IF EXISTS dbo.SESSIONS;
2DROP VIEW IF EXISTS dbo.SESSIONS_readonly;
3DROP VIEW IF EXISTS dbo.SESSIONS_editable;
4DROP VIEW IF EXISTS dbo.Session3;
5DROP TABLE IF EXISTS dbo.SessionAnon;
6DROP TABLE IF EXISTS dbo.Session1;
7DROP TABLE IF EXISTS dbo.Session2;
8
9/* Create tables, primary keys and constraints required for partioned views */
10/* Sessions 1 & 2 would be the equivalent of "logged in" users - i.e. they have all their own data */
11/* Session Anon is a dummy session that provides a template of data for any "not logged in" users */
12CREATE TABLE dbo.SessionAnon (Session INT           NOT NULL
13                                  DEFAULT 0
14                                  CONSTRAINT ck_sessionAnon_ID CHECK (Session = 0)
15,                             Name    NVARCHAR(255) NOT NULL
16,                             Val     INT           NOT NULL
17,                             CONSTRAINT PK_SessionANON
18                                  PRIMARY KEY (Session, Name));
19
20CREATE TABLE dbo.Session1 (Session INT           NOT NULL
21                               DEFAULT 1
22                               CONSTRAINT ck_session1_ID CHECK (Session = 1)
23,                          Name    NVARCHAR(255) NOT NULL
24,                          Val     INT           NOT NULL
25,                          CONSTRAINT PK_Session1
26                               PRIMARY KEY (Session, Name));
27
28CREATE TABLE dbo.Session2 (Session INT           NOT NULL
29                               DEFAULT 2
30                               CONSTRAINT ck_session2_ID CHECK (Session = 2)
31,                          Name    NVARCHAR(255) NOT NULL
32,                          Val     INT           NOT NULL
33,                          CONSTRAINT PK_Session2
34                               PRIMARY KEY (Session, Name));
35GO
36
37/* Create partitioned view */
38CREATE VIEW dbo.SESSIONS
39AS
40    SELECT * FROM dbo.SessionAnon UNION ALL 
41    SELECT * FROM dbo.Session1 UNION ALL 
42    SELECT * FROM dbo.Session2; 
43GO
44
45/* Insert data into sub tables via view */
46INSERT INTO dbo.SESSIONS (Session, Name, Val)
47VALUES (0, 'Children', 2)
48,      (0, 'Parents', 2)
49,      (0, 'GrandParents', 4)
50,      (1, 'Children', 1)
51,      (1, 'Parents', 2)
52,      (2, 'Children', 3)
53,      (2, 'Parents', 1)
54,      (2, 'GrandParents', 2);
55
56SELECT * FROM dbo.SESSIONS;
57GO 
58     
59/* Session 3 is an anonymous user with a view created that points to the anon session with its own session ID rather than a seperate table with its own data */
60/* This is done as the real data sets here a quite large and there is less overhead creating a view than copying the table. */
61
62CREATE VIEW dbo.Session3
63AS
64    SELECT  3 [Session] , Name  , Val
65      FROM  dbo.SessionAnon
66GO
67
68/* Add Session 3 to the view */
69
70DROP VIEW dbo.SESSIONS;
71GO
72
73CREATE VIEW dbo.SESSIONS
74AS
75    SELECT * FROM dbo.SessionAnon UNION ALL 
76    SELECT * FROM dbo.Session1 UNION ALL 
77    SELECT * FROM dbo.Session2 UNION ALL 
78    SELECT * FROM dbo.Session3;
79GO
80
81SELECT * FROM dbo.Sessions
82
83GO 
84     
85/* This will now fail as one of the tables in the view has a constant field */
86INSERT INTO dbo.SESSIONS (Session, Name, Val)
87VALUES (1, 'GrandParents', 1);
88
89GO 
90     
91/* Create two views, one editable (contains logged in sessions only */
92/* and one read-only which reads from eduitable and adds anon sessions */
93DROP VIEW IF EXISTS dbo.SESSIONS_readonly;
94DROP VIEW IF EXISTS dbo.SESSIONS_editable;
95GO
96
97CREATE VIEW dbo.SESSIONS_editable
98AS
99    SELECT * FROM dbo.SessionAnon UNION ALL 
100    SELECT * FROM dbo.Session1 UNION ALL 
101    SELECT * FROM dbo.Session2
102GO
103
104CREATE VIEW dbo.SESSIONS_readonly
105AS
106    SELECT * FROM dbo.SESSIONS_editable UNION ALL
107    SELECT * FROM dbo.Session3
108GO
109
110INSERT INTO dbo.SESSIONS_editable (Session, Name, Val)
111VALUES (1, 'GrandParents', 1);
112
113INSERT INTO dbo.Session1 (Session, Name, Val)
114VALUES ( 1 , N'Great Grand Parents' , 0 )
115
116SELECT * FROM dbo.SESSIONS_readonly