Changes

{{Project|Has project output=Data,Tool,Content,How-to,Guide|Has sponsor=McNair ProjectsCenter|Has Image=Uspto web logo.jpg

|Has owner=Oliver Chang,

A <onlyinclude>The [[Reproducible Patent Data]] project is a continuation of the [[Redesigning Patent Database]] that project. It aims to write faster, more centralized code to deal with data from the United States Patent and Trademark Office (USPTO). By having an end-to-end pipeline we can easily reproduce or update data without worrying about unintentional side effects or missing data. Currently, it succeeds in bulk downloading from See also the USPTO; streaming file splitting, that is, splitting large concatenated files into their component parts in-memory; and parsing of XML to Java objects, APS to Java Maps, and maintenance fee data to Java objects[[Patent Data]] umbrella project.</onlyinclude>

== Progress Quickstart ==

To get up and running with the code, do the following: # Clone the git project (link at end of page) to your user directory# Launch IntelliJ with >= Java 8 and Maven configured (default version installed on the RDP is setup to do this)# Open project in IntelliJ# Create an empty database (see [[#Database]])# Run the table creation scripts in <delcode>Downloadersrc/db/schemas/</delcode> ''done''in your new database# Modify the constant <code>DATABASE_NAME</code> in <delcode>SplitterE:\McNair\Projects\SimplerPatentData\src\main\java\org\bakerinstitute\mcnair\postgres\DatabaseHelper.java</delcode> ''done''# Run the Driver scripts in IntelliJ with the correct value for <code>DATA_DIRECTORY</code> (or run <delcode>ParserRunInitialImport.java</delcode> ''done''# Create tooling which will do all of the data directories for minionsthat patent item type)# Setup PostgreSQL JDBC# Create naive schema based [Take a really, really long lunch...in total should take no more than five hours to load data on previous approachesRDP]# Create new data structures# Database Insert (modify Run scripts in <code>modelssrc/db/constraints</code> files with some mapping to database fields)check data assumptions# Data Cleanup That's it! ===Troubleshooting=== If you're new to IntelliJ (reference [[Patent_Assignment_Data_Restructure|Marcela and Soniaeven if you's work]]re not)you might run into problems with importing the project.  '''Setting Up Project as a Maven project'''It should be clear if the project is not set up as a Maven project - when you right click on RunInitialImport.java, for example, you won't see an option with a green triangle next to it that says "Run 'RunInitialImport.java'", and the green triangle in the top toolbar will be grayed out. If the project is not set up as a Maven project, you will not be able to run any of the code. To set up the project as a Maven project, when you import the project, follow the instructions at the following [https://www.jetbrains.com/help/idea/maven.html# Setup pipeline script maven_import_project_start link]. * Note that when you click "Import Project", you should select the "Simpler Patent Data" folder, not the "src" folder within Simpler Patent Data, otherwise you won't get the pom.xml file that you need to complete all of these steps in serieslet IntelliJ know that this is a Maven project.# Investigate parallel speedup * On the second window (ethere will be several options with check boxes next to them) make sure "import Maven projects automatically" is selected* On the "Please select project SDK" window, make sure it says "1.g8" in the "Name" slot. multithread* On the next window, mmap)enter a name for the project and enter a folder location. This should ensure that the project is set up as a Maven project.  # '''Setting Up Your Data Source Merger (''only USPTO granted' If you run into a message across the top that says something along the lines of "Configure Data Source", then you have not connected IntelliJ to a database. You will not be able to run the code located under src/db until you configure one. Start by clicking on the link in the message, or if it doesn't appear, follow the instructions [https://www.jetbrains.com/help/idea/connecting-to-a-database.html here] to open up the "Data Sources and Drivers" pop-up to add a PostgreSQL database. When you get to the dialogue asking about the host, maintfeedatabase, assignmentuser, and password, do the following to connect to the database on the RDP:  host: localhost database: whatever the constant DATABASE_NAME is set to - the default is patentsj user: postgres password: tabspaceenter Make sure to test the connection by clicking "Test Connection". Now you should be able to run the scripts under src/db. If you're seeing issues such as "column [something] of relation [something] doesn't exist" but you' not USPTO applications or Harvard Dataverse or Lex Machina currently)ve run the schema scripts, you probably have different database name under the data source than the one the constant DATABASE_NAME is set to. To change this, right click on the data source in the Database tab and select "Properties".

<code>E:\McNair\Projects\SimplerPatentData\data\downloads\maintenance\</code>

TODOThe code has the same parent directory as the data, so it is at <code>E:\McNair\Projects\SimplerPatentData\src</code>. You might notice a lot of single-entry directories; this is an idiomatic Java pattern that is used for package separation. If using IntelliJ or some other IDE, these directories are a bit less annoying. The development environment is Java 8 JDK, IntelliJ Ultimate IDE, Maven build tools, and git VCS. The git repository can be found at http://rdp.mcnaircenter.org/codebase/Repository/ReproduciblePatent

==== Design Prior Art ====

TODOThis tool is not so concerned with adding new functionality; rather, it aims to take a bunch of spread out Perl scripts and create a faster system that is easier to work with. As such, its functionality is largely stolen from those scripts: * Downloader: <code>E:\McNair\Software\Scripts\Patent\USPTO_Parser.pl</code>* XML Splitter: <code>E:\McNair\PatentData\splitter.pl</code>* XML Parsing: <code>E:\McNair\PatentData\Processed\xmlparser_4.5_4.4_4.3.pl</code> and <code>E:\McNair\PatentData\Processed\*.pm</code> In addition, I used several non-standard Java libraries listed below: * [https://github.com/mashape/unirest-java/ Unirest] for easy HTTP requests (MIT License)* [https://github.com/google/guava Google Guava] for immutable collections and Stream utilities (Apache v2.0 License)* [https://github.com/jhy/jsoup/ jsoup] for HTML parsing (MIT License)* [http://commons.apache.org/proper/commons-codec/ Apache Commons Codec] (Apache v2.0 License)* [http://commons.apache.org/proper/commons-lang/ Apache Commons Lang v3] (Apache v2.0 License)* [https://mvnrepository.com/artifact/org.jetbrains/annotations/15.0 Jetbrains Annotations] for enhanced null checks (Apache v2.0 License)* [http://search.maven.org/#artifactdetails%7Corg.postgresql%7Cpostgresql%7C42.1.1.jre7%7Cbundle PostgreSQL JDBC] (BSD 3-clause per https://github.com/pgjdbc/pgjdbc-jre7/blob/master/LICENSE) If using maven, these dependencies are listed and should automatically be setup.

TODOAny file with a line that says <code>public static void main(String[] args) {</code> can be run as a standalone file. The easiest way to do this is to load the project and then the file in IntelliJ and click the little green play arrow next to this bit of code. The code can also be run via the standard <code>javac</code> and <code>java</code> commands but since this project has a complicated structure you end up having to run commands like  <code>"C:\Program Files\Java\jdk1.8.0_131\bin\java" "-javaagent:C:\Users\OliverC\IntelliJ IDEA 2017.1.3\lib\idea_rt.jar=62364:C:\Users\OliverC\IntelliJ IDEA 2017.1.3\bin" -Dfile.encoding=UTF-8 -classpath "[...contents truncated...];C:\Users\OliverC\.m2\repository\org\postgresql\postgresql\42.1.1\postgresql-42.1.1.jar" org.bakerinstitute.mcnair.uspto_assignments.XmlDriver</code> to include all of the runtime dependencies and it's just not worth it.

TODO* Use the IntelliJ command Reformat code (found in the menus at <code>Code > Reformat Code</code>* Use the optimize imports function found under the same menu* Use spaces for indentation* Loosely try to keep lines below 120 characters* Commit changes to the Git remote repository "bonobo" == Schema Reconciliation ==

== Schema Reconciliation =Patents (Granted) === See <code>E:\McNair\Projects\SimplerPatentData\data\examples\granted</code> for extracted examples of what specific data is available for a sample of the data.

|+Granted Patent Data Formats

! scope="col" | Dates Used !! scope="col" | Format !! scope="col" | Location !! scope="col" | Supported by Parser? !! scope="col" | Utility !! scope="col" | Reissue !! scope="col" | Design !! scope="col" | Plant

|Only syntax<code>data/extracts/granted/vintage</code>|style="background: green; color: white;" | Yes|✓|~|~|~

|Only syntax<code>data/extracts/granted/blunderyears</code>|style="background: green; color: white;" | Yes|✓|~|~|~

|Maybe<code>data/extracts/granted/modern</code>|style="background: green; color: white;" | Yes|✓|~|~|~

|Maybe<code>data/extracts/granted/modern</code>|style="background: green; color: white;" | Yes|✓|~|~|~

|Maybe<code>data/extracts/granted/modern</code>|style="background: green; color: white;" | Yes|✓|~|~|~

It'''Note''': these values are likely s worth mentioning that the APS contains an advanced text markup system for chemical formulae, basic text markup, tables, etc. that can lead to change without warning. For the latest version of these see the actual files at <code>E:\McNair\Projects\SimplerPatentData\src\main\java\org\bakerinstitute\mcnair\models</code>seemingly garbled text that is perfectly well-formed.

=== Assignee = APS Gotchas ====

text fields* PATN.WKU is the granted patent number. It is 7 digits while the spec promises 6 digits. The rightmost digit is a check digit modulus 11. See [[File: <code>NAME, ADDR1, ADDR2, CITY, STATE, COUNTRY_NAME, POSTCODE</code>Aps-wku-modulus11.pdf]] for the words from the horse's mouth.

=== Assignment Patents (Applications) ===

text fields: {| class="wikitable"|+Patent Application Data Formats|-! scope="col" | Dates Used !! scope="col" | Format !! scope="col" | Location !! scope="col" | Supported by Parser?|-|March 15, 2001 to December 2001|XML Version 1.5|<code>REEL_NUMBER, FRAME_NUMBER, LAST_UPDATE_DATE, RECORDED_DATE, CONVEYANCE_TEXTdata/extracts/applications/vintage</code>|style="background: yellow;" | Yes, for basic information, inventors, and correspondentslists: |-|January 2002 to December 2004|XML Version 1.6|<code>correspondents, assignors, assigneesdata/extracts/applications/vintage</code>|style="background: yellow;" | Ditto=== Assignment Summary ===|-|January 2005 to December 2005|XML Version 4.0 ICEtext fields: |<code>LAST_NAME, FIRST_NAME, ORG_NAME, CITY, COUNTRY, STATE, ADDRESS, POSTCODEdata/extracts/applications/modern</code>|style="background: yellow;" | Ditto|-=== Assignor ===|January 2006 to December 2006|XML Version 4.1 ICEtext fields: |<code>NAME, EXECUTION_DATE, DATE_ACKNOWLEDGEDdata/extracts/applications/modern</code>|style="background: yellow;" | Ditto=== Citation ===|-|January 2007 to December 2012|XML Version 4.2 ICEtext fields: |<code>CITED_PATENT_NUMBER, CITED_PATENT_COUNTRY, CITED_PATENT_KIND, CITED_PATENT_CATEGORYdata/extracts/applications/modern</code>|style="background: yellow;" | Ditto=== Correspondent ===|-|January 2013 to December 2014|XML Version 4.3 ICEtext fields: |<code>NAME, ADDR1, ADDR2, ADDR3, ADDR4data/extracts/applications/modern</code>|style="background: yellow;" | Ditto=== GrantedPatent ===|-|January 2015 to ''Present''|XML Version 4.4 ICEtext fields: |<code> PATENT_TYPE, TITLE, PCT_DOCUMENT_NUMBER, PATENT_COUNTRY, PATENT_NUMBER,PATENT_KIND, PATENT_GRANT_DATE, APPLICATION_NUMBER, APPLICATION_FILING_DATE, PRIORITY_CLAIMS_DATE, PRIORITY_CLAIMS_COUNTRY, PRIORITY_CLAIMS_PATENT_NUMBER, CLASSIFICATION_NATIONAL_COUNTRY, CLASSIFICATION_NATIONAL_CLASS, PRIMARY_EXAMINER_FIRST_NAME, PRIMARY_EXAMINER_LAST_NAME, PRIMARY_EXAMINER_DEPARTMENTdata/extracts/applications/modern</code>|style="background: yellow;" | Ditto|}

list fields: <code>citationsBecause there isn't a compelling reason not to, scirefsI used the existing PostgreSQL infrastructure on the RDP.The "Java Way" of interacting with databases is the Java Database Connectivity API (JDBC), inventorsan implementation-agnostic API for interacting with databases.This project uses the stock [https://jdbc.postgresql.org/ Postgres JDBC], assignmentsummaries, lawyers</code>version 42.1.1

=== Inventor Create an empty database on RDP ===

text fieldsTo create an empty database, run this command: <code>SEQUENCE, LAST_NAME, FIRST_NAME, ORG_NAME, CITY, COUNTRY, STATE, ADDRESS, POSTCODE$ createdb --username=postgres database-name-goes-here # password is tabspaceenter</code>

=== Lawyer Abstraction Layer ===

text fields: Since writing raw SQL is a bit cumbersome and error-prone, I have added some abstraction layers that make it much easier to quickly add bulk data. By using Postgres's <code>CopyManager</code>SEQUENCEclass, LAST_NAMEwe buffer SQL copy commands in memory (as many as possible) and then flush these rows. To understand how the abstraction layers work, FIRST_NAME, ORG_NAME, CITY, COUNTRY, STATE, ADDRESS, POSTCODEsee the code in <code>E:\McNair\Projects\SimplerPatentData\src\main\java\org\bakerinstitute\mcnair\postgres</code>. See <code>E:\McNair\Projects\SimplerPatentData\src\main\java\org\bakerinstitute\mcnair\models\GrantedPatent.java</code>for '''an example of how to extend''' the abstraction layer to deal with more complex scenarios.

=== MaintenanceFeeEvent New Table Checklist ===

text fields* Create schema DDL SQL code for the new table in <code>E: \McNair\Projects\SimplerPatentData\src\db</code>* Run the schema creation* Create an enum with the same names for attributes as in the DDL (case-insensitive! prefer all-caps screaming snake case)* Create a class which subclasses <code>AbstractInsertableData</code>US_PATENT_NUMBER* Inside that class, US_APPLICATION_NUMBER, IS_SMALL_ENTITY, US_APPLICATION_FILING_DATE, US_GRANT_ISSUE_DATE, EVENT_ENTRY_DATE, EVENT_CODEcreate a static class which subclasses <code>AbstractTableMetadata</code>and has the proper values for getTableName(), getStringColumns(), getIntColumns()* (Optional) Implement builder pattern* (Optional) Create a custom databasehelper for complex extras (see PatentApplication and GrantedPatent for examples)* Write the data to the table (see DatabaseHelper for the pattern I use)

=== Sciref =Address Data ==

text fields: To get the most granular address data (street level, or at least postcode level) about who owns patents, the path is not so straightforward because off the complicated mapping of ownership to a granted patent.This is the final part of this project that I am working on and it is all at the level of SQL.See <code>CITATION_DESCRIPTIONE:\McNair\Projects\SimplerPatentData\src\db\joins</code>for my attempts to create a clean mapping.Optimistically speaking, the data generated here should be superset of the data present in the Patent Assignment Data Restructure project.

[[File:Oyc-Use <code>assignments_longform.last_update_date</code> to find current/latest (or first/earliest) date of assignment. Then match with <code>properties.docid</code> on <code>reelno, frameno</code> to find patent application id. With this mapping to granted patents, we can discover the details of the original granted patent-schema-1.PNG|640px]]--And with the right date and reelno and frameno, we can match to the <code>assignees</code> table and get fine granularity addresses.

== Related Projects Pages ==

* [[Redesign_Assignment_and_Patent_Database|Redesign Assignment and Patent Database, Fall 2017 by Shelby]]* [[Equivalent_XPath_and_APS_Queries|Equivalent XPath and APS Queries, Summer 2017 by Oliver & Joe]]* [[US_Address_Verification|US Address Verification, Summer 2017 based on tables from Assignment Data Restructure]]* [[Patent_Assignment_Data_Restructure|Assignment Data Restructure, Spring 2017 by Marcela and Sonia]]* [[Redesigning_Patent_Database|Redesigning Patent Database, Spring 2017 by Shelby]]* [[Patent_Data_Cleanup_(June_2016)|Patent Data Cleanup, June 2016 by Marcela]]* [[Patent_Data|Patent Data, Spring 2016 by Marcela]] * [[Lex_Machina|Lex Machina]]* [[USPTO_Patent_Litigation_Data|USPTO Patent Litigation Research Dataset by Ed]]* [[Patent_Litigation_and_Review|Patent Litigation and Review by Marcela]]* [[Patent|Existing Database Schema]]* [[Oliver_Chang_(Work_Log)|My Work Log]]