internal static void CreateSampleSchema(SQLiteConnection connection)
{
try { connection.Execute("DROP TABLE table1"); }
catch { }
try { connection.Execute("CREATE TABLE table1 (id integer primary key, name text)"); }
catch { }
}
private void btnClear_Click(object sender, EventArgs e)
{
using (var conn = new SQLiteConnection(Common.ConnectionString))
{
//清理无效远程主机
conn.Execute(
"DELETE FROM RemoteHost WHERE FParentId>0 AND FConectId NOT IN (SELECT FId FROM ConnectLib)");
//清理无效父级节点
conn.Execute(
"DELETE FROM RemoteHost WHERE FParentId=0 AND FId NOT IN (SELECT FParentId FROM RemoteHost)");
}
LoadHostConfig();
}
public void TestDapper()
{
var dataSource = @"test.sqlite";
SQLiteConnection.CreateFile(dataSource);
var builder = new SQLiteConnectionStringBuilder
{
DataSource = dataSource,
LegacyFormat = false,
Version = 3,
SyncMode = SynchronizationModes.Off,
JournalMode = SQLiteJournalModeEnum.Wal
};
using (var tran = new TransactionScope(TransactionScopeOption.Required))
using (var connection = new SQLiteConnection(builder.ToString()))
{
connection.Open();
CreateSampleSchema(connection);
try
{
connection.Execute("INSERT INTO table1 VALUES (@Id, @Name)", new { Id = 1, Name = "Name-1" });
connection.Execute("INSERT INTO table1 VALUES (@Id, @Name)", new { Id = 2, Name = "Name-2" });
connection.Execute("INSERT INTO table1 VALUES (@Id, @Name)", new { Id = 3, Name = "Name-3" });
dynamic dynamicObj = connection.Query("SELECT id, name FROM table1 LIMIT 1").FirstOrDefault();
Console.WriteLine("Id: {0}", dynamicObj.id);
Console.WriteLine("Name: {0}", dynamicObj.name);
var mappingObj = connection.Query<Table1>("SELECT id, name FROM table1 LIMIT 1").FirstOrDefault();
Console.WriteLine("Id: {0}", mappingObj.Id);
Console.WriteLine("Name: {0}", mappingObj.Name);
dynamic results = connection.Query("SELECT id, name FROM table1");
foreach (dynamic item in results)
{
Console.WriteLine(item);
}
tran.Complete();
}
catch
{
}
}
}
private void ClearLogs()
{
using (var connection = new SQLiteConnection(ConnectionString))
{
connection.Execute("delete from Log");
}
}
public ModuleConfigurations()
{
db = new SQLiteConnection("Data Source=:memory:;Version=3;New=True");
//todo: create platy table
db.Open();
db.Execute("PRAGMA foreign_keys=ON");
}
public virtual void Setup()
{
string connectionString = string.Format("Data Source=.\\dapperTest_{0}.sqlite", Guid.NewGuid());
string[] connectionParts = connectionString.Split(';');
string file = connectionParts
.ToDictionary(k => k.Split('=')[0], v => v.Split('=')[1])
.Where(d => d.Key.Equals("Data Source", StringComparison.OrdinalIgnoreCase))
.Select(k => k.Value).Single();
if (File.Exists(file))
{
File.Delete(file);
}
SQLiteConnection connection = new SQLiteConnection(connectionString);
var config = new DapperExtensionsConfiguration(typeof(AutoClassMapper<>), new List<Assembly>(), new SqliteDialect());
var sqlGenerator = new SqlGeneratorImpl(config);
Db = new Database(connection, sqlGenerator);
var files = new List<string>
{
ReadScriptFile("CreateAnimalTable"),
ReadScriptFile("CreateFooTable"),
ReadScriptFile("CreateMultikeyTable"),
ReadScriptFile("CreatePersonTable"),
ReadScriptFile("CreateCarTable")
};
foreach (var setupFile in files)
{
connection.Execute(setupFile);
}
}
public void AddProfile(EmployeeProfile commandDto, string imgURL, string cartoonURL)
{
string user = System.Web.HttpContext.Current.User.Identity.Name;
using (DbConnection conn = new SQLiteConnection(ConfigurationManager.ConnectionStrings["reedDirectory"].ConnectionString))
{
conn.Open();
conn.Execute(
"insert into EmployeeProfile (Department, FirstName, ImageUrl, LastName, Role, Tags, CreatedBy, CreatedOn, IsSuperUser, Extension, CartoonURL, WindowsIdentity, emailaddress, SeatingFloor, seatNo) values " +
"(@Department, @FirstName, @ImageUrl, @LastName, @Role, @Tags, @CreatedBy, @CreatedOn, @IsSuperUser, @Extension, @CartoonURL, @WindowsIdentity, @emailAddress, @SeatingFloor, @seatNo)",
new
{
Department = commandDto.Department,
FirstName = commandDto.FirstName.Replace("'","''"),
ImageUrl = imgURL,
LastName = commandDto.LastName.Replace("'", "''"),
Role = commandDto.Role,
Tags = !string.IsNullOrEmpty(commandDto.Tags) ? commandDto.Tags.Replace("'", "''"): "",
CreatedBy = user.Remove(0, 11),
CreatedOn = commandDto.CreatedOn,
IsSuperUser = false,
Extension = !string.IsNullOrEmpty(commandDto.Extension) ? commandDto.Extension.Replace("'", "''") : "",
CartoonURL = cartoonURL,
windowsIdentity = user,
emailAddress = commandDto.EmailAddress,
SeatingFloor = commandDto.SeatingFloor,
seatNo = commandDto.SeatNo
});
conn.Close();
}
}
public bool Execute(string connectionString, string sql)
{
using (var cnn = new SQLiteConnection(connectionString))
{
cnn.Open();
return cnn.Execute(sql) > 0;
}
}
protected override DbConnection CreateConnectionCore()
{
var con = new SQLiteConnection(ConnectionStringBuilder.CreateWithFullUri(
"file::memory:?cache=shared"));
con.Open();
con.Execute("PRAGMA case_sensitive_like=1");
return con;
}
public LitePostRepository()
{
using (var conn = new SQLiteConnection(Connectionstring))
{
conn.Open();
conn.Execute(@" create table IF NOT EXISTS Posts (Id INTEGER PRIMARY KEY, Title nvarchar(255), Content nvarchar(1000) not null) ");
}
}
private void btnSave_Click(object sender, System.EventArgs e)
{
if (InfoIsError()) return;
using (var conn = new SQLiteConnection(Common.ConnectionString))
{
if (IsModify)
{
conn.Execute("UPDATE RemoteHost SET FName=@name,FConectId=@connectId,FParentId=@parentId,FSort=@sort WHERE FId=@id",
new { name = txtName.Text, connectId = cbIpAddress.SelectedValue, parentId = chIsParent.Checked ? 0 : cbParent.SelectedValue, sort = numSort.Text, id = RemoteHost.FId });
}
else
{
conn.Execute("INSERT INTO RemoteHost('FName','FConectId','FParentId','FSort') VALUES(@name,@connectId,@parentId,@sort)",
new { name = txtName.Text, connectId = cbIpAddress.SelectedValue, parentId = chIsParent.Checked ? 0 : cbParent.SelectedValue, sort = numSort.Text });
}
}
DialogResult = DialogResult.OK;
}
public void CreateDb()
{
using (var connection = new SQLiteConnection(ConnectionString))
{
var query = @"CREATE TABLE Init (Id INT NOT NULL)";
connection.Open();
connection.Execute(query);
}
}
public static void SaveEvent(EventInfo eventInfo)
{
using (var conn = new SQLiteConnection(_connectionString))
{
var query = @" insert into EventInfo(WindowTitle, EventType)
values (@title, @type);";
conn.Open();
conn.Execute(query, new { title = eventInfo.WindowTitle, type = eventInfo.EventType }, null, null, null);
}
}
public ModulesRepository(SQLiteConnection database)
{
this.db = database;
lock(database) {
if (db.State == System.Data.ConnectionState.Closed)
{
db.Open();
}
}
db.Execute("PRAGMA foreign_keys=ON");
}
public void Set(IContent content)
{
string connString = ConfigurationManager.ConnectionStrings["WilliamsonFamilyConnectionString"].ConnectionString;
//var connection = new ProfiledDbConnection(new SQLiteConnection(connString), MiniProfiler.Current);
using (var conn = new SQLiteConnection(connString))
{
conn.Open();
var contentExists = conn.Query<Content>("SELECT * FROM Content WHERE Token = @token", new { @token = content.Token }, null, true, null, null).FirstOrDefault();
if (contentExists == null)
conn.Execute("INSERT INTO Content (Token, Value) VALUES (@Token, @Value)", new { @token = content.Token, @value = content.Value }, null, null, null);
else
conn.Execute(@"
UPDATE Content
SET Token = @Token,
Value = @Value
WHERE ContentID = @ContentID", new { @token = content.Token, @value = content.Value, @ContentID = content.ContentID }, null, null, null);
conn.Close();
}
}
public SystemProperties(SQLiteConnection database)
{
this.db = database;
lock (database)
{
if (db.State == System.Data.ConnectionState.Closed)
{
db.Open();
}
}
db.Execute("PRAGMA foreign_keys=ON");
}
private void btnSetStyle_Click(object sender, EventArgs e)
{
var nextStyle = Convert.ToInt32(StyleManager.Style);
do
{
nextStyle = nextStyle == 16 ? 1 : nextStyle + 1;
} while (nextStyle == 2);//忽略白色
btnSetStyle.Text = string.Format("切换主题[{0}/15]", nextStyle < 2 ? nextStyle : nextStyle - 1);
StyleManager.Style = (MetroColorStyle)nextStyle;
using (var conn = new SQLiteConnection(Common.ConnectionString))
{
conn.Execute("UPDATE MyConfig SET FValue=@styleId WHERE FKey='Style'", new { styleId = nextStyle });
}
}
public void Add(IToken token)
{
using (var connection = new SQLiteConnection(ConnectionString))
{
connection.Open();
connection.Execute("DELETE FROM tokens WHERE OrganisationId = @OrganisationId", new
{
token.OrganisationId
});
connection.Execute(@"INSERT INTO tokens (UserId, OrganisationId, ConsumerKey, ConsumerSecret, TokenKey, TokenSecret, ExpiresAt, Session, SessionExpiresAt)
VALUES (@UserId, @OrganisationID, @ConsumerKey, @ConsumerSecret, @TokenKey, @TokenSecret, @ExpiresAt, @Session, @SessionExpiresAt)", token);
}
}
public CustomerModel AddCustomer(CustomerModel customerModel)
{
try
{
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString(db)))
{
customerModel.Id = connection.Query <int>(@"INSERT INTO Customer (salution, firstname, lastname, street,
housenumber, zipcode, city, phonenumber, mobilenumber, email,
birthday, create_date, edit_date,active) VALUES(
@Salution, @FirstName, @LastName, @Street, @Housenumber, @ZipCode,
@City, @PhoneNumber, @MobileNumber, @Email, @Birthday, datetime('now'),
null, 1); SELECT last_insert_rowid()", customerModel).First();
if (customerModel.Notes != null && customerModel.Notes.Count > 0)
{
foreach (NoteModel nModel in customerModel.Notes)
{
nModel.Id = connection.Query <int>(@"INSERT INTO note (description, active) VALUES (@Description, 1); SELECT last_insert_rowid()", nModel).First();
connection.Execute("INSERT INTO note_to_customer (customerId, noteId) Values(" + customerModel.Id + ", " + nModel.Id + ")");
}
}
if (customerModel.OwnedDogs != null && customerModel.OwnedDogs.Count > 0)
{
foreach (DogModel dModel in customerModel.OwnedDogs)
{
if (dModel.Id > 0)
{
AddDogToCustomer(dModel, customerModel);
}
else
{
AddDogToDatabase(dModel);
AddDogToCustomer(dModel, customerModel);
}
}
}
}
return(customerModel);
}
catch (SQLiteException ex)
{
Console.WriteLine(ex.Message);
return(null);
}
}
static SQLiteTestSuite()
{
if (File.Exists(FileName))
{
File.Delete(FileName);
}
SqliteConnection.CreateFile(FileName);
using (var connection = new SqliteConnection(ConnectionString))
{
connection.Open();
connection.Execute(@"CREATE TABLE Stuff (TheId integer primary key autoincrement not null, Name nvarchar(100) not null, Created DateTime null) ");
connection.Execute(@"CREATE TABLE People (Id integer primary key autoincrement not null, Name nvarchar(100) not null) ");
connection.Execute(@"CREATE TABLE Users (Id integer primary key autoincrement not null, Name nvarchar(100) not null, Age int not null) ");
connection.Execute(@"CREATE TABLE Automobiles (Id integer primary key autoincrement not null, Name nvarchar(100) not null) ");
connection.Execute(@"CREATE TABLE Results (Id integer primary key autoincrement not null, Name nvarchar(100) not null, [Order] int not null) ");
connection.Execute(@"CREATE TABLE ObjectX (ObjectXId nvarchar(100) not null, Name nvarchar(100) not null) ");
connection.Execute(@"CREATE TABLE ObjectY (ObjectYId integer not null, Name nvarchar(100) not null) ");
connection.Execute(@"CREATE TABLE ObjectZ (Id integer not null, Name nvarchar(100) not null) ");
connection.Execute(@"CREATE TABLE NullableKey (Id integer primary key autoincrement not null, Name nvarchar(100) not null) ");
}
}
static SQLiteTestSuite()
{
if (File.Exists(FileName))
{
File.Delete(FileName);
}
SqliteConnection.CreateFile(FileName);
using (var connection = new SqliteConnection(ConnectionString))
{
connection.Open();
connection.Execute(@"CREATE TABLE Stuff (TheId integer primary key autoincrement not null, Name nvarchar(100) not null, Created DateTime null) ");
connection.Execute(@"CREATE TABLE People (Id integer primary key autoincrement not null, Name nvarchar(100) not null) ");
connection.Execute(@"CREATE TABLE Users (Id integer primary key autoincrement not null, Name nvarchar(100) not null, Age int not null) ");
connection.Execute(@"CREATE TABLE Automobiles (Id integer primary key autoincrement not null, Name nvarchar(100) not null) ");
connection.Execute(@"CREATE TABLE Results (Id integer primary key autoincrement not null, Name nvarchar(100) not null, [Order] int not null) ");
connection.Execute(@"CREATE TABLE ObjectX (ObjectXId nvarchar(100) not null, Name nvarchar(100) not null) ");
connection.Execute(@"CREATE TABLE ObjectY (ObjectYId integer not null, Name nvarchar(100) not null) ");
}
}
public void RunBeforeAnyTests()
{
string connectionString = ConfigurationManager.ConnectionStrings["__DefaultSQLite"].ConnectionString;
string[] connectionParts = connectionString.Split(';');
string file = connectionParts
.ToDictionary(k => k.Split('=')[0], v => v.Split('=')[1])
.Where(d => d.Key.Equals("Data Source", StringComparison.OrdinalIgnoreCase))
.Select(k => k.Value).Single();
if (File.Exists(file))
File.Delete(file);
Container = new Castle.Windsor.WindsorContainer();
DapperConfiguration
.Use()
.UseClassMapper(typeof(AutoClassMapper<>))
.UseContainer<ContainerForWindsor>(cfg => cfg.UseExisting(Container))
.UseSqlDialect(new SQLiteDialect())
.WithDefaultConnectionStringNamed("__DefaultSQLite")
.FromAssembly("Dapper.Extensions.Linq.Test.Entities")
.FromAssembly("Dapper.Extensions.Linq.Test.Maps")
.Build();
_connection = new SQLiteConnection(connectionString);
var files = new List<string>
{
ReadScriptFile("CreateAnimalTable"),
ReadScriptFile("CreateFooTable"),
ReadScriptFile("CreateMultikeyTable"),
ReadScriptFile("CreatePersonTable"),
ReadScriptFile("CreateCarTable"),
ReadScriptFile("CreatePhoneTable")
};
foreach (var setupFile in files)
{
_connection.Execute(setupFile);
}
}
public void CreateDatabaseIfNeeded()
{
SQLiteConnection connection = null;
if (!File.Exists(this._databasePath))
{
connection = new SQLiteConnection(this.ConnectionString);
connection.Open();
var createTable = @"CREATE TABLE RunResults (
TestName VARCHAR(50),
Title VARCHAR(255),
YAxisLegend VARCHAR(255),
RanAt DATETIME,
TimeTaken LONG,
TimeTakenFormatted VARCHAR(10),
Error INTEGER
)";
connection.Execute(createTable);
}
}
public NoteModel AddNoteToCustomer(CustomerModel customerModel, string note)
{
try
{
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString(db)))
{
NoteModel nModel = new NoteModel();
nModel.Description = note;
nModel.Id = connection.Query <int>("INSERT INTO note (description, active) Values('" + note + "', 1); SELECT last_insert_rowid();").First();
connection.Query("UPDATE customer SET edit_date = datetime('now') WHERE customer.id= " + customerModel.Id);
connection.Execute("INSERT INTO note_to_customer (customerId, noteId) Values(" + customerModel.Id + ", " + nModel.Id + ")");
customerModel.Notes = connection.Query <NoteModel>("SELECT n.* FROM note n INNER JOIN note_to_customer nc on noteId = n.id WHERE active = 1 AND customerId = " + customerModel.Id).ToList();
return(nModel);
}
}
catch (SQLiteException sqLiteEx)
{
Console.WriteLine(sqLiteEx.Message);
return(null);
}
}
private void CreateDatabase()
{
using (var connection = new SQLiteConnection(GetConnectionString()))
{
connection.Open();
connection.Execute(@"create table User
(
ID integer identity primary key AUTOINCREMENT,
Name varchar(100) not null,
);
create table WorkItem
(
ID integer identity primary key AUTOINCREMENT,
UserID integer
TaskName varchar(100) not null,
Completed bit not null,
CreatedDate datetime not null,
CompletedDate datetime,
);");
}
}
private void CreateEmptyDatabase()
{
SQLiteConnection.CreateFile(FileName);
using (var connection = new SQLiteConnection(ConnectionString))
{
connection.Open();
connection.Execute(
@"
CREATE TABLE tokens (
Id INTEGER PRIMARY KEY AUTOINCREMENT,
UserId TEXT NOT NULL,
OrganisationId TEXT NOT NULL,
ConsumerKey TEXT NOT NULL,
ConsumerSecret TEXT NOT NULL,
TokenKey TEXT NOT NULL,
TokenSecret TEXT NOT NULL,
ExpiresAt DATETIME,
Session TEXT,
SessionExpiresAt DATETIME)");
}
}
static void Main(string[] args)
{
string mod_spatialite_folderPath = "mod_spatialite-4.3.0a-win-amd64";
if (is64bitProcess())
{
Console.WriteLine("64bit process");
mod_spatialite_folderPath = "mod_spatialite-4.3.0a-win-amd64";
}
else if(is32bitProcess())
{
Console.WriteLine("32bit process");
mod_spatialite_folderPath = "mod_spatialite-4.3.0a-win-x86";
}
//using relative path, cannot use absolute path, dll load will fail
//string mod_spatialite_dllPath = @"mod_spatialite-4.3.0a-win-amd64\mod_spatialite";
string path = Environment.GetEnvironmentVariable("Path", EnvironmentVariableTarget.Machine) + ";" + mod_spatialite_folderPath;
Environment.SetEnvironmentVariable("Path", path, EnvironmentVariableTarget.Process);
string sqlitePath = "_BCC_replication.sqlite";
//select 'geometry::STGeomFromWKB(0x' || hex(ST_AsBinary(geometry)) || ', 28356) ' as geometry2 from qpp_airport_ols;
string sqliteConnectionString = getSqliteV3ConnectionString(sqlitePath);
using (var sqliteConnection = new SQLiteConnection(sqliteConnectionString))
{
sqliteConnection.Open();
Console.WriteLine("Load-ing mod_spatialite");
string mod_spatialite_dllPath = mod_spatialite_folderPath + @"\mod_spatialite";
sqliteConnection.LoadExtension(mod_spatialite_dllPath);
Console.WriteLine("Load-ed mod_spatialite");
sqliteConnection.Execute("update qpp_airport_ols set lga_code = 111122 where ogc_fid = 2;");
}
Console.ReadLine();
}
public static bool CreateDataBase()
{
bool success = true;
String iniPath = Path.Combine(Directory.GetCurrentDirectory().ToString(), "Scripts\\DbInstall.ini");
StreamReader file = new StreamReader(iniPath);
string line;
while ((line = file.ReadLine()) != null)
{
try
{
string Queries = File.ReadAllText(Path.Combine(Directory.GetCurrentDirectory(), String.Format("Scripts\\{0}", line)));
Queries = Queries.Replace("\n", "");
Queries = Queries.Replace("\r", "");
Queries = Queries.Replace("\t", " ");
string[] queriesList = Queries.Split(';');
foreach (String oneQ in queriesList)
{
using (SQLiteConnection sqlite = new SQLiteConnection(GlobalManager.SQLCHAIN))
{
sqlite.Open();
sqlite.Execute(oneQ);
sqlite.Close();
}
Console.WriteLine(Queries);
}
}
catch (Exception e)
{
success = false;
Console.WriteLine("Exception: " + e.Message);
}
}
return success;
}
private static void Setup()
{
var projLoc = Assembly.GetAssembly(typeof(Program)).Location;
var projFolder = Path.GetDirectoryName(projLoc);
if (File.Exists(projFolder + "\\Test.sqlite"))
File.Delete(projFolder + "\\Test.sqlite");
SQLiteConnection.CreateFile(projFolder + "\\Test.sqlite");
var connectionString = "Data Source = " + projFolder + "\\Test.sqlite;";
using (var connection = new SQLiteConnection(connectionString))
{
connection.Open();
connection.Execute(@" create table Stuff (TheId integer primary key autoincrement not null, Name nvarchar(100) not null, Created DateTime null) ");
connection.Execute(@" create table People (Id integer primary key autoincrement not null, Name nvarchar(100) not null) ");
connection.Execute(@" create table Users (Id integer primary key autoincrement not null, Name nvarchar(100) not null, Age int not null) ");
connection.Execute(@" create table Automobiles (Id integer primary key autoincrement not null, Name nvarchar(100) not null) ");
connection.Execute(@" create table Results (Id integer primary key autoincrement not null, Name nvarchar(100) not null, [Order] int not null) ");
connection.Execute(@" create table ObjectX (ObjectXId nvarchar(100) not null, Name nvarchar(100) not null) ");
connection.Execute(@" create table ObjectY (ObjectYId integer not null, Name nvarchar(100) not null) ");
}
Console.WriteLine("Created database");
}
static void Main(string[] args)
{
string dbLocation = Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), "tilecache.s3db");
int minz = 7;
int maxz = 10;
double minx = -95.844727;
double miny = 35.978006;
double maxx = -88.989258;
double maxy = 40.563895;
string mapServiceUrl = "http://sampleserver1.arcgisonline.com/ArcGIS/rest/services/Demographics/ESRI_Census_USA/MapServer";
int maxDegreeOfParallelism = 10;
bool replaceExistingCacheDB = true;
bool showHelp = false;
bool verbose = false;
string mapServiceType = "agsd";
string settings = string.Empty;
bool showInfo = false;
ITileUrlSource tileSource = new OSMTileUrlSource()
{
MapServiceUrl = TileHelper.OSM_BASE_URL_TEMPLATE,
};
var options = new OptionSet()
{
{"h|?|help", "Show this message and exits", h => showHelp = h != null},
{"v|verbose", "Display verbose information logs while running", v => verbose = v != null},
{"t|type=", "Type of the map service to be cached", t => mapServiceType = t.ToLower()},
{"m|mapservice=", "Url of the Map Service to be cached", m => mapServiceUrl = m},
{"s|settings=", "Extra settings needed by the type of map service being used", s => settings = s},
{"o|output=", "Complete file path and file name where the tile cache needs to be outputted", o => dbLocation = o},
{"z|minz=", "Minimum zoom scale at which to begin caching", z => int.TryParse(z, out minz)},
{"Z|maxz=", "Maximum zoom scale at which to end caching", Z => int.TryParse(Z, out maxz)},
{"x|minx=", "Minimum X coordinate value of the extent to cache", x => double.TryParse(x, out minx)},
{"y|miny=", "Minimum Y coordinate value of the extent to cache", y => double.TryParse(y, out miny)},
{"X|maxx=", "Maximum X coordinate value of the extent to cache", X => double.TryParse(X, out maxx)},
{"Y|maxy=", "Maximum Y coordinate value of the extent to cache", Y => double.TryParse(Y, out maxy)},
{"p|parallelops=", "Limits the number of concurrent operations run by TileCutter", p => int.TryParse(p, out maxDegreeOfParallelism)},
{"r|replace=", "Delete existing tile cache MBTiles database if already present and create a new one.", r => Boolean.TryParse(r, out replaceExistingCacheDB)},
{"i|info=", "", i => showInfo = i != null}
};
options.Parse(args);
if (showHelp)
{
ShowHelp(options);
return;
}
var tiles = GetTiles(minz, maxz, minx, miny, maxx, maxy);
if (showInfo)
{
var zoomLevels = Enumerable.Range(0, 21);
var tileCountByZoomLevel = new Dictionary<int, int>();
foreach (var level in zoomLevels)
tileCountByZoomLevel[level] = 0;
foreach (var tile in tiles)
tileCountByZoomLevel[tile.Level]++;
foreach (var item in tileCountByZoomLevel)
if (item.Value > 0)
Console.WriteLine(string.Format("Zoom level {0} - {1} tiles", item.Key, item.Value));
Console.WriteLine("Press any key to exit...");
Console.ReadKey();
return;
}
if (!string.IsNullOrEmpty(mapServiceType))
tileSource = GetTileSource(mapServiceType, mapServiceUrl, settings);
string tileCacheDirectory = Path.GetDirectoryName(dbLocation);
string tilecacheFileName = Path.GetFileNameWithoutExtension(dbLocation);
if (!Directory.Exists(tileCacheDirectory))
{
Console.WriteLine("The tilecache path provided is not valid");
return;
}
string errorLogFile = Path.Combine(tileCacheDirectory, tilecacheFileName + ".log");
//if the user option to delete existing tile cache db is true delete it or else add to it
if (replaceExistingCacheDB && File.Exists(dbLocation))
File.Delete(dbLocation);
//Connect to the sqlite database
string connectionString = string.Format("Data Source={0}; FailIfMissing=False", dbLocation);
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
connection.Open();
//Check if the 'metadata' table exists, if not create it
var rows = connection.GetSchema("Tables").Select("Table_Name = 'metadata'");
if (!rows.Any())
{
var command = connection.CreateCommand();
command.CommandText = "CREATE TABLE metadata (name text, value text);";
command.ExecuteNonQuery();
connection.Execute("CREATE UNIQUE INDEX name ON metadata ('name')");
connection.Execute(@"INSERT INTO metadata(name, value) values (@a, @b)",
new[] {
new { a = "name", b = "cache" },
new { a = "type", b = "overlay" },
new { a = "version", b = "1" },
new { a = "description", b = "some info here" },
new { a = "format", b = "png" },
new { a = "bounds", b = string.Format("{0},{1},{2},{3}", minx, miny, maxx, maxy)}
});
}
//Check if the 'images' table exists, if not create it
rows = connection.GetSchema("Tables").Select("Table_Name = 'images'");
if (!rows.Any())
{
var command = connection.CreateCommand();
command.CommandText = "CREATE TABLE [images] ([tile_id] INTEGER NOT NULL PRIMARY KEY, [tile_md5hash] VARCHAR(256) NOT NULL, [tile_data] BLOB NULL);";
command.ExecuteNonQuery();
command = connection.CreateCommand();
command.CommandText = "CREATE UNIQUE INDEX images_hash on images (tile_md5hash)";
command.ExecuteNonQuery();
}
//Check if the 'map' table exists, if not create it
rows = connection.GetSchema("Tables").Select("Table_Name = 'map'");
if (!rows.Any())
{
var command = connection.CreateCommand();
command.CommandText = "CREATE TABLE [map] ([map_id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [tile_id] INTEGER NOT NULL, [zoom_level] INTEGER NOT NULL, [tile_row] INTEGER NOT NULL, [tile_column] INTEGER NOT NULL);";
command.ExecuteNonQuery();
}
}
FileStream errorLog = File.Create(errorLogFile);
StreamWriter errorWriter = new StreamWriter(errorLog);
Console.WriteLine("Output Cache file is: " + dbLocation);
BlockingCollection<TileImage> images = new BlockingCollection<TileImage>();
Task.Factory.StartNew(() =>
{
ParallelOptions parallelOptions = new ParallelOptions() { MaxDegreeOfParallelism = maxDegreeOfParallelism };
Parallel.ForEach(tiles, parallelOptions, (tile) =>
{
string tileUrl = tileSource.GetTileUrl(tile);
WebClient client = new WebClient();
try
{
byte[] image = client.DownloadData(tileUrl);
images.Add(new TileImage()
{
Tile = tile,
Image = image
});
if (verbose)
Console.WriteLine(string.Format("Tile Level:{0}, Row:{1}, Column:{2} downloaded.", tile.Level, tile.Row, tile.Column));
}
catch (WebException ex)
{
errorWriter.WriteLine(String.Format("{0},{1},{2} - {3}", tile.Level, tile.Column, tile.Row, ex.Message));
errorLog.Flush();
Console.WriteLine(string.Format("Error while downloading tile Level:{0}, Row:{1}, Column:{2} - {3}.", tile.Level, tile.Row, tile.Column, ex.Message));
return;
}
finally { client.Dispose(); }
});
}).ContinueWith(t =>
{
images.CompleteAdding();
if (verbose)
Console.WriteLine("All downloads complete.");
errorLog.Flush();
errorLog.Dispose();
});
int currentTileId = 1;
Action<TileImage[]> processBatch = (batch) =>
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
connection.Open();
using (SQLiteTransaction transaction = connection.BeginTransaction())
{
using (SQLiteCommand mapCommand = connection.CreateCommand(),
imagesCommand = connection.CreateCommand(), tileCountCommand = connection.CreateCommand(),
mapDeleteCommand = connection.CreateCommand(),
imageDeleteCommand = connection.CreateCommand())
{
tileCountCommand.CommandText = "select ifnull(max(tile_id), 1) from images";
var tileCountObj = tileCountCommand.ExecuteScalar();
if (tileCountObj != null)
{
int.TryParse(tileCountObj.ToString(), out currentTileId);
currentTileId++;
}
//Create a dummy query for the [map] table and fill the adapter with it
//the purpose of this is to get the table structure in a DataTable
//the adapter also builds the insert command for it in when it is populated
mapCommand.CommandText = "SELECT * FROM [map] WHERE 1 = 2";
SQLiteDataAdapter mapAdapter = new SQLiteDataAdapter(mapCommand);
//Create a dummy query for the [images] table and fill the adapter with it
//the purpose of this is to get the table structure in a DataTable
//the adapter also builds the insert command for it in when it is populated
imagesCommand.CommandText = "SELECT * FROM [images] WHERE 1 = 2";
SQLiteDataAdapter imagesAdapter = new SQLiteDataAdapter(imagesCommand);
using (SQLiteCommandBuilder mapCmdBuilder = new SQLiteCommandBuilder(mapAdapter),
imagesCmdBuilder = new SQLiteCommandBuilder(imagesAdapter))
{
using (imagesAdapter.InsertCommand = (SQLiteCommand)((ICloneable)imagesCmdBuilder.GetInsertCommand()).Clone())
using (mapAdapter.InsertCommand = (SQLiteCommand)((ICloneable)mapCmdBuilder.GetInsertCommand()).Clone())
{
imagesCmdBuilder.DataAdapter = null;
mapCmdBuilder.DataAdapter = null;
using (DataTable mapTable = new DataTable(),
imagesTable = new DataTable())
{
if (batch.Any())
{
var conditions = batch.Select(b => string.Format("([zoom_level]={0} and [tile_row]={1} and [tile_column]={2})", b.Tile.Level, b.Tile.Row, b.Tile.Column));
var whereClause = string.Join("or", conditions);
var mapDeleteStatement = string.Format("delete from map where {0}", whereClause);
var imageDeleteStatement = string.Format("delete from images where [tile_id] in (select [tile_id] from map where {0})", whereClause);
imageDeleteCommand.CommandText = imageDeleteStatement;
imageDeleteCommand.ExecuteNonQuery();
mapDeleteCommand.CommandText = mapDeleteStatement;
mapDeleteCommand.ExecuteNonQuery();
}
imagesAdapter.Fill(imagesTable);
mapAdapter.Fill(mapTable);
//Dictionary to eliminate duplicate images within batch
Dictionary<string, int> added = new Dictionary<string, int>();
//looping thru keys is safe to do here because
//the Keys property of concurrentDictionary provides a snapshot of the keys
//while enumerating
//the TryGet & TryRemove inside the loop checks for items that were removed by another thread
List<int> tileIdsInCurrentBatch = new List<int>();
foreach (var tileimg in batch)
{
string hash = Convert.ToBase64String(new System.Security.Cryptography.MD5CryptoServiceProvider().ComputeHash(tileimg.Image));
int tileid = -1;
if (!added.ContainsKey(hash))
{
mapCommand.CommandText = "SELECT [tile_id] FROM [images] WHERE [tile_md5hash] = @hash";
mapCommand.Parameters.Add(new SQLiteParameter("hash", hash));
object tileObj = mapCommand.ExecuteScalar();
if (tileObj != null && int.TryParse(tileObj.ToString(), out tileid))
added.Add(hash, tileid);
else
{
tileid = currentTileId++;
added.Add(hash, tileid);
DataRow idr = imagesTable.NewRow();
idr["tile_md5hash"] = hash;
idr["tile_data"] = tileimg.Image;
idr["tile_id"] = added[hash];
imagesTable.Rows.Add(idr);
}
}
tileIdsInCurrentBatch.Add(added[hash]);
DataRow mdr = mapTable.NewRow();
mdr["zoom_level"] = tileimg.Tile.Level;
mdr["tile_column"] = tileimg.Tile.Column;
mdr["tile_row"] = tileimg.Tile.Row;
mdr["tile_id"] = added[hash];
mapTable.Rows.Add(mdr);
}//for loop thru images
tileIdsInCurrentBatch.Clear();
imagesAdapter.Update(imagesTable);
mapAdapter.Update(mapTable);
transaction.Commit();
if (verbose)
Console.WriteLine(String.Format("Saving an image batch of {0}.", batch.Length));
}//using for datatable
}//using for insert command
}//using for command builder
}//using for select command
}
}//using for connection
};
List<TileImage> tilebatch = new List<TileImage>();
foreach (var tileImage in images.GetConsumingEnumerable())
{
tilebatch.Add(tileImage);
if (tilebatch.Count < 50)
continue;
processBatch(tilebatch.ToArray());
tilebatch.Clear();
}
if (verbose)
Console.WriteLine("Saving remaining images that didn't fit into a batch.");
processBatch(tilebatch.ToArray());
tilebatch.Clear();
if (verbose)
Console.WriteLine("Creating Index on table [map] and Creating View [tiles].");
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
connection.Open();
connection.Execute("CREATE UNIQUE INDEX IF NOT EXISTS map_index on map (zoom_level, tile_column, tile_row)");
connection.Execute("CREATE VIEW IF NOT EXISTS tiles as SELECT map.zoom_level as zoom_level, map.tile_column as tile_column, map.tile_row as tile_row, images.tile_data as tile_data FROM map JOIN images on images.tile_id = map.tile_id");
connection.Close();
}
Console.WriteLine("All Done !!!");
}
public void UpdateProfile(EmployeeProfile commandDto, string imgURL, string cartoonURL)
{
string user = System.Web.HttpContext.Current.User.Identity.Name;
using (DbConnection conn = new SQLiteConnection(ConfigurationManager.ConnectionStrings["reedDirectory"].ConnectionString))
{
conn.Open();
conn.Execute("Update EmployeeProfile set Department=@Department, " +
"FirstName=@FirstName, " +
"ImageUrl=@ImageUrl, " +
"LastName=@LastName, " +
"Role=@Role, " +
"Tags=@Tags, " +
"UpdatedBy=@UpdatedBy, " +
"UpdatedOn=@UpdatedOn, " +
"Extension=@Extension, " +
"CartoonURL = @CartoonUrl, " +
"emailaddress = @emailaddress, " +
"SeatingFloor= @SeatingFloor, " +
"seatNo = @seatNo " +
"where ProfileId = @ProfileId",
new
{
Department = commandDto.Department,
FirstName = commandDto.FirstName.Replace("'", "''"),
ProfileId = commandDto.ProfileId,
ImageUrl = (string.IsNullOrEmpty(imgURL) ? commandDto.ImageUrl : imgURL),
LastName = commandDto.LastName.Replace("'", "''"),
Role = commandDto.Role,
Tags = !string.IsNullOrEmpty(commandDto.Tags) ? commandDto.Tags.Replace("'", "''") : "",
UpdatedBy = user,
UpdatedOn = DateTime.Now,
Extension = !string.IsNullOrEmpty(commandDto.Extension) ? commandDto.Extension.Replace("'", "''") : "",
CartoonUrl = (string.IsNullOrEmpty(cartoonURL) ? commandDto.CartoonUrl : cartoonURL),
emailaddress = commandDto.EmailAddress,
SeatingFloor = commandDto.SeatingFloor,
seatNo = commandDto.SeatNo
});
conn.Close();
}
}
public void UnassignSeat(int profileId)
{
string user = System.Web.HttpContext.Current.User.Identity.Name;
using (DbConnection conn = new SQLiteConnection(ConfigurationManager.ConnectionStrings["reedDirectory"].ConnectionString))
{
conn.Open();
conn.Execute(
"update EmployeeProfile set seatNo = null where profileId = @ProfileId",
new
{
ProfileId = profileId
});
conn.Close();
}
}
public void RemoveProfileImage(int profileId, bool isCartoonImg)
{
string query = "Update EmployeeProfile set " + (isCartoonImg ? "CartoonUrl" : "ImageUrl") + " = null where ProfileId = @ProfileId";
using (DbConnection conn = new SQLiteConnection(ConfigurationManager.ConnectionStrings["reedDirectory"].ConnectionString))
{
conn.Open();
conn.Execute(query,
new
{
ProfileId = profileId
});
conn.Close();
}
}