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);
}
