NOTE: this code has been quickly evolving into the functionality presented here. Eventually I will break it out into a simple exe such as listed below, but for now......
Currently the only way to find a user by the MD5 hash of a lowercased email address (user.email_hash as well as gravatar key) is to maintain a local datastore containing the users you are interested in servicing, which you may index and query for this and other information.
At first blush, this task seems straight forward. And it is. You surely can point a firehose at the API and get what you want. If you wish to piss off people you don't really want to piss off ;-) Not to mention poorly shepherding your rate-limit quota, network, disk and CPU saturation.
With a bit of planning and a measured approach this task can be broken down into a handful of not-long and not-short running processes that can gently provide you with a valuable dataset with many uses, one of which will be included in the following example.
Enough talk, let me introduce you to a simple Soapi spike named UserDatabase.exe, a windows console app that you may re-purpose to your heart's content.
UserDatabase.exe
The purpose of this app is to responsibly and efficiently maintain a local database of all (or most) Stack Exchange users above an arbitrary reputation threshold.
This implementation will use a System.Data.SQLite data store the code is quite easily ported to any RDBMS.
There are 2 modes of use for this application:
- Performing a full update via sequential trickle pull of all users at or above minimum rep threshold. mode="PULL"
- Refreshing with only users created since last pull. mode="REFRESH"
At the completion of each mode, users will be ranked ordered by siteUrl, rep desc, creation, and userId. (this is the little bit extra I was talking about).
Additionally we will explore using Site.Aliases to migrate users when a site moves.
What to pull
For most use cases, you are only interested in users above a certain reputation.
The minimum recommended reputation is 100 as the vast majority of users on the larger sites are both under 100, anonymous and/or inactive.
If you pull >= 1000 rep, the cycle will be very prompt, less than 5 minutes for ALL stackexchange users with rep >=1000 and result in a SQLite file around 10mb with around 220 total requests.
If you pull >= 100 rep, the cycle will be lest prompt, around 25 minutes to finish the sequential trickle pull of Stack Overflow and result in a SQLite file around 50mb.
This is at max 1 request per second. If you open the firehose and pull at full speed you will save only a very small percentage of time, so being conservative is the best approach all the way around.
A full pull will take several hours to complete the sites with larger user bases such as Stack Overflow and Meta Stack Overflow.
In the case of Stack Overflow, this means 3000+ requests vs 100 requests for rep 1000 and 400 requests for rep 100. The choice is up to you.
If there is a valid use case for a pull to 0, I would suggest performing it but once a week at a time when you know you have 3000 extra stack overflow requests to burn.
Usage:
The command line for this app is as follows:
UserDatabase.exe ("pull"|"refresh") "apiKey" "connectionString" minRep
e.g. the debug command line of the sample project is
// get all users from all sites with rep >= 1000
UserDatabase.exe pull "my-foo-bar-fu-is-strong" "data source=users.db" 1000
to top-off or refresh just those users that have been created since last pull:
// this will typically involve a single request for each site and take less
// than a minute unless your minRep is set very low.
UserDatabase.exe refresh "my-foo-bar-fu-is-strong" "data source=users.db" 1000
NOTE: Pull processes are throttled at a very low rate, max 1 request per second, and full pulls should be considered long running processes. The upside is that the impact on your overall throttle quota will be negligible as will network and CPU usage. Another compelling reason to restrict request rate as such is the fact that many threads will be in contention for a lock on the database file. This restricted rate gives those threads a good chance at the lock and actually adds to efficiency.
The code:
Program.cs
internal class Program
{
private static int _pullSiteCount;
private static IDataAccessHelper _dal;
private static ApiContext _context;
/// <summary>
/// NOTE: This application should be run as a single instance.
/// Various and sundry nasty and unexpected events _may_ occur if
/// multiple instances of this app run concurrently
/// </summary>
/// <param name="args">
/// args[0] = mode ["PULL","REFRESH"]
/// args[1] = apiKey
/// args[2] = connection string
/// args[3] = minimum reputation threshold
/// </param>
private static void Main(string[] args)
{
string mode = args[0];
string apiKey = args[1];
string connectionString = args[2];
bool pullAll = mode.ToUpper() == "PULL";
int minRep = args.Length > 2 ? Convert.ToInt32(args[3]) : 1000;
// 1 reqs per second, strict throttle
RequestThrottle.Instance.ThrottleWindowCount = 1;
RequestThrottle.Instance.ThrottleWindowTime = new TimeSpan(0, 0, 1);
Console.WriteLine("Initializing ApiContext...");
try
{
_context = new ApiContext(apiKey);
_context.Initialize(false);
}
catch (Exception ex)
{
Console.WriteLine("ERROR INITIALIZING CONTEXT: {0} ", ex);
return;
}
_dal = new SQLiteDataAccessHelper(_context,connectionString);
Console.WriteLine("Initializing database...");
try
{
_dal.EnsureDatabase();
}
catch (Exception ex)
{
Console.WriteLine("ERROR INITIALIZING DATABASE: {0} ", ex);
return;
}
Console.WriteLine("Migrating users...");
try
{
_dal.MigrateUsers();
}
catch (Exception ex)
{
Console.WriteLine("ERROR MIGRATING : {0} ", ex);
return;
}
Pull(minRep, pullAll);
Console.WriteLine("Ranking sites...");
try
{
_dal.RankSites();
}
catch (Exception ex)
{
Console.WriteLine("ERROR RANKING : {0} ", ex);
}
Console.WriteLine("Operation complete.");
if(Debugger.IsAttached)
{
Console.WriteLine("Press a key to exit.");
Console.ReadKey();
}
}
/// <summary>
/// Spawns a thread for each site and updates users.
/// </summary>
/// <param name = "_context">An initialized ApiContext</param>
/// <param name = "connection">An open connection</param>
/// <param name = "minRep">The minimum reputation threshold.</param>
/// <param name = "pullAll">If true, pull all users at or above minRep. If false, just pull those users created since last pull.</param>
private static void Pull(int minRep, bool pullAll)
{
_pullSiteCount = _context.Sites.Count;
var gate = new AutoResetEvent(false);
foreach (Site outerSite in _context.Sites)
{
ThreadPool.QueueUserWorkItem(state =>
{
var innerSite = (Site) state;
// get a consistent length for siteurl for clean output
int maxSiteUrlLength = innerSite.Context.Sites.Max(i => i.SiteUrl.Length);
string formattedSiteUrl = innerSite.SiteUrl.Substring(7).PadRight(maxSiteUrlLength + 2);
try
{
DateTime? lastCreated = pullAll
? new DateTime(1970, 1, 1)
: _dal.GetLatestCreationDate(innerSite.SiteUrl);
// we want finer control over the paging so we will go old school and use a reusable routemap
UsersRouteMap routeMap = innerSite.RouteFactory.Users(new UsersParameters
{
// we are only pulling those created on or after last pull there will be some duplicates but not many.
FromDate = lastCreated.Value,
ToDate = new DateTime(2040, 1, 1),
// only pull those with rep >= minRep
Min = minRep,
Max = int.MaxValue,
// sort asc because users are more likely to gain rep than lose and we want to catch them
Order = SortOrder.Asc,
Page = 1,
PageSize = 100
});
// prime the pump
UsersResponse response = routeMap.GetResponse();
while (response.Items.Count > 0 && response.Items[0].Reputation >= minRep)
{
double pageCount = Math.Floor((double)response.Total / (response.PageSize)) + 1;
Console.WriteLine("{0} page {1} of {2}", formattedSiteUrl, response.Page, pageCount);
// insert/update values
_dal.InsertUpdateUsers(response.Items);
// get next page
routeMap.Parameters.Page = routeMap.Parameters.Page + 1;
response = routeMap.GetResponse();
}
}
catch (Exception ex)
{
Console.WriteLine("{0} ERROR PULLING : {1} ", formattedSiteUrl, ex.ToString());
}
_pullSiteCount--;
if (_pullSiteCount == 0)
{
// all sites pulled, exit
gate.Set();
}
}, outerSite);
}
gate.WaitOne();
}
}
SQLiteDataAccessHelper .cs
public class SQLiteDataAccessHelper : IDataAccessHelper
{
#region Fields
private readonly string _connectionString;
private readonly object _syncLock=new object();
private readonly ApiContext _context; // controlled bleed
#endregion
#region Constructors
public SQLiteDataAccessHelper(ApiContext context, string connectionString)
{
_context = context;
_connectionString = connectionString;
}
#endregion
#region Public Methods
/// <summary>
/// Creates database if it does not exist.
/// </summary>
public void EnsureDatabase()
{
using (var connection = new SQLiteConnection(_connectionString))
{
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = CreateDatabaseScript;
cmd.ExecuteNonQuery();
}
connection.Close();
}
}
/// <summary>
/// Migrates users from a moved site to new home.
/// </summary>
/// <param name="siteUrl">The url of the site to query</param>
/// <returns></returns>
public DateTime GetLatestCreationDate(string siteUrl)
{
DateTime? lastCreated;
// only get those created since last full pull
using (var connection = new SQLiteConnection(_connectionString))
{
connection.Open();
using (SQLiteCommand cmd = connection.CreateCommand())
{
cmd.CommandText =
"select CreationDate from Users where SiteUrl=@SiteUrl order by CreationDate desc limit 1";
cmd.Parameters.AddWithValue("@SiteUrl", siteUrl);
lastCreated = (DateTime?)cmd.ExecuteScalar();
}
}
if (!lastCreated.HasValue)
{
lastCreated = new DateTime(1970, 1, 1);
}
return lastCreated.Value;
}
/// <summary>
/// Inserts or updates a list of users.
/// </summary>
/// <param name = "users">The list of User to insert or update</param>
public void InsertUpdateUsers(List<User> users)
{
// this method is going to be barraged by multiple threads.
// a lock here will dramatically improve performance
lock (_syncLock)
{
using (var connection = new SQLiteConnection(_connectionString))
{
connection.Open();
using (var txn = connection.BeginTransaction())
{
var cmd = BuildInsertCommand();
cmd.Connection = connection;
foreach (User item in users)
{
cmd.Parameters["@SiteUrl"].Value = item.Site.SiteUrl;
cmd.Parameters["@UserId"].Value = item.UserId;
cmd.Parameters["@AssociationId"].Value = item.AssociationId;
cmd.Parameters["@UserType"].Value = (int)item.UserType;
cmd.Parameters["@DisplayName"].Value = item.DisplayName;
cmd.Parameters["@AboutMe"].Value = item.AboutMe;
cmd.Parameters["@Location"].Value = item.Location;
cmd.Parameters["@WebsiteUrl"].Value = item.WebsiteUrl;
cmd.Parameters["@EmailHash"].Value = item.EmailHash;
cmd.Parameters["@CreationDate"].Value = item.CreationDate;
cmd.Parameters["@LastAccessDate"].Value = item.LastAccessDate;
cmd.Parameters["@TimedPenaltyDate"].Value = item.TimedPenaltyDate;
cmd.Parameters["@AcceptRate"].Value = item.AcceptRate;
cmd.Parameters["@Age"].Value = item.Age;
cmd.Parameters["@AnswerCount"].Value = item.AnswerCount;
cmd.Parameters["@DownVoteCount"].Value = item.DownVoteCount;
cmd.Parameters["@QuestionCount"].Value = item.QuestionCount;
cmd.Parameters["@Reputation"].Value = item.Reputation;
cmd.Parameters["@UpVoteCount"].Value = item.UpVoteCount;
cmd.Parameters["@ViewCount"].Value = item.ViewCount;
if (item.BadgeCounts != null)
{
cmd.Parameters["@BadgesGold"].Value = item.BadgeCounts.Gold;
cmd.Parameters["@BadgesSilver"].Value = item.BadgeCounts.Silver;
cmd.Parameters["@BadgesBronze"].Value = item.BadgeCounts.Bronze;
}
cmd.ExecuteNonQuery();
}
txn.Commit();
}
}
}
}
/// <summary>
/// Updates users that currently belong to sites that have moved since last update.
/// NOTE: this method is as yet untested. Keep an eye on it or force feed the RequestCache some
/// munged /sites JSON.
/// </summary>
public void MigrateUsers()
{
using (var connection = new SQLiteConnection(_connectionString))
{
connection.Open();
foreach (Site site in _context.Sites)
{
if (site.Aliases.Count > 0)
{
var siteList = "'" + string.Join("','", site.Aliases.ToArray()) + "'";
using (var txn = connection.BeginTransaction())
{
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = "update users set siteUrl=@siteUrl where siteUrl in (" + siteList + ");";
cmd.Parameters.AddWithValue("@siteUrl", site.SiteUrl);
cmd.ExecuteNonQuery();
}
txn.Commit();
}
}
}
}
}
/// <summary>
/// Ranks all users of a site by Reputation desc, CreationDate asc, UserId asc
/// e.g. Skeet is ranked 1 on stackoverflow
/// </summary>
/// <param name = "siteUrl">The url of the site to rank</param>
public void RankSite(string siteUrl)
{
using (var connection = new SQLiteConnection(_connectionString))
{
connection.Open();
using (var txn = connection.BeginTransaction())
{
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = "update users set rank=null where siteUrl=@siteUrl";
cmd.Parameters.AddWithValue("@siteUrl", siteUrl);
cmd.ExecuteNonQuery();
}
txn.Commit();
}
using (var txn = connection.BeginTransaction())
{
var ids = new List<int>();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText =
"select UserId from users where siteUrl=@siteUrl order by reputation desc, creationdate asc,userid asc ";
cmd.Parameters.AddWithValue("@siteUrl", siteUrl);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
ids.Add(reader.GetInt32(0));
}
}
}
var rank = 0;
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = "update users set rank = @rank where siteUrl=@siteUrl and userId=@userId";
cmd.Parameters.Add("@rank", DbType.Int32);
cmd.Parameters.Add("@userId", DbType.Int32);
cmd.Parameters.AddWithValue("@siteUrl", siteUrl);
ids.ForEach(id =>
{
cmd.Parameters["@userId"].Value = id;
cmd.Parameters["@rank"].Value = ++rank;
cmd.ExecuteNonQuery();
});
}
txn.Commit();
}
}
}
/// <summary>
/// Ranks all sites
/// </summary>
public void RankSites()
{
_context.Sites.ForEach(site => RankSite(site.SiteUrl));
}
#endregion
#region Private Methods
/// <returns>A command configured for inserting or updating a User</returns>
private static SQLiteCommand BuildInsertCommand()
{
var cmd = new SQLiteCommand(SqlUserInsertOrUpdate);
foreach (var item in new List<object[]>
{
new object[] {"@SiteUrl", DbType.String, false, 256},
new object[] {"@UserId", DbType.Int32, false},
new object[] {"@AssociationId", DbType.Guid, true},
new object[] {"@UserType", DbType.Int32, true},
new object[] {"@DisplayName", DbType.String, true, 50},
new object[] {"@AboutMe", DbType.String, true, 4000},
new object[] {"@Location", DbType.String, true, 128},
new object[] {"@WebsiteUrl", DbType.String, true, 256},
new object[] {"@EmailHash", DbType.String, true, 64},
new object[] {"@CreationDate", DbType.DateTime, false},
new object[] {"@LastAccessDate", DbType.DateTime, false},
new object[] {"@TimedPenaltyDate", DbType.DateTime, true},
new object[] {"@AcceptRate", DbType.Int32, true},
new object[] {"@Age", DbType.Int32, true},
new object[] {"@AnswerCount", DbType.Int32, false},
new object[] {"@DownVoteCount", DbType.Int32, false},
new object[] {"@QuestionCount", DbType.Int32, false},
new object[] {"@Reputation", DbType.Int32, false},
new object[] {"@UpVoteCount", DbType.Int32, false},
new object[] {"@ViewCount", DbType.Int32, false},
new object[] {"@BadgesGold", DbType.Int32, true},
new object[] {"@BadgesSilver", DbType.Int32, true},
new object[] {"@BadgesBronze", DbType.Int32, true},
new object[] {"@Rank", DbType.Int32, true}
})
{
var p = new SQLiteParameter
{
ParameterName = (string)item[0],
DbType = (DbType)item[1],
IsNullable = (bool)item[2]
};
if (p.DbType == DbType.String)
{
p.Size = (int)item[3];
}
cmd.Parameters.Add(p);
}
return cmd;
}
/// <summary>
/// data reader helper methods from http://skysanders.net/subtext/archive/0001/01/01/generic-nullsafe-idatarecord-field-getter.aspx
/// </summary>
private static T GetValueOrDefault<T>(IDataRecord row, int ordinal)
{
return (T)(row.IsDBNull(ordinal) ? default(T) : row.GetValue(ordinal));
}
#endregion
#region Constants
private const string CreateDatabaseScript =
@"
CREATE TABLE IF NOT EXISTS [Users] (
[RowId] integer PRIMARY KEY AUTOINCREMENT NOT NULL,
[SiteUrl] nvarchar(256) NOT NULL,
[UserId] int NOT NULL,
[AssociationId] guid,
[UserType] int,
[DisplayName] nvarchar(50),
[AboutMe] nvarchar(4000),
[Location] nvarchar(128),
[WebsiteUrl] nvarchar(256),
[EmailHash] varchar(64),
[CreationDate] datetime NOT NULL,
[LastAccessDate] datetime NOT NULL,
[TimedPenaltyDate] datetime,
[AcceptRate] int,
[Age] int,
[AnswerCount] int NOT NULL,
[DownVoteCount] int NOT NULL,
[QuestionCount] int NOT NULL,
[Reputation] int NOT NULL,
[UpVoteCount] int NOT NULL,
[ViewCount] int NOT NULL,
[BadgesGold] int,
[BadgesSilver] int,
[BadgesBronze] int,
[Rank] int
);
CREATE INDEX IF NOT EXISTS [IX_SiteUrl_Reputation_CreationDate_UserId] ON [Users] ([SiteUrl], [Reputation] DESC, [CreationDate], [UserId]);
CREATE UNIQUE INDEX IF NOT EXISTS [IX_SiteUrl_UserId] ON [Users] ([SiteUrl], [UserId]);
CREATE INDEX IF NOT EXISTS [IX_SiteUrl_CreationDate] ON [Users] ([SiteUrl], [CreationDate] DESC);
CREATE INDEX IF NOT EXISTS [IX_SiteUrl_Rank] ON [Users] ([SiteUrl], [Rank]);
CREATE TABLE IF NOT EXISTS [Profiles] (
[RowId] integer PRIMARY KEY AUTOINCREMENT NOT NULL,
[EmailAddressLowered] nvarchar(256) NOT NULL UNIQUE,
[EmailHash] nvarchar(64) NOT NULL,
[LocalDisplayName] nvarchar(40) NOT NULL,
[SiteUrl] nvarchar(256),
[UserId] int,
[AssociationId] guid,
[Verified] bit NOT NULL DEFAULT 0
);
";
/// <summary>
/// the special SQLite 'INSERT OR REPLACE INTO' syntax maps to SQL psuedocode as IF EXISTS UPDATE ELSE INSERT
/// </summary>
private const string SqlUserInsertOrUpdate =
@"
INSERT OR REPLACE INTO [Users] ([SiteUrl] , [UserId], [AssociationId], [UserType], [DisplayName], [AboutMe], [Location], [WebsiteUrl],
[EmailHash] , [CreationDate], [LastAccessDate], [TimedPenaltyDate], [AcceptRate], [Age], [AnswerCount], [DownVoteCount],
[QuestionCount], [Reputation], [UpVoteCount], [ViewCount], [BadgesGold], [BadgesSilver], [BadgesBronze], [Rank])
VALUES (@SiteUrl , @UserId, @AssociationId, @UserType, @DisplayName, @AboutMe, @Location, @WebsiteUrl, @EmailHash , @CreationDate,
@LastAccessDate, @TimedPenaltyDate, @AcceptRate, @Age, @AnswerCount, @DownVoteCount, @QuestionCount, @Reputation, @UpVoteCount,
@ViewCount, @BadgesGold, @BadgesSilver, @BadgesBronze, @Rank);";
#endregion
}
And thats it. Soapi.CS, making the Stack Exchange API easy peasy since 2010. ;-)
find the runnable project in the latest Soapi source code on BitBucket
Shhhh.. don't tell anyone but I just gave away the functional core of the upcoming V2 of SOREP. ;-)