April 1, 2021 at 9:21 pm
Following up on this post where Steve Jones wrote:
Steve Jones - SSC Editor wrote:"For SQL Sat, I do need some sort of forum place where people can suggest things, add comments, complain, criticize, praise, etc. I'll try to continue hosting here, but at some point I likely need something independent."
A forum API could be something interesting. It's a known challenge. If building API's has been difficult in the past it's because outside of the SQL realm we've been made to suffer with poor tools imo. Hopefully maybe what began as an interest project could be surprising in a good way. Maybe it's interesting on this site to shake the tree and see what happens imo.
There was a Reddit-type site years ago built on the Drupal CMS platform. The owner of the site shut it down permanently and told the users to go build their own sites so they could truly understand his burdens/drama. It's more complicated because his wife was sick and she wanted them to move back to Japan which they did. Anyway, it was a good community of people and the forum was very active. Now it's long gone. To me it's buried and it doesn't matter (if/how people remember it). There were multiple groups who got together to build replacement sites/forums and ??? idk what happened to any of them. Through a Facebook "alumni" Group group I joined 2 (really 1) .NET/math developers (or more like emailers) as the database person. We started a project called NDP or New Data Platform (it needed a name). The other person (Cyril) wrote a spec/description of what they thought would be a better forum system. At the time my partner and me had built several SQL Server/.NET Framework Web Api's. Based on the identity/access management of those APIs I told the others I'd try to implement the forum API spec. They were supposed to build a website which used the API for data access but they never did. The 2 became 1 and then 0 when they stopped responding. I don't think either of them thought it would work and we could create a functioning system the way it was described
More recently in 2019/2020 my business partner offered a forum API as a project to a consulting client. We do different projects and this was one which again didn't end up going through. We did create the build pipeline and deployment scripts and some other things in anticipation of the project moving forward in some way. The new build pipeline is based on .NET Core so there was going to be a migration. I've done quite a few of these migrations over years and it's been a big challenge (struggle) to fully migrate off. This is where the forum API project sits right now. It would be awesome to update the code and offer the forum API as a service to SQL Sat. My partner is on board with this too. Or maybe it won't be used for this purpose but maybe something else. Hopefully it will be found to be interesting and some use will come of it. If nothing else I personally think it's interesting and I've kept at it trying to find the right place for it 🙂
The person who wrote the spec is no longer around or in any way involved. He did imo seem to have some good ideas about how to address issues of the Drupal forum. One thing that's cool about Drupal and/or the way the forum was implemented on that site it was possible to have threaded subconversations. The way it was implemented was to nest the posts within each other within a single thread. This leads to shenanigans where members could/would change history or you'd have to go backward through the thread to find the unaltered original. Cyril was (overly) into Kendo UI which is what he said he was going to use tree structures to display hierarchical data (forum posts). That's my interpretation. What was actually built differs somewhat. Here's some of what he wrote:
Well, I think our regards to the eventual post/reply/reply to reply/reply to reply to reply/etc hierarchy can just be decoupled from either or both the "real time" timeline of user-generated content inception (captured in the p_dt column, say, in UTC below) and/or the PK values induced by whatever committed transactions of a relational engine; eg,
Say we have Chris, Paul, James (C, P, J) with Chris' posting first...
Then Paul replies to Chris;
then James does the same;
then James replies to Paul as well;
then Paul replies to James;
we may end up with something like, logically:
C(p_id1, ..., p_text1, p_dt1)
+-- P(p_id2, ..., p_text2, p_dt2)
+-- J(p_id4 ..., p_text4, p_dt4)
+-- J(p_id3 ..., p_text3, p_dt3)
+-- P(p_id5, ..., p_text5, p_dt5)
It's important to notice that, from a strictly content-focused perspective, all that matters is to reflect a faithful parent - child relation between the p_id's relatively to each other, whether or not those are identity-based PKs, or GUID PKs, or some other sort of sequence, deterministic or not...
I mean, we could as well have:
C(..., p_text1, p_dt1)
+-- J(..., p_text3, p_dt3) (inserted early)
+-- P(..., p_text5, p_dt5)
+-- P(..., p_text2, p_dt2) (inserted late)
+-- J(..., p_text4, p_dt4)
depending, say, on the latencies of the service tier while mutating on the persistence tier.
But this fundamentally wouldn't change anything of what can be observed by the user on the presentation tier of the application, as long as those p_dt's originated from there, precisely, and still honor the same invariant of their production:
p_dt1 < p_dt2 < ... < p_dt5
Also, there is only so much one can display on a single page on any end user device also, so it isn't too difficult to apply some tree transformation at UI rendering time, if necessary, if the tree node insertion order at the persistence layer isn't required to reflect exactly what was effected on the actual "real time" / chronological timeline (of the p_dt's), give or take a fraction of a millisecond either way.
Based on this and other back and forth I created 12 or so tables, many CRUD stored procedures, C# web API, and there's some test code and documentation too. We're not claiming this is "best practices" code either because it's 4 years old and prior to me being on SSC/SO and learning a lot. There are things I'd like to improve but it's a starting point. Hopefully it will all be improved. Having done many .NET Core migrations to simplify and speed the process through years I've developed a C# library which smooths the transition and addresses many of the classic mismatch issues. Because it's a migration both the "old way" (.NET Framework w/Controller logic) and the "new way" (.NET Core w/request filters) will exist and be a perfect comparison. The filters make migrating simple and easy. The code is open to criticism, feedback, sniping. On the old Drupal site the topic of the site itself was endless and always very interesting 🙂 I learned a lot from reading those technical discussions about the database, site, and development.
SQL and other code to follow
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
April 2, 2021 at 10:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
April 29, 2021 at 2:19 pm
Setup #2 Seed Data
This script inserts seed data into 5 of the 7 tables created by setup1.
7 users were created:
idemailusername
1admin@abc123.comSysAdmin
2stevej@abc123.comSteveJ
3stevec@abc123.comSteve Collins
4moderator1@abc123.comJose SQL
5moderator2@abc123.comJane5687
6member1@abc123.comAdamF
7member2@abc123.comBean When
8 claims were created:
cl_idsecuritystampclaimhierarchy
174FEAEBE-B13D-4759-9B4F-F63636F48E3FSystem Administrator1
2943E3560-11A5-4ABA-89F6-75791E06F328Elevated Moderator 21
314106F8F-CFF7-48C5-A5A7-0DEB67CE7F1BElevated Moderator 12
4D1D82622-09B2-43FF-8C7A-EF9811468A12Moderator3
5076B2062-B615-480F-8ECB-64891BF736BAElevated Member 33
61C958ECB-C76C-412E-A44D-475E5F57A119Elevated Member 24
7EB7C20A4-9E37-4AEB-9B2C-EF2E8AB07D24Elevated Member 15
8B6A7572D-4426-4C7D-93F6-A0CB077EBACCMember6
Users were assigned claims and hierarchies:
insert frm.user_claims(securitystamp, u_id, cl_id)
select newid(),u.id, cl.cl_id
from @u u
cross join frm.claims cl
where u.username in('SysAdmin', 'SteveJ') /* Assign System Administrators */
and cl.hierarchy>=1
union all
select newid(),u.id, cl.cl_id
from @u u
cross join frm.claims cl
where u.username in('Steve Collins') /* Elevated Moderator 2(s) */
and (cl.claim='Elevated Moderator 2'
or cl.hierarchy>1)
union all
select newid(),u.id, cl.cl_id
from @u u
cross join frm.claims cl
where u.username in('Jose SQL', 'Jane5687') /* Moderator(s) */
and cl.hierarchy>=3
union all
select newid(),u.id, cl.cl_id
from @u u
cross join frm.claims cl
where u.username in('AdamF', 'Bean When') /* Member(s) */
and cl.claim='Member';
2 forum types were created:
ft_idft_title
2Main
1Site
7 site forums were created:
f_idft_idu_idtitletitle_body
211About the NDPUpdates and information.
411QuestionsAnswers are optional.
611Welcome to the NDPSay hello to the community.
811SuggestionsHow could we improve?
1011Members OnlyMembers can message the other members.
1211Moderators OnlyModerators can message other mods.
1411Admins OnlyAdministrators can message other admins.
10 main forums were created:
f_idft_idu_idtitletitle_body
1621Title 1Title 1 description
1821Title 2Title 2 description
2021Title 3Title 3 description
2221Title 4Title 4 description
2421Title 5Title 5 description
2621Title 6Title 6 description
2821Title 7Title 7 description
3021Title 8Title 8 description
3221Title 9Title 9 description
3421Title 10Title 10 description
April 29, 2021 at 2:19 pm
Added N prefix to unicode strings. Added time_zone to frm.Users.
set nocount on;
set xact_abort on;
begin transaction
/* create users */
declare @u table(id int unique not null,
username nvarchar(128) unique not null);
insert frm.users(email, username, time_zone, created_dt, edited_dt)
output inserted.id, inserted.username into @u
values
(N'admin@abc123.com', N'SysAdmin', N'Eastern Standard Time', sysutcdatetime(), sysutcdatetime()),
(N'stevej@abc123.com', N'SteveJ', N'Eastern Standard Time', sysutcdatetime(), sysutcdatetime()),
(N'stevec@abc123.com', N'Steve Collins', N'Eastern Standard Time', sysutcdatetime(), sysutcdatetime()),
(N'moderator1@abc123.com', N'Jose SQL', N'Eastern Standard Time', sysutcdatetime(), sysutcdatetime()),
(N'moderator2@abc123.com', N'Jane5687', N'Eastern Standard Time', sysutcdatetime(), sysutcdatetime()),
(N'member1@abc123.com', N'AdamF', N'Eastern Standard Time', sysutcdatetime(), sysutcdatetime()),
(N'member2@abc123.com', N'Bean When', N'Eastern Standard Time', sysutcdatetime(), sysutcdatetime());
/* create claims according to hierarchy */
insert frm.claims(securitystamp, claim, hierarchy)
values
(newid(), N'System Administrator', 1),
(newid(), N'Elevated Moderator 2', 1),
(newid(), N'Elevated Moderator 1', 2),
(newid(), N'Moderator', 3),
(newid(), N'Elevated Member 3', 3),
(newid(), N'Elevated Member 2', 4),
(newid(), N'Elevated Member 1', 5),
(newid(), N'Member', 6);
/* assign claims to users */
insert frm.user_claims(securitystamp, u_id, cl_id)
select newid(),u.id, cl.cl_id
from @u u
cross join frm.claims cl
where u.username in(N'SysAdmin', N'SteveJ') /* Assign System Administrators */
and cl.hierarchy>=1
union all
select newid(),u.id, cl.cl_id
from @u u
cross join frm.claims cl
where u.username in(N'Steve Collins') /* Elevated Moderator 2(s) */
and (cl.claim=N'Elevated Moderator 2'
or cl.hierarchy>1)
union all
select newid(),u.id, cl.cl_id
from @u u
cross join frm.claims cl
where u.username in(N'Jose SQL', N'Jane5687') /* Moderator(s) */
and cl.hierarchy>=3
union all
select newid(),u.id, cl.cl_id
from @u u
cross join frm.claims cl
where u.username in(N'AdamF', N'Bean When') /* Member(s) */
and cl.claim=N'Member';
/* create forum types */
insert frm.forum_types(ft_title) values
(N'Site'),
(N'Main');
/* Site Forums*/
declare
@u_id int=(select id from frm.users where username='SysAdmin'),
@site_ft_id int=(select ft_id from frm.forum_types where ft_title='Site'),
@main_ft_id int=(select ft_id from frm.forum_types where ft_title='Main');
insert frm.forums(ft_id, u_id, title, title_body, display_order, created_dt, edited_dt) values
(@site_ft_id, @u_id, N'About the NDP', N'Updates and information.', 1, sysutcdatetime(), sysutcdatetime()),
(@site_ft_id, @u_id, N'Questions', N'Answers are optional.', 2, sysutcdatetime(), sysutcdatetime()),
(@site_ft_id, @u_id, N'Welcome to the NDP', N'Say hello to the community.', 3, sysutcdatetime(), sysutcdatetime()),
(@site_ft_id, @u_id, N'Suggestions', N'How could we improve?', 4, sysutcdatetime(), sysutcdatetime()),
(@site_ft_id, @u_id, N'Members Only', N'Members can message the other members.', 5, sysutcdatetime(), sysutcdatetime()),
(@site_ft_id, @u_id, N'Moderators Only', N'Moderators can message other mods.', 7, sysutcdatetime(), sysutcdatetime()),
(@site_ft_id, @u_id, N'Admins Only', N'Administrators can message other admins.', 8, sysutcdatetime(), sysutcdatetime());
/* Main Forums*/
insert frm.forums(ft_id, u_id, title, title_body, display_order, created_dt, edited_dt) values
(@main_ft_id, @u_id, N'Title 1', N'Title 1 description', 1, sysutcdatetime(), sysutcdatetime()),
(@main_ft_id, @u_id, N'Title 2', N'Title 2 description', 2, sysutcdatetime(), sysutcdatetime()),
(@main_ft_id, @u_id, N'Title 3', N'Title 3 description', 3, sysutcdatetime(), sysutcdatetime()),
(@main_ft_id, @u_id, N'Title 4', N'Title 4 description', 4, sysutcdatetime(), sysutcdatetime()),
(@main_ft_id, @u_id, N'Title 5', N'Title 5 description', 5, sysutcdatetime(), sysutcdatetime()),
(@main_ft_id, @u_id, N'Title 6', N'Title 6 description', 6, sysutcdatetime(), sysutcdatetime()),
(@main_ft_id, @u_id, N'Title 7', N'Title 7 description', 7, sysutcdatetime(), sysutcdatetime()),
(@main_ft_id, @u_id, N'Title 8', N'Title 8 description', 8, sysutcdatetime(), sysutcdatetime()),
(@main_ft_id, @u_id, N'Title 9', N'Title 9 description', 9, sysutcdatetime(), sysutcdatetime()),
(@main_ft_id, @u_id, N'Title 10', N'Title 10 description', 10, sysutcdatetime(), sysutcdatetime());
commit;
--select * from frm.users;
--select * from frm.claims;
--select * from frm.forum_types;
--select * from frm.forums where ft_id=1;
--select * from frm.forums where ft_id=2;
April 29, 2021 at 2:23 pm
This script creates 3 stored procedures which create rows in the remaining 2 tables, 'frm.threads' and 'frm.posts' tables. The proc to create new threads is a plain insert. The proc to create new posts in response to threads is a plain insert. As far as I can tell there's 1 key query in the whole system which is the procedure to create posts in response to other posts. In this case it's a MERGE statement which seems to be functionally correct but MERGE has many issues in SQL Server. This code is not production quality imo. My question to SSC is how to refactor/rewrite/test the MERGE statement into separate INSERT and UPDATE statements?
The MERGE statement does the following:
-- (Locate Branch) Locate the records within the current tree path (lessening mutation issues) which are children of the reply_p_id.
-- (Locate Twig) From the branch, find the single "last record" in the tree_path after which the new post will be inserted.
-- (Locate Updates) Locate the records "after" the newly inserted tree_path.
-- (Union Merge) Union the Twig cte and Update cte to create a "merge" table which contains a dummy record (p_id=-1) twig (to be inserted) and the tree_path records which need to be incremented.
-- (Merge Insert/Update) Using the Union Merge cte both insert and update (within a single transaction). The updated records are all posts after (by after it means where tree_path > replied to post's tree_path) the post which is being replied to. The inserted record gets tree_path=twig_cte.max_tree_path+1. The updated records have tree_path set to merge_cte.tree_path+1 (to "skip over" the inserted post).
set nocount off;
/* determine if schema 'frm' already exits. If it does not, then throw an exception. */
if not exists (select 1 from sys.schemas where name = 'frm')
throw 50000, 'The frm schema does not exist. Please run ForumApiSQLServerDDLSetup1.sql.', 1;
/* drop all objects */
drop proc if exists frm.api_posts_post;
drop proc if exists frm.api_threads_post;
drop proc if exists frm.api_posts_reply_post;
go
create or alter proc frm.api_threads_post
@f_idint,
@u_idint,
@titlenvarchar(256),
@bodynvarchar(4000),
@test_t_idint output,
@test_msgvarchar(2048) output
as
set nocount on;
begin try
insert frm.threads(f_id, u_id, title, body, created_dt, edited_dt) values(@f_id, @u_id, @title, @body, sysutcdatetime(), sysutcdatetime());
select @test_t_id=scope_identity();
select @test_msg='Ok';
end try
begin catch
select @test_t_id=0;
select @test_msg=error_message();;
end catch
set nocount off;
go
/*POST - new post*/
create or alter proc frm.api_posts_post
@t_idint,
@u_idint,
@titlenvarchar(256),
@bodynvarchar(4000),
@test_p_idbigint output,
@test_msgvarchar(2048) output
as
set nocount on;
begin try
insert frm.posts(t_id, u_id, reply_depth_count, tree_path, title, body, created_dt, edited_dt) values
(@t_id, @u_id, 1, 1, @title, @body, sysutcdatetime(), sysutcdatetime());
select @test_p_id=scope_identity();
/* set original reply id to the new p_id (to enforce unique constraint with tree_path) */
update frm.posts
set orig_p_id=@test_p_id
where p_id=@test_p_id;
select @test_msg='Ok';
end try
begin catch
select @test_p_id=0;
select @test_msg=error_message();;
end catch
set nocount off;
go
create or alter proc frm.api_posts_reply_post
@t_idint,
@u_idint,
@reply_p_idbigint,
@titlenvarchar(256),
@bodynvarchar(4000),
@test_p_idbigint output,
@test_msgvarchar(2048) output
as
set nocount on;
set xact_abort on;
begin transaction
begin try
declare
@inserted_replytable(actnvarchar(20),
p_idbigint);
with
branch_cte(p_id, orig_p_id, reply_p_id, tree_path, reply_depth_count, created_dt, calc)
as (
select
p_id,
orig_p_id,
cast(null as bigint),
tree_path,
reply_depth_count,
created_dt,
0 calc
from
frm.posts p
where
p_id=@reply_p_id
union all
select
p1.p_id,
p1.orig_p_id,
p1.reply_p_id,
p1.tree_path,
p1.reply_depth_count,
p1.created_dt,
calc + 1
from
frm.posts p1
join
branch_cte b on p1.reply_p_id=b.p_id
),
twig_cte(p_id, orig_p_id, min_reply_depth, tree_path)
as (
select
cast(-1 as bigint),
b.orig_p_id,
min(b.reply_depth_count) min_reply_depth,
max(b.tree_path) tree_path
from
branch_cte b
group by
b.orig_p_id
),
update_cte(p_id, orig_p_id, min_reply_depth, tree_path)
as (
select
p.p_id,
t.orig_p_id,
t.min_reply_depth,
p.tree_path
from
frm.posts p
join
twig_cte t on p.orig_p_id=t.orig_p_id
and p.tree_path>t.tree_path
),
merge_cte(p_id, orig_p_id, min_reply_depth, tree_path)
as (
select * from twig_cte
union all
select * from update_cte
)
merge
frm.posts as target
using
merge_cte as source
on
(target.p_id=source.p_id)
when not matched by target then
insert(t_id, u_id, orig_p_id, reply_p_id, reply_depth_count, tree_path, title, body, created_dt, edited_dt) values
(@t_id, @u_id, source.orig_p_id, @reply_p_id, source.min_reply_depth+1, source.tree_path+1, @title, @body, sysutcdatetime(), sysutcdatetime())
when matched then
update set tree_path=target.tree_path+1
output
$action, inserted.p_id into @inserted_reply;
select @test_p_id=p_id from @inserted_reply where act='INSERT';
select @test_msg='Ok';
commit;
end try
begin catch
select @test_p_id=0;
select @test_msg=error_message();
rollback transaction;
end catch
set nocount off;
go
April 29, 2021 at 2:24 pm
Regarding the MERGE statement in the frm.api_posts_reply_post procedure the major issue I saw is/was the target is the entire frm.posts table. The Docs for MERGE is a litany of warnings, notes, and issues. Basically, give it a shot and if it works great! If not, then tweak and fiddle endlessly until it does. There's early optimization (which is bad) and then there's doing something stupid on purpose (which is also bad, and actually takes precedence). So to avoid known performance issues I added this CTE as the TARGET in the MERGE
tgt_cte as (
select *
from frm.posts
where orig_p_id=@reply_p_id),
The temporal procedure to "reply to post" is a much simpler UPDATE (procedure now suffixed with _put) statement. The proc for the temporal table requires the "reply_depth_count" instead of the "reply_p_id") as an input variable
create or alter proc frm.api_posts_sv_reply_put
@t_idint,
@p_idint,
@u_idint,
@reply_depth_countint,
@titlenvarchar(256),
@bodynvarchar(4000),
@test_valuebit output,
@test_msgvarchar(2048) output
as
set nocount on;
set xact_abort on;
begin transaction
begin try
update frm.posts_sv
set u_id=@u_id, title=@title, body=@body, reply_depth_count=@reply_depth_count+1
where t_id=@t_id
and p_id=@p_id;
select @test_value=cast(1 as bit);
select @test_msg='Ok';
commit;
end try
begin catch
select @test_value=cast(0 as bit);
select @test_msg=error_message();
rollback transaction;
end catch
set nocount off;
go
The total script (there other minor adds/fixes)
set nocount off;
/* determine if schema 'frm' already exits. If it does not, then throw an exception. */
if not exists (select 1 from sys.schemas where name = 'frm')
throw 50000, 'The frm schema does not exist. Please run ForumApiSQLServerDDLSetup1.sql.', 1;
/* drop all objects */
drop proc if exists frm.api_posts_sv_post;
drop proc if exists frm.api_posts_post;
drop proc if exists frm.api_threads_post;
drop proc if exists frm.api_posts_reply_post;
drop proc if exists frm.api_posts_sv_reply_put
go
create or alter proc frm.api_threads_post
@f_idint,
@u_idint,
@titlenvarchar(256),
@bodynvarchar(4000),
@test_t_idint output,
@test_msgvarchar(2048) output
as
set nocount on;
set xact_abort on;
begin transaction
begin try
insert frm.threads(f_id, u_id, title, body, created_dt, edited_dt) values(@f_id, @u_id, @title, @body, sysutcdatetime(), sysutcdatetime());
select @test_t_id=scope_identity();
select @test_msg='Ok';
commit;
end try
begin catch
select @test_t_id=0;
select @test_msg=error_message();;
rollback transaction;
end catch
set nocount off;
go
/*POST - new post*/
create or alter proc frm.api_posts_post
@t_idint,
@u_idint,
@titlenvarchar(256),
@bodynvarchar(4000),
@test_p_idbigint output,
@test_msgvarchar(2048) output
as
set nocount on;
set xact_abort on;
begin transaction
begin try
insert frm.posts(t_id, u_id, reply_depth_count, tree_path, title, body, created_dt, edited_dt) values
(@t_id, @u_id, 1, 1, @title, @body, sysutcdatetime(), sysutcdatetime());
select @test_p_id=scope_identity();
/* set original reply id to the new p_id (to enforce unique constraint with tree_path) */
update frm.posts
set orig_p_id=@test_p_id
where p_id=@test_p_id;
select @test_msg='Ok';
commit;
end try
begin catch
select @test_p_id=0;
select @test_msg=error_message();
rollback transaction;
end catch
set nocount off;
go
/*POST - new post*/
create or alter proc frm.api_posts_sv_post
@t_idint,
@u_idint,
@titlenvarchar(256),
@bodynvarchar(4000),
@test_p_idbigint output,
@test_msgvarchar(2048) output
as
set nocount on;
set xact_abort on;
begin transaction
begin try
insert frm.posts_sv(t_id, u_id, reply_depth_count, tree_path, title, body) values
(@t_id, @u_id, 1, 1, @title, @body);
select @test_p_id=scope_identity();
select @test_msg='Ok';
commit;
end try
begin catch
select @test_p_id=0;
select @test_msg=error_message();
rollback transaction;
end catch
set nocount off;
go
create or alter proc frm.api_posts_reply_post
@t_idint,
@u_idint,
@reply_p_idbigint,
@titlenvarchar(256),
@bodynvarchar(4000),
@test_p_idbigint output,
@test_msgvarchar(2048) output
as
set nocount on;
set xact_abort on;
begin transaction
begin try
declare
@inserted_replytable(actnvarchar(20),
p_idbigint);
with
tgt_cte as (
select *
from frm.posts
where orig_p_id=@reply_p_id),
branch_cte(p_id, orig_p_id, reply_p_id, tree_path, reply_depth_count, created_dt, calc)as (
select
p_id,
orig_p_id,
cast(null as bigint),
tree_path,
reply_depth_count,
created_dt,
0 calc
from
frm.posts p
where
p_id=@reply_p_id
union all
select
p1.p_id,
p1.orig_p_id,
p1.reply_p_id,
p1.tree_path,
p1.reply_depth_count,
p1.created_dt,
calc + 1
from
frm.posts p1
join
branch_cte b on p1.reply_p_id=b.p_id),
twig_cte(p_id, orig_p_id, min_reply_depth, tree_path) as (
select
cast(-1 as bigint),
b.orig_p_id,
min(b.reply_depth_count) min_reply_depth,
max(b.tree_path) tree_path
from
branch_cte b
group by
b.orig_p_id),
update_cte(p_id, orig_p_id, min_reply_depth, tree_path)as (
select
p.p_id,
t.orig_p_id,
t.min_reply_depth,
p.tree_path
from
frm.posts p
join
twig_cte t on p.orig_p_id=t.orig_p_id
and p.tree_path>t.tree_path),
merge_cte(p_id, orig_p_id, min_reply_depth, tree_path) as (
select * from twig_cte
union all
select * from update_cte)
merge tgt_cte as target
using merge_cte as source
on (target.p_id=source.p_id)
when not matched by target then insert(t_id, u_id, orig_p_id, reply_p_id, reply_depth_count, tree_path,
title, body, created_dt, edited_dt) values
(@t_id, @u_id, source.orig_p_id, @reply_p_id, source.min_reply_depth+1,
source.tree_path+1, @title, @body, sysutcdatetime(), sysutcdatetime())
when matched then update set tree_path=target.tree_path+1
output $action, inserted.p_id into @inserted_reply;
select @test_p_id=p_id from @inserted_reply where act='INSERT';
select @test_msg='Ok';
commit;
end try
begin catch
select @test_p_id=0;
select @test_msg=error_message();
rollback transaction;
end catch
set nocount off;
go
create or alter proc frm.api_posts_sv_reply_put
@t_idint,
@p_idint,
@u_idint,
@reply_depth_countint,
@titlenvarchar(256),
@bodynvarchar(4000),
@test_valuebit output,
@test_msgvarchar(2048) output
as
set nocount on;
set xact_abort on;
begin transaction
begin try
update frm.posts_sv
set u_id=@u_id, title=@title, body=@body, reply_depth_count=@reply_depth_count+1
where t_id=@t_id
and p_id=@p_id;
select @test_value=cast(1 as bit);
select @test_msg='Ok';
commit;
end try
begin catch
select @test_value=cast(0 as bit);
select @test_msg=error_message();
rollback transaction;
end catch
set nocount off;
go
April 29, 2021 at 2:25 pm
This script creates 5 threads in the 'frm.threads' table.
/*
delete frm.threads;
dbcc checkident ('frm.threads', reseed, 0) with no_infomsgs
go
*/
set nocount on;
set xact_abort on;
begin transaction
/*create proc frm.api_threads_post
@f_idint,
@u_idint,
@titlenvarchar(256),
@bodynvarchar(4000),
@test_t_idint output,
@test_msgvarchar(2048) output*/
declare
@f_id int=(select f_id from frm.forums where title='Title 4'),
@jose_u_id int=(select id from frm.users where username='Jose SQL'),
@jane_u_id int=(select id from frm.users where username='Jane5687'),
@adam_u_id int=(select id from frm.users where username='AdamF'),
@bean_u_id int=(select id from frm.users where username='Bean When');
declare
@output_t_id int,
@output_msg varchar(2048);
/* thread 1 in title4 forum */
exec frm.api_threads_post
@f_id=@f_id,
@u_id=@adam_u_id,
@title='First Thread',
@body='This is the VARCHAR(4000) thread 1 body. Lorem ipsum dolor sit amet, consectetur adipiscing elit, ...',
@test_t_id=@output_t_id output,
@test_msg=@output_msg output;
if @output_t_id=0
begin
print(@output_msg);
throw 50000, 'Error creating thread 1:', 1;
end
/* thread 2 in title4 forum */
exec frm.api_threads_post
@f_id=@f_id,
@u_id=@jose_u_id,
@title='Something of interest',
@body='Thread 2 body. Lorem ipsum dolor sit amet, consectetur adipiscing elit, ...',
@test_t_id=@output_t_id output,
@test_msg=@output_msg output;
if @output_t_id=0
begin
print(@output_msg);
throw 50000, 'Error creating thread 2:', 1;
end
/* thread 3 in title4 forum */
exec frm.api_threads_post
@f_id=@f_id,
@u_id=@bean_u_id,
@title='Next topic of interest',
@body='This is the VARCHAR(4000) thread 3 body. Lorem ipsum dolor sit amet, consectetur adipiscing elit, ...',
@test_t_id=@output_t_id output,
@test_msg=@output_msg output;
if @output_t_id=0
begin
print(@output_msg);
throw 50000, 'Error creating thread 3:', 1;
end
/* thread 4 in title4 forum */
exec frm.api_threads_post
@f_id=@f_id,
@u_id=@jane_u_id,
@title='Big announcement today',
@body='Thread 4 body. Lorem ipsum dolor sit amet, consectetur adipiscing elit, ...',
@test_t_id=@output_t_id output,
@test_msg=@output_msg output;
if @output_t_id=0
begin
print(@output_msg);
throw 50000, 'Error creating thread 4:', 1;
end
/* thread 5 in title4 forum */
exec frm.api_threads_post
@f_id=@f_id,
@u_id=@jane_u_id,
@title='In depth coverage',
@body='Thread 5 body. Lorem ipsum dolor sit amet, consectetur adipiscing elit, ...',
@test_t_id=@output_t_id output,
@test_msg=@output_msg output;
if @output_t_id=0
begin
print(@output_msg);
throw 50000, 'Error creating thread 5:', 1;
end
commit;
--select * from frm.forums;
--select * from frm.threads;
April 29, 2021 at 2:25 pm
This script creates 5 posts in the 'frm.posts' table. These are direct responses to threads.
/*
delete frm.posts;
dbcc checkident ('frm.posts', reseed, 0) with no_infomsgs
go
*/
set nocount on;
set xact_abort on;
begin transaction
/*create proc frm.api_posts_post
@t_idint,
@u_idint,
@titlenvarchar(256),
@bodynvarchar(4000),
@test_p_idbigint output,
@test_msgvarchar(2048) output*/
declare
@t_id int=(select t_id from frm.threads where title='Something of interest'),
@jose_u_id int=(select id from frm.users where username='Jose SQL'),
@jane_u_id int=(select id from frm.users where username='Jane5687'),
@adam_u_id int=(select id from frm.users where username='AdamF'),
@bean_u_id int=(select id from frm.users where username='Bean When');
declare
@output_p_id bigint,
@output_msg varchar(2048);
/* post reponse 1 to THREAD titled 'Something of interest' */
exec frm.api_posts_post
@t_id=@t_id,
@u_id=@adam_u_id,
@title='Post 1 direct to Thread',
@body='Lorem ipsum dolor sit amet...',
@test_p_id=@output_p_id output,
@test_msg=@output_msg output;
if @output_p_id=0
begin
print(@output_msg);
throw 50000, 'Error creating post 1:', 1;
end
/* post reponse 2 to THREAD titled 'Something of interest' */
exec frm.api_posts_post
@t_id=@t_id,
@u_id=@jose_u_id,
@title='Direct post 2',
@body='Lorem ipsum dolor sit amet...',
@test_p_id=@output_p_id output,
@test_msg=@output_msg output;
if @output_p_id=0
begin
print(@output_msg);
throw 50000, 'Error creating post 2:', 1;
end
/* post reponse 3 to THREAD titled 'Something of interest' */
exec frm.api_posts_post
@t_id=@t_id,
@u_id=@jane_u_id,
@title='Direct post 3',
@body='Lorem ipsum dolor sit amet...',
@test_p_id=@output_p_id output,
@test_msg=@output_msg output;
if @output_p_id=0
begin
print(@output_msg);
throw 50000, 'Error creating post 3:', 1;
end
/* post reponse 4 to THREAD titled 'Something of interest' */
exec frm.api_posts_post
@t_id=@t_id,
@u_id=@jane_u_id,
@title='4th post to thread',
@body='Lorem ipsum dolor sit amet...',
@test_p_id=@output_p_id output,
@test_msg=@output_msg output;
if @output_p_id=0
begin
print(@output_msg);
throw 50000, 'Error creating post 4:', 1;
end
/* post reponse 5 to THREAD titled 'Something of interest' */
exec frm.api_posts_post
@t_id=@t_id,
@u_id=@bean_u_id,
@title='Yes/No 5th post to thread',
@body='Lorem ipsum dolor sit amet...',
@test_p_id=@output_p_id output,
@test_msg=@output_msg output;
if @output_p_id=0
begin
print(@output_msg);
throw 50000, 'Error creating post 5:', 1;
end
commit;
--select * from frm.forums;
--select * from frm.threads;
--select * from frm.posts;
April 29, 2021 at 2:26 pm
This script creates 5 posts in the 'frm.posts' table. These are direct responses to threads.
/*
delete frm.posts;
dbcc checkident ('frm.posts', reseed, 0) with no_infomsgs
go
*/
set nocount on;
set xact_abort on;
begin transaction
/*create proc frm.api_posts_post
@t_idint,
@u_idint,
@titlenvarchar(256),
@bodynvarchar(4000),
@test_p_idbigint output,
@test_msgvarchar(2048) output*/
declare
@t_id int=(select t_id from frm.threads where title='Something of interest'),
@jose_u_id int=(select id from frm.users where username='Jose SQL'),
@jane_u_id int=(select id from frm.users where username='Jane5687'),
@adam_u_id int=(select id from frm.users where username='AdamF'),
@bean_u_id int=(select id from frm.users where username='Bean When');
declare
@output_p_id bigint,
@output_msg varchar(2048);
/* post reponse 1 to THREAD titled 'Something of interest' */
exec frm.api_posts_post
@t_id=@t_id,
@u_id=@adam_u_id,
@title='Post 1 direct to Thread',
@body='Lorem ipsum dolor sit amet...',
@test_p_id=@output_p_id output,
@test_msg=@output_msg output;
if @output_p_id=0
begin
print(@output_msg);
throw 50000, 'Error creating post 1:', 1;
end
/* post reponse 2 to THREAD titled 'Something of interest' */
exec frm.api_posts_post
@t_id=@t_id,
@u_id=@jose_u_id,
@title='Direct post 2',
@body='Lorem ipsum dolor sit amet...',
@test_p_id=@output_p_id output,
@test_msg=@output_msg output;
if @output_p_id=0
begin
print(@output_msg);
throw 50000, 'Error creating post 2:', 1;
end
/* post reponse 3 to THREAD titled 'Something of interest' */
exec frm.api_posts_post
@t_id=@t_id,
@u_id=@jane_u_id,
@title='Direct post 3',
@body='Lorem ipsum dolor sit amet...',
@test_p_id=@output_p_id output,
@test_msg=@output_msg output;
if @output_p_id=0
begin
print(@output_msg);
throw 50000, 'Error creating post 3:', 1;
end
/* post reponse 4 to THREAD titled 'Something of interest' */
exec frm.api_posts_post
@t_id=@t_id,
@u_id=@jane_u_id,
@title='4th post to thread',
@body='Lorem ipsum dolor sit amet...',
@test_p_id=@output_p_id output,
@test_msg=@output_msg output;
if @output_p_id=0
begin
print(@output_msg);
throw 50000, 'Error creating post 4:', 1;
end
/* post reponse 5 to THREAD titled 'Something of interest' */
exec frm.api_posts_post
@t_id=@t_id,
@u_id=@bean_u_id,
@title='Yes/No 5th post to thread',
@body='Lorem ipsum dolor sit amet...',
@test_p_id=@output_p_id output,
@test_msg=@output_msg output;
if @output_p_id=0
begin
print(@output_msg);
throw 50000, 'Error creating post 5:', 1;
end
commit;
--select * from frm.forums;
--select * from frm.threads;
--select * from frm.posts;
April 29, 2021 at 2:27 pm
Added statements to populate frm.posts_sv as well as frm.posts.
/*
delete frm.posts;
dbcc checkident ('frm.posts', reseed, 0) with no_infomsgs
alter table frm.posts_sv set (system_versioning = off);
delete frm.posts_sv;
delete frm.posts_sv_history;
alter table frm.posts_sv set (system_versioning = on (history_table=frm.posts_sv_history));
dbcc checkident ('frm.posts_sv', reseed, 0) with no_infomsgs
go
*/
set nocount on;
set xact_abort on;
begin transaction
/*create proc frm.api_posts_post
@t_idint,
@u_idint,
@titlenvarchar(256),
@bodynvarchar(4000),
@test_p_idbigint output,
@test_msgvarchar(2048) output*/
declare
@t_id int=(select t_id from frm.threads where title='Something of interest'),
@jose_u_id int=(select id from frm.users where username='Jose SQL'),
@jane_u_id int=(select id from frm.users where username='Jane5687'),
@adam_u_id int=(select id from frm.users where username='AdamF'),
@bean_u_id int=(select id from frm.users where username='Bean When');
declare
@output_p_id bigint,
@output_msg varchar(2048);
/* post reponse 1 to THREAD titled 'Something of interest' */
exec frm.api_posts_post
@t_id=@t_id,
@u_id=@adam_u_id,
@title='Post 1 direct to Thread',
@body='Lorem ipsum dolor sit amet...',
@test_p_id=@output_p_id output,
@test_msg=@output_msg output;
if @output_p_id=0
begin
print(@output_msg);
throw 50000, 'Error creating post 1:', 1;
end
/* post reponse 2 to THREAD titled 'Something of interest' */
exec frm.api_posts_post
@t_id=@t_id,
@u_id=@jose_u_id,
@title='Direct post 2',
@body='Lorem ipsum dolor sit amet...',
@test_p_id=@output_p_id output,
@test_msg=@output_msg output;
if @output_p_id=0
begin
print(@output_msg);
throw 50000, 'Error creating post 2:', 1;
end
/* post reponse 3 to THREAD titled 'Something of interest' */
exec frm.api_posts_post
@t_id=@t_id,
@u_id=@jane_u_id,
@title='Direct post 3',
@body='Lorem ipsum dolor sit amet...',
@test_p_id=@output_p_id output,
@test_msg=@output_msg output;
if @output_p_id=0
begin
print(@output_msg);
throw 50000, 'Error creating post 3:', 1;
end
/* post reponse 4 to THREAD titled 'Something of interest' */
exec frm.api_posts_post
@t_id=@t_id,
@u_id=@jane_u_id,
@title='4th post to thread',
@body='Lorem ipsum dolor sit amet...',
@test_p_id=@output_p_id output,
@test_msg=@output_msg output;
if @output_p_id=0
begin
print(@output_msg);
throw 50000, 'Error creating post 4:', 1;
end
/* post reponse 5 to THREAD titled 'Something of interest' */
exec frm.api_posts_post
@t_id=@t_id,
@u_id=@bean_u_id,
@title='Yes/No 5th post to thread',
@body='Lorem ipsum dolor sit amet...',
@test_p_id=@output_p_id output,
@test_msg=@output_msg output;
if @output_p_id=0
begin
print(@output_msg);
throw 50000, 'Error creating post 5:', 1;
end
/*create proc frm.api_posts_sv_post
@t_idint,
@u_idint,
@titlenvarchar(256),
@bodynvarchar(4000),
@test_p_idbigint output,
@test_msgvarchar(2048) output*/
/*
declare
@t_id int=(select t_id from frm.threads where title='Something of interest'),
@jose_u_id int=(select id from frm.users where username='Jose SQL'),
@jane_u_id int=(select id from frm.users where username='Jane5687'),
@adam_u_id int=(select id from frm.users where username='AdamF'),
@bean_u_id int=(select id from frm.users where username='Bean When');
declare
@output_p_id bigint,
@output_msg varchar(2048);
*/
/* post reponse 1 to THREAD titled 'Something of interest' */
exec frm.api_posts_sv_post
@t_id=@t_id,
@u_id=@adam_u_id,
@title='Post 1 direct to Thread',
@body='Lorem ipsum dolor sit amet...',
@test_p_id=@output_p_id output,
@test_msg=@output_msg output;
if @output_p_id=0
begin
print(@output_msg);
throw 50000, 'Error creating post 1:', 1;
end
/* post reponse 2 to THREAD titled 'Something of interest' */
exec frm.api_posts_sv_post
@t_id=@t_id,
@u_id=@jose_u_id,
@title='Direct post 2',
@body='Lorem ipsum dolor sit amet...',
@test_p_id=@output_p_id output,
@test_msg=@output_msg output;
if @output_p_id=0
begin
print(@output_msg);
throw 50000, 'Error creating post 2:', 1;
end
/* post reponse 3 to THREAD titled 'Something of interest' */
exec frm.api_posts_sv_post
@t_id=@t_id,
@u_id=@jane_u_id,
@title='Direct post 3',
@body='Lorem ipsum dolor sit amet...',
@test_p_id=@output_p_id output,
@test_msg=@output_msg output;
if @output_p_id=0
begin
print(@output_msg);
throw 50000, 'Error creating post 3:', 1;
end
/* post reponse 4 to THREAD titled 'Something of interest' */
exec frm.api_posts_sv_post
@t_id=@t_id,
@u_id=@jane_u_id,
@title='4th post to thread',
@body='Lorem ipsum dolor sit amet...',
@test_p_id=@output_p_id output,
@test_msg=@output_msg output;
if @output_p_id=0
begin
print(@output_msg);
throw 50000, 'Error creating post 4:', 1;
end
/* post reponse 5 to THREAD titled 'Something of interest' */
exec frm.api_posts_sv_post
@t_id=@t_id,
@u_id=@bean_u_id,
@title='Yes/No 5th post to thread',
@body='Lorem ipsum dolor sit amet...',
@test_p_id=@output_p_id output,
@test_msg=@output_msg output;
if @output_p_id=0
begin
print(@output_msg);
throw 50000, 'Error creating post 5:', 1;
end
commit;
--select * from frm.forums;
--select * from frm.threads;
select * from frm.posts;
select * from frm.posts_sv;
April 29, 2021 at 2:27 pm
This script creates 7 posts in the 'frm.posts' table. These are "indirect responses" to thread(s) or replies to (other) posts. The variable declarations are repeated for each procedure execution (to step through 1 reply at a time). For the purpose of creating example posts the reply p_id's are located by the post title which is not really legitimate as the uniqueness is not enforced. This code is only for entering example data purposes.
/*
delete frm.posts;
dbcc checkident ('frm.posts', reseed, 0) with no_infomsgs
go
*/
/*create proc frm.api_posts_reply_post
@t_idint,
@u_idint,
@reply_p_idbigint,
@titlenvarchar(256),
@bodynvarchar(4000),
@test_p_idbigint output,
@test_msgvarchar(2048) output*/
/*declare
@jose_u_id int=(select id from frm.users where username='Jose SQL'),
@jane_u_id int=(select id from frm.users where username='Jane5687'),
@adam_u_id int=(select id from frm.users where username='AdamF'),
@bean_u_id int=(select id from frm.users where username='Bean When');*/
/* ------------------------------------------------------------------*/
/* ------------------------------------------------------------------*/
/* post reponse 1 to POST titled '4th post to thread' */
set nocount on;
set xact_abort on;
begin transaction
declare
@t_id int=(select t_id from frm.threads where title='Something of interest'),
@adam_u_id int=(select id from frm.users where username='AdamF');
declare
@output_p_id bigint,
@output_msg varchar(2048);
declare
@reply_p_id bigint=(select p_id from frm.posts where title='4th post to thread');
exec frm.api_posts_reply_post
@t_id=@t_id,
@u_id=@adam_u_id,
@reply_p_id=@reply_p_id,
@title='First reply post to another post.',
@body='Lorem ipsum dolor sit amet...',
@test_p_id=@output_p_id output,
@test_msg=@output_msg output;
if @output_p_id=0
begin
print(@output_msg);
throw 50000, 'Error creating reply post 1:', 1;
end
go
commit;
/* ------------------------------------------------------------------*/
/* ------------------------------------------------------------------*/
/* post reponse 2 to POST titled 'First reply post to another post.' */
set nocount on;
set xact_abort on;
begin transaction
declare
@t_id int=(select t_id from frm.threads where title='Something of interest'),
@jose_u_id int=(select id from frm.users where username='Jose SQL');
declare
@output_p_id bigint,
@output_msg varchar(2048);
declare
@reply_p_id bigint=(select p_id from frm.posts where title='First reply post to another post.');
exec frm.api_posts_reply_post
@t_id=@t_id,
@u_id=@jose_u_id,
@reply_p_id=@reply_p_id,
@title='Indirect post 2',
@body='Lorem ipsum dolor sit amet...',
@test_p_id=@output_p_id output,
@test_msg=@output_msg output;
if @output_p_id=0
begin
print(@output_msg);
throw 50000, 'Error creating reply post 2:', 1;
end
commit;
/* ------------------------------------------------------------------*/
/* ------------------------------------------------------------------*/
/* post reponse 3 to POST titled 'Indirect post 2' */
set nocount on;
set xact_abort on;
begin transaction
declare
@t_id int=(select t_id from frm.threads where title='Something of interest'),
@jane_u_id int=(select id from frm.users where username='Jane5687');
declare
@output_p_id bigint,
@output_msg varchar(2048);
declare
@reply_p_id bigint=(select p_id from frm.posts where title='Indirect post 2');
exec frm.api_posts_reply_post
@t_id=@t_id,
@u_id=@jane_u_id,
@reply_p_id=@reply_p_id,
@title='Indirect post 3',
@body='Lorem ipsum dolor sit amet...',
@test_p_id=@output_p_id output,
@test_msg=@output_msg output;
if @output_p_id=0
begin
print(@output_msg);
throw 50000, 'Error creating reply post 3:', 1;
end
commit;
/* ------------------------------------------------------------------*/
/* ------------------------------------------------------------------*/
/* post reponse 5 to POST titled 'Indirect post 3' */
set nocount on;
set xact_abort on;
begin transaction
declare
@t_id int=(select t_id from frm.threads where title='Something of interest'),
@bean_u_id int=(select id from frm.users where username='Bean When');
declare
@output_p_id bigint,
@output_msg varchar(2048);
declare
@reply_p_id bigint=(select p_id from frm.posts where title='Indirect post 3');
exec frm.api_posts_reply_post
@t_id=@t_id,
@u_id=@bean_u_id,
@reply_p_id=@reply_p_id,
@title='Indirect post 4',
@body='Lorem ipsum dolor sit amet...',
@test_p_id=@output_p_id output,
@test_msg=@output_msg output;
if @output_p_id=0
begin
print(@output_msg);
throw 50000, 'Error creating reply post 5:', 1;
end
commit;
/* ------------------------------------------------------------------*/
/* ------------------------------------------------------------------*/
/* post reponse 6 to POST titled 'Indirect post 2' */
set nocount on;
set xact_abort on;
begin transaction
declare
@t_id int=(select t_id from frm.threads where title='Something of interest'),
@jose_u_id int=(select id from frm.users where username='Jose SQL');
declare
@output_p_id bigint,
@output_msg varchar(2048);
declare
@reply_p_id bigint=(select p_id from frm.posts where title='Indirect post 2');
exec frm.api_posts_reply_post
@t_id=@t_id,
@u_id=@jose_u_id,
@reply_p_id=@reply_p_id,
@title='New Title (from test# 6)',
@body='Lorem ipsum dolor sit amet...',
@test_p_id=@output_p_id output,
@test_msg=@output_msg output;
if @output_p_id=0
begin
print(@output_msg);
throw 50000, 'Error creating reply post 6:', 1;
end
commit;
/* ------------------------------------------------------------------*/
/* ------------------------------------------------------------------*/
/* post reponse 7 to POST titled 'Indirect post 2' */
set nocount on;
set xact_abort on;
begin transaction
declare
@t_id int=(select t_id from frm.threads where title='Something of interest'),
@jose_u_id int=(select id from frm.users where username='Jose SQL');
declare
@output_p_id bigint,
@output_msg varchar(2048);
declare
@reply_p_id bigint=(select p_id from frm.posts where title='Indirect post 2');
exec frm.api_posts_reply_post
@t_id=@t_id,
@u_id=@jose_u_id,
@reply_p_id=@reply_p_id,
@title='New Next Title (from reply test# 7)',
@body='Lorem ipsum dolor sit amet...',
@test_p_id=@output_p_id output,
@test_msg=@output_msg output;
if @output_p_id=0
begin
print(@output_msg);
throw 50000, 'Error creating reply post 7:', 1;
end
commit;
--select * from frm.forums;
--select * from frm.threads;
/* recency view */
--select * from frm.posts order by created_dt;
/* threaded view */
--select * from frm.posts order by orig_p_id, tree_path;
April 29, 2021 at 2:28 pm
This script creates 2 procedures which return view models in JSON format. The view models return the posts for a thread in either recent view (plain old normal view) or threaded view (similar to old Drupal with nesting expanded). Both implement offset paging.
set nocount off;
/* determine if schema 'frm' already exits. If it does not, then throw an exception. */
if not exists (select 1 from sys.schemas where name = 'frm')
throw 50000, 'The frm schema does not exist. Please run ForumApiSQLServerDDLSetup1.sql.', 1;
/* drop all objects */
drop proc if exists frm.api_posts_recent_view_get;
drop proc if exists frm.api_posts_threaded_view_get;
go
--drop proc frm.api_posts_recent_view_get;
create or alter proc frm.api_posts_recent_view_get
@t_idint,
@nint,
@sint
as
set nocount on;
with
thread_cte as (
select
t.t_id,
t.title thread_title,
t.body thread_body
from
frm.threads t
where
t.t_id=@t_id),
posts_cte as (
select
p.p_id,
p.orig_p_id,
p.reply_p_id,
p.reply_depth_count,
p.title post_title,
p.body post_body,
p.created_dt post_created_dt,
p.edited_dt post_edited_dt,
p.up_count,
p.down_count,
u.id u_id,
u.username,
u.created_dt user_created_dt,
row_number() over(order by p.created_dt desc) ranking
from
frm.posts p
join
frm.users u on p.u_id=u.id
where
p.t_id=@t_id
and p.banned_flag<>1
order by
p.created_dt desc
offset
(@n-1)*@s rows
fetch next
@s rows only)
select
tc.*,
(select * from posts_cte order by ranking asc for json path) posts
from
thread_cte tc
for json path, without_array_wrapper;
set nocount off
go
--drop proc api_posts_threaded_view_get;
create or alter proc frm.api_posts_threaded_view_get
@t_idint,
@nint,
@sint
as
set nocount on;
with
thread_cte as (
select
t.t_id,
t.title thread_title,
t.body thread_body
from
frm.threads t
where
t.t_id=@t_id),
posts_cte as (
select
p.p_id,
p.orig_p_id,
p.reply_p_id,
p.reply_depth_count,
p.title post_title,
p.body post_body,
p.created_dt post_created_dt,
p.edited_dt post_edited_dt,
p.up_count,
p.down_count,
u.id u_id,
u.username,
u.created_dt user_created_dt,
row_number() over(order by p.orig_p_id desc, p.tree_path asc) ranking
from
frm.posts p
join
frm.users u on p.u_id=u.id
where
p.t_id=@t_id
and p.banned_flag<>1
order by
p.orig_p_id desc,
p.tree_path asc
offset
(@n-1)*@s rows
fetch next
@s rows only)
select
tc.*,
(select * from posts_cte order by ranking asc for json path) posts
from
thread_cte tc
for json path, without_array_wrapper;
set nocount off
go
--select * from frm.threads
--select * from frm.posts
exec frm.api_posts_recent_view_get 2, 1, 50;
exec frm.api_posts_threaded_view_get 2, 1, 50;
April 30, 2021 at 3:48 pm
.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
May 13, 2021 at 4:04 pm
.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
May 13, 2021 at 4:39 pm
.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply