自学教程：C++ sql_exec函数代码示例

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

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

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

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

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

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

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

voidaccount_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(&params[0],strnum,fmt_ulong(strnum,msgnum));  stralloc_copyb(&params[1],hashout,COOKIE);  stralloc_copyb(&params[2],strnum,fmt_ulong(strnum,bodysize));  stralloc_copyb(&params[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);}

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

voidaccount_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_tfind_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;}

voidaccount_set_trust(struct account *account, int trust){    account->trust = trust;    sql_exec("update accounts set trust=%d where idnum=%d",             trust, account->id);}

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

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

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

voidaccount_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 __unusedpkg_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(&params[0],strnum,fmt_ulong(strnum,num));  stralloc_copyb(&params[1],strnum,fmt_ulong(strnum,listno));  stralloc_copyb(&params[2],strnum,fmt_ulong(strnum,subs));  s = strnum;  if (done < 0) {    done = - done;    *s++ = '-';  }  s[fmt_uint(s,done)] = 0;  stralloc_copys(&params[3],s);  sql_exec(info,&query,4,params); /* ignore dups */  return 0;}

static intpkg_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. */voidsql_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");}