void mysql_idle_prevent()
{
if (time_passed_ms(mysql_last_time) < MYSQL_IDLE_TIMEOUT)
return;
db_select("select now()");
}
/* @return 0 if error occurs, else the id of table */
uint8_t dbms_select(uint8_t table_id, uint8_t scheme[], uint8_t size)
{
// get old database
database *db = db_list_get_table(table_id);
// unknown id
if(!db) {
#ifdef DEBUG_DBMS
printf("ERROR: dbms_select: unknown table with id %d!\n\r", table_id);
#endif //DEBUG_DBMS
return 0;
}
// create new database
database *new_db = db_select(table_id, db, scheme, size);
// memory full
if(!new_db) {
#ifdef DEBUG_DBMS
printf("ERROR: dbms_select: error while creating new database!\n\r");
#endif //DEBUG_DBMS
return 0;
}
// replace old database with new
db_drop(db_list_remove_table(table_id));
db_list_add_table(new_db);
return table_id;
}
int do_reconnects(char *sql) {
int m0, m1, m2 = 0, i;
//sql="select table_name from all_tables";
m0 = m1 = os_mem_used();
//int do_smth=0;
//fprintf(stderr," start reconnects with mem %d KB\n", m1/1024);
for(i=1;i<100*1000;i++) {
db_done(db); // clear prev
if (!db_connect_string(db,cs)) {
fprintf(stderr,"-reconnect failed on %d step err=%s\n",i,db->error);
msleep(1000); continue;
//return 0;
}
if (sql) {
if (!db_select(db,sql)) {
fprintf(stderr,"-select failed on step %d err=%s\n",i,db->error);
return 0;
}
//while(db_fetch(db)); // Fetch all
}
m2 = os_mem_used();
printf(" .. reconnect: %d mem_used: %d KB delta= %d Bytes \r",i,m2/1024, (m2-m1));
if (i%100==0) { m1=m2; printf("\n"); }
}
fprintf(stderr,"+%d reconnects done, leak: %d\n",i, (m2-m0));
return 0;
}
int process_get_public_key(CLIENT *client, const VCRYPT_PACKET *packet)
{
MYSQL_RES *res = db_select("select public_key from users where username=%s",
packet->username);
VCRYPT_PACKET *ret_packet;
if (res) {
MYSQL_ROW row = mysql_fetch_row(res);
unsigned long *lengths = mysql_fetch_lengths(res);
if (row) {
if (row[0]) {
ret_packet = packet_new(DEST_SERVER, NULL, RESP_OK, lengths[0]);
memcpy(ret_packet->payload, row[0], lengths[0]);
} else {
ret_packet = packet_new(DEST_SERVER, NULL, RESP_PUBLIC_KEY_NONE,
0);
}
} else {
ret_packet = packet_new(DEST_SERVER, NULL, RESP_ERR_NOSUCHUSER, 0);
}
} else {
ret_packet = packet_new(DEST_SERVER, NULL, RESP_ERR_TEMPORARY, 0);
}
ret_packet->queue_id = packet->queue_id;
int retval = packet_send_client(client, ret_packet);
packet_free(ret_packet);
return retval;
}
int do_stress_fetch(char *sql) {
int m0, m1, m2 = 0, i,p1,p2;
time_t t1,t2;
m0 = m1 = os_mem_used();
time(&TimeNow); t1=t2 = TimeNow;
p1=p2=0;
//int do_smth=0;
fprintf(stderr," start stress_fetch with mem %d KB, SQL:%s\n", m1/1024,sql);
for(i=0;i<100*10000;i++) {
int skip;
time(&TimeNow);
if (!db_select(db,sql)) {
fprintf(stderr,"-select failed on step %d err=%s\n",i,db->error);
return 0;
}
p2++;
while(db_fetch(db)) ; //p1++; // Fetch all
m2 = os_mem_used(); t2 = TimeNow;
skip = t2!=t1; // New Second
if (skip || (i%100 ==0))
printf(" .. stress_fetch: %d mem_used: %d KB delta= %d Bytes time=%d fetched:%d \r",i,m2/1024, (m2-m1), (int)(t2-t1),(p2-p1));
if (skip) {
m1=m2; t1 = t2; p1=p2; printf("\n");
}
}
fprintf(stderr,"+%d stress fetch done, leak: %d\n",i, (m2-m0));
return 0;
}
void *query_menu(int x, int format_x, char *sql, FUNCP callback, void *cb_arg, int col, ...)
{
va_list field;
va_start(field, col);
int col_type[col];
for (int i = 0; i < col; ++i) {
col_type[i] = va_arg(field, int);
}
char **res = db_select(get_db_main(), sql, col, col_type);
if (callback) {
struct daily_total *total = (struct daily_total *)callback(res, cb_arg);
print_notice("今日截止目前共%d笔生意 总收入%.1f 总成本%.1f 总盈利%.1f\n"
"平均每桌盈利%.1f",
total->cnt,
total->sales,
total->cost,
total->profil,
total->profil / total->cnt);
}
mt.x = x;
mt.format_x = format_x;
mt.choice = res;
mt.choice_n = get_sql_item_cnt();
mt.win = get_win(W_MID);
mt.format_y = LINES - 3;
menu_interact(&mt);
free(res);
return NULL;
}
int db_config_db(database *db) { // configure database by by a tables (nextN !!!)
if (db_select(db,"select TYP_SEQ from db where n = 0") && db_fetch(db)) { // Configure a db from db_info
db_col *c = db->out.cols;
db->typ_seq = db_int(c); // My Next N type
}
return 0;
}
static void select_func(uint64_t index) {
int32_t value;
db_select(test_data[index].id, &value);
if (value != test_data[index].value) {
printf("Invalid value selected!\n");
if (exit_on_error) exit(EXIT_FAILURE);
}
}
int db_count(MYSQL *pstMySQL, char *query)
{
int dRet;
MYSQL_ROW row;
MYSQL_RES *pstRst;
if( (dRet = db_select(pstMySQL,query,&pstRst)) <= 0 ){
return dRet;
}
if( dRet > 1 ){
return E_DB_ROW_OVER;
}
while( (row = mysql_fetch_row(pstRst)) != NULL ){
dRet = (int) atoi(row[0]);
}
mysql_free_result(pstRst);
return dRet;
}
static int setup_create_db(const char *host, const char *user,
const char *pass, const char *name)
{
char name_buf[512];
db_escape(name_buf, name, 256);
db_query(0, "CREATE DATABASE %s", name_buf);
if(setup_err)
{
printf("Could not create database.\n");
return -1;
}
if(db_select(0, name) != 0)
{
printf("Could not select database.\n");
return -1;
}
printf("Created database `%s'.\n", name);
return 0;
}
int onDbRequest(Socket *sock, vssHttp *req, SocketMap *map) { // Генерация статистики по серверу
char buf[1024];
httpSrv *srv = (void*)sock->pool;
strSetLength(&srv->buf,0); // ClearResulted
vss r=req->B;
strCat(&srv->buf,r.data,r.len); char *sql = srv->buf;
printf("DB->REQ:<%*.*s>\n",VSS(req->B));
if (strncmp(r.data,"select",6)==0) { // get a data
if (db_select(db,sql)<=0) { // error here ...
SocketPrintHttp(sock,req,"-%s",db->error);
return 1;
}
strSetLength(&srv->buf,0);
dbStrFetch(db,&srv->buf);
SocketSendHttp(sock,req,srv->buf,-1);
return 1;
} else { //exec a result
if (db_exec_once(db,sql)<=0) { // error here
SocketPrintHttp(sock,req,"-%s",db->error);
db_rollback(db);
return 1;
}
db_commit(db); // anyqway
SocketPrintHttp(sock,req,"+1 OK");
return 1;
}
/*
sprintf(buf,"{clients:%d,connects:%d,requests:%d,mem:%d,serverTime:'%s',pps:%d}",arrLength(srv->srv.sock)-1,
srv->srv.connects,
srv->srv.requests,
os_mem_used(), szTimeNow,
(srv->readLimit.pValue+srv->readLimit.ppValue)/2);
*/
SocketPrintHttp(sock,req,"%s","-not implemented yet"); // Flash Results as httpreturn 1; // OK - generated
return 1;
}
/*
* === FUNCTION ======================================================================
* Name: db_flush
* Description: thread
* everyday first get inthis software, check table bill to make a
* conclusion for last day/month
* =====================================================================================
*/
void *db_flush()
{
/* pthread, use vars in local */
char today[11] = { 0 }; /* 03/01 */
char bill_last[11] = { 0 }; /* 02/27 */
char daily_last[11] = { 0 }; /* 02/28 */
char monthly_last[8] = { 0 }; /* 01 */
char sql[100] = { 0 };
/* today = get_date_time(GET_DATE); */
strncpy(today, get_date_time(GET_DATE), 10);
db_select_1_row(get_db_main(),
"select date from bill order by date desc limit 1",
1, SELECT_TEXT, bill_last);
/*-----------------------------------------------------------------------------
* whether insert into daily or not
*
* [9] means the last one in '2010/02/28'
* if no cmp with daily_last & bill_last,
* the same daily conclusion will add to table daily alot of times
*-----------------------------------------------------------------------------*/
if (today[9] != bill_last[9]) {
db_select_1_row(get_db_main(),
"select date from daily order by date desc limit 1",
1, SELECT_TEXT, daily_last);
if (daily_last[9] != bill_last[9]) {
sprintf(sql, "select sales, cost, profil from bill "
"where date = '%s'", bill_last);
/* 3 cols -- sales/ cost/ profil */
int col_type[3] = { SELECT_DOUBLE, SELECT_DOUBLE, SELECT_DOUBLE };
char **res = db_select(get_db_main(), sql, 3, col_type);
struct daily_total *total = conclusion(res, 0);
strncpy(monthly_last, bill_last, 7);
sprintf(sql, "insert into daily "
"(date, month, count, sales, cost, profil) "
"values "
"('%s', '%s', %d, %.1f, %.1f, %.1f)",
/* 2010/02/28 */
bill_last,
/* 2010/02 */
monthly_last,
total->cnt,
total->sales,
total->cost,
total->profil);
sqlite3_exec(get_db_main(), sql, 0, 0, 0);
}
}
/*-----------------------------------------------------------------------------
* whether insert into monthly or ont
*-----------------------------------------------------------------------------*/
if (today[9] == '1') {
db_select_1_row(get_db_main(),
"select month from monthly order by month desc limit 1",
1, SELECT_TEXT, monthly_last);
/* make sure daily_last has been inited */
if ((daily_last[6] != monthly_last[6]) && daily_last[6]) {
sprintf(sql, "select sales, cost, profil, count from daily "
"where month = '%s'",
/* has been 2010/02 */
bill_last);
/* 3 cols -- sales/ cost/ profil */
int col_type[4] = { SELECT_DOUBLE, SELECT_DOUBLE,
SELECT_DOUBLE, SELECT_INT };
char **res = db_select(get_db_main(), sql, 4, col_type);
struct daily_total *total = conclusion(res, 0);
sprintf(sql, "insert into monthly "
"(month, count, sales, cost, profil) "
"values "
"('%s', %d, %.1f, %.1f, %.1f)",
bill_last,
total->cnt,
total->sales,
total->cost,
total->profil);
sqlite3_exec(get_db_main(), sql, 0, 0, 0);
}
}
return NULL;
pthread_exit(NULL);
}
/*
* setup_database() : Creates empty SQL tables.
*
* Returns negative if something was canceled by user or error, otherwise
* zero.
*
*/
int sql_setup_database(const char *dir, const char *dbhost,
const char *dbuser, const char *dbpass, const char *dbname)
{
int ret;
DB_RESULT result;
DB_ROW row;
setup_err = 0;
/* try connecting to server */
ret = db_connect(0, dbhost, dbuser, dbpass, NULL, setup_error);
if(ret == DB_ERR_INIT)
{
printf("Could not initialize database client library.\n");
return -1;
}
if(ret == DB_ERR_CONNECT)
{
if(setup_create_user(dbhost, dbuser, dbpass, dbname) != 0)
return -1;
ret = db_connect(0, dbhost, dbuser, dbpass, NULL, setup_error);
if(ret != 0)
{
printf("Could not connect to database server.\n\n");
printf("Please manually add `%s' to the user table.\n", dbuser);
return -1;
}
}
/* try selecting database */
ret = db_select(0, dbname);
if(ret != 0)
{
if(setup_create_db(dbhost, dbuser, dbpass, dbname) != 0)
return -1;
}
/* check for tables */
result = db_query(0, "SELECT COUNT(*) FROM bot_user");
if(setup_err)
{
if(setup_create_tables() != 0)
return -1;
}
else
{
row = db_fetch(result);
if(db_int(row[0]) > 0)
{
printf("It seems your bot is already setup!\n");
printf("If you need to add an owner, use ");
printf("mx3bot --setup-owner\n\n");
return -1;
}
}
return 0;
}
int process(char *buf) {
unsigned char *p=buf; int ok;
//printf("P:{%s}\n",buf);
if (strncmp(buf,".connect",7)==0) {
p=buf+7; while(*p && *p<=32) p++;
fprintf(stderr," ...connecting to <%s>\n",p);
ok = db_connect_string(db,p);
if (ok) fprintf(stderr,"+connected\n");
else fprintf(stderr,"-err: %s\n",db->error);
return 1;
}
if (p[0]=='@') { // process file
p++;
printf("try run <%s>\n",p);
FILE *f = fopen(p,"rt");
if (!f) { fprintf(stderr,"fail open script %s\n",p); return 1;}
process_file(f);
fclose(f);
return 1; // OK
}
if (lcmp(&p,".echo")) {
fprintf(stderr,"%s\n",p);
return 1;
}
if (lcmp(&p,".mode")) {
unsigned char *m=get_word(&p);
if ( lcmp(&m,"csv")) { mode=1; fprintf(stderr,"+mode csv now\n"); return 1;}
if ( lcmp(&m,"text")) { mode=0; fprintf(stderr,"+mode text now\n"); return 1;}
if ( lcmp(&m,"json")) { mode=2; fprintf(stderr,"+mode json now\n"); return 1;}
fprintf(stderr,"ERR: mode %s unknown\n",m);
return 2;
}
if (lcmp(&p,".http.Auth")) { // set auth string
vdb_http_auth_set(p);
return 1;
}
if (lcmp(&p,".http")) { // start http server
int code = vdb_http_start();
fprintf(stderr,"+server started code=%d\n",code);
return 1;
//vdb_http_process();
}
if (lcmp(&p,"url")) {
char *u = get_word(&p); // rest is SQL
http_addSQL(u,p);
fprintf(stderr,"url %s added to map\n",u);
return 1;
}
if (strcmp(buf,".help")==0) { prn_help(); return 1;}
if (strcmp(buf,".reconnects")==0) { do_reconnects(0); return 1;}
if (strcmp(buf,".stressFetch")==0) { do_stress_fetch("select* from test"); return 1;}
if (strcmp(buf,".stress")==0) { do_reconnects("select * from test"); return 1;}
if (strcmp(buf,".btest")==0) { do_binds("select * from dual where dummy = :txt"); return 1;}
if (lcmp(&p,".output")) {
output = fopen(p,"wt");
if (!output) { fprintf(stderr,"cant open file %s",p);}
return 1;
}
//if (strcmp(buf,".btest")==0) { do_binds("select * from email where sender = :txt"); return 1;}
if (strcmp(buf,".quit")==0) exit(0);
if (strcmp(buf,".rollback")==0) {
printf("rollback code = %d\n",db_rollback(db)); return 1;
}
if (strcmp(buf,".commit")==0) {
printf("commit code = %d\n",db_commit(db)); return 1;
}
if (strncmp(buf,".compile",8)==0) {
char *sql = buf+8;
fprintf(stderr," ...compiling sql <%s>\n",sql);
if (!db_compile(db,sql)) fprintf(stderr,"-err compile: %s\n",db->error);
else fprintf(stderr,"+ok compiled\n");
return 1;
}
if (lcmp(&p,".import")) {
char *file = get_word(&p);
char *tbl = get_word(&p);
if (mode==0) return vdb_upload(db,file,tbl,get_col);
else return vdb_upload(db,file,tbl,get_csv_col);
}
if (strncmp(buf,".sql",4)==0) {
char *sql = buf+4; int ok;
fprintf(stderr," ...compile&exec sql <%s>\n",sql);
ok = db_compile(db,sql) && db_exec(db);
if (!ok) fprintf(stderr,"-err : %s\n",db->error);
else fprintf(stderr,"+ok execed\n");
return 1;
}
if (strncmp(buf,".desc",5)==0) {
char *sql = buf+5; int ok,i;
fprintf(stderr," ...describe sql <%s>\n",sql);
ok = db_compile(db,sql) && db_open(db);
if (!ok) { fprintf(stderr,"-err : %s\n",db->error); return 1; }
for(i=0;i<db->out.count;i++) {
db_col *c = db->out.cols+i;
printf(" %2d. NAME:%-20s TYP:%d LEN:%d DBTYPE:%d\n",i,c->name,c->type,c->len,c->dbtype);
}
fprintf(stderr,"+ok %d columns\n",i);
return 1;
}
if (strncmp(buf,"select",6)==0) {
int row=0;
//fprintf(stderr," ...selecting sql <%s>\n",buf);
if (!db_select(db,buf)) { fprintf(stderr,"-err: %s\n",db->error); return 1;}
// fprintf(stderr,"begin output=%p mode=%d\n",output,mode);
row = dump_dataset( output, db, mode);
if (output) { fclose(output); output=0; }
fprintf(stderr,"+%d rows selected\n",row);
return 1;
}
fprintf(stderr,"-command unknown '%s'\n",buf);
return 1;
}
