Amalegeni-go

Use case : you have data lingering about in a postgres database, which you want to copy selectively to a mysql database.

For example, we have music album/tracks data in two tables in a postgres db, and want to select the tracks that contain the word 'streicher' in their title, and store this data in one table in a mysql db.

The major change to prior examples is that here we use the gq template instead of the se template. The gq template is typically used for writing go/sql programs for massaging or copying data, where you either need a bit more iterative logic than a sql script can offer or to copy data between different databases. Futher more, the generated code is self-contained, ie. it has everything to produce an executable, since the focus is on writing as little code as possible.

The code

This is all the code you need to write. Amalegeni-go will generate the rest for you. Then just compile and execute!

func Run() {{
    postgres:=0
    mysql:=1
    Insert( Select("%STREICHER%", postgres), mysql )
}} 
run 


type Track struct {
    AlbumTitle string
    Artist string 
    TrackTitle string
} 


func Select(titleLike string, d dbid) []Track
<< 
    select coalesce(a.title,'No title'),
           coalesce(t.artist, coalesce(a.artist, 'Unknown') ),
           coalesce(t.title,'No title')
    from   t_album a, t_track t 
    where  a.album_id=t.album_id
    and    t.title_uc like $1
>>


func Insert(tsl []Track, d dbid) string
<< 
    insert into t_selection ( album_title, artist, track_title)
    values (?,?,?) 
>>

The Select() function (line 16) returns a slice of Track objects, and a Track is a struct defined in line 9. The d argument is of type dbid, which is under the hood translated into the type int. This dbid parameter is not passed to the SQL statement, but to the GetDBH() function, to determine which database to connect to. The convention uses in the above example is that 0 stands for the postgres db, and 1 stands for the mysql db. Assigning these values to aptly named variables increases legibility.

The Insert() function (line 27): not much new here, but do note that this sql is targetted at MySQL since it uses the question mark as placeholders for the parameters (see line 30). The select sql, targetted at postgresql, uses the $1,$2,.. notation (eg. see line 23).

Note: the above program is called transfuse-gq.amg in the zip file (see 'Setup instructions' further down).

The scenic tour: some other examples

Here are some other programs included in the zip file (see 'Setup instructions' further down).

Count

Count the records in the source and target tables. Good for testing if your databases are correctly setup.

func Run() {{
    postgres:=0
    mysql:=1

    fmt.Printf("SRC: Albums    %d\n", CountAlbum(postgres) ) 
    fmt.Printf("SRC: Tracks    %d\n", CountTrack(postgres) ) 

    fmt.Printf("TRG: Selection %d\n", CountSelection(mysql) ) 
}} 
run 


func CountAlbum(d dbid) int
<< 
    select count(1)
    from   t_album 
>>


func CountTrack(d dbid) int
<< 
    select count(1)
    from   t_track 
>>


func CountSelection(d dbid) int
<< 
    select count(1)
    from   t_selection
>>

Show

func Run() {{
    mysql:=1
    
    for _,t := range( Select(mysql) ) {
        fmt.Printf("A:%-30s a:%-30s t:%s\n", 
                t.AlbumTitle, 
                t.Artist, 
                t.TrackTitle)
    }
}} 
run 


type Track struct {
    AlbumTitle string
    Artist string 
    TrackTitle string
} 


func Select(d dbid) []Track 
<<  
    select  left(album_title,30), 
            left(artist,30), 
            left(track_title,30)
    from t_selection
>>

Delete

func Run() {{
    mysql:=1
    DeleteTargetData(mysql) 
}}  
run     
                
func DeleteTargetData(d dbid) 
<<              
    delete from t_selection 
>>

Setup instructions

Prerequisites

Download the code and data

Database setup

See the db/setup_postgresql.txt and db/setup_mysql.txt file in the zipfile, on how to setup both databases. Data is included for copying into the source database.

Compile

Execute

The amalegeni-go template

The template used in above examples is the gq template, with a minor change to the GetDBH() function.

If you don't use the template of the above zip file, you can also reproduce it like this:

func GetDBH(d int) (dbh *sql.DB, err error) {
    if (d==0) {
        dbh, err = sql.Open("postgres", 
                "dbname=src_db user=src_usr password=src_pwd port=5432 host=localhost")
    } else if (d==1) {
        dbh, err = sql.Open("mysql", "trg_usr:trg_pwd@/trg_db")
    }
    return dbh, err
}

Example 4 : copy data from Postgres to Mysql