prosody
52ccbf71d062
tools/migration/migrator/prosody_sql.lua

Find changesets by keywords by author, files, the commit message, revision number or hash, or revset expression.

Software / code / prosody

Annotate

tools/migration/migrator/prosody_sql.lua @ 4310:52ccbf71d062

migrator/prosody_sql.lua: Fix for compatibility with non-MySQL databases
author Matthew Wild <mwild1@gmail.com>
date Sun, 05 Jun 2011 11:53:41 +0100
parent 4294:d2406f0ce8a5
child 5776:bd0ff8ae98a8
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
4197
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
1
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
2 local assert = assert;
4234
ce92aafc9c03 tools/migration/migrator/prosody_sql: Throw a friendlier error when LuaDBI is not found
Matthew Wild <mwild1@gmail.com>
parents: 4216
diff changeset
3 local have_DBI, DBI = pcall(require,"DBI");
4197
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
4 local print = print;
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
5 local type = type;
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
6 local next = next;
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
7 local pairs = pairs;
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
8 local t_sort = table.sort;
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
9 local json = require "util.json";
4216
ff80a8471e86 tools/migration/*: Numerous changes and restructuring, and the addition of a Makefile
Matthew Wild <mwild1@gmail.com>
parents: 4197
diff changeset
10 local mtools = require "migrator.mtools";
4197
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
11 local tostring = tostring;
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
12 local tonumber = tonumber;
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
13
4234
ce92aafc9c03 tools/migration/migrator/prosody_sql: Throw a friendlier error when LuaDBI is not found
Matthew Wild <mwild1@gmail.com>
parents: 4216
diff changeset
14 if not have_DBI then
ce92aafc9c03 tools/migration/migrator/prosody_sql: Throw a friendlier error when LuaDBI is not found
Matthew Wild <mwild1@gmail.com>
parents: 4216
diff changeset
15 error("LuaDBI (required for SQL support) was not found, please see http://prosody.im/doc/depends#luadbi", 0);
ce92aafc9c03 tools/migration/migrator/prosody_sql: Throw a friendlier error when LuaDBI is not found
Matthew Wild <mwild1@gmail.com>
parents: 4216
diff changeset
16 end
ce92aafc9c03 tools/migration/migrator/prosody_sql: Throw a friendlier error when LuaDBI is not found
Matthew Wild <mwild1@gmail.com>
parents: 4216
diff changeset
17
4197
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
18 module "prosody_sql"
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
19
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
20 local function create_table(connection, params)
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
21 local create_sql = "CREATE TABLE `prosody` (`host` TEXT, `user` TEXT, `store` TEXT, `key` TEXT, `type` TEXT, `value` TEXT);";
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
22 if params.driver == "PostgreSQL" then
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
23 create_sql = create_sql:gsub("`", "\"");
4294
d2406f0ce8a5 migrator/prosody_sql.lua: Create (and upgrade) MySQL tables to use MEDIUMTEXT for the 'value' column to avoid truncation
Matthew Wild <mwild1@gmail.com>
parents: 4247
diff changeset
24 elseif params.driver == "MySQL" then
d2406f0ce8a5 migrator/prosody_sql.lua: Create (and upgrade) MySQL tables to use MEDIUMTEXT for the 'value' column to avoid truncation
Matthew Wild <mwild1@gmail.com>
parents: 4247
diff changeset
25 create_sql = create_sql:gsub("`value` TEXT", "`value` MEDIUMTEXT");
4197
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
26 end
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
27
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
28 local stmt = connection:prepare(create_sql);
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
29 if stmt then
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
30 local ok = stmt:execute();
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
31 local commit_ok = connection:commit();
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
32 if ok and commit_ok then
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
33 local index_sql = "CREATE INDEX `prosody_index` ON `prosody` (`host`, `user`, `store`, `key`)";
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
34 if params.driver == "PostgreSQL" then
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
35 index_sql = index_sql:gsub("`", "\"");
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
36 elseif params.driver == "MySQL" then
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
37 index_sql = index_sql:gsub("`([,)])", "`(20)%1");
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
38 end
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
39 local stmt, err = connection:prepare(index_sql);
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
40 local ok, commit_ok, commit_err;
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
41 if stmt then
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
42 ok, err = assert(stmt:execute());
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
43 commit_ok, commit_err = assert(connection:commit());
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
44 end
4310
52ccbf71d062 migrator/prosody_sql.lua: Fix for compatibility with non-MySQL databases
Matthew Wild <mwild1@gmail.com>
parents: 4294
diff changeset
45 elseif params.driver == "MySQL" then -- COMPAT: Upgrade tables from 0.8.0
4294
d2406f0ce8a5 migrator/prosody_sql.lua: Create (and upgrade) MySQL tables to use MEDIUMTEXT for the 'value' column to avoid truncation
Matthew Wild <mwild1@gmail.com>
parents: 4247
diff changeset
46 -- Failed to create, but check existing MySQL table here
d2406f0ce8a5 migrator/prosody_sql.lua: Create (and upgrade) MySQL tables to use MEDIUMTEXT for the 'value' column to avoid truncation
Matthew Wild <mwild1@gmail.com>
parents: 4247
diff changeset
47 local stmt = connection:prepare("SHOW COLUMNS FROM prosody WHERE Field='value' and Type='text'");
d2406f0ce8a5 migrator/prosody_sql.lua: Create (and upgrade) MySQL tables to use MEDIUMTEXT for the 'value' column to avoid truncation
Matthew Wild <mwild1@gmail.com>
parents: 4247
diff changeset
48 local ok = stmt:execute();
d2406f0ce8a5 migrator/prosody_sql.lua: Create (and upgrade) MySQL tables to use MEDIUMTEXT for the 'value' column to avoid truncation
Matthew Wild <mwild1@gmail.com>
parents: 4247
diff changeset
49 local commit_ok = connection:commit();
d2406f0ce8a5 migrator/prosody_sql.lua: Create (and upgrade) MySQL tables to use MEDIUMTEXT for the 'value' column to avoid truncation
Matthew Wild <mwild1@gmail.com>
parents: 4247
diff changeset
50 if ok and commit_ok then
d2406f0ce8a5 migrator/prosody_sql.lua: Create (and upgrade) MySQL tables to use MEDIUMTEXT for the 'value' column to avoid truncation
Matthew Wild <mwild1@gmail.com>
parents: 4247
diff changeset
51 if stmt:rowcount() > 0 then
d2406f0ce8a5 migrator/prosody_sql.lua: Create (and upgrade) MySQL tables to use MEDIUMTEXT for the 'value' column to avoid truncation
Matthew Wild <mwild1@gmail.com>
parents: 4247
diff changeset
52 local stmt = connection:prepare("ALTER TABLE prosody MODIFY COLUMN `value` MEDIUMTEXT");
d2406f0ce8a5 migrator/prosody_sql.lua: Create (and upgrade) MySQL tables to use MEDIUMTEXT for the 'value' column to avoid truncation
Matthew Wild <mwild1@gmail.com>
parents: 4247
diff changeset
53 local ok = stmt:execute();
d2406f0ce8a5 migrator/prosody_sql.lua: Create (and upgrade) MySQL tables to use MEDIUMTEXT for the 'value' column to avoid truncation
Matthew Wild <mwild1@gmail.com>
parents: 4247
diff changeset
54 local commit_ok = connection:commit();
d2406f0ce8a5 migrator/prosody_sql.lua: Create (and upgrade) MySQL tables to use MEDIUMTEXT for the 'value' column to avoid truncation
Matthew Wild <mwild1@gmail.com>
parents: 4247
diff changeset
55 if ok and commit_ok then
d2406f0ce8a5 migrator/prosody_sql.lua: Create (and upgrade) MySQL tables to use MEDIUMTEXT for the 'value' column to avoid truncation
Matthew Wild <mwild1@gmail.com>
parents: 4247
diff changeset
56 print("Database table automatically upgraded");
d2406f0ce8a5 migrator/prosody_sql.lua: Create (and upgrade) MySQL tables to use MEDIUMTEXT for the 'value' column to avoid truncation
Matthew Wild <mwild1@gmail.com>
parents: 4247
diff changeset
57 end
d2406f0ce8a5 migrator/prosody_sql.lua: Create (and upgrade) MySQL tables to use MEDIUMTEXT for the 'value' column to avoid truncation
Matthew Wild <mwild1@gmail.com>
parents: 4247
diff changeset
58 end
d2406f0ce8a5 migrator/prosody_sql.lua: Create (and upgrade) MySQL tables to use MEDIUMTEXT for the 'value' column to avoid truncation
Matthew Wild <mwild1@gmail.com>
parents: 4247
diff changeset
59 repeat until not stmt:fetch();
d2406f0ce8a5 migrator/prosody_sql.lua: Create (and upgrade) MySQL tables to use MEDIUMTEXT for the 'value' column to avoid truncation
Matthew Wild <mwild1@gmail.com>
parents: 4247
diff changeset
60 end
4197
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
61 end
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
62 end
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
63 end
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
64
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
65 local function serialize(value)
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
66 local t = type(value);
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
67 if t == "string" or t == "boolean" or t == "number" then
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
68 return t, tostring(value);
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
69 elseif t == "table" then
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
70 local value,err = json.encode(value);
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
71 if value then return "json", value; end
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
72 return nil, err;
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
73 end
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
74 return nil, "Unhandled value type: "..t;
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
75 end
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
76 local function deserialize(t, value)
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
77 if t == "string" then return value;
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
78 elseif t == "boolean" then
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
79 if value == "true" then return true;
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
80 elseif value == "false" then return false; end
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
81 elseif t == "number" then return tonumber(value);
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
82 elseif t == "json" then
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
83 return json.decode(value);
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
84 end
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
85 end
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
86
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
87 local function decode_user(item)
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
88 local userdata = {
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
89 user = item[1][1].user;
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
90 host = item[1][1].host;
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
91 stores = {};
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
92 };
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
93 for i=1,#item do -- loop over stores
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
94 local result = {};
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
95 local store = item[i];
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
96 for i=1,#store do -- loop over store data
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
97 local row = store[i];
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
98 local k = row.key;
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
99 local v = deserialize(row.type, row.value);
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
100 if k and v then
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
101 if k ~= "" then result[k] = v; elseif type(v) == "table" then
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
102 for a,b in pairs(v) do
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
103 result[a] = b;
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
104 end
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
105 end
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
106 end
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
107 userdata.stores[store[1].store] = result;
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
108 end
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
109 end
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
110 return userdata;
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
111 end
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
112
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
113 function reader(input)
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
114 local dbh = assert(DBI.Connect(
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
115 assert(input.driver, "no input.driver specified"),
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
116 assert(input.database, "no input.database specified"),
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
117 input.username, input.password,
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
118 input.host, input.port
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
119 ));
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
120 assert(dbh:ping());
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
121 local stmt = assert(dbh:prepare("SELECT * FROM prosody"));
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
122 assert(stmt:execute());
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
123 local keys = {"host", "user", "store", "key", "type", "value"};
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
124 local f,s,val = stmt:rows(true);
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
125 -- get SQL rows, sorted
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
126 local iter = mtools.sorted {
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
127 reader = function() val = f(s, val); return val; end;
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
128 filter = function(x)
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
129 for i=1,#keys do
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
130 if not x[keys[i]] then return false; end -- TODO log error, missing field
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
131 end
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
132 if x.host == "" then x.host = nil; end
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
133 if x.user == "" then x.user = nil; end
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
134 if x.store == "" then x.store = nil; end
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
135 return x;
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
136 end;
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
137 sorter = function(a, b)
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
138 local a_host, a_user, a_store = a.host or "", a.user or "", a.store or "";
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
139 local b_host, b_user, b_store = b.host or "", b.user or "", b.store or "";
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
140 return a_host > b_host or (a_host==b_host and a_user > b_user) or (a_host==b_host and a_user==b_user and a_store > b_store);
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
141 end;
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
142 };
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
143 -- merge rows to get stores
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
144 iter = mtools.merged(iter, function(a, b)
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
145 return (a.host == b.host and a.user == b.user and a.store == b.store);
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
146 end);
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
147 -- merge stores to get users
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
148 iter = mtools.merged(iter, function(a, b)
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
149 return (a[1].host == b[1].host and a[1].user == b[1].user);
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
150 end);
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
151 return function()
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
152 local x = iter();
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
153 return x and decode_user(x);
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
154 end;
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
155 end
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
156
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
157 function writer(output, iter)
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
158 local dbh = assert(DBI.Connect(
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
159 assert(output.driver, "no output.driver specified"),
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
160 assert(output.database, "no output.database specified"),
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
161 output.username, output.password,
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
162 output.host, output.port
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
163 ));
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
164 assert(dbh:ping());
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
165 create_table(dbh, output);
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
166 local stmt = assert(dbh:prepare("SELECT * FROM prosody"));
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
167 assert(stmt:execute());
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
168 local stmt = assert(dbh:prepare("DELETE FROM prosody"));
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
169 assert(stmt:execute());
4247
6a372135b4c4 tools/migration/migrator/prosody_sql.lua: Fix compatibility with PostgreSQL (thanks Timo)
Matthew Wild <mwild1@gmail.com>
parents: 4234
diff changeset
170 local insert_sql = "INSERT INTO `prosody` (`host`,`user`,`store`,`key`,`type`,`value`) VALUES (?,?,?,?,?,?)";
6a372135b4c4 tools/migration/migrator/prosody_sql.lua: Fix compatibility with PostgreSQL (thanks Timo)
Matthew Wild <mwild1@gmail.com>
parents: 4234
diff changeset
171 if output.driver == "PostgreSQL" then
6a372135b4c4 tools/migration/migrator/prosody_sql.lua: Fix compatibility with PostgreSQL (thanks Timo)
Matthew Wild <mwild1@gmail.com>
parents: 4234
diff changeset
172 insert_sql = insert_sql:gsub("`", "\"");
6a372135b4c4 tools/migration/migrator/prosody_sql.lua: Fix compatibility with PostgreSQL (thanks Timo)
Matthew Wild <mwild1@gmail.com>
parents: 4234
diff changeset
173 end
6a372135b4c4 tools/migration/migrator/prosody_sql.lua: Fix compatibility with PostgreSQL (thanks Timo)
Matthew Wild <mwild1@gmail.com>
parents: 4234
diff changeset
174 local insert = assert(dbh:prepare(insert_sql));
4197
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
175
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
176 return function(item)
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
177 if not item then assert(dbh:commit()) return dbh:close(); end -- end of input
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
178 local host = item.host or "";
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
179 local user = item.user or "";
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
180 for store, data in pairs(item.stores) do
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
181 -- TODO transactions
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
182 local extradata = {};
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
183 for key, value in pairs(data) do
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
184 if type(key) == "string" and key ~= "" then
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
185 local t, value = assert(serialize(value));
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
186 local ok, err = assert(insert:execute(host, user, store, key, t, value));
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
187 else
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
188 extradata[key] = value;
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
189 end
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
190 end
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
191 if next(extradata) ~= nil then
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
192 local t, extradata = assert(serialize(extradata));
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
193 local ok, err = assert(insert:execute(host, user, store, "", t, extradata));
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
194 end
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
195 end
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
196 end;
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
197 end
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
198
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
199
bef732980436 tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4193
diff changeset
200 return _M;