int
pkgdb_repo_init(sqlite3 *sqlite)
{
int retcode = EPKG_OK;
retcode = sql_exec(sqlite, "PRAGMA synchronous=off");
if (retcode != EPKG_OK)
return (retcode);
retcode = sql_exec(sqlite, "PRAGMA journal_mode=memory");
if (retcode != EPKG_OK)
return (retcode);
retcode = sql_exec(sqlite, "PRAGMA foreign_keys=on");
if (retcode != EPKG_OK)
return (retcode);
retcode = initialize_prepared_statements(sqlite);
if (retcode != EPKG_OK)
return (retcode);
retcode = pkgdb_transaction_begin(sqlite, NULL);
if (retcode != EPKG_OK)
return (retcode);
return (EPKG_OK);
}
int
pkg_repo_binary_init(struct pkg_repo *repo)
{
int retcode = EPKG_OK;
sqlite3 *sqlite = PRIV_GET(repo);
sqlite3_create_function(sqlite, "file_exists", 2, SQLITE_ANY, NULL,
sqlite_file_exists, NULL, NULL);
retcode = sql_exec(sqlite, "PRAGMA synchronous=default");
if (retcode != EPKG_OK)
return (retcode);
retcode = sql_exec(sqlite, "PRAGMA foreign_keys=on");
if (retcode != EPKG_OK)
return (retcode);
pkgdb_sqlcmd_init(sqlite, NULL, NULL);
retcode = pkg_repo_binary_init_prstatements(sqlite);
if (retcode != EPKG_OK)
return (retcode);
repo->priv = sqlite;
return (EPKG_OK);
}
STDMETHODIMP_(void) CDbxSQLite::SetCacheSafetyMode(BOOL safeMode)
{
if (safeMode)
sql_exec(m_sqlite, "PRAGMA synchronous = NORMAL;");
else
sql_exec(m_sqlite, "PRAGMA synchronous = OFF;");
}
void
account_delete_char(struct account *account, struct creature *ch)
{
void remove_bounties(int);
int idx, count;
PGresult *res;
clear_clan_owner(GET_IDNUM(ch));
remove_char_clan(GET_CLAN(ch), GET_IDNUM(ch));
// TODO: Remove character from any access groups
sql_exec("delete from sgroup_members where player=%ld", GET_IDNUM(ch));
// Remove character from any quests they might have joined
if (GET_QUEST(ch)) {
struct quest *quest = quest_by_vnum(GET_QUEST(ch));
if (quest) {
remove_quest_player(quest, GET_IDNUM(ch));
save_quests();
}
}
// Remove character from trusted lists - we have to take the accounts
// in memory into consideration when we do this, so we have to go
// through each account
res =
sql_query("select account from trusted where player=%ld",
GET_IDNUM(ch));
count = PQntuples(res);
for (idx = 0; idx < count; idx++) {
struct account *acct = account_by_idnum(atoi(PQgetvalue(res, idx, 0)));
if (acct)
account_distrust(acct, GET_IDNUM(ch));
}
// Remove from the bounty list
remove_bounties(GET_IDNUM(ch));
sql_exec("delete from bounty_hunters where idnum=%ld or victim=%ld",
GET_IDNUM(ch), GET_IDNUM(ch));
// Disassociate author from board messages
sql_exec("update board_messages set author=null where author=%ld",
GET_IDNUM(ch));
// Remove character from account
account->chars = g_list_remove(account->chars,
GINT_TO_POINTER(GET_IDNUM(ch)));
sql_exec("delete from players where idnum=%ld", GET_IDNUM(ch));
// Remove character from game
if (ch->in_room) {
send_to_char(ch,
"A cold wind blows through your soul, and you disappear!\r\n");
creature_purge(ch, false);
}
}
INT_PTR utils_setSafetyMode(WPARAM wParam, LPARAM lParam) {
int safeMode = (int)wParam;
log1("db_setSafetyMode: %s", safeMode?"On":"Off");
if (safeMode)
sql_exec(g_sqlite, "PRAGMA synchronous = NORMAL;");
else
sql_exec(g_sqlite, "PRAGMA synchronous = OFF;");
return 0;
}
int
insert_playlist(const char * path, char * name)
{
struct song_metadata plist;
struct stat file;
int items = 0, matches, ret;
char type[4];
strncpy(type, strrchr(name, '.')+1, 4);
if( start_plist(path, NULL, &file, NULL, type) != 0 )
{
DPRINTF(E_WARN, L_SCANNER, "Bad playlist [%s]\n", path);
return -1;
}
while( (ret = next_plist_track(&plist, &file, NULL, type)) == 0 )
{
items++;
freetags(&plist);
}
if( ret == 2 ) // Bad playlist -- contains binary characters
{
DPRINTF(E_WARN, L_SCANNER, "Bad playlist [%s]\n", path);
return -1;
}
strip_ext(name);
DPRINTF(E_DEBUG, L_SCANNER, "Playlist %s contains %d items\n", name, items);
matches = sql_get_int_field(db, "SELECT count(*) from PLAYLISTS where NAME = '%q'", name);
if( matches > 0 )
{
sql_exec(db, "INSERT into PLAYLISTS"
" (NAME, PATH, ITEMS) "
"VALUES"
" ('%q(%d)', '%q', %d)",
name, matches, path, items);
}
else
{
sql_exec(db, "INSERT into PLAYLISTS"
" (NAME, PATH, ITEMS) "
"VALUES"
" ('%q', '%q', %d)",
name, path, items);
}
sql_exec(db, "INSERT into DETAILS"
" (PATH, TIMESTAMP, MIME) "
"VALUES"
" ('%q', %ld, 'audio/x-mpegurl')",
path, 0);
return 0;
}
int addFile(string ID, string path){
string sql="SELECT MAX(fid) FROM filelist;";
int fid=0;
cout<<sql<<endl;
sql_exec(sql);
showResult();
if(result[0][string("MAX(fid)")]!="NULL")fid=1+atoi(result[0][string("MAX(fid)")].c_str());
sql="INSERT INTO filelist (fid,author,path) VALUES('"+toString(fid)+"', '"+ID+"', '"+path+"');";
cout<<sql<<endl;
sql_exec(sql);
showResult();
printf("fid = %d\n", fid);
return fid;
}
int insertUser(Account acc){
string sql="SELECT * FROM user WHERE ID='"+acc.ID+"';";
cout<<sql<<endl;
sql_exec(sql);
if(result.size()){
showResult();
return 0;
}
sql="INSERT INTO user (ID, pw) VALUES('"+acc.ID+"', '"+acc.pw+"');";
sql_exec(sql);
showResult();
return 1;
}
void
gen_board_save(struct creature *ch, const char *board, int idnum,
const char *subject, const char *body)
{
if (idnum < 0)
sql_exec
("insert into board_messages (board, post_time, author, name, subject, body) values ('%s', now(), %ld, '%s', '%s', '%s')",
tmp_sqlescape(board), GET_IDNUM(ch),
tmp_sqlescape(tmp_capitalize(GET_NAME(ch))),
tmp_sqlescape(subject), tmp_sqlescape(body));
else
sql_exec
("update board_messages set post_time=now(), subject='%s', body='%s' where idnum=%d",
tmp_sqlescape(subject), tmp_sqlescape(body), idnum);
}
void
account_set_metric(struct account *account, bool metric)
{
account->metric_units = metric;
sql_exec("update accounts set metric_units='%s' where idnum=%d",
metric ? "T" : "F", account->id);
}
void
account_set_quest_banned(struct account *account, bool banned)
{
account->quest_banned = banned;
sql_exec("update accounts set quest_banned='%s' where idnum=%d",
banned ? "T" : "F", account->id);
}
void
account_set_compact_level(struct account *account, int level)
{
account->compact_level = level;
sql_exec("update accounts set compact_level=%d where idnum=%d",
account->compact_level, account->id);
}
/* This routine inserts the cookie into table_cookie. We log arrival of
* the message (done=0). */
void sub_sql_tagmsg(struct subdbinfo *info,
unsigned long msgnum, /* number of this message */
const char *hashout, /* previously calculated hash */
unsigned long bodysize,
unsigned long chunk)
{
const char *ret;
char strnum[FMT_ULONG];
if (chunk >= 53L) chunk = 0L; /* sanity */
/* INSERT INTO table_cookie (msgnum,tai,cookie,bodysize,chunk) VALUES (...) */
/* (we may have tried message before, but failed to complete, so */
/* ER_DUP_ENTRY is ok) */
stralloc_copys(&query,"INSERT INTO ");
stralloc_cats(&query,info->base_table);
stralloc_cats(&query,"_cookie (msgnum,tai,cookie,bodysize,chunk) VALUES ");
stralloc_cats(&query,sql_tagmsg_values_defn);
stralloc_copyb(¶ms[0],strnum,fmt_ulong(strnum,msgnum));
stralloc_copyb(¶ms[1],hashout,COOKIE);
stralloc_copyb(¶ms[2],strnum,fmt_ulong(strnum,bodysize));
stralloc_copyb(¶ms[3],strnum,fmt_ulong(strnum,chunk));
sql_exec(info,&query,4,params); /* ignore dups */
if (! (ret = logmsg(msgnum,0L,0L,1)))
return; /* log done=1*/
if (*ret) strerr_die2x(111,FATAL,ret);
}
void
account_exhume_char(struct account *account, struct creature *exhumer, long id)
{
if (player_idnum_exists(id)) {
send_to_char(exhumer, "That character has already been exhumed.\r\n");
return;
}
// load char from file
struct creature *victim;
victim = load_player_from_xml(id);
if (victim) {
sql_exec
("insert into players (idnum, account, name) values (%ld, %d, '%s')",
GET_IDNUM(victim), account->id, tmp_sqlescape(GET_NAME(victim)));
load_players(account);
send_to_char(exhumer, "%s exhumed.\r\n",
tmp_capitalize(GET_NAME(victim)));
slog("%s[%ld] exhumed into account %s[%d]", GET_NAME(victim),
GET_IDNUM(victim), account->name, account->id);
free_creature(victim);
} else {
send_to_char(exhumer, "Unable to load character %ld.\r\n", id);
}
}
char *config_get_fqdn()
{
char *sql = NULL, *fqdn = NULL, *tmp = NULL;
int result_size = 0, err_code = 0, fqdn_size = 0;
sql = sqlite3_mprintf("SELECT value FROM %s WHERE key = %Q LIMIT 1",CONFIG_TABLE,FQDN_NAME);
fqdn = sql_exec(sql,&result_size,&err_code);
sqlite3_free(sql);
if(err_code != SQLITE_OK){
sql_log_error();
fqdn = NULL;
} else if(fqdn){
tmp = fqdn;
fqdn_size = strlen(fqdn) + 2;
fqdn = realloc(fqdn,fqdn_size);
if(!fqdn){
perror("Malloc failure");
if(tmp) free(tmp);
} else {
memset((fqdn+fqdn_size-2),'.',1);
memset((fqdn+fqdn_size-1),'\0',1);
}
}
return fqdn;
}
void
account_distrust(struct account *account, long idnum)
{
account->trusted = g_list_remove(account->trusted, GINT_TO_POINTER(idnum));
sql_exec("delete from trusted where account=%d and player=%ld",
account->id, idnum);
}
// bool QmvSqlTuple::loadForeign( const QString & att_name )
// {
// QmvTupleAttribute *
bool QmvSqlTuple::purge()
{
QString query;
const QString & cn = parent_class->relationName();
const QString & pk = parent_class->serialKey();
const QString & pkv = this->attributeValue(pk);
int rows;
if (pkv) // This tuple exists in persistent storage.
{
query = QString("DELETE FROM %1 WHERE %2 = '%3'::%4")
.arg(cn)
.arg(pk)
.arg(pkv)
.arg(parent_class->attAttValue(pk, QString("mtat_type")));
rows = sql_exec(query);
if (rows < 0 )
{
qDebug("QmvSqlTuple::purge():lastError = <%s>", lastError().latin1() );
return(FALSE);
}
else
return(TRUE);
}
return(TRUE);
}
int64_t
find_album_art(const char *path, const char *image_data, int image_size)
{
char *album_art = NULL;
char *sql;
char **result;
int cols, rows;
int64_t ret = 0;
if( (image_size && (album_art = check_embedded_art(path, image_data, image_size))) ||
(album_art = check_for_album_file(path)) )
{
sql = sqlite3_mprintf("SELECT ID from ALBUM_ART where PATH = '%q'", album_art ? album_art : path);
if( (sql_get_table(db, sql, &result, &rows, &cols) == SQLITE_OK) && rows )
{
ret = strtoll(result[1], NULL, 10);
}
else
{
if( sql_exec(db, "INSERT into ALBUM_ART (PATH) VALUES ('%q')", album_art) == SQLITE_OK )
ret = sqlite3_last_insert_rowid(db);
}
sqlite3_free_table(result);
sqlite3_free(sql);
}
free(album_art);
return ret;
}
void
account_set_trust(struct account *account, int trust)
{
account->trust = trust;
sql_exec("update accounts set trust=%d where idnum=%d",
trust, account->id);
}
static int
pkg_repo_register(struct pkg_repo *repo, sqlite3 *sqlite)
{
sqlite3_stmt *stmt;
const char sql[] = ""
"INSERT OR REPLACE INTO repodata (key, value) "
"VALUES (\"packagesite\", ?1);";
/* register the packagesite */
if (sql_exec(sqlite, "CREATE TABLE IF NOT EXISTS repodata ("
" key TEXT UNIQUE NOT NULL,"
" value TEXT NOT NULL"
");") != EPKG_OK) {
pkg_emit_error("Unable to register the packagesite in the "
"database");
return (EPKG_FATAL);
}
if (sqlite3_prepare_v2(sqlite, sql, -1, &stmt, NULL) != SQLITE_OK) {
ERROR_SQLITE(sqlite);
return (EPKG_FATAL);
}
sqlite3_bind_text(stmt, 1, pkg_repo_url(repo), -1, SQLITE_STATIC);
if (sqlite3_step(stmt) != SQLITE_DONE) {
ERROR_SQLITE(sqlite);
sqlite3_finalize(stmt);
return (EPKG_FATAL);
}
sqlite3_finalize(stmt);
return (EPKG_OK);
}
void
account_set_future_bank(struct account *account, money_t amt)
{
account->bank_future = amt;
sql_exec("update accounts set bank_future=%" PRId64 " where idnum=%d",
account->bank_future, account->id);
}
void deleteUser(Account acc){
string sql="DELETE FROM user WHERE ID='"+acc.ID+"' AND pw='"+acc.pw+"';";
sql_exec(sql);
showResult();// print nothing
// del file
deleteFile(acc.ID);
}
static int init(sqlite3 *db, const char *db_name, errorstream_t *error) {
int result = SQLITE_OK;
const table_info_t *const *table = gpkg_tables;
result = sql_exec(db, "PRAGMA application_id = %d", 0x47503130);
if (result != SQLITE_OK) {
error_append(error, "Could not set application_id");
}
if (result == SQLITE_OK) {
while (*table != NULL) {
result = sql_init_table(db, db_name, *table, error);
if (result != SQLITE_OK) {
break;
}
table++;
}
}
if (result == SQLITE_OK && error_count(error) > 0) {
result = SQLITE_ERROR;
}
return result;
}
void
account_initialize(struct account *account,
const char *name,
struct descriptor_data *d,
int idnum)
{
account->id = idnum;
account->name = strdup(name);
if (strlen(account->name) > 20)
account->name[20] = '\0';
account->password = NULL;
account->creation_time = account->login_time = time(NULL);
account->creation_addr = strdup(d->host);
account->login_addr = strdup(d->host);
account->ansi_level = 0;
account->compact_level = 0;
account->term_height = 22;
account->term_width = 80;
account->bank_past = 0;
account->bank_future = 0;
account->banned = false;
account->reputation = 0;
account->quest_points = 0;
account->quest_banned = false;
account->metric_units = false;
account->trust = 0;
slog("new account: %s[%d] from %s", account->name, idnum, d->host);
sql_exec
("insert into accounts (idnum, name, creation_time, creation_addr, login_time, login_addr, ansi_level, compact_level, term_height, term_width, reputation, bank_past, bank_future) values (%d, '%s', now(), '%s', now(), '%s', 0, 0, %d, %d, 0, 0, 0)",
idnum, tmp_sqlescape(name), tmp_sqlescape(d->host),
tmp_sqlescape(d->host), DEFAULT_TERM_HEIGHT, DEFAULT_TERM_WIDTH);
}
void
account_update_last_entry(struct account *account)
{
account->entry_time = time(NULL);
sql_exec("update accounts set entry_time=now() where idnum=%d",
account->id);
}
static void __unused
pkg_repo_binary_parse_conflicts(FILE *f, sqlite3 *sqlite)
{
size_t linecap = 0;
ssize_t linelen;
char *linebuf = NULL, *p, **deps;
const char *origin, *pdep;
int ndep, i;
const char conflicts_clean_sql[] = ""
"DELETE FROM pkg_conflicts;";
pkg_debug(4, "pkg_parse_conflicts_file: running '%s'", conflicts_clean_sql);
(void)sql_exec(sqlite, conflicts_clean_sql);
while ((linelen = getline(&linebuf, &linecap, f)) > 0) {
p = linebuf;
origin = strsep(&p, ":");
/* Check dependencies number */
pdep = p;
ndep = 1;
while (*pdep != '\0') {
if (*pdep == ',')
ndep ++;
pdep ++;
}
deps = malloc(sizeof(char *) * ndep);
for (i = 0; i < ndep; i ++) {
deps[i] = strsep(&p, ",\n");
}
pkg_repo_binary_register_conflicts(origin, deps, ndep, sqlite);
free(deps);
}
free(linebuf);
}
/* Creates an entry for message num and the list listno and code "done".
* Returns NULL on success, and the error string on error. */
const char *sub_sql_logmsg(struct subdbinfo *info,
unsigned long num,
unsigned long listno,
unsigned long subs,
int done)
{
char *s;
char strnum[FMT_ULONG];
stralloc_copys(&query,"INSERT INTO ");
stralloc_cats(&query,info->base_table);
stralloc_cats(&query,"_mlog (msgnum,listno,subs,done) VALUES ");
stralloc_cats(&query,sql_logmsg_values_defn);
stralloc_copyb(¶ms[0],strnum,fmt_ulong(strnum,num));
stralloc_copyb(¶ms[1],strnum,fmt_ulong(strnum,listno));
stralloc_copyb(¶ms[2],strnum,fmt_ulong(strnum,subs));
s = strnum;
if (done < 0) {
done = - done;
*s++ = '-';
}
s[fmt_uint(s,done)] = 0;
stralloc_copys(¶ms[3],s);
sql_exec(info,&query,4,params); /* ignore dups */
return 0;
}
static int
pkg_repo_binary_init_update(struct pkg_repo *repo, const char *name)
{
sqlite3 *sqlite;
const char update_check_sql[] = ""
"INSERT INTO repo_update VALUES(1);";
const char update_start_sql[] = ""
"CREATE TABLE IF NOT EXISTS repo_update (n INT);";
/* [Re]create repo */
unlink(name);
if (repo->ops->create(repo) != EPKG_OK) {
pkg_emit_notice("Unable to create repository %s", repo->name);
return (EPKG_FATAL);
}
if (repo->ops->open(repo, R_OK|W_OK) != EPKG_OK) {
pkg_emit_notice("Unable to open created repository %s", repo->name);
return (EPKG_FATAL);
}
repo->ops->init(repo);
sqlite = PRIV_GET(repo);
if(sqlite3_exec(sqlite, update_check_sql, NULL, NULL, NULL) == SQLITE_OK) {
pkg_emit_notice("Previous update has not been finished, restart it");
return (EPKG_END);
}
else {
sql_exec(sqlite, update_start_sql);
}
return (EPKG_OK);
}
/*
* Dump all tables, indexes and sequences in the current database.
*/
void
sql_exec_dumpalltables(PGconn *conn, struct options * opts)
{
char todo[1024];
char *addfields = ",c.oid AS \"Oid\", nspname AS \"Schema\", spcname as \"Tablespace\" ";
snprintf(todo, sizeof(todo),
"SELECT pg_catalog.pg_relation_filenode(c.oid) as \"Filenode\", relname as \"Table Name\" %s "
"FROM pg_class c "
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace "
" LEFT JOIN pg_catalog.pg_database d ON d.datname = pg_catalog.current_database(),"
" pg_catalog.pg_tablespace t "
"WHERE relkind IN ('r'%s%s) AND "
" %s"
" t.oid = CASE"
" WHEN reltablespace <> 0 THEN reltablespace"
" ELSE dattablespace"
" END "
"ORDER BY relname",
opts->extended ? addfields : "",
opts->indexes ? ", 'i', 'S'" : "",
opts->systables ? ", 't'" : "",
opts->systables ? "" : "n.nspname NOT IN ('pg_catalog', 'information_schema') AND n.nspname !~ '^pg_toast' AND");
sql_exec(conn, todo, opts->quiet);
}
void test_db_setup()
{
db_open("muddytest.db", NULL);
char buf[BUF_SIZ];
sprintf(buf, "create table if not exists "DBNAME"(testId integer not null primary key autoincrement,"
"name varchar, intval integer)");
if (sql_exec(buf) != SQL_OK)
{
fail("Could not create "DBNAME" table");
}
if (sql_exec("delete from " DBNAME) != SQL_OK)
fail("could not delete from table");
}
