public JsonSyncingFeed GetJsonSyncingFeed(long feedId) { var row = SyncingFeeds.Query("WHERE FeedId = " + feedId).FirstOrDefault(); if (row == null) { return(null); } var state = SQLiteJsonConverter.LoadFromJson <JsonSyncingFeed>(row.Json); return(state); }
void UpdateConversation(DbArchiveRecord archiveConversationRef) { JsonConversationReference conversationRef = SQLiteJsonConverter.LoadFromJson <JsonConversationReference>( archiveConversationRef.Json); DbConversation coreConversation = new DbConversation(); coreConversation.LastFetchedUtc = archiveConversationRef.LastFetchedUtc; coreConversation.ConversationId = conversationRef.Id; coreConversation.ParticipantUserIds.AssignFrom(conversationRef.Participants.Select(x => x.Id)); Conversations.InsertRecord(coreConversation, SQLiteConflictResolution.Replace); }
public void UpdateJsonSyncingFeed(long groupId, JsonSyncingFeed state) { DbSyncingFeed row = new DbSyncingFeed(); row.FeedId = groupId; // Update the aggregated properties row.LastUpdateUtc = state.LastUpdateUtc; row.LastCheckNewUtc = state.LastCheckNewUtc; row.ReachedEmptyResult = state.ReachedEmptyResult; row.HasSpanGaps = state.HasSpanGaps; row.Json = SQLiteJsonConverter.SaveToJson(state); SyncingFeeds.InsertRecord(row, SQLiteConflictResolution.Replace); }
void UpdateUser(DbArchiveRecord archiveUser) { JsonUserReference userRef = SQLiteJsonConverter.LoadFromJson <JsonUserReference>(archiveUser.Json); DbUser coreUser = new DbUser(); coreUser.LastFetchedUtc = archiveUser.LastFetchedUtc; coreUser.UserId = userRef.Id; coreUser.Alias = userRef.Alias ?? ""; coreUser.Email = userRef.Email ?? ""; coreUser.FullName = userRef.DisplayValue ?? ""; coreUser.JobTitle = userRef.JobTitle ?? ""; coreUser.WebUrl = userRef.Permalink ?? ""; coreUser.MugshotUrl = userRef.MugshotUrl ?? ""; Users.InsertRecord(coreUser, SQLiteConflictResolution.Replace); }
void UpdateGroup(DbArchiveRecord archiveGroupRef) { JsonGroupReference groupRef = SQLiteJsonConverter.LoadFromJson <JsonGroupReference>(archiveGroupRef.Json); bool incompleteRecord = false; DbGroup coreGroup = new DbGroup(); coreGroup.LastFetchedUtc = archiveGroupRef.LastFetchedUtc; coreGroup.GroupId = groupRef.Id; coreGroup.GroupName = groupRef.FullName ?? ""; coreGroup.GroupDescription = groupRef.Description ?? ""; if (!Enum.TryParse <DbGroupPrivacy>(groupRef.Privacy, true, out coreGroup.Privacy)) { if (!string.IsNullOrEmpty(groupRef.Privacy)) { throw new YamsterProtocolException(string.Format("Unsupported group privacy \"{0}\"", coreGroup.Privacy)); } coreGroup.Privacy = DbGroupPrivacy.Unknown; incompleteRecord = true; } coreGroup.WebUrl = groupRef.WebUrl ?? ""; coreGroup.MugshotUrl = groupRef.MugshotUrl ?? ""; // If the record is incomplete, don't overwrite an existing record that might have complete data // TODO: this merging should be more fine-grained Groups.InsertRecord(coreGroup, incompleteRecord ? SQLiteConflictResolution.Ignore : SQLiteConflictResolution.Replace); DbGroupState groupState = new DbGroupState() { GroupId = groupRef.Id }; GroupStates.InsertRecord(groupState, SQLiteConflictResolution.Ignore); }
static JsonMessageEnvelope ConvertArchiveMessageEnvelope(JsonMessageEnvelopeUntyped untypedEnvelope) { string reserializedString = SQLiteJsonConverter.SaveToJson(untypedEnvelope); JsonMessageEnvelope envelope = SQLiteJsonConverter.LoadFromJson <JsonMessageEnvelope>(reserializedString); // Use untypedEnvelope to fill in the RawJson properties for (int i = 0; i < untypedEnvelope.Messages.Length; ++i) { JObject archiveMessage = untypedEnvelope.Messages[i]; string reserializedMessage = SQLiteJsonConverter.SaveToJson(archiveMessage); envelope.Messages[i].RawJson = reserializedMessage; } foreach (var pair in untypedEnvelope.ThreadedExtended) { long threadId = pair.Key; JObject[] archiveMessageList = pair.Value; var messageList = envelope.ThreadedExtended[threadId]; for (int i = 0; i < archiveMessageList.Length; ++i) { JObject archiveMessage = archiveMessageList[i]; string reserializedMessage = SQLiteJsonConverter.SaveToJson(archiveMessage); messageList[i].RawJson = reserializedMessage; } } // Two indexes are needed because YamsterReferenceJsonConverter discards unrecognized items // from envelope.References; we should probably fix that. for (int archiveIndex = 0, index = 0; ;) { if (archiveIndex >= untypedEnvelope.References.Length) { break; } if (index >= envelope.References.Length) { break; } var reference = envelope.References[index]; JObject archiveReference = untypedEnvelope.References[archiveIndex]; long archiveReferenceId = Convert.ToInt64(((JValue)archiveReference["id"]).Value); if (reference.Id == archiveReferenceId) { string reserializedMessage = SQLiteJsonConverter.SaveToJson(archiveReference); envelope.References[index].RawJson = reserializedMessage; ++index; } ++archiveIndex; } #if DEBUG foreach (var reference in envelope.References) { Debug.Assert(reference.RawJson != null); } #endif return(envelope); }
void UpgradeDatabase() { // Validate the database state int coreDbVersion = archiveDb.GetObjectVersion("Core"); bool inactiveArchiveDb = this.archiveDb.IsInactive(); if (coreDbVersion == CurrentCoreDbVersion) { return; } if (coreDbVersion > CurrentCoreDbVersion) { throw new UnsupportedDatabaseVersionException("Core", coreDbVersion, CurrentCoreDbVersion); } if (inactiveArchiveDb) { if (coreDbVersion < MinimumUpgradeableCoreDbVersionWithoutArchiveDb) { // The database cannot be upgraded because the ArchiveDB is missing, // e.g. because the data was imported using CsvDumpLoader throw new UnsupportedDatabaseVersionException("Core", coreDbVersion, CurrentCoreDbVersion); } } if (coreDbVersion < MinimumUpgradeableCoreDbVersion) { RebuildDatabase(coreDbVersion); return; } var args2 = new SQLiteDataContextUpgradeEventArgs("Core", "For a large database, the upgrade may take a while. Please be patient.", coreDbVersion, CurrentCoreDbVersion); OnBeforeUpgrade(args2); if (args2.CancelUpgrade) { throw new DatabaseUpgradeCanceledException("Core"); } var startTime = DateTime.Now; Debug.WriteLine("BEGIN UPGRADE CORE DATABASE"); RebuildableTable alreadyRebuiltTables = RebuildableTable.None; // Upgrade 1004 -> 1005 if (coreDbVersion < 1005) { Debug.Assert(coreDbVersion == 1004); using (var transaction = this.BeginTransaction()) { string sqlTemplate = @" ALTER TABLE [{0}] ADD COLUMN [ChangeNumber] INTEGER NOT NULL DEFAULT -1; -- Ensure ChangeNumber is unique UPDATE [{0}] SET [ChangeNumber] = [RowId]; CREATE UNIQUE INDEX [{0}_Index0] ON [{0}] ([ChangeNumber]); CREATE TRIGGER [{0}_ChangeNumberTrigger0] AFTER INSERT ON [{0}] BEGIN UPDATE [{0}] SET [ChangeNumber] = (SELECT MAX([ChangeNumber]) FROM [{0}] LIMIT 1)+1 WHERE ROWID = NEW.ROWID; END; CREATE TRIGGER [{0}_ChangeNumberTrigger1] AFTER UPDATE ON [{0}] BEGIN UPDATE [{0}] SET [ChangeNumber] = (SELECT MAX([ChangeNumber]) FROM [{0}] LIMIT 1)+1 WHERE ROWID = NEW.ROWID; END; "; foreach (string tableName in new string[] { "GroupStates", "MessageStates" }) { string sql = string.Format(sqlTemplate, tableName); this.Mapper.ExecuteNonQuery(sql); } this.RebuildTablesIfNeeded( RebuildableTable.Groups | RebuildableTable.Messages | RebuildableTable.Users, ref alreadyRebuiltTables ); archiveDb.SetObjectVersion("Core", 1005); transaction.Commit(); coreDbVersion = 1005; } } // Upgrade 1005 -> 1006 if (coreDbVersion < 1006) { Debug.Assert(coreDbVersion == 1005); using (var transaction = this.BeginTransaction()) { this.RebuildTablesIfNeeded( // Added CurrentUserId column RebuildableTable.Properties, ref alreadyRebuiltTables ); // Group name changed in this version InitVirtualGroups(); archiveDb.SetObjectVersion("Core", 1006); transaction.Commit(); coreDbVersion = 1006; } } // Upgrade 1006 -> 1007 if (coreDbVersion < 1007) { using (var transaction = this.BeginTransaction()) { this.RebuildTablesIfNeeded( // Added Conversations table RebuildableTable.Conversations // Added ConversationId column | RebuildableTable.Messages, ref alreadyRebuiltTables ); archiveDb.SetObjectVersion("Core", 1007); transaction.Commit(); coreDbVersion = 1007; } } // Upgrade 1007 -> 1008 if (coreDbVersion < 1008) { using (var transaction = this.BeginTransaction()) { // Added "(All Company)" group InitVirtualGroups(); GroupStates.InsertRecord(new DbGroupState() { GroupId = YamsterGroup.AllCompanyGroupId, ShowInYamster = true, ShouldSync = false, TrackRead = true }); archiveDb.SetObjectVersion("Core", 1008); transaction.Commit(); coreDbVersion = 1008; } } // Upgrade 1008 -> 1009 if (coreDbVersion < 1009) { using (var transaction = this.BeginTransaction()) { this.RebuildTablesIfNeeded( // Replaced DbConversation.ParticipantsJson with ParticipantUserIds RebuildableTable.Conversations // Added LikingUserIds and NotifiedUserIds | RebuildableTable.Messages, ref alreadyRebuiltTables ); archiveDb.SetObjectVersion("Core", 1009); transaction.Commit(); coreDbVersion = 1009; } } // Upgrade 1009 -> 1010 if (coreDbVersion < 1010) { using (var transaction = this.BeginTransaction()) { this.RebuildTablesIfNeeded( // Added CurrentNetworkId, FollowYamsterLastAskedUtc, and FollowYamsterState RebuildableTable.Properties, ref alreadyRebuiltTables ); archiveDb.SetObjectVersion("Core", 1010); transaction.Commit(); coreDbVersion = 1010; } } // Upgrade 1010 -> 1011 if (coreDbVersion < 1011) { using (var transaction = this.BeginTransaction()) { this.RebuildTablesIfNeeded( // Fixed issue where liking users weren't being extracted from the Messages // table into the Users table RebuildableTable.Messages, ref alreadyRebuiltTables ); archiveDb.SetObjectVersion("Core", 1011); transaction.Commit(); coreDbVersion = 1011; } } // Upgrade 1011 -> 1012 if (coreDbVersion < 1012) { using (var transaction = this.BeginTransaction()) { this.RebuildTablesIfNeeded( // Added SyncInbox RebuildableTable.Properties // These tables are new | RebuildableTable.SyncingFeeds | RebuildableTable.SyncingThreads, ref alreadyRebuiltTables ); // This could have been lost if the ArchiveDb was regenerated if (this.Mapper.QueryScalar <long>( "SELECT COUNT(*) FROM sqlite_master WHERE name ='ArchiveSyncState' and type='table'") > 0) { var syncStates = this.Mapper.Query <V1011_DbArchiveSyncState>( "SELECT [GroupId], [Json] FROM [ArchiveSyncState]"); foreach (var syncState in syncStates) { var jsonGroupState = SQLiteJsonConverter.LoadFromJson <V1011_MessagePullerGroupState>(syncState.Json); // Build a DbSyncingFeed record var syncingFeed = new JsonSyncingFeed() { ReachedEmptyResult = jsonGroupState.ReachedEmptyResult, SpanCyclesSinceCheckNew = jsonGroupState.SpanCyclesSinceCheckNew, LastUpdateUtc = jsonGroupState.LastUpdateUtc, LastCheckNewUtc = jsonGroupState.LastCheckNewUtc }; foreach (var span in jsonGroupState.Spans) { syncingFeed.AddSpan(new JsonMessagePullerSpan() { StartTimeUtc = span.StartTimeUtc, StartMessageId = span.StartMessageId, EndMessageId = span.EndMessageId } ); } this.UpdateJsonSyncingFeed(syncState.GroupId, syncingFeed); // Build DbSyncingThread records foreach (var gappedThread in jsonGroupState.GappedThreads) { var syncingThread = new DbSyncingThread() { ThreadId = gappedThread.ThreadId, FeedId = syncState.GroupId, LastPulledMessageId = gappedThread.LastPulledMessageId, StopMessageId = gappedThread.StopMessageId, RetryCount = gappedThread.RetryCount }; this.SyncingThreads.InsertRecord(syncingThread); } } this.Mapper.ExecuteNonQuery("DROP TABLE [ArchiveSyncState]"); } archiveDb.SetObjectVersion("Core", 1012); transaction.Commit(); coreDbVersion = 1012; } } // Upgrade 1012 -> 1013 if (coreDbVersion < 1013) { using (var transaction = this.BeginTransaction()) { this.Mapper.CreateTable(this.ThreadStates); // Make sure we have DbThreadState records for each thread foreach (var result in this.Mapper.Query <DbInt64Result>( "SELECT DISTINCT [ThreadId] AS [Value] FROM [Messages]")) { this.ThreadStates.InsertRecord(new DbThreadState() { ThreadId = result.Value }, SQLiteConflictResolution.Ignore); } archiveDb.SetObjectVersion("Core", 1013); transaction.Commit(); coreDbVersion = 1013; } } // Upgrade 1013 -> 1014 if (coreDbVersion < 1014) { using (var transaction = this.BeginTransaction()) { this.RebuildTablesIfNeeded( // Added DbMessage.MessageIdRepliedTo RebuildableTable.Messages, ref alreadyRebuiltTables ); archiveDb.SetObjectVersion("Core", 1014); transaction.Commit(); coreDbVersion = 1014; } } // Upgrade 1014 -> 1015 if (coreDbVersion < 1015) { using (var transaction = this.BeginTransaction()) { this.RebuildTablesIfNeeded( // Added DbMessage.AttachmentWidth/AttachmentHeight RebuildableTable.Messages, ref alreadyRebuiltTables ); archiveDb.SetObjectVersion("Core", 1015); transaction.Commit(); coreDbVersion = 1015; } } // Upgrade 1015 -> 1016 if (coreDbVersion < 1016) { using (var transaction = this.BeginTransaction()) { this.RebuildTablesIfNeeded( // Added DbUser.Alias RebuildableTable.Users, ref alreadyRebuiltTables ); archiveDb.SetObjectVersion("Core", 1016); transaction.Commit(); coreDbVersion = 1016; } } // Upgrade 1016 -> 1017 if (coreDbVersion < 1017) { using (var transaction = this.BeginTransaction()) { this.RebuildTablesIfNeeded( // Removed FollowYamsterLastAskedUtc and FollowYamsterState RebuildableTable.Properties, ref alreadyRebuiltTables ); archiveDb.SetObjectVersion("Core", 1017); transaction.Commit(); coreDbVersion = 1017; } } // Upgrade 1017 -> 1018 if (coreDbVersion < 1018) { using (var transaction = this.BeginTransaction()) { this.Mapper.ExecuteNonQuery(@"ALTER TABLE [MessageStates] ADD COLUMN [Deleted] INTEGER NOT NULL DEFAULT 0"); this.RebuildTablesIfNeeded( // Added DbMessage.MessageType RebuildableTable.Messages, ref alreadyRebuiltTables ); archiveDb.SetObjectVersion("Core", 1018); transaction.Commit(); coreDbVersion = 1018; } } // Upgrade 1018 -> 1019 if (coreDbVersion < 1019) { using (var transaction = this.BeginTransaction()) { this.RebuildTablesIfNeeded( // Added DbUser.Alias RebuildableTable.Users, ref alreadyRebuiltTables ); archiveDb.SetObjectVersion("Core", 1019); transaction.Commit(); coreDbVersion = 1019; } } if (coreDbVersion != CurrentCoreDbVersion) { // This is a program bug throw new InvalidOperationException("Upgrade failed"); } var totalTime = DateTime.Now - startTime; Debug.WriteLine("END UPGRADE CORE DATABASE: {0} secs processing time", totalTime.TotalSeconds); OnAfterUpgrade(); }
void UpdateMessage(DbArchiveMessageRecord archiveMessage) { JsonMessage message = SQLiteJsonConverter.LoadFromJson <JsonMessage>(archiveMessage.Json); DbMessage coreMessage = new DbMessage(); coreMessage.LastFetchedUtc = archiveMessage.LastFetchedUtc; coreMessage.MessageId = message.Id; coreMessage.GroupId = archiveMessage.GroupId; coreMessage.ThreadId = message.ThreadId; coreMessage.ConversationId = message.ConversationId; coreMessage.CreatedDate = message.Created; coreMessage.SenderUserId = message.SenderId; coreMessage.MessageIdRepliedTo = message.RepliedToId ?? 0; coreMessage.LikingUserIds.AssignFrom(message.Likes.Users.Select(x => x.UserId)); foreach (var likingUser in message.Likes.Users) { // We don't get a proper UserReference for liking users, but we do get // some basic information. Write this to the Users table *only* if there // is not already some real data there. DbUser coreUser = new DbUser(); coreUser.LastFetchedUtc = archiveMessage.LastFetchedUtc; coreUser.UserId = likingUser.UserId; coreUser.FullName = likingUser.FullName ?? ""; coreUser.JobTitle = ""; coreUser.WebUrl = ""; // we could infer this from likingUser.Alias coreUser.MugshotUrl = ""; // Ignore = only write if there isn't already an existing record Users.InsertRecord(coreUser, SQLiteConflictResolution.Ignore); } coreMessage.LikesCount = message.Likes.Count; coreMessage.NotifiedUserIds.AssignFrom(message.NotifiedUserIds ?? new long[0]); coreMessage.Body = message.Body.Plain ?? ""; coreMessage.WebUrl = message.Permalink ?? ""; var firstImageAttachment = message.Attachments.Where(x => x.AttachmentType == "image").FirstOrDefault(); if (firstImageAttachment != null) { coreMessage.AttachmentFilename = firstImageAttachment.Name; coreMessage.AttachmentWebUrl = firstImageAttachment.WebUrl; coreMessage.AttachmentScaledUrlTemplate = firstImageAttachment.ScaledUrlTemplate; coreMessage.AttachmentWidth = firstImageAttachment.Width ?? 0; coreMessage.AttachmentHeight = firstImageAttachment.Height ?? 0; } if (!Enum.TryParse(message.MessageType, true, out coreMessage.MessageType)) { coreMessage.MessageType = DbMessageType.Unknown; } Messages.InsertRecord(coreMessage, SQLiteConflictResolution.Replace); DbMessageState messageState = new DbMessageState() { MessageId = archiveMessage.Id }; MessageStates.InsertRecord(messageState, SQLiteConflictResolution.Ignore); // Ensure that every message has a corresponding DbThreadState for its thread DbThreadState threadState = new DbThreadState() { ThreadId = coreMessage.ThreadId }; ThreadStates.InsertRecord(threadState, SQLiteConflictResolution.Ignore); }