/// <summary>
/// Packs the Vista database to correct fragmenting and improve integrity. All connections must be closed for this process to work.
/// </summary>
private void PackingFunction()
{
Debug.WriteLine("* * * Account Venture PackingFunction");
try
{
using (DbConnection connection = Data.Concrete.UasAdo.GetUasAdoConnection())
{
IVistaDBDDA DDAObj = VistaDB.DDA.VistaDBEngine.Connections.OpenDDA();
// Rebuild the Vista DB path. IVistaDBDDA doesn't like the | character in the web config set up.
string localAppData = Environment.ExpandEnvironmentVariables("%LOCALAPPDATA%");
string ventureVersion = Session["venture_version"].ToString();
string databaseName = connection.DataSource.Substring(connection.DataSource.LastIndexOf(@"\") + 1);
string connectionPath = (connection.DataSource.Contains("|")) ? Path.Combine(localAppData, @"AAIDD\Venture", ventureVersion, databaseName)
: connection.DataSource;
Debug.WriteLine("* * * Account Venture PackingFunction database path: " + connectionPath);
DDAObj.PackDatabase(connectionPath, "P@ssword1", true, new OperationCallbackDelegate(this.OnPackInfo));
}
}
catch (Exception excptn)
{
Debug.WriteLine("* * * Account Venture PackingFunction error: " + excptn.Message);
}
}
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 void Close()
{
if (this.db != null)
{
this.tbRootRelations.Close();
this.tbInnerRelations.Close();
this.db.Close();
this.db = null;
this.dda.Dispose();
this.dda = null;
}
}
/// <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;
}
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 void CreateGameDatabase(IVistaDBDDA dda, string gamePath, IEnumerable<PropertyDef> properties)
{
string dbFile = Path.Combine(gamePath, "game.vdb3");
if (File.Exists(dbFile)) File.Delete(dbFile);
using (var gameDb = dda.CreateDatabase(dbFile, true, null, 0, 0, false))
{
// Table 'Set'
var setsSchema = gameDb.NewTable("Set");
setsSchema.AddColumn("id", VistaDBType.UniqueIdentifier);
setsSchema.DefineColumnAttributes("id", false, false, false, false, null, null);
setsSchema.AddColumn("name", VistaDBType.NVarChar, 200);
setsSchema.DefineColumnAttributes("name", false, false, false, false, null, null);
setsSchema.AddColumn("gameVersion", VistaDBType.NVarChar, 30);
setsSchema.DefineColumnAttributes("gameVersion", false, false, false, false, null, null);
setsSchema.AddColumn("version", VistaDBType.NVarChar, 30);
setsSchema.DefineColumnAttributes("version", false, false, false, false, null, null);
setsSchema.AddColumn("package", VistaDBType.NVarChar, 300);
setsSchema.DefineColumnAttributes("package", false, false, false, false, null, null);
using (var setTable = gameDb.CreateTable(setsSchema, false, false))
{
setTable.CreateIndex("SetPK", "id", true, true);
}
// Table 'Card'
var cardsSchema = gameDb.NewTable("Card");
cardsSchema.AddColumn("id", VistaDBType.UniqueIdentifier);
cardsSchema.DefineColumnAttributes("id", false, false, false, false, null, null);
cardsSchema.AddColumn("name", VistaDBType.NVarChar, 200);
cardsSchema.DefineColumnAttributes("name", false, false, false, false, null, null);
cardsSchema.AddColumn("image", VistaDBType.NVarChar, 200);
cardsSchema.DefineColumnAttributes("image", false, false, false, false, null, null);
cardsSchema.AddColumn("setId", VistaDBType.UniqueIdentifier);
cardsSchema.DefineColumnAttributes("setId", false, false, false, false, null, null);
foreach (PropertyDef prop in properties)
{
switch (prop.Type)
{
case PropertyType.Char:
cardsSchema.AddColumn(prop.Name, VistaDBType.NChar, 1); break;
case PropertyType.Integer:
cardsSchema.AddColumn(prop.Name, VistaDBType.Int); break;
case PropertyType.String:
cardsSchema.AddColumn(prop.Name, VistaDBType.NVarChar, 4000); break;
default:
throw new ArgumentOutOfRangeException("Unknown data type: " + prop.Type.ToString());
}
cardsSchema.DefineColumnAttributes(prop.Name, true, false, false, false, null, "Custom property");
}
using (var gameTable = gameDb.CreateTable(cardsSchema, false, false))
{
gameTable.CreateIndex("CardPK", "id", true, true);
gameTable.CreateIndex("CardNameIX", "name", false, false);
gameTable.CreateForeignKey("CardSetFK", "setId", "Set", VistaDBReferentialIntegrity.Cascade, VistaDBReferentialIntegrity.Cascade, null);
}
// Table 'Marker'
var markerSchema = gameDb.NewTable("Marker");
markerSchema.AddColumn("id", VistaDBType.UniqueIdentifier);
markerSchema.DefineColumnAttributes("id", false, false, false, false, null, null);
markerSchema.AddColumn("name", VistaDBType.NVarChar, 200);
markerSchema.DefineColumnAttributes("name", false, false, false, false, null, null);
markerSchema.AddColumn("icon", VistaDBType.NVarChar, 200);
markerSchema.DefineColumnAttributes("icon", false, false, false, false, null, null);
markerSchema.AddColumn("setId", VistaDBType.UniqueIdentifier);
markerSchema.DefineColumnAttributes("setId", false, false, false, false, null, null);
using (var markerTable = gameDb.CreateTable(markerSchema, false, false))
{
markerTable.CreateIndex("MarkerPK", "id; setId", true, true);
markerTable.CreateForeignKey("MarkerSetFK", "setId", "Set", VistaDBReferentialIntegrity.Cascade, VistaDBReferentialIntegrity.Cascade, null);
}
// Table 'Pack'
CreatePackTable(gameDb);
}
}
/// <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));
}
