prosody-hg: plugins/mod_storage_sql.lua history

mod_storage_sql: optimization: use table.move for copying items between arrays

Sat, 06 Dec 2025 15:58:40 +0000

mod_storage_sql: optimization: use table.move for copying items between arrays

Merge 13.0->trunk

Tue, 30 Sep 2025 18:20:17 +0200

Merge 13.0->trunk

mod_storage_sql: Switch mutating transactions to new API

Mon, 19 Aug 2024 00:11:06 +0200

mod_storage_sql: Switch mutating transactions to new API

This is just semantics at this point. A future commit will indicate
write transactions with BEGIN IMMEDIATE for SQLite3.

mod_storage_sql: Set configurable wait time for locked SQLite3 database

Tue, 30 Sep 2025 16:11:49 +0200

mod_storage_sql: Set configurable wait time for locked SQLite3 database

This tells SQLite3 to wait up to 1000 milliseconds (i.e. 1 second) for a
locked database file to become unlocked before giving up.

mod_storage_sql: Assert that serialization of archive:set() payload succeeds

Tue, 22 Jul 2025 16:34:27 +0100

mod_storage_sql: Assert that serialization of archive:set() payload succeeds

Potentially encountered on a production server - the assert() should prevent
more subtle failure modes, and help us pinpoint the root cause.

mod_storage_sql: Also retrieve all SQLite3 indices

Wed, 16 Apr 2025 15:35:07 +0200

mod_storage_sql: Also retrieve all SQLite3 indices

Missed this in 2558be2daaca

mod_storage_sql: Retrieve all indices to see if the new one exists

Fri, 11 Apr 2025 09:45:04 +0200

mod_storage_sql: Retrieve all indices to see if the new one exists

Otherwise it warns about it being missing, even if it exists.

mod_storage_sql: Mark unused argument as such

Thu, 10 Apr 2025 00:22:44 +0200

mod_storage_sql: Mark unused argument as such

Make `make lint` happy again

mod_storage_sql: Handle failure to deploy new UNIQUE index

Wed, 09 Apr 2025 18:27:42 +0200

mod_storage_sql: Handle failure to deploy new UNIQUE index

Somehow a user ended up with duplicate data preventing creation of the
new unique index needed for UPSERT (see 3ec48555b773).

This should eventually self-heal #1918 if the duplicate data is replaced
by the older DELETE + INSERT method.

Without any index at all, it will be slower.

mod_storage_sql: Add shell command to create tables and indices (again)

Wed, 09 Apr 2025 15:06:48 +0200

mod_storage_sql: Add shell command to create tables and indices (again)

This is meant as a way to diagnose e.g. issues creating indices.

It would have been nice to capture e.g. PostgreSQL notices, but LuaDBI
would need support for this first, see https://github.com/mwild1/luadbi/issues/62

mod_storage_sql: Delay showing SQL library error until attempted load

Mon, 07 Apr 2025 20:23:00 +0200

mod_storage_sql: Delay showing SQL library error until attempted load

This should ensure that e.g. failure to load LuaSQLite3 is not logged
unless it is needed, since module failures are very verbose.

Closes #1919

mod_storage_sql: Drop legacy index without confirmation to ease upgrades

Sun, 23 Mar 2025 12:15:16 +0100

mod_storage_sql: Drop legacy index without confirmation to ease upgrades

Deleting one index should be safe enough to do without requiring a
explicitly upgrading during downtime. People had trouble with this.

mod_storage_sql: Fix indentation

Wed, 19 Mar 2025 16:13:32 +0100

mod_storage_sql: Fix indentation

Off-by-one in autoindent after `if not success then` since 3ec48555b773

mod_storage_sql: Fix tests for SQLite3

Sat, 08 Feb 2025 12:55:40 +0100

mod_storage_sql: Fix tests for SQLite3

Tests does not run the code that initializes `sqlite_version`

mod_storage_sql: Don't treat Enter or Ctrl-C as yes in upgrade command

Thu, 23 Jan 2025 22:32:44 +0100

mod_storage_sql: Don't treat Enter or Ctrl-C as yes in upgrade command

It seems to imply that No is the default, so it shouldn't continue doing
the Yes action unless you actually press Y

mod_storage_sql: Detect SQLite3 without UPSERT (or SQLCipher 3.x)

Thu, 23 Jan 2025 19:33:05 +0100

mod_storage_sql: Detect SQLite3 without UPSERT (or SQLCipher 3.x)

SQLCipher v3.4.1 (the version in Debian 12) is based on SQLite3 v3.15.2,
while UPSERT support was introduced in SQLite3 v3.24.0

This check was not needed before because we v3.24.0 has not been in a
version of Debian we support for a long, long time.

Note however that SQLCipher databases are not compatible across major
versions, upgrading from v3.x to v4.x requires executing a migration.
Attempts at making `prosodyctl mod_storage_sql upgrade` perform such a
migration has not been successful.

Executing the following in the `sqlcipher` tool should do the migration:

PRAGMA key = '';
PRAGMA cipher_migrate;

mod_storage_sql: Remove the word 'error' from debug messages

Wed, 08 Jan 2025 22:30:30 +0100

mod_storage_sql: Remove the word 'error' from debug messages

The word 'error' anywhere, especially in harmless debug messages, are
too often interpreted as fatal errors my some users, so best avoid that
word. These look too scary as it is, being tracebacks.

mod_storage_sql: Use UUIDv7 as keys

Mon, 17 Jul 2023 04:30:35 +0200

mod_storage_sql: Use UUIDv7 as keys

Potentially allows sorting on those directly as they will be in
increasing order.

mod_storage_sql: Spell out missing dependencies

Sat, 22 Jul 2023 16:15:48 +0200

mod_storage_sql: Spell out missing dependencies

Using util.dependencies appeared to cause problems with running tests in
Busted, so this also removes that and uses pcall directly.

mod_storage_sql: Pass variables as arguments instead of upvalues

Sat, 22 Jul 2023 15:22:54 +0200

mod_storage_sql: Pass variables as arguments instead of upvalues

Probably a workaround for the lack of argument passing when using xpcall
in Lua 5.1, no longer relevant.

plugins: Use boolean config method in some places

Tue, 18 Jul 2023 12:38:16 +0200

plugins: Use boolean config method in some places

Because it makes sense and improves feedback via logging

mod_storage_sql: Use integer config option for cache size

Tue, 18 Jul 2023 11:53:31 +0200

mod_storage_sql: Use integer config option for cache size

Missed this one, was probably only looking for get_option_number

mod_storage_sql: Use config enum for 'sqlite_tune'

Tue, 18 Jul 2023 11:53:12 +0200

mod_storage_sql: Use config enum for 'sqlite_tune'

mod_storage_sql: Allow higher precision timestamps in SQLite3

Mon, 17 Jul 2023 04:52:50 +0200

mod_storage_sql: Allow higher precision timestamps in SQLite3

Since it doesn't actually do strict typing :)

plugins: Use integer config API with interval specification where sensible

Mon, 17 Jul 2023 01:38:54 +0200

plugins: Use integer config API with interval specification where sensible

Many of these fall into a few categories:
- util.cache size, must be >= 1
- byte or item counts that logically can't be negative
- port numbers that should be in 1..0xffff

mod_storage_sql: Remove completed TODO (testing UPSERT on PostgreSQL)

Wed, 12 Jul 2023 22:51:37 +0200

mod_storage_sql: Remove completed TODO (testing UPSERT on PostgreSQL)

mod_storage_sql: Fix column name in index check for PostgreSQL

Sat, 17 Jun 2023 13:37:33 +0200

mod_storage_sql: Fix column name in index check for PostgreSQL

Forgot to change the column name in 9a7523ea45cb

mod_storage_sql: Adjust indentation to align with surrounding code

Sat, 17 Jun 2023 13:36:42 +0200

mod_storage_sql: Adjust indentation to align with surrounding code

mod_storage_sql: Only remove old index if it exists

Sat, 17 Jun 2023 00:22:45 +0200

mod_storage_sql: Only remove old index if it exists

Avoids an error if the upgrade is performed twice..

mod_storage_sql: Be more specific when checking for old index in SQLite3

Sat, 17 Jun 2023 00:00:21 +0200

mod_storage_sql: Be more specific when checking for old index in SQLite3

Prevents false positives in the odd case where something other than an
index with this name might exist.

mod_storage_sql: Improve check for old table index on PostgreSQL

Fri, 16 Jun 2023 23:57:15 +0200

mod_storage_sql: Improve check for old table index on PostgreSQL

The "pg_indexes" view is much simpler to inspect than "pg_class"

mod_storage_sql: Enable UPSERT with PostgreSQL

Fri, 16 Jun 2023 13:03:34 +0200

mod_storage_sql: Enable UPSERT with PostgreSQL

Tested. Works.

mod_storage_sql: Add some TODO comments for future UPSERT work

Sun, 11 Jun 2023 20:25:50 +0200

mod_storage_sql: Add some TODO comments for future UPSERT work

mod_storage_sql: Do not keep track of quota when no quota is set

Sun, 11 Jun 2023 20:05:32 +0200

mod_storage_sql: Do not keep track of quota when no quota is set

No point in doing this expensive O(n) query if the result is not used
for anything.

Will still cache the total item count if an explicit query for this is
performed, then try to keep it updated with new items added. Will likely
forget eventually tho.

mod_storage_sql: Add setting to tune SQLite3 performance vs safety

Sun, 11 Jun 2023 17:04:11 +0200

mod_storage_sql: Add setting to tune SQLite3 performance vs safety

Notably the default journal_mode of DELETE is somewhat slow, some users
might want to catch up to the amazing performance of internal storage.

mod_storage_sql: Record all SQLite3 compile options for potential use

Sat, 10 Jun 2023 14:01:56 +0200

mod_storage_sql: Record all SQLite3 compile options for potential use

Knowing what features are available could be useful for future
experiments. For example, with the JSON module or full text search.

mod_storage_sql: Compose a keyval+ store out of keyval and map store methods

Sat, 10 Jun 2023 16:39:48 +0200

mod_storage_sql: Compose a keyval+ store out of keyval and map store methods

Removes the need for the shim in storagemanager.

The methods only really access the 'store' property of the first (self)
argument, so this is safe.

mod_storage_sql: Add UPSERT support

Sat, 10 Jun 2023 15:44:09 +0200

mod_storage_sql: Add UPSERT support

Currently limited to SQLite3 for lack of testing on other databases.

Adds a migration to replace the non-UNIQUE prosody_index, renaming it
prosody_unique_index since ALTER INDEX does not seem to be portable.

plugins: Prefix module imports with prosody namespace

Fri, 24 Mar 2023 13:15:28 +0100

plugins: Prefix module imports with prosody namespace

mod_storage_sql: Remove module status on unknown 'debug' level

Tue, 31 Jan 2023 07:52:33 +0100

mod_storage_sql: Remove module status on unknown 'debug' level

Expected this to be translated to 'core', but it logs an error instead.
See previous commit.

mod_storage_sql: Silence luacheck warning

Mon, 30 Jan 2023 00:40:47 +0100

mod_storage_sql: Silence luacheck warning

mod_storage_sql: Record connection to database as module status

Mon, 30 Jan 2023 00:38:26 +0100

mod_storage_sql: Record connection to database as module status

Allows retrieving this in e.g. a health reporting module

Thanks pfak

mod_storage_sql: Support SQLite3 without LuaDBI

Thu, 19 Jan 2023 16:56:12 +0100

mod_storage_sql: Support SQLite3 without LuaDBI

Merge 0.12->trunk

Fri, 06 Jan 2023 23:53:50 +0100

Merge 0.12->trunk

mod_storage_sql: Fix #1639

Sun, 01 Jan 2023 16:20:58 +0100

mod_storage_sql: Fix #1639

Patch by Peter Kieser

Merge 0.12->trunk

Thu, 29 Dec 2022 18:28:50 +0100

Merge 0.12->trunk

mod_storage_sql: Don't avoid initialization under prosodyctl (fix #1787)

Thu, 29 Dec 2022 18:06:35 +0100

mod_storage_sql: Don't avoid initialization under prosodyctl (fix #1787)

Fixes `prosodyctl adduser` etc.

Prior to d580e6a57cbb the line did nothing.

Sometimes storage in the prosodyctl context does cause weirdness, as it
is not in a host context, but rather a variant of global.

mod_storage_sql: Strip timestamp precision in queries to fix error (thanks muppeth)

Wed, 07 Sep 2022 12:27:12 +0200

mod_storage_sql: Strip timestamp precision in queries to fix error (thanks muppeth)

Fixes
Error in SQL transaction: Error executing statement parameters: ERROR: invalid input syntax for integer

This was handled for INSERT in 9524bb7f3944 but not SELECT.

mod_storage_sql: Drop archive timestamp precision pending schema update

Sun, 14 Aug 2022 17:29:39 +0200

mod_storage_sql: Drop archive timestamp precision pending schema update

The "when" column is an INTEGER which will probably be unhappy about
storing higher precision timestamps, so we keep the older behavior for
now.

Merge 0.12->trunk

Fri, 29 Jul 2022 17:10:31 +0200

Merge 0.12->trunk

mod_storage_sql: Fix summary API with Postgres (fixes #1766)

Fri, 22 Jul 2022 19:09:50 +0200

mod_storage_sql: Fix summary API with Postgres (fixes #1766)

The ORDER BY and LIMIT clauses are not needed and don't even make much
sense. This part was most likely a leftover from the :find method.

Tested with sqlite and postgres 14

Merge 0.12->trunk

Wed, 27 Jul 2022 00:10:08 +0200

Merge 0.12->trunk

mod_storage_sql: Fix bypass of load procedure under prosodyctl

Tue, 26 Jul 2022 00:39:16 +0200

mod_storage_sql: Fix bypass of load procedure under prosodyctl

There's no 'prosody.prosodyctl' property other than this one, introduced
in 6216743c188c in 2015.

Guessing that the intent was to skip this when running as a prosodyctl
command. The module.command code does its own version of this
initialization, so this seems likely.

Thanks raja for noticing

compat: Remove handling of Lua 5.1 location of 'unpack' function

Mon, 11 Jul 2022 19:07:38 +0200

compat: Remove handling of Lua 5.1 location of 'unpack' function

mod_storage_sql: Remove Lua 5.1 compatibility hack

Sat, 02 Jul 2022 17:30:06 +0200

mod_storage_sql: Remove Lua 5.1 compatibility hack

Part of #1600

mod_storage_sql: Return cached total where it makes sense

Tue, 30 Nov 2021 16:26:01 +0100

mod_storage_sql: Return cached total where it makes sense

This should skip the summary SQL query when not needed, ie when the
cached value can be used directly.

mod_storage_sql: Adjust handling of 'total' for multi-id queries

Tue, 30 Nov 2021 15:58:23 +0100

mod_storage_sql: Adjust handling of 'total' for multi-id queries

Otherwise a query for a set of ids may cause that value to be cached,
despite only covering a subset.

mod_storage_sql: Fix traceback on multi-user deletion

Tue, 30 Nov 2021 15:24:17 +0100

mod_storage_sql: Fix traceback on multi-user deletion

jid.join(true, ...) fails.

Since we don't know which users this affects, best to clear the whole
cache.

mod_storage_sql: Expose multi-user deletion capability

Tue, 30 Nov 2021 01:21:02 +0100

mod_storage_sql: Expose multi-user deletion capability

archive_store:delete(true) deletes messages for all users, which would
allow some simplification when this is supported.

mod_storage_sql: Fix for move of yes/no function (Thanks Kasim)

Mon, 21 Jun 2021 17:31:17 +0200

mod_storage_sql: Fix for move of yes/no function (Thanks Kasim)