Skip to content
forked from x2bool/xlite

A SQLite extension which loads a Google Sheet as a virtual table.

License

Notifications You must be signed in to change notification settings

0x6b/libgsqlite

 
 

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

37 Commits
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

libgsqlite

A SQLite extension which loads a Google Sheet as a virtual table.

demo.mp4

Tested Platform

  • SQLite 3.39.2
  • Rust 1.62.1-aarch64-apple-darwin
  • macOS 12.5 (Monterey) on Apple M1 MAX

Getting Started

Setup Google Cloud

Create a Project

  1. Log in to the Google Cloud console.
  2. Go to the Manage resources page.
  3. On the Select organization drop-down list at the top of the page, select the organization resource in which you want to create a project.
  4. Click Create Project.
  5. In the New Project window that appears, enter a project name, say libgsqlite, and select a billing account as applicable.
  6. Enter the parent organization or folder resource in the Location box.
  7. When you're finished entering new project details, click Create.

Enable Google Sheets API for the Project

  1. Go to the API Library page.
  2. From the projects list, select the project you just created.
  3. In the API Library, select Google Sheets API.
  4. On the API page, click Enable.

Setup Google OAuth Consent Screen

  1. Go to the OAuth consent screen page.
  2. Select Internal as User Type, then click Create
  3. Add required information like an app name (libgsqlite) and support email address.
  4. Click Save and Continue.
  5. Click Add or Remove Scopes.
  6. On the dialog that appears, select the scope .../auth/spreadsheets.readonly (See all your Google Sheets spreadsheets) and click Update.
  7. Click Save and Continue.
  8. Click Back to Dashboard.

Create a Credential

  1. Go to the Credentials page.
  2. Click Create CredentialsOAuth Client ID.
  3. Select Desktop app as Application Type.
  4. Type libgsqlite as Name.
  5. Click Download JSON to save your Client ID and Client Secret locally.

Create a Sample Spreadsheet

  1. Go to sheet.new to create a new spreadsheet, then copy and paste following data.
Employee Number First Name Last Name Department
1 Christine Haas A00
2 Michael Thompson B01
3 Sally Kwan C01
4 John Beyer E01
5 Irving Stern D11
6 Eva Pulaski E01
  1. Copy the URL of the spreadsheet.

Query the Spreadsheet with SQLite

  1. Setup required environment variables with the credential:
    $ export LIBGSQLITE_GOOGLE_CLIENT_ID=... # client_id property in the downloaded JSON
    $ export LIBGSQLITE_GOOGLE_CLIENT_SECRET=... # client_secret property
  2. Launch SQLite:
    $ sqlite3
  3. Load the extension:
    .load libgsqlite # or "gsqlite" on Windows
    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.
  4. Create a virtual table for your spreadsheet by providing ID (url of the spreadsheet), SHEET (sheet name), and RANGE for module arguments. All three arguments are mandatory. You'll be navigated to Google OAuth consent screen to get a secret to access the spreadsheet. You can create multiple virtual tables from different spreadsheets.
    CREATE VIRTUAL TABLE employees USING gsqlite(
        ID 'https://docs.google.com/spreadsheets/d/...', -- your spreadsheet URL
        SHEET 'Sheet1', -- name of the sheet
        RANGE 'A2:D7' -- range to fetch
    );
  5. Go back to your terminal, and run a query as usual:
    .mode column
    .headers on
    SELECT * FROM employees;
    SELECT * FROM employees WHERE D LIKE 'E%';

Contributing

Please read CONTRIBUTING for more detail.

Acknowledgements

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.

Limitations

  • The extension will load the spreadsheet only once while creating a virtual table. If you want to pick up recent changes, drop the table and create it again.
  • INSERT, UPDATE and DELETE statements won't be implemented. Welcome PRs.

Security

The extension is intended for use in personal, not-shared, environment. The Google Cloud secret will be cached for 59 minutes under the temporary directory (See std::env::temp_dir) with fixed name access_token.json for your convenience. Note that, as described at the doc, creating a file or directory with a fixed or predictable name may result in “insecure temporary file” security vulnerability.

Privacy

The extension never send your data to any server.

License

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

References

About

A SQLite extension which loads a Google Sheet as a virtual table.

Topics

Resources

License

Stars

Watchers

Forks

Languages

  • Rust 92.5%
  • C 7.5%