public App()
{
// The root page of your application
MainPage = new ContentPage {
Content = new StackLayout {
VerticalOptions = LayoutOptions.Center,
Children = {
new Label {
XAlign = TextAlignment.Center,
Text = "Welcome to Xamarin Forms!"
}
}
}
};
// path to db
var path = Path.Combine (Environment.GetFolderPath (Environment.SpecialFolder.MyDocuments), "mydb");
// open connection and attempt to apply encryption using PRAGMA statement
var conn = new SQLiteConnection (path);
conn.Execute ("PRAGMA key = 'passme'");
int v = conn.ExecuteScalar<int> ("SELECT count(*) FROM sqlite_master");
conn.Close ();
// open another connection, this time use wrong password. It will still open, but should fail the
// query (see docs on key PRAGMA https://www.zetetic.net/sqlcipher/sqlcipher-api/)
var conn2 = new SQLiteConnection (path);
conn2.Execute ("PRAGMA key = 'wrongpassword'");
int v2 = conn2.ExecuteScalar<int> ("SELECT count(*) FROM sqlite_master");
conn2.Close ();
}
private string CreateDatabase()
{
try
{
using (var connection = new SQLite.SQLiteConnection(System.IO.Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), "bancoteste.db3")))
{
connection.CreateTable <Categoria>(SQLite.CreateFlags.ImplicitPK | SQLite.CreateFlags.AutoIncPK);
connection.CreateTable <Policies>(SQLite.CreateFlags.ImplicitPK | SQLite.CreateFlags.AutoIncPK);
connection.CreateTable <Product>(SQLite.CreateFlags.ImplicitPK | SQLite.CreateFlags.AutoIncPK);
connection.CreateTable <Promotion>(SQLite.CreateFlags.ImplicitPK | SQLite.CreateFlags.AutoIncPK);
connection.CreateTable <Sessao>(SQLite.CreateFlags.ImplicitPK | SQLite.CreateFlags.AutoIncPK);
connection.Execute("DELETE FROM Categoria");
connection.Execute("DELETE FROM Policies");
connection.Execute("DELETE FROM Product");
connection.Execute("DELETE FROM Promotion");
connection.Execute("DELETE FROM Sessao");
}
return("Database created");
}
catch (SQLiteException ex)
{
return(ex.Message);
}
}
private void ShufflePlaylist(SQLiteConnection connection, int currentSong)
{
connection.Execute("create temp table TempPlaylistOrder as select Rank as ShuffledRank from CurrentPlaylistSong order by case when SongId = ? then -1 else abs(random()) end", currentSong);
connection.Execute("update CurrentPlaylistSong set Rank = (select rowid from TempPlaylistOrder where ShuffledRank = Rank) - 1");
connection.Execute("drop table TempPlaylistOrder");
this.CurrentIndex = 0;
}
public static void CreateTables(SQLiteConnection db)
{
db.BeginTransaction();
db.CreateTable<DriveInformation>();
db.CreateTable<DirectoryInformation>();
db.CreateTable<FileInformation>();
db.CreateTable<FileAttributeInformation>();
db.CreateTable<FileAttribute>();
db.Execute("CREATE INDEX if not exists \"main\".\"ix_DirectoryInformation_driveid_path\" ON \"DirectoryInformation\" (\"DriveId\" ASC, \"Path\" ASC)");
db.Execute("CREATE INDEX if not exists \"main\".\"ix_FileInformation_driveid_directoryid\" ON \"FileInformation\" (\"DirectoryId\" ASC, \"DriveId\" ASC)");
db.Commit();
}
public static int ExecuteNonQuery(string sql)
{
using(SQLiteConnection Con = new SQLiteConnection(conStr))
{
return Con.Execute(sql);
}
}
private void UnshufflePlaylist(SQLiteConnection connection, int currentSong)
{
connection.Execute("update CurrentPlaylistSong set Rank = ActualRank");
var song = connection.Query<CurrentPlaylistSong>("Select Rank from CurrentPlaylistSong where SongId = ?", currentSong).FirstOrDefault();
this.CurrentIndex = song == null ? 0 : song.Rank;
}
private static void UpdateData(string pathToDB)
{
var sqlPath = System.IO.Path.GetDirectoryName(System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName) + "\\updateddata.sql";
if (File.Exists(sqlPath))
{
// Updated Data exists.
// Clean existing base data first.
CleanBaseData(pathToDB);
// Update the database with the provided data.
try
{
var commands = File.ReadAllText(sqlPath);
var statements = commands.Split(new[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
using (var db = new SQLite.SQLiteConnection(pathToDB))
{
foreach (var statement in statements)
{
db.Execute(statement);
}
}
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
}
}
}
private static void CleanBaseData(string pathToDB)
{
try
{
using (var db = new SQLite.SQLiteConnection(pathToDB))
{
db.Execute("DELETE FROM EnvironmentalStandard");
db.Execute("DELETE FROM OxygenSolubility");
db.Execute("DELETE FROM OxygenDO");
db.Execute("DELETE FROM Index");
}
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
}
}
public void CompressDatabase()
{
SQLite.SQLiteConnection databaseConnection = this.GetConnection();
if (databaseConnection == null)
{
return;
}
databaseConnection.Execute("VACUUM");
}
private void BtnGuardar_Click(object sender, RoutedEventArgs e)
{
using (SQLite.SQLiteConnection conexion = new SQLite.SQLiteConnection(App.databasePath))
{
string sentenciaSQL = "update contactos set Nombre='"
+ txtNombre.Text + "',Correo='" + txtCorreo.Text + "',Telefono=" + txtTelefono.Text;
conexion.Execute(sentenciaSQL);
}
Close();
}
/// <summary>
/// Haltbarkeitsdatum der Testdaten aktualisieren
/// </summary>
/// <param name="destination"></param>
private void PrepareTestDatabase(string destination)
{
var conn = new SQLite.SQLiteConnection(destination, false);
DateTime bestBefore = new DateTime(2000, 1, 1);
TimeSpan span = DateTime.Today - bestBefore;
int daysAdd = (int)span.TotalDays;
string cmd = string.Format("UPDATE StorageItem SET BestBefore = date(BestBefore, '+{0} day')", daysAdd);
conn.Execute(cmd);
}
public List<Beacon> GetListOfBeacons()
{
lock (dbLock)
{
using (var sqlCon = new SQLiteConnection(DBPath))
{
sqlCon.Execute(Constants.DBClauseSyncOff);
sqlCon.BeginTransaction();
var data = sqlCon.Query<Beacon>("SELECT * FROM Beacon");
return data;
}
}
}
private static void Seed(string pathToDB)
{
var sqlPath = System.IO.Path.GetDirectoryName(System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName) + "\\predefineddata.sql";
if (File.Exists(sqlPath))
{
// Predefined Data exists.
// Read it and Save predefined data.
var commands = File.ReadAllText(sqlPath);
var statements = commands.Split(new[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
using (var db = new SQLite.SQLiteConnection(pathToDB))
{
try
{
foreach (var statement in statements)
{
//Console.WriteLine(statement);
db.Execute(statement);
}
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
}
}
}
else
{
// No Predefined Data exists.
// Generate base data and Store it.
var environmentalStandards = CrystalClearDBUtil.GenerateBaseDataForEnvironmentalStandard();
var oxygenSolubilities = CrystalClearDBUtil.GenerateBaseDataForOxygenSolubility();
var oxygenDOs = CrystalClearDBUtil.GenerateBaseDataForOxygenDO();
var indices = CrystalClearDBUtil.GenerateBaseDataForBaseIndex();
try
{
using (var db = new SQLite.SQLiteConnection(pathToDB))
{
db.InsertAll(environmentalStandards);
db.InsertAll(oxygenSolubilities);
db.InsertAll(oxygenDOs);
db.InsertAll(indices);
}
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
}
}
}
public void CleanUpDB()
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(Constants.DBClauseSyncOff);
sqlCon.BeginTransaction();
try
{
sqlCon.Execute("DELETE FROM Beacon");
sqlCon.Commit();
sqlCon.Execute(Constants.DBClauseVacuum);
}
catch (Exception ex)
{
#if(DEBUG)
System.Diagnostics.Debug.WriteLine("Error in CleanUpDB! {0}--{1}", ex.Message, ex.StackTrace);
#endif
sqlCon.Rollback();
}
}
}
}
// private void UpdateDatbase()
// {
// try {
// using (var conn = new SQLite.SQLiteConnection (pathToDatabase)) {
//
// string sql = @"ALTER TABLE AdPara RENAME TO sqlitestudio_temp_table;
// CREATE TABLE AdPara (ID integer PRIMARY KEY AUTOINCREMENT NOT NULL, PrinterName varchar, Prefix varchar, PaperSize varchar, Warehouse varchar, ReceiptTitle varchar, RunNo integer, CNRunNo integer, SORunNo integer, DORunNo integer, CNPrefix varchar, DOPrefix varchar, SOPrefix varchar, PrinterIP varchar, PrinterType varchar,FooterNote varchar,FooterCNNote varchar,FooterDONote,FooterSONote varchar);
// INSERT INTO AdPara (ID, PrinterName, Prefix, PaperSize, Warehouse, ReceiptTitle, RunNo, CNRunNo, SORunNo, DORunNo, CNPrefix, DOPrefix, SOPrefix) SELECT ID, PrinterName, Prefix, PaperSize, Warehouse, ReceiptTitle, RunNo, CNRunNo, SORunNo, DORunNo, CNPrefix, DOPrefix, SOPrefix FROM sqlitestudio_temp_table;
// DROP TABLE sqlitestudio_temp_table";
// string[] sqls = sql.Split (new char[]{ ';' });
// foreach (string ssql in sqls) {
// conn.Execute (ssql, new object[]{ });
// }
// }
// } catch (Exception ex) {
// Toast.MakeText (this, ex.Message, ToastLength.Long).Show ();
// }
// }
//
// private void UpdateItem()
// {
// try {
// using (var conn = new SQLite.SQLiteConnection (pathToDatabase)) {
//
// string sql = @"ALTER TABLE Item RENAME TO sqlitestudio_temp_table2;
// CREATE TABLE Item (ID integer PRIMARY KEY AUTOINCREMENT NOT NULL, ICode varchar, IDesc varchar, Price float, tax float, taxgrp varchar, isincludesive integer, VIPPrice float, RetailPrice float, WholeSalePrice float, Barcode varchar, StdUom VARCHAR);
// INSERT INTO Item (ID, ICode, IDesc, Price, tax, taxgrp, isincludesive, VIPPrice, RetailPrice, WholeSalePrice, Barcode) SELECT ID, ICode, IDesc, Price, tax, taxgrp, isincludesive, VIPPrice, RetailPrice, WholeSalePrice, Barcode FROM sqlitestudio_temp_table2;
// DROP TABLE sqlitestudio_temp_table2";
// string[] sqls = sql.Split (new char[]{ ';' });
// foreach (string ssql in sqls) {
// conn.Execute (ssql, new object[]{ });
// }
// }
// } catch (Exception ex) {
// //Toast.MakeText (this, ex.Message, ToastLength.Long).Show ();
// AlertShow("UpdateItem() "+ex.Message);
// }
// }
//
private void UpdateItem()
{
try {
using (var conn = new SQLite.SQLiteConnection(pathToDatabase)) {
string sql = @"ALTER TABLE Item RENAME TO sqlitestudio_temp_table;
CREATE TABLE Item (ID integer PRIMARY KEY AUTOINCREMENT NOT NULL, ICode varchar, IDesc varchar, Price float, tax float, taxgrp varchar, isincludesive integer, VIPPrice float, RetailPrice float, WholeSalePrice float, Barcode varchar, StdUom varchar, Class VARCHAR, ImageFilename VARCHAR);
INSERT INTO Item (ID, ICode, IDesc, Price, tax, taxgrp, isincludesive, VIPPrice, RetailPrice, WholeSalePrice, Barcode, StdUom,Class,ImageFilename) SELECT ID, ICode, IDesc, Price, tax, taxgrp, isincludesive, VIPPrice, RetailPrice, WholeSalePrice, Barcode, StdUom,'','' FROM sqlitestudio_temp_table;
DROP TABLE sqlitestudio_temp_table;" ;
string[] sqls = sql.Split(new char[] { ';' });
foreach (string ssql in sqls)
{
conn.Execute(ssql, new object[] { });
}
}
} catch (Exception ex) {
//Toast.MakeText (this, ex.Message, ToastLength.Long).Show ();
//AlertShow("UpdateItem() "+ex.Message);
}
}
public bool CheckContactExists(string contactAccountID)
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
List<ContactDB> contacts =
sqlCon.Query<ContactDB>("SELECT * FROM ContactDB WHERE ContactAccountGuid=?", contactAccountID);
return contacts.Count == 1;
}//end using sqlCon
}//end using lock
}
public static bool Delete <T>(ref T item, string db_path, string table, string strWhere)
{
db_path = ReturnConnection(db_path);
using (SQLite.SQLiteConnection connection = new SQLite.SQLiteConnection(db_path))
{
try
{
if (connection.Execute(string.Concat("DELETE FROM ", table, strWhere)) > 0)
{
return(true);
}
}
catch (Exception ex)
{
Debug.Write(ex.ToString());
}
}
return(false);
}
public void CompressDatabase()
{
SQLite.SQLiteConnection databaseConnection = this.GetConnection();
if (databaseConnection == null)
{
return;
}
databaseConnection.Execute("UPDATE Article SET Manufacturer = RTRIM(Manufacturer) WHERE LENGTH(Manufacturer) <> LENGTH(TRIM(Manufacturer))");
databaseConnection.Execute("UPDATE Article SET SubCategory = RTRIM(SubCategory) WHERE LENGTH(SubCategory) <> LENGTH(TRIM(SubCategory))");
databaseConnection.Execute("UPDATE Article SET StorageName = RTRIM(StorageName) WHERE LENGTH(StorageName) <> LENGTH(TRIM(StorageName))");
databaseConnection.Execute("UPDATE Article SET Supermarket = RTRIM(Supermarket) WHERE LENGTH(Supermarket) <> LENGTH(TRIM(Supermarket))");
databaseConnection.Execute("UPDATE StorageItem SET StorageName = RTRIM(StorageName) WHERE LENGTH(StorageName) <> LENGTH(TRIM(StorageName))");
databaseConnection.Execute("VACUUM");
}
public List<MessageDB> GetAllReceivedMessages(string ownerAccountID)
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
List<MessageDB> messages =
sqlCon.Query<MessageDB>("SELECT * FROM MessageDB WHERE FromAccountGuid<>? ORDER BY MessageSent DESC", ownerAccountID);
messages.ForEach(msg =>
{
msg.MessageStepDBList =
sqlCon.Query<MessageStepDB>("SELECT * FROM MessageStepDB WHERE MessageGuid=?", msg.MessageGuid);
msg.MessageRecipientDBList =
sqlCon.Query<MessageRecipientDB>("SELECT * FROM MessageRecipientDB WHERE MessageGuid=?", msg.MessageGuid);
});
return messages;
}//end using sqlCon
}//end lock
}
/// <summary>
/// Gets all sent a user has sent to the owner.
/// </summary>
/// <param name='fromAccountGuid'>
/// The user that has sent the messages.
/// </param>
/// <param name='ownerAccountGuid'>
/// Owner account GUID. Normally, this should be the user that is currently logged in.
/// </param>
public List<MessageDB> GetAllSentMessagesForUserToOwner(string fromAccountGuid, string ownerAccountGuid)
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
List<MessageDB> messages =
sqlCon.Query<MessageDB>("SELECT * FROM MessageDB WHERE FromAccountGuid=? AND " +
"MessageGuid IN (SELECT MessageGuid FROM MessageRecipientDB WHERE AccountGuid=?)",
fromAccountGuid, ownerAccountGuid);
messages.Sort(delegate(MessageDB x, MessageDB y)
{
return x.MessageSent.CompareTo(y.MessageSent);
});
// Add the MessageSteps
messages.ForEach(msg =>
{
msg.MessageStepDBList =
sqlCon.Query<MessageStepDB>("SELECT * FROM MessageStepDB WHERE MessageGuid=?", msg.MessageGuid);
msg.MessageRecipientDBList =
sqlCon.Query<MessageRecipientDB>("SELECT * FROM MessageRecipientDB WHERE MessageGuid=?", msg.MessageGuid);
});
return messages;
}//end using sqlCon
}//end lock
}
public void UpdateContentPackItemUsageDate(int contentPackItemID, DateTime dateTime)
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
sqlCon.BeginTransaction();
try
{
sqlCon.Execute("UPDATE ContentPackItemDB SET LastDateTimeUsed=? WHERE ContentPackItemID=?", dateTime, contentPackItemID);
sqlCon.Commit();
} catch (Exception ex)
{
sqlCon.Rollback();
#if(DEBUG)
Console.WriteLine("Error updating content pack item date! {0}--{1}", ex.Message, ex.StackTrace);
#endif
}//end try catch
}//end using sqlCon
}//end lock
}
public List<PollingStepDB> GetAllPollingSteps()
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
return sqlCon.Query<PollingStepDB>("SELECT * FROM PollingStepDB");
}//end using sqlCon
}//end lock
}
internal void ParseResultsAndAddToDatabase(TextReader reader, string dbFilename, string registryUrl) {
Directory.CreateDirectory(Path.GetDirectoryName(dbFilename));
using (var db = new SQLiteConnection(dbFilename)) {
db.RunInTransaction(() => {
db.CreateRegistryTableIfNotExists();
using (var jsonReader = new JsonTextReader(reader)) {
while (jsonReader.Read()) {
if (JsonToken.PropertyName != jsonReader.TokenType) {
continue;
}
if ((string)jsonReader.Value == "_updated") {
jsonReader.Read();
db.InsertOrReplace(new RegistryInfo() {
RegistryUrl = registryUrl,
Revision = (long)jsonReader.Value,
UpdatedOn = DateTime.Now
});
continue;
}
var builder = new NodeModuleBuilder();
JToken token = null;
#if DEV14_OR_LATER
try {
#endif
token = JToken.ReadFrom(jsonReader);
#if DEV14_OR_LATER
} catch (JsonReaderException) {
// Reached end of file, so continue.
break;
}
#endif
var module = token.FirstOrDefault();
while (module != null) {
try {
builder.Name = (string)module["name"];
if (string.IsNullOrEmpty(builder.Name)) {
continue;
}
builder.AppendToDescription((string)module["description"] ?? string.Empty);
var time = module["time"];
if (time != null) {
builder.AppendToDate((string)time["modified"]);
}
var distTags = module["dist-tags"];
if (distTags != null) {
var latestVersion = distTags
.OfType<JProperty>()
.Where(v => (string)v.Name == "latest")
.Select(v => (string)v.Value)
.FirstOrDefault();
if (!string.IsNullOrEmpty(latestVersion)) {
try {
builder.LatestVersion = SemverVersion.Parse(latestVersion);
} catch (SemverVersionFormatException) {
OnOutputLogged(String.Format(Resources.InvalidPackageSemVersion, latestVersion, builder.Name));
}
}
}
var versions = module["versions"];
if (versions != null) {
builder.AvailableVersions = GetVersions(versions);
}
AddKeywords(builder, module["keywords"]);
AddAuthor(builder, module["author"]);
AddHomepage(builder, module["homepage"]);
var package = builder.Build();
InsertCatalogEntry(db, package);
} catch (InvalidOperationException) {
// Occurs if a JValue appears where we expect JProperty
} catch (ArgumentException) {
OnOutputLogged(string.Format(Resources.ParsingError, builder.Name));
if (!string.IsNullOrEmpty(builder.Name)) {
var package = builder.Build();
InsertCatalogEntry(db, package);
}
}
builder.Reset();
#if DEV14_OR_LATER
try {
#endif
token = JToken.ReadFrom(jsonReader);
#if DEV14_OR_LATER
} catch (JsonReaderException) {
// Reached end of file, so continue.
break;
}
#endif
module = token.FirstOrDefault();
}
}
}
// FTS doesn't support INSERT OR REPLACE. This is the most efficient way to bypass that limitation.
db.Execute("DELETE FROM CatalogEntry WHERE docid NOT IN (SELECT MAX(docid) FROM CatalogEntry GROUP BY Name)");
});
}
}
public void DeleteContentPackItems(List<ContentPackItemDB> items)
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
sqlCon.BeginTransaction();
try
{
foreach (ContentPackItemDB eachPackItem in items)
{
sqlCon.Execute("DELETE FROM ContentPackItemDB WHERE ID=?", eachPackItem.ID);
}//end foreach
sqlCon.Commit();
} catch (Exception ex)
{
sqlCon.Rollback();
#if(DEBUG)
Console.WriteLine("Error deleting content pack item! {0}--{1}", ex.Message, ex.StackTrace);
#endif
}//end try catch
}//end using sqlCon
}//end lock
}
public List<ContentPackItemDB> GetAllContentPackItems(int forContentPackID)
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
return sqlCon.Query<ContentPackItemDB>("SELECT * FROM ContentPackItemDB WHERE ContentPackID=?", forContentPackID);
}//end sqlCon
}//end lock
}
internal void ParseResultsAndAddToDatabase(TextReader reader,
string dbFilename,
string registryUrl) {
Directory.CreateDirectory(Path.GetDirectoryName(dbFilename));
using (var db = new SQLiteConnection(dbFilename)) {
db.RunInTransaction(() => {
db.CreateRegistryTableIfNotExists();
using (var jsonReader = new JsonTextReader(reader)) {
/*
The schema seems to have changed over time.
The first format we need to handle is an object literal. It
starts with an "_updated" property, with a value of the
timestamp it was retrived, and then a property for each
package, with a name of the package name, and a value which
is on object literal representing the package info. An example
downloaded may start:
{
"_updated": 1413573404788,
"unlink-empty-files": {
"name": "unlink-empty-files",
"description": "given a directory, unlink (remove) all files with a length of 0",
"dist-tags": { "latest": "1.0.1" },
"maintainers": [
{
"name": "kesla",
etc.
The other format is an array literal, where each element is an
object literal for a package, similar to the value of the
properties above, for example:
[
{"name":"008-somepackage","description":"Test Package","dist-tags":{"latest":"1.1.1"}..
,
{"name":"01-simple","description":"That is the first app in order to study the ..."
,
etc.
In this second format, there is no "_updated" property with a
timestamp, and the 'Date' timestamp from the HTTP request for
the data is used instead.
The NPM code that handles the payload seems to be written in
a way to handle both formats
See https://github.com/npm/npm/blob/2.x-release/lib/cache/update-index.js#L87
*/
jsonReader.Read();
switch (jsonReader.TokenType) {
case JsonToken.StartObject:
ReadPackagesFromObject(db, jsonReader, registryUrl);
break;
case JsonToken.StartArray:
// The array format doesn't contain the "_update" field,
// so create a rough timestamp. Use the time from 30 mins
// ago (to set it before the download request started),
// converted to a JavaScript value (milliseconds since
// start of 1970)
var timestamp = DateTime.UtcNow
.Subtract(new DateTime(1970, 1, 1, 0, 30, 0, DateTimeKind.Utc))
.TotalMilliseconds;
ReadPackagesFromArray(db, jsonReader);
db.InsertOrReplace(new RegistryInfo() {
RegistryUrl = registryUrl,
Revision = (long)timestamp,
UpdatedOn = DateTime.Now
});
break;
default:
throw new JsonException("Unexpected JSON token at start of NPM catalog data");
}
}
// FTS doesn't support INSERT OR REPLACE. This is the most efficient way to bypass that limitation.
db.Execute("DELETE FROM CatalogEntry WHERE docid NOT IN (SELECT MAX(docid) FROM CatalogEntry GROUP BY Name)");
});
}
}
public List<ContactDB> GetAllContactsForOwner(string ownerAccountGuid)
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
List<UserDB> contactUsers =
sqlCon.Query<UserDB>("SELECT * FROM UserDB WHERE AccountGuid IN (SELECT ContactAccountGuid FROM ContactDB WHERE OwnerAccountGuid=?) " +
"ORDER BY LastName, FirstName", ownerAccountGuid);
List<ContactDB> contacts = new List<ContactDB>();
foreach (UserDB eachUser in contactUsers)
{
ContactDB contact =
sqlCon.Query<ContactDB>("SELECT * FROM ContactDB WHERE ContactAccountGuid=?", eachUser.AccountGuid).FirstOrDefault();
contact.ContactUser = UserDB.ConvertFromUserDB(eachUser);
contact.ContactOAuthItems =
sqlCon.Query<ContactOAuthDB>("SELECT * FROM ContactOAuthDB WHERE ContactGuid=?", contact.ContactGuid);
contacts.Add(contact);
}//end foreach
return contacts;
}//end using sqlCon
}//end lock
}
public List<ContentInfo> GetAllContentInfo()
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
List<ContentInfo> toReturn =
sqlCon.Query<ContentInfo>("SELECT * FROM ContentInfo");
foreach (ContentInfo eachContentInfo in toReturn)
{
MessageDB msg = sqlCon.Query<MessageDB>("SELECT * FROM MessageDB WHERE ID=?", eachContentInfo.MessageDBID)
.FirstOrDefault();
eachContentInfo.Message = MessageDB.ConvertFromMessageDB(msg);
List<MessageStepDBCache> msgSteps =
sqlCon.Query<MessageStepDBCache>("SELECT * FROM MessageStepDBCache WHERE MessageDBID=?", eachContentInfo.MessageDBID);
eachContentInfo.Message.MessageSteps = new List<MessageStep>();
for (int i = 0; i < msgSteps.Count; i++)
{
eachContentInfo.Message.MessageSteps [i] = MessageStepDBCache.ConvertFromMessageStepDB(msgSteps [i]);
}//end for
List<MessageRecipientDBCache> msgRcp =
sqlCon.Query<MessageRecipientDBCache>("SELECT * FROM MessageRecipientDBCache WHERE MessageDBID=?", eachContentInfo.MessageDBID);
eachContentInfo.Recipients = new List<Guid>();
eachContentInfo.Message.MessageRecipients = new List<Message.MessageRecipient>();
for (int i = 0; i < msgRcp.Count; i++)
{
eachContentInfo.Recipients [i] = new Guid(msgRcp [i].AccountGuid);
eachContentInfo.Message.MessageRecipients [i] = new Message.MessageRecipient() {
AccountID = eachContentInfo.Recipients[i],
IsRead = false
};
}//end for
List<VoiceCache> voiceRecordings =
sqlCon.Query<VoiceCache>("SELECT * FROM VoiceCache WHERE ContentInfoID=?", eachContentInfo.ID);
eachContentInfo.VoiceRecordings =
voiceRecordings.ToDictionary(s => s.StepNumber, s => s.VoiceData);
List<PollingStepDBCache> pollingSteps =
sqlCon.Query<PollingStepDBCache>("SELECT * FROM PollingStepDBCache WHERE ContentInfoID=?", eachContentInfo.ID);
eachContentInfo.PollingSteps =
pollingSteps.ToDictionary(s => s.StepNumber, s => PollingStepDB.ConvertFromPollingStepDB(s));
if (sqlCon.Query<ContentState>("SELECT * FROM ContentState WHERE ContentInfoID=?", eachContentInfo.ID).Count == 1)
{
ContentState contentState = new ContentState(MessageDB.ConvertFromMessage(eachContentInfo.Message));
//TODO: Set animation items to remove in DBManager.GetAllContentInfo()!
#if(DEBUG)
Console.WriteLine("TODO: Set animation items to remove in DBManager.GetAllContentInfo()!");
#endif
contentState.RemoveExistingItems(null,
new List<int>(voiceRecordings
.Where(s => s.IsSent)
.Select(s => s.StepNumber)),
new List<int>(pollingSteps
.Where(s => s.IsSent)
.Select(s => s.StepNumber)), null);
eachContentInfo.ContentState = contentState;
}//end if
}//end foreach
return toReturn;
}//end using sqlCon
}//end lock
}
public List<ContactDB> GetAllContacts()
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
List<ContactDB> toReturn =
sqlCon.Query<ContactDB>("SELECT * FROM ContactDB");
toReturn.ForEach(contact =>
{
contact.ContactOAuthItems =
sqlCon.Query<ContactOAuthDB>("SELECT * FROM ContactOAuthDB WHERE ContactGuid=?", contact.ContactGuid);
contact.ContactUser =
UserDB.ConvertFromUserDB(sqlCon.Query<UserDB>("SELECT * FROM UserDB WHERE AccountGuid=?", contact.ContactAccountGuid)
.First());
});
return toReturn;
}//end sqlCon
}//end lock
}
static DatabaseManager()
{
var folderPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
_dbPath = Path.Combine(folderPath, DatabaseName);
// ## Delete if exist (For test)
if (File.Exists(_dbPath))
{
File.Delete(_dbPath);
}
// Create DB if not exists
if (!File.Exists(_dbPath))
{
using (var db = new SQLite.SQLiteConnection(_dbPath))
{
// Turn on Foreign Key support
var foreignKeyOn = "PRAGMA foreign_keys = ON";
db.Execute(foreignKeyOn);
// Create tables using SQL commands
var createContact = "CREATE TABLE Contact (Id INTEGER PRIMARY KEY NOT NULL, FirstName VARCHAR, MiddleName VARCHAR, LastName VARCHAR, Organization VARCHAR, ImageName VARCHAR, Birthday DATETIME, Favourite BOOL DEFAULT 0)";
db.Execute(createContact);
var createPhoneNumber = "CREATE TABLE PhoneNumber (Id INTEGER PRIMARY KEY NOT NULL, Type VARCHAR, Number VARCHAR, ContactId INTEGER, FOREIGN KEY(ContactId) REFERENCES Contact(Id) ON DELETE CASCADE ON UPDATE CASCADE)";
db.Execute(createPhoneNumber);
var createAddress = "CREATE TABLE Address (Id INTEGER PRIMARY KEY NOT NULL, Type VARCHAR, StreetLine1 VARCHAR, StreetLine2 VARCHAR, City VARCHAR, Province VARCHAR, PostalCode VARCHAR, Country VARCHAR, ContactId INTEGER, FOREIGN KEY(ContactId) REFERENCES Contact(Id) ON DELETE CASCADE ON UPDATE CASCADE)";
db.Execute(createAddress);
var createEmail = "CREATE TABLE Email (Id INTEGER PRIMARY KEY NOT NULL, Type VARCHAR, Address VARCHAR, ContactId INTEGER, FOREIGN KEY(ContactId) REFERENCES Contact(Id) ON DELETE CASCADE ON UPDATE CASCADE)";
db.Execute(createEmail);
var createSpecialDate = "CREATE TABLE SpecialDate (Id INTEGER PRIMARY KEY NOT NULL , Type VARCHAR, Date DATETIME, ContactId INTEGER, FOREIGN KEY(ContactId) REFERENCES Contact(Id) ON DELETE CASCADE ON UPDATE CASCADE)";
db.Execute(createSpecialDate);
var createUrl = "CREATE TABLE Url (Id INTEGER PRIMARY KEY NOT NULL, Type VARCHAR, Link VARCHAR, ContactId INTEGER, FOREIGN KEY(ContactId) REFERENCES Contact(Id) ON DELETE CASCADE ON UPDATE CASCADE)";
db.Execute(createUrl);
var createInstantMessage = "CREATE TABLE InstantMessage (Id INTEGER PRIMARY KEY NOT NULL, Type VARCHAR, Nickname VARCHAR, ContactId INTEGER, FOREIGN KEY(ContactId) REFERENCES Contact(Id) ON DELETE CASCADE ON UPDATE CASCADE)";
db.Execute(createInstantMessage);
var createTag = "CREATE TABLE Tag (Id INTEGER PRIMARY KEY NOT NULL, Name VARCHAR, Detail VARCHAR)";
db.Execute(createTag);
var createContactTagMap = "CREATE TABLE ContactTagMap (Id INTEGER PRIMARY KEY NOT NULL, ContactId INTEGER, TagId INTEGER, FOREIGN KEY(ContactId) REFERENCES Contact(Id) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY(TagId) REFERENCES Tag(Id) ON DELETE CASCADE ON UPDATE CASCADE)";
db.Execute(createContactTagMap);
var createRelationshipType = "CREATE TABLE RelationshipType (Id INTEGER PRIMARY KEY NOT NULL, Name VARCHAR)";
db.Execute(createRelationshipType);
var createRelationship = "CREATE TABLE Relationship (Id INTEGER PRIMARY KEY NOT NULL, ExtraInfo VARCHAR, FromContactId INTEGER, ToContactId INTEGER, RelationshipTypeId INTEGER, FOREIGN KEY(FromContactId) REFERENCES Contact(Id) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY(ToContactId) REFERENCES Contact(Id) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY(RelationshipTypeId) REFERENCES RelationshipType(Id) ON DELETE CASCADE ON UPDATE CASCADE)";
db.Execute(createRelationship);
}
// ## For tests
CreateDummyData(_dbPath);
}
}
public void DeleteMessages(List<MessageDB> messages)
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
sqlCon.BeginTransaction();
try
{
foreach (MessageDB eachMessageDB in messages)
{
eachMessageDB.MessageStepDBList.ForEach(step => {
sqlCon.Execute("DELETE FROM MessageStepDB WHERE StepID=?", step.StepGuid);
});
eachMessageDB.MessageRecipientDBList.ForEach(s => {
sqlCon.Execute("DELETE FROM MessageRecipientDB WHERE AccountGuid=? AND MessageGuid=?", s.AccountGuid, s.MessageGuid);
});
sqlCon.Execute("DELETE FROM MessageDB WHERE MessageGuid=?", eachMessageDB.MessageGuid);
}//end foreach
sqlCon.Commit();
} catch (Exception ex)
{
#if(DEBUG)
Console.WriteLine("Error in DeleteMessages! {0}--{1}", ex.Message, ex.StackTrace);
#endif
sqlCon.Rollback();
}//end try catch
}//end using sqlCon
}//end using lock
}
public ContentInfo GetContentInfoByMessageDBID(int messageDBID)
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
ContentInfo toReturn =
sqlCon.Query<ContentInfo>("SELECT * FROM ContentInfo WHERE MessageDBID=?", messageDBID)
.FirstOrDefault();
if (null != toReturn)
{
MessageDB msg = sqlCon.Query<MessageDB>("SELECT * FROM MessageDB WHERE ID=?", toReturn.MessageDBID)
.FirstOrDefault();
toReturn.Message = MessageDB.ConvertFromMessageDB(msg);
List<MessageStepDBCache> msgSteps =
sqlCon.Query<MessageStepDBCache>("SELECT * FROM MessageStepDBCache WHERE MessageDBID=?", toReturn.MessageDBID);
toReturn.Message.MessageSteps = new List<MessageStep>();
for (int i = 0; i < msgSteps.Count; i++)
{
toReturn.Message.MessageSteps [i] = MessageStepDBCache.ConvertFromMessageStepDB(msgSteps [i]);
}//end for
List<MessageRecipientDBCache> msgRcp =
sqlCon.Query<MessageRecipientDBCache>("SELECT * FROM MessageRecipientDBCache WHERE MessageDBID=?", toReturn.MessageDBID);
toReturn.Recipients = new List<Guid>();
toReturn.Message.MessageRecipients = new List<Message.MessageRecipient>();
for (int i = 0; i < msgRcp.Count; i++)
{
toReturn.Recipients [i] = new Guid(msgRcp [i].AccountGuid);
toReturn.Message.MessageRecipients [i] = new Message.MessageRecipient() {
AccountID = toReturn.Recipients[i],
IsRead = false
};
}//end for
List<VoiceCache> voiceRecordings =
sqlCon.Query<VoiceCache>("SELECT * FROM VoiceCache WHERE ContentInfoID=?", toReturn.ID);
toReturn.VoiceRecordings =
voiceRecordings.ToDictionary(s => s.StepNumber, s => s.VoiceData);
List<PollingStepDBCache> pollingSteps =
sqlCon.Query<PollingStepDBCache>("SELECT * FROM PollingStepDBCache WHERE ContentInfoID=?", toReturn.ID);
toReturn.PollingSteps =
pollingSteps.ToDictionary(s => s.StepNumber, s => PollingStepDB.ConvertFromPollingStepDB(s));
// No need for content state here
// if (sqlCon.Query<ContentState>("SELECT * FROM ContentState WHERE ContentInfoID=?", toReturn.ID).Count == 1)
// {
//
// ContentState contentState = new ContentState(MessageDB.ConvertFromMessage(toReturn.Message));
// contentState.RemoveExistingItems(null,
// new List<int>(voiceRecordings
// .Where(s => s.IsSent)
// .Select(s => s.StepNumber)),
// new List<int>(pollingSteps
// .Where(s => s.IsSent)
// .Select(s => s.StepNumber)));
//
// toReturn.ContentState = contentState;
//
// }//end if
}//end if
return toReturn;
}//end using sqlCon
}//end lock
}
public List<ContentPackDB> GetAllContentPacks(GenericEnumsContentPackType packType)
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
return sqlCon.Query<ContentPackDB>("SELECT * FROM ContentPackDB WHERE ContentPackTypeID=?", packType);
}//end using sqlCon
}//end lock
}
private static void ProcessFolder(SQLiteConnection db, DriveInformation drive, List<string> arrHeaders, DirectoryInfo directory)
{
try {
if (!directory.Exists)
return;
if (IgnoreFolder(directory)) {
return;
}
//go get the cached items for the folder.
var directoryId = DatabaseLookups.GetDirectoryId(db, drive, directory);
var cmd = db.CreateCommand("Select * from " + typeof(FileInformation).Name + " Where DriveId = ? AND DirectoryId = ?", drive.DriveId, directoryId);
var databaseFiles = cmd.ExecuteQuery<FileInformation>();
//obtain the file metadata for all of the files in the directory so we can determine if we care about this folder.
var processList = GetFilesToProcess(databaseFiles, arrHeaders, directory);
if (processList.Count > 0) {
db.BeginTransaction();
Shell32.Shell shell = new Shell32.Shell();
Shell32.Folder folder = shell.NameSpace(directory.FullName);
foreach (var item in processList) {
try {
var fi = item.FileInfo;
var headerList = new List<FileAttributeInformation>();
for (int i = 0; i < arrHeaders.Count; i++) {
var header = arrHeaders[i];
if (!IgnoreHeader(header)) {
var value = folder.GetDetailsOf(item.FolderItem, i);
if (!string.IsNullOrWhiteSpace(value)) {
headerList.Add(new FileAttributeInformation() {
AttributeId = DatabaseLookups.GetAttributeId(db, header),
Value = value
});
}
}
}
//this should have been already checked but we want to be safe.
if (fi.Exists) {
var fileInfo = databaseFiles.FirstOrDefault(info => info.FileName.Equals(fi.Name, StringComparison.OrdinalIgnoreCase));
if (fileInfo == null) {
fileInfo = new FileInformation() {
DriveId = drive.DriveId,
DirectoryId = directoryId,
FileName = fi.Name
};
SetFileInformation(fi, fileInfo);
db.Insert(fileInfo);
Console.WriteLine("Inserted:" + fi.FullName);
}
else {
SetFileInformation(fi, fileInfo);
db.Update(fileInfo);
var deleteCount = db.Execute("Delete from " + typeof(FileAttributeInformation).Name + " WHERE FileId = ?", fileInfo.FileId);
Console.WriteLine("Changed:" + fi.FullName);
}
//save the headers
headerList.ForEach(hl => hl.FileId = fileInfo.FileId);
db.InsertAll(headerList);
}
}
catch (Exception ex) {
Console.WriteLine(ex.ToString());
}
}
db.Commit();
}
//see if we have any additional folders. If we get access denied it will throw an error
try {
foreach (var subDirectory in directory.GetDirectories()) {
ProcessFolder(db, drive, arrHeaders, subDirectory);
}
}
catch (Exception ex) {
Console.WriteLine(ex.ToString());
}
}
catch (UnauthorizedAccessException) {
}
catch (Exception ex) {
Console.WriteLine(ex.ToString());
}
}
/// <summary>
/// Removes secondary database "name" from current db
/// See attachDB()
/// </summary>
/// <param name="db"></param>
/// <param name="name"></param>
public static void DetachDB(this SQLite.SQLiteConnection db, string name)
{
db.Execute($"DETACH DATABASE ?1;", name);
}
protected override void OnCreate(Bundle bundle)
{
base.OnCreate(bundle);
// Create your application here
SetContentView(Resource.Layout.Update);
var buttonUpdate = FindViewById<Button>(Resource.Id.buttonUpdate);
var textViewCurrentVersion = FindViewById<TextView>(Resource.Id.textViewCurrentVersion);
var textViewLatestVersion = FindViewById<TextView>(Resource.Id.textViewLatestVersion);
var progressBarUpdate = FindViewById<ProgressBar>(Resource.Id.progressBarUpdate);
var buttonAppUpdate = FindViewById<Button>(Resource.Id.buttonAppUpdate);
var textViewCurrentAppVersion = FindViewById<TextView>(Resource.Id.textViewCurrentAppVersion);
var textViewLatestAppVersion = FindViewById<TextView>(Resource.Id.textViewLatestAppVersion);
progressBarUpdate.Visibility = ViewStates.Invisible;
var preference = GetSharedPreferences("settings", FileCreationMode.WorldWriteable);
var editor = preference.Edit();
if (!preference.Contains("version"))
{
editor.PutString("version", "20150504");
editor.Commit();
}
textViewCurrentVersion.Text = preference.GetString("version", "20150504");
editor.PutString("appVersion", PackageManager.GetPackageInfo(PackageName, 0).VersionName);
editor.Commit();
textViewCurrentAppVersion.Text = preference.GetString("appVersion", "0");
buttonUpdate.Click += async (sender, e) =>
{
try
{
buttonUpdate.Enabled = false;
buttonUpdate.Text = "正在更新";
var requestVersion = WebRequest.Create("http://kidfruit.github.io/WisdriContacts/LatestDataVersion.txt?" + Guid.NewGuid().ToString());
requestVersion.Method = "GET";
using (var response = await requestVersion.GetResponseAsync())
{
using (var responseStream = response.GetResponseStream())
{
using (var sr = new StreamReader(responseStream, Encoding.UTF8))
{
var receive = sr.ReadToEnd();
textViewLatestVersion.Text = receive;
}
}
}
if (!string.IsNullOrEmpty(textViewLatestVersion.Text.Trim()) &&
textViewCurrentVersion.Text != textViewLatestVersion.Text)
{
progressBarUpdate.Visibility = ViewStates.Visible;
var request = WebRequest.Create("http://kidfruit.github.io/WisdriContacts/data.txt?" + Guid.NewGuid().ToString());
request.Method = "GET";
using (var response = await request.GetResponseAsync())
{
using (var responseStream = response.GetResponseStream())
{
using (var sr = new StreamReader(responseStream, Encoding.GetEncoding("UTF-8")))
{
int count = 0;
long length = responseStream.Length;
byte[] receiveStream = new byte[length];
progressBarUpdate.Visibility = ViewStates.Visible;
progressBarUpdate.Indeterminate = false;
progressBarUpdate.Max = (int)length;
progressBarUpdate.Progress = 0;
while (true)
{
int readLength = (int)(length - count > 1000 ? 1000 : length - count);
int num = await responseStream.ReadAsync(receiveStream, count, readLength);
if (num == 0)
break;
count += num;
progressBarUpdate.Progress = count;
}
var receive = Encoding.UTF8.GetString(receiveStream, 0, (int)length);
//var receive = await sr.ReadToEndAsync();
var byteValue = Convert.FromBase64String(receive);
string decodeReceive = Encoding.UTF8.GetString(byteValue, 0, byteValue.Length);
var personList = JsonConvert.DeserializeObject<List<PERSON>>(decodeReceive);
var dbFile = Path.Combine(System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal), "contacts.db");
using (SQLiteConnection conn = new SQLiteConnection(dbFile))
{
try
{
var sql = "delete from PERSON";
int oldDataCount = conn.Execute(sql);
new AlertDialog.Builder(this).SetMessage(string.Format("旧数据有{0}条", oldDataCount)).Show();
sql = "DROP TABLE [PERSON];";
conn.Execute(sql);
sql = @"CREATE TABLE [PERSON] (
[ID] nvarchar(2147483647) NOT NULL
, [NAME] nvarchar(2147483647) NULL
, [DEPARTMENT] nvarchar(2147483647) NULL
, [MOBILE_PHONE] nvarchar(2147483647) NULL
, [VIRTUAL_PHONE] nvarchar(2147483647) NULL
, [POSITION] nvarchar(2147483647) NULL
, [REGION] nvarchar(2147483647) NULL
, [OFFICE_PHONE] nvarchar(2147483647) NULL
, [INNER_PHONE] nvarchar(2147483647) NULL
, [PY] nvarchar(2147483647) NULL
, [CAR] nvarchar(2147483647) NULL
, CONSTRAINT [sqlite_autoindex_PERSON_1] PRIMARY KEY ([ID])
);";
conn.Execute(sql);
conn.BeginTransaction();
conn.InsertAll(personList);
textViewCurrentVersion.Text = textViewLatestVersion.Text;
editor.PutString("version", textViewCurrentVersion.Text);
editor.Commit();
sql = "select count(ID) from PERSON";
int newDataCount = conn.ExecuteScalar<int>(sql);
new AlertDialog.Builder(this).SetMessage(string.Format("新数据有{0}条", newDataCount)).Show();
conn.Commit();
new AlertDialog.Builder(this).SetMessage("更新完毕").SetPositiveButton("确定", delegate
{
}).Show();
}
catch (Exception ex)
{
conn.Rollback();
new AlertDialog.Builder(this).SetMessage(ex.Message).Show();
}
}
}
}
}
}
else
{
new AlertDialog.Builder(this).SetMessage("无需更新").Show();
}
}
catch (System.Exception ex)
{
new AlertDialog.Builder(this).SetMessage(ex.Message).Show();
}
finally
{
progressBarUpdate.Visibility = ViewStates.Invisible;
buttonUpdate.Enabled = true;
buttonUpdate.Text = "更新数据";
}
};
buttonAppUpdate.Click += async (sender, e) =>
{
try
{
buttonAppUpdate.Enabled = false;
var requestAppVersion = WebRequest.Create("http://kidfruit.github.io/WisdriContacts/LatestAndroidVersion.txt?" + Guid.NewGuid().ToString());
requestAppVersion.Method = "GET";
using (var response = await requestAppVersion.GetResponseAsync())
{
using (var responseStream = response.GetResponseStream())
{
using (var sr = new StreamReader(responseStream, Encoding.UTF8))
{
var receive = sr.ReadToEnd();
textViewLatestAppVersion.Text = receive;
}
}
}
buttonAppUpdate.Enabled = true;
if (!string.IsNullOrEmpty(textViewLatestAppVersion.Text.Trim()) &&
textViewCurrentAppVersion.Text != textViewLatestAppVersion.Text)
{
var uri = Android.Net.Uri.Parse("http://pan.baidu.com/s/1ntypvj7");
var intent = new Intent(Intent.ActionView, uri);
StartActivity(intent);
new AlertDialog.Builder(this).SetMessage("下载更新包").Show();
}
else
{
new AlertDialog.Builder(this).SetMessage("无需更新").Show();
}
}
catch (System.Exception ex)
{
new AlertDialog.Builder(this).SetMessage(ex.Message).Show();
}
finally
{
progressBarUpdate.Visibility = ViewStates.Invisible;
}
};
}
public void UpdateUserImage(string accountGuid, byte[] imageBuffer)
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
sqlCon.BeginTransaction();
try
{
sqlCon.Execute("UPDATE UserDB SET Picture=? WHERE AccountGuid=?", imageBuffer, accountGuid);
sqlCon.Commit();
} catch (Exception ex)
{
Console.WriteLine("Error in UpdateUserImage! {0}--{1}", ex.Message, ex.StackTrace);
sqlCon.Rollback();
}//end try catch
}//end using sqlCon
}//end lock
}
//IntPtr sqlite3_session;
//IntPtr sqlite3_changegroup;
//IntPtr sqlite3_changeset_iter;
#region session management
/// <summary>
/// Attach a secondary database to current db as "name"
/// primary database is always attached as "main"
/// See detachDB()
/// </summary>
/// <param name="db"></param>
/// <param name="dbPath"></param>
/// <param name="name"></param>
public static void AttachDB(this SQLite.SQLiteConnection db, string dbPath, string name)
{
db.Execute($"ATTACH DATABASE ?1 AS ?2;", dbPath, name);
}
public void DeleteContentInfoIfExists(ContentInfo contentInfo)
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
sqlCon.BeginTransaction();
try
{
ContentInfo contentInfoToDelete =
sqlCon.Query<ContentInfo>("SELECT * FROM ContentInfo WHERE ID=?", contentInfo.ID)
.FirstOrDefault();
if (null != contentInfoToDelete)
{
// Take care of the message first.
MessageDB messageDB = sqlCon.Query<MessageDB>("SELECT * FROM MessageDB WHERE ID=?", contentInfo.MessageDBID)
.FirstOrDefault();
if (messageDB.MessageID == default(Guid))
{
// Delete it, because the message has already been inserted from message create.
sqlCon.Execute("DELETE FROM MessageDB WHERE ID=?", messageDB.ID);
}//end if
sqlCon.Execute("DELETE FROM VoiceCache WHERE ContentInfoID=?", contentInfo.ID);
sqlCon.Execute("DELETE FROM PollingStepDBCache WHERE ContentInfoID=?", contentInfo.ID);
sqlCon.Execute("DELETE FROM MessageRecipientDBCache WHERE MessageDBID=?", messageDB.ID);
sqlCon.Execute("DELETE FROM MessageStepDBCache WHERE MessageDBID=?", messageDB.ID);
sqlCon.Execute("DELETE FROM ContentState WHERE ContentInfoID=?", contentInfo.ID);
sqlCon.Execute("DELETE FROM ContentInfo WHERE ID=?", contentInfo.ID);
}//end if
sqlCon.Commit();
} catch (Exception ex)
{
#if(DEBUG)
Console.WriteLine("Error deleting content info! {0}--{1}", ex.Message, ex.StackTrace);
#endif
sqlCon.Rollback();
}//end try catch
}//end using sqlCon
}//end lock
}
/// <summary>
/// Gets all messages the owner user has sent or received.
/// </summary>
/// <param name='ownerAccountGuid'>
/// Owner account GUID. Normally, this should be the user that is currently logged in.
/// </param>
public List<MessageDB> GetAllMessagesForOwner(string ownerAccountGuid)
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
List<MessageDB> toReturn =
sqlCon.Query<MessageDB>("SELECT * FROM MessageDB WHERE FromAccountGuid=?", ownerAccountGuid);
List<MessageRecipientDB> ownerRecipient =
sqlCon.Query<MessageRecipientDB>("SELECT * FROM MessageRecipientDB WHERE AccountGuid=?", ownerAccountGuid);
ownerRecipient.ForEach(rcp => {
toReturn.AddRange(
sqlCon.Query<MessageDB>("SELECT * FROM MessageDB WHERE MessageGuid=?", rcp.MessageGuid)
);
});
toReturn.ForEach(msg => {
msg.MessageRecipientDBList =
sqlCon.Query<MessageRecipientDB>("SELECT * FROM MessageRecipientDB WHERE MessageGuid=?", msg.MessageGuid);
msg.MessageStepDBList =
sqlCon.Query<MessageStepDB>("SELECT * FROM MessageStepDB WHERE MessageGuid=?", msg.MessageGuid);
});
return toReturn;
}//end using sqlCon
}//end lock
}
public List<MessageDB> GetAllMessagesForUser(string userAccountGuid)
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
// TableQuery<MessageDB> messageTable = sqlCon.Table<MessageDB>();
//
// List<MessageDB> otherMessages =
// messageTable
// .Where(s => s.FromAccountGuid == otherAccountGuid)
// .ToList();
List<MessageDB> otherMessages =
sqlCon.Query<MessageDB>("SELECT * FROM MessageDB WHERE FromAccountGuid=?", userAccountGuid);
List<MessageDB> toReturn = new List<MessageDB>();
toReturn.AddRange(otherMessages);
// Sort the output list
toReturn.Sort(delegate(MessageDB x, MessageDB y)
{
return x.MessageSent.CompareTo(y.MessageSent);
});
// Add the MessageSteps
toReturn.ForEach(msg =>
{
msg.MessageStepDBList =
sqlCon.Query<MessageStepDB>("SELECT * FROM MessageStepDB WHERE MessageGuid=?", msg.MessageGuid);
msg.MessageRecipientDBList =
sqlCon.Query<MessageRecipientDB>("SELECT * FROM MessageRecipientDB WHERE MessageGuid=?", msg.MessageGuid);
});
return toReturn;
}//end using sqlCon
}//end lock
}
public void DeletePollingStep(PollingStepDB pollingStep)
{
lock (this.dbLock)
{
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.Execute(WZConstants.DBClauseSyncOff);
sqlCon.BeginTransaction();
try
{
sqlCon.Execute("DELETE FROM PollingStepDB WHERE MessageGuid=? AND StepNumber=?", pollingStep.MessageGuid, pollingStep.StepNumber);
sqlCon.Commit();
} catch (Exception ex)
{
Console.WriteLine("Error in DeletePollingStep! {0}--{1}", ex.Message, ex.StackTrace);
sqlCon.Rollback();
}//end try catch
}//end using sqlCon
}//end lock
}
public bool SetupDB()
{
lock (this.dbLock)
{
try
{
// LOL db
using (SQLiteConnection sqlCon = new SQLiteConnection(this.DBPath))
{
sqlCon.CreateTable<UserDB>();
sqlCon.CreateTable<ContactDB>();
sqlCon.CreateTable<ContactOAuthDB>();
sqlCon.CreateTable<ContentPackDB>();
sqlCon.CreateTable<ContentPackItemDB>();
sqlCon.CreateTable<MessageDB>();
sqlCon.CreateTable<MessageRecipientDB>();
sqlCon.CreateTable<MessageStepDB>();
sqlCon.CreateTable<PollingStepDB>();
sqlCon.CreateTable<ContentInfo>();
sqlCon.CreateTable<ContentState>();
sqlCon.CreateTable<MessageStepDBCache>();
sqlCon.CreateTable<MessageRecipientDBCache>();
sqlCon.CreateTable<PollingStepDBCache>();
sqlCon.CreateTable<VoiceCache>();
sqlCon.Execute(WZConstants.DBClauseVacuum);
}//end using sqlCon
// AnimationData db
using (SQLiteConnection sqlCon = new SQLiteConnection(this.AnimationDBPath))
{
sqlCon.CreateTable<AnimationInfo>();
sqlCon.CreateTable<FrameInfo>();
sqlCon.CreateTable<LayerInfo>();
sqlCon.CreateTable<DrawingInfo>();
sqlCon.CreateTable<BrushItem>();
sqlCon.CreateTable<TransitionInfo>();
sqlCon.CreateTable<TransitionEffectSettings>();
sqlCon.CreateTable<PathPointDB>();
sqlCon.CreateTable<AnimationAudioInfo>();
sqlCon.CreateTable<UndoInfo>();
// Create tables here
sqlCon.Execute(WZConstants.DBClauseVacuum);
}//end using sqlCon
return true;
} catch (SQLiteException ex)
{
throw ex;
} catch (Exception ex)
{
throw ex;
}
/*finally
{
if (File.Exists(this.DBPath))
{
// Mark the database to be excluded from iCloud backups.
NSError error = NSFileManager.SetSkipBackupAttribute(this.DBPath, true);
if (null != error)
{
Console.WriteLine("Could not mark LOL DB's SkipBackupAttribute: {0}", error.LocalizedDescription);
error.Dispose();
}//end if
}//end if
if (File.Exists(this.AnimationDBPath))
{
// Mark the database to be excluded from iCloud backups.
NSError error = NSFileManager.SetSkipBackupAttribute(this.AnimationDBPath, true);
if (null != error)
{
Console.WriteLine("Could not mark AnimationData DB's SkipBackupAttribute: {0}", error.LocalizedDescription);
error.Dispose();
}//end if
}//end if
}//end try catch finally
*/
}//end lock
}
