/// <summary>
/// Returns the column index of the column name. Will standardize the name, does a dictionary lookup and will return null if not present.
/// </summary>
/// <param name="columnName"></param>
/// <returns></returns>
public Int32?GetColumnIndex(String columnName)
{
var standardizedName = Model.StandardizeCasing(columnName);
if (ColumnNameMap.ContainsKey(standardizedName))
{
return(ColumnNameMap[standardizedName]);
}
else
{
return(null);
}
}
public SimpleSchema(IExceptionContext ectx, KeyValuePair <string, ColumnType>[] columns,
Dictionary <string, MetadataUtils.MetadataGetter <VBuffer <ReadOnlyMemory <char> > > > keyValues)
: this(ectx, columns)
{
foreach (var kvp in keyValues)
{
var name = kvp.Key;
var getter = kvp.Value;
if (!ColumnNameMap.TryGetValue(name, out int col))
{
throw Ectx.ExceptParam(nameof(keyValues), $"Output schema does not contain column '{name}'");
}
if (!Types[col].ItemType.IsKey)
{
throw Ectx.ExceptParam(nameof(keyValues), $"Column '{name}' is not a key column, so it cannot have key value metadata");
}
_keyValueGetters[col] = getter;
}
}
/// <summary>
/// Do an ALTER on the db table and add a column
/// </summary>
/// <param name="tableName">The table name</param>
/// <param name="columnName">The column to add</param>
/// <param name="columnType">The db column type</param>
/// <param name="columnNumber">The column number (-1 if not to be used)</param>
private void AddColumn(string tableName, string columnName, string columnType, int columnNumber)
{
string colName;
if (tableName.StartsWith("_") || columnNumber < 0 ||
columnName.Equals("SimulationID", StringComparison.OrdinalIgnoreCase) ||
columnName.Equals("SimulationName", StringComparison.OrdinalIgnoreCase) ||
columnName.Equals("CheckpointID", StringComparison.OrdinalIgnoreCase) ||
columnName.Equals("CheckpointName", StringComparison.OrdinalIgnoreCase))
{
colName = columnName.Substring(0, Math.Min(31, columnName.Length));
}
else
{
colName = "COL_" + columnNumber.ToString();
}
string sql;
if (FieldExists(tableName, colName))
{
DropColumn(tableName, colName);
}
sql = "ALTER TABLE \"" + tableName + "\" ADD \"" + colName + "\" " + columnType;
this.ExecuteNonQuery(sql);
this.ExecuteNonQuery("INSERT INTO \"_ColumnInfo\" VALUES('" + tableName + "', + '" + columnName + "', " + columnNumber.ToString() + ")");
ColumnNameMap nameMap;
if (!colInfoMap.TryGetValue(tableName, out nameMap))
{
nameMap = new ColumnNameMap();
colInfoMap.Add(tableName, nameMap);
}
nameMap.ColDict.Add(columnName, columnNumber);
nameMap.LongNames.Insert(columnNumber, columnName);
}
/// <summary>
/// Open the Firebird SQL connection
/// </summary>
/// <param name="dbpath">Path to database</param>
/// <param name="source">localhost or server name</param>
/// <param name="user">db user name</param>
/// <param name="pass">db password</param>
/// <returns>True if opened</returns>
private bool OpenSQLConnection(string dbpath, string source, string user, string pass)
{
try
{
if (fbDBConnection.State == ConnectionState.Closed)
{
fbDBDataSet.Locale = CultureInfo.InvariantCulture;
fbDBConnection.ConnectionString = GetConnectionString(dbpath, source, user, pass);
fbDBConnection.Open();
}
if (TableExists("_ColumnInfo"))
{
DataTable colInfoTable = ExecuteQuery("SELECT \"TableName\", \"ColumnName\", \"ColumnNumber\" FROM \"_ColumnInfo\" ORDER BY \"TableName\", \"ColumnNumber\"");
string currentTable = string.Empty;
ColumnNameMap currentMap = null;
foreach (DataRow row in colInfoTable.Rows)
{
string tableName = ((string)row[0]).Trim();
string columnName = ((string)row[1]).Trim();
int columnNumber = Convert.ToInt32(row[2]);
if (tableName != currentTable)
{
currentTable = tableName;
currentMap = new ColumnNameMap();
colInfoMap.Add(tableName, currentMap);
}
currentMap.ColDict.Add(columnName, columnNumber);
currentMap.LongNames.Insert(columnNumber, columnName);
}
}
return(true);
}
catch (Exception ex)
{
throw new FirebirdException("Cannot open database connection To " + dbpath + "!\r\n" + ex.Message);
}
}
/// <summary>
/// Returns true if the result set contains the columnName
/// </summary>
/// <param name="columnName"></param>
/// <returns></returns>
public Boolean HasColumn(String columnName)
{
return(ColumnNameMap.ContainsKey(columnName));
}
/// <summary>Create the new table</summary>
public void CreateTable(string tableName, List <string> colNames, List <string> colTypes)
{
// We use the _ColumnInfo table to map between column names and column numbers
if (TableExists("_ColumnInfo"))
{
// We're creating a new table here, so delete any old records
this.ExecuteNonQuery("DELETE FROM \"_ColumnInfo\" WHERE \"TableName\"='" + tableName + "'");
}
else
{
this.ExecuteNonQuery("CREATE TABLE \"_ColumnInfo\" (\"TableName\" VARCHAR(500), \"ColumnName\" VARCHAR(500), \"ColumnNumber\" INTEGER)");
}
ColumnNameMap nameMap;
if (colInfoMap.TryGetValue(tableName, out nameMap))
{
nameMap.ColDict.Clear();
nameMap.LongNames.Clear();
}
else
{
nameMap = new ColumnNameMap();
colInfoMap.Add(tableName, nameMap);
}
StringBuilder sql = new StringBuilder();
for (int c = 0; c < colNames.Count; c++)
{
if (sql.Length > 0)
{
sql.Append(',');
}
string columnName = colNames[c];
sql.Append("\"");
// sql.Append(colNames[c]);
if (tableName.StartsWith("_") ||
columnName.Equals("SimulationID", StringComparison.OrdinalIgnoreCase) ||
columnName.Equals("SimulationName", StringComparison.OrdinalIgnoreCase) ||
columnName.Equals("CheckpointID", StringComparison.OrdinalIgnoreCase) ||
columnName.Equals("CheckpointName", StringComparison.OrdinalIgnoreCase))
{
sql.Append(columnName.Substring(0, Math.Min(31, columnName.Length))); /////
}
else
{
sql.Append("COL_" + c.ToString());
}
sql.Append("\" ");
if (colTypes[c] == null)
{
sql.Append("INTEGER");
}
else
{
sql.Append(colTypes[c]);
}
this.ExecuteNonQuery("INSERT INTO \"_ColumnInfo\" VALUES('" + tableName + "', + '" + columnName + "', " + c.ToString() + ")");
nameMap.ColDict.Add(columnName, c);
nameMap.LongNames.Insert(c, columnName);
}
sql.Insert(0, "CREATE TABLE \"" + tableName + "\" (");
sql.Append(')');
this.ExecuteNonQuery(sql.ToString());
}
