From 16e8102a4e0ee8528925952f9b60ef87a4f8aa00 Mon Sep 17 00:00:00 2001 From: Matthew Wild Date: Tue, 11 Jan 2011 04:19:26 +0000 Subject: mod_storage_sql: Create index when creating a new table --- plugins/mod_storage_sql.lua | 45 ++++++++++++++++++++++++++++++++------------- 1 file changed, 32 insertions(+), 13 deletions(-) (limited to 'plugins') diff --git a/plugins/mod_storage_sql.lua b/plugins/mod_storage_sql.lua index 07735cb2..7917958d 100644 --- a/plugins/mod_storage_sql.lua +++ b/plugins/mod_storage_sql.lua @@ -64,6 +64,37 @@ local function connect() end end +local function create_table() + local create_sql = "CREATE TABLE `prosody` (`host` TEXT, `user` TEXT, `store` TEXT, `key` TEXT, `type` TEXT, `value` TEXT);"; + if params.driver == "PostgreSQL" then + create_sql = create_sql:gsub("`", "\""); + end + + local stmt = connection:prepare(create_sql); + if stmt then + local ok = stmt:execute(); + local commit_ok = connection:commit(); + if ok and commit_ok then + module:log("info", "Initialized new %s database with prosody table", params.driver); + local index_sql = "CREATE INDEX `prosody_index` ON `prosody` (`host`, `user`, `store`, `key`)"; + if params.driver == "PostgreSQL" then + index_sql = index_sql:gsub("`", "\""); + elseif params.driver == "MySQL" then + index_sql = index_sql:gsub("`([,)])", "`(20)%1"); + end + local stmt, err = connection:prepare(index_sql); + local ok, commit_ok, commit_err; + if stmt then + ok, err = stmt:execute(); + commit_ok, commit_err = connection:commit(); + end + if not(ok and commit_ok) then + module:log("warn", "Failed to create index (%s), lookups may not be optimised", err or commit_err); + end + end + end +end + do -- process options to get a db connection DBI = require "DBI"; @@ -78,19 +109,7 @@ do -- process options to get a db connection assert(connect()); -- Automatically create table, ignore failure (table probably already exists) - local create_sql = "CREATE TABLE `prosody` (`host` TEXT, `user` TEXT, `store` TEXT, `key` TEXT, `type` TEXT, `value` TEXT);"; - if params.driver == "PostgreSQL" then - create_sql = create_sql:gsub("`", "\""); - end - - local stmt = connection:prepare(create_sql); - if stmt then - local ok = stmt:execute(); - local commit_ok = connection:commit(); - if ok and commit_ok then - module:log("info", "Initialized new %s database with prosody table", params.driver); - end - end + create_table(); end local function serialize(value) -- cgit v1.2.3