15:17
It's related to this thread from sqlservercentral where Steve Jones talks about the plans for post-PASS
Also, in the past I recall discussion where you Solomon mention that the WordPress database is suboptimal and not properly designed. I believe you mentioned there's a fork which is implemented in SQL Server? Anyway, Steve Jones said he would be open to other alternatives which might be interesting
My question here is whether or not this is a coherent good idea or if it seems unworkable. A voice of reason would be greatly appreciated
So it's an idea/plan but I struggle with the story of it and making it flow. Jeff and you are the tops in SQL at explaining things. I feel like I'm pounding sand right now
The fastest backsplain possible: Many years ago a forum-type website based on Drupal was permanently shut down even though a potential buyer offered the owner of the site 6 figure dollars. So former members of the old site got together to build a new forum. I was the database guy. There were 2 .NET guys who wrote a technical spec for a forum API. Based on the spec I created a data model, procedures, and a .NET Framework API application
The .NET guys were supposed to build a UI but flaked out and never did. One left the project by nonresponse early and then the second I ended up de-friending on Facebook because he was a
So as side project that was developed in 2012-sih. Then around 2016-ish my business partner pitched a potential client a content site with a forum.
That client was interested and followed up once or twice but then it was dropped for another priority. We're a consulting company and we different projects. So in anticipation of moving forward we internally created CI/CD DevOps infrastructure for a forum API
The pipeline and DevOps conform to our current API configuration which is GitHub, Azure DevOps, App Service, .NET Core etc.
16:24
One thing that's cool about Drupal and/or the way the forum was implemented on this 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 lead to shenanigans where members could/would change history or you'd have to go backward through the thread to find the unaltered original
17:26
if object_id('dbo.posts') is not null
drop table if exists thr.posts;
create table posts(
p_id bigint identity(2,2) primary key not null,
t_id int not null references threads(t_id),
u_id int not null references users(id),
orig_p_id bigint null references posts(p_id),
reply_p_id bigint null references posts(p_id),
reply_depth_count int not null default 0,
tree_path int not null,
tree_path_bit bit not null default 0,
title nvarchar(256) null,
body nvarchar(4000) not null,
drop table if exists thr.posts;
create table posts(
p_id bigint identity(2,2) primary key not null,
t_id int not null references threads(t_id),
u_id int not null references users(id),
orig_p_id bigint null references posts(p_id),
reply_p_id bigint null references posts(p_id),
reply_depth_count int not null default 0,
tree_path int not null,
tree_path_bit bit not null default 0,
title nvarchar(256) null,
body nvarchar(4000) not null,
That's a table called 'posts' which is designed to persist forum posts. p_id is "post id", t_id is "thread id", u_id is "user id"
Users creates posts in response to threads. Users might reply directly to the thread overall or they might reply to a particular post. To persist this and to be able to quickly generate view models there are tradeoffs that have to be made.
If the user replies to another post (instead of directly to the top level of the thread) then columns: orig_p_id is "original post id", reply_depth_count is "degree of separation", tree_path is "ordinal pathway to traverse replies"
From these column JSON can be serialized and delivered to Kendo UI data source object and as far as I can tell it will render threaded conversations as tree object
To post to the top level of the thread is trivial as it's the root node of a recursion where orig_p_id is NULL.
drop proc api_post_post;
go
create proc api_post_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 posts(t_id, u_id, reply_depth_count, tree_path, title, body, created_dt, edited_dt) values(@t_id, @u_id, 2, 2, @title, @body, sysutcdatetime(), sysutcdatetime());
select @test_p_id=scope_identity();
select @test_msg='Ok';
end try
begin catch
select @test_p_id=0;
select @test_msg=error_message();;
go
create proc api_post_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 posts(t_id, u_id, reply_depth_count, tree_path, title, body, created_dt, edited_dt) values(@t_id, @u_id, 2, 2, @title, @body, sysutcdatetime(), sysutcdatetime());
select @test_p_id=scope_identity();
select @test_msg='Ok';
end try
begin catch
select @test_p_id=0;
select @test_msg=error_message();;
drop proc api_post_reply_post;
go
create proc api_post_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),
go
create proc api_post_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),
« first day (14 days earlier) ← previous day next day → last day (23 days later) »
Transcript for
Mar23
Mar '2125
Mar26
Discussion between SteveC and Solomon…
Imported from a comment discussion on dba.stackexchange.com/qu...
