Software `/` code `/` prosody

Annotate

plugins/mod_storage_sql.lua @ 3976:16170a66e140

mod_storage_sql: Dynamically replace backquotes with double quotes when connecting to PostgreSQL...

author	Waqas Hussain <waqas20@gmail.com>
date	Mon, 27 Dec 2010 06:10:35 +0500
parent	3975:aa5e93e61760
child	3977:6724853adb80

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
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	6 \| host \| user \| store \| key \| subkey \| type \| value \|
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
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	12 \| host \| user \| "roster" \| "contactjid" \| item-subkey \| type \| value \|
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	13 \| host \| user \| "roster" \| NULL \| NULL \| "json" \| roster[false] data \|
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	14 Account - Prosody
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	15 \| host \| user \| "accounts" \| "username" \| NULL \| type \| value \|
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;
3772 e1f6fe098404 mod_storage_sql: Fix a couple of bugs in "JSON" decoding Matthew Wild <mwild1@gmail.com> parents: 3744 diff changeset	28 local json = { stringify = function(s) return require"util.serialization".serialize(s) end, parse = require"util.serialization".deserialize };
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	29
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	30 local connection = ...;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	31 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	32 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	33
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	34 do -- process options to get a db connection
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	35 local DBI = require "DBI";
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	36
3976 16170a66e140 mod_storage_sql: Dynamically replace backquotes with double quotes when connecting to PostgreSQL... Waqas Hussain <waqas20@gmail.com> parents: 3975 diff changeset	37 params = params or { driver = "SQLite3", database = "prosody.sqlite" };
16170a66e140 mod_storage_sql: Dynamically replace backquotes with double quotes when connecting to PostgreSQL... Waqas Hussain <waqas20@gmail.com> parents: 3975 diff changeset	38 assert(params.driver and params.database, "invalid params");
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	39
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	40 prosody.unlock_globals();
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	41 local dbh, err = DBI.Connect(
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	42 params.driver, params.database,
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	43 params.username, params.password,
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	44 params.host, params.port
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	45 );
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	46 prosody.lock_globals();
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	47 assert(dbh, err);
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	48
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	49 dbh:autocommit(false); -- don't commit automatically
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	50 connection = dbh;
3732 bf449ecf2144 mod_storage_sql: Auto-initialize SQLite3 database. Waqas Hussain <waqas20@gmail.com> parents: 3731 diff changeset	51
bf449ecf2144 mod_storage_sql: Auto-initialize SQLite3 database. Waqas Hussain <waqas20@gmail.com> parents: 3731 diff changeset	52 if params.driver == "SQLite3" then -- auto initialize
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	53 local stmt = assert(connection:prepare("SELECT COUNT(*) FROM `sqlite_master` WHERE `type`='table' AND `name`='Prosody';"));
3732 bf449ecf2144 mod_storage_sql: Auto-initialize SQLite3 database. Waqas Hussain <waqas20@gmail.com> parents: 3731 diff changeset	54 local ok = assert(stmt:execute());
bf449ecf2144 mod_storage_sql: Auto-initialize SQLite3 database. Waqas Hussain <waqas20@gmail.com> parents: 3731 diff changeset	55 local count = stmt:fetch()[1];
bf449ecf2144 mod_storage_sql: Auto-initialize SQLite3 database. Waqas Hussain <waqas20@gmail.com> parents: 3731 diff changeset	56 if count == 0 then
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	57 local stmt = assert(connection:prepare("CREATE TABLE `Prosody` (`host` TEXT, `user` TEXT, `store` TEXT, `key` TEXT, `subkey` TEXT, `type` TEXT, `value` TEXT);"));
3732 bf449ecf2144 mod_storage_sql: Auto-initialize SQLite3 database. Waqas Hussain <waqas20@gmail.com> parents: 3731 diff changeset	58 assert(stmt:execute());
bf449ecf2144 mod_storage_sql: Auto-initialize SQLite3 database. Waqas Hussain <waqas20@gmail.com> parents: 3731 diff changeset	59 module:log("debug", "Initialized new SQLite3 database");
bf449ecf2144 mod_storage_sql: Auto-initialize SQLite3 database. Waqas Hussain <waqas20@gmail.com> parents: 3731 diff changeset	60 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	61 assert(connection:commit());
3732 bf449ecf2144 mod_storage_sql: Auto-initialize SQLite3 database. Waqas Hussain <waqas20@gmail.com> parents: 3731 diff changeset	62 --print("===", json.stringify())
bf449ecf2144 mod_storage_sql: Auto-initialize SQLite3 database. Waqas Hussain <waqas20@gmail.com> parents: 3731 diff changeset	63 end
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	64 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	65
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	66 local function serialize(value)
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	67 local t = type(value);
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	68 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	69 return t, tostring(value);
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	70 elseif t == "table" then
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	71 local value,err = json.stringify(value);
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	72 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	73 return nil, err;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	74 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	75 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	76 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	77 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	78 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	79 elseif t == "boolean" then
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	80 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	81 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	82 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	83 elseif t == "json" then
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	84 return json.parse(value);
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	85 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	86 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	87
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	88 local function getsql(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	89 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	90 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	91 end
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	92 -- do prepared statement stuff
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	93 local stmt, err = connection:prepare(sql);
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	94 if not stmt then return nil, err; end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	95 -- run query
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	96 local ok, err = stmt:execute(host or "", user or "", store or "", ...);
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	97 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	98
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	99 return stmt;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	100 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	101 local function setsql(sql, ...)
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	102 local stmt, err = getsql(sql, ...);
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	103 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	104 return stmt:affected();
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	105 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	106 local function transact(...)
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	107 -- ...
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	108 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	109 local function rollback(...)
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	110 connection:rollback(); -- FIXME check for rollback error?
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	111 return ...;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	112 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	113 local function commit(...)
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	114 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	115 return ...;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	116 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	117
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	118 local keyval_store = {};
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	119 keyval_store.__index = keyval_store;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	120 function keyval_store:get(username)
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	121 user,store = username,self.store;
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	122 local stmt, err = getsql("SELECT * FROM `Prosody` WHERE `host`=? AND `user`=? AND `store`=? AND `subkey`=''");
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	123 if not stmt then return nil, err; end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	124
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	125 local haveany;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	126 local result = {};
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	127 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	128 haveany = true;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	129 local k = row.key;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	130 local v = deserialize(row.type, row.value);
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	131 if v then
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	132 if k then result[k] = v; elseif type(v) == "table" then
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	133 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	134 result[a] = b;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	135 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	136 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	137 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	138 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	139 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	140 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	141 function keyval_store:set(username, data)
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	142 user,store = username,self.store;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	143 -- start transaction
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	144 local affected, err = setsql("DELETE FROM `Prosody` WHERE `host`=? AND `user`=? AND `store`=? AND `subkey`=''");
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	145
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	146 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	147 local extradata = {};
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	148 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	149 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	150 local t, value = serialize(value);
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	151 if not t then return rollback(t, value); end
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	152 local ok, err = setsql("INSERT INTO `Prosody` (`host`,`user`,`store`,`key`,`type`,`value`,`subkey`) VALUES (?,?,?,?,?,?,'')", key or "", t, value);
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	153 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	154 else
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	155 extradata[key] = value;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	156 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	157 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	158 if next(extradata) ~= nil then
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	159 local t, extradata = serialize(extradata);
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	160 if not t then return rollback(t, extradata); end
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	161 local ok, err = setsql("INSERT INTO `Prosody` (`host`,`user`,`store`,`key`,`type`,`value`,`subkey`) VALUES (?,?,?,?,?,?,'')", "", t, extradata);
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	162 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	163 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	164 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	165 return commit(true);
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	166 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	167
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	168 local map_store = {};
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	169 map_store.__index = map_store;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	170 function map_store:get(username, key)
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	171 user,store = username,self.store;
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	172 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	173 if not stmt then return nil, err; end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	174
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	175 local haveany;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	176 local result = {};
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	177 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	178 haveany = true;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	179 local k = row.subkey;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	180 local v = deserialize(row.type, row.value);
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	181 if v then
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	182 if k then result[k] = v; elseif type(v) == "table" then
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	183 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	184 result[a] = b;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	185 end
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 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	188 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	189 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	190 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	191 function map_store:set(username, key, data)
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	192 user,store = username,self.store;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	193 -- start transaction
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	194 local affected, err = setsql("DELETE 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	195
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	196 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	197 local extradata = {};
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	198 for subkey, 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	199 if type(subkey) == "string" and key ~= "" then
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	200 local t, value = serialize(value);
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	201 if not t then return rollback(t, value); end
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	202 local ok, err = setsql("INSERT INTO `Prosody` (`host`,`user`,`store`,`key`,`subkey`,`type`,`value`) VALUES (?,?,?,?,?,?,?)", key or "", subkey or "", t, value);
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	203 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	204 else
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	205 extradata[subkey] = value;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	206 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	207 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	208 if next(extradata) ~= nil then
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	209 local t, extradata = serialize(extradata);
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	210 if not t then return rollback(t, extradata); end
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	211 local ok, err = setsql("INSERT INTO `Prosody` (`host`,`user`,`store`,`key`,`subkey`,`type`,`value`) VALUES (?,?,?,?,?,?,?)", key or "", "", t, extradata);
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	212 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	213 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	214 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	215 return commit(true);
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	216 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	217
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	218 local list_store = {};
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	219 list_store.__index = list_store;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	220 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	221 user,store = username,self.store;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	222
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	223 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	224 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	225 local stmt, err;
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	226 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	227
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	228 query = query.." ORDER BY time";
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	229 --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	230
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	231 return nil, "not-implemented"
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	232 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	233
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	234 local driver = { name = "sql" };
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	235
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	236 function driver:open(store, typ)
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	237 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	238 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	239 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	240 return nil, "unsupported-store";
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	241 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	242
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	243 module:add_item("data-driver", driver);

prosody

16170a66e140

plugins/mod_storage_sql.lua

Software / code / prosody

Annotate

plugins/mod_storage_sql.lua @ 3976:16170a66e140

Software `/` code `/` prosody