**[Soapi.CS/C#][1]**
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 stackoverflow users above an arbitrary reputation threshold.
This implementation will use a [System.Data.SQLite][2] data store but with the exception of the special SQLite '[INSERT OR REPLACE INTO][3]', this code is quite easily ported to any RDBMS.
<sub>**NOTE**: The included System.Data.SQLite.dll is an x86 build, thus this app is forced X86 build. If you wish to run native x64, ensure that you have System.Data.SQLite installed and replace the reference and reset the build platform.</sub>
There are 2 modes of use for this application:
1. Performing a full update via sequential trickle pull of all users at or above minimum rep threshold. mode="PULL"
2. 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
<sub>**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.</sub>
**The code**:
---
**Program.cs**
internal class Program
{
#region Fields
private static int _pullSiteCount;
private static int _rankSiteCount;
#endregion
#region Private Methods
/// <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);
var context = new ApiContext(apiKey);
context.Initialize(false);
var connection = new SQLiteConnection(connectionString);
connection.Open();
try
{
DataAccessHelper.EnsureDatabase(connection);
}
catch (Exception ex)
{
Console.WriteLine("ERROR INITIALIZING DATABASE: {0} ", ex);
return;
}
// update users of sites that have moved, if any
try
{
DataAccessHelper.MigrateUsers(context, connection);
}
catch (Exception ex)
{
Console.WriteLine("ERROR MIGRATING : {0} ", ex);
return;
}
Pull(context, connection, minRep, pullAll);
}
/// <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(ApiContext context, SQLiteConnection connection, int minRep, bool pullAll)
{
_pullSiteCount = context.Sites.Count;
var gate = new AutoResetEvent(false);
foreach (Site outerSite in context.Sites)
{
ThreadPool.QueueUserWorkItem(state =>
{
var stateObj = (StateObj)state;
var innerSite = stateObj.Site;
// 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;
using (var conn = (SQLiteConnection)stateObj.Connection.Clone())
// need to clone on this thread
{
lastCreated = pullAll
? new DateTime(1970, 1, 1)
: DataAccessHelper.GetLatestCreationDate(conn, 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
DataAccessHelper.InsertUpdateUsers(response.Items, connection);
// 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();
}
}, new StateObj { Connection = connection, Site = outerSite });
}
gate.WaitOne();
Rank(context, connection);
}
/// <summary>
/// Updates all sites user ranking.
/// </summary>
/// <param name = "context"></param>
/// <param name = "connection"></param>
private static void Rank(ApiContext context, SQLiteConnection connection)
{
_rankSiteCount = context.Sites.Count;
var gate = new AutoResetEvent(false);
foreach (Site outerSite in context.Sites)
{
ThreadPool.QueueUserWorkItem(state =>
{
var stateObj = (StateObj)state;
Site innerSite = stateObj.Site;
// 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
{
using (var conn = (SQLiteConnection)stateObj.Connection.Clone())
// need to clone on this thread
{
DataAccessHelper.RankSite(conn, innerSite);
}
}
catch (Exception ex)
{
Console.WriteLine("{0} ERROR RANKING : {1} ", formattedSiteUrl, ex.ToString());
}
_rankSiteCount--;
if (_rankSiteCount == 0)
{
// all sites pulled, exit
gate.Set();
}
}, new StateObj { Connection = connection, Site = outerSite });
}
gate.WaitOne();
}
#endregion
/// <summary>
/// Simple object for maintaining state and scope in closures
/// </summary>
private class StateObj
{
public Site Site { get; set; }
public SQLiteConnection Connection { get; set; }
}
}
**DataAccessHelper.cs**
internal static class DataAccessHelper
{
#region Internal Methods
/// <summary>
/// Creates user table if it does not exist.
/// </summary>
/// <param name = "connection">A configured connection.
/// The connection Will be left in the same state as received.</param>
internal static void EnsureDatabase(SQLiteConnection connection)
{
bool shouldClose = connection.State != ConnectionState.Open;
if (shouldClose)
{
connection.Open();
}
var cmd = connection.CreateCommand();
cmd.CommandText = CreateUsersTable;
cmd.ExecuteNonQuery();
if (shouldClose)
{
connection.Close();
}
}
/// <summary>
/// Gets the latest creation_date for a particular site
/// </summary>
/// <param name = "connection">An open connection</param>
/// <param name="siteUrl">The site url to update if necessary</param>
/// <returns></returns>
internal static DateTime GetLatestCreationDate(SQLiteConnection connection, string siteUrl)
{
DateTime? lastCreated;
// only get those created since last full pull
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>
/// <param name = "connection">An open connection</param>
internal static void InsertUpdateUsers(List<User> users, SQLiteConnection connection)
{
lock (typeof(LockType))
{
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>
/// <param name = "context">An initialized ApiContext</param>
/// <param name = "connection">An open connection</param>
internal static void MigrateUsers(ApiContext context, SQLiteConnection connection)
{
lock (typeof(LockType))
{
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 = "connection">An open connection</param>
/// <param name = "site">The site to rank</param>
internal static void RankSite(SQLiteConnection connection, Site site)
{
lock (typeof(LockType))
{
using (var txn = connection.BeginTransaction())
{
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = "update users set rank=null where siteUrl=@siteUrl";
cmd.Parameters.AddWithValue("@siteUrl", site.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", site.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", site.SiteUrl);
ids.ForEach(id =>
{
cmd.Parameters["@userId"].Value = id;
cmd.Parameters["@rank"].Value = ++rank;
cmd.ExecuteNonQuery();
});
}
txn.Commit();
}
}
}
#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[] {"@Rank", DbType.Int32, true},
new object[] {"@BadgesBronze", 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;
}
#endregion
#region Constants
private const string CreateUsersTable =
@"
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,
[Rank] int,
[BadgesBronze] 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]);";
/// <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], [Rank], [BadgesBronze])
VALUES (@SiteUrl , @UserId, @AssociationId, @UserType, @DisplayName, @AboutMe, @Location, @WebsiteUrl, @EmailHash , @CreationDate,
@LastAccessDate, @TimedPenaltyDate, @AcceptRate, @Age, @AnswerCount, @DownVoteCount, @QuestionCount, @Reputation, @UpVoteCount,
@ViewCount, @BadgesGold, @BadgesSilver, @Rank, @BadgesBronze);";
#endregion
/// <summary>
/// A simple marker type to simplify database locking
/// </summary>
private class LockType
{
}
}
---
And thats it. [Soapi.CS][4], making the Stack Exchange API easy peasy since 2010. ;-)
find the runnable project in the [latest Soapi source code on BitBucket][5]
*Shhhh.. don't tell anyone but I just gave away the functional core of the upcoming V2 of [SOREP][6]. ;-)*
[1]: http://stackapps.com/questions/386
[2]: http://sqlite.phxsoftware.com/
[3]: http://www.sqlite.org/lang_insert.html
[4]: http://stackapps.com/questions/386
[5]: https://bitbucket.org/bitpusher/soapi.cs
[6]: http://stackapps.com/questions/543/