/* * Beans Common Source * * Tom Coppeto * MIT Network Group * 25 December 1997 * * $Source: $ * $Author: $ * $Locker: $ */ #ifndef lint static char *rcsid="$Header: $"; #endif /* lint */ #include #include #include #include #include #include #include #include int db_open(char *db) { int socket; static int init = 0; if(!init) { if((socket = msqlConnect(0)) < 0) { logmsg(LOG_ERR, msqlErrMsg, "unable to connect to db"); return(0); } if(msqlSelectDB(socket, db) < 0) { logmsg(LOG_ERR, msqlErrMsg, "unable to connect to db %s", db); msqlClose(socket); return(0); } init = 1; } return(socket); } int db_close(int socket) { msqlClose(socket); } int db_hit(int db, struct hit *hit) { char query[BUFSIZ]; m_result *result; m_row row; int url, hits, lhits, country; if(!hit || !hit->host || !hit->path) return(BADNESS); /* * lets go open for now */ sprintf(query, "select id from url where path='%s' and host='%s'", hit->path, hit->host); logmsg(LOG_DEBUG, "", "query %s", query); if(msqlQuery(db, query) <= 0) { /* * create url in url table */ sprintf(query, "select _seq from url"); if(msqlQuery(db, query) <= 0) { logmsg(LOG_ERR, msqlErrMsg, "unable to access url sequence"); return(DBFAILURE); } result = msqlStoreResult(); row = msqlFetchRow(result); url = atoi(row[0]); msqlFreeResult(result); sprintf(query, "insert into url values (%d,'%s','%s')", url, hit->host, hit->path); if(msqlQuery(db, query) <= 0) { logmsg(LOG_ERR, msqlErrMsg, "unable to insert url %s", hit->path); return(DBFAILURE); } } else { result = msqlStoreResult(); row = msqlFetchRow(result); url = atoi(row[0]); msqlFreeResult(result); } if(*hit->country) { sprintf(query, "select id from country where code='%s'", hit->country); if(msqlQuery(db, query) <= 0) { logmsg(LOG_WARNING, "", "unknown country %s", hit->country); country = 0; } else { result = msqlStoreResult(); row = msqlFetchRow(result); country = atoi(row[0]); msqlFreeResult(result); } } else country = 0; if(country) { /* * monthly table */ sprintf(query, "select hits,lhits from monthly where url=%d and year=%d and month=%d and country=%d", url, hit->year, hit->month, country); logmsg(LOG_DEBUG, "", "query %s", query); if(msqlQuery(db, query) <= 0) { /* * create entry */ sprintf(query, "insert into monthly values(%d,%d,%d,%d,%d,%d)", url, hit->year, hit->month, country, hit->hits, hit->lhits); logmsg(LOG_DEBUG, "", "query %s", query); if(msqlQuery(db, query) <= 0) return(DBFAILURE); } else { /* * found an entry.. add hits */ result = msqlStoreResult(); row = msqlFetchRow(result); hits = atoi(row[0]); lhits = atoi(row[1]); msqlFreeResult(result); hits += hit->hits; lhits += hit->lhits; sprintf(query, "update monthly set hits=%d, lhits=%d where url=%d and year=%d and month=%d and country=%d", hits, lhits, url, hit->year, hit->month, country); if(msqlQuery(db, query) <= 0) return(DBFAILURE); } /* * daily table */ sprintf(query, "select hits,lhits from daily where url=%d and year=%d and month=%d and day=%d and country=%d",url, hit->year, hit->month, hit->day, country); logmsg(LOG_DEBUG, "", "query %s", query); if(msqlQuery(db, query) <= 0) { /* * create entry */ sprintf(query, "insert into daily values(%d,%d,%d,%d,%d,%d,%d)", url, hit->year, hit->month, hit->day, country, hit->hits, hit->lhits); logmsg(LOG_DEBUG, "", "query %s", query); if(msqlQuery(db, query) <= 0) return(DBFAILURE); } else { /* * found an entry.. add hits */ result = msqlStoreResult(); row = msqlFetchRow(result); hits = atoi(row[0]); lhits = atoi(row[1]); msqlFreeResult(result); hits += hit->hits; lhits += hit->lhits; sprintf(query, "update daily set hits=%d, lhits=%d where url=%d and year=%d and month=%d and day=%d and country=%d", hits, lhits, url, hit->year, hit->month, hit->day, country); if(msqlQuery(db, query) <= 0) return(DBFAILURE); } #ifdef notdef /* * hourly table */ sprintf(query, "select hits,lhits from hourly where url=%d and year=%d and month=%d and day=%d and hour=%d and country=%d", url, hit->year, hit->month, hit->day, hit->hour, country); logmsg(LOG_DEBUG, "", "query %s", query); if(msqlQuery(db, query) <= 0) { /* * create entry */ sprintf(query, "insert into hourly values(%d,%d,%d,%d,%d,%d,%d,%d)", url, hit->year, hit->month, hit->day, hit->hour, country, hit->hits, hit->lhits); logmsg(LOG_DEBUG, "", "query %s", query); if(msqlQuery(db, query) <= 0) return(DBFAILURE); } else { /* * found an entry.. add hits */ result = msqlStoreResult(); row = msqlFetchRow(result); hits = atoi(row[0]); lhits = atoi(row[1]); msqlFreeResult(result); hits += hit->hits; lhits += hit->hits; sprintf(query, "update hourly set hits=%d, lhits=%d where url=%d and year=%d and month=%d and day=%d and hour=%d and country=%d", hits, lhits, url, hit->year, hit->month, hit->day, hit->hour, country); if(msqlQuery(db, query) <= 0) return(DBFAILURE); } #endif } /* * monthly table */ sprintf(query, "select hits, lhits from monthly where url=%d and year=%d and month=%d and country=%d", url, hit->year, hit->month, 0); logmsg(LOG_DEBUG, "", "query %s", query); if(msqlQuery(db, query) <= 0) { /* * create entry */ sprintf(query, "insert into monthly values(%d,%d,%d,%d,%d,%d)", url, hit->year, hit->month, 0, hit->hits, hit->lhits); logmsg(LOG_DEBUG, "", "query %s", query); if(msqlQuery(db, query) <= 0) return(DBFAILURE); } else { /* * found an entry.. add hits */ result = msqlStoreResult(); row = msqlFetchRow(result); hits = atoi(row[0]); lhits = atoi(row[1]); msqlFreeResult(result); hits += hit->hits; lhits += hit->lhits; sprintf(query, "update monthly set hits=%d, lhits=%d where url=%d and year=%d and month=%d and country=%d", hits, lhits, url, hit->year, hit->month, 0); if(msqlQuery(db, query) <= 0) return(DBFAILURE); } #ifdef notdef /* * daily table */ sprintf(query, "select hits,lhits from daily where url=%d and year=%d and month=%d and day=%d and country=%d", url, hit->year, hit->month, hit->day, 0); logmsg(LOG_DEBUG, "", "query %s", query); if(msqlQuery(db, query) <= 0) { /* * create entry */ sprintf(query, "insert into daily values(%d,%d,%d,%d,%d,%d,%d)", url, hit->year, hit->month, hit->day, 0, hit->hits, hit->lhits); logmsg(LOG_DEBUG, "", "query %s", query); if(msqlQuery(db, query) <= 0) return(DBFAILURE); } else { /* * found an entry.. add hits */ result = msqlStoreResult(); row = msqlFetchRow(result); hits = atoi(row[0]); lhits = atoi(row[1]); msqlFreeResult(result); hits += hit->hits; lhits += hit->lhits; sprintf(query, "update daily set hits=%d, lhits=%d where url=%d and year=%d and month=%d and day=%d and country=%d", hits, lhits, url, hit->year, hit->month, hit->day, 0); if(msqlQuery(db, query) <= 0) return(DBFAILURE); } /* * hourly table */ sprintf(query, "select hits, lhits from hourly where url=%d and year=%d and month=%d and day=%d and hour=%d and country=%d", url, hit->year, hit->month, hit->day, hit->hour, 0); logmsg(LOG_DEBUG, "", "query %s", query); if(msqlQuery(db, query) <= 0) { /* * create entry */ sprintf(query, "insert into hourly values(%d,%d,%d,%d,%d,%d,%d,%d)", url, hit->year, hit->month, hit->day, hit->hour, 0, hit->hits, hit->lhits); logmsg(LOG_DEBUG, "", "query %s", query); if(msqlQuery(db, query) <= 0) return(DBFAILURE); } else { /* * found an entry.. add hits */ result = msqlStoreResult(); row = msqlFetchRow(result); hits = atoi(row[0]); lhits = atoi(row[1]); msqlFreeResult(result); hits += hit->hits; lhits += hit->lhits; sprintf(query, "update hourly set hits=%d, lhits=%d where url=%d and year=%d and month=%d and day=%d and hour=%d and country=%d", hits, lhits, url, hit->year, hit->month, hit->day, hit->hour, country); if(msqlQuery(db, query) <= 0) return(DBFAILURE); } #endif return(OK); } struct hit *db_hitback(int db, struct hit *start, struct hit *end, char *interval) { char query[BUFSIZ]; m_result *result; m_row row; struct hit *h = (struct hit *) NULL; struct hit *hp = (struct hit *) NULL; struct hit *hl; if(*start->country) sprintf(query, "select monthly.year,monthly.month,monthly.lhits,monthly.hits,url.path,url.host,country.code,country.name from monthly,url,country where url.path like '%s' and url.host='%s' and country.code like '%s' and monthly.country=country.id and monthly.year>=%d and monthly.year<=%d and monthly.month>=%d and monthly.month<=%d and url.id=monthly.url order by monthly.year,monthly.month,url.host,url.path,country.code", start->path, start->host, start->country, start->year, end->year, start->month, end->month); else sprintf(query, "select monthly.year,monthly.month,monthly.lhits,monthly.hits,url.path,url.host from monthly,url where url.path like '%s' and url.host='%s' and monthly.country=0 and monthly.year>=%d and monthly.year<=%d and monthly.month>=%d and monthly.month<=%d and url.id=monthly.url order by monthly.year,monthly.month,url.host,url.path", start->path, start->host, start->year, end->year, start->month, end->month); logmsg(LOG_DEBUG, "", "query %s\n", query); if(msqlQuery(db, query) <= 0) return((struct hit *) NULL); result = msqlStoreResult(); while(row = msqlFetchRow(result)) { if(!(hp = (struct hit *) malloc(sizeof(struct hit)))) { logmsg(LOG_ALERT, strerror(errno), "unable to allocat hit"); msqlFreeResult(result); freeHit(h); return((struct hit *) NULL); } memset(hp, 0, sizeof(struct hit)); hp->year = atoi(row[0]); hp->month = atoi(row[1]); hp->lhits = atoi(row[2]); hp->hits = atoi(row[3]); strcpy(hp->path, row[4]); strcpy(hp->host, row[5]); if(*start->country) { strcpy(hp->country, row[6]); strcpy(hp->country_name, row[7]); } if(!h) hl = h = hp; else { hl->next = hp; hl = hp; } } msqlFreeResult(result); return(h); } /* * Local Variables: * c-indent-level: 8 * c-argdecl-indent: 8 * c-continued-statement-offset: 8 * c-continued-brace-offset: -8 * c-label-offset: -8 * c-brace-offset: -8 * End: */