database

package
v0.0.0-...-acaa9d6 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Jan 16, 2017 License: MIT Imports: 9 Imported by: 4

README

About

The server is powered by a PostgreSQL database.

These installation and setup instructions are specially for Debian/Ubuntu systems, but can be adapted to other flavors of Linux easily.

Installation

Install PostgreSQL using the default package as root/sudo, along with postgresql-contrib, since the data model uses universally unique identifiers (UUIDs) for its table primary keys:

apt-get install -y postgresql postgresql-contrib

Configuration

Once the packages have been installed, create the database user, database itself, and set the access level.

  1. Become or login as the postgres user:
su - postgres
  1. Create the database user (have a password ready):
createuser -P teamworkio
  1. Next, the database itself:
createdb teamworkdb
  1. Then apply the UUID extension:
psql -d teamworkdb
teamworkdb=# create extension "uuid-ossp";
CREATE EXTENSION
teamworkdb=# \q
exit
  1. Configure the client authentication (optional)

Stop the database server:

/etc/init.d/postgresql stop

Then edit the pg_hba.conf file with your desired settings.

Finally, restart the database server:

/etc/init.d/postgresql restart

Installing the data model

With the database created and the postgres server running, install the tables.sql file from this folder, as any system user:

psql -d teamworkdb -U teamworkio < tables.sql

The psql tool will prompt for the password used in step 2, above:

Password for user teamworkio:

The database will respond with:

CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE

Documentation

Index

Constants

View Source 
const (
	// add + delete
	MESSAGE_INSERT  = "insert into message (person_id, message, date_expires) values ($1, $2, $3 at time zone 'UTC') returning id"
	MESSAGE_DELETE  = "delete from message where id = $1"
	MESSAGE_CLEANUP = "select id from message where date_expires <= (now() at time zone 'UTC')"

	RECIPIENT_INSERT  = "insert into message_recipient (message_id, person_id) values ($1, $2)"
	RECIPIENT_DELETE  = "delete from message_recipient where message_id = $1 and person_id = $2"
	RECIPIENT_CLEANUP = "delete from message_recipient where message_id = $1"

	// lookups
	MESSAGES_BY_AUTHOR               = "select id, person_id, message, date_posted, date_expires from message where person_id = $1"
	MESSAGES_BY_RECIPIENT            = "" /* 152-byte string literal not displayed */
	RECIPIENTS_BY_MESSAGE            = "select person_id from message_recipient where message_id = $1"
	LATEST_MESSAGES                  = "select id, person_id, message, date_posted, date_expires from message order by date_posted desc limit $1 offset $2"
	LATEST_MESSAGES_INVOLVING_PERSON = `` /* 224-byte string literal not displayed */

	MESSAGE_BY_ID = "select id, person_id, message, date_posted, date_expires from message where id = $1 limit $2 offset $3"
)
View Source 
const (
	// person a/u/d
	PERSON_INSERT = "insert into person (email) values ($1) returning id"
	PERSON_UPDATE = "update person set email = $1, verified = $2, date_verified = (now() at time zone 'UTC'), enabled = $3 where id = $4"
	PERSON_DELETE = "delete from person where id = $1"

	// person lookup
	PERSON_LOOKUP_BY_ID    = "select id, email, date_added, verified, date_verified, enabled from person where id = $1"
	PERSON_LOOKUP_BY_EMAIL = "select id, email, date_added, verified, date_verified, enabled from person where email = $1"
)
View Source 
const (
	// public key a/u/d
	PK_INSERT = "insert into public_key (person_id, key, nickname, source) values ($1, $2, $3, $4) returning id"
	PK_UPDATE = "update public_key set key = $1, nickname = $2, source = $3 where id = $4"
	PK_DELETE = "delete from public_key where id = $1"

	// public key lookup
	PK_LOOKUP = "select id, key, date_added, nickname, source from public_key where person_id = $1"
)
View Source 
const (
	// session a/u/d
	SESSION_INSERT  = "insert into session (session_code, person_id, date_expires) values ($1, $2, $3 at time zone 'UTC') returning id"
	SESSION_UPDATE  = "update session set verified = $1, date_verified = (now() at time zone 'UTC') where id = $2"
	SESSION_CLEANUP = "delete from session where date_expires <= (now() at time zone 'UTC')"

	// session lookup
	SESSION_LOOKUP_BY_CODE   = "select id, person_id, session_code, date_created, verified, date_verified, date_expires from session where session_code = $1"
	SESSION_LOOKUP_BY_ID     = "select id, person_id, session_code, date_created, verified, date_verified, date_expires from session where id = $1"
	SESSION_LOOKUP_BY_PERSON = "select id, person_id, session_code, date_created, verified, date_verified, date_expires from session where person_id = $1"
)

Variables

View Source 
var WordTokens []string

Functions

func AddPersonWithKeys 

func AddPersonWithKeys(pStmt, pkStmt *sql.Stmt, email string, pkList []*PUBLIC_KEY) error

create a new Person in the db, and associate these public keys

func CleanupMessages 

func CleanupMessages(idStmt, msgStmt, recipientStmt *sql.Stmt) []error

Identify all expired messages, and remove them and their recipient list

func CleanupSessions 

func CleanupSessions(stmt *sql.Stmt) error

func ExpiredMessages 

func ExpiredMessages(stmt *sql.Stmt) ([]string, error)

Return a list of message ids that are now expired

func InitializeWords 

func InitializeWords(wordsFile string) error

func WithDatabase 

func WithDatabase(dbCoords DBConnection, fn func(map[string]*sql.Stmt))

Connect to the database with the given coordinates, and invoke the function, which gets passed a map of all the prepared statements

Types

type DBConnection 

type DBConnection struct {
	DBName  string
	User    string
	Pass    string
	SSLMode bool
}

type MESSAGE 

type MESSAGE struct {
	Id          string    `json:"id"`
	PersonId    string    `json:"person_id"`
	Message     string    `json:"message"`
	DatePosted  time.Time `json:"date_posted"`
	DateExpires time.Time `json:"date_expires"`
}

func RetrieveMessages 

func RetrieveMessages(stmt *sql.Stmt, uniqueId string, limit, offset int64) ([]*MESSAGE, error)

Return a list of messages for the given query limit/offset criteria

func (*MESSAGE) Add 

func (m *MESSAGE) Add(stmt *sql.Stmt, duration time.Duration) (string, error)

func (*MESSAGE) AddRecipients 

func (m *MESSAGE) AddRecipients(stmt *sql.Stmt, recipients []*PERSON) []error

func (*MESSAGE) Delete 

func (m *MESSAGE) Delete(stmt *sql.Stmt) error

func (*MESSAGE) DeleteRecipients 

func (m *MESSAGE) DeleteRecipients(stmt *sql.Stmt, recipients []*PERSON) []error

func (*MESSAGE) GetDigest 

func (m *MESSAGE) GetDigest(personStmt, recipientStmt *sql.Stmt, personId string) (*MESSAGE_DIGEST, error)

Retrieve the corresponding digest (which includes all the involved Person objects) for this Message

func (*MESSAGE) GetPreview 

func (m *MESSAGE) GetPreview() string

Fetch the first unique line of the armored message as a preview

func (*MESSAGE) ProcessRecipients 

func (m *MESSAGE) ProcessRecipients(stmt *sql.Stmt, recipients []*PERSON) []error

type MESSAGE_DIGEST 

type MESSAGE_DIGEST struct {
	Message           *MESSAGE
	Preview           string
	Sender            *PERSON
	Recipients        []*PERSON
	InvolvesRequestor bool
}

func GetMessageDigests 

func GetMessageDigests(personStmt, recipientStmt *sql.Stmt, messages []*MESSAGE, personId string) ([]*MESSAGE_DIGEST, []error)

Return the corresponding digests for this list of messages

type PERSON 

type PERSON struct {
	Id           string    `json:"id"`
	Email        string    `json:"email"`
	DateAdded    time.Time `json:"date_joined"`
	Verified     bool      `json:"verified"`
	DateVerified time.Time `json:"date_verified,omitempty"`
	Enabled      bool      `json:"enabled"`
}

func LookupPerson 

func LookupPerson(stmt *sql.Stmt, param string) (*PERSON, error)

func (*PERSON) Add 

func (p *PERSON) Add(stmt *sql.Stmt) (string, error)

func (*PERSON) Delete 

func (p *PERSON) Delete(stmt *sql.Stmt) error

func (*PERSON) LookupAuthoredMessages 

func (p *PERSON) LookupAuthoredMessages(stmt *sql.Stmt, limit, offset int64) ([]*MESSAGE, error)

Return a list of messages originated by this person

func (*PERSON) LookupInvolvedMessages 

func (p *PERSON) LookupInvolvedMessages(stmt *sql.Stmt, limit, offset int64) ([]*MESSAGE, error)

Return a list of messages in which this person was involved, either as an origniator or a recipient

func (*PERSON) LookupLatestMessages 

func (p *PERSON) LookupLatestMessages(stmt *sql.Stmt, limit, offset int64) ([]*MESSAGE, error)

Return a list of all messages, regardless of involvement by this person

func (*PERSON) LookupMessages 

func (p *PERSON) LookupMessages(stmt *sql.Stmt, usePersonId bool, limit, offset int64) ([]*MESSAGE, error)

func (*PERSON) LookupPublicKeys 

func (p *PERSON) LookupPublicKeys(stmt *sql.Stmt) ([]*PUBLIC_KEY, error)

func (*PERSON) LookupRecipientMessages 

func (p *PERSON) LookupRecipientMessages(stmt *sql.Stmt, limit, offset int64) ([]*MESSAGE, error)

Return a list of messages in which this person was a recipient

func (*PERSON) LookupSessions 

func (p *PERSON) LookupSessions(stmt *sql.Stmt) ([]*SESSION, error)

func (*PERSON) Update 

func (p *PERSON) Update(stmt *sql.Stmt) error

type PUBLIC_KEY 

type PUBLIC_KEY struct {
	Id       string    `json:"id,omitempty"`
	Key      string    `json:"key"`
	Added    time.Time `json:"date_added,omitempty"`
	Nickname string    `json:"name,omitempty"`
	Source   string    `json:"source,omitempty"`
}

func (*PUBLIC_KEY) Add 

func (pk *PUBLIC_KEY) Add(stmt *sql.Stmt, personId string) (string, error)

func (*PUBLIC_KEY) Delete 

func (pk *PUBLIC_KEY) Delete(stmt *sql.Stmt) error

func (*PUBLIC_KEY) Update 

func (pk *PUBLIC_KEY) Update(stmt *sql.Stmt) error

type SESSION 

type SESSION struct {
	Id           string    `json:"id"`
	PersonId     string    `json:"person_id"`
	Code         string    `json:"session_code"`
	DateCreated  time.Time `json:"date_created"`
	Verified     bool      `json:"verified"`
	DateVerified time.Time `json:"date_verified"`
	DateExpires  time.Time `json:"date_expires"`
}

func LookupSession 

func LookupSession(stmt *sql.Stmt, code string) (*SESSION, error)

func (*SESSION) Add 

func (s *SESSION) Add(stmt *sql.Stmt, PersonId string, codeSize int, duration time.Duration) (string, error)

func (*SESSION) Delete 

func (s *SESSION) Delete(stmt *sql.Stmt) error

func (*SESSION) Update 

func (s *SESSION) Update(stmt *sql.Stmt) error

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.