data/database.lua

   1 -- Freeciv - Copyright (C) 2011 - The Freeciv Project
   2 --   This program is free software; you can redistribute it and/or modify
   3 --   it under the terms of the GNU General Public License as published by
   4 --   the Free Software Foundation; either version 2, or (at your option)
   5 --   any later version.
   6 --
   7 --   This program is distributed in the hope that it will be useful,
   8 --   but WITHOUT ANY WARRANTY; without even the implied warranty of
   9 --   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  10 --   GNU General Public License for more details.
  11
  12 -- This file is the Freeciv server`s interface to the database backend
  13 -- when authentication is enabled. See doc/README.fcdb.
  14
  15 -- **************************************************************************
  16 -- basic mysql functions
  17 -- **************************************************************************
  18
  19 local dbh = nil
  20
  21 -- connect to a MySQL database (or stop with an error)
  22 local function mysql_connect()
  23   local err -- error message
  24
  25   if dbh then
  26     dbh:close()
  27   end
  28
  29   local sql = luasql.mysql()
  30
  31   log.verbose('MySQL database version is %s.', luasql._MYSQLVERSION)
  32
  33   -- Load the database parameters.
  34   local database = fcdb.option(fcdb.param.DATABASE)
  35   local user     = fcdb.option(fcdb.param.USER)
  36   local password = fcdb.option(fcdb.param.PASSWORD)
  37   local host     = fcdb.option(fcdb.param.HOST)
  38   local port     = fcdb.option(fcdb.param.PORT)
  39
  40   dbh, err = sql:connect(database, user, password, host, port)
  41   if not dbh then
  42     log.error('[mysql:connect]: %s', err)
  43     return fcdb.status.ERROR
  44   else
  45     return fcdb.status.TRUE
  46   end
  47 end
  48
  49 -- open a SQLite database (or stop with an error)
  50 local function sqlite_connect()
  51   local err -- error message
  52
  53   if dbh then
  54     dbh:close()
  55   end
  56
  57   local sql = luasql.sqlite3()
  58
  59   -- Load the database parameters.
  60   local database = fcdb.option(fcdb.param.DATABASE)
  61   -- USER/PASSWORD/HOST/PORT ignored for SQLite
  62
  63   dbh, err = sql:connect(database)
  64   if not dbh then
  65     log.error('[sqlite:connect]: %s', err)
  66     return fcdb.status.ERROR
  67   else
  68     return fcdb.status.TRUE
  69   end
  70 end
  71
  72 -- execute a sql query
  73 local function execute(query)
  74   local res -- result handle
  75   local err -- error message
  76
  77   if not dbh then
  78     return fcdb.status.ERROR, "[execute] Invalid database handle."
  79   end
  80
  81   -- log.verbose("Database query: %s", query)
  82
  83   res, err = dbh:execute(query)
  84   if not res then
  85     log.error("[luasql:execute]: %s", err)
  86     return fcdb.status.ERROR, err
  87   else
  88     return fcdb.status.TRUE, res
  89   end
  90 end
  91
  92 -- DIRTY: return a string to put in a database query which gets the
  93 -- current time (in seconds since the epoch, UTC).
  94 -- (This should be replaced with Lua os.time() once the script has access
  95 -- to this, see <http://gna.org/bugs/?19729>.)
  96 function sql_time()
  97   local backend = fcdb.option(fcdb.param.BACKEND)
  98   if backend == 'mysql' then
  99     return 'UNIX_TIMESTAMP()'
 100   elseif backend == 'sqlite' then
 101     return 'strftime(\'%s\',\'now\')'
 102   else
 103     log.error('Don\'t know how to do timestamps for database backend \'%s\'', backend)
 104     return 'ERROR'
 105   end
 106 end
 107
 108 -- Set up tables for an SQLite database.
 109 -- (Since there`s no concept of user rights, we can do this directly from Lua,
 110 -- without needing a separate script like MySQL. The server operator can do
 111 -- "/fcdb lua sqlite_createdb()" from the server prompt.)
 112 function sqlite_createdb()
 113   local query
 114   local res
 115
 116   if fcdb.option(fcdb.param.BACKEND) ~= 'sqlite' then
 117     log.error("'backend' in configuration file must be 'sqlite'")
 118     return fcdb.status.ERROR
 119   end
 120
 121   local table_user = fcdb.option(fcdb.param.TABLE_USER)
 122   local table_log = fcdb.option(fcdb.param.TABLE_LOG)
 123
 124   if not dbh then
 125     log.error("Missing database connection...")
 126     return fcdb.status.ERROR
 127   end
 128
 129   query = string.format([[
 130 CREATE TABLE %s (
 131   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 132   name VARCHAR(48) default NULL UNIQUE,
 133   password VARCHAR(32) default NULL,
 134   email VARCHAR default NULL,
 135   createtime INTEGER default NULL,
 136   accesstime INTEGER default NULL,
 137   address VARCHAR default NULL,
 138   createaddress VARCHAR default NULL,
 139   logincount INTEGER default '0'
 140 );
 141 ]], table_user)
 142   status, res = execute(query)
 143   if status == fcdb.status.TRUE then
 144     log.normal("Successfully created user table '%s'", table_user)
 145   else
 146     log.error("Error creating user table '%s'", table_user)
 147     return fcdb.status.ERROR
 148   end
 149
 150   query = string.format([[
 151 CREATE TABLE %s (
 152   id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 153   name VARCHAR(48) default NULL,
 154   logintime INTEGER default NULL,
 155   address VARCHAR default NULL,
 156   succeed TEXT default 'S'
 157 );]], table_log)
 158   status, res = execute(query)
 159   if status == fcdb.status.TRUE then
 160     log.normal("Successfully created log table '%s'", table_log)
 161   else
 162     log.error("Error creating log table '%s'", table_log)
 163     return fcdb.status.ERROR
 164   end
 165
 166   return fcdb.status.TRUE
 167
 168 end
 169
 170 -- **************************************************************************
 171 -- For MySQL, the following shapes of tables are expected
 172 -- (scripts/setup_auth_server.sh automates this):
 173 --
 174 -- CREATE TABLE fcdb_auth (
 175 --   id int(11) NOT NULL auto_increment,
 176 --   name varchar(48) default NULL,
 177 --   password varchar(32) default NULL,
 178 --   email varchar(128) default NULL,
 179 --   createtime int(11) default NULL,
 180 --   accesstime int(11) default NULL,
 181 --   address varchar(255) default NULL,
 182 --   createaddress varchar(255) default NULL,
 183 --   logincount int(11) default '0',
 184 --   PRIMARY KEY  (id),
 185 --   UNIQUE KEY name (name)
 186 -- ) TYPE=MyISAM;
 187 --
 188 -- CREATE TABLE fcdb_log (
 189 --   id int(11) NOT NULL auto_increment,
 190 --   name varchar(48) default NULL,
 191 --   logintime int(11) default NULL,
 192 --   address varchar(255) default NULL,
 193 --   succeed enum('S','F') default 'S',
 194 --   PRIMARY KEY  (id)
 195 -- ) TYPE=MyISAM;
 196 --
 197 -- N.B. if the tables are not of this format, then the select, insert,
 198 --      and update syntax in the following functions must be changed.
 199 -- **************************************************************************
 200
 201 -- **************************************************************************
 202 -- freeciv user auth functions
 203 -- **************************************************************************
 204
 205 -- load user data
 206 function user_load(conn)
 207   local status  -- return value (status of the request)
 208   local res     -- result handle
 209   local row     -- one row of the sql result
 210   local query   -- sql query
 211
 212   local fields = 'password'
 213
 214   local table_user = fcdb.option(fcdb.param.TABLE_USER)
 215   local table_log = fcdb.option(fcdb.param.TABLE_LOG)
 216
 217   if not dbh then
 218     log.error("Missing database connection...")
 219     return fcdb.status.ERROR
 220   end
 221
 222   local username = dbh:escape(auth.get_username(conn))
 223   local ipaddr = dbh:escape(auth.get_ipaddr(conn))
 224
 225   -- get the password for this user
 226   query = string.format([[SELECT %s FROM %s WHERE name = '%s']],
 227                         fields, table_user, username)
 228   status, res = execute(query)
 229   if status ~= fcdb.status.TRUE then
 230     return fcdb.status.ERROR
 231   end
 232
 233   row = res:fetch({}, 'a')
 234   if not row then
 235     -- No match
 236     res:close()
 237     return fcdb.status.FALSE
 238   end
 239
 240   -- There should be only one result
 241   if res:fetch() then
 242     log.error('[user_load]: multiple entries (%d) for user: %s',
 243               numrows, username)
 244     res:close()
 245     return fcdb.status.FALSE
 246   end
 247
 248   auth.set_password(conn, row.password)
 249
 250   res:close()
 251
 252   return fcdb.status.TRUE
 253 end
 254
 255 -- save a user to the database
 256 function user_save(conn)
 257   local status  -- return value (status of the request)
 258   local res     -- result handle
 259   local query   -- sql query
 260
 261   local table_user = fcdb.option(fcdb.param.TABLE_USER)
 262
 263   if not dbh then
 264     log.error("Missing database connection...")
 265     return fcdb.status.ERROR
 266   end
 267
 268   local username = dbh:escape(auth.get_username(conn))
 269   local password = dbh:escape(auth.get_password(conn))
 270   local ipaddr = auth.get_ipaddr(conn)
 271
 272   -- insert the user
 273   --local now = os.time()
 274   query = string.format([[INSERT INTO %s VALUES (NULL, '%s', '%s',
 275                           NULL, %s, %s, '%s', '%s', 0)]],
 276                         table_user, username, password,
 277                         sql_time(), sql_time(),
 278                         ipaddr, ipaddr)
 279   status, res = execute(query)
 280   if status ~= fcdb.status.TRUE then
 281     return fcdb.status.ERROR
 282   end
 283
 284   -- log this session
 285   return user_log(conn, true)
 286 end
 287
 288 -- log the session
 289 function user_log(conn, success)
 290   local status  -- return value (status of the request)
 291   local res     -- result handle
 292   local query   -- sql query
 293
 294   if not dbh then
 295     log.error("Missing database connection...")
 296     return fcdb.status.ERROR
 297   end
 298
 299   local table_user = fcdb.option(fcdb.param.TABLE_USER)
 300   local table_log = fcdb.option(fcdb.param.TABLE_LOG)
 301
 302   local username = dbh:escape(auth.get_username(conn))
 303   local ipaddr = auth.get_ipaddr(conn)
 304   local success_str = success and 'S' or 'F'
 305
 306   -- update user data
 307   --local now = os.time()
 308   query = string.format([[UPDATE %s SET accesstime = %s, address = '%s',
 309                           logincount = logincount + 1
 310                           WHERE name = '%s']], table_user, sql_time(),
 311                           ipaddr, username)
 312   status, res = execute(query)
 313   if status ~= fcdb.status.TRUE then
 314     return fcdb.status.ERROR
 315   end
 316
 317   -- insert the log row for this user
 318   query = string.format([[INSERT INTO %s (name, logintime, address, succeed)
 319                           VALUES ('%s', %s, '%s', '%s')]],
 320                         table_log, username, sql_time(), ipaddr, success_str)
 321   status, res = execute(query)
 322   if status ~= fcdb.status.TRUE then
 323     return fcdb.status.ERROR
 324   end
 325
 326   return fcdb.status.TRUE
 327 end
 328
 329 -- **************************************************************************
 330 -- freeciv database entry functions
 331 -- **************************************************************************
 332
 333 -- test and initialise the database connection
 334 function database_init()
 335   local status -- return value (status of the request)
 336
 337   local backend = fcdb.option(fcdb.param.BACKEND)
 338   if backend == 'mysql' then
 339     log.verbose('Opening MySQL database connection...')
 340     status = mysql_connect()
 341   elseif backend == 'sqlite' then
 342     log.verbose('Opening SQLite database connection...')
 343     status = sqlite_connect()
 344   else
 345     log.error('Database backend \'%s\' not supported by database.lua', backend)
 346     return fcdb.status.ERROR
 347   end
 348
 349   if status == fcdb.status.TRUE then
 350     log.verbose('Database connection successful.')
 351   end
 352
 353   return status
 354 end
 355
 356 -- free the database connection
 357 function database_free()
 358   log.verbose('Closing database connection...')
 359
 360   if dbh then
 361     dbh:close()
 362   end
 363
 364   return fcdb.status.TRUE;
 365 end