create or replace function Tig_MA_GetMessages(_ownerJid varchar(2049), _buddyJid varchar(2049), _from timestamp with time zone, _to timestamp with time zone, _refType smallint, _tags text, _contains text, _limit int, _offset int) returns table(
21
+
"msg" text, "ts" timestamp with time zone, "buddyJid" varchar(2049), "stableId" varchar(36), "refStableId" varchar(36)
22
+
) as $$
23
+
declare
24
+
tags_query text;
25
+
contains_query text;
26
+
msgs_query text;
27
+
pagination_query text;
28
+
query_sql text;
29
+
startTs timestamp with time zone;
30
+
endTs timestamp with time zone;
31
+
begin
32
+
if _tags is not null or _contains is not null then
33
+
select Tig_MA_GetHasTagsQuery(_tags) into tags_query;
34
+
select Tig_MA_GetBodyContainsQuery(_contains) into contains_query;
35
+
msgs_query := 'select m.msg, m.ts, b.jid, cast(m.stable_id as varchar(36)) as stable_id, cast(m.ref_stable_id as varchar(36)) as ref_stable_id
36
+
from tig_ma_msgs m
37
+
inner join tig_ma_jids o on m.owner_id = o.jid_id
38
+
inner join tig_ma_jids b on b.jid_id = m.buddy_id
39
+
where
40
+
lower(o.jid) = lower(%L)
41
+
and (%L is null or lower(b.jid) = lower(%L))
42
+
and (%L is null or m.ts >= %L)
43
+
and (%L is null or m.ts <= %L)';
44
+
pagination_query := ' limit %s offset %s';
45
+
query_sql = msgs_query || tags_query || contains_query || ' order by m.ts' || pagination_query;
return query select m.msg, m.ts,b.jid, cast(m.stable_id as varchar(36)) as stable_id, cast(m.ref_stable_id as varchar(36)) as ref_stable_id
51
+
from tig_ma_msgs m
52
+
inner join tig_ma_jids o on m.owner_id = o.jid_id
53
+
inner join tig_ma_jids b on b.jid_id = m.buddy_id
54
+
where
55
+
lower(o.jid) = lower(_ownerJid)
56
+
and (_buddyJid is null or lower(b.jid) = lower(_buddyJid))
57
+
and m.is_ref = 0
58
+
and (_from is null or m.ts >= _from)
59
+
and (_to is null or m.ts <= _to)
60
+
order by m.ts
61
+
limit _limit offset _offset;
62
+
when 1 then
63
+
return query select m.msg, m.ts, b.jid, cast(m.stable_id as varchar(36)) as stable_id, cast(m.ref_stable_id as varchar(36)) as ref_stable_id
64
+
from tig_ma_msgs m
65
+
inner join tig_ma_jids o on m.owner_id = o.jid_id
66
+
inner join tig_ma_jids b on b.jid_id = m.buddy_id
67
+
where
68
+
lower(o.jid) = lower(_ownerJid)
69
+
and (_buddyJid is null or lower(b.jid) = lower(_buddyJid))
70
+
and (_from is null or m.ts >= _from)
71
+
and (_to is null or m.ts <= _to)
72
+
order by m.ts
73
+
limit _limit offset _offset;
74
+
else
75
+
select into endTs, startTs max(x.ts), min(x.ts)
76
+
from (
77
+
select m.ts as ts
78
+
from tig_ma_msgs m
79
+
inner join tig_ma_jids o on m.owner_id = o.jid_id
80
+
inner join tig_ma_jids b on b.jid_id = m.buddy_id
81
+
where
82
+
lower(o.jid) = lower(_ownerJid)
83
+
and (_buddyJid is null or lower(b.jid) = lower(_buddyJid))
84
+
and m.is_ref = 0
85
+
and (_from is null or m.ts >= _from)
86
+
and (_to is null or m.ts <= _to)
87
+
order by m.ts
88
+
limit _limit offset _offset
89
+
) x;
90
+
91
+
return query select ref.msg, ref.ts, b.jid, cast(ref.stable_id as varchar(36)) as stable_id, cast(ref.ref_stable_id as varchar(36)) as ref_stable_id
92
+
from tig_ma_msgs m
93
+
inner join tig_ma_jids o on m.owner_id = o.jid_id
94
+
inner join tig_ma_jids b on m.buddy_id = b.jid_id
95
+
inner join tig_ma_msgs ref on ref.ref_stable_id = m.stable_id and ref.owner_id = o.jid_id
96
+
where
97
+
lower(o.jid) = lower(_ownerJid)
98
+
and (_buddyJid is null or lower(b.jid) = lower(_buddyJid))
99
+
and m.is_ref = 0
100
+
and m.ts >= startTs
101
+
and m.ts <= endTs
102
+
order by ref.ts;
103
+
end case;
104
+
end if;
105
+
end;
106
+
$$ LANGUAGE 'plpgsql';
107
+
-- QUERY END:
108
+
109
+
-- QUERY START:
110
+
create or replace function Tig_MA_GetMessagesCount(_ownerJid varchar(2049), _buddyJid varchar(2049), _from timestamp with time zone, _to timestamp with time zone, _refType smallint, _tags text, _contains text) returns table(
111
+
"count" bigint
112
+
) as $$
113
+
declare
114
+
tags_query text;
115
+
contains_query text;
116
+
msgs_query text;
117
+
query_sql text;
118
+
begin
119
+
if _tags is not null or _contains is not null then
120
+
select Tig_MA_GetHasTagsQuery(_tags) into tags_query;
121
+
select Tig_MA_GetBodyContainsQuery(_contains) into contains_query;
and (_buddyJid is null or lower(b.jid) = lower(_buddyJid))
143
+
and (_from is null or m.ts >= _from)
144
+
and (_to is null or m.ts <= _to);
145
+
else
146
+
return query select count(1)
147
+
from tig_ma_msgs m
148
+
inner join tig_ma_jids o on m.owner_id = o.jid_id
149
+
inner join tig_ma_jids b on b.jid_id = m.buddy_id
150
+
where
151
+
lower(o.jid) = lower(_ownerJid)
152
+
and (_buddyJid is null or lower(b.jid) = lower(_buddyJid))
153
+
and m.is_ref = 0
154
+
and (_from is null or m.ts >= _from)
155
+
and (_to is null or m.ts <= _to);
156
+
end case;
157
+
end if;
158
+
end;
159
+
$$ LANGUAGE 'plpgsql';
160
+
-- QUERY END:
161
+
162
+
-- QUERY START:
163
+
create or replace function Tig_MA_GetMessagePosition(_ownerJid varchar(2049), _buddyJid varchar(2049), _from timestamp with time zone, _to timestamp with time zone, _refType smallint, _tags text, _contains text, _stableId varchar(36)) returns table(
164
+
"position" bigint
165
+
) as $$
166
+
declare
167
+
tags_query text;
168
+
contains_query text;
169
+
msgs_query text;
170
+
query_sql text;
171
+
begin
172
+
if _tags is not null or _contains is not null then
173
+
select Tig_MA_GetHasTagsQuery(_tags) into tags_query;
174
+
select Tig_MA_GetBodyContainsQuery(_contains) into contains_query;
175
+
msgs_query := 'select x.position from (
176
+
select row_number() over (w) as position, m.stable_id
177
+
from tig_ma_msgs m
178
+
inner join tig_ma_jids o on m.owner_id = o.jid_id
179
+
inner join tig_ma_jids b on b.jid_id = m.buddy_id
180
+
where
181
+
lower(o.jid) = lower(%L)
182
+
and (%L is null or lower(b.jid) = lower(%L))
183
+
and (%L is null or m.ts >= %L)
184
+
and (%L is null or m.ts <= %L)';
185
+
query_sql = msgs_query || tags_query || contains_query || ' window w as (order by ts) ) x where x.stable_id = %L';
select row_number() over (w) as position, m.stable_id
192
+
from tig_ma_msgs m
193
+
inner join tig_ma_jids o on m.owner_id = o.jid_id
194
+
inner join tig_ma_jids b on b.jid_id = m.buddy_id
195
+
where
196
+
lower(o.jid) = lower(_ownerJid)
197
+
and (_buddyJid is null or lower(b.jid) = lower(_buddyJid))
198
+
and (_from is null or m.ts >= _from)
199
+
and (_to is null or m.ts <= _to)
200
+
window w as (order by ts)
201
+
) x where x.stable_id = uuid(_stableId);
202
+
else
203
+
return query select x.position from (
204
+
select row_number() over (w) as position, m.stable_id
205
+
from tig_ma_msgs m
206
+
inner join tig_ma_jids o on m.owner_id = o.jid_id
207
+
inner join tig_ma_jids b on b.jid_id = m.buddy_id
208
+
where
209
+
lower(o.jid) = lower(_ownerJid)
210
+
and (_buddyJid is null or lower(b.jid) = lower(_buddyJid))
211
+
and m.is_ref = 0
212
+
and (_from is null or m.ts >= _from)
213
+
and (_to is null or m.ts <= _to)
214
+
window w as (order by ts)
215
+
) x where x.stable_id = uuid(_stableId);
216
+
end case;
217
+
end if;
218
+
end;
219
+
$$ LANGUAGE 'plpgsql';
220
+
-- QUERY END:
221
+
222
+
-- QUERY START:
223
+
create or replace function Tig_MA_GetCollections(_ownerJid varchar(2049), _buddyJid varchar(2049), _from timestamp with time zone, _to timestamp with time zone, _tags text, _contains text, _limit int, _offset int) returns table(
224
+
"ts" timestamp with time zone, "with" varchar(2049)
225
+
) as $$
226
+
declare
227
+
tags_query text;
228
+
contains_query text;
229
+
msgs_query text;
230
+
pagination_query text;
231
+
groupby_query text;
232
+
query_sql text;
233
+
begin
234
+
if _tags is not null or _contains is not null then
235
+
select Tig_MA_GetHasTagsQuery(_tags) into tags_query;
236
+
select Tig_MA_GetBodyContainsQuery(_contains) into contains_query;
237
+
msgs_query := 'select min(m.ts), b.jid';
238
+
msgs_query := msgs_query ||
239
+
' from tig_ma_msgs m
240
+
inner join tig_ma_jids o on m.owner_id = o.jid_id
241
+
inner join tig_ma_jids b on b.jid_id = m.buddy_id
242
+
where
243
+
lower(o.jid) = lower(%L)
244
+
and (%L is null or lower(b.jid) = lower(%L))
245
+
and (%L is null or m.ts >= %L)
246
+
and (%L is null or m.ts <= %L)';
247
+
groupby_query := ' group by date(m.ts), m.buddy_id, b.jid';
248
+
pagination_query := ' limit %s offset %s';
249
+
query_sql := msgs_query || tags_query || contains_query || groupby_query || ' order by min(m.ts), b.jid' || pagination_query;
and (_buddyJid is null or lower(b.jid) = lower(_buddyJid))
259
+
and (_from is null or m.ts >= _from)
260
+
and (_to is null or m.ts <= _to)
261
+
group by date(m.ts), m.buddy_id, b.jid
262
+
order by min(m.ts), b.jid
263
+
limit _limit offset _offset;
264
+
end if;
265
+
end;
266
+
$$ LANGUAGE 'plpgsql';
267
+
-- QUERY END:
268
+
269
+
-- QUERY START:
270
+
create or replace function Tig_MA_GetCollectionsCount(_ownerJid varchar(2049), _buddyJid varchar(2049), _from timestamp with time zone, _to timestamp with time zone, _tags text, _contains text) returns table(
271
+
"count" bigint
272
+
) as $$
273
+
declare
274
+
tags_query text;
275
+
contains_query text;
276
+
msgs_query text;
277
+
groupby_query text;
278
+
query_sql text;
279
+
begin
280
+
if _tags is not null or _contains is not null then
281
+
select Tig_MA_GetHasTagsQuery(_tags) into tags_query;
282
+
select Tig_MA_GetBodyContainsQuery(_contains) into contains_query;
283
+
msgs_query := 'select count(1) from (select min(m.ts), b.jid';
284
+
msgs_query := msgs_query ||
285
+
' from tig_ma_msgs m
286
+
inner join tig_ma_jids o on m.owner_id = o.jid_id
287
+
inner join tig_ma_jids b on b.jid_id = m.buddy_id
288
+
where
289
+
lower(o.jid) = lower(%L)
290
+
and (%L is null or lower(b.jid) = lower(%L))
291
+
and (%L is null or m.ts >= %L)
292
+
and (%L is null or m.ts <= %L)';
293
+
groupby_query := ' group by date(m.ts), m.buddy_id, b.jid';
set @msgs_query = N'select m.msg, m.ts, b.jid, convert(nvarchar(36),m.stable_id) as stable_id, convert(nvarchar(36),m.ref_stable_id) as ref_stable_id, row_number() over (order by m.ts) as row_num
select m.msg, m.ts, b.jid, convert(nvarchar(36),m.stable_id) as stable_id, convert(nvarchar(36),m.ref_stable_id) as ref_stable_id, row_number() over (order by m.ts) as row_num
69
+
from tig_ma_msgs m
70
+
inner join tig_ma_jids o on m.owner_id = o.jid_id
71
+
inner join tig_ma_jids b on b.jid_id = m.buddy_id
72
+
where
73
+
o.jid_sha1 = HASHBYTES('SHA1', LOWER(@_ownerJid))
74
+
and (@_buddyJid is null or b.jid_sha1 = HASHBYTES('SHA1', LOWER(@_buddyJid)))
75
+
and (m.is_ref = 0)
76
+
and (@_from is null or m.ts >= @_from)
77
+
and (@_to is null or m.ts <= @_to)
78
+
)
79
+
select * from results_cte where row_num >= @_offset + 1 and row_num < @_offset + 1 + @_limit order by row_num;
80
+
end
81
+
else
82
+
begin
83
+
if @_refType = 1
84
+
begin
85
+
;with results_cte as (
86
+
select m.msg, m.ts, b.jid, convert(nvarchar(36),m.stable_id) as stable_id, convert(nvarchar(36),m.ref_stable_id) as ref_stable_id, row_number() over (order by m.ts) as row_num
87
+
from tig_ma_msgs m
88
+
inner join tig_ma_jids o on m.owner_id = o.jid_id
89
+
inner join tig_ma_jids b on b.jid_id = m.buddy_id
90
+
where
91
+
o.jid_sha1 = HASHBYTES('SHA1', LOWER(@_ownerJid))
92
+
and (@_buddyJid is null or b.jid_sha1 = HASHBYTES('SHA1', LOWER(@_buddyJid)))
93
+
and (@_from is null or m.ts >= @_from)
94
+
and (@_to is null or m.ts <= @_to)
95
+
)
96
+
select * from results_cte where row_num >= @_offset + 1 and row_num < @_offset + 1 + @_limit order by row_num;
97
+
end
98
+
else
99
+
begin
100
+
;with results_cte as (
101
+
select m.owner_id, m.stable_id, row_number() over (order by m.ts) as row_num
102
+
from tig_ma_msgs m
103
+
inner join tig_ma_jids o on m.owner_id = o.jid_id
104
+
inner join tig_ma_jids b on b.jid_id = m.buddy_id
105
+
where
106
+
o.jid_sha1 = HASHBYTES('SHA1', LOWER(@_ownerJid))
107
+
and (@_buddyJid is null or b.jid_sha1 = HASHBYTES('SHA1', LOWER(@_buddyJid)))
108
+
and (m.is_ref = 0)
109
+
and (@_from is null or m.ts >= @_from)
110
+
and (@_to is null or m.ts <= @_to)
111
+
)
112
+
select m.msg, m.ts, b.jid, convert(nvarchar(36),m.stable_id) as stable_id, convert(nvarchar(36),m.ref_stable_id) as ref_stable_id
113
+
from results_cte cte
114
+
inner join tigma_msgs m on m.owner_id = cte.owner_id and m.ref_stable_id = cte.stable_id
115
+
inner join tig_ma_jids b on b.jid_id = m.buddy_id
116
+
where cte.row_num >= @_offset + 1 and cte.row_num < @_offset + 1 + @_limit order by m.ts;
117
+
end
118
+
end
119
+
end
120
+
end
121
+
-- QUERY END:
122
+
GO
123
+
124
+
-- QUERY START:
125
+
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'Tig_MA_GetMessagesCount')
Andrzej Wójcik (Tigase) committed 4 months ago
1 parent 14f61c5a