~emersion/soju-dev

This thread contains a patchset. You're looking at the original emails, but you may wish to use the patch review UI. Review patch
4 3

[PATCH 1/2] PostgreSQL support

Details
Message ID
<20210913213437.40552-1-hubert@hirtz.pm>
DKIM signature
pass
Download raw message
Patch: +546 -6
---

Now soju accepts "db postgres ..." in the config. See doc/soju.1.scd.

Implem notes and TBDs:
- I don't see how it's possible to query the schema version without
  doing plpgsql, or some grep magic on tx.Query() error code, so I did
  plpgsql.
- All statements are prepared beforehand for performance, and it also
  allowed us to read Java-levels of boilerplate.
- I used database/sql since it's also what's used for db_sqlite.go, but
  I heard on "Hacker" "News" there are other (better) drivers out there,
  especially for PostgreSQL-only features like arrays, etc.

    https://github.com/jackc/pgx

  Since its interface is a superset of database/sql, I think the
  migration would be fairly easy if it ends up needed.

 cmd/soju/main.go    |   2 +-
 cmd/sojuctl/main.go |   2 +-
 db.go               |  12 ++
 db_postgres.go      | 510 ++++++++++++++++++++++++++++++++++++++++++++
 db_sqlite.go        |   4 +-
 doc/soju.1.scd      |  19 +-
 go.mod              |   1 +
 go.sum              |   2 +
 8 files changed, 546 insertions(+), 6 deletions(-)
 create mode 100644 db_postgres.go

diff --git a/cmd/soju/main.go b/cmd/soju/main.go
index 9f67182..879dd41 100644
--- a/cmd/soju/main.go
+++ b/cmd/soju/main.go
@@ -61,7 +61,7 @@ func main() {
		cfg.Listen = []string{":6697"}
	}

	db, err := soju.OpenSqliteDB(cfg.SQLDriver, cfg.SQLSource)
	db, err := soju.OpenDB(cfg.SQLDriver, cfg.SQLSource)
	if err != nil {
		log.Fatalf("failed to open database: %v", err)
	}
diff --git a/cmd/sojuctl/main.go b/cmd/sojuctl/main.go
index 48720b2..d19ccfb 100644
--- a/cmd/sojuctl/main.go
+++ b/cmd/sojuctl/main.go
@@ -43,7 +43,7 @@ func main() {
		cfg = config.Defaults()
	}

	db, err := soju.OpenSqliteDB(cfg.SQLDriver, cfg.SQLSource)
	db, err := soju.OpenDB(cfg.SQLDriver, cfg.SQLSource)
	if err != nil {
		log.Fatalf("failed to open database: %v", err)
	}
diff --git a/db.go b/db.go
index b28827a..d6fae28 100644
--- a/db.go
+++ b/db.go
@@ -26,6 +26,18 @@ type Database interface {
	StoreClientDeliveryReceipts(networkID int64, client string, receipts []DeliveryReceipt) error
}

func OpenDB(driver, source string) (Database, error) {
	switch driver {
	case "sqlite3":
		return OpenSqliteDB(source)
	case "postgres":
		return OpenPostgresDB(source)
	default:
		return nil, fmt.Errorf("unsupported database driver: %q", driver)
	}

}

type User struct {
	ID       int64
	Username string
diff --git a/db_postgres.go b/db_postgres.go
new file mode 100644
index 0000000..15d4695
--- /dev/null
+++ b/db_postgres.go
@@ -0,0 +1,510 @@
package soju

import (
	"database/sql"
	"fmt"
	"math"
	"strings"
	"time"

	_ "github.com/lib/pq"
)

const postgresFunctions = `
DROP FUNCTION IF EXISTS sojuVersion;
CREATE FUNCTION sojuVersion() RETURNS INTEGER AS $$
DECLARE
	version INTEGER;
BEGIN
	SELECT Config.version INTO version FROM Config;
	RETURN version;
EXCEPTION
	WHEN UNDEFINED_TABLE THEN RETURN 0;
END;
$$ LANGUAGE plpgsql
`

const postgresSchema = `
CREATE TABLE Config (
	id SMALLINT PRIMARY KEY,
	version INTEGER NOT NULL,
	CHECK(id = 1)
);

CREATE TABLE "User" (
	id SERIAL PRIMARY KEY,
	username VARCHAR(255) NOT NULL UNIQUE,
	password VARCHAR(255),
	admin BOOLEAN NOT NULL DEFAULT FALSE,
	realname VARCHAR(255)
);

CREATE TABLE Network (
	id SERIAL PRIMARY KEY,
	name VARCHAR(255),
	"user" INTEGER NOT NULL REFERENCES "User"(id) ON DELETE CASCADE,
	addr VARCHAR(255) NOT NULL,
	nick VARCHAR(255) NOT NULL,
	username VARCHAR(255),
	realname VARCHAR(255),
	pass VARCHAR(255),
	connect_commands VARCHAR(1023),
	sasl_mechanism VARCHAR(255),
	sasl_plain_username VARCHAR(255),
	sasl_plain_password VARCHAR(255),
	sasl_external_cert BYTEA DEFAULT NULL,
	sasl_external_key BYTEA DEFAULT NULL,
	enabled BOOLEAN NOT NULL DEFAULT TRUE,
	FOREIGN KEY("user") REFERENCES "User"(id),
	UNIQUE("user", addr, nick),
	UNIQUE("user", name)
);

CREATE TABLE Channel (
	id SERIAL PRIMARY KEY,
	network INTEGER NOT NULL REFERENCES Network(id) ON DELETE CASCADE,
	name VARCHAR(255) NOT NULL,
	key VARCHAR(255),
	detached BOOLEAN NOT NULL DEFAULT FALSE,
	detached_internal_msgid VARCHAR(255),
	relay_detached INTEGER NOT NULL DEFAULT 0,
	reattach_on INTEGER NOT NULL DEFAULT 0,
	detach_after INTEGER NOT NULL DEFAULT 0,
	detach_on INTEGER NOT NULL DEFAULT 0,
	UNIQUE(network, name)
);

CREATE TABLE DeliveryReceipt (
	id SERIAL PRIMARY KEY,
	network INTEGER NOT NULL REFERENCES Network(id) ON DELETE CASCADE,
	target VARCHAR(255) NOT NULL,
	client VARCHAR(255),
	internal_msgid VARCHAR(255) NOT NULL,
	UNIQUE(network, target, client)
);
`

var postgresMigrations = []string{
	"", // migration #0 is reserved for schema initialization
}

type PostgresDB struct {
	db *sql.DB

	listUsers                  *sql.Stmt
	getUser                    *sql.Stmt
	storeUser                  *sql.Stmt
	deleteUser                 *sql.Stmt
	listNetworks               *sql.Stmt
	storeNetwork               *sql.Stmt
	deleteNetwork              *sql.Stmt
	listChannels               *sql.Stmt
	storeChannel               *sql.Stmt
	deleteChannel              *sql.Stmt
	listDeliveryReceipts       *sql.Stmt
	storeClientDeliveryReceipt *sql.Stmt
}

func OpenPostgresDB(source string) (Database, error) {
	sqlPostgresDB, err := sql.Open("postgres", source)
	if err != nil {
		return nil, err
	}

	db := &PostgresDB{db: sqlPostgresDB}
	if err := db.upgrade(); err != nil {
		sqlPostgresDB.Close()
		return nil, err
	}

	if err := db.prepareStatements(); err != nil {
		db.Close()
		return nil, err
	}

	return db, nil
}

func (db *PostgresDB) Close() error {
	if db.listUsers != nil {
		db.listUsers.Close()
	}
	if db.getUser != nil {
		db.getUser.Close()
	}
	if db.storeUser != nil {
		db.storeUser.Close()
	}
	if db.deleteUser != nil {
		db.deleteUser.Close()
	}
	if db.listNetworks != nil {
		db.listNetworks.Close()
	}
	if db.storeNetwork != nil {
		db.storeNetwork.Close()
	}
	if db.deleteNetwork != nil {
		db.deleteNetwork.Close()
	}
	if db.listChannels != nil {
		db.listChannels.Close()
	}
	if db.storeChannel != nil {
		db.storeChannel.Close()
	}
	if db.deleteChannel != nil {
		db.deleteChannel.Close()
	}
	if db.listDeliveryReceipts != nil {
		db.listDeliveryReceipts.Close()
	}
	if db.storeClientDeliveryReceipt != nil {
		db.storeClientDeliveryReceipt.Close()
	}
	return db.db.Close()
}

func (db *PostgresDB) upgrade() error {
	tx, err := db.db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	if _, err := tx.Exec(postgresFunctions); err != nil {
		return fmt.Errorf("failed to install functions: %s", err)
	}

	var version int
	if err := tx.QueryRow("SELECT sojuVersion()").Scan(&version); err != nil {
		return fmt.Errorf("failed to query schema version: %s", err)
	}

	if version == len(postgresMigrations) {
		return nil
	}
	if version > len(postgresMigrations) {
		return fmt.Errorf("soju (version %d) older than schema (version %d)", len(postgresMigrations), version)
	}

	if version == 0 {
		if _, err := tx.Exec(postgresSchema); err != nil {
			return fmt.Errorf("failed to initialize schema: %s", err)
		}
	} else {
		for i := version; i < len(postgresMigrations); i++ {
			if _, err := tx.Exec(postgresMigrations[i]); err != nil {
				return fmt.Errorf("failed to execute migration #%v: %v", i, err)
			}
		}
	}

	_, err = tx.Exec(`INSERT INTO Config(id, version) VALUES (1, $1)
		ON CONFLICT (id) DO UPDATE SET version = $1`, len(postgresMigrations))
	if err != nil {
		return fmt.Errorf("failed to bump schema version: %v", err)
	}

	return tx.Commit()
}

func (db *PostgresDB) prepareStatements() error {
	var err error

	db.listUsers, err = db.db.Prepare(`SELECT id, username, password, admin FROM "User"`)
	if err != nil {
		return err
	}

	db.getUser, err = db.db.Prepare(`SELECT id, password, admin, realname FROM "User" WHERE username = $1`)
	if err != nil {
		return err
	}

	db.storeUser, err = db.db.Prepare(`
		INSERT INTO "User" (username, password, admin, realname)
		VALUES ($1, $2, $3, $4)
		ON CONFLICT (username)
		DO UPDATE SET password = $2, admin = $3, realname = $4
		RETURNING id`)
	if err != nil {
		return err
	}

	db.deleteUser, err = db.db.Prepare(`DELETE FROM "User" WHERE id = $1`)
	if err != nil {
		return err
	}

	db.listNetworks, err = db.db.Prepare(`
		SELECT id, name, addr, nick, username, realname, pass, connect_commands, sasl_mechanism,
		       sasl_plain_username, sasl_plain_password, sasl_external_cert, sasl_external_key, enabled
		FROM Network
		WHERE "user" = $1`)
	if err != nil {
		return err
	}

	db.storeNetwork, err = db.db.Prepare(`
		INSERT INTO Network("user", name, addr, nick, username, realname, pass, connect_commands,
		                    sasl_mechanism, sasl_plain_username, sasl_plain_password, sasl_external_cert,
		                    sasl_external_key, enabled)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)
		ON CONFLICT ("user", name)
		DO UPDATE SET name = $2, addr = $3, nick = $4, username = $5, realname = $6, pass = $7,
		              connect_commands = $8, sasl_mechanism = $9, sasl_plain_username = $10,
		              sasl_plain_password = $11, sasl_external_cert = $12, sasl_external_key = $13,
		              enabled = $14
		RETURNING id`)
	if err != nil {
		return err
	}

	db.deleteNetwork, err = db.db.Prepare(`DELETE FROM Network WHERE id = $1`)
	if err != nil {
		return err
	}

	db.listChannels, err = db.db.Prepare(`
		SELECT id, name, key, detached, detached_internal_msgid, relay_detached, reattach_on, detach_after,
		       detach_on
		FROM Channel
		WHERE network = $1`)
	if err != nil {
		return err
	}

	db.storeChannel, err = db.db.Prepare(`
		INSERT INTO Channel(network, name, key, detached, detached_internal_msgid, relay_detached, reattach_on,
		                    detach_after, detach_on)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
		ON CONFLICT (network, name)
		DO UPDATE SET network = $1, name = $2, key = $3, detached = $4, detached_internal_msgid = $5,
		              relay_detached = $6, reattach_on = $7, detach_after = $8, detach_on = $9
		RETURNING id`)
	if err != nil {
		return err
	}

	db.deleteChannel, err = db.db.Prepare(`DELETE FROM Channel WHERE id = $1`)
	if err != nil {
		return err
	}

	db.listDeliveryReceipts, err = db.db.Prepare(`
		SELECT id, target, client, internal_msgid
		FROM DeliveryReceipt
		WHERE network = $1`)
	if err != nil {
		return err
	}

	db.storeClientDeliveryReceipt, err = db.db.Prepare(`
		INSERT INTO DeliveryReceipt(network, target, client, internal_msgid)
		VALUES ($1, $2, $3, $4)
		ON CONFLICT (network, target, client)
		DO UPDATE SET internal_msgid = $4
		RETURNING id`)
	if err != nil {
		return err
	}

	return nil
}

func (db *PostgresDB) ListUsers() ([]User, error) {
	rows, err := db.listUsers.Query()
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var users []User
	for rows.Next() {
		var user User
		var password sql.NullString
		if err := rows.Scan(&user.ID, &user.Username, &password, &user.Admin); err != nil {
			return nil, err
		}
		user.Password = password.String
		users = append(users, user)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}

	return users, nil
}

func (db *PostgresDB) GetUser(username string) (*User, error) {
	user := &User{Username: username}

	var password, realname sql.NullString
	row := db.getUser.QueryRow(username)
	if err := row.Scan(&user.ID, &password, &user.Admin, &realname); err != nil {
		return nil, err
	}
	user.Password = password.String
	user.Realname = realname.String
	return user, nil
}

func (db *PostgresDB) StoreUser(user *User) error {
	password := toNullString(user.Password)
	realname := toNullString(user.Realname)
	err := db.storeUser.QueryRow(user.Username, password, user.Admin, realname).Scan(&user.ID)
	return err
}

func (db *PostgresDB) DeleteUser(id int64) error {
	_, err := db.deleteUser.Exec(id)
	return err
}

func (db *PostgresDB) ListNetworks(userID int64) ([]Network, error) {
	rows, err := db.listNetworks.Query(userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var networks []Network
	for rows.Next() {
		var net Network
		var name, username, realname, pass, connectCommands sql.NullString
		var saslMechanism, saslPlainUsername, saslPlainPassword sql.NullString
		err := rows.Scan(&net.ID, &name, &net.Addr, &net.Nick, &username, &realname,
			&pass, &connectCommands, &saslMechanism, &saslPlainUsername, &saslPlainPassword,
			&net.SASL.External.CertBlob, &net.SASL.External.PrivKeyBlob, &net.Enabled)
		if err != nil {
			return nil, err
		}
		net.Name = name.String
		net.Username = username.String
		net.Realname = realname.String
		net.Pass = pass.String
		if connectCommands.Valid {
			net.ConnectCommands = strings.Split(connectCommands.String, "\r\n")
		}
		net.SASL.Mechanism = saslMechanism.String
		net.SASL.Plain.Username = saslPlainUsername.String
		net.SASL.Plain.Password = saslPlainPassword.String
		networks = append(networks, net)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}

	return networks, nil
}

func (db *PostgresDB) StoreNetwork(userID int64, network *Network) error {
	netName := toNullString(network.Name)
	netUsername := toNullString(network.Username)
	realname := toNullString(network.Realname)
	pass := toNullString(network.Pass)
	connectCommands := toNullString(strings.Join(network.ConnectCommands, "\r\n"))

	var saslMechanism, saslPlainUsername, saslPlainPassword sql.NullString
	if network.SASL.Mechanism != "" {
		saslMechanism = toNullString(network.SASL.Mechanism)
		switch network.SASL.Mechanism {
		case "PLAIN":
			saslPlainUsername = toNullString(network.SASL.Plain.Username)
			saslPlainPassword = toNullString(network.SASL.Plain.Password)
			network.SASL.External.CertBlob = nil
			network.SASL.External.PrivKeyBlob = nil
		case "EXTERNAL":
			// keep saslPlain* nil
		default:
			return fmt.Errorf("soju: cannot store network: unsupported SASL mechanism %q", network.SASL.Mechanism)
		}
	}

	err := db.storeNetwork.QueryRow(userID, netName, network.Addr, network.Nick, netUsername, realname, pass, connectCommands,
		saslMechanism, saslPlainUsername, saslPlainPassword, network.SASL.External.CertBlob,
		network.SASL.External.PrivKeyBlob, network.Enabled).Scan(&network.ID)
	return err
}

func (db *PostgresDB) DeleteNetwork(id int64) error {
	_, err := db.deleteNetwork.Exec(id)
	return err
}

func (db *PostgresDB) ListChannels(networkID int64) ([]Channel, error) {
	rows, err := db.listChannels.Query(networkID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var channels []Channel
	for rows.Next() {
		var ch Channel
		var key, detachedInternalMsgID sql.NullString
		var detachAfter int64
		if err := rows.Scan(&ch.ID, &ch.Name, &key, &ch.Detached, &detachedInternalMsgID, &ch.RelayDetached, &ch.ReattachOn, &detachAfter, &ch.DetachOn); err != nil {
			return nil, err
		}
		ch.Key = key.String
		ch.DetachedInternalMsgID = detachedInternalMsgID.String
		ch.DetachAfter = time.Duration(detachAfter) * time.Second
		channels = append(channels, ch)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}

	return channels, nil
}

func (db *PostgresDB) StoreChannel(networkID int64, ch *Channel) error {
	key := toNullString(ch.Key)
	detachAfter := int64(math.Ceil(ch.DetachAfter.Seconds()))
	err := db.storeChannel.QueryRow(networkID, ch.Name, key, ch.Detached, toNullString(ch.DetachedInternalMsgID),
		ch.RelayDetached, ch.ReattachOn, detachAfter, ch.DetachOn).Scan(&ch.ID)
	return err
}

func (db *PostgresDB) DeleteChannel(id int64) error {
	_, err := db.deleteChannel.Exec(id)
	return err
}

func (db *PostgresDB) ListDeliveryReceipts(networkID int64) ([]DeliveryReceipt, error) {
	rows, err := db.listDeliveryReceipts.Query(networkID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var receipts []DeliveryReceipt
	for rows.Next() {
		var rcpt DeliveryReceipt
		var client sql.NullString
		if err := rows.Scan(&rcpt.ID, &rcpt.Target, &client, &rcpt.InternalMsgID); err != nil {
			return nil, err
		}
		rcpt.Client = client.String
		receipts = append(receipts, rcpt)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}

	return receipts, nil
}

func (db *PostgresDB) StoreClientDeliveryReceipts(networkID int64, client string, receipts []DeliveryReceipt) error {
	// No need for a transaction since all changes are atomic and don't break data coherence.
	for i := range receipts {
		rcpt := &receipts[i]
		err := db.storeClientDeliveryReceipt.QueryRow(networkID, rcpt.Target, toNullString(client), rcpt.InternalMsgID).Scan(&rcpt.ID)
		if err != nil {
			return err
		}
	}
	return nil
}
diff --git a/db_sqlite.go b/db_sqlite.go
index 7c0840a..1ff872e 100644
--- a/db_sqlite.go
+++ b/db_sqlite.go
@@ -142,8 +142,8 @@ type SqliteDB struct {
	db   *sql.DB
}

func OpenSqliteDB(driver, source string) (Database, error) {
	sqlSqliteDB, err := sql.Open(driver, source)
func OpenSqliteDB(source string) (Database, error) {
	sqlSqliteDB, err := sql.Open("sqlite3", source)
	if err != nil {
		return nil, err
	}
diff --git a/doc/soju.1.scd b/doc/soju.1.scd
index 6838bbc..c3779f0 100644
--- a/doc/soju.1.scd
+++ b/doc/soju.1.scd
@@ -106,8 +106,23 @@ The following directives are supported:
*tls* <cert> <key>
	Enable TLS support. The certificate and the key files must be PEM-encoded.

*db* sqlite3 <path>
	Set the SQLite database path (default: "soju.db" in the current directory).
*db* <driver> <path>
	Set the database location for user, network and channel storage.  By
	default, a _sqlite3_ database is opened in "./soju.db".

	Supported drivers:

	- _sqlite3_ expects <path> to point to the SQLite file
	- _postgres_ expects <path> to be a space-separated list of _key=value_
	  parameters, e.g.

		db postgres "user=soju dbname=soju host=/run/postgres sslmode=disable"

		Please note that _sslmode_ defaults to _require_.

		See the documentation of your version of PostgreSQL for the full list of
		allowed parameters.  The current version can be found here:
		<https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING>.

*log* fs <path>
	Path to the bouncer logs root directory, or empty to disable logging. By
diff --git a/go.mod b/go.mod
index 3c3072e..6634d67 100644
--- a/go.mod
+++ b/go.mod
@@ -7,6 +7,7 @@ require (
	git.sr.ht/~sircmpwn/go-bare v0.0.0-20210406120253-ab86bc2846d9
	github.com/emersion/go-sasl v0.0.0-20200509203442-7bfe0ed36a21
	github.com/klauspost/compress v1.13.5 // indirect
	github.com/lib/pq v1.10.3
	github.com/mattn/go-sqlite3 v1.14.8
	github.com/pires/go-proxyproto v0.6.1
	golang.org/x/crypto v0.0.0-20210817164053-32db794688a5
diff --git a/go.sum b/go.sum
index dfce806..0497d01 100644
--- a/go.sum
+++ b/go.sum
@@ -43,6 +43,8 @@ github.com/klauspost/compress v1.13.5 h1:9O69jUPDcsT9fEm74W92rZL9FQY7rCdaXVneq+y
github.com/klauspost/compress v1.13.5/go.mod h1:/3/Vjq9QcHkK5uEr5lBEmyoZ1iFhe47etQ6QUkpK6sk=
github.com/leodido/go-urn v1.2.0 h1:hpXL4XnriNwQ/ABnpepYM/1vCLWNDfUNts8dX3xTG6Y=
github.com/leodido/go-urn v1.2.0/go.mod h1:+8+nEpDfqqsY+g338gtMEUOtuK+4dEMhiQEgxpxOKII=
github.com/lib/pq v1.10.3 h1:v9QZf2Sn6AmjXtQeFpdoq/eaNtYP6IN+7lcrygsIAtg=
github.com/lib/pq v1.10.3/go.mod h1:AlVN5x4E4T544tWzH6hKfbfQvm3HdbOxrmggDNAPY9o=
github.com/mattn/go-isatty v0.0.12 h1:wuysRhFDzyxgEmMf5xjvJ2M9dZoWAXNNr5LSBS7uHXY=
github.com/mattn/go-isatty v0.0.12/go.mod h1:cbi8OIDigv2wuxKPP5vlRcQ1OAZbq2CE4Kysco4FUpU=
github.com/mattn/go-sqlite3 v1.14.8 h1:gDp86IdQsN/xWjIEmr9MF6o9mpksUgh0fu+9ByFxzIU=
-- 
2.33.0

[PATCH 2/2] Add a script to migrate from SQLite to PostgreSQL

Details
Message ID
<20210913213437.40552-2-hubert@hirtz.pm>
In-Reply-To
<20210913213437.40552-1-hubert@hirtz.pm> (view parent)
DKIM signature
pass
Download raw message
Patch: +129 -0
---

Limitation: PostgreSQL seems to handle neither quoted values in CSV, nor
multi-char delimiters, so the script will fail on weird content and
passwords...

 contrib/sqlite2pg.sh | 129 +++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 129 insertions(+)
 create mode 100755 contrib/sqlite2pg.sh

diff --git a/contrib/sqlite2pg.sh b/contrib/sqlite2pg.sh
new file mode 100755
index 0000000..5d65ee5
--- /dev/null
+++ b/contrib/sqlite2pg.sh
@@ -0,0 +1,129 @@
#!/bin/sh -eu

# Migrate from SQLite3 to PostgreSQL

[ $# -lt 2 ] && {
	echo "usage: sqlite2pg.sh SQLITE_PATH DBNAME [DBUSER]"
	echo
	echo "Refer to soju(1) for details."
	exit 1
}

SQLITE_PATH=$1
shift

SQLITE_VERSION=$(sqlite3 -list -noheader "$SQLITE_PATH" "PRAGMA user_version;")
[ "$SQLITE_VERSION" -lt 13 ] && {
	echo "Unsupported SQLite schema version: $SQLITE_VERSION"
	echo "Supported version is #13"
	echo
	echo "Run 'go run ./cmd/sojuctl help' to update the schema"
	exit 1
}
[ "$SQLITE_VERSION" -gt 13 ] && {
	echo "Unsupported SQLite schema version: $SQLITE_VERSION"
	echo "Supported version is #13"
	echo
	printf "Continue? [y/N] "
	read yn
        case $yn in
	[Yy] ) echo "Continuing";;
	* ) echo "Aborting"; exit 1;;
	esac
}

psql "$@" <<EOF
CREATE TABLE IF NOT EXISTS Config (
	id SMALLINT PRIMARY KEY,
	version INTEGER NOT NULL,
	CHECK(id = 1)
);
INSERT INTO Config (id, version) VALUES (1, 1) ON CONFLICT DO NOTHING;

CREATE TABLE IF NOT EXISTS "User" (
	id SERIAL PRIMARY KEY,
	username VARCHAR(255) NOT NULL UNIQUE,
	password VARCHAR(255),
	admin BOOLEAN NOT NULL DEFAULT FALSE,
	realname VARCHAR(255)
);

CREATE TABLE IF NOT EXISTS Network (
	id SERIAL PRIMARY KEY,
	name VARCHAR(255),
	"user" INTEGER NOT NULL REFERENCES "User"(id) ON DELETE CASCADE,
	addr VARCHAR(255) NOT NULL,
	nick VARCHAR(255) NOT NULL,
	username VARCHAR(255),
	realname VARCHAR(255),
	pass VARCHAR(255),
	connect_commands VARCHAR(1023),
	sasl_mechanism VARCHAR(255),
	sasl_plain_username VARCHAR(255),
	sasl_plain_password VARCHAR(255),
	sasl_external_cert BYTEA DEFAULT NULL,
	sasl_external_key BYTEA DEFAULT NULL,
	enabled BOOLEAN NOT NULL DEFAULT TRUE,
	FOREIGN KEY("user") REFERENCES "User"(id),
	UNIQUE("user", addr, nick),
	UNIQUE("user", name)
);

CREATE TABLE IF NOT EXISTS Channel (
	id SERIAL PRIMARY KEY,
	network INTEGER NOT NULL REFERENCES Network(id) ON DELETE CASCADE,
	name VARCHAR(255) NOT NULL,
	key VARCHAR(255),
	detached BOOLEAN NOT NULL DEFAULT FALSE,
	detached_internal_msgid VARCHAR(255),
	relay_detached INTEGER NOT NULL DEFAULT 0,
	reattach_on INTEGER NOT NULL DEFAULT 0,
	detach_after INTEGER NOT NULL DEFAULT 0,
	detach_on INTEGER NOT NULL DEFAULT 0,
	UNIQUE(network, name)
);

CREATE TABLE IF NOT EXISTS DeliveryReceipt (
	id SERIAL PRIMARY KEY,
	network INTEGER NOT NULL REFERENCES Network(id) ON DELETE CASCADE,
	target VARCHAR(255) NOT NULL,
	client VARCHAR(255),
	internal_msgid VARCHAR(255) NOT NULL,
	UNIQUE(network, target, client)
);
EOF

TMP=$(mktemp)

# postgresql doesn't support quoted values in CSV xD
sqlite3 -noheader -csv  -separator "&" "$SQLITE_PATH" "
SELECT id, username, password, admin, realname
FROM User" >"$TMP"
psql "$@" <<EOF
\COPY "User" (id, username, password, admin, realname) FROM '$TMP' DELIMITER '&'
EOF

sqlite3 -noheader -csv  -separator "&" "$SQLITE_PATH" "
SELECT id, name, user, addr, nick, username, realname, pass, connect_commands, sasl_mechanism,
       sasl_plain_username, sasl_plain_password, sasl_external_cert, sasl_external_key, enabled
FROM Network" >"$TMP"
psql "$@" <<EOF
\COPY Network (id, name, "user", addr, nick, username, realname, pass, connect_commands, sasl_mechanism, sasl_plain_username, sasl_plain_password, sasl_external_cert, sasl_external_key, enabled) FROM '$TMP' DELIMITER '&'
EOF

sqlite3 -noheader -csv  -separator "&" "$SQLITE_PATH" "
SELECT id, network, name, key, detached, detached_internal_msgid, relay_detached, reattach_on,
       detach_after, detach_on
FROM Channel" >"$TMP"
psql "$@" <<EOF
\COPY Channel (id, network, name, key, detached, detached_internal_msgid, relay_detached, reattach_on, detach_after, detach_on) FROM '$TMP' DELIMITER '&'
EOF

sqlite3 -noheader -csv  -separator "&" "$SQLITE_PATH" "
SELECT id, network, target, client, internal_msgid
FROM DeliveryReceipt" >"$TMP"
psql "$@" <<EOF
\COPY DeliveryReceipt (id, network, target, client, internal_msgid) FROM '$TMP' DELIMITER '&'
EOF

rm "$TMP"
-- 
2.33.0
minus
Details
Message ID
<8ce2e61e-8941-d1df-0bf0-60cc9d4cd202@mnus.de>
In-Reply-To
<20210913213437.40552-1-hubert@hirtz.pm> (view parent)
DKIM signature
pass
Download raw message
On 13/09/2021 23.34, Hubert Hirtz wrote:
> - All statements are prepared beforehand for performance, and it also
>    allowed us to read Java-levels of boilerplate.
That's really not nice to read. The $1, … parameters to SQL queries are 
bad enough as it is, having the query away from the parameters being 
passed is just awful. I'd probably just skip on the prepared statements, 
I doubt there's ever a notable performance difference. Alternatively 
going with a `if db.getUser != nil { db.getUser = db.db.Prepare(…) }` 
inside db.GetUser seems like a more reasonable approach to the problem 
to me.
Details
Message ID
<20210915102056.28a6f515@vroom.localdomain>
In-Reply-To
<8ce2e61e-8941-d1df-0bf0-60cc9d4cd202@mnus.de> (view parent)
DKIM signature
pass
Download raw message
On Tue, 14 Sep 2021 15:35:00 +0200, minus wrote:
> On 13/09/2021 23.34, Hubert Hirtz wrote:
> > - All statements are prepared beforehand for performance, and it also
> >    allowed us to read Java-levels of boilerplate.  
> That's really not nice to read. The $1, … parameters to SQL queries are 
> bad enough as it is, having the query away from the parameters being 
> passed is just awful. I'd probably just skip on the prepared statements, 
> I doubt there's ever a notable performance difference. 

I don't have any opinion on that, nor made measurements.  It just
seemed like a free improvement.  If emersion doesn't want prepared
statements then I'll remove them.

> Alternatively 
> going with a `if db.getUser != nil { db.getUser = db.db.Prepare(…) }` 
> inside db.GetUser seems like a more reasonable approach to the problem 
> to me.

This would require additional locking and prevent concurrent execution
of SQL queries.  Instead, SQL statement strings could be placed just
above where they're used, like so:

    const listUsers = `
        SELECT users
        FROM database`

    func (db *PostgresDB) ListUsers() ([]User, error) {
        rows, err := db.listUsers.Query()
        ...
    }

In any case, if emersion doesn't want prepared statements then I'll
remove them.

Re: [PATCH 2/2] Add a script to migrate from SQLite to PostgreSQL

Details
Message ID
<oI3AHq8JIFCysin8nD6RsF2e4YRMv7hvriX1R_XdJCizgL-KHtWvL4C9KR57tbRYsZkDHVMoDk3sj5dc19ymLIBwsS7GjG3ecp9cJ1OePrE=@emersion.fr>
In-Reply-To
<20210913213437.40552-2-hubert@hirtz.pm> (view parent)
DKIM signature
pass
Download raw message
Maybe a better approach would be a Go binary which uses soju's database
abstraction for the migration…
Reply to thread Export thread (mbox)