Because so much is already automatically tracked in my calendar (e.g. the HR system adds calendar events for booked holiday), I just needed a way to query it to get the metrics I wanted.
Originally I used a Go MySQL implementation to evaluate queries but it was a bit buggy and didn’t support all the SQL I wanted. Now I’m using a full SQLite database for each user so you can really do some gnarly queries (recursive CTEs, window functions, the works).
Personally, I’m using QueryCal as a Grafana datasource to power a dashboard that’s displayed on an old Kindle on my desk (using this great project: https://github.com/pascalw/kindle-dash).
For example, could I create a panel in Grafana showing the busy vs. free ratio over the last three months? That kind of things.
This is one of the queries I'm using to track a rolling average of how many times a week I've been cycling: WITH recursive dates(day) AS ( SELECT date($__unixepochfrom(), 'unixepoch') UNION ALL SELECT date(day, '+1 DAY') FROM dates WHERE day<date($__unixepochto(), 'unixepoch') ) SELECT day AS time, ( SELECT count(DISTINCT julianday(start)) FROM events WHERE summary = " Cycling" AND start <= date(day, '+1 DAY') AND start > date(day, '-7 DAY')) AS cycling, FROM dates ORDER BY day DESC
And yeah 100% admit the documentation is lacking. I'll be honest, I got to the 3 week mark of building and decided I need to validate the idea before putting any more work in. Going to be adding some more pre-done queries like this as inspiration.
I'm also intrigued by your 'SQLite-per-user' structure and would be keen to hear how you get on with that long-term. For instance, how will you tackle migrations?
I don't have any worries about managing long-term schema migrations though because the per-user databases get blown away and reconstructed every time the calendars are refreshed.
At the moment there's just an `events` table but I might handle migrations by having: `events_v1`, `events_v2`, etc. and just have `events` be an SQL view onto the version you chose.
Managing as little persistent state myself was a specific goal so this project is perfect because apart from some authentication info and a list of iCal URLs, the source of truth for your calendar is always with Google/Microsoft/etc.
In more complex systems though it feels like it's going to require quite a bit of orchestration.
The big option that SQLite opens up though is portability - I imagine one day just saying to customers that they can download their database and interface with it themselves or shift it to another provider.
I’m a corporate user and unfortunately locked into Outlook and usually can’t run outside service against my calendar, e.g. calendly. I can occasionally get an Outlook plugin to work though. Have you considered anything like that?
OAuth + calendar plugins are definitely on the roadmap though because it's a much nicer UX than having to dig around in calendar sharing menus
Yes, at the moment there's a batch job to download your calendars and convert them into a SQLite DB. Then, all queries are done directly on that concrete DB (read only).
Originally I was using https://github.com/dolthub/go-mysql-server which is quite similar to the virtual table feature (you just provide a struct which implements some getter methods). Unfortunately I found it a bit slow though (it had to call back into my table many times for even simple queries). Might just be a problem with that implementation and not a limitation of virtual tables themselves so thanks for pointing it out!
I'm planning to have a little SQL client on the homepage you can use to query against demo data (maybe the public holidays calendars from Google?) but I didn't get around to it yet.
It's a bit of a fun challenge locking it down: purposely putting unauthenticated SQLi on your homepage isn't usually recommended!
But the more I experience the document DBs forced down my gullet by the "tech guys" at the startup, the more I appreciate the sheer power of SQL to make things tidy.
My last pet project was basically entirely modeled in a .sql code file (this: https://github.com/asemic-horizon/hyscan/blob/master/prisone...). Very little remains to be written as procedural code.
