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