00001
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011
00012
00013
00014
00015
00016
00017
00018
00019 #include "config.h"
00020
00021 #ifdef ENABLE_SQLITE
00022
00023 #include "SQLiteUtil.h"
00024 #include "base/util/StringBuffer.h"
00025 #include "vocl/VConverter.h"
00026
00027 #include <stdarg.h>
00028 #include <sstream>
00029
00030 void SQLiteUtil::throwError(const string &operation)
00031 {
00032 string descr = m_name + ": '" + m_fileid + "': " + operation + " failed";
00033
00034 if (m_db) {
00035 const char *error = sqlite3_errmsg(m_db);
00036 descr += ": ";
00037 descr += error ? error : "unspecified error";
00038 }
00039
00040 throw runtime_error(descr);
00041 }
00042
00043 sqlite3_stmt *SQLiteUtil::prepareSQLWrapper(const char *sql, const char **nextsql)
00044 {
00045 sqlite3_stmt *stmt = NULL;
00046
00047 checkSQL(sqlite3_prepare(m_db, sql, -1, &stmt, nextsql), sql);
00048 return stmt;
00049 }
00050
00051 sqlite3_stmt *SQLiteUtil::prepareSQL(const char *sqlfmt, ...)
00052 {
00053 StringBuffer sql;
00054 va_list ap;
00055
00056 va_start(ap, sqlfmt);
00057 sql.vsprintf(sqlfmt, ap);
00058 va_end(ap);
00059
00060 return prepareSQLWrapper(sql.c_str());
00061 }
00062
00063
00064 SQLiteUtil::key_t SQLiteUtil::findKey(const char *database, const char *keyname, const char *key)
00065 {
00066 sqliteptr query(prepareSQL("SELECT ROWID FROM %s WHERE %s = '%s';", database, keyname, key));
00067
00068 int res = checkSQL(sqlite3_step(query), "getting key");
00069 if (res == SQLITE_ROW) {
00070 return sqlite3_column_int64(query, 0);
00071 } else {
00072 return -1;
00073 }
00074 }
00075
00076 string SQLiteUtil::findColumn(const char *database, const char *keyname, const char *key, const char *column, const char *def)
00077 {
00078 sqliteptr query(prepareSQL("SELECT %s FROM %s WHERE %s = '%s';", column, database, keyname, key));
00079
00080 int res = checkSQL(sqlite3_step(query), "getting key");
00081 if (res == SQLITE_ROW) {
00082 const unsigned char *text = sqlite3_column_text(query, 0);
00083
00084 return text ? (const char *)text : def;
00085 } else {
00086 return def;
00087 }
00088 }
00089
00090 string SQLiteUtil::getTextColumn(sqlite3_stmt *stmt, int col, const char *def)
00091 {
00092 const unsigned char *text = sqlite3_column_text(stmt, col);
00093 return text ? (const char *)text : def;
00094 }
00095
00096 SQLiteUtil::syncml_time_t SQLiteUtil::getTimeColumn(sqlite3_stmt *stmt, int col)
00097 {
00098
00099 return sqlite3_column_int64(stmt, col);
00100 }
00101
00102 string SQLiteUtil::time2str(SQLiteUtil::syncml_time_t t)
00103 {
00104 char buffer[128];
00105 sprintf(buffer, "%lu", t);
00106 return buffer;
00107 }
00108
00109 void SQLiteUtil::rowToVObject(sqlite3_stmt *stmt, vocl::VObject &vobj)
00110 {
00111 const unsigned char *text;
00112 const char *tablename;
00113 int i;
00114
00115 for (i = 0; m_mapping[i].colname; i++) {
00116 if (m_mapping[i].colindex < 0 ||
00117 !m_mapping[i].propname) {
00118 continue;
00119 }
00120 tablename = sqlite3_column_table_name(stmt, m_mapping[i].colindex);
00121 if (!tablename || strcasecmp(tablename, m_mapping[i].tablename)) {
00122 continue;
00123 }
00124 text = sqlite3_column_text(stmt, m_mapping[i].colindex);
00125 if (text) {
00126 vobj.addProperty(m_mapping[i].propname, (const char *)text);
00127 }
00128 }
00129 }
00130
00131 sqlite3_stmt *SQLiteUtil::vObjectToRow(vocl::VObject &vobj,
00132 const string &tablename,
00133 int numparams,
00134 const string &cols,
00135 const string &values)
00136 {
00137 stringstream cols_stream;
00138 stringstream values_stream;
00139 int i;
00140
00141 cols_stream << cols;
00142 values_stream << values;
00143
00144
00145 for (i = 0; m_mapping[i].colname; i++) {
00146 if (m_mapping[i].colindex < 0 ||
00147 !m_mapping[i].propname ||
00148 tablename != m_mapping[i].tablename) {
00149 continue;
00150 }
00151
00152 vocl::VProperty *vprop = vobj.getProperty(m_mapping[i].propname);
00153 if (vprop) {
00154 if (cols.size()) {
00155 cols_stream << ", ";
00156 values_stream << ", ";
00157 }
00158 cols_stream << m_mapping[i].colname;
00159 values_stream << "?";
00160 }
00161 }
00162
00163
00164 sqliteptr insert(prepareSQL("INSERT INTO ABPerson( %s ) "
00165 "VALUES( %s );",
00166 cols_stream.str().c_str(),
00167 values_stream.str().c_str()));
00168
00169 int param = numparams + 1;
00170 for (i = 0; m_mapping[i].colname; i++) {
00171 if (m_mapping[i].colindex < 0 ||
00172 !m_mapping[i].propname ||
00173 tablename != m_mapping[i].tablename) {
00174 continue;
00175 }
00176
00177 vocl::VProperty *vprop = vobj.getProperty(m_mapping[i].propname);
00178 if (vprop) {
00179 const char *text = vprop->getValue();
00180 checkSQL(sqlite3_bind_text(insert, param++, text ? text : "", -1, SQLITE_TRANSIENT));
00181 }
00182 }
00183
00184 return insert.release();
00185 }
00186
00187 void SQLiteUtil::open(const string &name,
00188 const string &fileid,
00189 const SQLiteUtil::Mapping *mapping,
00190 const char *schema)
00191 {
00192 close();
00193 m_name = name;
00194 m_fileid = fileid;
00195
00196 const string prefix("file://");
00197 bool create = fileid.substr(0, prefix.size()) == prefix;
00198 string filename = create ? fileid.substr(prefix.size()) : fileid;
00199
00200 if (!create && access(filename.c_str(), F_OK)) {
00201 throw runtime_error(m_name + ": no such database: '" + filename + "'");
00202 }
00203
00204 sqlite3 *db;
00205 int res = sqlite3_open(filename.c_str(), &db);
00206 m_db = db;
00207 checkSQL(res, "opening");
00208
00209
00210 sqliteptr check(prepareSQLWrapper("SELECT * FROM sqlite_master;"));
00211 switch (sqlite3_step(check)) {
00212 case SQLITE_ROW:
00213
00214 break;
00215 case SQLITE_DONE: {
00216
00217 const char *nextsql = schema;
00218 while (nextsql && *nextsql) {
00219 const char *sql = nextsql;
00220 sqliteptr create(prepareSQLWrapper(sql, &nextsql));
00221 while (true) {
00222 int res = sqlite3_step(create);
00223 if (res == SQLITE_DONE) {
00224 break;
00225 } else if (res == SQLITE_ROW) {
00226
00227 } else {
00228 throwError("creating database");\
00229 }
00230 }
00231 }
00232 break;
00233 }
00234 default:
00235 throwError("checking content");
00236 break;
00237 }
00238
00239
00240 int i;
00241 for (i = 0; mapping[i].colname; i++) ;
00242 m_mapping.set(new Mapping[i + 1]);
00243 sqliteptr query;
00244 string tablename;
00245 for (i = 0; mapping[i].colname; i++) {
00246 m_mapping[i] = mapping[i];
00247
00248
00249 if (tablename != m_mapping[i].tablename) {
00250 tablename = m_mapping[i].tablename;
00251 query.set(prepareSQL("SELECT * FROM %s;", tablename.c_str()));
00252 }
00253
00254
00255 for (m_mapping[i].colindex = sqlite3_column_count(query) - 1;
00256 m_mapping[i].colindex >= 0;
00257 m_mapping[i].colindex--) {
00258 const char *name = sqlite3_column_name(query, m_mapping[i].colindex);
00259 if (name && !strcasecmp(m_mapping[i].colname, name)) {
00260 break;
00261 }
00262 }
00263 }
00264 memset(&m_mapping[i], 0, sizeof(m_mapping[i]));
00265 }
00266
00267 void SQLiteUtil::close()
00268 {
00269 m_db = NULL;
00270 }
00271
00272
00273 #endif