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
|
#include "DB.h"
using namespace std;
dbVec DB::readSqlToVec(const string& s)
{
#if _SQL
int cols, step;
dbVec results;
if (sqlite3_prepare(db, (const char*)s.c_str(), -1, &stmt, NULL) != SQLITE_OK)
exit(EXIT_FAILURE);
cols = sqlite3_column_count(stmt);
while (true) {
dbVars temp;
step = sqlite3_step(stmt);
if (step == SQLITE_ROW) {
for (int i=0; i<cols; i++)
temp.push_back(
(sqlite3_column_text(stmt, i) != NULL) ? string((const char*)sqlite3_column_text(stmt, i)) : ""
);
results.push_back(temp);
} else if (step == SQLITE_DONE) {
break;
} else {
exit(EXIT_FAILURE);
}
}
sqlite3_finalize(stmt);
return results;
#else
(void)s;
return dbVec();
#endif
}
string DB::getProperTableName(const string& table)
{
string temp = table;
replace(temp.begin(), temp.end(), '-', '_');
return temp;
}
void DB::createTables()
{
stringstream sst;
sst
<< "\
create table if not exists Species (\
species_name text primary key);\
create table if not exists Strains (\
strain_name text primary key,species_name text,\
foreign key(species_name) references Species(species_name));\
create table if not exists Libraries (\
library_name text primary key,strain_name text,\
foreign key(strain_name) references Strains(strain_name));\
create table if not exists Run_pe (\
run_id text primary key,species_name text,strain_name text,library_name text,abyss_version text,\
pe_name text,pe_k integer,pe_lib text,\
time_start_run not null default (datetime(current_timestamp,'localtime')),stage integer default 0,\
foreign key(library_name) references Libraries(library_name));";
if (query(sst.str()) && verbose_val > 2)
cerr << sst.str() << endl;
}
void DB::insertToMetaTables(const dbVars& v)
{
stringstream sst;
sst
<< "\
insert or ignore into species values('" << v[3] << "');\
insert or ignore into strains values('" << v[2] << "','" << v[3] << "');\
insert or ignore into libraries values('" << v[1] << "','" << v[2] << "');\
insert into run_pe(run_id,species_name,strain_name,library_name,abyss_version,pe_name,pe_k,pe_lib) \
values('" << v[0] << "','" << v[3] << "','" << v[2] << "','" << v[1] << "','" << VERSION << "','" << v[4] <<
"','" << v[5] << "','" << v[6] << "');";
if (query(sst.str()) && verbose_val > 1)
cerr << sst.str() << endl;
}
string DB::initializeRun()
{
string id("");
createTables();
ifstream ifile("db.txt");
if (ifile) {
dbVars iV;
string eachLine;
while (getline(ifile, eachLine)) iV.push_back(eachLine);
if (iV.size() == 7) {
insertToMetaTables(iV);
ofstream ofile("db.txt");
ofile << iV[0] << "\n";
}
stringstream uStream;
uStream
<< "update Run_pe set stage=stage+1 where run_id = '" << iV[0] << "';";
if (query (uStream.str()) && verbose_val > 2)
cerr << uStream.str() << endl;
id = iV[0];
}
return id;
}
/** Execute the specified shell command and exit if it fails. */
static inline void systemOrExit(const char* s)
{
int status = system(s);
if (status != 0) {
std::cerr << "error: status " << status << ": " << s << '\n';
exit(EXIT_FAILURE);
}
}
string DB::getPath(const string& program)
{
stringstream echoStream, pathStream;
echoStream
<< "echo `which " << program << "` > temp.txt";
systemOrExit(echoStream.str().c_str());
ifstream ifile("temp.txt");
if (ifile) {
string line;
while (getline (ifile, line)) pathStream << line;
}
systemOrExit("rm -f temp.txt");
return pathStream.str();
}
bool DB::definePeVars(const string& id)
{
bool defined = false;
stringstream select_cmd;
select_cmd
<< "select species_name, strain_name, library_name from Run_pe where run_id = '" << id << "';";
dbVec v;
v = readSqlToVec(select_cmd.str());
if (v[0].size() == peVars.size()) {
unsigned i = 0;
while (i<v[0].size()) {
peVars[i] = v[0][i];
i++;
}
defined = true;
}
return defined;
}
void DB::assemblyStatsToDb()
{
string temp = getProperTableName(prog);
stringstream sqlStream, mapKeys, mapValues;
string id = initializeRun();
if (temp == "ABYSS" || temp == "ABYSS_P")
temp = "ABYSS_assembly";
sqlStream
<< "\
create table if not exists " << temp << " (\
run_id text default null, run_stage integer default null, species_name text, strain_name text, library_name text, \
exec_path text, command_line text, time_finish_" << temp << " not null default (datetime(current_timestamp,'localtime')), ";
mapKeys
<< "run_id, run_stage, species_name, strain_name, library_name, exec_path, command_line, ";
if (id.length() == 0)
mapValues
<< "null,null,";
else
mapValues
<< "'" << id << "',(select stage from Run_pe where run_id = '" << id << "'),";
if ((id.length() > 0) && definePeVars(id))
mapValues
<< "'" << peVars[0] << "', '" << peVars[1] << "', '" << peVars[2] << "', '";
else
mapValues
<< "'" << initVars[2] << "', '" << initVars[1] << "', '" << initVars[0] << "', '";
mapValues
<< getPath(prog)
<< "', '" << cmd << "', ";
while (!statMap.empty()) {
mapKeys
<< statMap.getFirst(statMap.begin())
<< (statMap.size() == 1 ? "" : ", ");
mapValues
<< statMap.getSecond(statMap.begin())
<< (statMap.size() == 1 ? "" : ", ");
sqlStream
<< statMap.getFirst(statMap.begin())
<< (statMap.size() == 1 ? " int, foreign key(run_id) references Run_pe(run_id));" : " int, ");
statMap.erase(statMap.begin());
}
if (query(sqlStream.str()) && verbose_val > 1)
cerr << sqlStream.str() << endl;
sqlStream.str("");
sqlStream
<< "insert into "<< temp << " (" << mapKeys.str() << ") values (" << mapValues.str() << ");";
if (query(sqlStream.str()) && verbose_val > 0)
cerr << sqlStream.str() << endl;
}
|
