...
With the deployment of the cumulative patch alexandria-sql-patch-alpa-3636-20191101
, on-site CLAIMS Direct installations now have the ability to utilize family and citation functionality in-house. What was previously only available using the remote CLAIMS Direct shared API is now possible internally with simple SQL functions. The following post will outline the steps required to prepare the data tables as well as presenting a brief walk-through of the functionality.
Preparation
Sequential Loading
...
Note |
---|
Note: If your initial load occurred after May 13, 2020, these tables were included in the initial load and you do not need to follow the preparation instructions here. However, to maintain the supplemental tables required for on-site family and citation functionality, you must add |
Sequential Loading
In order to leverage the new functionality, the necessary lookup tables need to be populated on-site. The functions available to achieve this are:
cdws.f_update_cited_documents
– serving forward and backward citationscdws.f_update_priority_documents
– serving simple and extended family
...
Code Block | ||||
---|---|---|---|---|
| ||||
SELECT cdws.f_update_cited_documents_bf(); SELECT cdws.f_update_priority_documents_bf(); |
Each function requires approximately at least 24 hours to complete and may take up to 7 days, depending on the environment. They can be run in parallel if desired. Because of the duration required to complete the functions, it's important that the SQL runs uninterrupted by server restarts or terminal disconnects.
...
Note | ||
---|---|---|
| ||
In order to automatically populate these auxiliary tables every new load-id, you must use the |
Usage
Once the auxiliary table loading is complete, a wide variety of citation and family functionality is available directly from the on-site CLAIMS Direct instance.
Citations
Bringing the CLAIMS Direct API methods citations/forward
and citations/backward
on-site is achieved using the new functions:
- FUNCTION
cdws.f_citations_backward
- FUNCTION
cdws.f_citations_forward
Each function takes as input a publication_id
and returns 2 columns: ucid
and source.
Code Block | ||
---|---|---|
sql | sql | SELECT * FROM cdws.f_citations_backward( xml.f_ucid2id('US-5551212-A') );
ucid | source
---------------+--------
US-4905451-A | SEA
US-2043419-A | SEA
US-4631900-A | SEA
DE-8002686-U1 | SEA
EP-0225665-A1 | SEA
US-3187480 . In versions 2.5.3 and above, this parameter is set in |
To confirm that the citation and family functionality have completed loading, locate the log files (citation.log
and family.log
). The last line of each log should show an entry in the following format:
Code Block | ||
---|---|---|
| ||
INFO: added N new citations records from the work table (time)
INFO: added N new priority records from the work table (time) |
To check whether the calculations are still running, use one of the two SQL queries below while in the database or using pgAdmin.
Code Block | ||
---|---|---|
| ||
SELECT datname, query FROM pg_stat_activity;
SELECT * FROM pg_stat_activity; |
Usage
Once the auxiliary table loading is complete, a wide variety of citation and family functionality is available directly from the on-site CLAIMS Direct instance.
Citations
Bringing the CLAIMS Direct API methods citations/forward
and citations/backward
on-site is achieved using the new functions:
- FUNCTION
cdws.f_citations_backward
- FUNCTION
cdws.f_citations_forward
Each function takes as input a publication_id
and returns 2 columns: ucid
and source.
Code Block | ||||
---|---|---|---|---|
| ||||
SELECT * FROM cdws.f_citations_backward( xml.f_ucid2id('US-5551212-A') ); ucid | source ---------------+-------- US-4905451-A | SEA US-48281102043419-A | SEA US-4631900-A | SEA DE-8002686-U1 | SEA EP-0225665-A1 | SEA US-3187480-A | SEA US-4828110-A | SEA DE-3138439-A1 | SEA EP-0061805-A1 | SEA DE-3606826-A1 | SEA US-4546875-A | SEA US-4534151-A | SEA EP-0313721-A2 | SEA |
...
Code Block | ||||
---|---|---|---|---|
| ||||
SELECT * FROM cdws.f_family_simple( xml.f_ucid2id('US-5000000-A') ); ucid | family_id ------------------+----------- AR-242634-A1 | 26932266 CA-1335430-C | 26932266 EP-0431047-A1 | 26932266 HU-T60328-A | 26932266 JP-H05502366-A | 26932266 KR-900702042-A | 26932266 WO-1990002193-A1 | 26932266 SELECT * FROM cdws.f_family_extended( xml.f_ucid2id('US-5551212-A') ); publication_id | ucid | level ----------------+----------------+------- 71329085 | US-5551212-A | 0 302369 | AT-96388-T | 1 1220699 | AU-636238-B2 | 1 2293384 | AU-8174591-A | 1 5137694 | CA-2050271-A1 | 1 5137693 | CA-2050271-C | 1 14883151 | DE-4027762-C1 | 1 17156127 | DE-59100535-D1 | 1 93003830 | DK-0477487-T3 | 1 21052692 | EP-0477487-A1 | 1 21052691 | EP-0477487-B1 | 1 25818932 | ES-2046827-T3 | 1 128970015 | JP-H054613-A | 1 131336800 | JP-H0825524-B2 | 1 59117993 | NO-913030-D0 | 1 177975213 | NO-913030-L | 1 62462393 | TR-25401-A | 1 159577685 | ZA-9106183-B | 1 1 62462393 | TR-25401-A | 1 159577685 | ZA-9106183-B | 1 |
A second optional argument in the cdws.f_family_extended
function, v_include_apps Boolean
, can be used to include application filing data from the patent office. The default setting of this argument is false
in order to maintain consistency with the EPO. In some cases, however, application filing data is not included in the priority claims data. Setting this argument to true
will include the application filing data, as in the following example.
Code Block | ||
---|---|---|
| ||
SELECT ucid
FROM cdws.f_family_extended(xml.f_ucid2id('AU-2012201497-B2'), true ) as f
WHERE f.ucid = 'WO-2008136115-A1';
ucid
------------------
WO-2008136115-A1
(1 row) |
Additional Functions
In addition to the above, there are other functions in the supplemental patch worth exploring, namely:
...
The cdws.f_family_meta
function brings a variety of information together for a simple family. The following columns are returned:
- family-id - DocDBDOCDB-assigned simple family integer identifier
- ucid – unique character identifier (publication)
- published – publication date
- anucid – unique character identifier (application)
- filed – application filing date
- title – title of document
- ifi_status – IFI integrated content Snapshot document status
- ifi_anticipated_expiration - IFI anticipated expiration date
- ifi_adjusted_expiration - IFI adjusted expiration date
- epo_legal_status_xml – XML representation of EPO legal status
...