20160126_jeffrey - silenceuncrio/diary GitHub Wiki

Index

  • 0920 - 寫 engineering notebook
  • 1045 - 支援 Smart Plug 的事情優先考慮
  • 1115 - get_common.c
  • 1335 - 修正 iot_init.sql
  • 1635 - 進度快照
  • 1550 - 在 database 新增一個 WhiteBoxes

0920

寫 engineering notebook

mantis 上的 database 就夠了

1045

交 Engineering Notebook 時
Pioneer 告知說要調一下 Schedule 將支援 Smart Plug 的事情優先考慮

藍芽模組對 Control Center 來說要透過 UART 溝通

如果是 JS9331 的話

image

TTL/RS232 串口應該可以拿來用

Linkit 那一片看來也沒啥問題
Pin out 圖看來有兩組 UART

1115

繼續上禮拜的 get_common.c

1335

修正上禮拜的 iot_init.sql

from ThingType INTEGER NOT NULL,
to ThingType TEXT NOT NULL,

BEGIN TRANSACTION;

CREATE TABLE IF NOT EXISTS Boxes (
    BoxId         INTEGER   PRIMARY KEY   NOT NULL,
    BoxName       TEXT   NOT NULL,
    BoxLocation   TEXT   NOT NULL,
    BoxConfig_    TEXT   NOT NULL
);

CREATE TABLE Things (
    ThingId        INTEGER   PRIMARY KEY,
    ThingType      TEXT   NOT NULL,
    ThingStatus_   TEXT   NOT NULL,
    BoxId          INTEGER   NOT NULL   REFERENCES Boxes( BoxId )
);

CREATE TABLE OpenDetectors (
    OpenId        INTEGER   PRIMARY KEY,
    OpenConfig_   TEXT   NOT NULL,
    OpenStatus_   TEXT   NOT NULL,
    ThingId       INTEGER   NOT NULL   REFERENCES Things( ThingId )
);

CREATE TABLE LightDetectors (
    LightId        INTEGER   PRIMARY KEY,
    LightConfig_   TEXT   NOT NULL,
    LightStatus_   TEXT   NOT NULL,
    ThingId        INTEGER   NOT NULL   REFERENCES Things( ThingId )
);

COMMIT;

1635

今天的進度快照

Makefile

CROSS_COMPILE	 = mips-openwrt-linux-uclibc-
CC				 = $(CROSS_COMPILE)gcc
AR				 = $(CROSS_COMPILE)ar
CC_I			 = -I/home/jeffrey/openwrt/staging_dir/target-mips_34kc_uClibc-0.9.33.2/usr/include/
CC_I			+= -I/home/jeffrey/openwrt/staging_dir/toolchain-mips_34kc_gcc-4.8-linaro_uClibc-0.9.33.2/include/
CC_L			 = -L/home/jeffrey/openwrt/staging_dir/target-mips_34kc_uClibc-0.9.33.2/usr/lib/
CC_L			+= -L/home/jeffrey/openwrt/staging_dir/toolchain-mips_34kc_gcc-4.8-linaro_uClibc-0.9.33.2/lib
LIBS			 = -lm -ldl -lpthread -lfcgi -ljson-c -lpolarssl -lcurl -lsqlite3

get_common_1: get_common_1.o database.o
	$(CC) $(CC_I) $(CC_L) $(LIBS) -o $@ get_common_1.o database.o

%.o: %.c
	@echo Compile $< ...
	$(CC) $(CC_I) $(CC_L) $(LIBS) -c -o $@ $<

get_common_1.c

/* standard includes */
#include <stdio.h>
#include <stdlib.h>
#include <stdarg.h>
#include <string.h>

/* json-c (https://github.com/json-c/json-c) */
#include <json-c/json.h>

/* libcurl (http://curl.haxx.se/libcurl/c) */
#include <curl/curl.h>

#include <sys/time.h>

#include "database.h"



/* holder for curl fetch */
struct curl_fetch_st {
    char *payload;
    size_t size;
};

/* callback for curl fetch */
size_t curl_callback (void *contents, size_t size, size_t nmemb, void *userp) {
    size_t realsize = size * nmemb;                             /* calculate buffer size */
    struct curl_fetch_st *p = (struct curl_fetch_st *) userp;   /* cast pointer to fetch struct */

    /* expand buffer */
    p->payload = (char *) realloc(p->payload, p->size + realsize + 1);

    /* check buffer */
    if (p->payload == NULL) {
      /* this isn't good */
      fprintf(stderr, "ERROR: Failed to expand buffer in curl_callback");
      /* free buffer */
      free(p->payload);
      /* return */
      return -1;
    }

    /* copy contents to buffer */
    memcpy(&(p->payload[p->size]), contents, realsize);

    /* set new buffer size */
    p->size += realsize;

    /* ensure null termination */
    p->payload[p->size] = 0;

    /* return size */
    return realsize;
}

/* fetch and return url body via curl */
CURLcode curl_fetch_url(CURL *ch, const char *url, struct curl_fetch_st *fetch) {
    CURLcode rcode;                   /* curl result code */

    /* init payload */
    fetch->payload = (char *) calloc(1, sizeof(fetch->payload));

    /* check payload */
    if (fetch->payload == NULL) {
        /* log error */
        fprintf(stderr, "ERROR: Failed to allocate payload in curl_fetch_url");
        /* return error */
        return CURLE_FAILED_INIT;
    }

    /* init size */
    fetch->size = 0;

    /* set url to fetch */
    curl_easy_setopt(ch, CURLOPT_URL, url);

    /* set calback function */
    curl_easy_setopt(ch, CURLOPT_WRITEFUNCTION, curl_callback);

    /* pass fetch struct pointer */
    curl_easy_setopt(ch, CURLOPT_WRITEDATA, (void *) fetch);

    /* set default user agent */
    curl_easy_setopt(ch, CURLOPT_USERAGENT, "libcurl-agent/1.0");

    /* set timeout */
    curl_easy_setopt(ch, CURLOPT_TIMEOUT, 5);

    /* enable location redirects */
    curl_easy_setopt(ch, CURLOPT_FOLLOWLOCATION, 1);

    /* set maximum allowed redirects */
    curl_easy_setopt(ch, CURLOPT_MAXREDIRS, 1);

    /* fetch the url */
    rcode = curl_easy_perform(ch);

    /* return */
    return rcode;
}



static int _key2string(json_object *obj, const char *key, char *value) {
    json_object *j;
    if (!json_object_object_get_ex(obj, key, &j)) {
        printf("Error: %s(%s) fail\n", __FUNCTION__, key);
        return -1;
    }

    strcpy(value, json_object_get_string(j));
    printf("%s: %s\n", key, value);
    return 0;
}



static int _key2obj(json_object *obj, const char *key, json_object **keyObj) {
    if (!json_object_object_get_ex(obj, key, keyObj)) {
        printf("Error: %s(%s) fail\n", __FUNCTION__, key);
        return -1;
    }

    printf("%s: %s\n", key, json_object_to_json_string(*keyObj));
    return 0;
}



static int _hasKey(json_object *obj, const char *key) {
    json_object *j;
    if (!json_object_object_get_ex(obj, key, &j)) {
        printf("Error: %s(%s) fail\n", __FUNCTION__, key);
        return 0;
    }
    return 1;
}



int main(int argc, char *argv[]) {
    CURL *ch;                                               /* curl handle */
    CURLcode rcode;                                         /* curl result code */

    json_object *json;                                      /* json post body */
    enum json_tokener_error jerr = json_tokener_success;    /* json parse error */

    struct curl_fetch_st curl_fetch;                        /* curl fetch struct */
    struct curl_fetch_st *cf = &curl_fetch;                 /* pointer to fetch struct */


    struct timeval t1, t2;
    double timecost;

    // start timer
    gettimeofday(&t1, NULL);


    /* url to test site */
    char *url = "http://192.168.1.113:8888/common";

    /* init curl handle */
    if ((ch = curl_easy_init()) == NULL) {
        /* log error */
        fprintf(stderr, "ERROR: Failed to create curl handle in fetch_session");
        /* return error */
        return 1;
    }

    /* fetch page and capture return code */
    rcode = curl_fetch_url(ch, url, cf);

    /* cleanup curl handle */
    curl_easy_cleanup(ch);

    /* check return code */
    if (rcode != CURLE_OK || cf->size < 1) {
        /* log error */
        fprintf(stderr, "ERROR: Failed to fetch url (%s) - curl said: %s",
            url, curl_easy_strerror(rcode));
        /* return error */
        return 2;
    }

    /* check payload */
    if (cf->payload != NULL) {
        /* print result */
        printf("CURL Returned: \n%s\n", cf->payload);
        /* parse return */
        json = json_tokener_parse_verbose(cf->payload, &jerr);
        /* free payload */
        free(cf->payload);
    } else {
        /* error */
        fprintf(stderr, "ERROR: Failed to populate payload");
        /* free payload */
        free(cf->payload);
        /* return */
        return 3;
    }

    /* check error */
    if (jerr != json_tokener_success) {
        /* error */
        fprintf(stderr, "ERROR: Failed to parse json string");
        /* free json object */
        json_object_put(json);
        /* return */
        return 4;
    }

    /* debugging */
    printf("Parsed JSON: %s\n", json_object_to_json_string(json));



    /*
    ** {
    **   "name": "ward",
    **   "location": "room",
    **   "config": {
    **     "mode": "on"
    **   }, 
    **   "thing": {
    **     "openDetector" : 1,
    **     "lightDetector": 1
    **   }
    ** }
    */


    char name[32];
    char location[32];
    json_object *config;
    json_object *things;

    if (_key2string(json, "name", name)         < 0) return 0;
    if (_key2string(json, "location", location) < 0) return 0;
    if (_key2obj(json, "config", &config) < 0) return 0;
    if (_key2obj(json, "things", &things) < 0) return 0;

    printf("config:  %s\n", json_object_to_json_string(config));

    int last_box_id, last_thing_id;



    database_open();

    last_box_id = database_insert_into_boxes(name, location, json_object_to_json_string(config));

    printf("last_box_id after insert_into_boxes(%s, %s): %d\n", name, location, last_box_id);

    printf("things: %s\n", json_object_to_json_string(things));


    if (_hasKey(things, "openDetector")) {
        printf("find thing: openDetector\n");
        last_thing_id = database_insert_into_things("OpenDetector", "OpenDetector Status ...", last_box_id);
        printf("last_thing_id after insert_into_things(OpenDetector): %d\n", last_thing_id);

        int last_openDetector_id = database_insert_into_openDetectors(
            "openDetector config ...", 
            "openDetector status ...", 
            last_thing_id);

        printf("last_openDetector_id after insert_into_openDetectors(): %d\n", last_openDetector_id);
    }


    if (_hasKey(things, "lightDetector")) {
        printf("find thing: lightDetector\n");
        last_thing_id = database_insert_into_things("LightDetector", "LightDetector Status ...", last_box_id);
        printf("last_thing_id after insert_into_things(lightDetector): %d\n", last_thing_id);

        int last_openDetector_id = database_insert_into_lightDetectors(
            "openDetector config ...", 
            "openDetector status ...", 
            last_thing_id);

        printf("last_openDetector_id after insert_into_openDetectors(): %d\n", last_openDetector_id);
    }

    database_close();



    if (_hasKey(things, "xxx")) {
        printf("find thing: xxx\n");
    }




    // stop timer
    gettimeofday(&t2, NULL);

    // compute and print time cost in ms
    timecost = (t2.tv_sec - t1.tv_sec) * 1000.0;    // sec to ms
    timecost += (t2.tv_usec - t1.tv_usec) / 1000.0; // us to ms
    printf("Code time cost: %f\n", timecost);


    
    /* exit */
    return 0;
}

database.c

#include "database.h"



sqlite3 *Database;



int database_insert_into_openDetectors(const char *OpenConfig_, const char *OpenStatus_, int ThingId) {
    char sql[256];
    sprintf(sql, 
        "INSERT INTO OpenDetectors(OpenConfig_, OpenStatus_, ThingId)"
        "VALUES ('%s', '%s', %d);"
        , OpenConfig_, OpenStatus_, ThingId);

    char *err_msg = 0;
    if (SQLITE_OK != sqlite3_exec(Database, sql, 0, 0, &err_msg)) {
        fprintf(stderr, "SQL error: %s\n", err_msg);
        sqlite3_free(err_msg);
        return -1;
    }

    return sqlite3_last_insert_rowid(Database);
}



int database_insert_into_lightDetectors(const char *LightConfig_, const char *LightStatus_, int ThingId) {
    char sql[256];
    sprintf(sql, 
        "INSERT INTO LightDetectors(LightConfig_, LightStatus_, ThingId)"
        "VALUES ('%s', '%s', %d);"
        , LightConfig_, LightStatus_, ThingId);

    char *err_msg = 0;
    if (SQLITE_OK != sqlite3_exec(Database, sql, 0, 0, &err_msg)) {
        fprintf(stderr, "SQL error: %s\n", err_msg);
        sqlite3_free(err_msg);
        return -1;
    }

    return sqlite3_last_insert_rowid(Database);
}



int database_insert_into_things(const char *ThingType, const char *ThingStatus_, int BoxId) {
    char sql[256];
    sprintf(sql, 
        "INSERT INTO Things(ThingType, ThingStatus_, BoxId) "
        "VALUES ('%s', '%s', %d);"
        , ThingType, ThingStatus_, BoxId);

    char *err_msg = 0;
    if (SQLITE_OK != sqlite3_exec(Database, sql, 0, 0, &err_msg)) {
        fprintf(stderr, "Failed to insert into Things\n");
        fprintf(stderr, "SQL error: %s\n", err_msg);
        sqlite3_free(err_msg);
        return -1;
    }

    return sqlite3_last_insert_rowid(Database);
}



int database_insert_into_boxes(const char *BoxName, const char *BoxLocation, const char *BoxConfig_) {
    char sql[256];
    sprintf(sql, 
        "INSERT INTO Boxes(BoxName, BoxLocation, BoxConfig_) "
        "VALUES ('%s', '%s', '%s');"
        , BoxName, BoxLocation, BoxConfig_);

    char *err_msg = 0;
    if (SQLITE_OK != sqlite3_exec(Database, sql, 0, 0, &err_msg)) {
        fprintf(stderr, "SQL error: %s\n", err_msg);
        sqlite3_free(err_msg);
        return -1;
    }

    return sqlite3_last_insert_rowid(Database);
}



int database_open() {
    if (SQLITE_OK != sqlite3_open("iot.db", &Database)) {
        fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(Database));
        sqlite3_close(Database);
        return -1;
    }
}



void database_close() {
    sqlite3_close(Database);
}

database.h

#include <stdio.h>
#include <stdlib.h>
#include <stdarg.h>
#include <string.h>

#include <sqlite3.h>



extern sqlite3 *Database;



int database_insert_into_openDetectors(const char *OpenConfig_, const char *OpenStatus_, int ThingId);
int database_insert_into_lightDetectors(const char *LightConfig_, const char *LightStatus_, int ThingId);
int database_insert_into_things(const char *ThingType, const char *ThingStatus_, int BoxId);
int database_insert_into_boxes(const char *BoxName, const char *BoxLocation, const char *BoxConfig_);
int database_open();
void database_close();

1550

應該在 database 新增一個 WhiteBoxes

CREATE TABLE WhiteBoxes (
    WhiteBoxId       INTEGER   PRIMARY KEY   NOT NULL,
    WhiteBoxUrl      TEXT   NOT NULL,
    WhiteBoxActive   INTEGER   PRIMARY KEY   NOT NULL
);

initialize 的時候就放個幾筆假想的 box 資料方便讓 get_common 有個清單可以做事

修正 iot_init.sql

BEGIN TRANSACTION;

DROP TABLE IF EXISTS WhiteBoxes;
DROP TABLE IF EXISTS Boxes;
DROP TABLE IF EXISTS Things;
DROP TABLE IF EXISTS OpenDetectors;
DROP TABLE IF EXISTS LightDetectors;

CREATE TABLE IF NOT EXISTS WhiteBoxes (
    WhiteBoxId       INTEGER   PRIMARY KEY   NOT NULL,
    WhiteBoxUrl      TEXT   NOT NULL,
    WhiteBoxActive   INTEGER   PRIMARY KEY   NOT NULL
);

INSERT INTO WhiteBoxes(WhiteBoxUrl, WhiteBoxActive) VALUES('http://192.168.1.113:8888/common', 0);

CREATE TABLE IF NOT EXISTS Boxes (
    BoxId         INTEGER   PRIMARY KEY   NOT NULL,
    BoxName       TEXT   NOT NULL,
    BoxLocation   TEXT   NOT NULL,
    BoxConfig_    TEXT   NOT NULL
);

CREATE TABLE Things (
    ThingId        INTEGER   PRIMARY KEY,
    ThingType      TEXT   NOT NULL,
    ThingStatus_   TEXT   NOT NULL,
    BoxId          INTEGER   NOT NULL   REFERENCES Boxes( BoxId )
);

CREATE TABLE OpenDetectors (
    OpenId        INTEGER   PRIMARY KEY,
    OpenConfig_   TEXT   NOT NULL,
    OpenStatus_   TEXT   NOT NULL,
    ThingId       INTEGER   NOT NULL   REFERENCES Things( ThingId )
);

CREATE TABLE LightDetectors (
    LightId        INTEGER   PRIMARY KEY,
    LightConfig_   TEXT   NOT NULL,
    LightStatus_   TEXT   NOT NULL,
    ThingId        INTEGER   NOT NULL   REFERENCES Things( ThingId )
);

COMMIT;
Add a custom footer
⚠️ **GitHub.com Fallback** ⚠️