prosody
57780ba1938f
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 @ 8666:57780ba1938f

util.startup: Move original_logging_config to a local variable
author Matthew Wild <mwild1@gmail.com>
date Thu, 22 Mar 2018 21:46:55 +0000
parent 8074:4b403f881176
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
4166
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
1
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
2 local assert = assert;
8063
605fa6bfafd1 migrator.prosody_sql: Switch to util.sql (#635)
Kim Alvefur <zash@zash.se>
parents: 7881
diff changeset
3 local have_DBI = pcall(require,"DBI");
4166
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
4 local print = print;
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
5 local type = type;
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
6 local next = next;
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
7 local pairs = pairs;
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
8 local t_sort = table.sort;
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
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: 4166
diff changeset
10 local mtools = require "migrator.mtools";
4166
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
11 local tostring = tostring;
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
12 local tonumber = tonumber;
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
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
7359
a5a080c12c96 Update every link to the documentation to use HTTPS
Emmanuel Gil Peyrot <linkmauve@linkmauve.fr>
parents: 5776
diff changeset
15 error("LuaDBI (required for SQL support) was not found, please see https://prosody.im/doc/depends#luadbi", 0);
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
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
8063
605fa6bfafd1 migrator.prosody_sql: Switch to util.sql (#635)
Kim Alvefur <zash@zash.se>
parents: 7881
diff changeset
18 local sql = require "util.sql";
4166
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
19
8063
605fa6bfafd1 migrator.prosody_sql: Switch to util.sql (#635)
Kim Alvefur <zash@zash.se>
parents: 7881
diff changeset
20 local function create_table(engine, name) -- luacheck: ignore 431/engine
605fa6bfafd1 migrator.prosody_sql: Switch to util.sql (#635)
Kim Alvefur <zash@zash.se>
parents: 7881
diff changeset
21 local Table, Column, Index = sql.Table, sql.Column, sql.Index;
5776
bd0ff8ae98a8 Remove all trailing whitespace
Florian Zeitz <florob@babelmonkeys.de>
parents: 4310
diff changeset
22
8063
605fa6bfafd1 migrator.prosody_sql: Switch to util.sql (#635)
Kim Alvefur <zash@zash.se>
parents: 7881
diff changeset
23 local ProsodyTable = Table {
605fa6bfafd1 migrator.prosody_sql: Switch to util.sql (#635)
Kim Alvefur <zash@zash.se>
parents: 7881
diff changeset
24 name= name or "prosody";
605fa6bfafd1 migrator.prosody_sql: Switch to util.sql (#635)
Kim Alvefur <zash@zash.se>
parents: 7881
diff changeset
25 Column { name="host", type="TEXT", nullable=false };
605fa6bfafd1 migrator.prosody_sql: Switch to util.sql (#635)
Kim Alvefur <zash@zash.se>
parents: 7881
diff changeset
26 Column { name="user", type="TEXT", nullable=false };
605fa6bfafd1 migrator.prosody_sql: Switch to util.sql (#635)
Kim Alvefur <zash@zash.se>
parents: 7881
diff changeset
27 Column { name="store", type="TEXT", nullable=false };
605fa6bfafd1 migrator.prosody_sql: Switch to util.sql (#635)
Kim Alvefur <zash@zash.se>
parents: 7881
diff changeset
28 Column { name="key", type="TEXT", nullable=false };
605fa6bfafd1 migrator.prosody_sql: Switch to util.sql (#635)
Kim Alvefur <zash@zash.se>
parents: 7881
diff changeset
29 Column { name="type", type="TEXT", nullable=false };
605fa6bfafd1 migrator.prosody_sql: Switch to util.sql (#635)
Kim Alvefur <zash@zash.se>
parents: 7881
diff changeset
30 Column { name="value", type="MEDIUMTEXT", nullable=false };
605fa6bfafd1 migrator.prosody_sql: Switch to util.sql (#635)
Kim Alvefur <zash@zash.se>
parents: 7881
diff changeset
31 Index { name="prosody_index", "host", "user", "store", "key" };
605fa6bfafd1 migrator.prosody_sql: Switch to util.sql (#635)
Kim Alvefur <zash@zash.se>
parents: 7881
diff changeset
32 };
605fa6bfafd1 migrator.prosody_sql: Switch to util.sql (#635)
Kim Alvefur <zash@zash.se>
parents: 7881
diff changeset
33 engine:transaction(function()
605fa6bfafd1 migrator.prosody_sql: Switch to util.sql (#635)
Kim Alvefur <zash@zash.se>
parents: 7881
diff changeset
34 ProsodyTable:create(engine);
605fa6bfafd1 migrator.prosody_sql: Switch to util.sql (#635)
Kim Alvefur <zash@zash.se>
parents: 7881
diff changeset
35 end);
605fa6bfafd1 migrator.prosody_sql: Switch to util.sql (#635)
Kim Alvefur <zash@zash.se>
parents: 7881
diff changeset
36
4166
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
37 end
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
38
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
39 local function serialize(value)
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
40 local t = type(value);
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
41 if t == "string" or t == "boolean" or t == "number" then
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
42 return t, tostring(value);
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
43 elseif t == "table" then
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
44 local value,err = json.encode(value);
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
45 if value then return "json", value; end
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
46 return nil, err;
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
47 end
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
48 return nil, "Unhandled value type: "..t;
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
49 end
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
50 local function deserialize(t, value)
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
51 if t == "string" then return value;
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
52 elseif t == "boolean" then
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
53 if value == "true" then return true;
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
54 elseif value == "false" then return false; end
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
55 elseif t == "number" then return tonumber(value);
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
56 elseif t == "json" then
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
57 return json.decode(value);
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
58 end
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
59 end
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
60
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
61 local function decode_user(item)
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
62 local userdata = {
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
63 user = item[1][1].user;
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
64 host = item[1][1].host;
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
65 stores = {};
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
66 };
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
67 for i=1,#item do -- loop over stores
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
68 local result = {};
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
69 local store = item[i];
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
70 for i=1,#store do -- loop over store data
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
71 local row = store[i];
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
72 local k = row.key;
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
73 local v = deserialize(row.type, row.value);
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
74 if k and v then
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
75 if k ~= "" then result[k] = v; elseif type(v) == "table" then
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
76 for a,b in pairs(v) do
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
77 result[a] = b;
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
78 end
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
79 end
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
80 end
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
81 userdata.stores[store[1].store] = result;
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
82 end
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
83 end
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
84 return userdata;
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
85 end
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
86
8064
ffb36d1ae23b migrator/prosody_sql: Abort and demand database be upgraded if it needs to be (#635)
Kim Alvefur <zash@zash.se>
parents: 8063
diff changeset
87 local function needs_upgrade(engine, params)
ffb36d1ae23b migrator/prosody_sql: Abort and demand database be upgraded if it needs to be (#635)
Kim Alvefur <zash@zash.se>
parents: 8063
diff changeset
88 if params.driver == "MySQL" then
ffb36d1ae23b migrator/prosody_sql: Abort and demand database be upgraded if it needs to be (#635)
Kim Alvefur <zash@zash.se>
parents: 8063
diff changeset
89 local success = engine:transaction(function()
ffb36d1ae23b migrator/prosody_sql: Abort and demand database be upgraded if it needs to be (#635)
Kim Alvefur <zash@zash.se>
parents: 8063
diff changeset
90 local result = engine:execute("SHOW COLUMNS FROM prosody WHERE Field='value' and Type='text'");
ffb36d1ae23b migrator/prosody_sql: Abort and demand database be upgraded if it needs to be (#635)
Kim Alvefur <zash@zash.se>
parents: 8063
diff changeset
91 assert(result:rowcount() == 0);
ffb36d1ae23b migrator/prosody_sql: Abort and demand database be upgraded if it needs to be (#635)
Kim Alvefur <zash@zash.se>
parents: 8063
diff changeset
92
ffb36d1ae23b migrator/prosody_sql: Abort and demand database be upgraded if it needs to be (#635)
Kim Alvefur <zash@zash.se>
parents: 8063
diff changeset
93 -- COMPAT w/pre-0.10: Upgrade table to UTF-8 if not already
ffb36d1ae23b migrator/prosody_sql: Abort and demand database be upgraded if it needs to be (#635)
Kim Alvefur <zash@zash.se>
parents: 8063
diff changeset
94 local check_encoding_query = [[
8073
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8066
diff changeset
95 SELECT "COLUMN_NAME","COLUMN_TYPE","TABLE_NAME"
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8066
diff changeset
96 FROM "information_schema"."columns"
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8066
diff changeset
97 WHERE "TABLE_NAME" LIKE 'prosody%%' AND ( "CHARACTER_SET_NAME"!='%s' OR "COLLATION_NAME"!='%s_bin' );
8064
ffb36d1ae23b migrator/prosody_sql: Abort and demand database be upgraded if it needs to be (#635)
Kim Alvefur <zash@zash.se>
parents: 8063
diff changeset
98 ]];
ffb36d1ae23b migrator/prosody_sql: Abort and demand database be upgraded if it needs to be (#635)
Kim Alvefur <zash@zash.se>
parents: 8063
diff changeset
99 check_encoding_query = check_encoding_query:format(engine.charset, engine.charset);
ffb36d1ae23b migrator/prosody_sql: Abort and demand database be upgraded if it needs to be (#635)
Kim Alvefur <zash@zash.se>
parents: 8063
diff changeset
100 local result = engine:execute(check_encoding_query);
ffb36d1ae23b migrator/prosody_sql: Abort and demand database be upgraded if it needs to be (#635)
Kim Alvefur <zash@zash.se>
parents: 8063
diff changeset
101 assert(result:rowcount() == 0)
ffb36d1ae23b migrator/prosody_sql: Abort and demand database be upgraded if it needs to be (#635)
Kim Alvefur <zash@zash.se>
parents: 8063
diff changeset
102 end);
ffb36d1ae23b migrator/prosody_sql: Abort and demand database be upgraded if it needs to be (#635)
Kim Alvefur <zash@zash.se>
parents: 8063
diff changeset
103 if not success then
ffb36d1ae23b migrator/prosody_sql: Abort and demand database be upgraded if it needs to be (#635)
Kim Alvefur <zash@zash.se>
parents: 8063
diff changeset
104 -- Upgrade required
ffb36d1ae23b migrator/prosody_sql: Abort and demand database be upgraded if it needs to be (#635)
Kim Alvefur <zash@zash.se>
parents: 8063
diff changeset
105 return true;
ffb36d1ae23b migrator/prosody_sql: Abort and demand database be upgraded if it needs to be (#635)
Kim Alvefur <zash@zash.se>
parents: 8063
diff changeset
106 end
ffb36d1ae23b migrator/prosody_sql: Abort and demand database be upgraded if it needs to be (#635)
Kim Alvefur <zash@zash.se>
parents: 8063
diff changeset
107 end
ffb36d1ae23b migrator/prosody_sql: Abort and demand database be upgraded if it needs to be (#635)
Kim Alvefur <zash@zash.se>
parents: 8063
diff changeset
108 return false;
ffb36d1ae23b migrator/prosody_sql: Abort and demand database be upgraded if it needs to be (#635)
Kim Alvefur <zash@zash.se>
parents: 8063
diff changeset
109 end
ffb36d1ae23b migrator/prosody_sql: Abort and demand database be upgraded if it needs to be (#635)
Kim Alvefur <zash@zash.se>
parents: 8063
diff changeset
110
7881
4e3067272fae tools/migration/migrator/*: Remove use of module()
Kim Alvefur <zash@zash.se>
parents: 5776
diff changeset
111 local function reader(input)
8064
ffb36d1ae23b migrator/prosody_sql: Abort and demand database be upgraded if it needs to be (#635)
Kim Alvefur <zash@zash.se>
parents: 8063
diff changeset
112 local engine = assert(sql:create_engine(input, function (engine) -- luacheck: ignore 431/engine
ffb36d1ae23b migrator/prosody_sql: Abort and demand database be upgraded if it needs to be (#635)
Kim Alvefur <zash@zash.se>
parents: 8063
diff changeset
113 if needs_upgrade(engine, input) then
ffb36d1ae23b migrator/prosody_sql: Abort and demand database be upgraded if it needs to be (#635)
Kim Alvefur <zash@zash.se>
parents: 8063
diff changeset
114 error("Old database format detected. Please run: prosodyctl mod_storage_sql upgrade");
ffb36d1ae23b migrator/prosody_sql: Abort and demand database be upgraded if it needs to be (#635)
Kim Alvefur <zash@zash.se>
parents: 8063
diff changeset
115 end
ffb36d1ae23b migrator/prosody_sql: Abort and demand database be upgraded if it needs to be (#635)
Kim Alvefur <zash@zash.se>
parents: 8063
diff changeset
116 end));
4166
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
117 local keys = {"host", "user", "store", "key", "type", "value"};
8063
605fa6bfafd1 migrator.prosody_sql: Switch to util.sql (#635)
Kim Alvefur <zash@zash.se>
parents: 7881
diff changeset
118 assert(engine:connect());
8073
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8066
diff changeset
119 local f,s,val = assert(engine:select("SELECT \"host\", \"user\", \"store\", \"key\", \"type\", \"value\" FROM \"prosody\";"));
4166
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
120 -- get SQL rows, sorted
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
121 local iter = mtools.sorted {
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
122 reader = function() val = f(s, val); return val; end;
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
123 filter = function(x)
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
124 for i=1,#keys do
8063
605fa6bfafd1 migrator.prosody_sql: Switch to util.sql (#635)
Kim Alvefur <zash@zash.se>
parents: 7881
diff changeset
125 x[ keys[i] ] = x[i];
4166
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
126 end
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
127 if x.host == "" then x.host = nil; end
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
128 if x.user == "" then x.user = nil; end
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
129 if x.store == "" then x.store = nil; end
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
130 return x;
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
131 end;
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
132 sorter = function(a, b)
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
133 local a_host, a_user, a_store = a.host or "", a.user or "", a.store or "";
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
134 local b_host, b_user, b_store = b.host or "", b.user or "", b.store or "";
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
135 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);
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
136 end;
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
137 };
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
138 -- merge rows to get stores
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
139 iter = mtools.merged(iter, function(a, b)
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
140 return (a.host == b.host and a.user == b.user and a.store == b.store);
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
141 end);
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
142 -- merge stores to get users
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
143 iter = mtools.merged(iter, function(a, b)
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
144 return (a[1].host == b[1].host and a[1].user == b[1].user);
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
145 end);
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
146 return function()
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
147 local x = iter();
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
148 return x and decode_user(x);
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
149 end;
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
150 end
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
151
7881
4e3067272fae tools/migration/migrator/*: Remove use of module()
Kim Alvefur <zash@zash.se>
parents: 5776
diff changeset
152 local function writer(output, iter)
8063
605fa6bfafd1 migrator.prosody_sql: Switch to util.sql (#635)
Kim Alvefur <zash@zash.se>
parents: 7881
diff changeset
153 local engine = assert(sql:create_engine(output, function (engine) -- luacheck: ignore 431/engine
8064
ffb36d1ae23b migrator/prosody_sql: Abort and demand database be upgraded if it needs to be (#635)
Kim Alvefur <zash@zash.se>
parents: 8063
diff changeset
154 if needs_upgrade(engine, output) then
ffb36d1ae23b migrator/prosody_sql: Abort and demand database be upgraded if it needs to be (#635)
Kim Alvefur <zash@zash.se>
parents: 8063
diff changeset
155 error("Old database format detected. Please run: prosodyctl mod_storage_sql upgrade");
ffb36d1ae23b migrator/prosody_sql: Abort and demand database be upgraded if it needs to be (#635)
Kim Alvefur <zash@zash.se>
parents: 8063
diff changeset
156 end
8063
605fa6bfafd1 migrator.prosody_sql: Switch to util.sql (#635)
Kim Alvefur <zash@zash.se>
parents: 7881
diff changeset
157 create_table(engine);
605fa6bfafd1 migrator.prosody_sql: Switch to util.sql (#635)
Kim Alvefur <zash@zash.se>
parents: 7881
diff changeset
158 end));
605fa6bfafd1 migrator.prosody_sql: Switch to util.sql (#635)
Kim Alvefur <zash@zash.se>
parents: 7881
diff changeset
159 assert(engine:connect());
8073
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8066
diff changeset
160 assert(engine:delete("DELETE FROM \"prosody\""));
7361412a9664 SQL: Use standard quotes for columns and other identifiers, rewrite to grave accents for MySQL only (fixes #885)
Kim Alvefur <zash@zash.se>
parents: 8066
diff changeset
161 local insert_sql = "INSERT INTO \"prosody\" (\"host\",\"user\",\"store\",\"key\",\"type\",\"value\") VALUES (?,?,?,?,?,?)";
4166
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
162
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
163 return function(item)
8066
5eec340c75fb migration/prosody_sql: Commit transaction when all items have been processed
Kim Alvefur <zash@zash.se>
parents: 8064
diff changeset
164 if not item then assert(engine.conn:commit()) return end -- end of input
4166
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
165 local host = item.host or "";
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
166 local user = item.user or "";
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
167 for store, data in pairs(item.stores) do
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
168 -- TODO transactions
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
169 local extradata = {};
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
170 for key, value in pairs(data) do
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
171 if type(key) == "string" and key ~= "" then
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
172 local t, value = assert(serialize(value));
8063
605fa6bfafd1 migrator.prosody_sql: Switch to util.sql (#635)
Kim Alvefur <zash@zash.se>
parents: 7881
diff changeset
173 local ok, err = assert(engine:insert(insert_sql, host, user, store, key, t, value));
4166
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
174 else
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
175 extradata[key] = value;
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
176 end
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
177 end
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
178 if next(extradata) ~= nil then
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
179 local t, extradata = assert(serialize(extradata));
8063
605fa6bfafd1 migrator.prosody_sql: Switch to util.sql (#635)
Kim Alvefur <zash@zash.se>
parents: 7881
diff changeset
180 local ok, err = assert(engine:insert(insert_sql, host, user, store, "", t, extradata));
4166
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
181 end
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
182 end
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
183 end;
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
184 end
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
185
3ac90743039b tools/migration/*.lua: Convert to unix line endings
Matthew Wild <mwild1@gmail.com>
parents: 4162
diff changeset
186
7881
4e3067272fae tools/migration/migrator/*: Remove use of module()
Kim Alvefur <zash@zash.se>
parents: 5776
diff changeset
187 return {
4e3067272fae tools/migration/migrator/*: Remove use of module()
Kim Alvefur <zash@zash.se>
parents: 5776
diff changeset
188 reader = reader;
4e3067272fae tools/migration/migrator/*: Remove use of module()
Kim Alvefur <zash@zash.se>
parents: 5776
diff changeset
189 writer = writer;
4e3067272fae tools/migration/migrator/*: Remove use of module()
Kim Alvefur <zash@zash.se>
parents: 5776
diff changeset
190 }