Hall-D Software  alpha
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Groups Pages
DESDBProviderMySQL.cc
Go to the documentation of this file.
1 // $Id$
2 //
3 // File: DESDBProviderMySQL.cc
4 // Creator: sdobbs
5 //
6 
7 #include <iostream>
8 #include <string>
9 #include <sstream>
10 #include <cstdlib>
11 
12 #include "DESDBProviderMySQL.h"
13 
14 
15 
16 //---------------------------------
17 // DESDBProviderMySQL (Constructor)
18 //---------------------------------
19 DESDBProviderMySQL::DESDBProviderMySQL(string connection_str) : DESDBProvider(connection_str)
20 {
21  // defaults
22  user_name = "es_user";
23  password = "";
24  host_name = "hallddb.jlab.org";
25  database = "EventStoreTMP";
26  port = 3306; // this is the default MySQL port
27  DBresult = NULL;
28 
29  is_connected = false;
30 
31  // parse the connection string
32  // format: mysql://user@password:host/database
33  // first check the URI
34  size_t type_pos = connection_str.find("mysql://");
35  if(type_pos==string::npos)
36  {
37  throw JException("Invalid EventStore DB definition: "+connection_str);
38  }
39 
40  // clear out the protocol
41  connection_str.erase(0,8);
42 
43  // see if there is user/password information...
44  size_t at_pos = connection_str.find('@');
45  if(at_pos != string::npos)
46  {
47  string user_pass_str;
48 
49  // Handle a few different connection string cases
50  if(at_pos == connection_str.length()-1) {
51  // case: 'user:password@'
52  user_pass_str = connection_str.substr(0, at_pos);
53  connection_str = string("");
54  } else if(at_pos==0) {
55  // case: '@localhost'
56  connection_str = connection_str.substr(1);
57  user_pass_str = string("");
58  } else {
59  // everything else
60  user_pass_str = connection_str.substr(0,at_pos);
61  connection_str = connection_str.substr(at_pos+1);
62  }
63 
64  // see if there's just a user name or user/login info
65  size_t colon_pos = user_pass_str.find(':');
66  if(colon_pos != string::npos) {
67  user_name = user_pass_str.substr(0,colon_pos);
68  password = user_pass_str.substr(colon_pos+1);
69  } else {
70  user_name = user_pass_str;
71  }
72  }
73 
74  // now parse host and database information
75  // move from back to front
76 
77  // pull out database name
78  size_t white_pos = connection_str.find('/');
79  if(white_pos != string::npos)
80  {
81  database = connection_str.substr(white_pos+1);
82  connection_str.erase(white_pos);
83  }
84 
85  // get the TCP port, if specified
86  size_t colon_pos = connection_str.find(':');
87  if(colon_pos != string::npos)
88  {
89  string port_str = connection_str.substr(colon_pos+1);
90  connection_str.erase(colon_pos);
91 
92  port = atoi(port_str.c_str());
93  }
94 
95  //3) everything that is last whould be address
96  host_name = connection_str;
97 }
98 
99 
100 //---------------------------------
101 // ~DESDBProviderMySQL (Destructor)
102 //---------------------------------
104 {
105  Disconnect();
106 
107 }
108 
109 //---------------------------------
110 // Open
111 //---------------------------------
113 {
114  // Initialize MYSQL data
115  DBptr = mysql_init(NULL);
116  if(DBptr == NULL) {
117  throw JException("Unable to initialize MySQL connection information...");
118  }
119 
120  // Finally, open a connection to the database
121  return Connect();
122 }
123 
124 //---------------------------------
125 // Connect
126 //---------------------------------
128 {
129  if(IsConnected())
130  return true;
131 
132  if(!mysql_real_connect(DBptr, host_name.c_str(), user_name.c_str(),
133  password.c_str(), database.c_str(), port, NULL, 0)) {
134  jerr << FormatMySQLError("mysql_real_connect()") << endl;
135 
136  DBptr = NULL; // to be safe?
137  return false;
138  }
139 
140  is_connected = true;
141  return true;
142 }
143 
144 //---------------------------------
145 // Disconnect
146 //---------------------------------
148 {
149  if(!IsConnected())
150  return;
151 
152  mysql_close(DBptr);
153  DBptr = NULL;
154 }
155 
156 //---------------------------------
157 // GetGrades
158 //---------------------------------
159 bool DESDBProviderMySQL::GetGrades(vector<string> &grades)
160 {
161  string query_str = "SELECT DISTINCT grade FROM Version WHERE state='active'";
162 
163  // clear out any lingering data
164  if(DBresult != NULL) {
165  mysql_free_result(DBresult);
166  DBresult = NULL;
167  }
168 
169  // execute the query
170  if(mysql_query(DBptr, query_str.c_str())) {
171  jerr << FormatMySQLError("mysql_query()") << endl
172  << "Query: " << query_str << endl;
173  return false;
174  }
175 
176  //get results
177  DBresult = mysql_store_result(DBptr);
178 
179  if(!DBresult) {
180  jerr << FormatMySQLError("mysql_query()") << endl
181  << "Query: " << query_str << endl;
182  return false;
183  }
184 
185  //int num_returned_rows = mysql_num_rows(DBresult);
186  //int num_returned_cols = mysql_num_fields(DBresult);
187 
188  MYSQL_ROW row;
189  while((row = mysql_fetch_row(DBresult))) {
190  grades.push_back(row[0]);
191  }
192 
193  // clean up
194  mysql_free_result(DBresult);
195  DBresult = NULL;
196  return true;
197 }
198 
199 //---------------------------------
200 // GetSkims
201 //---------------------------------
202 bool DESDBProviderMySQL::GetSkims(vector<string> &skims, string timestamp, string grade)
203 {
204  stringstream query_ss;
205  // first figure out the closest timestamp to the one requested
206  query_ss << "SELECT MAX(timeStamp) FROM Version WHERE timeStamp<='"
207  << timestamp <<"' AND grade='" << grade << "' AND state='active'";
208 
209  // clear out any lingering data
210  if(DBresult != NULL) {
211  mysql_free_result(DBresult);
212  DBresult = NULL;
213  }
214 
215  // execute the query
216  if(mysql_query(DBptr, query_ss.str().c_str())) {
217  jerr << FormatMySQLError("mysql_query()") << endl
218  << "Query: " << query_ss.str() << endl;
219  return false;
220  }
221 
222  //get results
223  DBresult = mysql_store_result(DBptr);
224 
225  if(!DBresult) {
226  jerr << FormatMySQLError("mysql_query()") << endl
227  << "Query: " << query_ss.str() << endl;
228  return false;
229  }
230 
231  int num_returned_rows = mysql_num_rows(DBresult);
232  if(num_returned_rows == 0) {
233  jerr << "Invalid timestamp: " + timestamp << endl;
234  return false;
235  }
236 
237  MYSQL_ROW row;
238  row = mysql_fetch_row(DBresult);
239  string real_timestamp(row[0]);
240 
241  // clear results
242  mysql_free_result(DBresult);
243  DBresult = NULL;
244 
245  query_ss.str(""); // clear stringstream
246  query_ss << "SELECT DISTINCT view FROM Version,KeyFile WHERE timeStamp='"
247  << real_timestamp <<"' AND Version.grade='" << grade
248  << "' AND Version.state='active' AND Version.graphid=KeyFile.graphid GROUP BY view";
249 
250  // execute the query
251  if(mysql_query(DBptr, query_ss.str().c_str())) {
252  jerr << FormatMySQLError("mysql_query()") << endl
253  << "Query: " << query_ss.str() << endl;
254  return false;
255  }
256 
257  //get results
258  DBresult = mysql_store_result(DBptr);
259 
260  if(!DBresult) {
261  jerr << FormatMySQLError("mysql_query()") << endl
262  << "Query: " << query_ss.str() << endl;
263  return false;
264  }
265 
266  // get results
267  while((row = mysql_fetch_row(DBresult))) {
268  skims.push_back(row[0]);
269  }
270 
271  // clean up
272  mysql_free_result(DBresult);
273  DBresult = NULL;
274 
275  return true;
276 }
277 
char string[256]
bool GetGrades(vector< string > &grades)
string FormatMySQLError(string mysql_func_name)
DESDBProviderMySQL(string connection_str)
bool GetSkims(vector< string > &grades, string timestamp, string grade)