/* * TOPPERS Software * Toyohashi Open Platform for Embedded Real-Time Systems * * Copyright (C) 2013 by Kijineko Inc. * Copyright (C) 2013 by Embedded and Real-Time Systems Laboratory * Graduate School of Information Science, Nagoya Univ., JAPAN * * 上記著作権者は，以下の(1)〜(4)の条件を満たす場合に限り，本ソフトウェ * ア（本ソフトウェアを改変したものを含む．以下同じ）を使用・複製・改 * 変・再配布（以下，利用と呼ぶ）することを無償で許諾する． * (1) 本ソフトウェアをソースコードの形で利用する場合には，上記の著作 * 権表示，この利用条件および下記の無保証規定が，そのままの形でソー * スコード中に含まれていること． * (2) 本ソフトウェアを，ライブラリ形式など，他のソフトウェア開発に使 * 用できる形で再配布する場合には，再配布に伴うドキュメント（利用 * 者マニュアルなど）に，上記の著作権表示，この利用条件および下記 * の無保証規定を掲載すること． * (3) 本ソフトウェアを，機器に組み込むなど，他のソフトウェア開発に使 * 用できない形で再配布する場合には，次のいずれかの条件を満たすこ * と． * (a) 再配布に伴うドキュメント（利用者マニュアルなど）に，上記の著 * 作権表示，この利用条件および下記の無保証規定を掲載すること． * (b) 再配布の形態を，別に定める方法によって，TOPPERSプロジェクトに * 報告すること． * (4) 本ソフトウェアの利用により直接的または間接的に生じるいかなる損 * 害からも，上記著作権者およびTOPPERSプロジェクトを免責すること． * また，本ソフトウェアのユーザまたはエンドユーザからのいかなる理 * 由に基づく請求からも，上記著作権者およびTOPPERSプロジェクトを * 免責すること． * * 本ソフトウェアは，無保証で提供されているものである．上記著作権者お * よびTOPPERSプロジェクトは，本ソフトウェアに関して，特定の使用目的 * に対する適合性も含めて，いかなる保証も行わない．また，本ソフトウェ * アの利用により直接的または間接的に生じたいかなる損害に関しても，そ * の責任を負わない． * * $Id: sample1.c 58 2013-04-12 00:36:18Z ertl-honda $ */ #include #include #include #include "syssvc/serial.h" #include "syssvc/syslog.h" #include "sample1.h" #include "sqlite3.h" #define GEN_TABLE_FROM_STATICDATA /* * サービスコールのエラーのログ出力 */ Inline void svc_perror(const char *file, int_t line, const char *expr, ER ercd) { if (ercd < 0) { t_perror(LOG_ERROR, file, line, expr, ercd); } } #define SVC_PERROR(expr) svc_perror(__FILE__, __LINE__, #expr, (expr)) /* * SQLの実行 */ static void exec_sql(sqlite3 *db, const char *sql, sqlite3_callback cb, void *first_arg) { char *pStr; if (sqlite3_exec(db, sql, cb, first_arg, &pStr) != SQLITE_OK) { syslog(LOG_NOTICE, "Error %s %d: %s\n%s\n", __FILE__, __LINE__, pStr, sql); sqlite3_close(db); ext_tsk(); } } #define TNUM_DATA 100 #ifdef GEN_TABLE_FROM_STATICDATA /* * テーブへのデータの格納のための文字列テーブル */ char init_data[TNUM_DATA][100] = { "INSERT INTO sample (id, student, score, commit_date) values (1, 'Ey', 1, '1999-12-31 00:04:53')", "INSERT INTO sample (id, student, score, commit_date) values (2, 'nJ', 20, '1999-12-31 00:57:41')", "INSERT INTO sample (id, student, score, commit_date) values (3, 'um', 9, '1999-12-31 01:09:32')", "INSERT INTO sample (id, student, score, commit_date) values (4, 'lN', 33, '1999-12-31 01:40:25')", "INSERT INTO sample (id, student, score, commit_date) values (5, 'xV', 81, '1999-12-31 02:28:02')", "INSERT INTO sample (id, student, score, commit_date) values (6, 'pC', 70, '1999-12-31 02:38:45')", "INSERT INTO sample (id, student, score, commit_date) values (7, 'fE', 36, '1999-12-31 03:26:36')", "INSERT INTO sample (id, student, score, commit_date) values (8, 'vX', 85, '1999-12-31 04:11:56')", "INSERT INTO sample (id, student, score, commit_date) values (9, 'ZL', 77, '1999-12-31 04:33:59')", "INSERT INTO sample (id, student, score, commit_date) values (10, 'kD', 92, '1999-12-31 04:43:50')", "INSERT INTO sample (id, student, score, commit_date) values (11, 'uc', 30, '1999-12-31 05:08:40')", "INSERT INTO sample (id, student, score, commit_date) values (12, 'Ey', 90, '1999-12-31 05:36:10')", "INSERT INTO sample (id, student, score, commit_date) values (13, 'SO', 57, '1999-12-31 06:17:34')", "INSERT INTO sample (id, student, score, commit_date) values (14, 'EZ', 10, '1999-12-31 07:10:06')", "INSERT INTO sample (id, student, score, commit_date) values (15, 'ny', 28, '1999-12-31 07:56:05')", "INSERT INTO sample (id, student, score, commit_date) values (16, 'SM', 50, '1999-12-31 08:14:24')", "INSERT INTO sample (id, student, score, commit_date) values (17, 'HM', 8, '1999-12-31 08:59:20')", "INSERT INTO sample (id, student, score, commit_date) values (18, 'EZ', 66, '1999-12-31 09:29:46')", "INSERT INTO sample (id, student, score, commit_date) values (19, 'vO', 31, '1999-12-31 09:48:09')", "INSERT INTO sample (id, student, score, commit_date) values (20, 'Aw', 12, '1999-12-31 10:44:46')", "INSERT INTO sample (id, student, score, commit_date) values (21, 'DK', 13, '1999-12-31 11:07:13')", "INSERT INTO sample (id, student, score, commit_date) values (22, 'iX', 72, '1999-12-31 11:58:34')", "INSERT INTO sample (id, student, score, commit_date) values (23, 'MO', 90, '1999-12-31 12:13:25')", "INSERT INTO sample (id, student, score, commit_date) values (24, 'UW', 88, '1999-12-31 12:32:10')", "INSERT INTO sample (id, student, score, commit_date) values (25, 'fL', 96, '1999-12-31 12:40:01')", "INSERT INTO sample (id, student, score, commit_date) values (26, 'LF', 35, '1999-12-31 13:31:31')", "INSERT INTO sample (id, student, score, commit_date) values (27, 'xO', 41, '1999-12-31 14:19:44')", "INSERT INTO sample (id, student, score, commit_date) values (28, 'vL', 98, '1999-12-31 15:18:41')", "INSERT INTO sample (id, student, score, commit_date) values (29, 'Hw', 86, '1999-12-31 15:52:29')", "INSERT INTO sample (id, student, score, commit_date) values (30, 'Fw', 47, '1999-12-31 16:41:02')", "INSERT INTO sample (id, student, score, commit_date) values (31, 'hn', 69, '1999-12-31 17:32:29')", "INSERT INTO sample (id, student, score, commit_date) values (32, 'iX', 6, '1999-12-31 18:26:32')", "INSERT INTO sample (id, student, score, commit_date) values (33, 'EZ', 3, '1999-12-31 19:19:55')", "INSERT INTO sample (id, student, score, commit_date) values (34, 'Sv', 97, '1999-12-31 20:05:05')", "INSERT INTO sample (id, student, score, commit_date) values (35, 'Ie', 25, '1999-12-31 20:31:47')", "INSERT INTO sample (id, student, score, commit_date) values (36, 'hH', 1, '1999-12-31 20:50:58')", "INSERT INTO sample (id, student, score, commit_date) values (37, 'Gx', 62, '1999-12-31 21:06:53')", "INSERT INTO sample (id, student, score, commit_date) values (38, 'tK', 71, '1999-12-31 21:29:19')", "INSERT INTO sample (id, student, score, commit_date) values (39, 'VP', 20, '1999-12-31 21:33:57')", "INSERT INTO sample (id, student, score, commit_date) values (40, 'RV', 75, '1999-12-31 22:29:47')", "INSERT INTO sample (id, student, score, commit_date) values (41, 'PE', 31, '1999-12-31 22:40:22')", "INSERT INTO sample (id, student, score, commit_date) values (42, 'ZR', 49, '1999-12-31 23:26:17')", "INSERT INTO sample (id, student, score, commit_date) values (43, 'hH', 42, '2000-01-01 00:00:10')", "INSERT INTO sample (id, student, score, commit_date) values (44, 'KR', 38, '2000-01-01 00:25:26')", "INSERT INTO sample (id, student, score, commit_date) values (45, 'iX', 11, '2000-01-01 01:21:25')", "INSERT INTO sample (id, student, score, commit_date) values (46, 'Nm', 74, '2000-01-01 02:19:52')", "INSERT INTO sample (id, student, score, commit_date) values (47, 'WR', 96, '2000-01-01 02:25:58')", "INSERT INTO sample (id, student, score, commit_date) values (48, 'ay', 56, '2000-01-01 02:39:31')", "INSERT INTO sample (id, student, score, commit_date) values (49, 'ii', 100, '2000-01-01 03:16:08')", "INSERT INTO sample (id, student, score, commit_date) values (50, 'hi', 56, '2000-01-01 03:25:22')", "INSERT INTO sample (id, student, score, commit_date) values (51, 'hH', 31, '2000-01-01 04:02:52')", "INSERT INTO sample (id, student, score, commit_date) values (52, 'RK', 8, '2000-01-01 04:07:52')", "INSERT INTO sample (id, student, score, commit_date) values (53, 'TC', 54, '2000-01-01 05:07:26')", "INSERT INTO sample (id, student, score, commit_date) values (54, 'NT', 18, '2000-01-01 05:24:18')", "INSERT INTO sample (id, student, score, commit_date) values (55, 'xh', 17, '2000-01-01 05:48:45')", "INSERT INTO sample (id, student, score, commit_date) values (56, 'hH', 8, '2000-01-01 06:08:31')", "INSERT INTO sample (id, student, score, commit_date) values (57, 'KN', 55, '2000-01-01 06:49:27')", "INSERT INTO sample (id, student, score, commit_date) values (58, 'hH', 5, '2000-01-01 07:07:57')", "INSERT INTO sample (id, student, score, commit_date) values (59, 'fI', 99, '2000-01-01 07:58:31')", "INSERT INTO sample (id, student, score, commit_date) values (60, 'QJ', 23, '2000-01-01 08:40:22')", "INSERT INTO sample (id, student, score, commit_date) values (61, 'BF', 72, '2000-01-01 09:13:39')", "INSERT INTO sample (id, student, score, commit_date) values (62, 'TD', 43, '2000-01-01 09:19:52')", "INSERT INTO sample (id, student, score, commit_date) values (63, 'Hm', 5, '2000-01-01 10:08:09')", "INSERT INTO sample (id, student, score, commit_date) values (64, 'CL', 37, '2000-01-01 10:44:52')", "INSERT INTO sample (id, student, score, commit_date) values (65, 'Bq', 41, '2000-01-01 10:52:54')", "INSERT INTO sample (id, student, score, commit_date) values (66, 'yX', 68, '2000-01-01 11:09:52')", "INSERT INTO sample (id, student, score, commit_date) values (67, 'Vo', 31, '2000-01-01 11:28:29')", "INSERT INTO sample (id, student, score, commit_date) values (68, 'dn', 10, '2000-01-01 11:45:09')", "INSERT INTO sample (id, student, score, commit_date) values (69, 'bO', 44, '2000-01-01 12:44:02')", "INSERT INTO sample (id, student, score, commit_date) values (70, 'pR', 2, '2000-01-01 12:53:11')", "INSERT INTO sample (id, student, score, commit_date) values (71, 'HK', 50, '2000-01-01 13:28:05')", "INSERT INTO sample (id, student, score, commit_date) values (72, 'OU', 49, '2000-01-01 13:46:50')", "INSERT INTO sample (id, student, score, commit_date) values (73, 'ch', 10, '2000-01-01 14:16:53')", "INSERT INTO sample (id, student, score, commit_date) values (74, 'NV', 72, '2000-01-01 14:51:01')", "INSERT INTO sample (id, student, score, commit_date) values (75, 'wQ', 6, '2000-01-01 15:16:37')", "INSERT INTO sample (id, student, score, commit_date) values (76, 'Ik', 24, '2000-01-01 15:29:08')", "INSERT INTO sample (id, student, score, commit_date) values (77, 'hH', 3, '2000-01-01 16:14:29')", "INSERT INTO sample (id, student, score, commit_date) values (78, 'ks', 76, '2000-01-01 16:58:13')", "INSERT INTO sample (id, student, score, commit_date) values (79, 'Cw', 60, '2000-01-01 17:38:39')", "INSERT INTO sample (id, student, score, commit_date) values (80, 'xq', 48, '2000-01-01 17:57:02')", "INSERT INTO sample (id, student, score, commit_date) values (81, 'WY', 95, '2000-01-01 18:46:18')", "INSERT INTO sample (id, student, score, commit_date) values (82, 'HK', 25, '2000-01-01 19:37:57')", "INSERT INTO sample (id, student, score, commit_date) values (83, 'OO', 50, '2000-01-01 19:40:04')", "INSERT INTO sample (id, student, score, commit_date) values (84, 'Dl', 59, '2000-01-01 20:29:56')", "INSERT INTO sample (id, student, score, commit_date) values (85, 'SJ', 7, '2000-01-01 20:44:38')", "INSERT INTO sample (id, student, score, commit_date) values (86, 'ks', 20, '2000-01-01 20:45:49')", "INSERT INTO sample (id, student, score, commit_date) values (87, 'ql', 20, '2000-01-01 21:14:47')", "INSERT INTO sample (id, student, score, commit_date) values (88, 'nM', 67, '2000-01-01 21:31:50')", "INSERT INTO sample (id, student, score, commit_date) values (89, 'nF', 56, '2000-01-01 22:24:10')", "INSERT INTO sample (id, student, score, commit_date) values (90, 'zQ', 1, '2000-01-01 22:36:01')", "INSERT INTO sample (id, student, score, commit_date) values (91, 'Ce', 29, '2000-01-01 22:40:17')", "INSERT INTO sample (id, student, score, commit_date) values (92, 'xg', 98, '2000-01-01 23:06:18')", "INSERT INTO sample (id, student, score, commit_date) values (93, 'Qs', 55, '2000-01-01 23:44:24')", "INSERT INTO sample (id, student, score, commit_date) values (94, 'bF', 42, '2000-01-02 00:42:50')", "INSERT INTO sample (id, student, score, commit_date) values (95, 'Nz', 91, '2000-01-02 00:55:16')", "INSERT INTO sample (id, student, score, commit_date) values (96, 'ln', 86, '2000-01-02 01:28:52')", "INSERT INTO sample (id, student, score, commit_date) values (97, 'KW', 84, '2000-01-02 01:36:19')", "INSERT INTO sample (id, student, score, commit_date) values (98, 'Dw', 72, '2000-01-02 02:19:29')", "INSERT INTO sample (id, student, score, commit_date) values (99, 'FW', 80, '2000-01-02 02:45:21')", "INSERT INTO sample (id, student, score, commit_date) values (100, 'Js', 76, '2000-01-02 03:07:38')" }; /* * データの格納(スタティックデータから) */ static void insert_data(sqlite3 *db) { int i; for(i = 0; i < TNUM_DATA; i++) { exec_sql(db, init_data[i], NULL, NULL); } } #else /* !GEN_TABLE_FROM_STATICDATA */ #include #include #include #include /* * ランダムな英字を取得 */ static int get_random_letter(void) { static const char letters[] = "abcdefghijklmnopqrstuvwxyz" "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; int i = rand() % sizeof(letters); return letters[i]; } /* * データの格納(ランダムに生成) */ static void insert_data(sqlite3 *db) { static char str[STRBUF_SIZE], data[4][STRBUF_SIZE]; int i; struct tm t = { 0 }; t.tm_year = -1900 + 2000; for(i = 0; i < NUM_OF_DATA; i++) { t.tm_sec = (t.tm_sec + rand()) % 60; t.tm_min += rand() % 60 + 1; mktime(&t); sprintf(data[0], "%d", i + 1); sprintf(data[1], "%c%c", get_random_letter(), get_random_letter()); sprintf(data[2], "%d", rand() % NUM_OF_DATA + 1); sprintf(data[3], "%04d-%02d-%02d %02d:%02d:%02d", t.tm_year + 1900, t.tm_mon + 1, t.tm_mday, t.tm_hour, t.tm_min, t.tm_sec); sprintf(str, "INSERT INTO sample (id, student, score, " "commit_date) values (%s, '%s', %s, '%s')", data[0], data[1], data[2], data[3]); exec_sql(db, str, NULL, NULL); } } #endif /* GEN_TABLE_FROM_STATICDATA */ /* * テーブルの作成 */ static void make_table(sqlite3 *db) { exec_sql(db, "CREATE TABLE sample " "(id INTEGER PRIMARY KEY, student TEXT NOT NULL, " "score INTEGER NOT NULL, commit_date TEXT NOT NULL)", NULL, NULL); insert_data(db); } /* * コールバック関数用のデータ */ #define TNUM_COLUMN 4 #define TNUM_RAW 11 #define STRBUF_SIZE 256 typedef struct { char values[TNUM_COLUMN][STRBUF_SIZE]; } RAW; typedef struct { int raw_count; RAW raw_name; RAW raw_data[TNUM_RAW]; }SQL_RESULT; /* * SELECT文によって得られた結果行を1行ずつ取得するコールバック関数 */ static int callback(void *option, int columnCount, char **columnValues, char **columnNames) { int i,e; SQL_RESULT *psql_result; psql_result = (SQL_RESULT*)option; for(i = 0; ((i < TNUM_COLUMN) && (i < columnCount)); i++) { char *pstr; if (psql_result->raw_count == 0) { pstr = (psql_result->raw_name).values[i]; for(e = 0; (e <= (STRBUF_SIZE - 1)) && (columnNames[i][e] != '\0'); e++) { pstr[e] = columnNames[i][e]; } pstr[e] = '\0'; } pstr = (psql_result->raw_data[psql_result->raw_count]).values[i]; for(e = 0; (e <= (STRBUF_SIZE - 1)) && (columnValues[i][e] != '\0'); e++) { pstr[e] = columnValues[i][e]; } pstr[e] = '\0'; } psql_result->raw_count++; return 0; } /* * コールバック関数に渡すデータ */ SQL_RESULT sql_result; /* * メインタスク */ void main_task(intptr_t exinf) { ER_UINT ercd; int rawCount; sqlite3 *db; SVC_PERROR(syslog_msk_log(LOG_UPTO(LOG_INFO), LOG_UPTO(LOG_EMERG))); syslog(LOG_NOTICE, "Sample program starts (exinf = %d).", (int_t) exinf); /* * シリアルポートの初期化 * * システムログタスクと同じシリアルポートを使う場合など，シリアル * ポートがオープン済みの場合にはここでE_OBJエラーになるが，支障は * ない． */ ercd = serial_opn_por(TASK_PORTID); if (ercd < 0 && MERCD(ercd) != E_OBJ) { syslog(LOG_ERROR, "%s (%d) reported by `serial_opn_por'.", itron_strerror(ercd), SERCD(ercd)); } SVC_PERROR(serial_ctl_por(TASK_PORTID, (IOCTL_CRLF | IOCTL_FCSND | IOCTL_FCRCV))); /* メモリ上にDBを構築 （":memory:"を指定） */ if (sqlite3_open(":memory:", &db) != SQLITE_OK) { syslog(LOG_ERROR, "sqliet3 : open error!"); sqlite3_close(db); return; } /* テーブルの作成 */ make_table(db); /* DBの検索 */ syslog(LOG_NOTICE, "total_score top10"); /* socreの合計値のtop10の学生を取得 */ sql_result.raw_count = 0; exec_sql(db, "SELECT student, sum(score) " "AS total_score FROM sample GROUP BY student " "ORDER BY total_score DESC LIMIT 10", callback, (void*)(&sql_result)); /* Output Name */ syslog(LOG_NOTICE, "%s,%s", sql_result.raw_name.values[0], sql_result.raw_name.values[1]); /* Output Data */ for(rawCount = 0; rawCount < sql_result.raw_count; rawCount++) { syslog(LOG_NOTICE, "%s,%s", sql_result.raw_data[rawCount].values[0], sql_result.raw_data[rawCount].values[1]); } syslog(LOG_NOTICE, "total_commit_count top10"); sql_result.raw_count = 0; exec_sql(db, "SELECT student, count(id) AS total_commit_count " "FROM sample GROUP BY student ORDER BY total_commit_count DESC LIMIT 10", callback, (void*)(&sql_result)); /* Output Name */ syslog(LOG_NOTICE, "%s,%s", sql_result.raw_name.values[0], sql_result.raw_name.values[1]); /* Output Data */ for(rawCount = 0; rawCount < sql_result.raw_count; rawCount++) { syslog(LOG_NOTICE, "%s,%s", sql_result.raw_data[rawCount].values[0], sql_result.raw_data[rawCount].values[1]); } sqlite3_close(db); }