public DataTable GetIndexes(string tableName)
{
DataTable metaData = new DataTable();
//metaData.Columns.Add("TABLE_CATALOG", Type.GetType("System.String"));
//metaData.Columns.Add("TABLE_SCHEMA", Type.GetType("System.String"));
//metaData.Columns.Add("TABLE_NAME", Type.GetType("System.String"));
//metaData.Columns.Add("INDEX_CATALOG", Type.GetType("System.String"));
//metaData.Columns.Add("INDEX_SCHEMA", Type.GetType("System.String"));
//metaData.Columns.Add("INDEX_NAME", Type.GetType("System.String"));
//metaData.Columns.Add("UNIQUE", Type.GetType("System.String"));
//metaData.Columns.Add("CLUSTERED", Type.GetType("System.String"));
//metaData.Columns.Add("TYPE", Type.GetType("System.String"));
//metaData.Columns.Add("FILL_FACTOR", Type.GetType("System.String"));
//metaData.Columns.Add("INITIAL_SIZE", Type.GetType("System.String"));
//metaData.Columns.Add("NULLS", Type.GetType("System.String"));
//metaData.Columns.Add("SORT_BOOKMARKS", Type.GetType("System.String"));
//metaData.Columns.Add("AUTO_UPDATE", Type.GetType("System.String"));
//metaData.Columns.Add("NULL_COLLATION", Type.GetType("System.String"));
//metaData.Columns.Add("COLLATION", Type.GetType("System.String"));
//metaData.Columns.Add("CARDINALITY", Type.GetType("System.String"));
//metaData.Columns.Add("PAGES", Type.GetType("System.String"));
//metaData.Columns.Add("FILTER_CONDITION", Type.GetType("System.String"));
//metaData.Columns.Add("INTEGRATED", Type.GetType("System.String"));
metaData.Columns.Add("TABLE_CATALOG", Type.GetType("System.String"));
metaData.Columns.Add("TABLE_NAME", Type.GetType("System.String"));
metaData.Columns.Add("INDEX_CATALOG", Type.GetType("System.String"));
metaData.Columns.Add("INDEX_NAME", Type.GetType("System.String"));
metaData.Columns.Add("UNIQUE", Type.GetType("System.Boolean"));
metaData.Columns.Add("COLLATION", Type.GetType("System.Int16"));
metaData.Columns.Add("COLUMN_NAME", Type.GetType("System.String"));
IVistaDBDatabase db = DDA.OpenDatabase(dbName, VistaDBDatabaseOpenMode.NonexclusiveReadOnly, "");
ArrayList tables = db.EnumTables();
IVistaDBTableStructure tblStructure = db.TableStructure(tableName);
foreach (IVistaDBIndexInformation indexInfo in tblStructure.Indexes)
{
string[] pks = indexInfo.KeyExpression.Split(',');
int index = 0;
foreach (string colName in pks)
{
metaData.Rows.Add(new object[]
{
GetDatabaseName(),
tblStructure.Name,
GetDatabaseName(),
indexInfo.Name,
indexInfo.Unique,
indexInfo.KeyStructure[index++].Descending ? 2 : 1,
colName
});
}
}
return(metaData);
}
public DataTable GetForeignKeys(string tableName)
{
DataTable metaData = new DataTable();
//metaData.Columns.Add("PK_TABLE_CATALOG", Type.GetType("System.String"));
//metaData.Columns.Add("PK_TABLE_SCHEMA", Type.GetType("System.String"));
//metaData.Columns.Add("PK_TABLE_NAME", Type.GetType("System.String"));
//metaData.Columns.Add("FK_TABLE_CATALOG", Type.GetType("System.String"));
//metaData.Columns.Add("FK_TABLE_SCHEMA", Type.GetType("System.String"));
//metaData.Columns.Add("FK_TABLE_NAME", Type.GetType("System.String"));
//metaData.Columns.Add("ORDINAL", Type.GetType("System.String"));
//metaData.Columns.Add("UPDATE_RULE", Type.GetType("System.String"));
//metaData.Columns.Add("DELETE_RULE", Type.GetType("System.String"));
//metaData.Columns.Add("PK_NAME", Type.GetType("System.String"));
//metaData.Columns.Add("FK_NAME", Type.GetType("System.String"));
//metaData.Columns.Add("DEFERRABILITY", Type.GetType("System.String"));
metaData.Columns.Add("PK_TABLE_CATALOG", Type.GetType("System.String"));
metaData.Columns.Add("PK_TABLE_SCHEMA", Type.GetType("System.String"));
metaData.Columns.Add("FK_TABLE_CATALOG", Type.GetType("System.String"));
metaData.Columns.Add("FK_TABLE_SCHEMA", Type.GetType("System.String"));
metaData.Columns.Add("FK_TABLE_NAME", Type.GetType("System.String"));
metaData.Columns.Add("PK_TABLE_NAME", Type.GetType("System.String"));
metaData.Columns.Add("ORDINAL", Type.GetType("System.Int32"));
metaData.Columns.Add("FK_NAME", Type.GetType("System.String"));
metaData.Columns.Add("PK_NAME", Type.GetType("System.String"));
metaData.Columns.Add("PK_COLUMN_NAME", Type.GetType("System.String"));
metaData.Columns.Add("FK_COLUMN_NAME", Type.GetType("System.String"));
IVistaDBDatabase db = DDA.OpenDatabase(dbName, VistaDBDatabaseOpenMode.NonexclusiveReadOnly, "");
ArrayList tables = db.EnumTables();
IVistaDBTableStructure tblStructure = db.TableStructure(tableName);
foreach (IVistaDBRelationshipInformation relInfo in tblStructure.ForeignKeys)
{
//string[] fColumns = relInfo.ForeignKey.Split(new char[] {';'});
//string[] pColumns = relInfo.p.ForeignKey.pprimaryKey.Split(new char[] {';'});
//for(int i = 0; i < fColumns.GetLength(0); i++)
{
metaData.Rows.Add(new object[]
{
GetDatabaseName(),
DBNull.Value,
DBNull.Value,
DBNull.Value,
tblStructure.Name,
relInfo.PrimaryTable,
0,
relInfo.Name,
"PKEY",
"", //pColumns[i],
""
}); //fColumns[i]});
}
}
return(metaData);
}
public TableSchema[] GetTables(string connectionString, DatabaseSchema database)
{
var tables = new List <TableSchema>();
var extendedProperties = new List <ExtendedProperty>();
using (IVistaDBDatabase vistaDb = GetDatabase(connectionString))
{
if (vistaDb == null)
{
return(tables.ToArray());
}
foreach (string tableName in vistaDb.GetTableNames())
{
IVistaDBTableSchema table = vistaDb.TableSchema(tableName);
if (table == null)
{
continue;
}
extendedProperties.Clear();
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.Description, table.Description));
var tableSchema = new TableSchema(database, table.Name, String.Empty, DateTime.MinValue, extendedProperties.ToArray());
tables.Add(tableSchema);
}
}
return(tables.ToArray());
}
public DataTable GetTables()
{
DataTable metaData = new DataTable();
//metaData.Columns.Add("TABLE_CATALOG", Type.GetType("System.String"));
//metaData.Columns.Add("TABLE_SCHEMA", Type.GetType("System.String"));
metaData.Columns.Add("TABLE_NAME", Type.GetType("System.String"));
//metaData.Columns.Add("TABLE_TYPE", Type.GetType("System.String"));
//metaData.Columns.Add("TABLE_GUID", Type.GetType("System.String"));
metaData.Columns.Add("DESCRIPTION", Type.GetType("System.String"));
//metaData.Columns.Add("TABLE_PROPID", Type.GetType("System.String"));
//metaData.Columns.Add("DATE_CREATED", Type.GetType("System.String"));
//metaData.Columns.Add("DATE_MODIFIED", Type.GetType("System.String"));
IVistaDBDatabase db = DDA.OpenDatabase(dbName, VistaDBDatabaseOpenMode.NonexclusiveReadOnly, "");
ArrayList tables = db.EnumTables();
foreach (string table in tables)
{
IVistaDBTableStructure tblStructure = db.TableStructure(table);
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["TABLE_NAME"] = tblStructure.Name;
row["DESCRIPTION"] = tblStructure.Description;
}
return(metaData);
}
public TableKeySchema[] GetTableKeys(string connectionString, TableSchema table)
{
var keys = new List <TableKeySchema>();
var foreignColumns = new List <string>();
var primaryColumns = new List <string>();
var extendedProperties = new List <ExtendedProperty>();
using (IVistaDBDatabase vistaDb = GetDatabase(connectionString))
{
if (vistaDb == null)
{
return(keys.ToArray());
}
IVistaDBTableSchema vistaTable = vistaDb.TableSchema(table.Name);
if (vistaTable == null)
{
return(keys.ToArray());
}
foreach (IVistaDBRelationshipInformation vistaKey in vistaTable.ForeignKeys)
{
foreignColumns.Clear();
foreignColumns.AddRange(vistaKey.ForeignKey.Split(
new[] { ';' }, StringSplitOptions.RemoveEmptyEntries));
IVistaDBTableSchema vistaPrimaryTable = vistaDb.TableSchema(vistaKey.PrimaryTable);
if (vistaPrimaryTable == null)
{
continue;
}
primaryColumns.Clear();
//find primary key index
foreach (IVistaDBIndexInformation i in vistaPrimaryTable.Indexes)
{
if (i.Primary)
{
primaryColumns.AddRange(i.KeyExpression.Split(
new[] { ';' }, StringSplitOptions.RemoveEmptyEntries));
}
}
extendedProperties.Clear();
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.Description, vistaKey.Description));
var key = new TableKeySchema(
table.Database, vistaKey.Name,
foreignColumns.ToArray(),
string.Empty, vistaKey.ForeignTable,
primaryColumns.ToArray(),
string.Empty, vistaKey.PrimaryTable,
extendedProperties.ToArray());
keys.Add(key);
}
}
return(keys.ToArray());
}
public PrimaryKeySchema GetTablePrimaryKey(string connectionString, TableSchema table)
{
using (IVistaDBDatabase vistaDb = GetDatabase(connectionString))
{
if (vistaDb == null)
{
return(null);
}
IVistaDBTableSchema vistaTable = vistaDb.TableSchema(table.Name);
if (vistaTable == null)
{
return(null);
}
foreach (IVistaDBIndexInformation vistaIndex in vistaTable.Indexes)
{
if (!vistaIndex.Primary)
{
continue;
}
var key = new PrimaryKeySchema(
table, vistaIndex.Name,
vistaIndex.KeyExpression.Split(new[] { ';' }, StringSplitOptions.RemoveEmptyEntries));
return(key);
}
}
return(null);
}
private IVistaDBDatabase GetDatabase(string connectionString)
{
var builder = new VistaDBConnectionStringBuilder(connectionString);
IVistaDBDatabase database = _dda.OpenDatabase(builder.DataSource, builder.OpenMode, builder.Password);
return(database);
}
DataTable IMyMetaPlugin.GetTables(string database)
{
DataTable metaData = new DataTable();
IVistaDBDatabase db = null;
try
{
metaData = context.CreateTablesDataTable();
db = DDA.OpenDatabase(this.GetFullDatabaseName(),
VistaDBDatabaseOpenMode.NonexclusiveReadOnly, "");
ArrayList tables = db.EnumTables();
foreach (string table in tables)
{
IVistaDBTableSchema tblStructure = db.TableSchema(table);
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["TABLE_NAME"] = tblStructure.Name;
row["DESCRIPTION"] = tblStructure.Description;
}
}
finally
{
if (db != null)
{
db.Close();
}
}
return(metaData);
}
private IVistaDBDatabase GetDatabase(string connectionString)
{
var builder = new VistaDBConnectionStringBuilder(connectionString);
var password = !String.IsNullOrWhiteSpace(builder.Password) ? builder.Password : null;
IVistaDBDatabase database = _dda.OpenDatabase(builder.DataSource, builder.OpenMode, password);
return(database);
}
public string GetDatabaseName(string connectionString)
{
using (IVistaDBDatabase vistaDb = GetDatabase(connectionString))
{
//HACK: name is the file name?
return(Path.GetFileNameWithoutExtension(vistaDb.Name));
}
}
public void Open(string connectionString)
{
if (this.db == null)
{
this.connectionString = connectionString;
this.dda = VistaDBEngine.Connections.OpenDDA();
if (!System.IO.File.Exists(connectionString))
{
// create database
this.db = this.dda.CreateDatabase(connectionString, true, null, 0, 0, false);
#region table structures
IVistaDBTableSchema tbSchema = this.db.NewTable("InnerRelations");
tbSchema.AddColumn("id", VistaDBType.BigInt);
tbSchema.DefineColumnAttributes("id", false, false, false, false, null, null);
tbSchema.AddColumn("nParentId", VistaDBType.BigInt);
tbSchema.DefineColumnAttributes("nParentId", false, false, false, false, null, null);
tbSchema.AddColumn("aParentId", VistaDBType.BigInt);
tbSchema.DefineColumnAttributes("aParentId", false, false, false, false, null, null);
tbSchema.AddColumn("qualifierId", VistaDBType.BigInt);
tbSchema.DefineColumnAttributes("qualifierId", false, false, false, false, null, null);
this.tbInnerRelations = this.db.CreateTable(tbSchema, false, false);
tbSchema = this.db.NewTable("RootRelations");
tbSchema.AddColumn("id", VistaDBType.BigInt);
tbSchema.DefineColumnAttributes("id", false, false, false, false, null, null);
tbSchema.AddColumn("terminalValue", VistaDBType.VarChar);
tbSchema.DefineColumnAttributes("terminalValue", false, false, false, false, null, null);
tbSchema.AddColumn("qualifierId", VistaDBType.BigInt);
tbSchema.DefineColumnAttributes("qualifierId", false, false, false, false, null, null);
this.tbRootRelations = this.db.CreateTable(tbSchema, false, false);
#endregion
#region indexes
this.tbInnerRelations.CreateIdentity("id", "1", "1");
this.tbInnerRelations.CreateIndex("findById", "id", true, true);
this.tbInnerRelations.CreateIndex("findByParents", "nParentId;aParentId", false, true);
this.tbInnerRelations.CreateIndex("findByAParent", "aParentId", false, false);
this.tbInnerRelations.CreateIndex("findByQualifier", "qualifierId", false, false);
this.tbRootRelations.CreateIdentity("id", "-1", "-1");
this.tbRootRelations.CreateIndex("findById", "id", true, true);
this.tbRootRelations.CreateIndex("findByTerminalValue", "terminalValue", false, true);
this.tbRootRelations.CreateIndex("findByQualifier", "qualifierId", false, false);
#endregion
}
else
{
// open existing database
this.db = this.dda.OpenDatabase(connectionString, VistaDBDatabaseOpenMode.ExclusiveReadWrite, null);
this.tbInnerRelations = this.db.OpenTable("InnerRelations", false, false);
this.tbRootRelations = this.db.OpenTable("RootRelations", false, false);
}
}
}
public ColumnSchema[] GetTableColumns(string connectionString, TableSchema table)
{
var columns = new List <ColumnSchema>();
var extendedProperties = new List <ExtendedProperty>();
using (IVistaDBDatabase vistaDb = GetDatabase(connectionString))
{
if (vistaDb == null)
{
return(columns.ToArray());
}
IVistaDBTableSchema vistaTable = vistaDb.TableSchema(table.Name);
if (vistaTable == null)
{
return(columns.ToArray());
}
foreach (IVistaDBColumnAttributes vistaColumn in vistaTable)
{
string nativeType = vistaColumn.Type.ToString();
extendedProperties.Clear();
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.Description, vistaColumn.Description));
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.ObjectID, vistaColumn.UniqueId));
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.DefaultValue, ""));
extendedProperties.Add(ExtendedProperty.Readonly("CS_CodePage", vistaColumn.CodePage));
extendedProperties.Add(ExtendedProperty.Readonly("CS_Encrypted", vistaColumn.Encrypted));
extendedProperties.Add(ExtendedProperty.Readonly("CS_Packed", vistaColumn.Packed));
extendedProperties.Add(ExtendedProperty.Readonly("CS_ReadOnly", vistaColumn.ReadOnly));
bool isIdentity = false;
foreach (IVistaDBIdentityInformation identity in vistaTable.Identities)
{
isIdentity = (identity.ColumnName == vistaColumn.Name);
if (isIdentity)
{
break;
}
}
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.IsIdentity, isIdentity));
var column = new ColumnSchema(table, vistaColumn.Name, GetDbType(nativeType),
nativeType, vistaColumn.MaxLength, 0, 0, vistaColumn.AllowNull,
extendedProperties.ToArray());
columns.Add(column);
}
}
return(columns.ToArray());
}
public void Close()
{
if (this.db != null)
{
this.tbRootRelations.Close();
this.tbInnerRelations.Close();
this.db.Close();
this.db = null;
this.dda.Dispose();
this.dda = null;
}
}
List <string> IPlugin.GetPrimaryKeyColumns(string database, string table)
{
List <string> primaryKeys = new List <string>();
IVistaDBDatabase db = null;
try
{
using (VistaDBConnection cn = new VistaDBConnection(context.ConnectionString))
{
db = DDA.OpenDatabase(this.GetFullDatabaseName(), VistaDBDatabaseOpenMode.NonexclusiveReadOnly, GetPassword(cn));
}
IVistaDBTableSchema tblStructure = db.TableSchema(table);
string[] pks = null;
if (tblStructure.Indexes.Contains("PrimaryKey"))
{
pks = tblStructure.Indexes["PrimaryKey"].KeyExpression.Split(';');
}
else
{
foreach (IVistaDBIndexInformation pk in tblStructure.Indexes)
{
if (pk.Primary)
{
pks = pk.KeyExpression.Split(';');
break;
}
}
}
if (pks != null)
{
foreach (string pkColName in pks)
{
primaryKeys.Add(pkColName);
}
}
}
finally
{
if (db != null)
{
db.Close();
}
}
return(primaryKeys);
}
DataTable IPlugin.GetTableIndexes(string database, string table)
{
DataTable metaData = new DataTable();
IVistaDBDatabase db = null;
try
{
metaData = context.CreateIndexesDataTable();
using (VistaDBConnection cn = new VistaDBConnection(context.ConnectionString))
{
db = DDA.OpenDatabase(this.GetFullDatabaseName(), VistaDBDatabaseOpenMode.NonexclusiveReadOnly, GetPassword(cn));
}
ArrayList tables = db.EnumTables();
IVistaDBTableSchema tblStructure = db.TableSchema(table);
foreach (IVistaDBIndexInformation indexInfo in tblStructure.Indexes)
{
string[] pks = indexInfo.KeyExpression.Split(';');
int index = 0;
foreach (string colName in pks)
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["TABLE_CATALOG"] = GetDatabaseName();
row["TABLE_NAME"] = tblStructure.Name;
row["INDEX_CATALOG"] = GetDatabaseName();
row["INDEX_NAME"] = indexInfo.Name;
row["UNIQUE"] = indexInfo.Unique;
row["COLLATION"] = indexInfo.KeyStructure[index++].Descending ? 2 : 1;
row["COLUMN_NAME"] = colName;
}
}
}
finally
{
if (db != null)
{
db.Close();
}
}
return(metaData);
}
public void SetupServer()
{
string path = Path.GetFullPath(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "..\\Data\\coolstorage.vdb3"));
if (File.Exists(path))
{
File.Delete(path);
}
IVistaDBDatabase database = VistaDBEngine.Connections.OpenDDA().CreateDatabase(path, false, null, 0, 0, false);
database.Close();
CSConfig.SetDB(new CSDataProviderVistaDB(@"Data Source=" + path));
CSDatabase.ExecuteNonQuery(_sqlCreateTables);
}
public IndexSchema[] GetTableIndexes(string connectionString, TableSchema table)
{
var indexes = new List <IndexSchema>();
var memberColumns = new List <string>();
var extendedProperties = new List <ExtendedProperty>();
using (IVistaDBDatabase vistaDb = GetDatabase(connectionString))
{
if (vistaDb == null)
{
return(indexes.ToArray());
}
IVistaDBTableSchema vistaTable = vistaDb.TableSchema(table.Name);
if (vistaTable == null)
{
return(indexes.ToArray());
}
foreach (IVistaDBIndexInformation vistaIndex in vistaTable.Indexes)
{
memberColumns.Clear();
foreach (IVistaDBKeyColumn keyColumn in vistaIndex.KeyStructure)
{
memberColumns.Add(vistaTable[keyColumn.RowIndex].Name);
}
extendedProperties.Clear();
extendedProperties.Add(ExtendedProperty.Readonly(ExtendedPropertyNames.Description, vistaIndex.Description));
extendedProperties.Add(ExtendedProperty.Readonly("CS_FastTextSearch", vistaIndex.FullTextSearch));
var index = new IndexSchema(table, vistaIndex.Name, vistaIndex.Primary,
vistaIndex.Unique, false,
memberColumns.ToArray(),
extendedProperties.ToArray());
indexes.Add(index);
}
}
return(indexes.ToArray());
}
public DataTable GetDatabases()
{
DataTable metaData = new DataTable();
metaData.Columns.Add("CATALOG_NAME", Type.GetType("System.String"));
metaData.Columns.Add("DESCRIPTION", Type.GetType("System.String"));
metaData.Columns.Add("SCHEMA_NAME", Type.GetType("System.String"));
metaData.Columns.Add("SCHEMA_OWNER", Type.GetType("System.String"));
metaData.Columns.Add("DEFAULT_CHARACTER_SET_CATALOG", Type.GetType("System.String"));
metaData.Columns.Add("DEFAULT_CHARACTER_SET_SCHEMA", Type.GetType("System.String"));
metaData.Columns.Add("DEFAULT_CHARACTER_SET_NAME", Type.GetType("System.String"));
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
IVistaDBDatabase db = DDA.OpenDatabase(dbName, VistaDBDatabaseOpenMode.NonexclusiveReadOnly, password);
row["CATALOG_NAME"] = GetDatabaseName();
row["DESCRIPTION"] = db.Description;
return(metaData);
}
DataTable IPlugin.GetTables(string database)
{
DataTable metaData = new DataTable();
IVistaDBDatabase db = null;
try
{
metaData = context.CreateTablesDataTable();
using (VistaDBConnection cn = new VistaDBConnection(context.ConnectionString))
{
db = DDA.OpenDatabase(this.GetFullDatabaseName(), VistaDBDatabaseOpenMode.NonexclusiveReadOnly, GetPassword(cn));
}
IVistaDBTableNameCollection tables = db.GetTableNames();
foreach (string table in tables)
{
IVistaDBTableSchema tblStructure = db.TableSchema(table);
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["TABLE_NAME"] = tblStructure.Name;
row["DESCRIPTION"] = tblStructure.Description;
}
}
finally
{
if (db != null)
{
db.Close();
}
}
return(metaData);
}
DataTable IPlugin.GetTableColumns(string database, string table)
{
DataTable metaData = new DataTable();
IVistaDBDatabase db = null;
try
{
metaData = context.CreateColumnsDataTable();
using (VistaDBConnection cn = new VistaDBConnection(context.ConnectionString))
{
db = DDA.OpenDatabase(this.GetFullDatabaseName(), VistaDBDatabaseOpenMode.NonexclusiveReadOnly, GetPassword(cn));
}
ArrayList tables = db.EnumTables();
IVistaDBTableSchema tblStructure = db.TableSchema(table);
foreach (IVistaDBColumnAttributes c in tblStructure)
{
string colName = c.Name;
string def = "";
if (tblStructure.Defaults.Contains(colName))
{
def = tblStructure.Defaults[colName].Expression;
}
int width = c.MaxLength; //c.ColumnWidth;
int dec = 0; //c.ColumnDecimals;
int length = 0;
int octLength = width;
IVistaDBIdentityInformation identity = null;
if (tblStructure.Identities.Contains(colName))
{
identity = tblStructure.Identities[colName];
}
string[] pks = null;
if (tblStructure.Indexes.Contains("PrimaryKey"))
{
pks = tblStructure.Indexes["PrimaryKey"].KeyExpression.Split(';');
}
else
{
foreach (IVistaDBIndexInformation pk in tblStructure.Indexes)
{
if (pk.Primary)
{
pks = pk.KeyExpression.Split(';');
break;
}
}
}
System.Collections.Hashtable pkCols = null;
if (pks != null)
{
pkCols = new Hashtable();
foreach (string pkColName in pks)
{
pkCols[pkColName] = true;
}
}
switch (c.Type)
{
case VistaDBType.Char:
case VistaDBType.NChar:
case VistaDBType.NText:
case VistaDBType.NVarChar:
case VistaDBType.Text:
case VistaDBType.VarChar:
length = width;
width = 0;
dec = 0;
break;
case VistaDBType.Money:
case VistaDBType.Float:
case VistaDBType.Decimal:
case VistaDBType.Real:
break;
default:
width = 0;
dec = 0;
break;
}
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["TABLE_NAME"] = tblStructure.Name;
row["COLUMN_NAME"] = c.Name;
row["ORDINAL_POSITION"] = c.RowIndex;
row["IS_NULLABLE"] = c.AllowNull;
row["COLUMN_HASDEFAULT"] = def == string.Empty ? false : true;
row["COLUMN_DEFAULT"] = def;
row["IS_AUTO_KEY"] = identity == null ? false : true;
row["AUTO_KEY_SEED"] = 1;
row["AUTO_KEY_INCREMENT"] = identity == null ? 0 : Convert.ToInt32(identity.StepExpression);
row["TYPE_NAME"] = c.Type.ToString();
row["NUMERIC_PRECISION"] = width;
row["NUMERIC_SCALE"] = dec;
row["CHARACTER_MAXIMUM_LENGTH"] = length;
row["CHARACTER_OCTET_LENGTH"] = octLength;
row["DESCRIPTION"] = c.Description;
string type = (string)row["TYPE_NAME"];
row["TYPE_NAME_COMPLETE"] = this.GetDataTypeNameComplete(type, length, (short)width, (short)dec);
if (c.Type == VistaDBType.Timestamp)
{
row["IS_COMPUTED"] = true;
}
row["IS_CONCURRENCY"] = type == "Timestamp" ? true : false;
}
}
finally
{
if (db != null)
{
db.Close();
}
}
return(metaData);
}
public void OpenDatabase(bool readOnly)
{
System.Diagnostics.Debug.Assert(dda == null, "The database is already open");
dda = VistaDBEngine.Connections.OpenDDA();
db = dda.OpenDatabase(Path.Combine(basePath, "game.vdb3"),
readOnly ? VistaDBDatabaseOpenMode.NonexclusiveReadOnly : VistaDBDatabaseOpenMode.ExclusiveReadWrite, null);
packTable = db.OpenTable("Pack", false, readOnly);
markerTable = db.OpenTable("Marker", false, readOnly);
cardTable = db.OpenTable("Card", false, readOnly);
cardModelCache = new Dictionary<Guid, CardModel>();
setCache = new Dictionary<Guid, Set>();
}
public void CloseDatabase()
{
packTable.Dispose(); packTable = null;
cardTable.Dispose(); cardTable = null;
markerTable.Dispose(); markerTable = null;
db.Dispose(); db = null;
dda.Dispose(); dda = null;
cardModelCache = null;
setCache = null;
}
public void GetDBCompexInfo(string dbName)
{
try
{
using (IVistaDBDDA conn = VistaDBEngine.Connections.OpenDDA())
{
using (IVistaDBDatabase db = conn.OpenDatabase(dbName, VistaDBDatabaseOpenMode.ExclusiveReadOnly, null))
{
Console.WriteLine("METAINFORMATION FOR " + dbName + " DATABASE");
Console.WriteLine("-------------------------------------------");
Console.WriteLine("Table Description: " + db.Description);
Console.WriteLine("Row count: " + db.RowCount.ToString());
Console.WriteLine("PageSize: " + db.PageSize.ToString());
Console.WriteLine("Open mode: " + db.Mode.ToString());
Console.WriteLine("Culture: " + db.Culture.ToString());
Console.WriteLine("Case Sensitive: " + db.CaseSensitive.ToString());
ArrayList tables = db.EnumTables();
foreach (string table in tables)
{
IVistaDBTableStructure tblStructure = db.TableStructure(table);
Console.WriteLine("============================================");
Console.WriteLine("Table " + table);
Console.WriteLine("============================================");
//columns
Console.WriteLine("COLUMNS:");
foreach (IVistaDBColumnAttributes colInfo in tblStructure)
{
Console.WriteLine("\t" + colInfo.Name);
//use colInfo for getting columns metadata
}
//indexes
Console.WriteLine("INDEXES:");
foreach (IVistaDBIndexInformation indexInfo in tblStructure.Indexes)
{
Console.WriteLine("\t" + indexInfo.Name);
//use indexInfo for getting columns metadata
}
//constraints
Console.WriteLine("CONSTRAINTS:");
foreach (IVistaDBConstraintInformation constrInfo in tblStructure.Constraints)
{
Console.WriteLine("\t" + constrInfo.Name);
//use constrInfo for getting columns metadata
}
//foreignKeys
Console.WriteLine("FOREIGN KEYS:");
foreach (IVistaDBRelationshipInformation relInfo in tblStructure.ForeignKeys)
{
Console.WriteLine("\t" + relInfo.Name);
//use foreignKeys for getting columns metadata
}
}
}
}
}
catch (VistaDBException ex)
{
}
catch
{
}
}
private static void CreatePackTable(IVistaDBDatabase gameDb)
{
var packSchema = gameDb.NewTable("Pack");
packSchema.AddColumn("id", VistaDBType.UniqueIdentifier);
packSchema.DefineColumnAttributes("id", false, false, false, false, null, null);
packSchema.AddColumn("name", VistaDBType.NVarChar, 200);
packSchema.DefineColumnAttributes("name", false, false, false, false, null, null);
packSchema.AddColumn("setId", VistaDBType.UniqueIdentifier);
packSchema.DefineColumnAttributes("setId", false, false, false, false, null, null);
packSchema.AddColumn("xml", VistaDBType.NText);
using (var packTable = gameDb.CreateTable(packSchema, false, false))
{
packTable.CreateIndex("PackPK", "id", true, true);
packTable.CreateIndex("SetIX", "setId", false, false);
packTable.CreateForeignKey("PackSetFK", "setId", "Set", VistaDBReferentialIntegrity.Cascade, VistaDBReferentialIntegrity.Cascade, null);
}
}
DataTable IPlugin.GetForeignKeys(string database, string tableName)
{
DataTable metaData = new DataTable();
IVistaDBDatabase db = null;
try
{
metaData = context.CreateForeignKeysDataTable();
using (VistaDBConnection cn = new VistaDBConnection(context.ConnectionString))
{
db = DDA.OpenDatabase(this.GetFullDatabaseName(), VistaDBDatabaseOpenMode.NonexclusiveReadOnly, GetPassword(cn));
}
ArrayList tables = db.EnumTables();
foreach (string table in tables)
{
IVistaDBTableSchema tblStructure = db.TableSchema(table);
//==================================================================
// This works around a change that was made to the VistaDB provider
// It's ugly, we know
//==================================================================
IEnumerator enumerator = null;
if (useOldForeignKeyWay)
{
enumerator = tblStructure.ForeignKeys.GetEnumerator();
}
else
{
try
{
enumerator = tblStructure.ForeignKeys.Values.GetEnumerator();
}
catch
{
enumerator = tblStructure.ForeignKeys.GetEnumerator();
useOldForeignKeyWay = true;
}
}
// Okay, now that the version issues are over we just use the 'enumerator'
while (enumerator.MoveNext())
{
IVistaDBRelationshipInformation relInfo = enumerator.Current as IVistaDBRelationshipInformation;
if (relInfo.ForeignTable != tableName && relInfo.PrimaryTable != tableName)
{
continue;
}
string fCols = relInfo.ForeignKey;
string pCols = String.Empty;
string primaryTbl = relInfo.PrimaryTable;
string pkName = "";
using (IVistaDBTableSchema pkTableStruct = db.TableSchema(primaryTbl))
{
foreach (IVistaDBIndexInformation idxInfo in pkTableStruct.Indexes)
{
if (!idxInfo.Primary)
{
continue;
}
pkName = idxInfo.Name;
pCols = idxInfo.KeyExpression;
break;
}
}
string [] fColumns = fCols.Split(';');
string [] pColumns = pCols.Split(';');
for (int i = 0; i < fColumns.GetLength(0); i++)
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["PK_TABLE_CATALOG"] = GetDatabaseName();
row["PK_TABLE_SCHEMA"] = DBNull.Value;
row["FK_TABLE_CATALOG"] = DBNull.Value;
row["FK_TABLE_SCHEMA"] = DBNull.Value;
row["FK_TABLE_NAME"] = tblStructure.Name;
row["PK_TABLE_NAME"] = relInfo.PrimaryTable;
row["ORDINAL"] = 0;
row["FK_NAME"] = relInfo.Name;
row["PK_NAME"] = pkName;
row["PK_COLUMN_NAME"] = pColumns[i];
row["FK_COLUMN_NAME"] = fColumns[i];
row["UPDATE_RULE"] = relInfo.UpdateIntegrity;
row["DELETE_RULE"] = relInfo.DeleteIntegrity;
}
}
}
}
finally
{
if (db != null)
{
db.Close();
}
}
return(metaData);
}
DataTable IMyMetaPlugin.GetForeignKeys(string database, string tableName)
{
DataTable metaData = new DataTable();
IVistaDBDatabase db = null;
try
{
metaData = context.CreateForeignKeysDataTable();
db = DDA.OpenDatabase(this.GetFullDatabaseName(),
VistaDBDatabaseOpenMode.NonexclusiveReadOnly, "");
ArrayList tables = db.EnumTables();
foreach (string table in tables)
{
IVistaDBTableSchema tblStructure = db.TableSchema(table);
foreach (IVistaDBRelationshipInformation relInfo in tblStructure.ForeignKeys)
{
if (relInfo.ForeignTable != tableName && relInfo.PrimaryTable != tableName)
{
continue;
}
string fCols = relInfo.ForeignKey;
string pCols = String.Empty;
string primaryTbl = relInfo.PrimaryTable;
string pkName = "";
using (IVistaDBTableSchema pkTableStruct = db.TableSchema(primaryTbl))
{
foreach (IVistaDBIndexInformation idxInfo in pkTableStruct.Indexes)
{
if (!idxInfo.Primary)
{
continue;
}
pkName = idxInfo.Name;
pCols = idxInfo.KeyExpression;
break;
}
}
string [] fColumns = fCols.Split(';');
string [] pColumns = pCols.Split(';');
for (int i = 0; i < fColumns.GetLength(0); i++)
{
DataRow row = metaData.NewRow();
metaData.Rows.Add(row);
row["PK_TABLE_CATALOG"] = GetDatabaseName();
row["PK_TABLE_SCHEMA"] = DBNull.Value;
row["FK_TABLE_CATALOG"] = DBNull.Value;
row["FK_TABLE_SCHEMA"] = DBNull.Value;
row["FK_TABLE_NAME"] = tblStructure.Name;
row["PK_TABLE_NAME"] = relInfo.PrimaryTable;
row["ORDINAL"] = 0;
row["FK_NAME"] = relInfo.Name;
row["PK_NAME"] = pkName;
row["PK_COLUMN_NAME"] = pColumns[i];
row["FK_COLUMN_NAME"] = fColumns[i];
row["UPDATE_RULE"] = relInfo.UpdateIntegrity;
row["DELETE_RULE"] = relInfo.DeleteIntegrity;
}
}
}
}
finally
{
if (db != null)
{
db.Close();
}
}
return(metaData);
}
public DataTable GetColumns(string tableName)
{
DataTable metaData = new DataTable();
//metaData.Columns.Add("TABLE_CATALOG", Type.GetType("System.String"));
//metaData.Columns.Add("TABLE_SCHEMA", Type.GetType("System.String"));
//metaData.Columns.Add("TABLE_NAME", Type.GetType("System.String"));
//metaData.Columns.Add("COLUMN_NAME", Type.GetType("System.String"));
//metaData.Columns.Add("COLUMN_GUID", Type.GetType("System.String"));
//metaData.Columns.Add("COLUMN_PROPID", Type.GetType("System.String"));
//metaData.Columns.Add("ORDINAL_POSITION", Type.GetType("System.String"));
//metaData.Columns.Add("COLUMN_HASDEFAULT", Type.GetType("System.String"));
//metaData.Columns.Add("COLUMN_DEFAULT", Type.GetType("System.String"));
//metaData.Columns.Add("COLUMN_FLAGS", Type.GetType("System.String"));
//metaData.Columns.Add("IS_NULLABLE", Type.GetType("System.String"));
//metaData.Columns.Add("DATA_TYPE", Type.GetType("System.String"));
//metaData.Columns.Add("TYPE_GUID", Type.GetType("System.String"));
//metaData.Columns.Add("CHARACTER_MAXIMUM_LENGTH", Type.GetType("System.String"));
//metaData.Columns.Add("CHARACTER_OCTET_LENGTH", Type.GetType("System.String"));
//metaData.Columns.Add("NUMERIC_PRECISION", Type.GetType("System.String"));
//metaData.Columns.Add("NUMERIC_SCALE", Type.GetType("System.String"));
//metaData.Columns.Add("DATETIME_PRECISION", Type.GetType("System.String"));
//metaData.Columns.Add("CHARACTER_SET_CATALOG", Type.GetType("System.String"));
//metaData.Columns.Add("CHARACTER_SET_SCHEMA", Type.GetType("System.String"));
//metaData.Columns.Add("CHARACTER_SET_NAME", Type.GetType("System.String"));
//metaData.Columns.Add("COLLATION_CATALOG", Type.GetType("System.String"));
//metaData.Columns.Add("COLLATION_SCHEMA", Type.GetType("System.String"));
//metaData.Columns.Add("COLLATION_NAME", Type.GetType("System.String"));
//metaData.Columns.Add("DOMAIN_CATALOG", Type.GetType("System.String"));
//metaData.Columns.Add("DOMAIN_SCHEMA", Type.GetType("System.String"));
//metaData.Columns.Add("DOMAIN_NAME", Type.GetType("System.String"));
//metaData.Columns.Add("DESCRIPTION", Type.GetType("System.String"));
//metaData.Columns.Add("COLUMN_LCID", Type.GetType("System.String"));
//metaData.Columns.Add("COLUMN_COMPFLAGS", Type.GetType("System.String"));
//metaData.Columns.Add("COLUMN_SORTID", Type.GetType("System.String"));
//metaData.Columns.Add("COLUMN_TDSCOLLATION", Type.GetType("System.String"));
//metaData.Columns.Add("IS_COMPUTED", Type.GetType("System.String"));
//metaData.Columns.Add("IS_AUTO_KEY", Type.GetType("System.String"));
//metaData.Columns.Add("AUTO_KEY_SEED", Type.GetType("System.String"));
//metaData.Columns.Add("AUTO_KEY_INCREMENT", Type.GetType("System.String"));
metaData.Columns.Add("TABLE_NAME", Type.GetType("System.String"));
metaData.Columns.Add("COLUMN_NAME", Type.GetType("System.String"));
metaData.Columns.Add("ORDINAL_POSITION", Type.GetType("System.Int32"));
metaData.Columns.Add("IS_NULLABLE", Type.GetType("System.Boolean"));
metaData.Columns.Add("COLUMN_HASDEFAULT", Type.GetType("System.Boolean"));
metaData.Columns.Add("COLUMN_DEFAULT", Type.GetType("System.String"));
metaData.Columns.Add("IS_AUTO_KEY", Type.GetType("System.Boolean"));
metaData.Columns.Add("AUTO_KEY_SEED", Type.GetType("System.Int32"));
metaData.Columns.Add("AUTO_KEY_INCREMENT", Type.GetType("System.Int32"));
metaData.Columns.Add("DATA_TYPE_NAME", Type.GetType("System.String"));
metaData.Columns.Add("NUMERIC_PRECISION", Type.GetType("System.Int32"));
metaData.Columns.Add("NUMERIC_SCALE", Type.GetType("System.Int32"));
metaData.Columns.Add("CHARACTER_MAXIMUM_LENGTH", Type.GetType("System.Int32"));
metaData.Columns.Add("CHARACTER_OCTET_LENGTH", Type.GetType("System.Int32"));
metaData.Columns.Add("DESCRIPTION", Type.GetType("System.String"));
metaData.Columns.Add("IS_PRIMARY_KEY", Type.GetType("System.Boolean"));
IVistaDBDatabase db = DDA.OpenDatabase(dbName, VistaDBDatabaseOpenMode.NonexclusiveReadOnly, "");
ArrayList tables = db.EnumTables();
IVistaDBTableStructure tblStructure = db.TableStructure(tableName);
foreach (IVistaDBColumnAttributes c in tblStructure)
{
string colName = c.Name;
string def = "";
if (tblStructure.Defaults.Contains(colName))
{
def = tblStructure.Defaults[colName].Expression;
}
int width = c.MaxLength; //c.ColumnWidth;
int dec = 0; //c.ColumnDecimals;
int length = 0;
int octLength = width;
IVistaDBIdentityInformation identity = null;
if (tblStructure.Identities.Contains(colName))
{
identity = tblStructure.Identities[colName];
}
string[] pks = null;
if (tblStructure.Indexes.Contains("PrimaryKey"))
{
pks = tblStructure.Indexes["PrimaryKey"].KeyExpression.Split(',');
}
else
{
foreach (IVistaDBIndexInformation pk in tblStructure.Indexes)
{
if (pk.Primary)
{
pks = pk.KeyExpression.Split(',');
break;
}
}
}
System.Collections.Hashtable pkCols = null;
if (pks != null)
{
pkCols = new Hashtable();
foreach (string pkColName in pks)
{
pkCols[pkColName] = true;
}
}
switch (c.Type)
{
case VistaDBType.Char:
case VistaDBType.NChar:
case VistaDBType.NText:
case VistaDBType.NVarchar:
case VistaDBType.Text:
case VistaDBType.Varchar:
length = width;
width = 0;
dec = 0;
break;
case VistaDBType.Currency:
case VistaDBType.Double:
case VistaDBType.Decimal:
case VistaDBType.Single:
break;
default:
width = 0;
dec = 0;
break;
}
metaData.Rows.Add(new object[]
{
tblStructure.Name,
c.Name,
c.RowIndex,
c.AllowNull,
def == string.Empty ? false : true,
def,
identity == null ? false : true,
1,
identity == null ? 0 : Convert.ToInt32(identity.StepExpression),
c.Type.ToString(),
width,
dec,
length,
octLength,
c.Description,
pkCols == null ? false : pkCols.Contains(colName)
});
}
return(metaData);
}
/// <summary>
/// Turns off auto record identity generation so the record id from the master database can be used.
/// </summary>
/// <param name="tableName">SQL table name</param>
/// <param name="columnName">the identity column</param>
/// <param name="enable"></param>
public void SetIdentityInsert(string tableName, string columnName, bool enable)
{
// VistaDB DDA code
IVistaDBTable tbl = null;
try
{
IVistaDBDDA DDAObj = VistaDBEngine.Connections.OpenDDA();
string vistaDbPath = (string)AppDomain.CurrentDomain.GetData("DataDirectory");
Debug.WriteLine("FormsSql.SetIdentityInsert VistaDbPath: " + vistaDbPath);
// IVistaDBDatabase db = DDAObj.OpenDatabase(System.AppDomain.CurrentDomain.BaseDirectory + "App_Data\\forms.vdb5", VistaDB.VistaDBDatabaseOpenMode.NonexclusiveReadWrite, "aj80995");
IVistaDBDatabase db = DDAObj.OpenDatabase(vistaDbPath + @"\forms.vdb5", VistaDB.VistaDBDatabaseOpenMode.NonexclusiveReadWrite, "P@ssword1");
tbl = db.OpenTable(tableName, false, false);
}
catch (Exception xcptn)
{
Debug.WriteLine("FormsSql.SetIdentityInsert Exception: " + xcptn.Message);
throw xcptn;
}
// table1s.AddColumn("ID", VistaDBType.Int);
// table1s.DefineColumnAttributes("ID", false, false, false, false, null, null);
// table1s.AddColumn("COLINT", VistaDBType.Int);
// table1s.DefineColumnAttributes("COLINT", false, false, false, false, null, null);
if (tbl.EnforceIdentities)
{
if (enable)
{
tbl.DropIdentity(columnName);
}
else
{
if (tableName == "def_FormResults")
{
int min = getMin(columnName, tableName);
int next = min - 1;
tbl.CreateIdentity(columnName, next.ToString(), "-1");
}
else
{
int max = getMax(columnName, tableName);
int next = max + 1;
tbl.CreateIdentity(columnName, next.ToString(), "1");
}
}
}
else
{
if (!enable)
{
if (tableName == "def_FormResults")
{
int min = getMin(columnName, tableName);
int next = min - 1;
tbl.CreateIdentity(columnName, next.ToString(), "-1");
}
else
{
int max = getMax(columnName, tableName);
int next = max + 1;
tbl.CreateIdentity(columnName, next.ToString(), "1");
}
}
}
// tbl.CreateIndex("Primary", "ID", true, true);
// tbl.CreateIndex("idxDate", "COLDATETIME", false, false);
tbl.Close();
tbl.Dispose();
tbl = null;
}
/// <summary>
/// Returns a page of errors from the databse in descending order
/// of logged time.
/// </summary>
public override int GetErrors(int pageIndex, int pageSize, IList errorEntryList)
{
if (pageIndex < 0)
{
throw new ArgumentOutOfRangeException("pageIndex", pageIndex, null);
}
if (pageSize < 0)
{
throw new ArgumentOutOfRangeException("pageSize", pageSize, null);
}
VistaDBConnectionStringBuilder builder = new VistaDBConnectionStringBuilder(_connectionString);
// Use the VistaDB Direct Data Access objects
IVistaDBDDA ddaObjects = VistaDBEngine.Connections.OpenDDA();
// Create a connection object to a VistaDB database
IVistaDBDatabase vistaDB = ddaObjects.OpenDatabase(_databasePath, builder.OpenMode, builder.Password);
// Open the table
IVistaDBTable elmahTable = vistaDB.OpenTable("ELMAH_Error", false, true);
elmahTable.ActiveIndex = "IX_ELMAH_Error_App_Time_Id";
if (errorEntryList != null)
{
if (!elmahTable.EndOfTable)
{
// move to the correct record
elmahTable.First();
elmahTable.MoveBy(pageIndex * pageSize);
int rowsProcessed = 0;
// Traverse the table to get the records we want
while (!elmahTable.EndOfTable && rowsProcessed < pageSize)
{
rowsProcessed++;
string id = Convert.ToString(elmahTable.Get("ErrorId").Value, CultureInfo.InvariantCulture);
Error error = new Error();
error.ApplicationName = (string)elmahTable.Get("Application").Value;
error.HostName = (string)elmahTable.Get("Host").Value;
error.Type = (string)elmahTable.Get("Type").Value;
error.Source = (string)elmahTable.Get("Source").Value;
error.Message = (string)elmahTable.Get("Message").Value;
error.User = (string)elmahTable.Get("User").Value;
error.StatusCode = (int)elmahTable.Get("StatusCode").Value;
error.Time = ((DateTime)elmahTable.Get("TimeUtc").Value).ToLocalTime();
errorEntryList.Add(new ErrorLogEntry(this, id, error));
// move to the next record
elmahTable.Next();
}
}
}
return(Convert.ToInt32(elmahTable.RowCount));
}
