create.sql
3.05 KB
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
CREATE TABLE subregion_ext (
ug INTEGER NOT NULL UNIQUE,
region TEXT NOT NULL,
PRIMARY KEY(ug)
);
CREATE TABLE xy_med_int (
ug INTEGER NOT NULL,
x REAL NOT NULL,
y REAL NOT NULL
);
CREATE TABLE xy_med_ext (
ug INTEGER NOT NULL,
x REAL NOT NULL,
y REAL NOT NULL
);
CREATE TABLE xy_int (
ug INTEGER NOT NULL,
x REAL NOT NULL,
y REAL NOT NULL
);
CREATE TABLE xy_ext (
ug INTEGER NOT NULL,
x REAL NOT NULL,
y REAL NOT NULL
);
CREATE TABLE region_ext (
ug INTEGER NOT NULL UNIQUE,
region TEXT NOT NULL,
PRIMARY KEY(ug)
);
CREATE TABLE region_int (
ug INTEGER NOT NULL UNIQUE,
region TEXT NOT NULL,
PRIMARY KEY(ug)
);
CREATE TABLE perc_r10 (
ug INTEGER NOT NULL,
presc INTEGER NOT NULL,
perc_r10_total REAL NOT NULL
);
CREATE TABLE perc_r5 (
ug INTEGER NOT NULL,
presc INTEGER NOT NULL,
perc_r5_total REAL NOT NULL
);
CREATE TABLE perc_r0 (
ug INTEGER NOT NULL,
presc INTEGER NOT NULL,
perc_r0_total REAL NOT NULL
);
CREATE TABLE soilloss (
ug INTEGER NOT NULL,
presc INTEGER NOT NULL,
soilloss REAL NOT NULL
);
CREATE TABLE wood_rewards (
ug INTEGER NOT NULL,
presc INTEGER NOT NULL,
wood REAL NOT NULL
);
CREATE TABLE IF NOT EXISTS adj_int_gt50(
ug1 INTEGER NOT NULL,
ug2 INTEGER NOT NULL,
front REAL NOT NULL,
PRIMARY KEY(ug1,ug2)
FOREIGN KEY(ug1) REFERENCES ug(ug)
FOREIGN KEY(ug2) REFERENCES ug(ug));
CREATE TABLE IF NOT EXISTS adj_int(
ug1 INTEGER NOT NULL,
ug2 INTEGER NOT NULL,
front REAL NOT NULL,
PRIMARY KEY(ug1,ug2)
FOREIGN KEY(ug1) REFERENCES ug(ug)
FOREIGN KEY(ug2) REFERENCES ug(ug));
CREATE TABLE IF NOT EXISTS adj_ext(
ug1 INTEGER NOT NULL,
ug2 INTEGER NOT NULL,
front REAL NOT NULL,
PRIMARY KEY(ug1,ug2)
FOREIGN KEY(ug1) REFERENCES ug(ug)
FOREIGN KEY(ug2) REFERENCES ug(ug));
CREATE TABLE IF NOT EXISTS area_internal (
ug INTEGER NOT NULL UNIQUE,
area REAL NOT NULL,
PRIMARY KEY(ug)
FOREIGN KEY(ug) REFERENCES ug(ug));
CREATE TABLE IF NOT EXISTS area_external (
ug INTEGER NOT NULL UNIQUE,
area REAL NOT NULL,
PRIMARY KEY(ug)
FOREIGN KEY(ug) REFERENCES ug(ug));
CREATE TABLE IF NOT EXISTS ug_ids (
external TEXT NOT NULL UNIQUE,
internal INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE
);
CREATE TABLE IF NOT EXISTS action_external (
Id TEXT NOT NULL,
Area REAL NOT NULL,
Presc INTEGER NOT NULL,
Year INTEGER NOT NULL,
Age INTEGER NOT NULL,
TotalVolume REAL NOT NULL,
V_thin REAL NOT NULL,
V_harv REAL NOT NULL,
Species TEXT NOT NULL,
Biodiversity0 REAL NOT NULL,
Biodiversity5 REAL NOT NULL,
Biodiversity10 REAL NOT NULL,
Perc_r0 REAL NOT NULL,
Perc_r5 REAL NOT NULL,
Perc_r10 REAL NOT NULL,
Soilloss REAL NOT NULL,
Cork REAL NOT NULL
);
CREATE TABLE IF NOT EXISTS action_internal (
Id TEXT NOT NULL,
Area REAL NOT NULL,
Presc INTEGER NOT NULL,
Year INTEGER NOT NULL,
Age INTEGER NOT NULL,
TotalVolume REAL NOT NULL,
V_thin REAL NOT NULL,
V_harv REAL NOT NULL,
Species TEXT NOT NULL,
Biodiversity0 REAL NOT NULL,
Biodiversity5 REAL NOT NULL,
Biodiversity10 REAL NOT NULL,
Perc_r0 REAL NOT NULL,
Perc_r5 REAL NOT NULL,
Perc_r10 REAL NOT NULL,
Soilloss REAL NOT NULL,
Cork REAL NOT NULL
);