prosody
605fa6bfafd1
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

Comparison

tools/migration/migrator/prosody_sql.lua @ 8063:605fa6bfafd1

migrator.prosody_sql: Switch to util.sql (#635)
author Kim Alvefur <zash@zash.se>
date Fri, 07 Apr 2017 13:16:12 +0200
parent 7881:4e3067272fae
child 8064:ffb36d1ae23b
comparison
equal deleted inserted replaced
8062:739bb455cafd 8063:605fa6bfafd1
1 1
2 local assert = assert; 2 local assert = assert;
3 local have_DBI, DBI = pcall(require,"DBI"); 3 local have_DBI = pcall(require,"DBI");
4 local print = print; 4 local print = print;
5 local type = type; 5 local type = type;
6 local next = next; 6 local next = next;
7 local pairs = pairs; 7 local pairs = pairs;
8 local t_sort = table.sort; 8 local t_sort = table.sort;
13 13
14 if not have_DBI then 14 if not have_DBI then
15 error("LuaDBI (required for SQL support) was not found, please see http://prosody.im/doc/depends#luadbi", 0); 15 error("LuaDBI (required for SQL support) was not found, please see http://prosody.im/doc/depends#luadbi", 0);
16 end 16 end
17 17
18 local sql = require "util.sql";
18 19
19 local function create_table(connection, params) 20 local function create_table(engine, name) -- luacheck: ignore 431/engine
20 local create_sql = "CREATE TABLE `prosody` (`host` TEXT, `user` TEXT, `store` TEXT, `key` TEXT, `type` TEXT, `value` TEXT);"; 21 local Table, Column, Index = sql.Table, sql.Column, sql.Index;
21 if params.driver == "PostgreSQL" then
22 create_sql = create_sql:gsub("`", "\"");
23 elseif params.driver == "MySQL" then
24 create_sql = create_sql:gsub("`value` TEXT", "`value` MEDIUMTEXT");
25 end
26 22
27 local stmt = connection:prepare(create_sql); 23 local ProsodyTable = Table {
28 if stmt then 24 name= name or "prosody";
29 local ok = stmt:execute(); 25 Column { name="host", type="TEXT", nullable=false };
30 local commit_ok = connection:commit(); 26 Column { name="user", type="TEXT", nullable=false };
31 if ok and commit_ok then 27 Column { name="store", type="TEXT", nullable=false };
32 local index_sql = "CREATE INDEX `prosody_index` ON `prosody` (`host`, `user`, `store`, `key`)"; 28 Column { name="key", type="TEXT", nullable=false };
33 if params.driver == "PostgreSQL" then 29 Column { name="type", type="TEXT", nullable=false };
34 index_sql = index_sql:gsub("`", "\""); 30 Column { name="value", type="MEDIUMTEXT", nullable=false };
35 elseif params.driver == "MySQL" then 31 Index { name="prosody_index", "host", "user", "store", "key" };
36 index_sql = index_sql:gsub("`([,)])", "`(20)%1"); 32 };
37 end 33 engine:transaction(function()
38 local stmt, err = connection:prepare(index_sql); 34 ProsodyTable:create(engine);
39 local ok, commit_ok, commit_err; 35 end);
40 if stmt then 36
41 ok, err = assert(stmt:execute());
42 commit_ok, commit_err = assert(connection:commit());
43 end
44 elseif params.driver == "MySQL" then -- COMPAT: Upgrade tables from 0.8.0
45 -- Failed to create, but check existing MySQL table here
46 local stmt = connection:prepare("SHOW COLUMNS FROM prosody WHERE Field='value' and Type='text'");
47 local ok = stmt:execute();
48 local commit_ok = connection:commit();
49 if ok and commit_ok then
50 if stmt:rowcount() > 0 then
51 local stmt = connection:prepare("ALTER TABLE prosody MODIFY COLUMN `value` MEDIUMTEXT");
52 local ok = stmt:execute();
53 local commit_ok = connection:commit();
54 if ok and commit_ok then
55 print("Database table automatically upgraded");
56 end
57 end
58 repeat until not stmt:fetch();
59 end
60 end
61 end
62 end 37 end
63 38
64 local function serialize(value) 39 local function serialize(value)
65 local t = type(value); 40 local t = type(value);
66 if t == "string" or t == "boolean" or t == "number" then 41 if t == "string" or t == "boolean" or t == "number" then
108 end 83 end
109 return userdata; 84 return userdata;
110 end 85 end
111 86
112 local function reader(input) 87 local function reader(input)
113 local dbh = assert(DBI.Connect( 88 local engine = assert(sql:create_engine(input);
114 assert(input.driver, "no input.driver specified"),
115 assert(input.database, "no input.database specified"),
116 input.username, input.password,
117 input.host, input.port
118 ));
119 assert(dbh:ping());
120 local stmt = assert(dbh:prepare("SELECT * FROM prosody"));
121 assert(stmt:execute());
122 local keys = {"host", "user", "store", "key", "type", "value"}; 89 local keys = {"host", "user", "store", "key", "type", "value"};
123 local f,s,val = stmt:rows(true); 90 assert(engine:connect());
91 local f,s,val = assert(engine:select("SELECT `host`, `user`, `store`, `key`, `type`, `value` FROM `prosody`;"));
124 -- get SQL rows, sorted 92 -- get SQL rows, sorted
125 local iter = mtools.sorted { 93 local iter = mtools.sorted {
126 reader = function() val = f(s, val); return val; end; 94 reader = function() val = f(s, val); return val; end;
127 filter = function(x) 95 filter = function(x)
128 for i=1,#keys do 96 for i=1,#keys do
129 if not x[keys[i]] then return false; end -- TODO log error, missing field 97 x[ keys[i] ] = x[i];
130 end 98 end
131 if x.host == "" then x.host = nil; end 99 if x.host == "" then x.host = nil; end
132 if x.user == "" then x.user = nil; end 100 if x.user == "" then x.user = nil; end
133 if x.store == "" then x.store = nil; end 101 if x.store == "" then x.store = nil; end
134 return x; 102 return x;
152 return x and decode_user(x); 120 return x and decode_user(x);
153 end; 121 end;
154 end 122 end
155 123
156 local function writer(output, iter) 124 local function writer(output, iter)
157 local dbh = assert(DBI.Connect( 125 local engine = assert(sql:create_engine(output, function (engine) -- luacheck: ignore 431/engine
158 assert(output.driver, "no output.driver specified"), 126 create_table(engine);
159 assert(output.database, "no output.database specified"), 127 end));
160 output.username, output.password, 128 assert(engine:connect());
161 output.host, output.port 129 assert(engine:delete("DELETE FROM prosody"));
162 ));
163 assert(dbh:ping());
164 create_table(dbh, output);
165 local stmt = assert(dbh:prepare("SELECT * FROM prosody"));
166 assert(stmt:execute());
167 local stmt = assert(dbh:prepare("DELETE FROM prosody"));
168 assert(stmt:execute());
169 local insert_sql = "INSERT INTO `prosody` (`host`,`user`,`store`,`key`,`type`,`value`) VALUES (?,?,?,?,?,?)"; 130 local insert_sql = "INSERT INTO `prosody` (`host`,`user`,`store`,`key`,`type`,`value`) VALUES (?,?,?,?,?,?)";
170 if output.driver == "PostgreSQL" then
171 insert_sql = insert_sql:gsub("`", "\"");
172 end
173 local insert = assert(dbh:prepare(insert_sql));
174 131
175 return function(item) 132 return function(item)
176 if not item then assert(dbh:commit()) return dbh:close(); end -- end of input 133 if not item then return end -- end of input
177 local host = item.host or ""; 134 local host = item.host or "";
178 local user = item.user or ""; 135 local user = item.user or "";
179 for store, data in pairs(item.stores) do 136 for store, data in pairs(item.stores) do
180 -- TODO transactions 137 -- TODO transactions
181 local extradata = {}; 138 local extradata = {};
182 for key, value in pairs(data) do 139 for key, value in pairs(data) do
183 if type(key) == "string" and key ~= "" then 140 if type(key) == "string" and key ~= "" then
184 local t, value = assert(serialize(value)); 141 local t, value = assert(serialize(value));
185 local ok, err = assert(insert:execute(host, user, store, key, t, value)); 142 local ok, err = assert(engine:insert(insert_sql, host, user, store, key, t, value));
186 else 143 else
187 extradata[key] = value; 144 extradata[key] = value;
188 end 145 end
189 end 146 end
190 if next(extradata) ~= nil then 147 if next(extradata) ~= nil then
191 local t, extradata = assert(serialize(extradata)); 148 local t, extradata = assert(serialize(extradata));
192 local ok, err = assert(insert:execute(host, user, store, "", t, extradata)); 149 local ok, err = assert(engine:insert(insert_sql, host, user, store, "", t, extradata));
193 end 150 end
194 end 151 end
195 end; 152 end;
196 end 153 end
197 154