diff --git a/db/create.go b/db/create.go index 1e9e679..164eab8 100644 --- a/db/create.go +++ b/db/create.go @@ -1,263 +1,272 @@ /* * Copyright © 2019-2020 Musing Studio 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 db import ( "fmt" "strings" ) type ColumnType int type OptionalInt struct { Set bool Value int } type OptionalString struct { Set bool Value string } type SQLBuilder interface { ToSQL() (string, error) } type Column struct { Dialect DialectType Name string Nullable bool Default OptionalString Type ColumnType Size OptionalInt PrimaryKey bool } type CreateTableSqlBuilder struct { Dialect DialectType Name string IfNotExists bool ColumnOrder []string Columns map[string]*Column Constraints []string } const ( ColumnTypeBool ColumnType = iota ColumnTypeSmallInt ColumnType = iota ColumnTypeInteger ColumnType = iota ColumnTypeChar ColumnType = iota ColumnTypeVarChar ColumnType = iota ColumnTypeText ColumnType = iota + ColumnTypeLongText ColumnType = iota ColumnTypeDateTime ColumnType = iota ) var _ SQLBuilder = &CreateTableSqlBuilder{} var UnsetSize OptionalInt = OptionalInt{Set: false, Value: 0} var UnsetDefault OptionalString = OptionalString{Set: false, Value: ""} func (d ColumnType) Format(dialect DialectType, size OptionalInt) (string, error) { if dialect != DialectMySQL && dialect != DialectSQLite { return "", fmt.Errorf("unsupported column type %d for dialect %d and size %v", d, dialect, size) } switch d { case ColumnTypeSmallInt: { if dialect == DialectSQLite { return "INTEGER", nil } mod := "" if size.Set { mod = fmt.Sprintf("(%d)", size.Value) } return "SMALLINT" + mod, nil } case ColumnTypeInteger: { if dialect == DialectSQLite { return "INTEGER", nil } mod := "" if size.Set { mod = fmt.Sprintf("(%d)", size.Value) } return "INT" + mod, nil } case ColumnTypeChar: { if dialect == DialectSQLite { return "TEXT", nil } mod := "" if size.Set { mod = fmt.Sprintf("(%d)", size.Value) } return "CHAR" + mod, nil } case ColumnTypeVarChar: { if dialect == DialectSQLite { return "TEXT", nil } mod := "" if size.Set { mod = fmt.Sprintf("(%d)", size.Value) } return "VARCHAR" + mod, nil } case ColumnTypeBool: { if dialect == DialectSQLite { return "INTEGER", nil } return "TINYINT(1)", nil } case ColumnTypeDateTime: return "DATETIME", nil case ColumnTypeText: return "TEXT", nil + case ColumnTypeLongText: + { + // MySQL TEXT is limited to 64KB, so use MEDIUMTEXT for larger sizes (up to 16MB) + if dialect == DialectMySQL { + return "MEDIUMTEXT", nil + } + return "TEXT", nil + } } return "", fmt.Errorf("unsupported column type %d for dialect %d and size %v", d, dialect, size) } func (c *Column) SetName(name string) *Column { c.Name = name return c } func (c *Column) SetNullable(nullable bool) *Column { c.Nullable = nullable return c } func (c *Column) SetPrimaryKey(pk bool) *Column { c.PrimaryKey = pk return c } func (c *Column) SetDefault(value string) *Column { c.Default = OptionalString{Set: true, Value: value} return c } func (c *Column) SetDefaultCurrentTimestamp() *Column { def := "NOW()" if c.Dialect == DialectSQLite { def = "CURRENT_TIMESTAMP" } c.Default = OptionalString{Set: true, Value: def} return c } func (c *Column) SetType(t ColumnType) *Column { c.Type = t return c } func (c *Column) SetSize(size int) *Column { c.Size = OptionalInt{Set: true, Value: size} return c } func (c *Column) String() (string, error) { var str strings.Builder str.WriteString(c.Name) str.WriteString(" ") typeStr, err := c.Type.Format(c.Dialect, c.Size) if err != nil { return "", err } str.WriteString(typeStr) if !c.Nullable { str.WriteString(" NOT NULL") } if c.Default.Set { str.WriteString(" DEFAULT ") val := c.Default.Value if val == "" { val = "''" } str.WriteString(val) } if c.PrimaryKey { str.WriteString(" PRIMARY KEY") } return str.String(), nil } func (b *CreateTableSqlBuilder) Column(column *Column) *CreateTableSqlBuilder { if b.Columns == nil { b.Columns = make(map[string]*Column) } b.Columns[column.Name] = column b.ColumnOrder = append(b.ColumnOrder, column.Name) return b } func (b *CreateTableSqlBuilder) UniqueConstraint(columns ...string) *CreateTableSqlBuilder { for _, column := range columns { if _, ok := b.Columns[column]; !ok { // This fails silently. return b } } b.Constraints = append(b.Constraints, fmt.Sprintf("UNIQUE(%s)", strings.Join(columns, ","))) return b } func (b *CreateTableSqlBuilder) SetIfNotExists(ine bool) *CreateTableSqlBuilder { b.IfNotExists = ine return b } func (b *CreateTableSqlBuilder) ToSQL() (string, error) { var str strings.Builder str.WriteString("CREATE TABLE ") if b.IfNotExists { str.WriteString("IF NOT EXISTS ") } str.WriteString(b.Name) var things []string for _, columnName := range b.ColumnOrder { column, ok := b.Columns[columnName] if !ok { return "", fmt.Errorf("column not found: %s", columnName) } columnStr, err := column.String() if err != nil { return "", err } things = append(things, columnStr) } things = append(things, b.Constraints...) if thingLen := len(things); thingLen > 0 { str.WriteString(" ( ") for i, thing := range things { str.WriteString(thing) if i < thingLen-1 { str.WriteString(", ") } } str.WriteString(" )") } return str.String(), nil } diff --git a/migrations/v16.go b/migrations/v16.go new file mode 100644 index 0000000..bf0a730 --- /dev/null +++ b/migrations/v16.go @@ -0,0 +1,50 @@ +/* + * Copyright © 2019-2024 Musing Studio 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 + +import ( + "context" + "database/sql" + + wf_db "github.com/writefreely/writefreely/db" +) + +func increasePostContentSize(db *datastore) error { + if db.driverName != driverMySQL { + // Only MySQL databases need this migration + return nil + } + + dialect := wf_db.DialectMySQL + return wf_db.RunTransactionWithOptions(context.Background(), db.DB, &sql.TxOptions{}, func(ctx context.Context, tx *sql.Tx) error { + builders := []wf_db.SQLBuilder{ + dialect.AlterTable("posts"). + ChangeColumn("content", + dialect.Column( + "column", + wf_db.ColumnTypeLongText, + wf_db.OptionalInt{ + Set: false, + Value: 0, + }).SetNullable(false)), + } + for _, builder := range builders { + query, err := builder.ToSQL() + if err != nil { + return err + } + if _, err := tx.ExecContext(ctx, query); err != nil { + return err + } + } + return nil + }) +} diff --git a/schema.sql b/schema.sql index b3fae97..c043532 100644 --- a/schema.sql +++ b/schema.sql @@ -1,241 +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, + `content` mediumtext 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;