Look out, @donvnielsen… I’m almost done…
Use SQLite \ Manifest.json
{
"id": "{1DA0060B-CE84-4F46-8BB3-BB2C0288A562}",
"name": "Use SQLite",
"nuGetPackageReferences": {
"sqlite-net-pcl": {
"version": "1.7.302-beta",
"paths": [
"lib\\netstandard2.0\\SQLite-net.dll"
]
},
"SQLitePCLRaw.bundle_green": {
"version": "2.0.2",
"paths": [
"lib\\net461\\SQLitePCLRaw.batteries_v2.dll",
"lib\\net461\\SQLitePCLRaw.nativelibrary.dll"
]
},
"SQLitePCLRaw.core": {
"version": "2.0.2",
"paths": [
"lib\\netstandard2.0\\SQLitePCLRaw.core.dll"
]
},
"SQLitePCLRaw.provider.dynamic_cdecl": {
"version": "2.0.2",
"paths": [
"lib\\netstandard2.0\\SQLitePCLRaw.provider.dynamic_cdecl.dll"
]
},
"System.Buffers": {
"version": "4.5.0",
"paths": [
"lib\\netstandard2.0\\System.Buffers.dll"
]
},
"System.Memory": {
"version": "4.5.3",
"paths": [
"lib\\netstandard2.0\\System.Memory.dll"
]
},
"System.Numerics.Vectors": {
"version": "4.5.0",
"paths": [
"lib\\net46\\System.Numerics.Vectors.dll"
]
},
"System.Runtime.CompilerServices.Unsafe": {
"version": "4.7.0",
"paths": [
"lib\\netstandard2.0\\System.Runtime.CompilerServices.Unsafe.dll"
]
}
},
"requiredExtensions": {
"{27F65593-7235-4108-B5D9-F0DE417D8536}": {
"name": "Extensions Menu"
}
}
}
Use SQLite \ Extension.cs
using Quantum;
using Quantum.Client.Windows;
using Quantum.Entities;
using SQLite;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Runtime.CompilerServices;
using System.Runtime.ExceptionServices;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using Telerik.Windows.Controls;
class BooleanIsNegatedValueConverter : IValueConverter
{
public object Convert(object value, Type targetType, object parameter, CultureInfo culture) =>
value is bool boolean ? !boolean : Binding.DoNothing;
public object ConvertBack(object value, Type targetType, object parameter, CultureInfo culture) =>
value is bool boolean ? !boolean : Binding.DoNothing;
}
[Table("Assignments")]
class SqliteAssignment
{
[PrimaryKey] public Guid Id { get; set; }
[Indexed, NotNull] public Guid WorkItemId { get; set; }
[Indexed, NotNull] public Guid PersonId { get; set; }
public DateTimeOffset? Complete { get; set; }
public DateTimeOffset? Due { get; set; }
public TimeSpan? Estimate { get; set; }
}
[Table("ListValueProperties")]
class SqliteListValueProperty
{
[PrimaryKey] public Guid Id { get; set; }
[NotNull] public string Name { get; set; }
public string Notes { get; set; }
}
[Table("ListValuePropertyValues")]
class SqliteListValuePropertyValue
{
[Indexed(Name = "WorkItemAndListValueProperty", Order = 1, Unique = true), NotNull] public Guid WorkItemId { get; set; }
[Indexed(Name = "WorkItemAndListValueProperty", Order = 2, Unique = true), NotNull] public Guid ListValuePropertyId { get; set; }
[NotNull] public Guid ListValueId { get; set; }
}
[Table("ListValues")]
class SqliteListValue
{
[PrimaryKey] public Guid Id { get; set; }
[Indexed, NotNull] public Guid ListValuePropertyId { get; set; }
[NotNull] public string Name { get; set; }
}
[Table("People")]
class SqlitePerson
{
[PrimaryKey] public Guid Id { get; set; }
[NotNull] public string Name { get; set; }
public DateTimeOffset? WentAway { get; set; }
}
[Table("TextProperties")]
class SqliteTextProperty
{
[PrimaryKey] public Guid Id { get; set; }
[NotNull] public string Name { get; set; }
public string Notes { get; set; }
}
[Table("TextPropertyValues")]
class SqliteTextPropertyValue
{
[Indexed(Name = "WorkItemAndTextPropertyId", Order = 1, Unique = true), NotNull] public Guid WorkItemId { get; set; }
[Indexed(Name = "WorkItemAndTextPropertyId", Order = 2, Unique = true), NotNull] public Guid TextPropertyId { get; set; }
[NotNull] public string Text { get; set; }
}
[Table("TimeSlices")]
class SqliteTimeSlice
{
[PrimaryKey] public Guid Id { get; set; }
[Indexed, NotNull] public Guid WorkItemId { get; set; }
[Indexed, NotNull] public Guid PersonId { get; set; }
[NotNull] public DateTimeOffset Start { get; set; }
[NotNull] public DateTimeOffset End { get; set; }
public string Notes { get; set; }
}
[Table("TimingSlices")]
class SqliteTimingSlice
{
[PrimaryKey] public Guid Id { get; set; }
}
[Table("WorkItems")]
class SqliteWorkItem
{
[PrimaryKey] public Guid Id { get; set; }
[NotNull] public string Name { get; set; }
public string Notes { get; set; }
}
class SqliteTransactor : Transactor, INotifyPropertyChanged, INotifyPropertyChanging
{
public SqliteTransactor(ExtensionGlobals extension, EngineStartingEventArgs engineStartingEventArgs)
{
this.extension = extension;
this.engineStartingEventArgs = engineStartingEventArgs;
}
ChangeSet changesLoadedFromSql;
SQLiteAsyncConnection conn = null;
readonly EngineStartingEventArgs engineStartingEventArgs;
readonly ExtensionGlobals extension;
bool isConnectedToSqlite;
public bool IsConnectedToSqlite
{
get => isConnectedToSqlite;
private set => SetBackedProperty(ref isConnectedToSqlite, in value);
}
public event PropertyChangedEventHandler PropertyChanged;
public event PropertyChangingEventHandler PropertyChanging;
async Task ApplyChangesToSqliteAsync(ChangeSet changes)
{
// TODO
}
public async Task ConnectToSqliteAsync(string filePath)
{
if (conn is SqlConnection)
throw new InvalidOperationException();
try
{
conn = new SQLiteAsyncConnection(new SQLiteConnectionString(filePath));
await conn.CreateTableAsync<SqliteAssignment>();
await conn.CreateTableAsync<SqliteListValueProperty>();
await conn.CreateTableAsync<SqliteListValuePropertyValue>();
await conn.CreateTableAsync<SqliteListValue>();
await conn.CreateTableAsync<SqlitePerson>();
await conn.CreateTableAsync<SqliteTextProperty>();
await conn.CreateTableAsync<SqliteTextPropertyValue>();
await conn.CreateTableAsync<SqliteTimeSlice>();
await conn.CreateTableAsync<SqliteTimingSlice>();
await conn.CreateTableAsync<SqliteWorkItem>();
await ApplyChangesToSqliteAsync((await SnapshotAsync()).ToInitializationChangeSet());
extension.DatabaseStorage.Set("FilePath", filePath);
IsConnectedToSqlite = true;
}
catch (Exception ex)
{
conn?.Dispose();
conn = null;
ExceptionDispatchInfo.Capture(ex).Throw();
throw;
}
}
public async Task DisconnectFromSqliteAsync()
{
if (conn is null)
throw new InvalidOperationException();
await conn.CloseAsync();
conn = null;
extension.DatabaseStorage.Set("FilePath", null);
IsConnectedToSqlite = false;
}
public T GetDatabaseStorageValue<T>(string key) => extension.DatabaseStorage is null ? engineStartingEventArgs.ReadOnlyDatabaseStorage.Get<T>(key) : extension.DatabaseStorage.Get<T>(key);
public async Task LoadDataFromSqlAsync()
{
var attributes = new Dictionary<Guid, Quantum.Entities.Attribute>();
var attributeValues = new Dictionary<AttributeObjectCompositeKey, object>();
var enumerationValues = new Dictionary<Guid, AttributeCorrelation<EnumerationValue>>();
var interests = new Dictionary<Guid, ItemPersonCorrelation<Interest>>();
var items = new Dictionary<Guid, Item>();
var people = new Dictionary<Guid, Person>();
var periods = new Dictionary<Guid, ItemPersonCorrelation<Period>>();
var periodsTiming = new List<Guid>();
// TODO
changesLoadedFromSql = (await SnapshotAsync()).Difference(new Quantum.Entities.Frame(attributes, attributeValues, enumerationValues, interests, items, people, periods, periodsTiming));
await ApplyChangesAsync(changesLoadedFromSql);
changesLoadedFromSql = null;
}
protected override async Task OnConnectedAsync()
{
await base.OnConnectedAsync();
if (GetDatabaseStorageValue<string>("FilePath") is string filePath)
{
try
{
conn = new SQLiteAsyncConnection(new SQLiteConnectionString(filePath));
await LoadDataFromSqlAsync();
IsConnectedToSqlite = true;
}
catch (Exception ex)
{
if (conn is SQLiteAsyncConnection liveConn)
await liveConn.CloseAsync();
conn = null;
extension.Error(ex);
}
}
}
protected virtual void OnPropertyChanged(PropertyChangedEventArgs e)
{
if (e is null)
throw new ArgumentNullException(nameof(e));
PropertyChanged?.Invoke(this, e);
}
protected void OnPropertyChanged([CallerMemberName] string propertyName = null)
{
if (propertyName is null)
throw new ArgumentNullException(nameof(propertyName));
OnPropertyChanged(new PropertyChangedEventArgs(propertyName));
}
protected virtual void OnPropertyChanging(PropertyChangingEventArgs e)
{
if (e is null)
throw new ArgumentNullException(nameof(e));
PropertyChanging?.Invoke(this, e);
}
protected void OnPropertyChanging([CallerMemberName] string propertyName = null)
{
if (propertyName is null)
throw new ArgumentNullException(nameof(propertyName));
OnPropertyChanging(new PropertyChangingEventArgs(propertyName));
}
public async Task PrepareForDatabaseDismountAsync()
{
if (conn is SQLiteAsyncConnection liveConn)
{
await liveConn.CloseAsync();
conn = null;
await ApplyChangesAsync((await SnapshotAsync()).Difference(Quantum.Entities.Frame.Empty));
IsConnectedToSqlite = false;
}
}
protected override async Task<ChangeSet> ProcessAppliedChangesAsync(IndexedFrame originalState, ChangeSet changesApplied, ChangeSet inScopeChangesApplied)
{
if (!ReferenceEquals(changesApplied, changesLoadedFromSql) && conn is SQLiteAsyncConnection)
{
try
{
await ApplyChangesToSqliteAsync(inScopeChangesApplied);
}
catch (Exception ex)
{
extension.Error(ex);
ExceptionDispatchInfo.Capture(ex).Throw();
throw;
}
}
return await base.ProcessAppliedChangesAsync(originalState, changesApplied, inScopeChangesApplied);
}
protected bool SetBackedProperty<TValue>(ref TValue backingField, TValue value, [CallerMemberName] string propertyName = null)
{
if (!EqualityComparer<TValue>.Default.Equals(backingField, value))
{
OnPropertyChanging(propertyName);
backingField = value;
OnPropertyChanged(propertyName);
return true;
}
return false;
}
protected bool SetBackedProperty<TValue>(ref TValue backingField, in TValue value, [CallerMemberName] string propertyName = null)
{
if (!EqualityComparer<TValue>.Default.Equals(backingField, value))
{
OnPropertyChanging(propertyName);
backingField = value;
OnPropertyChanged(propertyName);
return true;
}
return false;
}
}
SqliteTransactor currentSqliteTransactor;
RadMenuItem extensionMenuItem;
var extensionsMenuExtensionId = Guid.Parse("{27F65593-7235-4108-B5D9-F0DE417D8536}");
void AboutUseSqliteClick(object sender, RoutedEventArgs e)
{
MessageDialog.Present(Window.GetWindow((DependencyObject)sender), "This extension demonstrates fully interacting with the lifecycle of changes to the user's data by storing it in a completely different place just for giggles.", "About Use SQLite", MessageBoxImage.Information);
}
void DatabaseDismountingHandler(object sender, EventArgs e)
{
currentSqliteTransactor?.PrepareForDatabaseDismountAsync().Wait();
}
Task DisconnectedAsyncHandler(object sender, DisconnectedEventArgs e)
{
Extension.OnUiThreadAsync(() => extensionMenuItem.DataContext = null);
currentSqliteTransactor = null;
return Task.CompletedTask;
}
void EngineStartingHandler(object sender, EngineStartingEventArgs e)
{
currentSqliteTransactor = new SqliteTransactor(Extension, e);
currentSqliteTransactor.DisconnectedAsync += DisconnectedAsyncHandler;
Extension.OnUiThreadAsync(() => extensionMenuItem.DataContext = currentSqliteTransactor);
e.AddTransactor(currentSqliteTransactor);
}
await Extension.OnUiThreadAsync(() =>
{
extensionMenuItem = new RadMenuItem { Header = "Use SQLite" };
var connectToSqliteMenuItem = new RadMenuItem { Header = "Connect to SQLite" };
connectToSqliteMenuItem.SetBinding(RadMenuItem.IsEnabledProperty, new Binding("IsConnectedToSqlite") { Converter = new BooleanIsNegatedValueConverter() });
connectToSqliteMenuItem.Click += (sender, e) =>
{
// TODO
};
extensionMenuItem.Items.Add(connectToSqliteMenuItem);
var disconnectToSqliteMenuItem = new RadMenuItem { Header = "Disconnect from SQLite" };
disconnectToSqliteMenuItem.SetBinding(RadMenuItem.IsEnabledProperty, new Binding("IsConnectedToSqlite"));
disconnectToSqliteMenuItem.Click += (sender, e) => currentSqliteTransactor.DisconnectFromSqliteAsync();
extensionMenuItem.Items.Add(disconnectToSqliteMenuItem);
extensionMenuItem.Items.Add(new RadMenuItem { IsSeparator = true });
var reloadDataFromSqliteMenuItem = new RadMenuItem { Header = "Reload Data from SQLite" };
reloadDataFromSqliteMenuItem.SetBinding(RadMenuItem.IsEnabledProperty, new Binding("IsConnectedToSqlite"));
reloadDataFromSqliteMenuItem.Click += (sender, e) => currentSqliteTransactor.LoadDataFromSqlAsync();
extensionMenuItem.Items.Add(reloadDataFromSqliteMenuItem);
extensionMenuItem.Items.Add(new RadMenuItem { IsSeparator = true });
var aboutUseSqliteMenuItem = new RadMenuItem { Header = "About Use SQLite" };
aboutUseSqliteMenuItem.Click += AboutUseSqliteClick;
extensionMenuItem.Items.Add(aboutUseSqliteMenuItem);
});
Extension.EngineStarting += EngineStartingHandler;
Extension.App.DatabaseDismounting += DatabaseDismountingHandler;
Extension.PostMessage(extensionsMenuExtensionId, extensionMenuItem);
