github.com/olivere/camlistore@v0.0.0-20140121221811-1b7ac2da0199/pkg/sorted/postgres/postgreskv.go (about)

     1  /*
     2  Copyright 2012 The Camlistore Authors.
     3  
     4  Licensed under the Apache License, Version 2.0 (the "License");
     5  you may not use this file except in compliance with the License.
     6  You may obtain a copy of the License at
     7  
     8       http://www.apache.org/licenses/LICENSE-2.0
     9  
    10  Unless required by applicable law or agreed to in writing, software
    11  distributed under the License is distributed on an "AS IS" BASIS,
    12  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
    13  See the License for the specific language governing permissions and
    14  limitations under the License.
    15  */
    16  
    17  // Package postgres provides an implementation of sorted.KeyValue
    18  // on top of PostgreSQL.
    19  package postgres
    20  
    21  import (
    22  	"database/sql"
    23  	"fmt"
    24  	"os"
    25  	"regexp"
    26  
    27  	"camlistore.org/pkg/jsonconfig"
    28  	"camlistore.org/pkg/sorted"
    29  	"camlistore.org/pkg/sorted/sqlkv"
    30  
    31  	_ "camlistore.org/third_party/github.com/lib/pq"
    32  )
    33  
    34  func init() {
    35  	sorted.RegisterKeyValue("postgresql", newKeyValueFromJSONConfig)
    36  }
    37  
    38  // Config holds the parameters used to connect to the PostgreSQL db.
    39  type Config struct {
    40  	Host     string // Optional. Defaults to "localhost" in ConfigFromJSON.
    41  	Database string // Required.
    42  	User     string // Required.
    43  	Password string // Optional.
    44  	SSLMode  string // Optional. Defaults to "require" in ConfigFromJSON.
    45  }
    46  
    47  // ConfigFromJSON populates Config from config, and validates
    48  // config. It returns an error if config fails to validate.
    49  func ConfigFromJSON(config jsonconfig.Obj) (Config, error) {
    50  	conf := Config{
    51  		Host:     config.OptionalString("host", "localhost"),
    52  		User:     config.RequiredString("user"),
    53  		Password: config.OptionalString("password", ""),
    54  		Database: config.RequiredString("database"),
    55  		SSLMode:  config.OptionalString("sslmode", "require"),
    56  	}
    57  	if err := config.Validate(); err != nil {
    58  		return Config{}, err
    59  	}
    60  	return conf, nil
    61  }
    62  
    63  func newKeyValueFromJSONConfig(cfg jsonconfig.Obj) (sorted.KeyValue, error) {
    64  	conf, err := ConfigFromJSON(cfg)
    65  	if err != nil {
    66  		return nil, err
    67  	}
    68  	return NewKeyValue(conf)
    69  }
    70  
    71  // NewKeyValue returns a sorted.KeyValue implementation of the described PostgreSQL database.
    72  func NewKeyValue(cfg Config) (sorted.KeyValue, error) {
    73  	conninfo := fmt.Sprintf("user=%s dbname=%s host=%s password=%s sslmode=%s",
    74  		cfg.User, cfg.Database, cfg.Host, cfg.Password, cfg.SSLMode)
    75  	db, err := sql.Open("postgres", conninfo)
    76  	if err != nil {
    77  		return nil, err
    78  	}
    79  
    80  	kv := &keyValue{
    81  		db: db,
    82  		KeyValue: &sqlkv.KeyValue{
    83  			DB:              db,
    84  			SetFunc:         altSet,
    85  			BatchSetFunc:    altBatchSet,
    86  			PlaceHolderFunc: replacePlaceHolders,
    87  		},
    88  		conf: cfg,
    89  	}
    90  	if err := kv.ping(); err != nil {
    91  		return nil, fmt.Errorf("PostgreSQL db unreachable: %v", err)
    92  	}
    93  	version, err := kv.SchemaVersion()
    94  	if err != nil {
    95  		return nil, fmt.Errorf("error getting schema version (need to init database?): %v", err)
    96  	}
    97  	if version != requiredSchemaVersion {
    98  		if os.Getenv("CAMLI_DEV_CAMLI_ROOT") != "" {
    99  			// Good signal that we're using the devcam server, so help out
   100  			// the user with a more useful tip:
   101  			return nil, fmt.Errorf("database schema version is %d; expect %d (run \"devcam server --wipe\" to wipe both your blobs and re-populate the database schema)", version, requiredSchemaVersion)
   102  		}
   103  		return nil, fmt.Errorf("database schema version is %d; expect %d (need to re-init/upgrade database?)",
   104  			version, requiredSchemaVersion)
   105  	}
   106  
   107  	return kv, nil
   108  }
   109  
   110  type keyValue struct {
   111  	*sqlkv.KeyValue
   112  	conf Config
   113  	db   *sql.DB
   114  }
   115  
   116  // postgres does not have REPLACE INTO (upsert), so we use that custom
   117  // one for Set operations instead
   118  func altSet(db *sql.DB, key, value string) error {
   119  	r, err := db.Query("SELECT replaceinto($1, $2)", key, value)
   120  	if err != nil {
   121  		return err
   122  	}
   123  	return r.Close()
   124  }
   125  
   126  // postgres does not have REPLACE INTO (upsert), so we use that custom
   127  // one for Set operations in batch instead
   128  func altBatchSet(tx *sql.Tx, key, value string) error {
   129  	r, err := tx.Query("SELECT replaceinto($1, $2)", key, value)
   130  	if err != nil {
   131  		return err
   132  	}
   133  	return r.Close()
   134  }
   135  
   136  var qmark = regexp.MustCompile(`\?`)
   137  
   138  // replace all ? placeholders into the corresponding $n in queries
   139  var replacePlaceHolders = func(query string) string {
   140  	i := 0
   141  	dollarInc := func(b []byte) []byte {
   142  		i++
   143  		return []byte(fmt.Sprintf("$%d", i))
   144  	}
   145  	return string(qmark.ReplaceAllFunc([]byte(query), dollarInc))
   146  }
   147  
   148  func (kv *keyValue) ping() error {
   149  	_, err := kv.SchemaVersion()
   150  	return err
   151  }
   152  
   153  func (kv *keyValue) SchemaVersion() (version int, err error) {
   154  	err = kv.db.QueryRow("SELECT value FROM meta WHERE metakey='version'").Scan(&version)
   155  	return
   156  }