A SQLite extension which loads data from Soracom Harvest Data as a virtual table.

• SQLite 3.40.0
• Rust 1.65.0 (stable-aarch64-apple-darwin)
• macOS 12.6 (Monterey) on Apple M1 MAX
• SORACOM Harvest (English/Japanese)

$ git clone github.com/0x6b/libshsqlite
$ cd libshsqlite

1. Sign-up SORACOM to get a (virtual) SIM (English/Japanese)
2. Enable Soracom Harvest Data (English/Japanese)
3. Create a SAM user with following permission:

   {
     "statements": [
       {
         "api": [
           "Sim:getDataFromSim",
           "Subscriber:getDataFromSubscriber",
           "DataEntry:getDataEntries",
           "DataEntry:getDataEntry"
         ],
         "effect": "allow"
       }
     ]
   }

4. Generate and authentication information for the user and save it for future reference

See documentation (English/Japanese) for detail, or use deadly simple client in this repository (soracom_harvest_client), from your SIM or virtual SIM connected machine as follows:

$ cargo run -p soracom_harvest_client -- --udp hey # Say hello, via UDP

$ cargo build --release

1. Export required environment variables with the credential:

   $ export LIBSHSQLITE_AUTH_KEY_ID=keyId-.. # authKeyId
   $ export LIBSHSQLITE_AUTH_KEY_SECRET=secret-... # authKey

2. Launch SQLite:

   $ sqlite3

3. Load the extension (you have to use "shsqlite" on Windows):

   .load target/release/libshsqlite

   If you get Error: unknown command or invalid arguments: "load". Enter ".help" for help , your SQLite is not capable for loading an extension. For macOS, install it with brew install sqlite3, and use it.

1. Create a virtual table for your Soracom Harvest Data by providing IMSI (IMSI of target SIM) as module argument. See reference below for available arguments other than IMSI:

   CREATE VIRTUAL TABLE harvest_data USING shsqlite(IMSI 'imsi-of-your-sim', COVERAGE 'japan');

2. Run queries as usual:

   SELECT * FROM harvest_data;
   SELECT * FROM harvest_data WHERE value ->>'$.temperature' > 10;

CREATE VIRTUAL TABLE harvest_data USING shsqlite(
    IMSI '...',
    FROM '...',
    TO '...',
    COVERAGE 'japan|global',
    LIMIT '...'
);

Please read CONTRIBUTING for more detail.

An article, Extending SQLite with Rust to support Excel files as virtual tables | Sergey Khabibullin , and its companion repository x2bool/xlite, for great write up and inspiration.

• The extension will load the data only once while creating a virtual table. If you want to pick up recent data, drop the table and create it again. Dropping the table won't erase your data on Soracom Harvest.
• INSERT, UPDATE and DELETE statements won't be implemented.

The extension never send your data to any server.

This extension is released under the MIT License. See LICENSE for details.