author | Kim Alvefur <zash@zash.se> |
Thu, 25 Apr 2013 17:50:22 +0200 | |
changeset 5518 | 0220093e34fa |
parent 5209 | f5d121846d53 |
child 5776 | bd0ff8ae98a8 |
permissions | -rw-r--r-- |
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
1 |
|
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
2 |
--[[ |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
3 |
|
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
4 |
DB Tables: |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
5 |
Prosody - key-value, map |
3977
6724853adb80
mod_storage_sql: Remove the subkey column from the Prosody table, and make the map store compatible with the key-value store.
Waqas Hussain <waqas20@gmail.com>
parents:
3976
diff
changeset
|
6 |
| host | user | store | key | type | value | |
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
7 |
ProsodyArchive - list |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
8 |
| host | user | store | key | time | stanzatype | jsonvalue | |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
9 |
|
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
10 |
Mapping: |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
11 |
Roster - Prosody |
3977
6724853adb80
mod_storage_sql: Remove the subkey column from the Prosody table, and make the map store compatible with the key-value store.
Waqas Hussain <waqas20@gmail.com>
parents:
3976
diff
changeset
|
12 |
| host | user | "roster" | "contactjid" | type | value | |
6724853adb80
mod_storage_sql: Remove the subkey column from the Prosody table, and make the map store compatible with the key-value store.
Waqas Hussain <waqas20@gmail.com>
parents:
3976
diff
changeset
|
13 |
| host | user | "roster" | NULL | "json" | roster[false] data | |
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
14 |
Account - Prosody |
3977
6724853adb80
mod_storage_sql: Remove the subkey column from the Prosody table, and make the map store compatible with the key-value store.
Waqas Hussain <waqas20@gmail.com>
parents:
3976
diff
changeset
|
15 |
| host | user | "accounts" | "username" | type | value | |
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
16 |
|
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
17 |
Offline - ProsodyArchive |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
18 |
| host | user | "offline" | "contactjid" | time | "message" | json|XML | |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
19 |
|
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
20 |
]] |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
21 |
|
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
22 |
local type = type; |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
23 |
local tostring = tostring; |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
24 |
local tonumber = tonumber; |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
25 |
local pairs = pairs; |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
26 |
local next = next; |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
27 |
local setmetatable = setmetatable; |
4007
062b849ca088
mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents:
4004
diff
changeset
|
28 |
local xpcall = xpcall; |
3980
6b2fac6602b3
mod_storage_sql: Use util.json instead of util.serialization.
Waqas Hussain <waqas20@gmail.com>
parents:
3978
diff
changeset
|
29 |
local json = require "util.json"; |
5054
97385c45e670
mod_storage_sql: Keep connections in a shared cache table
Kim Alvefur <zash@zash.se>
parents:
5040
diff
changeset
|
30 |
local build_url = require"socket.url".build; |
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
31 |
|
4105
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
32 |
local DBI; |
4004
c1b3ecbed6c0
mod_storage_sql: Removed unnecessary initialization of a variable.
Waqas Hussain <waqas20@gmail.com>
parents:
3980
diff
changeset
|
33 |
local connection; |
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
34 |
local host,user,store = module.host; |
3976
16170a66e140
mod_storage_sql: Dynamically replace backquotes with double quotes when connecting to PostgreSQL...
Waqas Hussain <waqas20@gmail.com>
parents:
3975
diff
changeset
|
35 |
local params = module:get_option("sql"); |
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
36 |
|
5054
97385c45e670
mod_storage_sql: Keep connections in a shared cache table
Kim Alvefur <zash@zash.se>
parents:
5040
diff
changeset
|
37 |
local dburi; |
97385c45e670
mod_storage_sql: Keep connections in a shared cache table
Kim Alvefur <zash@zash.se>
parents:
5040
diff
changeset
|
38 |
local connections = module:shared "/*/sql/connection-cache"; |
97385c45e670
mod_storage_sql: Keep connections in a shared cache table
Kim Alvefur <zash@zash.se>
parents:
5040
diff
changeset
|
39 |
|
97385c45e670
mod_storage_sql: Keep connections in a shared cache table
Kim Alvefur <zash@zash.se>
parents:
5040
diff
changeset
|
40 |
local function db2uri(params) |
97385c45e670
mod_storage_sql: Keep connections in a shared cache table
Kim Alvefur <zash@zash.se>
parents:
5040
diff
changeset
|
41 |
return build_url{ |
97385c45e670
mod_storage_sql: Keep connections in a shared cache table
Kim Alvefur <zash@zash.se>
parents:
5040
diff
changeset
|
42 |
scheme = params.driver, |
97385c45e670
mod_storage_sql: Keep connections in a shared cache table
Kim Alvefur <zash@zash.se>
parents:
5040
diff
changeset
|
43 |
user = params.username, |
97385c45e670
mod_storage_sql: Keep connections in a shared cache table
Kim Alvefur <zash@zash.se>
parents:
5040
diff
changeset
|
44 |
password = params.password, |
97385c45e670
mod_storage_sql: Keep connections in a shared cache table
Kim Alvefur <zash@zash.se>
parents:
5040
diff
changeset
|
45 |
host = params.host, |
97385c45e670
mod_storage_sql: Keep connections in a shared cache table
Kim Alvefur <zash@zash.se>
parents:
5040
diff
changeset
|
46 |
port = params.port, |
97385c45e670
mod_storage_sql: Keep connections in a shared cache table
Kim Alvefur <zash@zash.se>
parents:
5040
diff
changeset
|
47 |
path = params.database, |
97385c45e670
mod_storage_sql: Keep connections in a shared cache table
Kim Alvefur <zash@zash.se>
parents:
5040
diff
changeset
|
48 |
}; |
97385c45e670
mod_storage_sql: Keep connections in a shared cache table
Kim Alvefur <zash@zash.se>
parents:
5040
diff
changeset
|
49 |
end |
97385c45e670
mod_storage_sql: Keep connections in a shared cache table
Kim Alvefur <zash@zash.se>
parents:
5040
diff
changeset
|
50 |
|
97385c45e670
mod_storage_sql: Keep connections in a shared cache table
Kim Alvefur <zash@zash.se>
parents:
5040
diff
changeset
|
51 |
|
4096
3b991ceb228e
mod_storage_sql: Make the 'database' value for the SQLite3 driver relative to the data path
Matthew Wild <mwild1@gmail.com>
parents:
4007
diff
changeset
|
52 |
local resolve_relative_path = require "core.configmanager".resolve_relative_path; |
3b991ceb228e
mod_storage_sql: Make the 'database' value for the SQLite3 driver relative to the data path
Matthew Wild <mwild1@gmail.com>
parents:
4007
diff
changeset
|
53 |
|
4105
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
54 |
local function test_connection() |
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
55 |
if not connection then return nil; end |
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
56 |
if connection:ping() then |
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
57 |
return true; |
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
58 |
else |
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
59 |
module:log("debug", "Database connection closed"); |
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
60 |
connection = nil; |
5054
97385c45e670
mod_storage_sql: Keep connections in a shared cache table
Kim Alvefur <zash@zash.se>
parents:
5040
diff
changeset
|
61 |
connections[dburi] = nil; |
4105
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
62 |
end |
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
63 |
end |
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
64 |
local function connect() |
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
65 |
if not test_connection() then |
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
66 |
prosody.unlock_globals(); |
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
67 |
local dbh, err = DBI.Connect( |
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
68 |
params.driver, params.database, |
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
69 |
params.username, params.password, |
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
70 |
params.host, params.port |
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
71 |
); |
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
72 |
prosody.lock_globals(); |
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
73 |
if not dbh then |
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
74 |
module:log("debug", "Database connection failed: %s", tostring(err)); |
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
75 |
return nil, err; |
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
76 |
end |
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
77 |
module:log("debug", "Successfully connected to database"); |
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
78 |
dbh:autocommit(false); -- don't commit automatically |
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
79 |
connection = dbh; |
5054
97385c45e670
mod_storage_sql: Keep connections in a shared cache table
Kim Alvefur <zash@zash.se>
parents:
5040
diff
changeset
|
80 |
|
97385c45e670
mod_storage_sql: Keep connections in a shared cache table
Kim Alvefur <zash@zash.se>
parents:
5040
diff
changeset
|
81 |
connections[dburi] = dbh; |
4105
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
82 |
end |
5056
a125daa42ad4
mod_storage_sql: Return connection from connect even if already connected (thanks IRON)
Matthew Wild <mwild1@gmail.com>
parents:
5055
diff
changeset
|
83 |
return connection; |
4105
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
84 |
end |
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
85 |
|
4109
d26db1f936f8
mod_storage_sql: Create index when creating a new table
Matthew Wild <mwild1@gmail.com>
parents:
4105
diff
changeset
|
86 |
local function create_table() |
4317
5b0fcc5cdd4d
mod_storage_sql: Add sql_manage_tables to disable table creation/updating.
Matthew Wild <mwild1@gmail.com>
parents:
4309
diff
changeset
|
87 |
if not module:get_option("sql_manage_tables", true) then |
5b0fcc5cdd4d
mod_storage_sql: Add sql_manage_tables to disable table creation/updating.
Matthew Wild <mwild1@gmail.com>
parents:
4309
diff
changeset
|
88 |
return; |
5b0fcc5cdd4d
mod_storage_sql: Add sql_manage_tables to disable table creation/updating.
Matthew Wild <mwild1@gmail.com>
parents:
4309
diff
changeset
|
89 |
end |
4109
d26db1f936f8
mod_storage_sql: Create index when creating a new table
Matthew Wild <mwild1@gmail.com>
parents:
4105
diff
changeset
|
90 |
local create_sql = "CREATE TABLE `prosody` (`host` TEXT, `user` TEXT, `store` TEXT, `key` TEXT, `type` TEXT, `value` TEXT);"; |
d26db1f936f8
mod_storage_sql: Create index when creating a new table
Matthew Wild <mwild1@gmail.com>
parents:
4105
diff
changeset
|
91 |
if params.driver == "PostgreSQL" then |
d26db1f936f8
mod_storage_sql: Create index when creating a new table
Matthew Wild <mwild1@gmail.com>
parents:
4105
diff
changeset
|
92 |
create_sql = create_sql:gsub("`", "\""); |
4285
c806a599224a
mod_storage_sql: Switch to MEDIUMTEXT for the 'value' column when using MySQL, as it imposes a 64K limit otherwise, potentially truncating data. Automatically upgrades existing tables.
Matthew Wild <mwild1@gmail.com>
parents:
4218
diff
changeset
|
93 |
elseif params.driver == "MySQL" then |
c806a599224a
mod_storage_sql: Switch to MEDIUMTEXT for the 'value' column when using MySQL, as it imposes a 64K limit otherwise, potentially truncating data. Automatically upgrades existing tables.
Matthew Wild <mwild1@gmail.com>
parents:
4218
diff
changeset
|
94 |
create_sql = create_sql:gsub("`value` TEXT", "`value` MEDIUMTEXT"); |
4109
d26db1f936f8
mod_storage_sql: Create index when creating a new table
Matthew Wild <mwild1@gmail.com>
parents:
4105
diff
changeset
|
95 |
end |
d26db1f936f8
mod_storage_sql: Create index when creating a new table
Matthew Wild <mwild1@gmail.com>
parents:
4105
diff
changeset
|
96 |
|
4318
44b131d7041b
mod_storage_sql: Add extra logging and error handling around table creation
Matthew Wild <mwild1@gmail.com>
parents:
4317
diff
changeset
|
97 |
local stmt, err = connection:prepare(create_sql); |
4109
d26db1f936f8
mod_storage_sql: Create index when creating a new table
Matthew Wild <mwild1@gmail.com>
parents:
4105
diff
changeset
|
98 |
if stmt then |
d26db1f936f8
mod_storage_sql: Create index when creating a new table
Matthew Wild <mwild1@gmail.com>
parents:
4105
diff
changeset
|
99 |
local ok = stmt:execute(); |
d26db1f936f8
mod_storage_sql: Create index when creating a new table
Matthew Wild <mwild1@gmail.com>
parents:
4105
diff
changeset
|
100 |
local commit_ok = connection:commit(); |
d26db1f936f8
mod_storage_sql: Create index when creating a new table
Matthew Wild <mwild1@gmail.com>
parents:
4105
diff
changeset
|
101 |
if ok and commit_ok then |
d26db1f936f8
mod_storage_sql: Create index when creating a new table
Matthew Wild <mwild1@gmail.com>
parents:
4105
diff
changeset
|
102 |
module:log("info", "Initialized new %s database with prosody table", params.driver); |
d26db1f936f8
mod_storage_sql: Create index when creating a new table
Matthew Wild <mwild1@gmail.com>
parents:
4105
diff
changeset
|
103 |
local index_sql = "CREATE INDEX `prosody_index` ON `prosody` (`host`, `user`, `store`, `key`)"; |
d26db1f936f8
mod_storage_sql: Create index when creating a new table
Matthew Wild <mwild1@gmail.com>
parents:
4105
diff
changeset
|
104 |
if params.driver == "PostgreSQL" then |
d26db1f936f8
mod_storage_sql: Create index when creating a new table
Matthew Wild <mwild1@gmail.com>
parents:
4105
diff
changeset
|
105 |
index_sql = index_sql:gsub("`", "\""); |
d26db1f936f8
mod_storage_sql: Create index when creating a new table
Matthew Wild <mwild1@gmail.com>
parents:
4105
diff
changeset
|
106 |
elseif params.driver == "MySQL" then |
d26db1f936f8
mod_storage_sql: Create index when creating a new table
Matthew Wild <mwild1@gmail.com>
parents:
4105
diff
changeset
|
107 |
index_sql = index_sql:gsub("`([,)])", "`(20)%1"); |
d26db1f936f8
mod_storage_sql: Create index when creating a new table
Matthew Wild <mwild1@gmail.com>
parents:
4105
diff
changeset
|
108 |
end |
d26db1f936f8
mod_storage_sql: Create index when creating a new table
Matthew Wild <mwild1@gmail.com>
parents:
4105
diff
changeset
|
109 |
local stmt, err = connection:prepare(index_sql); |
d26db1f936f8
mod_storage_sql: Create index when creating a new table
Matthew Wild <mwild1@gmail.com>
parents:
4105
diff
changeset
|
110 |
local ok, commit_ok, commit_err; |
d26db1f936f8
mod_storage_sql: Create index when creating a new table
Matthew Wild <mwild1@gmail.com>
parents:
4105
diff
changeset
|
111 |
if stmt then |
d26db1f936f8
mod_storage_sql: Create index when creating a new table
Matthew Wild <mwild1@gmail.com>
parents:
4105
diff
changeset
|
112 |
ok, err = stmt:execute(); |
d26db1f936f8
mod_storage_sql: Create index when creating a new table
Matthew Wild <mwild1@gmail.com>
parents:
4105
diff
changeset
|
113 |
commit_ok, commit_err = connection:commit(); |
d26db1f936f8
mod_storage_sql: Create index when creating a new table
Matthew Wild <mwild1@gmail.com>
parents:
4105
diff
changeset
|
114 |
end |
d26db1f936f8
mod_storage_sql: Create index when creating a new table
Matthew Wild <mwild1@gmail.com>
parents:
4105
diff
changeset
|
115 |
if not(ok and commit_ok) then |
d26db1f936f8
mod_storage_sql: Create index when creating a new table
Matthew Wild <mwild1@gmail.com>
parents:
4105
diff
changeset
|
116 |
module:log("warn", "Failed to create index (%s), lookups may not be optimised", err or commit_err); |
d26db1f936f8
mod_storage_sql: Create index when creating a new table
Matthew Wild <mwild1@gmail.com>
parents:
4105
diff
changeset
|
117 |
end |
4309
7ad81d81f5ec
mod_storage_sql: Fix commit c806a599224a for compatibility with non-MySQL databases (thanks Ognyan Kulev)
Matthew Wild <mwild1@gmail.com>
parents:
4297
diff
changeset
|
118 |
elseif params.driver == "MySQL" then -- COMPAT: Upgrade tables from 0.8.0 |
4285
c806a599224a
mod_storage_sql: Switch to MEDIUMTEXT for the 'value' column when using MySQL, as it imposes a 64K limit otherwise, potentially truncating data. Automatically upgrades existing tables.
Matthew Wild <mwild1@gmail.com>
parents:
4218
diff
changeset
|
119 |
-- Failed to create, but check existing MySQL table here |
c806a599224a
mod_storage_sql: Switch to MEDIUMTEXT for the 'value' column when using MySQL, as it imposes a 64K limit otherwise, potentially truncating data. Automatically upgrades existing tables.
Matthew Wild <mwild1@gmail.com>
parents:
4218
diff
changeset
|
120 |
local stmt = connection:prepare("SHOW COLUMNS FROM prosody WHERE Field='value' and Type='text'"); |
c806a599224a
mod_storage_sql: Switch to MEDIUMTEXT for the 'value' column when using MySQL, as it imposes a 64K limit otherwise, potentially truncating data. Automatically upgrades existing tables.
Matthew Wild <mwild1@gmail.com>
parents:
4218
diff
changeset
|
121 |
local ok = stmt:execute(); |
c806a599224a
mod_storage_sql: Switch to MEDIUMTEXT for the 'value' column when using MySQL, as it imposes a 64K limit otherwise, potentially truncating data. Automatically upgrades existing tables.
Matthew Wild <mwild1@gmail.com>
parents:
4218
diff
changeset
|
122 |
local commit_ok = connection:commit(); |
c806a599224a
mod_storage_sql: Switch to MEDIUMTEXT for the 'value' column when using MySQL, as it imposes a 64K limit otherwise, potentially truncating data. Automatically upgrades existing tables.
Matthew Wild <mwild1@gmail.com>
parents:
4218
diff
changeset
|
123 |
if ok and commit_ok then |
c806a599224a
mod_storage_sql: Switch to MEDIUMTEXT for the 'value' column when using MySQL, as it imposes a 64K limit otherwise, potentially truncating data. Automatically upgrades existing tables.
Matthew Wild <mwild1@gmail.com>
parents:
4218
diff
changeset
|
124 |
if stmt:rowcount() > 0 then |
4318
44b131d7041b
mod_storage_sql: Add extra logging and error handling around table creation
Matthew Wild <mwild1@gmail.com>
parents:
4317
diff
changeset
|
125 |
module:log("info", "Upgrading database schema..."); |
4285
c806a599224a
mod_storage_sql: Switch to MEDIUMTEXT for the 'value' column when using MySQL, as it imposes a 64K limit otherwise, potentially truncating data. Automatically upgrades existing tables.
Matthew Wild <mwild1@gmail.com>
parents:
4218
diff
changeset
|
126 |
local stmt = connection:prepare("ALTER TABLE prosody MODIFY COLUMN `value` MEDIUMTEXT"); |
4318
44b131d7041b
mod_storage_sql: Add extra logging and error handling around table creation
Matthew Wild <mwild1@gmail.com>
parents:
4317
diff
changeset
|
127 |
local ok, err = stmt:execute(); |
4285
c806a599224a
mod_storage_sql: Switch to MEDIUMTEXT for the 'value' column when using MySQL, as it imposes a 64K limit otherwise, potentially truncating data. Automatically upgrades existing tables.
Matthew Wild <mwild1@gmail.com>
parents:
4218
diff
changeset
|
128 |
local commit_ok = connection:commit(); |
c806a599224a
mod_storage_sql: Switch to MEDIUMTEXT for the 'value' column when using MySQL, as it imposes a 64K limit otherwise, potentially truncating data. Automatically upgrades existing tables.
Matthew Wild <mwild1@gmail.com>
parents:
4218
diff
changeset
|
129 |
if ok and commit_ok then |
c806a599224a
mod_storage_sql: Switch to MEDIUMTEXT for the 'value' column when using MySQL, as it imposes a 64K limit otherwise, potentially truncating data. Automatically upgrades existing tables.
Matthew Wild <mwild1@gmail.com>
parents:
4218
diff
changeset
|
130 |
module:log("info", "Database table automatically upgraded"); |
4318
44b131d7041b
mod_storage_sql: Add extra logging and error handling around table creation
Matthew Wild <mwild1@gmail.com>
parents:
4317
diff
changeset
|
131 |
else |
44b131d7041b
mod_storage_sql: Add extra logging and error handling around table creation
Matthew Wild <mwild1@gmail.com>
parents:
4317
diff
changeset
|
132 |
module:log("error", "Failed to upgrade database schema (%s), please see " |
44b131d7041b
mod_storage_sql: Add extra logging and error handling around table creation
Matthew Wild <mwild1@gmail.com>
parents:
4317
diff
changeset
|
133 |
.."http://prosody.im/doc/mysql for help", |
44b131d7041b
mod_storage_sql: Add extra logging and error handling around table creation
Matthew Wild <mwild1@gmail.com>
parents:
4317
diff
changeset
|
134 |
err or "unknown error"); |
4285
c806a599224a
mod_storage_sql: Switch to MEDIUMTEXT for the 'value' column when using MySQL, as it imposes a 64K limit otherwise, potentially truncating data. Automatically upgrades existing tables.
Matthew Wild <mwild1@gmail.com>
parents:
4218
diff
changeset
|
135 |
end |
c806a599224a
mod_storage_sql: Switch to MEDIUMTEXT for the 'value' column when using MySQL, as it imposes a 64K limit otherwise, potentially truncating data. Automatically upgrades existing tables.
Matthew Wild <mwild1@gmail.com>
parents:
4218
diff
changeset
|
136 |
end |
c806a599224a
mod_storage_sql: Switch to MEDIUMTEXT for the 'value' column when using MySQL, as it imposes a 64K limit otherwise, potentially truncating data. Automatically upgrades existing tables.
Matthew Wild <mwild1@gmail.com>
parents:
4218
diff
changeset
|
137 |
repeat until not stmt:fetch(); |
c806a599224a
mod_storage_sql: Switch to MEDIUMTEXT for the 'value' column when using MySQL, as it imposes a 64K limit otherwise, potentially truncating data. Automatically upgrades existing tables.
Matthew Wild <mwild1@gmail.com>
parents:
4218
diff
changeset
|
138 |
end |
4109
d26db1f936f8
mod_storage_sql: Create index when creating a new table
Matthew Wild <mwild1@gmail.com>
parents:
4105
diff
changeset
|
139 |
end |
4318
44b131d7041b
mod_storage_sql: Add extra logging and error handling around table creation
Matthew Wild <mwild1@gmail.com>
parents:
4317
diff
changeset
|
140 |
elseif params.driver ~= "SQLite3" then -- SQLite normally fails to prepare for existing table |
44b131d7041b
mod_storage_sql: Add extra logging and error handling around table creation
Matthew Wild <mwild1@gmail.com>
parents:
4317
diff
changeset
|
141 |
module:log("warn", "Prosody was not able to automatically check/create the database table (%s), " |
44b131d7041b
mod_storage_sql: Add extra logging and error handling around table creation
Matthew Wild <mwild1@gmail.com>
parents:
4317
diff
changeset
|
142 |
.."see http://prosody.im/doc/modules/mod_storage_sql#table_management for help.", |
44b131d7041b
mod_storage_sql: Add extra logging and error handling around table creation
Matthew Wild <mwild1@gmail.com>
parents:
4317
diff
changeset
|
143 |
err or "unknown error"); |
4109
d26db1f936f8
mod_storage_sql: Create index when creating a new table
Matthew Wild <mwild1@gmail.com>
parents:
4105
diff
changeset
|
144 |
end |
d26db1f936f8
mod_storage_sql: Create index when creating a new table
Matthew Wild <mwild1@gmail.com>
parents:
4105
diff
changeset
|
145 |
end |
d26db1f936f8
mod_storage_sql: Create index when creating a new table
Matthew Wild <mwild1@gmail.com>
parents:
4105
diff
changeset
|
146 |
|
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
147 |
do -- process options to get a db connection |
4218
8a271b3fcaa7
mod_storage_sql: Display friendlier error when LuaDBI is missing
Matthew Wild <mwild1@gmail.com>
parents:
4109
diff
changeset
|
148 |
local ok; |
8a271b3fcaa7
mod_storage_sql: Display friendlier error when LuaDBI is missing
Matthew Wild <mwild1@gmail.com>
parents:
4109
diff
changeset
|
149 |
prosody.unlock_globals(); |
8a271b3fcaa7
mod_storage_sql: Display friendlier error when LuaDBI is missing
Matthew Wild <mwild1@gmail.com>
parents:
4109
diff
changeset
|
150 |
ok, DBI = pcall(require, "DBI"); |
8a271b3fcaa7
mod_storage_sql: Display friendlier error when LuaDBI is missing
Matthew Wild <mwild1@gmail.com>
parents:
4109
diff
changeset
|
151 |
if not ok then |
8a271b3fcaa7
mod_storage_sql: Display friendlier error when LuaDBI is missing
Matthew Wild <mwild1@gmail.com>
parents:
4109
diff
changeset
|
152 |
package.loaded["DBI"] = {}; |
8a271b3fcaa7
mod_storage_sql: Display friendlier error when LuaDBI is missing
Matthew Wild <mwild1@gmail.com>
parents:
4109
diff
changeset
|
153 |
module:log("error", "Failed to load the LuaDBI library for accessing SQL databases: %s", DBI); |
8a271b3fcaa7
mod_storage_sql: Display friendlier error when LuaDBI is missing
Matthew Wild <mwild1@gmail.com>
parents:
4109
diff
changeset
|
154 |
module:log("error", "More information on installing LuaDBI can be found at http://prosody.im/doc/depends#luadbi"); |
8a271b3fcaa7
mod_storage_sql: Display friendlier error when LuaDBI is missing
Matthew Wild <mwild1@gmail.com>
parents:
4109
diff
changeset
|
155 |
end |
8a271b3fcaa7
mod_storage_sql: Display friendlier error when LuaDBI is missing
Matthew Wild <mwild1@gmail.com>
parents:
4109
diff
changeset
|
156 |
prosody.lock_globals(); |
8a271b3fcaa7
mod_storage_sql: Display friendlier error when LuaDBI is missing
Matthew Wild <mwild1@gmail.com>
parents:
4109
diff
changeset
|
157 |
if not ok or not DBI.Connect then |
8a271b3fcaa7
mod_storage_sql: Display friendlier error when LuaDBI is missing
Matthew Wild <mwild1@gmail.com>
parents:
4109
diff
changeset
|
158 |
return; -- Halt loading of this module |
8a271b3fcaa7
mod_storage_sql: Display friendlier error when LuaDBI is missing
Matthew Wild <mwild1@gmail.com>
parents:
4109
diff
changeset
|
159 |
end |
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
160 |
|
4096
3b991ceb228e
mod_storage_sql: Make the 'database' value for the SQLite3 driver relative to the data path
Matthew Wild <mwild1@gmail.com>
parents:
4007
diff
changeset
|
161 |
params = params or { driver = "SQLite3" }; |
3b991ceb228e
mod_storage_sql: Make the 'database' value for the SQLite3 driver relative to the data path
Matthew Wild <mwild1@gmail.com>
parents:
4007
diff
changeset
|
162 |
|
3b991ceb228e
mod_storage_sql: Make the 'database' value for the SQLite3 driver relative to the data path
Matthew Wild <mwild1@gmail.com>
parents:
4007
diff
changeset
|
163 |
if params.driver == "SQLite3" then |
3b991ceb228e
mod_storage_sql: Make the 'database' value for the SQLite3 driver relative to the data path
Matthew Wild <mwild1@gmail.com>
parents:
4007
diff
changeset
|
164 |
params.database = resolve_relative_path(prosody.paths.data or ".", params.database or "prosody.sqlite"); |
3b991ceb228e
mod_storage_sql: Make the 'database' value for the SQLite3 driver relative to the data path
Matthew Wild <mwild1@gmail.com>
parents:
4007
diff
changeset
|
165 |
end |
3b991ceb228e
mod_storage_sql: Make the 'database' value for the SQLite3 driver relative to the data path
Matthew Wild <mwild1@gmail.com>
parents:
4007
diff
changeset
|
166 |
|
3b991ceb228e
mod_storage_sql: Make the 'database' value for the SQLite3 driver relative to the data path
Matthew Wild <mwild1@gmail.com>
parents:
4007
diff
changeset
|
167 |
assert(params.driver and params.database, "Both the SQL driver and the database need to be specified"); |
5054
97385c45e670
mod_storage_sql: Keep connections in a shared cache table
Kim Alvefur <zash@zash.se>
parents:
5040
diff
changeset
|
168 |
|
97385c45e670
mod_storage_sql: Keep connections in a shared cache table
Kim Alvefur <zash@zash.se>
parents:
5040
diff
changeset
|
169 |
dburi = db2uri(params); |
97385c45e670
mod_storage_sql: Keep connections in a shared cache table
Kim Alvefur <zash@zash.se>
parents:
5040
diff
changeset
|
170 |
connection = connections[dburi]; |
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
171 |
|
4105
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
172 |
assert(connect()); |
3732
bf449ecf2144
mod_storage_sql: Auto-initialize SQLite3 database.
Waqas Hussain <waqas20@gmail.com>
parents:
3731
diff
changeset
|
173 |
|
4101
06778bc27d53
mod_storage_sql: Create table automatically for all databases now, not just SQLite. Also rename table from Prosody -> prosody.
Matthew Wild <mwild1@gmail.com>
parents:
4096
diff
changeset
|
174 |
-- Automatically create table, ignore failure (table probably already exists) |
4109
d26db1f936f8
mod_storage_sql: Create index when creating a new table
Matthew Wild <mwild1@gmail.com>
parents:
4105
diff
changeset
|
175 |
create_table(); |
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
176 |
end |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
177 |
|
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
178 |
local function serialize(value) |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
179 |
local t = type(value); |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
180 |
if t == "string" or t == "boolean" or t == "number" then |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
181 |
return t, tostring(value); |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
182 |
elseif t == "table" then |
3980
6b2fac6602b3
mod_storage_sql: Use util.json instead of util.serialization.
Waqas Hussain <waqas20@gmail.com>
parents:
3978
diff
changeset
|
183 |
local value,err = json.encode(value); |
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
184 |
if value then return "json", value; end |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
185 |
return nil, err; |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
186 |
end |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
187 |
return nil, "Unhandled value type: "..t; |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
188 |
end |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
189 |
local function deserialize(t, value) |
3743
5adfb8d0444d
mod_storage_sql: Fixed the deserialization of string-typed values.
Waqas Hussain <waqas20@gmail.com>
parents:
3732
diff
changeset
|
190 |
if t == "string" then return value; |
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
191 |
elseif t == "boolean" then |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
192 |
if value == "true" then return true; |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
193 |
elseif value == "false" then return false; end |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
194 |
elseif t == "number" then return tonumber(value); |
3772
e1f6fe098404
mod_storage_sql: Fix a couple of bugs in "JSON" decoding
Matthew Wild <mwild1@gmail.com>
parents:
3744
diff
changeset
|
195 |
elseif t == "json" then |
3980
6b2fac6602b3
mod_storage_sql: Use util.json instead of util.serialization.
Waqas Hussain <waqas20@gmail.com>
parents:
3978
diff
changeset
|
196 |
return json.decode(value); |
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
197 |
end |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
198 |
end |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
199 |
|
5034
2dbb3bf74090
mod_storage_sql: Split out query handling logic from getsql() into a separate function
Kim Alvefur <zash@zash.se>
parents:
4318
diff
changeset
|
200 |
local function dosql(sql, ...) |
3976
16170a66e140
mod_storage_sql: Dynamically replace backquotes with double quotes when connecting to PostgreSQL...
Waqas Hussain <waqas20@gmail.com>
parents:
3975
diff
changeset
|
201 |
if params.driver == "PostgreSQL" then |
16170a66e140
mod_storage_sql: Dynamically replace backquotes with double quotes when connecting to PostgreSQL...
Waqas Hussain <waqas20@gmail.com>
parents:
3975
diff
changeset
|
202 |
sql = sql:gsub("`", "\""); |
16170a66e140
mod_storage_sql: Dynamically replace backquotes with double quotes when connecting to PostgreSQL...
Waqas Hussain <waqas20@gmail.com>
parents:
3975
diff
changeset
|
203 |
end |
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
204 |
-- do prepared statement stuff |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
205 |
local stmt, err = connection:prepare(sql); |
4105
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
206 |
if not stmt and not test_connection() then error("connection failed"); end |
3978
13ee740b1f89
mod_storage_sql: Log an error on query failure.
Waqas Hussain <waqas20@gmail.com>
parents:
3977
diff
changeset
|
207 |
if not stmt then module:log("error", "QUERY FAILED: %s %s", err, debug.traceback()); return nil, err; end |
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
208 |
-- run query |
5034
2dbb3bf74090
mod_storage_sql: Split out query handling logic from getsql() into a separate function
Kim Alvefur <zash@zash.se>
parents:
4318
diff
changeset
|
209 |
local ok, err = stmt:execute(...); |
4105
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
210 |
if not ok and not test_connection() then error("connection failed"); end |
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
211 |
if not ok then return nil, err; end |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
212 |
|
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
213 |
return stmt; |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
214 |
end |
5034
2dbb3bf74090
mod_storage_sql: Split out query handling logic from getsql() into a separate function
Kim Alvefur <zash@zash.se>
parents:
4318
diff
changeset
|
215 |
local function getsql(sql, ...) |
2dbb3bf74090
mod_storage_sql: Split out query handling logic from getsql() into a separate function
Kim Alvefur <zash@zash.se>
parents:
4318
diff
changeset
|
216 |
return dosql(sql, host or "", user or "", store or "", ...); |
2dbb3bf74090
mod_storage_sql: Split out query handling logic from getsql() into a separate function
Kim Alvefur <zash@zash.se>
parents:
4318
diff
changeset
|
217 |
end |
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
218 |
local function setsql(sql, ...) |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
219 |
local stmt, err = getsql(sql, ...); |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
220 |
if not stmt then return stmt, err; end |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
221 |
return stmt:affected(); |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
222 |
end |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
223 |
local function transact(...) |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
224 |
-- ... |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
225 |
end |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
226 |
local function rollback(...) |
4105
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
227 |
if connection then connection:rollback(); end -- FIXME check for rollback error? |
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
228 |
return ...; |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
229 |
end |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
230 |
local function commit(...) |
5209
f5d121846d53
mod_storage_sql: Return actual error string on commit failure.
Waqas Hussain <waqas20@gmail.com>
parents:
5154
diff
changeset
|
231 |
local success,err = connection:commit(); |
f5d121846d53
mod_storage_sql: Return actual error string on commit failure.
Waqas Hussain <waqas20@gmail.com>
parents:
5154
diff
changeset
|
232 |
if not success then return nil, "SQL commit failed: "..tostring(err); end |
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
233 |
return ...; |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
234 |
end |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
235 |
|
4007
062b849ca088
mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents:
4004
diff
changeset
|
236 |
local function keyval_store_get() |
4101
06778bc27d53
mod_storage_sql: Create table automatically for all databases now, not just SQLite. Also rename table from Prosody -> prosody.
Matthew Wild <mwild1@gmail.com>
parents:
4096
diff
changeset
|
237 |
local stmt, err = getsql("SELECT * FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=?"); |
4105
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
238 |
if not stmt then return rollback(nil, err); end |
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
239 |
|
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
240 |
local haveany; |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
241 |
local result = {}; |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
242 |
for row in stmt:rows(true) do |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
243 |
haveany = true; |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
244 |
local k = row.key; |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
245 |
local v = deserialize(row.type, row.value); |
3977
6724853adb80
mod_storage_sql: Remove the subkey column from the Prosody table, and make the map store compatible with the key-value store.
Waqas Hussain <waqas20@gmail.com>
parents:
3976
diff
changeset
|
246 |
if k and v then |
6724853adb80
mod_storage_sql: Remove the subkey column from the Prosody table, and make the map store compatible with the key-value store.
Waqas Hussain <waqas20@gmail.com>
parents:
3976
diff
changeset
|
247 |
if k ~= "" then result[k] = v; elseif type(v) == "table" then |
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
248 |
for a,b in pairs(v) do |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
249 |
result[a] = b; |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
250 |
end |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
251 |
end |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
252 |
end |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
253 |
end |
3975
aa5e93e61760
mod_storage_sql: Call commit() after all SQL statements, including SELECT, to get SQLite to drop its locks.
Waqas Hussain <waqas20@gmail.com>
parents:
3974
diff
changeset
|
254 |
return commit(haveany and result or nil); |
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
255 |
end |
4007
062b849ca088
mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents:
4004
diff
changeset
|
256 |
local function keyval_store_set(data) |
4101
06778bc27d53
mod_storage_sql: Create table automatically for all databases now, not just SQLite. Also rename table from Prosody -> prosody.
Matthew Wild <mwild1@gmail.com>
parents:
4096
diff
changeset
|
257 |
local affected, err = setsql("DELETE FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=?"); |
4105
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
258 |
if not affected then return rollback(affected, err); end |
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
259 |
|
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
260 |
if data and next(data) ~= nil then |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
261 |
local extradata = {}; |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
262 |
for key, value in pairs(data) do |
3974
af40a7ce4f77
mod_storage_sql: Quote identifiers in SQL with backquotes, and use the empty string for NULL, and '=' instead of 'IS' for comparison, to work with MySQL's limitations...
Waqas Hussain <waqas20@gmail.com>
parents:
3772
diff
changeset
|
263 |
if type(key) == "string" and key ~= "" then |
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
264 |
local t, value = serialize(value); |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
265 |
if not t then return rollback(t, value); end |
4101
06778bc27d53
mod_storage_sql: Create table automatically for all databases now, not just SQLite. Also rename table from Prosody -> prosody.
Matthew Wild <mwild1@gmail.com>
parents:
4096
diff
changeset
|
266 |
local ok, err = setsql("INSERT INTO `prosody` (`host`,`user`,`store`,`key`,`type`,`value`) VALUES (?,?,?,?,?,?)", key, t, value); |
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
267 |
if not ok then return rollback(ok, err); end |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
268 |
else |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
269 |
extradata[key] = value; |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
270 |
end |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
271 |
end |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
272 |
if next(extradata) ~= nil then |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
273 |
local t, extradata = serialize(extradata); |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
274 |
if not t then return rollback(t, extradata); end |
4101
06778bc27d53
mod_storage_sql: Create table automatically for all databases now, not just SQLite. Also rename table from Prosody -> prosody.
Matthew Wild <mwild1@gmail.com>
parents:
4096
diff
changeset
|
275 |
local ok, err = setsql("INSERT INTO `prosody` (`host`,`user`,`store`,`key`,`type`,`value`) VALUES (?,?,?,?,?,?)", "", t, extradata); |
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
276 |
if not ok then return rollback(ok, err); end |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
277 |
end |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
278 |
end |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
279 |
return commit(true); |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
280 |
end |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
281 |
|
4007
062b849ca088
mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents:
4004
diff
changeset
|
282 |
local keyval_store = {}; |
062b849ca088
mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents:
4004
diff
changeset
|
283 |
keyval_store.__index = keyval_store; |
062b849ca088
mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents:
4004
diff
changeset
|
284 |
function keyval_store:get(username) |
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
285 |
user,store = username,self.store; |
4105
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
286 |
if not connection and not connect() then return nil, "Unable to connect to database"; end |
4007
062b849ca088
mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents:
4004
diff
changeset
|
287 |
local success, ret, err = xpcall(keyval_store_get, debug.traceback); |
4105
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
288 |
if not connection and connect() then |
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
289 |
success, ret, err = xpcall(keyval_store_get, debug.traceback); |
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
290 |
end |
4007
062b849ca088
mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents:
4004
diff
changeset
|
291 |
if success then return ret, err; else return rollback(nil, ret); end |
062b849ca088
mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents:
4004
diff
changeset
|
292 |
end |
062b849ca088
mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents:
4004
diff
changeset
|
293 |
function keyval_store:set(username, data) |
062b849ca088
mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents:
4004
diff
changeset
|
294 |
user,store = username,self.store; |
4105
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
295 |
if not connection and not connect() then return nil, "Unable to connect to database"; end |
4007
062b849ca088
mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents:
4004
diff
changeset
|
296 |
local success, ret, err = xpcall(function() return keyval_store_set(data); end, debug.traceback); |
4105
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
297 |
if not connection and connect() then |
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
298 |
success, ret, err = xpcall(function() return keyval_store_set(data); end, debug.traceback); |
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
299 |
end |
4007
062b849ca088
mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents:
4004
diff
changeset
|
300 |
if success then return ret, err; else return rollback(nil, ret); end |
062b849ca088
mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents:
4004
diff
changeset
|
301 |
end |
5154
429c8eeea035
mod_storage_sql: Add support for iterating over users with data in a store
Kim Alvefur <zash@zash.se>
parents:
5133
diff
changeset
|
302 |
function keyval_store:users() |
429c8eeea035
mod_storage_sql: Add support for iterating over users with data in a store
Kim Alvefur <zash@zash.se>
parents:
5133
diff
changeset
|
303 |
local stmt, err = dosql("SELECT DISTINCT `user` FROM `prosody` WHERE `host`=? AND `store`=?", host, self.store); |
429c8eeea035
mod_storage_sql: Add support for iterating over users with data in a store
Kim Alvefur <zash@zash.se>
parents:
5133
diff
changeset
|
304 |
if not stmt then |
429c8eeea035
mod_storage_sql: Add support for iterating over users with data in a store
Kim Alvefur <zash@zash.se>
parents:
5133
diff
changeset
|
305 |
return rollback(nil, err); |
429c8eeea035
mod_storage_sql: Add support for iterating over users with data in a store
Kim Alvefur <zash@zash.se>
parents:
5133
diff
changeset
|
306 |
end |
429c8eeea035
mod_storage_sql: Add support for iterating over users with data in a store
Kim Alvefur <zash@zash.se>
parents:
5133
diff
changeset
|
307 |
local next = stmt:rows(); |
429c8eeea035
mod_storage_sql: Add support for iterating over users with data in a store
Kim Alvefur <zash@zash.se>
parents:
5133
diff
changeset
|
308 |
return commit(function() |
429c8eeea035
mod_storage_sql: Add support for iterating over users with data in a store
Kim Alvefur <zash@zash.se>
parents:
5133
diff
changeset
|
309 |
local row = next(); |
429c8eeea035
mod_storage_sql: Add support for iterating over users with data in a store
Kim Alvefur <zash@zash.se>
parents:
5133
diff
changeset
|
310 |
return row and row[1]; |
429c8eeea035
mod_storage_sql: Add support for iterating over users with data in a store
Kim Alvefur <zash@zash.se>
parents:
5133
diff
changeset
|
311 |
end); |
429c8eeea035
mod_storage_sql: Add support for iterating over users with data in a store
Kim Alvefur <zash@zash.se>
parents:
5133
diff
changeset
|
312 |
end |
4007
062b849ca088
mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents:
4004
diff
changeset
|
313 |
|
062b849ca088
mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents:
4004
diff
changeset
|
314 |
local function map_store_get(key) |
4101
06778bc27d53
mod_storage_sql: Create table automatically for all databases now, not just SQLite. Also rename table from Prosody -> prosody.
Matthew Wild <mwild1@gmail.com>
parents:
4096
diff
changeset
|
315 |
local stmt, err = getsql("SELECT * FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?", key or ""); |
4105
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
316 |
if not stmt then return rollback(nil, err); end |
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
317 |
|
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
318 |
local haveany; |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
319 |
local result = {}; |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
320 |
for row in stmt:rows(true) do |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
321 |
haveany = true; |
3977
6724853adb80
mod_storage_sql: Remove the subkey column from the Prosody table, and make the map store compatible with the key-value store.
Waqas Hussain <waqas20@gmail.com>
parents:
3976
diff
changeset
|
322 |
local k = row.key; |
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
323 |
local v = deserialize(row.type, row.value); |
3977
6724853adb80
mod_storage_sql: Remove the subkey column from the Prosody table, and make the map store compatible with the key-value store.
Waqas Hussain <waqas20@gmail.com>
parents:
3976
diff
changeset
|
324 |
if k and v then |
6724853adb80
mod_storage_sql: Remove the subkey column from the Prosody table, and make the map store compatible with the key-value store.
Waqas Hussain <waqas20@gmail.com>
parents:
3976
diff
changeset
|
325 |
if k ~= "" then result[k] = v; elseif type(v) == "table" then |
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
326 |
for a,b in pairs(v) do |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
327 |
result[a] = b; |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
328 |
end |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
329 |
end |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
330 |
end |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
331 |
end |
3977
6724853adb80
mod_storage_sql: Remove the subkey column from the Prosody table, and make the map store compatible with the key-value store.
Waqas Hussain <waqas20@gmail.com>
parents:
3976
diff
changeset
|
332 |
return commit(haveany and result[key] or nil); |
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
333 |
end |
4007
062b849ca088
mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents:
4004
diff
changeset
|
334 |
local function map_store_set(key, data) |
4101
06778bc27d53
mod_storage_sql: Create table automatically for all databases now, not just SQLite. Also rename table from Prosody -> prosody.
Matthew Wild <mwild1@gmail.com>
parents:
4096
diff
changeset
|
335 |
local affected, err = setsql("DELETE FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?", key or ""); |
4105
08560575762f
mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents:
4101
diff
changeset
|
336 |
if not affected then return rollback(affected, err); end |
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
337 |
|
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
338 |
if data and next(data) ~= nil then |
3977
6724853adb80
mod_storage_sql: Remove the subkey column from the Prosody table, and make the map store compatible with the key-value store.
Waqas Hussain <waqas20@gmail.com>
parents:
3976
diff
changeset
|
339 |
if type(key) == "string" and key ~= "" then |
6724853adb80
mod_storage_sql: Remove the subkey column from the Prosody table, and make the map store compatible with the key-value store.
Waqas Hussain <waqas20@gmail.com>
parents:
3976
diff
changeset
|
340 |
local t, value = serialize(data); |
6724853adb80
mod_storage_sql: Remove the subkey column from the Prosody table, and make the map store compatible with the key-value store.
Waqas Hussain <waqas20@gmail.com>
parents:
3976
diff
changeset
|
341 |
if not t then return rollback(t, value); end |
4101
06778bc27d53
mod_storage_sql: Create table automatically for all databases now, not just SQLite. Also rename table from Prosody -> prosody.
Matthew Wild <mwild1@gmail.com>
parents:
4096
diff
changeset
|
342 |
local ok, err = setsql("INSERT INTO `prosody` (`host`,`user`,`store`,`key`,`type`,`value`) VALUES (?,?,?,?,?,?)", key, t, value); |
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
343 |
if not ok then return rollback(ok, err); end |
3977
6724853adb80
mod_storage_sql: Remove the subkey column from the Prosody table, and make the map store compatible with the key-value store.
Waqas Hussain <waqas20@gmail.com>
parents:
3976
diff
changeset
|
344 |
else |
6724853adb80
mod_storage_sql: Remove the subkey column from the Prosody table, and make the map store compatible with the key-value store.
Waqas Hussain <waqas20@gmail.com>
parents:
3976
diff
changeset
|
345 |
-- TODO non-string keys |
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
346 |
end |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
347 |
end |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
348 |
return commit(true); |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
349 |
end |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
350 |
|
4007
062b849ca088
mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents:
4004
diff
changeset
|
351 |
local map_store = {}; |
062b849ca088
mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents:
4004
diff
changeset
|
352 |
map_store.__index = map_store; |
062b849ca088
mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents:
4004
diff
changeset
|
353 |
function map_store:get(username, key) |
062b849ca088
mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents:
4004
diff
changeset
|
354 |
user,store = username,self.store; |
062b849ca088
mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents:
4004
diff
changeset
|
355 |
local success, ret, err = xpcall(function() return map_store_get(key); end, debug.traceback); |
062b849ca088
mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents:
4004
diff
changeset
|
356 |
if success then return ret, err; else return rollback(nil, ret); end |
062b849ca088
mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents:
4004
diff
changeset
|
357 |
end |
062b849ca088
mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents:
4004
diff
changeset
|
358 |
function map_store:set(username, key, data) |
062b849ca088
mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents:
4004
diff
changeset
|
359 |
user,store = username,self.store; |
062b849ca088
mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents:
4004
diff
changeset
|
360 |
local success, ret, err = xpcall(function() return map_store_set(key, data); end, debug.traceback); |
062b849ca088
mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents:
4004
diff
changeset
|
361 |
if success then return ret, err; else return rollback(nil, ret); end |
062b849ca088
mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents:
4004
diff
changeset
|
362 |
end |
062b849ca088
mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents:
4004
diff
changeset
|
363 |
|
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
364 |
local list_store = {}; |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
365 |
list_store.__index = list_store; |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
366 |
function list_store:scan(username, from, to, jid, typ) |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
367 |
user,store = username,self.store; |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
368 |
|
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
369 |
local cols = {"from", "to", "jid", "typ"}; |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
370 |
local vals = { from , to , jid , typ }; |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
371 |
local stmt, err; |
4101
06778bc27d53
mod_storage_sql: Create table automatically for all databases now, not just SQLite. Also rename table from Prosody -> prosody.
Matthew Wild <mwild1@gmail.com>
parents:
4096
diff
changeset
|
372 |
local query = "SELECT * FROM `prosodyarchive` WHERE `host`=? AND `user`=? AND `store`=?"; |
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
373 |
|
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
374 |
query = query.." ORDER BY time"; |
4101
06778bc27d53
mod_storage_sql: Create table automatically for all databases now, not just SQLite. Also rename table from Prosody -> prosody.
Matthew Wild <mwild1@gmail.com>
parents:
4096
diff
changeset
|
375 |
--local stmt, err = getsql("SELECT * FROM `prosody` WHERE `host`=? AND `user`=? AND `store`=? AND `key`=?", key or ""); |
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
376 |
|
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
377 |
return nil, "not-implemented" |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
378 |
end |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
379 |
|
5122
b41c33dc7c36
mod_storage_*: Don't explicitly set driver name, to ease copying/renaming modules.
Waqas Hussain <waqas20@gmail.com>
parents:
5121
diff
changeset
|
380 |
local driver = {}; |
3729
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
381 |
|
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
382 |
function driver:open(store, typ) |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
383 |
if not typ then -- default key-value store |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
384 |
return setmetatable({ store = store }, keyval_store); |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
385 |
end |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
386 |
return nil, "unsupported-store"; |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
387 |
end |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
388 |
|
5132
ce6546f867f9
mod_storage_sql: Remove obsolete comment
Matthew Wild <mwild1@gmail.com>
parents:
5130
diff
changeset
|
389 |
function driver:stores(username) |
5130
051d352ed03c
storagemanager, datamanager, mod_storage_{internal,sql}: Replace list_stores() with an iterator version
Kim Alvefur <zash@zash.se>
parents:
5056
diff
changeset
|
390 |
local sql = "SELECT DISTINCT `store` FROM `prosody` WHERE `host`=? AND `user`" .. |
051d352ed03c
storagemanager, datamanager, mod_storage_{internal,sql}: Replace list_stores() with an iterator version
Kim Alvefur <zash@zash.se>
parents:
5056
diff
changeset
|
391 |
(username == true and "!=?" or "=?"); |
5035
874cab7b4b3e
mod_storage_sql: Add method for listing stores
Kim Alvefur <zash@zash.se>
parents:
5034
diff
changeset
|
392 |
if username == true or not username then |
874cab7b4b3e
mod_storage_sql: Add method for listing stores
Kim Alvefur <zash@zash.se>
parents:
5034
diff
changeset
|
393 |
username = ""; |
874cab7b4b3e
mod_storage_sql: Add method for listing stores
Kim Alvefur <zash@zash.se>
parents:
5034
diff
changeset
|
394 |
end |
874cab7b4b3e
mod_storage_sql: Add method for listing stores
Kim Alvefur <zash@zash.se>
parents:
5034
diff
changeset
|
395 |
local stmt, err = dosql(sql, host, username); |
874cab7b4b3e
mod_storage_sql: Add method for listing stores
Kim Alvefur <zash@zash.se>
parents:
5034
diff
changeset
|
396 |
if not stmt then |
5055
d466d2088a61
mod_storage_sql: Complete transactions in list_stores and purge
Kim Alvefur <zash@zash.se>
parents:
5054
diff
changeset
|
397 |
return rollback(nil, err); |
5035
874cab7b4b3e
mod_storage_sql: Add method for listing stores
Kim Alvefur <zash@zash.se>
parents:
5034
diff
changeset
|
398 |
end |
5130
051d352ed03c
storagemanager, datamanager, mod_storage_{internal,sql}: Replace list_stores() with an iterator version
Kim Alvefur <zash@zash.se>
parents:
5056
diff
changeset
|
399 |
local next = stmt:rows(); |
051d352ed03c
storagemanager, datamanager, mod_storage_{internal,sql}: Replace list_stores() with an iterator version
Kim Alvefur <zash@zash.se>
parents:
5056
diff
changeset
|
400 |
return commit(function() |
051d352ed03c
storagemanager, datamanager, mod_storage_{internal,sql}: Replace list_stores() with an iterator version
Kim Alvefur <zash@zash.se>
parents:
5056
diff
changeset
|
401 |
local row = next(); |
051d352ed03c
storagemanager, datamanager, mod_storage_{internal,sql}: Replace list_stores() with an iterator version
Kim Alvefur <zash@zash.se>
parents:
5056
diff
changeset
|
402 |
return row and row[1]; |
051d352ed03c
storagemanager, datamanager, mod_storage_{internal,sql}: Replace list_stores() with an iterator version
Kim Alvefur <zash@zash.se>
parents:
5056
diff
changeset
|
403 |
end); |
5035
874cab7b4b3e
mod_storage_sql: Add method for listing stores
Kim Alvefur <zash@zash.se>
parents:
5034
diff
changeset
|
404 |
end |
874cab7b4b3e
mod_storage_sql: Add method for listing stores
Kim Alvefur <zash@zash.se>
parents:
5034
diff
changeset
|
405 |
|
5040
685403a6fee1
mod_storage_sql: Add method for removing all data belonging to a user
Kim Alvefur <zash@zash.se>
parents:
5035
diff
changeset
|
406 |
function driver:purge(username) |
685403a6fee1
mod_storage_sql: Add method for removing all data belonging to a user
Kim Alvefur <zash@zash.se>
parents:
5035
diff
changeset
|
407 |
local stmt, err = dosql("DELETE FROM `prosody` WHERE `host`=? AND `user`=?", host, username); |
5055
d466d2088a61
mod_storage_sql: Complete transactions in list_stores and purge
Kim Alvefur <zash@zash.se>
parents:
5054
diff
changeset
|
408 |
if not stmt then return rollback(stmt, err); end |
5040
685403a6fee1
mod_storage_sql: Add method for removing all data belonging to a user
Kim Alvefur <zash@zash.se>
parents:
5035
diff
changeset
|
409 |
local changed, err = stmt:affected(); |
5055
d466d2088a61
mod_storage_sql: Complete transactions in list_stores and purge
Kim Alvefur <zash@zash.se>
parents:
5054
diff
changeset
|
410 |
if not changed then return rollback(changed, err); end |
d466d2088a61
mod_storage_sql: Complete transactions in list_stores and purge
Kim Alvefur <zash@zash.se>
parents:
5054
diff
changeset
|
411 |
return commit(true, changed); |
5040
685403a6fee1
mod_storage_sql: Add method for removing all data belonging to a user
Kim Alvefur <zash@zash.se>
parents:
5035
diff
changeset
|
412 |
end |
685403a6fee1
mod_storage_sql: Add method for removing all data belonging to a user
Kim Alvefur <zash@zash.se>
parents:
5035
diff
changeset
|
413 |
|
5121
b5a5643f8572
core.storagemanager, mod_storage_*: "data-driver" -> "storage-provider", to allow using module:provides().
Waqas Hussain <waqas20@gmail.com>
parents:
5056
diff
changeset
|
414 |
module:provides("storage", driver); |