prosody
d26db1f936f8
plugins/mod_storage_sql.lua

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

Software / code / prosody

Comparison

plugins/mod_storage_sql.lua @ 4109:d26db1f936f8

mod_storage_sql: Create index when creating a new table
author Matthew Wild <mwild1@gmail.com>
date Tue, 11 Jan 2011 04:19:26 +0000
parent 4105:08560575762f
child 4218:8a271b3fcaa7
comparison
equal deleted inserted replaced
4108:e3e3aa286334 4109:d26db1f936f8
62 connection = dbh; 62 connection = dbh;
63 return connection; 63 return connection;
64 end 64 end
65 end 65 end
66 66
67 do -- process options to get a db connection 67 local function create_table()
68 DBI = require "DBI";
69
70 params = params or { driver = "SQLite3" };
71
72 if params.driver == "SQLite3" then
73 params.database = resolve_relative_path(prosody.paths.data or ".", params.database or "prosody.sqlite");
74 end
75
76 assert(params.driver and params.database, "Both the SQL driver and the database need to be specified");
77
78 assert(connect());
79
80 -- Automatically create table, ignore failure (table probably already exists)
81 local create_sql = "CREATE TABLE `prosody` (`host` TEXT, `user` TEXT, `store` TEXT, `key` TEXT, `type` TEXT, `value` TEXT);"; 68 local create_sql = "CREATE TABLE `prosody` (`host` TEXT, `user` TEXT, `store` TEXT, `key` TEXT, `type` TEXT, `value` TEXT);";
82 if params.driver == "PostgreSQL" then 69 if params.driver == "PostgreSQL" then
83 create_sql = create_sql:gsub("`", "\""); 70 create_sql = create_sql:gsub("`", "\"");
84 end 71 end
85 72
87 if stmt then 74 if stmt then
88 local ok = stmt:execute(); 75 local ok = stmt:execute();
89 local commit_ok = connection:commit(); 76 local commit_ok = connection:commit();
90 if ok and commit_ok then 77 if ok and commit_ok then
91 module:log("info", "Initialized new %s database with prosody table", params.driver); 78 module:log("info", "Initialized new %s database with prosody table", params.driver);
92 end 79 local index_sql = "CREATE INDEX `prosody_index` ON `prosody` (`host`, `user`, `store`, `key`)";
93 end 80 if params.driver == "PostgreSQL" then
81 index_sql = index_sql:gsub("`", "\"");
82 elseif params.driver == "MySQL" then
83 index_sql = index_sql:gsub("`([,)])", "`(20)%1");
84 end
85 local stmt, err = connection:prepare(index_sql);
86 local ok, commit_ok, commit_err;
87 if stmt then
88 ok, err = stmt:execute();
89 commit_ok, commit_err = connection:commit();
90 end
91 if not(ok and commit_ok) then
92 module:log("warn", "Failed to create index (%s), lookups may not be optimised", err or commit_err);
93 end
94 end
95 end
96 end
97
98 do -- process options to get a db connection
99 DBI = require "DBI";
100
101 params = params or { driver = "SQLite3" };
102
103 if params.driver == "SQLite3" then
104 params.database = resolve_relative_path(prosody.paths.data or ".", params.database or "prosody.sqlite");
105 end
106
107 assert(params.driver and params.database, "Both the SQL driver and the database need to be specified");
108
109 assert(connect());
110
111 -- Automatically create table, ignore failure (table probably already exists)
112 create_table();
94 end 113 end
95 114
96 local function serialize(value) 115 local function serialize(value)
97 local t = type(value); 116 local t = type(value);
98 if t == "string" or t == "boolean" or t == "number" then 117 if t == "string" or t == "boolean" or t == "number" then