postgres-ical - a PostgreSQL extension that adds features related to parsing RFC-5545 « iCalendar » data from within a PostgreSQL database

Overview

postgres-ical

postgres-ical is a PostgreSQL extension that adds features related to parsing RFC-5545 « iCalendar » data from within a PostgreSQL database.

Why ?

iCalendar files are nothing more than a big table of « components » with a lot of properties. That's what relational databases handle every day.

The format is specifically designed not as a way to store calendar data, but as a way to transfer calendar data from a piece of software to another. Quite a few online calendar software programs allow their users to export a live version of their calendars as an iCalendar URL. There are many situations in which being able to run SQL queries on such a file maybe be useful. Also, importing an iCalendar file in an SQL database can be used as an easy caching system.

Can't we simply have a client daemon do this syncing between the database and the remote source ?

Yes, definitely. And sometimes, installing PostgreSQL extensions is simply not possible. However, here are a few advantages of having the querying and parsing done from within the database :

  1. Separation of concerns: Whatever it is you're actually doing with the calendar data, it's probably not the business of your application to keep the table of events in sync. This whole system is just a kind of one-way replication, which is usually done by the database and not by database clients.
  2. Atomicity and correctness: more often than not, the queried calendar data would be stored in a materialized view. Even if you could store your data in a normal table and always try to use a transaction to update it, nothing prevents you fundamentally, from doing "illegal" operations on the table, such as updates or insertions of data that is not present in the actual iCalendar source.
  3. Unique source of truth: since the calendar data is actually a replication of data from elsewhere, by using a materialized view that directly queries the source on refresh, it is clear to anyone reading your database structure that the data is a copy from elsewhere. Additionally, the queried data would not appear in a database dump, which just makes sense since it's replicated.
  4. Simplicity ?: Depending on your use-case, doing part of the work inside the database might just be simpler than having to set up multiple clients, multiple systemd services, etc.

Building

To be documented...

Usage

After installing the extension, you can use the 2 following functions :

select * from pg_ical('BEGIN:VCALENDAR...');
select * from pg_ical_curl('https://example.com/calendar.ical');

The columns that are returned are documented on the Rustdoc, by the structure called Component. You can build the Rustdoc using cargo doc --no-deps --open.

Regarding compatibility and versioning, I don't consider column additions to be breaking changes, but alterations and deletions obviously are. You should ideally use precise select statements in order not to have surprises.

Tech stack

The extension is made in Rust, with the pgx library doing the rotten job of handling FFI. General iCalendar parsing is done by the ical crate, while the actual meaning of properties is inferred by a local crate (/postgres-ical-parser), that will be published independently one day.

License

The project doesn't have I license yet, and is thus considered "all rights reserved". I probably won't sue you if use it, but you technically don't have permission for the moment. I'm looking for a license similar to the AGPL, that would require you to distribute the source code to any remote users who ask for it, but in a way that doesn't infect your SQL code that uses the library functions (similar to the LGPL). An "LAGPL", so to say. Suggestions are welcome.

You might also like...
Scalable and fast data store optimised for time series data such as financial data, events, metrics for real time analysis

OnTimeDB Scalable and fast data store optimised for time series data such as financial data, events, metrics for real time analysis OnTimeDB is a time

Manage Redshift/Postgres privileges in GitOps style written in Rust

grant-rs An open-source project that aims to manage Postgres/Redshift database roles and privileges in GitOps style, written in Rust. Home | Documenta

Making Postgres and Elasticsearch work together like it's 2021

Making Postgres and Elasticsearch work together like it's 2021 Readme ZomboDB brings powerful text-search and analytics features to Postgres by using

rust-postgres support library for the r2d2 connection pool

r2d2-postgres Documentation rust-postgres support library for the r2d2 connection pool. Example use std::thread; use r2d2_postgres::{postgres::NoTls,

An easy-to-use, zero-downtime schema migration tool for Postgres

Reshape is an easy-to-use, zero-downtime schema migration tool for Postgres. It automatically handles complex migrations that would normally require downtime or manual multi-step changes.

Command-line tool to make Rust source code entities from Postgres tables.

pg2rs Command-line tool to make Rust source code entities from Postgres tables. Generates: enums structs which can be then used like mod structs; use

The simplest implementation of LLM-backed vector search on Postgres.

pg_vectorize under development The simplest implementation of LLM-backed vector search on Postgres. -- initialize an existing table select vectorize.i

A Pub/Sub library for Rust backed by Postgres

Unisub Unisub is a Pub/Sub library for Rust, using Postgres as the backend. It offers a convenient way to publish and subscribe to messages across dif

Seed your development database with real data ⚡️
Seed your development database with real data ⚡️

Seed Your Development Database With Real Data ⚡️ Replibyte is a blazingly fast tool to seed your databases with your production data while keeping sen

Owner
Edgar Onghena
FLOSS fanatic | 📧 [email protected]
Edgar Onghena
🐸Slippi DB ingests Slippi replays and puts the data into a SQLite database for easier parsing.

The primary goal of this project is to make it easier to analyze large amounts of Slippi data. Its end goal is to create something similar to Ballchasing.com but for Melee.

Max Timkovich 20 Jan 2, 2023
cogo rust coroutine database driver (Mysql,Postgres,Sqlite)

cdbc Coroutine Database driver Connectivity.based on cogo High concurrency,based on coroutine No Future<'q,Output=*>,No async fn, No .await , no Poll*

co-rs 10 Nov 13, 2022
Distributed, version controlled, SQL database with cryptographically verifiable storage, queries and results. Think git for postgres.

SDB - SignatureDB Distributed, version controlled, SQL database with cryptographically verifiable storage, queries and results. Think git for postgres

Fremantle Industries 5 Apr 26, 2022
A Rust application that inserts Discogs data dumps into Postgres

Discogs-load A Rust application that inserts Discogs data dumps into Postgres. Discogs-load uses a simple state machine with the quick-xml Rust librar

Dylan 7 Dec 9, 2022
Postgres Foreign Data Wrapper for Clerk.com API

Pre-requisites Postgres-15 Rust pgrx Getting Started To run the program locally, clone the repository git clone https://github.com/tembo-io/clerk_fdw.

Tembo 3 Aug 22, 2023
The Solana AccountsDb plugin for PostgreSQL database.

The solana-accountsdb-plugin-postgres crate implements a plugin storing account data to a PostgreSQL database to illustrate how a plugin can be develo

Lijun Wang 3 Jun 16, 2022
Teach your PostgreSQL database how to speak MongoDB Wire Protocol

“If it looks like MongoDB, swims like MongoDB, and quacks like MongoDB, then it probably is PostgreSQL.” ?? Discord | Online Demo | Intro Video | Quic

Felipe Coury 261 Jun 18, 2023
Materialize simplifies application development with streaming data. Incrementally-updated materialized views - in PostgreSQL and in real time. Materialize is powered by Timely Dataflow.

Materialize is a streaming database for real-time applications. Get started Check out our getting started guide. About Materialize lets you ask questi

Materialize, Inc. 4.7k Jan 8, 2023
Zenith substitutes PostgreSQL storage layer and redistributes data across a cluster of nodes

Zenith substitutes PostgreSQL storage layer and redistributes data across a cluster of nodes

null 5.7k Jan 6, 2023
Efficient and fast querying and parsing of GTDB's data

xgt xgt is a Rust tool that enables efficient querying and parsing of the GTDB database. xgt consists of a collection of commands mirroring the GTDB A

Anicet Ebou 7 Apr 1, 2023