No OneTemporary
Actions

Size

32 KB

Subscribers

None

View Options

	diff --git a/app.go b/app.go
	index e286ef0..40f42c9 100644
	--- a/app.go
	+++ b/app.go
	@@ -1,617 +1,627 @@
	/*
	* Copyright © 2018 A Bunch Tell LLC.
	*
	* This file is part of WriteFreely.
	*
	* WriteFreely is free software: you can redistribute it and/or modify
	* it under the terms of the GNU Affero General Public License, included
	* in the LICENSE file in this source code package.
	*/

	package writefreely

	import (
	"database/sql"
	"flag"
	"fmt"
	"html/template"
	"net/http"
	"net/url"
	"os"
	"os/signal"
	"path/filepath"
	"regexp"
	"strings"
	"syscall"
	"time"

	_ "github.com/go-sql-driver/mysql"

	"github.com/gorilla/mux"
	"github.com/gorilla/schema"
	"github.com/gorilla/sessions"
	"github.com/manifoldco/promptui"
	"github.com/writeas/go-strip-markdown"
	"github.com/writeas/web-core/auth"
	"github.com/writeas/web-core/converter"
	"github.com/writeas/web-core/log"
	"github.com/writeas/writefreely/author"
	"github.com/writeas/writefreely/config"
	"github.com/writeas/writefreely/migrations"
	"github.com/writeas/writefreely/page"
	)

	const (
	staticDir = "static"
	assumedTitleLen = 80
	postsPerPage = 10

	serverSoftware = "WriteFreely"
	softwareURL = "https://writefreely.org"
	)

	var (
	debugging bool

	// Software version can be set from git env using -ldflags
	softwareVer = "0.7.1"

	// DEPRECATED VARS
	// TODO: pass app.cfg into GetCollection* calls so we can get these values
	// from Collection methods and we no longer need these.
	hostName string
	isSingleUser bool
	)

	type app struct {
	router *mux.Router
	db *datastore
	cfg *config.Config
	cfgFile string
	keys *keychain
	sessionStore *sessions.CookieStore
	formDecoder *schema.Decoder

	timeline *localTimeline
	}

	// handleViewHome shows page at root path. Will be the Pad if logged in and the
	// catch-all landing page otherwise.
	func handleViewHome(app app, w http.ResponseWriter, r http.Request) error {
	if app.cfg.App.SingleUser {
	// Render blog index
	return handleViewCollection(app, w, r)
	}

	// Multi-user instance
	u := getUserSession(app, r)
	if u != nil {
	// User is logged in, so show the Pad
	return handleViewPad(app, w, r)
	}

	p := struct {
	page.StaticPage
	Flashes []template.HTML
	}{
	StaticPage: pageForReq(app, r),
	}

	// Get error messages
	session, err := app.sessionStore.Get(r, cookieName)
	if err != nil {
	// Ignore this
	log.Error("Unable to get session in handleViewHome; ignoring: %v", err)
	}
	flashes, _ := getSessionFlashes(app, w, r, session)
	for _, flash := range flashes {
	p.Flashes = append(p.Flashes, template.HTML(flash))
	}

	// Show landing page
	return renderPage(w, "landing.tmpl", p)
	}

	func handleTemplatedPage(app app, w http.ResponseWriter, r http.Request, t *template.Template) error {
	p := struct {
	page.StaticPage
	Content template.HTML
	PlainContent string
	Updated string

	AboutStats *InstanceStats
	}{
	StaticPage: pageForReq(app, r),
	}
	if r.URL.Path == "/about" \|\| r.URL.Path == "/privacy" {
	var c string
	var updated *time.Time
	var err error

	if r.URL.Path == "/about" {
	c, err = getAboutPage(app)

	// Fetch stats
	p.AboutStats = &InstanceStats{}
	p.AboutStats.NumPosts, _ = app.db.GetTotalPosts()
	p.AboutStats.NumBlogs, _ = app.db.GetTotalCollections()
	} else {
	c, updated, err = getPrivacyPage(app)
	}

	if err != nil {
	return err
	}
	p.Content = template.HTML(applyMarkdown([]byte(c)))
	p.PlainContent = shortPostDescription(stripmd.Strip(c))
	if updated != nil {
	p.Updated = updated.Format("January 2, 2006")
	}
	}

	// Serve templated page
	err := t.ExecuteTemplate(w, "base", p)
	if err != nil {
	log.Error("Unable to render page: %v", err)
	}
	return nil
	}

	func pageForReq(app app, r http.Request) page.StaticPage {
	p := page.StaticPage{
	AppCfg: app.cfg.App,
	Path: r.URL.Path,
	Version: "v" + softwareVer,
	}

	// Add user information, if given
	var u *User
	accessToken := r.FormValue("t")
	if accessToken != "" {
	userID := app.db.GetUserID(accessToken)
	if userID != -1 {
	var err error
	u, err = app.db.GetUserByID(userID)
	if err == nil {
	p.Username = u.Username
	}
	}
	} else {
	u = getUserSession(app, r)
	if u != nil {
	p.Username = u.Username
	}
	}

	return p
	}

	var shttp = http.NewServeMux()
	var fileRegex = regexp.MustCompile("/([^/]\\.[^/])$")

	func Serve() {
	debugPtr := flag.Bool("debug", false, "Enables debug logging.")
	createConfig := flag.Bool("create-config", false, "Creates a basic configuration and exits")
	doConfig := flag.Bool("config", false, "Run the configuration process")
	genKeys := flag.Bool("gen-keys", false, "Generate encryption and authentication keys")
	createSchema := flag.Bool("init-db", false, "Initialize app database")
	migrate := flag.Bool("migrate", false, "Migrate the database")
	createAdmin := flag.String("create-admin", "", "Create an admin with the given username:password")
	createUser := flag.String("create-user", "", "Create a regular user with the given username:password")
	resetPassUser := flag.String("reset-pass", "", "Reset the given user's password")
	configFile := flag.String("c", "config.ini", "The configuration file to use")
	outputVersion := flag.Bool("v", false, "Output the current version")
	flag.Parse()

	debugging = *debugPtr

	app := &app{
	cfgFile: *configFile,
	}

	if *outputVersion {
	fmt.Println(serverSoftware + " " + softwareVer)
	os.Exit(0)
	} else if *createConfig {
	log.Info("Creating configuration...")
	c := config.New()
	log.Info("Saving configuration %s...", app.cfgFile)
	err := config.Save(c, app.cfgFile)
	if err != nil {
	log.Error("Unable to save configuration: %v", err)
	os.Exit(1)
	}
	os.Exit(0)
	} else if *doConfig {
	d, err := config.Configure(app.cfgFile)
	if err != nil {
	log.Error("Unable to configure: %v", err)
	os.Exit(1)
	}
	if d.User != nil {
	app.cfg = d.Config
	connectToDatabase(app)
	defer shutdown(app)

	if !app.db.DatabaseInitialized() {
	adminInitDatabase(app)
	}

	u := &User{
	Username: d.User.Username,
	HashedPass: d.User.HashedPass,
	Created: time.Now().Truncate(time.Second).UTC(),
	}

	// Create blog
	log.Info("Creating user %s...\n", u.Username)
	err = app.db.CreateUser(u, app.cfg.App.SiteName)
	if err != nil {
	log.Error("Unable to create user: %s", err)
	os.Exit(1)
	}
	log.Info("Done!")
	}
	os.Exit(0)
	} else if *genKeys {
	errStatus := 0

	// Read keys path from config
	loadConfig(app)

	// Create keys dir if it doesn't exist yet
	fullKeysDir := filepath.Join(app.cfg.Server.KeysParentDir, keysDir)
	if _, err := os.Stat(fullKeysDir); os.IsNotExist(err) {
	err = os.Mkdir(fullKeysDir, 0700)
	if err != nil {
	log.Error("%s", err)
	os.Exit(1)
	}
	}

	// Generate keys
	initKeyPaths(app)
	err := generateKey(emailKeyPath)
	if err != nil {
	errStatus = 1
	}
	err = generateKey(cookieAuthKeyPath)
	if err != nil {
	errStatus = 1
	}
	err = generateKey(cookieKeyPath)
	if err != nil {
	errStatus = 1
	}

	os.Exit(errStatus)
	} else if *createSchema {
	loadConfig(app)
	connectToDatabase(app)
	defer shutdown(app)
	adminInitDatabase(app)
	} else if *createAdmin != "" {
	adminCreateUser(app, *createAdmin, true)
	} else if *createUser != "" {
	adminCreateUser(app, *createUser, false)
	} else if *resetPassUser != "" {
	// Connect to the database
	loadConfig(app)
	connectToDatabase(app)
	defer shutdown(app)

	// Fetch user
	u, err := app.db.GetUserForAuth(*resetPassUser)
	if err != nil {
	log.Error("Get user: %s", err)
	os.Exit(1)
	}

	// Prompt for new password
	prompt := promptui.Prompt{
	Templates: &promptui.PromptTemplates{
	Success: "{{ . \| bold \| faint }}: ",
	},
	Label: "New password",
	Mask: '*',
	}
	newPass, err := prompt.Run()
	if err != nil {
	log.Error("%s", err)
	os.Exit(1)
	}

	// Do the update
	log.Info("Updating...")
	err = adminResetPassword(app, u, newPass)
	if err != nil {
	log.Error("%s", err)
	os.Exit(1)
	}
	log.Info("Success.")
	os.Exit(0)
	} else if *migrate {
	loadConfig(app)
	connectToDatabase(app)
	defer shutdown(app)

	err := migrations.Migrate(migrations.NewDatastore(app.db.DB, app.db.driverName))
	if err != nil {
	log.Error("migrate: %s", err)
	os.Exit(1)
	}

	os.Exit(0)
	}

	log.Info("Initializing...")

	loadConfig(app)

	hostName = app.cfg.App.Host
	isSingleUser = app.cfg.App.SingleUser
	app.cfg.Server.Dev = *debugPtr

	err := initTemplates(app.cfg)
	if err != nil {
	log.Error("load templates: %s", err)
	os.Exit(1)
	}

	// Load keys
	log.Info("Loading encryption keys...")
	initKeyPaths(app)
	err = initKeys(app)
	if err != nil {
	log.Error("\n%s\n", err)
	}

	// Initialize modules
	app.sessionStore = initSession(app)
	app.formDecoder = schema.NewDecoder()
	app.formDecoder.RegisterConverter(converter.NullJSONString{}, converter.ConvertJSONNullString)
	app.formDecoder.RegisterConverter(converter.NullJSONBool{}, converter.ConvertJSONNullBool)
	app.formDecoder.RegisterConverter(sql.NullString{}, converter.ConvertSQLNullString)
	app.formDecoder.RegisterConverter(sql.NullBool{}, converter.ConvertSQLNullBool)
	app.formDecoder.RegisterConverter(sql.NullInt64{}, converter.ConvertSQLNullInt64)
	app.formDecoder.RegisterConverter(sql.NullFloat64{}, converter.ConvertSQLNullFloat64)

	// Check database configuration
	if app.cfg.Database.Type == driverMySQL && (app.cfg.Database.User == "" \|\| app.cfg.Database.Password == "") {
	log.Error("Database user or password not set.")
	os.Exit(1)
	}
	if app.cfg.Database.Host == "" {
	app.cfg.Database.Host = "localhost"
	}
	if app.cfg.Database.Database == "" {
	app.cfg.Database.Database = "writefreely"
	}

	connectToDatabase(app)
	defer shutdown(app)

	// Test database connection
	err = app.db.Ping()
	if err != nil {
	log.Error("Database ping failed: %s", err)
	}

	r := mux.NewRouter()
	handler := NewHandler(app)
	handler.SetErrorPages(&ErrorPages{
	NotFound: pages["404-general.tmpl"],
	Gone: pages["410.tmpl"],
	InternalServerError: pages["500.tmpl"],
	Blank: pages["blank.tmpl"],
	})

	// Handle app routes
	initRoutes(handler, r, app.cfg, app.db)

	// Handle local timeline, if enabled
	if app.cfg.App.LocalTimeline {
	log.Info("Initializing local timeline...")
	initLocalTimeline(app)
	}

	// Handle static files
	fs := http.FileServer(http.Dir(filepath.Join(app.cfg.Server.StaticParentDir, staticDir)))
	shttp.Handle("/", fs)
	r.PathPrefix("/").Handler(fs)

	// Handle shutdown
	c := make(chan os.Signal, 2)
	signal.Notify(c, os.Interrupt, syscall.SIGTERM)
	go func() {
	<-c
	log.Info("Shutting down...")
	shutdown(app)
	log.Info("Done.")
	os.Exit(0)
	}()

	http.Handle("/", r)

	// Start web application server
	var bindAddress = app.cfg.Server.Bind
	if bindAddress == "" {
	bindAddress = "localhost"
	}
	if app.cfg.IsSecureStandalone() {
	log.Info("Serving redirects on http://%s:80", bindAddress)
	go func() {
	err = http.ListenAndServe(
	fmt.Sprintf("%s:80", bindAddress), http.HandlerFunc(func(w http.ResponseWriter, r *http.Request) {
	http.Redirect(w, r, app.cfg.App.Host, http.StatusMovedPermanently)
	}))
	log.Error("Unable to start redirect server: %v", err)
	}()

	log.Info("Serving on https://%s:443", bindAddress)
	log.Info("---")
	err = http.ListenAndServeTLS(
	fmt.Sprintf("%s:443", bindAddress), app.cfg.Server.TLSCertPath, app.cfg.Server.TLSKeyPath, nil)
	} else {
	log.Info("Serving on http://%s:%d\n", bindAddress, app.cfg.Server.Port)
	log.Info("---")
	err = http.ListenAndServe(fmt.Sprintf("%s:%d", bindAddress, app.cfg.Server.Port), nil)
	}
	if err != nil {
	log.Error("Unable to start: %v", err)
	os.Exit(1)
	}
	}

	func loadConfig(app *app) {
	log.Info("Loading %s configuration...", app.cfgFile)
	cfg, err := config.Load(app.cfgFile)
	if err != nil {
	log.Error("Unable to load configuration: %v", err)
	os.Exit(1)
	}
	app.cfg = cfg
	}

	func connectToDatabase(app *app) {
	log.Info("Connecting to %s database...", app.cfg.Database.Type)

	var db *sql.DB
	var err error
	if app.cfg.Database.Type == driverMySQL {
	db, err = sql.Open(app.cfg.Database.Type, fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=true&loc=%s", app.cfg.Database.User, app.cfg.Database.Password, app.cfg.Database.Host, app.cfg.Database.Port, app.cfg.Database.Database, url.QueryEscape(time.Local.String())))
	db.SetMaxOpenConns(50)
	} else if app.cfg.Database.Type == driverSQLite {
	if !SQLiteEnabled {
	log.Error("Invalid database type '%s'. Binary wasn't compiled with SQLite3 support.", app.cfg.Database.Type)
	os.Exit(1)
	}
	if app.cfg.Database.FileName == "" {
	log.Error("SQLite database filename value in config.ini is empty.")
	os.Exit(1)
	}
	db, err = sql.Open("sqlite3_with_regex", app.cfg.Database.FileName+"?parseTime=true&cached=shared")
	db.SetMaxOpenConns(1)
	} else {
	log.Error("Invalid database type '%s'. Only 'mysql' and 'sqlite3' are supported right now.", app.cfg.Database.Type)
	os.Exit(1)
	}
	if err != nil {
	log.Error("%s", err)
	os.Exit(1)
	}
	app.db = &datastore{db, app.cfg.Database.Type}
	}

	func shutdown(app *app) {
	log.Info("Closing database connection...")
	app.db.Close()
	}

	func adminCreateUser(app *app, credStr string, isAdmin bool) {
	// Create an admin user with --create-admin
	creds := strings.Split(credStr, ":")
	if len(creds) != 2 {
	log.Error("usage: writefreely --create-admin username:password")
	os.Exit(1)
	}

	loadConfig(app)
	connectToDatabase(app)
	defer shutdown(app)

	// Ensure an admin / first user doesn't already exist
	firstUser, _ := app.db.GetUserByID(1)
	if isAdmin {
	// Abort if trying to create admin user, but one already exists
	if firstUser != nil {
	log.Error("Admin user already exists (%s). Create a regular user with: writefreely --create-user", firstUser.Username)
	os.Exit(1)
	}
	} else {
	// Abort if trying to create regular user, but no admin exists yet
	if firstUser == nil {
	log.Error("No admin user exists yet. Create an admin first with: writefreely --create-admin")
	os.Exit(1)
	}
	}

	// Create the user
	username := creds[0]
	password := creds[1]

	// Normalize and validate username
	desiredUsername := username
	username = getSlug(username, "")

	usernameDesc := username
	if username != desiredUsername {
	usernameDesc += " (originally: " + desiredUsername + ")"
	}

	if !author.IsValidUsername(app.cfg, username) {
	log.Error("Username %s is invalid, reserved, or shorter than configured minimum length (%d characters).", usernameDesc, app.cfg.App.MinUsernameLen)
	os.Exit(1)
	}

	// Hash the password
	hashedPass, err := auth.HashPass([]byte(password))
	if err != nil {
	log.Error("Unable to hash password: %v", err)
	os.Exit(1)
	}

	u := &User{
	Username: username,
	HashedPass: hashedPass,
	Created: time.Now().Truncate(time.Second).UTC(),
	}

	userType := "user"
	if isAdmin {
	userType = "admin"
	}
	log.Info("Creating %s %s...", userType, usernameDesc)
	err = app.db.CreateUser(u, desiredUsername)
	if err != nil {
	log.Error("Unable to create user: %s", err)
	os.Exit(1)
	}
	log.Info("Done!")
	os.Exit(0)
	}

	func adminInitDatabase(app *app) {
	schemaFileName := "schema.sql"
	if app.cfg.Database.Type == driverSQLite {
	schemaFileName = "sqlite.sql"
	}

	schema, err := Asset(schemaFileName)
	if err != nil {
	log.Error("Unable to load schema file: %v", err)
	os.Exit(1)
	}

	tblReg := regexp.MustCompile("CREATE TABLE (IF NOT EXISTS )?`([a-z_]+)`")

	queries := strings.Split(string(schema), ";\n")
	for _, q := range queries {
	if strings.TrimSpace(q) == "" {
	continue
	}
	parts := tblReg.FindStringSubmatch(q)
	if len(parts) >= 3 {
	log.Info("Creating table %s...", parts[2])
	} else {
	log.Info("Creating table ??? (Weird query) No match in: %v", parts)
	}
	_, err = app.db.Exec(q)
	if err != nil {
	log.Error("%s", err)
	} else {
	log.Info("Created.")
	}
	}
	+
	+ // Set up migrations table
	+ log.Info("Updating appmigrations table...")
	+ err = migrations.SetInitialMigrations(migrations.NewDatastore(app.db.DB, app.db.driverName))
	+ if err != nil {
	+ log.Error("Unable to set initial migrations: %v", err)
	+ os.Exit(1)
	+ }
	+ log.Info("Done.")
	+
	os.Exit(0)
	}
	diff --git a/migrations/migrations.go b/migrations/migrations.go
	index 2aa3643..1d6e0c4 100644
	--- a/migrations/migrations.go
	+++ b/migrations/migrations.go
	@@ -1,118 +1,131 @@
	/*
	* Copyright © 2019 A Bunch Tell LLC.
	*
	* This file is part of WriteFreely.
	*
	* WriteFreely is free software: you can redistribute it and/or modify
	* it under the terms of the GNU Affero General Public License, included
	* in the LICENSE file in this source code package.
	*/

	// Package migrations contains database migrations for WriteFreely
	package migrations

	import (
	"database/sql"
	"github.com/writeas/web-core/log"
	)

	// TODO: refactor to use the datastore struct from writefreely pkg
	type datastore struct {
	*sql.DB
	driverName string
	}

	func NewDatastore(db sql.DB, dn string) datastore {
	return &datastore{db, dn}
	}

	// TODO: use these consts from writefreely pkg
	const (
	driverMySQL = "mysql"
	driverSQLite = "sqlite3"
	)

	type Migration interface {
	Description() string
	Migrate(db *datastore) error
	}

	type migration struct {
	description string
	migrate func(db *datastore) error
	}

	func New(d string, fn func(db *datastore) error) Migration {
	return &migration{d, fn}
	}

	func (m *migration) Description() string {
	return m.description
	}

	func (m migration) Migrate(db datastore) error {
	return m.migrate(db)
	}

	var migrations = []Migration{
	New("support user invites", supportUserInvites), // -> V1 (v0.8.0)
	}

	+// CurrentVer returns the current migration version the application is on
	+func CurrentVer() int {
	+ return len(migrations)
	+}
	+
	+func SetInitialMigrations(db *datastore) error {
	+ _, err := db.Exec("INSERT INTO appmigrations (version, migrated, result) VALUES (?, "+db.now()+", ?)", CurrentVer(), "")
	+ if err != nil {
	+ return err
	+ }
	+ return nil
	+}
	+
	func Migrate(db *datastore) error {
	var version int
	var err error
	if db.tableExists("appmigrations") {
	err = db.QueryRow("SELECT MAX(version) FROM appmigrations").Scan(&version)
	} else {
	log.Info("Initializing appmigrations table...")
	version = 0
	_, err = db.Exec(`CREATE TABLE appmigrations (
	version ` + db.typeInt() + ` NOT NULL,
	migrated ` + db.typeDateTime() + ` NOT NULL,
	result ` + db.typeText() + ` NOT NULL
	) ` + db.engine() + `;`)
	if err != nil {
	return err
	}
	}

	if len(migrations[version:]) > 0 {
	for i, m := range migrations[version:] {
	curVer := version + i + 1
	log.Info("Migrating to V%d: %s", curVer, m.Description())
	err = m.Migrate(db)
	if err != nil {
	return err
	}

	// Update migrations table
	_, err = db.Exec("INSERT INTO appmigrations (version, migrated, result) VALUES (?, "+db.now()+", ?)", curVer, "")
	if err != nil {
	return err
	}
	}
	} else {
	log.Info("Database up-to-date. No migrations to run.")
	}

	return nil
	}

	func (db *datastore) tableExists(t string) bool {
	var dummy string
	var err error
	if db.driverName == driverSQLite {
	err = db.QueryRow("SELECT name FROM sqlite_master WHERE type = 'table' AND name = ?", t).Scan(&dummy)
	} else {
	err = db.QueryRow("SHOW TABLES LIKE '" + t + "'").Scan(&dummy)
	}
	switch {
	case err == sql.ErrNoRows:
	return false
	case err != nil:
	log.Error("Couldn't SHOW TABLES: %v", err)
	return false
	}

	return true
	}
	diff --git a/schema.sql b/schema.sql
	index 6687f5d..b3fae97 100644
	--- a/schema.sql
	+++ b/schema.sql
	@@ -1,229 +1,241 @@
	--
	-- Database: `writefreely`
	--

	-- --------------------------------------------------------

	--
	-- Table structure for table `accesstokens`
	--

	CREATE TABLE IF NOT EXISTS `accesstokens` (
	`token` binary(16) NOT NULL,
	`user_id` int(6) NOT NULL,
	`sudo` tinyint(1) NOT NULL DEFAULT '0',
	`one_time` tinyint(1) NOT NULL DEFAULT '0',
	`created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`expires` datetime DEFAULT NULL,
	`user_agent` varchar(255) DEFAULT NULL,
	PRIMARY KEY (`token`)
	) ENGINE=InnoDB DEFAULT CHARSET=latin1;

	-- --------------------------------------------------------

	--
	-- Table structure for table `appcontent`
	--

	CREATE TABLE IF NOT EXISTS `appcontent` (
	`id` varchar(36) NOT NULL,
	`content` mediumtext CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
	`updated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`)
	) ENGINE=InnoDB DEFAULT CHARSET=latin1;

	-- --------------------------------------------------------

	+--
	+-- Table structure for table `appmigrations`
	+--
	+
	+CREATE TABLE `appmigrations` (
	+ `version` int(11) NOT NULL,
	+ `migrated` datetime NOT NULL,
	+ `result` text NOT NULL
	+) ENGINE=InnoDB DEFAULT CHARSET=latin1;
	+
	+-- --------------------------------------------------------
	+
	--
	-- Table structure for table `collectionattributes`
	--

	CREATE TABLE IF NOT EXISTS `collectionattributes` (
	`collection_id` int(6) NOT NULL,
	`attribute` varchar(128) NOT NULL,
	`value` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
	PRIMARY KEY (`collection_id`,`attribute`)
	) ENGINE=InnoDB DEFAULT CHARSET=latin1;

	-- --------------------------------------------------------

	--
	-- Table structure for table `collectionkeys`
	--

	CREATE TABLE IF NOT EXISTS `collectionkeys` (
	`collection_id` int(6) NOT NULL,
	`public_key` blob NOT NULL,
	`private_key` blob NOT NULL,
	PRIMARY KEY (`collection_id`)
	) ENGINE=InnoDB DEFAULT CHARSET=latin1;

	-- --------------------------------------------------------

	--
	-- Table structure for table `collectionpasswords`
	--

	CREATE TABLE IF NOT EXISTS `collectionpasswords` (
	`collection_id` int(6) NOT NULL,
	`password` char(60) NOT NULL,
	PRIMARY KEY (`collection_id`)
	) ENGINE=InnoDB DEFAULT CHARSET=latin1;

	-- --------------------------------------------------------

	--
	-- Table structure for table `collectionredirects`
	--

	CREATE TABLE IF NOT EXISTS `collectionredirects` (
	`prev_alias` varchar(100) NOT NULL,
	`new_alias` varchar(100) NOT NULL,
	PRIMARY KEY (`prev_alias`)
	) ENGINE=InnoDB DEFAULT CHARSET=latin1;

	-- --------------------------------------------------------

	--
	-- Table structure for table `collections`
	--

	CREATE TABLE IF NOT EXISTS `collections` (
	`id` int(6) NOT NULL AUTO_INCREMENT,
	`alias` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
	`title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
	`description` varchar(160) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
	`style_sheet` text,
	`script` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
	`format` varchar(8) DEFAULT NULL,
	`privacy` tinyint(1) NOT NULL,
	`owner_id` int(6) NOT NULL,
	`view_count` int(6) NOT NULL,
	PRIMARY KEY (`id`),
	UNIQUE KEY `alias` (`alias`)
	) ENGINE=InnoDB DEFAULT CHARSET=latin1;

	-- --------------------------------------------------------

	--
	-- Table structure for table `posts`
	--

	CREATE TABLE IF NOT EXISTS `posts` (
	`id` char(16) NOT NULL,
	`slug` varchar(100) DEFAULT NULL,
	`modify_token` char(32) DEFAULT NULL,
	`text_appearance` char(4) NOT NULL DEFAULT 'norm',
	`language` char(2) DEFAULT NULL,
	`rtl` tinyint(1) DEFAULT NULL,
	`privacy` tinyint(1) NOT NULL,
	`owner_id` int(6) DEFAULT NULL,
	`collection_id` int(6) DEFAULT NULL,
	`pinned_position` tinyint(1) UNSIGNED DEFAULT NULL,
	`created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`view_count` int(6) NOT NULL,
	`title` varchar(160) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
	`content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
	PRIMARY KEY (`id`),
	UNIQUE KEY `id_slug` (`collection_id`,`slug`),
	UNIQUE KEY `owner_id` (`owner_id`,`id`),
	KEY `privacy_id` (`privacy`,`id`)
	) ENGINE=InnoDB DEFAULT CHARSET=latin1;

	-- --------------------------------------------------------

	--
	-- Table structure for table `remotefollows`
	--

	CREATE TABLE IF NOT EXISTS `remotefollows` (
	`collection_id` int(11) NOT NULL,
	`remote_user_id` int(11) NOT NULL,
	`created` datetime NOT NULL,
	PRIMARY KEY (`collection_id`,`remote_user_id`)
	) ENGINE=InnoDB DEFAULT CHARSET=latin1;

	-- --------------------------------------------------------

	--
	-- Table structure for table `remoteuserkeys`
	--

	CREATE TABLE IF NOT EXISTS `remoteuserkeys` (
	`id` varchar(255) NOT NULL,
	`remote_user_id` int(11) NOT NULL,
	`public_key` blob NOT NULL,
	PRIMARY KEY (`id`),
	UNIQUE KEY `follower_id` (`remote_user_id`)
	) ENGINE=InnoDB DEFAULT CHARSET=latin1;

	-- --------------------------------------------------------

	--
	-- Table structure for table `remoteusers`
	--

	CREATE TABLE IF NOT EXISTS `remoteusers` (
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`actor_id` varchar(255) NOT NULL,
	`inbox` varchar(255) NOT NULL,
	`shared_inbox` varchar(255) NOT NULL,
	PRIMARY KEY (`id`),
	UNIQUE KEY `collection_id` (`actor_id`)
	) ENGINE=InnoDB DEFAULT CHARSET=latin1;

	-- --------------------------------------------------------

	--
	-- Table structure for table `userattributes`
	--

	CREATE TABLE IF NOT EXISTS `userattributes` (
	`user_id` int(6) NOT NULL,
	`attribute` varchar(64) NOT NULL,
	`value` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
	PRIMARY KEY (`user_id`,`attribute`)
	) ENGINE=InnoDB DEFAULT CHARSET=latin1;

	-- --------------------------------------------------------

	--
	-- Table structure for table `userinvites`
	--

	CREATE TABLE `userinvites` (
	`id` char(6) NOT NULL,
	`owner_id` int(11) NOT NULL,
	`max_uses` smallint(6) DEFAULT NULL,
	`created` datetime NOT NULL,
	`expires` datetime DEFAULT NULL,
	`inactive` tinyint(1) NOT NULL
	) ENGINE=InnoDB DEFAULT CHARSET=latin1;

	-- --------------------------------------------------------

	--
	-- Table structure for table `users`
	--

	CREATE TABLE IF NOT EXISTS `users` (
	`id` int(6) NOT NULL AUTO_INCREMENT,
	`username` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
	`password` char(60) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
	`email` varbinary(255) DEFAULT NULL,
	`created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`),
	UNIQUE KEY `username` (`username`)
	) ENGINE=InnoDB DEFAULT CHARSET=latin1;

	-- --------------------------------------------------------

	--
	-- Table structure for table `usersinvited`
	--

	CREATE TABLE `usersinvited` (
	`invite_id` char(6) NOT NULL,
	`user_id` int(11) NOT NULL
	) ENGINE=InnoDB DEFAULT CHARSET=latin1;
	diff --git a/sqlite.sql b/sqlite.sql
	index 6c7160a..90989ed 100644
	--- a/sqlite.sql
	+++ b/sqlite.sql
	@@ -1,217 +1,229 @@
	--
	-- Database: writefreely
	--

	-- --------------------------------------------------------

	--
	-- Table structure for table accesstokens
	--

	CREATE TABLE IF NOT EXISTS `accesstokens` (
	token TEXT NOT NULL PRIMARY KEY,
	user_id INTEGER NOT NULL,
	sudo INTEGER NOT NULL DEFAULT '0',
	one_time INTEGER NOT NULL DEFAULT '0',
	created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	expires DATETIME DEFAULT NULL,
	user_agent TEXT DEFAULT NULL
	);

	-- --------------------------------------------------------

	--
	-- Table structure for table appcontent
	--

	CREATE TABLE IF NOT EXISTS `appcontent` (
	id TEXT NOT NULL PRIMARY KEY,
	content TEXT NOT NULL,
	updated DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
	);

	-- --------------------------------------------------------

	+--
	+-- Table structure for table appmigrations
	+--
	+
	+CREATE TABLE `appmigrations` (
	+ `version` INT NOT NULL,
	+ `migrated` DATETIME NOT NULL,
	+ `result` TEXT NOT NULL
	+);
	+
	+-- --------------------------------------------------------
	+
	--
	-- Table structure for table collectionattributes
	--

	CREATE TABLE IF NOT EXISTS `collectionattributes` (
	collection_id INTEGER NOT NULL,
	attribute TEXT NOT NULL,
	value TEXT NOT NULL,
	PRIMARY KEY (collection_id, attribute)
	);

	-- --------------------------------------------------------

	--
	-- Table structure for table collectionkeys
	--

	CREATE TABLE IF NOT EXISTS `collectionkeys` (
	collection_id INTEGER PRIMARY KEY,
	public_key blob NOT NULL,
	private_key blob NOT NULL
	);

	-- --------------------------------------------------------

	--
	-- Table structure for table collectionpasswords
	--

	CREATE TABLE IF NOT EXISTS `collectionpasswords` (
	collection_id INTEGER PRIMARY KEY,
	password TEXT NOT NULL
	);

	-- --------------------------------------------------------

	--
	-- Table structure for table collectionredirects
	--

	CREATE TABLE IF NOT EXISTS `collectionredirects` (
	prev_alias TEXT NOT NULL PRIMARY KEY,
	new_alias TEXT NOT NULL
	);

	-- --------------------------------------------------------

	--
	-- Table structure for table collections
	--

	CREATE TABLE IF NOT EXISTS `collections` (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	alias TEXT DEFAULT NULL UNIQUE,
	title TEXT NOT NULL,
	description TEXT NOT NULL,
	style_sheet TEXT,
	script TEXT,
	format TEXT DEFAULT NULL,
	privacy INTEGER NOT NULL,
	owner_id INTEGER NOT NULL,
	view_count INTEGER NOT NULL
	);

	-- --------------------------------------------------------

	--
	-- Table structure for table posts
	--

	CREATE TABLE IF NOT EXISTS `posts` (
	id TEXT NOT NULL,
	slug TEXT DEFAULT NULL,
	modify_token TEXT DEFAULT NULL,
	text_appearance TEXT NOT NULL DEFAULT 'norm',
	language TEXT DEFAULT NULL,
	rtl INTEGER DEFAULT NULL,
	privacy INTEGER NOT NULL,
	owner_id INTEGER DEFAULT NULL,
	collection_id INTEGER DEFAULT NULL,
	pinned_position INTEGER UNSIGNED DEFAULT NULL,
	created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	view_count INTEGER NOT NULL,
	title TEXT NOT NULL,
	content TEXT NOT NULL,
	CONSTRAINT id_slug UNIQUE (collection_id, slug),
	CONSTRAINT owner_id UNIQUE (owner_id, id),
	CONSTRAINT privacy_id UNIQUE (privacy, id)
	);

	-- --------------------------------------------------------

	--
	-- Table structure for table remotefollows
	--

	CREATE TABLE IF NOT EXISTS `remotefollows` (
	collection_id INTEGER NOT NULL,
	remote_user_id INTEGER NOT NULL,
	created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (collection_id,remote_user_id)
	);

	-- --------------------------------------------------------

	--
	-- Table structure for table remoteuserkeys
	--

	CREATE TABLE IF NOT EXISTS `remoteuserkeys` (
	id TEXT NOT NULL,
	remote_user_id INTEGER NOT NULL,
	public_key blob NOT NULL,
	CONSTRAINT follower_id UNIQUE (remote_user_id)
	);

	-- --------------------------------------------------------

	--
	-- Table structure for table remoteusers
	--

	CREATE TABLE IF NOT EXISTS `remoteusers` (
	id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	actor_id TEXT NOT NULL,
	inbox TEXT NOT NULL,
	shared_inbox TEXT NOT NULL,
	CONSTRAINT collection_id UNIQUE (actor_id)
	);

	-- --------------------------------------------------------

	--
	-- Table structure for table userattributes
	--

	CREATE TABLE IF NOT EXISTS `userattributes` (
	user_id INTEGER NOT NULL,
	attribute TEXT NOT NULL,
	value TEXT NOT NULL,
	PRIMARY KEY (user_id, attribute)
	);

	-- --------------------------------------------------------

	--
	-- Table structure for table `userinvites`
	--

	CREATE TABLE `userinvites` (
	`id` TEXT NOT NULL,
	`owner_id` INTEGER NOT NULL,
	`max_uses` INTEGER DEFAULT NULL,
	`created` DATETIME NOT NULL,
	`expires` DATETIME DEFAULT NULL,
	`inactive` INTEGER NOT NULL
	);

	-- --------------------------------------------------------

	--
	-- Table structure for table users
	--

	CREATE TABLE IF NOT EXISTS `users` (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	username TEXT NOT NULL UNIQUE,
	password TEXT NOT NULL,
	email TEXT DEFAULT NULL,
	created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
	);

	-- --------------------------------------------------------

	--
	-- Table structure for table `usersinvited`
	--

	CREATE TABLE `usersinvited` (
	`invite_id` TEXT NOT NULL,
	`user_id` INTEGER NOT NULL
	);

File Metadata

Mime Type: text/x-diff
Expires: Sun, Dec 14, 3:21 AM (1 d, 15 h)
Storage Engine: blob
Storage Format: Raw Data
Storage Handle: 3537459

No OneTemporaryActions

View Options

File Metadata

Event Timeline

No OneTemporary
Actions