private string FormatColumnValue(string colName, string value)
{
SQLiteColumnStatement col = Utils.GetColumnByName(_columns, colName);
DbType dbtype = Utils.GetDbType(col.ColumnType);
if (dbtype == DbType.Int64 || dbtype == DbType.Double || dbtype == DbType.Single)
{
double dval;
if (double.TryParse(value, out dval))
{
return(dval.ToString());
}
}
if (value.Length >= 2)
{
if (value[0] == '\'' && value[value.Length - 1] == '\'')
{
value = value.Substring(1, value.Length - 2);
}
}
value = value.Replace("'", "''");
value = "'" + value + "'";
return(value);
}
private DialogResult OpenCellEditDialog(SQLiteCreateTableStatement table, SQLiteColumnStatement column, ref object value)
{
DialogResult res;
CellValueEditorDialog dlg = new CellValueEditorDialog();
res = dlg.ShowEditor(this, table, column, ref value);
return(res);
}
/// <summary>
/// Show the editor for the specified column and field value.
/// </summary>
/// <param name="owner">The owner window</param>
/// <param name="table">The table sql schema object</param>
/// <param name="column">The column whose field is edited</param>
/// <param name="value">The initial value to edit</param>
/// <returns>
/// If the user chose to apply his changes - DialogResult.OK (in which case the <paramref name="value"/>
/// parameter is changed to the edited value). Otherwise a DialogResult.Cancel value is returned.
/// </returns>
public DialogResult ShowEditor(IWin32Window owner, SQLiteCreateTableStatement table, SQLiteColumnStatement column, ref object value)
{
_table = table;
_column = column;
this.Text = "Edit " + column.ObjectName.ToString();
TabPage tbp = null;
_nullable = column.IsNullable;
if (value == DBNull.Value)
{
cbxSetAsNull.Checked = true;
tbp = GetTabPageForColumnType(column);
}
else
{
cbxSetAsNull.Checked = false;
tbp = GetTabPageForValueType(value);
}
if (Utils.IsColumnActingAsRowIdAlias(_table, column.ObjectName.ToString()))
{
// This is a special case in which we allow the user to edit a INTEGER PRIMARY KEY column
// that acts as an alias to the underlying RowID column. In this case we can't allow the user
// to choose any other type other than INTEGER when editing the field.
tbp = tbpEditInteger;
}
TabPage tbp2 = GetTabPageForColumnType(column);
if (!object.ReferenceEquals(tbp, tbp2))
{
RemoveAllPagesExcept(tbp, tbp2);
SetTabPageValue(tbp, value);
SetTabPageValue(tbp2, value);
}
else
{
RemoveAllPagesExcept(tbp);
SetTabPageValue(tbp, value);
}
_origTabPage = tbp;
tbcTypes.SelectedTab = tbp;
UpdateState();
DialogResult res = ShowDialog(owner);
if (res == DialogResult.OK)
{
value = _value;
}
GC.Collect();
return(res);
}
private bool CanEditGrid(bool right, out TableChangeItem citem, out FastGridLocation sloc)
{
citem = null;
sloc = FastGridLocation.Empty;
if (_item == null)
{
return(false);
}
FastGrid grid;
SQLiteCreateTableStatement table;
string dbpath;
if (right)
{
grid = grdRight;
table = (SQLiteCreateTableStatement)_item.RightDdlStatement;
dbpath = _rightdb;
}
else
{
grid = grdLeft;
table = (SQLiteCreateTableStatement)_item.LeftDdlStatement;
dbpath = _leftdb;
}
sloc = grid.SelectedCellLocation;
string columnName = (string)grid.Columns[sloc.ColumnIndex].Tag;
SQLiteColumnStatement column = Utils.FindColumn(table.Columns, columnName);
if (column == null)
{
return(false);
}
long total = _tableChanges.GetTotalChangesCount(new string[] { _diff });
if (sloc.RowIndex >= total)
{
return(false);
}
citem = _tableChanges.GetChangeItem(_diff, sloc.RowIndex);
// Can't edit a cell that belongs to a row that doesn't exist
if (right && citem.Result == ComparisonResult.ExistsInLeftDB)
{
return(false);
}
else if (!right && citem.Result == ComparisonResult.ExistsInRightDB)
{
return(false);
}
return(true);
}
/// <summary>
/// Checks if the specified table contains a column with the specified name.
/// </summary>
/// <param name="table">The table to check</param>
/// <param name="column">The column to chcek</param>
/// <returns>TRUE if the table contains a column with the specified name, FALSE otherwise.</returns>
private bool TableContainsColumn(SQLiteCreateTableStatement table, SQLiteColumnStatement column)
{
foreach (SQLiteColumnStatement tc in table.Columns)
{
if (tc.ObjectName.Equals(column.ObjectName))
{
return(true);
}
} // foreach
return(false);
}
private void UpdateInsertCommandFields(SQLiteCreateTableStatement table,
SQLiteCommand insert, SQLiteDataReader reader)
{
for (int i = 0; i < table.Columns.Count; i++)
{
SQLiteColumnStatement col = table.Columns[i];
string prmName = Utils.GetColumnParameterName(col.ObjectName.ToString());
string colName = SQLiteParser.Utils.Chop(col.ObjectName.ToString());
insert.Parameters[prmName].Value = reader[colName];
} // for
}
private string FormatColumnValue(string colName)
{
colName = SQLiteParser.Utils.QuoteIfNeeded(colName);
MultiPanelPage page = pnlValues.SelectedPage;
if (page == pnlBlobValue)
{
return(colName + " = x'" + txtBlob.Text.Trim() + "'");
}
else if (page == pnlBooleanValue)
{
return(colName + " = " + (rbtnTrue.Checked ? "1" : "0"));
}
else if (page == pnlDateTimeValue)
{
DateTime dt = new DateTime(dtpDate.Value.Year, dtpDate.Value.Month, dtpDate.Value.Day);
return("DATE(" + colName + ") = DATE('" + SQLiteParser.Utils.GetDateTimeLiteralString(dt, false) + "')");
}
else if (page == pnlFloatingPoint)
{
string colname = (string)cboColumnName.SelectedItem;
SQLiteColumnStatement c = Utils.GetColumnByName(_columns, colname);
if (Utils.GetDbType(c.ColumnType) == DbType.Double)
{
double dval = (double)numFloatingPoint.Value;
return(colName + " = " + dval.ToString());
}
else
{
float fval = (float)numFloatingPoint.Value;
return(colName + " = " + fval.ToString());
}
}
else if (page == pnlGuidValue)
{
return(colName + " = " + "X'" + txtGuid.Text.Replace("-", string.Empty) + "'");
}
else if (page == pnlIntegerValue)
{
return(colName + " = " + numIntValue.Value.ToString());
}
else if (page == pnlTextValue)
{
return(colName + " = " + SQLiteParser.Utils.QuoteLiteralString(txtFieldValue.Text));
}
throw new InvalidOperationException();
}
private FastGridColumn MakeGridColumn(bool pkey, SQLiteColumnStatement col)
{
string cname = SQLiteParser.Utils.Chop(col.ObjectName.ToString());
FastGridColumn res = new FastGridColumn(cname);
if (Utils.GetDbType(col.ColumnType) == DbType.Boolean)
{
res.ColumnType = FastGridColumnType.CheckBox;
}
if (pkey)
{
res.Image = imageList1.Images[0];
}
res.Tag = cname;
return(res);
}
private SQLiteCommand BuildInsertCommand(SQLiteCreateTableStatement table, string tableName,
SQLiteConnection conn, SQLiteTransaction tx)
{
SQLiteCommand res = new SQLiteCommand();
StringBuilder sb = new StringBuilder();
sb.Append("INSERT INTO " + tableName + " (");
for (int i = 0; i < table.Columns.Count; i++)
{
SQLiteColumnStatement col = table.Columns[i];
sb.Append(col.ObjectName.ToString());
if (i < table.Columns.Count - 1)
{
sb.Append(",");
}
} // for
sb.Append(") VALUES (");
for (int i = 0; i < table.Columns.Count; i++)
{
SQLiteColumnStatement col = table.Columns[i];
string prmName = Utils.GetColumnParameterName(col.ObjectName.ToString());
sb.Append(prmName);
if (i < table.Columns.Count - 1)
{
sb.Append(",");
}
res.Parameters.Add(prmName, Utils.GetDbType(col.ColumnType));
} // for
sb.Append(")");
res.CommandText = sb.ToString();
res.Connection = conn;
res.Transaction = tx;
return(res);
}
/// <summary>
/// This purpose of this method is to fill the needed row with data
/// taken from the table-changes object. This is part of a virtual mode
/// implementation of the FastGrid control designed to minimize memory
/// requirements only to the bare essentials.
/// </summary>
/// <param name="grid">The grid that issued to the RowNeeded request</param>
/// <param name="rowIndex">The index of the row to fill</param>
/// <param name="row">The row to fill</param>
private void FillRow(FastGrid grid, long rowIndex, FastGridRow row)
{
// Fetch the table change item from the cache/database
TableChangeItem item = _tableChanges.GetChangeItem(_diff, rowIndex);
bool empty;
bool isLeft;
SQLiteCreateTableStatement table = null;
if (grid == grdLeft)
{
isLeft = true;
empty = item.LeftFields == null;
table = _tableChanges.LeftTable;
}
else
{
isLeft = false;
empty = item.RightFields == null;
table = _tableChanges.RightTable;
} // else
if (empty)
{
for (int i = 0; i < row.Cells.Length; i++)
{
row.Cells[i].Style.BackColor = EMPTY_ROW_BACK_COLOR;
}
}
else
{
for (int i = 0; i < row.Cells.Length; i++)
{
string cname = (string)grid.Columns[i].Tag;
SQLiteColumnStatement col = Utils.GetColumnByName(table, cname);
object fval = item.GetField(cname, isLeft);
if (fval == DBNull.Value || fval == null)
{
row.Cells[i].Value = "NULL";
row.Cells[i].Style.Font = _italic;
}
else if (Utils.GetDbType(col.ColumnType) == DbType.Binary)
{
long v = (long)fval;
if (v == 1)
{
row.Cells[i].Value = "BLOB";
}
else
{
row.Cells[i].Value = "NULL";
}
row.Cells[i].Style.Font = _italic;
}
else
{
if (fval is byte[])
{
string tmp = Encoding.ASCII.GetString((byte[])fval);
row.Cells[i].Value = tmp;
}
else
{
row.Cells[i].Value = fval;
}
} // else
// Mark different cells with special background color
if (item.Result == ComparisonResult.DifferentData)
{
if (i < item.LeftFields.Length && i < item.RightFields.Length)
{
if (item.ChangedBlobsColumnNames != null && item.ChangedBlobsColumnNames.Contains(col.ObjectName.ToString()))
{
row.Cells[i].Style.BackColor = DIFFERENT_CELL_BACK_COLOR;
}
else
{
// Check only if the field appears in both tables and has different values
if (item.HasField(cname, true) && item.HasField(cname, false) && !item.GetField(cname, true).Equals(item.GetField(cname, false)))
{
object tmp = item.GetField(cname, isLeft);
if (tmp is long && ((long)tmp) == 0 && Utils.GetDbType(col.ColumnType) == DbType.Binary &&
item.GetField(cname, !isLeft) == DBNull.Value)
{
// Ignore the case when the values are not equal when one of the fields is a BLOB and the
// other is not, but both values indicate NULL content.
}
else
{
tmp = item.GetField(cname, !isLeft);
SQLiteCreateTableStatement tbl;
if (isLeft)
{
tbl = _tableChanges.RightTable;
}
else
{
tbl = _tableChanges.LeftTable;
}
object tmp2 = item.GetField(cname, isLeft);
SQLiteColumnStatement ocol = Utils.GetColumnByName(tbl.Columns, cname);
if (tmp2 == DBNull.Value && tmp != null && tmp is long && ((long)tmp) == 0)
{
// Ignore the reverse case when both fields are actually NULL, but one of them is BLOB
// and ther other is not.
}
else
{
row.Cells[i].Style.BackColor = DIFFERENT_CELL_BACK_COLOR;
}
} // else
} // if
} // else
} // if
} // if
} // for
} // else
}
private void HandleCellEdit(bool right)
{
FastGrid grid;
SQLiteCreateTableStatement table;
string dbpath;
if (right)
{
grid = grdRight;
table = (SQLiteCreateTableStatement)_item.RightDdlStatement;
dbpath = _rightdb;
}
else
{
grid = grdLeft;
table = (SQLiteCreateTableStatement)_item.LeftDdlStatement;
dbpath = _leftdb;
}
FastGridLocation sloc = grid.SelectedCellLocation;
string columnName = (string)grid.Columns[sloc.ColumnIndex].Tag;
SQLiteColumnStatement column = Utils.FindColumn(table.Columns, columnName);
if (column == null)
{
return;
}
TableChangeItem citem = null;
try
{
citem = _tableChanges.GetChangeItem(_diff, sloc.RowIndex);
}
catch (IndexOutOfRangeException iox)
{
// Ignore (the user must have pressed on a cell when the table is actually empty)
return;
} // catch
// Can't edit a cell that belongs to a row that doesn't exist
if (right && citem.Result == ComparisonResult.ExistsInLeftDB)
{
return;
}
else if (!right && citem.Result == ComparisonResult.ExistsInRightDB)
{
return;
}
object otherBlob = null;
// The row id is needed when displaying editor dialog for a BLOB field
long rowId = -1;
if (right)
{
rowId = citem.RightRowId;
if (citem.LeftFields != null && TableContainsColumn((SQLiteCreateTableStatement)_item.LeftDdlStatement, column))
{
otherBlob = GetRowFieldValue(citem, false, columnName);
}
}
else
{
rowId = citem.LeftRowId;
if (citem.RightFields != null && TableContainsColumn((SQLiteCreateTableStatement)_item.RightDdlStatement, column))
{
otherBlob = GetRowFieldValue(citem, true, columnName);
}
}
// Extract the current field value from the change item
object value = GetRowFieldValue(citem, right, columnName);
// Adjust the BLOB value since it was fetched as the IS NOT NULL expression
// in order to avoid loading the BLOB field into main memory.
if (Utils.GetDbType(column.ColumnType) == DbType.Binary)
{
long v = (long)value;
if (v == 0) // means NULL
{
value = DBNull.Value;
}
}
// When the user is clicking on a BLOB field - we need to extract its value
// to a local file before opening the cell-edit dialog.
Be.Windows.Forms.DynamicFileByteProvider origProvider = null;
if (value != DBNull.Value && Utils.GetDbType(column.ColumnType) == DbType.Binary)
{
// In case of BLOBs - we have to first load them to the local file-system and
// only then we can allow the user to edit their contents
using (BlobLoader loader = new BlobLoader(dbpath,
table.ObjectName.ToString(), column.ObjectName.ToString(), rowId, Configuration.TempBlobFilePath))
{
ProgressDialog dlg = new ProgressDialog();
dlg.Start(this, loader);
if (dlg.Error != null)
{
return;
}
} // using
// Instead of passing a byte[] array to the cell editor dialog - we'll pass
// a reference to the dynamic file byte provider that is opened on the data file
// that was written with the BLOB data. This allows us to conserve memory (BLOB
// fields can be quite large).
origProvider = new Be.Windows.Forms.DynamicFileByteProvider(Configuration.TempBlobFilePath);
value = origProvider;
}
// Open the cell editor dialog and allow the user to edit the contents of the field.
DialogResult res = OpenCellEditDialog(table, column, ref value);
if (res == DialogResult.Cancel)
{
return;
}
// This part of the IF statement deals with BLOB fields - these require
// a special (and lengthy) handling.
if (Utils.GetDbType(column.ColumnType) == DbType.Binary || value is Be.Windows.Forms.DynamicFileByteProvider)
{
if (otherBlob != null && (citem.Result == ComparisonResult.Same || citem.Result == ComparisonResult.DifferentData))
{
if (otherBlob is long)
{
// Another adjustment for the other BLOB field value
long ob = (long)otherBlob;
if (ob == 0) // means NULL blob field
{
otherBlob = DBNull.Value;
}
}
}
if (value != DBNull.Value)
{
string fpath = null;
long blobLength = 0;
if (value is Be.Windows.Forms.DynamicFileByteProvider)
{
Be.Windows.Forms.DynamicFileByteProvider dp = (Be.Windows.Forms.DynamicFileByteProvider)value;
if (dp.HasChanges())
{
dp.ApplyChanges();
}
dp.Dispose();
blobLength = dp.Length;
}
else
{
throw new InvalidOperationException("cell editor returned unexpected value");
}
BlobSaver saver = null;
try
{
// In this case we need to store the updated file/buffer back to the BLOB field
// and run BLOB comparison in order to check if the BLOB is different then
// the one in the other database.
// Save the file specified in the call as a BLOB
saver = new BlobSaver(dbpath, table.ObjectName.ToString(), column.ObjectName.ToString(), rowId, fpath);
ProgressDialog dlg = new ProgressDialog();
dlg.Start(this, saver);
if (dlg.Error != null)
{
// Notify that a row has changed
if (RowsChanged != null)
{
RowsChanged(this, EventArgs.Empty);
}
return;
}
}
finally
{
saver.Dispose();
} // finally
// Update the table change item with an indication that the BLOB field that was just saved
// is not null.
citem.SetField(column.ObjectName.ToString(), !right, (long)1);
if (otherBlob != null && (citem.Result == ComparisonResult.DifferentData || citem.Result == ComparisonResult.Same))
{
// At this point we need to compare the BLOB that was saved with the BLOB field in the other
// database in order to update the ChangedBlobsColumnNames field of the table change item so
// that the user can know if the BLOB is equal to the other BLOB or if the BLOB is different.
if (otherBlob != DBNull.Value)
{
// Run BLOB comparison
bool equalBlobs = false;
using (BlobCompareWorker bcw = new BlobCompareWorker(_leftdb, _rightdb,
SQLiteParser.Utils.Chop(table.ObjectName.ToString()),
SQLiteParser.Utils.Chop(column.ObjectName.ToString()),
citem.LeftRowId, citem.RightRowId))
{
ProgressDialog pdlg = new ProgressDialog();
pdlg.Start(this, bcw);
if (pdlg.Error != null)
{
// Notify that a row has changed
if (RowsChanged != null)
{
RowsChanged(this, EventArgs.Empty);
}
return;
}
equalBlobs = bcw.IsBlobsEqual;
} // using
if (equalBlobs)
{
// The two BLOBs are equal so remove any difference mark
if (citem.ChangedBlobsColumnNames != null && citem.ChangedBlobsColumnNames.Contains(column.ObjectName.ToString()))
{
citem.ChangedBlobsColumnNames.Remove(column.ObjectName.ToString());
}
}
else
{
// The two BLOBs are different so add a difference mark if necessary
if (citem.ChangedBlobsColumnNames == null)
{
citem.ChangedBlobsColumnNames = new List <string>();
}
if (!citem.ChangedBlobsColumnNames.Contains(column.ObjectName.ToString()))
{
citem.ChangedBlobsColumnNames.Add(column.ObjectName.ToString());
}
citem.Result = ComparisonResult.DifferentData;
} // else
}
else
{
// If the other BLOB field is NULL - it means that the two BLOBs are different
// because one is NULL and the other is not.
if (citem.ChangedBlobsColumnNames == null)
{
citem.ChangedBlobsColumnNames = new List <string>();
}
if (!citem.ChangedBlobsColumnNames.Contains(column.ObjectName.ToString()))
{
citem.ChangedBlobsColumnNames.Add(column.ObjectName.ToString());
}
citem.Result = ComparisonResult.DifferentData;
} // else
} // if
}
else
{
// Ask the table changes object to set this field to null
_tableChanges.SetColumnField(_diff, sloc.RowIndex, column.ObjectName.ToString(), right, DBNull.Value);
citem.SetField(column.ObjectName.ToString(), !right, (long)0);
if (otherBlob != null && (citem.Result == ComparisonResult.Same || citem.Result == ComparisonResult.DifferentData))
{
// A BLOB field was set to NULL - compare it again to the other BLOB field and decide
// if the ChangedBlobsColumnNames field should be updated to reflect this.
if (otherBlob == DBNull.Value)
{
// The other BLOB field is NULL so the two fields are equal
if (citem.ChangedBlobsColumnNames != null && citem.ChangedBlobsColumnNames.Contains(column.ObjectName.ToString()))
{
citem.ChangedBlobsColumnNames.Remove(column.ObjectName.ToString());
}
}
else
{
if (citem.ChangedBlobsColumnNames == null)
{
citem.ChangedBlobsColumnNames = new List <string>();
}
// The other BLOB field is not NULL so the two fields are not equal
if (!citem.ChangedBlobsColumnNames.Contains(column.ObjectName.ToString()))
{
citem.ChangedBlobsColumnNames.Add(column.ObjectName.ToString());
}
citem.Result = ComparisonResult.DifferentData;
} // else
} // if
} // else
}
else
{
// The field that was edited is not a BLOB so we can deal with it normally
try
{
_tableChanges.SetColumnField(_diff, sloc.RowIndex, column.ObjectName.ToString(), right, value);
}
catch (Exception ex)
{
_log.Error("Failed to set column field", ex);
MessageBox.Show(this, ex.Message, "Operation Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);
} // catch
} // else
GC.Collect();
// Mark that the table changes object does not contain precise results
_tableChanges.HasPreciseResults = false;
// Notify that a row has changed
if (RowsChanged != null)
{
RowsChanged(this, EventArgs.Empty);
}
// Refresh the right/left grids again from the database.
grdRight.RefreshLayout();
grdLeft.RefreshLayout();
}
private void UpdateState()
{
if (!cbxValue.Checked)
{
pnlValues.SelectedPage = pnlNull;
}
else
{
if (cboColumnName.SelectedIndex != -1)
{
SQLiteColumnStatement col = Utils.GetColumnByName(_columns, (string)cboColumnName.SelectedItem);
DbType dbtype = Utils.GetDbType(col.ColumnType);
if (dbtype == DbType.String)
{
pnlValues.SelectedPage = pnlTextValue;
}
else if (dbtype == DbType.DateTime)
{
pnlValues.SelectedPage = pnlDateTimeValue;
}
else if (dbtype == DbType.Double || dbtype == DbType.Single)
{
pnlValues.SelectedPage = pnlFloatingPoint;
}
else if (dbtype == DbType.Int64)
{
pnlValues.SelectedPage = pnlIntegerValue;
}
else if (dbtype == DbType.Boolean)
{
pnlValues.SelectedPage = pnlBooleanValue;
}
else if (dbtype == DbType.Binary)
{
pnlValues.SelectedPage = pnlBlobValue;
}
else if (dbtype == DbType.Guid)
{
pnlValues.SelectedPage = pnlGuidValue;
}
else
{
throw new InvalidOperationException("Internal software error");
}
} // if
} // else
if (pnlValues.SelectedPage == pnlGuidValue)
{
Guid dummy;
btnOK.Enabled = Utils.IsGuid(txtGuid.Text, out dummy);
}
else if (pnlValues.SelectedPage == pnlBlobValue)
{
btnOK.Enabled = (txtBlob.Text.Trim().Length % 2 == 0);
}
else
{
btnOK.Enabled = true;
}
}
private TabPage GetTabPageForColumnType(SQLiteColumnStatement column)
{
TabPage res = null;
DbType dbtype = Utils.GetDbType(column.ColumnType);
switch (dbtype)
{
case DbType.AnsiString:
case DbType.AnsiStringFixedLength:
case DbType.String:
case DbType.StringFixedLength:
case DbType.Xml:
res = tbpEditText;
break;
case DbType.Guid:
res = tbpEditGuid;
break;
case DbType.Binary:
case DbType.Object:
res = tbpEditBlob;
break;
case DbType.Boolean:
res = tbpEditBoolean;
break;
case DbType.Date:
case DbType.DateTime:
case DbType.DateTime2:
case DbType.DateTimeOffset:
case DbType.Time:
res = tbpEditDateTime;
break;
case DbType.Byte:
case DbType.Int16:
case DbType.Int32:
case DbType.Int64:
case DbType.SByte:
case DbType.UInt16:
case DbType.UInt32:
case DbType.UInt64:
res = tbpEditInteger;
break;
case DbType.Currency:
case DbType.Decimal:
case DbType.Double:
case DbType.Single:
case DbType.VarNumeric:
res = tbpEditFloatingPoint;
break;
default:
throw new ArgumentException($"illegal column dbtype [{dbtype}]");
} // switch
return(res);
}
