public static void LoadVerses(List <SongVerse> listToLoad, int autoNumber, bool removeFormatting)
{
listToLoad.Clear();
using (FBirdTask t = new FBirdTask())
{
t.CommandText =
"SELECT [AutoNumber], [IsChorus], [Verse], [OrderNum] " +
"FROM [SongVerses] " +
"WHERE [AutoNumber] = @AutoNumber " +
"ORDER BY [OrderNum]";
t.Parameters.Add("@AutoNumber", FbDbType.Integer);
t.Parameters["@AutoNumber"].Value = autoNumber;
t.ExecuteReader();
int verseNumber = 1;
while (t.DR.Read())
{
bool isChorus = t.GetInt32(1) == 1;
int vnum = isChorus ? -1 : verseNumber;
string verseData = t.GetString(2);
if (removeFormatting)
{
verseData = RemoveVerseFormatting(verseData);
}
SongVerse sv = new SongVerse(isChorus, verseData, vnum);
listToLoad.Add(sv);
if (!isChorus)
{
verseNumber++;
}
}
}
}
private void UpdateVerseList()
{
if (lbBibleBook == null || lbBibleBook.SelectedIndex == -1 || lbBibleChapter.SelectedIndex == -1)
{
return;
}
// Get the data from db & load into the list
lbBibleVerse.SuspendLayout();
lbBibleVerse.Items.Clear();
using (FBirdTask t = new FBirdTask())
{
t.CommandText = "SELECT \"DATA\" FROM \"BIBLEVERSES\" WHERE " +
"\"VERSION\" = '" + Program.ConfigHelper.BiblePrimaryTranslation + "' AND " +
"\"REFBOOK\" = '" + ((BookData)lbBibleBook.SelectedItem).mappingName + "' AND " +
"\"REFCHAPTER\" = " + lbBibleChapter.SelectedItem.ToString() + " " +
"ORDER BY \"REFBOOK\", \"REFCHAPTER\", \"REFVERSE\"";
t.ExecuteReader();
int i = 0;
while (t.DR.Read())
{
i++;
VerseData vd = new VerseData(t.GetString(0), i);
lbBibleVerse.Items.Add(vd);
}
}
lbBibleVerse.ResumeLayout();
}
public static List <BibleSearchResult> Search(List <string> searchTerms)
{
if (searchTerms.Count < 1)
{
throw (new ApplicationException("No search terms"));
}
// Double check sql
string p = ",./<>?[]\\{}|!@#$%^&*()-=_+:;'\"";
foreach (string str in searchTerms)
{
if (str.IndexOfAny(p.ToCharArray()) != -1)
{
throw (new ApplicationException("Malformed sql passed to Search"));
}
}
// Build the query
string query = "SELECT \"VERSION\", \"REFBOOK\", \"REFCHAPTER\", \"REFVERSE\", \"DATA\", \"BOOK\", \"CHAPTER\", \"VERSE\" FROM \"BIBLEVERSES\" WHERE ";
foreach (string s in searchTerms)
{
query += "(\"DATA\" LIKE '%" + s + "%') AND ";
}
query = query.Remove(query.Length - 4);
// Query the database
List <BibleSearchResult> lResults = new List <BibleSearchResult>();
using (FBirdTask t = new FBirdTask())
{
t.CommandText = query;
t.ExecuteReader();
int cutoff = 250;
while (t.DR.Read() && cutoff > 0)
{
cutoff--;
BibleSearchResult r = new BibleSearchResult();
BibleVerse bv = new BibleVerse();
bv.RefVersion = t.GetString(0);
bv.RefBook = t.GetString(1);
bv.RefChapter = t.GetInt32(2);
bv.RefVerse = t.GetInt32(3);
bv.Text = t.GetString(4);
bv.SecondaryBook = t.GetString(5);
bv.SecondaryChapter = t.GetInt32(6);
bv.SecondaryVerse = t.GetInt32(7);
r.bibVerse = bv;
r.searchResult = bv.Text;
lResults.Add(r);
}
}
return(lResults);
}
public SongFormatter(DS.SongsRow row)
{
InitializeComponent();
currentRow = row;
// accept changes if not new
if (row.RowState == DataRowState.Detached)
{
this.Text = "Add a new song";
this.btnSave.Text = "Add";
}
else
{
this.btnSave.Text = "Save";
row.AcceptChanges();
}
dt = new EmpowerPresenter.DS.SongVersesDataTable();
// load song verses
using (FBirdTask t = new FBirdTask())
{
t.CommandText =
"SELECT \"AutoNumber\", \"IsChorus\", \"Verse\", \"OrderNum\" " +
"FROM \"SongVerses\" " +
"WHERE \"AutoNumber\" = @AutoNumber " +
"ORDER BY \"OrderNum\"";
t.Parameters.Add("@AutoNumber", FbDbType.Integer);
t.Parameters["@AutoNumber"].Value = row.AutoNumber;
DS.SongVersesRow r;
t.ExecuteReader();
while (t.DR.Read())
{
r = dt.NewSongVersesRow();
r.AutoNumber = t.DR.GetInt32(0);
r.IsChorus = t.DR.GetInt16(1) == 1 ? true : false;
r.Verse = t.DR.GetString(2);
r.OrderNum = t.DR.GetInt32(3);
dt.AddSongVersesRow(r);
}
dt.AcceptChanges();
}
dataView1.BeginInit();
dataView1.Table = dt;
dataView1.EndInit();
// disect song
SongNumber = currentRow.Number.ToString();
txtTitle.Text = currentRow.Title;
txtChorus.Text = currentRow.Chorus;
currentImage = new PhotoInfo();
currentImage.ImageId = currentRow.Image;
button1_Click(null, null);
gdbVerses.Model.Refresh();
}
// Buttons
private void save()
{
#if DEMO
new DemoVersionOnly("Modifying song data").ShowDialog();
#else
// update row
currentRow.Number = Convert.ToInt32(txtNumber.Text);
currentRow.Title = txtTitle.Text;
currentRow.Chorus = txtChorus.Text;
currentRow.Image = currentImage.ImageId;
// save changes
if (currentRow.RowState == DataRowState.Detached)
{
Program.SongsDS.Songs.AddSongsRow(currentRow);
}
Data.Songs.AddUpdateSong(currentRow);
currentRow.AcceptChanges();
#region Save datatable changes
Data.Songs.UpdateSongCopyright(currentRow.AutoNumber, txtCopyright.Text);
using (FBirdTask t = new FBirdTask())
{
DataTable changes = dt.GetChanges();
if (changes != null)
{
t.CommandText = "DELETE FROM [SongVerses] WHERE [AutoNumber] = @AutoNumber";
t.Parameters.Add("@AutoNumber", FbDbType.Integer);
t.Parameters["@AutoNumber"].Value = currentRow.AutoNumber;
t.ExecuteNonQuery();
t.CommandText =
"INSERT INTO [SongVerses] ([AutoNumber], [IsChorus], [Verse], [OrderNum]) " +
"VALUES (@AutoNumber, @IsChorus, @Verse, @OrderNum)";
t.Parameters.Add("@IsChorus", FbDbType.SmallInt);
t.Parameters.Add("@Verse", FbDbType.VarChar, 0);
t.Parameters.Add("@OrderNum", FbDbType.Integer);
int i = 0;
foreach (DataRow r in dt.Rows)
{
if (r.RowState == DataRowState.Deleted)
{
continue;
}
t.Parameters["@OrderNum"].Value = i.ToString();
t.Parameters["@IsChorus"].Value = (bool)r["IsChorus"] == true ? 1 : 0;
t.Parameters["@Verse"].Value = r["Verse"];
t.ExecuteNonQuery();
i++;
}
}
}
#endregion
#endif
}
public static List <SongSearchResult> Search(List <string> searchTerms)
{
if (searchTerms.Count < 1)
{
throw (new ApplicationException("No search terms"));
}
// Double check sql
string p = ",./<>?[]\\{}|!@#$%^&*()-=_+:;'\"";
foreach (string str in searchTerms)
{
if (str.IndexOfAny(p.ToCharArray()) != -1)
{
throw (new ApplicationException("Malformed sql passed to Search"));
}
}
// Build the query
string query = "SELECT \"SongVerses\".\"AutoNumber\", \"Songs\".\"Number\", \"SongVerses\".\"Verse\", \"SongVerses\".\"OrderNum\", \"SongVerses\".\"IsChorus\" " +
"FROM \"SongVerses\" INNER JOIN \"Songs\" ON \"SongVerses\".\"AutoNumber\" = \"Songs\".\"AutoNumber\" WHERE ";
foreach (string s in searchTerms)
{
query += "(\"Verse\" LIKE '%" + s + "%') AND ";
}
query = query.Remove(query.Length - 4);
query += "ORDER BY \"Number\"";
// Query the database
List <SongSearchResult> lResult = new List <SongSearchResult>();
using (FBirdTask t = new FBirdTask())
{
t.CommandText = query;
t.ExecuteReader();
int cutoff = 100;
while (t.DR.Read() && cutoff > 0)
{
cutoff--;
SongSearchResult ssr = new SongSearchResult();
ssr.autoNumber = t.GetInt32(0);
ssr.songNumber = t.GetInt32(1);
ssr.verseData = t.GetString(2);
ssr.isAtStart = t.GetInt32(3) == 0;
ssr.isChorus = t.GetBoolean(4);
ssr.verseData = SongProject.RemoveVerseFormatting(ssr.verseData);
ssr.searchResult = ssr.verseData;
lResult.Add(ssr);
}
}
return(lResult);
}
public static void SaveFontToDatabase(int id, PresenterFont font)
{
using (FBirdTask t = new FBirdTask())
{
// check is font saved
t.CommandText = "SELECT COUNT(*) FROM [PPTFONT] WHERE [AUTONUMBER] = @AUTONUMBER";
t.AddParameter("@AUTONUMBER", id);
if ((int)t.ExecuteScalar() == 0)
{
// insert
t.CommandText =
"INSERT INTO [PPTFONT] ([AUTONUMBER], [FONTNAME], [SIZEINPOINTS], [COLOR], [VERTICALALIGNMENT], [HORIZONTALALIGNMENT], [OUTLINE], [SHADOW], [OUTLINECOLOR], [SHADOWCOLOR], [ITALIC], [BOLD], [DOUBLESPACE]) " +
"VALUES (@AUTONUMBER, @FONTNAME, @SIZEINPOINTS, @COLOR, @VERTICALALIGNMENT, @HORIZONTALALIGNMENT, @OUTLINE, @SHADOW, @OUTLINECOLOR, @SHADOWCOLOR, @ITALIC, @BOLD, @DOUBLESPACE)";
}
else
{
// update
t.CommandText =
"UPDATE [PPTFONT] SET " +
"[FONTNAME] = @FONTNAME, " +
"[SIZEINPOINTS] = @SIZEINPOINTS," +
"[COLOR] = @COLOR," +
"[VERTICALALIGNMENT] = @VERTICALALIGNMENT," +
"[HORIZONTALALIGNMENT] = @HORIZONTALALIGNMENT," +
"[OUTLINE] = @OUTLINE," +
"[SHADOW] = @SHADOW," +
"[ITALIC] = @ITALIC," +
"[BOLD] = @BOLD, " +
"[OUTLINECOLOR] = @OUTLINECOLOR, " +
"[SHADOWCOLOR] = @SHADOWCOLOR, " +
"[DOUBLESPACE] = @DOUBLESPACE " +
"WHERE [AUTONUMBER] = @AUTONUMBER";
}
// add parameters
t.AddParameter("@FONTNAME", 512, font.FontName);
t.AddParameter("@SIZEINPOINTS", font.SizeInPoints);
t.AddParameter("@COLOR", font.Color.ToArgb());
t.AddParameter("@VERTICALALIGNMENT", (int)font.VerticalAlignment);
t.AddParameter("@HORIZONTALALIGNMENT", (int)font.HorizontalAlignment);
t.AddParameter("@OUTLINE", font.Outline);
t.AddParameter("@SHADOW", font.Shadow);
t.AddParameter("@ITALIC", font.Italic);
t.AddParameter("@BOLD", font.Bold);
t.AddParameter("@OUTLINECOLOR", font.OutlineColor.ToArgb());
t.AddParameter("@SHADOWCOLOR", font.ShadowColor.ToArgb());
t.AddParameter("@DOUBLESPACE", font.DoubleSpace);
// save to db
t.ExecuteNonQuery();
}
}
/// <summary></summary>
private void btnSave_Click(object sender, System.EventArgs e)
{
this.DialogResult = DialogResult.OK;
// update row
currentRow.Number = Convert.ToInt32(txtNumber.Text);
currentRow.Title = txtTitle.Text;
currentRow.Chorus = txtChorus.Text;
// save changes
if (currentRow.RowState == DataRowState.Detached)
{
Presenter.DefaultDS.Songs.AddSongsRow(currentRow);
}
currentRow.AcceptChanges();
// save datatable changes
using (FBirdTask t = new FBirdTask())
{
DataTable changes = dt.GetChanges();
if (changes != null)
{
t.CommandText = "DELETE FROM \"SongVerses\" WHERE \"AutoNumber\" = @AutoNumber";
t.Parameters.Add("@AutoNumber", FbDbType.Integer);
t.Parameters["@AutoNumber"].Value = currentRow.AutoNumber;
t.ExecuteNonQuery();
t.CommandText =
"INSERT INTO \"SongVerses\" (\"AutoNumber\", \"IsChorus\", \"Verse\", \"OrderNum\") " +
"VALUES (@AutoNumber, @IsChorus, @Verse, @OrderNum)";
t.Parameters.Add("@IsChorus", FbDbType.SmallInt);
t.Parameters.Add("@Verse", FbDbType.VarChar, 2048);
t.Parameters.Add("@OrderNum", FbDbType.Integer);
int i = 0;
foreach (DataRow r in dt.Rows)
{
t.Parameters["@OrderNum"].Value = i.ToString();
t.Parameters["@IsChorus"].Value = (bool)r["IsChorus"] == true ? 1 : 0;
t.Parameters["@Verse"].Value = r["Verse"];
t.ExecuteNonQuery();
i++;
}
}
}
this.Close();
}
private void deleteToolStripMenuItem_Click(object sender, EventArgs e)
{
int index;
index = SndataGrid.CurrentCell.RowNumber;
int rNumber = sRow.Number;
using (FBirdTask t = new FBirdTask())
{
t.CommandText = "DELETE FROM [SongNotes] WHERE [Number] = @NNumber";
t.AddParameter("@Number", rNumber);
t.ExecuteNonQuery();
}
SNdataView.Delete(index);
}
private void save()
{
int rNum = dt.Rows.Count;
using (FBirdTask t = new FBirdTask())
{
t.CommandText = "INSERT INTO [SongNotes] ([Note],[IdSong]) " +
"VALUES (@Note, @IdSong)";
t.Parameters.Add("@Note", FbDbType.VarChar);
t.Parameters.Add("@IdSong", FbDbType.Integer);
for (int i = rowCount; i < rNum; i++)
{
t.Parameters["@Note"].Value = sRow.Note;
t.Parameters["@IdSong"].Value = SNumber;
t.ExecuteNonQuery();
}
}
}
private void LoadSongNote(int songNumber)
{
dt = new PresenterDataset.SongNotesDataTable();
SNdataView = new DataView();
this.Controls.Add(SndataGrid);
BindingSource source = new BindingSource();
source.DataSource = SNdataView;
SndataGrid.DataSource = SNdataView;
int SongId = 5;
using (FBirdTask t = new FBirdTask())
{
t.CommandText = "SELECT [SongNotes].[Number], [SongNotes].[Note], [SongNotes].[IdSong] FROM [SongNotes]" +
" where [SongNotes].[IdSong]=" + songNumber;
t.ExecuteReader();
if (t.DR != null)
{
int rowNumber = 1;
while (t.DR.Read())
{
sRow = dt.NewSongNotesRow();
sRow.Number = t.GetInt32(0); // rowNumber;
sRow.Note = t.GetString(1);
// sRow.SongNumber= t.GetInt32(2);
dt.AddSongNotesRow(sRow);
rowNumber++;
}
t.DR.Close();
SNdataView.BeginInit();
SNdataView.Table = dt;
SNdataView.EndInit();
}
else
{
sRow = dt.NewSongNotesRow();
sRow.Note = "";
dt.AddSongNotesRow(sRow);
}
rowCount = dt.Rows.Count;
}
}
private void InternalLoadSongFromDB(PresenterDataset.SongsRow row, bool isNew)
{
dt = new PresenterDataset.SongVersesDataTable();
dt.Columns["AutoNumber"].DefaultValue = row.AutoNumber;
if (!isNew)
{
using (FBirdTask t = new FBirdTask())
{
t.CommandText =
"SELECT [AutoNumber], [IsChorus], [Verse], [OrderNum] " +
"FROM [SongVerses] " +
"WHERE [AutoNumber] = @AutoNumber " +
"ORDER BY [OrderNum]";
t.Parameters.Add("@AutoNumber", FbDbType.Integer);
t.Parameters["@AutoNumber"].Value = row.AutoNumber;
PresenterDataset.SongVersesRow r;
t.ExecuteReader();
while (t.DR.Read())
{
r = dt.NewSongVersesRow();
r.AutoNumber = t.GetInt32(0);
r.IsChorus = t.GetInt32(1) == 1 ? true : false;
r.Verse = t.GetString(2).Replace("\r\n", "\n").Replace("\n", "\r\n");
r.OrderNum = t.GetInt32(3);
dt.AddSongVersesRow(r);
}
}
}
dt.AcceptChanges();
if (dt.Count == 0)
{
btnSave.Enabled = false;
}
dataView1.BeginInit();
dataView1.Table = dt;
dataView1.EndInit();
dataGrid1.TableStyles[0].MappingName = dt.TableName;
}
public string BookFromId(int id)
{
if (reverseBook == null)
{
reverseBook = new Dictionary <int, string>();
using (FBirdTask t = new FBirdTask())
{
t.CommandText = "SELECT \"OrderNum\", \"RefBook\" FROM \"BibleLookUp\"";
t.ExecuteReader();
while (t.DR.Read())
{
if (!reverseBook.ContainsKey(t.DR.GetInt32(0)))
{
reverseBook.Add(t.DR.GetInt32(0), t.DR.GetString(1));
}
}
}
}
return(reverseBook[id]);
}
// Database support
public static PresenterFont GetFontFromDatabase(int id)
{
PresenterFont font = new PresenterFont();
using (FBirdTask t = new FBirdTask())
{
t.CommandText =
"SELECT [FONTNAME], [SIZEINPOINTS], [COLOR], [VERTICALALIGNMENT], [HORIZONTALALIGNMENT], [OUTLINE], [SHADOW], [OUTLINECOLOR], [SHADOWCOLOR], [ITALIC], [BOLD], [DOUBLESPACE] " +
"FROM [PPTFONT] " +
"WHERE [AUTONUMBER] = @AUTONUMBER";
t.AddParameter("@AUTONUMBER", id);
t.ExecuteReader();
if (t.DR.Read())
{
font.FontName = t.GetString(0);
font.SizeInPoints = t.GetInt32(1);
font.Color = t.GetColor(2, Color.Black);
switch (t.GetInt32(3))
{
case 0:
font.VerticalAlignment = VerticalAlignment.Top;
break;
case 1:
font.VerticalAlignment = VerticalAlignment.Middle;
break;
case 2:
font.VerticalAlignment = VerticalAlignment.Bottom;
break;
default:
break;
}
switch (t.GetInt32(4))
{
case 0:
font.HorizontalAlignment = HorizontalAlignment.Left;
break;
case 2:
font.HorizontalAlignment = HorizontalAlignment.Center;
break;
case 1:
font.HorizontalAlignment = HorizontalAlignment.Right;
break;
default:
break;
}
font.Outline = t.GetBoolean(5);
font.Shadow = t.GetBoolean(6);
font.OutlineColor = t.GetColor(7, Color.White);
font.ShadowColor = t.GetColor(8, Color.Gray);
font.Italic = t.GetBoolean(9);
font.Bold = t.GetBoolean(10);
font.DoubleSpace = t.GetBoolean(11);
}
return(font);
}
}
private bool GetDifSongVerses(string oldRoot, Dictionary <string, MigrationSongVerse> mVerses)
{
// Approach:
// - open two database connection and send identical sorted queries
// - add each result to the list
// - if two results have no changes then drop them from the list
// - if there are changes mark what kind
// - after queries are finished execute add / update queries
using (FBirdTask told = new FBirdTask())
using (FBirdTask tnew = new FBirdTask())
{
// Set old connection string
string path2olddb = Path.Combine(oldRoot, "bibdata.fdb");
string oldConstring = "ServerType=1;Database=" + path2olddb + ";User=SYSDBA;Password=masterkey;Charset=WIN1251";
told.Connection = new FbConnection(oldConstring);
string fbQuery = "SELECT " +
"\"SongVerses\".\"AutoNumber\", " +
"\"SongVerses\".\"OrderNum\", " +
"\"SongVerses\".\"IsChorus\", " +
"\"SongVerses\".\"Verse\" " +
"FROM " +
"\"SongVerses\" " +
"ORDER BY " +
"\"SongVerses\".\"AutoNumber\", " +
"\"SongVerses\".\"OrderNum\"";
string jtQuery = "SELECT " +
"[SongVerses].[AutoNumber], " +
"[SongVerses].[OrderNum], " +
"[SongVerses].[IsChorus], " +
"[SongVerses].[Verse] " +
"FROM " +
"[SongVerses] " +
"ORDER BY " +
"[SongVerses].[AutoNumber], " +
"[SongVerses].[OrderNum]";
told.CommandText = fbQuery;
tnew.CommandText = jtQuery;
told.ExecuteReader();
tnew.ExecuteReader();
if (told.DR == null || tnew.DR == null)
{
this.TopMost = false;
MessageBox.Show(this, "Migration failed to migrate song data", "Error");
return(false);
}
bool oldAvailable = true;
bool newAvailable = true;
while (oldAvailable || newAvailable)
{
oldAvailable = told.DR.Read();
newAvailable = tnew.DR.Read();
// Old data
if (oldAvailable)
{
MigrationSongVerse vo = new MigrationSongVerse();
DrFillSongVerseObj(told.DR, vo);
vo.foundInOld = true;
string id = vo.GetId();
if (mVerses.ContainsKey(id))
{
MigrationSongVerse v2 = mVerses[id];
v2.foundInOld = true;
// Drop if the two are the same
if (v2.Compare(vo, v2))
{
mVerses.Remove(id);
}
}
else
{
mVerses[id] = vo;
}
}
// New data
if (newAvailable)
{
MigrationSongVerse vn = new MigrationSongVerse();
DrFillSongVerseObj(tnew.DR, vn);
vn.foundInNew = true;
string id = vn.GetId();
if (mVerses.ContainsKey(id))
{
MigrationSongVerse v2 = mVerses[id];
v2.foundInNew = true;
// Drop if the two are the same
if (v2.Compare(vn, v2))
{
mVerses.Remove(id);
}
}
else
{
mVerses[id] = vn;
}
}
}
}
return(true);
}
private static void Application_ThreadExit(object sender, EventArgs e)
{
Program.exiting = true;
if (!Program.Presenter.songsInitComplete)
{
try
{
if (Program.Presenter.bwSongsInit.IsBusy)
{
Program.Presenter.bwSongsInit.CancelAsync();
}
}
catch (Exception ex) { System.Diagnostics.Trace.WriteLine(ex.ToString()); }
return;
}
/*
* Save all the unsaved data
*/
using (FBirdTask t = new FBirdTask())
{
t.CommandText = "INSERT INTO [Songs] " +
"([AutoNumber], [Number], [Title], [Chorus], [Location], [DisplayDefault], [ImageId], [Overlay], [FontId], [Settings]) VALUES " +
"(@AutoNumber, @Number, @Title, @Chorus, @Location, @DisplayDefault, @ImageId, @Overlay, @FontId, @Settings)";
t.Parameters.Add("@AutoNumber", FbDbType.Integer);
t.Parameters.Add("@Number", FbDbType.Integer);
t.Parameters.Add("@Title", FbDbType.VarChar, 0);
t.Parameters.Add("@Chorus", FbDbType.VarChar, 0);
t.Parameters.Add("@Location", FbDbType.VarChar, 0);
t.Parameters.Add("@DisplayDefault", FbDbType.SmallInt);
t.Parameters.Add("@ImageId", FbDbType.Integer);
t.Parameters.Add("@Overlay", FbDbType.Integer);
t.Parameters.Add("@FontId", FbDbType.Integer);
t.Parameters.Add("@Settings", FbDbType.VarChar, 0);
DataTable dt = Program.SongsDS.Songs.GetChanges(DataRowState.Added);
if (dt != null)
{
foreach (DataRow row in dt.Rows)
{
t.Parameters["@AutoNumber"].Value = row["AutoNumber"];
t.Parameters["@Number"].Value = row["Number"];
t.Parameters["@Title"].Value = row["Title"];
t.Parameters["@Chorus"].Value = row["Chorus"];
t.Parameters["@Location"].Value = row["Location"];
t.Parameters["@DisplayDefault"].Value = (bool)row["DisplayDefault"] == true ? 1 : 0;
t.Parameters["@ImageId"].Value = row["Image"];
t.Parameters["@Overlay"].Value = row["Overlay"];
t.Parameters["@FontId"].Value = row["FontId"];
t.Parameters["@Settings"].Value = row["Settings"];
t.ExecuteNonQuery();
}
}
dt = Program.SongsDS.Songs.GetChanges(DataRowState.Modified);
if (dt != null)
{
t.CommandText = "UPDATE [Songs] SET " +
"[Number] = @Number, " +
"[Title] = @Title, " +
"[Chorus] = @Chorus, " +
"[Location] = @Location, " +
"[DisplayDefault] = @DisplayDefault, " +
"[ImageId] = @ImageId, " +
"[Overlay] = @Overlay, " +
"[FontId] = @FontId, " +
"[Settings] = @Settings WHERE [AutoNumber] = @AutoNumber";
foreach (DataRow row in dt.Rows)
{
t.Parameters["@AutoNumber"].Value = row["AutoNumber"];
t.Parameters["@Number"].Value = row["Number"];
t.Parameters["@Title"].Value = row["Title"];
t.Parameters["@Chorus"].Value = row["Chorus"];
t.Parameters["@Location"].Value = row["Location"];
t.Parameters["@DisplayDefault"].Value = (bool)row["DisplayDefault"] == true ? 1 : 0;
t.Parameters["@ImageId"].Value = row["Image"];
t.Parameters["@Overlay"].Value = row["Overlay"];
t.Parameters["@FontId"].Value = row["FontId"];
t.Parameters["@Settings"].Value = row["Settings"];
t.ExecuteNonQuery();
}
}
dt = Program.SongsDS.Songs.GetChanges(DataRowState.Deleted);
if (dt != null)
{
t.Parameters.Clear();
t.Parameters.Add("@AutoNumber", FbDbType.Integer);
t.CommandText = "DELETE FROM [Songs] WHERE [AutoNumber] = @AutoNumber";
foreach (DataRow row in dt.Rows)
{
t.Parameters["@AutoNumber"].Value = row["AutoNumber"];
t.ExecuteNonQuery();
}
}
}//*/
}
private bool GetDifSongs(string oldRoot, Dictionary <int, MigrationSong> mSongs)
{
using (FBirdTask told = new FBirdTask())
using (JetTask tnew = new JetTask())
{
// Set old connection string
string path2olddb = Path.Combine(oldRoot, "bibdata.fdb");
string oldConstring = "ServerType=1;Database=" + path2olddb + ";User=SYSDBA;Password=masterkey;Charset=WIN1251";
told.Connection = new FbConnection(oldConstring);
string fbQuery = "SELECT " +
"\"Songs\".\"AutoNumber\", " +
"\"Songs\".\"Number\", " +
"\"Songs\".\"Title\", " +
"\"Songs\".\"Chorus\", " +
"\"Songs\".\"Location\", " +
"\"Songs\".\"DisplayDefault\", " +
"\"Songs\".\"ImageId\", " +
"\"Songs\".\"Overlay\", " +
"\"Songs\".\"Copyright\" " +
"FROM \"Songs\" " +
"ORDER BY \"AutoNumber\"";
string jtQuery = "SELECT " +
"[Songs].[AutoNumber], " +
"[Songs].[Number], " +
"[Songs].[Title], " +
"[Songs].[Chorus], " +
"[Songs].[Location], " +
"[Songs].[DisplayDefault], " +
"[Songs].[ImageId], " +
"[Songs].[Overlay], " +
"[Songs].[Copyright] " +
"FROM [Songs] " +
"ORDER BY [AutoNumber]";
told.CommandText = fbQuery;
tnew.CommandText = jtQuery;
told.ExecuteReader();
tnew.ExecuteReader();
if (told.DR == null || tnew.DR == null)
{
this.TopMost = false;
MessageBox.Show(this, "Migration failed to migrate song data", "Error");
return(false);
}
bool oldAvailable = true;
bool newAvailable = true;
while (oldAvailable || newAvailable)
{
oldAvailable = told.DR.Read();
newAvailable = tnew.DR.Read();
// Old data
if (oldAvailable)
{
MigrationSong mgo = new MigrationSong();
DrFillSongObj(told.DR, mgo);
mgo.foundInOld = true;
// Check if image got changed
if (mgo.imageId == 4)
{
mgo.imageId = -2;
mgo.overlay = 777;
}
if (mSongs.ContainsKey(mgo.autoNumber))
{
MigrationSong v2 = mSongs[mgo.autoNumber];
v2.foundInOld = true;
// Drop if the two are the same
if (v2.Compare(mgo, v2))
{
mSongs.Remove(mgo.autoNumber);
}
}
else
{
mSongs[mgo.autoNumber] = mgo;
}
}
// New data
if (newAvailable)
{
MigrationSong mgn = new MigrationSong();
DrFillSongObj(tnew.DR, mgn);
mgn.foundInNew = true;
if (mSongs.ContainsKey(mgn.autoNumber))
{
MigrationSong v2 = mSongs[mgn.autoNumber];
v2.foundInNew = true;
// Drop if the two are the same
if (v2.Compare(mgn, v2))
{
mSongs.Remove(mgn.autoNumber);
}
}
else
{
mSongs[mgn.autoNumber] = mgn;
}
}
}
}
return(true);
}
private void LoadBibDat(BibleVerse verse)
{
// Understanding the databse
// SELECT FIRST 10 * FROM BIBLEVERSES
// SELECT DISTINCT VERSION FROM BIBLEVERSES
// SELECT DISTINCT BOOK FROM BIBLEVERSES
// Verses are queried in the number scheme of the primary translation
// KJV Psalms 23 == RST Psalms 22
// SELECT FIRST 10 * FROM BIBLEVERSES AS PRI WHERE PRI.VERSION = 'RST' AND PRI.BOOK = 'Psalms'
// Old implementation of the Bibleverse table
// KJV | Ref book = original KJV numbering | Book = RST equivalent numbering
// RST | Ref book = original RST numbering | Book = KJV equivalent numbering
// One advantage of the old method is that ability to preload all the version of a chapter relative to the primary translation
// PENDING: This code needs to be refactored to use a universal numbering scheme
// https://www.biblegateway.com/passage/?search=Psalm+23&version=RUSV
// KJV | Ref book = KJV numbering | Book = KJV numbering
// RST | Ref book = KJV numbering | Book = RST numbering
List <string> translations = TranslationList();
List <BibleVerse> verses = new List <BibleVerse>();
bibVerses.Clear();
currentVerseNum = verse.RefVerse;
using (FBirdTask t = new FBirdTask())
{
string select = "SELECT ";
for (int tnum = 1; tnum <= translations.Count; tnum++)
{
if (tnum > 1)
{
select += ", ";
}
select += string.Format("T{0}.REFBOOK, T{0}.REFCHAPTER, T{0}.REFVERSE, T{0}.DATA", tnum);
}
string from = " FROM BIBLEVERSES AS T1";
for (int transIx = 1; transIx < translations.Count; transIx++)
{
bool doMaping = translations[0] == "KJV" || translations[transIx] == "KJV";
if (doMaping)
{
from += string.Format(" LEFT JOIN BIBLEVERSES AS T{0} ON T1.BOOK = T{0}.REFBOOK AND T1.CHAPTER = T{0}.REFCHAPTER AND T1.VERSE = T{0}.REFVERSE", (transIx + 1));
}
else
{
from += string.Format(" LEFT JOIN BIBLEVERSES AS T{0} ON T1.REFBOOK = T{0}.REFBOOK AND T1.REFCHAPTER = T{0}.REFCHAPTER AND T1.REFVERSE = T{0}.REFVERSE", (transIx + 1));
}
}
string where = " WHERE T1.VERSION = @Version1 AND T1.REFBOOK = @Book AND T1.REFCHAPTER = @Chapter";
for (int transIx = 1; transIx < translations.Count; transIx++)
{
where += string.Format(" AND T{0}.VERSION = @Version{0}", (transIx + 1));
}
string order = " ORDER BY T1.REFBOOK, T1.REFCHAPTER, T1.REFVERSE, T1.VERSE";
t.CommandText = select + from + where + order;
t.Command.Parameters.Add("@Book", FbDbType.VarChar, 50).Value = verse.RefBook;
t.Command.Parameters.Add("@Chapter", FbDbType.Integer).Value = verse.RefChapter;
for (int tnum = 1; tnum <= translations.Count; tnum++)
{
t.Command.Parameters.Add("@Version" + tnum, FbDbType.VarChar, 10).Value = translations[tnum - 1];
}
t.ExecuteReader();
while (t.DR.Read())
{
int ix = 0;
BibleVerse bVerse = new BibleVerse();
bVerse.RefVersion = translations[0];
bVerse.RefBook = t.GetString(ix++);
bVerse.RefChapter = t.GetInt32(ix++);
bVerse.RefVerse = t.GetInt32(ix++);
bVerse.Text = t.GetString(ix++);
if (translations.Count > 1)
{
bVerse.SecondaryVersion = translations[1];
bVerse.SecondaryBook = t.GetString(ix++);
bVerse.SecondaryChapter = t.GetInt32(ix++);
bVerse.SecondaryVerse = t.GetInt32(ix++);
bVerse.SecondaryText = t.GetString(ix++);
}
if (translations.Count > 2)
{
bVerse.TertiaryVersion = translations[2];
bVerse.TertiaryBook = t.GetString(ix++);
bVerse.TertiaryChapter = t.GetInt32(ix++);
bVerse.TertiaryVerse = t.GetInt32(ix++);
bVerse.TertiaryText = t.GetString(ix++);
}
verses.Add(bVerse);
}
}
verses.ForEach(v => bibVerses.Add(v.RefVerse, v));
}
public void IndexAllVersions()
{
/////////////////////////////////////////////////////////////////
// Go through the bible and build a forward index
Dictionary <string, List <int> > forwardIndex = new Dictionary <string, List <int> >();
string badchars = "',.?!:;`-\"()[]";
using (PerformanceTimer pt = new PerformanceTimer("Analyze"))
using (FBirdTask t = new FBirdTask())
{
t.CommandText = "SELECT \"BibleLookUp\".\"OrderNum\", BIBLEVERSES.REFCHAPTER, BIBLEVERSES.REFVERSE, BIBLEVERSES.DATA, Version " +
"FROM BIBLEVERSES " +
"INNER JOIN \"BibleLookUp\" ON (BIBLEVERSES.REFBOOK=\"BibleLookUp\".\"RefBook\") " +
"WHERE (Version = 'KJV')";
using (PerformanceTimer pt2 = new PerformanceTimer("Analyze - Initial exec"))
t.ExecuteReader();
using (PerformanceTimer pt2 = new PerformanceTimer("Analyze - Data read"))
while (t.DR.Read())
{
string s = t.DR.GetString(3).ToLower(); // get raw
foreach (char c in badchars) // strip bad chars
{
s = s.Replace(c.ToString(), " ");
}
string[] words = s.Split(" ".ToCharArray());
// Count
List <string> localWords = new List <string>();
foreach (string word in words)
{
if (word.Length > 0)
{
// Check local
if (!localWords.Contains(word))
{
localWords.Add(word);
// Ensure list
if (!forwardIndex.ContainsKey(word))
{
forwardIndex[word] = new List <int>();
}
int z = ((byte)t.DR.GetInt16(2))
+ ((byte)t.DR.GetInt16(1) << 8)
+ ((byte)t.DR.GetInt16(0) << 16);
// Adjust for version
if (t.DR.GetString(4) == "RST")
{
z += ((byte)1 << 24);
}
else
{
z += ((byte)2 << 24);
}
forwardIndex[word].Add(z);
}
}
}
}
}
/////////////////////////////////////////////////////////////////
// Sort words
List <string> sortedWords = new List <string>();
foreach (string s in forwardIndex.Keys)
{
sortedWords.Add(s);
}
sortedWords.Sort();
/////////////////////////////////////////////////////////////////
// Write indexs to file
Dictionary <string, int> wordIndex = new Dictionary <string, int>();
string pthIndex = Path.Combine(Path.GetDirectoryName(Application.ExecutablePath), "bibndx.bin");
FileStream fs = new FileStream(pthIndex, FileMode.Create, FileAccess.Write);
foreach (string key in sortedWords)
{
// Store the current word offset into word map
wordIndex.Add(key, (int)fs.Position);
// Write the count of results
byte[] bInt = BitConverter.GetBytes((int)forwardIndex[key].Count);
fs.Write(bInt, 0, 4);
// Write out all the result pointers
foreach (int i in forwardIndex[key])
{
bInt = BitConverter.GetBytes(i);
fs.Write(bInt, 0, 4);
}
}
fs.Flush();
fs.Close();
/////////////////////////////////////////////////////////////////
// Write word list to file
string pthWords = Path.Combine(Path.GetDirectoryName(Application.ExecutablePath), "bibwds.bin");
FileStream fs2 = new FileStream(pthWords, FileMode.Create, FileAccess.Write, FileShare.None);
foreach (string s in wordIndex.Keys)
{
byte[] bCurWord = UnicodeEncoding.Unicode.GetBytes(s);
fs2.WriteByte((byte)bCurWord.Length);
fs2.Write(bCurWord, 0, bCurWord.Length);
byte[] bInt = BitConverter.GetBytes((int)wordIndex[s]);
fs2.Write(bInt, 0, 4);
}
fs2.Flush();
fs2.Close();
}
