Forum Api Project

  • 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

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • Setup #2 Seed Data

    This script inserts seed data into 5 of the 7 tables created by setup1.

    7 users were created:

    id	email			username
    1 admin@abc123.com SysAdmin
    2 stevej@abc123.com SteveJ
    3 stevec@abc123.com Steve Collins
    4 moderator1@abc123.com Jose SQL
    5 moderator2@abc123.com Jane5687
    6 member1@abc123.com AdamF
    7 member2@abc123.com Bean When

    8 claims were created:

    cl_id	securitystamp				claim			hierarchy
    1 74FEAEBE-B13D-4759-9B4F-F63636F48E3F System Administrator 1
    2 943E3560-11A5-4ABA-89F6-75791E06F328 Elevated Moderator 2 1
    3 14106F8F-CFF7-48C5-A5A7-0DEB67CE7F1B Elevated Moderator 1 2
    4 D1D82622-09B2-43FF-8C7A-EF9811468A12 Moderator 3
    5 076B2062-B615-480F-8ECB-64891BF736BA Elevated Member 3 3
    6 1C958ECB-C76C-412E-A44D-475E5F57A119 Elevated Member 2 4
    7 EB7C20A4-9E37-4AEB-9B2C-EF2E8AB07D24 Elevated Member 1 5
    8 B6A7572D-4426-4C7D-93F6-A0CB077EBACC Member 6

    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_id	ft_title
    2 Main
    1 Site

    7 site forums were created:

    f_id	ft_id	u_id	title			title_body
    2 1 1 About the NDP Updates and information.
    4 1 1 Questions Answers are optional.
    6 1 1 Welcome to the NDP Say hello to the community.
    8 1 1 Suggestions How could we improve?
    10 1 1 Members Only Members can message the other members.
    12 1 1 Moderators Only Moderators can message other mods.
    14 1 1 Admins Only Administrators can message other admins.

    10 main forums were created:

    f_id	ft_id	u_id	title		title_body
    16 2 1 Title 1 Title 1 description
    18 2 1 Title 2 Title 2 description
    20 2 1 Title 3 Title 3 description
    22 2 1 Title 4 Title 4 description
    24 2 1 Title 5 Title 5 description
    26 2 1 Title 6 Title 6 description
    28 2 1 Title 7 Title 7 description
    30 2 1 Title 8 Title 8 description
    32 2 1 Title 9 Title 9 description
    34 2 1 Title 10 Title 10 description
  • 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;
  • 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_id int,
    @u_id int,
    @title nvarchar(256),
    @body nvarchar(4000),
    @test_t_id int output,
    @test_msg varchar(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_id int,
    @u_id int,
    @title nvarchar(256),
    @body nvarchar(4000),
    @test_p_id bigint output,
    @test_msg varchar(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_id int,
    @u_id int,
    @reply_p_id bigint,
    @title nvarchar(256),
    @body nvarchar(4000),
    @test_p_id bigint output,
    @test_msg varchar(2048) output
    as
    set nocount on;
    set xact_abort on;

    begin transaction
    begin try
    declare
    @inserted_reply table(act nvarchar(20),
    p_id bigint);

    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
  • 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_id int,
    @p_id int,
    @u_id int,
    @reply_depth_count int,
    @title nvarchar(256),
    @body nvarchar(4000),
    @test_value bit output,
    @test_msg varchar(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_id int,
    @u_id int,
    @title nvarchar(256),
    @body nvarchar(4000),
    @test_t_id int output,
    @test_msg varchar(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_id int,
    @u_id int,
    @title nvarchar(256),
    @body nvarchar(4000),
    @test_p_id bigint output,
    @test_msg varchar(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_id int,
    @u_id int,
    @title nvarchar(256),
    @body nvarchar(4000),
    @test_p_id bigint output,
    @test_msg varchar(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_id int,
    @u_id int,
    @reply_p_id bigint,
    @title nvarchar(256),
    @body nvarchar(4000),
    @test_p_id bigint output,
    @test_msg varchar(2048) output
    as
    set nocount on;
    set xact_abort on;

    begin transaction
    begin try
    declare
    @inserted_reply table(act nvarchar(20),
    p_id bigint);

    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_id int,
    @p_id int,
    @u_id int,
    @reply_depth_count int,
    @title nvarchar(256),
    @body nvarchar(4000),
    @test_value bit output,
    @test_msg varchar(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

     

  • 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_id int,
    @u_id int,
    @title nvarchar(256),
    @body nvarchar(4000),
    @test_t_id int output,
    @test_msg varchar(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;
  • 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_id int,
    @u_id int,
    @title nvarchar(256),
    @body nvarchar(4000),
    @test_p_id bigint output,
    @test_msg varchar(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;
  • 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_id int,
    @u_id int,
    @title nvarchar(256),
    @body nvarchar(4000),
    @test_p_id bigint output,
    @test_msg varchar(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;
  • 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_id int,
    @u_id int,
    @title nvarchar(256),
    @body nvarchar(4000),
    @test_p_id bigint output,
    @test_msg varchar(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_id int,
    @u_id int,
    @title nvarchar(256),
    @body nvarchar(4000),
    @test_p_id bigint output,
    @test_msg varchar(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;
  • 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_id int,
    @u_id int,
    @reply_p_id bigint,
    @title nvarchar(256),
    @body nvarchar(4000),
    @test_p_id bigint output,
    @test_msg varchar(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;

     

     

  • 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_id int,
    @n int,
    @s int
    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_id int,
    @n int,
    @s int
    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;

     

  • Thank you for consolidating these into one post.  To summarize the current code (from above):

    /* 1 schema */
    frm

    /* 8 tables */
    frm.posts_sv (frm.posts_sv_history)
    frm.posts
    frm.threads
    frm.forums
    frm.forum_types
    frm.user_claims
    frm.claims
    frm.users

    /* 7 Users */
    id email username
    1 admin@abc123.com SysAdmin
    2 stevej@abc123.com SteveJ
    3 stevec@abc123.com Steve Collins
    4 moderator1@abc123.com Jose SQL
    5 moderator2@abc123.com Jane5687
    6 member1@abc123.com AdamF
    7 member2@abc123.com Bean When

    /* 8 User Claims (with 6 hierarchy levels) */
    cl_id claim hierarchy
    1 System Administrator 1
    2 Elevated Moderator 2 1
    3 Elevated Moderator 1 2
    4 Moderator 3
    5 Elevated Member 3 3
    6 Elevated Member 2 4
    7 Elevated Member 1 5
    8 Member 6

    /* 35 Claims assigned to users */
    id email username claim
    1 admin@abc123.com SysAdmin System Administrator
    1 admin@abc123.com SysAdmin Elevated Moderator 2
    1 admin@abc123.com SysAdmin Elevated Moderator 1
    1 admin@abc123.com SysAdmin Moderator
    1 admin@abc123.com SysAdmin Elevated Member 3
    1 admin@abc123.com SysAdmin Elevated Member 2
    1 admin@abc123.com SysAdmin Elevated Member 1
    1 admin@abc123.com SysAdmin Member
    2 stevej@abc123.com SteveJ System Administrator
    2 stevej@abc123.com SteveJ Elevated Moderator 2
    2 stevej@abc123.com SteveJ Elevated Moderator 1
    2 stevej@abc123.com SteveJ Moderator
    2 stevej@abc123.com SteveJ Elevated Member 3
    2 stevej@abc123.com SteveJ Elevated Member 2
    2 stevej@abc123.com SteveJ Elevated Member 1
    2 stevej@abc123.com SteveJ Member
    3 stevec@abc123.com Steve Collins Elevated Moderator 2
    3 stevec@abc123.com Steve Collins Elevated Moderator 1
    3 stevec@abc123.com Steve Collins Moderator
    3 stevec@abc123.com Steve Collins Elevated Member 3
    3 stevec@abc123.com Steve Collins Elevated Member 2
    3 stevec@abc123.com Steve Collins Elevated Member 1
    3 stevec@abc123.com Steve Collins Member
    4 moderator1@abc123.com Jose SQL Moderator
    4 moderator1@abc123.com Jose SQL Elevated Member 3
    4 moderator1@abc123.com Jose SQL Elevated Member 2
    4 moderator1@abc123.com Jose SQL Elevated Member 1
    4 moderator1@abc123.com Jose SQL Member
    5 moderator2@abc123.com Jane5687 Moderator
    5 moderator2@abc123.com Jane5687 Elevated Member 3
    5 moderator2@abc123.com Jane5687 Elevated Member 2
    5 moderator2@abc123.com Jane5687 Elevated Member 1
    5 moderator2@abc123.com Jane5687 Member
    6 member1@abc123.com AdamF Member
    7 member2@abc123.com Bean When Member

    /* 2 forum types */
    Site
    Main

    /* 7 site forums */
    title title_body
    About the NDP Updates and information.
    Questions Answers are optional.
    Welcome to the NDP Say hello to the community.
    Suggestions How could we improve?
    Members Only Members can message the other members.
    Moderators Only Moderators can message other mods.
    Admins Only Administrators can message other admins.

    /* 10 main forums */
    title title_body
    Title 1 Title 1 description
    Title 2 Title 2 description
    Title 3 Title 3 description
    Title 4 Title 4 description
    Title 5 Title 5 description
    Title 6 Title 6 description
    Title 7 Title 7 description
    Title 8 Title 8 description
    Title 9 Title 9 description
    Title 10 Title 10 description

    /* 5 stored procedures (2 different approaches: 1) rCTE, and 2) System Versioned) */
    frm.api_threads_post (Create new forum thread)
    frm.api_posts_post (Create new forum post (direct) (rCTE))
    frm.api_posts_reply_post (Create new forum post (indirect) (rCTE))
    frm.api_posts_sv_post (Create new forum post (direct) (System Versioned))
    frm.api_posts_sv_reply_put (Create new forum post (indirect) (System Versioned))

    /* 2 view models (stored procedures) */
    frm.api_posts_recent_view_get
    frm.api_posts_threaded_view_get

    /* 5 Example Threads */
    (within forum: 'Title 4')

    /* 5 Example Posts ((direct) (rCTE)) */
    (within forum: 'Title 4' and thread: 'Something of interest')
    /* 7 Example Reply Posts ((indirect) (rCTE)) */
    (within forum: 'Title 4', thread: 'Something of interest', post(s): '...[multiple]')

    /* 5 Example Posts ((direct) (System Versioned)) */
    (within forum: 'Title 4' and thread: 'Something of interest')
    /* 7 Example Reply Posts ((indirect) (System Versioned)) */
    (within forum: 'Title 4', thread: 'Something of interest', post(s): '...[multiple]')

    The purpose so far has been to demonstrate the fundamental "persistence" functionality of a threaded forum system.  Beyond SQL tho there are currently a lot of missing pieces needed before this project could become a credible service offering.   At this point it seems to make sense to explain a little the plan for filling in these gaps.

    First, one way to look at it is there are the 3 main areas which must be addressed:

    1.  More SQL DDL+CRUD DML+View Models
    2. API server code (C# WebAPI), Controllers, Models, etc.
    3. Identity and Access Management / API Infrastructure (Build/Test/Deploy)

    So what's my plan?

    1. Use (library-based, optimized) C# code and T4 templates to generate boilerplate SQL code.  Use OpenAPI-based swagger.json as input to code generator.
    2. Use .NET Core filters to intercept/short-circuit API requests.  Replace custom Controller logic with (library-based, optimized) framework code
    3. Skip I&AM for testing purposes.  If this project proceeds to a detailed consideration then my employer would offer I&AM services along with other aspects of a deployment.

    Unlike other SSC forum posters who've created data access workflows this could be demonstrated without the library first.  That's my plan: set up a project in GitHub which has all of the underlying code which make up the C# library and also structure the project so (if someone wanted to) to replace the solution code with library code would be very simple.

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Hopefully this update starts the Forum Api project on track to make steady progress.  It's been a bit delayed because the code is very new and it's just taken a while to refine if/how it works.  My partner tested out the code on his PC yesterday and it worked without issue so I'm optimistic.

    https://github.com/SteveCollins/RelatableCodeGeneratorApi

    By cloning and running the Visual Studio 2019 solution file contained in this repository and inputting a JSON file containing endpoint information a fully functional Forum API can be created based on 100% generated code.

    Generated code:

    In RelatableCodeGeneratorApi/Controllers - 4 API controllers containing 32 different method endpoints (264 lines of C# code)

    In RelatableCodeGeneratorApi/Procedures - 4 SQL scripts containing 32 structured subscripts (2,237 lines of t-sql SQL Server code)

    Configuration is very simple.  The connection string(s) (with appropriate privileges) to an instance of SQL Server (must be 2016 or newer afaik) must be provided in 2 places.  The RelatableCodeGenerator and API projects both contain 'appsettings.json' files in which the connection string objects contain placeholder values which must be replaced with the real strings.  The input Controller JSON is  from a Google Sheet which is the API design document for the Forum Api.

    With the 2 connection strings in place the code generators can be executed.  There are 2 "base" templates which control the codegen.    ControllersTemplate1.tt defines and controls the C# Controller codegen.  ProceduresTemplate1.tt defines and controls the SQL Server codegen. There are 2 ways to execute the codegen: 1) by opening and saving either of the base template files, or 2) by right clicking on the ControllersTemplate1.tt or ProceduresTemplate1.tt file in Solution Explorer and selecting 'Run Custom Tool'.

    RunCustomTool

    Once the code has been generated and the SQL executed, the API project can be built and run.  Hit F5 and it will build the API and launch a browser with SwaggerUI website which will allow testing of each of the endpoints.  The API should return 200's.

    At this point the autogenerated procedures aren't connected to the data model.  That's the next step.  What the codegen does is generate all of the needed C# Controller code and boilerplate SQL.  It structures the project and makes both the Controllers and SQL procedures available in the same API solution as one codebase.  Once the 32 SQL procs are written to fit the framework the whole project should be ready to test out.

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • To make sure building the API (by hitting F5) in the solution file launches SwaggerUI there are 2 required settings in Visual Studio.

    First is to make sure the RelatableCodeGeneratorApi project is set to be the start up project.

    SetStartUpProject

    Second is to use the RelatableCodeGeneratorApi launch settings (the underlying code is located in the Properties folder of the RelatableCodeGeneratorApi project)

    LaunchSettings

    When the project launches it should generate a SwaggerUI website

    SwaggerUI

     

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply