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;