Example 3 : map and injection

This page introduces the 'injection' keyword (think 'sql-injection'), and shows how to return and use a map of structs and a map of slices of structs.

Slice of strings

This one you have already seen from prior examples:

func SelectMasterIdList(artistLike string) []string
<<  
    select id 
    from   t_master
    where  artist like $1
>>  

Map of structs and injection

The struct is defined as in regular go.

type Master struct {
    Id string
    Artist string 
    Album string
} 

The function is declared to return a mapping from string to Master struct. Convention: the first field in your sql select statement will be taken as the key of the map.

func SelectMaster( masterIdInList injection ) map[string]Master
<<  
    select  id as key, 
            id, 
            artist, 
            album 
    from    t_master
    where   id in ( masterIdInList )
>>  

Also new here is that the parameter to be passed into the SelectMaster() function is of type 'injection', see in line 1: masterIdInList injection

In the generated code, this parameter will be of type 'string', and on composing the actual sql string that will be sent to the database, the injection parameters will be replaced by their value. Ie. the go code for creating the sql for the above function will look like this (see line 8 for the injected parameter):

var buf bytes.Buffer
buf.WriteString(" select  id as key,") 
buf.WriteString("         id,") 
buf.WriteString("         artist,") 
buf.WriteString("         album") 
buf.WriteString(" from    t_master") 
buf.WriteString(" where   id in ( ") 
buf.WriteString(masterIdInList) 
buf.WriteString(" )") 

Map of slices of structs

Instead of every map value only being a single struct, you can just as well create a mapping to slices of structs:

func SelectDetail( masterIdInList injection ) map[string] []Detail

Again: the first field in your select serves as the key of the map. The other fields are put in the struct.

Here's the code:

type Detail struct {
    TrackNo int
    Title string
}

func SelectDetail( masterIdInList injection ) map[string][]Detail
<<
    select  master_id as key, 
            trackno, 
            title
    from   t_detail
    where  master_id in ( masterIdInList )
    order by master_id, trackno
>>

Putting it all together

The Amgo code

File: plain/src/ex03/ex03.amg

As you notice: in the above code snippets the test definitions had been omitted: see the run{{ }} statements after every function below. These definitions are used to generate the test source code: src/ex03/ex03_generated_test.go.

func SelectMasterIdList(artistLike string) []string
<<  
    select id 
    from   t_master
    where  artist like $1
>>  
run 
{{
    artistLike:="%a%"
}}



type Master struct {
    Id string
    Artist string 
    Album string
} 

func SelectMaster( masterIdInList injection ) map[string]Master
<<  
    select  id as key, 
            id, 
            artist, 
            album 
    from    t_master
    where   id in ( masterIdInList )
>>  
run 
{{
    masterIdInList:="'2a087a04'"
}}



type Detail struct {
    TrackNo int
    Title string
}

func SelectDetail( masterIdInList injection ) map[string][]Detail
<<
    select  master_id as key, 
            trackno, 
            title
    from   t_detail
    where  master_id in ( masterIdInList )
    order by master_id, trackno
>>
run
{{
    masterIdInList:="'2a087a04'"
}}

The Go code

File plain/src/run/ex03/ex03_main.go

package main

import ( 
    "ex03"
    "fmt" 
    "strings"
) 

func main() { 
      
    // get the list of master ids 
    artistLike:="%a%"
    masterIdList, err:= ex03.SelectMasterIdList(artistLike)
    if err!=nil {
        fmt.Printf("Error: %v\n",err)
        return
    }

    // construct the 'where-in-list' string
    inlist:="'"+strings.Join( masterIdList,"','")+"'" 
    fmt.Printf("\nwhere-in-list = %+q\n\n",inlist)

    // get the master map: string -> Master 
    masterMap,err:= ex03.SelectMaster(inlist) 
    if err!=nil {
        fmt.Printf("Error: %v\n",err)
        return
    }

    // get the detail map: string -> Detail slice
    detailMap,err:= ex03.SelectDetail(inlist) 
    if err!=nil {
        fmt.Printf("Error: %v\n",err)
        return
    }


    // print-it
    for _,id:= range (masterIdList) {  
        m:=masterMap[id] 
        fmt.Printf("* %s / %s\n",m.Artist,m.Album)
        for _,d := range (detailMap[m.Id]) { 
            fmt.Printf("   - %d) %s\n",d.TrackNo,d.Title)
        }
    }
        
}  

Download, compile and run it

Download the zipfile ex03.zip containing all the code needed to run the above example.

As in prior examples, edit the setenv.sh script and execute it:

$ unzip ex03.zip
$ cd plain
$ vi setenv.sh
$ . ./setenv.sh

If need be: change the database connection parameters in the file janitor.go :

$  vi src/janitor/janitor.go

The tables in question can be created with this SQL-script: ddl.sql, and populated with this script: data.sql

All setup? Let's kick off amgo!

Run amgo on the ex03.amg file:

$ amgo ex03.amg
template/se.tpl * src/ex03/ex03.amg 
Generating: src/ex03/ex03_generated_type.go
Generating: src/ex03/ex03_generated_func.go
Generating: src/ex03/ex03_generated_test.go

Test the generated code (the tests were written to src/ex03/ex03_generated_test.go) :

$ go test ex03 -test.v
=== RUN TestSelectMasterIdList
[2a087a04 f40d3812]
--- PASS: TestSelectMasterIdList (0.01 seconds)
=== RUN TestSelectMaster
map[2a087a04:{2a087a04 Frank Zappa Waka Jawaka}]
--- PASS: TestSelectMaster (0.01 seconds)
=== RUN TestSelectDetail
map[2a087a04:[{1 Big Swifty} {2 Your Mouth} {3 It Just Might Be a One-Shot Deal} {4 Waka/Jawaka}]]
--- PASS: TestSelectDetail (0.01 seconds)
PASS
ok      ex03    0.040s

Compile/install the ex03 main go file:

go install run/ex03

Run it:

$ ex03 

where-in-list = "'2a087a04','f40d3812'"

* Frank Zappa / Waka Jawaka
   - 1) Big Swifty
   - 2) Your Mouth
   - 3) It Just Might Be a One-Shot Deal
   - 4) Waka/Jawaka
* Johann Sebastian Bach / Suites for Cello
   - 1) Suite No. 1 in G major, BWV 1007 - I. Prelude (moderato)
   - 2) Suite No. 1 in G major, BWV 1007 - II. Allemande (molto moderato)
   - 3) Suite No. 1 in G major, BWV 1007 - III. Courante (allegro non troppo)
   - 4) Suite No. 1 in G major, BWV 1007 - IV. Sarabande (lento)
   - 5) Suite No. 1 in G major, BWV 1007 - V. Menuetto I & II (allegro moderato)
   - 6) Suite No. 1 in G major, BWV 1007 - VI. Gigue (vivace)
   - 7) Suite No. 2 in D minor, BWV 1008 - I. Praeludium
   - 8) Suite No. 2 in D minor, BWV 1008 - II. Allemande
   - 9) Suite No. 2 in D minor, BWV 1008 - III. Courante
   - 10) Suite No. 2 in D minor, BWV 1008 - IV. Sarabande
   - 11) Suite No. 2 in D minor, BWV 1008 - V. Menuetto I & II
   - 12) Suite No. 2 in D minor, BWV 1008 - VI. Gigue
   - 13) Suite No. 3 in C major, BWV 1009 - I. Praeludium
   - 14) Suite No. 3 in C major, BWV 1009 - II. Allemande
   - 15) Suite No. 3 in C major, BWV 1009 - III. Courante
   - 16) Suite No. 3 in C major, BWV 1009 - IV. Sarabande
   - 17) Suite No. 3 in C major, BWV 1009 - V. Bourree I & II
   - 18) Suite No. 3 in C major, BWV 1009 - VI. Gigue