static UsageDataMessage FetchDataForUpload(SQLiteConnection connection, bool fetchIncompleteSessions)
{
UsageDataMessage message = new UsageDataMessage();
// Retrieve the User ID
using (SQLiteCommand cmd = connection.CreateCommand()) {
cmd.CommandText = "SELECT value FROM Properties WHERE name = 'userID';";
string userID = (string)cmd.ExecuteScalar();
message.UserID = new Guid(userID);
}
// Retrieve the list of sessions
using (SQLiteCommand cmd = connection.CreateCommand()) {
if (fetchIncompleteSessions) {
cmd.CommandText = @"SELECT id, startTime, endTime FROM Sessions;";
} else {
// Fetch all sessions which are either closed or inactive for more than 24 hours
cmd.CommandText = @"SELECT id, startTime, endTime FROM Sessions
WHERE (endTime IS NOT NULL)
OR (ifnull((SELECT max(time) FROM FeatureUses WHERE FeatureUses.session = Sessions.id), Sessions.startTime)
< datetime('now','-1 day'));";
}
using (SQLiteDataReader reader = cmd.ExecuteReader()) {
while (reader.Read()) {
UsageDataSession session = new UsageDataSession();
session.SessionID = reader.GetInt64(0);
session.StartTime = reader.GetDateTime(1);
if (!reader.IsDBNull(2))
session.EndTime = reader.GetDateTime(2);
message.Sessions.Add(session);
}
}
}
string commaSeparatedSessionIDList = GetCommaSeparatedIDList(message.Sessions);
StringInterner stringInterning = new StringInterner();
// Retrieve the environment
using (SQLiteCommand cmd = connection.CreateCommand()) {
cmd.CommandText = "SELECT session, name, value FROM Environment WHERE session IN (" + commaSeparatedSessionIDList + ");";
using (SQLiteDataReader reader = cmd.ExecuteReader()) {
while (reader.Read()) {
long sessionID = reader.GetInt64(0);
UsageDataSession session = message.FindSession(sessionID);
session.EnvironmentProperties.Add(
new UsageDataEnvironmentProperty {
Name = stringInterning.Intern(reader.GetString(1)),
Value = stringInterning.Intern(reader.GetString(2))
});
}
}
}
// Retrieve the feature uses
using (SQLiteCommand cmd = connection.CreateCommand()) {
cmd.CommandText = "SELECT session, time, endTime, feature, activationMethod FROM FeatureUses WHERE session IN (" + commaSeparatedSessionIDList + ");";
using (SQLiteDataReader reader = cmd.ExecuteReader()) {
while (reader.Read()) {
long sessionID = reader.GetInt64(0);
UsageDataSession session = message.FindSession(sessionID);
UsageDataFeatureUse featureUse = new UsageDataFeatureUse();
featureUse.Time = reader.GetDateTime(1);
if (!reader.IsDBNull(2))
featureUse.EndTime = reader.GetDateTime(2);
featureUse.FeatureName = stringInterning.Intern(reader.GetString(3));
if (!reader.IsDBNull(4))
featureUse.ActivationMethod = stringInterning.Intern(reader.GetString(4));
session.FeatureUses.Add(featureUse);
}
}
}
// Retrieve the exceptions
using (SQLiteCommand cmd = connection.CreateCommand()) {
cmd.CommandText = "SELECT session, time, type, stackTrace FROM Exceptions WHERE session IN (" + commaSeparatedSessionIDList + ");";
using (SQLiteDataReader reader = cmd.ExecuteReader()) {
while (reader.Read()) {
long sessionID = reader.GetInt64(0);
UsageDataSession session = message.FindSession(sessionID);
UsageDataException exception = new UsageDataException();
exception.Time = reader.GetDateTime(1);
exception.ExceptionType = stringInterning.Intern(reader.GetString(2));
if (!reader.IsDBNull(3))
exception.StackTrace = stringInterning.Intern(reader.GetString(3));
session.Exceptions.Add(exception);
}
}
}
return message;
}
public ExceptionImport(UsageDataException ude)
: this(ude.ExceptionType, ude.StackTrace, ude.Time)
{
}