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())); }