Enhancing Time Slice with User Property

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

I’m always learning something new. Never heard of SqlitePCL before. Interesting.

Sweet Mother of Moses. You are really on top of all the new coding techniques. This will take a while for me to digest.

I’m very glad you expressed an interest in trying to do this, as I ran into an issue here or there during development of the Use SQLite extension which required some enhanced capabilities in the extension system, including but not limited to support for native libraries (such as the SQLite engine) that .NET assemblies can reference using P/Invokes. Sufficed to say, thank you, @donvnielsen . This process you kicked off has made extensions in Grindstone Pro more robust that they otherwise would have been upon release.

Now, go get the preview release and have fun with the awesome power of extensions and NuGet!

So one last question. I went to activate Grindstone Pro–Lifetime. Denied…said it is not supported yet. How do I take advantage of that? I already have an Epiforge account.

The web page download link downloads UseSqlServer

You fixed it. I got it.

Please note this statement.

Sorry, don’t know how that happened. I didn’t change anything. Wires must have gotten crossed somewhere in the internets.

I still can’t switch to Grindstone Pro. What’s the secret sauce?

image.png

While we are testing, only monthly licenses will be issued. But they are free during the testing period, so just tell Grindstone you want a monthly one!

You’ll be able to purchase a lifetime license after Grindstone Pro hits the stable release channel.

We can close this ticket. It is beyond my expectations and I am looking forward to playing with it tonight.

Yikes, didn’t mean to over do it. Let me know if you’d like just a simple exporter instead.

Everything is perfect.

I am having trouble converting the datetimeoffset values, however. When I convert offset integer to unixepoch, I get close to the date but not on it. I’m still trying to figure it all out. (Working with date/time conversions is always so difficult.)

Start
637183254460000000 1990-03-11 19:20:54

The proper time datetime should be “2020-02-26T16:16:10Z”

Ahh, it’s in 100-nanosecond intervals since the invariant culture’s origin. Microsoft calls them ticks.

Uhh, does this help?

Also, you could change the extension. The SQLiteAsyncConnection class constructor has an optional parameter to store dates and times as ticks and its defaulted on. You could pass false to it in Extension.cs and see if you prefer that result. Just disconnect from SQLite before making the change!

Ahh, we are using DateTimeOffsets in the extension as well, so it would be more work than just changing the constructor call. You might want to just take the ticks conversion route. I guess the cool thing is that you can change it however you want. :wink:

What coding environment are you working with in accessing this SQLite database?

I’m good for now. Have a good night.

with Items as (
    select wi.Id WorkItem
         , wi.name Description
         , wi.notes Notes
         , lv.name Task
         , TP.name Property
         , TPV.Text Value
    from WorkItems WI
             left join ListValuePropertyValues tasks on tasks.WorkItemId = WI.Id
             left join ListValues lv on lv.Id = tasks.ListValueId
             left join ListValueProperties LVP on lvp.Id = lv.ListValuePropertyId
             left join TextPropertyValues TPV on TPV.WorkItemId = WI.Id
             left join TextProperties TP on TP.Id = TPV.TextPropertyId
)
select W.*,ts.id
    ,datetime(ts.start/10000000-62136892800, 'unixepoch' ) begin
    ,datetime(ts.End/10000000-62136892800, 'unixepoch' ) end
    ,(ts.start - ts.End)/10000000 - 62136892800
from Items W
join TimeSlices TS on WorkItemId = W.WorkItem

Awesome! Good night to you, as well.