Mercurial > prosody-wiki

annotate mod_mam_sql.wiki @ 347:eebd8417a2ad

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Updated as the whole module folder is supposed to be copied now.
author maranda3985@gmail.com
date Wed, 01 May 2013 22:43:09 +0000
parents 6cc2d1e280c9
children 817e175c37df
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
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
2
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
3 = Introduction =
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
4
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
5 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
6
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 = Details =
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
9
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
10 See [mod_mam] for details.
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
11
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
12 = Usage =
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
13
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
14 First copy the module to the prosody plugins directory.
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
15
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
16 Then add "mam_sql" to your modules_enabled list:
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
17 {{{
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
18 modules_enabled = {
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
19 -- ...
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
20 "mam_sql",
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
21 -- ...
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 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
26
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 CREATE TABLE `prosodyarchive` (
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
29 `host` TEXT,
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
30 `user` TEXT,
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
31 `store` TEXT,
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
32 `id` INTEGER PRIMARY KEY AUTOINCREMENT,
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
33 `when` INTEGER,
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
34 `with` TEXT,
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
35 `resource` TEXT,
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
36 `stanza` TEXT
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
37 );
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
38 CREATE INDEX `hus` ON `prosodyarchive` (`host`, `user`, `store`);
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
39 CREATE INDEX `with` ON `prosodyarchive` (`with`);
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
40 CREATE INDEX `thetime` ON `prosodyarchive` (`when`);
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
41 }}}
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 (*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
44
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 CREATE TABLE prosodyarchive (
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
47 `host` VARCHAR(16) NOT NULL,
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
48 `user` VARCHAR(16) NOT NULL,
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
49 `store` CHAR(8) NOT NULL,
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
50 `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
51 `when` INTEGER NOT NULL,
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
52 `with` VARCHAR(32) NOT NULL,
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
53 `resource` VARCHAR(16) NOT NULL,
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
54 `stanza` TEXT NOT NULL
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
55 );
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
56 CREATE INDEX hus ON prosodyarchive (host, user, store);
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
57 CREATE INDEX `with` ON prosodyarchive (`with`);
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
58 CREATE INDEX thetime ON prosodyarchive (`when`);
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
59 }}}
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 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
62
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
63 = Configuration =
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
64
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
65 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
66
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
67 = Compatibility =
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
68 || 0.8 || ? ||
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
69 || 0.9 || Works ||
6cc2d1e280c9 Created wiki page through web user interface.
rdhoelz@gmail.com
parents:
diff changeset
70 || trunk || Works ||