/// <summary>
/// Marks an emoticon last usage using the current date.
/// </summary>
/// <param name="guid"></param>
public void UseEmoticon(string guid)
{
using (DbCommand command = conn.CreateCommand())
{
command.CommandText = "UPDATE Emoticon SET lastUsed=@lastUsed WHERE guid=@guid";
command.Parameters.Add(SQLiteAdapter.NewParameter("guid", guid));
command.Parameters.Add(SQLiteAdapter.NewParameter("lastUsed", DateTime.Now.Ticks));
command.ExecuteNonQuery();
}
}
/// <summary>
/// Updates the database with the passed emoticon or creates a new one.
/// A new emoticon is created when the emoticon GUID is null.
/// </summary>
/// <param name="emot"></param>
public void UpdateEmoticon(Emoticon emot)
{
using (DbCommand command = conn.CreateCommand())
{
if (emot.guid == null)
{
emot.guid = Guid.NewGuid().ToString();
emot.lastUsed = 0;
command.CommandText = "INSERT INTO Emoticon (guid, name, category, lastUsed, type, data) VALUES (@guid, @name, @category, @lastUsed, @type, @data);";
command.Prepare();
}
else
{
command.CommandText = "UPDATE Emoticon SET name=@name, category=@category, lastUsed=@lastUsed, type=@type, data=@data WHERE guid=@guid;";
command.Prepare();
}
command.Parameters.Add(SQLiteAdapter.NewParameter("guid", emot.guid));
command.Parameters.Add(SQLiteAdapter.NewParameter("name", emot.name));
command.Parameters.Add(SQLiteAdapter.NewParameter("category", emot.category));
command.Parameters.Add(SQLiteAdapter.NewParameter("type", emot.type));
command.Parameters.Add(SQLiteAdapter.NewParameter("data", emot.data));
command.Parameters.Add(SQLiteAdapter.NewParameter("parentGuid", emot.parentGuid));
command.Parameters.Add(SQLiteAdapter.NewParameter("keyboardRegex", emot.keyboardRegex));
command.Parameters.Add(SQLiteAdapter.NewParameter("flags", emot.flags));
string[] keyboardEquivalent = emot.keyboardEquivalent;
if (keyboardEquivalent != null && keyboardEquivalent.Length > 0)
{
StringBuilder strBuilder = new StringBuilder(keyboardEquivalent[0].Replace(":", "::").Replace(";", ":,"));
for (int i = 1; i < keyboardEquivalent.Length; i++)
{
strBuilder.Append(';').Append(keyboardEquivalent[i].Replace(":", "::").Replace(";", ":,"));
}
command.Parameters.Add(SQLiteAdapter.NewParameter("keyboardEquivalent", strBuilder.ToString()));
}
else
{
command.Parameters.Add(SQLiteAdapter.NewParameter("keyboardEquivalent", ""));
}
// Only save lastUsed when we actually have used it. Zero means unused
if (emot.lastUsed > 0)
{
command.Parameters.Add(SQLiteAdapter.NewParameter("lastUsed", emot.lastUsed));
}
else
{
command.Parameters.Add(SQLiteAdapter.NewParameter("lastUsed", DBNull.Value));
}
command.ExecuteNonQuery();
}
}
/// <summary>
/// Queries emoticons from the database, using offset & count.
/// The parameters are inserted as query like so: 'SELECT * FROM Emoticon [query] LIMIT [count] OFFSET [offset]'.
/// </summary>
/// <param name="query">A query string, something along the lines of 'WHERE something ORDER BY else ASC'</param>
/// <param name="filter">If non-null, this adds a parameter value for 'filter'. This parameter has to be in the query for this to work.</param>
/// <param name="offset">Zero-based index to offset the results</param>
/// <param name="count">Amount of emoticons to query</param>
/// <returns></returns>
public List <Emoticon> PartialQueryEmoticons(string query, string filter, int offset, int count)
{
using (DbCommand command = conn.CreateCommand())
{
command.CommandText = string.Format("SELECT * FROM Emoticon {0} LIMIT {1} OFFSET {2}", query, count, offset);
if (filter != null)
{
command.Parameters.Add(SQLiteAdapter.NewParameter("filter", '%' + filter + '%'));
}
using (DbDataReader reader = command.ExecuteReader())
{
return(ParseEmoticonResult(reader));
}
}
}
private int CountQuery(string table, string query, string filter)
{
using (DbCommand command = conn.CreateCommand())
{
string commandText = "SELECT COUNT(guid) FROM " + table + " " + query;
// Strip the ORDER BY clause from the query, as count() does not support that.
int pos = commandText.IndexOf("ORDER BY", StringComparison.InvariantCultureIgnoreCase);
if (pos > -1)
{
commandText = commandText.Substring(0, pos);
}
command.CommandText = commandText;
if (filter != null)
{
command.Parameters.Add(SQLiteAdapter.NewParameter("filter", '%' + filter + '%'));
}
return((int)(long)command.ExecuteScalar());
}
}
public static DbConnection openSQLite(string file)
{
return(SQLiteAdapter.Connect(file));
}
