public void SqlTestDatabase_CreateDelete()
{
SqlTestDatabase dbTest;
SqlConnectionInfo conInfo;
SqlContext ctx = null;
SqlCommand cmd;
DataTable dt;
bool found;
// Verify that the test database is created.
dbTest = SqlTestDatabase.Create();
try
{
conInfo = dbTest.ConnectionInfo;
ctx = new SqlContext(conInfo);
ctx.Open();
cmd = ctx.CreateCommand("exec sp_databases");
dt = ctx.ExecuteTable(cmd);
found = false;
for (int i = 0; i < dt.Rows.Count; i++)
{
if (String.Compare(SqlHelper.AsString(dt.Rows[i]["DATABASE_NAME"]), SqlTestDatabase.DefTestDatabase, true) == 0)
{
found = true;
break;
}
}
Assert.IsTrue(found);
}
finally
{
if (ctx != null)
{
ctx.Close();
ctx = null;
}
dbTest.Dispose();
}
// Verify that the test database was deleted
conInfo.Database = "MASTER";
ctx = new SqlContext(conInfo);
ctx.Open();
try
{
cmd = ctx.CreateCommand("exec sp_databases");
dt = ctx.ExecuteTable(cmd);
found = false;
for (int i = 0; i < dt.Rows.Count; i++)
{
if (String.Compare(SqlHelper.AsString(dt.Rows[i]["DATABASE_NAME"]), SqlTestDatabase.DefTestDatabase, true) == 0)
{
found = true;
break;
}
}
Assert.IsFalse(found);
}
finally
{
if (ctx != null)
{
ctx.Close();
ctx = null;
}
}
}
/// <summary>
/// Installs the package returning true on success.
/// </summary>
private bool Install()
{
Package package = wizard.Package;
PackageEntry schemaFile = package["/Schema/Schema.sql"];
PackageEntry delProcFile = package["/Schema/DeleteProcs.sql"];
PackageEntry grantFile = package["/Schema/GrantAccess.sql"];
PackageEntry funcFolder = package["/Funcs"];
PackageEntry procFolder = package["/Procs"];
int opCount;
string script;
SqlConnection sqlCon;
QueryDisposition[] qd;
if (schemaFile == null || !schemaFile.IsFile)
{
MessageBox.Show("Invalid Database Package: /Schema/Schema.sql missing.",
wizard.SetupTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
if (delProcFile == null || !delProcFile.IsFile)
{
MessageBox.Show("Invalid Database Package: /Schema/DeleteProcs.sql missing.",
wizard.SetupTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
if (grantFile == null || !grantFile.IsFile)
{
MessageBox.Show("Invalid Database Package: /Schema/GrantAccess.sql missing.",
wizard.SetupTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
// Count the number of operations we're going to perform and
// initialize the progress bar.
opCount = 0;
opCount++; // Delete functions and stored procedures
opCount++; // Run the schema script
opCount++; // Create database user
opCount++; // Grant access
if (funcFolder != null)
{
opCount += funcFolder.Children.Length;
}
if (procFolder != null)
{
opCount += procFolder.Children.Length;
}
progressBar.Minimum = 0;
progressBar.Maximum = opCount;
progressBar.Step = 1;
sqlCon = new SqlConnection(conString);
try
{
sqlCon.Open();
}
catch (Exception e)
{
Append("Error: " + e.Message);
return(false);
}
try
{
// Remove the functions and procedures
Append("Removing functions and procedures");
script = Helper.FromAnsi(delProcFile.GetContents());
qd = new SqlScriptRunner(script).Run(sqlCon, true);
for (int i = 0; i < qd.Length; i++)
{
if (qd[i].Message != null)
{
Append(qd[i].Message);
return(false);
}
}
progressBar.Value++;
// Create the schema
Append("Creating the database schema");
script = Helper.FromAnsi(schemaFile.GetContents());
qd = new SqlScriptRunner(script).Run(sqlCon, true);
for (int i = 0; i < qd.Length; i++)
{
if (qd[i].Message != null)
{
Append(qd[i].Message);
return(false);
}
}
progressBar.Value++;
// Add the functions
if (funcFolder != null)
{
foreach (var file in funcFolder.Children)
{
if (!file.IsFile)
{
continue;
}
Append("Adding: {0}", file.Name);
script = Helper.FromAnsi(file.GetContents());
qd = new SqlScriptRunner(script).Run(sqlCon, true);
for (int i = 0; i < qd.Length; i++)
{
if (qd[i].Message != null)
{
Append(qd[i].Message);
return(false);
}
}
progressBar.Value++;
}
}
// Add the procedures
if (procFolder != null)
{
foreach (var file in procFolder.Children)
{
if (!file.IsFile)
{
continue;
}
Append("Adding: {0}", file.Name);
script = Helper.FromAnsi(file.GetContents());
qd = new SqlScriptRunner(script).Run(sqlCon, true);
for (int i = 0; i < qd.Length; i++)
{
if (qd[i].Message != null)
{
Append(qd[i].Message);
return(false);
}
}
progressBar.Value++;
}
}
// Create a database user for the login if necessary
SqlContext ctx = new SqlContext(conString);
DataTable dt;
try
{
ctx.Open();
dt = ctx.ExecuteTable(ctx.CreateCommand("select name from sysusers where name='{0}'", wizard.SetupState["account"]));
if (dt.Rows.Count == 0)
{
// The database user doesn't already exist, so create one.
Append("Creating database user: {0}", wizard.SetupState["account"]);
ctx.Execute(ctx.CreateCommand("create user {0} from login {0}", wizard.SetupState["account"]));
}
}
catch (Exception e)
{
ctx.Close();
Append("Error: " + e.Message);
return(false);
}
progressBar.Value++;
// Grant access to the application account
Append("Granting access to: {0}", wizard.SetupState["account"]);
script = Helper.FromAnsi(grantFile.GetContents());
script = script.Replace("%account%", wizard.SetupState["account"]);
qd = new SqlScriptRunner(script).Run(sqlCon, true);
for (int i = 0; i < qd.Length; i++)
{
if (qd[i].Message != null)
{
Append(qd[i].Message);
return(false);
}
}
progressBar.Value++;
}
catch (Exception e)
{
Append("Error: " + e.Message);
return(false);
}
finally
{
sqlCon.Close();
}
return(true);
}
/// <summary>
/// Called when the step is deactivated.
/// </summary>
/// <param name="steps">The step list.</param>
/// <param name="forward"><c>true</c> if we're stepping forward in the wizard.</param>
/// <returns><c>true</c> if the transition can proceed.</returns>
public bool OnStepOut(WizardStepList steps, bool forward)
{
if (!forward)
{
return(true);
}
string database;
database = (string)databaseList.SelectedItem;
if (database == null)
{
MessageBox.Show("Select a database from the list or click Create\r\nto create a new one.", wizard.SetupTitle,
MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
else if (database.ToUpper() == "MASTER")
{
MessageBox.Show("Cannot install into the MASTER database.", wizard.SetupTitle,
MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
// Take a look at the database and ensure that it is either empty
// or is already associated with this product ID and database type.
SqlContext ctx;
SqlCommand cmd;
DataTable dt;
WaitForm waitForm;
string cs;
wizard.Enabled = false;
this.Update();
waitForm = new WaitForm(string.Format("Examining database [{0}]...", database));
waitForm.TopLevel = true;
waitForm.Show();
waitForm.Update();
Thread.Sleep(2000);
cs = string.Format("server={0};database={1};uid={2};pwd={3}",
wizard.SetupState["server"],
database,
wizard.SetupState["adminAccount"],
wizard.SetupState["adminPassword"]);
ctx = new SqlContext(cs);
try
{
ctx.Open();
// I'm going to determine whether the database is empty or
// not by looking at the sysobjects table. We'll consider
// it to be not empty if any these conditions are true:
//
// 1. Any user tables are present whose names
// don't begin with "dt".
//
// 2. Any stored procedures or functions are present
// whose names don't begin with "dt".
cmd = ctx.CreateCommand("select 1 from sysobjects where (xtype='U' or xtype='P' or xtype='FN') and name not like 'dt%'");
dt = ctx.ExecuteTable(cmd);
if (dt.Rows.Count == 0)
{
// The database appears to be empty.
wizard.SetupState["Action"] = "Install";
}
else
{
// The database appears to be not empty. Try calling the
// GetProductInfo procedure. If this fails then assume that
// the database belongs to some other application. If it
// succeeds then check the productID and database type against
// the setup settings.
try
{
cmd = ctx.CreateSPCall("GetProductInfo");
dt = ctx.ExecuteTable(cmd);
// Compare the database's product ID and database type to
// the setup settings.
if (SqlHelper.AsString(dt.Rows[0]["ProductID"]) != wizard.SetupState["productID"] ||
SqlHelper.AsString(dt.Rows[0]["DatabaseType"]) != wizard.SetupState["databaseType"])
{
wizard.Enabled = true;
waitForm.Close();
MessageBox.Show(string.Format("Database [{0}] is configured for use by [{1}:{2}].\r\n\r\nPlease select a different database.",
database,
SqlHelper.AsString(dt.Rows[0]["ProductName"]),
SqlHelper.AsString(dt.Rows[0]["DatabaseType"])),
wizard.SetupTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
// The database looks like can accept the installation.
wizard.SetupState["Action"] = "Upgrade";
wizard.SetupState["CurSchemaVersion"] = SqlHelper.AsString(dt.Rows[0]["SchemaVersion"]);
}
catch
{
wizard.Enabled = true;
waitForm.Close();
MessageBox.Show(string.Format("Database [{0}] is not empty and appears to in use by another application.\r\n\r\nPlease select a different database.", database),
wizard.SetupTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}
}
catch (Exception e)
{
wizard.Enabled = true;
waitForm.Close();
MessageBox.Show("Setup could not connect to the database. Please check\r\nthe server name and account settings.\r\n\r\n" + e.Message,
wizard.SetupTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
finally
{
ctx.Close();
}
wizard.Enabled = true;
waitForm.Close();
// Success!
wizard.SetupState["database"] = database;
wizard.SetupState["connectionString"] = string.Format("server={0};database={1};uid={2};pwd={3}",
wizard.SetupState["server"],
wizard.SetupState["database"],
wizard.SetupState["adminAccount"],
wizard.SetupState["adminPassword"]);
this.Hide();
return(true);
}
public void SqlTestDatabase_DeleteExisting()
{
SqlTestDatabase dbTest;
SqlConnectionInfo conInfo;
string database;
SqlContext ctx = null;
SqlCommand cmd;
DataTable dt;
bool found;
// Create a default test database and create a table within it.
dbTest = SqlTestDatabase.Create();
conInfo = dbTest.ConnectionInfo;
database = conInfo.Database;
dbTest.Dispose();
conInfo.Database = "MASTER";
ctx = new SqlContext(conInfo);
ctx.Open();
try
{
cmd = ctx.CreateCommand("create database [{0}]", database);
ctx.Execute(cmd);
ctx.Close();
ctx = null;
conInfo.Database = database;
ctx = new SqlContext(conInfo);
ctx.Open();
cmd = ctx.CreateCommand("create table Test (field1 int)");
ctx.Execute(cmd);
}
finally
{
if (ctx != null)
{
ctx.Close();
ctx = null;
}
}
// OK, now use SqlTestDatabase to create a new database and verify
// that it actually deleted the old database by checking to see that
// the table we created above no longer exists.
dbTest = SqlTestDatabase.Create();
try
{
conInfo = dbTest.ConnectionInfo;
ctx = new SqlContext(conInfo);
ctx.Open();
cmd = ctx.CreateCommand("exec sp_tables");
dt = ctx.ExecuteTable(cmd);
found = false;
for (int i = 0; i < dt.Rows.Count; i++)
{
if (String.Compare(SqlHelper.AsString(dt.Rows[i]["TABLE_NAME"]), "Test", true) == 0)
{
found = true;
break;
}
}
Assert.IsFalse(found);
}
finally
{
if (ctx != null)
{
ctx.Close();
ctx = null;
}
dbTest.Dispose();
}
}
public void SentinelServiceDB_DeployDB()
{
SqlTestDatabase dbTest;
Package dbPackage = null;
DBPackageInstaller dbInstaller;
DBInstallParams dbParams;
DBInstallResult result;
using (dbTest = SqlTestDatabase.Create())
{
SqlConnectionInfo conInfo;
SqlContext ctx = null;
SqlCommand cmd;
DataTable dt;
try
{
// Deploy to a non-existent database
dbPackage = new Package(EnvironmentVars.Expand("$(LT_BUILD)\\LillTek.SentinelService.dbpack"));
dbParams = new DBInstallParams("SentinelService", dbTest.ConnectionInfo.Database);
dbInstaller = new DBPackageInstaller(dbPackage);
result = dbInstaller.Install(dbParams);
Assert.AreEqual(DBInstallResult.Installed, result);
conInfo = SqlConnectionInfo.Parse(dbInstaller.ConnectionString);
ctx = new SqlContext(conInfo);
ctx.Open();
cmd = ctx.CreateSPCall("GetProductInfo");
dt = ctx.ExecuteTable(cmd);
Assert.AreEqual(1, dt.Rows.Count);
cmd = ctx.CreateSPCall("Ping");
dt = ctx.ExecuteTable(cmd);
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual("OK", SqlHelper.AsString(dt.Rows[0]["STATUS"]));
ctx.Close();
ctx = null;
// Deploy again and we should see that the database is up-to-date.
SqlConnection.ClearAllPools();
result = dbInstaller.Install(dbParams);
Assert.AreEqual(DBInstallResult.UpToDate, result);
}
finally
{
if (dbPackage != null)
{
dbPackage.Close();
}
if (ctx != null)
{
ctx.Close();
}
}
}
}
private void LoadDatabases()
{
// Load the listbox with the list of databases on the server and
// while we're at it we'll extract the master database's data
// and log file folders.
//
// Note that we're not going to add the following system databases
// to the list:
//
// master
// model
// msdb
// tempdb
// ReportServer
// ReportServerTempDB
Dictionary <string, bool> ignoreDBs = new Dictionary <string, bool>(StringComparer.OrdinalIgnoreCase);
SqlContext ctx;
SqlCommand cmd;
DataSet ds;
DataTable dt;
WaitForm waitForm;
string dbName;
ignoreDBs.Add("master", true);
ignoreDBs.Add("model", true);
ignoreDBs.Add("msdb", true);
ignoreDBs.Add("tempdb", true);
ignoreDBs.Add("ReportServer", true);
ignoreDBs.Add("ReportServerTempDB", true);
wizard.Enabled = false;
this.Update();
waitForm = new WaitForm("Scanning databases...");
waitForm.TopLevel = true;
waitForm.Show();
waitForm.Update();
Thread.Sleep(2000);
ctx = new SqlContext(conString);
try
{
ctx.Open();
// Get the databases (note that the sp_databases sproc does not
// exist on SQL Azure).
if (wizard.IsSqlAzure)
{
cmd = ctx.CreateCommand("select name as DATABASE_NAME from sys.sysdatabases");
}
else
{
cmd = ctx.CreateSPCall("sp_databases");
}
dt = ctx.ExecuteTable(cmd);
databaseList.Items.Clear();
foreach (DataRow row in dt.Rows)
{
dbName = SqlHelper.AsString(row["DATABASE_NAME"]);
if (ignoreDBs.ContainsKey(dbName))
{
continue;
}
databaseList.Items.Add(dbName);
if (String.Compare(dbName, (string)wizard.SetupState["database"], true) == 0)
{
databaseList.SelectedIndex = databaseList.Items.Count - 1;
}
}
if (!wizard.IsSqlAzure && (masterDataPath == null || masterLogPath == null))
{
// Get the master database file paths
cmd = ctx.CreateSPCall("sp_helpdb");
cmd.Parameters.Add("@dbname", SqlDbType.NVarChar).Value = "master";
ds = ctx.ExecuteSet(cmd);
dt = ds.Tables["1"];
foreach (DataRow row in dt.Rows)
{
string file;
int pos;
if (SqlHelper.AsString(row["usage"]).ToLowerInvariant().IndexOf("data") != -1)
{
file = SqlHelper.AsString(row["filename"]);
pos = file.LastIndexOf('\\');
if (pos != -1)
{
masterDataPath = file.Substring(0, pos + 1);
}
}
else if (SqlHelper.AsString(row["usage"]).ToLowerInvariant().IndexOf("log") != -1)
{
file = SqlHelper.AsString(row["filename"]);
pos = file.LastIndexOf('\\');
if (pos != -1)
{
masterLogPath = file.Substring(0, pos + 1);
}
}
}
// Set the paths to the empty string if all else fails
if (masterDataPath == null)
{
masterDataPath = string.Empty;
}
if (masterLogPath == null)
{
masterLogPath = string.Empty;
}
}
}
catch (Exception e)
{
wizard.Enabled = true;
waitForm.Close();
MessageBox.Show("Setup could not connect to the database. Please check\r\nthe server name and account settings.\r\n\r\n" + e.Message,
wizard.SetupTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
finally
{
ctx.Close();
}
wizard.Enabled = true;
waitForm.Close();
}
/// <summary>
/// Used during touch-free deployment to verify that the database doesn't exist
/// or is empty (in which case an installation needs to be performed) or if the
/// database does exist and belongs to the same product (so an upgrade should
/// be attempted).
/// </summary>
/// <returns>Indicates whether an install or upgrade should be performed.</returns>
private DBInstallResult CheckDatabase()
{
// Take a look at the database and ensure that it is either empty
// or is already associated with this product ID and database type.
DBInstallResult result = DBInstallResult.Unknown;
SqlContext ctx;
SqlCommand cmd;
DataTable dt;
string cs;
string dbName;
string query;
bool exists;
cs = string.Format("server={0};database={1};{2}", server, database, dbParams.AdminSecurity);
ctx = new SqlContext(cs);
try
{
ctx.Open();
// Create the database if the doesn't already exist.
cmd = ctx.CreateSPCall("sp_databases");
dt = ctx.ExecuteTable(cmd);
exists = false;
foreach (DataRow row in dt.Rows)
{
dbName = SqlHelper.AsString(row["DATABASE_NAME"]);
if (String.Compare(dbName, database, true) == 0)
{
exists = true;
break;
}
}
if (!exists)
{
if (dbParams.DBPath != null && dbParams.LogPath != null)
{
Helper.CreateFolderTree(dbParams.DBPath);
Helper.CreateFolderTree(dbParams.LogPath);
query = string.Format("create database [{0}] on (name='{0}_data', filename='{1}') log on (name='{0}_log', filename='{2}')", database, dbParams.DBPath, dbParams.LogPath);
}
else if (dbParams.DBPath != null)
{
Helper.CreateFolderTree(dbParams.DBPath);
query = string.Format("create database [{0}] on (name='{0}_data', filename='{1}')", database, dbParams.DBPath);
}
else
{
query = string.Format("create database [{0}]", database);
}
cmd = ctx.CreateCommand(query);
ctx.Execute(cmd);
return(DBInstallResult.Installed);
}
// I'm going to determine whether the database is empty or
// not by looking at the sysobjects table. We'll consider
// it to be not empty if any these conditions are true:
//
// 1. Any user tables are present whose names
// don't begin with "dt".
//
// 2. Any stored procedures or functions are present
// whose names don't begin with "dt".
cmd = ctx.CreateCommand("select 1 from sysobjects where (xtype='U' or xtype='P' or xtype='FN') and name not like 'dt%'");
dt = ctx.ExecuteTable(cmd);
if (dt.Rows.Count == 0)
{
// The database appears to be empty.
result = DBInstallResult.Installed;
}
else
{
// The database appears to be not empty. Try calling the
// GetProductInfo procedure. If this fails then assume that
// the database belongs to some other application. If it
// succeeds then check the productID and database type against
// the setup settings.
try
{
cmd = ctx.CreateSPCall("GetProductInfo");
dt = ctx.ExecuteTable(cmd);
// Compare the database's product ID and database type to
// the setup settings.
if (SqlHelper.AsString(dt.Rows[0]["ProductID"]) != productID ||
SqlHelper.AsString(dt.Rows[0]["DatabaseType"]) != databaseType)
{
throw new InvalidOperationException(string.Format("Package cannot be deployed. Database [{0}] is configured for use by [{1}:{2}].",
database, SqlHelper.AsString(dt.Rows[0]["ProductName"]), SqlHelper.AsString(dt.Rows[0]["DatabaseType"])));
}
// The database looks like can accept the installation.
result = DBInstallResult.Upgraded;
curVersion = new Version(SqlHelper.AsString(dt.Rows[0]["SchemaVersion"]));
}
catch
{
throw new InvalidOperationException(string.Format("Database [{0}] is not empty and appears to in use by another application.\r\n\r\nPlease select a different database.", database));
}
}
}
finally
{
ctx.Close();
}
Assertion.Test(result != DBInstallResult.Unknown);
return(result);
}
public void Archivers_SqlArchiver_Blast()
{
// Archive 25K fixes in random blocks over an extended period
// of time with the buffer interval set to 2 seconds to exercise
// the interaction between archival submissions and the buffer handling
// background thread.
const string appConfig =
@"
§ion LillTek.GeoTracker.Server
GeoFixArchiver = LillTek.GeoTracker.Server.SqlGeoFixArchiver:LillTek.GeoTracker.Server.dll
// Archiver implementation specific arguments (such as a database connection string)
// formatted as name=value pairs separated by semicolons.
GeoFixArchiverArgs = {{
ConnectionString = {0}
BufferSize = 100
BufferInterval = 2s
AddScript = insert into GeoFixes(TimeUtc,EntityID,GroupID,Technology,Latitude,Longitude,Altitude,Course,Speed,HorizontalAccuracy,VerticalAccurancy,NetworkStatus) values (@(TimeUtc),@(EntityID),@(GroupID),@(Technology),@(Latitude),@(Longitude),@(Altitude),@(Course),@(Speed),@(HorizontalAccuracy),@(VerticalAccurancy),@(NetworkStatus))
}}
&endsection
";
const string createTableScript =
@"create table GeoFixes (
ID int primary key identity,
TimeUtc datetime not null,
EntityID varchar(32) not null,
GroupID varchar(32) null,
Technology varchar(32) not null,
Latitude float(53) not null,
Longitude float(53) not null,
Altitude float(53) null,
Course float(53) null,
Speed float(53) null,
HorizontalAccuracy float(53) null,
VerticalAccurancy float(53) null,
NetworkStatus varchar(32) not null
)
";
using (var dbTest = SqlTestDatabase.Create())
{
DateTime time = new DateTime(2011, 4, 27, 9, 58, 0);
SqlContext ctx = null;
DataTable dt;
try
{
ctx = new SqlContext(dbTest.ConnectionInfo);
ctx.Open();
ctx.Execute(createTableScript);
try
{
TestInit(appConfig.Replace("{0}", dbTest.ConnectionInfo));
const int cTotalFixes = 25000;
const int maxBlock = 300;
Random rand = new Random(0);
int cSubmitted = 0;
while (cSubmitted < cTotalFixes)
{
// Blast out a block of between 0...maxBlock-1 fixes.
int cBlock = rand.Next(maxBlock);
if (cBlock + cSubmitted > cTotalFixes)
{
cBlock = cTotalFixes - cSubmitted;
}
for (int i = 0; i < cBlock; i++)
{
client.SubmitEntityFix((cSubmitted + i).ToString(), "group", new GeoFix()
{
TimeUtc = time, Latitude = 10, Longitude = 20
});
}
cSubmitted += cBlock;
// Wait a random time between 0 and 100ms
Thread.Sleep(rand.Next(101));
}
// Stop the server so that any buffered fixes will be flushed.
server.Stop();
// Verify that the fixes are in the database.
dt = ctx.ExecuteTable("select * from GeoFixes");
Assert.AreEqual(cTotalFixes, dt.Rows.Count);
for (int i = 0; i < cTotalFixes; i++)
{
Assert.AreEqual(time, dt.Rows[i].Field <DateTime>(dt.Columns["TimeUtc"]));
Assert.AreEqual(i.ToString(), dt.Rows[i].Field <string>(dt.Columns["EntityID"]));
Assert.AreEqual("group", dt.Rows[i].Field <string>(dt.Columns["GroupID"]));
Assert.AreEqual(10.0, dt.Rows[i].Field <double>(dt.Columns["Latitude"]));
Assert.AreEqual(20.0, dt.Rows[i].Field <double>(dt.Columns["Longitude"]));
Assert.IsNull(dt.Rows[i].Field <double?>(dt.Columns["Altitude"]));
Assert.IsNull(dt.Rows[i].Field <double?>(dt.Columns["Course"]));
Assert.IsNull(dt.Rows[i].Field <double?>(dt.Columns["Speed"]));
Assert.IsNull(dt.Rows[i].Field <double?>(dt.Columns["HorizontalAccuracy"]));
Assert.IsNull(dt.Rows[i].Field <double?>(dt.Columns["VerticalAccurancy"]));
Assert.AreEqual("Unknown", dt.Rows[i].Field <string>(dt.Columns["NetworkStatus"]));
}
}
finally
{
TestCleanup();
Helper.DeleteFile(AppLogFolder, true);
}
}
finally
{
if (ctx != null)
{
ctx.Close();
}
}
}
}
public void Archivers_SqlArchiver_FillBuffer()
{
// Create a test SQL database with a GeoFixes table where we'll have the
// SQL archiver write the fixes. Configure a buffer size of 100 fixes,
// submit 100 fixes and then verify that they were written immediately
// to the database (e.g. the buffer was flushed).
const string appConfig =
@"
§ion LillTek.GeoTracker.Server
GeoFixArchiver = LillTek.GeoTracker.Server.SqlGeoFixArchiver:LillTek.GeoTracker.Server.dll
// Archiver implementation specific arguments (such as a database connection string)
// formatted as name=value pairs separated by semicolons.
GeoFixArchiverArgs = {{
ConnectionString = {0}
BufferSize = 100
BufferInterval = 5m
AddScript = insert into GeoFixes(TimeUtc,EntityID,GroupID,Technology,Latitude,Longitude,Altitude,Course,Speed,HorizontalAccuracy,VerticalAccurancy,NetworkStatus) values (@(TimeUtc),@(EntityID),@(GroupID),@(Technology),@(Latitude),@(Longitude),@(Altitude),@(Course),@(Speed),@(HorizontalAccuracy),@(VerticalAccurancy),@(NetworkStatus))
}}
&endsection
";
const string createTableScript =
@"create table GeoFixes (
ID int primary key identity,
TimeUtc datetime not null,
EntityID varchar(32) not null,
GroupID varchar(32) null,
Technology varchar(32) not null,
Latitude float(53) not null,
Longitude float(53) not null,
Altitude float(53) null,
Course float(53) null,
Speed float(53) null,
HorizontalAccuracy float(53) null,
VerticalAccurancy float(53) null,
NetworkStatus varchar(32) not null
)
";
using (var dbTest = SqlTestDatabase.Create())
{
DateTime time = new DateTime(2011, 4, 27, 9, 58, 0);
SqlContext ctx = null;
DataTable dt;
try
{
ctx = new SqlContext(dbTest.ConnectionInfo);
ctx.Open();
ctx.Execute(createTableScript);
try
{
TestInit(appConfig.Replace("{0}", dbTest.ConnectionInfo));
for (int i = 0; i < 100; i++)
{
client.SubmitEntityFix(i.ToString(), "group", new GeoFix()
{
TimeUtc = time, Latitude = 10, Longitude = 20
});
}
// Wait 4 times the server's background task scheduling interval to give the
// archiver a fair chance to perform the operation.
Thread.Sleep(Helper.Multiply(server.Settings.BkInterval, 4));
dt = ctx.ExecuteTable("select * from GeoFixes");
Assert.AreEqual(100, dt.Rows.Count);
for (int i = 0; i < 100; i++)
{
Assert.AreEqual(time, dt.Rows[i].Field <DateTime>(dt.Columns["TimeUtc"]));
Assert.AreEqual(i.ToString(), dt.Rows[i].Field <string>(dt.Columns["EntityID"]));
Assert.AreEqual("group", dt.Rows[i].Field <string>(dt.Columns["GroupID"]));
Assert.AreEqual(10.0, dt.Rows[i].Field <double>(dt.Columns["Latitude"]));
Assert.AreEqual(20.0, dt.Rows[i].Field <double>(dt.Columns["Longitude"]));
Assert.IsNull(dt.Rows[i].Field <double?>(dt.Columns["Altitude"]));
Assert.IsNull(dt.Rows[i].Field <double?>(dt.Columns["Course"]));
Assert.IsNull(dt.Rows[i].Field <double?>(dt.Columns["Speed"]));
Assert.IsNull(dt.Rows[i].Field <double?>(dt.Columns["HorizontalAccuracy"]));
Assert.IsNull(dt.Rows[i].Field <double?>(dt.Columns["VerticalAccurancy"]));
Assert.AreEqual("Unknown", dt.Rows[i].Field <string>(dt.Columns["NetworkStatus"]));
}
}
finally
{
TestCleanup();
Helper.DeleteFile(AppLogFolder, true);
}
}
finally
{
if (ctx != null)
{
ctx.Close();
}
}
}
}
public void Archivers_SqlArchiver_Flush()
{
// Create a test SQL database with a GeoFixes table where we'll have the
// SQL archiver write the fixes. Configure a 1 second archiver buffer interval,
// start the GeoTracker, submit a single fix to the server and and pause for 5
// seconds and verify that the buffered fix was persisted to the database.
const string appConfig =
@"
§ion LillTek.GeoTracker.Server
GeoFixArchiver = LillTek.GeoTracker.Server.SqlGeoFixArchiver:LillTek.GeoTracker.Server.dll
// Archiver implementation specific arguments (such as a database connection string)
// formatted as name=value pairs separated by semicolons.
GeoFixArchiverArgs = {{
ConnectionString = {0}
BufferSize = 100
BufferInterval = 1s
AddScript = insert into GeoFixes(TimeUtc,EntityID,GroupID,Technology,Latitude,Longitude,Altitude,Course,Speed,HorizontalAccuracy,VerticalAccurancy,NetworkStatus) values (@(TimeUtc),@(EntityID),@(GroupID),@(Technology),@(Latitude),@(Longitude),@(Altitude),@(Course),@(Speed),@(HorizontalAccuracy),@(VerticalAccurancy),@(NetworkStatus))
}}
&endsection
";
const string createTableScript =
@"create table GeoFixes (
ID int primary key identity,
TimeUtc datetime not null,
EntityID varchar(32) not null,
GroupID varchar(32) null,
Technology varchar(32) not null,
Latitude float(53) not null,
Longitude float(53) not null,
Altitude float(53) null,
Course float(53) null,
Speed float(53) null,
HorizontalAccuracy float(53) null,
VerticalAccurancy float(53) null,
NetworkStatus varchar(32) not null
)
";
using (var dbTest = SqlTestDatabase.Create())
{
DateTime time = new DateTime(2011, 4, 27, 9, 58, 0);
SqlContext ctx = null;
DataTable dt;
try
{
ctx = new SqlContext(dbTest.ConnectionInfo);
ctx.Open();
ctx.Execute(createTableScript);
try
{
TestInit(appConfig.Replace("{0}", dbTest.ConnectionInfo));
client.SubmitEntityFix("jeff", "group", new GeoFix()
{
TimeUtc = time, Latitude = 10, Longitude = 20
});
Thread.Sleep(5000);
dt = ctx.ExecuteTable("select * from GeoFixes");
Assert.AreEqual(1, dt.Rows.Count);
Assert.AreEqual(time, dt.Rows[0].Field <DateTime>(dt.Columns["TimeUtc"]));
Assert.AreEqual("jeff", dt.Rows[0].Field <string>(dt.Columns["EntityID"]));
Assert.AreEqual("group", dt.Rows[0].Field <string>(dt.Columns["GroupID"]));
Assert.AreEqual(10.0, dt.Rows[0].Field <double>(dt.Columns["Latitude"]));
Assert.AreEqual(20.0, dt.Rows[0].Field <double>(dt.Columns["Longitude"]));
Assert.IsNull(dt.Rows[0].Field <double?>(dt.Columns["Altitude"]));
Assert.IsNull(dt.Rows[0].Field <double?>(dt.Columns["Course"]));
Assert.IsNull(dt.Rows[0].Field <double?>(dt.Columns["Speed"]));
Assert.IsNull(dt.Rows[0].Field <double?>(dt.Columns["HorizontalAccuracy"]));
Assert.IsNull(dt.Rows[0].Field <double?>(dt.Columns["VerticalAccurancy"]));
Assert.AreEqual("Unknown", dt.Rows[0].Field <string>(dt.Columns["NetworkStatus"]));
}
finally
{
TestCleanup();
Helper.DeleteFile(AppLogFolder, true);
}
}
finally
{
if (ctx != null)
{
ctx.Close();
}
}
}
}
/// <summary>
/// Called when the step is deactivated.
/// </summary>
/// <param name="steps">The step list.</param>
/// <param name="forward"><c>true</c> if we're stepping forward in the wizard.</param>
/// <returns><c>true</c> if the transition can proceed.</returns>
public bool OnStepOut(WizardStepList steps, bool forward)
{
if (!forward)
{
return(true);
}
string server;
string account;
string password;
// Validate the dialog entries.
server = serverName.Text.Trim();
account = adminAccount.Text.Trim();
password = adminPassword.Text.Trim();
if (server == string.Empty)
{
MessageBox.Show("Please enter the database server name or IP address.", wizard.SetupTitle,
MessageBoxButtons.OK, MessageBoxIcon.Error);
serverName.Focus();
serverName.SelectAll();
return(false);
}
if (account == string.Empty)
{
MessageBox.Show("Please enter the database administrator account.", wizard.SetupTitle,
MessageBoxButtons.OK, MessageBoxIcon.Error);
adminAccount.Focus();
adminAccount.SelectAll();
return(false);
}
// Verify that the database server exists and that the account information
// is valid by connecting to the database executing sp_helpsrvrolemember
// and verifying that the account is returned as one of the accounts having
// the sysadmin role.
//
// Note that sp_helpsrvrolemember does not exist on SQL Azure, so we're going
// to first determine whether we're running on Azure and then skip this call
// if we are.
string conString;
SqlContext ctx;
SqlCommand cmd;
DataTable dt;
bool found;
WaitForm waitForm;
conString = string.Format("server={0};database={1};uid={2};pwd={3}",
server, "master", account, password);
wizard.Enabled = false;
this.Update();
waitForm = new WaitForm("Verifying administrator credentials...");
waitForm.TopLevel = true;
waitForm.Show();
waitForm.Update();
Thread.Sleep(2000);
ctx = new SqlContext(conString);
try
{
ctx.Open();
// SQL Azure detection
wizard.IsSqlAzure = ctx.IsSqlAzure;
// Verify that the account is an admin
if (!wizard.IsSqlAzure)
{
cmd = ctx.CreateSPCall("sp_helpsrvrolemember");
cmd.Parameters.Add("@srvrolename", SqlDbType.NVarChar).Value = "sysadmin";
dt = ctx.ExecuteTable(cmd);
found = false;
foreach (DataRow row in dt.Rows)
{
if (String.Compare(account, SqlHelper.AsString(row["MemberName"]), true) == 0)
{
found = true;
break;
}
}
if (!found)
{
wizard.Enabled = true;
waitForm.Close();
MessageBox.Show(string.Format("Account [{0}] is not a system administrator.", account.ToUpper()),
wizard.SetupTitle,
MessageBoxButtons.OK, MessageBoxIcon.Error);
adminAccount.Focus();
adminAccount.SelectAll();
return(false);
}
}
}
catch (Exception e)
{
wizard.Enabled = true;
waitForm.Close();
MessageBox.Show("Setup could not connect to the database. Please check\r\nthe server name and account settings.\r\n\r\n" + e.Message,
wizard.SetupTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
finally
{
ctx.Close();
}
wizard.Enabled = true;
waitForm.Close();
wizard.SetupState["connectionString"] = conString;
wizard.SetupState["server"] = server;
wizard.SetupState["adminAccount"] = account;
wizard.SetupState["adminPassword"] = password;
this.Hide();
return(true);
}