I need to create a template for SQL

Before you yell at me how Hugo is a SSG, let me preface that I know that.
I am coming from Jekyll and my solution to a full site search is a small rest like php script to have a full text search with javascript on my site, without relying on external services.

So what I want to know is, how I can use the template system to generate a sql file in Hugo? In Jekyll I achieve this with this frontmatter:

---
layout: null
sitemap:
  exclude: 'yes'
---

This makes sure no HTML is getting in the way and I strip the HTML from the markdown, getting pure text:

{% for post in site.posts %}
INSERT INTO database.blog (the_date, title, body, url, excerpt, image_url)
VALUES(
    '{{ post.date | date: "%Y-%m-%d" }}',
	'{{ post.title | striphtml | escape }}',
	'{{ post.content | striphtml | escape }}',
    '{{ site.url }}{{ post.url }}',
	'{{ post.excerpt | striphtml | escape }}',
	'{{ post.post_image | striphtml  }}'
);
{% endfor %}

Does anyone have any hints and tips to achieve this?

What you probably end up doing is to create a custom “SQL output format”, and then configure your site to publish the home page in both HTML and SQL. That way you can create a template in /layouts/index.sql (or something).

1 Like

Took me a while, but I have finally got it.
Here is the solution for anyone who wants to attempt something similar.

config.toml:

[mediaTypes]
[mediaTypes."text/sql"]
    suffixes = ["sql"]

[outputFormats]
[outputFormats.SQL]
    mediaType = "text/sql"

[outputs]
    home = ["sql", "html"]

baseof.sql.sql:

DROP TABLE IF EXISTS database.blog;

CREATE TABLE database..blog (
	id INT NOT NULL AUTO_INCREMENT,
	published DATE NOT NULL,
	title TEXT NOT NULL,
	body TEXT NOT NULL,
	url varchar(100) NOT NULL,
	image varchar(50) NOT NULL,
	CONSTRAINT id_PK PRIMARY KEY (id)
)
ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_general_ci;
CREATE FULLTEXT INDEX blog_body_IDX ON database.blog (body);

{{ block "main" .}}
{{ end }}

home.sql.sql:

{{ define "main" }}
{{ range (where .Pages "Section" "ne" "gist") }}
        {{ range .Pages }}
        INSERT INTO database.blog (published, title, body, url, image)
        VALUES(
            '{{ .Date.Format "01.02.2006" }}',
            '{{ plainify .Title }}',
            '{{ (plainify .Content) }}',
            '{{ .Permalink }}',
            '{{ .Params.post_image }}'
        );
        {{ end }}
    {{ end }}
{{ end }}

The range has a negative selection, since I do not need my gists to be searchable.
This is put into a mysql database and queried with js to create a site search.

5 Likes

This topic was automatically closed 2 days after the last reply. New replies are no longer allowed.