89 lines
2.4 KiB
Markdown
89 lines
2.4 KiB
Markdown
# Postgres
|
|
- url
|
|
- Format: `postgres://user@host:port/dbname` or `postgres:///dbname?host=/var/run/postgresql`.
|
|
- Default: `postgres://nxcaldav@localhost:5432/nxcaldav?sslmode=disable`
|
|
|
|
# Users
|
|
|
|
- name: required
|
|
- password: cleartext or bcrypt hash
|
|
- password_cmd: shell command
|
|
# password_cmd: "echo secretpassword" # Command (output will be hashed in DB)
|
|
# password: "$2y$12$LU.8xNK6m98hEJ5oRnBsDuMamfIjXoWTW0eMIJ6yGdLoP3nJAHWH6"
|
|
|
|
# SQL
|
|
## delte user
|
|
```sql
|
|
DELETE FROM users WHERE name = 'bob';
|
|
```
|
|
|
|
## delete calendar
|
|
```sql
|
|
DELETE FROM calendars WHERE name = 'bob_calendar' AND owner_id = (SELECT id FROM users WHERE name = 'bob');
|
|
/* or */
|
|
DELETE FROM calendars WHERE path = '/bob/calendars/old/';
|
|
```
|
|
## rename calendar
|
|
```sql
|
|
UPDATE calendars SET name = 'new', path '/bob/calendars/new/' WHERE path = '/bob/calendars/old/';
|
|
UPDATE calendar_objects SET path = regexp_replace(path, '/old/', '/new/')' WHERE your_column ~ '/sport/';
|
|
```
|
|
|
|
## Match Value in calendar objects
|
|
```sql
|
|
select id, path, linecut from calendar_objects, LATERAL regexp_split_to_table(data, E'\r\n') AS linecut where linecut ~ 'CLASS';
|
|
```
|
|
|
|
|
|
---
|
|
|
|
# Helpers
|
|
|
|
## Prerequisites
|
|
The scripts require psycopg2 (or psycopg2-binary) and PyYAML.
|
|
|
|
```shell
|
|
pip install psycopg2-binary pyyaml
|
|
```
|
|
|
|
## Exporting Events
|
|
The `export_events.py` script extracts events from the database and saves them as `.ics` files in a structured directory format: `username/calendarID/filename.ics`.
|
|
|
|
### Examples:
|
|
- Export everything:
|
|
```shell
|
|
python export_events.py --output ./my_backup
|
|
```
|
|
|
|
- Export only one user:
|
|
```shell
|
|
python export_events.py --user alice --output ./alice_backup
|
|
```
|
|
|
|
- Export a specific calendar:
|
|
```shell
|
|
python export_events.py --user alice --calendar preservation --output ./preservation_backup
|
|
```
|
|
|
|
|
|
## Importing Events
|
|
The `import_events.py` script uploads events back into the database. It can either walk a structured directory (like the one created by the export script) or upload a flat directory of files to a specific target calendar.
|
|
|
|
### Examples:
|
|
- Restore everything from a backup:
|
|
```shell
|
|
python import_events.py --input ./my_backup
|
|
```
|
|
|
|
- Upload a directory of .ics files to a specific calendar:
|
|
```shell
|
|
python import_events.py --input ./some_events/ --user alice --calendar work
|
|
```
|
|
|
|
- Upload a single file to a specific calendar:
|
|
```shell
|
|
python import_events.py --input ./event.ics --user bob --calendar bob
|
|
```
|
|
|
|
|