/
queries.go
133 lines (131 loc) · 4.94 KB
/
queries.go
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
133
package model
// SQLMap is a map of name/query. Each entry has a name and an SQL query used
// in some method.
type SQLMap map[string]any
var queryTemplates = SQLMap{
"INSERT": `INSERT INTO ${table} (${columns}) VALUES(${placeholders})`,
"GetByID": `SELECT * FROM ${table} WHERE id=?`,
"SELECT": `SELECT ${columns} FROM ${table} ${WHERE} LIMIT ${limit} OFFSET ${offset}`,
// 0. WHERE alias = $alias failed to match
// 1. Suppose the user stumbled on a link with the old most recent alias.
// 2. Look for the most recent id which had such $alias in the given table.
// To be embedded in other queries
"ALIAS_IS": `(SELECT new_alias FROM aliases
WHERE alias_table='${table}' AND (old_alias=:alias) ORDER BY ID DESC LIMIT 1)
OR ${table}.id=(SELECT alias_id FROM aliases
WHERE alias_table='${table}' AND (old_alias=:alias OR new_alias=:alias) ORDER BY ID DESC LIMIT 1)`,
"CELINA_ALIAS_IS": `(SELECT new_alias FROM aliases
WHERE alias_table='celini' AND (old_alias=:celina) ORDER BY ID DESC LIMIT 1)
OR celini.id=(SELECT alias_id FROM aliases
WHERE alias_table='celini' AND (old_alias=:celina OR new_alias=:celina) ORDER BY ID DESC LIMIT 1)`,
// To be embedded in other queries
"PUBLISHED_DOMAIN_ID_BY_NAME_IS": `(SELECT id FROM domove
WHERE (:domain LIKE '%'||domain OR aliases LIKE :domain OR ips LIKE '%'||:domain||'%') AND published = 2 LIMIT 1)`,
"GET_DOMAIN": `SELECT * from ${table} WHERE id=${PUBLISHED_DOMAIN_ID_BY_NAME_IS}`,
// To be embedded in other queries
"PERMISSIONS_ARE": `(
-- others can read and execute AND stranica/celina is published
( ${table}.permissions LIKE '%r_x' AND ${table}.published = 2 )
-- owner can read and execute
OR ( ${table}.permissions LIKE '_r_x%' AND ${table}.user_id = :user_id )
-- user has the group group_id and the page is rx by this group_id and is published(0|1|2)
OR (
( ${table}.group_id IN (SELECT group_id from user_group WHERE user_id=:user_id)
AND ${table}.permissions LIKE '____r_x%' AND ${table}.published = :pub
)
)
)`,
`LANG_LIKE`: `(c.language LIKE :lang||'%' OR :lang LIKE c.language||'%' )`,
"GET_PAGE_FOR_DISPLAY": `SELECT ${table}.*, c.title, c.body, c.language, c.data_type, c.data_format
FROM ${table}
JOIN celini AS c ON (
stranici.id = c.page_id AND c.pid=0 AND c.permissions LIKE 'd%'
AND ${LANG_LIKE}
AND c.data_type='title'
AND c.published = stranici.published)
WHERE (
${PERMISSIONS_ARE}
AND ( ${table}.alias = :alias OR ${table}.alias = ${ALIAS_IS})
AND ${table}.dom_id = ${PUBLISHED_DOMAIN_ID_BY_NAME_IS}
AND ${table}.hidden = 0
${AND_FOR_DISPLAY}
) LIMIT 1`,
"GET_CELINA_FOR_DISPLAY": `SELECT * from ${table} WHERE (
page_id=(SELECT id from stranici WHERE alias = :alias)
AND (language LIKE :lang||'%' OR :lang LIKE language||'%')
AND box = :box
AND ${PERMISSIONS_ARE}
AND ( ${table}.alias = :celina OR ${table}.alias = ${CELINA_ALIAS_IS})
AND ${table}.bad = 0
${AND_FOR_DISPLAY}
)
`,
"CELINI_FOR_LIST_IN_PAGE": `
SELECT id, alias, title, body, language FROM ${table} WHERE (
page_id = (SELECT id FROM stranici WHERE alias=:alias
AND page_type = 'regular' AND dom_id = ${PUBLISHED_DOMAIN_ID_BY_NAME_IS})
AND pid = (SELECT id FROM ${table} WHERE alias=:alias) AND box = :box
AND (language LIKE :lang||'%' OR :lang LIKE language||'%')
AND ${PERMISSIONS_ARE}
${AND_FOR_DISPLAY}
)
ORDER BY featured DESC, id DESC, sorting ASC
LIMIT :limit OFFSET :offset
`,
"AND_FOR_DISPLAY": `
AND ${table}.deleted = 0
AND ( ${table}.start = 0 OR ${table}.start < :now )
AND ( ${table}.stop = 0 OR ${table}.stop > :now )
`,
"SELECT_PAGES_FOR_MAIN_MENU": `
SELECT
${table}.id AS id,
${table}.pid AS pid,
${table}.alias AS alias,
c.title AS title,
c.language as language,
${table}.permissions
FROM ${table}
JOIN celini AS c ON (
${table}.id = c.page_id AND c.pid=0 AND c.permissions LIKE 'd%'
-- consider 'en' 'en-US', 'en-GB' as one language
AND ${LANG_LIKE}
AND c.data_type='title'
AND c.published = ${table}.published)
WHERE (
${table}.pid=(
SELECT id FROM ${table} WHERE page_type='root'
AND dom_id = ${PUBLISHED_DOMAIN_ID_BY_NAME_IS}
)
AND ${table}.page_type = 'regular'
AND ${PERMISSIONS_ARE}
AND ${table}.hidden = 0
${AND_FOR_DISPLAY}
) ORDER BY ${table}.sorting
`,
"SELECT_CHILD_PAGES": `SELECT
${table}.id AS id,
${table}.pid AS pid,
${table}.alias AS alias,
c.title AS title,
c.language as language,
c.body as body
FROM ${table}
JOIN celini AS c ON (
${table}.id = c.page_id AND c.pid=0 AND c.permissions LIKE 'd%'
AND ${LANG_LIKE}
AND c.data_type='title'
AND c.published = ${table}.published)
WHERE (
${table}.pid=(
SELECT id FROM ${table} WHERE alias=:alias
AND dom_id = ${PUBLISHED_DOMAIN_ID_BY_NAME_IS}
AND ( ${table}.alias = :alias OR ${table}.alias = ${ALIAS_IS})
)
AND ${table}.page_type = 'regular'
AND ${PERMISSIONS_ARE}
AND ${table}.hidden = 0
${AND_FOR_DISPLAY}
) ORDER BY ${table}.sorting
`,
}