|
public ExecuteNonQuery ( |
||
| command | The SQLCommand to be run. | |
| return | int | |
/**
* Updates a Slot1 and Slot2 of any GEItem in the database instantly.
* @param GEItem
*/
public void updateGEItemSlotsDatabase(GEItem geItem)
{
try
{
SQLiteDatabase db = new SQLiteDatabase(Constants.databaseName);
/**
* Each time you do in GESession.cs [checkOffer or abortOffer]
* A Slot1 and Slot2 is generated but those Slot's themselves are never saved, less data like this.
* Only thing thats saved is how much to generate of those Slots next time you call [checkOffer].
* Even generated Slots check against how much they can truly take out.
*/
if (geItem is BuyOffer)
{
db.ExecuteNonQuery("UPDATE grandExchangeBuying SET collectedItem = " + geItem.getAmountCollectedItem() + ", collectedGold = " + geItem.getAmountCollectedGold() + " WHERE slot = " + geItem.getSlot() + " AND playerHash = " + geItem.getPlayerHash());
}
else if (geItem is SellOffer)
{
db.ExecuteNonQuery("UPDATE grandExchangeSelling SET collectedItem = " + geItem.getAmountCollectedItem() + ", collectedGold = " + geItem.getAmountCollectedGold() + " WHERE slot = " + geItem.getSlot() + " AND playerHash = " + geItem.getPlayerHash());
}
db.CloseDatabase();
}
catch (Exception e)
{
misc.WriteError("GrandExchange Error]: " + e.Message);
}
}
public void CreateDirectory(FileSystemPath path)
{
DataTable parentDir = db.GetDataTable("SELECT * FROM directories WHERE fullpath = @path",
new Dictionary <string, object>()
{
{ "@path", path.ParentPath.Path }
});
string sqlCreateDirectory = "INSERT INTO directories (dirname, fullpath) VALUES (@dirname, @fullpath)";
db.ExecuteNonQuery(sqlCreateDirectory, new Dictionary <string, object>()
{
{ "@dirname", path.EntityName },
{ "@fullpath", path.Path }
});
double now = DateTime.Now.UnixTimeStamp();
string sqlCreateEntity =
"INSERT INTO entities (fname, dir, cdate, mdate, isdir, accessdate, size) VALUES (@fname, @dir, @cdate, @mdate, @isdir, @accessdate, @size)";
db.ExecuteNonQuery(sqlCreateEntity, new Dictionary <string, object>()
{
{ "@fname", path.EntityName },
{ "@dir", parentDir.Rows[0]["id"] },
{ "@cdate", now },
{ "@mdate", now },
{ "@isdir", 1 },
{ "@accessdate", now },
{ "@size", 0 }
});
}
private void ReqOnResponseReceived(File obj)
{
switch (op)
{
case Operation.CREATE:
db.ExecuteNonQuery(
"INSERT INTO gdrive (filename, fileid, directory) VALUES (@fileName, @newFileId, 0)",
new Dictionary <string, object>()
{
{ "@fileName", fileName },
{ "@newFileID", obj.Id }
});
break;
case Operation.UPDATE:
db.ExecuteNonQuery(
"UPDATE gdrive set fileid = @newFileId WHERE filename = @fileName and directory = 0",
new Dictionary <string, object>()
{
{ "@newFileId", obj.Id },
{ "@fileName", fileName }
});
break;
case Operation.NONE:
default:
break;
}
}
private void InitializeTables(SQLiteDatabase db)
{
db.ExecuteNonQuery("BEGIN EXCLUSIVE");
for(int i = 0; i < CREATE_Commands.Length; i++)
{
db.ExecuteNonQuery(CREATE_Commands[i]);
}
}
private void InitializeTables(SQLiteDatabase db)
{
db.ExecuteNonQuery("BEGIN EXCLUSIVE");
for (int i = 0; i < CREATE_Commands.Length; i++)
{
db.ExecuteNonQuery(CREATE_Commands[i]);
}
}
private static void ParseRaceHistory(int heatNo)
{
HeatResult heat = ClubSpeedAustin.GetHeatResults(heatNo);
if (heat == null)
{
return;
}
string racersQuery = "INSERT INTO Racers VALUES ";
string racesQuery = "INSERT INTO Races VALUES";
string lapsQuery = "INSERT INTO Laps VALUES";
for (int i = 0; i < heat.Racers.Count; i++)
{
string racerName = heat.Racers[i].RacerName.Replace("'", "''");
racersQuery += string.Format("('{0}', '{1}'){2}", heat.Racers[i].CustId, racerName, (i < heat.Racers.Count - 1) ? "," : ";");
racesQuery += string.Format("('{0}', '{1}', '{2}', '{3}'){4}", heat.HeatNo, heat.Racers[i].CustId, heat.Racers[i].Kart, heat.DateTime.ToString("yyyy-MM-dd HH:mm:ss"), (i < heat.Racers.Count - 1) ? "," : ";");
}
for (int i = 0; i < heat.Laps.Count; i++)
{
lapsQuery += string.Format("('{0}', '{1}', '{2}', '{3}'){4}", heat.HeatNo, heat.Laps[i].CustId, heat.Laps[i].LapNum, heat.Laps[i].LapTime, (i < heat.Laps.Count - 1) ? "," : ";");
}
_database.ExecuteNonQuery(racersQuery + racesQuery + lapsQuery);
}
static void SafeMain1(string[] args)
{
string name = "test.db";
if (File.Exists(name))
{
File.Delete(name);
}
using (var db = new SQLiteDatabase(name))
{
using (var tok = db.GetTokenizer("unicode61", "remove_diacritics=0", "tokenchars=.=", "separators=X"))
{
Console.WriteLine(string.Join(Environment.NewLine, tok.Tokenize("hello friends")));
//GC.Collect(1000, GCCollectionMode.Forced, true);
}
var sp = new StopWordTokenizer(db);
Console.WriteLine(db.Configure(SQLiteDatabaseConfiguration.SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER, true, 1));
db.SetTokenizer(sp);
db.ExecuteNonQuery("CREATE VIRTUAL TABLE tok1 USING fts3tokenize('" + sp.Name + "');");
for (int i = 0; i < 10; i++)
{
var tokens = db.LoadRows(@"SELECT token, start, end, position FROM tok1 WHERE input=?;",
"This is a test sentence.");
Console.Write(tokens.ToArray().Length);
//GC.Collect(1000, GCCollectionMode.Forced, true);
}
//Console.WriteLine(string.Join(Environment.NewLine, tokens.Select(t => t["token"])));
}
}
static void CreateBooksTable()
{
string createTable = "create table Books (TITLE varchar(50), " +
"AUTHOR varchar(50), PUBDATE varchar(10), ISBN int)";
db.ExecuteNonQuery(createTable);
}
public SQLiteDataStorage(string database = ":memory:")
{
Db = new SQLiteDatabase(database);
foreach (string command in PRAGMA_Commands)
{
Db.ExecuteNonQuery(command);
}
}
/// <summary>
/// 创建数据库
/// </summary>
/// <returns>true：创建成功，false：创建失败</returns>
public override bool CreateDatabase()
{
int count = 0;
string path = System.IO.Path.GetDirectoryName(this.file);
if (!System.IO.Directory.Exists(path))
{
System.IO.Directory.CreateDirectory(path);
}
using (var db = new SQLiteDatabase(this.connectionString))
{
count += db.ExecuteNonQuery("create table _tb_create_db_temp(id int not null)");
count += db.ExecuteNonQuery("drop table _tb_create_db_temp");
}
return(count > 0);
}
//private DeDupeFileSystem fs;
private void setUpDatabase(SQLiteDatabase db)
{
// Not that this matters - as input is not coming from externally....
// But I'm sure some junior cyber "expert" would bitch about it so....
db.ExecuteNonQuery(System.IO.File.ReadAllText(System.IO.Directory.GetCurrentDirectory() + "/migrations/sqlite/1.sql"));
//db.ExecuteNonQuery("INSERT INTO settings (`key`, `value`) VALUES ('version', '1.0')");
db.ExecuteNonQuery("INSERT INTO settings (`key`, `value`) VALUES (@setting, @value)", new Dictionary <string, object>()
{
{ "@setting", "version" },
{ "@value", "0.3b" }
});
//db.ExecuteNonQuery("INSERT INTO settings (`key`, `value`) VALUES ('migration', '1')");
db.ExecuteNonQuery("INSERT INTO settings (`key`, `value`) VALUES (@setting, @value)", new Dictionary <string, object>()
{
{ "@setting", "migration" },
{ "@value", "1" }
});
}
/// <summary>
/// This tests the SQLite in-memory DB by creating some data and then selecting it
/// </summary>
public static void TestDatabase()
{
var watch = new DiagnosticTimer();
var db = new SQLiteDatabase(":memory:");
watch.Checkpoint("Create DB");
db.ExecuteNonQuery("CREATE TABLE Root (intIndex INTEGER PRIMARY KEY, strIndex TEXT, nr REAL)");
watch.Checkpoint("Create table 1");
db.ExecuteNonQuery("CREATE TABLE This (intIndex INTEGER PRIMARY KEY, strIndex TEXT, nr REAL)");
watch.Checkpoint("Create table 2");
db.ExecuteNonQuery("CREATE INDEX RootStrIndex ON Root (strIndex)");
const string insertCommand = "INSERT INTO Root VALUES (?,?,?)";
int i;
var stmt = new SQLiteVdbe(db, insertCommand);
long key = 1999;
for (i = 0; i < 10000; i++)
{
key = (3141592621L * key + 2718281829L) % 1000000007L;
stmt.Reset();
stmt.BindLong(1, key);
stmt.BindText(2, key.ToString());
stmt.BindDouble(3, 12.34);
stmt.ExecuteStep();
}
stmt.Close();
watch.Checkpoint("Insert 10000 rows");
i = 0;
var c1 = new SQLiteVdbe(db, "SELECT * FROM Root ORDER BY intIndex LIMIT 5000");
while (c1.ExecuteStep() != Sqlite3.SQLITE_DONE)
{
long intKey = c1.Result_Long(0);
key = intKey;
i += 1;
}
c1.Close();
var diagnostic = watch.LastCheckpoint("Select 5000 sorted rows");
MessageBox.Show(diagnostic);
}
public static bool Delete(string userId, string profileId)
{
bool deleted = false;
SQLiteDatabase db = new SQLiteDatabase(true);
// Can only delete planned
string sql = @"delete from cycli_profile_spots where ProfileId = @p";
db.ExecuteNonQuery(sql, "@p", profileId);
sql = @"delete from cycli_profiles where ProfileId = @p";
db.ExecuteNonQuery(sql, "@p", profileId);
try
{
db.CommitTransaction();
deleted = true;
} // No need to close Db as this is done by CommitTranaction
catch (Exception ex)
{
db.RollbackTransaction();
Console.WriteLine(ex);
}
return deleted;
}
internal bool SaveAnswers(IList <Answer> answers)
{
SQLiteDatabase db = new SQLiteDatabase();
StringBuilder sql = new StringBuilder(Constants.SqlSaveAnswers);
foreach (Answer answer in answers)
{
sql.AppendFormat("({0},{1},{2}),", answer.QuestionId, answer.ParticipantId, answer.IsCorrect ? 1: 0);
}
return(db.ExecuteNonQuery(sql.ToString().TrimEnd(',')) > 0);
}
public static string ChangeFriendCode(string userId)
{
SQLiteDatabase db = new SQLiteDatabase();
// Some logic here - we only allow a new friend if
// (a) The friendship does not already exist
// (b) The friendCode is correct
string g = Guid.NewGuid().ToString();
string sqlFriendCode = "update cycli_riders set friendCode=@g where UserId = @u";
db.ExecuteNonQuery(sqlFriendCode, "@g", g, "@u", userId);
// This query checks that the user has not already been invited
db.Close();
return g;
}
private void runMigrations(SQLiteDatabase db)
{
string[] files = System.IO.Directory.GetFiles(System.IO.Directory.GetCurrentDirectory() + "/migrations/sqlite/").Select(Path.GetFileName).ToArray();
files = files.OrderByNatural(file => file).ToArray();
//int lastMigration = Int32.Parse(db.ExecuteScalar("SELECT value FROM settings WHERE key = 'migration'"));
int lastMigration = Int32.Parse(db.ExecuteScalar("SELECT value FROM settings WHERE key = @migration", new Dictionary <string, object>()
{
{ "@migration", "migration" }
}));
Log.Instance.Add($"lastMigration = {lastMigration}");
if (files.Length > 0)
{
int highestMigration = lastMigration;
foreach (string file in files)
{
int migrationNumber = Int32.Parse(file.Split('.')[0]);
Log.Instance.Add($"migrationNumber = {migrationNumber}");
if (migrationNumber > lastMigration)
{
db.ExecuteNonQuery(System.IO.File.ReadAllText(System.IO.Directory.GetCurrentDirectory() + $"/migrations/sqlite/{migrationNumber}.sql"));
highestMigration = migrationNumber;
Log.Instance.Add($"highestMigration = {highestMigration}");
}
}
//db.ExecuteNonQuery($"UPDATE settings set key = '{highestMigration}' WHERE value = 'migration'");
Log.Instance.Add($"key = {highestMigration}");
db.ExecuteNonQuery("UPDATE settings set value = @highestMigration WHERE key = @migration",
new Dictionary <string, object>()
{
{ "@highestMigration", highestMigration },
{ "@migration", "migration" }
});
}
}
private SQLiteDatabase OpenDB(string fileName)
{
if (File.Exists(fileName))
{
File.Delete(fileName);
}
var db = new SQLiteDatabase(fileName);
for (int i = 0; i < PRAGMA_Commands.Length; i++)
{
db.ExecuteNonQuery(PRAGMA_Commands[i]);
}
return(db);
}
public void AddFile(string file, byte[] data)
{
string check = _db.ExecuteScalar("SELECT name FROM filestorage WHERE name = @name",
new Dictionary <string, object>()
{
{ "@name", file }
});
if (check != string.Empty)
{
throw new Exception("File already exists in storage - no support for overwriting");
}
long start, end, size;
size = data.Length;
if (_file != string.Empty)
{
using (var fs = new FileStream(_file, FileMode.OpenOrCreate))
{
long endPoint = fs.Length;
fs.Seek(endPoint, SeekOrigin.Begin);
start = fs.Position;
end = start + size;
fs.Write(data);
}
}
else
{
long endPoint = _stream.Length;
_stream.Seek(endPoint, SeekOrigin.Begin);
start = _stream.Position;
end = start + size;
_stream.Write(data);
}
_db.ExecuteNonQuery(
"INSERT INTO filestorage (name, start, end, size) VALUES (@name, @start, @end, @size)",
new Dictionary <string, object>()
{
{ "@name", file },
{ "@start", start },
{ "@end", end },
{ "@size", size }
});
}
public EventRecorder(string pPath, string pDatabaseFilename)
{
//check if file exists
if (System.IO.File.Exists(pPath + pDatabaseFilename)) {
_database = new SQLiteDatabase(pPath + pDatabaseFilename);
Console.WriteLine("opened old database");
}
else {
if(!System.IO.Directory.Exists(pPath))
System.IO.Directory.CreateDirectory(pPath);
SQLiteConnection.CreateFile(pPath + pDatabaseFilename);
_database = new SQLiteDatabase(pPath + pDatabaseFilename);
_database.ExecuteNonQuery(
@"CREATE TABLE [events] (
[id] INTEGER NOT NULL PRIMARY KEY,
[name] VARCHAR(64) NULL,
[time_start] INTEGER NOT NULL,
[time_end] INTEGER NOT NULL)");
}
}
public static bool ReturnResults(string SQLStatement, string DatabaseLocation, ref DataSet ds, out string message)
{
//Add a call here to a parser that will
//ensure the SQLStatement is properly formed
if (SQLStatement.ToLower().StartsWith("select") || SQLStatement.ToLower().StartsWith("pragma"))
{
SQLiteDatabase db = new SQLiteDatabase(DatabaseLocation);
ds = db.ExecuteQuery(SQLStatement);
message = string.Format(ds != null ? "ExecuteQuery: ok" : "ExecuteQuery Failed");
return(ds == null);
}
else
{
int result = SQLiteDatabase.ExecuteNonQuery(SQLStatement);
ds = null;
message = string.Format("ExecuteNonQuery: Records Modified {0}", result);
return(result > -1);
}
}
public void CustomTokenizer_tokenize()
{
using (var db = new SQLiteDatabase(":memory:"))
{
using (var tok = db.GetTokenizer("unicode61", "remove_diacritics=0", "tokenchars=.=", "separators=X"))
{
Assert.AreEqual("hellofriends", string.Join(string.Empty, tok.Tokenize("hello friends")));
GC.Collect(1000, GCCollectionMode.Forced, true);
}
var sp = new StopWordTokenizer(db);
Assert.AreEqual(1, db.Configure(SQLiteDatabaseConfiguration.SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER, true, 1));
db.SetTokenizer(sp);
db.ExecuteNonQuery("CREATE VIRTUAL TABLE tok1 USING fts3tokenize('" + sp.Name + "');");
var tokens = db.LoadRows(@"SELECT token, start, end, position FROM tok1 WHERE input=?;",
"This is a test sentence.");
Assert.AreEqual("testsentence", string.Join(string.Empty, tokens.Select(t => t["token"])));
}
}
/// <summary>
/// Handles the OK button, adds a word
/// </summary>
private void btnOK_Click(object sender, EventArgs e)
{
// Checks whether the description and word are filled in
if (txtDesc.Text != "" && txtWord.Text != "")
{
SQLiteDatabase db = new SQLiteDatabase();
/* Attempts an insert and if successful it clears the desc and word boxes
* to allow further entry, else it shows a messagebox and since most errors
* will be that the word is already there, the error is shown */
try
{
db.ExecuteNonQuery(string.Format("INSERT INTO words VALUES (\"{0}\", \"{1}\")",
txtWord.Text, txtDesc.Text));
txtDesc.Clear();
txtWord.Clear();
}
catch (Exception ex)
{
MessageBox.Show(
string.Format("Something went wrong, the error is given below:\n\n{0}", ex.Message),
"Error", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
}
}
/// <summary>
/// Adds a student to the system
/// </summary>
/// <param name="name">The student's full name</param>
/// <param name="pass">The student's password</param>
/// <param name="stuClass">The student's class</param>
public static void addStudent(string name, string pass, string stuClass)
{
/* The following variables are
* db - Instance of the SQLiteDatabase class
* hash - The SHA-256 hash of the password to be added as a string
* added - Used to avoid a bug with adding students if one has been deleted
* attempts - See above
* studentID - The ID of the student to be added
*/
SQLiteDatabase db = new SQLiteDatabase();
string hash = Password.hashAsString(pass);
bool added = false;
int attempts = 0;
int studentID = Convert.ToInt16(db.ExecuteScalar("SELECT id FROM students ORDER BY id DESC")) + 1;
// While the db call hasn't executed
while (!added)
{
try
{
db.ExecuteNonQuery(string.Format("INSERT INTO students VALUES ({0}, \"{1}\", \"{2}\", \"{3}\");",
studentID, name, stuClass, hash));
added = true;
}
// Checks the number of attempts and if they're less than 10 it adds one to the studentID
catch
{
if (attempts > 9)
{
throw new Exception("Too many attempts");
}
studentID++;
attempts++;
}
}
}
public void Schedule()
{
// Set race to scheduled
string sqlRiderUpdate = @"update cycli_race_riders set Status='Scheduled' " +
"where RaceId=@r and Status='Joined'";
string sqlRaceUpdate = @"update cycli_races set Status='Scheduled' " +
"where RaceId=@r and Status='Planned'";
SQLiteDatabase db = new SQLiteDatabase(true);
try
{
db.ExecuteNonQuery(sqlRaceUpdate, "@r", this.RaceId);
db.ExecuteNonQuery(sqlRiderUpdate, "@r", this.RaceId);
db.CommitTransaction();
}
catch (Exception ex)
{
try
{
db.RollbackTransaction();
}
catch (Exception ex1)
{
}
}
finally
{
// Don't need to close on a transaction
}
this.Status = @"Scheduled";
foreach (Participant p in this.Participants)
{
if (p.Status == @"Joined")
{
p.Status = @"Scheduled";
}
}
}
public void FinishRider(string userId)
{
SQLiteDatabase db = new SQLiteDatabase();
try
{
string sqlRiderUpdate = @"update cycli_race_riders set Status='Finished' " +
"where RaceId=@r and UserId=@u and Status='Started'";
db.ExecuteNonQuery(sqlRiderUpdate, "@r", this.RaceId, "@u", userId);
}
finally
{
db.Close();
}
// SHould always be here
_Participants[userId].Status = @"Finished";
}
public static void SetRiderHandicap(string userId, string riderId, string raceId, int handicap)
{
SQLiteDatabase db = new SQLiteDatabase();
// Update with checks on race status and ownership
string sql = @"update cycli_race_riders set Handicap=@h " +
"where RaceId=@r and UserId=@rr and (Status='Invited' or Status='Joined') " +
"and exists (select 1 from cycli_races where RaceId=@r1 and Status='Planned' and RaceDirectorId=@u)";
db.ExecuteNonQuery(sql, "@h", handicap, "@r", raceId, "@rr", riderId,"@r1",raceId, "@u", userId);
db.Close();
}
public static Race New(string userId)
{
Race race = null;
UserRider rider = UserRider.Load(userId);
if (rider != null)
{
// Check we haven't exeeded our limit - this is a backstop test
// First check prevents a user creating races
Race[] existingRaces = Race.LoadPlannedOrRacing(userId);
if (existingRaces.Length < rider.MaximumRaces)
{
string newRaceId = Guid.NewGuid().ToString();
long startDateTime = Utilities.DbTime.ToDbSecs(DateTime.UtcNow.AddDays(7));
SQLiteDatabase db = new SQLiteDatabase(true);
string sql = "insert into cycli_races (RaceId, StartDateTime, RaceDirectorId) " +
"values (@r, @s, @rd)";
db.ExecuteNonQuery(sql, "@r", newRaceId, "@s", startDateTime, "@rd", userId);
sql = "insert into cycli_race_riders (UserId, RaceId) " +
"values (@u, @r)";
db.ExecuteNonQuery(sql, "@r", newRaceId, "@u", userId);
try
{
db.CommitTransaction();
try
{
race = Load(newRaceId);
}
catch (Exception ex1)
{
Console.WriteLine(ex1);
}
} // No need to close Db as this is done by CommitTranaction
catch (Exception ex)
{
db.RollbackTransaction();
Console.WriteLine(ex);
}
}
}
return race;
}
public static void JoinRace(string userId, string raceId)
{
// What is the race status
SQLiteDatabase db = new SQLiteDatabase();
string sql = @"update cycli_race_riders set Status='Joined' where UserId=@u and RaceId In (" +
"select r.RaceId from cycli_races r, cycli_race_riders rr where r.RaceId=@r and r.RaceId = rr.RaceId and r.Status='Planned')";
db.ExecuteNonQuery(sql, "@r", raceId, "@u", userId);
db.Close();
Race race = Race.Load(raceId);
if (race != null)
{
CycliManager.Instance.SendRiderJoin(race, userId);
}
}
public static void BailVirtualRider(string userId, string riderId, string raceId)
{
SQLiteDatabase db = new SQLiteDatabase();
string sql = "delete from cycli_race_riders " +
"where Status='Joined' and RaceId=@r and UserId=@f " +
"and exists (select 1 from cycli_races where RaceId=@r1 and Status='Planned' and RaceDirectorId=@u)";
db.ExecuteNonQuery(sql, "@r", raceId, "@f", riderId, "r1", raceId, "@u", userId);
db.Close();
Race race = Race.Load(raceId);
if (race != null)
{
CycliManager.Instance.SendRiderBail(race, riderId);
}
}
public void Save(string userId)
{
if (userId != this.UserId)
{
// stop anyone other than the owner changing
return;
}
long timeNowDb = Utilities.DbTime.ToDbSecs(DateTime.UtcNow);
// It's an update
string sqlProfile = @"update cycli_profiles set Name=@n, Description=@d, Favourite=@f, ModifiedOn=@m where ProfileId=@p";
string sqlDeleteSpots = @"delete from cycli_profile_spots where ProfileId=@p";
string sqlSpot = @"insert into cycli_profile_spots (ProfileId, X, Y, PointOrder) values (@p, @x, @y, @i)";
SQLiteDatabase db = new SQLiteDatabase(true);
try
{
db.ExecuteNonQuery(sqlProfile,
"@n", this.Name,
"@d", this.Description,
"@f", this.Favourite.ToString(),
"@m", timeNowDb,
"@p", this.ProfileId);
db.ExecuteNonQuery(sqlDeleteSpots,"@p", this.ProfileId);
int i = 0;
foreach (float[] pt in data)
{
db.ExecuteNonQuery(sqlSpot,
"@p", this.ProfileId,
"@x", pt[0],
"@y", pt[1],
"@i",i);
i++;
}
db.CommitTransaction();
}
catch (Exception ex)
{
try
{
db.RollbackTransaction();
}
catch (Exception ex1)
{
}
}
finally
{
}
}
public static void Remove(string userId, string friendId)
{
SQLiteDatabase db = new SQLiteDatabase();
// This query checks that the user has not already been invited
string sql = "delete from cycli_friends where (userId=@u1 and friendId=@f1) or (friendId=@u2 and userId=@f2)";
db.ExecuteNonQuery(sql, "@u1", userId, "@f1", friendId, "@u2", userId, "@f2", friendId);
db.Close();
}
public override void ExecuteNonQuery(string query)
{
Trace.TraceInformation($"SQLite ExecuteNonQuery '{query}'");
Db.ExecuteNonQuery(query);
}
public void WebRequestPostJson(string url, string postData)
{
var httpWebRequest = (HttpWebRequest)WebRequest.Create(url);
httpWebRequest.ContentType = "application/json";
httpWebRequest.Method = "POST";
using (var streamWriter = new StreamWriter(httpWebRequest.GetRequestStream()))
{
string sql = "";
string apcode = "AP-0000";
streamWriter.Write(postData);
streamWriter.Flush();
streamWriter.Close();
try
{
var httpResponse = (HttpWebResponse)httpWebRequest.GetResponse();
using (var streamReader = new StreamReader(httpResponse.GetResponseStream()))
{
var result = streamReader.ReadToEnd();
// MessageBox.Show(result.ToString());
//using (SQLiteDatabase db = new SQLiteDatabase(@"DATA\bcapinvoicesub.db"))
//{
if (result.ToString().StartsWith("["))
{
sql = result.ToString().Replace("[", "").Replace("]", "");
}
else
{
sql = result.ToString();
}
// string strcon=@"Server=MACAIR\SQLEXPRESS;Database=minismart;User Id=sa;Password=password;";
if (sql != string.Empty)
{
var cuscode = JsonSerializer.DeserializeFromString <BCAPINVOICESUB>(sql);
if (getMSSQLRows(string.Format("select docno from bcapinvoice where docno='{0}'", cuscode.DOCNO.Trim()), strcon).Rows.Count == 0)
{
DataTable dtap = getMSSQLRows(string.Format("select code from bcap where name1 like '{0}'", cuscode.APCODE), strcon);
if (dtap.Rows.Count > 0)
{
apcode = dtap.Rows[0][0].ToString();
}
else
{
DataTable dtt = getMSSQLRows("select max(code) from bcap", strcon);
if (dtt.Rows.Count > 0)
{
apcode = (Convert.ToInt16(dtt.Rows[0][0].ToString().Substring(dtt.Rows[0][0].ToString().IndexOf("-") + 1, 4)) + 1).ToString();
for (int i = 0; i < 5; i++)
{
apcode = "0" + apcode;
}
apcode = "AP-" + apcode.Substring(apcode.Length - 4, 4);
}
insertMSSQL(string.Format("insert into bcap(code,name1,address)values('{0}','{1}','{2}')", apcode, _apcode, "..."), strcon);
}
sql = string.Format("insert into bcapinvoice(docno,docdate,apcode,iscompletesave,taxtype,billtype,grirbillstatus)values('{0}','{1}','{2}',1,1,1,1)", cuscode.DOCNO.Trim(), ConvertToDate(cuscode.DOCDATE), apcode, cuscode.ID);
insertMSSQL(sql, strcon);
}
sql = string.Format("select itemcode,sum(qty) as qty from bcapinvoicesub where docno='{0}' and itemcode='{1}' group by docno,itemcode", cuscode.DOCNO, cuscode.ITEMCODE);
if (getMSSQLRows(sql, strcon).Rows.Count > 0)
{
int _qty = Convert.ToInt16(getMSSQLRows(sql, strcon).Rows[0]["qty"]) + 1;
sql = string.Format("update bcapinvoicesub set qty={0},cnqty={0},grremainqty={0},amount=price*({0}),netamount=amount/1.05,homeamount=amount/1.07 where docno='{1}' and itemcode='{2}'", _qty, cuscode.DOCNO, cuscode.ITEMCODE);
insertMSSQL(sql, strcon);
}
else
{
sql = string.Format("insert into bcapinvoicesub(docno,docdate,apcode,itemcode,itemname,qty,cnqty,grremainqty,stocktype,unitcode,shelfcode,whcode,price,amount,netamount,homeamount)values('{5}','{6}','{8}','{0}','{1}',{2},{2},{2},2,'001','001','001',{3},{4},{7},{7})", cuscode.ITEMCODE, cuscode.ITEMNAME.ToString().Replace("'", "นิ้ว"), cuscode.QTY, cuscode.SALEPRICE, (Convert.ToDouble(cuscode.QTY) * Convert.ToDouble(cuscode.SALEPRICE)).ToString(), cuscode.DOCNO, ConvertToDate(cuscode.DOCDATE), ((Convert.ToDouble(cuscode.QTY) * Convert.ToDouble(cuscode.SALEPRICE)) / 1.07).ToString(), apcode);
insertMSSQL(sql, strcon);
db.ExecuteNonQuery(string.Format("insert into bcapinvoicesub (docno,docdate,apcode,itemcode,price,qty,amount,cost)values('{0}','{1}','{2}','{3}',{4},{5},{6},{7})", cuscode.DOCNO, ConvertToDate(cuscode.DOCDATE), "AP-0001", cuscode.ITEMCODE, cuscode.SALEPRICE, cuscode.QTY, (Convert.ToDouble(cuscode.QTY) * Convert.ToDouble(cuscode.SALEPRICE)), cuscode.SALEPRICE));
}
sql = string.Format("update bcitem set lastbuyprice={0} where code='{1}'", cuscode.SALEPRICE, cuscode.ITEMCODE);
db.ExecuteNonQuery(sql);
if (getMSSQLRows(string.Format("select code from bcitem where code='{0}'", cuscode.ITEMCODE), strcon).Rows.Count == 0)
{
sql = string.Format("insert into bcitem(id,code,name1,defstkunitcode,vendorcode)values('{4}','{0}','{1}','{2}','{3}')", cuscode.ITEMCODE, cuscode.ITEMNAME, "001", cuscode.SUPPLIERCO);
insertMSSQL(sql, strcon);
}
sql = string.Format("update bcapinvoice set AfterDiscount={0},BeforeTaxAmount={0}/1.07,TaxAmount={0}-({0}/1.07),TotalAmount={0},BillBalance={0},NetDebtAmount={0},HomeAmount={0}/1.07,SumOfItemAmount={0}", string.Format("(select sum(amount) from bcapinvoicesub where docno='{0}')", cuscode.DOCNO));
insertMSSQL(sql, strcon);
sql = string.Format("insert into bcserialmaster(CtrlNo,serialno,ItemCode,ReceiveDate,RegisDate,ReceiveDocNo,ApCode,ItemStatus,RegisterNo,ShelfCode,WHCode,ActiveStatus)values('{0}','{0}','{1}','{2}','{2}','{3}','AP-0001',0,'{0}','001','001',1)", cuscode.SERIALNO, cuscode.ITEMCODE, ConvertToDate(cuscode.DOCDATE), cuscode.DOCNO);
insertMSSQL(sql, strcon);
sql = string.Format("insert into bcserialtrans(CtrlNo,serialno,ItemCode,DocDate,DocNo,ShelfCode,WHCode,savefrom)values('{0}','{0}','{1}','{2}','{3}','001','001',2)", cuscode.SERIALNO, cuscode.ITEMCODE, ConvertToDate(cuscode.DOCDATE), cuscode.DOCNO);
insertMSSQL(sql, strcon);
sql = string.Format("insert into bcserialprintform(StartCtrlNo,StopCtrlNo,serialtext,ItemCode,DocNo,savefrom)values('{0}','{0}','{0}','{1}','{2}',2)", cuscode.SERIALNO, cuscode.ITEMCODE, cuscode.DOCNO);
insertMSSQL(sql, strcon);
sql = string.Format("INSERT INTO ProcessStock (ItemCode,DocDate,ProcessFlag,FlowStatus,ProcessType,ProcessCase) VALUES('{0}','{1}',0,1,5,2)", cuscode.ITEMCODE, ConvertToDate(cuscode.DOCDATE));
insertMSSQL(sql, strcon);
// db.ExecuteNonQuery(string.Format("update bcapinvoicesub set sending='1' where cuscode='{0}' and docno='{1}'", cuscode.CUSCODE, cuscode.DOCNO));
}
else
{
MessageBox.Show("ไม่พบหมายเลขซีเรียลนี้!โปรดตรวจสอบ");
}
}
}
catch (WebException ex)
{
MessageBox.Show(ex.Message);
}
}
}
/// <summary>
/// Allows deleting of student/teacher accounts
/// </summary>
/// <param name="id">The ID of the account to delete</param>
/// <param name="stuteach">A string containing either "students" or "teachers"</param>
/// <returns>1 if the delete was successful, 0 otherwise</returns>
public static int deleteAccount(int id, string stuteach)
{
// Validates input
if (stuteach.ToLower() == "students" || stuteach.ToLower() == "teachers")
{
try
{
SQLiteDatabase db = new SQLiteDatabase();
db.ExecuteNonQuery(string.Format(
"DELETE FROM {0} WHERE id={1};", stuteach, id));
}
// Returns 0 if error, else returns 1
catch { return 0; }
return 1;
}
// This is if the input is invalid
return 0;
}
private static void TestCsharpSqlite()
{
SQLiteDatabase db;
SQLiteVdbe stmt;
SQLiteVdbe c1, c2;
bool found;
int i;
string databaseName = "Benchmark_cs-SQLite.sqlite";
if ( File.Exists( databaseName ) ) File.Delete( databaseName );
db = new SQLiteDatabase( databaseName );
for ( i = 0; i < PRAGMA_Commands.Length; i++ ) { db.ExecuteNonQuery( PRAGMA_Commands[i] ); }
db.ExecuteNonQuery( "BEGIN EXCLUSIVE" );
for ( i = 0; i < CREATE_Commands.Length; i++ ) { db.ExecuteNonQuery( CREATE_Commands[i] ); }
stmt = new SQLiteVdbe( db, INSERT_Command );
long start = DateTime.Now.Ticks;
long key = 1999;
for ( i = 0; i < nRecords; i++ )
{
key = ( 3141592621L * key + 2718281829L ) % 1000000007L;
stmt.Reset();
stmt.BindLong( 1, key );
stmt.BindText( 2, key.ToString() );
stmt.ExecuteStep();
}
stmt.Close();
db.ExecuteNonQuery( "END" );
timer[1, 0] = DateTime.Now.Ticks - start;
db.ExecuteNonQuery( "BEGIN EXCLUSIVE" );
start = DateTime.Now.Ticks;
c1 = new SQLiteVdbe( db, SELECT_Bind_i );
c2 = new SQLiteVdbe( db, SELECT_Bind_s );
key = 1999;
for ( i = 0; i < nRecords; i++ )
{
key = ( 3141592621L * key + 2718281829L ) % 1000000007L;
c1.Reset();
c1.BindLong( 1, key );
c1.ExecuteStep();
c2.Reset();
c2.BindText( 1, key.ToString() );
c2.ExecuteStep();
long id = (long)c1.Result_Long( 0 );
Debug.Assert( id == (long)c2.Result_Long( 0 ) );
}
c1.Close();
c2.Close();
db.ExecuteNonQuery( "END" );
timer[1, 1] = DateTime.Now.Ticks - start;
db.ExecuteNonQuery( "BEGIN EXCLUSIVE" );
start = DateTime.Now.Ticks;
key = Int64.MinValue;
i = 0;
c1 = new SQLiteVdbe( db, SELECT_Command_i );
while ( c1.ExecuteStep() != Sqlite3.SQLITE_DONE )
{
long intKey = (long)c1.Result_Long( 0 );
Debug.Assert( intKey >= key );
key = intKey;
i += 1;
}
c1.Close();
Debug.Assert( i == nRecords );
String strKey = "";
i = 0;
c2 = new SQLiteVdbe( db, SELECT_Command_s );
while ( c2.ExecuteStep() != Sqlite3.SQLITE_DONE )
{
string recStrKey = (string)c2.Result_Text( 1 );
Debug.Assert( recStrKey.CompareTo( strKey ) >= 0 );
strKey = recStrKey;
i += 1;
}
c2.Close();
Debug.Assert( i == nRecords );
timer[1, 2] = DateTime.Now.Ticks - start;
db.ExecuteNonQuery( "END" );
db.ExecuteNonQuery( "BEGIN EXCLUSIVE" );
start = DateTime.Now.Ticks;
key = 1999;
stmt = new SQLiteVdbe( db, DELETE_Bind );
for ( i = 0; i < nRecords; i++ )
{
key = ( 3141592621L * key + 2718281829L ) % 1000000007L;
stmt.Reset();
stmt.BindLong( 1, key );
stmt.ExecuteStep();
}
stmt.Close();
db.ExecuteNonQuery( "END" );
timer[1, 3] = DateTime.Now.Ticks - start;
db.CloseDatabase();
#if NET_35
Sqlite3.Shutdown();
#else
Sqlite3.sqlite3_shutdown();
#endif
}
public void Flush()
{
byte[] buffer = new byte[4096];
int bytesRead = 0;
int blockCount = 0;
string fileId = "";
string directoryID = db.ExecuteScalar("SELECT id FROM directories WHERE fullpath = @path",
new Dictionary <string, object>()
{
{ "@path", vsrc.ParentPath.Path }
});
DataTable fileInfo =
db.GetDataTable("SELECT * from entities where isdir = @isdir and fname = @name and dir = @dir", new Dictionary <string, object>()
{
{ "@name", vsrc.EntityName },
{ "@isdir", 0 },
{ "@dir", directoryID }
});
// If file does not exist - create file record
if (fileInfo.Rows.Count == 0)
{
string insertEntityQuery = "INSERT INTO entities (fname, dir, size, cdate, mdate, isdir, accessdate, filehash) VALUES (@fname, @dir, @size, @cdate, @mdate, @isdir, @access, @filehash)";
double ctime = DateTime.Now.UnixTimeStamp();
string fileHash = "";
using (FileStream fs = new FileStream(this.Path, FileMode.Open, FileAccess.Read))
{
fileHash = fs.GetSHA512();
}
db.ExecuteNonQuery(insertEntityQuery, new Dictionary <string, object>()
{
{ "@fname", vsrc.EntityName },
{ "@dir", directoryID },
{ "@size", new FileInfo(Path).Length },
{ "@cdate", ctime },
{ "@mdate", ctime },
{ "@access", ctime },
{ "@isdir", 0 },
{ "@filehash", fileHash }
});
fileInfo =
db.GetDataTable("SELECT * from entities where isdir = @isdir and fname = @name and dir = @dir", new Dictionary <string, object>()
{
{ "@name", vsrc.EntityName },
{ "@isdir", 0 },
{ "@dir", directoryID }
});
}
fileId = fileInfo.Rows[0]["id"].ToString();
using (FileStream f = System.IO.File.Open(this.Path, FileMode.Open, FileAccess.Read))
{
Int64 lenthOfFile = f.Length;
Int64 totalRead = 0;
using (ProgressBar pb = new ProgressBar(disableProgress))
{
while ((bytesRead = f.Read(buffer, 0, buffer.Length)) > 0)
{
pb.Report((((double)totalRead) / lenthOfFile));
totalRead = totalRead + bytesRead;
string hash1 = buffer.JenkinsOneAtATime();
string id = "";
string hash2 = "";
string hash2Check = "";
hash2 = buffer.GetSHA512(0, bytesRead);
hash2Check = db.ExecuteScalar("SELECT id FROM blocks WHERE hash2 = @hash2 and hash1 = @hash1",
new Dictionary <string, object>()
{
{ "@hash2", hash2 },
{ "@hash1", hash1 }
});
id = hash2Check;
if (id == "")
{
// need to create block
Guid g = Guid.NewGuid();
string name = g.ToString();
string encName = AESWrapper.EncryptToString(name, key);
encName = encName.Replace("/", "_");
encName = "/" + encName;
encName = encName.Replace("//", "__");
/*byte[] compressed;
* using (System.IO.MemoryStream instream = new MemoryStream(buffer))
* {
* using (System.IO.MemoryStream outstream = new MemoryStream())
* {
* using (GZipStream s = new GZipStream(outstream, CompressionMode.Compress))
* {
* instream.CopyTo(s);
* }
*
* compressed = outstream.ToArray();
* }
* }*/
foreach (IFileSystem fs in fsdst)
{
/*try
* {
* using (Stream s = fs.CreateFile(FileSystemPath.Parse("/test")))
* {
*
* }
* }
* catch (Exception e)
* {
* Console.WriteLine("[Error]: file system - " + fs.ToString() + " is unreachable.");
* continue;
* }*/
//fs.
using (var fstream = fs.OpenOrCreate(FileSystemPath.Parse($"/{DATAFILE}"),
FileAccess.ReadWrite))
{
//byte[] cipher = AESWrapper.EncryptToByte(buffer, key, 0, bytesRead);
DeDupeStorage storage = new DeDupeStorage(fstream, db);
byte[] cipher = AESWrapper.EncryptToByte(buffer, key, 0, bytesRead);
storage.AddFile(encName, cipher);
}
/*using (var fstream = fs.OpenOrCreate(FileSystemPath.Parse($"/{DATAFILE}"),
* FileAccess.ReadWrite))
* {
* //byte[] cipher = AESWrapper.EncryptToByte(buffer, key, 0, bytesRead);
* DeDupeStorage storage = new DeDupeStorage(fstream, db);
* using (var zippedStream = new MemoryStream())
* {
* using (var archive = new ZipArchive(zippedStream, ZipArchiveMode.Update))
* {
* var ent = archive.CreateEntry("data.bin", CompressionLevel.Optimal);
* using (var zipent = ent.Open())
* {
* zipent.Write(buffer, 0, bytesRead);
* }
* }
*
* byte[] zipData = zippedStream.ToArray();
* byte[] cipher = AESWrapper.EncryptToByte(zipData, key, 0, zipData.Length);
* storage.AddFile(encName, cipher);
*
* }
* }*/
/*using (var fstream = fs.OpenOrCreate(FileSystemPath.Parse($"/{DATAFILE}"), FileAccess.ReadWrite))
* {
* using (var zip = new ZipArchive(fstream, ZipArchiveMode.Update))
* {
* var ent = zip.CreateEntry(encName, CompressionLevel.Optimal);
* using (var entfs = ent.Open())
* {
* byte[] cipher = AESWrapper.EncryptToByte(buffer, key, 0, bytesRead);
* entfs.Write(cipher);
* }
* }
* }*/
/*using (Stream s = fs.CreateFile(FileSystemPath.Parse($"{encName}")))
* {
* byte[] cipher = AESWrapper.EncryptToByte(buffer, key, 0, bytesRead);
* s.Write(cipher);
* }*/
string blockInsertSQL =
"INSERT INTO blocks (hash1, size, name, location, hash2) VALUES (@hash1, @size, @name, @location, @hash2)";
db.ExecuteNonQuery(blockInsertSQL, new Dictionary <string, object>()
{
{ "@hash1", hash1 },
{ "@size", bytesRead },
{ "@name", encName },
{ "@location", fs.ToString() },
{ "@hash2", hash2 }
});
hash2Check = db.ExecuteScalar(
"SELECT id FROM blocks WHERE hash2 = @hash2 and hash1 = @hash1 and location = @location",
new Dictionary <string, object>()
{
{ "@hash2", hash2 },
{ "@hash1", hash1 },
{ "@location", fs.ToString() }
});
id = hash2Check;
string fileBlockInsertSQL =
"INSERT INTO fileblocks (file_id, block_id, block_order) VALUES (@fileId, @blockId, @blockOrder)";
db.ExecuteNonQuery(fileBlockInsertSQL, new Dictionary <string, object>()
{
{ "@fileId", fileId },
{ "@blockId", id },
{ "@blockOrder", blockCount }
});
}
}
else
{
string fileBlockInsertSQL =
"INSERT INTO fileblocks (file_id, block_id, block_order) VALUES (@fileId, @blockId, @blockOrder)";
db.ExecuteNonQuery(fileBlockInsertSQL, new Dictionary <string, object>()
{
{ "@fileId", fileId },
{ "@blockId", id },
{ "@blockOrder", blockCount }
});
}
blockCount++;
}
}
}
}
public void Save(string userId)
{
SQLiteDatabase db = new SQLiteDatabase();
// Query forces identity
string sql = @"update cycli_rider_turbos set Type=@t, " +
"Power_Model_C1=@c1, Power_Model_C2=@c2, Power_Model_C3=@c3 " +
"where UserId=@u";
int updated = db.ExecuteNonQuery(sql, "@t", this.Type,
"@c1", this.Coefficients.Length == 0 ? 0 : this.Coefficients[0],
"@c2", this.Coefficients.Length == 0 ? 0 : this.Coefficients[1],
"@c3", this.Coefficients.Length == 0 ? 0 : this.Coefficients[2],
"@u", userId);
if (updated == 0)
{
// It's a new entry
sql = @"insert into cycli_rider_turbos (UserId, Type, Power_Model_C1, Power_Model_C2, Power_Model_C3) " +
"values (@u, @t, @c1, @c2, @c3)";
db.ExecuteNonQuery(sql,
"@u", userId,
"@t", this.Type,
"@c1", this.Coefficients.Length == 0 ? 0 : this.Coefficients[0],
"@c2", this.Coefficients.Length == 0 ? 0 : this.Coefficients[1],
"@c3", this.Coefficients.Length == 0 ? 0 : this.Coefficients[2]);
}
db.Close();
}
protected void removeAddUpdateOffersToGrandExchangeDatabase()
{
while (true)
{
try
{
Thread.Sleep(100); //100 milliseconds.
}
catch (ThreadInterruptedException) { }
lock (removeOffersLocker)
{
foreach (GEItem geItem in offersToRemove)
{
removingOffers.Add(geItem);
}
offersToRemove.Clear();
}
lock (addOffersLocker)
{
foreach (GEItem geItem in offersToAdd)
{
addingOffers.Add(geItem);
}
offersToAdd.Clear();
}
lock (abortOffersLocker)
{
foreach (GEItem geItem in offersToAbort)
{
abortingOffers.Add(geItem);
}
offersToAbort.Clear();
}
try
{
//this check should go straight to Thread.Sleep.
if (removingOffers.Count == 0 && addingOffers.Count == 0 && abortingOffers.Count == 0)
{
continue;
}
//Run this on seperate thread so it won't slow down the process.
SQLiteDatabase db = new SQLiteDatabase(Constants.databaseName); //opens new database connection.
//Removes all the removingOffers from grandExchangeSelling table
//This delayed offer deleter should always be ran before inserter (below) or else it's possible bug may occur.
foreach (GEItem geItem in removingOffers)
{
if (geItem is BuyOffer)
{
db.ExecuteNonQuery("DELETE FROM grandExchangeBuying WHERE `playerHash` = " + geItem.getPlayerHash() + " AND `itemId` = " + geItem.getItem() + " AND `amount` = " + geItem.getTotalAmount() + " AND `price` = " + geItem.getPriceEach() + " AND `slot` = " + geItem.getSlot());
}
else if (geItem is SellOffer)
{
db.ExecuteNonQuery("DELETE FROM grandExchangeSelling WHERE `playerHash` = " + geItem.getPlayerHash() + " AND `itemId` = " + geItem.getItem() + " AND `amount` = " + geItem.getTotalAmount() + " AND `price` = " + geItem.getPriceEach() + " AND `slot` = " + geItem.getSlot());
}
}
//Add all new addingOffers to grandExchangeBuying or grandExchangeSelling table.
//This delayed offer inserter would cause a possible dupe, if both tables didn't have UNIQUE( slot, playerHash ), fixed now.
foreach (GEItem geItem in addingOffers)
{
if (geItem is BuyOffer)
{
db.ExecuteNonQuery("INSERT INTO grandExchangeBuying(itemId, amount, price, bought, slot, playerHash) VALUES (" + geItem.getItem() + ", " + geItem.getTotalAmount() + ", " + geItem.getPriceEach() + ", " + geItem.getAmountTraded() + ", " + geItem.getSlot() + ", " + geItem.getPlayerHash() + ")");
}
else if (geItem is SellOffer)
{
db.ExecuteNonQuery("INSERT INTO grandExchangeSelling(itemId, amount, price, sold, slot, playerHash) VALUES (" + geItem.getItem() + ", " + geItem.getTotalAmount() + ", " + geItem.getPriceEach() + ", " + geItem.getAmountTraded() + ", " + geItem.getSlot() + ", " + geItem.getPlayerHash() + ")");
}
}
//Aborts any Buying Offer or Selling Offer from grandExchangeBuying or grandExchangeSelling table.
foreach (GEItem geItem in abortingOffers)
{
if (geItem is BuyOffer)
{
db.ExecuteNonQuery("UPDATE grandExchangeBuying SET aborted = 1 WHERE slot = " + geItem.getSlot() + " AND playerHash = " + geItem.getPlayerHash());
}
else if (geItem is SellOffer)
{
db.ExecuteNonQuery("UPDATE grandExchangeSelling SET aborted = 1 WHERE slot = " + geItem.getSlot() + " AND playerHash = " + geItem.getPlayerHash());
}
}
db.CloseDatabase(); //closes the connection.
removingOffers.Clear();
addingOffers.Clear();
abortingOffers.Clear();
} catch (Exception e) {
misc.WriteError(e.Message);
}
}
}
public static void Delete(string userId, string riderId)
{
// Has this rider been employed in a race
string sqlPlayed = @"select count(*) from cycli_race_riders where userid=@u and (Status='Finished' or Status='Abandoned')";
string sqlRiderDelete = @"delete from cycli_virtual_riders where ownerid = @o and userid = @u";
string sqlRiderRaceDelete = @"delete from cycli_race_riders where userid = @u";
string sqlRiderInactive = @"update cycli_virtual_riders set status='Inactive' where ownerid = @o and userid = @u";
bool riderPlayed = false;
SQLiteDatabase db = new SQLiteDatabase();
try
{
riderPlayed = (int.Parse(db.ExecuteScalar(sqlPlayed,"@u",riderId)) > 0);
}
finally
{
db.Close();
}
// Now reopen for transactions
db = new SQLiteDatabase(true);
try
{
if (riderPlayed)
{
db.ExecuteNonQuery(sqlRiderInactive, "@o", userId, "@u", riderId);
}
else
{
// Get rid of rider from any pending races
db.ExecuteNonQuery(sqlRiderRaceDelete, "@u", riderId);
db.ExecuteNonQuery(sqlRiderDelete, "@o", userId, "@u", riderId);
}
// Commit closes db
db.CommitTransaction();
}
catch (Exception ex)
{
db.RollbackTransaction();
}
}
private SQLiteDatabase OpenDB(string fileName)
{
if(File.Exists(fileName))
File.Delete(fileName);
var db = new SQLiteDatabase(fileName);
for(int i = 0; i < PRAGMA_Commands.Length; i++)
{
db.ExecuteNonQuery(PRAGMA_Commands[i]);
}
return db;
}
// Returns invites, joins and finishes - in that order
public static void Cancel(string userId, string raceId)
{
// Load the race as we need to check status - can only delete planned
Race r = Load(raceId);
// Protection - we can only cancel if we're the race director. Good try Mr, Mrs or Ms Hacker
if (r != null && r.RaceDirectorId == userId && r.Status == @"Planned")
{
SQLiteDatabase db = new SQLiteDatabase(true);
// Can only delete planned
string sql = @"delete from cycli_race_riders where RaceId = @r";
db.ExecuteNonQuery(sql, "@r", r.RaceId);
sql = @"delete from cycli_races where RaceId = @r";
db.ExecuteNonQuery(sql, "@r", r.RaceId);
try
{
db.CommitTransaction();
} // No need to close Db as this is done by CommitTranaction
catch (Exception ex)
{
db.RollbackTransaction();
Console.WriteLine(ex);
}
}
}
public override void Write(byte[] buffer, int offset, int count)
{
base.Write(buffer, offset, count);
// Someday I will separate it out and make a PR to SharpFileSystem
//string directoryID =
// db.ExecuteScalar("SELECT fileid FROM gdrive WHERE filename = 'distrodedup' AND directory = 1");
string directoryID =
db.ExecuteScalar("SELECT fileid FROM gdrive WHERE filename = @fileName AND directory = @directory",
new Dictionary <string, object>()
{
{ "@fileName", "distrodedup" },
{ "@directory", 1 }
});
if (directoryID == "")
{
File fileData = new File();
fileData.Name = "DistroDeDup";
fileData.MimeType = "application/vnd.google-apps.folder";
var req = svc.Files.Create(fileData);
req.Fields = "id";
var folder = req.Execute();
//db.ExecuteNonQuery(
// $"INSERT INTO gdrive (filename, fileid, directory) VALUES ('distrodedup', '{folder.Id}', 1)");
db.ExecuteNonQuery(
"INSERT INTO gdrive (filename, fileid, directory) VALUES (@fileName, @fileID, @directory)",
new Dictionary <string, object>()
{
{ "@fileName", "distrodedup" },
{ "@fileID", folder.Id },
{ "@directory", 1 }
});
directoryID = folder.Id;
}
//string fileId =
// db.ExecuteScalar($"SELECT fileid FROM gdrive WHERE filename = '{fileName}' AND directory = 0");
string fileId =
db.ExecuteScalar("SELECT fileid FROM gdrive WHERE filename = @fileName AND directory = @directory",
new Dictionary <string, object>()
{
{ "@fileName", fileName },
{ "@directory", 0 }
});
if (fileId != "")
{
FilesResource.ListRequest reqExists = svc.Files.List();
reqExists.Q = String.Format("name = '{0}' and trashed=false and parents in '{1}'", fileName, directoryID);
reqExists.Fields = "nextPageToken, files(id, name,parents,mimeType, trashed)";
var result = reqExists.Execute();
bool foundFile = false;
foreach (var file in result.Files)
{
if (file.Id == fileId)
{
// I guess we need to create another file object?
/*File uploadFile = new File();
* uploadFile.Name = fileName;
* uploadFile.MimeType = mimeType;
* var res = svc.Files.Update(uploadFile, fileId, this, mimeType);
* var req = res.Upload();*/
GDriveFile f = new GDriveFile(this, fileName, mimeType, directoryID, db, GDriveFile.Operation.NONE);
f.Update(svc, fileId);
foundFile = true;
break;
}
}
if (!foundFile)
{
GDriveFile f = new GDriveFile(this, fileName, mimeType, directoryID, db, GDriveFile.Operation.UPDATE);
f.Upload(svc);
/*File body = new File();
* body.Name = fileName;
* body.MimeType = mimeType;
* body.Parents = new List<string>();
* body.Parents.Add(directoryID);
* FilesResource.CreateMediaUpload req = svc.Files.Create(body, this, mimeType);
* req.Fields = "id, parents";
* var res = req.Upload();
* string newFileId = req.ResponseBody.Id;
*
* //db.ExecuteNonQuery(
* // $"UPDATE gdrive set fileid = '{newFileId}' WHERE filename = '{fileName}' and directory = 0");
*
* db.ExecuteNonQuery(
* "UPDATE gdrive set fileid = @newFileId WHERE filename = @fileName and directory = 0",
* new Dictionary<string, object>()
* {
* {"@newFileId", newFileId},
* {"@fileName", fileName}
* });*/
}
}
else
{
GDriveFile f = new GDriveFile(this, fileName, mimeType, directoryID, db, GDriveFile.Operation.CREATE);
f.Upload(svc);
/*File body = new File();
* body.Name = fileName;
* body.MimeType = mimeType;
* body.Parents = new List<string>();
* body.Parents.Add(directoryID);
* FilesResource.CreateMediaUpload req = svc.Files.Create(body, this, mimeType);
* req.Fields = "id, parents";
* req.ProgressChanged += ReqOnProgressChanged;
* //req.UploadAsync()
* var res = req.Upload();
*
* string newFileId = req.ResponseBody.Id;
*
* //db.ExecuteNonQuery(
* // $"INSERT INTO gdrive (filename, fileid, directory) VALUES ('{fileName}', '{newFileId}', 0)");
* db.ExecuteNonQuery(
* "INSERT INTO gdrive (filename, fileid, directory) VALUES (@fileName, @newFileId, 0)",
* new Dictionary<string, object>()
* {
* {"@fileName", fileName},
* {"@newFileID", newFileId}
* });*/
}
}
public static void JoinVirtualRider(string userId, string riderId, string raceId)
{
// What is the race status
// Validation occurs first on the client - this is the backstop
UserRider owner = UserRider.Load(userId);
if (owner != null)
{
// Query checks that race is in the right mode, the invitee is not already invited and that the invite limit has not been exceeded
SQLiteDatabase db = new SQLiteDatabase();
// This query checks that the user has not already been invited
string sql = "insert into cycli_race_riders (UserId, RaceId, Status, RiderType) " +
"select @f, @r, 'Joined', 'VIRTUAL' where exists (select 1 from cycli_races where " +
"RaceId=@r1 and Status='Planned' and RaceDirectorId=@u) " +
"and not exists (select 1 from cycli_race_riders where RaceId=@r1 and UserId=@f1) " +
"and (select count(*) from cycli_race_riders where raceId=@r2) < @c ";
db.ExecuteNonQuery(sql, "@f", riderId, "@r", raceId, "@r1", raceId, "@u", userId, "@f1", riderId, "@r2", raceId, "@c", owner.MaximumRiders);
db.Close();
Race race = Race.Load(raceId);
if (race != null)
{
CycliManager.Instance.SendRiderJoin(race, riderId);
}
}
}
public static void ProcessLibretroDb(string libretroDir, string outputDir, bool generateSql, bool generateYaml)
{
FileSystem.CreateDirectory(outputDir);
Func <DatGame, string> libretroDbDefaultGroupSelectors = (g) => g.RomCrc;
var libretroDbGroupSelectors = new Dictionary <string, Func <DatGame, string> >(StringComparer.OrdinalIgnoreCase)
{
{ "Sony - PlayStation", (g) => g.Serial },
{ "Sony - PlayStation 3", (g) => g.Serial },
{ "Nintendo - GameCube", (g) => g.Serial },
{ "Nintendo - Wii", (g) => g.Serial },
{ "Sega - Dreamcast", (g) => g.Serial },
{ "Sega - Mega-CD - Sega CD", (g) => g.Serial },
{ "Sega - Saturn", (g) => g.Serial },
{ "Sony - PlayStation 2", (g) => g.Serial },
{ "Sony - PlayStation Portable", (g) => g.Serial },
{ "Sony - PlayStation Vita", (g) => g.Serial },
{ "Nintendo - Wii U", (g) => g.Serial }
};
var datDirs = new List <string>
{
Path.Combine(libretroDir, @"dat"),
Path.Combine(libretroDir, @"metadat\no-intro"),
Path.Combine(libretroDir, @"metadat\redump"),
Path.Combine(libretroDir, @"metadat\libretro-dats"),
Path.Combine(libretroDir, @"metadat\fbneo-split"),
Path.Combine(libretroDir, @"metadat\mame"),
Path.Combine(libretroDir, @"metadat\mame-member"),
Path.Combine(libretroDir, @"metadat\mame-split"),
Path.Combine(libretroDir, @"metadat\mame-nonmerged"),
Path.Combine(libretroDir, @"metadat\homebrew"),
Path.Combine(libretroDir, @"metadat\hacks"),
Path.Combine(libretroDir, @"metadat\headered"),
Path.Combine(libretroDir, @"metadat\serial"),
Path.Combine(libretroDir, @"metadat\releaseyear"),
Path.Combine(libretroDir, @"metadat\origin")
};
var ignoreList = new List <string>
{
"MAME 2000 XML.dat",
"MAME BIOS.dat",
"FBNeo_romcenter.dat",
"System.dat",
"MAME 2000 BIOS.dat",
"Cannonball.dat",
"Cave Story.dat",
"Dinothawr.dat",
"DOOM.dat",
"Flashback.dat",
"ChaiLove.dat",
"LeapFrog - LeapPad.dat",
"Lutro.dat",
"Microsoft - Xbox One.dat",
"Mobile - J2ME.dat",
"Mobile - Palm OS.dat",
"Mobile - Symbian.dat",
"Mobile - Zeebo.dat",
"MrBoom.dat",
"Quake.dat",
"Quake II.dat",
"Quake III.dat",
"Rick Dangerous.dat",
"RPG Maker.dat",
"Sony - PlayStation 4.dat",
"Sony - PlayStation Portable (UMD Music).dat",
"Sony - PlayStation Portable (UMD Video).dat",
"Tiger - Game.com.dat",
"Tiger - Gizmondo.dat",
"Tomb Raider.dat",
"Wolfenstein 3D.dat",
"Microsoft - XBOX 360 (Title Updates).dat",
"HBMAME.dat",
"MAME 2000.dat",
"MAME 2003.dat",
"MAME 2003-Plus.dat",
"MAME 2010.dat",
"MAME 2015.dat",
"MAME 2016.dat",
"MAME.dat",
"ScummVM.dat",
"Atomiswave.dat",
"Sony - PlayStation Minis.dat",
"Thomson - MOTO.dat"
};
var datFiles = new List <FileInfo>();
datDirs.ForEach(d => datFiles.AddRange(
Directory.GetFiles(d, "*.dat", SearchOption.AllDirectories).Where(f => !ignoreList.Contains(Path.GetFileName(f), StringComparer.OrdinalIgnoreCase)).
Select(f => new FileInfo(f))));
var databases = datFiles.GroupBy(a => a.Name, StringComparer.OrdinalIgnoreCase);
Parallel.ForEach(
databases,
(db, _, __) =>
{
var dbName = Path.GetFileNameWithoutExtension(db.Key);
var selector = libretroDbDefaultGroupSelectors;
if (libretroDbGroupSelectors.TryGetValue(dbName, out var sel))
{
selector = sel;
}
var cons = DatParser.ConsolidateDatFiles(db.Select(a => a.FullName), selector);
if (dbName == "Sony - PlayStation 3")
{
string ps3ResgionCharToRegion(char regionKey)
{
switch (regionKey)
{
case 'A': return("Asia");
case 'C': return("China");
case 'E': return("Europe");
case 'H': return("Hong Kong");
case 'J': return("Japan");
case 'K': return("Korea");
case 'P': return("Japan");
case 'U': return("USA");
default: return(null);
}
}
foreach (var game in cons)
{
if (game.Region.IsNullOrEmpty() && !game.Serial.IsNullOrEmpty())
{
game.Region = ps3ResgionCharToRegion(game.Serial[2]);
}
}
}
if (generateYaml)
{
var yamlPath = Path.Combine(outputDir, $"{dbName}.yaml");
FileSystem.DeleteFile(yamlPath);
File.WriteAllText(
yamlPath,
Serialization.ToYaml(cons),
Encoding.UTF8);
}
if (generateSql)
{
var dbPath = Path.Combine(outputDir, $"{dbName}.db");
FileSystem.DeleteFile(dbPath);
using (var sqlDb = new SQLiteDatabase(dbPath))
{
sqlDb.ExecuteNonQuery("PRAGMA journal_mode = OFF;");
sqlDb.Save(cons);
}
}
});
}
public static Dictionary<string, int> SetHandicaps(string raceId)
{
Dictionary<string, int> handicaps = new Dictionary<string, int>();
Race race = Load(raceId);
foreach (Participant p in race.Participants)
{
handicaps.Add(p.UserId, 100);
}
if (race != null)
{
SQLiteDatabase db = new SQLiteDatabase();
long yearAgo = DbTime.ToDbSecs(DateTime.UtcNow);
string sql = "select rr1.UserId, " +
"r.StartDateTime, " +
"(1000 * cast(rr1.Energy as real)/ cast(rr1.Time as real)) as Power " +
"From cycli_race_riders rr1, cycli_race_riders rr2, cycli_races r " +
"where r.StartDateTime > @t " +
"and r.RaceId = rr2.RaceId " +
"and rr1.Time > 0 " +
"and rr1.Energy > 0 " +
"and (rr1.Status='Finished' or rr1.Status='Abandoned') " +
"and rr1.UserId = rr2.UserId " +
"and r.RaceId = @r " +
"order by rr1.Time";
DataTable dt = db.GetDataTable(sql, "@t", yearAgo, "@r", raceId);
db.Close();
// Weight the average power by (a) time in the past
// (b) closeness to the selected distance
Dictionary<string, double> userPowers = dt.AsEnumerable()
.GroupBy(p => p.Field<string>("UserId"))
.ToDictionary(p => p.Key,
p => p.Sum(q => (double)(yearAgo - q.Field<long>("StartDateTime")) * q.Field<double>("Power"))/
p.Sum(q => yearAgo - q.Field<long>("StartDateTime")));
// Total AveragePower across all users
double meanUserPower = userPowers.Values.Average(p => p);
if (meanUserPower > 0)
{
foreach (KeyValuePair<string, double> k in userPowers)
{
int handicap = (int)Math.Round(100 * meanUserPower / k.Value);
handicaps[k.Key] = handicap;
}
}
string updateSql = "update cycli_race_riders set handicap=@h where raceid=@r and userid=@u";
db = new SQLiteDatabase(true);
try
{
foreach (KeyValuePair<string, int> h in handicaps)
{
db.ExecuteNonQuery(updateSql, "@h", h.Value, "@r", raceId, "@u", h.Key);
}
try
{
db.CommitTransaction();
}
catch (Exception ex)
{
db.RollbackTransaction();
}
}
finally
{
}
}
return handicaps;
}
private static void TestcsSQLite()
{
SQLiteDatabase db;
SQLiteVdbe stmt;
SQLiteVdbe c1, c2;
bool found;
int i;
string databaseName = "Benchmark_cs-SQLite.sqlite";
Console.WriteLine("\n\r" + databaseName);
if (File.Exists(databaseName))
{
File.Delete(databaseName);
}
db = new SQLiteDatabase(databaseName);
for (i = 0; i < PRAGMA_Commands.Length; i++)
{
db.ExecuteNonQuery(PRAGMA_Commands[i]);
}
db.ExecuteNonQuery("BEGIN EXCLUSIVE");
for (i = 0; i < CREATE_Commands.Length; i++)
{
db.ExecuteNonQuery(CREATE_Commands[i]);
}
stmt = new SQLiteVdbe(db, INSERT_Command);
long start = DateTime.Now.Ticks;
long key = 1999;
for (i = 0; i < nRecords; i++)
{
key = (3141592621L * key + 2718281829L) % 1000000007L;
stmt.Reset();
stmt.BindLong(1, key);
stmt.BindText(2, key.ToString());
stmt.ExecuteStep();
}
stmt.Close();
db.ExecuteNonQuery("END");
Console.WriteLine("inserting " + nRecords + " records: "
+ ((DateTime.Now.Ticks - start) * 10e-8 + .05).ToString("#.0") + " seconds");
db.ExecuteNonQuery("BEGIN EXCLUSIVE");
start = DateTime.Now.Ticks;
c1 = new SQLiteVdbe(db, SELECT_Bind_i);
c2 = new SQLiteVdbe(db, SELECT_Bind_s);
key = 1999;
for (i = 0; i < nRecords; i++)
{
key = (3141592621L * key + 2718281829L) % 1000000007L;
c1.Reset();
c1.BindLong(1, key);
c1.ExecuteStep();
c2.Reset();
c2.BindText(1, key.ToString());
c2.ExecuteStep();
long id = (long)c1.Result_Long(0);
Debug.Assert(id == ( long)c2.Result_Long(0));
}
c1.Close();
c2.Close();
db.ExecuteNonQuery("END");
Console.WriteLine("performing " + nRecords * 2 + " index searches: "
+ ((DateTime.Now.Ticks - start) * 10e-8 + .05).ToString("#.0") + " seconds");
db.ExecuteNonQuery("BEGIN EXCLUSIVE");
start = DateTime.Now.Ticks;
key = Int64.MinValue;
i = 0;
c1 = new SQLiteVdbe(db, SELECT_Command_i);
while (c1.ExecuteStep() != csSQLite.SQLITE_DONE)
{
long intKey = (long)c1.Result_Long(0);
Debug.Assert(intKey >= key);
key = intKey;
i += 1;
}
c1.Close();
Debug.Assert(i == nRecords);
String strKey = "";
i = 0;
c2 = new SQLiteVdbe(db, SELECT_Command_s);
while (c2.ExecuteStep() != csSQLite.SQLITE_DONE)
{
string recStrKey = (string)c2.Result_Text(1);
Debug.Assert(recStrKey.CompareTo(strKey) >= 0);
strKey = recStrKey;
i += 1;
}
c2.Close();
Debug.Assert(i == nRecords);
Console.WriteLine("iterating through " + (nRecords * 2) + " records: "
+ ((DateTime.Now.Ticks - start) * 10e-8 + .05).ToString("#.0") + " seconds");
db.ExecuteNonQuery("END");
db.ExecuteNonQuery("BEGIN EXCLUSIVE");
start = DateTime.Now.Ticks;
key = 1999;
stmt = new SQLiteVdbe(db, DELETE_Bind);
for (i = 0; i < nRecords; i++)
{
key = (3141592621L * key + 2718281829L) % 1000000007L;
stmt.Reset();
stmt.BindLong(1, key);
stmt.ExecuteStep();
}
stmt.Close();
db.ExecuteNonQuery("END");
Console.WriteLine("deleting " + nRecords + " records: "
+ ((DateTime.Now.Ticks - start) * 10e-8 + .05).ToString("#.0") + " seconds");
db.CloseDatabase();
}
public void Finish(RaceSpot[] rss, Dictionary<string, Stack<RaceSpot>> history)
{
string sqlRiderUpdate = @"update cycli_race_riders set Position=@p, Distance=@d, Time=@t, Energy=@e, PedalStrokes=@k, Heartbeats=@b, TSS=@i " +
"where RaceId=@r and UserId=@u";
string sqlRaceUpdate = @"update cycli_races set Status='Finished' " +
"where RaceId=@r and Status='Started'";
SQLiteDatabase db = new SQLiteDatabase(true);
try
{
db.ExecuteNonQuery(sqlRaceUpdate, "@r", this.RaceId);
foreach (RaceSpot rs in rss)
{
db.ExecuteNonQuery(sqlRiderUpdate, "@p", rs.pos, "@d", rs.d, "@t", rs.t, "@e", (int)rs.e, "@k", (int)rs.k, "@b", (int)rs.b, "@i", (int)rs.i, "@r", this.RaceId, "@u", rs.id);
}
db.CommitTransaction();
}
catch (Exception ex)
{
try
{
db.RollbackTransaction();
}
catch (Exception ex1)
{
}
}
finally
{
// Don't need to close on a transaction
}
// Do points as a separate transaction
string raceSpotSql = @"insert into cycli_race_spots (UserId, RaceId, Time, Distance, Speed, Cadence, HR, Power) " +
"values (@u, @r, @t, @d, @s, @c, @h, @p)";
db = new SQLiteDatabase(true);
try
{
foreach (string userId in history.Keys)
{
Stack<RaceSpot> userHistory = history[userId];
foreach (RaceSpot rs in userHistory)
{
db.ExecuteNonQuery(raceSpotSql, "@r", this.RaceId, "@u", userId, "@t", rs.t, "@d", rs.d, "@s", rs.s, "@c", rs.c, "@h", rs.h, "@p", rs.p);
}
}
db.CommitTransaction();
}
catch (Exception ex)
{
try
{
db.RollbackTransaction();
}
catch (Exception ex1)
{
}
}
finally
{
}
// Mark the race as Finished
this.Status = @"Finished";
}
private static void TestCsharpSqlite()
{
SQLiteDatabase db;
SQLiteVdbe stmt;
SQLiteVdbe c1, c2;
bool found;
int i;
string databaseName = "Benchmark_cs-SQLite.sqlite";
if (File.Exists(databaseName))
{
File.Delete(databaseName);
}
db = new SQLiteDatabase(databaseName);
for (i = 0; i < PRAGMA_Commands.Length; i++)
{
db.ExecuteNonQuery(PRAGMA_Commands[i]);
}
db.ExecuteNonQuery("BEGIN EXCLUSIVE");
for (i = 0; i < CREATE_Commands.Length; i++)
{
db.ExecuteNonQuery(CREATE_Commands[i]);
}
stmt = new SQLiteVdbe(db, INSERT_Command);
long start = DateTime.Now.Ticks;
long key = 1999;
for (i = 0; i < nRecords; i++)
{
key = (3141592621L * key + 2718281829L) % 1000000007L;
stmt.Reset();
stmt.BindLong(1, key);
stmt.BindText(2, key.ToString());
stmt.ExecuteStep();
}
stmt.Close();
db.ExecuteNonQuery("END");
timer[1, 0] = DateTime.Now.Ticks - start;
db.ExecuteNonQuery("BEGIN EXCLUSIVE");
start = DateTime.Now.Ticks;
c1 = new SQLiteVdbe(db, SELECT_Bind_i);
c2 = new SQLiteVdbe(db, SELECT_Bind_s);
key = 1999;
for (i = 0; i < nRecords; i++)
{
key = (3141592621L * key + 2718281829L) % 1000000007L;
c1.Reset();
c1.BindLong(1, key);
c1.ExecuteStep();
c2.Reset();
c2.BindText(1, key.ToString());
c2.ExecuteStep();
long id = (long)c1.Result_Long(0);
Debug.Assert(id == (long)c2.Result_Long(0));
}
c1.Close();
c2.Close();
db.ExecuteNonQuery("END");
timer[1, 1] = DateTime.Now.Ticks - start;
db.ExecuteNonQuery("BEGIN EXCLUSIVE");
start = DateTime.Now.Ticks;
key = Int64.MinValue;
i = 0;
c1 = new SQLiteVdbe(db, SELECT_Command_i);
while (c1.ExecuteStep() != Sqlite3.SQLITE_DONE)
{
long intKey = (long)c1.Result_Long(0);
Debug.Assert(intKey >= key);
key = intKey;
i += 1;
}
c1.Close();
Debug.Assert(i == nRecords);
String strKey = "";
i = 0;
c2 = new SQLiteVdbe(db, SELECT_Command_s);
while (c2.ExecuteStep() != Sqlite3.SQLITE_DONE)
{
string recStrKey = (string)c2.Result_Text(1);
Debug.Assert(recStrKey.CompareTo(strKey) >= 0);
strKey = recStrKey;
i += 1;
}
c2.Close();
Debug.Assert(i == nRecords);
timer[1, 2] = DateTime.Now.Ticks - start;
db.ExecuteNonQuery("END");
db.ExecuteNonQuery("BEGIN EXCLUSIVE");
start = DateTime.Now.Ticks;
key = 1999;
stmt = new SQLiteVdbe(db, DELETE_Bind);
for (i = 0; i < nRecords; i++)
{
key = (3141592621L * key + 2718281829L) % 1000000007L;
stmt.Reset();
stmt.BindLong(1, key);
stmt.ExecuteStep();
}
stmt.Close();
db.ExecuteNonQuery("END");
timer[1, 3] = DateTime.Now.Ticks - start;
db.CloseDatabase();
#if NET_35
Sqlite3.Shutdown();
#else
Sqlite3.sqlite3_shutdown();
#endif
}
public void Save(string userId)
{
// Check the time - only allow a minimum of 2 minutes
long timeNow = Utilities.DbTime.ToDbSecs(DateTime.UtcNow);
if (this.StartDateTime < timeNow - (timeNow % 60) + 120)
{
this.StartDateTime = timeNow - (timeNow % 60) + 120;
}
SQLiteDatabase db = new SQLiteDatabase(true);
// Query will only allow the race director to save
string sqlRace = @"update cycli_races set Name=@n, Description=@de, TargetType=@tt, Configuration=@c, StartDateTime=@d, PNS=@p, " +
"Drafting=@dr, HandicapType=@h, " +
"ProfileId=@pi, ProfileType=@pt, Minimum=@mn, Maximum=@mx " +
"where RaceId=@r and Status='Planned' and RaceDirectorId=@u";
db.ExecuteNonQuery(sqlRace, "@n", this.Name, "@de", this.Description,
"@tt", this.TargetType,
"@c", this.Configuration,
"@d", this.StartDateTime,
"@p", this.PowerNormalisedSpeed.ToString(),
"@dr", this.Drafting.ToString(),
"@h", this.HandicapType.ToString(),
"@pi", string.IsNullOrEmpty(this.ProfileId) ? "" : this.ProfileId,
"@pt", string.IsNullOrEmpty(this.ProfileType) ? "" : this.ProfileType,
"@mn", this.ProfileMin,
"@mx", this.ProfileMax,
"@r", this.RaceId,
"@u", userId);
// Delete any old riders
string sqlDelete = "delete from cycli_race_riders where raceId = @r";
db.ExecuteNonQuery(sqlDelete, "@r", this.RaceId);
// Add in the new riders
string sqlParticipant = "insert into cycli_race_riders (UserId, RaceId, Status, RiderType, Handicap) " +
"values (@u, @r, @s, @t, @h)";
foreach (Participant p in this.Participants)
{
db.ExecuteNonQuery(sqlParticipant, "@u", p.UserId, "@r", this.RaceId, "@s", p.Status, "@t",
(p.RiderType == Rider.RiderType.User ? "USER" : "VIRTUAL"), "@h", p.Handicap);
}
try
{
db.CommitTransaction();
} // No need to close Db as this is done by CommitTranaction
catch (Exception ex)
{
db.RollbackTransaction();
Console.WriteLine(ex);
}
}
public override void SaveDetails(string userId)
{
SQLiteDatabase db = new SQLiteDatabase();
// Query forces identity
string sql = @"update cycli_virtual_riders set Username=@n, PowerMinimum=@p4, Power1Hr=@p3, " +
"Power5Min=@p2, Power1Min=@p1, Power5Sec=@p0, Aggression=@a " +
"where OwnerId=@u and UserId=@r ";
db.ExecuteNonQuery(sql, "@n", this.UserName, "@p4", this.PowerMin, "@p3", this.Power1Hr,
"@p2", this.Power5Min, "@p1", this.Power1Min, "@p0", this.Power5Sec,"@a", this.Aggression,
"@u", userId, "@r", this.UserId);
db.Close();
}
public void Start()
{
// If real person has joined - finish the race straight away
if (Participants.Where(p => p.Status == @"Scheduled" && p.RiderType == Rider.RiderType.User).Count() == 0)
{
this.Status = @"Finished";
}
else
{
this.Status = @"Started";
}
System.Diagnostics.Debug.WriteLine("BANG! " + this.Name + " started at " + DateTime.UtcNow.ToString());
SQLiteDatabase db = new SQLiteDatabase(true);
try
{
string sqlUpdate = @"update cycli_races set Status='" + this.Status + "' " +
"where RaceId=@r";
db.ExecuteNonQuery(sqlUpdate, "@r", this.RaceId);
// Next query won't set any unscheduled to started
sqlUpdate = @"update cycli_race_riders set Status='Started' " +
"where RaceId=@r and Status='Scheduled'";
db.ExecuteNonQuery(sqlUpdate, "@r", this.RaceId);
// Tell the ride manager
db.CommitTransaction();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
db.RollbackTransaction();
}
foreach (Participant p in Participants)
{
if (p.Status == @"Scheduled")
{
p.Status = @"Started";
}
}
}
public static Profile New(string userId)
{
Profile p = new Profile();
long timeNowDb = Utilities.DbTime.ToDbSecs(DateTime.UtcNow);
p.ProfileId = Guid.NewGuid().ToString();
p.UserId = userId;
p.Name = "New Profile";
p.Description = "";
p.Favourite = false;
// It's a new profile
string sqlProfile = @"insert into cycli_profiles (ProfileId, UserId, Name, Description, Favourite, CreatedOn, ModifiedOn) " +
"values (@p, @u, @n, @d, @f, @c, @m)";
string sqlSpot = @"insert into cycli_profile_spots (ProfileId, X, Y) values (@p, @x, @y)";
SQLiteDatabase db = new SQLiteDatabase(true);
try
{
db.ExecuteNonQuery(sqlProfile,
"@p", p.ProfileId,
"@u", p.UserId,
"@n", p.Name,
"@d", p.Description,
"@f", p.Favourite.ToString(),
"@c", timeNowDb,
"@m", timeNowDb);
db.ExecuteNonQuery(sqlSpot,
"@p", p.ProfileId,
"@x", 0,
"@y", 50);
db.ExecuteNonQuery(sqlSpot,
"@p", p.ProfileId,
"@x", 100,
"@y", 50);
db.CommitTransaction();
}
catch (Exception ex)
{
try
{
db.RollbackTransaction();
}
catch(Exception ex1)
{
}
}
finally
{
}
return p;
}
private static void TestcsSQLite()
{
SQLiteDatabase db;
SQLiteVdbe stmt;
SQLiteVdbe c1, c2;
bool found;
int i;
string databaseName = "Benchmark_cs-SQLite.sqlite";
Console.WriteLine( "\n\r" + databaseName );
if ( File.Exists( databaseName ) ) File.Delete( databaseName );
db = new SQLiteDatabase( databaseName );
for (i = 0; i < PRAGMA_Commands.Length; i++) {db.ExecuteNonQuery( PRAGMA_Commands[i]);}
db.ExecuteNonQuery( "BEGIN EXCLUSIVE" );
for (i = 0; i < CREATE_Commands.Length; i++) {db.ExecuteNonQuery( CREATE_Commands[i]);}
stmt = new SQLiteVdbe( db, INSERT_Command );
long start = DateTime.Now.Ticks;
long key = 1999;
for ( i = 0 ; i < nRecords ; i++ )
{
key = ( 3141592621L * key + 2718281829L ) % 1000000007L;
stmt.Reset();
stmt.BindLong( 1, key );
stmt.BindText( 2, key.ToString() );
stmt.ExecuteStep();
}
stmt.Close();
db.ExecuteNonQuery( "END" );
Console.WriteLine( "inserting " + nRecords + " records: "
+ ( ( DateTime.Now.Ticks - start ) * 10e-8 + .05).ToString( "#.0" ) + " seconds" );
db.ExecuteNonQuery( "BEGIN EXCLUSIVE" );
start = DateTime.Now.Ticks;
c1 = new SQLiteVdbe( db, SELECT_Bind_i );
c2 = new SQLiteVdbe( db, SELECT_Bind_s);
key = 1999;
for ( i = 0 ; i < nRecords ; i++ )
{
key = ( 3141592621L * key + 2718281829L ) % 1000000007L;
c1.Reset();
c1.BindLong( 1, key );
c1.ExecuteStep();
c2.Reset();
c2.BindText( 1, key.ToString() );
c2.ExecuteStep();
long id = (long)c1.Result_Long( 0 );
Debug.Assert( id == ( long)c2.Result_Long(0) );
}
c1.Close();
c2.Close();
db.ExecuteNonQuery( "END" );
Console.WriteLine( "performing " + nRecords * 2 + " index searches: "
+ ( ( DateTime.Now.Ticks - start ) * 10e-8 + .05).ToString( "#.0" ) + " seconds" );
db.ExecuteNonQuery( "BEGIN EXCLUSIVE" );
start = DateTime.Now.Ticks;
key = Int64.MinValue;
i = 0;
c1 = new SQLiteVdbe( db, SELECT_Command_i );
while ( c1.ExecuteStep() != csSQLite.SQLITE_DONE )
{
long intKey = (long)c1.Result_Long( 0 );
Debug.Assert( intKey>= key );
key = intKey;
i += 1;
}
c1.Close();
Debug.Assert( i == nRecords );
String strKey = "";
i = 0;
c2 = new SQLiteVdbe( db, SELECT_Command_s );
while ( c2.ExecuteStep() != csSQLite.SQLITE_DONE )
{
string recStrKey = (string)c2.Result_Text( 1 );
Debug.Assert( recStrKey.CompareTo( strKey ) >= 0 );
strKey = recStrKey;
i += 1;
}
c2.Close();
Debug.Assert( i == nRecords );
Console.WriteLine( "iterating through " + ( nRecords * 2 ) + " records: "
+ ( ( DateTime.Now.Ticks - start ) * 10e-8 + .05).ToString( "#.0" ) + " seconds" );
db.ExecuteNonQuery( "END" );
db.ExecuteNonQuery( "BEGIN EXCLUSIVE" );
start = DateTime.Now.Ticks;
key = 1999;
stmt = new SQLiteVdbe( db, DELETE_Bind);
for ( i = 0 ; i < nRecords ; i++ )
{
key = ( 3141592621L * key + 2718281829L ) % 1000000007L;
stmt.Reset();
stmt.BindLong( 1, key );
stmt.ExecuteStep();
}
stmt.Close();
db.ExecuteNonQuery( "END" );
Console.WriteLine( "deleting " + nRecords + " records: "
+ ( ( DateTime.Now.Ticks - start ) * 10e-8 + .05).ToString( "#.0" ) + " seconds" );
db.CloseDatabase();
}
private void FormMain_KeyPress(object sender, KeyPressEventArgs e)
{
// vars
int timeStampId;
string firstName;
// check for user/scanner pressing enter
if (e.KeyChar.Equals('\r') || e.KeyChar.Equals('\n'))
{
// remove leading and trailing spaces
input = input.Trim();
// check for existence of card
if (Helper.EmployeeExists(input, sql))
{
dt = sql.GetDataTable("select * from employees where employeeID=" + input + ";");
timeStampId = int.Parse(dt.Rows[0].ItemArray[6].ToString());
firstName = dt.Rows[0].ItemArray[1].ToString();
// check for clock-in or -out
if (timeStampId > 0)
{
try {
// clock out
Dictionary <String, String> data = new Dictionary <String, String>();
data.Add("clockOut", DateTime.Now.ToString(StringFormats.sqlTimeFormat));
sql.Update("timeStamps", data, String.Format("timeStamps.id = {0}", timeStampId));
sql.ExecuteNonQuery("update employees set currentClockInId = 0 where employeeId=" + input + ";");
} catch (Exception err) {
MessageBox.Show(this, "There was an error while trying to clock you out.\n\n" + err.Message, "Clock Out Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
// calculate time logged
dt = sql.GetDataTable("select strftime('%s', clockOut) - strftime('%s', clockIn) from timeStamps where id=" + timeStampId + ";");
TimeSpan diff = TimeSpan.FromSeconds(long.Parse(dt.Rows[0].ItemArray[0].ToString()));
// show confirmation
LabelStatus.Text = "Goodbye " + firstName + ".\nYou logged " + GenerateClockedTime(diff) + ".";
TimerInputTimeout.Interval = 3000;
TimerInputTimeout.Enabled = true;
}
else
{
// clock in
try {
Dictionary <String, String> data = new Dictionary <String, String>();
data.Add("employeeID", input);
data.Add("clockIn", DateTime.Now.ToString(StringFormats.sqlTimeFormat));
sql.Insert("timeStamps", data);
dt = sql.GetDataTable("select seq from sqlite_sequence where name='timeStamps';");
data.Clear();
data.Add("currentClockInId", dt.Rows[0].ItemArray[0].ToString());
sql.Update("employees", data, String.Format("employees.employeeId = {0}", input));
} catch (Exception err) {
MessageBox.Show(this, "There was an error while trying to clock you in.\n\n" + err.Message, "Clock In Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
// show confirmation
LabelStatus.Text = "Hello " + firstName + "!\nYou're now clocked in.";
TimerInputTimeout.Interval = 2500;
TimerInputTimeout.Enabled = true;
}
}
else
{
// notify user of unrecognized card
LabelStatus.Text = "Unrecognized card!";
TimerInputTimeout.Interval = 2500;
TimerInputTimeout.Enabled = true;
}
// reset input storage
LabelInput.Text = "input: ";
input = "";
}
else if (Char.IsDigit(e.KeyChar) || e.KeyChar == (char)Keys.Back)
{
// add the input
StoreInput(e.KeyChar);
}
}
protected void processSales()
{
OrderedDictionary soldBoughtItems = new OrderedDictionary();
/*
* First of all the old system was not possible to do with SQL
* Would of required to do a SQL Query for each itemId sold and bought by all players
* That would of ment over 10,000 items x 2 (buyers+sellers) so 20,000 queries to process.
*
* The new system I created to fix this.
* Works like this it everytime this runs matches up 1 GE Buying/Selling auction.
* Then loops it 100 times or until no more matches are possible.
*
* TODO: Match Buyer's of larger item quantity with Sellers with larger quanity instead of Sellers with
* any item quantity. (this requires editing the query below to a better suited query).
*/
BuyOffer buyer = null;
SellOffer seller = null;
int itemId;
int itemAmount;
int price;
int sold;
int bought;
int collectedItem;
int collectedGold;
int overpaid;
byte slot;
long playerHash;
SQLiteDatabase db = new SQLiteDatabase(Constants.databaseName);
for (int processNumSales = 0; processNumSales < 100; processNumSales++)
{
soldBoughtItems.Clear();
//Can't do more then 1 sale at a time, LIMIT 100 wont work since it will pick duplicate sellers.
DataTable dt = db.ExecuteQuery("SELECT S.itemId AS sell_itemId, S.amount AS sell_amount, S.price AS sell_price, S.sold AS sell_sold, S.collectedItem AS sell_collectedItem, S.collectedGold AS sell_collectedGold, S.overpaid AS sell_overpaid, S.slot AS sell_slot, S.playerHash AS sell_playerHash, B.itemId AS buy_itemId, B.amount AS buy_amount, B.price AS buy_price, B.bought AS buy_bought, B.collectedItem AS buy_collectedItem, B.collectedGold AS buy_collectedGold, B.overpaid AS buy_overpaid, B.slot AS buy_slot, B.playerHash AS buy_playerHash FROM grandExchangeBuying AS B, grandExchangeSelling AS S ON B.itemId = S.itemId AND B.aborted = 0 AND S.aborted = 0 AND B.price >= S.price AND S.sold < S.amount AND B.bought < B.amount ORDER BY B.price DESC LIMIT 1");
if (dt.Rows.Count == 0)
{
db.CloseDatabase();
return;
}
else
{
itemId = Convert.ToInt32(dt.Rows[0]["sell_itemId"]);
itemAmount = Convert.ToInt32(dt.Rows[0]["sell_amount"]);
price = Convert.ToInt32(dt.Rows[0]["sell_price"]);
sold = Convert.ToInt32(dt.Rows[0]["sell_sold"]);
collectedItem = Convert.ToInt32(dt.Rows[0]["sell_collectedItem"]);
collectedGold = Convert.ToInt32(dt.Rows[0]["sell_collectedGold"]);
overpaid = Convert.ToInt32(dt.Rows[0]["sell_overpaid"]);
slot = Convert.ToByte(dt.Rows[0]["sell_slot"]);
playerHash = (long)dt.Rows[0]["sell_playerHash"];
seller = new SellOffer(itemId, itemAmount, price, sold, collectedItem, collectedGold, overpaid, slot, playerHash);
itemId = Convert.ToInt32(dt.Rows[0]["buy_itemId"]);
itemAmount = Convert.ToInt32(dt.Rows[0]["buy_amount"]);
price = Convert.ToInt32(dt.Rows[0]["buy_price"]);
bought = Convert.ToInt32(dt.Rows[0]["buy_bought"]);
collectedItem = Convert.ToInt32(dt.Rows[0]["buy_collectedItem"]);
collectedGold = Convert.ToInt32(dt.Rows[0]["buy_collectedGold"]);
overpaid = Convert.ToInt32(dt.Rows[0]["buy_overpaid"]);
slot = Convert.ToByte(dt.Rows[0]["buy_slot"]);
playerHash = (long)dt.Rows[0]["buy_playerHash"];
buyer = new BuyOffer(itemId, itemAmount, price, bought, collectedItem, collectedGold, overpaid, slot, playerHash);
}
if (seller == null || buyer == null)
{
continue;
}
int amountToBuy = buyer.getTotalAmount() - buyer.getAmountTraded();
int amountToSell = seller.getTotalAmount() - seller.getAmountTraded();
// This check will never happen. SQL Query will not allow it.. Just old code I left in.
if (amountToBuy <= 0 || amountToSell <= 0)
{
continue;
}
int amount = (amountToBuy > amountToSell) ? amountToSell : amountToBuy;
// Buys from Seller a random amount of a item.
amount = misc.random(1, amount);
// Buyer will pay minimum what the seller wants.
int amountBuyerOverpaid = (buyer.getPriceEach() - seller.getPriceEach()) * amount; // buyer is paying more than the seller wants, therefore MAY recieve this amount as a refund.
bool buyerKeepsRefund = misc.random(1) == 0; // if 0, the buyer gets a refund, if its 1...the seller gets more.
buyer.setAmountTraded(buyer.getAmountTraded() + amount);
seller.setAmountTraded(seller.getAmountTraded() + amount);
/**
* How much refunded gold Buyer gets in addition to his previous refunded gold.
* or
* How much of the Buyer's overpaid gold that they couldn't keep goes as extra profit to the Seller.
*/
if (buyerKeepsRefund && amountBuyerOverpaid > 0)
{
buyer.setAmountOverpaid(buyer.getAmountOverpaid() + amountBuyerOverpaid);
}
else if (!buyerKeepsRefund && amountBuyerOverpaid > 0)
{
seller.setAmountOverpaid(seller.getAmountOverpaid() + amountBuyerOverpaid);
}
// Shows amount of Item Buyer bought in Slot 1 minus how much he already took out.
if (buyer.getAmountItemsLeftToCollect() > 0)
{
buyer.setSlot1(new Item(buyer.getItem(), buyer.getAmountItemsLeftToCollect()));
}
//Shows amount of Gold Buyer has in Slot 2 from previous refunded money plus how much he maybe will be refunded this sale.
if ((buyer.getAmountCollectedGold() < buyer.getAmountOverpaid()) && (buyer.getAmountOverpaid() - buyer.getAmountCollectedGold()) > 0)
{
buyer.setSlot2(new Item(995, (buyer.getAmountOverpaid() - buyer.getAmountCollectedGold())));
}
//Shows amount of Gold Seller in Slot 2 has minus how much he already took out.
if (seller.getAmountGoldLeftToCollect() > 0)
{
seller.setSlot2(new Item(995, seller.getAmountGoldLeftToCollect()));
}
Player buyerP = Server.getPlayerForName(buyer.getPlayerName());
Player sellerP = Server.getPlayerForName(seller.getPlayerName());
//Update both Buyer and Seller's Slots in the database
try
{
db.ExecuteNonQuery("UPDATE grandExchangeBuying SET bought = " + buyer.getAmountTraded() + ", overpaid = " + buyer.getAmountOverpaid() + " WHERE slot = " + buyer.getSlot() + " AND playerHash = " + buyer.getPlayerHash());
db.ExecuteNonQuery("UPDATE grandExchangeSelling SET sold = " + seller.getAmountTraded() + ", overpaid = " + seller.getAmountOverpaid() + " WHERE slot = " + seller.getSlot() + " AND playerHash = " + seller.getPlayerHash());
}
catch (Exception e)
{
//Instantly skip this sale if any database errors occurred.
//If one of those queries works and other fails then you will have a dupe -_-.
//TODO: A fix would be to keep retrying both queries until both successful.
misc.WriteError("[GrandExchange SQL Error]: " + e.Message);
continue;
}
/*
* TODO: Fix When player is offline and logs in after item is bought. He won't see messages below..
* Add something to Packets.cs (sendLogin() method) to process this.
* Maybe a (boolean in playerSaves for GE update?)
*/
if (buyerP != null)
{
buyerP.getPackets().sendMessage("One or more of your Grand Exchange offers has been updated.");
buyerP.getPackets().updateGEProgress(buyer);
if (buyerP.getGESession() != null)
{
Item[] items = { buyer.getSlot1(), buyer.getSlot2() };
buyerP.getPackets().sendItems(-1, -1757, 523 + buyer.getSlot(), items);
}
}
/*
* TODO: Fix When player is offline and logs in after item is bought. He won't see messages below..
* Add something to Packets.cs (sendLogin() method) to process this.
* Maybe a (boolean in playerSaves for GE update?)
*/
if (sellerP != null)
{
sellerP.getPackets().sendMessage("One or more of your Grand Exchange offers has been updated.");
sellerP.getPackets().updateGEProgress(seller);
if (sellerP.getGESession() != null)
{
Item[] items = { seller.getSlot1(), seller.getSlot2() };
sellerP.getPackets().sendItems(-1, -1757, 523 + seller.getSlot(), items);
}
}
}
}
private void simpleButton1_Click(object sender, EventArgs e)
{
Dictionary <string, string> dic = new Dictionary <string, string>();
if (vGridControl3.Rows["editorRow6"].Properties.Value == null)
{
dic.Add("iscost", "0");
}
else
{
dic.Add("iscost", vGridControl3.Rows["editorRow6"].Properties.Value.ToString() == "เตือน"?"1":"0");
}
if (vGridControl3.Rows["editorRow7"].Properties.Value == null)
{
dic.Add("ismin", "0");
}
else
{
dic.Add("ismin", vGridControl3.Rows["editorRow7"].Properties.Value.ToString() == "เตือน" ? "1" : "0");
}
if (vGridControl3.Rows["editorRow8"].Properties.Value == null)
{
dic.Add("islowcost", "0");
}
else
{
dic.Add("islowcost", vGridControl3.Rows["editorRow8"].Properties.Value.ToString() == "เตือน" ? "1" : "0");
}
if (vGridControl3.Rows["editorRow9"].Properties.Value == null)
{
dic.Add("ispricechange", "0");
}
dic.Add("ispricechange", vGridControl3.Rows["editorRow9"].Properties.Value.ToString() == "เตือน" ? "1" : "0");
if (ditem.Rows.Count > 0)
{
db.ExecuteNonQuery("delete from itemconfig");
}
db.Insert("itemconfig", dic);
dic = new Dictionary <string, string>();
dic.Add("formtype", vGridControl2.Rows["editorRow1"].Properties.Value.ToString());
dic.Add("billlock", vGridControl2.Rows["editorRow2"].Properties.Value.ToString() == "ทำงาน" ? "1" : "0");
dic.Add("saleprice", vGridControl2.Rows["editorRow3"].Properties.Value.ToString());
dic.Add("costprice", vGridControl2.Rows["editorRow4"].Properties.Value.ToString());
dic.Add("gp", vGridControl2.Rows["editorRow5"].Properties.Value.ToString());
dic.Add("autopurch", vGridControl2.Rows["row5"].Properties.Value.ToString() == "ทำงาน" ? "1" : "0");
dic.Add("docno", vGridControl2.Rows["row7"].Properties.Value.ToString());
dic.Add("customer", vGridControl2.Rows["row8"].Properties.Value.ToString());
if (dsale.Rows.Count > 0)
{
db.ExecuteNonQuery("delete from saleconfig");
}
db.Insert("saleconfig", dic);
dic = new Dictionary <string, string>();
if (vGridControl1.Rows["row"].Properties.Value == null)
{
dic.Add("companyname", "");
}
else
{
dic.Add("companyname", vGridControl1.Rows["row"].Properties.Value.ToString());
}
if (vGridControl1.Rows["row1"].Properties.Value == null)
{
dic.Add("address", "");
}
else
{
dic.Add("address", vGridControl1.Rows["row1"].Properties.Value.ToString());
}
// = dconfig.Rows[0]["address"].ToString();
if (vGridControl1.Rows["row2"].Properties.Value == null)
{
dic.Add("telephone", "");
}
else
{
dic.Add("telephone", vGridControl1.Rows["row2"].Properties.Value.ToString());
}
if (vGridControl1.Rows["row3"].Properties.Value == null)
{
dic.Add("province", "");
}
else
{
dic.Add("province", vGridControl1.Rows["row3"].Properties.Value.ToString());
}
if (vGridControl1.Rows["row4"].Properties.Value == null)
{
dic.Add("taxid", "");
}
else
{
dic.Add("taxid", vGridControl1.Rows["row4"].Properties.Value.ToString());
}
if (vGridControl1.Rows["row6"].Properties.Value == null)
{
dic.Add("taxrate", "");
}
else
{
dic.Add("taxrate", vGridControl1.Rows["row6"].Properties.Value.ToString());
}
if (dconfig.Rows.Count > 0)
{
db.ExecuteNonQuery("delete from configuration");
}
db.Insert("configuration", dic);
this.Close();
this.DialogResult = DialogResult.OK;
}
public int newUniqueID(MediaStreamingRequest request, bool getIDOnly)
{
int newId = IdsAndInputFilesContains(request.InputFile + request.UniekClientID);
if (request.KeepSameIDForSameInputFile && newId != 0) // != 0 means found as existing id that can be resumed
{
var ms = GetStreamerByID(newId);
if (ms != null && !getIDOnly)
{
//mediaStreamers.Remove(newId);
Functions.WriteLineToLogFile("Streamer newId=" + newId + " about to stop (in background), mediaStreamers.ContainsKey(newId) : " +
mediaStreamers.ContainsKey(newId));
StopStreamer(newId, 2);
Functions.WriteLineToLogFile("Streamer newId=" + newId + " stopped (in background), mediaStreamers.ContainsKey(newId) : " +
mediaStreamers.ContainsKey(newId));
}
if (!getIDOnly)
{
DeleteStreamingFiles(newId); //begin anew, s.t. if new quality, this will be used.
}
//bump up the id in the database
db.Delete("IDANDINPUTFILE", String.Format("STREAMID = {0}", "" + newId));
var item = new Dictionary <string, string>();
item.Add("STREAMID", "" + newId);
string i = request.InputFile.Replace("'", "''");
string u = request.UniekClientID.Replace("'", "''");
item.Add("INPUTFILE", i + u);
db.Insert("IDANDINPUTFILE", item);
return(newId);
}
//clean up if database is large
const int maxFileToRememberResume = 1000;
int count = 0;
Int32.TryParse(db.ExecuteScalar("select COUNT(*) from IDANDINPUTFILE where STREAMID IS NOT NULL;"),
out count);
if (count > maxFileToRememberResume)
{
try
{
DataTable tabel;
String query = "select STREAMID, INPUTFILE from IDANDINPUTFILE;";
tabel = db.GetDataTable(query);
// The results can be directly applied to a DataGridView control
// recipeDataGrid.DataSource = tabel;
// Or looped through for some other reason
var i = 0;
foreach (DataRow r in tabel.Rows)
{
if (i < count / 2)
{
db.ExecuteNonQuery("delete from IDANDINPUTFILE where STREAMID=" + r["STREAMID"] + ";");
}
else
{
}
i++;
}
}
catch (Exception fail)
{
String error = "The following error has occurred in cleaning up database : " + db.ToString() + "\n";
error += fail.Message;
if (Settings.Default.DebugStreaming)
{
Functions.WriteLineToLogFile("StreamingManager: " + error);
}
}
}
do
{
var r = new Random();
//newId = (getIDOnly ? r.Next(100000, 999999) : r.Next(10000, 99999));
newId = r.Next(10000, 99999);
} while (mediaStreamers.ContainsKey(newId) || !IdsAndInputFilesContains(newId).Equals(""));
if (IdsAndInputFilesContains(request.InputFile + request.UniekClientID) == 0 && !request.InputFile.Contains("RMCLiveTV")) //live tv gets a new iD all the time anyway, due to randowm nr in inputfile string
{
var item = new Dictionary <string, string>();
item.Add("STREAMID", "" + newId);
string i = request.InputFile.Replace("'", "''");
string u = request.UniekClientID.Replace("'", "''");
item.Add("INPUTFILE", i + u);
db.Insert("IDANDINPUTFILE", item);
// db.CommitTransaction();
}
return(newId);
}
public static VirtualRider New(string userId)
{
VirtualRider r = new VirtualRider();
UserRider u = UserRider.Load(userId);
r.UserId = Guid.NewGuid().ToString();
r.OwnerId = userId;
r.UserName = "******";
r.BikeWheelSizeMm = 700;
r.EstimatedPower = false;
r.TurboIsCalibrated = false;
r.CurrentTurbo = "";
r.Aggression = 50;
r.PowerMin = 150;
r.Power1Hr = 200;
r.Power5Min = 250;
r.Power1Min = 300;
r.Power5Sec = 500;
// It's a new profile
string sqlCount = @"select count(*) from cycli_virtual_riders where OwnerId=@o";
string sqlRider = @"insert into cycli_virtual_riders (UserId, OwnerId, Username, PowerMinimum, Power1Hr, Power5Min, Power1Min, Power5Sec, Aggression) " +
"values (@u, @o, @n, @p4, @p3, @p2, @p1, @p0, @a) ";
SQLiteDatabase db = new SQLiteDatabase();
try
{
// This is a backstop test - the front end should prevent extra riders
if (int.Parse(db.ExecuteScalar(sqlCount, "@o", userId)) < u.MaximumVirtualRiders)
{
db.ExecuteNonQuery(sqlRider,
"@u", r.UserId,
"@o", r.OwnerId,
"@n", r.UserName,
"@p4", r.PowerMin,
"@p3", r.Power1Hr,
"@p2", r.Power5Min,
"@p1", r.Power1Min,
"@p0", r.Power5Sec,
"@a", r.Aggression);
}
}
finally
{
db.Close();
}
return r;
}
public static Friend MakeFriend(string userId, string friendCode)
{
Friend friend = null;
UserRider rider = UserRider.Load(userId);
if (rider != null)
{
Friend[] existingFriends = Load(userId);
SQLiteDatabase db = new SQLiteDatabase();
// Some logic here - we only allow a new friend if
// (a) The friendship does not already exist
// (b) The friendCode is correct
// (c) We haven't exeeded out friend limit
string sqlFriend = "select UserId, UserName from cycli_riders where FriendCode = @c";
DataTable dt = db.GetDataTable(sqlFriend, "@c", friendCode);
if (dt.Rows.Count > 0)
{
string friendId = dt.Rows[0].Field<string>("UserId");
string friendName = dt.Rows[0].Field<string>("UserName");
if (existingFriends.Count() < rider.MaximumFriends && existingFriends.Count(p => p.UserId == friendId) == 0)
{
string sql = "insert into cycli_friends (UserId, FriendId, Status) values (@u, @f, 'Accepted')";
if (db.ExecuteNonQuery(sql, "@u", userId, "@f", friendId) == 1)
{
friend = new Friend { UserName = friendName, UserId = friendId, Status = "Accepted" };
}
}
}
// This query checks that the user has not already been invited
db.Close();
}
return friend;
}
static void Main(string[] args)
{
// Initialize the Chrome Driver
using (var driver = new ChromeDriver())
{
// Go to the home page
driver.Navigate().GoToUrl("http://www.amazon.com/s/ref=lp_2368343011_nr_n_0?rh=n%3A1036592%2Cn%3A%211036682%2Cn%3A1040660%2Cn%3A2368343011%2Cn%3A2368365011&bbn=2368343011&ie=UTF8&qid=1397584947&rnid=2368343011");
bool pagnNextLink = true;
#region sample to input a form
// Get User Name field, Password field and Login Button
//var userNameField = driver.FindElementById("usr");
//var userPasswordField = driver.FindElementById("pwd");
//var loginButton = driver.FindElementByXPath("//input[@value='Login']");
//// Type user name and password
//userNameField.SendKeys("admin");
//userPasswordField.SendKeys("12345");
//// and click the login button
//loginButton.Click();
#endregion
ReadOnlyCollection <IWebElement> products = driver.FindElementsByClassName("newaps");
SQLiteDatabase db = new SQLiteDatabase();
db.ExecuteNonQuery(@"CREATE TABLE IF NOT EXISTS products (id integer primary key,
product varchar(1024), url varchar(1024), price varchar(16), sku varchar(64), description,
attribute_key_value_json varchar(2048) , crawled_on datetime
)");
int page = 1;
Int64 total_products = 0;
total_products += products.Count;
Console.WriteLine("---------------------------------------------");
Console.WriteLine("------------------- Page {0} , Products in page {1}, Total Products {2} ", page, products.Count, total_products);
Console.WriteLine("---------------------------------------------");
do
{
int index = 0;
foreach (IWebElement product in products)
{
IList <IWebElement> prices = (IList <IWebElement>)driver.FindElementsByCssSelector(".bld.lrg.red,.price.bld");
IList <IWebElement> names = (IList <IWebElement>)driver.FindElementsByCssSelector(".prod.celwidget");
IWebElement anchor = product.FindElement(By.TagName("a"));
Dictionary <String, String> data = new Dictionary <String, String>();
data.Add("product", product.Text.Replace("'", "''"));
data.Add("price", prices[index].Text.Replace("'", "''"));
data.Add("sku", names[index].GetAttribute("name").Replace("'", "''"));
data.Add("url", anchor.GetAttribute("href").Replace("'", "''"));
db.Insert("products", data);
Console.WriteLine(names[index].GetAttribute("name").Replace("'", "''"));
index++;
}
total_products += products.Count;
if (driver.FindElementById("pagnNextLink") != null)
{
//driver.Manage().Timeouts().SetPageLoadTimeout(TimeSpan.FromSeconds(10));
string _nextUrl = driver.FindElementById("pagnNextLink").GetAttribute("href");
driver.Navigate().GoToUrl(_nextUrl);
products = null;
products = driver.FindElementsByClassName("newaps");
WebDriverWait wait = new WebDriverWait(driver, TimeSpan.FromSeconds(5));
wait.Until(i => i.FindElement(By.Id("footer")));
page++;
}
else
{
pagnNextLink = false;
}
Console.WriteLine("---------------------------------------------");
Console.WriteLine("------------------- Page {0} , Products in page {1}, Total Products {2} ", page, products.Count, total_products);
Console.WriteLine("---------------------------------------------");
}while (pagnNextLink);
Console.WriteLine("Total products : {0},", products.Count);
// Extract resulting message and save it into result.txt
//var result = driver.FindElementByXPath("//div[@id='case_login']/h3").Text;
//sFile.WriteAllText("result.txt", result);
// Take a screenshot and save it into screen.png
//driver.GetScreenshot().SaveAsFile(@"screen.png", ImageFormat.Png);
}
Console.ReadKey();
}
