<commit_before>/* This Source Code Form is subject to the terms of the Mozilla Public
 * License, v. 2.0. If a copy of the MPL was not distributed with this
 * file, You can obtain one at http://mozilla.org/MPL/2.0/. */

package storage

import (
	"mozilla.org/util"

	"crypto/md5"
	"database/sql"
	"encoding/hex"
	"errors"
	"fmt"
	_ "github.com/lib/pq"
	"io"
	"strconv"
	"strings"
	"time"
)

var ErrDatabase = errors.New("Database Error")
var ErrUnknownDevice = errors.New("Unknown device")

// Storage abstration
type Storage struct {
	config   *util.MzConfig
	logger   *util.HekaLogger
	metrics  *util.Metrics
	dsn      string
	logCat   string
	defExpry int64
	db       *sql.DB
}

// Device position
type Position struct {
	Latitude  float64
	Longitude float64
	Altitude  float64
	Time      int64
	Cmd       map[string]interface{}
}

// Device information
type Device struct {
	ID                string // device Id
	User              string // userID
	Name              string
	PreviousPositions []Position
	HasPasscode       bool   // is device lockable
	LoggedIn          bool   // is the device logged in
	Secret            string // HAWK secret
	PushUrl           string // SimplePush URL
	Pending           string // pending command
	LastExchange      int32  // last time we did anything
	Accepts           string // commands the device accepts
	AccessToken       string // OAuth Access token
}

type DeviceList struct {
	ID   string
	Name string
}

// Generic structure useful for JSON
type Unstructured map[string]interface{}

/* Relative:

   table userToDeviceMap:
       userId   UUID index
       deviceId UUID

   table pendingCommands:
       deviceId UUID index
       time     timeStamp
       cmd      string

   table deviceInfo:
       deviceId       UUID index
       name           string
       lockable       boolean
       lastExchange   time
       hawkSecret     string
       pushUrl        string
       accepts        string
       accesstoken    string

   table position:
       positionId UUID index
       deviceId   UUID index
       time       timeStamp
       latitude   float
       longitude  float
       altitude   float

   // misc administrivia table.
   table meta:
       key        string
       value      string
*/
/* key:
deviceId {positions:[{lat:float, lon: float, alt: float, time:int},...],
		 lockable: bool
		 lastExchange: int
		 secret: string
		 pending: string
		}

user [deviceId:name,...]
*/

// Get a time string that makes psql happy.
func dbNow() (ret string) {
	r, _ := time.Now().UTC().MarshalText()
	return string(r)
}

// Open the database.
func Open(config *util.MzConfig, logger *util.HekaLogger, metrics *util.Metrics) (store *Storage, err error) {
	dsn := fmt.Sprintf("user=%s password=%s host=%s dbname=%s sslmode=%s",
		config.Get("db.user", "user"),
		config.Get("db.password", "password"),
		config.Get("db.host", "localhost"),
		config.Get("db.db", "wmf"),
		config.Get("db.sslmode", "disable"))
	logCat := "storage"
	defExpry, err := strconv.ParseInt(config.Get("db.default_expry", "1500"), 0, 64)
	if err != nil {
		defExpry = 1500
	}
	db, err := sql.Open("postgres", dsn)
	if err != nil {
        panic("Storage is unavailable: " + err.Error() + "\n")
		return nil, err
	}
	db.SetMaxIdleConns(100)
	if err = db.Ping(); err != nil {
		return nil, err
	}
	store = &Storage{
		config:   config,
		logger:   logger,
		logCat:   logCat,
		defExpry: defExpry,
		metrics:  metrics,
		dsn:      dsn,
		db:       db}
	//	if err = store.Init(); err != nil {
	//		return nil, err
	//	}
	return store, nil
}

// Create the tables, indexes and other needed items.
func (self *Storage) Init() (err error) {
	// TODO: create a versioned db update system that contains commands
	// to execute.
	cmds := []string{
		"create table if not exists userToDeviceMap (userId varchar, deviceId varchar, name varchar, date date);",
		"create index on userToDeviceMap (userId);",
		"create index on userToDeviceMap (deviceId);",
		"create unique index on userToDeviceMap (userId, deviceId);",

		"create table if not exists deviceInfo (deviceId varchar unique, lockable boolean, loggedin boolean, lastExchange timestamp, hawkSecret varchar, pushurl varchar, accepts varchar, accesstoken varchar);",
		"create index on deviceInfo (deviceId);",

		"create table if not exists pendingCommands (id bigserial, deviceId varchar, time timestamp, cmd varchar);",
		"create index on pendingCommands (deviceId);",

		"create table if not exists position (id bigserial, deviceId varchar, time  timestamp, latitude real, longitude real, altitude real);",
		"create index on position (deviceId);",
		"create or replace function update_time() returns trigger as $$ begin new.lastexchange = now(); return new; end; $$ language 'plpgsql';",
		"drop trigger if exists update_le on deviceinfo;",
		"create trigger update_le before update on deviceinfo for each row execute procedure update_time();",
		"create table if not exists meta (key varchar, value varchar);",
		"create index on meta (key);",
		"create table if not exists nonce (key varchar, value varchar, time timestamp);",
		"create index on nonce (key);",
		"create index on nonce (time);",
		"set time zone utc;",
	}

	dbh := self.db
	for _, s := range cmds {
		res, err := dbh.Exec(s)
		self.logger.Debug(self.logCat, "db init",<commit_msg>Fix typo in table create;<commit_after>/* This Source Code Form is subject to the terms of the Mozilla Public
 * License, v. 2.0. If a copy of the MPL was not distributed with this
 * file, You can obtain one at http://mozilla.org/MPL/2.0/. */

package storage

import (
	"mozilla.org/util"

	"crypto/md5"
	"database/sql"
	"encoding/hex"
	"errors"
	"fmt"
	_ "github.com/lib/pq"
	"io"
	"strconv"
	"strings"
	"time"
)

var ErrDatabase = errors.New("Database Error")
var ErrUnknownDevice = errors.New("Unknown device")

// Storage abstration
type Storage struct {
	config   *util.MzConfig
	logger   *util.HekaLogger
	metrics  *util.Metrics
	dsn      string
	logCat   string
	defExpry int64
	db       *sql.DB
}

// Device position
type Position struct {
	Latitude  float64
	Longitude float64
	Altitude  float64
	Time      int64
	Cmd       map[string]interface{}
}

// Device information
type Device struct {
	ID                string // device Id
	User              string // userID
	Name              string
	PreviousPositions []Position
	HasPasscode       bool   // is device lockable
	LoggedIn          bool   // is the device logged in
	Secret            string // HAWK secret
	PushUrl           string // SimplePush URL
	Pending           string // pending command
	LastExchange      int32  // last time we did anything
	Accepts           string // commands the device accepts
	AccessToken       string // OAuth Access token
}

type DeviceList struct {
	ID   string
	Name string
}

// Generic structure useful for JSON
type Unstructured map[string]interface{}

/* Relative:

   table userToDeviceMap:
       userId   UUID index
       deviceId UUID

   table pendingCommands:
       deviceId UUID index
       time     timeStamp
       cmd      string

   table deviceInfo:
       deviceId       UUID index
       name           string
       lockable       boolean
       lastExchange   time
       hawkSecret     string
       pushUrl        string
       accepts        string
       accesstoken    string

   table position:
       positionId UUID index
       deviceId   UUID index
       time       timeStamp
       latitude   float
       longitude  float
       altitude   float

   // misc administrivia table.
   table meta:
       key        string
       value      string
*/
/* key:
deviceId {positions:[{lat:float, lon: float, alt: float, time:int},...],
		 lockable: bool
		 lastExchange: int
		 secret: string
		 pending: string
		}

user [deviceId:name,...]
*/

// Get a time string that makes psql happy.
func dbNow() (ret string) {
	r, _ := time.Now().UTC().MarshalText()
	return string(r)
}

// Open the database.
func Open(config *util.MzConfig, logger *util.HekaLogger, metrics *util.Metrics) (store *Storage, err error) {
	dsn := fmt.Sprintf("user=%s password=%s host=%s dbname=%s sslmode=%s",
		config.Get("db.user", "user"),
		config.Get("db.password", "password"),
		config.Get("db.host", "localhost"),
		config.Get("db.db", "wmf"),
		config.Get("db.sslmode", "disable"))
	logCat := "storage"
	defExpry, err := strconv.ParseInt(config.Get("db.default_expry", "1500"), 0, 64)
	if err != nil {
		defExpry = 1500
	}
	db, err := sql.Open("postgres", dsn)
	if err != nil {
        panic("Storage is unavailable: " + err.Error() + "\n")
		return nil, err
	}
	db.SetMaxIdleConns(100)
	if err = db.Ping(); err != nil {
		return nil, err
	}
	store = &Storage{
		config:   config,
		logger:   logger,
		logCat:   logCat,
		defExpry: defExpry,
		metrics:  metrics,
		dsn:      dsn,
		db:       db}
	//	if err = store.Init(); err != nil {
	//		return nil, err
	//	}
	return store, nil
}

// Create the tables, indexes and other needed items.
func (self *Storage) Init() (err error) {
	// TODO: create a versioned db update system that contains commands
	// to execute.
	cmds := []string{
		"create table if not exists userToDeviceMap (userId varchar, deviceId varchar, name varchar, date date);",
		"create index on userToDeviceMap (userId);",
		"create index on userToDeviceMap (deviceId);",
		"create unique index on userToDeviceMap (userId, deviceId);",

		"create table if not exists deviceInfo (deviceId varchar unique, lockable boolean, loggedin boolean, lastExchange timestamp, hawkSecret varchar, pushurl varchar, accepts varchar, accesstoken varchar);",
		"create index on deviceInfo (deviceId);",

		"create table if not exists pendingCommands (id bigserial, deviceId varchar, time timestamp, cmd varchar);",
		"create index on pendingCommands (deviceId);",

		"create table if not exists position (id bigserial, deviceId varchar, time  timestamp, latitude real, longitude real, altitude real);",
		"create index on position (deviceId);",
		"create or replace function update_time() returns trigger as $$ begin new.lastexchange = now(); return new; end; $$ language 'plpgsql';",
		"drop trigger if exists update_le on deviceinfo;",
		"create trigger update_le before update on deviceinfo for each row execute procedure update_time();",
		"create table if not exists meta (key varchar, value varchar);",
		"create index on meta (key);",
		"create table if not exists nonce (key varchar, val varchar, time timestamp);",
		"create index on nonce (key);",
		"create index on nonce (time);",
		"set time zone utc;",
	}

	dbh := self.db
	for _, s := range cmds {
		res, err := dbh.Exec(s)
		self.logger.Debug(self.logCat, "db init",
{'fraction_non_alphanumeric': 0.10634118967452301, 'fraction_numerical': 0.005798728020950243, 'mean_word_length': 2.906832298136646, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3297700', 'n_tokens_mistral': 3457, 'n_tokens_neox': 3245, 'n_words': 1145}