From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: X-Spam-Checker-Version: SpamAssassin 3.4.6 (2021-04-09) on dcvr.yhbt.net X-Spam-Level: X-Spam-ASN: X-Spam-Status: No, score=-4.2 required=3.0 tests=ALL_TRUSTED,AWL,BAYES_00, DKIM_SIGNED,DKIM_VALID,DKIM_VALID_AU,DKIM_VALID_EF shortcircuit=no autolearn=ham autolearn_force=no version=3.4.6 Received: from localhost (dcvr.yhbt.net [127.0.0.1]) by dcvr.yhbt.net (Postfix) with ESMTP id 6DA7E1F4D3 for ; Wed, 4 Dec 2024 19:39:15 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=80x24.org; s=selector1; t=1733341155; bh=6nMqKtox0c9jm7dmc+/66QSsUcdooTlXk3jqntybZs4=; h=From:To:Subject:Date:In-Reply-To:References:From; b=tFo4KhyekJZf2LU+ECMpJ83lJDXPVqqj41VuAy1tUImRY7tIXOaIVFgLXA+OIemRO etIpYy3okZ4R76t/6T1X/dylmviXIpj3ErWY9FRFfz6xL0cidGnA5I+oXHCBQMWmf3 lfOvWW9CfcC9p3CwhDufXw7n6fpLo91hxkfHk5Dk= From: Eric Wong To: meta@public-inbox.org Subject: [PATCH 2/3] sqlite: use `BLOB' column type instead of `VARBINARY' Date: Wed, 4 Dec 2024 19:39:13 +0000 Message-ID: <20241204193914.3227868-3-e@80x24.org> In-Reply-To: <20241204193914.3227868-1-e@80x24.org> References: <20241204193914.3227868-1-e@80x24.org> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit List-Id: `VARBINARY' isn't actually a documented type for SQLite but rather the result of MySQL infecting my mind decades ago. `BLOB' gives it the proper affinity and probably makes it easier for 3rd-party and one-off scripts to deal with such columns and should also make things more familiar to existing users. Surprisingly, this appears to have no functional change with forward or backwards compatibility since we ->bind_param binary data with SQL_BLOB on INSERTs anyways to prevent the flexible typing of SQLite from trying to guess types for us. --- lib/PublicInbox/LeiMailSync.pm | 8 ++++---- lib/PublicInbox/OverIdx.pm | 4 ++-- lib/PublicInbox/POP3D.pm | 4 ++-- lib/PublicInbox/SharedKV.pm | 4 ++-- 4 files changed, 10 insertions(+), 10 deletions(-) diff --git a/lib/PublicInbox/LeiMailSync.pm b/lib/PublicInbox/LeiMailSync.pm index d23f6b4b..fc7963a1 100644 --- a/lib/PublicInbox/LeiMailSync.pm +++ b/lib/PublicInbox/LeiMailSync.pm @@ -61,13 +61,13 @@ sub create_tables { $dbh->do(<<''); CREATE TABLE IF NOT EXISTS folders ( fid INTEGER PRIMARY KEY, - loc VARBINARY NOT NULL, /* URL;UIDVALIDITY=$N or $TYPE:/pathname */ + loc BLOB NOT NULL, /* URL;UIDVALIDITY=$N or $TYPE:/pathname */ UNIQUE (loc) ) $dbh->do(<<''); CREATE TABLE IF NOT EXISTS blob2num ( - oidbin VARBINARY NOT NULL, + oidbin BLOB NOT NULL, fid INTEGER NOT NULL, /* folder ID */ uid INTEGER NOT NULL, /* NNTP article number, IMAP UID, MH number */ /* not UNIQUE(fid, uid), since we may have broken servers */ @@ -80,9 +80,9 @@ CREATE INDEX IF NOT EXISTS idx_fid_uid ON blob2num(fid,uid) $dbh->do(<<''); CREATE TABLE IF NOT EXISTS blob2name ( - oidbin VARBINARY NOT NULL, + oidbin BLOB NOT NULL, fid INTEGER NOT NULL, /* folder ID */ - name VARBINARY NOT NULL, /* Maildir basename, JMAP blobId */ + name BLOB NOT NULL, /* Maildir basename, JMAP blobId */ /* not UNIQUE(fid, name), since we may have broken software */ UNIQUE (oidbin, fid, name) ) diff --git a/lib/PublicInbox/OverIdx.pm b/lib/PublicInbox/OverIdx.pm index 10cf8c39..dea6f0fc 100644 --- a/lib/PublicInbox/OverIdx.pm +++ b/lib/PublicInbox/OverIdx.pm @@ -389,7 +389,7 @@ CREATE TABLE IF NOT EXISTS over ( sid INTEGER, /* Subject ID (IMAP ORDEREDSUBJECT "threading") */ ts INTEGER, /* IMAP INTERNALDATE (Received: header, git commit time) */ ds INTEGER, /* RFC-2822 sent Date: header, git author time */ - ddd VARBINARY /* doc-data-deflated (->to_doc_data, ->load_from_data) */ + ddd BLOB /* doc-data-deflated (->to_doc_data, ->load_from_data) */ ) $dbh->do('CREATE INDEX IF NOT EXISTS idx_tid ON over (tid)'); @@ -543,7 +543,7 @@ CREATE TABLE IF NOT EXISTS xref3 ( docid INTEGER NOT NULL, /* <=> over.num */ ibx_id INTEGER NOT NULL, /* <=> inboxes.ibx_id */ xnum INTEGER NOT NULL, /* NNTP article number in ibx */ - oidbin VARBINARY NOT NULL, /* 20-byte SHA-1 or 32-byte SHA-256 */ + oidbin BLOB NOT NULL, /* 20-byte SHA-1 or 32-byte SHA-256 */ UNIQUE (docid, ibx_id, xnum, oidbin) ) diff --git a/lib/PublicInbox/POP3D.pm b/lib/PublicInbox/POP3D.pm index bd440434..1898c89d 100644 --- a/lib/PublicInbox/POP3D.pm +++ b/lib/PublicInbox/POP3D.pm @@ -93,7 +93,7 @@ sub create_state_tables ($$) { $dbh->do(<<''); # map publicinbox..newsgroup to integers CREATE TABLE IF NOT EXISTS newsgroups ( newsgroup_id INTEGER PRIMARY KEY NOT NULL, - newsgroup VARBINARY NOT NULL, + newsgroup BLOB NOT NULL, UNIQUE (newsgroup) ) # the $NEWSGROUP_NAME.$SLICE_INDEX is part of the POP3 username; @@ -108,7 +108,7 @@ CREATE TABLE IF NOT EXISTS mailboxes ( $dbh->do(<<''); # actual users are differentiated by their UUID CREATE TABLE IF NOT EXISTS users ( user_id INTEGER PRIMARY KEY NOT NULL, - uuid VARBINARY NOT NULL, + uuid BLOB NOT NULL, last_seen INTEGER NOT NULL, /* to expire idle accounts */ UNIQUE (uuid) ) diff --git a/lib/PublicInbox/SharedKV.pm b/lib/PublicInbox/SharedKV.pm index 51ece48d..062d5e3e 100644 --- a/lib/PublicInbox/SharedKV.pm +++ b/lib/PublicInbox/SharedKV.pm @@ -32,8 +32,8 @@ sub dbh { ($opt->{journal_mode} // 'WAL')); $dbh->do(<<''); CREATE TABLE IF NOT EXISTS kv ( - k VARBINARY PRIMARY KEY NOT NULL, - v VARBINARY NOT NULL, + k BLOB PRIMARY KEY NOT NULL, + v BLOB NOT NULL, UNIQUE (k) )