flounder

A simple gemini site builder
git clone git://git.alexwennerberg.com/flounder
Log | Files | Refs | README | LICENSE

db.go (7053B)


      1 package main
      2 
      3 import (
      4 	"crypto/rand"
      5 	"database/sql"
      6 	"fmt"
      7 	"io"
      8 	"io/ioutil"
      9 	"log"
     10 	"os"
     11 	"path"
     12 	"path/filepath"
     13 	"sort"
     14 	"strings"
     15 	"time"
     16 
     17 	"golang.org/x/crypto/bcrypt"
     18 )
     19 
     20 var DB *sql.DB
     21 
     22 func initializeDB() {
     23 	var err error
     24 	DB, err = sql.Open("sqlite3", c.DBFile)
     25 	if err != nil {
     26 		log.Fatal(err)
     27 	}
     28 	createTablesIfDNE()
     29 }
     30 
     31 // returns nil if login OK, err otherwise
     32 // log in with email or username
     33 func checkLogin(name string, password string) (string, bool, error) {
     34 	row := DB.QueryRow("SELECT username, password_hash, active, admin FROM user where username = $1 OR email = $1", name)
     35 	var db_password []byte
     36 	var username string
     37 	var active bool
     38 	var isAdmin bool
     39 	err := row.Scan(&username, &db_password, &active, &isAdmin)
     40 	if err != nil {
     41 		if strings.Contains(err.Error(), "no rows") {
     42 			return username, isAdmin, fmt.Errorf("Username or email '" + name + "' does not exist")
     43 		} else {
     44 			return username, isAdmin, err
     45 		}
     46 	}
     47 	if db_password != nil && !active {
     48 		return username, isAdmin, fmt.Errorf("Your account is not active yet. Pending admin approval")
     49 	}
     50 	if bcrypt.CompareHashAndPassword(db_password, []byte(password)) == nil {
     51 		return username, isAdmin, nil
     52 	} else {
     53 		return username, isAdmin, fmt.Errorf("Invalid password")
     54 	}
     55 }
     56 
     57 type File struct { // also folders
     58 	Creator     string
     59 	Name        string // includes folder
     60 	UpdatedTime time.Time
     61 	TimeAgo     string
     62 	IsText      bool
     63 	Children    []File
     64 	Host        string
     65 }
     66 
     67 func fileFromPath(fullPath string) File {
     68 	info, _ := os.Stat(fullPath)
     69 	creatorFolder := getCreator(fullPath)
     70 	updatedTime := info.ModTime()
     71 	return File{
     72 		Name:        getLocalPath(fullPath),
     73 		Creator:     path.Base(creatorFolder),
     74 		UpdatedTime: updatedTime,
     75 		TimeAgo:     timeago(&updatedTime),
     76 		Host:        c.Host,
     77 	}
     78 
     79 }
     80 
     81 type User struct {
     82 	Username      string
     83 	Email         string
     84 	Active        bool
     85 	Admin         bool
     86 	CreatedAt     int64 // timestamp
     87 	Reference     string
     88 	Domain        string
     89 	DomainEnabled bool
     90 }
     91 
     92 func getActiveUserNames() ([]string, error) {
     93 	rows, err := DB.Query(`SELECT username from user WHERE active is true order by username`)
     94 	if err != nil {
     95 		return nil, err
     96 	}
     97 	var users []string
     98 	for rows.Next() {
     99 		var user string
    100 		err = rows.Scan(&user)
    101 		if err != nil {
    102 			return nil, err
    103 		}
    104 		users = append(users, user)
    105 	}
    106 
    107 	return users, nil
    108 }
    109 
    110 var domains map[string]string
    111 
    112 func refreshDomainMap() error {
    113 	domains = make(map[string]string)
    114 	rows, err := DB.Query(`SELECT domain, username from user WHERE domain != ""`)
    115 	if err != nil {
    116 		log.Println(err)
    117 		return err
    118 	}
    119 	for rows.Next() {
    120 		var domain string
    121 		var username string
    122 		err = rows.Scan(&domain, &username)
    123 		if err != nil {
    124 			return err
    125 		}
    126 		domains[domain] = username
    127 	}
    128 	return nil
    129 }
    130 
    131 func getUserByName(username string) (*User, error) {
    132 	var user User
    133 	row := DB.QueryRow(`SELECT username, email, active, admin, created_at, reference, domain, domain_enabled from user WHERE username = ?`, username)
    134 	err := row.Scan(&user.Username, &user.Email, &user.Active, &user.Admin, &user.CreatedAt, &user.Reference, &user.Domain, &user.DomainEnabled)
    135 	if err != nil {
    136 		return nil, err
    137 	}
    138 	return &user, nil
    139 }
    140 
    141 func getUsers() ([]User, error) {
    142 	rows, err := DB.Query(`SELECT username, email, active, admin, created_at, reference, domain from user ORDER BY created_at DESC`)
    143 	if err != nil {
    144 		return nil, err
    145 	}
    146 	var users []User
    147 	for rows.Next() {
    148 		var user User
    149 		err = rows.Scan(&user.Username, &user.Email, &user.Active, &user.Admin, &user.CreatedAt, &user.Reference, &user.Domain)
    150 		if err != nil {
    151 			return nil, err
    152 		}
    153 		users = append(users, user)
    154 	}
    155 	return users, nil
    156 }
    157 
    158 // admin tells you whether to show hidden files etc
    159 // make sure user is a clean string
    160 func getUpdatedFiles(admin bool, user string) ([]*File, error) { // TODO cache this function
    161 	result := []*File{}
    162 	dir := c.FilesDirectory
    163 	if user != "" {
    164 		dir = path.Join(dir, user)
    165 	}
    166 	err := filepath.Walk(dir, func(thepath string, info os.FileInfo, err error) error {
    167 		if err != nil {
    168 			log.Printf("Failure accessing a path %q: %v\n", thepath, err)
    169 			return err // think about
    170 		}
    171 		if info.Name() == "bl4kers" {
    172 			// Lazy hack
    173 			return filepath.SkipDir
    174 		}
    175 		if !admin && info.IsDir() && info.Name() == HiddenFolder {
    176 			return filepath.SkipDir
    177 		}
    178 		// make this do what it should
    179 		if !info.IsDir() {
    180 			res := fileFromPath(thepath)
    181 			result = append(result, &res)
    182 		}
    183 		return nil
    184 	})
    185 	if err != nil {
    186 		return nil, err
    187 	}
    188 	sort.Slice(result, func(i, j int) bool {
    189 		return result[i].UpdatedTime.After(result[j].UpdatedTime)
    190 	})
    191 	// if many in a row, truncate
    192 	if user == "" {
    193 		newResult := []*File{}
    194 		for _, f := range result {
    195 			var already bool
    196 			// slow hack
    197 			for _, ff := range newResult {
    198 				if ff.Creator == f.Creator {
    199 					already = true
    200 					break
    201 				}
    202 			}
    203 			if !already {
    204 				newResult = append(newResult, f)
    205 			}
    206 		}
    207 		result = newResult
    208 	}
    209 
    210 	if len(result) > 50 {
    211 		result = result[:50]
    212 	}
    213 	return result, nil
    214 } // todo clean up paths
    215 
    216 func getMyFilesRecursive(p string, creator string) ([]File, error) {
    217 	result := []File{}
    218 	files, err := ioutil.ReadDir(p)
    219 	if err != nil {
    220 		return nil, err
    221 	}
    222 	for _, file := range files {
    223 		fullPath := path.Join(p, file.Name())
    224 		f := fileFromPath(fullPath)
    225 		f.IsText = isTextFile(fullPath)
    226 		if file.IsDir() {
    227 			f.Children, err = getMyFilesRecursive(path.Join(p, file.Name()), creator)
    228 		}
    229 		result = append(result, f)
    230 	}
    231 	return result, nil
    232 }
    233 
    234 func createTablesIfDNE() {
    235 	_, err := DB.Exec(`CREATE TABLE user (
    236   id INTEGER PRIMARY KEY NOT NULL,
    237   username TEXT NOT NULL UNIQUE,
    238   email TEXT NOT NULL UNIQUE,
    239   password_hash TEXT NOT NULL,
    240   reference TEXT NOT NULL default "",
    241   active boolean NOT NULL DEFAULT false,
    242   admin boolean NOT NULL DEFAULT false,
    243   created_at INTEGER DEFAULT (strftime('%s', 'now')),
    244   domain TEXT NOT NULL default "",
    245   domain_enabled BOOLEAN NOT NULL DEFAULT false
    246 );`)
    247 	if err == nil {
    248 		// on first creation, create admin user with pw admin
    249 		hashedPassword, err := bcrypt.GenerateFromPassword([]byte("admin"), 8) // TODO handle error
    250 		if err != nil {
    251 			log.Fatal(err)
    252 		}
    253 		_, err = DB.Exec(`INSERT OR IGNORE INTO user (username, email, password_hash, admin) values ('admin', 'default@flounder.local', ?, true)`, hashedPassword)
    254 		activateUser("admin")
    255 		if err != nil {
    256 			log.Fatal(err)
    257 		}
    258 	}
    259 
    260 	_, err = DB.Exec(`CREATE TABLE IF NOT EXISTS cookie_key (
    261   value TEXT NOT NULL
    262 );`)
    263 	if err != nil {
    264 		log.Fatal(err)
    265 	}
    266 }
    267 
    268 // Generate a cryptographically secure key for the cookie store
    269 func generateCookieKeyIfDNE() []byte {
    270 	rows, err := DB.Query("SELECT value FROM cookie_key LIMIT 1")
    271 	defer rows.Close()
    272 	if err != nil {
    273 		log.Fatal(err)
    274 	}
    275 	if rows.Next() {
    276 		var cookie []byte
    277 		err := rows.Scan(&cookie)
    278 		if err != nil {
    279 			log.Fatal(err)
    280 		}
    281 		return cookie
    282 	} else {
    283 		k := make([]byte, 32)
    284 		_, err := io.ReadFull(rand.Reader, k)
    285 		if err != nil {
    286 			log.Fatal(err)
    287 		}
    288 		_, err = DB.Exec("insert into cookie_key values (?)", k)
    289 		if err != nil {
    290 			log.Fatal(err)
    291 		}
    292 		return k
    293 	}
    294 }