prosody
d466d2088a61
plugins/mod_storage_sql.lua

Find changesets by keywords by author, files, the commit message, revision number or hash, or revset expression.

Software / code / prosody

Annotate

plugins/mod_storage_sql.lua @ 5055:d466d2088a61

mod_storage_sql: Complete transactions in list_stores and purge
author Kim Alvefur <zash@zash.se>
date Mon, 30 Jul 2012 06:01:41 +0200
parent 5054:97385c45e670
child 5056:a125daa42ad4
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
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 return connection;
08560575762f mod_storage_sql: Reconnect in all cases, and rollback on error.
Waqas Hussain <waqas20@gmail.com>
parents: 4101
diff changeset
83 end
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(...)
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
231 if not connection:commit() then return nil, "SQL commit failed"; end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
232 return ...;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
233 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
234
4007
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
235 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
236 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
237 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
238
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
239 local haveany;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
240 local result = {};
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
241 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
242 haveany = true;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
243 local k = row.key;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
244 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
245 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
246 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
247 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
248 result[a] = b;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
249 end
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
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
253 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
254 end
4007
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
255 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
256 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
257 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
258
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
259 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
260 local extradata = {};
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
261 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
262 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
263 local t, value = serialize(value);
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
264 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
265 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
266 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
267 else
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
268 extradata[key] = value;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
269 end
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 if next(extradata) ~= nil then
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
272 local t, extradata = serialize(extradata);
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
273 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
274 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
275 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
276 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 return commit(true);
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
279 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
280
4007
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
281 local keyval_store = {};
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
282 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
283 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
284 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
285 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
286 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
287 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
288 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
289 end
4007
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
290 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
291 end
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
292 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
293 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
294 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
295 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
296 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
297 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
298 end
4007
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
299 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
300 end
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
301
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
302 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
303 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
304 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
305
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
306 local haveany;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
307 local result = {};
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
308 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
309 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
310 local k = row.key;
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
311 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
312 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
313 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
314 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
315 result[a] = b;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
316 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
317 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
318 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
319 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
320 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
321 end
4007
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
322 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
323 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
324 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
325
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
326 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
327 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
328 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
329 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
330 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
331 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
332 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
333 -- TODO non-string keys
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
334 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
335 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
336 return commit(true);
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
337 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
338
4007
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
339 local map_store = {};
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
340 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
341 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
342 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
343 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
344 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
345 end
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
346 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
347 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
348 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
349 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
350 end
062b849ca088 mod_storage_sql: Catch Lua errors during SQL transactions, and rollback.
Waqas Hussain <waqas20@gmail.com>
parents: 4004
diff changeset
351
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
352 local list_store = {};
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
353 list_store.__index = list_store;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
354 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
355 user,store = username,self.store;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
356
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
357 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
358 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
359 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
360 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
361
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
362 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
363 --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
364
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
365 return nil, "not-implemented"
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
366 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
367
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
368 local driver = { name = "sql" };
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
369
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
370 function driver:open(store, typ)
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
371 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
372 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
373 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
374 return nil, "unsupported-store";
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
375 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
376
5035
874cab7b4b3e mod_storage_sql: Add method for listing stores
Kim Alvefur <zash@zash.se>
parents: 5034
diff changeset
377 function driver:list_stores(username) -- Not to be confused with the list store type
874cab7b4b3e mod_storage_sql: Add method for listing stores
Kim Alvefur <zash@zash.se>
parents: 5034
diff changeset
378 local sql = (username == true
874cab7b4b3e mod_storage_sql: Add method for listing stores
Kim Alvefur <zash@zash.se>
parents: 5034
diff changeset
379 and "SELECT DISTINCT `store` FROM `prosody` WHERE `host`=? AND `user`!=?"
874cab7b4b3e mod_storage_sql: Add method for listing stores
Kim Alvefur <zash@zash.se>
parents: 5034
diff changeset
380 or "SELECT DISTINCT `store` FROM `prosody` WHERE `host`=? AND `user`=?");
874cab7b4b3e mod_storage_sql: Add method for listing stores
Kim Alvefur <zash@zash.se>
parents: 5034
diff changeset
381 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
382 username = "";
874cab7b4b3e mod_storage_sql: Add method for listing stores
Kim Alvefur <zash@zash.se>
parents: 5034
diff changeset
383 end
874cab7b4b3e mod_storage_sql: Add method for listing stores
Kim Alvefur <zash@zash.se>
parents: 5034
diff changeset
384 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
385 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
386 return rollback(nil, err);
5035
874cab7b4b3e mod_storage_sql: Add method for listing stores
Kim Alvefur <zash@zash.se>
parents: 5034
diff changeset
387 end
874cab7b4b3e mod_storage_sql: Add method for listing stores
Kim Alvefur <zash@zash.se>
parents: 5034
diff changeset
388 local stores = {};
874cab7b4b3e mod_storage_sql: Add method for listing stores
Kim Alvefur <zash@zash.se>
parents: 5034
diff changeset
389 for row in stmt:rows() do
874cab7b4b3e mod_storage_sql: Add method for listing stores
Kim Alvefur <zash@zash.se>
parents: 5034
diff changeset
390 stores[#stores+1] = row[1];
874cab7b4b3e mod_storage_sql: Add method for listing stores
Kim Alvefur <zash@zash.se>
parents: 5034
diff changeset
391 end
5055
d466d2088a61 mod_storage_sql: Complete transactions in list_stores and purge
Kim Alvefur <zash@zash.se>
parents: 5054
diff changeset
392 return commit(stores);
5035
874cab7b4b3e mod_storage_sql: Add method for listing stores
Kim Alvefur <zash@zash.se>
parents: 5034
diff changeset
393 end
874cab7b4b3e mod_storage_sql: Add method for listing stores
Kim Alvefur <zash@zash.se>
parents: 5034
diff changeset
394
5040
685403a6fee1 mod_storage_sql: Add method for removing all data belonging to a user
Kim Alvefur <zash@zash.se>
parents: 5035
diff changeset
395 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
396 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
397 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
398 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
399 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
400 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
401 end
685403a6fee1 mod_storage_sql: Add method for removing all data belonging to a user
Kim Alvefur <zash@zash.se>
parents: 5035
diff changeset
402
3729
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff changeset
403 module:add_item("data-driver", driver);