Big News: Socket raises $60M Series C at a $1B valuation to secure software supply chains for AI-driven development.Announcement →
jekyll-sqlite
Artifacts are distributable versions of a package. It typically includes the source code and metadata
Advanced tools
jekyll-sqlite - rubygems Package Compare versions
| theme: just-the-docs | ||
| title: Jekyll SQLite | ||
| description: >- | ||
| generator plugin to lets you use SQLite database instead of data files as a | ||
| data source. It lets you easily create APIs and websites from a SQLite | ||
| database, by linking together a database file, your template, and the relevant | ||
| queries. | ||
| baseurl: "/jekyll-sqlite" | ||
| url: "https://captnemo.in" | ||
| github_username: captn3m0 | ||
| # https://just-the-docs.com/docs/configuration/ | ||
| favicon_ico: https://jekyllrb.com/favicon.ico | ||
| aux_links: | ||
| GitHub: | ||
| - "https://github.com/captn3m0/jekyll-sqlite/" | ||
| RubyGems: | ||
| - "https://rubygems.org/gems/jekyll-sqlite" | ||
| gh_edit_link: true | ||
| gh_edit_link_text: "Edit this page on GitHub." | ||
| gh_edit_repository: "https://github.com/captn3m0/jekyll-sqlite" # the github URL for your repo | ||
| gh_edit_branch: "main" | ||
| gh_edit_source: docs | ||
| gh_edit_view_mode: "edit" | ||
| callouts: | ||
| demo: | ||
| color: green | ||
| opacity: 0.5 | ||
| note: | ||
| color: yellow | ||
| opacity: 0.3 | ||
| # https://jekyllrb.com/docs/configuration/front-matter-defaults/ | ||
| defaults: | ||
| - scope: | ||
| path: "" | ||
| values: | ||
| layout: default | ||
| encoding: utf-8 | ||
| markdown: kramdown | ||
| strict_front_matter: true | ||
| # Silence Saas deprecation warnings, to be removed after this is fixed in just-the-docs | ||
| sass: | ||
| quiet_deps: true # https://github.com/just-the-docs/just-the-docs/issues/1541 | ||
| silence_deprecations: ['import'] |
| _site | ||
| .sass-cache | ||
| .jekyll-cache | ||
| .jekyll-metadata | ||
| vendor |
| --- | ||
| permalink: /404.html | ||
| layout: page | ||
| --- | ||
| <h1>404</h1> | ||
| <p><strong>Page not found :(</strong></p> | ||
| <p>The requested page could not be found.</p> |
+29
| --- | ||
| title: Demo | ||
| permalink: /demo.html | ||
| --- | ||
| 🏁 A fully-functional demo website that uses this plugin is available at | ||
| [northwind.captnemo.in](https://northwind.captnemo.in). The source code for | ||
| the demo is available at [captn3m0/northwind](https://github.com/captn3m0/northwind). | ||
| You can find more details at the demo page. | ||
| Here is a screenshot: | ||
|  | ||
| It relies on all features of the plugin, along with using `jekyll-datapage_gen` | ||
| plugin to generate individual pages for each data item. | ||
| 1. A [per-page query](usage/#per-page-queries) is used on the restock page to generate list of | ||
| products that need to be restocked. [source](https://github.com/captn3m0/northwind/blob/main/restock.md?plain=1) | ||
| 2. Customers, Orders, Products, Categories are set as global data items | ||
| in [config.yml](https://github.com/captn3m0/northwind/blob/main/_config.yml) | ||
| 3. `site.data.categories[*].products` is filled using a parameterised query | ||
| in [`config.yml`](https://github.com/captn3m0/northwind/blob/main/_config.yml#L47-L49) | ||
| 4. Featured Product and Employee of the Month, shown on homepage are set by a query | ||
| in `config.yml`, but the query parameters are set in [`_data`](https://github.com/captn3m0/northwind/tree/main/_data) | ||
| directory as YML files. | ||
| 5. The datapage plugin config generates a page for every product and customer. | ||
| The database is a trimmed-version of the northwind database from https://github.com/jpwhite3/northwind-SQLite3. |
| source "https://rubygems.org" | ||
| gem "jekyll", "~> 4.4.1" | ||
| gem "just-the-docs" | ||
| gem "logger", "~> 1.7" |
Sorry, the diff of this file is not supported yet
Sorry, the diff of this file is not supported yet
| --- | ||
| title: Home | ||
| nav_order: 0 | ||
| --- | ||
| A Jekyll generator plugin to lets you use SQLite databases instead of [Data Files][df] as a | ||
| data source. It lets you easily create APIs and websites from a SQLite | ||
| database, by linking together a database file, your template, and the relevant | ||
| queries. | ||
| Jekyll's Data Files are great, but they are limited to YAML/JSON/TSV/CSV file | ||
| formats - this plugin gives you another option: SQLite databases. | ||
| It supports site-level queries, per-page queries, and prepared queries that can | ||
| use existing data (possibly generated via more queries) as parameters. | ||
| The primary usecase is to **avoid Liquid Hell**, wherein you're left mangling | ||
| multiple data sources from CSV/JSON/YAML files using liquid templating by | ||
| saving temporary variables, creating maps, and so on. SQL is a decent language | ||
| for reshaping datasets - supporting joins, filters, and aggregations. So this | ||
| allows you to use SQL for reshaping your data, and then use liquid | ||
| for what it was meant for - presentation and templating. | ||
| [](https://github.com/captn3m0/jekyll-sqlite/actions/workflows/main.yml) [](https://badge.fury.io/rb/jekyll-sqlite) | ||
| [df]: https://jekyllrb.com/docs/datafiles/ "Data Files at Jekyll Docs site" |
| --- | ||
| title: Installation | ||
| --- | ||
| Add this line to your site's `Gemfile`: | ||
| ```ruby | ||
| gem 'jekyll-sqlite' | ||
| ``` | ||
| And then add this line to your site's `_config.yml`: | ||
| ```yml | ||
| plugins: | ||
| - jekyll_sqlite | ||
| ``` | ||
| See [Usage](/jekyll-sqlite/usage/) for next steps. | ||
| --- | ||
| Note that only supported versions of [Ruby](https://endoflife.date/ruby) | ||
| and [Jekyll](https://endoflife.date/ruby) are supported. |
| --- | ||
| title: Using with Datapage Plugin | ||
| parent: Usage | ||
| nav_order: 2 | ||
| --- | ||
| The Jekyll [Datapage Generator](https://github.com/avillafiorita/jekyll-datapage_gen) | ||
| plugin allows you to specify data files for which we want to | ||
| generate one page per record. | ||
| You can use it alongside this plugin to generate data from a SQLite database, | ||
| and generate a page per row of your resultset. | ||
| This is how a simple configuration would look: | ||
| ```yaml | ||
| # for the sqlite plugin | ||
| sqlite: | ||
| - data: restaurants | ||
| file: _db/reviews.db | ||
| query: SELECT id, name, last_review_date > 1672531200 as active, address FROM restaurants; | ||
| # for the datapage_gen plugin | ||
| page_gen: | ||
| - data: restaurants | ||
| # The layout used for each generated page _layouts/restaurant.html | ||
| template: restaurant | ||
| page_data_prefix: restaurants | ||
| name: id | ||
| title: name | ||
| filter: active | ||
| ``` | ||
| This will automatically generate a file for each restaurant `restaurants/# | ||
| {id}.html` file with the layout `_layouts/restaurant.html` and page.id, | ||
| page.name, page.active set and page.title set to restaurant name. Query data is | ||
| accessed in the page template via `{%raw%}{{ page.restaurants.address }}{%endraw%}` - the | ||
| namespace set in `page_data_prefix`. | ||
| Note that the `datapage_gen` plugin will run _after_ the `jekyll-sqlite` plugin, if you generate any pages with per-page queries, these queries will not execute. | ||
| ## Demo Example | ||
| The following example comes from the [Demo](../demo/). | ||
| The following datapage configuration in `_config.yml`: | ||
| ```yml | ||
| page_gen: | ||
| - data: products | ||
| template: product # _layouts/product.html | ||
| page_data_prefix: product | ||
| title: ProductName | ||
| name: ProductID | ||
| extension: html | ||
| ``` | ||
| will generate a page for each product in the `site.data.products` array. | ||
| In order to get data into the array, we can use: | ||
| ```yml | ||
| sqlite: | ||
| - data: products | ||
| file: _db/northwind.db | ||
| query: SELECT * from Products | ||
| ``` | ||
| Here's a screenshot of how it looks: | ||
|  | ||
| See it in action at <https://northwind.captnemo.in/products/1.html> |
| --- | ||
| title: Dynamic DB File | ||
| parent: Usage | ||
| nav_order: 3 | ||
| --- | ||
| If you want to select the database filename via an environment variable, | ||
| you can use the following options as a workaround: | ||
| ## Using `envsubst` from GNU gettext | ||
| You can install it via [brew](https://formulae.brew.sh/formula/gettext) | ||
| or [on linux](https://repology.org/project/gettext/versions). | ||
| First, define your database filename, | ||
| and create a new configuration template file. | ||
| ```sh | ||
| export JEKYLL_DB=events-blr.db | ||
| cp _config.yml _config.txt | ||
| ``` | ||
| Then, use the database name in your new ocnfig file | ||
| ```yaml | ||
| sqlite: | ||
| data: events | ||
| file: $JEKYLL_DB | ||
| query: SELECT * FROM events | ||
| ``` | ||
| Then, use `envsubst` to generate the `_config.yml` | ||
| ```bash | ||
| envsubst < "_config.txt" > "_config.yml" | ||
| ``` | ||
| ## Using multiple configuration files | ||
| You can define your `sqlite` parameter multiple times | ||
| across multiple files, using different database filenames | ||
| For eg, `_config-blr.yml` could only include: | ||
| ``` | ||
| sqlite: | ||
| data: events | ||
| file: events-blr.db | ||
| query: SELECT * FROM events | ||
| ``` | ||
| And you can run jekyll using `jekyll --config _config.yml,_config-blr.yml` | ||
| However, this requires duplicating your query across multiple files. |
| --- | ||
| title: Usage | ||
| has_toc: false | ||
| permalink: /usage/ | ||
| --- | ||
| Update your `_config.yml` to define your data sources with your SQLite database. | ||
| ```yml | ||
| ... | ||
| sqlite: | ||
| - data: customers | ||
| file: *db | ||
| query: SELECT * from Customers | ||
| ``` | ||
| Then, you can use the `site.data` attributes accordingly: | ||
| ```liquid{%raw%} | ||
| {{ site.data.customers | jsonify }}{%endraw%} | ||
| ``` |
| --- | ||
| title: Nested Queries | ||
| parent: Usage | ||
| nav_order: 4 | ||
| --- | ||
| Starting from `0.2.0`, queries can be nested infinitely. | ||
| The following configuration is used in the [demo](../demo/): | ||
| ```yaml | ||
| sqlite: | ||
| - data: regions | ||
| file: *db | ||
| query: | | ||
| SELECT RegionID, RegionDescription FROM Regions | ||
| ORDER BY RegionID | ||
| - data: regions.territories | ||
| file: *db | ||
| query: | | ||
| SELECT TerritoryID, TerritoryDescription FROM Territories | ||
| WHERE RegionID = :RegionID | ||
| ORDER BY TerritoryDescription | ||
| - data: regions.territories.EmployeeIDs | ||
| file: *db | ||
| query: | | ||
| SELECT T.EmployeeID as EmployeeID, FirstName,LastName | ||
| FROM EmployeeTerritories T,Employees | ||
| WHERE T.TerritoryID = :TerritoryID | ||
| AND T.EmployeeID = Employees.EmployeeID | ||
| ``` | ||
| The first query generates `site.data.regions` as a list. The second query | ||
| sets territories inside each of the regions, and the third query | ||
| sets the list of employees inside each territory. | ||
| {: .note } | ||
| > Per Page Query | ||
| > | ||
| > On the Demo website, you can see the result at | ||
| > [the regions page](https://northwind.captnemo.in/regions.html) | ||
| > where each region is broken into territories, with the name | ||
| > of the employee under each region. |
| --- | ||
| title: Per-page Queries | ||
| nav_order: 0 | ||
| parent: Usage | ||
| --- | ||
| The exact same syntax can be used on a per-page basis to generate data within | ||
| each page. This is helpful for keeping page-specific queries within the page | ||
| itself. Here's an example: | ||
| ```yaml | ||
| --- | ||
| FeaturedSupplierID: 2 | ||
| sqlite: | ||
| - data: suppliers | ||
| file: "_db/northwind.db" | ||
| query: "SELECT CompanyName, SupplierID FROM suppliers ORDER BY SupplierID" | ||
| - data: suppliers.products | ||
| # This is a prepared query, where SupplierID is coming from the previous query. | ||
| file: "_db/northwind.db" | ||
| query: "SELECT ProductName, CategoryID,UnitPrice FROM products WHERE SupplierID = :SupplierID" | ||
| # :FeaturedSupplierID is picked up automatically from the page frontmatter. | ||
| - data: FeaturedSupplier | ||
| file: "_db/northwind.db" | ||
| query: "SELECT * SupplierID = :FeaturedSupplierID" | ||
| --- | ||
| {%raw%}{{page.suppliers|jsonify}}{%endraw%} | ||
| ``` | ||
| This will generate a `page.suppliers` array with all the suppliers, and a `page.FeaturedSupplier` object with the details of the featured supplier. | ||
| Each supplier will have a `products` array with all the products for that supplier. | ||
| {: .note } | ||
| > Per Page Query | ||
| > | ||
| > On the Demo website, a per-page query | ||
| > is used on the restock page to generate | ||
| > list of products that need to be restocked. You can see the | ||
| > [source](https://github.com/captn3m0/northwind/blob/main/restock.md?plain=1) | ||
| > and the [resulting page](https://northwind.captnemo.in/restock.html) |
| --- | ||
| title: Parametrized Queries | ||
| parent: Usage | ||
| nav_order: 1 | ||
| --- | ||
| This plugin supports prepared queries with parameter binding. This lets you | ||
| use existing data from a previous query, or some other source (such as | ||
| `site.data.*` or `page.*`) as a parameter in your query. | ||
| Say you have a YAML file defining your items (`data/books.yaml`): | ||
| ```yaml | ||
| - id: 31323952-2708-42dc-a995-6006a23cbf00 | ||
| name: Time Travel with a Rubber Band | ||
| - id: 5c8e67a0-d490-4743-b5b8-8e67bd1f95a2 | ||
| name: The Art of Cache Invalidation | ||
| ``` | ||
| and the prices for the items in your SQLite database, the following configuration will enrich the `items` array with the price: | ||
| ```yaml | ||
| sql: | ||
| - data: items.books | ||
| file: books.db | ||
| query: SELECT price, author FROM pricing WHERE id =:id | ||
| ``` | ||
| This would allow the following Liquid loop to be written: | ||
| ```liquid{%raw%} | ||
| {% for item in site.data.items %} | ||
| {{item.meta.price}}, {{item.meta.author}} | ||
| {% endfor %}{%endraw%} | ||
| ``` |
+6
-3
@@ -1,4 +0,4 @@ | ||
| require: rubocop-rake | ||
| plugins: rubocop-rake | ||
| AllCops: | ||
| TargetRubyVersion: 3.0 | ||
| TargetRubyVersion: 3.2 | ||
| NewCops: enable | ||
@@ -11,2 +11,3 @@ Exclude: | ||
| - "test/_plugins/jekyll_sqlite_generator.rb" | ||
| - "docs/**/*" | ||
| Style/StringLiterals: | ||
@@ -24,2 +25,4 @@ Enabled: true | ||
| Metrics/AbcSize: | ||
| Max: 15 | ||
| Max: 20 | ||
| Metrics/MethodLength: | ||
| Max: 100 |
+21
-0
@@ -0,3 +1,24 @@ | ||
| --- | ||
| title: Changelog | ||
| nav_order: "ZZZ" | ||
| --- | ||
| All notable changes to this project will be documented in this file. | ||
| The format is based on [Keep a Changelog](https://keepachangelog.com/en/1.1.0/), | ||
| and this project adheres to [Semantic Versioning](https://semver.org/spec/v2.0.0.html). | ||
| ## [Unreleased] | ||
| ## [0.2.0] - 2025-08-23 | ||
| - Multiple-levels of nesting is now supported | ||
| - Slight performance improvement by keeping database open for entire plugin run | ||
| ## [0.1.6] - 2025-07-31 | ||
| - Drop Ruby 3.1 | ||
| - sqlite3 requirement to `2.7.3` | ||
| ## [0.1.5] - 2024-07-31 | ||
| - sqlite3 requirement changed to `1.6` | ||
| ## [0.1.4] - 2024-07-17 | ||
@@ -4,0 +25,0 @@ - Per-page queries are now supported via a `sqlite` config block in the front matter. |
+5
-8
@@ -9,10 +9,7 @@ # frozen_string_literal: true | ||
| # These are development dependencies | ||
| gem "jekyll", "~> 4.0" | ||
| gem "rake", "~> 13.0" | ||
| gem "rubocop", "~> 1.21" | ||
| gem "rubocop-rake", "~> 0.6.0" | ||
| gem "jekyll", "~> 4.4", ">= 4.4.1" | ||
| gem "rake", "~> 13.3" | ||
| gem "rubocop", "~> 1.80" | ||
| gem "rubocop-rake", "~> 0.7" | ||
| # Ruby 3.4 preparedness | ||
| gem "base64", "~> 0.2.0" | ||
| gem "bigdecimal", "~> 3.1" | ||
| gem "csv", "~> 3.3" | ||
| gem "logger", "~> 1.7" |
@@ -14,3 +14,3 @@ # frozen_string_literal: true | ||
| spec.homepage = "https://github.com/captn3m0/jekyll-sqlite" | ||
| spec.required_ruby_version = ">= 3.0.0" | ||
| spec.required_ruby_version = ">= 3.2.0" | ||
@@ -31,5 +31,4 @@ spec.metadata["homepage_uri"] = spec.homepage | ||
| spec.require_paths = ["lib"] | ||
| spec.add_dependency "sqlite3", "~> 2.0" | ||
| spec.add_dependency "sqlite3", "~> 2.7.3" | ||
| spec.metadata["rubygems_mfa_required"] = "true" | ||
| end |
@@ -11,18 +11,36 @@ # frozen_string_literal: true | ||
| ## | ||
| # Split the given key using dots and return the last part | ||
| # customers.order -> order | ||
| def get_tip(name) | ||
| name.split(".")[-1] | ||
| def get_database(file) | ||
| return @db[file] if @db.key?(file) | ||
| @db[file] = SQLite3::Database.new file, readonly: true | ||
| end | ||
| def close_all_databases | ||
| @db.each_value(&:close) | ||
| end | ||
| ## | ||
| # Get the root of where we are generating the data | ||
| def get_root(root, db_name) | ||
| db_name.split(".")[0..-2].each do |p| | ||
| root = root[p] | ||
| rescue KeyError | ||
| raise "Jekyll SQLite: Invalid root. #{p} not found while iterating to #{db_name}" | ||
| # Recursively attach query results to nested data structures | ||
| # Supports arbitrary levels of nesting (e.g., regions.territories.EmployeeIDs) | ||
| # Handles both arrays and hashes at each level | ||
| def attach_nested_data(root, path_segments, db, query) | ||
| return 0 if path_segments.empty? | ||
| if path_segments.size == 1 | ||
| key = path_segments.first | ||
| db.prepare(query) do |stmt| | ||
| _prepare_query(stmt, get_bind_params(root)) | ||
| root[key] = stmt.execute.to_a | ||
| end | ||
| return root[key].size | ||
| end | ||
| root | ||
| first, *remaining = path_segments | ||
| current_level = root[first] | ||
| if current_level.is_a?(Array) | ||
| current_level.sum { |item| attach_nested_data(item, remaining, db, query) } | ||
| else | ||
| attach_nested_data(current_level, remaining, db, query) | ||
| end | ||
| end | ||
@@ -43,33 +61,4 @@ | ||
| ## | ||
| # Internal function to generate data given | ||
| # root: a Hash-Like root object (site.data, site.data.*, page.data) | ||
| # key: string as the key to use to attach the data to the root | ||
| # db: SQLite3 Database object to execute the query on | ||
| # query: string containing the query to execute | ||
| # Sets root[db_name] = ResultSet of the query, as an array | ||
| # Returns the count of the result set | ||
| def _gen_data(root, key, db, query) | ||
| db.prepare(query) do |stmt| | ||
| _prepare_query stmt, get_bind_params(root) | ||
| root[key] = stmt.execute.to_a | ||
| end | ||
| root[key].count | ||
| end | ||
| ## | ||
| # Calls _gen_data for the given root | ||
| # iterates through the array if root is an array | ||
| def gen_data(root, ...) | ||
| if root.is_a? Array | ||
| # call gen_data for each item in the array | ||
| # and return the sum of all the counts | ||
| root.map { |item| gen_data(item, ...) }.sum | ||
| else | ||
| _gen_data(root, ...) | ||
| end | ||
| end | ||
| ## | ||
| # Validate given configuration object | ||
| def validate_config(config) | ||
| def valid_config?(config) | ||
| return false unless config.is_a? Hash | ||
@@ -91,5 +80,2 @@ return false unless config.key?("query") | ||
| ## | ||
| # Given a configuration, generate the data | ||
| # and attach it to the given data_root | ||
| def generate_data_from_config(root, config) | ||
@@ -99,23 +85,11 @@ key = config["data"] | ||
| file = config["file"] | ||
| SQLite3::Database.new file, readonly: true do |db| | ||
| db.results_as_hash = config.fetch("results_as_hash", true) | ||
| branch = get_root(root, key) | ||
| tip = get_tip(config["data"]) | ||
| count = gen_data(branch, tip, db, query) | ||
| Jekyll.logger.info "Jekyll SQLite:", "Loaded #{key}. Count=#{count}" | ||
| end | ||
| db = get_database(file) | ||
| db.results_as_hash = config.fetch("results_as_hash", true) | ||
| path_segments = key.split(".") | ||
| count = attach_nested_data(root, path_segments, db, query) | ||
| Jekyll.logger.info "Jekyll SQLite:", "Loaded #{key}. Count=#{count}" | ||
| end | ||
| ## | ||
| # Iterate through all the pages in the site | ||
| # and generate the data from the configuration | ||
| def gen_pages(site) | ||
| site.pages.each do |page| | ||
| gen(page.data, page) | ||
| end | ||
| end | ||
| ## | ||
| # Generate the data from the configuration | ||
@@ -127,5 +101,4 @@ # Takes as input the root where the data will be attached | ||
| def gen(root, config_holder) | ||
| sqlite_configs = config_holder["sqlite"] || [] | ||
| sqlite_configs.each do |config| | ||
| unless validate_config(config) | ||
| (config_holder["sqlite"] || []).each do |config| | ||
| unless valid_config?(config) | ||
| Jekyll.logger.error "Jekyll SQLite:", "Invalid Configuration. Skipping" | ||
@@ -141,6 +114,11 @@ next | ||
| def generate(site) | ||
| @db = {} | ||
| gen(site.data, site.config) | ||
| gen_pages(site) | ||
| site.pages.each do |page| | ||
| gen(page.data, page) | ||
| end | ||
| ensure | ||
| close_all_databases | ||
| end | ||
| end | ||
| end |
@@ -5,4 +5,4 @@ # frozen_string_literal: true | ||
| module Sqlite | ||
| VERSION = "0.1.5" | ||
| VERSION = "0.2.0" | ||
| end | ||
| end |
+19
-3
@@ -22,3 +22,2 @@ # frozen_string_literal: true | ||
| # rubocop:disable Metrics/AbcSize | ||
| # rubocop:disable Metrics/MethodLength | ||
| def validate_json | ||
@@ -58,5 +57,21 @@ file = "_site/data.json" | ||
| end | ||
| def validate_employees_json | ||
| file = "_site/employees.json" | ||
| regions = JSON.parse(File.read(file)) | ||
| assert regions.size == 4 | ||
| assert regions[0]["RegionID"] == 1, "First RegionID should be 1" | ||
| assert regions[0]["RegionDescription"] == "Eastern", "First Region is Eastern" | ||
| assert regions[-1]["RegionID"] == 4, "Four zotal Regions" | ||
| assert regions[0]["territories"].size == 19, "There should be 19 territories in Eastern" | ||
| assert regions[0]["territories"][0]["TerritoryID"] == "01730", "First TerritoryID should be 1" | ||
| assert regions[0]["territories"][0]["TerritoryDescription"] == "Bedford", "First TerritoryID should be Bedford" | ||
| bedford = regions[0]["territories"][0] | ||
| assert bedford == { | ||
| "TerritoryID" => "01730", | ||
| "TerritoryDescription" => "Bedford", | ||
| "EmployeeIDs" => [{ "EmployeeID" => 2, "FirstName" => "Andrew", "LastName" => "Fuller" }] | ||
| }, "Bedford should have Andrew Fuller" | ||
| end | ||
| # rubocop:enable Metrics/AbcSize | ||
| # rubocop:enable Metrics/MethodLength | ||
| task default: :rubocop | ||
@@ -70,2 +85,3 @@ | ||
| validate_page_json | ||
| validate_employees_json | ||
| end |
+10
-127
@@ -8,132 +8,13 @@ # Jekyll SQLite plugin | ||
| The primary usecase is to **avoid Liquid Hell**, wherein you're left mangling | ||
| multiple data sources from CSV/JSON/YAML files using liquid templating by | ||
| saving temporary variables, creating maps, and so on. SQL is a decent language | ||
| for reshaping datasets - supporting joins, filters, and aggregations. So this | ||
| allows you to use SQL for reshaping your data, and then use liquid | ||
| for what it was meant for - presentation and templating. | ||
| [](https://github.com/captn3m0/jekyll-sqlite/actions/workflows/main.yml) [](https://badge.fury.io/rb/jekyll-sqlite) | ||
| ## Installation | ||
| Documentation is now maintained at <https://captnemo.in/jekyll-sqlite/>. | ||
| Add this line to your site's `Gemfile`: | ||
| ```ruby | ||
| gem 'jekyll-sqlite' | ||
| ``` | ||
| And then add this line to your site's `_config.yml`: | ||
| ```yml | ||
| plugins: | ||
| - jekyll_sqlite | ||
| ``` | ||
| :warning: If you are using Jekyll < 3.5.0 use the `gems` key instead of `plugins`. | ||
| ## Usage | ||
| Update your `_config.yml` to define your data sources with your SQLite database. Please see | ||
| the `test` directory for a functional example with the [Northwind database](https://github.com/jpwhite3/northwind-SQLite3). | ||
| ```yml | ||
| ... | ||
| sqlite: | ||
| - data: customers | ||
| file: *db | ||
| query: SELECT * from Customers | ||
| ``` | ||
| Then, you can use the `site.data` attributes accordingly: | ||
| ```liquid | ||
| {{ site.data.customers | jsonify }} | ||
| ``` | ||
| ## Prepared Queries | ||
| This plugin supports prepared queries with parameter binding. This lets you | ||
| use existing data from a previous query, or some other source (such as | ||
| `site.data.*` or `page.*`) as a parameter in your query. | ||
| Say you have a YAML file defining your items (`data/books.yaml`): | ||
| ```yaml | ||
| - id: 31323952-2708-42dc-a995-6006a23cbf00 | ||
| name: Time Travel with a Rubber Band | ||
| - id: 5c8e67a0-d490-4743-b5b8-8e67bd1f95a2 | ||
| name: The Art of Cache Invalidation | ||
| ``` | ||
| and the prices for the items in your SQLite database, the following configuration will enrich the `items` array with the price: | ||
| ```yaml | ||
| sql: | ||
| - data: items.books | ||
| query: SELECT price, author FROM pricing WHERE id =:id | ||
| db: books.db | ||
| ``` | ||
| This would allow the following Liquid loop to be written: | ||
| ```liquid | ||
| {% for item in site.data.items %} | ||
| {{item.meta.price}}, {{item.meta.author}} | ||
| {% endfor %} | ||
| ``` | ||
| ## Per Page Queries | ||
| The exact same syntax can be used on a per-page basis to generate data within | ||
| each page. This is helpful for keeping page-specific queries within the page | ||
| itself. Here's an example: | ||
| ```yaml | ||
| --- | ||
| FeaturedSupplierID: 2 | ||
| sqlite: | ||
| - data: suppliers | ||
| file: "_db/northwind.db" | ||
| query: "SELECT CompanyName, SupplierID FROM suppliers ORDER BY SupplierID" | ||
| - data: suppliers.products | ||
| # This is a prepared query, where SupplierID is coming from the previous query. | ||
| file: "_db/northwind.db" | ||
| query: "SELECT ProductName, CategoryID,UnitPrice FROM products WHERE SupplierID = :SupplierID" | ||
| # :FeaturedSupplierID is picked up automatically from the page frontmatter. | ||
| - data: FeaturedSupplier | ||
| file: "_db/northwind.db" | ||
| query: "SELECT * SupplierID = :FeaturedSupplierID" | ||
| --- | ||
| {{page.suppliers|jsonify}} | ||
| ``` | ||
| This will generate a `page.suppliers` array with all the suppliers, and a `page.FeaturedSupplier` object with the details of the featured supplier. | ||
| Each supplier will have a `products` array with all the products for that supplier. | ||
| ## Generating Pages | ||
| It works well with the `datapage_gen` plugin: | ||
| See the [datapage_gen](https://github.com/avillafiorita/jekyll-datapage_gen) docs for more details. | ||
| Here's a sample configuration: | ||
| ```yaml | ||
| sqlite: | ||
| restaurants: | ||
| file: _db/reviews.db | ||
| sql: SELECT id, name, last_review_date > 1672531200 as active, address FROM restaurants; | ||
| page_gen: | ||
| - data: restaurants | ||
| template: restaurant | ||
| name: id | ||
| title: name | ||
| filter: active | ||
| ``` | ||
| This will automatically generate a file for each restaurant | ||
| restaurants/#{id}.html file with the layout `_layouts/restaurant.html` and page.id, page.name, page.active set and page.title set to restaurant name | ||
| Note that the `datapage_gen` plugin will run _after_ the `jekyll-sqlite` plugin, | ||
| if you generate any pages with per-page queries, these queries will not execute. | ||
| ## Development | ||
| After checking out the repo, run `bin/setup` to install dependencies. You can also run `bin/console` for an interactive prompt that will allow you to experiment. | ||
| To install this gem onto your local machine, run `bundle exec rake install`. To release a new version, update the version number in `version.rb`, and then run `bundle exec rake release`, which will create a git tag for the version, push git commits and the created tag, and push the `.gem` file to [rubygems.org](https://rubygems.org). | ||
| ## Contributing | ||
@@ -143,4 +24,6 @@ | ||
| Note that only maintained versions of [Jekyll](https://endoflife.date/jekyll) and [Ruby](https://endoflife.date/ruby) are supported. | ||
| ## Code of Conduct | ||
| Everyone interacting in the Jekyll::Sqlite project's codebases, issue trackers, chat rooms and mailing lists is expected to follow the [code of conduct](https://github.com/captn3m0/jekyll-sqlite/blob/main/CODE_OF_CONDUCT.md). |
No alert changes
No metric changes