We built something like this internally and it was more tricky than we expected. Calendars are hard as are Google's different authentication methods and APIs, especially for service accounts at a GSuite level.
I can't imagine using a third party service for this as calendar data is pretty confidential. Would probably have saved us a lot of time if this were an open source project though so we could use our own database.
We sure can relate! Setting up the sync logic and coming up with a reasonable first-cut database schema was considerably more complicated than we'd anticipated.
What was your motivation for working on something like this? That would be hugely helpful for us to understand!
Amazing! We also wanted to do analytics to analyse and allocate some people's time better and use this to autoschedule work based on a pretty complicated internal system.
This project grew out of our interest in understanding and optimizing time spent in meetings, but don't let that stop you from using it some other way. We're providing direct access to a MySQL database specifically so you can slice the data however you like, with any tool you please.
Thanks for your input! As future features require continuous syncing between calendar data and the database, SQLite is not really an option.
We chose MySQL based on the following requirements:
- It should be easy to write data to a database created by us, or hosted (on-premise) by a customer due to the sensitive nature of the data (which excludes Google Cloud Spanner and Amazon Aurora)
- As the purpose of this data is analyzed, the DB should be compatible with most BI tools (which excludes SQLite)
- There should be no vendor lock-in
- Due to the analysis purpose of this tool, we prefer a database that focusses on read performance vs write performance (MySQL > PostgreSQL)
- The Calendar Events table can grow long and we don't anticipate it getting wide, which favors row-based databases vs column-based databases (MySQL > Redshift)
- I personally use the WITH and WITH RECURSIVE statements a TON when doing analysis which originally excluded MySQL, but with the launch of MySQL 8, that no longer was an issue.
- We can easily build export functionality to SQLite
For all these reasons, we thought (and think) that MySQL 8 fits the use case quite well and we can relatively easily support other RDBMS databases if needed. Would love to hear your thoughts!
MySQL seemed a good start and we'll roll out more options upon request, but we first want to see some engagement and get some feedback before investing in that.
To test out how hard it would be to support another DB type, we already have a branch working that writes to a Postgres DB, which was a minor adjustment. So: MySQL for now, but we are writing our code to easily support other DBs if needed.
There are migration tools available for almost any database type from MySQL
What?!? You do realize that MySQL is one of the most popular and battle tested databases on the internet?
Amazon literally just finished moving their entire e-commerce operations from Oracle to Aurora which is based on MySQL open source.
What in the world do you think is a production ready if not the database used by nearly every company in silicon valley and around 85% of the rest of the "production" web?
Aurora ... Is "compatible" with postgres and MySQL. Not based on it.
I can imagine the reason Amazon moved away from Oracle's flagship DB because of the licensing fees.
You've obviously not used MySQL instead of a proper database, that doesn't blow up with a few million records which MySQL can and will do. Often irretrievably.
Seriously, MySQL is a toy compared to postgress, oracle and MSSQL.
And looking at your recent comments, never been zapped by 240v A/C either.
Please refrain from making massively uninformed comments, it's just embarrassing.
Mysql won't even break a sweat for a few million records. I have run mysql databases with a single instance and billions of records with little optimization needed.
PostgreSQL is great for developers and I love some of the features it has that mysql doesn't (although mysql 8.0 has caught up quite a bit). But Postgresql is not nearly as battle tested for highly scaled implementations and in many cases will not perform as well as mysql.
I get the impression you have never actually built anything or you would know how so much of the internet runs on a php/mysql stack (think big, like Wikipedia).
And your comment about being zapped by 240v shows that you don't understand how electricity works. You could be zapped by 240v and barely feel it if you aren't grounded well. So to say you know what it is like to be zapped by 240v is like saying you know what it is like to get hit by a car. There are a million shades of grey of how that could end up.
I've had a look at the database schema and data but there's no data for the recurrence_id field since google calendar can express an infinite set of recurrences how is this represented in the database structure?
Right, naturally none of the rocket launches in the demo data set are recurring (perhaps one day...). In another calendar with recurring events, that ID will be a consistent string across all events that are part of the same series. It's pulled from the `recurringEventId` field in the Google Calendar API: https://developers.google.com/calendar/v3/reference/events#r...
At the moment, the tool just syncs events from one month in the past through one month in the future, so it will pick up a finite subset of a potentially infinite series of recurring events. In future iterations, we imagine users defining their own timeframe to sync.
I can't imagine using a third party service for this as calendar data is pretty confidential. Would probably have saved us a lot of time if this were an open source project though so we could use our own database.