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.
whatgaul 1589 days ago [-]
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.
Will take a look!
whatgaul 1590 days ago [-]
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.
Questions? Feedback? All very very welcome!
paulryanrogers 1590 days ago [-]
Sqlite please. Or DB-agnostic SQL.
luuuuk 1589 days ago [-]
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!
paulryanrogers 1588 days ago [-]
> - There should be no vendor lock-in
Then why only support MySQL?
luuuuk 1588 days ago [-]
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
opless 1590 days ago [-]
Agreed. Like PHP, MySQL really ought not be used in production systems.
(Also just because LAMP stacks are popular, it doesn't mean they're much more than toys)
jjeaff 1589 days ago [-]
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?
opless 1589 days ago [-]
Yes! Mind-blowing isn't it?
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.
opless 1589 days ago [-]
> Tell me how uninformed I am
From https://aws.amazon.com/rds/aurora/
Amazon Aurora is a MySQL and PostgreSQL-compatible relational database built for the cloud, that combines the performance and availability of traditional enterprise databases with the simplicity and cost-effectiveness of open source databases.
Compatible
As I understand it they're protocol compatible and that's it. I shall have to ask my guy at Amazon about it further as clearly it's somewhat ambiguous.
> MySql
I've managed to happily provoke all manner of poor behaviour from MySQL and MariaDB. Some of it repeatable, with certain backends. I've used and admined MySQL since 98 or so. Enough to compare and contrast it with Ora/MSSQL/postgres.
Before I swore off the frankly horrid lamp stack about a decade ago, I had built quite a number of sites for SMEs, including Recruitment Agencies , A racing federation, A major airline ticketing company, I could go on, but I assure you that I'm not a noob as you suggested.
My point before, what just because LAMP stacks are well used, doesn't make them GOOD. It doesn't matter what's built with them. Even Facebook uses/used PHP, but it completely redesigned its own runtime IIRC.
> Shades of grey/240v
I estimate that there's only about 50 ;)
Yes, get it going through your heart and you're toast, or you bridge live and neutral and your muscles contract stopping you from pulling away.
Anecdotally I've had bigger zaps from electric fences than mains.
Anyway my assertion still stands, MySQL is a toy database and should not be used in a critical environment.
LAMP stacks are horrible.
Arguing with a stranger over the internet is futile. :-)
Good night!
jjeaff 1588 days ago [-]
Spend 30 seconds educating yourself on Aurora. It's not ambiguous. It clearly says it on the AWS page I linked. MySQL compatible Aurora is a fork of MySQL with a modified storage engine. According to AWS's own docs, the performance increase is mostly a hardware play where they are replicating and striping the storage across various SSDs.
If you haven't used LAMP in 10 years, oh my. 10 years in the world of devops and software development might as well be an eternity. MySQL's InnoDB storage engine is a totally different animal that MyIsam.
Your experience is severely out of date and/or incorrect. You should keep it to yourself and avoid contradicting strangers over the internet when you know very little about what you are talking about.
Which clearly says compatible and goes into more depth than anyone really needs.
I would say this person would be authoritive, rather than documentation which is usually not written by the developers themselves. But you knew that already. Right?
So I educated myself. Maybe you should too :-)
"Mostly a hardware play", mmhm. That's contradicted by the video. But I can't be right can I? I don't have enough fake internet points! SMH
BTW my test with MySQL was only a few years ago, and it was InnoDB, thanks.
I should say I've not used LAMP stacks in anger for that period of time. I still very occasionally pick up php and still find the documentation woefully inadequate and wrong. Over time I've moved over to python for my hacky projects and whilst I still consider whitespace as syntax to be insane, it's actually easier to get stuff done.
You might disagree with my opinions, but disagreeing with my experience is quite embarrassing.
jjeaff 1583 days ago [-]
You do realize that MariaDB is also Mysql compatible? As is PerconaDB. No one is arguing that aurora isn't mysql compatible and that video doesn't contradict AWSs own website that says mysql compatible aurora is based on mysql.
slifin 1590 days ago [-]
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?
whatgaul 1590 days ago [-]
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.
theknarf 1589 days ago [-]
I would love this if it was open source and I could self host it.
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.
What was your motivation for working on something like this? That would be hugely helpful for us to understand!
We also just decided to go ahead and open-source it: https://github.com/belvinlabs/proost-app. (Very rough; hit us up for help if you'd like to get involved.)
Will take a look!
Questions? Feedback? All very very welcome!
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!
Then why only support MySQL?
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
(Also just because LAMP stacks are popular, it doesn't mean they're much more than toys)
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.
>Amazon Aurora with MySQL compatibility is also built using MySQL https://aws.amazon.com/rds/mysql/what-is-mysql/
Tell me more about how uninformed I am...
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.
Compatible As I understand it they're protocol compatible and that's it. I shall have to ask my guy at Amazon about it further as clearly it's somewhat ambiguous.
> MySql I've managed to happily provoke all manner of poor behaviour from MySQL and MariaDB. Some of it repeatable, with certain backends. I've used and admined MySQL since 98 or so. Enough to compare and contrast it with Ora/MSSQL/postgres.
Before I swore off the frankly horrid lamp stack about a decade ago, I had built quite a number of sites for SMEs, including Recruitment Agencies , A racing federation, A major airline ticketing company, I could go on, but I assure you that I'm not a noob as you suggested.
My point before, what just because LAMP stacks are well used, doesn't make them GOOD. It doesn't matter what's built with them. Even Facebook uses/used PHP, but it completely redesigned its own runtime IIRC.
> Shades of grey/240v I estimate that there's only about 50 ;)
Yes, get it going through your heart and you're toast, or you bridge live and neutral and your muscles contract stopping you from pulling away.
Anecdotally I've had bigger zaps from electric fences than mains.
Anyway my assertion still stands, MySQL is a toy database and should not be used in a critical environment.
LAMP stacks are horrible.
Arguing with a stranger over the internet is futile. :-)
Good night!
If you haven't used LAMP in 10 years, oh my. 10 years in the world of devops and software development might as well be an eternity. MySQL's InnoDB storage engine is a totally different animal that MyIsam.
Your experience is severely out of date and/or incorrect. You should keep it to yourself and avoid contradicting strangers over the internet when you know very little about what you are talking about.
Which clearly says compatible and goes into more depth than anyone really needs.
I would say this person would be authoritive, rather than documentation which is usually not written by the developers themselves. But you knew that already. Right?
So I educated myself. Maybe you should too :-)
"Mostly a hardware play", mmhm. That's contradicted by the video. But I can't be right can I? I don't have enough fake internet points! SMH
BTW my test with MySQL was only a few years ago, and it was InnoDB, thanks.
I should say I've not used LAMP stacks in anger for that period of time. I still very occasionally pick up php and still find the documentation woefully inadequate and wrong. Over time I've moved over to python for my hacky projects and whilst I still consider whitespace as syntax to be insane, it's actually easier to get stuff done.
You might disagree with my opinions, but disagreeing with my experience is quite embarrassing.
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.
We'd love to hear how you'd like to use it, whether in the comments here or through Issues in that repo. That's hugely helpful to us right now!