-
Notifications
You must be signed in to change notification settings - Fork 1
/
hof3.sql
159 lines (143 loc) · 5.32 KB
/
hof3.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
PRAGMA foreign_keys = ON; -- Turn on foreign key constraints.
CREATE TABLE Admin (
Version TEXT,
LastBagFilterDescription TEXT
);
INSERT INTO Admin(Version) VALUES("0.8");
CREATE TABLE Membranes (
MembraneID INTEGER PRIMARY KEY,
Name TEXT NOT NULL UNIQUE,
Description TEXT,
MWCO INTEGER, -- Daltons
SurfaceArea REAL, -- m^2
MaxInletPressure REAL, -- bar
MaxAlongMembranePressure REAL, -- bar
MaxTransMembranePressure REAL, -- bar
MaxBackPressure REAL, -- bar
MinTemperature REAL, -- degrees C
MaxTemperature REAL, -- degrees C
MinPH REAL,
MaxPH REAL,
Retired BOOLEAN NOT NULL DEFAULT 0
);
CREATE TABLE Products (
ProductID INTEGER PRIMARY KEY,
Name TEXT NOT NULL UNIQUE,
Description TEXT,
MinTemperature REAL, -- degrees C
MaxTemperature REAL, -- degrees C
Retired BOOLEAN NOT NULL DEFAULT 0
);
CREATE TABLE Chemicals (
ChemicalID INTEGER PRIMARY KEY,
Retired BOOLEAN NOT NULL DEFAULT 0,
Name TEXT NOT NULL UNIQUE,
Description TEXT,
MinTemperature REAL, -- degrees C
MaxTemperature REAL, -- degrees C
DosedManually BOOLEAN NOT NULL -- true if the chemical is added by the user
);
CREATE TABLE Programs (
ProgramID INTEGER PRIMARY KEY,
Description TEXT,
);
CREATE TABLE Instructions (
InstructionID INTEGER PRIMARY KEY,
ProgramID INTEGER REFERENCES Programs(ProgramID),
Queued BOOLEAN, -- true if this instruction is queued and not yet run
Description TEXT,
StartTime TEXT,
EndTime TEXT,
MembraneID INTEGER REFERENCES Membranes(MembraneID),
BagFilter TEXT, -- a description of the bag filter in use
ProductID INTEGER REFERENCES Products(ProductID),
AutoChemical INTEGER REFERENCES Chemicals(ChemicalID),
ManualChemical INTEGER REFERENCES Chemicals(ChemicalID),
FillSource TEXT,
FillLevel REAL,
FillLevelHysteresis REAL,
StartLevel REAL,
StartLevelHysteresis REAL,
DoseTime REAL, -- dosing pump run duration
TemperatureControlMethod TEXT,
DesiredTemperature REAL,
DesiredTemperatureHysteresis REAL,
MixTime REAL, -- duration of mix stage, -1 to skip
MixTargetPressure REAL, -- percentage of maximum inlet pressure
MembraneUseTime REAL, -- seconds
MaximumMembraneInletPressureTarget REAL, -- bar
AlongMembranePressureDropTarget REAL, -- bar
TransMembranePressureDropTarget REAL, -- bar
BackwashPressureTarget REAL, -- bar
BackwashControllerStartingPosition REAL, -- percentage
TimeBetweenBackwashes REAL, -- seconds
TimeBetweenDirectionChanges REAL, -- seconds
RecircTime REAL, -- duration of recirculation stage, -1 to skip
PermeateBlastTime REAL, -- duration of permeate line fully open (for cleaning)
RetentateBlastTime REAL, -- duration of retentate line fully open (for cleaning)
ConcRatio REAL, -- concentration ratio (can't go below one)
EmptyLevel REAL, -- feed tank percentage level after run down
MaximumMembraneInletPressureFault REAL, -- bar
AlongMembranePressureDropFault REAL, -- bar
TransMembranePressureDropFault REAL, -- bar
BackwashPressureFault REAL, -- bar
BagFilterPressureDropFault REAL, -- bar
MinimumTemperatureFault REAL, -- deg C
MaximumTemperatureFault REAL, -- deg C
MinimumPHFault REAL,
MaximumPHFault REAL,
LoggingInterval REAL, -- seconds
DetailedBackwashLogging BOOLEAN
);
CREATE TABLE CleaningRegimes (
RegimeID INTEGER PRIMARY KEY,
Retired BOOLEAN NOT NULL DEFAULT 0,
Name TEXT NOT NULL UNIQUE,
Description TEXT
);
CREATE TABLE CleaningRegimeSteps (
RegimeID INTEGER NOT NULL,
StepNumber INTEGER NOT NULL,
Duration INTEGER, -- Duration of step, in seconds
CleaningChemicalStrength REAL, -- Strength as percentage by volume; NULL if no chemicals
Temperature REAL, -- degrees C; null if not rinsing or washing or don't care
DumpDuration INTEGER, -- Dump duration in seconds; NULL if no dump
FOREIGN KEY(RegimeID) REFERENCES CleaningRegimes(RegimeID)
);
CREATE TABLE Actions (
ActionID INTEGER PRIMARY KEY,
MembraneID INTEGER NOT NULL,
ActionType INTEGER NOT NULL, -- 1: Clean, 2:
DateStarted INTEGER,
DateFinished INTEGER, -- NULL if cancelled rather than finished
DateCancelled INTEGER, -- NULL if finished and not cancelled
FOREIGN KEY(MembraneID) REFERENCES Membranes(MembraneID)
);
CREATE TABLE Cleans (
CleanID INTEGER PRIMARY KEY,
ActionID INTEGER NOT NULL,
ChemicalID INTEGER,
FOREIGN KEY(ActionID) REFERENCES Actions(ActionID),
FOREIGN KEY(ChemicalID) REFERENCES CleaningChemicals(ChemicalID)
);
CREATE TABLE WaterFluxTests (
WaterFluxTestID INTEGER PRIMARY KEY,
ActionID INTEGER NOT NULL,
AvgTemperature REAL NOT NULL, -- Data are averages over 2 minutes, after 1 minute
AvgPH REAL NOT NULL,
AvgPressure1 REAL NOT NULL,
AvgPressure2 REAL NOT NULL,
AvgPressure3 REAL NOT NULL,
AvgPressure4 REAL NOT NULL,
AvgFlow1 REAL NOT NULL,
AvgFlow2 REAL NOT NULL,
AvgFlow3 REAL NOT NULL,
AvgPump1Speed REAL NOT NULL,
FOREIGN KEY(ActionID) REFERENCES Actions(ActionID)
);
CREATE TABLE ContinuousConcentrations (
ContinuousID INTEGER PRIMARY KEY,
ActionID INTEGER NOT NULL,
ProductID INTEGER NOT NULL,
FOREIGN KEY(ProductID) REFERENCES Products(ProductID)
);