prosody
36d9c1226fbc
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 @ 8065:36d9c1226fbc

Merge 0.10->trunk
author Kim Alvefur <zash@zash.se>
date Sat, 08 Apr 2017 00:48:15 +0200
parent 7885:236b5a6154b2
parent 8064:ffb36d1ae23b
child 8068:5abb6bc45edd
comparison
equal deleted inserted replaced
8060:71bdfdf6565d 8065:36d9c1226fbc
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 https://prosody.im/doc/depends#luadbi", 0); 15 error("LuaDBI (required for SQL support) was not found, please see https://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
107 end 82 end
108 end 83 end
109 return userdata; 84 return userdata;
110 end 85 end
111 86
87 local function needs_upgrade(engine, params)
88 if params.driver == "MySQL" then
89 local success = engine:transaction(function()
90 local result = engine:execute("SHOW COLUMNS FROM prosody WHERE Field='value' and Type='text'");
91 assert(result:rowcount() == 0);
92
93 -- COMPAT w/pre-0.10: Upgrade table to UTF-8 if not already
94 local check_encoding_query = [[
95 SELECT `COLUMN_NAME`,`COLUMN_TYPE`,`TABLE_NAME`
96 FROM `information_schema`.`columns`
97 WHERE `TABLE_NAME` LIKE 'prosody%%' AND ( `CHARACTER_SET_NAME`!='%s' OR `COLLATION_NAME`!='%s_bin' );
98 ]];
99 check_encoding_query = check_encoding_query:format(engine.charset, engine.charset);
100 local result = engine:execute(check_encoding_query);
101 assert(result:rowcount() == 0)
102 end);
103 if not success then
104 -- Upgrade required
105 return true;
106 end
107 end
108 return false;
109 end
110
112 local function reader(input) 111 local function reader(input)
113 local dbh = assert(DBI.Connect( 112 local engine = assert(sql:create_engine(input, function (engine) -- luacheck: ignore 431/engine
114 assert(input.driver, "no input.driver specified"), 113 if needs_upgrade(engine, input) then
115 assert(input.database, "no input.database specified"), 114 error("Old database format detected. Please run: prosodyctl mod_storage_sql upgrade");
116 input.username, input.password, 115 end
117 input.host, input.port 116 end));
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"}; 117 local keys = {"host", "user", "store", "key", "type", "value"};
123 local f,s,val = stmt:rows(true); 118 assert(engine:connect());
119 local f,s,val = assert(engine:select("SELECT `host`, `user`, `store`, `key`, `type`, `value` FROM `prosody`;"));
124 -- get SQL rows, sorted 120 -- get SQL rows, sorted
125 local iter = mtools.sorted { 121 local iter = mtools.sorted {
126 reader = function() val = f(s, val); return val; end; 122 reader = function() val = f(s, val); return val; end;
127 filter = function(x) 123 filter = function(x)
128 for i=1,#keys do 124 for i=1,#keys do
129 if not x[keys[i]] then return false; end -- TODO log error, missing field 125 x[ keys[i] ] = x[i];
130 end 126 end
131 if x.host == "" then x.host = nil; end 127 if x.host == "" then x.host = nil; end
132 if x.user == "" then x.user = nil; end 128 if x.user == "" then x.user = nil; end
133 if x.store == "" then x.store = nil; end 129 if x.store == "" then x.store = nil; end
134 return x; 130 return x;
152 return x and decode_user(x); 148 return x and decode_user(x);
153 end; 149 end;
154 end 150 end
155 151
156 local function writer(output, iter) 152 local function writer(output, iter)
157 local dbh = assert(DBI.Connect( 153 local engine = assert(sql:create_engine(output, function (engine) -- luacheck: ignore 431/engine
158 assert(output.driver, "no output.driver specified"), 154 if needs_upgrade(engine, output) then
159 assert(output.database, "no output.database specified"), 155 error("Old database format detected. Please run: prosodyctl mod_storage_sql upgrade");
160 output.username, output.password, 156 end
161 output.host, output.port 157 create_table(engine);
162 )); 158 end));
163 assert(dbh:ping()); 159 assert(engine:connect());
164 create_table(dbh, output); 160 assert(engine:delete("DELETE FROM prosody"));
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 (?,?,?,?,?,?)"; 161 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 162
175 return function(item) 163 return function(item)
176 if not item then assert(dbh:commit()) return dbh:close(); end -- end of input 164 if not item then return end -- end of input
177 local host = item.host or ""; 165 local host = item.host or "";
178 local user = item.user or ""; 166 local user = item.user or "";
179 for store, data in pairs(item.stores) do 167 for store, data in pairs(item.stores) do
180 -- TODO transactions 168 -- TODO transactions
181 local extradata = {}; 169 local extradata = {};
182 for key, value in pairs(data) do 170 for key, value in pairs(data) do
183 if type(key) == "string" and key ~= "" then 171 if type(key) == "string" and key ~= "" then
184 local t, value = assert(serialize(value)); 172 local t, value = assert(serialize(value));
185 local ok, err = assert(insert:execute(host, user, store, key, t, value)); 173 local ok, err = assert(engine:insert(insert_sql, host, user, store, key, t, value));
186 else 174 else
187 extradata[key] = value; 175 extradata[key] = value;
188 end 176 end
189 end 177 end
190 if next(extradata) ~= nil then 178 if next(extradata) ~= nil then
191 local t, extradata = assert(serialize(extradata)); 179 local t, extradata = assert(serialize(extradata));
192 local ok, err = assert(insert:execute(host, user, store, "", t, extradata)); 180 local ok, err = assert(engine:insert(insert_sql, host, user, store, "", t, extradata));
193 end 181 end
194 end 182 end
195 end; 183 end;
196 end 184 end
197 185