void library_file_clear(int64_t file) { static const char *sql_album_tracks = "UPDATE albums SET tracks = (SELECT COUNT(tracks.rowid) FROM tracks WHERE tracks.albumid = albums.rowid AND tracks.fileid != ?1) WHERE albums.rowid IN (SELECT albumid FROM tracks WHERE fileid = ?1)"; static const char *sql_tracks = "DELETE FROM tracks WHERE fileid = ?"; static const char *sql_images = "DELETE FROM images WHERE fileid = ?"; sqlite3_stmt *query; if (!prepare_query(sql_album_tracks, &query)) { return; } sqlite3_bind_int64(query, 1, file); execute(query); if (!prepare_query(sql_tracks, &query)) { return; } sqlite3_bind_int64(query, 1, file); execute(query); if (!prepare_query(sql_images, &query)) { return; } sqlite3_bind_int64(query, 1, file); execute(query); }
void set_db_version(int db_version) { /** RESULT : sets the database version entry in the database RETURNS : void PURPOSE : to enable database upgrade procedures and error checking NOTES : **/ //check database is open and table exists check_db_open(GET_CALL_INFO); check_table_exists("GAME_DATA_TABLE", GET_CALL_INFO); char *sql="UPDATE GAME_DATA_TABLE SET DB_VERSION = ?"; sqlite3_stmt *stmt=NULL; prepare_query(sql, &stmt, GET_CALL_INFO); sqlite3_bind_int(stmt, 1, db_version); step_query(sql, &stmt, GET_CALL_INFO); destroy_query(sql, &stmt, GET_CALL_INFO); }
void library_iterate_directories (int64_t parent, bool (*callback)(library_directory_t *directory, void *opaque), void *opaque) { static const char *sql = "SELECT rowid, path, mtime, parentid FROM directories WHERE parentid = ?"; sqlite3_stmt *query; int result; library_directory_t directory; bool cb_result; if (!prepare_query(sql, &query)) { return; } sqlite3_bind_int64(query, 1, parent); while ((result = sqlite3_step(query)) == SQLITE_ROW) { directory.id = sqlite3_column_int64(query, 0); directory.path = (const char*)sqlite3_column_text(query, 1); directory.mtime = sqlite3_column_int64(query, 2); directory.parent = sqlite3_column_int64(query, 3); cb_result = callback(&directory, opaque); if (cb_result == false) { break; } } if (result != SQLITE_DONE && result != SQLITE_ROW) { musicd_log(LOG_ERROR, "library", "sqlite3_step failed for '%s'", sql); } sqlite3_finalize(query); }
int query(const char* query, char* received_data) { int result = SEND_SUCCESS; struct string response_message; if (!check_URL(query)) { return 0; } if (!prepare_query(query)) { return 0; } init_string(&response_message); curl_easy_setopt(curl, CURLOPT_WRITEFUNCTION, curl_write); curl_easy_setopt(curl, CURLOPT_WRITEDATA, response_message); CURLcode response = curl_easy_perform(curl); if (response != CURLE_OK) { result = SEND_FAILED; const char *error_msg = curl_easy_strerror(response); log_error("query(const char*, char*) %s", error_msg); } received_data = (char*) realloc (received_data, response_message.len); received_data = response_message.ptr; curl_easy_reset(curl); return result; }
int main(int argc, const char *argv[]) { struct addrinfo hints, *res; struct sockaddr_storage recv_addr; char buf[MAXBUFLEN]; int sockfd, recv_addr_len; char *cursor; int protocol; sqlite3 *db; int rc; char query[256]; sqlite3_stmt *ppStmt; if (argc != 2) { fprintf(stderr, "Usage: %s database\n", argv[0]); exit(1); } rc = sqlite3_open(argv[1], &db); if (rc) { fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db)); sqlite3_close(db); exit(1); } memset(&hints, 0, sizeof(hints)); hints.ai_family = AF_INET; hints.ai_socktype = SOCK_DGRAM; hints.ai_flags = AI_PASSIVE; getaddrinfo(NULL, PORT, &hints, &res); sockfd = socket(res->ai_family, res->ai_socktype, res->ai_protocol); bind(sockfd, res->ai_addr, res->ai_addrlen); for (;;) { memset(buf, 0, sizeof(buf)); cursor = buf; recvfrom(sockfd, buf, MAXBUFLEN-1, 0, (struct sockaddr *)&recv_addr, &recv_addr_len); if (prepare_query(cursor, query, sizeof(query))) { sqlite3_prepare_v2(db, query, sizeof(query), &ppStmt, NULL); sqlite3_step(ppStmt); sqlite3_finalize(ppStmt); } } sqlite3_close(db); freeaddrinfo(res); close(sockfd); exit(0); }
char *library_image_path(int64_t image) { static const char *sql = "SELECT files.path AS path FROM images JOIN files ON images.fileid = files.rowid WHERE images.rowid = ?"; sqlite3_stmt *query; int result; char *path = NULL;; if (!prepare_query(sql, &query)) { return NULL; } sqlite3_bind_int64(query, 1, image); result = sqlite3_step(query); if (result != SQLITE_DONE && result != SQLITE_ROW) { musicd_log(LOG_ERROR, "library", "sqlite3_step failed for '%s'", sql); } if (result == SQLITE_ROW) { path = strcopy((const char *)sqlite3_column_text(query, 0)); } sqlite3_finalize(query); return path; }
int64_t library_directory(const char* path, int64_t parent) { static const char *sql = "INSERT INTO directories (path, parentid) VALUES(?, ?)"; sqlite3_stmt *query; int64_t result; result = field_rowid("directories", "path", path); /* Result is nonzero (found or error) or parent < 0. */ if (result != 0 || parent < 0) { return result; } if (!prepare_query(sql, &query)) { return -1; } sqlite3_bind_text(query, 1, path, -1, NULL); sqlite3_bind_int64(query, 2, parent); result = execute_scalar(query); return result ? result : sqlite3_last_insert_rowid(db_handle()); }
char *library_directory_path(int64_t directory) { static const char *sql = "SELECT path FROM directories WHERE rowid = ?"; sqlite3_stmt *query; int result; char *path = NULL;; if (!prepare_query(sql, &query)) { return NULL; } sqlite3_bind_int64(query, 1, directory); result = sqlite3_step(query); if (result != SQLITE_DONE && result != SQLITE_ROW) { musicd_log(LOG_ERROR, "library", "sqlite3_step failed for '%s'", sql); } if (result == SQLITE_ROW) { path = strcopy((const char *)sqlite3_column_text(query, 0)); } sqlite3_finalize(query); return path; }
int main(int argc, char *argv[]) { CardDavRequest *request = (CardDavRequest *)malloc(sizeof(CardDavRequest)); if (argc >= 5) { request->url = argv[1]; request->method = argv[2]; request->body = argv[3]; request->depth = argv[4]; if (argc >= 7) { request->digest_auth_username = argv[5]; request->digest_auth_password = argv[6]; } } else { belle_sip_error("Usage: carddav_http_query <url> <method> <body> <depth> [username password]"); return 0; } /* Examples: "http://192.168.0.230/sabredav/addressbookserver.php/addressbooks/sylvain/default"; request->method = "PROPFIND"; request->body = "<d:propfind xmlns:d=\"DAV:\" xmlns:cs=\"http://calendarserver.org/ns/\"><d:prop><d:displayname /><cs:getctag /></d:prop></d:propfind>"; request->depth = "0"; request->url = "http://192.168.0.230/sabredav/addressbookserver.php/addressbooks/sylvain/default"; request->method = "REPORT"; request->body = "<card:addressbook-query xmlns:d=\"DAV:\" xmlns:card=\"urn:ietf:params:xml:ns:carddav\"><d:prop><d:getetag /><card:address-data content-type='text-vcard' version='4.0'/></d:prop></card:addressbook-query>"; request->depth = "1"; request->url = "http://192.168.0.230/sabredav/addressbookserver.php/addressbooks/sylvain/default"; request->method = "REPORT"; request->body = "<card:addressbook-multiget xmlns:d=\"DAV:\" xmlns:card=\"urn:ietf:params:xml:ns:carddav\"><d:prop><d:getetag /><card:address-data content-type='text-vcard' version='4.0'/></d:prop><d:href>/sabredav/addressbookserver.php/addressbooks/sylvain/default/me.vcf</d:href></card:addressbook-multiget>" request->depth = "1"; */ prepare_query(request); }
void library_iterate_images_by_album (int64_t album, bool (*callback)(library_image_t *file, void *opaque), void *opaque) { static const char *sql = "SELECT images.rowid AS id, files.path AS path, files.directoryid AS directoryid FROM images JOIN files ON images.fileid = files.rowid WHERE images.albumid = ?;"; sqlite3_stmt *query; int result; library_image_t image; bool cb_result = true; if (!prepare_query(sql, &query)) { return; } sqlite3_bind_int64(query, 1, album); image.album = album; while ((result = sqlite3_step(query)) == SQLITE_ROW) { image.id = sqlite3_column_int64(query, 0); image.path = (const char*)sqlite3_column_text(query, 1); image.directory = sqlite3_column_int64(query, 2); cb_result = callback(&image, opaque); if (cb_result == false) { break; } } if (result != SQLITE_DONE && result != SQLITE_ROW) { musicd_log(LOG_ERROR, "library", "sqlite3_step failed for '%s'", sql); } sqlite3_finalize(query); }
void library_iterate_files_by_directory (int64_t directory, bool (*callback)(library_file_t *file)) { static const char *sql = "SELECT rowid, path, mtime, directoryid FROM files WHERE directoryid = ?"; sqlite3_stmt *query; int result; library_file_t file; bool cb_result = true; if (!prepare_query(sql, &query)) { return; } sqlite3_bind_int64(query, 1, directory); while ((result = sqlite3_step(query)) == SQLITE_ROW) { file.id = sqlite3_column_int64(query, 0); file.path = (const char*)sqlite3_column_text(query, 1); file.mtime = sqlite3_column_int64(query, 2); file.directory = sqlite3_column_int64(query, 3); cb_result = callback(&file); if (cb_result == false) { break; } } if (result != SQLITE_DONE && result != SQLITE_ROW) { musicd_log(LOG_ERROR, "library", "sqlite3_step failed for '%s'", sql); } sqlite3_finalize(query); }
int64_t library_tracks_total() { sqlite3_stmt *query; if (!prepare_query("SELECT COUNT(rowid) FROM tracks", &query)) { return 0; } return execute_scalar(query); }
int64_t library_randomid() { sqlite3_stmt *query; if (!prepare_query("SELECT rowid FROM tracks ORDER BY RANDOM() LIMIT 1", &query)) { return 0; } return execute_scalar(query); }
time_t library_directory_mtime(int64_t directory) { static const char *sql = "SELECT mtime FROM directories WHERE rowid = ?"; sqlite3_stmt *query; if (!prepare_query(sql, &query)) { return -1; } sqlite3_bind_int64(query, 1, directory); return execute_scalar(query); }
int library_directory_tracks_count(int64_t directory) { static const char *sql = "SELECT COUNT(tracks.rowid) FROM directories JOIN files ON files.directoryid = directories.rowid JOIN tracks ON tracks.fileid = files.rowid WHERE directories.rowid = ?"; sqlite3_stmt *query; if (!prepare_query(sql, &query)) { return -1; } sqlite3_bind_int64(query, 1, directory); return execute_scalar(query); }
/** Assigns all the prepared statements (ever created within the session) to the given connection. Possibly modifies the value pointed by `stream'. */ bool cql::cql_session_impl_t::setup_prepared_statements( boost::shared_ptr<cql_connection_t> conn, cql_stream_t* stream) { if (!conn) { return false; } std::vector<std::vector<cql_byte_t> > unprepared; conn->get_unprepared_statements(unprepared); bool is_success = true; for (size_t i = 0u; i < unprepared.size(); ++i) { if (_prepare_statements.find(unprepared[i]) == _prepare_statements.end()) { // This should not happen: the connection was told to prepare a // statement, but session knows nothing about it. We'll skip this statement. is_success = false; continue; } std::string prepare_query_text = _prepare_statements[unprepared[i]]; boost::shared_ptr<cql_query_t> prepare_query( new cql_query_t(prepare_query_text)); prepare_query->set_stream(*stream); boost::shared_future<cql::cql_future_result_t> future_result = conn->prepare(prepare_query)->shared_future(); if (future_result.timed_wait(boost::posix_time::seconds(30))) { // TODO: set sensible (or none) time limit // The stream was released after receiving the body. Now we need to re-acquire it. *stream = conn->acquire_stream(); if (future_result.get().error.is_err()) { is_success = false; } else { // It's a good idea to check whether returned query_id matches the requested one. // If they don't - it would be a sign of a severe failure. BOOST_ASSERT(future_result.get().result->query_id() == unprepared[i]); } } else { is_success = false; } } return is_success; }
int64_t library_album_image(int64_t album) { static const char *sql = "SELECT imageid FROM albums WHERE rowid = ?"; sqlite3_stmt *query; if (!prepare_query(sql, &query)) { return 0; } sqlite3_bind_int64(query, 1, album); return execute_scalar(query); }
int64_t library_album_by_directory(int64_t directory) { static const char *sql = "SELECT tracks.albumid FROM directories JOIN files ON files.directoryid = directories.rowid JOIN tracks ON tracks.fileid = files.rowid WHERE directories.rowid = ? GROUP BY tracks.albumid ORDER BY COUNT(tracks.albumid) DESC LIMIT 1"; sqlite3_stmt *query; if (!prepare_query(sql, &query)) { return 0; } sqlite3_bind_int64(query, 1, directory); return execute_scalar(query); }
void library_album_image_set(int64_t album, int64_t image) { static const char *sql = "UPDATE albums SET imageid = ? WHERE rowid = ?"; sqlite3_stmt *query; if (!prepare_query(sql, &query)) { return; } sqlite3_bind_int64(query, 1, image); sqlite3_bind_int64(query, 2, album); execute(query); }
void library_directory_mtime_set(int64_t directory, time_t mtime) { static const char *sql = "UPDATE directories SET mtime = ? WHERE rowid = ?"; sqlite3_stmt *query; if (!prepare_query(sql, &query)) { return; } sqlite3_bind_int64(query, 1, mtime); sqlite3_bind_int64(query, 2, directory); execute(query); }
void load_db_e3ds(){ /** public function - see header */ log_event(EVENT_INITIALISATION, "loading e3d..."); sqlite3_stmt *stmt; //check database is open and table exists check_db_open(GET_CALL_INFO); check_table_exists("E3D_TABLE", GET_CALL_INFO); char *sql="SELECT * FROM E3D_TABLE"; prepare_query(sql, &stmt, GET_CALL_INFO); //read the sql query result into the e3d array int i=0; int rc=0; while ( (rc = sqlite3_step(stmt)) == SQLITE_ROW) { //get the object id and check that the value does not exceed the maximum permitted int id=sqlite3_column_int(stmt,0); if(id>MAX_E3D_TYPES){ log_event(EVENT_ERROR, "id [%i] exceeds range [%i] in function %s: module %s: line %i", id, MAX_E3D_TYPES, GET_CALL_INFO); stop_server(); } //handle null string which would crash strcpy if(sqlite3_column_text(stmt, 1)) strcpy(e3ds.e3d[id].e3d_filename, (char*)sqlite3_column_text(stmt, 1)); e3ds.e3d[id].object_id=sqlite3_column_int(stmt, 2); log_event(EVENT_INITIALISATION, "loaded [%i] [%s]", id, e3ds.e3d[id].e3d_filename); i++; } destroy_query(sql, &stmt, GET_CALL_INFO); if(i==0){ log_event(EVENT_ERROR, "no e3ds found in database", i); stop_server(); } }
void library_image_album_set_by_directory(int64_t directory, int64_t album) { static const char *sql = "UPDATE images SET albumid = ? WHERE fileid IN (SELECT rowid FROM files WHERE directoryid = ?)"; sqlite3_stmt *query; if (!prepare_query(sql, &query)) { return; } sqlite3_bind_int64(query, 1, album); sqlite3_bind_int64(query, 2, directory); execute(query); }
void library_file_delete(int64_t file) { static const char *sql = "DELETE FROM files WHERE rowid = ?"; sqlite3_stmt *query; library_file_clear(file); if (!prepare_query(sql, &query)) { return; } sqlite3_bind_int64(query, 1, file); execute(query); }
void library_directory_delete(int64_t directory) { static const char *sql = "DELETE FROM directories WHERE rowid = ?"; sqlite3_stmt *query; library_iterate_files_by_directory(directory, delete_files_cb); library_iterate_directories(directory, delete_directories_cb, NULL); if (!prepare_query(sql, &query)) { return; } sqlite3_bind_int64(query, 1, directory); execute(query); }
static void increment_album_tracks(int64_t album) { static const char *sql = "UPDATE albums SET tracks = tracks + 1 WHERE rowid = ?"; sqlite3_stmt *query; if (!prepare_query(sql, &query)) { return; } sqlite3_bind_int64(query, 1, album); execute(query); }
void load_db_char_races(){ /** public function - see header */ //check database is open and table exists check_db_open(GET_CALL_INFO); check_table_exists("RACE_TABLE", GET_CALL_INFO); log_event(EVENT_INITIALISATION, "loading races..."); sqlite3_stmt *stmt; char *sql="SELECT * FROM RACE_TABLE"; prepare_query(sql, &stmt, GET_CALL_INFO); //read the sql query result into the race array int i=0; int rc=0; while ( (rc = sqlite3_step(stmt)) == SQLITE_ROW) { int race_id=sqlite3_column_int(stmt, 0); if(race_id>MAX_RACES) { log_event(EVENT_ERROR, "race_id [%i] exceeds max [%i] in function %s: module %s: line %i", race_id, MAX_RACES, GET_CALL_INFO); stop_server(); } //handle null string which would crash strcpy if(sqlite3_column_text(stmt, 1)) strcpy(races.race[race_id].race_name, (char*)sqlite3_column_text(stmt, 1)); //handle null string which would crash strcpy if(sqlite3_column_text(stmt, 2)) strcpy(races.race[race_id].race_description, (char*)sqlite3_column_text(stmt, 2)); log_event(EVENT_INITIALISATION, "loaded [%i] [%s]", race_id, races.race[race_id].race_name); i++; } destroy_query(sql, &stmt, GET_CALL_INFO); if(i==0){ log_event(EVENT_ERROR, "no races found in database", i); stop_server(); } }
int64_t library_track_add(track_t *track, int64_t directory) { static const char *sql = "INSERT INTO tracks (fileid, file, cuefileid, cuefile, track, title, artistid, artist, albumid, album, start, duration, trackindex) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"; sqlite3_stmt *query; if (!prepare_query(sql, &query)) { return -1; } track->fileid = library_file(track->file, directory); if (track->cuefile) { track->cuefileid = library_file(track->cuefile, directory); } if (track->artist) { track->artistid = field_rowid_create("artists", "name", track->artist); } if (track->album) { track->albumid = field_rowid_create("albums", "name", track->album); } sqlite3_bind_int64(query, 1, track->fileid); sqlite3_bind_text(query, 2, track->file, -1, NULL); sqlite3_bind_int64(query, 3, track->cuefileid); sqlite3_bind_text(query, 4, track->cuefile, -1, NULL); sqlite3_bind_int(query, 5, track->track); sqlite3_bind_text(query, 6, track->title, -1, NULL); sqlite3_bind_int64(query, 7, track->artistid); sqlite3_bind_text(query, 8, track->artist, -1, NULL); sqlite3_bind_int64(query, 9, track->albumid); sqlite3_bind_text(query, 10, track->album, -1, NULL); sqlite3_bind_double(query, 11, track->start); sqlite3_bind_double(query, 12, track->duration); sqlite3_bind_double(query, 13, track->trackindex); if (!execute(query)) { return -1; } if (track->album) { increment_album_tracks(track->albumid); } return sqlite3_last_insert_rowid(db_handle()); }
void library_lyrics_set(int64_t track, lyrics_t *lyrics) { static const char *sql = "INSERT OR REPLACE INTO lyrics (trackid, lyrics, provider, source, mtime) VALUES(?, ?, ?, ?, ?)"; sqlite3_stmt *query; if (!prepare_query(sql, &query)) { return; } sqlite3_bind_int64(query, 1, track); sqlite3_bind_text(query, 2, lyrics ? lyrics->lyrics : NULL, -1, NULL); sqlite3_bind_text(query, 3, lyrics ? lyrics->provider : NULL, -1, NULL); sqlite3_bind_text(query, 4, lyrics ? lyrics->source : NULL, -1, NULL); sqlite3_bind_int64(query, 5, time(NULL)); execute(query); }
static int64_t field_rowid(const char *table, const char *field, const char *value) { sqlite3_stmt *query; int64_t result; char *sql = malloc(strlen(table) + strlen(field) + 32); sprintf(sql, "SELECT rowid FROM %s WHERE %s = ?", table, field); if (!prepare_query(sql, &query)) { result = -1; } else { sqlite3_bind_text(query, 1, value, -1, NULL); result = execute_scalar(query); } free(sql); return result; }
void run_select_queries(void* data) { int i; CassSession* session = (CassSession*)data; const CassPrepared* select_prepared = NULL; const char* select_query = "SELECT * FROM songs WHERE id = a98d21b2-1900-11e4-b97b-e5e358e71e0d"; #if USE_PREPARED if (prepare_query(session, select_query, &select_prepared) == CASS_OK) { #endif for (i = 0; i < NUM_ITERATIONS; ++i) { select_from_perf(session, select_query, select_prepared); } #if USE_PREPARED cass_prepared_free(select_prepared); } #endif status_notify(&status); }