Bulk Data Import
This is a simple example of using Soapi.CS for bulk data importation.
This sample creates/updates a SQLite database with the the complete profile of all Stack Exchange users with reputation >= 100 and assigns a rank according to order of appearance.
Soapi processes the ~1000 requests required in less than 5 minutes.
You may run this code against the same database and the records are updated in place.
This code requires references to System.Data.SQLite and Soapi.dll.
Code very similar to this is the core of the SO-REP service.
Usage:
new SoapiUserIndexer()
.Index("data source=users.db;new=true", apikey);
Code:
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using Soapi.Domain;
namespace Soapi.Data
{
public class SoapiUserIndexer
{
/// <summary>
///
/// Provides you with a local database of all stack exchange users with
/// rep >=100, 90,000 records, in less than 5 minutes using Soapi.CS
///
/// It is not recommended to decrease the minimum rep value as the numbers
/// increase drastically with questionable benefit.
///
/// This is the core of the SO-REP Service, which ranks provides
/// ranking data.
///
/// Requires System.Data.SQLite
/// http://sqlite.phxsoftware.com/
///
/// </summary>
/// <param name="connectionString"></param>
/// <param name="apiKey"></param>
public void Index(string connectionString, string apiKey)
{
var ctx = new ApiContext(apiKey).Initialize(false);
var lastModified = DateTime.Now;
const int batchSize = 50;
const int minRep = 100;
using (var connection = new SQLiteConnection(connectionString))
{
connection.Open();
var cmd = connection.CreateCommand();
cmd.CommandText = CreateUsersTable;
cmd.ExecuteNonQuery();
foreach (Site site in ctx.Sites.Where(s => s.ApiEndpoint.IndexOf("stackauth") == -1))
{
// get the count of users with Rep >= 100 and div by 100 to get a page count
var pageCount = (int)(Math.Ceiling((double)site.Users.Min(minRep).Total / 100) + 1);
var batchCount = Math.Ceiling((double)pageCount / batchSize);
for (int i = 0; i < batchCount; i++)
{
var startPage = i*batchSize + 1;
var rank = (batchCount*i) + 1;
var users = site.Users.PageCount(batchSize).Page(startPage).Min(minRep).ToList();
using (var txn = connection.BeginTransaction())
{
cmd = BuildInsertCommand();
cmd.Connection = connection;
foreach (User item in users)
{
cmd.Parameters["@ApiEndpoint"].Value = item.Site.ApiEndpoint;
cmd.Parameters["@UserId"].Value = item.UserId;
cmd.Parameters["@AssociationId"].Value = item.AssociationId;
cmd.Parameters["@UserType"].Value = item.UserType.ToString();
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;
cmd.Parameters["@BadgesGold"].Value = item.BadgeCounts.Gold;
cmd.Parameters["@BadgesSilver"].Value = item.BadgeCounts.Silver;
cmd.Parameters["@BadgesBronze"].Value = item.BadgeCounts.Bronze;
cmd.Parameters["@Rank"].Value = rank++;
cmd.Parameters["@LastModifiedDate"].Value = lastModified;
cmd.ExecuteNonQuery();
}
txn.Commit();
}
}
}
connection.Close();
}
}
private static SQLiteCommand BuildInsertCommand()
{
var cmd = new SQLiteCommand(SqlUerInsertOrUpdate)
{
CommandText = SqlUerInsertOrUpdate
};
foreach (var item in new List<object[]>
{
new object[]{"@ApiEndpoint", DbType.String,128},
new object[]{"@UserId", DbType.Int32},
new object[]{"@AssociationId", DbType.Guid},
new object[]{"@UserType", DbType.String,20},
new object[]{"@DisplayName", DbType.String,50},
new object[]{"@AboutMe", DbType.String,4000},
new object[]{"@Location", DbType.String,128},
new object[]{"@WebsiteUrl", DbType.String,256},
new object[]{"@EmailHash", DbType.String,64},
new object[]{"@CreationDate", DbType.DateTime},
new object[]{"@LastAccessDate", DbType.DateTime},
new object[]{"@TimedPenaltyDate", DbType.DateTime},
new object[]{"@AcceptRate", DbType.Int32},
new object[]{"@Age", DbType.Int32},
new object[]{"@AnswerCount", DbType.Int32},
new object[]{"@DownVoteCount", DbType.Int32},
new object[]{"@QuestionCount", DbType.Int32},
new object[]{"@Reputation", DbType.Int32},
new object[]{"@UpVoteCount", DbType.Int32},
new object[]{"@ViewCount", DbType.Int32},
new object[]{"@BadgesGold", DbType.Int32},
new object[]{"@BadgesSilver", DbType.Int32},
new object[]{"@BadgesBronze", DbType.Int32},
new object[]{"@Rank", DbType.Int32},
new object[]{"@LastModifiedDate",DbType.DateTime}
})
{
var p = new SQLiteParameter { ParameterName = (string)item[0], DbType = (DbType)item[1] };
if (p.DbType == DbType.String)
{
p.Size = (int)item[2];
}
cmd.Parameters.Add(p);
}
return cmd;
}
private const string SqlUerInsertOrUpdate = @"
INSERT OR REPLACE INTO [Users] ([ApiEndpoint],[UserId],[AssociationId],[UserType],[DisplayName],[AboutMe],[Location]
,[WebsiteUrl],[EmailHash],[CreationDate],[LastAccessDate],[TimedPenaltyDate],[AcceptRate],[Age],[AnswerCount],[DownVoteCount]
,[QuestionCount],[Reputation],[UpVoteCount],[ViewCount],[BadgesGold],[BadgesSilver],[BadgesBronze],[Rank],[LastModifiedDate])
VALUES (@ApiEndpoint,@UserId,@AssociationId,@UserType,@DisplayName,@AboutMe,@Location,@WebsiteUrl,@EmailHash,@CreationDate
,@LastAccessDate,@TimedPenaltyDate,@AcceptRate,@Age,@AnswerCount,@DownVoteCount,@QuestionCount,@Reputation,@UpVoteCount
,@ViewCount,@BadgesGold,@BadgesSilver,@BadgesBronze,@Rank,@LastModifiedDate);";
private const string CreateUsersTable = @"
CREATE TABLE IF NOT EXISTS [Users] (
[RowId] integer PRIMARY KEY AUTOINCREMENT NOT NULL,
[ApiEndpoint] varchar(128) NOT NULL,
[UserId] int NOT NULL,
[AssociationId] guid NOT NULL,
[UserType] varchar(20) NOT NULL,
[DisplayName] nvarchar(50) NOT NULL,
[AboutMe] nvarchar(4000) NOT NULL,
[Location] nvarchar(128) NOT NULL,
[WebsiteUrl] nvarchar(256) NOT NULL,
[EmailHash] varchar(64) NOT NULL,
[CreationDate] datetime NOT NULL,
[LastAccessDate] datetime NOT NULL,
[TimedPenaltyDate] datetime NOT NULL,
[AcceptRate] int NOT NULL,
[Age] int NOT NULL,
[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 NOT NULL,
[BadgesSilver] int NOT NULL,
[BadgesBronze] int NOT NULL,
[Rank] int NOT NULL,
[LastModifiedDate] datetime NOT NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS [IX_ApiEndpoint_UserId] ON [Users] ([ApiEndpoint], [UserId]);";
}
}
