Example 5 : pump web data into your database

func D01() <<   drop table if exists t_bid >> run 
func D02() <<   drop table if exists t_ask >> run 
func D03() <<   drop table if exists t_order  >> run 
func D04() <<   drop sequence seq_order_id >> run 

import func ( "os" ) 

func CheckFirst() {{
    if (CountTable( " 't_order','t_bid','t_ask' " ) > 0 ) {
        fmt.Println("Cannot create, first drop existing tables!") 
        os.Exit(1)        
    }
}}
run


func CountTable( tableInList injection ) int <<
    select count(1) 
    from   pg_tables
    where  tablename in ( tableInList ) 
>>


func C01() << 
    create sequence seq_order_id start with 1000 increment by 1 cache 1 
>> run 


func C02() << 
    create table t_order ( 
          order_id integer primary key default nextval('seq_order_id') 
        , creation_time timestamp default current_timestamp
        , bitstamp_timestamp integer
        ) 
>> run


func C03() << 
    create table t_bid ( 
          order_id integer references t_order(order_id)
        , price numeric
        , amount numeric
        ) 
>> run


func C04() << 
    create table t_ask ( 
          order_id integer references t_order(order_id)
        , price numeric
        , amount numeric
        ) 
>> run


func C05() << 
    create index i_ask_d1 on t_ask (order_id) 
>> run


func C06() << 
    create index i_bid_d1 on t_bid(order_id) 
>>  run


func CheckAfter() {{
    if (CountTable( " 't_order','t_bid','t_ask' " ) == 3 ) {
        fmt.Println("All three tables succesfully created!") 
    } else {
        fmt.Println("Something went wrong, not all three tables exist! Not good...") 
    }
}}
run

import func ( 
   "encoding/json"
   "io/ioutil"
   "regexp"
   "net/http"
)

func Grab() 
{{
    // get the data via HTTP GET from bitstamp
    resp, err := http.Get("https://www.bitstamp.net/api/order_book/")
    if err != nil {
        panic(err)
    }
    defer resp.Body.Close()
    rawdata, err := ioutil.ReadAll(resp.Body)
    if err != nil {
        panic(err)
    }

    // cleanup the Json data: turn every double quoted number into an unquoted number 
    re := regexp.MustCompile( `"([0-9.][^"]*)"` )
    data:= re.ReplaceAll(rawdata, []byte("$1"))

    // turn the cleansed Json data into the OrderBook object
    var ob OrderBook
    err=json.Unmarshal( data , &ob)
    if (err!=nil) { 
        panic(err)
    }

    // start inserting the data into the db 
    id:=NextSeq() 
    fmt.Println("* Insert order") 
    InsertOrder(id, ob.Timestamp ) 

    // convert the bids into a PriceAmount slice for insertion
    bidSl:=make([]PriceAmount,0,0)
    for _,bid := range( ob.Bids ) { 
        bidSl=append(bidSl, PriceAmount{ Id: id, Price: bid[0], Amount:bid[1] } )
    }
    fmt.Println("* Insert bids") 
    InsertBid(bidSl)

    // convert the asks into a PriceAmount slice for insertion
    askSl:=make([]PriceAmount,0,0)
    for _,ask:= range( ob.Asks) { 
        askSl=append(askSl, PriceAmount{ Id: id, Price: ask[0], Amount:ask[1] } )
    }
    fmt.Println("* Insert asks") 
    InsertAsk(askSl)

    fmt.Printf("Inserted order id %d: %v.\n", id, Count(id)) 
}}



type OrderBook struct {
    Timestamp int64
    Bids [][]float32
    Asks [][]float32
}

type PriceAmount struct {
    Id int
    Price float32
    Amount float32
}


func NextSeq() int << 
    select nextval('seq_order_id') 
>> 

func InsertOrder( id int, tst int64 ) <<
    insert into t_order( order_id, bitstamp_timestamp ) values( $1, $2 ) 
>>

func InsertAsk( sl []PriceAmount) <<
    insert into t_ask( order_id, price, amount ) values( $1, $2, $3 ) 
>>

func InsertBid( sl []PriceAmount) <<
    insert into t_bid( order_id, price, amount ) values( $1, $2, $3 ) 
>>

func Count( id int ) []string << 
    select to_char(count(1),'999') || ' order ' from t_order where order_id=$1 
        union all
    select to_char(count(1),'999,999') || ' asks' from t_ask where order_id=$1   
        union all
    select to_char(count(1),'999,999') || ' bids' from t_bid where order_id=$1   
>>

func SimpleStats() {{
    for _,id := range(OrderList()) {
        bidvol:=BidVolume(id)    
        askvol:=AskVolume(id) 

        fmt.Printf("\n%s\torder:%d\n",OrderTst(id),id)
        fmt.Printf("\tBid:\tvol:%-9.0f\tnum:%d\tmax:%-9.2f\n",
            bidvol, 
            BidCount(id), 
            BidMaxPrice(id) )
        fmt.Printf("\tAsk:\tvol:%-9.0f\tnum:%d\tmin:%-9.2f\n",
            askvol,
            AskCount(id), 
            AskMinPrice(id) )

        if (bidvol!=0.0) {
            ratio:=askvol/bidvol
            fmt.Printf("\tVol-Ratio:\t%-9.2f\n",ratio)
        }
    }

}} run


func OrderList() []int << 
    select order_id 
    from t_order 
    order by 1
>>

func OrderTst(id int) string <<
    select to_char(creation_time,'YYYYMMDD HH24:MI') ct 
    from t_order 
    where order_id=$1
>>


func AskCount( id int ) int << 
    select count(1) from t_ask where order_id=$1   
>>

func AskVolume(id int) float64 << 
    select sum(price*amount) 
    from   t_ask
    where  order_id=$1
>>

func AskMinPrice(id int) float64 <<
    select min(price) 
    from   t_ask
    where  order_id=$1
>>


func BidCount( id int ) int << 
    select count(1) from t_bid where order_id=$1   
>>

func BidVolume(id int) float64 <<
    select sum(price*amount) 
    from   t_bid
    where  order_id=$1
>>

func BidMaxPrice(id int) float64 <<
    select max(price) 
    from   t_bid
    where  order_id=$1
>>