Mercurial > prosody-wiki

annotate mod_mam_sql.wiki @ 416:b95d737dc014

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
show and tell, example invocation and description of fields
author Kim Alvefur <zash@zash.se>
date Fri, 17 Jan 2014 00:55:52 +0100
parents 46342407997c
children 4d4d81fa31fa
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
343
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
1 #summary XEP-0313: Message Archive Management using SQL
386
46342407997c Add Deprecated tag, no longer supported
Kim Alvefur <zash@zash.se>
parents: 352
diff changeset
2 #labels Stage-Alpha, Deprecated
343
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
3
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
4 = Introduction =
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
5
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
6 Implementation of [http://xmpp.org/extensions/xep-0313.html XEP-0313: Message Archive Management] backed by a SQL database. Like [mod_mam], but using SQL.
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
7
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
8
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
9 = Details =
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
10
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
11 See [mod_mam] for details.
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
12
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
13 = Usage =
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
14
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
15 First copy the module to the prosody plugins directory.
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
16
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
17 Then add "mam_sql" to your modules_enabled list:
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
18 {{{
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
19 modules_enabled = {
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
20 -- ...
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
21 "mam_sql",
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
22 -- ...
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
23 }
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
24 }}}
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
25
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
26 You should probably run the SQL to create the archive table/indexes:
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
27
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
28 {{{
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
29 CREATE TABLE `prosodyarchive` (
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
30 `host` TEXT,
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
31 `user` TEXT,
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
32 `store` TEXT,
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
33 `id` INTEGER PRIMARY KEY AUTOINCREMENT,
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
34 `when` INTEGER,
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
35 `with` TEXT,
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
36 `resource` TEXT,
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
37 `stanza` TEXT
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
38 );
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
39 CREATE INDEX `hus` ON `prosodyarchive` (`host`, `user`, `store`);
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
40 CREATE INDEX `with` ON `prosodyarchive` (`with`);
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
41 CREATE INDEX `thetime` ON `prosodyarchive` (`when`);
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
42 }}}
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
43
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
44 (*NOTE*: I ran the following SQL to initialize the table/indexes on MySQL):
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
45
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
46 {{{
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
47 CREATE TABLE prosodyarchive (
351
817e175c37df Fix the suggested mysql schema
Kim Alvefur <zash@zash.se>
parents: 343
diff changeset
48 `host` VARCHAR(1023) NOT NULL,
817e175c37df Fix the suggested mysql schema
Kim Alvefur <zash@zash.se>
parents: 343
diff changeset
49 `user` VARCHAR(1023) NOT NULL,
817e175c37df Fix the suggested mysql schema
Kim Alvefur <zash@zash.se>
parents: 343
diff changeset
50 `store` VARCHAR(1023) NOT NULL,
343
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
51 `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
52 `when` INTEGER NOT NULL,
351
817e175c37df Fix the suggested mysql schema
Kim Alvefur <zash@zash.se>
parents: 343
diff changeset
53 `with` VARCHAR(2047) NOT NULL,
817e175c37df Fix the suggested mysql schema
Kim Alvefur <zash@zash.se>
parents: 343
diff changeset
54 `resource` VARCHAR(1023),
343
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
55 `stanza` TEXT NOT NULL
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
56 );
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
57 CREATE INDEX hus ON prosodyarchive (host, user, store);
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
58 CREATE INDEX `with` ON prosodyarchive (`with`);
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
59 CREATE INDEX thetime ON prosodyarchive (`when`);
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
60 }}}
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
61
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
62 You may want to tweak the column sizes a bit; I did for my own purposes.
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
63
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
64 = Configuration =
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
65
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
66 This module uses the same configuration settings that [mod_mam] does, in addition to the [http://prosody.im/doc/modules/mod_storage_sql SQL storage settings]. You may also name the SQL connection settings 'mam_sql' if you want.
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
67
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
68 = Compatibility =
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
69 || 0.8 || ? ||
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
70 || 0.9 || Works ||
351
817e175c37df Fix the suggested mysql schema
Kim Alvefur <zash@zash.se>
parents: 343
diff changeset
71 || trunk || Works ||