public void AddFavorite(User user, Post post)
{
if (dbConnection == null)
throw new InvalidOperationException("Database Connection is null!");
using (var cmd = dbConnection.CreateCommand())
{
cmd.CommandText = "INSERT OR IGNORE INTO favorites(id, userid, parent_id, has_children, rating, uploader, uploader_id, md5, image_height, image_width, file_size, file_ext, tag_string_artist, tag_string_character, tag_string_copyright, tag_string_general, has_large, add_time) " +
"VALUES(@id, @userid, @parent_id, @has_children, @rating, @uploader, @uploader_id, @md5, @image_height, @image_width, @file_size, @file_ext, @tag_string_artist, @tag_string_character, @tag_string_copyright, @tag_string_general, @has_large, @add_time);SELECT last_insert_rowid();";
cmd.Parameters.Add("@id", post.ID);
cmd.Parameters.Add("@userid", user.ID);
cmd.Parameters.Add("@parent_id", post.ParentIdString);
cmd.Parameters.Add("@has_children", post.HasChildren ? 1 : 0);
cmd.Parameters.Add("@rating", post.RatingString);
cmd.Parameters.Add("@uploader", post.Author);
cmd.Parameters.Add("@uploader_id", post.AuthorID);
cmd.Parameters.Add("@md5", post.MD5);
cmd.Parameters.Add("@image_height", post.Height);
cmd.Parameters.Add("@image_width", post.Width);
cmd.Parameters.Add("@file_size", post.FileSize);
cmd.Parameters.Add("@file_ext", post.FileExtension);
cmd.Parameters.Add("@tag_string_artist", post.ArtistTagsString);
cmd.Parameters.Add("@tag_string_character", post.CharacterTagsString);
cmd.Parameters.Add("@tag_string_copyright", post.CopyrightTagsString);
cmd.Parameters.Add("@tag_string_general", post.GeneralTagsString);
cmd.Parameters.Add("@has_large", post.HasLarge ? 1 : 0);
cmd.Parameters.Add("@add_time", DateTime.Now.Ticks / 10000);
cmd.ExecuteScalar();
}
}
public void RemoveFavorite(User user, Post post)
{
if (dbConnection == null)
throw new InvalidOperationException("Database Connection is null!");
using (var cmd = dbConnection.CreateCommand())
{
cmd.CommandText = "DELETE FROM favorites WHERE userid = @userid AND id = @id";
cmd.Parameters.Add("@id", post.ID);
cmd.Parameters.Add("@userid", user.ID);
cmd.ExecuteScalar();
}
}
public void RemoveUser(User user)
{
if (dbConnection == null)
throw new InvalidOperationException("Database Connection is null!");
using (var cmd = dbConnection.CreateCommand())
{
cmd.CommandText = "DELETE FROM user WHERE id = @id";
cmd.Parameters.Add("@id", user.ID);
cmd.ExecuteScalar();
}
}
public Boolean HasUser(User user)
{
if (dbConnection == null)
throw new InvalidOperationException("Database Connection is null!");
Int32 result;
using (var cmd = dbConnection.CreateCommand())
{
cmd.CommandText = "SELECT COUNT(id) FROM user WHERE id=@id";
cmd.Parameters.Add("@id", user.ID);
using (var reader = cmd.ExecuteReader())
{
reader.Read();
result = reader.GetInt32(0);
}
}
return result > 0;
}
public UserConfig GetUserConfig(User user)
{
if (dbConnection == null)
throw new InvalidOperationException("Database Connection is null!");
UserConfig config = new UserConfig();
using (var cmd = dbConnection.CreateCommand())
{
cmd.CommandText = "SELECT * FROM user WHERE id=@id";
cmd.Parameters.Add("@id", user.ID);
using (var reader = cmd.ExecuteReader())
{
reader.Read();
config.ID = reader.GetInt32(0);
config.Username = reader.GetString(1);
config.Rating = reader.GetString(2);
config.Feed = reader.GetString(3);
}
}
return config;
}
public Post[] GetFavorites(User user, Int64 lastAddTime)
{
if (dbConnection == null)
throw new InvalidOperationException("Database Connection is null!");
List<Post> result = new List<Post>();
using (var cmd = dbConnection.CreateCommand())
{
cmd.CommandText = "SELECT * FROM favorites WHERE userid = @userid AND add_time < @last_time ORDER BY add_time DESC LIMIT @page_size";
cmd.Parameters.Add("@userid", user.ID);
cmd.Parameters.Add("@last_time", lastAddTime);
cmd.Parameters.Add("@page_size", PostPageSize);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Post p = new Post();
p.ID = reader.GetInt32(0);
if (reader.IsDBNull(2)) p.ParentIdString = null;
else p.ParentIdString = reader.GetString(2);
p.HasChildren = ReadNullableInt32(reader, 3, 0) != 0;
p.RatingString = ReadNullableString(reader, 4, "s");
p.Author = ReadNullableString(reader, 5, "Anonymous");
p.AuthorID = ReadNullableInt32(reader, 6, 0);
p.MD5 = ReadNullableString(reader, 7, "");
p.Height = ReadNullableInt32(reader, 8, 0);
p.Width = ReadNullableInt32(reader, 9, 0);
p.FileSize = ReadNullableInt32(reader, 10, 0);
p.FileExtension = ReadNullableString(reader, 11, ".jpg");
p.ArtistTagsString = ReadNullableString(reader, 12, "");
p.CharacterTagsString = ReadNullableString(reader, 13, "");
p.CopyrightTagsString = ReadNullableString(reader, 14, "");
p.GeneralTagsString = ReadNullableString(reader, 15, "");
p.HasLarge = ReadNullableInt32(reader, 16, 0) != 0;
p.LastAddTime = reader.GetInt64(17);
result.Add(p);
}
}
}
return result.ToArray();
}
public void AddUser(User user, String rating, String feed)
{
if (dbConnection == null)
throw new InvalidOperationException("Database Connection is null!");
using (var cmd = dbConnection.CreateCommand())
{
cmd.CommandText = "INSERT OR REPLACE INTO user(id, name, rating, feed) VALUES(@id, @name, @rating, @feed);SELECT last_insert_rowid();";
cmd.Parameters.Add("@id", user.ID);
cmd.Parameters.Add("@name", user.Name);
cmd.Parameters.Add("@rating", rating);
cmd.Parameters.Add("@feed", feed);
cmd.ExecuteScalar();
}
}
/// <summary>
/// Initializes the session on a background thread
/// and
/// </summary>
/// <param name="callback"></param>
public void InitializeAsync(Action callback)
{
BackgroundWorker bw = new BackgroundWorker();
bw.DoWork += (@s, e) =>
{
Logging.D("Session.InitializeAsync(): Starting Session Initialization");
// Create the client
Client = new Danbooru2Client(Constants.SiteHostname, Credentials);
// Get User Profile
User = Client.GetUser(Credentials.Username);
// Load User Settings
if (!Database.Instance.HasUser(User))
Database.Instance.AddUser(User, "s", "new");
Database.UserConfig config = Database.Instance.GetUserConfig(User);
MaxRating = RatingEx.Parse(config.Rating);
Logging.D("Session.InitializeAsync(): Loading posts");
// Load Template generator
PostViewerTemplate = new PostViewerTemplate("Assets/template.html");
// Load first 3 pages
NewPosts = new PostTupleCollection();
//PostTuple[] tuples = GetMoreNewPosts(3);
Post[] newPosts = Client.GetPosts(1, 3, ResolveQueryString(""));
NewPosts.AddRange(newPosts);
};
bw.RunWorkerCompleted += (@s, e) => callback();
bw.RunWorkerAsync();
}
