Use SQLite

Grindstone 4 Extension Gallery
1

This extension allows the user to create a SQLite database and move Grindstone’s data into it, replacing the factory data storage mechanism. It was originally created to demonstrate how to interact with user data lifecycle events, but may actually be used by users that would prefer to keep their Grindstone data in a relational database instead of their Grindstone database file.

sqlite
sqlite536×593 31 KB

This extension requires that the following other extensions are also installed:

Download | Open Source Repository

2

EDIT: Sorry. I posted this to the wrong forum.

Here is an issue I am experiencing. I am expecting what you show. But what I see is this, the lack of an Extensions menu:

image

This was after [Restart Grindstone], and after completely stopping Grindstone manually and starting again.

Grindstone is recognizing the extensions. They are listed, as seen below. (Again, I have restarted already.)
image

My extensions folder looks like the following. You see both “Extensions Menu” and “Extensions.Menu” in the folder because I was confused at what folder level should be present in the extension lib. It was after moving the inner folder out that they appeared in the extensions menu.

image

3

Good, it looks like you figured it out!

4

So far, things have been better than expected. I thought this was going to be an export process. Instead, you delivered something the is kept up in real-time. Beyond expectations. I’ll be playing with this some tonight. Can’t wait for it to all come together.

5

I would like to re-iterate my thanks for your extension, and that it is working beyond expectations. To anyone who may read this, I was thinking the extension was going to be instance export of the data to a sqlite database. I don’t think it was clear in the description of this extension or the sqlserver extension that the databases are actively kept up-to-date in real-time. It’s as if the extension databases are the backbone of Grindstone. Once the connection is set, so are you.

My efforts are basically complete, now. I now have something I can query, format, and use to populate the new time tracking requirements here at work. There is going to be a lot less redundant hand typing in the future, thanks to this. With the ability to use sqlite for queries, each property can be queried and summarize in its unique way.

Thanks for all the work you had done. I wish others could/would benefit from this, here at my employer and abroad. I’m going to share Grindstone with others on the appdev team, and with the infrastructure folks and see if they can realize the benefit of it.

Thanks again,
dvn

1 Like
6

For anyone wanting to deal with dates in the SQLite database, @donvnielsen came up with a nice solution here.

Apparently taking the bigint for a date and dividing by 10000000 and then subtracting 62135596800 will give you a Unix timestamp. I also believe taking the bigint for the estimate on an assignment and dividing by 10000000 will also give you total seconds.

On the other hand, if you’re dealing with this from a .NET codebase, you can deal with dates & times and durations of time by just using the constructors for DateTimeOffset and TimeSpan, respectively.

Edit: corrected the constant per @donvnielsen‘s reply below.

7

Many apologies, but there is a correction on the constant. The original value I calculated was a little off. The correct constant value is 62135596800.

select TS.Id
     , TS.WorkItemId
     , datetime(TS.start / 10000000 - 62135596800, 'unixepoch') Start
     , datetime(TS.End / 10000000 - 62135596800, 'unixepoch')   End
     , (TS.End - TS.start) / 10000000                           Seconds
from Grindstone.TimeSlices TS;

When quoting the time, I formatted it as follows:

         printf('%3d', (sum(Seconds) / 3600)) || ':' || strftime('%M:%S', sum(Seconds) / 86400.0) AS TotalTime
8

I don’t think reloading a database from Use SQLite functions, as intended. I’m not sure how it can, and maybe it should just go away. (This likely applies to Use SQL Server, too.)

image

Use SQLite is a synchronized copy of what is in the .gsjdb file. It’s a real time backup. (A really cool backup, at that.) If you are connected to it, all is grand. Connecting to a database after it has been disconnected asks you for a new file. You have the option to replace the existing one, which will result in it being reinitialized. All is lost.

I attempted to outsmart Grindstone and failed. (Good job?) Anywho, after breaking my .gsjdb file, I attempted to recover the data from the sqlite file. I renamed the existing sqlite file, started up Grindstone…creating a new file with the old name and then connecting to it. (Connecting initializes the db, hence, rename and save it.)

I then exited out of Grindstone and renamed the sqlite files, returning the old one to the original name. My intent was to restart Grindstone and it would reconnect to the sqlite file without reinitializing it. I would then reload my data from the sqlite file. Unfortunately, this resulted in errors that I was unable to get around. (Sorry, I don’t have the errors any longer.)

Have a good one. (Keep working on 5.0, but this might be something to consider in that effort.)
dvn