author | Kim Alvefur <zash@zash.se> |
Sat, 28 Sep 2013 21:58:01 +0200 | |
changeset 5847 | 6aaa7ad4463c |
parent 5776 | bd0ff8ae98a8 |
child 5848 | 06156bfd4eaf |
permissions | -rw-r--r-- |
5494
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
1 |
|
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
2 |
local json = require "util.json"; |
5735
24f4e73645fe
mod_storage_sql2: Support XML serialization for stanzas.
Kim Alvefur <zash@zash.se>
parents:
5734
diff
changeset
|
3 |
local xml_parse = require "util.xml".parse; |
5741
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
4 |
local uuid = require "util.uuid"; |
5494
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
5 |
local resolve_relative_path = require "core.configmanager".resolve_relative_path; |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
6 |
|
5735
24f4e73645fe
mod_storage_sql2: Support XML serialization for stanzas.
Kim Alvefur <zash@zash.se>
parents:
5734
diff
changeset
|
7 |
local stanza_mt = require"util.stanza".stanza_mt; |
24f4e73645fe
mod_storage_sql2: Support XML serialization for stanzas.
Kim Alvefur <zash@zash.se>
parents:
5734
diff
changeset
|
8 |
local getmetatable = getmetatable; |
5741
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
9 |
local t_concat = table.concat; |
5735
24f4e73645fe
mod_storage_sql2: Support XML serialization for stanzas.
Kim Alvefur <zash@zash.se>
parents:
5734
diff
changeset
|
10 |
local function is_stanza(x) return getmetatable(x) == stanza_mt; end |
24f4e73645fe
mod_storage_sql2: Support XML serialization for stanzas.
Kim Alvefur <zash@zash.se>
parents:
5734
diff
changeset
|
11 |
|
5741
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
12 |
local noop = function() end |
5733
aeeced7b0149
mod_storage_sql2: Fix iteration over users and stores
Kim Alvefur <zash@zash.se>
parents:
5732
diff
changeset
|
13 |
local unpack = unpack |
aeeced7b0149
mod_storage_sql2: Fix iteration over users and stores
Kim Alvefur <zash@zash.se>
parents:
5732
diff
changeset
|
14 |
local function iterator(result) |
aeeced7b0149
mod_storage_sql2: Fix iteration over users and stores
Kim Alvefur <zash@zash.se>
parents:
5732
diff
changeset
|
15 |
return function(result) |
aeeced7b0149
mod_storage_sql2: Fix iteration over users and stores
Kim Alvefur <zash@zash.se>
parents:
5732
diff
changeset
|
16 |
local row = result(); |
aeeced7b0149
mod_storage_sql2: Fix iteration over users and stores
Kim Alvefur <zash@zash.se>
parents:
5732
diff
changeset
|
17 |
if row ~= nil then |
aeeced7b0149
mod_storage_sql2: Fix iteration over users and stores
Kim Alvefur <zash@zash.se>
parents:
5732
diff
changeset
|
18 |
return unpack(row); |
aeeced7b0149
mod_storage_sql2: Fix iteration over users and stores
Kim Alvefur <zash@zash.se>
parents:
5732
diff
changeset
|
19 |
end |
aeeced7b0149
mod_storage_sql2: Fix iteration over users and stores
Kim Alvefur <zash@zash.se>
parents:
5732
diff
changeset
|
20 |
end, result, nil; |
aeeced7b0149
mod_storage_sql2: Fix iteration over users and stores
Kim Alvefur <zash@zash.se>
parents:
5732
diff
changeset
|
21 |
end |
aeeced7b0149
mod_storage_sql2: Fix iteration over users and stores
Kim Alvefur <zash@zash.se>
parents:
5732
diff
changeset
|
22 |
|
5494
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
23 |
local mod_sql = module:require("sql"); |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
24 |
local params = module:get_option("sql"); |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
25 |
|
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
26 |
local engine; -- TODO create engine |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
27 |
|
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
28 |
local function create_table() |
5740
6fce00f61acf
mod_storage_sql2: Create an additional table `prosodyarchive` for chronological collections
Kim Alvefur <zash@zash.se>
parents:
5735
diff
changeset
|
29 |
local Table,Column,Index = mod_sql.Table,mod_sql.Column,mod_sql.Index; |
6fce00f61acf
mod_storage_sql2: Create an additional table `prosodyarchive` for chronological collections
Kim Alvefur <zash@zash.se>
parents:
5735
diff
changeset
|
30 |
--[[ |
5494
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
31 |
local ProsodyTable = Table { |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
32 |
name="prosody"; |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
33 |
Column { name="host", type="TEXT", nullable=false }; |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
34 |
Column { name="user", type="TEXT", nullable=false }; |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
35 |
Column { name="store", type="TEXT", nullable=false }; |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
36 |
Column { name="key", type="TEXT", nullable=false }; |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
37 |
Column { name="type", type="TEXT", nullable=false }; |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
38 |
Column { name="value", type="TEXT", nullable=false }; |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
39 |
Index { name="prosody_index", "host", "user", "store", "key" }; |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
40 |
}; |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
41 |
engine:transaction(function() |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
42 |
ProsodyTable:create(engine); |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
43 |
end);]] |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
44 |
if not module:get_option("sql_manage_tables", true) then |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
45 |
return; |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
46 |
end |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
47 |
|
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
48 |
local create_sql = "CREATE TABLE `prosody` (`host` TEXT, `user` TEXT, `store` TEXT, `key` TEXT, `type` TEXT, `value` TEXT);"; |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
49 |
if params.driver == "PostgreSQL" then |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
50 |
create_sql = create_sql:gsub("`", "\""); |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
51 |
elseif params.driver == "MySQL" then |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
52 |
create_sql = create_sql:gsub("`value` TEXT", "`value` MEDIUMTEXT") |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
53 |
:gsub(";$", " CHARACTER SET 'utf8' COLLATE 'utf8_bin';"); |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
54 |
end |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
55 |
|
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
56 |
local index_sql = "CREATE INDEX `prosody_index` ON `prosody` (`host`, `user`, `store`, `key`)"; |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
57 |
if params.driver == "PostgreSQL" then |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
58 |
index_sql = index_sql:gsub("`", "\""); |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
59 |
elseif params.driver == "MySQL" then |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
60 |
index_sql = index_sql:gsub("`([,)])", "`(20)%1"); |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
61 |
end |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
62 |
|
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
63 |
local success,err = engine:transaction(function() |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
64 |
engine:execute(create_sql); |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
65 |
engine:execute(index_sql); |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
66 |
end); |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
67 |
if not success then -- so we failed to create |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
68 |
if params.driver == "MySQL" then |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
69 |
success,err = engine:transaction(function() |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
70 |
local result = engine:execute("SHOW COLUMNS FROM prosody WHERE Field='value' and Type='text'"); |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
71 |
if result:rowcount() > 0 then |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
72 |
module:log("info", "Upgrading database schema..."); |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
73 |
engine:execute("ALTER TABLE prosody MODIFY COLUMN `value` MEDIUMTEXT"); |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
74 |
module:log("info", "Database table automatically upgraded"); |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
75 |
end |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
76 |
return true; |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
77 |
end); |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
78 |
if not success then |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
79 |
module:log("error", "Failed to check/upgrade database schema (%s), please see " |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
80 |
.."http://prosody.im/doc/mysql for help", |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
81 |
err or "unknown error"); |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
82 |
end |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
83 |
end |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
84 |
end |
5740
6fce00f61acf
mod_storage_sql2: Create an additional table `prosodyarchive` for chronological collections
Kim Alvefur <zash@zash.se>
parents:
5735
diff
changeset
|
85 |
local ProsodyArchiveTable = Table { |
6fce00f61acf
mod_storage_sql2: Create an additional table `prosodyarchive` for chronological collections
Kim Alvefur <zash@zash.se>
parents:
5735
diff
changeset
|
86 |
name="prosodyarchive"; |
6fce00f61acf
mod_storage_sql2: Create an additional table `prosodyarchive` for chronological collections
Kim Alvefur <zash@zash.se>
parents:
5735
diff
changeset
|
87 |
Column { name="sort_id", type="INTEGER PRIMARY KEY AUTOINCREMENT", nullable=false }; |
6fce00f61acf
mod_storage_sql2: Create an additional table `prosodyarchive` for chronological collections
Kim Alvefur <zash@zash.se>
parents:
5735
diff
changeset
|
88 |
Column { name="host", type="TEXT", nullable=false }; |
6fce00f61acf
mod_storage_sql2: Create an additional table `prosodyarchive` for chronological collections
Kim Alvefur <zash@zash.se>
parents:
5735
diff
changeset
|
89 |
Column { name="user", type="TEXT", nullable=false }; |
6fce00f61acf
mod_storage_sql2: Create an additional table `prosodyarchive` for chronological collections
Kim Alvefur <zash@zash.se>
parents:
5735
diff
changeset
|
90 |
Column { name="store", type="TEXT", nullable=false }; |
6fce00f61acf
mod_storage_sql2: Create an additional table `prosodyarchive` for chronological collections
Kim Alvefur <zash@zash.se>
parents:
5735
diff
changeset
|
91 |
Column { name="key", type="TEXT", nullable=false }; -- item id |
6fce00f61acf
mod_storage_sql2: Create an additional table `prosodyarchive` for chronological collections
Kim Alvefur <zash@zash.se>
parents:
5735
diff
changeset
|
92 |
Column { name="when", type="INTEGER", nullable=false }; -- timestamp |
6fce00f61acf
mod_storage_sql2: Create an additional table `prosodyarchive` for chronological collections
Kim Alvefur <zash@zash.se>
parents:
5735
diff
changeset
|
93 |
Column { name="with", type="TEXT", nullable=false }; -- related id |
6fce00f61acf
mod_storage_sql2: Create an additional table `prosodyarchive` for chronological collections
Kim Alvefur <zash@zash.se>
parents:
5735
diff
changeset
|
94 |
Column { name="type", type="TEXT", nullable=false }; |
6fce00f61acf
mod_storage_sql2: Create an additional table `prosodyarchive` for chronological collections
Kim Alvefur <zash@zash.se>
parents:
5735
diff
changeset
|
95 |
Column { name="value", type=params.driver == "MySQL" and "MEDIUMTEXT" or "TEXT", nullable=false }; |
6fce00f61acf
mod_storage_sql2: Create an additional table `prosodyarchive` for chronological collections
Kim Alvefur <zash@zash.se>
parents:
5735
diff
changeset
|
96 |
Index { name="prosodyarchive_index", "host", "user", "store", "key" }; |
6fce00f61acf
mod_storage_sql2: Create an additional table `prosodyarchive` for chronological collections
Kim Alvefur <zash@zash.se>
parents:
5735
diff
changeset
|
97 |
}; |
6fce00f61acf
mod_storage_sql2: Create an additional table `prosodyarchive` for chronological collections
Kim Alvefur <zash@zash.se>
parents:
5735
diff
changeset
|
98 |
engine:transaction(function() |
6fce00f61acf
mod_storage_sql2: Create an additional table `prosodyarchive` for chronological collections
Kim Alvefur <zash@zash.se>
parents:
5735
diff
changeset
|
99 |
ProsodyArchiveTable:create(engine); |
6fce00f61acf
mod_storage_sql2: Create an additional table `prosodyarchive` for chronological collections
Kim Alvefur <zash@zash.se>
parents:
5735
diff
changeset
|
100 |
end); |
5494
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
101 |
end |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
102 |
local function set_encoding() |
5765
d854c17a45fd
mod_storage_sql2: Do an early return and drop an indentation level
Kim Alvefur <zash@zash.se>
parents:
5741
diff
changeset
|
103 |
if params.driver == "SQLite3" then return end |
d854c17a45fd
mod_storage_sql2: Do an early return and drop an indentation level
Kim Alvefur <zash@zash.se>
parents:
5741
diff
changeset
|
104 |
local set_names_query = "SET NAMES 'utf8';"; |
d854c17a45fd
mod_storage_sql2: Do an early return and drop an indentation level
Kim Alvefur <zash@zash.se>
parents:
5741
diff
changeset
|
105 |
if params.driver == "MySQL" then |
d854c17a45fd
mod_storage_sql2: Do an early return and drop an indentation level
Kim Alvefur <zash@zash.se>
parents:
5741
diff
changeset
|
106 |
set_names_query = set_names_query:gsub(";$", " COLLATE 'utf8_bin';"); |
d854c17a45fd
mod_storage_sql2: Do an early return and drop an indentation level
Kim Alvefur <zash@zash.se>
parents:
5741
diff
changeset
|
107 |
end |
d854c17a45fd
mod_storage_sql2: Do an early return and drop an indentation level
Kim Alvefur <zash@zash.se>
parents:
5741
diff
changeset
|
108 |
local success,err = engine:transaction(function() return engine:execute(set_names_query); end); |
d854c17a45fd
mod_storage_sql2: Do an early return and drop an indentation level
Kim Alvefur <zash@zash.se>
parents:
5741
diff
changeset
|
109 |
if not success then |
d854c17a45fd
mod_storage_sql2: Do an early return and drop an indentation level
Kim Alvefur <zash@zash.se>
parents:
5741
diff
changeset
|
110 |
module:log("error", "Failed to set database connection encoding to UTF8: %s", err); |
d854c17a45fd
mod_storage_sql2: Do an early return and drop an indentation level
Kim Alvefur <zash@zash.se>
parents:
5741
diff
changeset
|
111 |
return; |
d854c17a45fd
mod_storage_sql2: Do an early return and drop an indentation level
Kim Alvefur <zash@zash.se>
parents:
5741
diff
changeset
|
112 |
end |
d854c17a45fd
mod_storage_sql2: Do an early return and drop an indentation level
Kim Alvefur <zash@zash.se>
parents:
5741
diff
changeset
|
113 |
if params.driver == "MySQL" then |
d854c17a45fd
mod_storage_sql2: Do an early return and drop an indentation level
Kim Alvefur <zash@zash.se>
parents:
5741
diff
changeset
|
114 |
-- COMPAT w/pre-0.9: Upgrade tables to UTF-8 if not already |
d854c17a45fd
mod_storage_sql2: Do an early return and drop an indentation level
Kim Alvefur <zash@zash.se>
parents:
5741
diff
changeset
|
115 |
local check_encoding_query = "SELECT `COLUMN_NAME`,`COLUMN_TYPE` FROM `information_schema`.`columns` WHERE `TABLE_NAME`='prosody' AND ( `CHARACTER_SET_NAME`!='utf8' OR `COLLATION_NAME`!='utf8_bin' );"; |
d854c17a45fd
mod_storage_sql2: Do an early return and drop an indentation level
Kim Alvefur <zash@zash.se>
parents:
5741
diff
changeset
|
116 |
local success,err = engine:transaction(function() |
d854c17a45fd
mod_storage_sql2: Do an early return and drop an indentation level
Kim Alvefur <zash@zash.se>
parents:
5741
diff
changeset
|
117 |
local result = engine:execute(check_encoding_query); |
d854c17a45fd
mod_storage_sql2: Do an early return and drop an indentation level
Kim Alvefur <zash@zash.se>
parents:
5741
diff
changeset
|
118 |
local n_bad_columns = result:rowcount(); |
d854c17a45fd
mod_storage_sql2: Do an early return and drop an indentation level
Kim Alvefur <zash@zash.se>
parents:
5741
diff
changeset
|
119 |
if n_bad_columns > 0 then |
d854c17a45fd
mod_storage_sql2: Do an early return and drop an indentation level
Kim Alvefur <zash@zash.se>
parents:
5741
diff
changeset
|
120 |
module:log("warn", "Found %d columns in prosody table requiring encoding change, updating now...", n_bad_columns); |
d854c17a45fd
mod_storage_sql2: Do an early return and drop an indentation level
Kim Alvefur <zash@zash.se>
parents:
5741
diff
changeset
|
121 |
local fix_column_query1 = "ALTER TABLE `prosody` CHANGE `%s` `%s` BLOB;"; |
d854c17a45fd
mod_storage_sql2: Do an early return and drop an indentation level
Kim Alvefur <zash@zash.se>
parents:
5741
diff
changeset
|
122 |
local fix_column_query2 = "ALTER TABLE `prosody` CHANGE `%s` `%s` %s CHARACTER SET 'utf8' COLLATE 'utf8_bin';"; |
d854c17a45fd
mod_storage_sql2: Do an early return and drop an indentation level
Kim Alvefur <zash@zash.se>
parents:
5741
diff
changeset
|
123 |
for row in result:rows() do |
d854c17a45fd
mod_storage_sql2: Do an early return and drop an indentation level
Kim Alvefur <zash@zash.se>
parents:
5741
diff
changeset
|
124 |
local column_name, column_type = unpack(row); |
d854c17a45fd
mod_storage_sql2: Do an early return and drop an indentation level
Kim Alvefur <zash@zash.se>
parents:
5741
diff
changeset
|
125 |
engine:execute(fix_column_query1:format(column_name, column_name)); |
d854c17a45fd
mod_storage_sql2: Do an early return and drop an indentation level
Kim Alvefur <zash@zash.se>
parents:
5741
diff
changeset
|
126 |
engine:execute(fix_column_query2:format(column_name, column_name, column_type)); |
d854c17a45fd
mod_storage_sql2: Do an early return and drop an indentation level
Kim Alvefur <zash@zash.se>
parents:
5741
diff
changeset
|
127 |
end |
d854c17a45fd
mod_storage_sql2: Do an early return and drop an indentation level
Kim Alvefur <zash@zash.se>
parents:
5741
diff
changeset
|
128 |
module:log("info", "Database encoding upgrade complete!"); |
d854c17a45fd
mod_storage_sql2: Do an early return and drop an indentation level
Kim Alvefur <zash@zash.se>
parents:
5741
diff
changeset
|
129 |
end |
d854c17a45fd
mod_storage_sql2: Do an early return and drop an indentation level
Kim Alvefur <zash@zash.se>
parents:
5741
diff
changeset
|
130 |
end); |
d854c17a45fd
mod_storage_sql2: Do an early return and drop an indentation level
Kim Alvefur <zash@zash.se>
parents:
5741
diff
changeset
|
131 |
local success,err = engine:transaction(function() return engine:execute(check_encoding_query); end); |
5494
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
132 |
if not success then |
5765
d854c17a45fd
mod_storage_sql2: Do an early return and drop an indentation level
Kim Alvefur <zash@zash.se>
parents:
5741
diff
changeset
|
133 |
module:log("error", "Failed to check/upgrade database encoding: %s", err or "unknown error"); |
5494
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
134 |
end |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
135 |
end |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
136 |
end |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
137 |
|
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
138 |
do -- process options to get a db connection |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
139 |
params = params or { driver = "SQLite3" }; |
5776
bd0ff8ae98a8
Remove all trailing whitespace
Florian Zeitz <florob@babelmonkeys.de>
parents:
5765
diff
changeset
|
140 |
|
5494
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
141 |
if params.driver == "SQLite3" then |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
142 |
params.database = resolve_relative_path(prosody.paths.data or ".", params.database or "prosody.sqlite"); |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
143 |
end |
5776
bd0ff8ae98a8
Remove all trailing whitespace
Florian Zeitz <florob@babelmonkeys.de>
parents:
5765
diff
changeset
|
144 |
|
5494
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
145 |
assert(params.driver and params.database, "Both the SQL driver and the database need to be specified"); |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
146 |
|
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
147 |
--local dburi = db2uri(params); |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
148 |
engine = mod_sql:create_engine(params); |
5776
bd0ff8ae98a8
Remove all trailing whitespace
Florian Zeitz <florob@babelmonkeys.de>
parents:
5765
diff
changeset
|
149 |
|
5494
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
150 |
-- Encoding mess |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
151 |
set_encoding(); |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
152 |
|
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
153 |
-- Automatically create table, ignore failure (table probably already exists) |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
154 |
create_table(); |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
155 |
end |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
156 |
|
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
157 |
local function serialize(value) |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
158 |
local t = type(value); |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
159 |
if t == "string" or t == "boolean" or t == "number" then |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
160 |
return t, tostring(value); |
5735
24f4e73645fe
mod_storage_sql2: Support XML serialization for stanzas.
Kim Alvefur <zash@zash.se>
parents:
5734
diff
changeset
|
161 |
elseif is_stanza(value) then |
24f4e73645fe
mod_storage_sql2: Support XML serialization for stanzas.
Kim Alvefur <zash@zash.se>
parents:
5734
diff
changeset
|
162 |
return "xml", tostring(value); |
5494
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
163 |
elseif t == "table" then |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
164 |
local value,err = json.encode(value); |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
165 |
if value then return "json", value; end |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
166 |
return nil, err; |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
167 |
end |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
168 |
return nil, "Unhandled value type: "..t; |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
169 |
end |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
170 |
local function deserialize(t, value) |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
171 |
if t == "string" then return value; |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
172 |
elseif t == "boolean" then |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
173 |
if value == "true" then return true; |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
174 |
elseif value == "false" then return false; end |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
175 |
elseif t == "number" then return tonumber(value); |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
176 |
elseif t == "json" then |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
177 |
return json.decode(value); |
5735
24f4e73645fe
mod_storage_sql2: Support XML serialization for stanzas.
Kim Alvefur <zash@zash.se>
parents:
5734
diff
changeset
|
178 |
elseif t == "xml" then |
24f4e73645fe
mod_storage_sql2: Support XML serialization for stanzas.
Kim Alvefur <zash@zash.se>
parents:
5734
diff
changeset
|
179 |
return xml_parse(value); |
5494
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
180 |
end |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
181 |
end |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
182 |
|
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
183 |
local host = module.host; |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
184 |
local user, store; |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
185 |
|
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
186 |
local function keyval_store_get() |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
187 |
local haveany; |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
188 |
local result = {}; |
5732
4aa1d6f5083a
mod_storage_sql2: Make sure the user field is not NULL
Kim Alvefur <zash@zash.se>
parents:
5711
diff
changeset
|
189 |
for row in engine:select("SELECT `key`,`type`,`value` FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=?", host, user or "", store) do |
5494
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
190 |
haveany = true; |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
191 |
local k = row[1]; |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
192 |
local v = deserialize(row[2], row[3]); |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
193 |
if k and v then |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
194 |
if k ~= "" then result[k] = v; elseif type(v) == "table" then |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
195 |
for a,b in pairs(v) do |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
196 |
result[a] = b; |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
197 |
end |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
198 |
end |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
199 |
end |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
200 |
end |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
201 |
if haveany then |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
202 |
return result; |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
203 |
end |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
204 |
end |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
205 |
local function keyval_store_set(data) |
5732
4aa1d6f5083a
mod_storage_sql2: Make sure the user field is not NULL
Kim Alvefur <zash@zash.se>
parents:
5711
diff
changeset
|
206 |
engine:delete("DELETE FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=?", host, user or "", store); |
5776
bd0ff8ae98a8
Remove all trailing whitespace
Florian Zeitz <florob@babelmonkeys.de>
parents:
5765
diff
changeset
|
207 |
|
5494
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
208 |
if data and next(data) ~= nil then |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
209 |
local extradata = {}; |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
210 |
for key, value in pairs(data) do |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
211 |
if type(key) == "string" and key ~= "" then |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
212 |
local t, value = serialize(value); |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
213 |
assert(t, value); |
5732
4aa1d6f5083a
mod_storage_sql2: Make sure the user field is not NULL
Kim Alvefur <zash@zash.se>
parents:
5711
diff
changeset
|
214 |
engine:insert("INSERT INTO `prosody` (`host`,`user`,`store`,`key`,`type`,`value`) VALUES (?,?,?,?,?,?)", host, user or "", store, key, t, value); |
5494
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
215 |
else |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
216 |
extradata[key] = value; |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
217 |
end |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
218 |
end |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
219 |
if next(extradata) ~= nil then |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
220 |
local t, extradata = serialize(extradata); |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
221 |
assert(t, extradata); |
5732
4aa1d6f5083a
mod_storage_sql2: Make sure the user field is not NULL
Kim Alvefur <zash@zash.se>
parents:
5711
diff
changeset
|
222 |
engine:insert("INSERT INTO `prosody` (`host`,`user`,`store`,`key`,`type`,`value`) VALUES (?,?,?,?,?,?)", host, user or "", store, "", t, extradata); |
5494
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
223 |
end |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
224 |
end |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
225 |
return true; |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
226 |
end |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
227 |
|
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
228 |
local keyval_store = {}; |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
229 |
keyval_store.__index = keyval_store; |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
230 |
function keyval_store:get(username) |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
231 |
user,store = username,self.store; |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
232 |
return select(2, engine:transaction(keyval_store_get)); |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
233 |
end |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
234 |
function keyval_store:set(username, data) |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
235 |
user,store = username,self.store; |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
236 |
return engine:transaction(function() |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
237 |
return keyval_store_set(data); |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
238 |
end); |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
239 |
end |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
240 |
function keyval_store:users() |
5733
aeeced7b0149
mod_storage_sql2: Fix iteration over users and stores
Kim Alvefur <zash@zash.se>
parents:
5732
diff
changeset
|
241 |
local ok, result = engine:transaction(function() |
5494
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
242 |
return engine:select("SELECT DISTINCT `user` FROM `prosody` WHERE `host`=? AND `store`=?", host, self.store); |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
243 |
end); |
5733
aeeced7b0149
mod_storage_sql2: Fix iteration over users and stores
Kim Alvefur <zash@zash.se>
parents:
5732
diff
changeset
|
244 |
if not ok then return ok, result end |
aeeced7b0149
mod_storage_sql2: Fix iteration over users and stores
Kim Alvefur <zash@zash.se>
parents:
5732
diff
changeset
|
245 |
return iterator(result); |
5494
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
246 |
end |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
247 |
|
5741
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
248 |
local archive_store = {} |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
249 |
archive_store.__index = archive_store |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
250 |
function archive_store:append(username, when, with, value) |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
251 |
local user,store = username,self.store; |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
252 |
return engine:transaction(function() |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
253 |
local key = uuid.generate(); |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
254 |
local t, value = serialize(value); |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
255 |
engine:insert("INSERT INTO `prosodyarchive` (`host`, `user`, `store`, `when`, `with`, `key`, `type`, `value`) VALUES (?,?,?,?,?,?,?,?)", host, user or "", store, when, with, key, t, value); |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
256 |
return key; |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
257 |
end); |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
258 |
end |
5847
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
259 |
|
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
260 |
-- Helpers for building the WHERE clause |
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
261 |
local function archive_where(query, args, where) |
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
262 |
-- Time range, inclusive |
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
263 |
if query.start then |
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
264 |
args[#args+1] = query.start |
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
265 |
where[#where+1] = "`when` >= ?" |
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
266 |
end |
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
267 |
|
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
268 |
if query["end"] then |
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
269 |
args[#args+1] = query["end"]; |
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
270 |
if query.start then |
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
271 |
where[#where] = "`when` BETWEEN ? AND ?" -- is this inclusive? |
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
272 |
else |
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
273 |
where[#where+1] = "`when` >= ?" |
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
274 |
end |
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
275 |
end |
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
276 |
|
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
277 |
-- Related name |
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
278 |
if query.with then |
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
279 |
where[#where+1] = "`with` = ?"; |
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
280 |
args[#args+1] = query.with |
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
281 |
end |
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
282 |
|
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
283 |
-- Unique id |
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
284 |
if query.key then |
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
285 |
where[#where+1] = "`key` = ?"; |
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
286 |
args[#args+1] = query.key |
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
287 |
end |
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
288 |
end |
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
289 |
local function archive_where_id_range(query, args, where) |
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
290 |
-- Before or after specific item, exclusive |
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
291 |
if query.after then -- keys better be unique! |
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
292 |
where[#where+1] = "`sort_id` > (SELECT `sort_id` FROM `prosodyarchive` WHERE `key` = ? LIMIT 1)" |
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
293 |
args[#args+1] = query.after |
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
294 |
end |
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
295 |
if query.before then |
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
296 |
where[#where+1] = "`sort_id` < (SELECT `sort_id` FROM `prosodyarchive` WHERE `key` = ? LIMIT 1)" |
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
297 |
args[#args+1] = query.before |
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
298 |
end |
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
299 |
end |
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
300 |
|
5741
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
301 |
function archive_store:find(username, query) |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
302 |
query = query or {}; |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
303 |
local user,store = username,self.store; |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
304 |
local total; |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
305 |
local ok, result = engine:transaction(function() |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
306 |
local sql_query = "SELECT `key`, `type`, `value`, `when` FROM `prosodyarchive` WHERE %s ORDER BY `sort_id` %s%s;"; |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
307 |
local args = { host, user or "", store, }; |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
308 |
local where = { "`host` = ?", "`user` = ?", "`store` = ?", }; |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
309 |
|
5847
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
310 |
archive_where(query, args, where); |
5741
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
311 |
|
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
312 |
-- Total matching |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
313 |
if query.total then |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
314 |
local stats = engine:select(sql_query:gsub("^(SELECT).-(FROM)", "%1 COUNT(*) %2"):format(t_concat(where, " AND "), "DESC", ""), unpack(args)); |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
315 |
if stats then |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
316 |
local _total = stats() |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
317 |
total = _total and _total[1]; |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
318 |
end |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
319 |
if query.limit == 0 then -- Skip the real query |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
320 |
return noop, total; |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
321 |
end |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
322 |
end |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
323 |
|
5847
6aaa7ad4463c
mod_storage_sql2: Split out code for building WHERE clauses into separate functions
Kim Alvefur <zash@zash.se>
parents:
5776
diff
changeset
|
324 |
archive_where_id_range(query, args, where); |
5741
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
325 |
|
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
326 |
if query.limit then |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
327 |
args[#args+1] = query.limit; |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
328 |
end |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
329 |
|
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
330 |
sql_query = sql_query:format(t_concat(where, " AND "), query.reverse and "DESC" or "ASC", query.limit and " LIMIT ?" or ""); |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
331 |
module:log("debug", sql_query); |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
332 |
return engine:select(sql_query, unpack(args)); |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
333 |
end); |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
334 |
if not ok then return ok, result end |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
335 |
return function() |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
336 |
local row = result(); |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
337 |
if row ~= nil then |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
338 |
return row[1], deserialize(row[2], row[3]), row[4]; |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
339 |
end |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
340 |
end, total; |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
341 |
end |
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
342 |
|
5734
49f1fed6e25e
mod_storage_sql2: Keep available store types in a table
Kim Alvefur <zash@zash.se>
parents:
5733
diff
changeset
|
343 |
local stores = { |
49f1fed6e25e
mod_storage_sql2: Keep available store types in a table
Kim Alvefur <zash@zash.se>
parents:
5733
diff
changeset
|
344 |
keyval = keyval_store; |
5741
c7a664e496b3
mod_storage_sql2: Add archive store with append and find methods
Kim Alvefur <zash@zash.se>
parents:
5740
diff
changeset
|
345 |
archive = archive_store; |
5734
49f1fed6e25e
mod_storage_sql2: Keep available store types in a table
Kim Alvefur <zash@zash.se>
parents:
5733
diff
changeset
|
346 |
}; |
49f1fed6e25e
mod_storage_sql2: Keep available store types in a table
Kim Alvefur <zash@zash.se>
parents:
5733
diff
changeset
|
347 |
|
5494
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
348 |
local driver = {}; |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
349 |
|
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
350 |
function driver:open(store, typ) |
5734
49f1fed6e25e
mod_storage_sql2: Keep available store types in a table
Kim Alvefur <zash@zash.se>
parents:
5733
diff
changeset
|
351 |
local store_mt = stores[typ or "keyval"]; |
49f1fed6e25e
mod_storage_sql2: Keep available store types in a table
Kim Alvefur <zash@zash.se>
parents:
5733
diff
changeset
|
352 |
if store_mt then |
49f1fed6e25e
mod_storage_sql2: Keep available store types in a table
Kim Alvefur <zash@zash.se>
parents:
5733
diff
changeset
|
353 |
return setmetatable({ store = store }, store_mt); |
5494
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
354 |
end |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
355 |
return nil, "unsupported-store"; |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
356 |
end |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
357 |
|
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
358 |
function driver:stores(username) |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
359 |
local sql = "SELECT DISTINCT `store` FROM `prosody` WHERE `host`=? AND `user`" .. |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
360 |
(username == true and "!=?" or "=?"); |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
361 |
if username == true or not username then |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
362 |
username = ""; |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
363 |
end |
5733
aeeced7b0149
mod_storage_sql2: Fix iteration over users and stores
Kim Alvefur <zash@zash.se>
parents:
5732
diff
changeset
|
364 |
local ok, result = engine:transaction(function() |
5494
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
365 |
return engine:select(sql, host, username); |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
366 |
end); |
5733
aeeced7b0149
mod_storage_sql2: Fix iteration over users and stores
Kim Alvefur <zash@zash.se>
parents:
5732
diff
changeset
|
367 |
if not ok then return ok, result end |
aeeced7b0149
mod_storage_sql2: Fix iteration over users and stores
Kim Alvefur <zash@zash.se>
parents:
5732
diff
changeset
|
368 |
return iterator(result); |
5494
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
369 |
end |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
370 |
|
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
371 |
function driver:purge(username) |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
372 |
return engine:transaction(function() |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
373 |
local stmt,err = engine:delete("DELETE FROM `prosody` WHERE `host`=? AND `user`=?", host, username); |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
374 |
return true,err; |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
375 |
end); |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
376 |
end |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
377 |
|
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
378 |
module:provides("storage", driver); |
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
379 |
|
9916f0a2d178
mod_storage_sql2 (temporary name), sql.lib, util.sql: New SQL API supporting cross-module connection sharing, transactions and Things - a work in progress
Matthew Wild <mwild1@gmail.com>
parents:
diff
changeset
|
380 |