public SubCss GetStylesForSub(Guid subId)
{
return(_conn.Perform(conn =>
{
return conn.Single <SubCss>(x => x.SubId == subId);
}));
}
public void VoteForPost(Guid postId, Guid userId, string ipAddress, VoteType voteType, DateTime dateCasted)
{
_conn.Perform(conn =>
{
var vote = conn.Single <Vote>(x => x.UserId == userId && x.PostId == postId);
if (vote == null)
{
conn.Insert(new Vote
{
Id = Guid.NewGuid(),
CreatedAt = TimeHelper.CurrentTime(),
UserId = userId,
PostId = postId,
VoteType = voteType,
DateCasted = dateCasted,
IpAddress = ipAddress
});
}
else
{
if (vote.VoteType != voteType)
{
conn.Update <Vote>(new { Type = (int)voteType }, x => x.Id == vote.Id);
}
}
});
}
public void Add(Token entity)
{
if (entity == null)
{
throw new ArgumentNullException("TokenRepository.entity");
}
_provider.Perform(con => con.Save(entity, true));
}
public List <Moderator> GetAllModsForSub(Guid subId)
{
return(_conn.Perform(conn =>
{
return conn.Select(conn.From <Moderator>().Where(x => x.SubId == subId).OrderBy(x => x.AddedOn));
}));
}
public override void Execute(IDbConnectionProvider conn)
{
conn.Perform(x =>
{
x.Execute(@"
CREATE TABLE messages
(
id uuid NOT NULL,
date_created timestamp with time zone NOT NULL,
type integer,
parent_id uuid,
first_message uuid,
deleted boolean,
author_id uuid NOT NULL,
author_ip text,
is_new boolean,
to_user uuid,
to_sub uuid,
from_sub uuid,
subject text,
body text,
body_formatted text,
CONSTRAINT messages_pkey PRIMARY KEY (id)
);");
x.Execute(@"
CREATE INDEX messages_datecreated_index ON messages(date_created);
");
});
}
public override void Execute(IDbConnectionProvider conn)
{
conn.Perform(x =>
{
x.Execute("ALTER TABLE posts ADD COLUMN number_of_comments integer NOT NULL DEFAULT 0;");
});
}
public override void Execute(IDbConnectionProvider conn)
{
conn.Perform(x =>
{
x.Execute("ALTER TABLE users ADD COLUMN show_nsfw boolean NOT NULL default FALSE;");
});
}
public override void Execute(IDbConnectionProvider conn)
{
conn.Perform(x =>
{
x.Execute(@"
CREATE TABLE messages
(
id uuid NOT NULL,
date_created timestamp with time zone NOT NULL,
type integer,
parent_id uuid,
first_message uuid,
deleted boolean,
author_id uuid NOT NULL,
author_ip text,
is_new boolean,
to_user uuid,
to_sub uuid,
from_sub uuid,
subject text,
body text,
body_formatted text,
CONSTRAINT messages_pkey PRIMARY KEY (id)
);");
x.Execute(@"
CREATE INDEX messages_datecreated_index ON messages(date_created);
");
});
}
public override void Execute(IDbConnectionProvider conn)
{
conn.Perform(x =>
{
x.Execute("ALTER TABLE posts ADD COLUMN deleted boolean NOT NULL default false;");
});
}
public override void Execute(IDbConnectionProvider conn)
{
conn.Perform(x =>
{
x.Execute("ALTER TABLE posts ADD COLUMN number_of_reports integer NOT NULL DEFAULT 0;");
x.Execute("ALTER TABLE comments ADD COLUMN number_of_reports integer NOT NULL DEFAULT 0;");
x.Execute("ALTER TABLE posts ADD COLUMN ignore_reports boolean NOT NULL DEFAULT false;");
x.Execute("ALTER TABLE comments ADD COLUMN ignore_reports boolean NOT NULL DEFAULT false;");
x.Execute(@"
CREATE TABLE reported_comments
(
id uuid NOT NULL,
created_date timestamp with time zone NOT NULL,
reported_by uuid NOT NULL,
reason text,
comment uuid,
CONSTRAINT reported_comments_pkey PRIMARY KEY (id)
);");
x.Execute(@"
CREATE TABLE reported_posts
(
id uuid NOT NULL,
created_date timestamp with time zone NOT NULL,
reported_by uuid NOT NULL,
reason text,
post uuid,
CONSTRAINT reported_posts_pkey PRIMARY KEY (id)
);");
});
}
public override void Execute(IDbConnectionProvider conn)
{
conn.Perform(x =>
{
x.Execute("ALTER TABLE users ADD COLUMN is_admin boolean NOT NULL default false;");
});
}
protected DataTestBase()
: base(new Skimur.App.Registrar(),
new Skimur.App.Handlers.Registrar(),
new Skimur.Markdown.Registrar(),
new Skimur.Scraper.Registrar())
{
Monitor.Enter(_sync);
_conn = _serviceProvider.GetService <IDbConnectionProvider>();
// recreate the entire postgres database
_conn.Perform(conn =>
{
conn.ExecuteSql("drop schema public cascade;create schema public;");
});
// recreate the entire cassandra keyspace
using (var cassandraCluster = Cluster.Builder()
.AddContactPoint(_serviceProvider.GetService <ICassandraConnectionStringProvider>().ConnectionString)
.Build())
{
using (var cassandraConnection = cassandraCluster.Connect())
{
cassandraConnection.DeleteKeyspaceIfExists("skimur");
cassandraConnection.CreateKeyspace("skimur");
}
}
Cassandra.Migrations.Migrations.Run(_serviceProvider);
Postgres.Migrations.Migrations.Run(_serviceProvider);
}
public override void Execute(IDbConnectionProvider conn)
{
conn.Perform(x =>
{
x.Execute("ALTER TABLE posts ADD COLUMN content_formatted text NULL;");
});
}
protected DataTestBase()
: base(new Skimur.App.Registrar(),
new Skimur.App.Handlers.Registrar(),
new Skimur.Markdown.Registrar(),
new Skimur.Scraper.Registrar())
{
Monitor.Enter(_sync);
_conn = _serviceProvider.GetService<IDbConnectionProvider>();
// recreate the entire postgres database
_conn.Perform(conn =>
{
conn.ExecuteSql("drop schema public cascade;create schema public;");
});
// recreate the entire cassandra keyspace
using (var cassandraCluster = Cluster.Builder()
.AddContactPoint(_serviceProvider.GetService<ICassandraConnectionStringProvider>().ConnectionString)
.Build())
{
using (var cassandraConnection = cassandraCluster.Connect())
{
cassandraConnection.DeleteKeyspaceIfExists("skimur");
cassandraConnection.CreateKeyspace("skimur");
}
}
Cassandra.Migrations.Migrations.Run(_serviceProvider);
Postgres.Migrations.Migrations.Run(_serviceProvider);
}
public override void Execute(IDbConnectionProvider conn)
{
conn.Perform(x =>
{
x.Execute("ALTER TABLE posts ADD COLUMN media json NULL;");
});
}
public override void Execute(IDbConnectionProvider conn)
{
conn.Perform(x =>
{
x.Execute("ALTER TABLE subs ADD COLUMN created_by uuid NULL;");
});
}
public override void Execute(IDbConnectionProvider conn)
{
conn.Perform(x =>
{
x.Execute("ALTER TABLE posts ADD COLUMN sticky boolean NOT NULL DEFAULT false;");
});
}
public override void Execute(IDbConnectionProvider conn)
{
conn.Perform(x =>
{
x.Execute("ALTER TABLE posts ADD COLUMN number_of_comments integer NOT NULL DEFAULT 0;");
});
}
public override void Execute(IDbConnectionProvider conn)
{
conn.Perform(x =>
{
x.Execute("ALTER TABLE users ADD COLUMN is_admin boolean NOT NULL default false;");
});
}
public override void Execute(IDbConnectionProvider conn)
{
conn.Perform(x =>
{
x.Execute("ALTER TABLE posts ADD COLUMN number_of_reports integer NOT NULL DEFAULT 0;");
x.Execute("ALTER TABLE comments ADD COLUMN number_of_reports integer NOT NULL DEFAULT 0;");
x.Execute("ALTER TABLE posts ADD COLUMN ignore_reports boolean NOT NULL DEFAULT false;");
x.Execute("ALTER TABLE comments ADD COLUMN ignore_reports boolean NOT NULL DEFAULT false;");
x.Execute(@"
CREATE TABLE reported_comments
(
id uuid NOT NULL,
created_at timestamp without time zone NOT NULL,
reported_by uuid NOT NULL,
reason text,
comment uuid,
CONSTRAINT reported_comments_pkey PRIMARY KEY (id)
);");
x.Execute(@"
CREATE TABLE reported_posts
(
id uuid NOT NULL,
created_date timestamp with time zone NOT NULL,
reported_by uuid NOT NULL,
reason text,
post uuid,
CONSTRAINT reported_posts_pkey PRIMARY KEY (id)
);");
});
}
public int CurrentVersion(MigrationType type)
{
var dbVersion = _conn.Perform(conn => conn.Select(conn.From <DatabaseVersion>()
.Where(x => x.Type == type)
.OrderByDescending(x => x.Version)
.Take(1)))
.FirstOrDefault();
if (dbVersion == null)
{
_logger.Info("No entries in database version table. Defaulting version value to 0.");
return(0);
}
return(dbVersion.Version);
}
public override void Execute(IDbConnectionProvider conn)
{
conn.Perform(x =>
{
x.Execute("ALTER TABLE users ADD COLUMN ip text NULL;");
});
}
public override void Execute(IDbConnectionProvider conn)
{
conn.Perform(x =>
{
x.Execute("ALTER TABLE posts ADD COLUMN sticky boolean NOT NULL DEFAULT false;");
});
}
public override void Execute(IDbConnectionProvider conn)
{
conn.Perform(x =>
{
x.Execute("ALTER TABLE subs ADD COLUMN created_by uuid NULL;");
});
}
public override void Execute(IDbConnectionProvider conn)
{
conn.Perform(x =>
{
x.Execute("ALTER TABLE posts ADD COLUMN thumb text NULL;");
});
}
public Versioner(IDbConnectionProvider connectionProvider, ILogger<Version> logger)
{
_conn = connectionProvider;
_logger = logger;
_logger.Debug("Create mapper and table instances");
_conn.Perform(conn => conn.CreateTableIfNotExists<DatabaseVersion>());
}
public override void Execute(IDbConnectionProvider conn)
{
conn.Perform(x =>
{
CreateFunctions(x);
CreateTables(x);
});
}
public override void Execute(IDbConnectionProvider conn)
{
conn.Perform(x =>
{
x.Execute("ALTER TABLE posts ADD COLUMN nsfw boolean NOT NULL default false;");
x.Execute("ALTER TABLE subs ADD COLUMN nsfw boolean NOT NULL default false;");
});
}
public override void Execute(IDbConnectionProvider conn)
{
conn.Perform(x =>
{
x.Execute("ALTER TABLE messages ADD COLUMN post uuid NULL;");
x.Execute("ALTER TABLE messages ADD COLUMN comment uuid NULL;");
});
}
public override void Execute(IDbConnectionProvider conn)
{
conn.Perform(x =>
{
CreateFunctions(x);
CreateTables(x);
});
}
public override void Execute(IDbConnectionProvider conn)
{
conn.Perform(x =>
{
x.Execute("ALTER TABLE subs ADD COLUMN in_all boolean NOT NULL default TRUE;");
x.Execute("ALTER TABLE posts ADD COLUMN in_all boolean NOT NULL default TRUE;");
});
}
public override void Execute(IDbConnectionProvider conn)
{
conn.Perform(x =>
{
x.Execute("ALTER TABLE messages ADD COLUMN post uuid NULL;");
x.Execute("ALTER TABLE messages ADD COLUMN comment uuid NULL;");
});
}
public override void Execute(IDbConnectionProvider conn)
{
conn.Perform(x =>
{
x.Execute("ALTER TABLE subs ADD COLUMN sidebar_text_formatted text NULL;");
x.Execute("ALTER TABLE subs ADD COLUMN submission_text text NULL;");
x.Execute("ALTER TABLE subs ADD COLUMN submission_text_formatted text NULL;");
});
}
public override void Execute(IDbConnectionProvider conn)
{
conn.Perform(x =>
{
x.Execute("ALTER TABLE subs ADD COLUMN sidebar_text_formatted text NULL;");
x.Execute("ALTER TABLE subs ADD COLUMN submission_text text NULL;");
x.Execute("ALTER TABLE subs ADD COLUMN submission_text_formatted text NULL;");
});
}
public override void Execute(IDbConnectionProvider conn)
{
conn.Perform(x =>
{
x.Execute("ALTER TABLE sub_admins RENAME TO moderators");
// "1" is "All" permission
x.Execute("ALTER TABLE moderators ADD COLUMN permissions integer DEFAULT 1");
});
}
public override void Execute(IDbConnectionProvider conn)
{
conn.Perform(x =>
{
x.Execute("ALTER TABLE sub_admins RENAME TO moderators");
// "1" is "All" permission
x.Execute("ALTER TABLE moderators ADD COLUMN permissions integer DEFAULT 1");
});
}
public void Add(Room entity)
{
_provider.Perform(con => {
using (var tran = con.OpenTransaction())
{
con.Save(entity);
SaveRacks(con, entity.Racks);
tran.Commit();
}
});
}
public override void Execute(IDbConnectionProvider conn)
{
conn.Perform(x =>
{
x.Execute("ALTER TABLE posts ADD COLUMN verdict integer NOT NULL DEFAULT 0;");
x.Execute("ALTER TABLE posts ADD COLUMN approved_by uuid NULL;");
x.Execute("ALTER TABLE posts ADD COLUMN removed_by uuid NULL;");
x.Execute("ALTER TABLE posts ADD COLUMN verdict_message text NULL;");
});
}
public override void Execute(IDbConnectionProvider conn)
{
conn.Perform(x =>
{
x.Execute("ALTER TABLE posts ADD COLUMN verdict integer NOT NULL DEFAULT 0;");
x.Execute("ALTER TABLE posts ADD COLUMN approved_by uuid NULL;");
x.Execute("ALTER TABLE posts ADD COLUMN removed_by uuid NULL;");
x.Execute("ALTER TABLE posts ADD COLUMN verdict_message text NULL;");
});
}
public void AddInvite(Guid userId, Guid subId, Guid?invitedBy, ModeratorPermissions permissions)
{
_conn.Perform(conn =>
{
if (conn.Count <ModeratorInvite>(x => x.UserId == userId && x.SubId == subId) == 0)
{
conn.Insert(new ModeratorInvite
{
Id = GuidUtil.NewSequentialId(),
UserId = userId,
SubId = subId,
InvitedBy = invitedBy,
InvitedOn = Common.CurrentTime(),
Permissions = permissions
});
}
// update permissions if invite already present?
});
}
protected override void Setup()
{
base.Setup();
Cassandra.Migrations.Migrations.Run(_serviceProvider);
Postgres.Migrations.Migrations.Run(_serviceProvider);
_conn = _serviceProvider.GetService<IDbConnectionProvider>();
_conn.Perform(conn =>
{
conn.ExecuteProcedure(new ClearDatabaseFunction());
conn.ExecuteProcedure(new EnsureDefaultUserFunction());
});
}
protected override void Setup()
{
base.Setup();
Infrastructure.Cassandra.Migrations.Migrations.Run(_container);
Infrastructure.Postgres.Migrations.Migrations.Run(_container);
_conn = _container.GetInstance<IDbConnectionProvider>();
_conn.Perform(conn =>
{
conn.ExecuteProcedure(new ClearDatabaseFunction());
conn.ExecuteProcedure(new EnsureDefaultUserFunction());
});
}
protected override void Setup()
{
base.Setup();
Cassandra.Migrations.Migrations.Run(_serviceProvider);
Postgres.Migrations.Migrations.Run(_serviceProvider);
_conn = _serviceProvider.GetService <IDbConnectionProvider>();
_conn.Perform(conn =>
{
conn.ExecuteProcedure(new ClearDatabaseFunction());
conn.ExecuteProcedure(new EnsureDefaultUserFunction());
});
}
public override void Execute(IDbConnectionProvider conn)
{
conn.Perform(x =>
{
x.Execute(@"
CREATE TABLE moderator_invites
(
id uuid NOT NULL,
user_id uuid NOT NULL,
sub_id uuid NOT NULL,
invited_by uuid NULL,
invited_on timestamp without time zone NOT NULL,
permissions integer NOT NULL,
CONSTRAINT moderator_invites_pkey PRIMARY KEY (id)
);");
});
}
public override void Execute(IDbConnectionProvider conn)
{
conn.Perform(x =>
{
x.Execute(@"
CREATE TABLE moderator_invites
(
id uuid NOT NULL,
user_id uuid NOT NULL,
sub_id uuid NOT NULL,
invited_by uuid NULL,
invited_on timestamp without time zone NOT NULL,
permissions integer NOT NULL,
CONSTRAINT moderator_invites_pkey PRIMARY KEY (id)
);");
});
}
public Versioner(IDbConnectionProvider connectionProvider, ILogger<Version> logger)
{
_conn = connectionProvider;
_logger = logger;
_logger.Debug("Create mapper and table instances");
_conn.Perform(conn =>
{
conn.ExecuteSql(@"CREATE TABLE IF NOT EXISTS database_version
(
type text NOT NULL,
version integer NOT NULL,
timestamp bigint NOT NULL,
description text
)");
});
}
public Versioner(IDbConnectionProvider connectionProvider, ILogger <Version> logger)
{
_conn = connectionProvider;
_logger = logger;
_logger.Debug("Create mapper and table instances");
_conn.Perform(conn =>
{
conn.ExecuteSql(@"CREATE TABLE IF NOT EXISTS database_version
(
type text NOT NULL,
version integer NOT NULL,
timestamp bigint NOT NULL,
description text
)");
});
}
public Dictionary <Guid, double> GetCommentTreeSorter(Guid postId, CommentSortBy sortBy)
{
// todo: this should be cached and updated periodically
return(_conn.Perform(conn =>
{
var query = conn.From <Comment>().Where(x => x.PostId == postId);
switch (sortBy)
{
case CommentSortBy.Best:
query.OrderBy(x => x.SortConfidence);
break;
case CommentSortBy.Top:
query.OrderByExpression = "ORDER BY (score(up_vote_count, vote_down_count), created_at)";
break;
case CommentSortBy.New:
query.OrderBy(x => x.CreatedAt);
break;
case CommentSortBy.Controversial:
query.OrderByExpression = "ORDER BY (controversy(vote_up_count, vote_down_count), created_at)";
break;
case CommentSortBy.Old:
query.OrderByDescending(x => x.CreatedAt);
break;
case CommentSortBy.Qa:
query.OrderBy(x => x.SortQa);
break;
default:
throw new Exception("Unknown sort.");
}
query.SelectExpression = "SELECT \"id\"";
var commentsSorted = conn.Select(query).Select(x => x.Id).ToList();
return commentsSorted.ToDictionary(x => x, x => (double)commentsSorted.IndexOf(x));
}));
}
public void AddModToSub(Guid userId, Guid subId, ModeratorPermissions permissions, Guid?addedBy = null)
{
_conn.Perform(conn =>
{
if (conn.Count <Moderator>(x => x.UserId == userId && x.SubId == subId) > 0)
{
return;
}
conn.Insert(new Moderator
{
Id = Guid.NewGuid(),
UserId = userId,
SubId = subId,
AddedOn = TimeHelper.CurrentTime(),
AddedBy = addedBy,
Permissions = permissions
});
});
}
public override void Execute(IDbConnectionProvider conn)
{
conn.Perform(x =>
{
x.Execute("DROP INDEX IF EXISTS comments_score_index;");
x.Execute("DROP INDEX IF EXISTS comments_controversy_index;");
x.Execute("DROP INDEX IF EXISTS posts_hot_index;");
x.Execute("DROP INDEX IF EXISTS posts_score_index;");
x.Execute("DROP INDEX IF EXISTS posts_controversy_index;");
x.Execute("DROP INDEX IF EXISTS messages_datecreated_index;");
x.Execute(@"
create or replace function hot(ups integer, downs integer, date timestamp without time zone) returns numeric as $$
select round(cast(log(greatest(abs($1 - $2), 1)) * sign($1 - $2) + (date_part('epoch', $3) - 1134028003) / 45000.0 as numeric), 7)
$$ language sql immutable;");
x.Execute("ALTER TABLE comments ALTER COLUMN date_created TYPE timestamp without time zone;");
x.Execute("ALTER TABLE comments ALTER COLUMN date_edited TYPE timestamp without time zone;");
x.Execute("ALTER TABLE posts ALTER COLUMN date_created TYPE timestamp without time zone;");
x.Execute("ALTER TABLE posts ALTER COLUMN last_edit_date TYPE timestamp without time zone;");
x.Execute("ALTER TABLE messages ALTER COLUMN date_created TYPE timestamp without time zone;");
x.Execute("CREATE INDEX comments_score_index ON comments(hot(vote_up_count, vote_down_count, date_created), date_created);");
x.Execute("CREATE INDEX comments_controversy_index ON comments(controversy(vote_up_count, vote_down_count), date_created);");
x.Execute("CREATE INDEX posts_hot_index ON posts(hot(vote_up_count, vote_down_count, date_created), date_created);");
x.Execute("CREATE INDEX posts_score_index ON posts(score(vote_up_count, vote_down_count), date_created);");
x.Execute("CREATE INDEX posts_controversy_index ON posts(controversy(vote_up_count, vote_down_count), date_created);");
x.Execute("CREATE INDEX messages_datecreated_index ON messages(date_created);");
x.Execute("ALTER TABLE users ALTER COLUMN created_date TYPE timestamp without time zone;");
x.Execute("ALTER TABLE users ALTER COLUMN lockout_end_date TYPE timestamp without time zone;");
x.Execute("ALTER TABLE subs ALTER COLUMN created_date TYPE timestamp without time zone;");
x.Execute("ALTER TABLE moderators ALTER COLUMN added_on TYPE timestamp without time zone;");
x.Execute("ALTER TABLE votes ALTER COLUMN date_created TYPE timestamp without time zone;");
x.Execute("ALTER TABLE votes ALTER COLUMN date_casted TYPE timestamp without time zone;");
x.Execute("ALTER TABLE sub_user_bans ALTER COLUMN banned_until TYPE timestamp without time zone;");
x.Execute("ALTER TABLE sub_user_bans ALTER COLUMN date_banned TYPE timestamp without time zone;");
x.Execute("ALTER TABLE reported_comments ALTER COLUMN created_date TYPE timestamp without time zone;");
x.Execute("ALTER TABLE reported_posts ALTER COLUMN created_date TYPE timestamp without time zone;");
});
}
public override void Execute(IDbConnectionProvider conn)
{
conn.Perform(x =>
{
x.Execute(@"
CREATE TABLE sub_css
(
id uuid NOT NULL,
sub_id uuid NOT NULL,
type int NOT NULL,
embedded text NULL,
external_css text NULL,
github_css_project_name text NULL,
github_css_project_tag text NULL,
github_less_project_name text NULL,
github_less_project_tag text NULL,
CONSTRAINT sub_css_pkey PRIMARY KEY (id)
);");
x.Execute("ALTER TABLE users ADD COLUMN styles boolean NOT NULL default TRUE;");
});
}
protected override void Setup()
{
base.Setup();
_conn = _container.GetInstance<IDbConnectionProvider>();
_conn.Perform(conn => conn.ExecuteProcedure(new ClearDatabaseFunction()));
}