![stackoverflow](https://i-blog.csdnimg.cn/blog_migrate/f4dacac6569037d385df48afaae950a4.png)
stackoverflow
I emailed Jeff Atwood last night a one line email. "You should make a StackOverflow API using OData." Then I realized that, as Linus says, Talk is Cheap, Show me the Code. So I created an initial prototype of a StackOverflow API using OData on an Airplane. I allocated the whole 12 hour flight. Unfortunately it took 30 minutes so I watched movies the rest of the time.
我昨晚给Jeff Atwood发了一封单行电子邮件。 “您应该使用OData制作StackOverflow API。” 然后我意识到,正如Linus所说的那样,“谈话很便宜”,请给我看代码。 因此,我在飞机上使用OData创建了StackOverflow API的初始原型。 我分配了整个12小时的飞行时间。 不幸的是,这花了30分钟,所以我其余时间都看电影。
You can follow along and do this yourself if you like.
您可以按照自己的意愿进行操作,并自行完成操作。
制备 (Preparation)
Before I left for my flight, I downloaded two things.
在出发前,我下载了两件东西。
First, I got Sam Saffron's "So Slow" StackOverflow SQL Server Importer. This is a little spike of Sam's that takes the 3gigs of XML Dump Files from StackOverflow's monthly dump and imports it into SQL Server.
首先,我得到了萨姆·萨弗隆(Sam Saffron)的 “这么慢” StackOverflow SQL Server导入器。 这是Sam的一个小峰值,它从StackOverflow的每月转储中提取3gig的XML转储文件并将其导入SQL Server。
Second, I got the StackOverflow Monthly Dump. I downloaded it with uTorrent and unzipped it in preparation for the flight.
其次,我得到了StackOverflow月度转储。 我用uTorrent下载了该文件并解压缩,以准备飞行。
导入到SQL Server (Importing into SQL Server)
I went into Visual Studio 2010 (although I could have used 2008, I like the Entity Framework improvements in 2010 enough that it made this job easier). I right clicked on the Data Connections node in the Server Explorer and created a database in SQL Express called, ahem, "StackOverflow."
我进入了Visual Studio 2010(尽管我可以使用2008,但我很喜欢2010年对Entity Framework的改进,足以使这项工作变得更加容易)。 我右键单击“服务器资源管理器”中的“数据连接”节点,并在SQL Express中创建了一个数据库,名为“ StackOverflow”。
![Create New SQL Server Database Create New SQL Server Database](https://i-blog.csdnimg.cn/blog_migrate/f120a07103fe7e415229d254fa810900.png)
Next, I opened up Sam's RecreateDB.sql file from his project in Visual Studio (I avoid using SQL Server Management Studio when I can) and connected to the ".\SQLEXPRESS" instance, selected the new StackOverflow database and hit "execute."
接下来,我从他在Visual Studio中的项目中打开Sam的RecreateDB.sql文件(尽量避免使用SQL Server Management Studio),并连接到“。\ SQLEXPRESS”实例,选择新的StackOverflow数据库,然后单击“执行”。
![Recreate DB SQL inside of Visual Studio Recreate DB SQL inside of Visual Studio](https://i-blog.csdnimg.cn/blog_migrate/df39c8696491f6bf3638c73af1cdbf26.png)
One nit about Sam's SQL file, it creates tables that line up nicely with the dump, but it includes no referential integrity. The tables don't know about each other and there's no cardinality setup. I've overwritten the brain cells in my head that know how to do that stuff without
Google
Bing so I figured I'd deal with it later. You will too.
关于SamSQL文件,它创建的表与转储很好地对齐,但是不包含参照完整性。 这些表彼此不了解,也没有基数设置。 我已经覆盖了脑部的大脑细胞,这些大脑细胞知道如何在没有
Google
Bing的情况下进行处理,所以我想以后再进行处理。 你也会。
Next, I opened Sam's SoSlow application and ran it. Lovely little app that works as advertised with a gloriously intuitive user interface. I probably would have named the "Import" button something like "Release the Hounds!" but that's just me.
接下来,我打开并运行了Sam的SoSlow应用程序。 可爱的小应用程序,具有光鲜直观的用户界面,如广告所示。 我可能会将“导入”按钮命名为“释放猎犬!”之类的名称。 就是我
At this point I have a lovely database of a few hundred megs filled with StackOverflow's public data.
此时,我有一个包含数百个兆的漂亮数据库,其中充满了StackOverflow的公共数据。
制作Web项目和实体模型 (Making a Web Project and an Entity Model)
Now, from within Visual Studio I selected File | New Project | ASP.NET Web Application. Then I right clicked on the resulting project and selected Add | New Item, then clicked Data, then ADO.NET Entity Data Model.
现在,在Visual Studio中,我选择了File | File。 新项目| ASP.NET Web应用程序。 然后,右键单击生成的项目,然后选择“添加” |“添加”。 新建项目,然后单击数据,然后单击ADO.NET实体数据模型。
![Add New Item - StackOveflow Add New Item - StackOveflow](https://i-blog.csdnimg.cn/blog_migrate/04253c231f45d0da67607bbfbb7bc506.png)
What's the deal with that, Hanselman? You know StackOverflow uses LINQ to SQL? Have you finally sold out and are trying to force Entity Framework on us sneakily within this cleverly disguised blog post?
汉瑟曼,这是怎么回事? 您知道StackOverflow使用LINQ to SQL吗? 您是否最终卖光了,并试图在这个巧妙地伪装的博客文章中偷偷地将Entity Framework强加给我们?
No. I used EF for a few reasons. One, it's fast enough (both at runtime and at design time) in Visual Studio 2010 that I don't notice the difference anymore. Two, I knew that the lack of formal referential integrity was going to be a problem (remember I mentioned that earlier?) and since LINQ to SQL is 1:1 physical/logical and EF offers flexible mapping, I figured it be easier with EF. Thirdly, "WCF Data Services" (the data services formerly known as ADO.NET Data Services or "Astoria") maps nicely to EF.
不。出于某些原因,我使用了EF。 第一,它在Visual Studio 2010中足够快(在运行时和设计时),我不再注意到它们之间的区别了。 第二,我知道缺少正式的参照完整性会成为一个问题(还记得我之前提到的吗?),由于LINQ to SQL是1:1的物理/逻辑,并且EF提供了灵活的映射,所以我认为使用EF会更容易。 第三,“ WCF数据服务”(以前称为ADO.NET数据服务或“ Astoria”的数据服务)很好地映射到EF。
I named it StackOverflowEntities.edmx and selected "Update Model from Database" and selected all the tables just to get started. When the designer opened, I noticed there were no reference lines, just tables in islands by themselves.
我将其命名为StackOverflowEntities.edmx,然后选择“从数据库更新模型”,然后选择所有表以开始使用。 当设计师打开时,我注意到没有参考线,只有岛上的表格本身。
So I was right about there being no relationships between the tables in SQL Server. If I was a smarter person, I'd have hooked up the SQL to include these relationships, but I figured I could add them here as well as a few other things that would make our OData Service more pleasant to use.
因此,我对SQL Server中的表之间没有任何关系是正确的。 如果我是一个更聪明的人,我会连接SQL来包含这些关系,但是我认为我可以在这里添加它们以及其他一些使我们的OData服务使用起来更愉快的东西。
I started by looking at Posts and thinking that if I was looking at a Post in this API, I'd want to see Comments. So, I right-clicked on a Post and click Add | Association. The dialog took me a second to understand (I'd never seen it before) be then I realized that it was creating an English sentence at the bottom, so I just focused on getting that sentence correct.
我从查看帖子开始,以为如果我在此API中查看帖子,那么我想查看评论。 因此,我右键单击一个帖子,然后单击添加|。 协会。 对话花了我一秒钟的时间来理解(我以前从未见过),然后我才意识到它在底部创建了一个英语句子,因此我只是专注于使该句子正确无误。
In this case, "Post can have * (Many) instances of Comment. Use Post.Comments to access the Comment instances. Comment can have 1 (One) instance of Post. Use Comment.Post to access the Post instance." was exactly what I wanted. I also already had the foreign keys properties, so I unchecked that and clicked OK.
在这种情况下, “ Post可以具有*(很多)Comment实例。使用Post.Comments访问Comment实例。Comment可以具有1(一个)Post实例。使用Comment.Post访问Post实例。” 正是我想要的我还已经具有外键属性,因此我取消选中它并单击“确定”。
![Add Association Add Association](https://i-blog.csdnimg.cn/blog_migrate/9d7c3134ced024be25674909614fdde4.png)
That got me here in the Designer. Note the line with the 1...* and the Comments Navigation Property on Post and the Post Navigation Property on Comment. That all came from that dialog.
那使我进入了设计师。 请注意带有1 ... *的行以及Post上的Comment导航属性和Comment上的Post导航属性。 全部来自该对话框。
Next, I figured since I didn't have it auto-generate the foreign key properties, I'd need to map them myself. I double clicked on the Association Line. I selected Post as the Principal and mapped its Id to the PostId property in Comments.
接下来,我发现由于没有自动生成外键属性的功能,因此我需要自己映射它们。 我双击了关联行。 我选择Post作为主体,并将其ID映射到Comments中的PostId属性。
![Referential Constraint Referential Constraint](https://i-blog.csdnimg.cn/blog_migrate/080f42f9396553701083355084e12b49.png)
Having figured this out, I just did the same thing a bunch more times for the obvious stuff, as seen in this diagram where Users have Badges, and Posts have Votes, etc.
弄清楚这一点后,我对明显的内容做了很多次相同的事情,如该图所示,其中用户具有徽章,帖子具有投票等。
Now, let's make a service.
现在,让我们提供服务。
创建一个OData服务 (Creating an OData Service)
Right-click on the Project in Solution Explorer and select Add | New Item | Web | WCF Data Service. I named mine Service.svc. All you technically need to do to have a full, working OData service is add a class in between the angle brackets (DataService<YourTypeHere>) and include one line for config.EntitySetAccessRule. Here's my initial minimal class. I added the SetEntitySetPageSize after I tried to get all the posts. ;)
在解决方案资源管理器中的项目上单击鼠标右键,然后选择添加|。 新商品网页| WCF数据服务。 我将其命名为Service.svc。 要获得完整的有效OData服务,技术上所需要做的就是在尖括号(DataService <YourTypeHere>)之间添加一个类,并为config.EntitySetAccessRule包含一行。 这是我最初的基础课。 在尝试获取所有帖子后,我添加了SetEntitySetPageSize。 ;)
public class Service : DataService<StackOverflowEntities>
{
// This method is called only once to initialize service-wide policies.
public static void InitializeService(DataServiceConfiguration config)
{
config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
//Set a reasonable paging site
config.SetEntitySetPageSize("*", 25);
config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;
}
}
Expanding on this class, I added caching, and an example Service Operation, as well as WCF Data Services support for JSONP. Note that the Service Operation is just an example there to show StackOverflow that they CAN have total control. Using OData doesn't mean checking a box and putting your database on the web. It means exposing specific entities with as much or as little granularity as you like. You can intercept queries, make custom behaviors (like the JSONP one), make custom Service Operations (they can include query strings, of course), and much more. OData supports JSON natively and will return JSON when an accept: header is set, but I added the JSONP support to allow cross-domain use of the service as well as allow the format parameter in the URL, which is preferred by man as it's just easier.
在此类的基础上,我添加了缓存和示例服务操作,以及对JSONP的WCF数据服务支持。 请注意,服务操作只是那里的一个示例,以显示StackOverflow他们可以完全控制。 使用OData并不意味着选中一个复选框并将您的数据库放在Web上。 这意味着要根据您的喜好公开特定的实体。 您可以拦截查询,进行自定义行为(例如JSONP),进行自定义服务操作(当然,它们可以包括查询字符串)等等。 OData本机支持JSON,并且在设置accept:标头时将返回JSON,但是我添加了JSONP支持以允许跨域使用服务以及允许URL中的format参数,这是人所偏爱的,因为它只是更轻松。
namespace StackOveflow
{
[JSONPSupportBehavior]
public class Service : DataService<StackOverflowEntities>
{
// This method is called only once to initialize service-wide policies.
public static void InitializeService(DataServiceConfiguration config)
{
config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
//This could be "*" and could also be ReadSingle, etc, etc.
config.SetServiceOperationAccessRule("GetPopularPosts", ServiceOperationRights.AllRead);
//Set a reasonable paging site
config.SetEntitySetPageSize("*", 25);
config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;
}
protected override void OnStartProcessingRequest(ProcessRequestArgs args)
{
base.OnStartProcessingRequest(args);
//Cache for a minute based on querystring
HttpContext context = HttpContext.Current;
HttpCachePolicy c = HttpContext.Current.Response.Cache;
c.SetCacheability(HttpCacheability.ServerAndPrivate);
c.SetExpires(HttpContext.Current.Timestamp.AddSeconds(60));
c.VaryByHeaders["Accept"] = true;
c.VaryByHeaders["Accept-Charset"] = true;
c.VaryByHeaders["Accept-Encoding"] = true;
c.VaryByParams["*"] = true;
}
[WebGet]
public IQueryable<Post> GetPopularPosts()
{
var popularPosts = (from p in this.CurrentDataSource.Posts
orderby p.ViewCount
select p).Take(20);
return popularPosts;
}
}
}
But what does this get us? So what?
但是,这对我们有什么帮助? 所以呢?
通过OData访问StackOverflow的数据 (Accessing StackOverflow's Data via OData)
Well, if I hit http://mysite/service.svc I see this service. Note the relative HREFs.
好吧,如果我点击http://mysite/service.svc,我会看到此服务。 注意相对的HREF。
If I hit http://173.46.159.103/service.svc/Posts I get the posts (paged, as I mentioned). Look real close in there. Notice the <link> stuff before the content? Notice the relative href="Posts(23)"?
如果我点击http://173.46.159.103/service.svc/Posts,则会得到帖子(如我提到的那样是分页的)。 看起来真的很近。 注意内容之前的<link>东西吗? 注意相对的href =“ Posts(23)”吗?
Remember all those associations I set up before? Now I can see:
还记得我以前建立的所有那些协会吗? 现在我可以看到:
Get a post: http://173.46.159.103/service.svc/Posts(23)/
Votes on a post http://173.46.159.103/service.svc/Posts(23)/Votes
Get a post and get all the comments at the same time: http://173.46.159.103/service.svc/Posts(23)?$expand=Comments
获取帖子并同时获得所有评论: http : //173.46.159.103/service.svc/Posts(23)? $ expand=Comments
I can even navigate to a user's details off a comment off a post: http://173.46.159.103/service.svc/Posts(23)/Comments(55049)/User
我什至可以从帖子的评论中导航到用户的详细信息: http : //173.46.159.103/service.svc/Posts(23)/Comments(55049)/ User
But that's just navigation. I can also do queries. Go download LINQPad Beta for .NET 4. Peep this. Click on Add Connection, and put in my little Orcsweb test server.
但这仅仅是导航。 我也可以查询。 下载.NET 4的LINQPad Beta 。 偷看单击添加连接,然后放入我的小Orcsweb测试服务器。
Disclaimer: This is a test server that Orcsweb may yank at any moment. Note also, that you can sign up for your own at http://www.vs2010host.com or find a host at ASP.NET or host your own OData in the cloud.
免责声明:这是Orcsweb随时可能会拉动的测试服务器。 另请注意,您可以在http://www.vs2010host.com上注册自己的主机,或者在ASP.NET上找到主机,或者在云中托管您自己的OData。
I put this in and hit OK.
我把它放进去,然后点击确定。
![LINQPad Connection String LINQPad Connection String](https://i-blog.csdnimg.cn/blog_migrate/1722d76e9f2b84b0cecc334de3e5c499.png)
Now I'm writing LINQ queries against StackOverflow over the web. No Twitter-style API, JSON or otherwise can do this. StackOverflow data was meant for OData. The more I mess around with this, the more I realize it's true.
现在,我正在通过Web针对StackOverflow编写LINQ查询。 没有Twitter风格的API,JSON或其他方式可以执行此操作。 StackOverflow数据用于OData。 我越不喜欢这个,我越会意识到这是真的。
This LINQ query actually turns into this URL. Again, you don't need .NET for this, it's just HTTP:
该LINQ查询实际上变成了该URL。 同样,您不需要.NET,它只是HTTP:
Try the same thing with an accept header of accept: application/json or just add $format=json
使用accept的accept头尝试相同的事情:application / json或只添加$ format = json
It'll automatically return the same data as JSON or Atom, as you like.
您会根据需要自动返回与JSON或Atom相同的数据。
If you've got Visual Studio, just go bust out a Console App real quick. File | New Console App, then right-click in references and hit Add Service Reference. Put in http://173.46.159.103/service.svc and hit OK.
如果您拥有Visual Studio,只需快速退出控制台应用程序即可。 档案| 新控制台应用程序,然后右键单击引用,然后单击添加服务引用。 放入http://173.46.159.103/service.svc,然后单击“确定”。
Try something like this. I put the URIs in comments to show you there's no trickery.
尝试这样的事情。 我将URI放在注释中,以表明您没有诡计。
class Program
{
static void Main(string[] args)
{
StackOverflowEntities so = new StackOverflowEntities(new Uri("http://173.46.159.103/service.svc"));
//{http://173.46.159.103/service.svc/Users()?$filter=substringof('Hanselman',DisplayName)}
var user = from u in so.Users
where u.DisplayName.Contains("Hanselman")
select u;
//{http://173.46.159.103/service.svc/Posts()?$filter=OwnerUserId eq 209}
var posts =
from p in so.Posts
where p.OwnerUserId == user.Single().Id
select p;
foreach (Post p in posts)
{
Console.WriteLine(p.Body);
}
Console.ReadLine();
}
}
I could keep going with examples in PHP, JavaScript, etc, but you get the point.
我可以继续使用PHP,JavaScript等示例,但是您明白了。
结论 (Conclusion)
StackOverflow has always been incredibly open and generous with their data. I propose that an OData endpint would give us much more flexible access to their data than a custom XML and/or JSON API that they'll need be constantly rev'ing.
StackOverflow一直非常开放并且慷慨地提供了他们的数据。 我建议OData endpint将给我们提供比他们不断需要的自定义XML和/或JSON API更灵活的数据访问方式。
With a proprietary API, folks will rush to create StackOverflow clients in many languages, but that work is already done with OData including libraries for iPhone, PHP and Java. There's a growing list of OData SDKs that could all be used to talk to a service like this. I could load it into Excel using PowerPivot if I like as well.
使用专有的API,人们会急于用多种语言创建StackOverflow客户端,但是OData已经完成了这项工作,包括iPhone,PHP和Java的库。 越来越多的OData SDK可以用于与这样的服务进行通信。 我也可以使用PowerPivot将其加载到Excel中。
Also, this service could totally be extended beyond this simple GET example. You can do complete CRUD with OData and it's not tied to .NET in anyway. TweetDeck for StackOverflow perhaps?
而且,此服务可以完全扩展到此简单的GET示例之外。 您可以使用OData进行完整的CRUD,并且无论如何它都不与.NET绑定。 TweetDeck是否适用于StackOverflow?
I propose we encourage StackOverflow to put more than the 30 minutes that I have put into it and make a proper OData service for their data, rather than a custom API. I volunteer to help. If not, we can do it ourselves with their dump data (perhaps weekly if they can step it up?) and a cloud instance.
我建议我们鼓励StackOverflow投入超过30分钟的时间,并为其数据提供适当的OData服务,而不是自定义API。 我自愿帮助。 如果没有,我们就可以自己处理他们的转储数据(如果可以的话,也许每周一次?)和一个云实例。
Thoughts?
有什么想法吗?
stackoverflow