Mercurial > prosody-modules

annotate mod_mam_sql/README.markdown @ 1819:1b08597b5e6f

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
mod_storage_xmlarchive: Add support for purging (used when deleting users)
author Kim Alvefur <zash@zash.se>
date Wed, 02 Sep 2015 16:43:34 +0200
parents 4d73a1a6ba68
children 8de50be756e5
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
1803
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
1 ---
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
2 labels:
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
3 - 'Stage-Alpha'
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
4 - Deprecated
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
5 summary: 'XEP-0313: Message Archive Management using SQL'
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
6 ...
1782
29f3d6b7ad16 Import wiki pages
Kim Alvefur <zash@zash.se>
parents:
diff changeset
7
1803
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
8 **Note:** This module is unsupported and not up to date with the MAM
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
9 specification
1782
29f3d6b7ad16 Import wiki pages
Kim Alvefur <zash@zash.se>
parents:
diff changeset
10
1803
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
11 Introduction
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
12 ============
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
13
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
14 This is an old fork of mod\_mam with the purpose of figuring out and
1797
a946aa422503 mod_mam_sql/README: More obvious pointers to use mod_mam instead
Kim Alvefur <zash@zash.se>
parents: 1782
diff changeset
15 testing an appropriate schema for future inclusion in prosodys
1803
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
16 mod\_storage\_sql. That work is currently available in
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
17 mod\_storage\_sql2, pending merging with mod\_storage\_sql.
1782
29f3d6b7ad16 Import wiki pages
Kim Alvefur <zash@zash.se>
parents:
diff changeset
18
1797
a946aa422503 mod_mam_sql/README: More obvious pointers to use mod_mam instead
Kim Alvefur <zash@zash.se>
parents: 1782
diff changeset
19 It talks SQL directly, bypassing prosodys storage layer.
a946aa422503 mod_mam_sql/README: More obvious pointers to use mod_mam instead
Kim Alvefur <zash@zash.se>
parents: 1782
diff changeset
20
a946aa422503 mod_mam_sql/README: More obvious pointers to use mod_mam instead
Kim Alvefur <zash@zash.se>
parents: 1782
diff changeset
21 It is no longer maintained and is unlikely to work with modern clients.
1782
29f3d6b7ad16 Import wiki pages
Kim Alvefur <zash@zash.se>
parents:
diff changeset
22
1803
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
23 Details
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
24 =======
1782
29f3d6b7ad16 Import wiki pages
Kim Alvefur <zash@zash.se>
parents:
diff changeset
25
1803
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
26 See [mod\_mam](mod_mam.md) for details.
1782
29f3d6b7ad16 Import wiki pages
Kim Alvefur <zash@zash.se>
parents:
diff changeset
27
1803
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
28 Usage
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
29 =====
1782
29f3d6b7ad16 Import wiki pages
Kim Alvefur <zash@zash.se>
parents:
diff changeset
30
29f3d6b7ad16 Import wiki pages
Kim Alvefur <zash@zash.se>
parents:
diff changeset
31 First copy the module to the prosody plugins directory.
29f3d6b7ad16 Import wiki pages
Kim Alvefur <zash@zash.se>
parents:
diff changeset
32
1803
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
33 Then add "mam\_sql" to your modules\_enabled list:
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
34
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
35 modules_enabled = {
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
36 -- ...
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
37 "mam_sql",
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
38 -- ...
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
39 }
1782
29f3d6b7ad16 Import wiki pages
Kim Alvefur <zash@zash.se>
parents:
diff changeset
40
29f3d6b7ad16 Import wiki pages
Kim Alvefur <zash@zash.se>
parents:
diff changeset
41 You should probably run the SQL to create the archive table/indexes:
29f3d6b7ad16 Import wiki pages
Kim Alvefur <zash@zash.se>
parents:
diff changeset
42
1803
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
43 CREATE TABLE `prosodyarchive` (
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
44 `host` TEXT,
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
45 `user` TEXT,
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
46 `store` TEXT,
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
47 `id` INTEGER PRIMARY KEY AUTOINCREMENT,
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
48 `when` INTEGER,
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
49 `with` TEXT,
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
50 `resource` TEXT,
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
51 `stanza` TEXT
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
52 );
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
53 CREATE INDEX `hus` ON `prosodyarchive` (`host`, `user`, `store`);
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
54 CREATE INDEX `with` ON `prosodyarchive` (`with`);
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
55 CREATE INDEX `thetime` ON `prosodyarchive` (`when`);
1782
29f3d6b7ad16 Import wiki pages
Kim Alvefur <zash@zash.se>
parents:
diff changeset
56
1803
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
57 (**NOTE**: I ran the following SQL to initialize the table/indexes on
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
58 MySQL):
1782
29f3d6b7ad16 Import wiki pages
Kim Alvefur <zash@zash.se>
parents:
diff changeset
59
1803
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
60 CREATE TABLE prosodyarchive (
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
61 `host` VARCHAR(1023) NOT NULL,
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
62 `user` VARCHAR(1023) NOT NULL,
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
63 `store` VARCHAR(1023) NOT NULL,
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
64 `id` INTEGER PRIMARY KEY AUTO_INCREMENT,
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
65 `when` INTEGER NOT NULL,
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
66 `with` VARCHAR(2047) NOT NULL,
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
67 `resource` VARCHAR(1023),
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
68 `stanza` TEXT NOT NULL
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
69 );
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
70 CREATE INDEX hus ON prosodyarchive (host, user, store);
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
71 CREATE INDEX `with` ON prosodyarchive (`with`);
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
72 CREATE INDEX thetime ON prosodyarchive (`when`);
1782
29f3d6b7ad16 Import wiki pages
Kim Alvefur <zash@zash.se>
parents:
diff changeset
73
29f3d6b7ad16 Import wiki pages
Kim Alvefur <zash@zash.se>
parents:
diff changeset
74 You may want to tweak the column sizes a bit; I did for my own purposes.
29f3d6b7ad16 Import wiki pages
Kim Alvefur <zash@zash.se>
parents:
diff changeset
75
1803
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
76 Configuration
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
77 =============
1782
29f3d6b7ad16 Import wiki pages
Kim Alvefur <zash@zash.se>
parents:
diff changeset
78
1803
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
79 This module uses the same configuration settings that
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
80 [mod\_mam](mod_mam.md) does, in addition to the [SQL storage
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
81 settings](http://prosody.im/doc/modules/mod_storage_sql). You may also
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
82 name the SQL connection settings 'mam\_sql' if you want.
1782
29f3d6b7ad16 Import wiki pages
Kim Alvefur <zash@zash.se>
parents:
diff changeset
83
1803
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
84 Compatibility
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
85 =============
1797
a946aa422503 mod_mam_sql/README: More obvious pointers to use mod_mam instead
Kim Alvefur <zash@zash.se>
parents: 1782
diff changeset
86
1803
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
87 ------- ----------------------
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
88 0.8 ?
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
89 0.9 Works
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
90 0.10 Use mod\_mam instead
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
91 trunk Use mod\_mam instead
4d73a1a6ba68 Convert all wiki pages to Markdown
Kim Alvefur <zash@zash.se>
parents: 1797
diff changeset
92 ------- ----------------------