DAP/database.sql at master · sirredbeard/DAP · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
-- we don't know how to generate root <with-no-name> (class Root) :(
create table CASE_NUMBER
(
	ID INT not null
		constraint CASE_NUMBER_PK
			primary key
		unique,
	COURT_NAME TEXT not null,
	CASE_NUMBER INT not null
);

create unique index CASE_NUMBER_COURT_uindex
	on CASE_NUMBER (COURT_NAME);

create table CREDITOR
(
	CREDITOR TEXT not null
		constraint CREDITOR_pk
			primary key
);

create table MATCHED_CASE
(
	COURT_NAME TEXT not null
		constraint MATCHED_CASE_CASE_NUMBER_COURT_NAME_fk
			references CASE_NUMBER (COURT_NAME),
	CASE_NUMBER INTEGER not null
		constraint MATCHED_CASE_pk
			primary key,
	YEAR INTEGER,
	JUDGE TEXT,
	DATE_FILED TEXT,
	TIME_FILED TEXT,
	PLAINTIFF_NAME TEXT,
	PLAINTIFF_COUNSEL TEXT,
	DEFENDANT_NAME TEXT,
	DEFENDANT_COUNSEL TEXT,
	CIVIL_ACTION TEXT,
	ACTION_DESCRIPTION TEXT,
	DEFENDANT_HOUSE TEXT,
	DEFENDANT_STREET TEXT,
	DEFENDANT_APT TEXT,
	DEFENDANT_CITY TEXT,
	DEFENDANT_STATE TEXT,
	DEFENDANT_ZIP INTEGER,
	DEFENDANT_EMAIL TEXT,
	DEFENDANT_FACEBOOK TEXT
);

create table NEW_CASE
(
	COURT_NAME     TEXT    not null
		constraint NEW_CASE_CASE_NUMBER_COURT_NAME_fk
			references CASE_NUMBER (COURT_NAME),
	CASE_NUMBER    INTEGER not null
		constraint NEW_CASE_pk
			primary key,
	YEAR INTEGER,
	JUDGE TEXT,
	DATE_FILED TEXT,
	TIME_FILED TEXT,
	PLAINTIFF_NAME TEXT,
	PLAINTIFF_COUNSEL TEXT,
	DEFENDANT_NAME TEXT,
	DEFENDANT_COUNSEL TEXT,
	CIVIL_ACTION TEXT,
	ACTION_DESCRIPTION TEXT
);

create table POSSIBLE_CASE
(
	COURT_NAME TEXT not null
		constraint POSSIBLE_CASE_CASE_NUMBER_COURT_NAME_fk
			references CASE_NUMBER (COURT_NAME),
	CASE_NUMBER INTEGER not null
		constraint POSSIBLE_CASE_pk
			primary key,
	YEAR INTEGER,
	JUDGE TEXT,
	DATE_FILED TEXT,
	TIME_FILED TEXT,
	PLAINTIFF_NAME TEXT,
	PLAINTIFF_COUNSEL TEXT,
	DEFENDANT_NAME TEXT,
	DEFENDANT_COUNSEL TEXT,
	CIVIL_ACTION TEXT,
	ACTION_DESCRIPTION TEXT
);

create table PROCESSED_CASE
(
	COURT_NAME TEXT not null
		constraint PROCESSED_CASE_CASE_NUMBER_COURT_NAME_fk
			references CASE_NUMBER (COURT_NAME),
	CASE_NUMBER INTEGER not null
		constraint PROCESSED_CASE_pk
			primary key,
	YEAR INTEGER,
	JUDGE TEXT,
	DATE_FILED TEXT,
	TIME_FILED TEXT,
	PLAINTIFF_NAME TEXT,
	PLAINTIFF_COUNSEL TEXT,
	DEFENDANT_NAME TEXT,
	DEFENDANT_COUNSEL TEXT,
	CIVIL_ACTION TEXT,
	ACTION_DESCRIPTION TEXT,
	DEFENDANT_HOUSE TEXT,
	DEFENDANT_STREET TEXT,
	DEFENDANT_APT TEXT,
	DEFENDANT_CITY TEXT,
	DEFENDANT_STATE TEXT,
	DEFENDANT_ZIP INTEGER,
	DEFENDANT_EMAIL TEXT,
	DEFENDANT_FACEBOOK TEXT,
	MAIL_TIMESTAMP TEXT,
	EMAIL_TIMESTAMP TEXT,
	FB_TIMESTAMP TEXT
);

create table REJECTED_CASE
(
	COURT_NAME TEXT not null
		constraint REJECTED_CASE_CASE_NUMBER_COURT_NAME_fk
			references CASE_NUMBER (COURT_NAME),
	CASE_NUMBER INTEGER not null
		constraint REJECTED_CASE_pk
			primary key,
	YEAR INTEGER,
	JUDGE TEXT,
	DATE_FILED TEXT,
	TIME_FILED TEXT,
	PLAINTIFF_NAME TEXT,
	PLAINTIFF_COUNSEL TEXT,
	DEFENDANT_NAME TEXT,
	DEFENDANT_COUNSEL TEXT,
	CIVIL_ACTION TEXT,
	ACTION_DESCRIPTION TEXT,
	REJECTED_REASON TEXT
);