sqlite3_mod_vtable.cc 5.3 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238
  1. #include <string>
  2. #include <sstream>
  3. #include <sqlite3-binding.h>
  4. #include <sqlite3ext.h>
  5. #include <curl/curl.h>
  6. #include "picojson.h"
  7. #ifdef _WIN32
  8. # define EXPORT __declspec(dllexport)
  9. #else
  10. # define EXPORT
  11. #endif
  12. SQLITE_EXTENSION_INIT1;
  13. typedef struct {
  14. char* data; // response data from server
  15. size_t size; // response size of data
  16. } MEMFILE;
  17. MEMFILE*
  18. memfopen() {
  19. MEMFILE* mf = (MEMFILE*) malloc(sizeof(MEMFILE));
  20. if (mf) {
  21. mf->data = NULL;
  22. mf->size = 0;
  23. }
  24. return mf;
  25. }
  26. void
  27. memfclose(MEMFILE* mf) {
  28. if (mf->data) free(mf->data);
  29. free(mf);
  30. }
  31. size_t
  32. memfwrite(char* ptr, size_t size, size_t nmemb, void* stream) {
  33. MEMFILE* mf = (MEMFILE*) stream;
  34. int block = size * nmemb;
  35. if (!mf) return block; // through
  36. if (!mf->data)
  37. mf->data = (char*) malloc(block);
  38. else
  39. mf->data = (char*) realloc(mf->data, mf->size + block);
  40. if (mf->data) {
  41. memcpy(mf->data + mf->size, ptr, block);
  42. mf->size += block;
  43. }
  44. return block;
  45. }
  46. char*
  47. memfstrdup(MEMFILE* mf) {
  48. char* buf;
  49. if (mf->size == 0) return NULL;
  50. buf = (char*) malloc(mf->size + 1);
  51. memcpy(buf, mf->data, mf->size);
  52. buf[mf->size] = 0;
  53. return buf;
  54. }
  55. static int
  56. my_connect(sqlite3 *db, void *pAux, int argc, const char * const *argv, sqlite3_vtab **ppVTab, char **c) {
  57. std::stringstream ss;
  58. ss << "CREATE TABLE " << argv[0]
  59. << "(id int, full_name text, description text, html_url text)";
  60. int rc = sqlite3_declare_vtab(db, ss.str().c_str());
  61. *ppVTab = (sqlite3_vtab *) sqlite3_malloc(sizeof(sqlite3_vtab));
  62. memset(*ppVTab, 0, sizeof(sqlite3_vtab));
  63. return rc;
  64. }
  65. static int
  66. my_create(sqlite3 *db, void *pAux, int argc, const char * const * argv, sqlite3_vtab **ppVTab, char **c) {
  67. return my_connect(db, pAux, argc, argv, ppVTab, c);
  68. }
  69. static int my_disconnect(sqlite3_vtab *pVTab) {
  70. sqlite3_free(pVTab);
  71. return SQLITE_OK;
  72. }
  73. static int
  74. my_destroy(sqlite3_vtab *pVTab) {
  75. sqlite3_free(pVTab);
  76. return SQLITE_OK;
  77. }
  78. typedef struct {
  79. sqlite3_vtab_cursor base;
  80. int index;
  81. picojson::value* rows;
  82. } cursor;
  83. static int
  84. my_open(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor) {
  85. MEMFILE* mf;
  86. CURL* curl;
  87. char* json;
  88. CURLcode res = CURLE_OK;
  89. char error[CURL_ERROR_SIZE] = {0};
  90. char* cert_file = getenv("SSL_CERT_FILE");
  91. mf = memfopen();
  92. curl = curl_easy_init();
  93. curl_easy_setopt(curl, CURLOPT_SSL_VERIFYPEER, 1);
  94. curl_easy_setopt(curl, CURLOPT_SSL_VERIFYHOST, 2);
  95. curl_easy_setopt(curl, CURLOPT_USERAGENT, "curl/7.29.0");
  96. curl_easy_setopt(curl, CURLOPT_URL, "https://api.github.com/repositories");
  97. if (cert_file)
  98. curl_easy_setopt(curl, CURLOPT_CAINFO, cert_file);
  99. curl_easy_setopt(curl, CURLOPT_FOLLOWLOCATION, 1);
  100. curl_easy_setopt(curl, CURLOPT_ERRORBUFFER, error);
  101. curl_easy_setopt(curl, CURLOPT_WRITEDATA, mf);
  102. curl_easy_setopt(curl, CURLOPT_WRITEFUNCTION, memfwrite);
  103. res = curl_easy_perform(curl);
  104. curl_easy_cleanup(curl);
  105. if (res != CURLE_OK) {
  106. std::cerr << error << std::endl;
  107. return SQLITE_FAIL;
  108. }
  109. picojson::value* v = new picojson::value;
  110. std::string err;
  111. picojson::parse(*v, mf->data, mf->data + mf->size, &err);
  112. memfclose(mf);
  113. if (!err.empty()) {
  114. delete v;
  115. std::cerr << err << std::endl;
  116. return SQLITE_FAIL;
  117. }
  118. cursor *c = (cursor *)sqlite3_malloc(sizeof(cursor));
  119. c->rows = v;
  120. c->index = 0;
  121. *ppCursor = &c->base;
  122. return SQLITE_OK;
  123. }
  124. static int
  125. my_close(cursor *c) {
  126. delete c->rows;
  127. sqlite3_free(c);
  128. return SQLITE_OK;
  129. }
  130. static int
  131. my_filter(cursor *c, int idxNum, const char *idxStr, int argc, sqlite3_value **argv) {
  132. c->index = 0;
  133. return SQLITE_OK;
  134. }
  135. static int
  136. my_next(cursor *c) {
  137. c->index++;
  138. return SQLITE_OK;
  139. }
  140. static int
  141. my_eof(cursor *c) {
  142. return c->index >= c->rows->get<picojson::array>().size() ? 1 : 0;
  143. }
  144. static int
  145. my_column(cursor *c, sqlite3_context *ctxt, int i) {
  146. picojson::value v = c->rows->get<picojson::array>()[c->index];
  147. picojson::object row = v.get<picojson::object>();
  148. const char* p = NULL;
  149. switch (i) {
  150. case 0:
  151. p = row["id"].to_str().c_str();
  152. break;
  153. case 1:
  154. p = row["full_name"].to_str().c_str();
  155. break;
  156. case 2:
  157. p = row["description"].to_str().c_str();
  158. break;
  159. case 3:
  160. p = row["html_url"].to_str().c_str();
  161. break;
  162. }
  163. sqlite3_result_text(ctxt, strdup(p), strlen(p), free);
  164. return SQLITE_OK;
  165. }
  166. static int
  167. my_rowid(cursor *c, sqlite3_int64 *pRowid) {
  168. *pRowid = c->index;
  169. return SQLITE_OK;
  170. }
  171. static int
  172. my_bestindex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo) {
  173. return SQLITE_OK;
  174. }
  175. static const sqlite3_module module = {
  176. 0,
  177. my_create,
  178. my_connect,
  179. my_bestindex,
  180. my_disconnect,
  181. my_destroy,
  182. my_open,
  183. (int (*)(sqlite3_vtab_cursor *)) my_close,
  184. (int (*)(sqlite3_vtab_cursor *, int, char const *, int, sqlite3_value **)) my_filter,
  185. (int (*)(sqlite3_vtab_cursor *)) my_next,
  186. (int (*)(sqlite3_vtab_cursor *)) my_eof,
  187. (int (*)(sqlite3_vtab_cursor *, sqlite3_context *, int)) my_column,
  188. (int (*)(sqlite3_vtab_cursor *, sqlite3_int64 *)) my_rowid,
  189. NULL, // my_update
  190. NULL, // my_begin
  191. NULL, // my_sync
  192. NULL, // my_commit
  193. NULL, // my_rollback
  194. NULL, // my_findfunction
  195. NULL, // my_rename
  196. };
  197. static void
  198. destructor(void *arg) {
  199. return;
  200. }
  201. extern "C" {
  202. EXPORT int
  203. sqlite3_extension_init(sqlite3 *db, char **errmsg, const sqlite3_api_routines *api) {
  204. SQLITE_EXTENSION_INIT2(api);
  205. sqlite3_create_module_v2(db, "github", &module, NULL, destructor);
  206. return 0;
  207. }
  208. }