AlaSQLのデータベースに外部ファイルやWebAPIを使う方法

アシアル情報教育研究所・所長の岡本です

Monaca(JavaScript)だけでSQL言語を学ぶ方法①
Monaca(JavaScript)だけでSQL言語を学ぶ方法②

先日投稿した上記の記事ではデータベースとして「二次元配列」を使用しました。

サンプルコードとしては手軽なのですが、実際にアプリを開発するタイミングになると以下のような課題が考えられます。

• 扱いたいデータが表計算ソフトの中にある（CSVに書き出してアプリに取り込みたい）
• 扱いたいデータがインターネット上にある(WebAPI・JSONなど)

AlaSQLには外部ファイルを扱う仕組みがあるので、今回はその機能を紹介したいと思います。
なお、外部ファイルを使うときの注意点として「非同期処理」の考え方が必要になります。
そのため、サンプルコードも若干、非同期を意識した書き方になることを予めご了承ください。

扱いたいデータが表計算ソフトの中にある場合

まずは表計算ソフトのデータをCSVファイル形式で保存してください。その上で、アプリの中に取り込みます。

ソースコードの例

FROM句のテーブル指定でcsv(ファイル名)と記述することでcsvファイルを表として扱えます。
なお、外部ファイルを使う場合は非同期になるため、alasql()関数でsqlを実行した後の処理もセットで指定する必要があります。

function doSelect(data) {
    console.log(data);
}
let query = "SELECT * FROM csv('sales.csv') AS sales";
alasql(query, doSelect);

実行結果

0: {id: 1, item_id: 1, price: 200, cost: 50}
1: {id: 2, item_id: 1, price: 180, cost: 50}
2: {id: 3, item_id: 1, price: 250, cost: 60}
3: {id: 4, item_id: 1, price: 200, cost: 60}
4: {id: 5, item_id: 1, price: 150, cost: 60}

先ほどの例ではsqlを実行した後の処理を予め関数で定義しましたが、JavaScriptの非同期処理に馴れている人は、promiseや無名関数を使った書き方の方が良いかもしれません。

let query = "SELECT * FROM csv('sales.csv') AS sales";

alasql.promise(query)
    .then(function(result){
        console.log(result);
});

無名関数は再利用を考慮しない使い捨ての関数を用意したいときに使うテクニックです。
また、JavaScriptのES6から導入されたアロー関数を使うと更に短く記述できます。

let query = "SELECT * FROM csv('sales.csv') AS sales";

alasql.promise(query)
    .then(result => console.log(result));

別のWebページにあるCSVファイルを利用する

URLを指定するだけで利用可能です。

ただし、CORSの関係でブロックされる可能性があるため，ファイルを何処に置くかは重要です。
今回はFJCTのmBaasにファイルを置きました。

https://mbaas.api.nifcloud.com/2013-09-01/applications/8LMX9TIFvXDwdJj8/publicFiles/sales.csv

function doSelect(data) {
    console.log(data);
}
let url = "https://mbaas.api.nifcloud.com/2013-09-01/applications/8LMX9TIFvXDwdJj8/publicFiles/sales.csv"
let query = "SELECT * FROM csv('" + url + "') AS sales";
alasql(query, doSelect);

実行結果（特に変化はありません）

0: {id: 1, item_id: 1, price: 200, cost: 50}
1: {id: 2, item_id: 1, price: 180, cost: 50}
2: {id: 3, item_id: 1, price: 250, cost: 60}
3: {id: 4, item_id: 1, price: 200, cost: 60}
4: {id: 5, item_id: 1, price: 150, cost: 60}

JSONファイルを使う方法

次のようなJSONファイル(zipcode.json)があったとします。

[{"code": "1000013", "prefcode": "13", "pref": "\u6771\u4eac\u90fd", "city": "\u5343\u4ee3\u7530\u533a", "area": "\u971e\u304c\u95a2"},
{"code": "1130034", "prefcode": "13", "pref": "\u6771\u4eac\u90fd", "city": "\u6587\u4eac\u533a", "area": "\u6e6f\u5cf6"}]

このようなファイルをAlaSQLで扱うこともjson()命令をFROM句を指定すれば可能です。

function doSelect(data) {
    console.log(data);
}
let query = "SELECT * FROM json('zipcode.json') AS zipcode";
alasql(query, doSelect);

取得結果は以下のようになります。

0: {code: "1000013", prefcode: "13", pref: "東京都", city: "千代田区", area: "霞が関"}
1: {code: "1130001", prefcode: "13", pref: "東京都", city: "文京区", area: "白山"}

二次元のJSONが返ってくるようなWebAPIを呼び出す場合にも、この方法が使えます。

二次元配列じゃない外部ファイルやAPIを扱うには？

外部のAPIやファイルが都合良くAlaSQLで扱えるような二次元配列とは限りません。例えば、このサイトで公開している「あんこ郵便番号API」は教育用に仕様をぎゅっと絞ってあるため、一次元の結果しか返ってきません。そのため、上記のように指定しても上手く動作しません。

次元数が足りない場合には、2次元配列に詰め直せば解決できます。JavaScriptのfetch命令を使ってJSONを取得しつつ、二次元に変換してからAlaSQLに渡してみましょう。

var url = 'https://api.anko.education/zipcode?zipcode=100-0013';
fetch(url)
    .then(function(response) {
        return response.json();
    })
    .then(function(address) {
        let result = alasql('SELECT * FROM ?', [[address]]);
        console.log(result);
});

[[address]]のように、配列を配列で囲んでいるのが鍵です。
取得結果は以下のようになります。

0: {code: "1000013", prefcode: "13", pref: "東京都", city: "千代田区", area: "霞が関"}

Monacaファイル上のjsonファイルをfetchで取得してAlaSQLで使う

JavaScriptのfetch命令はMonaca上のファイルにも使えますので、以下のような書き方も可能です。

var url = 'zipcode.json';
fetch(url)
    .then(function(response) {
        return response.json();
    })
    .then(function(address) {
        let result = alasql('SELECT * FROM ?', [address]);
        console.log(result);
});

CSVファイルをJavaScriptでfetchしてからAlaSQLに渡すには？

技術的には可能ですが、面倒です。
AlaSQLのcsv()命令と違って、JavaScriptのfetch命令はCSVをただの文字列として扱いますので、CSV文字列を二次元配列に変換する処理を自分で書く必要があります
（JSONの場合はfetch命令がJSONを文字列ではなくJSONとして扱えるため、楽）

一応、サンプルコードを掲載しておきます。

function csv2table(string) {
    let array = [];
    let records = string.split("\r\n");
    let record_headers = records[0].split(",");
    console.log(record_headers);
    for (let i = 1; i < records.length; i++) {
        let record = {};
        let record_values = records[i].split(",");

        for (let j = 0; j < record_headers.length; j++) {
            let key = record_headers[j];
            let value = parseInt(record_values[j]);
            record[key] = value;
        }
        array.push(record);       
    }
    return array;
}

let url = 'sales.csv';
fetch(url)
    .then(function(response) {
        return response.text();
    })
    .then(function(csv) {
        let sales = csv2table(csv);
        let result = alasql('SELECT price FROM ? AS sales', [sales]);
        console.log(result);
});