Software /
code /
prosody
Annotate
plugins/mod_storage_sql.lua @ 3758:41f174b61b6a
modulemanager, mod_console: Rename mod_console -> mod_admin_telnet - add compatibility code to modulemanager for existing configs
author | Matthew Wild <mwild1@gmail.com> |
---|---|
date | Fri, 17 Dec 2010 12:44:24 +0000 |
parent | 3744:ed76b64da9d1 |
child | 3772:e1f6fe098404 |
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; |
3730
5aa586d1366e
mod_storage_sql: Fixed a typo.
Waqas Hussain <waqas20@gmail.com>
parents:
3729
diff
changeset
|
28 local json = { stringify = function(s) return require"util.serialization".serialize(s) end, parse = require"util.serialization".deserialze }; |
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 |
bf449ecf2144
mod_storage_sql: Auto-initialize SQLite3 database.
Waqas Hussain <waqas20@gmail.com>
parents:
3731
diff
changeset
|
52 local stmt = assert(connection:prepare("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='Prosody';")); |
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 |
bf449ecf2144
mod_storage_sql: Auto-initialize SQLite3 database.
Waqas Hussain <waqas20@gmail.com>
parents:
3731
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);")); |
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); |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
82 elseif value == "json" then |
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 |
b351fa47a743
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, ...); |
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; |
3744
ed76b64da9d1
mod_storage_sql: Use 'IS' for comparison instead of '=', to avoid SQL's NULL insanity.
Waqas Hussain <waqas20@gmail.com>
parents:
3743
diff
changeset
|
118 local stmt, err = getsql("SELECT * FROM Prosody WHERE host IS ? AND user IS ? AND store IS ? AND subkey IS NULL"); |
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 |
3744
ed76b64da9d1
mod_storage_sql: Use 'IS' for comparison instead of '=', to avoid SQL's NULL insanity.
Waqas Hussain <waqas20@gmail.com>
parents:
3743
diff
changeset
|
140 local affected, err = setsql("DELETE FROM Prosody WHERE host IS ? AND user IS ? AND store IS ? AND subkey IS NULL"); |
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 |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
145 if type(key) == "string" then |
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 |
b351fa47a743
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); |
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 |
b351fa47a743
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); |
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; |
3744
ed76b64da9d1
mod_storage_sql: Use 'IS' for comparison instead of '=', to avoid SQL's NULL insanity.
Waqas Hussain <waqas20@gmail.com>
parents:
3743
diff
changeset
|
168 local stmt, err = getsql("SELECT * FROM Prosody WHERE host IS ? AND user IS ? AND store IS ? AND key IS ?", key); |
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 |
3744
ed76b64da9d1
mod_storage_sql: Use 'IS' for comparison instead of '=', to avoid SQL's NULL insanity.
Waqas Hussain <waqas20@gmail.com>
parents:
3743
diff
changeset
|
190 local affected, err = setsql("DELETE FROM Prosody WHERE host IS ? AND user IS ? AND store IS ? AND key IS ?", key); |
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 |
b351fa47a743
mod_storage_sql: Initial commit of new SQL data driver.
Waqas Hussain <waqas20@gmail.com>
parents:
diff
changeset
|
195 if type(subkey) == "string" then |
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 |
b351fa47a743
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); |
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 |
b351fa47a743
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); |
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; |
3744
ed76b64da9d1
mod_storage_sql: Use 'IS' for comparison instead of '=', to avoid SQL's NULL insanity.
Waqas Hussain <waqas20@gmail.com>
parents:
3743
diff
changeset
|
222 local query = "SELECT * FROM ProsodyArchive WHERE host IS ? AND user IS ? AND store IS ?"; |
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"; |
3744
ed76b64da9d1
mod_storage_sql: Use 'IS' for comparison instead of '=', to avoid SQL's NULL insanity.
Waqas Hussain <waqas20@gmail.com>
parents:
3743
diff
changeset
|
225 --local stmt, err = getsql("SELECT * FROM Prosody WHERE host IS ? AND user IS ? AND store IS ? AND key IS ?", key); |
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); |