// sample.c
// written by mnagaku @ 2006/04/27
// id, worker, change_line_count, commit_date
// の形式のデータをDBに取り込み、
// change_line_countの総計の多いworkerのtop10と
// commit数(データ数)の総計の多いworkerのtop10を調べます

#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include "sqlite3.h"

#define MAX_STR 256
#define MAX_COLUMN 4
// SQL文
#define CREATE_TABLE "CREATE TABLE sample (id INTEGER PRIMARY KEY, \
    worker TEXT NOT NULL, change_line_count INTEGER NOT NULL, \
    commit_date TEXT NOT NULL)"
#define INSERT_TABLE "INSERT INTO sample (id, worker, change_line_count, \
    commit_date) values (%s, '%s', %s, '%s')"
#define SELECT_TABLE "SELECT worker, sum(change_line_count) \
    AS total_change_line_count FROM sample GROUP BY worker \
    ORDER BY total_change_line_count DESC LIMIT 10"
#define SELECT_TABLE2 "SELECT worker, count(id) AS total_commit_count \
    FROM sample GROUP BY worker ORDER BY total_commit_count DESC LIMIT 10"

// DBの検索結果を大域変数で保持
typedef struct {
    char values[MAX_COLUMN][MAX_STR];
} RAW;
RAW *sqlResult;
int rawSize, columnSize;

// 検索結果を1行取得する度に呼ばれるコールバック関数
static int callback(void *option, int columnCount, char **columnValues,
    char **columnNames) {
    int columnCounter;

    if(sqlResult == NULL) {
        sqlResult = (RAW *)malloc(MAX_COLUMN * MAX_STR * 2);
        columnSize = columnCount;
    } else if((sqlResult = (RAW *)realloc(sqlResult,
        MAX_COLUMN * MAX_STR * (rawSize + 2))) == NULL)
        return -1;

    for(columnCounter = 0;
        columnCounter < MAX_COLUMN && columnCounter < columnCount;
        columnCounter++) {
        if(rawSize == 1)
            strncpy(sqlResult[0].values[columnCounter],
                columnNames[columnCounter], MAX_STR);
        strncpy(sqlResult[rawSize + 1].values[columnCounter],
            columnValues[columnCounter], MAX_STR);
    }
    rawSize++;
    return 0;
}

// SQLの実行
void sqlExec(sqlite3 *db, const char *sql, sqlite3_callback cb) {
    char *pStr;

    free(sqlResult);
    sqlResult = NULL;
    rawSize = 0;

    if(sqlite3_exec(db, sql, cb, NULL, &pStr) != SQLITE_OK) {
        fprintf(stderr, "Error %s %d: %s\n%s\n", __FILE__, __LINE__, pStr, sql);
        sqlite3_close(db);
        exit(0);
    }
}

// メイン
void main() {
    char str[MAX_STR], data[4][MAX_STR], *pStr;
    int lineCount, dataCount;
    FILE *fp;
    sqlite3 *db;

// メモリ上にDBを構築
    if(sqlite3_open(":memory:", &db)) {
        fprintf(stderr, "Error %s %d: Can't open database %s\n",
            __FILE__, __LINE__, sqlite3_errmsg(db));
        sqlite3_close(db);
        return;
    }
// テーブル作成
    sqlExec(db, CREATE_TABLE, NULL);
// データファイル読込み
    if((fp = fopen("sample.csv", "rt")) == NULL) {
        fprintf(stderr, "Error %s %d: Can't open datafile.\n",
            __FILE__, __LINE__);
        sqlite3_close(db);
        return;
    }
    for(lineCount = 0; fgets(str, MAX_STR, fp) != NULL; lineCount++) {
        for(pStr = strtok(str, ","), dataCount = 0;
            dataCount < 4 && strlen(pStr) > 0 && pStr[0] != '\n';
            pStr = strtok(NULL, ","), dataCount++)
            strncpy(data[dataCount], pStr, MAX_STR);
        if(dataCount < 4) {
            printf("Warnning %s %d: Find illegal data at %d\n",
                __FILE__, __LINE__, lineCount);
            break;
        }
// 読み込んだデータをDBに投入
        sprintf(str, INSERT_TABLE, data[0], data[1], data[2], data[3]);
        sqlExec(db, str, NULL);
    }
    fclose(fp);
// DBの検索
    puts("total_change_line_count top10");
    sqlExec(db, SELECT_TABLE, callback);
    for(lineCount = 0; lineCount <= rawSize; lineCount++) {
        for(dataCount = 0; dataCount < columnSize; dataCount++)
            printf("%s,", sqlResult[lineCount].values[dataCount]);
        puts("");
    }
    puts("");
    puts("total_commit_count top10");
    sqlExec(db, SELECT_TABLE2, callback);
    for(lineCount = 0; lineCount <= rawSize; lineCount++) {
        for(dataCount = 0; dataCount < columnSize; dataCount++)
            printf("%s,", sqlResult[lineCount].values[dataCount]);
        puts("");
    }
    sqlite3_close(db);
}

