Add PostgreSQL backend

[leso-kn]

This PR adds experimental PostgreSQL support.

Based upon the work of @dxtr, extended and adapted to the latest version of Baikal.

✅ The following features were tested:

• Baikal setup procedure / UI (with Postgres)
• Creating / deleting / editing a user
• Creating / deleting / editing a calendar
• Listing contacts
• Logging in / creating / deleting an event through the webui (User login)
• Subscribing to a calendar / adding / deleting an event through a DAV client
  • Thunderbird
  • DAVx⁵ for Android

[leso-kn]

ref #122

[2Belette]

since one ! does this PR would make it into new release ?

[comrada]

Looking forward to Postgresql support!! I keep MySQL on the server just for the sake of Baikal.

[n1dr00]

I use Baikal with this patch for my personal use during half year from clean install and don't have any troubles.

The following features were tested:

Baikal setup procedure / UI (with Postgres) Creating a user Creating / editing a calendar (with Todos and Tasks) Creating / editing Address Book Logging in, listing an event and contacts through the webui (dav.php) Subscribing to a calendar and address book / adding / deleting an event and contacts through a DAV client Thunderbird Carddav-sync/Caldav-sync

[leso-kn]

@Terrance Done! Sorry for the late reply, the updates described in your previous comment are now included in 54ebfe0

[dewey]

Great to see this, any idea what would be needed to get this merged?

[PrivatePuffin]

Combined with CloudNative-PG this finally gives an easy route to enterprise-grade hosted baikal, nice!

[nileshtrivedi]

Looking forward to this being merged that would finally fix #122

[kotovalexarian]

I've just installed Baikal with this patch to my server. Looks like everything works well with PostgreSQL. I hope this patch will be accepted so I can use the official build.

[n-rodriguez]

Hi there! Any news? kind ping to @ByteHamster

[n-rodriguez]

Hi there! Any news? kind ping to @ByteHamster

[ByteHamster]

Thanks!

[MrAlucardDante]

Is there a migration path available? Or do we have to export everything, switch to Postgres then re-import dav entries?

[ByteHamster]

There is no migration path. Upgrading Baikal will keep the database that you had before.

[ByteHamster]

Hmm @leso-kn I noticed that this PR broke part of the initial installation wizard (showing pgsql settings even when they should not be shown). Could you please have a look at that and fix it?

[ByteHamster]

Oh. I just noticed that this PR actually completely breaks new installs with sqlite... This is kind of bad.

[MrAlucardDante]

Since it's not released yet, could you please try to load a populated sqlite db into postgres with something like pgloader to see if we have things to adapt, like autoincremented ids or special conversion for timestamps? Here's so useful infos https://stackoverflow.com/questions/4581727/how-to-convert-sqlite-sql-dump-file-to-postgresql

[ByteHamster]

It is already released.

[MrAlucardDante]

It is already released.

My bad, browser cache showed me the previous release. I thought it was just merged. But since it breaks new installs using sqlite, the test could be included in an upcoming fix

[n-rodriguez]

What I did :

1. Edit db.sqlite with https://sqlitebrowser.org/dl/ 1.1 modify all tables to check AI (auto increment) checkbox on id columns 1.2 modify calendarinstances to change access column type from integer COMMENT 1 = owner, 2 = read, 3 = readwrite to integer 1.3 modify propertystorage to change value column type from string to text

2. Load in Postgres

load database
  from sqlite:///Users/nicolas/BAIKAL/db.sqlite
  into postgresql:///baikal
  with include drop, create tables, create indexes, reset sequences, on error resume next
  CAST type blob to text
  ;

It works :)

nicolas@MacBook-Pro-de-Nicolas:~/BAIKAL$ pgloader db.load
2024-11-04T13:06:24.003361-05:00 LOG pgloader version "3.6.9"
2024-11-04T13:06:24.036862-05:00 LOG Migrating from #<SQLITE-CONNECTION sqlite:///Users/nicolas/BAIKAL/db.sqlite {700BC7D7D3}>
2024-11-04T13:06:24.036942-05:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://nicolas@UNIX:5432/baikal {700BC7D863}>
2024-11-04T13:06:24.232287-05:00 LOG report summary reset
             table name     errors       rows      bytes      total time
-----------------------  ---------  ---------  ---------  --------------
                  fetch          0          0                     0.000s
        fetch meta data          0         38                     0.018s
         Create Schemas          0          0                     0.001s
       Create SQL Types          0          0                     0.002s
          Create tables          0         28                     0.039s
         Set Table OIDs          0         14                     0.007s
-----------------------  ---------  ---------  ---------  --------------
                  users          0          2     0.1 kB          0.012s
      schedulingobjects          0          0                     0.009s
        propertystorage          0          0                     0.016s
             principals          0          2     0.2 kB          0.010s
                  locks          0          0                     0.016s
           groupmembers          0          0                     0.018s
                  cards          0        114    49.1 kB          0.026s
  calendarsubscriptions          0          0                     0.019s
        calendarobjects          0        125   470.6 kB          0.036s
              calendars          0          2     0.0 kB          0.023s
      calendarinstances          0          2     0.2 kB          0.030s
           addressbooks          0          2     0.1 kB          0.032s
        calendarchanges          0        479    26.6 kB          0.025s
     addressbookchanges          0       2202   115.7 kB          0.037s
-----------------------  ---------  ---------  ---------  --------------
COPY Threads Completion          0          4                     0.043s
         Create Indexes          0         24                     0.021s
 Index Build Completion          0         24                     0.018s
        Reset Sequences          0          9                     0.011s
           Primary Keys          0         14                     0.002s
    Create Foreign Keys          0          0                     0.000s
        Create Triggers          0          0                     0.000s
       Install Comments          0          0                     0.000s
-----------------------  ---------  ---------  ---------  --------------
      Total import time          ✓       2930   662.7 kB          0.096s

I've done some tests in Thunderbird (create/update/remove events in calendar, create/update/remove contacts in address book) it works 👍

[leso-kn]

Oh. I just noticed that this PR actually completely breaks new installs with sqlite... This is kind of bad.

@ByteHamster Oh, sorry for the late reply, I missed that notification. Wow crap, that is kind of bad! Did you already have a look by the time? I should be able to look into it tomorrow

[ByteHamster]

@leso-kn I did not have a look, so it would be good if you could

[leso-kn]

@ByteHamster Done, see #1302.

Again my apologies for breaking this in the first place! Don't know what I was thinking when I worked on the install page, I had not tested that code at all.