Fri, 17 Dec 2010 17:28:05 +0100
util.stanza: Iterate on childtags instead of all childs.
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); |