Day 12 - Gorp with Indexes is now ready for beta tests

Gorp with Indexes now passes the test cases for MySQL and PostgreSQL.

Get it from github: http://github.com/kimxilxyong/gorp

The latest (as of 2015.05.29) upstream changes have been merged, the code is now ready for testers.

If you want to use "Gorp with Indexes" just add index tags to your table struct
(idx_user in this example)  and add a import "github.com/kimxilxyong/gorp"

type Post struct {

    Id           uint64    `db:"notnull, PID, primarykey, autoincrement"`

    User         string    `db:"index:idx_user, size:64"`

    PostSub      string    `db:"index:idx_user, size:128"`

    UserIP       string    `db:"notnull, index:idx_user, size:16"`

    BodyType     string    `db:"notnull, size:64"`

    Body         string    `db:"name:PostBody, size:16384"`

    Err          error     `db:"-"` // ignore this field when storing with gorp

}

 Then you only need to call: CreateIndexes

       tablename := "posts_index_test"

    // SetKeys(true) means we have a auto increment primary key, which

    // will get automatically bound to your struct post-insert

    table := dbmap.AddTableWithName(post.Post{}, tablename)

    table.SetKeys(true, "PID")

    // create the table. in a production system you'd generally

    // use a migration tool, or create the tables via scripts

    if err = dbmap.CreateTablesIfNotExists(); err != nil {

        return errors.New("Create tables failed: " + err.Error())

    }

    // Force create all indexes for this database

    if err = dbmap.CreateIndexes(); err != nil {

        return errors.New("Create indexes failed: " + err.Error())

    }

Day 11 - Index creation and primary keys in gorp, gorp with indexes

As index creation is currently not available in the gorp master https://github.com/go-gorp/gorp I did as suggested by github, created a fork and added the index creation code myself. I think thats how opensource should work: Create a fork with experimental features and if they are stable and tested it will get merged back into the origin (eventually).

The test is to crawl content from Reddit and store it into a mysql database. The interresting part is in:

App Logic: Crawler
Gorp Struct: The Post struct which gets stored to mysql

If you dont already know how to use git or go get please check this out: http://githowto.com/

The key part is the Post struct, which defines how the Posts table in mysql will be created:

Note: the table creation will NOT work with the master gorp, you have to use: gorp with indexes


// holds a single post

type Post struct {

    Id           uint64    `db:"notnull, PID, primarykey, autoincrement"`

    SecondTestID int       `db:"notnull, name: SID"`

    Created      time.Time `db:"notnull, primarykey"`

    PostDate     time.Time `db:"notnull"`

    Site         string    `db:"name: PostSite, notnull, size:50"`

    PostId       string    `db:"notnull, size:32, unique"`

    Score        int       `db:"notnull"`

    Title        string    `db:"notnull"`

    Url          string    `db:"notnull"`

    User         string    `db:"index:idx_user, size:64"`

    PostSub      string    `db:"index:idx_user, size:128"`

    UserIP       string    `db:"notnull, size:16"`

    BodyType     string    `db:"notnull, size:64"`

    Body         string    `db:"name:PostBody, size:16384"`

    Err          error     `db:"-"` // ignore this field when storing with gorp

}

The part of the source which connects your go code to the database is redditFetchGorp:

    // connect to db using standard Go database/sql API
    //db, err := sql.Open("mysql", "user:password@/dbname")
    db, err := sql.Open("mysql", "golang:golang@/golang")
    if err != nil {
        return errors.New("sql.Open failed: " + err.Error())
    }

    // construct a gorp DbMap
    dbmap := &gorp.DbMap{Db: db, Dialect: gorp.MySQLDialect{"InnoDB", "UTF8"}}
    defer dbmap.Db.Close()

    // register the structs you wish to use with gorp
    // you can also use the shorter dbmap.AddTable() if you
    // don't want to override the table name
    _ = dbmap.AddTableWithName(post.Post{}, "posts")

    // create the table. in a production system you'd generally
    // use a migration tool, or create the tables via scripts
    err = dbmap.CreateTablesIfNotExists()
    if err != nil {
        return errors.New("Create table 'posts' failed: " + err.Error())
    } 

The resulting table should be:

CREATE TABLE `posts` (
  `PID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `SID` int(11) NOT NULL,
  `Created` datetime NOT NULL,
  `PostDate` datetime NOT NULL,
  `PostSite` varchar(50) NOT NULL,
  `PostId` varchar(32) NOT NULL,
  `Score` int(11) NOT NULL,
  `Title` varchar(255) NOT NULL,
  `Url` varchar(255) NOT NULL,
  `User` varchar(64) DEFAULT NULL,
  `PostSub` varchar(128) DEFAULT NULL,
  `UserIP` varchar(16) NOT NULL,
  `BodyType` varchar(64) NOT NULL,
  `PostBody` varchar(16384) DEFAULT NULL,
  PRIMARY KEY (`PID`,`Created`),
  UNIQUE KEY `PostId` (`PostId`),
  KEY `idx_user` (`User`,`PostSub`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8;