private static List <Domain.Objects.Scrobble> GetScrobblesFromOldTrack(string md5, int trackId)
{
List <Domain.Objects.Scrobble> scrobbles = new List <Domain.Objects.Scrobble>();
using (Microsoft.Data.Sqlite.SqliteConnection conn = new Microsoft.Data.Sqlite.SqliteConnection(string.Format("Data Source={0}", oldDatabasePath)))
{
conn.Open();
using (var cmd = new Microsoft.Data.Sqlite.SqliteCommand("SELECT * FROM Scrobble WHERE MD5=@MD5", conn))
{
cmd.Parameters.AddWithValue("MD5", md5);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
DateTime playedOn = Convert.ToDateTime(reader["PlayedOn"]);
scrobbles.Add(new Domain.Objects.Scrobble()
{
On = playedOn,
TrackId = trackId,
PlayedToEnd = true // old scrobbles were only made when played to end
});
}
}
}
}
return(scrobbles);
}
public static List <String> GetChannels()
{
List <String> entries = new List <string>();
string dbpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "sqliteSample.db");
using (Microsoft.Data.Sqlite.SqliteConnection db =
new Microsoft.Data.Sqlite.SqliteConnection($"Filename={dbpath}"))
{
db.Open();
Microsoft.Data.Sqlite.SqliteCommand selectCommand = new Microsoft.Data.Sqlite.SqliteCommand
("SELECT name from channels", db);
Microsoft.Data.Sqlite.SqliteDataReader query = selectCommand.ExecuteReader();
while (query.Read())
{
entries.Add(query.GetString(0));
}
/*foreach(var entry in entries)
* {
* System.Diagnostics.Debug.WriteLine("------------------------------------"+entry);
* }
* //System.Diagnostics.Debug.WriteLine("++++++++++++++++++++++" + entries[2]);*/
db.Close();
}
return(entries);
}
public async static void ResetChannels()
{
await Windows.Storage.ApplicationData.Current.LocalFolder.CreateFileAsync("sqliteSample.db", Windows.Storage.CreationCollisionOption.OpenIfExists);
string dbpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "sqliteSample.db");
using (Microsoft.Data.Sqlite.SqliteConnection db =
new Microsoft.Data.Sqlite.SqliteConnection($"Filename={dbpath}"))
{
db.Open();
String tableCommand = "DROP TABLE IF EXISTS channels";
Microsoft.Data.Sqlite.SqliteCommand dropTable = new Microsoft.Data.Sqlite.SqliteCommand(tableCommand, db);
dropTable.ExecuteReader();
String tableCommand2 = "CREATE TABLE IF NOT " +
"EXISTS channels (name TEXT UNIQUE)";
//System.Diagnostics.Debug.WriteLine(tableCommand);
Microsoft.Data.Sqlite.SqliteCommand createTable2 = new Microsoft.Data.Sqlite.SqliteCommand(tableCommand2, db);
createTable2.ExecuteReader();
}
AddChannel("General");
}
public static void Post(string channel, string title, string content)
{
string dbpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "sqliteSample.db");
using (Microsoft.Data.Sqlite.SqliteConnection db =
new Microsoft.Data.Sqlite.SqliteConnection($"Filename={dbpath}"))
{
db.Open();
Microsoft.Data.Sqlite.SqliteCommand insertCommand = new Microsoft.Data.Sqlite.SqliteCommand();
insertCommand.Connection = db;
// Use parameterized query to prevent SQL injection attacks
insertCommand.CommandText = "INSERT INTO @channel VALUES (@title, @content);";
insertCommand.Parameters.AddWithValue("@channel", channel);
insertCommand.Parameters.AddWithValue("@title", title);
insertCommand.Parameters.AddWithValue("@content", content);
//System.Diagnostics.Debug.WriteLine("-------------------------------------" + insertCommand.CommandText.ToString());
//System.Diagnostics.Debug.WriteLine("-------------------------------------" + insertCommand.Parameters[0].Value.ToString());
// System.Diagnostics.Debug.WriteLine("-------------------------------------" + insertCommand.Parameters[1].Value.ToString());
//System.Diagnostics.Debug.WriteLine("-------------------------------------" + insertCommand.Parameters[2].Value.ToString());
title = title.Trim();
content = content.Trim();
String insertCommand2 = "INSERT INTO " + channel + " VALUES ('" + title + "', '" + content + "');";
Microsoft.Data.Sqlite.SqliteCommand selection = new Microsoft.Data.Sqlite.SqliteCommand(insertCommand2, db);
System.Diagnostics.Debug.WriteLine("-------------------------------------" + insertCommand2);
selection.ExecuteReader();
//insertCommand.ExecuteReader();
db.Close();
}
}
public static List <post> GetPosts(string channel)
{
List <post> posts = new List <post>();
List <string> contents = new List <string>();
List <string> titles = new List <string>();
string dbpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "sqliteSample.db");
using (Microsoft.Data.Sqlite.SqliteConnection db =
new Microsoft.Data.Sqlite.SqliteConnection($"Filename={dbpath}"))
{
db.Open();
Microsoft.Data.Sqlite.SqliteCommand selectCommand = new Microsoft.Data.Sqlite.SqliteCommand
("SELECT title from General", db);
//selectCommand.Parameters.AddWithValue("@channel", channel);
String selCommand = "SELECT title from " + channel;
Microsoft.Data.Sqlite.SqliteCommand selection = new Microsoft.Data.Sqlite.SqliteCommand(selCommand, db);
System.Diagnostics.Debug.WriteLine("---------------------" + channel);
Microsoft.Data.Sqlite.SqliteDataReader query = selection.ExecuteReader();
while (query.Read())
{
titles.Add(query.GetString(0));
}
System.Diagnostics.Debug.WriteLine("++++++++++++++++++++++" + titles.Count());
System.Diagnostics.Debug.WriteLine("========================" + titles[0]);
Microsoft.Data.Sqlite.SqliteCommand selectCommand2 = new Microsoft.Data.Sqlite.SqliteCommand
("SELECT content from General", db);
//selectCommand2.Parameters.AddWithValue("@channel", channel);
String selCommand2 = "SELECT content from " + channel;
Microsoft.Data.Sqlite.SqliteCommand selection2 = new Microsoft.Data.Sqlite.SqliteCommand(selCommand2, db);
Microsoft.Data.Sqlite.SqliteDataReader query2 = selection2.ExecuteReader();
while (query2.Read())
{
contents.Add(query2.GetString(0));
}
db.Close();
}
System.Diagnostics.Debug.WriteLine("++++++++++++++++++++++" + titles.Count());
int k = 0;
for (int i = 0; i < titles.Count(); i++)
{
post p = new post();
p.content = contents[i];
p.title = titles[i];
posts.Add(p);
}
return(posts);
}
private static List <int> ReadResearcherIDs(Microsoft.Data.Sqlite.SqliteCommand command)
{
List <int> Ids = new List <int>();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
Ids.Add(reader.GetInt32(0));
}
}
return(Ids);
}
private static void MigrateOldMusicCache()
{
// configure to use sqlite
Dapper.SimpleCRUD.SetDialect(Dapper.SimpleCRUD.Dialect.SQLite);
using (DAL.DALManager mgr = new DAL.DALManager(newDatabasePath))
{
using (Microsoft.Data.Sqlite.SqliteConnection conn = new Microsoft.Data.Sqlite.SqliteConnection(string.Format("Data Source={0}", oldDatabasePath)))
{
conn.Open();
var cmd = new Microsoft.Data.Sqlite.SqliteCommand("SELECT * FROM MusicCache", conn);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
string filename = reader["Filename"] + "";
string md5 = reader["MD5"] + "";
int played = Convert.ToInt32(reader["Played"]);
int playedToEnd = Convert.ToInt32(reader["PlayedToEnd"]);
DateTime lastPlayed = reader["LastPlayed"] == DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(reader["LastPlayed"]);
LikeStatus likeStatus = (LikeStatus)Convert.ToInt32(reader["LikeStatus"]);
if (filename.StartsWith("/media/hdd/Dwight/Music/"))
{
filename = filename.Substring("/media/hdd/Dwight/Music/".Length);
}
var track = mgr.GetTrackByFilename(filename);
if (track != null)
{
track.NrPlayed = played;
track.NrPlayedToEnd = playedToEnd;
track.LastPlayed = lastPlayed;
track.LikeStatus = likeStatus;
mgr.Set(track);
var oldScrobbles = GetScrobblesFromOldTrack(md5, track.Id);
mgr.SetAll(oldScrobbles);
}
else
{
Console.Error.WriteLine("Track with relative path " + filename + " not found");
}
}
}
}
}
private List <ColumnMetadata <DbType> > GetColumns(SQLiteObjectName tableName, bool isTable)
{
/* NOTE: Should be safe since GetTableOrViewInternal returns null after querying the table name with a
** prepared statement, thus proving that the table name exists.
*/
var hasPrimarykey = false;
var columnSql = $"PRAGMA table_info('{tableName.Name}')";
var columns = new List <ColumnMetadata <DbType> >();
using (var con = new SQLiteConnection(m_ConnectionBuilder.ConnectionString))
{
con.Open();
using (var cmd = new SQLiteCommand(columnSql, con))
{
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var name = reader.GetString(reader.GetOrdinal("name"));
var typeName = reader.GetString(reader.GetOrdinal("type"));
var isPrimaryKey = reader.GetInt32(reader.GetOrdinal("pk")) != 0 ? true : false;
var isnNullable = !reader.GetBoolean(reader.GetOrdinal("notnull"));
hasPrimarykey = hasPrimarykey || isPrimaryKey;
columns.Add(new ColumnMetadata <DbType>(name, false, isPrimaryKey, false, typeName, null, "[" + name + "]", isnNullable, null, null, null, null));
}
}
}
}
//Tables wihtout a primary key always have a ROWID.
//We can't tell if other tables have one or not.
if (isTable && !hasPrimarykey)
{
columns.Add(new ColumnMetadata <DbType>("ROWID", true, false, true, "INTEGER", null, "[ROWID]", false, null, null, null, null));
}
return(columns);
}
public static void AddChannel(string name)
{
string dbpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "sqliteSample.db");
using (Microsoft.Data.Sqlite.SqliteConnection db =
new Microsoft.Data.Sqlite.SqliteConnection($"Filename={dbpath}"))
{
db.Open();
Microsoft.Data.Sqlite.SqliteCommand insertCommand = new Microsoft.Data.Sqlite.SqliteCommand();
insertCommand.Connection = db;
// Use parameterized query to prevent SQL injection attacks
insertCommand.CommandText = "INSERT INTO channels VALUES (@name);";
insertCommand.Parameters.AddWithValue("@name", name);
System.Diagnostics.Debug.WriteLine("-------------------------------------" + insertCommand.CommandText.ToString());
try
{
insertCommand.ExecuteReader();
}
catch (Exception)
{
ContentDialog dialog = new ContentDialog();
dialog.Title = "That Channel Already Exists";
dialog.IsSecondaryButtonEnabled = false;
dialog.PrimaryButtonText = "Continue";
//dialog.SecondaryButtonText = "Cancel";
}
string trimmed = String.Concat(name.Where(c => !Char.IsWhiteSpace(c)));
String tableCommand = "CREATE TABLE IF NOT " +
"EXISTS " + trimmed + " (title TEXT, content TEXT)";
//System.Diagnostics.Debug.WriteLine(tableCommand);
System.Diagnostics.Debug.WriteLine("-------------------------------------" + tableCommand);
Microsoft.Data.Sqlite.SqliteCommand createTable = new Microsoft.Data.Sqlite.SqliteCommand(tableCommand, db);
createTable.ExecuteReader();
}
}
public static void AddUser(string name, string pass)
{
string dbpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "sqliteSample.db");
using (Microsoft.Data.Sqlite.SqliteConnection db =
new Microsoft.Data.Sqlite.SqliteConnection($"Filename={dbpath}"))
{
db.Open();
Microsoft.Data.Sqlite.SqliteCommand insertCommand = new Microsoft.Data.Sqlite.SqliteCommand();
insertCommand.Connection = db;
// Use parameterized query to prevent SQL injection attacks
insertCommand.CommandText = "INSERT INTO users VALUES (@user, @pass);";
insertCommand.Parameters.AddWithValue("@user", name);
insertCommand.Parameters.AddWithValue("@pass", pass);
insertCommand.ExecuteReader();
db.Close();
}
}
/// <summary>
/// 执行标准的SQL语句并返回单行数据
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public Row GetRow(string sql)
{
//throw new NotImplementedException();
Row row = new Row();
using (Microsoft.Data.Sqlite.SqliteCommand sqlCommand = new Microsoft.Data.Sqlite.SqliteCommand(sql, dbc)) {
using (Microsoft.Data.Sqlite.SqliteDataReader reader = sqlCommand.ExecuteReader(System.Data.CommandBehavior.Default)) {
if (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
//item.Set(SqlReader.GetName(i), SqlReader[i].ToString());
string szName = reader.GetName(i);
row[szName] = reader[i].ToString();
}
}
reader.Close();
}
}
return(row);
}
private TableOrViewMetadata <SQLiteObjectName, DbType> GetTableOrViewInternal(SQLiteObjectName tableName)
{
const string tableSql =
@"SELECT
type AS ObjectType,
tbl_name AS ObjectName
FROM sqlite_master
WHERE UPPER(tbl_name) = UPPER(@Name) AND
(type='table' OR type='view')";
string actualName;
bool isTable;
using (var con = new SQLiteConnection(m_ConnectionBuilder.ConnectionString))
{
con.Open();
using (var cmd = new SQLiteCommand(tableSql, con))
{
cmd.Parameters.AddWithValue("@Name", tableName.Name);
using (var reader = cmd.ExecuteReader())
{
if (!reader.Read())
{
throw new MissingObjectException($"Could not find table or view {tableName}");
}
actualName = reader.GetString(reader.GetOrdinal("ObjectName"));
var objectType = reader.GetString(reader.GetOrdinal("ObjectType"));
isTable = objectType.Equals("table");
}
}
}
var columns = GetColumns(tableName, isTable);
return(new TableOrViewMetadata <SQLiteObjectName, DbType>(actualName, isTable, columns));
}
/// <summary>
/// Preloads metadata for all database tables.
/// </summary>
/// <remarks>This is normally used only for testing. By default, metadata is loaded as needed.</remarks>
public void PreloadTables()
{
const string tableSql =
@"SELECT
tbl_name as TableName
FROM sqlite_master
WHERE type = 'table'";
using (var con = new SQLiteConnection(m_ConnectionBuilder.ConnectionString))
{
con.Open();
using (var cmd = new SQLiteCommand(tableSql, con))
{
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var tableName = reader.GetString(reader.GetOrdinal("TableName"));
GetTableOrView(tableName);
}
}
}
}
}
public static List <String> TestGet(string name)
{
List <String> entries = new List <string>();
string dbpath = System.IO.Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "sqliteSample.db");
using (Microsoft.Data.Sqlite.SqliteConnection db =
new Microsoft.Data.Sqlite.SqliteConnection($"Filename={dbpath}"))
{
db.Open();
Microsoft.Data.Sqlite.SqliteCommand selectCommand = new Microsoft.Data.Sqlite.SqliteCommand
("SELECT password from users WHERE username = '******'", db);
Microsoft.Data.Sqlite.SqliteDataReader query = selectCommand.ExecuteReader();
while (query.Read())
{
entries.Add(query.GetString(0));
}
db.Close();
}
return(entries);
}
