Software `/` code `/` prosody

Annotate

plugins/mod_storage_sql.lua @ 3864:345f1d5b7022

mod_storage_sql: Use 'IS' for comparison instead of '=', to avoid SQL's NULL insanity.

author	Waqas Hussain <waqas20@gmail.com>
date	Mon, 13 Dec 2010 19:28:57 +0500
parent	3863:2a9475dce7ff
child	3772:e1f6fe098404

rev	line source
3851 e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	1
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	2 --[[
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	3
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	4 DB Tables:
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	5 Prosody - key-value, map
e979b5fe859d 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 \|
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	7 ProsodyArchive - list
e979b5fe859d 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 \|
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	9
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	10 Mapping:
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	11 Roster - Prosody
e979b5fe859d 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 \|
e979b5fe859d 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 \|
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	14 Account - Prosody
e979b5fe859d 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 \|
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	16
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	17 Offline - ProsodyArchive
e979b5fe859d 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 \|
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	19
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	20 ]]
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	21
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	22 local type = type;
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	23 local tostring = tostring;
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	24 local tonumber = tonumber;
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	25 local pairs = pairs;
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	26 local next = next;
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	27 local setmetatable = setmetatable;
3852 ab8d1c90527c mod_storage_sql: Fixed a typo. Waqas Hussain <waqas20@gmail.com> parents: 3851 diff changeset	28 local json = { stringify = function(s) return require"util.serialization".serialize(s) end, parse = require"util.serialization".deserialze };
3851 e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	29
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	30 local connection = ...;
e979b5fe859d 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;
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	32
e979b5fe859d 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
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	34 local DBI = require "DBI";
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	35
3853 1a58bbe19c60 mod_storage_sql: Default value for option sql = { driver = "SQLite3", database = "prosody.sqlite" }. Waqas Hussain <waqas20@gmail.com> parents: 3852 diff changeset	36 local params = module:get_option("sql") or { driver = "SQLite3", database = "prosody.sqlite" };
3851 e979b5fe859d 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");
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	38
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	39 prosody.unlock_globals();
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	40 local dbh, err = DBI.Connect(
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	41 params.driver, params.database,
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	42 params.username, params.password,
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	43 params.host, params.port
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	44 );
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	45 prosody.lock_globals();
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	46 assert(dbh, err);
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	47
e979b5fe859d 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
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	49 connection = dbh;
3854 5a1551d604b1 mod_storage_sql: Auto-initialize SQLite3 database. Waqas Hussain <waqas20@gmail.com> parents: 3853 diff changeset	50
5a1551d604b1 mod_storage_sql: Auto-initialize SQLite3 database. Waqas Hussain <waqas20@gmail.com> parents: 3853 diff changeset	51 if params.driver == "SQLite3" then -- auto initialize
5a1551d604b1 mod_storage_sql: Auto-initialize SQLite3 database. Waqas Hussain <waqas20@gmail.com> parents: 3853 diff changeset	52 local stmt = assert(connection:prepare("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='Prosody';"));
5a1551d604b1 mod_storage_sql: Auto-initialize SQLite3 database. Waqas Hussain <waqas20@gmail.com> parents: 3853 diff changeset	53 local ok = assert(stmt:execute());
5a1551d604b1 mod_storage_sql: Auto-initialize SQLite3 database. Waqas Hussain <waqas20@gmail.com> parents: 3853 diff changeset	54 local count = stmt:fetch()[1];
5a1551d604b1 mod_storage_sql: Auto-initialize SQLite3 database. Waqas Hussain <waqas20@gmail.com> parents: 3853 diff changeset	55 if count == 0 then
5a1551d604b1 mod_storage_sql: Auto-initialize SQLite3 database. Waqas Hussain <waqas20@gmail.com> parents: 3853 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);"));
5a1551d604b1 mod_storage_sql: Auto-initialize SQLite3 database. Waqas Hussain <waqas20@gmail.com> parents: 3853 diff changeset	57 assert(stmt:execute());
5a1551d604b1 mod_storage_sql: Auto-initialize SQLite3 database. Waqas Hussain <waqas20@gmail.com> parents: 3853 diff changeset	58 assert(connection:commit());
5a1551d604b1 mod_storage_sql: Auto-initialize SQLite3 database. Waqas Hussain <waqas20@gmail.com> parents: 3853 diff changeset	59 module:log("debug", "Initialized new SQLite3 database");
5a1551d604b1 mod_storage_sql: Auto-initialize SQLite3 database. Waqas Hussain <waqas20@gmail.com> parents: 3853 diff changeset	60 end
5a1551d604b1 mod_storage_sql: Auto-initialize SQLite3 database. Waqas Hussain <waqas20@gmail.com> parents: 3853 diff changeset	61 --print("===", json.stringify())
5a1551d604b1 mod_storage_sql: Auto-initialize SQLite3 database. Waqas Hussain <waqas20@gmail.com> parents: 3853 diff changeset	62 end
3851 e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	63 end
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	64
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	65 local function serialize(value)
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	66 local t = type(value);
e979b5fe859d 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
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	68 return t, tostring(value);
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	69 elseif t == "table" then
e979b5fe859d 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);
e979b5fe859d 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
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	72 return nil, err;
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	73 end
e979b5fe859d 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;
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	75 end
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	76 local function deserialize(t, value)
3863 2a9475dce7ff mod_storage_sql: Fixed the deserialization of string-typed values. Waqas Hussain <waqas20@gmail.com> parents: 3854 diff changeset	77 if t == "string" then return value;
3851 e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	78 elseif t == "boolean" then
e979b5fe859d 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;
e979b5fe859d 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
e979b5fe859d 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);
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	82 elseif value == "json" then
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	83 return json.parse(value);
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	84 end
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	85 end
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	86
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	87 local function getsql(sql, ...)
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	88 -- do prepared statement stuff
e979b5fe859d 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);
e979b5fe859d 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
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	91 -- run query
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	92 local ok, err = stmt:execute(host, user, store, ...);
e979b5fe859d 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
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	94
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	95 return stmt;
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	96 end
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	97 local function setsql(sql, ...)
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	98 local stmt, err = getsql(sql, ...);
e979b5fe859d 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
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	100 return stmt:affected();
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	101 end
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	102 local function transact(...)
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	103 -- ...
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	104 end
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	105 local function rollback(...)
e979b5fe859d 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?
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	107 return ...;
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	108 end
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	109 local function commit(...)
e979b5fe859d 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
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	111 return ...;
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	112 end
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	113
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	114 local keyval_store = {};
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	115 keyval_store.__index = keyval_store;
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	116 function keyval_store:get(username)
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	117 user,store = username,self.store;
3864 345f1d5b7022 mod_storage_sql: Use 'IS' for comparison instead of '=', to avoid SQL's NULL insanity. Waqas Hussain <waqas20@gmail.com> parents: 3863 diff changeset	118 local stmt, err = getsql("SELECT * FROM Prosody WHERE host IS ? AND user IS ? AND store IS ? AND subkey IS NULL");
3851 e979b5fe859d 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
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	120
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	121 local haveany;
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	122 local result = {};
e979b5fe859d 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
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	124 haveany = true;
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	125 local k = row.key;
e979b5fe859d 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);
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	127 if v then
e979b5fe859d 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
e979b5fe859d 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
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	130 result[a] = b;
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	131 end
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	132 end
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	133 end
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	134 end
e979b5fe859d 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;
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	136 end
e979b5fe859d 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)
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	138 user,store = username,self.store;
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	139 -- start transaction
3864 345f1d5b7022 mod_storage_sql: Use 'IS' for comparison instead of '=', to avoid SQL's NULL insanity. Waqas Hussain <waqas20@gmail.com> parents: 3863 diff changeset	140 local affected, err = setsql("DELETE FROM Prosody WHERE host IS ? AND user IS ? AND store IS ? AND subkey IS NULL");
3851 e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	141
e979b5fe859d 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
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	143 local extradata = {};
e979b5fe859d 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
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	145 if type(key) == "string" then
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	146 local t, value = serialize(value);
e979b5fe859d 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
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	148 local ok, err = setsql("INSERT INTO Prosody (host,user,store,key,type,value) VALUES (?,?,?,?,?,?)", key, t, value);
e979b5fe859d 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
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	150 else
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	151 extradata[key] = value;
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	152 end
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	153 end
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	154 if next(extradata) ~= nil then
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	155 local t, extradata = serialize(extradata);
e979b5fe859d 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
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	157 local ok, err = setsql("INSERT INTO Prosody (host,user,store,key,type,value) VALUES (?,?,?,?,?,?)", nil, t, extradata);
e979b5fe859d 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
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	159 end
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	160 end
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	161 return commit(true);
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	162 end
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	163
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	164 local map_store = {};
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	165 map_store.__index = map_store;
e979b5fe859d 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)
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	167 user,store = username,self.store;
3864 345f1d5b7022 mod_storage_sql: Use 'IS' for comparison instead of '=', to avoid SQL's NULL insanity. Waqas Hussain <waqas20@gmail.com> parents: 3863 diff changeset	168 local stmt, err = getsql("SELECT * FROM Prosody WHERE host IS ? AND user IS ? AND store IS ? AND key IS ?", key);
3851 e979b5fe859d 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
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	170
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	171 local haveany;
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	172 local result = {};
e979b5fe859d 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
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	174 haveany = true;
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	175 local k = row.subkey;
e979b5fe859d 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);
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	177 if v then
e979b5fe859d 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
e979b5fe859d 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
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	180 result[a] = b;
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	181 end
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	182 end
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	183 end
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	184 end
e979b5fe859d 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;
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	186 end
e979b5fe859d 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)
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	188 user,store = username,self.store;
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	189 -- start transaction
3864 345f1d5b7022 mod_storage_sql: Use 'IS' for comparison instead of '=', to avoid SQL's NULL insanity. Waqas Hussain <waqas20@gmail.com> parents: 3863 diff changeset	190 local affected, err = setsql("DELETE FROM Prosody WHERE host IS ? AND user IS ? AND store IS ? AND key IS ?", key);
3851 e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	191
e979b5fe859d 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
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	193 local extradata = {};
e979b5fe859d 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
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	195 if type(subkey) == "string" then
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	196 local t, value = serialize(value);
e979b5fe859d 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
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	198 local ok, err = setsql("INSERT INTO Prosody (host,user,store,key,subkey,type,value) VALUES (?,?,?,?,?,?)", key, subkey, t, value);
e979b5fe859d 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
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	200 else
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	201 extradata[subkey] = value;
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	202 end
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	203 end
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	204 if next(extradata) ~= nil then
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	205 local t, extradata = serialize(extradata);
e979b5fe859d 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
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	207 local ok, err = setsql("INSERT INTO Prosody (host,user,store,key,subkey,type,value) VALUES (?,?,?,?,?,?)", key, nil, t, extradata);
e979b5fe859d 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
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	209 end
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	210 end
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	211 return commit(true);
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	212 end
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	213
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	214 local list_store = {};
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	215 list_store.__index = list_store;
e979b5fe859d 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)
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	217 user,store = username,self.store;
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	218
e979b5fe859d 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"};
e979b5fe859d 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 };
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	221 local stmt, err;
3864 345f1d5b7022 mod_storage_sql: Use 'IS' for comparison instead of '=', to avoid SQL's NULL insanity. Waqas Hussain <waqas20@gmail.com> parents: 3863 diff changeset	222 local query = "SELECT * FROM ProsodyArchive WHERE host IS ? AND user IS ? AND store IS ?";
3851 e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	223
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	224 query = query.." ORDER BY time";
3864 345f1d5b7022 mod_storage_sql: Use 'IS' for comparison instead of '=', to avoid SQL's NULL insanity. Waqas Hussain <waqas20@gmail.com> parents: 3863 diff changeset	225 --local stmt, err = getsql("SELECT * FROM Prosody WHERE host IS ? AND user IS ? AND store IS ? AND key IS ?", key);
3851 e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	226
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	227 return nil, "not-implemented"
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	228 end
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	229
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	230 local driver = { name = "sql" };
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	231
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	232 function driver:open(store, typ)
e979b5fe859d 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
e979b5fe859d 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);
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	235 end
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	236 return nil, "unsupported-store";
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	237 end
e979b5fe859d mod_storage_sql: Initial commit of new SQL data driver. Waqas Hussain <waqas20@gmail.com> parents: diff changeset	238
e979b5fe859d 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

345f1d5b7022

plugins/mod_storage_sql.lua

Software / code / prosody

Annotate

plugins/mod_storage_sql.lua @ 3864:345f1d5b7022

Software `/` code `/` prosody