public IEnumerable <Song> getSongs()
{
IEnumerable <Song> songs = new List <Song>();
if (songsConnection.State == System.Data.ConnectionState.Open && songWordsConnection.State == System.Data.ConnectionState.Open)
{
var fetchSongsCommand = new SQLiteCommand("select * from song", songsConnection);
var fetchSongWordsCommand = new SQLiteCommand("select * from word", songWordsConnection);
Dictionary <int, Song> dSongs = new Dictionary <int, Song>();
SQLiteDataReader songsReader = fetchSongsCommand.ExecuteReader();
SQLiteDataReader songWordsReader = fetchSongWordsCommand.ExecuteReader();
while (songsReader.Read())
{
var song = new Song()
{
id = int.Parse(songsReader["rowid"].ToString()),
title = songsReader["title"] != System.DBNull.Value ? (string)songsReader["title"] : "",
author = songsReader["author"] != System.DBNull.Value ? (string)songsReader["author"] : "",
copyright = songsReader["copyright"] != System.DBNull.Value ? (string)songsReader["copyright"] : "",
administrator = songsReader["administrator"] != System.DBNull.Value ? (string)songsReader["administrator"] : "",
description = songsReader["description"] != System.DBNull.Value ? (string)songsReader["description"] : "",
tags = songsReader["tags"] != System.DBNull.Value ? (string)songsReader["tags"] : "",
text = ""
};
dSongs[(int)song.id] = song;
}
while (songWordsReader.Read())
{
Song song;
var id = int.Parse(songWordsReader["song_id"].ToString());
if (dSongs.ContainsKey(id))
{
song = dSongs[id];
}
else
{
song = new Song()
{
id = id
};
dSongs[id] = song;
}
song.text = songWordsReader["words"] != System.DBNull.Value ? RichTextStripper.StripRichTextFormat((string)songWordsReader["words"]) : "";
}
songs = dSongs.Select(p => p.Value);
}
return(songs);
}
public Dictionary <int, int> fixDatabase()
{
Console.WriteLine("fixing database");
if (songsConnection.State == System.Data.ConnectionState.Open && songWordsConnection.State == System.Data.ConnectionState.Open)
{
var fetchSongsCommand = new SQLiteCommand("select * from song", songsConnection);
var fetchSongWordsCommand = new SQLiteCommand("select * from word", songWordsConnection);
SQLiteDataReader songsReader = fetchSongsCommand.ExecuteReader();
Dictionary <int, Song> dbSongs = new Dictionary <int, Song>();
while (songsReader.Read())
{
var song = new Song()
{
id = int.Parse(songsReader["rowid"].ToString()),
title = songsReader["title"] != System.DBNull.Value ? (string)songsReader["title"] : "",
author = songsReader["author"] != System.DBNull.Value ? (string)songsReader["author"] : "",
copyright = songsReader["copyright"] != System.DBNull.Value ? (string)songsReader["copyright"] : "",
administrator = songsReader["administrator"] != System.DBNull.Value ? (string)songsReader["administrator"] : "",
description = songsReader["description"] != System.DBNull.Value ? (string)songsReader["description"] : "",
tags = songsReader["tags"] != System.DBNull.Value ? (string)songsReader["tags"] : ""
};
dbSongs[(int)song.id] = song;
}
SQLiteDataReader songWordsReader = fetchSongWordsCommand.ExecuteReader();
while (songWordsReader.Read())
{
Song song;
var id = int.Parse(songWordsReader["song_id"].ToString());
if (dbSongs.ContainsKey(id))
{
song = dbSongs[id];
}
else
{
song = new Song()
{
id = id
};
dbSongs[id] = song;
}
song.text = songWordsReader["words"] != System.DBNull.Value ? RichTextStripper.StripRichTextFormat((string)songWordsReader["words"]) : "";
}
var removeSongWords = new SQLiteCommand("delete from word", songWordsConnection);
removeSongWords.ExecuteNonQuery();
var removeSongTableCommand = new SQLiteCommand("DROP TABLE song", songsConnection);
removeSongTableCommand.ExecuteNonQuery();
var createSongTableCommand = new SQLiteCommand("CREATE TABLE song (rowid integer PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, song_item_uid text UNIQUE, song_rev_uid text, song_uid text, title text NOT NULL, author text, copyright text, administrator text, description text, tags text, reference_number text, vendor_id integer, presentation_id integer, layout_revision integer DEFAULT 1, revision integer DEFAULT 1 )", songsConnection);
createSongTableCommand.ExecuteNonQuery();
Dictionary <int, int> newidToOldId = new Dictionary <int, int>();
Dictionary <int, int> oldidToNewId = new Dictionary <int, int>();
foreach (var item in dbSongs)
{
var songUid = Guid.NewGuid();
var insertSongCommand = new SQLiteCommand("insert into song (song_item_uid, song_uid, title, author, copyright, administrator, description, tags) values(@song_item_uid, @song_uid, @title, @author, @copyright, @administrator, @description, @tags)", songsConnection);
insertSongCommand.Parameters.Add(new SQLiteParameter("@song_item_uid", songUid));
insertSongCommand.Parameters.Add(new SQLiteParameter("@song_uid", songUid));
insertSongCommand.Parameters.Add(new SQLiteParameter("@title", item.Value.title));
insertSongCommand.Parameters.Add(new SQLiteParameter("@author", item.Value.author));
insertSongCommand.Parameters.Add(new SQLiteParameter("@copyright", item.Value.copyright));
insertSongCommand.Parameters.Add(new SQLiteParameter("@administrator", item.Value.administrator));
insertSongCommand.Parameters.Add(new SQLiteParameter("@description", item.Value.description));
insertSongCommand.Parameters.Add(new SQLiteParameter("@tags", item.Value.tags));
insertSongCommand.ExecuteNonQuery(System.Data.CommandBehavior.SingleRow);
var songId = songsConnection.LastInsertRowId;
oldidToNewId[item.Key] = (int)songId;
string words = @"{\rtf1{\pard " + item.Value.text.Replace("\n", @"\par ") + "}}";
var insertSongWordsCommand = new SQLiteCommand("insert into word (song_id, words) values (@song_id, @words)", songWordsConnection);
insertSongWordsCommand.Parameters.Add(new SQLiteParameter("@song_id", songId.ToString()));
insertSongWordsCommand.Parameters.Add(new SQLiteParameter("@words", words));
insertSongWordsCommand.ExecuteNonQuery();
}
return(oldidToNewId);
}
return(null);
}
public Song getSong(uint id)
{
Song song = null;
if (songsConnection.State == System.Data.ConnectionState.Open && songWordsConnection.State == System.Data.ConnectionState.Open)
{
song = new Song();
var fetchSongCommand = new SQLiteCommand("select * from song", songsConnection);
var fetchSongWordsCommand = new SQLiteCommand("select * from word", songWordsConnection);
SQLiteDataReader songReader = fetchSongCommand.ExecuteReader();
SQLiteDataReader songWordsReader = fetchSongWordsCommand.ExecuteReader();
while (songReader.Read())
{
song = new Song()
{
id = (int)songReader["rowid"],
title = songReader["title"] != System.DBNull.Value ? (string)songReader["title"] : "",
author = songReader["author"] != System.DBNull.Value ? (string)songReader["author"] : "",
copyright = songReader["copyright"] != System.DBNull.Value ? (string)songReader["copyright"] : "",
administrator = songReader["administrator"] != System.DBNull.Value ? (string)songReader["administrator"] : "",
description = songReader["description"] != System.DBNull.Value ? (string)songReader["description"] : "",
tags = songReader["tags"] != System.DBNull.Value ? (string)songReader["tags"] : ""
};
}
if (song != null)
{
while (songWordsReader.Read())
{
song.text = songWordsReader["words"] != System.DBNull.Value ? RichTextStripper.StripRichTextFormat((string)songWordsReader["words"]) : "";
}
}
}
return(song);
}
