Skip to main content

reducing duplicate SQL in Go project

Recently I started writing reports for a client of mine. At first I thought it was going to be just a few reports but over time a number of things have happened. (a) more and more reports (b) even more reports (c) I'm getting lazy so my tools are starting to scale (d) I'm getting lazier and I also have a need to reuse code without cut, copy, paste. (e) the need to share code that the reports generate similar results when based on the same foundation.

While part of the implementation means using CTEs (common template expression) it's not the whole story as I implemented a complete reporting engine that exports to CSV, TSV, text tables, XSLX, JSON, DOT, go templates, and supports it's own DSL including loops and dynamic queries.

In my current implementation I store the SQL in bash, yes bash, shell scripts that export the SQL names like this:

export hello_cte="hello_cte (hello) as (select 'HELLO')"
export hello=";with ${hello_cte} select * from hello"

In one case I need to pass variables into the SQL:

export hello_cte="hello_cte (hello) as (select 'HELLO')"
declare Hello_${name}=";with ${hello_cte} select *, '${name}' from hello"

Then the SQL get's baked into the go code using my envtmpl project. envtmpl works with go's generate action to execute a template and replace the template variables with data from the environment... the SQL. (in the example above the ${name} is expanded by bash not go templates)

NOTE: top level queries are named with a leading uppercase letter and use camel case.

This framework works well for me as I have over 50 reports, however, there are two/three downsides. (1) because I'm using bash variables the SQL statements are losing their formatting [bash's multi-line strings are kludgy at best] (2) any benefit from a modern text editor with syntax highlighting is impossible even though some editors support multiple languages. (3) the interaction between bash and go is so tightly coupled it's going to make decoupling a challenge.

As an aside I've been looking for a SQL reformatter that is written or can be embedded into a go project. I'm not going to use a 3rd party service for the very obvious reasons.

It was the search for a reformatter that offered a glint into the future. I found dotsql. The dotsql project is essentially a librarian and an execution wrapper around go's sql. Give the Load() method the name of a file, reader, or string and it will be parsed into a map[string]string. Then the SQL can be accessed by Prepare, Exec, etc... however, there are two interesting methods. Raw() and QueryMap().  With these two methods I can use my CTE strategy above and get all the benefits I was hoping for as I described my problem space.

var (
        doc = `
-- name: hello_cte
hello_cte (hello) as (select 'HELLO')

-- name: hello
;with
{{.hello_cte}}
select * from hello_cte
        `
)

Notice the embedded {{.hello_cte}} in SQL.

Here is the complete source:

package main

import (
        "bytes"
        "log"
        "text/template"

        "github.com/gchaincl/dotsql"
)

var (
        doc = `
-- name: hello_cte
hello_cte (hello) as (select 'HELLO')
-- name: hello
;with
{{.hello_cte}}
select * from hello_cte
        `
)

func main() {
        d, err := dotsql.LoadFromString(doc)
        if err != nil {
                log.Printf("ERROR: %v", err)
        }
        sql, err := d.Raw("hello")
        if err != nil {
                log.Printf("ERROR: %v", err)
        }
        tmpl := template.Must(template.New("dotsql").Parse(sql))
        buf := bytes.NewBufferString("")
        err = tmpl.Execute(buf, d.QueryMap())
        if err != nil {
                log.Printf("ERROR: %v", err)
        }
        log.Printf("Finished:\n%s\n", buf)
}

And this is the output:

2015/09/03 11:56:12 Finished:
;with
hello_cte (hello) as (select 'HELLO')
select * from hello_cte

I still have to validate the dotsql behavior against my DSL but I'm confident it'll work since the '-- name:' seems to be a token used to separate the SQL statements from each other and my DSL uses '----[]----' for a similar but different purpose.

And eventually I'll also use go-bindata in order to embed the complete SQL string into the executable.

CRAP! Just as I wrote that last sentence I realized I could have accomplished the (almost) same thing with go-bindata. That exercise is left to the reader. dotsql has one advantage and that is more than one SQL statement in each file or a single master file. Where go-bindata requires a single file per SQL statement.

Comments

Popular posts from this blog

Entry level cost for CoreOS+Tectonic

CoreOS and Tectonic start their pricing at 10 servers. Managed CoreOS starts at $1000 per month for those first 10 servers and Tectonic is $5000 for the same 10 servers. Annualized that is $85K or at least one employee depending on your market. As a single employee company I'd rather hire the employee. Specially since I only have 3 servers.

The pricing is biased toward the largest servers with the largest capacities; my dual core 32GB i5 IntelNuc can never be mistaken for a 96-CPU dual or quad core DELL

If CoreOS does not figure out a different barrier of entry they are going to follow the Borland path to obscurity.

UPDATE 2017-10-30: With gratitude the CoreOS team has provided updated information on their pricing, however, I stand by my conclusion that the effective cost is lower when you deploy monster machines. The cost per node of my 1 CPU Intel NUC is the same as a 96 CPU server when you get beyond 10 nodes. I'll also reiterate that while my pricing notes are not currently…

eGalax touch on default Ubuntu 14.04.2 LTS

I have not had success with the touch drivers as yet.  The touch works and evtest also seems to report events, however, I have noticed that the button click is not working and no matter what I do xinput refuses to configure the buttons correctly.  When I downgraded to ubuntu 10.04 LTS everything sort of worked... there must have been something in the kermel as 10.04 was in the 2.6 kernel and 4.04 is in the 3.x branch.

One thing ... all of the documentation pointed to the wrong website or one in Taiwanese. I was finally able to locate the drivers again: http://www.eeti.com.tw/drivers_Linux.html (it would have been nice if they provided the install instructions in text rather than PDF)
Please open the document "EETI_eGTouch_Programming_Guide" under the Guide directory, and follow the Guidline to install driver.
download the appropriate versionunzip the fileread the programming manual And from that I'm distilling to the following: execute the setup.sh answer all of the questio…

Prometheus vs Bosun

In conclusion... while Bosun(B) is still not the ideal monitoring system neither is Prometheus(P).

TL;DR;

I am running Bosun in a Docker container hosted on CoreOS. Fleet service/unit files keep it running. However in once case I have experienced at least one severe crash as a result of a disk full condition. That it is implemented as part golang, java and python is an annoyance. The MIT license is about the only good thing.

I am trying to integrate Prometheus into my pipeline but losing steam fast. The Prometheus design seems to desire that you integrate your own cache inside your application and then allow the server to scrape the data, however, if the interval between scrapes is shorter than the longest transient session of your application then you need a gateway. A place to shuttle your data that will be a little more persistent.

(1) storing the data in my application might get me started more quickly
(2) getting the server to pull the data might be more secure
(3) using a push g…