patstat2014a.sql

-- Copyright (2014) Matteo Chinazzi
-- This script is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike Unported License 3.0 

-- Code based on https://dl.dropboxusercontent.com/u/3004945/rawpatentdata/PATSTAT201310mysql.zip 
-- by Gianluca Tarasconi - http://rawpatentdata.blogspot.com for more info



----- Create patstat2014a database on MySQL -----

CREATE DATABASE patstat2014a;


----- Create tables -----

USE patstat2014a;

-- Table TLS201_APPLN
DROP TABLE IF EXISTS TLS201_APPLN;
CREATE TABLE TLS201_APPLN (
APPLN_ID int(10) unsigned NOT NULL,
APPLN_AUTH char(2) NOT NULL default ' ',
APPLN_NR varchar(20) NOT NULL default ' ',
APPLN_KIND char(2) default ' ',
APPLN_FILING_DATE date default NULL,
APPLN_NR_EPODOC varchar(15) NOT NULL default ' ',
IPR_TYPE char(2) default ' ',
APPLN_TITLE_LG char(2) default ' ',
APPLN_ABSTRACT_LG char(2) default ' ',
INTERNAT_APPLN_ID int(11) default NULL,
PRIMARY KEY  (APPLN_ID)
) ENGINE=MyISAM 
CHARACTER SET utf8 COLLATE utf8_general_ci;

-- Table TLS202_APPLN_TITLE
DROP TABLE IF EXISTS TLS202_APPLN_TITLE;
CREATE TABLE TLS202_APPLN_TITLE (
  APPLN_ID int(10) unsigned NOT NULL,
  APPLN_TITLE       VARCHAR(3000)		NOT NULL,
 PRIMARY KEY (APPLN_ID)
)
ENGINE = MyISAM
CHARACTER SET utf8 COLLATE utf8_general_ci;

-- Table TLS203_APPLN_ABSTR
DROP TABLE IF EXISTS TLS203_APPLN_ABSTR;
CREATE TABLE TLS203_APPLN_ABSTR(
  APPLN_ID int(10) unsigned NOT NULL,
  APPLN_ABSTRACT       VARCHAR(10000)		NOT NULL
)
MAX_ROWS = 60000000
AVG_ROW_LENGTH=900
ENGINE = MyISAM
CHARACTER SET utf8 COLLATE utf8_general_ci;

-- Table TLS204_APPLN_PRIOR
DROP TABLE IF EXISTS TLS204_APPLN_PRIOR;
CREATE TABLE TLS204_APPLN_PRIOR (
  APPLN_ID int(10) unsigned NOT NULL,
  PRIOR_APPLN_ID int(10) unsigned NOT NULL,
  PRIOR_APPLN_SEQ_NR smallint(4) unsigned default NULL
)
ENGINE = MyISAM
CHARACTER SET utf8 COLLATE utf8_general_ci;

-- Table TLS205_TECH_REL
DROP TABLE IF EXISTS TLS205_TECH_REL;
CREATE TABLE TLS205_TECH_REL (
  APPLN_ID  int(10) unsigned NOT NULL,
  TECH_REL_APPLN_ID int(10) unsigned NOT NULL
)
ENGINE = MyISAM
CHARACTER SET utf8 COLLATE utf8_general_ci;

-- Table TLS206_PERSON
DROP TABLE IF EXISTS TLS206_PERSON;
CREATE TABLE TLS206_PERSON (
  PERSON_ID int(10) unsigned NOT NULL,
  PERSON_CTRY_CODE     CHAR(2) NOT NULL,
  DOC_STD_NAME_ID   int(10) unsigned NOT NULL,
  PERSON_NAME          VARCHAR(300),
  PERSON_ADDRESS       VARCHAR(500)
)
MAX_ROWS = 30000000
AVG_ROW_LENGTH=100
ENGINE = MyISAM
CHARACTER SET utf8 COLLATE utf8_general_ci;


-- Table TLS207_PERS_APPLN
DROP TABLE IF EXISTS TLS207_PERS_APPLN;
CREATE TABLE TLS207_PERS_APPLN (
  PERSON_ID int(10) unsigned NOT NULL,
  APPLN_ID  int(10) unsigned NOT NULL,
  APPLT_SEQ_NR smallint(4) unsigned default NULL,
  INVT_SEQ_NR smallint(4) unsigned default NULL
)
ENGINE = MyISAM
CHARACTER SET utf8 COLLATE utf8_general_ci;

-- Table TLS208_DOC_STD_NMS
DROP TABLE IF EXISTS TLS208_DOC_STD_NMS;
CREATE TABLE TLS208_DOC_STD_NMS (
  DOC_STD_NAME_ID int(10) unsigned NOT NULL,
  DOC_STD_NAME         VARCHAR(100) NOT NULL
)
ENGINE = MyISAM
CHARACTER SET utf8 COLLATE utf8_general_ci;

-- Table TLS209_APPLN_IPC
DROP TABLE IF EXISTS TLS209_APPLN_IPC;
CREATE TABLE TLS209_APPLN_IPC (
  APPLN_ID  int(10) unsigned NOT NULL,
  IPC_CLASS_SYMBOL   VARCHAR(16) NOT NULL,
  IPC_CLASS_LEVEL    CHAR(1),
  IPC_VERSION        DATE,
  IPC_VALUE          CHAR(1),
  IPC_POSITION       CHAR(1),
  IPC_GENER_AUTH     CHAR(2)
)
ENGINE = MyISAM
CHARACTER SET utf8 COLLATE utf8_general_ci;

-- Table TLS210_APPLN_N_CLS
DROP TABLE IF EXISTS TLS210_APPLN_N_CLS;
CREATE TABLE TLS210_APPLN_N_CLS (
  APPLN_ID  int(10) unsigned NOT NULL,
  NAT_CLASS_SYMBOL   VARCHAR(16) NOT NULL
)
ENGINE = MyISAM
CHARACTER SET utf8 COLLATE utf8_general_ci;

-- Table TLS211_PAT_PUBLN
DROP TABLE IF EXISTS TLS211_PAT_PUBLN;
CREATE TABLE TLS211_PAT_PUBLN (
  PAT_PUBLN_ID int(10) unsigned NOT NULL,
  PUBLN_AUTH             CHAR(2) NOT NULL,
  PUBLN_NR               VARCHAR(15) NOT NULL,
  PUBLN_KIND             CHAR(2),
  APPLN_ID int(10) unsigned NOT NULL,
  PUBLN_DATE             DATE,
  PUBLN_LG               CHAR(2),
  PUBLN_FIRST_GRANT      SMALLINT(4),
  PUBLN_CLAIMS       VARCHAR(4) NOT NULL
)
ENGINE = MyISAM
CHARACTER SET utf8 COLLATE utf8_general_ci;

-- Table TLS212_CITATION
DROP TABLE IF EXISTS TLS212_CITATION;
CREATE TABLE TLS212_CITATION (
  PAT_PUBLN_ID int(10) unsigned NOT NULL,
  CITN_ID  smallint(4) unsigned NOT NULL,
  CITN_ORIGIN VARCHAR(5),
  CITED_PAT_PUBLN_ID int(10) unsigned default NULL,
  CITED_APPLN_ID int(10) unsigned NOT NULL,
  PAT_CITN_SEQ_NR  smallint(4) unsigned default NULL,
  NPL_PUBLN_ID  int(10) unsigned NOT NULL,
  NPL_CITN_SEQ_NR smallint(4) unsigned default NULL,
  CITN_GENER_AUTH  CHAR(2) NOT NULL
)
ENGINE = MyISAM
CHARACTER SET utf8 COLLATE utf8_general_ci;

-- Table TLS214_NPL_PUBLN
DROP TABLE IF EXISTS TLS214_NPL_PUBLN;
CREATE TABLE TLS214_NPL_PUBLN (
  NPL_PUBLN_ID int(10) unsigned NOT NULL,
  NPL_BIBLIO          VARCHAR(3000)
)
ENGINE = MyISAM
CHARACTER SET utf8 COLLATE utf8_general_ci;

-- Table TLS215_CITN_CATEG;
DROP TABLE IF EXISTS TLS215_CITN_CATEG;
CREATE TABLE TLS215_CITN_CATEG (
  PAT_PUBLN_ID  int(10) unsigned NOT NULL,
  CITN_ID smallint(4) unsigned NOT NULL,
  CITN_CATEG          CHAR(1) NOT NULL
)
ENGINE = MyISAM
CHARACTER SET utf8 COLLATE utf8_general_ci;

-- Table TLS216_APPLN_CONTN
DROP TABLE IF EXISTS TLS216_APPLN_CONTN;
CREATE TABLE TLS216_APPLN_CONTN (
  APPLN_ID  int(10) unsigned NOT NULL,
  PARENT_APPLN_ID  int(10) unsigned NOT NULL,
  CONTN_TYPE      CHAR(3)
)
ENGINE = MyISAM
CHARACTER SET utf8 COLLATE utf8_general_ci;

-- Table TLS218_DOCDB_FAM
DROP TABLE IF EXISTS TLS218_DOCDB_FAM;
CREATE TABLE TLS218_DOCDB_FAM (
  APPLN_ID  int(10) unsigned NOT NULL,
  DOCDB_FAMILY_ID   int(10) unsigned NOT NULL
)
ENGINE = MyISAM
CHARACTER SET utf8 COLLATE utf8_general_ci;

-- Table TLS219_INPADOC_FAM
DROP TABLE IF EXISTS TLS219_INPADOC_FAM;
CREATE TABLE TLS219_INPADOC_FAM (
  APPLN_ID  int(10) unsigned NOT NULL,
  INPADOC_FAMILY_ID   int(10) unsigned NOT NULL
)
ENGINE = MyISAM
CHARACTER SET utf8 COLLATE utf8_general_ci;

-- Table TLS222_APPLN_JP_CLASS
DROP TABLE IF EXISTS TLS222_APPLN_JP_CLASS;
CREATE TABLE TLS222_APPLN_JP_CLASS (
  APPLN_ID  int(10) unsigned NOT NULL,
  JP_CLASS_SCHEME VARCHAR(6) NOT NULL,  
  JP_CLASS_SYMBOL VARCHAR(50) NOT NULL
)
ENGINE = MyISAM
CHARACTER SET utf8 COLLATE utf8_general_ci;

-- Table TLS223_APPLN_DOCUS
DROP TABLE IF EXISTS TLS223_APPLN_DOCUS;
CREATE TABLE TLS223_APPLN_DOCUS (
  APPLN_ID  int(10) unsigned NOT NULL,
  docus_class_symbol VARCHAR(50) NOT NULL
)
ENGINE = MyISAM
CHARACTER SET utf8 COLLATE utf8_general_ci;

-- Table TLS224_APPLN_CPC
DROP TABLE IF EXISTS TLS224_APPLN_CPC;
CREATE TABLE TLS224_APPLN_CPC(
	appln_id int(10) unsigned NOT NULL,
	cpc_class_symbol varchar(19) NOT NULL DEFAULT ' ',
	cpc_scheme varchar(5) NOT NULL DEFAULT ' ',
	cpc_version date NOT NULL DEFAULT '9999-12-31',
	cpc_value char(1) NOT NULL DEFAULT ' ',
	cpc_position char(1) NOT NULL DEFAULT ' ',
	cpc_gener_auth char(2) NOT NULL DEFAULT ' ')
ENGINE = MyISAM
CHARACTER SET utf8 COLLATE utf8_general_ci;

-- Table TLS226_PERSON_ORIG
DROP TABLE IF EXISTS TLS226_PERSON_ORIG;
CREATE TABLE TLS226_PERSON_ORIG(
	person_orig_id int(10) unsigned NOT NULL,
	person_id int(10) unsigned NOT NULL,
	source char(5) NOT NULL DEFAULT ' ',
	source_version varchar(10) NOT NULL DEFAULT ' ',
	name_freeform varchar(500) NOT NULL DEFAULT ' ',
	last_name varchar(400) NOT NULL DEFAULT ' ',
	first_name varchar(200) NOT NULL DEFAULT ' ',
	middle_name varchar(50) NOT NULL DEFAULT ' ',
	address_freeform varchar(1000) NOT NULL DEFAULT ' ',
	street varchar(500) NOT NULL DEFAULT ' ',
	city varchar(200) NOT NULL DEFAULT ' ',
  	zip_code varchar(30) NOT NULL DEFAULT ' ',
	state char(2) NOT NULL DEFAULT ' ',
	person_ctry_code char(2) NOT NULL DEFAULT ' ',
	residence_ctry_code char(2) NOT NULL DEFAULT ' ',
	role varchar(2) NOT NULL DEFAULT ' ')
ENGINE = MyISAM
CHARACTER SET utf8 COLLATE utf8_general_ci;

-- Table TLS227_PERS_PUBLN
DROP TABLE IF EXISTS TLS227_PERS_PUBLN;
CREATE TABLE TLS227_PERS_PUBLN(
	person_id int(10) unsigned NOT NULL,
	pat_publn_id int(10) unsigned NOT NULL,
	applt_seq_nr smallint NOT NULL,
	invt_seq_nr smallint NOT NULL)
ENGINE = MyISAM
CHARACTER SET utf8 COLLATE utf8_general_ci;


----- Load data -----

-- Table TLS201_AAPLN
LOAD DATA INFILE 'patstat/raw/tls201_part01.txt' INTO TABLE TLS201_AAPLN
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls201_part02.txt' INTO TABLE TLS201_AAPLN
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls201_part03.txt' INTO TABLE TLS201_AAPLN
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls201_part04.txt' INTO TABLE TLS201_AAPLN
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;

-- Table TLS202_APPLN_TITLE
LOAD DATA INFILE 'patstat/raw/tls202_part01.txt' INTO TABLE TLS202_APPLN_TITLE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls202_part02.txt' INTO TABLE TLS202_APPLN_TITLE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls202_part03.txt' INTO TABLE TLS202_APPLN_TITLE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;

-- Table TLS203_APPLN_ABSTR
LOAD DATA INFILE 'patstat/raw/tls203_part01.txt' INTO TABLE TLS203_APPLN_ABSTR
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls203_part02.txt' INTO TABLE TLS203_APPLN_ABSTR
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls203_part03.txt' INTO TABLE TLS203_APPLN_ABSTR
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls203_part04.txt' INTO TABLE TLS203_APPLN_ABSTR
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls203_part05.txt' INTO TABLE TLS203_APPLN_ABSTR
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls203_part06.txt' INTO TABLE TLS203_APPLN_ABSTR
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls203_part07.txt' INTO TABLE TLS203_APPLN_ABSTR
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls203_part08.txt' INTO TABLE TLS203_APPLN_ABSTR
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls203_part09.txt' INTO TABLE TLS203_APPLN_ABSTR
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls203_part10.txt' INTO TABLE TLS203_APPLN_ABSTR
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls203_part11.txt' INTO TABLE TLS203_APPLN_ABSTR
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls203_part12.txt' INTO TABLE TLS203_APPLN_ABSTR
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls203_part13.txt' INTO TABLE TLS203_APPLN_ABSTR
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls203_part14.txt' INTO TABLE TLS203_APPLN_ABSTR
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls203_part15.txt' INTO TABLE TLS203_APPLN_ABSTR
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls203_part16.txt' INTO TABLE TLS203_APPLN_ABSTR
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls203_part17.txt' INTO TABLE TLS203_APPLN_ABSTR
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls203_part18.txt' INTO TABLE TLS203_APPLN_ABSTR
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls203_part19.txt' INTO TABLE TLS203_APPLN_ABSTR
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls203_part20.txt' INTO TABLE TLS203_APPLN_ABSTR
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls203_part21.txt' INTO TABLE TLS203_APPLN_ABSTR
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls203_part22.txt' INTO TABLE TLS203_APPLN_ABSTR
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls203_part23.txt' INTO TABLE TLS203_APPLN_ABSTR
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls203_part24.txt' INTO TABLE TLS203_APPLN_ABSTR
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;

-- Table TLS204_APPLN_PRIOR
LOAD DATA INFILE 'patstat/raw/tls204_part01.txt' INTO TABLE TLS204_APPLN_PRIOR
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
IGNORE 1 LINES ;

-- Table TLS205_TECH_REL
LOAD DATA INFILE 'patstat/raw/tls205_part01.txt' INTO TABLE TLS205_TECH_REL
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
IGNORE 1 LINES ;

-- Table TLS206_PERSON
LOAD DATA INFILE 'patstat/raw/tls206_part01.txt' INTO TABLE TLS206_PERSON
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls206_part02.txt' INTO TABLE TLS206_PERSON
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;

-- Table TLS207_PERS_APPLN
LOAD DATA INFILE 'patstat/raw/tls207_part01.txt' INTO TABLE TLS207_PERS_APPLN
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls207_part02.txt' INTO TABLE TLS207_PERS_APPLN
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
IGNORE 1 LINES ;

-- Table TLS208_DOC_STD_NMS
LOAD DATA INFILE 'patstat/raw/tls208_part01.txt' INTO TABLE TLS208_DOC_STD_NMS
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;

-- Table TLS209_APPLN_IPC
LOAD DATA INFILE 'patstat/raw/tls209_part01.txt' INTO TABLE TLS209_APPLN_IPC
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls209_part02.txt' INTO TABLE TLS209_APPLN_IPC
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls209_part03.txt' INTO TABLE TLS209_APPLN_IPC
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls209_part04.txt' INTO TABLE TLS209_APPLN_IPC
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls209_part05.txt' INTO TABLE TLS209_APPLN_IPC
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls209_part06.txt' INTO TABLE TLS209_APPLN_IPC
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;

-- Table TLS210_APPLN_N_CLS
LOAD DATA INFILE 'patstat/raw/tls210_part01.txt' INTO TABLE TLS210_APPLN_N_CLS
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls214_part01.txt' INTO TABLE TLS214_NPL_PUBLN
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;

-- Table TLS211_PAT_PUBLN
LOAD DATA INFILE 'patstat/raw/tls211_part01.txt' INTO TABLE TLS211_PAT_PUBLN
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls211_part02.txt' INTO TABLE TLS211_PAT_PUBLN
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls211_part03.txt' INTO TABLE TLS211_PAT_PUBLN
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;

-- Table TLS212_CITATION
LOAD DATA INFILE 'patstat/raw/tls212_part01.txt' INTO TABLE TLS212_CITATION
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls212_part02.txt' INTO TABLE TLS212_CITATION
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls212_part03.txt' INTO TABLE TLS212_CITATION
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls212_part04.txt' INTO TABLE TLS212_CITATION
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls212_part05.txt' INTO TABLE TLS212_CITATION
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls212_part06.txt' INTO TABLE TLS212_CITATION
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls212_part07.txt' INTO TABLE TLS212_CITATION
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;

-- Table TLS214_NPL_PUBLN
LOAD DATA INFILE 'patstat/raw/tls214_part02.txt' INTO TABLE TLS214_NPL_PUBLN
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;

-- Table TLS215_CITN_CATEG
LOAD DATA INFILE 'patstat/raw/tls215_part01.txt' INTO TABLE TLS215_CITN_CATEG
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;

-- Table TLS216_APPLN_CONTN
LOAD DATA INFILE 'patstat/raw/tls216_part01.txt' INTO TABLE TLS216_APPLN_CONTN
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;

-- Table TLS218_DOCDB_FAM
LOAD DATA INFILE 'patstat/raw/tls218_part01.txt' INTO TABLE TLS218_DOCDB_FAM
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;

-- Table TLS219_INPADOC_FAM
LOAD DATA INFILE 'patstat/raw/tls219_part01.txt' INTO TABLE TLS219_INPADOC_FAM
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;

-- Table TLS222_APPLN_JP_CLASS 
LOAD DATA INFILE 'patstat/raw/tls222_part01.txt' INTO TABLE TLS222_APPLN_JP_CLASS 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls222_part02.txt' INTO TABLE TLS222_APPLN_JP_CLASS 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls222_part03.txt' INTO TABLE TLS222_APPLN_JP_CLASS 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls222_part04.txt' INTO TABLE TLS222_APPLN_JP_CLASS 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls222_part05.txt' INTO TABLE TLS222_APPLN_JP_CLASS 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls222_part06.txt' INTO TABLE TLS222_APPLN_JP_CLASS 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;

-- Table TLS223_APPLN_DOCUS 
LOAD DATA INFILE 'patstat/raw/tls223_part01.txt' INTO TABLE TLS223_APPLN_DOCUS 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;

-- Table TLS224_APPLN_CPC
LOAD DATA INFILE 'patstat/raw/tls224_part01.txt' INTO TABLE TLS224_APPLN_CPC
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls224_part02.txt' INTO TABLE TLS224_APPLN_CPC
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls224_part03.txt' INTO TABLE TLS224_APPLN_CPC
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls224_part04.txt' INTO TABLE TLS224_APPLN_CPC
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls224_part05.txt' INTO TABLE TLS224_APPLN_CPC
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;

-- Table TLS226_PERSON_ORIG
LOAD DATA INFILE 'patstat/raw/tls226_part01.txt' INTO TABLE TLS226_PERSON_ORIG
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls226_part02.txt' INTO TABLE TLS226_PERSON_ORIG
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls226_part03.txt' INTO TABLE TLS226_PERSON_ORIG
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;

-- Table TLS227_PERS_PUBLN 
LOAD DATA INFILE 'patstat/raw/tls227_part01.txt' INTO TABLE TLS227_PERS_PUBLN 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls227_part02.txt' INTO TABLE TLS227_PERS_PUBLN  
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;
LOAD DATA INFILE 'patstat/raw/tls227_part03.txt' INTO TABLE TLS227_PERS_PUBLN 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
lines terminated by '\r\n'
IGNORE 1 LINES ;


----- Create indexes -----

CREATE INDEX IDX201_APPLN ON TLS201_APPLN(APPLN_ID);

CREATE INDEX IDX202_APPLN ON TLS202_APPLN_TITLE (APPLN_ID);

CREATE INDEX IDX203_APPLN ON TLS203_APPLN_ABSTR(APPLN_ID);

CREATE INDEX IDX204_APPLN ON TLS204_APPLN_PRIOR(APPLN_ID);

CREATE INDEX IDX204_PRIOR_APPLN ON TLS204_APPLN_PRIOR(PRIOR_APPLN_ID);

CREATE INDEX IDX205_APPLN ON TLS205_TECH_REL(APPLN_ID);

CREATE INDEX IDX206_PERSON ON TLS206_PERSON(PERSON_ID);

CREATE INDEX IDX207_PERSON ON TLS207_PERS_APPLN(PERSON_ID);

CREATE INDEX IDX207_APPLN ON TLS207_PERS_APPLN(APPLN_ID);

CREATE INDEX IDX208_NAME_ID ON TLS208_DOC_STD_NMS(DOC_STD_NAME_ID);

CREATE INDEX IDX209_APPLN ON TLS209_APPLN_IPC(APPLN_ID);

CREATE INDEX IDX209_APPLN_IPC4 ON TLS209_APPLN_IPC(IPC_CLASS_SYMBOL(4));

CREATE INDEX IDX210_APPLN ON TLS210_APPLN_N_CLS(APPLN_ID);

CREATE INDEX IDX211_PAT_PUBNL ON TLS211_PAT_PUBLN(PAT_PUBLN_ID);

CREATE INDEX IDX211_APPLN ON TLS211_PAT_PUBLN(APPLN_ID);

CREATE INDEX IDX212_PAT_PUBLN ON TLS212_CITATION(PAT_PUBLN_ID);

CREATE INDEX IDX212_NPL_PUBLN ON TLS212_CITATION(NPL_PUBLN_ID);

CREATE INDEX IDX214_NPL_PUBLN ON TLS214_NPL_PUBLN(NPL_PUBLN_ID);

CREATE INDEX IDX215_PAT_PUBLN ON TLS215_CITN_CATEG(PAT_PUBLN_ID);

CREATE INDEX IDX215_CITN_ID ON TLS215_CITN_CATEG(CITN_ID);

CREATE INDEX IDX216_APPLN ON TLS216_APPLN_CONTN(APPLN_ID);

CREATE INDEX IDX218_APPLN ON TLS218_DOCDB_FAM(APPLN_ID);

CREATE INDEX IDX219_APPLN ON TLS219_INPADOC_FAM(APPLN_ID);

CREATE INDEX IDX222_APPLN ON TLS222_APPLN_JP_CLASS(APPLN_ID);

CREATE INDEX IDX223_APPLN ON TLS223_APPLN_DOCUS(APPLN_ID);

CREATE INDEX IDX224_APPLN ON TLS224_APPLN_CPC(APPLN_ID);

CREATE INDEX IDX226_PERSON ON TLS226_PERSON_ORIG(PERSON_ID);

CREATE INDEX IDX227_PERSON ON TLS227_PERS_PUBLN(PERSON_ID);

CREATE INDEX IDX227_PUBLN ON TLS227_PERS_PUBLN(PAT_PUBLN_ID);


----- Count records -----

SELECT COUNT(*) FROM TLS201_APPLN;
-- +----------+
-- | COUNT(*) |
-- +----------+
-- | 78288848 |
-- +----------+

SELECT COUNT(*) FROM TLS202_APPLN_TITLE;
-- +----------+
-- | COUNT(*) |
-- +----------+
-- | 58811236 |
-- +----------+

SELECT COUNT(*) FROM TLS203_APPLN_ABSTR;
-- +----------+
-- | COUNT(*) |
-- +----------+
-- | 35618573 |
-- +----------+

SELECT COUNT(*) FROM TLS204_APPLN_PRIOR;
-- +----------+
-- | COUNT(*) |
-- +----------+
-- | 34305456 |
-- +----------+

SELECT COUNT(*) FROM TLS205_TECH_REL;
-- +----------+
-- | COUNT(*) |
-- +----------+
-- |  2144359 |
-- +----------+

SELECT COUNT(*) FROM TLS206_PERSON;
-- +----------+
-- | COUNT(*) |
-- +----------+
-- | 46937224 |
-- +----------+

SELECT COUNT(*) FROM TLS207_PERS_APPLN;
-- +-----------+
-- | COUNT(*)  |
-- +-----------+
-- | 172342876 |
-- +-----------+

SELECT COUNT(*) FROM TLS208_DOC_STD_NMS;
-- +----------+
-- | COUNT(*) |
-- +----------+
-- | 21236160 |
-- +----------+

SELECT COUNT(*) FROM TLS209_APPLN_IPC;
-- +-----------+
-- | COUNT(*)  |
-- +-----------+
-- | 190257596 |
-- +-----------+

SELECT COUNT(*) FROM TLS210_APPLN_N_CLS;
-- +----------+
-- | COUNT(*) |
-- +----------+
-- | 22009798 |
-- +----------+

SELECT COUNT(*) FROM TLS211_PAT_PUBLN;
-- +----------+
-- | COUNT(*) |
-- +----------+
-- | 88155133 |
-- +----------+

SELECT COUNT(*) FROM TLS212_CITATION;
-- +-----------+
-- | COUNT(*)  |
-- +-----------+
-- | 152026281 |
-- +-----------+

SELECT COUNT(*) FROM TLS214_NPL_PUBLN;
-- +----------+
-- | COUNT(*) |
-- +----------+
-- | 23806537 |
-- +----------+

SELECT COUNT(*) FROM TLS215_CITN_CATEG;
-- +----------+
-- | COUNT(*) |
-- +----------+
-- | 22870807 |
-- +----------+

SELECT COUNT(*) FROM TLS216_APPLN_CONTN;
-- +----------+
-- | COUNT(*) |
-- +----------+
-- |  2368734 |
-- +----------+

SELECT COUNT(*) FROM TLS218_DOCDB_FAM;
-- +----------+
-- | COUNT(*) |
-- +----------+
-- | 69410834 |
-- +----------+

SELECT COUNT(*) FROM TLS219_INPADOC_FAM;
-- +----------+
-- | COUNT(*) |
-- +----------+
-- | 76550961 |
-- +----------+

SELECT COUNT(*) FROM TLS222_APPLN_JP_CLASS;
-- +-----------+
-- | COUNT(*)  |
-- +-----------+
-- | 297385326 |
-- +-----------+

SELECT COUNT(*) FROM TLS223_APPLN_DOCUS;
-- +----------+
-- | COUNT(*) |
-- +----------+
-- | 36669038 |
-- +----------+

SELECT COUNT(*) FROM TLS224_APPLN_CPC;
-- +-----------+
-- | COUNT(*)  |
-- +-----------+
-- | 144533124 |
-- +-----------+

SELECT COUNT(*) FROM TLS226_PERSON_ORIG;
-- +----------+
-- | COUNT(*) |
-- +----------+
-- | 50811731 |
-- +----------+

SELECT COUNT(*) FROM TLS227_PERS_PUBLN;
-- +-----------+
-- | COUNT(*)  |
-- +-----------+
-- | 206515190 |
-- +-----------+