kosyncsrv.git

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139

package main

import (
	"database/sql"
	"log"
	"time"

	"github.com/jmoiron/sqlx"
	_ "github.com/mattn/go-sqlite3"
)

var schemaUser string = `
CREATE TABLE IF NOT EXISTS "user" (
	"username"  TEXT(255),
	"password"  TEXT(255)
);
`
var schemaDocument string = `
CREATE TABLE IF NOT EXISTS "document" (
	"username"  TEXT(255),
	"documentid"  TEXT(255),
	"percentage"  REAL(64,4),
	"progress"  TEXT(255),
	"device"  TEXT(255),
	"device_id"  TEXT(255),
	"timestamp"  INTEGER
);`

var (
	db     *sqlx.DB
	dbname string
)

type dbUser struct {
	Username string `db:"username"`
	Password string `db:"password"`
}

type dbDocument struct {
	Username   string  `db:"username"`
	DocumentID string  `db:"documentid"`
	Percentage float64 `db:"percentage"`
	Progress   string  `db:"progress"`
	Device     string  `db:"device"`
	DeviceID   string  `db:"device_id"`
	Timestamp  int64   `db:"timestamp"`
}

func initDB() {
	var err error
	db, err = sqlx.Connect("sqlite3", dbname)
	if err != nil {
		log.Fatalln(err)
	}
	db.MustExec(schemaUser)
	db.MustExec(schemaDocument)
}

func getDBUser(username string) (dbUser, bool) {
	var result dbUser
	var norows bool = false
	err := db.Get(&result, "SELECT * FROM user WHERE username=$1", username)
	if err != nil {
		log.Println(err)
		if err == sql.ErrNoRows {
			norows = true
		}
	}
	return result, norows
}

func addDBUser(username string, password string) bool {
	_, norows := getDBUser(username)
	if norows {
		tx := db.MustBegin()
		tx.MustExec("INSERT INTO user (username, password) VALUES ($1, $2)", username, password)
		tx.Commit()
		return true
	}
	return false
}

func getDBPosition(username string, documentid string) requestPosition {
	var rPos requestPosition
	var resultDBdoc dbDocument
	err := db.Get(&resultDBdoc, "SELECT * FROM document WHERE document.username=$1 AND document.documentid=$2 ORDER BY document.timestamp DESC", username, documentid)
	if err != nil {
		log.Println(err)
	}
	rPos.DocumentID = documentid
	rPos.Percentage = resultDBdoc.Percentage
	rPos.Progress = resultDBdoc.Progress
	rPos.Device = resultDBdoc.Device
	rPos.DeviceID = resultDBdoc.DeviceID
	return rPos
}

func existDoc(docid string, devid string) bool {
	var result dbDocument
	err := db.Get(&result, "SELECT * FROM document WHERE documentid=$1 AND device_id=$2", docid, devid)
	if err != nil {
		log.Println(err)
		if err == sql.ErrNoRows {
			return false
		}
	}
	return true
}

func updateDBdocument(username string, rPos requestPosition) int64 {
	nowtime := time.Now().Unix()
	if existDoc(rPos.DocumentID, rPos.DeviceID) {
		_, err := db.NamedExec("UPDATE document set username=:user, percentage=:perc, progress=:prog, timestamp=:time WHERE documentid=:docid AND device_id=:devid", map[string]interface{}{
			"user":  username,
			"perc":  rPos.Percentage,
			"prog":  rPos.Progress,
			"time":  nowtime,
			"docid": rPos.DocumentID,
			"devid": rPos.DeviceID,
		})
		if err != nil {
			log.Fatalln(err)
		}
		return nowtime
	}
	_, err := db.NamedExec("INSERT INTO document (username, documentid, percentage, progress, device, device_id, timestamp) VALUES (:user, :docid, :perc, :prog, :dev, :devid, :time)", map[string]interface{}{
		"user":  username,
		"docid": rPos.DocumentID,
		"perc":  rPos.Percentage,
		"prog":  rPos.Progress,
		"dev":   rPos.Device,
		"devid": rPos.DeviceID,
		"time":  nowtime,
	})
	if err != nil {
		log.Fatalln(err)
	}
	return nowtime
}