/// <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>
/// Creates the application database account if one doesn't already exist.
/// </summary>
private void CheckAppAccount()
{
List <string> accounts;
string login;
string cs;
SqlContext ctx;
SqlCommand cmd;
DataSet ds;
DataTable dt;
bool exists;
if (account == null)
{
return; // Looks like we're using integrated security
}
// Get the current set of accounts from the database
cs = string.Format("server={0};database={1};{2}", server, database, dbParams.AdminSecurity);
ctx = new SqlContext(cs);
try
{
ctx.Open();
// Get the accounts (note that the sp_helplogins sproc does not exist on SQL Azure).
if (ctx.IsSqlAzure)
{
cmd = ctx.CreateCommand("select name as 'LoginName' from sys.sql_logins");
}
else
{
cmd = ctx.CreateSPCall("sp_helplogins");
}
ds = ctx.ExecuteSet(cmd);
dt = ds.Tables["0"];
accounts = new List <string>();
foreach (DataRow row in dt.Rows)
{
login = SqlHelper.AsString(row["LoginName"]);
// Append the account, skipping any that are empty or
// appear to be a server role or a Windows domain account.
if (login != null && login.IndexOf('\\') == -1)
{
accounts.Add(login);
}
}
}
finally
{
ctx.Close();
}
// Create the account, recreating it if it already exists.
exists = false;
for (int i = 0; i < accounts.Count; i++)
{
if (String.Compare(account, accounts[i], true) == 0)
{
exists = true;
break;
}
}
cs = string.Format("server={0};database=master;{1}", server, database, dbParams.AdminSecurity);
ctx = new SqlContext(cs);
try
{
ctx.Open();
if (exists)
{
if (ctx.IsSqlAzure)
{
cmd = ctx.CreateCommand("drop login '{0}'", account);
}
else
{
cmd = ctx.CreateSPCall("sp_droplogin");
cmd.Parameters.Add("@loginame", SqlDbType.VarChar).Value = account;
}
ctx.Execute(cmd);
}
if (ctx.IsSqlAzure)
{
ctx.CreateCommand("create login {0} with password='******'", account, password);
}
else
{
cmd = ctx.CreateSPCall("sp_addlogin");
cmd.Parameters.Add("@loginame", SqlDbType.VarChar).Value = account;
cmd.Parameters.Add("@passwd", SqlDbType.VarChar).Value = password;
}
ctx.Execute(cmd);
}
finally
{
ctx.Close();
}
}
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();
}
}
private void okButton_Click(object sender, System.EventArgs args)
{
account = accountBox.Text.Trim();
if (account.Length == 0)
{
MessageBox.Show("Please enter an account name.",
wizard.SetupTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
accountBox.Focus();
accountBox.SelectAll();
return;
}
password = passwordBox.Text.Trim();
if (password != confirmBox.Text.Trim())
{
MessageBox.Show("The password and confirmation are not the same.",
wizard.SetupTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
passwordBox.Text = string.Empty;
confirmBox.Text = string.Empty;
passwordBox.Focus();
passwordBox.SelectAll();
return;
}
// Create the account
SqlConnectionInfo conInfo;
SqlContext ctx;
SqlCommand cmd;
WaitForm waitForm;
wizard.Enabled = false;
this.Update();
waitForm = new WaitForm("Creating account [" + account + "]...");
waitForm.TopLevel = true;
waitForm.Show();
waitForm.Update();
Thread.Sleep(2000);
conInfo = new SqlConnectionInfo((string)wizard.SetupState["connectionString"]);
conInfo.Database = "master";
ctx = new SqlContext(conInfo.ToString());
try
{
ctx.Open();
if (ctx.IsSqlAzure)
{
cmd = ctx.CreateCommand("create login {0} with password='******'", account, password);
}
else
{
cmd = ctx.CreateSPCall("sp_addlogin");
cmd.Parameters.Add("@loginame", SqlDbType.VarChar).Value = account;
cmd.Parameters.Add("@passwd", SqlDbType.VarChar).Value = password;
}
ctx.Execute(cmd);
}
catch (Exception e)
{
passwordBox.Text = string.Empty;
confirmBox.Text = string.Empty;
wizard.Enabled = true;
waitForm.Close();
MessageBox.Show("Cannot create the new database account.\r\n\r\n" + e.Message,
wizard.SetupTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
finally
{
ctx.Close();
}
wizard.Enabled = true;
waitForm.Close();
// Success!
DialogResult = DialogResult.OK;
}
/// <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>
/// Implements the background thread responsible for persisting buffered <see cref="GeoFix "/>es .
/// </summary>
private void FlushThread()
{
bool stopCompleted = false;
List <FixRecord> writeFixes;
while (true)
{
writeFixes = null;
try
{
lock (syncLock)
{
if (!isRunning)
{
return;
}
if (stopPending || bufferedFixes.Count >= bufferSize || flushTimer.HasFired)
{
writeFixes = bufferedFixes;
bufferedFixes = new List <FixRecord>(writeFixes.Count);
}
}
if (writeFixes != null && writeFixes.Count > 0)
{
// Build SQL batch command that to add all of the buffered fixes using
// the SQL script template to generate the T-SQL statements for each fix.
var processor = new MacroProcessor();
var sqlBatch = new StringBuilder(8192);
foreach (var record in writeFixes)
{
if (record.EntityID == null)
{
SysLog.LogWarning("SqlGeoFixArchiver: GeoFix has a [EntityID=NULL] field and will be ignored.");
}
if (!record.Fix.TimeUtc.HasValue)
{
SysLog.LogWarning("SqlGeoFixArchiver: GeoFix has a [TimeUtc=NULL] field and will be ignored.");
}
if (double.IsNaN(record.Fix.Latitude))
{
SysLog.LogWarning("SqlGeoFixArchiver: GeoFix has a [Latitude=NaN] field and will be ignored.");
}
if (double.IsNaN(record.Fix.Longitude))
{
SysLog.LogWarning("SqlGeoFixArchiver: GeoFix has a [Longitude=NaN] field and will be ignored.");
}
processor["EntityID"] = ToSqlLiteral(record.EntityID);
processor["GroupID"] = ToSqlLiteral(record.GroupID);
processor["TimeUtc"] = ToSqlLiteral(record.Fix.TimeUtc);
processor["Technology"] = ToSqlLiteral(record.Fix.Technology);
processor["Latitude"] = ToSqlLiteral(record.Fix.Latitude);
processor["Longitude"] = ToSqlLiteral(record.Fix.Longitude);
processor["Altitude"] = ToSqlLiteral(record.Fix.Altitude);
processor["Course"] = ToSqlLiteral(record.Fix.Course);
processor["Speed"] = ToSqlLiteral(record.Fix.Speed);
processor["HorizontalAccuracy"] = ToSqlLiteral(record.Fix.HorizontalAccuracy);
processor["VerticalAccurancy"] = ToSqlLiteral(record.Fix.VerticalAccurancy);
processor["NetworkStatus"] = ToSqlLiteral(record.Fix.NetworkStatus);
sqlBatch.AppendLine(processor.Expand(addScript));
}
// Submit the T-SQL batch to the server.
var sqlCtx = new SqlContext(conString);
sqlCtx.Open();
try
{
sqlCtx.Execute(sqlBatch.ToString());
node.IncrementFixesReceivedBy(writeFixes.Count);
}
catch (SqlException e)
{
const string msgTemplate =
@"SQL Error [Line {1}]: {0}
{2}
";
SysLog.LogException(e);
SysLog.LogError(msgTemplate, e.Message, e.LineNumber, sqlBatch);
}
finally
{
sqlCtx.Close();
}
}
}
catch (Exception e)
{
SysLog.LogException(e);
}
finally
{
if (writeFixes != null)
{
writeFixes = null;
flushTimer.Reset();
}
}
if (stopCompleted)
{
return;
}
if (stopPending)
{
// Loop one more time to archive any fixes cached while we were
// persisting to the database.
stopCompleted = true;
continue;
}
Thread.Sleep(settings.BkInterval);
}
}
private void okButton_Click(object sender, System.EventArgs args)
{
database = nameBox.Text.Trim();
// Validate the database name
if (database.Length == 0)
{
MessageBox.Show("Please enter a name for the new database.",
wizard.SetupTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
nameBox.Focus();
nameBox.SelectAll();
return;
}
if (Char.IsDigit(database[0]))
{
MessageBox.Show("Database names cannot start with a number.",
wizard.SetupTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
nameBox.Focus();
nameBox.SelectAll();
return;
}
for (int i = 0; i < database.Length; i++)
{
if (!Char.IsLetterOrDigit(database[i]) && database[i] != '_')
{
MessageBox.Show("Invalid character in the database name.\r\n\r\nDatabase names may include only letters, numbers,\r\nor underscores.",
wizard.SetupTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
nameBox.Focus();
nameBox.SelectAll();
return;
}
}
// Create the database
SqlContext ctx;
SqlCommand cmd;
WaitForm waitForm;
string query;
string dataFile;
string logFile;
string maxSize;
wizard.Enabled = false;
this.Update();
waitForm = new WaitForm("Creating database [" + database + "]...");
waitForm.TopLevel = true;
waitForm.Show();
waitForm.Update();
Thread.Sleep(2000);
ctx = new SqlContext((string)wizard.SetupState["connectionString"]);
try
{
ctx.Open();
if (ctx.IsSqlAzure)
{
maxSize = string.Format(" (maxsize={0}GB)", sizeComboBox.SelectedItem);
}
else
{
maxSize = string.Empty;
}
dataFile = dataFileBox.Text.Trim();
if (wizard.IsSqlAzure || dataFile.Length == 0 || dataFile == "[default]")
{
dataFile = null;
}
logFile = logFileBox.Text.Trim();
if (wizard.IsSqlAzure || logFile.Length == 0 || logFile == "[default]")
{
logFile = null;
}
if (dataFile != null && logFile != null)
{
query = string.Format("create database [{0}] on (name='{0}_data', filename='{1}') log on (name='{0}_log', filename='{2}')", database, dataFile, logFile);
}
else if (dataFile != null)
{
query = string.Format("create database [{0}] on (name='{0}_data', filename='{1}')", database, dataFile);
}
else
{
query = string.Format("create database [{0}]{1}", database, maxSize);
}
cmd = ctx.CreateCommand(query);
ctx.Execute(cmd);
}
catch (Exception e)
{
wizard.Enabled = true;
waitForm.Close();
MessageBox.Show("Cannot create the database.\r\n\r\n" + e.Message,
wizard.SetupTitle, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
finally
{
ctx.Close();
}
wizard.Enabled = true;
waitForm.Close();
// Success!
DialogResult = DialogResult.OK;
}