MySql_DEV_Views.sql
3.42 KB
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
-- CREATE
-- [OR REPLACE]
-- [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
-- [DEFINER = { user | CURRENT_USER }]
-- [SQL SECURITY { DEFINER | INVOKER }]
-- VIEW view_name [(column_list)]
-- AS select_statement
-- [WITH [CASCADED | LOCAL] CHECK OPTION]
DROP VIEW IF EXISTS View_BasisElement_BasisClipboards;
CREATE VIEW View_BasisElement_BasisClipboards AS
select
PartOfID as PartOfID, t_PartOfClass.ClassName as PClass, t_PartOfBE.Subject as Subject, PartOfObjVar as ObjVar, CategoryName as Category, AttachID as A_ID, t_AttachBE.Subject as A_Subject, CDate, LinkerProfileID, AttachStateID
from losp_BasisClipboard
inner join losp_BasisClipboard_objvar on losp_BasisClipboard_objvar.PartOfObjVarID=losp_BasisClipboard.PartOfObjVarID
inner join losp_BasisClipboard_category on losp_BasisClipboard_category.CategoryID=losp_BasisClipboard.CategoryID
inner join losp_BasisElement as t_PartOfBE on PartOfID=t_PartOfBE.ID
inner join losp_class as t_PartOfClass on t_PartOfClass.ClassID=t_PartOfBE.ClassID
inner join losp_BasisElement as t_AttachBE on AttachID=t_AttachBE.ID
order by PartOfID
;
DROP VIEW IF EXISTS View_BasisElement_RightManagement;
CREATE VIEW View_BasisElement_RightManagement AS
select
ID, Subject,
losp_BasisElement_readauthorisation.ListID as R_List, losp_BasisElement_readauthorisation.ProfileID as R_PID, t_R_profile.NickName as R_NickName,
losp_BasisElement_writeauthorisation.ListID as W_List, losp_BasisElement_writeauthorisation.ProfileID as W_PID, t_W_profile.NickName as W_NickName
from losp_BasisElement
inner join losp_BasisElement_readauthorisation on losp_BasisElement.AuthReadListID=losp_BasisElement_readauthorisation.ListID
inner join losp_BE_profile as t_R_profile on t_R_profile.MTextID=losp_BasisElement_readauthorisation.ProfileID
inner join losp_BasisElement_writeauthorisation on losp_BasisElement.AuthWriteListID=losp_BasisElement_writeauthorisation.ListID
inner join losp_BE_profile as t_W_profile on t_W_profile.MTextID=losp_BasisElement_writeauthorisation.ProfileID
;
DROP VIEW IF EXISTS View_BasisElement_categorylist;
CREATE VIEW View_BasisElement_categorylist AS
select
ID, Subject, CreateDate, losp_BasisElement.BECategoryListID, losp_BasisElement.BECategoryChoiceBEID,
categorynamelist.BECategoryID, categorynamelist.BECategoryName, categorynamelist.BECategoryGroupID,
groups.BECategoryID as G_BECategoryID, groups.BECategoryName as G_BECategoryName, groups.BECategoryGroupID as G_BECategoryGroupID
from losp_BasisElement
inner join losp_BasisElement_categorylist on losp_BasisElement_categorylist.BECategoryListID=losp_BasisElement.BECategoryListID
inner join losp_BasisElement_categorynamelist as categorynamelist on categorynamelist.BECategoryID=losp_BasisElement_categorylist.BECategoryID
left join losp_BasisElement_categorynamelist as groups on categorynamelist.BECategoryID=groups.BECategoryGroupID
;
DROP VIEW IF EXISTS View_BasisClipboard_RightManagement;
CREATE VIEW View_BasisClipboard_RightManagement AS
select ID, Subject, PartOfObjVar, CategoryName, WriteAuthorisaton, ReadAuthorisation
from losp_BasisElement
inner join losp_BEBasisClipboardsRightManagement on ID=PartOfID
inner join losp_BasisClipboard_objvar on losp_BasisClipboard_objvar.PartOfObjVarID=losp_BEBasisClipboardsRightManagement.PartOfObjVarID
inner join losp_BasisClipboard_category on losp_BasisClipboard_category.CategoryID=losp_BEBasisClipboardsRightManagement.CategoryID
;