2

I have downloaded the Stackoverflow data dump updated as of August 2012 [0]. I am particularly interested in the history of a particular post and therefore, I want to concentrate on posts.xml and posthistory.xml files. The details of the XML file according to readme.txt is as follows :

 - **posts**.xml
       - Id 
       - PostTypeId
          - 1: Question
          - 2: Answer
       - ParentID (only present if PostTypeId is 2)
       - AcceptedAnswerId (only present if PostTypeId is 1)
       - CreationDate 
       - Score 
       - ViewCount
       - Body 
       - OwnerUserId
       - LastEditorUserId 
       - LastEditorDisplayName="Jeff Atwood" 
       - LastEditDate="2009-03-05T22:28:34.823" 
       - LastActivityDate="2009-03-11T12:51:01.480" 
       - CommunityOwnedDate="2009-03-11T12:51:01.480" 
       - ClosedDate="2009-03-11T12:51:01.480" 
       - Title= 
       - Tags= 
       - AnswerCount 
       - CommentCount 
       - FavoriteCount
   - **posthistory**.xml
       - Id
       - PostHistoryTypeId 
            - 1: Initial Title - The first title a question is asked with.
            - 2: Initial Body - The first raw body text a post is submitted with.
            - 3: Initial Tags - The first tags a question is asked with.
            - 4: Edit Title - A question's title has been changed.
            - 5: Edit Body - A post's body has been changed, the raw text is stored here as markdown.
            - 6: Edit Tags - A question's tags have been changed.
            - 7: Rollback Title - A question's title has reverted to a previous version.
            - 8: Rollback Body - A post's body has reverted to a previous version - the raw text is stored here.
            - 9: Rollback Tags - A question's tags have reverted to a previous version.
            - 10: Post Closed - A post was voted to be closed.
            - 11: Post Reopened - A post was voted to be reopened.
            - 12: Post Deleted - A post was voted to be removed.
            - 13: Post Undeleted - A post was voted to be restored.
            - 14: Post Locked - A post was locked by a moderator.
            - 15: Post Unlocked - A post was unlocked by a moderator.
            - 16: Community Owned - A post has become community owned.
            - 17: Post Migrated - A post was migrated.
            - 18: Question Merged - A question has had another, deleted question merged into itself.
            - 19: Question Protected - A question was protected by a moderator
            - 20: Question Unprotected - A question was unprotected by a moderator
            - 21: Post Disassociated - An admin removes the OwnerUserId from a post.
            - 22: Question Unmerged - A previously merged question has had its answers and votes restored.  
        - PostId
        - RevisionGUID: At times more than one type of history record can be recorded by a single action.  All of these will be grouped using the same RevisionGUID
        - CreationDate: "2009-03-05T22:28:34.823" 
        - UserId
        - UserDisplayName: populated if a user has been removed and no longer referenced by user Id
        - Comment: This field will contain the comment made by the user who edited a post
        - Text: A raw version of the new value for a given revision
            - If PostHistoryTypeId = 10, 11, 12, 13, 14, or 15  this column will contain a JSON encoded string with all users who have voted for the PostHistoryTypeId
            - If PostHistoryTypeId = 17 this column will contain migration details of either "from <url>" or "to <url>"
        - CloseReasonId
            - 1: Exact Duplicate - This question covers exactly the same ground as earlier questions on this topic; its answers may be merged with another identical question.
            - 2: off-topic 
            - 3: subjective 
            - 4: not a real question 
            - 7: too localized  

Now, how can I know the PostHistoryTypeId of a particular post. I would imagine that the posts.xml file would have a PostId field but apparently it doesn't. Am I missing something here or this intentional?

[0] http://www.clearbits.net/creators/146-stack-exchange-data-dump

1

Id in Posts is what you want. Match it up against the PostId in PostHistory.

You might also find playing around with some queries live at the Data Explorer helpful. I believe the schemas are the same there and in the data dump.

  • Thanks! I just wanted to confirm this. – Dexter Mar 22 '13 at 20:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .