Logo Search packages:      
Sourcecode: galaxium version File versions  Download package

SQLiteConversationLog.cs

/*
 * Galaxium Messenger
 * Copyright (C) 2008 Paul Burton <paulburton89@gmail.com>
 * 
 * License: GNU General Public License (GPL)
 *
 * This program is free software; you can redistribute it and/or modify it
 * under the terms of the GNU General Public License as published by the Free
 * Software Foundation; either version 2 of the License, or (at your option)
 * any later version.
 *
 * This program is distributed in the hope that it will be useful, but
 * WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
 * or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General Public License
 * for more details.
 *
 * You should have received a copy of the GNU General Public License along
 * with this program; if not, write to the Free Software Foundation, Inc.,
 * 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 */


using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;

using Mono.Data.Sqlite;

using Anculus.Core;

using Galaxium.Core;

namespace Galaxium.Protocol
{
      public class SQLiteConversationLog : IConversationLog
      {
            enum ItemType : byte
            {
                  Message = 0,
                  Event = 1
            }
            
            const short _currentVersion = 1;
            
            string _directory;
            string _logName;
            string _dbFile;
            bool _keepAlive;
            
            SqliteConnection _dbConn;
            
            SqliteCommand _logMessageCommand;
            SqliteCommand _logEventCommand;
            
            object _lock = new object ();
            
            public bool KeepAlive
            {
                  get { return _keepAlive; }
                  set { _keepAlive = value; }
            }
            
            int DBVersion
            {
                  get
                  {
                        int version = -1;
                        
                        lock (_lock)
                        {
                              try
                              {
                                    OpenDB ();
                                    version = DBVersionNoOpenClose;
                              }
                              catch (SqliteException ex)
                              {
                                    Log.Error ("Error reasing DBVersion");
                              }
                              finally
                              {
                                    CloseDB ();
                              }
                        }
                        
                        return version;
                  }
            }
            
            private int DBVersionNoOpenClose
            {
                  get
                  {
                        int version = -1;
                        
                        using (SqliteCommand cmd = _dbConn.CreateCommand ())
                        {
                              cmd.CommandText = "SELECT version FROM dbinfo";
                              IDataReader reader = cmd.ExecuteReader();
                              while (reader.Read ())
                                    version = reader.GetInt32 (0);
                              
                              reader.Close ();
                        }
                        
                        return version;
                  }
            }
            
            public SQLiteConversationLog (string directory, string logName, bool keepAlive)
            {
                  _directory = directory;
                  _logName = logName;
                  _keepAlive = keepAlive;
                  
                  _dbFile = Path.Combine (directory, logName + ".db");
                  
                  _logMessageCommand = new SqliteCommand (string.Format ("INSERT INTO items (timestamp, type, uid, displayname, data) VALUES(@timestamp, {0}, @uid, @display, @data);", (int)ItemType.Message));
                  _logMessageCommand.Parameters.Add ("@timestamp", DbType.Int64);
                  _logMessageCommand.Parameters.Add ("@uid", DbType.String);
                  _logMessageCommand.Parameters.Add ("@display", DbType.String);
                  _logMessageCommand.Parameters.Add ("@data", DbType.String);
                  
                  _logEventCommand = new SqliteCommand (string.Format ("INSERT INTO items (timestamp, type, data) VALUES(@timestamp, {0}, @data);", (int)ItemType.Event));
                  _logEventCommand.Parameters.Add ("@timestamp", DbType.Int64);
                  _logEventCommand.Parameters.Add ("@data", DbType.String);
                  
                  lock (_lock)
                  {
                        try
                        {
                              OpenDB ();
                              CreateDBTables ();
                              
                              int dbVersion = DBVersionNoOpenClose;
                              
                              if (dbVersion < _currentVersion)
                                    UpdateDB ();
                              else if (dbVersion > _currentVersion)
                                    Log.Warn ("Database is newer than this version of Galaxium knows how to handle");
                        }
                        catch (SqliteException ex)
                        {
                              Log.Error (ex, "Error finding database version");
                        }
                        finally
                        {
                              CloseDB ();
                        }
                  }
            }
            
            void OpenDB ()
            {
                  if (_dbConn != null)
                        return;
                  
                  try
                  {
                        //Log.Debug ("Opening " + _dbFile);
                        
                        _dbConn = new SqliteConnection ("Data Source=" + _dbFile);
                        _dbConn.Open ();
                        
                        _logMessageCommand.Connection = _dbConn;
                        _logEventCommand.Connection = _dbConn;
                  }
                  catch (SqliteException ex)
                  {
                        Log.Error (ex, "Unable to open database ({0})", ex.ErrorCode);
                        throw ex;
                  }
            }
            
            void CloseDB ()
            {
                  if (_dbConn == null)
                        return;
                  
                  try
                  {
                        //Log.Debug ("Closing {0}", _dbFile);
                        
                        _dbConn.Close ();
                        _dbConn = null;
                  }
                  catch (SqliteException ex)
                  {
                        Log.Error (ex, "Unable to close database");
                  }
            }
            
            void CreateDBTables ()
            {
                  using (SqliteCommand cmd = _dbConn.CreateCommand ())
                  {
                        cmd.CommandText = "CREATE TABLE IF NOT EXISTS items (timestamp int64, type byte, uid text, displayname text, data text);";
                        cmd.ExecuteNonQuery ();
                  }
                  
                  using (SqliteCommand cmd = _dbConn.CreateCommand ())
                  {
                        cmd.CommandText = "CREATE TABLE IF NOT EXISTS dbinfo (version int16);";
                        cmd.ExecuteNonQuery ();
                  }
                  
                  if (DBVersionNoOpenClose == -1)
                  {
                        using (SqliteCommand cmd = _dbConn.CreateCommand ())
                        {
                              cmd.CommandText = string.Format ("INSERT INTO dbinfo VALUES({0});", _currentVersion);
                              cmd.ExecuteNonQuery ();
                        }
                  }
            }
            
            void UpdateDB ()
            {
                  // This will update the database if we change it's structure
            }
            
            public void LogMessage (ITextMessage msg)
            {
                  lock (_lock)
                  {
                        try
                        {
                              OpenDB ();
                              
                              _logMessageCommand.Parameters["@timestamp"].Value = msg.TimeStamp.Ticks;
                              _logMessageCommand.Parameters["@uid"].Value = msg.Source.UniqueIdentifier;
                              _logMessageCommand.Parameters["@display"].Value = msg.Source.DisplayName;
                              _logMessageCommand.Parameters["@data"].Value = msg.GetText ();
                              _logMessageCommand.ExecuteNonQuery ();
                        }
                        catch (Exception ex)
                        {
                              Log.Error (ex, "Error logging message");
                        }
                        finally
                        {
                              CloseDB ();
                        }
                  }
            }
            
            public void LogEvent (DateTime timestamp, string description)
            {
                  lock (_lock)
                  {
                        try
                        {
                              OpenDB ();
                              
                              _logEventCommand.Parameters["@timestamp"].Value = timestamp.Ticks;
                              _logEventCommand.Parameters["@data"].Value = description;
                              _logEventCommand.ExecuteNonQuery ();
                        }
                        catch (Exception ex)
                        {
                              Log.Error (ex, "Error logging event");
                        }
                        finally
                        {
                              CloseDB ();
                        }
                  }
            }
            
            public void Close ()
            {
                  lock (_lock)
                        CloseDB ();
            }
            
            public IEnumerable<ConversationLogEntry> GetNLastEntries (int n)
            {
                  List<ConversationLogEntry> entries = new List<ConversationLogEntry> ();
                  
                  lock (_lock)
                  {
                        try
                        {
                              OpenDB ();
                              
                              using (SqliteCommand cmd = _dbConn.CreateCommand ())
                              {
                                    cmd.CommandText = string.Format ("SELECT timestamp, type, uid, displayname, data FROM items ORDER BY timestamp DESC LIMIT {0}", n);
                                    IDataReader reader = cmd.ExecuteReader();
                                    
                                    while (reader.Read ())
                                    {
                                          long timestamp = reader.GetInt64 (0);
                                          ItemType type = (ItemType)reader.GetByte (1);
                                          
                                          if (type == ItemType.Message)
                                          {
                                                string uid = reader.GetString (2);
                                                string displayName = reader.GetString (3);
                                                string data = reader.GetString (4);
                                                
                                                ConversationLogEntry entry = new ConversationLogEntry (this, 0, 0, new DateTime (timestamp), uid, displayName, data);
                                                entries.Insert (0, entry);
                                          }
                                          else
                                          {
                                                string data = reader.GetString (4);
                                                
                                                ConversationLogEntry entry = new ConversationLogEntry (this, 0, 0, new DateTime (timestamp), data);
                                                entries.Insert (0, entry);
                                          }
                                    }
                                    
                                    reader.Close ();
                              }
                        }
                        catch (SqliteException ex)
                        {
                              Log.Error (ex, "Error reading log entries");
                        }
                        finally
                        {
                              CloseDB ();
                        }
                  }
                  
                  return entries;
            }

            public IEnumerable<ConversationLogEntry> SearchAll (string keyword)
            {
                  List<ConversationLogEntry> entries = new List<ConversationLogEntry> ();
                  
                  lock (_lock)
                  {
                        try
                        {
                              OpenDB ();
                              
                              using (SqliteCommand cmd = _dbConn.CreateCommand ())
                              {
                                    cmd.CommandText = "SELECT timestamp, type, uid, displayname, data FROM items ORDER BY timestamp ASC WHERE data LIKE @search";
                                    cmd.Parameters.Add (new SqliteParameter ("@search", keyword));
                                    IDataReader reader = cmd.ExecuteReader();
                                    
                                    while (reader.Read ())
                                    {
                                          long timestamp = reader.GetInt64 (0);
                                          ItemType type = (ItemType)reader.GetByte (1);
                                          string uid = reader.GetString (2);
                                          string displayName = reader.GetString (3);
                                          string data = reader.GetString (4);
                                          
                                          ConversationLogEntry entry = new ConversationLogEntry (this, 0, 0, new DateTime (timestamp), uid, displayName, data);
                                          entries.Add (entry);
                                    }
                                    
                                    reader.Close ();
                              }
                        }
                        catch (SqliteException ex)
                        {
                              Log.Error (ex, "Error reading log entries");
                        }
                        finally
                        {
                              CloseDB ();
                        }
                  }
                  
                  return entries;
            }
            
            public ConversationLogEntry Search (string keyword)
            {
                  ConversationLogEntry entry = null;
                  
                  lock (_lock)
                  {
                        try
                        {
                              OpenDB ();
                              
                              using (SqliteCommand cmd = _dbConn.CreateCommand ())
                              {
                                    cmd.CommandText = "SELECT timestamp, type, uid, displayname, data FROM items ORDER BY timestamp DESC WHERE data LIKE @search LIMIT 1";
                                    cmd.Parameters.Add (new SqliteParameter ("@search", keyword));
                                    IDataReader reader = cmd.ExecuteReader();
                                    
                                    while (reader.Read ())
                                    {
                                          long timestamp = reader.GetInt64 (0);
                                          ItemType type = (ItemType)reader.GetByte (1);
                                          string uid = reader.GetString (2);
                                          string displayName = reader.GetString (3);
                                          string data = reader.GetString (4);
                                          
                                          entry = new ConversationLogEntry (this, 0, 0, new DateTime (timestamp), uid, displayName, data);
                                          break;
                                    }
                                    
                                    reader.Close ();
                              }
                        }
                        catch (SqliteException ex)
                        {
                              Log.Error (ex, "Error reading log entries");
                        }
                        finally
                        {
                              CloseDB ();
                        }
                  }
                  
                  return entry;
            }
            
            public ConversationLogEntry SearchNext (string keyword, ConversationLogEntry entry)
            {
                  ConversationLogEntry newEntry = null;
                  
                  lock (_lock)
                  {
                        try
                        {
                              OpenDB ();
                              
                              using (SqliteCommand cmd = _dbConn.CreateCommand ())
                              {
                                    cmd.CommandText = string.Format ("SELECT timestamp, type, uid, displayname, data FROM items ORDER BY timestamp DESC WHERE timestamp < {0} AND data LIKE @search LIMIT 1", entry.TimeStamp.Ticks);
                                    cmd.Parameters.Add (new SqliteParameter ("@search", keyword));
                                    IDataReader reader = cmd.ExecuteReader();
                                    
                                    while (reader.Read ())
                                    {
                                          long timestamp = reader.GetInt64 (0);
                                          ItemType type = (ItemType)reader.GetByte (1);
                                          string uid = reader.GetString (2);
                                          string displayName = reader.GetString (3);
                                          string data = reader.GetString (4);
                                          
                                          newEntry = new ConversationLogEntry (this, 0, 0, new DateTime (timestamp), uid, displayName, data);
                                          break;
                                    }
                                    
                                    reader.Close ();
                              }
                        }
                        catch (SqliteException ex)
                        {
                              Log.Error (ex, "Error reading log entries");
                        }
                        finally
                        {
                              CloseDB ();
                        }
                  }
                  
                  return newEntry;
            }
      }
}

Generated by  Doxygen 1.6.0   Back to index