Schema for lm30

CREATE TABLE "filer" (
   [srFilerId] INTEGER PRIMARY KEY,
   [srNum] INTEGER,
   [name] TEXT,
   [suffix] TEXT,
   [city] TEXT,
   [state] TEXT,
   [affAbbr] TEXT,
   [unionName] TEXT,
   [unionCity] TEXT,
   [unionState] TEXT,
   [detailId] INTEGER
);
CREATE TABLE "filing" (
   [rptId] INTEGER PRIMARY KEY,
   [srFilerId] INTEGER REFERENCES [filer]([srFilerId]),
   [amended] TEXT,
   [amendment] INTEGER,
   [beginDate] TEXT,
   [endDate] TEXT,
   [receiveDate] TEXT,
   [registerDate] TEXT,
   [formFiled] TEXT,
   [yrCovered] INTEGER,
   [unionName] TEXT,
   [unionCity] TEXT,
   [unionState] TEXT,
   [filing_url] TEXT,
   [file_path] TEXT,
   [file_checksum] TEXT,
   [file_status] TEXT
);
CREATE TABLE "report_identity" (
   [rptId] INTEGER PRIMARY KEY REFERENCES [filing]([rptId]),
   [filer_name] TEXT,
   [filer_street] TEXT,
   [filer_city] TEXT,
   [filer_state] TEXT,
   [filer_zip] TEXT,
   [filer_email] TEXT,
   [filer_role] TEXT,
   [filer_position_title] TEXT,
   [union_name] TEXT,
   [union_street] TEXT,
   [union_city] TEXT,
   [union_state] TEXT,
   [union_zip] TEXT,
   [union_file_number] TEXT
);
CREATE TABLE "represented_employer_interest" (
   [rptId] INTEGER REFERENCES [filing]([rptId]),
   [entry_order] INTEGER,
   [represented_employer] TEXT,
   [contact_name] TEXT,
   [telephone] TEXT,
   [street] TEXT,
   [city] TEXT,
   [state] TEXT,
   [zip] TEXT,
   [nature_of_interest] TEXT,
   [amount] TEXT,
   PRIMARY KEY ([rptId], [entry_order])
);
CREATE TABLE "business_interest" (
   [rptId] INTEGER REFERENCES [filing]([rptId]),
   [entry_order] INTEGER,
   [business_name] TEXT,
   [contact_name] TEXT,
   [telephone] TEXT,
   [street] TEXT,
   [city] TEXT,
   [state] TEXT,
   [zip] TEXT,
   [deals_with] TEXT,
   [deals_with_name] TEXT,
   [deals_with_contact_name] TEXT,
   [deals_with_telephone] TEXT,
   [deals_with_street] TEXT,
   [deals_with_city] TEXT,
   [deals_with_state] TEXT,
   [deals_with_zip] TEXT,
   [nature_of_dealings] TEXT,
   [value_of_dealings] TEXT,
   [nature_of_interest] TEXT,
   [amount_of_interest] TEXT,
   PRIMARY KEY ([rptId], [entry_order])
);
CREATE TABLE "other_employer_payment" (
   [rptId] INTEGER REFERENCES [filing]([rptId]),
   [entry_order] INTEGER,
   [other_employer] TEXT,
   [contact_name] TEXT,
   [telephone] TEXT,
   [street] TEXT,
   [city] TEXT,
   [state] TEXT,
   [zip] TEXT,
   [entity_type] TEXT,
   [nature_of_payment] TEXT,
   [amount] TEXT,
   PRIMARY KEY ([rptId], [entry_order])
);
CREATE TABLE "amendment" (
   [rptId] INTEGER PRIMARY KEY,
   [srFilerId] INTEGER REFERENCES [filer]([srFilerId]),
   [yrCovered] INTEGER,
   [amendment] INTEGER,
   [amended] TEXT,
   [beginDate] TEXT,
   [endDate] TEXT,
   [receiveDate] TEXT,
   [formFiled] TEXT,
   [unionName] TEXT,
   [unionCity] TEXT,
   [unionState] TEXT,
   [filing_url] TEXT
);
CREATE TABLE sqlite_stat1(tbl,idx,stat);
CREATE INDEX [idx_filing_srFilerId]
    ON [filing] ([srFilerId]);
CREATE INDEX [idx_report_identity_rptId]
    ON [report_identity] ([rptId]);
CREATE INDEX [idx_represented_employer_interest_rptId]
    ON [represented_employer_interest] ([rptId]);
CREATE INDEX [idx_business_interest_rptId]
    ON [business_interest] ([rptId]);
CREATE INDEX [idx_other_employer_payment_rptId]
    ON [other_employer_payment] ([rptId]);
CREATE INDEX [idx_amendment_srFilerId]
    ON [amendment] ([srFilerId])