sqlstore

README

Postgres notes
==============


Permissions setup
-----------------

Postgresql can be picky on permissions and access.
You might have your own way of working with it, but if not,
here are my notes for setting up a development db:

I create a new postgresq user ("scrape"):

    $ sudo -u postgres createuser --no-superuser --no-createrole --no-createdb scrape
    $ sudo -u postgres psql -c "ALTER ROLE scrape WITH PASSWORD 'SooperSecretPassword'"

I then set up a pg_ident mapping to allow my usual unix account ("ben")
to connect as that pg user.
So, in /etc/postgresql/x.y/main/pg_ident.conf:

    # MAPNAME   SYSTEM-USERNAME    PG-USERNAME

    scrapedev   ben                     scrape

To create database ("nzarts":

    $ sudo -u postgres createdb -O scrape -E utf8 nzarts

And then set up my new database to use that pg_ident mapping.
In /etc/postgresql/x.y/main/pg_hba.conf:

    local   nzarts       scrape      peer map=scrapedev


Reload the postgresql config to make the changes take effect:
    $ sudo /etc/init.d/postgresql reload

...or systemctl or whatever:

    $ sudo systemctl reload postgresql

Then load the base schema:

    $ cat pg/schema.sql | psql -U scrape nzarts

Documentation

Index

• Constants
• type SQLArtIter
  • func (it *SQLArtIter) Article() *store.Article
  • func (it *SQLArtIter) Close() error
  • func (it *SQLArtIter) Err() error
  • func (it *SQLArtIter) Next() bool
• type SQLStore
  • func New(driver string, connStr string) (*SQLStore, error)
  • func NewFromDB(driver string, db *sql.DB) (*SQLStore, error)
  • func NewWithEnv(driver string, connStr string) (*SQLStore, error)
  • func (ss *SQLStore) Close()
  • func (ss *SQLStore) Fetch(filt *store.Filter) store.ArtIter
  • func (ss *SQLStore) FetchArt(artID int) (*store.Article, error)
  • func (ss *SQLStore) FetchCount(filt *store.Filter) (int, error)
  • func (ss *SQLStore) FetchPublications() ([]store.Publication, error)
  • func (ss *SQLStore) FetchSummary(filt *store.Filter, group string) ([]store.DatePubCount, error)
  • func (ss *SQLStore) FindURLs(urls []string) ([]int, error)
  • func (ss *SQLStore) Stash(arts ...*store.Article) ([]int, error)
  • func (ss *SQLStore) WhichAreNew(artURLs []string) ([]string, error)

Constants

const (
	UNKNOWN = iota
	QUESTION
	DOLLAR
	NAMED
	AT
)

Bindvar types supported by Rebind, BindMap and BindStruct.

const (
	DUNNO     = iota
	RESULT    // use Result.LastInsertID()
	RETURNING // use sql "RETURNING" clause
)

Which method to use to get last insert IDs

Types

type SQLArtIter

type SQLArtIter struct {
	// contains filtered or unexported fields
}

func (*SQLArtIter) Article

func (it *SQLArtIter) Article() *store.Article

func (*SQLArtIter) Close

func (it *SQLArtIter) Close() error

func (*SQLArtIter) Err

func (it *SQLArtIter) Err() error

func (*SQLArtIter) Next

func (it *SQLArtIter) Next() bool

if it returns true there will be an article.

type SQLStore

type SQLStore struct {
	ErrLog   store.Logger
	DebugLog store.Logger
	// contains filtered or unexported fields
}

SQLStore stashes articles in an SQL database

func New

func New(driver string, connStr string) (*SQLStore, error)

eg "postgres", "postgres://username@localhost/dbname" eg "sqlite3", "/tmp/foo.db"

func NewFromDB

func NewFromDB(driver string, db *sql.DB) (*SQLStore, error)

func NewWithEnv

func NewWithEnv(driver string, connStr string) (*SQLStore, error)

Same as New(), but if driver or connStr is missing, will try and read them from environment vars: SCRAPEOMAT_DRIVER & SCRAPEOMAT_DB. If both driver and SCRAPEOMAT_DRIVER are empty, default is "sqlite3".

func (*SQLStore) Close

func (ss *SQLStore) Close()

func (*SQLStore) Fetch

func (ss *SQLStore) Fetch(filt *store.Filter) store.ArtIter

func (*SQLStore) FetchArt

func (ss *SQLStore) FetchArt(artID int) (*store.Article, error)

Fetch a single article by ID

func (*SQLStore) FetchCount

func (ss *SQLStore) FetchCount(filt *store.Filter) (int, error)

func (*SQLStore) FetchPublications

func (ss *SQLStore) FetchPublications() ([]store.Publication, error)

func (*SQLStore) FetchSummary

func (ss *SQLStore) FetchSummary(filt *store.Filter, group string) ([]store.DatePubCount, error)

func (*SQLStore) FindURLs

func (ss *SQLStore) FindURLs(urls []string) ([]int, error)

FindURLs Looks up article urls, returning a list of matching article IDs. usually you'd use this on the URLs for a single article, expecting zero or one IDs back, but there's no reason you can't look up a whole bunch of articles at once, although you won't know which ones match which URLs. remember that there can be multiple URLs for a single article, AND also multiple articles can share the same URL (hopefully much much more rare).

func (*SQLStore) Stash

func (ss *SQLStore) Stash(arts ...*store.Article) ([]int, error)

Stash adds or updates articles in the database. If the article has an ID, it's assumed to be an update. If it doesn't, then it's an add.

Returns a list of article IDs, one per input article.

func (*SQLStore) WhichAreNew

func (ss *SQLStore) WhichAreNew(artURLs []string) ([]string, error)

NOTE: remember article urls don't _have_ to be unique. If you only pass canonical urls in here you should be ok :-)