Mercurial > prosody-hg

comparison plugins/mod_storage_sql2.lua @ 5897:296e32a1ff33

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merge 0.10 -> trunk
author Kim Alvefur <zash@zash.se>
date Thu, 31 Oct 2013 20:49:03 +0100
parents 9e47ece9457c
children 0e0aab930e10
comparison
equal deleted inserted replaced
5880:11f14d44438e 5897:296e32a1ff33
25 25
26 local engine; -- TODO create engine 26 local engine; -- TODO create engine
27 27
28 local function create_table() 28 local function create_table()
29 local Table,Column,Index = mod_sql.Table,mod_sql.Column,mod_sql.Index; 29 local Table,Column,Index = mod_sql.Table,mod_sql.Column,mod_sql.Index;
30 --[[ 30
31 local ProsodyTable = Table { 31 local ProsodyTable = Table {
32 name="prosody"; 32 name="prosody";
33 Column { name="host", type="TEXT", nullable=false }; 33 Column { name="host", type="TEXT", nullable=false };
34 Column { name="user", type="TEXT", nullable=false }; 34 Column { name="user", type="TEXT", nullable=false };
35 Column { name="store", type="TEXT", nullable=false }; 35 Column { name="store", type="TEXT", nullable=false };
36 Column { name="key", type="TEXT", nullable=false }; 36 Column { name="key", type="TEXT", nullable=false };
37 Column { name="type", type="TEXT", nullable=false }; 37 Column { name="type", type="TEXT", nullable=false };
38 Column { name="value", type="TEXT", nullable=false }; 38 Column { name="value", type="MEDIUMTEXT", nullable=false };
39 Index { name="prosody_index", "host", "user", "store", "key" }; 39 Index { name="prosody_index", "host", "user", "store", "key" };
40 }; 40 };
41 engine:transaction(function() 41 engine:transaction(function()
42 ProsodyTable:create(engine); 42 ProsodyTable:create(engine);
43 end);]] 43 end);
44 if not module:get_option("sql_manage_tables", true) then 44
45 return;
46 end
47
48 local create_sql = "CREATE TABLE `prosody` (`host` TEXT, `user` TEXT, `store` TEXT, `key` TEXT, `type` TEXT, `value` TEXT);";
49 if params.driver == "PostgreSQL" then
50 create_sql = create_sql:gsub("`", "\"");
51 elseif params.driver == "MySQL" then
52 create_sql = create_sql:gsub("`value` TEXT", "`value` MEDIUMTEXT")
53 :gsub(";$", " CHARACTER SET 'utf8' COLLATE 'utf8_bin';");
54 end
55
56 local index_sql = "CREATE INDEX `prosody_index` ON `prosody` (`host`, `user`, `store`, `key`)";
57 if params.driver == "PostgreSQL" then
58 index_sql = index_sql:gsub("`", "\"");
59 elseif params.driver == "MySQL" then
60 index_sql = index_sql:gsub("`([,)])", "`(20)%1");
61 end
62
63 local success,err = engine:transaction(function()
64 engine:execute(create_sql);
65 engine:execute(index_sql);
66 end);
67 if not success then -- so we failed to create
68 if params.driver == "MySQL" then
69 success,err = engine:transaction(function()
70 local result = engine:execute("SHOW COLUMNS FROM prosody WHERE Field='value' and Type='text'");
71 if result:rowcount() > 0 then
72 module:log("info", "Upgrading database schema...");
73 engine:execute("ALTER TABLE prosody MODIFY COLUMN `value` MEDIUMTEXT");
74 module:log("info", "Database table automatically upgraded");
75 end
76 return true;
77 end);
78 if not success then
79 module:log("error", "Failed to check/upgrade database schema (%s), please see "
80 .."http://prosody.im/doc/mysql for help",
81 err or "unknown error");
82 end
83 end
84 end
85 local ProsodyArchiveTable = Table { 45 local ProsodyArchiveTable = Table {
86 name="prosodyarchive"; 46 name="prosodyarchive";
87 Column { name="sort_id", type="INTEGER PRIMARY KEY AUTOINCREMENT", nullable=false }; 47 Column { name="sort_id", type="INTEGER", primary_key=true, auto_increment=true, nullable=false };
88 Column { name="host", type="TEXT", nullable=false }; 48 Column { name="host", type="TEXT", nullable=false };
89 Column { name="user", type="TEXT", nullable=false }; 49 Column { name="user", type="TEXT", nullable=false };
90 Column { name="store", type="TEXT", nullable=false }; 50 Column { name="store", type="TEXT", nullable=false };
91 Column { name="key", type="TEXT", nullable=false }; -- item id 51 Column { name="key", type="TEXT", nullable=false }; -- item id
92 Column { name="when", type="INTEGER", nullable=false }; -- timestamp 52 Column { name="when", type="INTEGER", nullable=false }; -- timestamp
93 Column { name="with", type="TEXT", nullable=false }; -- related id 53 Column { name="with", type="TEXT", nullable=false }; -- related id
94 Column { name="type", type="TEXT", nullable=false }; 54 Column { name="type", type="TEXT", nullable=false };
95 Column { name="value", type=params.driver == "MySQL" and "MEDIUMTEXT" or "TEXT", nullable=false }; 55 Column { name="value", type="MEDIUMTEXT", nullable=false };
96 Index { name="prosodyarchive_index", "host", "user", "store", "key" }; 56 Index { name="prosodyarchive_index", unique = true, "host", "user", "store", "key" };
97 }; 57 };
98 engine:transaction(function() 58 engine:transaction(function()
99 ProsodyArchiveTable:create(engine); 59 ProsodyArchiveTable:create(engine);
100 end); 60 end);
101 end 61 end
102 local function set_encoding() 62
103 if params.driver == "SQLite3" then return end 63 local function upgrade_table()
104 local set_names_query = "SET NAMES 'utf8';";
105 if params.driver == "MySQL" then 64 if params.driver == "MySQL" then
106 set_names_query = set_names_query:gsub(";$", " COLLATE 'utf8_bin';"); 65 local success,err = engine:transaction(function()
107 end 66 local result = engine:execute("SHOW COLUMNS FROM prosody WHERE Field='value' and Type='text'");
108 local success,err = engine:transaction(function() return engine:execute(set_names_query); end); 67 if result:rowcount() > 0 then
109 if not success then 68 module:log("info", "Upgrading database schema...");
110 module:log("error", "Failed to set database connection encoding to UTF8: %s", err); 69 engine:execute("ALTER TABLE prosody MODIFY COLUMN `value` MEDIUMTEXT");
111 return; 70 module:log("info", "Database table automatically upgraded");
112 end 71 end
113 if params.driver == "MySQL" then 72 return true;
73 end);
74 if not success then
75 module:log("error", "Failed to check/upgrade database schema (%s), please see "
76 .."http://prosody.im/doc/mysql for help",
77 err or "unknown error");
78 return false;
79 end
114 -- COMPAT w/pre-0.9: Upgrade tables to UTF-8 if not already 80 -- COMPAT w/pre-0.9: Upgrade tables to UTF-8 if not already
115 local check_encoding_query = "SELECT `COLUMN_NAME`,`COLUMN_TYPE` FROM `information_schema`.`columns` WHERE `TABLE_NAME`='prosody' AND ( `CHARACTER_SET_NAME`!='utf8' OR `COLLATION_NAME`!='utf8_bin' );"; 81 local check_encoding_query = "SELECT `COLUMN_NAME`,`COLUMN_TYPE` FROM `information_schema`.`columns` WHERE `TABLE_NAME`='prosody' AND ( `CHARACTER_SET_NAME`!='utf8' OR `COLLATION_NAME`!='utf8_bin' );";
116 local success,err = engine:transaction(function() 82 success,err = engine:transaction(function()
117 local result = engine:execute(check_encoding_query); 83 local result = engine:execute(check_encoding_query);
118 local n_bad_columns = result:rowcount(); 84 local n_bad_columns = result:rowcount();
119 if n_bad_columns > 0 then 85 if n_bad_columns > 0 then
120 module:log("warn", "Found %d columns in prosody table requiring encoding change, updating now...", n_bad_columns); 86 module:log("warn", "Found %d columns in prosody table requiring encoding change, updating now...", n_bad_columns);
121 local fix_column_query1 = "ALTER TABLE `prosody` CHANGE `%s` `%s` BLOB;"; 87 local fix_column_query1 = "ALTER TABLE `prosody` CHANGE `%s` `%s` BLOB;";
126 engine:execute(fix_column_query2:format(column_name, column_name, column_type)); 92 engine:execute(fix_column_query2:format(column_name, column_name, column_type));
127 end 93 end
128 module:log("info", "Database encoding upgrade complete!"); 94 module:log("info", "Database encoding upgrade complete!");
129 end 95 end
130 end); 96 end);
131 local success,err = engine:transaction(function() return engine:execute(check_encoding_query); end); 97 success,err = engine:transaction(function() return engine:execute(check_encoding_query); end);
132 if not success then 98 if not success then
133 module:log("error", "Failed to check/upgrade database encoding: %s", err or "unknown error"); 99 module:log("error", "Failed to check/upgrade database encoding: %s", err or "unknown error");
134 end 100 end
135 end 101 end
136 end 102 end
145 assert(params.driver and params.database, "Both the SQL driver and the database need to be specified"); 111 assert(params.driver and params.database, "Both the SQL driver and the database need to be specified");
146 112
147 --local dburi = db2uri(params); 113 --local dburi = db2uri(params);
148 engine = mod_sql:create_engine(params); 114 engine = mod_sql:create_engine(params);
149 115
150 -- Encoding mess 116 engine:set_encoding();
151 set_encoding(); 117
152 118 if module:get_option("sql_manage_tables", true) then
153 -- Automatically create table, ignore failure (table probably already exists) 119 -- Automatically create table, ignore failure (table probably already exists)
154 create_table(); 120 create_table();
121 -- Encoding mess
122 upgrade_table();
123 end
155 end 124 end
156 125
157 local function serialize(value) 126 local function serialize(value)
158 local t = type(value); 127 local t = type(value);
159 if t == "string" or t == "boolean" or t == "number" then 128 if t == "string" or t == "boolean" or t == "number" then