Software `/` code `/` prosody

Annotate

plugins/mod_storage_sql.lua @ 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...

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

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;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	32
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	33 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	34 local DBI = require "DBI";
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	35
3731 b3a5f9b1d1df mod_storage_sql: Default value for option sql = { driver = "SQLite3", database = "prosody.sqlite" }. Waqas Hussain <waqas20@gmail.com> parents: 3730 diff changeset	36 local params = module:get_option("sql") or { driver = "SQLite3", database = "prosody.sqlite" };
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	37 assert(params and params.driver and params.database, "invalid params");
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	38
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	39 prosody.unlock_globals();
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	40 local dbh, err = DBI.Connect(
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	41 params.driver, params.database,
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	42 params.username, params.password,
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	43 params.host, params.port
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	44 );
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	45 prosody.lock_globals();
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	46 assert(dbh, err);
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	47
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	48 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	49 connection = dbh;
3732 bf449ecf2144 mod_storage_sql: Auto-initialize SQLite3 database. Waqas Hussain <waqas20@gmail.com> parents: 3731 diff changeset	50
bf449ecf2144 mod_storage_sql: Auto-initialize SQLite3 database. Waqas Hussain <waqas20@gmail.com> parents: 3731 diff changeset	51 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	52 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	53 local ok = assert(stmt:execute());
bf449ecf2144 mod_storage_sql: Auto-initialize SQLite3 database. Waqas Hussain <waqas20@gmail.com> parents: 3731 diff changeset	54 local count = stmt:fetch()[1];
bf449ecf2144 mod_storage_sql: Auto-initialize SQLite3 database. Waqas Hussain <waqas20@gmail.com> parents: 3731 diff changeset	55 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	56 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	57 assert(stmt:execute());
bf449ecf2144 mod_storage_sql: Auto-initialize SQLite3 database. Waqas Hussain <waqas20@gmail.com> parents: 3731 diff changeset	58 assert(connection:commit());
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
bf449ecf2144 mod_storage_sql: Auto-initialize SQLite3 database. Waqas Hussain <waqas20@gmail.com> parents: 3731 diff changeset	61 --print("===", json.stringify())
bf449ecf2144 mod_storage_sql: Auto-initialize SQLite3 database. Waqas Hussain <waqas20@gmail.com> parents: 3731 diff changeset	62 end
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	63 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	64
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	65 local function serialize(value)
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	66 local t = type(value);
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	67 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	68 return t, tostring(value);
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	69 elseif t == "table" then
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	70 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	71 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	72 return nil, err;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	73 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	74 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	75 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	76 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	77 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	78 elseif t == "boolean" then
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	79 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	80 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	81 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	82 elseif t == "json" then
3729 b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	83 return json.parse(value);
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	84 end
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
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	87 local function getsql(sql, ...)
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	88 -- do prepared statement stuff
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	89 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	90 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	91 -- 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	92 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	93 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	94
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	95 return stmt;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	96 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	97 local function setsql(sql, ...)
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	98 local stmt, err = getsql(sql, ...);
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	99 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	100 return stmt:affected();
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	101 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	102 local function transact(...)
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	103 -- ...
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	104 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	105 local function rollback(...)
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	106 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	107 return ...;
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 commit(...)
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	110 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	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
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	114 local keyval_store = {};
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	115 keyval_store.__index = keyval_store;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	116 function keyval_store:get(username)
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	117 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	118 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	119 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	120
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	121 local haveany;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	122 local result = {};
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	123 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	124 haveany = true;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	125 local k = row.key;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	126 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	127 if v then
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	128 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	129 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	130 result[a] = b;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	131 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	132 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	133 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	134 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	135 return haveany and result or nil;
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 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	138 user,store = username,self.store;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	139 -- 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	140 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	141
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	142 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	143 local extradata = {};
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	144 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	145 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	146 local t, value = serialize(value);
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	147 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	148 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	149 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	150 else
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	151 extradata[key] = value;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	152 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	153 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	154 if next(extradata) ~= nil then
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	155 local t, extradata = serialize(extradata);
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	156 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	157 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	158 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	159 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	160 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	161 return commit(true);
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	162 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	163
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	164 local map_store = {};
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	165 map_store.__index = map_store;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	166 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	167 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	168 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	169 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	170
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	171 local haveany;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	172 local result = {};
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	173 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	174 haveany = true;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	175 local k = row.subkey;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	176 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	177 if v then
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	178 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	179 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	180 result[a] = b;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	181 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	182 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	183 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	184 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	185 return haveany and result or nil;
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 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	188 user,store = username,self.store;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	189 -- 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	190 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	191
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	192 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	193 local extradata = {};
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	194 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	195 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	196 local t, value = serialize(value);
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	197 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	198 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	199 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	200 else
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	201 extradata[subkey] = value;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	202 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	203 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	204 if next(extradata) ~= nil then
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	205 local t, extradata = serialize(extradata);
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	206 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	207 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	208 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	209 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	210 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	211 return commit(true);
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	212 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	213
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	214 local list_store = {};
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	215 list_store.__index = list_store;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	216 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	217 user,store = username,self.store;
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	218
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	219 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	220 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	221 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	222 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	223
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	224 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	225 --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	226
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	227 return nil, "not-implemented"
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	228 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	229
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	230 local driver = { name = "sql" };
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	231
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	232 function driver:open(store, typ)
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	233 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	234 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	235 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	236 return nil, "unsupported-store";
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	237 end
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	238
b351fa47a743 mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	239 module:add_item("data-driver", driver);

prosody

af40a7ce4f77

plugins/mod_storage_sql.lua

Software / code / prosody

Annotate

plugins/mod_storage_sql.lua @ 3974:af40a7ce4f77

Software `/` code `/` prosody