protected override void PopulateVersionAndTypes() { base._sqlVersion = "10"; List<KeyValuePair<int, byte>> list = new List<KeyValuePair<int, byte>>(); using (SqlDataReader reader = new SqlCommand("select system_type_id, user_type_id, name from sys.types where system_type_id = user_type_id", base._cx).ExecuteReader()) { while (reader.Read()) { DbTypeInfo dbTypeInfo = SqlSchemaReader.GetDbTypeInfo(reader.GetString(2)); if (!((dbTypeInfo == null) || base._sqlTypes.ContainsKey(reader.GetInt32(1)))) { base._sqlTypes.Add(reader.GetInt32(1), dbTypeInfo); } else { list.Add(new KeyValuePair<int, byte>(reader.GetInt32(1), reader.GetByte(0))); } } } foreach (KeyValuePair<int, byte> pair in list) { if (base._sqlTypes.ContainsKey(pair.Value)) { base._sqlTypes[pair.Key] = base._sqlTypes[pair.Value]; } } }
public void readFromDataBase() { items.Clear(); SqlDataReader reader = new SqlCommand(string.Format("SELECT * FROM Items ORDER BY Id;SELECT * FROM Jobs ORDER BY Item;SELECT * FROM Jobs ORDER BY Item;"), cn).ExecuteReader(); //читаем лист items while (reader.Read()) { item newItem = new item(); newItem.id = reader.GetInt32(0); newItem.firstName = reader.GetString(1); newItem.lastName = reader.GetString(2); items.Add(newItem); } int i; int lastid; //читаем лист jobs reader.NextResult(); lastid = i = -1; while (reader.Read()) { int k = reader.GetInt32(4); if (lastid != k) { do ++i; while (k != items[i].id); lastid = reader.GetInt32(4); } items[i].jobs.Add(new job(reader.GetDateTime(0), reader.GetString(1), reader.GetString(2), reader.GetString(3))); } //читаем лист positions reader.NextResult(); lastid = i = -1; while (reader.Read()) { if (lastid != reader.GetInt32(4)) { do i++; while (reader.GetInt32(4) != items[i].id); lastid = reader.GetInt32(4); } items[i].positions.Add(new position(reader.GetInt64(0),reader.GetInt64(1),reader.GetInt32(2),reader.GetDateTime(3))); } reader.Close(); }
public static void Main(string[] args) { //Console.WriteLine(response); try { while (true) { using (var sql = new SqlConnection(Secrets.ConnectionString)) { sql.Open(); // dbo.StravaKeys contains registered API keys; let's collect all of them #region strava var api_base = "https://www.strava.com/api/v3/"; var keysQuery = "SELECT * from dbo.StravaKeys;"; Log.Debug(keysQuery); var results = new List<Tuple<int, string>>(); using (var keysResults = new SqlCommand(keysQuery, sql).ExecuteReader()) { while (keysResults.Read()) { results.Add(new Tuple<int, string>(keysResults.GetInt32(0), keysResults.GetString(1))); } } Log.Info("Got {0} keys to check", results.Count); // get updated results for each StravaKey and stick them in the DB. foreach (var res in results) { var accessToken = $"&access_token={res.Item2}"; var epoch = 1451624400; // jan 1 2016 long secondsSinceEpoch; // select the last time we updated this user; if we've never updated them, get events since January 1, 2016 var lastRunQuery = $"SELECT activity_time FROM dbo.StravaActivities WHERE strava_id={res.Item1} ORDER BY activity_time DESC;"; Log.Debug(lastRunQuery); using (var lastRun = new SqlCommand(lastRunQuery, sql).ExecuteReader()) { if (lastRun.HasRows) { lastRun.Read(); secondsSinceEpoch = (long) Math.Max(epoch, lastRun.GetDateTime(0).Subtract(new DateTime(1970, 1, 1)).TotalSeconds); } else { secondsSinceEpoch = epoch; } } // transform our DB results into <DateTime, double, string> tuples var apiUrl = $"{api_base}athlete/activities/?per_page=200&after={secondsSinceEpoch}{accessToken}"; Log.Info("Hitting API at {0}", apiUrl); var runs = GetArrayFromApi(apiUrl) .Select(e => new Tuple<DateTime, double, string>( DateTime.Parse((string)e["start_date"]), (double)e["distance"] / 1609, // translate meters to miles (string)e["type"])) .ToList(); // if we actually got any tuples, transform them into an INSERT query Log.Info("Got {0} results from Strava API", runs.Count); if (runs.Any()) { // aggregate the tuples into a StringBuilder var insertionQuery = runs.Aggregate( new StringBuilder( "INSERT INTO dbo.StravaActivities (strava_id, activity_time, activity_distance, activity_type) VALUES "), (acc, e) => acc.Append($"({res.Item1}, '{e.Item1}', {e.Item2}, '{e.Item3}'), ")); // trim the last ", " from the aggregated tuples and append a semicolon insertionQuery.Remove(insertionQuery.Length - 2, 2).Append(";"); // insert! Log.Debug(insertionQuery); using (var inserter = new SqlCommand(insertionQuery.ToString(), sql)) inserter.ExecuteNonQuery(); } } #endregion #region withings Log.Info("Hitting Withings API"); var lastMeasure = new DateTime(2016, 1, 1); var unixTimeStart = new DateTime(1970, 1, 1); var lastEntryQuery = "SELECT measure_date FROM dbo.bodyFat ORDER BY measure_date DESC;"; using (var lastEntry = new SqlCommand(lastEntryQuery, sql).ExecuteReader()) { if (lastEntry.Read()) { lastMeasure = lastEntry.GetDateTime(0); } } var withingsResponse = GetObjectFromApi(Secrets.WithingsApi); var bodyFatMeasures = ((JArray)withingsResponse["body"]["measuregrps"]) .Where(e => (long)e["date"] > (long)lastMeasure.Subtract(unixTimeStart).TotalSeconds && ((JArray)e["measures"]).Any(f => (int)f["type"] == 8)) .Select(e => new Tuple<DateTime, double>(unixTimeStart.AddSeconds((int)e["date"]), (double)(((JArray)e["measures"]).First(token => (int)token["type"] == 8)["value"]) * Math.Pow(10, (double)((JArray)e["measures"]).First(token => (int)token["type"] == 8)["unit"]))) .ToList(); if (bodyFatMeasures.Any()) { Log.Info("Got {0} new body fat measurements", bodyFatMeasures.Count); var bodyFatQuery = bodyFatMeasures.Aggregate( new StringBuilder("INSERT INTO dbo.bodyFat (measure_date, measure) VALUES "), (a, b) => a.Append($"('{b.Item1}', {b.Item2}), ")); bodyFatQuery.Remove(bodyFatQuery.Length - 2, 2).Append(";"); Log.Debug(bodyFatQuery); using (var insertionQuery = new SqlCommand(bodyFatQuery.ToString(), sql)) { insertionQuery.ExecuteNonQuery(); } } else { Log.Info("No new body fat measurements."); } #endregion } Log.Info("Sleeping..."); System.Threading.Thread.Sleep(15 * 1000 * 60); } } catch (WebException ex) { Log.Error(ex); } catch (Exception ex) { Log.Fatal(ex); throw; } }
public void Update() { Log.WriteLine(LogLevel.Info, "Looking for database updates..."); int version = 0; int patch = 0; using (SqlConnection connection = new SqlConnection(ConnectionStringbuilder.CreateConnectionString(this._setting))) { connection.Open(); // Check DB version try { using (var reader = new SqlCommand("SELECT [Version] FROM [SolarVersion]", connection).ExecuteReader()) { if (reader.Read()) { version = reader.GetInt32(0); } } } catch { } // default is set to 0 :D Log.WriteLine(LogLevel.Debug, "Current Database version is {0}", version); // Now we have the function, lets see which files there are... if (Directory.Exists("SQL")) { string type = _type == DatabaseTypes.Login ? "login" : "world"; foreach (string filename in Directory.GetFiles("SQL", type + "_*.sql")) { try { string[] pieces = filename.Split('_'); // login_XX_dat-a-lawl.sql int p = int.Parse(pieces[1]); if (p <= version) continue; // Already ran this one! if (p < patch) { Log.WriteLine(LogLevel.Warn, "Patch ID out of order O.o. Using last patch ID instead: {0}", patch); } else { patch = p; } string message = pieces[2].Replace(".sql", ""); Log.WriteLine(LogLevel.Info, "Trying to update {0} database with patch {1}. Message:", type, patch); Log.WriteLine(LogLevel.Info, message); RunFile(filename, connection); } catch (Exception ex) { Log.WriteLine(LogLevel.Exception, "Could not parse file {0}: {1}", filename, ex.ToString()); Console.ReadLine(); Environment.Exit(400); } } if (version < patch) { Log.WriteLine(LogLevel.Info, "Database updated!"); version = patch; // Try to update table to new version using (SqlCommand cmd = new SqlCommand("DELETE FROM [SolarVersion];", connection)) cmd.ExecuteNonQuery(); using (SqlCommand cmd = new SqlCommand("INSERT INTO [SolarVersion] VALUES (" + patch.ToString() + ");", connection)) cmd.ExecuteNonQuery(); } else { Log.WriteLine(LogLevel.Info, "Database up-to-date!"); } } else { Log.WriteLine(LogLevel.Error, "Couldn't find SQL dir. Cannot update db."); } connection.Close(); } }