Software `/` code `/` prosody

Annotate

plugins/mod_storage_sql1.lua @ 8528:67311cda0625

net.server_select: Better detection of errors for outgoing connections On connection failure, a socket is marked readable and writable. So to detect initial connection failures (connection refused, etc.) we now watch for sockets becoming readable during initial connection, and also read from readable sockets before writing to writable sockets. This should fix 'onconnect' being called for outgoing connections that actually failed.

author	Matthew Wild <mwild1@gmail.com>
date	Fri, 23 Feb 2018 15:30:00 +0000
parent	7359:a5a080c12c96

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
6165 6a184b16b717 core.certmanager, core.moduleapi, mod_storage_sql, mod_storage_sql2: Import from util.paths Kim Alvefur <zash@zash.se> parents: 5776 diff changeset	52 local resolve_relative_path = require "util.paths".resolve_relative_path;
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	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
5776 bd0ff8ae98a8 Remove all trailing whitespace Florian Zeitz <florob@babelmonkeys.de> parents: 5209 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 "
7359 a5a080c12c96 Update every link to the documentation to use HTTPS Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 6951 diff changeset	133 .."https://prosody.im/doc/mysql for help",
4318 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), "
7359 a5a080c12c96 Update every link to the documentation to use HTTPS Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 6951 diff changeset	142 .."see https://prosody.im/doc/modules/mod_storage_sql#table_management for help.",
4318 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);
7359 a5a080c12c96 Update every link to the documentation to use HTTPS Emmanuel Gil Peyrot <linkmauve@linkmauve.fr> parents: 6951 diff changeset	154 module:log("error", "More information on installing LuaDBI can be found at https://prosody.im/doc/depends#luadbi");
4218 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" };
5776 bd0ff8ae98a8 Remove all trailing whitespace Florian Zeitz <florob@babelmonkeys.de> parents: 5209 diff changeset	162
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	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
5776 bd0ff8ae98a8 Remove all trailing whitespace Florian Zeitz <florob@babelmonkeys.de> parents: 5209 diff changeset	166
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	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];
5776 bd0ff8ae98a8 Remove all trailing whitespace Florian Zeitz <florob@babelmonkeys.de> parents: 5209 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());
5776 bd0ff8ae98a8 Remove all trailing whitespace Florian Zeitz <florob@babelmonkeys.de> parents: 5209 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
5776 bd0ff8ae98a8 Remove all trailing whitespace Florian Zeitz <florob@babelmonkeys.de> parents: 5209 diff changeset	212
3729 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
5776 bd0ff8ae98a8 Remove all trailing whitespace Florian Zeitz <florob@babelmonkeys.de> parents: 5209 diff changeset	239
3729 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
5776 bd0ff8ae98a8 Remove all trailing whitespace Florian Zeitz <florob@babelmonkeys.de> parents: 5209 diff changeset	259
3729 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
5776 bd0ff8ae98a8 Remove all trailing whitespace Florian Zeitz <florob@babelmonkeys.de> parents: 5209 diff changeset	317
3729 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
5776 bd0ff8ae98a8 Remove all trailing whitespace Florian Zeitz <florob@babelmonkeys.de> parents: 5209 diff changeset	337
3729 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;
5776 bd0ff8ae98a8 Remove all trailing whitespace Florian Zeitz <florob@babelmonkeys.de> parents: 5209 diff changeset	368
3729 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`=?";
5776 bd0ff8ae98a8 Remove all trailing whitespace Florian Zeitz <florob@babelmonkeys.de> parents: 5209 diff changeset	373
3729 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 "");
5776 bd0ff8ae98a8 Remove all trailing whitespace Florian Zeitz <florob@babelmonkeys.de> parents: 5209 diff changeset	376
3729 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)
6283 7cf6d3a2c855 mod_storage_{none,internal,sql}: Return error for unsupported (everything but keyval) store types Kim Alvefur <zash@zash.se> parents: 6165 diff changeset	383 if typ and typ ~= "keyval" then
7cf6d3a2c855 mod_storage_{none,internal,sql}: Return error for unsupported (everything but keyval) store types Kim Alvefur <zash@zash.se> parents: 6165 diff changeset	384 return nil, "unsupported-store";
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	385 end
6283 7cf6d3a2c855 mod_storage_{none,internal,sql}: Return error for unsupported (everything but keyval) store types Kim Alvefur <zash@zash.se> parents: 6165 diff changeset	386 return setmetatable({ store = store }, keyval_store);
3729 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);

prosody

67311cda0625

plugins/mod_storage_sql1.lua

Software / code / prosody

Annotate

plugins/mod_storage_sql1.lua @ 8528:67311cda0625

Software `/` code `/` prosody