Return to Answer

###Bulk Data Import

Bulk Data Import

Code very similar to this is the core of the SO-REP service.

Code very similar to this is the core of the SO-REP service.

##Soapi.WP7

Porting Soapi.SL to Windows Phone 7 was as easy as adding one compiler conditional to exclude a using statement.

But getting a unit testing environment set up is turning out to be a nightmare. Everything is just a bit out of sync right now. All of the documentation and examples I have found seem to be just a bit off and I have more pressing issues to deal with.

Have to wait for the dust to settle and let the Silverlight toolkit catch up with the phone.

If anyone has a working unit testing project using the latest CTP of the WPDT, please let me know.

alt text http://www.freeimagehosting.net/uploads/e270762cf6.png

###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]);";
    }
    
}