-
Notifications
You must be signed in to change notification settings - Fork 6
/
jptables.sql
274 lines (239 loc) · 9.75 KB
/
jptables.sql
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
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
--
-- Create SQLite3 database tables for J-Pilot plugin.
--
-- SQLite3 table names and column names are case insensitive by default.
-- SQLite3 uses type affinity and not rigid types.
--
-- Elmar Klausmeier, 17-Apr-2020
--
-- By default SQLite3 does not enforce foreign key constraints
PRAGMA foreign_keys = ON;
-- Drop all tables
drop table if exists Addr;
drop table if exists AddrLabel;
drop table if exists AddrCategory;
drop table if exists PhoneLabel;
drop table if exists Datebook;
drop table if exists ToDo;
drop table if exists ToDoCategory;
drop table if exists Memo;
drop table if exists Expense;
drop table if exists MemoCategory;
drop table if exists ExpenseCategory;
drop table if exists ExpenseType;
drop table if exists ExpensePayment;
-- Labels for address columns
create table AddrLabel (
Id int primary key,
Label text
);
-- Labels for address categories, like 'Business', 'Travel', etc.
create table AddrCategory (
Id int primary key,
Label text
);
-- Labels for address phone entries, like 'Work', 'Mobile', etc.
create table PhoneLabel (
Id int primary key,
Label text
);
-- Actual address information
create table Addr (
Id int primary key, -- unique_ID
Category int default(0),
Private int default(0), -- boolean, zero or one
showPhone int default(1), -- which of phone1...5 to show as default
Lastname text,
Firstname text,
Title text,
Company text,
PhoneLabel1 int,
PhoneLabel2 int,
PhoneLabel3 int,
PhoneLabel4 int,
PhoneLabel5 int,
Phone1 text, -- either telephone, fax, e-mail, mobile, etc.
Phone2 text, -- either telephone, fax, e-mail, mobile, etc.
Phone3 text, -- either telephone, fax, e-mail, mobile, etc.
Phone4 text, -- either telephone, fax, e-mail, mobile, etc.
Phone5 text, -- either telephone, fax, e-mail, mobile, etc.
Address text,
City text,
State text,
Zip text,
Country text,
Custom1 text,
Custom2 text,
Custom3 text,
Custom4 text,
Note text,
foreign key (Category) references AddrCategory(Id),
foreign key (PhoneLabel1) references PhoneLabel(Id),
foreign key (PhoneLabel2) references PhoneLabel(Id),
foreign key (PhoneLabel3) references PhoneLabel(Id),
foreign key (PhoneLabel4) references PhoneLabel(Id),
foreign key (PhoneLabel5) references PhoneLabel(Id)
);
create table Datebook (
Id int primary key,
Private int default(0), -- boolean, zero or one
Timeless int default(0), -- boolean, zero or one
Begin text, -- begin date in format YYYY-MM-DDTHH:MM
End text, -- end date in format YYYY-MM-DDTHH:MM
Alarm int, -- boolean, zero or one
Advance int, -- alarm in advance minutes/hours/days
AdvanceUnit int, -- 0=minutes, 1=hours, 2=days
RepeatType int, -- 0=none, 1=daily, 2=weekly, 3=monthly by day, 4=monthly by date, 5=yearly
RepeatForever int, -- boolean, zero or one
RepeatEnd text, -- end date in format YYYY-MM-DD
RepeatFreq int,
RepeatDay int,
RepeatDaySu int,
RepeatDayMo int,
RepeatDayTu int,
RepeatDayWe int,
RepeatDayTh int,
RepeatDayFr int,
RepeatDaySa int,
Description text,
Note text
);
-- Labels for ToDo categories, like 'Business', 'Personal', etc.
create table ToDoCategory (
Id int primary key,
Label text
);
create table ToDo (
Id int primary key,
Category int default(0),
Private int default(0), -- boolean, zero or one
Indefinite int default(0), -- boolean, zero or one
Due text, -- due date in format YYYY-MM-DD
Priority int default(1),
Complete int, -- boolean, zero or one
Description text,
Note text,
foreign key (Category) references ToDoCategory(Id)
);
-- Labels for memo categories, like 'Business', 'Personal', etc.
create table MemoCategory (
Id int primary key,
Label text
);
create table Memo (
Id int primary key,
Category int default(0),
Private int default(0), -- boolean, zero or one
Text text,
foreign key (Category) references MemoCategory(Id)
);
-- Labels for expense categories, like 'Project A', 'Internal', etc.
create table ExpenseCategory (
Id int primary key,
Label text
);
-- Labels for expense types, like 'airfaire', 'car rental', etc.
create table ExpenseType (
Id int primary key,
Label text
);
-- Taken from /usr/include/pi-expense.h
insert into ExpenseType (Id,Label) values (0,'Airfare');
insert into ExpenseType (Id,Label) values (1,'Breakfast');
insert into ExpenseType (Id,Label) values (2,'Bus');
insert into ExpenseType (Id,Label) values (3,'Business Meals');
insert into ExpenseType (Id,Label) values (4,'Car Rental');
insert into ExpenseType (Id,Label) values (5,'Dinner');
insert into ExpenseType (Id,Label) values (6,'Entertainment');
insert into ExpenseType (Id,Label) values (7,'Fax');
insert into ExpenseType (Id,Label) values (8,'Gas');
insert into ExpenseType (Id,Label) values (9,'Gifts');
insert into ExpenseType (Id,Label) values (10,'Hotel');
insert into ExpenseType (Id,Label) values (11,'Incidentals');
insert into ExpenseType (Id,Label) values (12,'Laundry');
insert into ExpenseType (Id,Label) values (13,'Limo');
insert into ExpenseType (Id,Label) values (14,'Lodging');
insert into ExpenseType (Id,Label) values (15,'Lunch');
insert into ExpenseType (Id,Label) values (16,'Mileage');
insert into ExpenseType (Id,Label) values (17,'Other');
insert into ExpenseType (Id,Label) values (18,'Parking');
insert into ExpenseType (Id,Label) values (19,'Postage');
insert into ExpenseType (Id,Label) values (20,'Snack');
insert into ExpenseType (Id,Label) values (21,'Subway');
insert into ExpenseType (Id,Label) values (22,'Supplies');
insert into ExpenseType (Id,Label) values (23,'Taxi');
insert into ExpenseType (Id,Label) values (24,'Telephone');
insert into ExpenseType (Id,Label) values (25,'Tips');
insert into ExpenseType (Id,Label) values (26,'Tolls');
insert into ExpenseType (Id,Label) values (27,'Train');
-- Labels for expense payments, like 'cash', 'Visa', etc.
create table ExpensePayment (
Id int primary key,
Label text
);
-- Taken from /usr/include/pi-expense.h
insert into ExpensePayment (Id,Label) values (1,'AmEx');
insert into ExpensePayment (Id,Label) values (2,'Cash');
insert into ExpensePayment (Id,Label) values (3,'Check');
insert into ExpensePayment (Id,Label) values (4,'Credit Card');
insert into ExpensePayment (Id,Label) values (5,'MasterCard');
insert into ExpensePayment (Id,Label) values (6,'Prepaid');
insert into ExpensePayment (Id,Label) values (7,'Visa');
insert into ExpensePayment (Id,Label) values (8,'Unfiled');
-- Labels for expense currency, like 'US', 'Germany', etc.
create table ExpenseCurrency (
Id int primary key,
Label text
);
-- Taken from Expense/expense.c
insert into ExpenseCurrency (Id,Label) values (0,'Australia');
insert into ExpenseCurrency (Id,Label) values (1,'Austria');
insert into ExpenseCurrency (Id,Label) values (2,'Belgium');
insert into ExpenseCurrency (Id,Label) values (3,'Brazil');
insert into ExpenseCurrency (Id,Label) values (4,'Canada');
insert into ExpenseCurrency (Id,Label) values (5,'Denmark');
insert into ExpenseCurrency (Id,Label) values (133,'EU (Euro)');
insert into ExpenseCurrency (Id,Label) values (6,'Finland');
insert into ExpenseCurrency (Id,Label) values (7,'France');
insert into ExpenseCurrency (Id,Label) values (8,'Germany');
insert into ExpenseCurrency (Id,Label) values (9,'Hong Kong');
insert into ExpenseCurrency (Id,Label) values (10,'Iceland');
insert into ExpenseCurrency (Id,Label) values (24,'India');
insert into ExpenseCurrency (Id,Label) values (25,'Indonesia');
insert into ExpenseCurrency (Id,Label) values (11,'Ireland');
insert into ExpenseCurrency (Id,Label) values (12,'Italy');
insert into ExpenseCurrency (Id,Label) values (13,'Japan');
insert into ExpenseCurrency (Id,Label) values (26,'Korea');
insert into ExpenseCurrency (Id,Label) values (14,'Luxembourg');
insert into ExpenseCurrency (Id,Label) values (27,'Malaysia');
insert into ExpenseCurrency (Id,Label) values (15,'Mexico');
insert into ExpenseCurrency (Id,Label) values (16,'Netherlands');
insert into ExpenseCurrency (Id,Label) values (17,'New Zealand');
insert into ExpenseCurrency (Id,Label) values (18,'Norway');
insert into ExpenseCurrency (Id,Label) values (28,'P.R.C.');
insert into ExpenseCurrency (Id,Label) values (29,'Philippines');
insert into ExpenseCurrency (Id,Label) values (30,'Singapore');
insert into ExpenseCurrency (Id,Label) values (19,'Spain');
insert into ExpenseCurrency (Id,Label) values (20,'Sweden');
insert into ExpenseCurrency (Id,Label) values (21,'Switzerland');
insert into ExpenseCurrency (Id,Label) values (32,'Taiwan');
insert into ExpenseCurrency (Id,Label) values (31,'Thailand');
insert into ExpenseCurrency (Id,Label) values (22,'United Kingdom');
insert into ExpenseCurrency (Id,Label) values (23,'United States');
create table Expense (
Id int primary key,
Category int default(0),
Date text, -- date in format YYYY-MM-DD
Type int, -- 0=airfare, 1=breakfast, etc.
Payment int, -- 0=AmEx, 1=Cash, etc.
Currency int,
Amount text,
Vendor text,
City text,
Attendees text,
Note text,
foreign key (Category) references ExpenseCategory(Id),
foreign key (Type) references ExpenseType(Id),
foreign key (Payment) references ExpensePayment(Id),
foreign key (Currency) references ExpenseCurrency(Id)
);