code:
package cmd

import (
	"fmt"
	"os"
	fp "path/filepath"
	"time"

	"github.com/go-shiori/shiori/internal/config"
	"github.com/go-shiori/shiori/internal/database"
	"github.com/go-shiori/shiori/internal/domains"
	"github.com/go-shiori/shiori/internal/model"
	"github.com/sirupsen/logrus"
	"github.com/spf13/cobra"
	"golang.org/x/net/context"
)

// ShioriCmd returns the root command for shiori
func ShioriCmd() *cobra.Command {
	rootCmd := &cobra.Command{
		Use:   "shiori",
		Short: "Simple command-line bookmark manager built with Go",
	}

	rootCmd.PersistentFlags().Bool("portable", false, "run shiori in portable mode")
	rootCmd.PersistentFlags().String("storage-directory", "", "path to store shiori data")
	rootCmd.MarkFlagsMutuallyExclusive("portable", "storage-directory")

	rootCmd.PersistentFlags().String("log-level", logrus.InfoLevel.String(), "set logrus loglevel")
	rootCmd.PersistentFlags().Bool("log-caller", false, "logrus report caller or not")

	rootCmd.AddCommand(
		addCmd(),
		printCmd(),
		updateCmd(),
		deleteCmd(),
		openCmd(),
		importCmd(),
		exportCmd(),
		pocketCmd(),
		serveCmd(),
		checkCmd(),
		newVersionCommand(),
		newServerCommand(),
	)

	return rootCmd
}

func initShiori(ctx context.Context, cmd *cobra.Command) (*config.Config, *config.Dependencies) {
	logger := logrus.New()

	portableMode, _ := cmd.Flags().GetBool("portable")
	logLevel, _ := cmd.Flags().GetString("log-level")
	logCaller, _ := cmd.Flags().GetBool("log-caller")
	storageDirectory, _ := cmd.Flags().GetString("storage-directory")

	logger.SetReportCaller(logCaller)
	logger.SetFormatter(&logrus.TextFormatter{
		FullTimestamp:    true,
		TimestampFormat:  time.RFC3339,
		CallerPrettyfier: SFCallerPrettyfier,
	})

	if lvl, err := logrus.ParseLevel(logLevel); err != nil {
		logger.WithError(err).Panic("failed to set log level")
	} else {
		logger.SetLevel(lvl)
	}

	cfg := config.ParseServerConfiguration(ctx, logger)

	if storageDirectory != "" && cfg.Storage.DataDir != "" {
		logger.Warn("--storage-directory is set, overriding SHIORI_DIR.")
		cfg.Storage.DataDir = storageDirectory
	}

	cfg.SetDefaults(logger, portableMode)

	err := os.MkdirAll(cfg.Storage.DataDir, model.DataDirPerm)
	if err != nil {
		logger.WithError(err).Fatal("error creating data directory")
	}

	db, err := openDatabase(logger, ctx, cfg)
	if err != nil {
		logger.WithError(err).Fatal("error opening database")
	}

	// Migrate
	if err := db.Migrate(); err != nil {
		logger.WithError(err).Fatalf("Error running migration")
	}

	if cfg.Development {
		logger.Warn("Development mode is ENABLED, this will enable some helpers for local development, unsuitable for production environments")
	}

	dependencies := config.NewDependencies(logger, db, cfg)
	dependencies.Domains.Auth = domains.NewAccountsDomain(logger, cfg.Http.SecretKey, db)
	dependencies.Domains.Archiver = domains.NewArchiverDomain(logger, cfg.Storage.DataDir)

	// Workaround: Get accounts to make sure at least one is present in the database.
	// If there's no accounts in the database, create the shiori/gopher account the legacy api
	// hardcoded in the login handler.
	accounts, err := db.GetAccounts(cmd.Context(), database.GetAccountsOptions{})
	if err != nil {
		cError.Printf("Failed to get owner account: %v\n", err)
		os.Exit(1)
	}

	if len(accounts) == 0 {
		account := model.Account{
			Username: "shiori",
			Password: "gopher",
			Owner:    true,
		}

		if err := db.SaveAccount(cmd.Context(), account); err != nil {
			logger.WithError(err).Fatal("error ensuring owner account")
		}
	}

	return cfg, dependencies
}

func openDatabase(logger *logrus.Logger, ctx context.Context, cfg *config.Config) (database.DB, error) {
	if cfg.Database.URL != "" {
		return database.Connect(ctx, cfg.Database.URL)
	}

	if cfg.Database.DBMS != "" {
		logger.Warnf("The use of SHIORI_DBMS is deprecated and will be removed in the future. Please migrate to SHIORI_DATABASE_URL instead.")
	}

	// TODO remove this the moment DBMS is deprecated
	if cfg.Database.DBMS == "mysql" {
		return openMySQLDatabase(ctx)
	}
	if cfg.Database.DBMS == "postgresql" {
		return openPostgreSQLDatabase(ctx)
	}

	return database.OpenSQLiteDatabase(ctx, fp.Join(cfg.Storage.DataDir, "shiori.db"))
}

func openMySQLDatabase(ctx context.Context) (database.DB, error) {
	user, _ := os.LookupEnv("SHIORI_MYSQL_USER")
	password, _ := os.LookupEnv("SHIORI_MYSQL_PASS")
	dbName, _ := os.LookupEnv("SHIORI_MYSQL_NAME")
	dbAddress, _ := os.LookupEnv("SHIORI_MYSQL_ADDRESS")

	connString := fmt.Sprintf("%s:%s@%s/%s?charset=utf8mb4", user, password, dbAddress, dbName)
	return database.OpenMySQLDatabase(ctx, connString)
}

func openPostgreSQLDatabase(ctx context.Context) (database.DB, error) {
	host, _ := os.LookupEnv("SHIORI_PG_HOST")
	port, _ := os.LookupEnv("SHIORI_PG_PORT")
	user, _ := os.LookupEnv("SHIORI_PG_USER")
	password, _ := os.LookupEnv("SHIORI_PG_PASS")
	dbName, _ := os.LookupEnv("SHIORI_PG_NAME")
	sslmode, _ := os.LookupEnv("SHIORI_PG_SSLMODE")
	if sslmode == "" {
		sslmode = "disable"
	}

	connString := fmt.Sprintf("host=%s port=%s user=%s password=%s dbname=%s sslmode=%s",
		host, port, user, password, dbName, sslmode)
	return database.OpenPGDatabase(ctx, connString)
}


code:
package cmd

import (
	"github.com/spf13/cobra"
)

func serveCmd() *cobra.Command {
	cmd := &cobra.Command{
		Use:   "serve",
		Short: "Serve web interface for managing bookmarks",
		Long: "Run a simple and performant web server which " +
			"serves the site for managing bookmarks. If --port " +
			"flag is not used, it will use port 8080 by default.",
		Deprecated: "use server instead",
		Run:        newServerCommandHandler(),
	}

	cmd.Flags().IntP("port", "p", 8080, "Port used by the server")
	cmd.Flags().StringP("address", "a", "", "Address the server listens to")
	cmd.Flags().StringP("webroot", "r", "/", "Root path that used by server")
	cmd.Flags().Bool("log", true, "Print out a non-standard access log")

	return cmd
}

repo_name: orochi/shiori

license: mit


code:
package cmd

import (
	"context"
	"strings"

	"github.com/go-shiori/shiori/internal/http"
	"github.com/go-shiori/shiori/internal/model"
	"github.com/spf13/cobra"
)

func newServerCommand() *cobra.Command {
	cmd := &cobra.Command{
		Use:   "server",
		Short: "Starts the Shiori webserver",
		Long:  "Serves the Shiori web interface and API.",
		Run:   newServerCommandHandler(),
	}

	cmd.Flags().IntP("port", "p", 8080, "Port used by the server")
	cmd.Flags().StringP("address", "a", "", "Address the server listens to")
	cmd.Flags().StringP("webroot", "r", "/", "Root path that used by server")
	cmd.Flags().Bool("access-log", false, "Print out a non-standard access log")
	cmd.Flags().Bool("serve-web-ui", true, "Serve static files from the webroot path")
	cmd.Flags().String("secret-key", "", "Secret key used for encrypting session data")

	return cmd
}

func newServerCommandHandler() func(cmd *cobra.Command, args []string) {
	return func(cmd *cobra.Command, args []string) {
		ctx := context.Background()

		// Get flags values
		port, _ := cmd.Flags().GetInt("port")
		address, _ := cmd.Flags().GetString("address")
		rootPath, _ := cmd.Flags().GetString("webroot")
		accessLog, _ := cmd.Flags().GetBool("access-log")
		serveWebUI, _ := cmd.Flags().GetBool("serve-web-ui")
		secretKey, _ := cmd.Flags().GetString("secret-key")

		cfg, dependencies := initShiori(ctx, cmd)

		cfg.Http.SetDefaults(dependencies.Log)

		// Validate root path
		if rootPath == "" {
			rootPath = "/"
		}

		if !strings.HasPrefix(rootPath, "/") {
			rootPath = "/" + rootPath
		}

		if !strings.HasSuffix(rootPath, "/") {
			rootPath += "/"
		}

		// Override configuration from flags
		cfg.Http.Port = port
		cfg.Http.Address = address + ":"
		cfg.Http.RootPath = rootPath
		cfg.Http.AccessLog = accessLog
		cfg.Http.ServeWebUI = serveWebUI
		cfg.Http.SecretKey = secretKey

		dependencies.Log.Infof("Starting Shiori v%s", model.BuildVersion)

		server := http.NewHttpServer(dependencies.Log).Setup(cfg, dependencies)

		if err := server.Start(ctx); err != nil {
			dependencies.Log.WithError(err).Fatal("error starting server")
		}
		dependencies.Log.WithField("addr", address).Debug("started http server")

		server.WaitStop(ctx)
	}
}


code:
package cmd

import (
	"fmt"
	"os"
	"sort"
	"strings"
	"sync"

	"github.com/go-shiori/shiori/internal/core"
	"github.com/go-shiori/shiori/internal/database"
	"github.com/go-shiori/shiori/internal/model"
	"github.com/spf13/cobra"
)

func updateCmd() *cobra.Command {
	cmd := &cobra.Command{
		Use:   "update [indices]",
		Short: "Update the saved bookmarks",
		Long: "Update fields and archive of an existing bookmark. " +
			"Accepts space-separated list of indices (e.g. 5 6 23 4 110 45), " +
			"hyphenated range (e.g. 100-200) or both (e.g. 1-3 7 9). " +
			"If no arguments, ALL bookmarks will be updated. Update works differently depending on the flags:\n" +
			"- If indices are passed without any flags (--url, --title, --tag and --excerpt), read the URLs from database and update titles from web.\n" +
			"- If --url is passed (and --title is omitted), update the title from web using the URL. While using this flag, update only accept EXACTLY one index.\n" +
			"While updating bookmark's tags, you can use - to remove tag (e.g. -nature to remove nature tag from this bookmark).",
		Run: updateHandler,
	}

	cmd.Flags().StringP("url", "u", "", "New URL for this bookmark")
	cmd.Flags().StringP("title", "i", "", "New title for this bookmark")
	cmd.Flags().StringP("excerpt", "e", "", "New excerpt for this bookmark")
	cmd.Flags().StringSliceP("tags", "t", []string{}, "Comma-separated tags for this bookmark")
	cmd.Flags().BoolP("offline", "o", false, "Update bookmark without fetching data from internet")
	cmd.Flags().BoolP("yes", "y", false, "Skip confirmation prompt and update ALL bookmarks")
	cmd.Flags().Bool("keep-metadata", false, "Keep existing metadata. Useful when only want to update bookmark's content")
	cmd.Flags().BoolP("no-archival", "a", false, "Update bookmark without updating offline archive")
	cmd.Flags().Bool("log-archival", false, "Log the archival process")

	return cmd
}

func updateHandler(cmd *cobra.Command, args []string) {
	cfg, deps := initShiori(cmd.Context(), cmd)

	// Parse flags
	url, _ := cmd.Flags().GetString("url")
	title, _ := cmd.Flags().GetString("title")
	excerpt, _ := cmd.Flags().GetString("excerpt")
	tags, _ := cmd.Flags().GetStringSlice("tags")
	offline, _ := cmd.Flags().GetBool("offline")
	skipConfirm, _ := cmd.Flags().GetBool("yes")
	noArchival, _ := cmd.Flags().GetBool("no-archival")
	logArchival, _ := cmd.Flags().GetBool("log-archival")
	keep_metadata := cmd.Flags().Changed("keep-metadata")

	// If no arguments (i.e all bookmarks going to be updated), confirm to user
	if len(args) == 0 && !skipConfirm {
		confirmUpdate := ""
		fmt.Print("Update ALL bookmarks? (y/N): ")
		fmt.Scanln(&confirmUpdate)

		if confirmUpdate != "y" {
			fmt.Println("No bookmarks updated")
			return
		}
	}

	// Convert args to ids
	ids, err := parseStrIndices(args)
	if err != nil {
		cError.Printf("Failed to parse args: %v\n", err)
		os.Exit(1)
	}

	// Clean up new parameter from flags
	title = validateTitle(title, "")
	excerpt = normalizeSpace(excerpt)

	if cmd.Flags().Changed("url") {
		// Clean up bookmark URL
		url, err = core.RemoveUTMParams(url)
		if err != nil {
			panic(fmt.Errorf("failed to clean URL: %v", err))
		}

		// Since user uses custom URL, make sure there is only one ID to update
		if len(ids) != 1 {
			cError.Println("Update only accepts one index while using --url flag")
			os.Exit(1)
		}
	}

	// Fetch bookmarks from database
	filterOptions := database.GetBookmarksOptions{
		IDs: ids,
	}

	bookmarks, err := deps.Database.GetBookmarks(cmd.Context(), filterOptions)
	if err != nil {
		cError.Printf("Failed to get bookmarks: %v\n", err)
		os.Exit(1)
	}

	if len(bookmarks) == 0 {
		cError.Println("No matching index found")
		os.Exit(1)
	}

	// Check if user really want to batch update archive
	if nBook := len(bookmarks); nBook > 5 && !offline && !noArchival && !skipConfirm {
		fmt.Printf("This update will generate offline archive for %d bookmark(s).\n", nBook)
		fmt.Println("This might take a long time and uses lot of your network bandwidth.")

		confirmUpdate := ""
		fmt.Printf("Continue update and archival process ? (y/N): ")
		fmt.Scanln(&confirmUpdate)

		if confirmUpdate != "y" {
			fmt.Println("No bookmarks updated")
			return

		}
	}

	// If it's not offline mode, fetch data from internet
	idWithProblems := []int{}

	if !offline {
		mx := sync.RWMutex{}
		wg := sync.WaitGroup{}
		chDone := make(chan struct{})
		chProblem := make(chan int, 10)
		chMessage := make(chan interface{}, 10)
		semaphore := make(chan struct{}, 10)

		cInfo.Println("Downloading article(s)...")

		for i, book := range bookmarks {
			wg.Add(1)

			// Mark whether book will be archived
			book.CreateArchive = !noArchival

			// If used, use submitted URL
			if url != "" {
				book.URL = url
			}

			go func(i int, book model.Bookmark) {
				// Make sure to finish the WG
				defer wg.Done()

				// Register goroutine to semaphore
				semaphore <- struct{}{}
				defer func() {
					<-semaphore
				}()

				// Download data from internet
				content, contentType, err := core.DownloadBookmark(book.URL)
				if err != nil {
					chProblem <- book.ID
					chMessage <- fmt.Errorf("failed to download %s: %v", book.URL, err)
					return
				}

				request := core.ProcessRequest{
					DataDir:     cfg.Storage.DataDir,
					Bookmark:    book,
					Content:     content,
					ContentType: contentType,
					KeepTitle:   keep_metadata,
					KeepExcerpt: keep_metadata,
					LogArchival: logArchival,
				}

				book, _, err = core.ProcessBookmark(request)
				content.Close()

				if err != nil {
					chProblem <- book.ID
					chMessage <- fmt.Errorf("failed to process %s: %v", book.URL, err)
					return
				}

				// Send success message
				chMessage <- fmt.Sprintf("Downloaded %s", book.URL)

				// Save parse result to bookmark
				mx.Lock()
				bookmarks[i] = book
				mx.Unlock()
			}(i, book)
		}

		// Print log message
		go func(nBookmark int) {
			logIndex := 0

			for {
				select {
				case <-chDone:
					cInfo.Println("Download finished")
					return
				case id := <-chProblem:
					idWithProblems = append(idWithProblems, id)
				case msg := <-chMessage:
					logIndex++

					switch msg.(type) {
					case error:
						cError.Printf("[%d/%d] %v\n", logIndex, nBookmark, msg)
					case string:
						cInfo.Printf("[%d/%d] %s\n", logIndex, nBookmark, msg)
					}
				}
			}
		}(len(bookmarks))

		// Wait until all download finished
		wg.Wait()
		close(chDone)
	}

	// Map which tags is new or deleted from flag --tags
	addedTags := make(map[string]struct{})
	deletedTags := make(map[string]struct{})
	for _, tag := range tags {
		tagName := strings.ToLower(tag)
		tagName = strings.TrimSpace(tagName)

		if strings.HasPrefix(tagName, "-") {
			tagName = strings.TrimPrefix(tagName, "-")
			deletedTags[tagName] = struct{}{}
		} else {
			addedTags[tagName] = struct{}{}
		}
	}

	// Attach user submitted value to the bookmarks
	for i, book := range bookmarks {
		// If user submit his own title or excerpt, use it
		if title != "" {
			book.Title = title
		}

		if excerpt != "" {
			book.Excerpt = excerpt
		}

		// If user submits url, use it
		if url != "" {
			book.URL = url
		}

		// Make sure title is valid and not empty
		book.Title = validateTitle(book.Title, book.URL)

		// Generate new tags
		tmpAddedTags := make(map[string]struct{})
		for key, value := range addedTags {
			tmpAddedTags[key] = value
		}

		newTags := []model.Tag{}
		for _, tag := range book.Tags {
			if _, isDeleted := deletedTags[tag.Name]; isDeleted {
				tag.Deleted = true
			}

			if _, alreadyExist := addedTags[tag.Name]; alreadyExist {
				delete(tmpAddedTags, tag.Name)
			}

			newTags = append(newTags, tag)
		}

		for tag := range tmpAddedTags {
			newTags = append(newTags, model.Tag{Name: tag})
		}

		book.Tags = newTags

		// Set bookmark's new data
		bookmarks[i] = book
	}

	// Save bookmarks to database
	bookmarks, err = deps.Database.SaveBookmarks(cmd.Context(), false, bookmarks...)
	if err != nil {
		cError.Printf("Failed to save bookmark: %v\n", err)
		os.Exit(1)
	}

	// Print updated bookmarks
	fmt.Println()
	printBookmarks(bookmarks...)

	var code int
	if len(idWithProblems) > 0 {
		code = 1
		sort.Ints(idWithProblems)

		cError.Println("Encountered error while downloading some bookmark(s):")
		for _, id := range idWithProblems {
			cError.Printf("%d ", id)
		}
		fmt.Println()
	}
	os.Exit(code)
}


code:
package cmd

import (
	"errors"
	"fmt"
	nurl "net/url"
	"os"
	"os/exec"
	"path"
	"runtime"
	"strconv"
	"strings"
	"unicode/utf8"

	"github.com/fatih/color"
	"github.com/go-shiori/shiori/internal/model"
	"golang.org/x/term"
)

var (
	cIndex   = color.New(color.FgHiCyan)
	cSymbol  = color.New(color.FgHiMagenta)
	cTitle   = color.New(color.FgHiGreen).Add(color.Bold)
	cURL     = color.New(color.FgHiYellow)
	cExcerpt = color.New(color.FgHiWhite)
	cTag     = color.New(color.FgHiBlue)

	cInfo  = color.New(color.FgHiCyan)
	cError = color.New(color.FgHiRed)

	errInvalidIndex = errors.New("index is not valid")
)

func normalizeSpace(str string) string {
	str = strings.TrimSpace(str)
	return strings.Join(strings.Fields(str), " ")
}

func isURLValid(s string) bool {
	tmp, err := nurl.Parse(s)
	return err == nil && tmp.Scheme != "" && tmp.Hostname() != ""
}

func printBookmarks(bookmarks ...model.Bookmark) {
	for _, bookmark := range bookmarks {
		// Create bookmark index
		strBookmarkIndex := fmt.Sprintf("%d. ", bookmark.ID)
		strSpace := strings.Repeat(" ", len(strBookmarkIndex))

		// Print bookmark title
		cIndex.Print(strBookmarkIndex)
		cTitle.Println(bookmark.Title)

		// Print bookmark URL
		cSymbol.Print(strSpace + "> ")
		cURL.Println(bookmark.URL)

		// Print bookmark excerpt
		if bookmark.Excerpt != "" {
			cSymbol.Print(strSpace + "+ ")
			cExcerpt.Println(bookmark.Excerpt)
		}

		// Print bookmark tags
		if len(bookmark.Tags) > 0 {
			cSymbol.Print(strSpace + "# ")
			for i, tag := range bookmark.Tags {
				if i == len(bookmark.Tags)-1 {
					cTag.Println(tag.Name)
				} else {
					cTag.Print(tag.Name + ", ")
				}
			}
		}

		// Append new line
		fmt.Println()
	}
}

// parseStrIndices converts a list of indices to their integer values
func parseStrIndices(indices []string) ([]int, error) {
	var listIndex []int
	for _, strIndex := range indices {
		if !strings.Contains(strIndex, "-") {
			index, err := strconv.Atoi(strIndex)
			if err != nil || index < 1 {
				return nil, errInvalidIndex
			}

			listIndex = append(listIndex, index)
			continue
		}

		parts := strings.Split(strIndex, "-")
		if len(parts) != 2 {
			return nil, errInvalidIndex
		}

		minIndex, errMin := strconv.Atoi(parts[0])
		maxIndex, errMax := strconv.Atoi(parts[1])
		if errMin != nil || errMax != nil || minIndex < 1 || minIndex > maxIndex {
			return nil, errInvalidIndex
		}

		for i := minIndex; i <= maxIndex; i++ {
			listIndex = append(listIndex, i)
		}
	}

	return listIndex, nil
}

// openBrowser tries to open the URL in a browser,
// and returns any error if it happened.
func openBrowser(url string) error {
	var args []string
	switch runtime.GOOS {
	case "darwin":
		args = []string{"open"}
	case "windows":
		args = []string{"cmd", "/c", "start"}
	default:
		args = []string{"xdg-open"}
	}

	cmd := exec.Command(args[0], append(args[1:], url)...)
	return cmd.Run()
}

func getTerminalWidth() int {
	width, _, _ := term.GetSize(int(os.Stdin.Fd()))
	return width
}

func validateTitle(title, fallback string) string {
	// Normalize spaces before we begin
	title = normalizeSpace(title)
	title = strings.TrimSpace(title)

	// If at this point title already empty, just uses fallback
	if title == "" {
		return fallback
	}

	// Check if it's already valid UTF-8 string
	if valid := utf8.ValidString(title); valid {
		return title
	}

	// Remove invalid runes to get the valid UTF-8 title
	fixUtf := func(r rune) rune {
		if r == utf8.RuneError {
			return -1
		}
		return r
	}
	validUtf := strings.Map(fixUtf, title)

	// If it's empty use fallback string
	validUtf = strings.TrimSpace(validUtf)
	if validUtf == "" {
		return fallback
	}

	return validUtf
}

func SFCallerPrettyfier(frame *runtime.Frame) (string, string) {
	return "", fmt.Sprintf("%s:%d", path.Base(frame.File), frame.Line)
}


code:
package cmd

import (
	"reflect"
	"testing"
)

func Test_normalizeSpace(t *testing.T) {
	tests := []struct {
		name string
		args string
		want string
	}{{
		name: "normal sentence",
		args: "What a perfect, beautiful sentence",
		want: "What a perfect, beautiful sentence",
	}, {
		name: "has unnecessary space before and after sentence",
		args: "    I'm surrounded with spaces    ",
		want: "I'm surrounded with spaces",
	}, {
		name: "has unnecessary spaces in middle of sentence",
		args: "I'm hollow         inside",
		want: "I'm hollow inside",
	}, {
		name: "has unnecessary new line in middle of sentence",
		args: "I'm broken \n\n\ninside",
		want: "I'm broken inside",
	}, {
		name: "has unnecessary new line and spaces everywhere",
		args: "    I'm hollow     broken\n\n\n\nand surrounded by spaces    ",
		want: "I'm hollow broken and surrounded by spaces",
	}}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			if got := normalizeSpace(tt.args); got != tt.want {
				t.Errorf("normalizeSpace() = %v, want %v", got, tt.want)
			}
		})
	}
}

func Test_isURLValid(t *testing.T) {
	tests := []struct {
		name string
		args string
		want bool
	}{{
		name: "valid URL",
		args: "https://www.google.com",
		want: true,
	}, {
		name: "valid localhost URL",
		args: "http://localhost:8080",
		want: true,
	}, {
		name: "valid non-HTTP URL",
		args: "ftp://www.example.com/storage",
		want: true,
	}, {
		name: "invalid URL",
		args: "https:/www.google.com",
		want: false,
	}, {
		name: "hash URL",
		args: "#some-awesome-heading",
		want: false,
	}, {
		name: "relative URL",
		args: "/page/contact",
		want: false,
	}}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			if got := isURLValid(tt.args); got != tt.want {
				t.Errorf("isURLValid() = %v, want %v", got, tt.want)
			}
		})
	}
}

func Test_parseStrIndices(t *testing.T) {
	tests := []struct {
		name    string
		args    []string
		want    []int
		wantErr bool
	}{{
		name:    "single number",
		args:    []string{"1"},
		want:    []int{1},
		wantErr: false,
	}, {
		name:    "multiple number",
		args:    []string{"1", "2", "3"},
		want:    []int{1, 2, 3},
		wantErr: false,
	}, {
		name:    "single ranged number",
		args:    []string{"1-5"},
		want:    []int{1, 2, 3, 4, 5},
		wantErr: false,
	}, {
		name:    "multiple ranged number",
		args:    []string{"1-5", "8-9"},
		want:    []int{1, 2, 3, 4, 5, 8, 9},
		wantErr: false,
	}, {
		name:    "mixed single and ranged number",
		args:    []string{"1-5", "8-9", "11", "12"},
		want:    []int{1, 2, 3, 4, 5, 8, 9, 11, 12},
		wantErr: false,
	}, {
		name:    "invalid number",
		args:    []string{"AAA"},
		want:    nil,
		wantErr: true,
	}, {
		name:    "mixed number and string",
		args:    []string{"1", "2", "A"},
		want:    nil,
		wantErr: true,
	}, {
		name:    "reversed ranged number",
		args:    []string{"5-1"},
		want:    nil,
		wantErr: true,
	}}

	for _, tt := range tests {
		t.Run(tt.name, func(t *testing.T) {
			got, err := parseStrIndices(tt.args)
			if (err != nil) != tt.wantErr {
				t.Errorf("parseStrIndices() error = %v, wantErr %v", err, tt.wantErr)
				return
			}

			if !reflect.DeepEqual(got, tt.want) {
				t.Errorf("parseStrIndices() = %v, want %v", got, tt.want)
			}
		})
	}
}


code:
package cmd

import (
	"github.com/go-shiori/shiori/internal/model"
	"github.com/spf13/cobra"
)

func newVersionCommand() *cobra.Command {
	cmd := &cobra.Command{
		Use:   "version",
		Short: "Output the shiori version",
		Run:   newVersionCommandHandler(),
	}

	return cmd
}

func newVersionCommandHandler() func(cmd *cobra.Command, args []string) {
	return func(cmd *cobra.Command, args []string) {
		cmd.Printf("Shiori version %s (build %s) at %s\n", model.BuildVersion, model.BuildCommit, model.BuildDate)
	}
}


code:
package config

import (
	"bufio"
	"context"
	"fmt"
	"os"
	"path/filepath"
	"strings"
	"time"

	"github.com/gofrs/uuid/v5"
	"github.com/sethvargo/go-envconfig"
	"github.com/sirupsen/logrus"
)

// readDotEnv reads the configuration from variables in a .env file (only for contributing)
func readDotEnv(logger *logrus.Logger) map[string]string {
	file, err := os.Open(".env")
	if err != nil {
		return nil
	}
	defer file.Close()

	result := make(map[string]string)

	scanner := bufio.NewScanner(file)

	for scanner.Scan() {
		line := scanner.Text()
		if strings.HasPrefix(line, "#") {
			continue
		}

		keyval := strings.SplitN(line, "=", 2)
		result[keyval[0]] = keyval[1]
	}

	if err := scanner.Err(); err != nil {
		logger.WithError(err).Fatal("error reading dotenv")
	}

	return result
}

type HttpConfig struct {
	Enabled    bool   `env:"HTTP_ENABLED,default=True"`
	Port       int    `env:"HTTP_PORT,default=8080"`
	Address    string `env:"HTTP_ADDRESS,default=:"`
	RootPath   string `env:"HTTP_ROOT_PATH,default=/"`
	AccessLog  bool   `env:"HTTP_ACCESS_LOG,default=True"`
	ServeWebUI bool   `env:"HTTP_SERVE_WEB_UI,default=True"`
	SecretKey  string `env:"HTTP_SECRET_KEY"`
	// Fiber Specific
	BodyLimit                    int           `env:"HTTP_BODY_LIMIT,default=1024"`
	ReadTimeout                  time.Duration `env:"HTTP_READ_TIMEOUT,default=10s"`
	WriteTimeout                 time.Duration `env:"HTTP_WRITE_TIMEOUT,default=10s"`
	IDLETimeout                  time.Duration `env:"HTTP_IDLE_TIMEOUT,default=10s"`
	DisableKeepAlive             bool          `env:"HTTP_DISABLE_KEEP_ALIVE,default=true"`
	DisablePreParseMultipartForm bool          `env:"HTTP_DISABLE_PARSE_MULTIPART_FORM,default=true"`
}

type DatabaseConfig struct {
	DBMS string `env:"DBMS"` // Deprecated
	// DBMS requires more environment variables. Check the database package for more information.
	URL string `env:"DATABASE_URL"`
}

type StorageConfig struct {
	DataDir string `env:"DIR"` // Using DIR to be backwards compatible with the old config
}

type Config struct {
	Hostname    string `env:"HOSTNAME,required"`
	Development bool   `env:"DEVELOPMENT,default=False"`
	Database    *DatabaseConfig
	Storage     *StorageConfig
	// LogLevel string `env:"LOG_LEVEL,default=info"`
	Http *HttpConfig
}

// SetDefaults sets the default values for the configuration
func (c *HttpConfig) SetDefaults(logger *logrus.Logger) {
	// Set a random secret key if not set
	if c.SecretKey == "" {
		logger.Warn("SHIORI_HTTP_SECRET_KEY is not set, using random value. This means that all sessions will be invalidated on server restart.")
		randomUUID, err := uuid.NewV4()
		if err != nil {
			logger.WithError(err).Fatal("couldn't generate a random UUID")
		}
		c.SecretKey = randomUUID.String()
	}
}

// SetDefaults sets the default values for the configuration
func (c Config) SetDefaults(logger *logrus.Logger, portableMode bool) {
	// Set the default storage directory if not set, setting also the database url for
	// sqlite3 if that engine is used
	if c.Storage.DataDir == "" {
		var err error
		c.Storage.DataDir, err = getStorageDirectory(portableMode)
		if err != nil {
			logger.WithError(err).Fatal("couldn't determine the data directory")
		}
	}

	// Set default database url if not set
	if c.Database.DBMS == "" && c.Database.URL == "" {
		c.Database.URL = fmt.Sprintf("sqlite:///%s", filepath.Join(c.Storage.DataDir, "shiori.db"))
	}
}

func ParseServerConfiguration(ctx context.Context, logger *logrus.Logger) *Config {
	var cfg Config

	lookuper := envconfig.MultiLookuper(
		envconfig.MapLookuper(map[string]string{"HOSTNAME": os.Getenv("HOSTNAME")}),
		envconfig.MapLookuper(readDotEnv(logger)),
		envconfig.PrefixLookuper("SHIORI_", envconfig.OsLookuper()),
		envconfig.OsLookuper(),
	)
	if err := envconfig.ProcessWith(ctx, &cfg, lookuper); err != nil {
		logger.WithError(err).Fatal("Error parsing configuration")
	}

	return &cfg
}


code:
package config

import (
	"github.com/go-shiori/shiori/internal/database"
	"github.com/go-shiori/shiori/internal/domains"
	"github.com/sirupsen/logrus"
)

type Dependencies struct {
	Log      *logrus.Logger
	Database database.DB
	Config   *Config
	Domains  struct {
		Auth     domains.AccountsDomain
		Archiver domains.ArchiverDomain
	}
}

func NewDependencies(log *logrus.Logger, db database.DB, cfg *Config) *Dependencies {
	return &Dependencies{
		Log:      log,
		Config:   cfg,
		Database: db,
	}
}


code:
package config

import (
	"fmt"
	"os"
	"path/filepath"

	gap "github.com/muesli/go-app-paths"
)

func getStorageDirectory(portableMode bool) (string, error) {
	// If in portable mode, uses directory of executable
	if portableMode {
		exePath, err := os.Executable()
		if err != nil {
			return "", err
		}

		exeDir := filepath.Dir(exePath)
		return filepath.Join(exeDir, "shiori-data"), nil
	}

	// Try to use platform specific app path
	userScope := gap.NewScope(gap.User, "shiori")
	dataDir, err := userScope.DataPath("")
	if err == nil {
		return dataDir, nil
	}

	return "", fmt.Errorf("couldn't determine the data directory")
}


code:
package core

const userAgent = "Shiori/2.0.0 (+https://github.com/go-shiori/shiori)"


code:
package core

import (
	"io"
	"net/http"
	"time"
)

var httpClient = &http.Client{Timeout: time.Minute}

// DownloadBookmark downloads bookmarked page from specified URL.
// Return response body, make sure to close it later.
func DownloadBookmark(url string) (io.ReadCloser, string, error) {
	// Prepare download request
	req, err := http.NewRequest("GET", url, nil)
	if err != nil {
		return nil, "", err
	}

	// Send download request
	req.Header.Set("User-Agent", userAgent)
	resp, err := httpClient.Do(req)
	if err != nil {
		return nil, "", err
	}

	// Get content type
	contentType := resp.Header.Get("Content-Type")

	return resp.Body, contentType, nil
}


code:
package core

import (
	"fmt"
	"os"
	fp "path/filepath"
	"strconv"
	"strings"

	epub "github.com/go-shiori/go-epub"
	"github.com/go-shiori/shiori/internal/model"
	"github.com/pkg/errors"
)

// GenerateEbook receives a `ProcessRequest` and generates an ebook file in the destination path specified.
// The destination path `dstPath` should include file name with ".epub" extension
// The bookmark model will be used to update the UI based on whether this function is successful or not.
func GenerateEbook(req ProcessRequest, dstPath string) (book model.Bookmark, err error) {

	book = req.Bookmark

	// Make sure bookmark ID is defined
	if book.ID == 0 {
		return book, errors.New("bookmark ID is not valid")
	}

	// Get current state of bookmark cheak archive and thumb
	strID := strconv.Itoa(book.ID)

	imagePath := fp.Join(req.DataDir, "thumb", fmt.Sprintf("%d", book.ID))
	archivePath := fp.Join(req.DataDir, "archive", fmt.Sprintf("%d", book.ID))

	if _, err := os.Stat(imagePath); err == nil {
		book.ImageURL = fp.Join("/", "bookmark", strID, "thumb")
	}

	if _, err := os.Stat(archivePath); err == nil {
		book.HasArchive = true
	}

	// This function create ebook from reader mode of bookmark so
	// we can't create ebook from PDF so we return error here if bookmark is a pdf
	contentType := req.ContentType
	if strings.Contains(contentType, "application/pdf") {
		return book, errors.New("can't create ebook for pdf")
	}

	// Create temporary epub file
	tmpFile, err := os.CreateTemp("", "ebook")
	if err != nil {
		return book, errors.Wrap(err, "can't create temporary EPUB file")
	}
	defer os.Remove(tmpFile.Name())

	// Create last line of ebook
	lastline := `<hr/><p style="text-align:center">Generated By <a href="https://github.com/go-shiori/shiori">Shiori</a> From <a href="` + book.URL + `">This Page</a></p>`

	// Create ebook
	ebook, err := epub.NewEpub(book.Title)
	if err != nil {
		return book, errors.Wrap(err, "can't create EPUB")
	}

	ebook.SetTitle(book.Title)
	ebook.SetAuthor(book.Author)
	ebook.SetDescription(book.Excerpt)
	_, err = ebook.AddSection(`<h1 style="text-align:center"> `+book.Title+` </h1>`+book.HTML+lastline, book.Title, "", "")
	if err != nil {
		return book, errors.Wrap(err, "can't add ebook Section")
	}
	ebook.EmbedImages()
	err = ebook.Write(tmpFile.Name())
	if err != nil {
		return book, errors.Wrap(err, "can't create ebook file")
	}

	defer tmpFile.Close()

	// If everything go well we move ebook to dstPath
	err = MoveFileToDestination(dstPath, tmpFile)
	if err != nil {
		return book, errors.Wrap(err, "failed move ebook to destination")
	}

	book.HasEbook = true
	return book, nil
}


code:
package core_test

import (
	"fmt"
	"os"
	fp "path/filepath"
	"testing"

	"github.com/go-shiori/shiori/internal/core"
	"github.com/go-shiori/shiori/internal/model"
	"github.com/stretchr/testify/assert"
)

func TestGenerateEbook(t *testing.T) {
	t.Run("Successful ebook generate", func(t *testing.T) {
		t.Run("valid bookmarkId that return HasEbook true", func(t *testing.T) {
			// test cae
			tempDir := t.TempDir()
			dstDir := t.TempDir()

			mockRequest := core.ProcessRequest{
				Bookmark: model.Bookmark{
					ID:       1,
					Title:    "Example Bookmark",
					HTML:     "Example HTML",
					HasEbook: false,
				},
				DataDir:     dstDir,
				ContentType: "text/html",
			}

			bookmark, err := core.GenerateEbook(mockRequest, fp.Join(tempDir, "1"))

			assert.True(t, bookmark.HasEbook)
			assert.NoError(t, err)
		})
		t.Run("ebook generate with valid BookmarkID EbookExist ImagePathExist ReturnWithHasEbookTrue", func(t *testing.T) {
			tempDir := t.TempDir()
			dstDir := t.TempDir()

			mockRequest := core.ProcessRequest{
				Bookmark: model.Bookmark{
					ID:       1,
					HasEbook: false,
				},
				DataDir:     dstDir,
				ContentType: "text/html",
			}
			// Create the image directory
			imageDir := fp.Join(mockRequest.DataDir, "thumb")
			err := os.MkdirAll(imageDir, os.ModePerm)
			if err != nil {
				t.Fatal(err)
			}
			// Create the image file
			imagePath := fp.Join(mockRequest.DataDir, "thumb", fmt.Sprintf("%d", mockRequest.Bookmark.ID))
			file, err := os.Create(imagePath)
			if err != nil {
				t.Fatal(err)
			}
			defer file.Close()

			bookmark, err := core.GenerateEbook(mockRequest, fp.Join(tempDir, "1"))
			expectedimagePath := "/bookmark/1/thumb"
			if expectedimagePath != bookmark.ImageURL {
				t.Errorf("Expected imageURL %s, but got %s", bookmark.ImageURL, expectedimagePath)
			}
			assert.True(t, bookmark.HasEbook)
			assert.NoError(t, err)
		})
		t.Run("generate ebook valid BookmarkID EbookExist Returnh HasArchive True", func(t *testing.T) {

			tempDir := t.TempDir()
			dstDir := t.TempDir()

			mockRequest := core.ProcessRequest{
				Bookmark: model.Bookmark{
					ID:       1,
					HasEbook: false,
				},
				DataDir:     dstDir,
				ContentType: "text/html",
			}
			// Create the archive directory
			archiveDir := fp.Join(mockRequest.DataDir, "archive")
			err := os.MkdirAll(archiveDir, os.ModePerm)
			if err != nil {
				t.Fatal(err)
			}
			// Create the archive file
			archivePath := fp.Join(mockRequest.DataDir, "archive", fmt.Sprintf("%d", mockRequest.Bookmark.ID))
			file, err := os.Create(archivePath)
			if err != nil {
				t.Fatal(err)
			}
			defer file.Close()

			bookmark, err := core.GenerateEbook(mockRequest, fp.Join(tempDir, "1"))
			assert.True(t, bookmark.HasArchive)
			assert.NoError(t, err)
		})
	})
	t.Run("specific ebook generate case", func(t *testing.T) {
		t.Run("invalid bookmarkId that return Error", func(t *testing.T) {
			tempDir := t.TempDir()
			mockRequest := core.ProcessRequest{
				Bookmark: model.Bookmark{
					ID:       0,
					HasEbook: false,
				},
				DataDir:     tempDir,
				ContentType: "text/html",
			}

			bookmark, err := core.GenerateEbook(mockRequest, tempDir)

			assert.Equal(t, model.Bookmark{
				ID:       0,
				HasEbook: false,
			}, bookmark)
			assert.Error(t, err)
		})
		t.Run("ebook exist return HasEbook true", func(t *testing.T) {
			tempDir := t.TempDir()
			dstDir := t.TempDir()

			mockRequest := core.ProcessRequest{
				Bookmark: model.Bookmark{
					ID:       1,
					HasEbook: false,
				},
				DataDir:     dstDir,
				ContentType: "text/html",
			}
			// Create the ebook directory
			ebookDir := fp.Join(mockRequest.DataDir, "ebook")
			err := os.MkdirAll(ebookDir, os.ModePerm)
			if err != nil {
				t.Fatal(err)
			}
			// Create the ebook file
			ebookfile := fp.Join(mockRequest.DataDir, "ebook", fmt.Sprintf("%d.epub", mockRequest.Bookmark.ID))
			file, err := os.Create(ebookfile)
			if err != nil {
				t.Fatal(err)
			}
			defer file.Close()

			bookmark, err := core.GenerateEbook(mockRequest, fp.Join(tempDir, "1"))

			assert.True(t, bookmark.HasEbook)
			assert.NoError(t, err)
		})
		t.Run("generate ebook valid BookmarkID RetuenError for PDF file", func(t *testing.T) {
			tempDir := t.TempDir()

			mockRequest := core.ProcessRequest{
				Bookmark: model.Bookmark{
					ID:       1,
					HasEbook: false,
				},
				DataDir:     tempDir,
				ContentType: "application/pdf",
			}

			bookmark, err := core.GenerateEbook(mockRequest, tempDir)

			assert.False(t, bookmark.HasEbook)
			assert.Error(t, err)
			assert.Contains(t, err.Error(), "can't create ebook for pdf")
		})
	})
}


code:
package core

import (
	"bytes"
	"fmt"
	"image"
	"image/color"
	"image/draw"
	"image/jpeg"
	"io"
	"log"
	"math"
	"net/url"
	"os"
	fp "path/filepath"
	"strconv"
	"strings"

	"github.com/disintegration/imaging"
	"github.com/go-shiori/go-readability"
	"github.com/go-shiori/shiori/internal/model"
	"github.com/go-shiori/warc"
	"github.com/pkg/errors"
	_ "golang.org/x/image/webp"

	// Add support for png
	_ "image/png"
)

// ProcessRequest is the request for processing bookmark.
type ProcessRequest struct {
	DataDir     string
	Bookmark    model.Bookmark
	Content     io.Reader
	ContentType string
	KeepTitle   bool
	KeepExcerpt bool
	LogArchival bool
}

var ErrNoSupportedImageType = errors.New("unsupported image type")

// ProcessBookmark process the bookmark and archive it if needed.
// Return three values, is error fatal, and error value.
func ProcessBookmark(req ProcessRequest) (book model.Bookmark, isFatalErr bool, err error) {
	book = req.Bookmark
	contentType := req.ContentType

	// Make sure bookmark ID is defined
	if book.ID == 0 {
		return book, true, fmt.Errorf("bookmark ID is not valid")
	}

	// Split bookmark content so it can be processed several times
	archivalInput := bytes.NewBuffer(nil)
	readabilityInput := bytes.NewBuffer(nil)
	readabilityCheckInput := bytes.NewBuffer(nil)

	var multiWriter io.Writer
	if !strings.Contains(contentType, "text/html") {
		multiWriter = io.MultiWriter(archivalInput)
	} else {
		multiWriter = io.MultiWriter(archivalInput, readabilityInput, readabilityCheckInput)
	}

	_, err = io.Copy(multiWriter, req.Content)
	if err != nil {
		return book, false, fmt.Errorf("failed to process article: %v", err)
	}

	// If this is HTML, parse for readable content
	strID := strconv.Itoa(book.ID)
	imgPath := fp.Join(req.DataDir, "thumb", strID)
	var imageURLs []string
	if strings.Contains(contentType, "text/html") {
		isReadable := readability.Check(readabilityCheckInput)

		nurl, err := url.Parse(book.URL)
		if err != nil {
			return book, true, fmt.Errorf("failed to parse url: %v", err)
		}

		article, err := readability.FromReader(readabilityInput, nurl)
		if err != nil {
			return book, false, fmt.Errorf("failed to parse article: %v", err)
		}

		book.Author = article.Byline
		book.Content = article.TextContent
		book.HTML = article.Content

		// If title and excerpt doesnt have submitted value, use from article
		if !req.KeepTitle || book.Title == "" {
			book.Title = article.Title
		}

		if !req.KeepExcerpt || book.Excerpt == "" {
			book.Excerpt = article.Excerpt
		}

		// Sometimes article doesn't have any title, so make sure it is not empty
		if book.Title == "" {
			book.Title = book.URL
		}

		// Get image URL
		if article.Image != "" {
			imageURLs = append(imageURLs, article.Image)
		} else {
			os.Remove(imgPath)
		}

		if article.Favicon != "" {
			imageURLs = append(imageURLs, article.Favicon)
		}

		if !isReadable {
			book.Content = ""
		}

		book.HasContent = book.Content != ""
	}

	// Save article image to local disk
	for i, imageURL := range imageURLs {
		err = DownloadBookImage(imageURL, imgPath)
		if err != nil && errors.Is(err, ErrNoSupportedImageType) {
			log.Printf("%s: %s", err, imageURL)
			if i == len(imageURLs)-1 {
				os.Remove(imgPath)
			}
		}
		if err != nil {
			log.Printf("File download not successful for image URL: %s", imageURL)
			continue
		}
		if err == nil {
			book.ImageURL = fp.Join("/", "bookmark", strID, "thumb")
			break
		}
	}

	// If needed, create ebook as well
	if book.CreateEbook {
		ebookPath := fp.Join(req.DataDir, "ebook", strID+".epub")
		req.Bookmark = book

		if strings.Contains(contentType, "application/pdf") {
			return book, false, errors.Wrap(err, "can't create ebook from pdf")
		} else {
			_, err = GenerateEbook(req, ebookPath)
			if err != nil {
				return book, true, errors.Wrap(err, "failed to create ebook")
			}
			book.HasEbook = true
		}
	}

	// If needed, create offline archive as well
	if book.CreateArchive {
		tmpFile, err := os.CreateTemp("", "archive")
		if err != nil {
			return book, false, fmt.Errorf("failed to create temp archive: %v", err)
		}
		defer os.Remove(tmpFile.Name())

		archivalRequest := warc.ArchivalRequest{
			URL:         book.URL,
			Reader:      archivalInput,
			ContentType: contentType,
			UserAgent:   userAgent,
			LogEnabled:  req.LogArchival,
		}

		err = warc.NewArchive(archivalRequest, tmpFile.Name())
		if err != nil {
			defer os.Remove(tmpFile.Name())
			return book, false, fmt.Errorf("failed to create archive: %v", err)
		}

		// Prepare destination file.
		dstPath := fp.Join(req.DataDir, "archive", fmt.Sprintf("%d", book.ID))

		err = MoveFileToDestination(dstPath, tmpFile)
		if err != nil {
			return book, false, fmt.Errorf("failed move archive to destination `: %v", err)
		}

		book.HasArchive = true
	}

	return book, false, nil
}

func DownloadBookImage(url, dstPath string) error {
	// Fetch data from URL
	resp, err := httpClient.Get(url)
	if err != nil {
		return err
	}
	defer resp.Body.Close()

	// Make sure it's JPG or PNG image
	cp := resp.Header.Get("Content-Type")
	if !strings.Contains(cp, "image/jpeg") &&
		!strings.Contains(cp, "image/pjpeg") &&
		!strings.Contains(cp, "image/jpg") &&
		!strings.Contains(cp, "image/webp") &&
		!strings.Contains(cp, "image/png") {
		return ErrNoSupportedImageType
	}

	// At this point, the download has finished successfully.
	// Create tmpFile
	tmpFile, err := os.CreateTemp("", "image")
	if err != nil {
		return fmt.Errorf("failed to create temporary image file: %v", err)
	}
	defer os.Remove(tmpFile.Name())

	// Parse image and process it.
	// If image is smaller than 600x400 or its ratio is less than 4:3, resize.
	// Else, save it as it is.
	img, _, err := image.Decode(resp.Body)
	if err != nil {
		return fmt.Errorf("failed to parse image %s: %v", url, err)
	}

	imgRect := img.Bounds()
	imgWidth := imgRect.Dx()
	imgHeight := imgRect.Dy()
	imgRatio := float64(imgWidth) / float64(imgHeight)

	if imgWidth >= 600 && imgHeight >= 400 && imgRatio > 1.3 {
		err = jpeg.Encode(tmpFile, img, nil)
	} else {
		// Create background
		bg := image.NewNRGBA(imgRect)
		draw.Draw(bg, imgRect, image.NewUniform(color.White), image.Point{}, draw.Src)
		draw.Draw(bg, imgRect, img, image.Point{}, draw.Over)

		bg = imaging.Fill(bg, 600, 400, imaging.Center, imaging.Lanczos)
		bg = imaging.Blur(bg, 150)
		bg = imaging.AdjustBrightness(bg, 30)

		// Create foreground
		fg := imaging.Fit(img, 600, 400, imaging.Lanczos)

		// Merge foreground and background
		bgRect := bg.Bounds()
		fgRect := fg.Bounds()
		fgPosition := image.Point{
			X: bgRect.Min.X - int(math.Round(float64(bgRect.Dx()-fgRect.Dx())/2)),
			Y: bgRect.Min.Y - int(math.Round(float64(bgRect.Dy()-fgRect.Dy())/2)),
		}

		draw.Draw(bg, bgRect, fg, fgPosition, draw.Over)

		// Save to file
		err = jpeg.Encode(tmpFile, bg, nil)
	}

	if err != nil {
		return fmt.Errorf("failed to save image %s: %v", url, err)
	}

	err = MoveFileToDestination(dstPath, tmpFile)
	if err != nil {
		return err
	}

	return nil
}

// dstPath requires the filename
func MoveFileToDestination(dstPath string, tmpFile *os.File) error {
	// Prepare destination file.
	err := os.MkdirAll(fp.Dir(dstPath), model.DataDirPerm)
	if err != nil {
		return fmt.Errorf("failed to create destination dir: %v", err)
	}

	dstFile, err := os.Create(dstPath)
	if err != nil {
		return fmt.Errorf("failed to create destination file: %v", err)
	}
	defer dstFile.Close()
	// Copy temporary file to destination
	_, err = tmpFile.Seek(0, io.SeekStart)
	if err != nil {
		return fmt.Errorf("failed to rewind temporary file: %v", err)
	}

	_, err = io.Copy(dstFile, tmpFile)
	if err != nil {
		return fmt.Errorf("failed to copy file to the destination")
	}

	return nil
}


code:
package core_test

import (
	"bytes"
	"net/http"
	"net/http/httptest"
	"os"
	fp "path/filepath"
	"testing"

	"github.com/go-shiori/shiori/internal/core"
	"github.com/go-shiori/shiori/internal/model"
	"github.com/stretchr/testify/assert"
)

func TestMoveFileToDestination(t *testing.T) {
	t.Run("create  fails", func(t *testing.T) {
		t.Run("directory create fails", func(t *testing.T) {
			// test if create dir fails
			tmpFile, err := os.CreateTemp("", "image")

			assert.NoError(t, err)
			defer os.Remove(tmpFile.Name())

			err = core.MoveFileToDestination("/destination/test", tmpFile)
			assert.Error(t, err)
			assert.Contains(t, err.Error(), "failed to create destination dir")
		})
		t.Run("file create fails", func(t *testing.T) {
			// if create file failed
			tmpFile, err := os.CreateTemp("", "image")
			assert.NoError(t, err)
			defer os.Remove(tmpFile.Name())

			// Create a destination directory
			dstDir := t.TempDir()
			assert.NoError(t, err)
			defer os.Remove(dstDir)

			// Set destination path to an invalid file name to force os.Create to fail
			dstPath := fp.Join(dstDir, "\000invalid\000")

			err = core.MoveFileToDestination(dstPath, tmpFile)
			assert.Error(t, err)
			assert.Contains(t, err.Error(), "failed to create destination file")
		})
	})
}
func TestDownloadBookImage(t *testing.T) {
	t.Run("Download Images", func(t *testing.T) {
		t.Run("fails", func(t *testing.T) {
			// images is too small with unsupported format with a valid URL
			imageURL := "https://github.com/go-shiori/shiori/blob/master/internal/view/assets/res/apple-touch-icon-152x152.png"
			tempDir := t.TempDir()
			dstPath := fp.Join(tempDir, "1")
			defer os.Remove(dstPath)

			// Act
			err := core.DownloadBookImage(imageURL, dstPath)

			// Assert
			assert.EqualError(t, err, "unsupported image type")
			assert.NoFileExists(t, dstPath)
		})
		t.Run("sucssesful downlosd image", func(t *testing.T) {
			// Arrange
			imageURL := "https://raw.githubusercontent.com/go-shiori/shiori/master/docs/readme/cover.png"
			tempDir := t.TempDir()
			dstPath := fp.Join(tempDir, "1")
			defer os.Remove(dstPath)

			// Act
			err := core.DownloadBookImage(imageURL, dstPath)

			// Assert
			assert.NoError(t, err)
			assert.FileExists(t, dstPath)
		})
		t.Run("sucssesful downlosd medium size image", func(t *testing.T) {
			// create a file server handler for the 'testdata' directory
			fs := http.FileServer(http.Dir("../../testdata/"))

			// start a test server with the file server handler
			server := httptest.NewServer(fs)
			defer server.Close()

			// Arrange
			imageURL := server.URL + "/medium_image.png"
			tempDir := t.TempDir()
			dstPath := fp.Join(tempDir, "1")
			defer os.Remove(dstPath)

			// Act
			err := core.DownloadBookImage(imageURL, dstPath)

			// Assert
			assert.NoError(t, err)
			assert.FileExists(t, dstPath)

		})
	})
}

func TestProcessBookmark(t *testing.T) {
	t.Run("ProcessRequest with sucssesful result", func(t *testing.T) {
		t.Run("Normal without image", func(t *testing.T) {
			bookmark := model.Bookmark{
				ID:            1,
				URL:           "https://example.com",
				Title:         "Example",
				Excerpt:       "This is an example article",
				CreateEbook:   true,
				CreateArchive: true,
			}
			content := bytes.NewBufferString("
This is an example article
")
			request := core.ProcessRequest{
				Bookmark:    bookmark,
				Content:     content,
				ContentType: "text/html",
				DataDir:     "/tmp",
				KeepTitle:   true,
				KeepExcerpt: true,
			}
			expected, _, _ := core.ProcessBookmark(request)

			if expected.ID != bookmark.ID {
				t.Errorf("Unexpected ID: got %v, want %v", expected.ID, bookmark.ID)
			}
			if expected.URL != bookmark.URL {
				t.Errorf("Unexpected URL: got %v, want %v", expected.URL, bookmark.URL)
			}
			if expected.Title != bookmark.Title {
				t.Errorf("Unexpected Title: got %v, want %v", expected.Title, bookmark.Title)
			}
			if expected.Excerpt != bookmark.Excerpt {
				t.Errorf("Unexpected Excerpt: got %v, want %v", expected.Excerpt, bookmark.Excerpt)
			}
		})
		t.Run("Normal with multipleimage", func(t *testing.T) {

			html := `html

		    <meta property="og:image" content="http://example.com/image1.jpg">
		    <meta property="og:image" content="http://example.com/image2.jpg">
		    <link rel="icon" type="image/png" href="http://example.com/favicon.png">

This is an example article

		`
			bookmark := model.Bookmark{
				ID:            1,
				URL:           "https://example.com",
				Title:         "Example",
				Excerpt:       "This is an example article",
				CreateEbook:   true,
				CreateArchive: true,
			}
			content := bytes.NewBufferString(html)
			request := core.ProcessRequest{
				Bookmark:    bookmark,
				Content:     content,
				ContentType: "text/html",
				DataDir:     "/tmp",
				KeepTitle:   true,
				KeepExcerpt: true,
			}
			expected, _, _ := core.ProcessBookmark(request)

			if expected.ID != bookmark.ID {
				t.Errorf("Unexpected ID: got %v, want %v", expected.ID, bookmark.ID)
			}
			if expected.URL != bookmark.URL {
				t.Errorf("Unexpected URL: got %v, want %v", expected.URL, bookmark.URL)
			}
			if expected.Title != bookmark.Title {
				t.Errorf("Unexpected Title: got %v, want %v", expected.Title, bookmark.Title)
			}
			if expected.Excerpt != bookmark.Excerpt {
				t.Errorf("Unexpected Excerpt: got %v, want %v", expected.Excerpt, bookmark.Excerpt)
			}
		})
		t.Run("ProcessRequest sucssesful with multipleimage included favicon and Thumbnail ", func(t *testing.T) {
			// create a file server handler for the 'testdata' directory
			fs := http.FileServer(http.Dir("../../testdata/"))

			// start a test server with the file server handler
			server := httptest.NewServer(fs)
			defer server.Close()

			html := `html

    		<meta property="og:image" content="http://example.com/image1.jpg">
    		<meta property="og:image" content="` + server.URL + `/big_image.png">
    		<link rel="icon" type="image/svg" href="` + server.URL + `/favicon.svg">

This is an example article

			`
			bookmark := model.Bookmark{
				ID:            1,
				URL:           "https://example.com",
				Title:         "Example",
				Excerpt:       "This is an example article",
				CreateEbook:   true,
				CreateArchive: true,
			}
			content := bytes.NewBufferString(html)
			request := core.ProcessRequest{
				Bookmark:    bookmark,
				Content:     content,
				ContentType: "text/html",
				DataDir:     "/tmp",
				KeepTitle:   true,
				KeepExcerpt: true,
			}
			expected, _, _ := core.ProcessBookmark(request)

			if expected.ID != bookmark.ID {
				t.Errorf("Unexpected ID: got %v, want %v", expected.ID, bookmark.ID)
			}
			if expected.URL != bookmark.URL {
				t.Errorf("Unexpected URL: got %v, want %v", expected.URL, bookmark.URL)
			}
			if expected.Title != bookmark.Title {
				t.Errorf("Unexpected Title: got %v, want %v", expected.Title, bookmark.Title)
			}
			if expected.Excerpt != bookmark.Excerpt {
				t.Errorf("Unexpected Excerpt: got %v, want %v", expected.Excerpt, bookmark.Excerpt)
			}
		})
		t.Run("ProcessRequest sucssesful with empty title ", func(t *testing.T) {
			bookmark := model.Bookmark{
				ID:            1,
				URL:           "https://example.com",
				Title:         "",
				Excerpt:       "This is an example article",
				CreateEbook:   true,
				CreateArchive: true,
			}
			content := bytes.NewBufferString("
This is an example article
")
			request := core.ProcessRequest{
				Bookmark:    bookmark,
				Content:     content,
				ContentType: "text/html",
				DataDir:     "/tmp",
				KeepTitle:   true,
				KeepExcerpt: true,
			}
			expected, _, _ := core.ProcessBookmark(request)

			if expected.ID != bookmark.ID {
				t.Errorf("Unexpected ID: got %v, want %v", expected.ID, bookmark.ID)
			}
			if expected.URL != bookmark.URL {
				t.Errorf("Unexpected URL: got %v, want %v", expected.URL, bookmark.URL)
			}
			if expected.Title != bookmark.URL {
				t.Errorf("Unexpected Title: got %v, want %v", expected.Title, bookmark.Title)
			}
			if expected.Excerpt != bookmark.Excerpt {
				t.Errorf("Unexpected Excerpt: got %v, want %v", expected.Excerpt, bookmark.Excerpt)
			}
		})
		t.Run("ProcessRequest sucssesful with empty Excerpt", func(t *testing.T) {
			bookmark := model.Bookmark{
				ID:            1,
				URL:           "https://example.com",
				Title:         "",
				Excerpt:       "This is an example article",
				CreateEbook:   true,
				CreateArchive: true,
			}
			content := bytes.NewBufferString("
This is an example article
")
			request := core.ProcessRequest{
				Bookmark:    bookmark,
				Content:     content,
				ContentType: "text/html",
				DataDir:     "/tmp",
				KeepTitle:   true,
				KeepExcerpt: false,
			}
			expected, _, _ := core.ProcessBookmark(request)

			if expected.ID != bookmark.ID {
				t.Errorf("Unexpected ID: got %v, want %v", expected.ID, bookmark.ID)
			}
			if expected.URL != bookmark.URL {
				t.Errorf("Unexpected URL: got %v, want %v", expected.URL, bookmark.URL)
			}
			if expected.Title != bookmark.URL {
				t.Errorf("Unexpected Title: got %v, want %v", expected.Title, bookmark.Title)
			}
			if expected.Excerpt != bookmark.Excerpt {
				t.Errorf("Unexpected Excerpt: got %v, want %v", expected.Excerpt, bookmark.Excerpt)
			}
		})
		t.Run("Specific case", func(t *testing.T) {
			t.Run("ProcessRequest with ID zero", func(t *testing.T) {

				bookmark := model.Bookmark{
					ID:            0,
					URL:           "https://example.com",
					Title:         "Example",
					Excerpt:       "This is an example article",
					CreateEbook:   true,
					CreateArchive: true,
				}
				content := bytes.NewBufferString("
This is an example article
")
				request := core.ProcessRequest{
					Bookmark:    bookmark,
					Content:     content,
					ContentType: "text/html",
					DataDir:     "/tmp",
					KeepTitle:   true,
					KeepExcerpt: true,
				}
				_, isFatal, err := core.ProcessBookmark(request)
				assert.Error(t, err)
				assert.Contains(t, err.Error(), "bookmark ID is not valid")
				assert.True(t, isFatal)
			})

			t.Run("ProcessRequest that content type not zero", func(t *testing.T) {

				bookmark := model.Bookmark{
					ID:            1,
					URL:           "https://example.com",
					Title:         "Example",
					Excerpt:       "This is an example article",
					CreateEbook:   true,
					CreateArchive: true,
				}
				content := bytes.NewBufferString("
This is an example article
")
				request := core.ProcessRequest{
					Bookmark:    bookmark,
					Content:     content,
					ContentType: "application/pdf",
					DataDir:     "/tmp",
					KeepTitle:   true,
					KeepExcerpt: true,
				}
				_, _, err := core.ProcessBookmark(request)
				assert.NoError(t, err)
			})
		})
	})
}


code:
package core

import (
	"fmt"
	nurl "net/url"
	"sort"
	"strings"
)

// queryEncodeWithoutEmptyValues is a copy of `values.Encode` but checking if the queryparam
// value is empty to prevent sending the = symbol empty which breaks in some servers.
func queryEncodeWithoutEmptyValues(v nurl.Values) string {
	if v == nil {
		return ""
	}
	var buf strings.Builder
	keys := make([]string, 0, len(v))
	for k := range v {
		keys = append(keys, k)
	}
	sort.Strings(keys)
	for _, k := range keys {
		vs := v[k]
		keyEscaped := nurl.QueryEscape(k)
		for _, v := range vs {
			if buf.Len() > 0 {
				buf.WriteByte('&')
			}
			buf.WriteString(keyEscaped)
			if v != "" {
				buf.WriteByte('=')
				buf.WriteString(nurl.QueryEscape(v))
			}
		}
	}
	return buf.String()
}

// RemoveUTMParams removes the UTM parameters from URL.
func RemoveUTMParams(url string) (string, error) {
	// Parse string URL
	tmp, err := nurl.Parse(url)
	if err != nil || tmp.Scheme == "" || tmp.Hostname() == "" {
		return url, fmt.Errorf("URL is not valid")
	}

	// Remove UTM queries
	queries := tmp.Query()
	for key := range queries {
		if strings.HasPrefix(key, "utm_") {
			queries.Del(key)
		}
	}

	tmp.RawQuery = queryEncodeWithoutEmptyValues(queries)
	return tmp.String(), nil
}


code:
package database

import (
	"context"
	"embed"
	"fmt"
	"log"
	"net/url"
	"strings"

	"github.com/go-shiori/shiori/internal/model"
	"github.com/jmoiron/sqlx"
	"github.com/pkg/errors"
)

//go:embed migrations/*
var migrations embed.FS

// OrderMethod is the order method for getting bookmarks
type OrderMethod int

const (
	// DefaultOrder is oldest to newest.
	DefaultOrder OrderMethod = iota
	// ByLastAdded is from newest addition to the oldest.
	ByLastAdded
	// ByLastModified is from latest modified to the oldest.
	ByLastModified
)

// GetBookmarksOptions is options for fetching bookmarks from database.
type GetBookmarksOptions struct {
	IDs          []int
	Tags         []string
	ExcludedTags []string
	Keyword      string
	WithContent  bool
	OrderMethod  OrderMethod
	Limit        int
	Offset       int
}

// GetAccountsOptions is options for fetching accounts from database.
type GetAccountsOptions struct {
	Keyword string
	Owner   bool
}

// Connect connects to database based on submitted database URL.
func Connect(ctx context.Context, dbURL string) (DB, error) {
	dbU, err := url.Parse(dbURL)
	if err != nil {
		return nil, errors.Wrap(err, "failed to parse database URL")
	}

	switch dbU.Scheme {
	case "mysql":
		urlNoSchema := strings.Split(dbURL, "://")[1]
		return OpenMySQLDatabase(ctx, urlNoSchema)
	case "postgres":
		return OpenPGDatabase(ctx, dbURL)
	case "sqlite":
		return OpenSQLiteDatabase(ctx, dbU.Path[1:])
	}

	return nil, fmt.Errorf("unsupported database scheme: %s", dbU.Scheme)
}

// DB is interface for accessing and manipulating data in database.
type DB interface {
	// Migrate runs migrations for this database
	Migrate() error

	// SaveBookmarks saves bookmarks data to database.
	SaveBookmarks(ctx context.Context, create bool, bookmarks ...model.Bookmark) ([]model.Bookmark, error)

	// GetBookmarks fetch list of bookmarks based on submitted options.
	GetBookmarks(ctx context.Context, opts GetBookmarksOptions) ([]model.Bookmark, error)

	// GetBookmarksCount get count of bookmarks in database.
	GetBookmarksCount(ctx context.Context, opts GetBookmarksOptions) (int, error)

	// DeleteBookmarks removes all record with matching ids from database.
	DeleteBookmarks(ctx context.Context, ids ...int) error

	// GetBookmark fetches bookmark based on its ID or URL.
	GetBookmark(ctx context.Context, id int, url string) (model.Bookmark, bool, error)

	// SaveAccount saves new account in database
	SaveAccount(ctx context.Context, a model.Account) error

	// SaveAccountSettings saves settings for specific user in database
	SaveAccountSettings(ctx context.Context, a model.Account) error

	// GetAccounts fetch list of account (without its password) with matching keyword.
	GetAccounts(ctx context.Context, opts GetAccountsOptions) ([]model.Account, error)

	// GetAccount fetch account with matching username.
	GetAccount(ctx context.Context, username string) (model.Account, bool, error)

	// DeleteAccounts removes all record with matching usernames
	DeleteAccounts(ctx context.Context, usernames ...string) error

	// CreateTags creates new tags in database.
	CreateTags(ctx context.Context, tags ...model.Tag) error

	// GetTags fetch list of tags and its frequency from database.
	GetTags(ctx context.Context) ([]model.Tag, error)

	// RenameTag change the name of a tag.
	RenameTag(ctx context.Context, id int, newName string) error
}

type dbbase struct {
	sqlx.DB
}

func (db *dbbase) withTx(ctx context.Context, fn func(tx *sqlx.Tx) error) error {
	tx, err := db.BeginTxx(ctx, nil)
	if err != nil {
		return errors.WithStack(err)
	}

	defer func() {
		if err := tx.Commit(); err != nil {
			log.Printf("error during commit: %s", err)
		}
	}()

	err = fn(tx)
	if err != nil {
		if err := tx.Rollback(); err != nil {
			log.Printf("error during rollback: %s", err)
		}
		return errors.WithStack(err)
	}

	return err
}


code:
package database

import (
	"context"
	"testing"

	"github.com/go-shiori/shiori/internal/model"
	"github.com/stretchr/testify/assert"
)

type databaseTestCase func(t *testing.T, db DB)
type testDatabaseFactory func(ctx context.Context) (DB, error)

func testDatabase(t *testing.T, dbFactory testDatabaseFactory) {
	tests := map[string]databaseTestCase{
		// Bookmarks
		"testBookmarkAutoIncrement":         testBookmarkAutoIncrement,
		"testCreateBookmark":                testCreateBookmark,
		"testCreateBookmarkWithContent":     testCreateBookmarkWithContent,
		"testCreateBookmarkTwice":           testCreateBookmarkTwice,
		"testCreateBookmarkWithTag":         testCreateBookmarkWithTag,
		"testCreateTwoDifferentBookmarks":   testCreateTwoDifferentBookmarks,
		"testUpdateBookmark":                testUpdateBookmark,
		"testUpdateBookmarkWithContent":     testUpdateBookmarkWithContent,
		"testGetBookmark":                   testGetBookmark,
		"testGetBookmarkNotExistent":        testGetBookmarkNotExistent,
		"testGetBookmarks":                  testGetBookmarks,
		"testGetBookmarksWithSQLCharacters": testGetBookmarksWithSQLCharacters,
		"testGetBookmarksCount":             testGetBookmarksCount,
		// Tags
		"testCreateTag":  testCreateTag,
		"testCreateTags": testCreateTags,
	}

	for testName, testCase := range tests {
		t.Run(testName, func(tInner *testing.T) {
			ctx := context.TODO()
			db, err := dbFactory(ctx)
			assert.NoError(tInner, err, "Error recreating database")
			testCase(tInner, db)
		})
	}
}

func testBookmarkAutoIncrement(t *testing.T, db DB) {
	ctx := context.TODO()

	book := model.Bookmark{
		URL:   "https://github.com/go-shiori/shiori",
		Title: "shiori",
	}

	result, err := db.SaveBookmarks(ctx, true, book)
	assert.NoError(t, err, "Save bookmarks must not fail")
	assert.Equal(t, 1, result[0].ID, "Saved bookmark must have ID %d", 1)

	book = model.Bookmark{
		URL:   "https://github.com/go-shiori/obelisk",
		Title: "obelisk",
	}

	result, err = db.SaveBookmarks(ctx, true, book)
	assert.NoError(t, err, "Save bookmarks must not fail")
	assert.Equal(t, 2, result[0].ID, "Saved bookmark must have ID %d", 2)
}

func testCreateBookmark(t *testing.T, db DB) {
	ctx := context.TODO()

	book := model.Bookmark{
		URL:   "https://github.com/go-shiori/obelisk",
		Title: "shiori",
	}

	result, err := db.SaveBookmarks(ctx, true, book)

	assert.NoError(t, err, "Save bookmarks must not fail")
	assert.Equal(t, 1, result[0].ID, "Saved bookmark must have an ID set")
}

func testCreateBookmarkWithContent(t *testing.T, db DB) {
	ctx := context.TODO()

	book := model.Bookmark{
		URL:     "https://github.com/go-shiori/obelisk",
		Title:   "shiori",
		Content: "Some content",
		HTML:    "Some HTML content",
	}

	result, err := db.SaveBookmarks(ctx, true, book)
	assert.NoError(t, err, "Save bookmarks must not fail")

	books, err := db.GetBookmarks(ctx, GetBookmarksOptions{
		IDs:         []int{result[0].ID},
		WithContent: true,
	})
	assert.NoError(t, err, "Get bookmarks must not fail")
	assert.Len(t, books, 1)

	assert.Equal(t, 1, books[0].ID, "Saved bookmark must have an ID set")
	assert.Equal(t, book.Content, books[0].Content, "Saved bookmark must have content")
	assert.Equal(t, book.HTML, books[0].HTML, "Saved bookmark must have HTML")
}

func testCreateBookmarkWithTag(t *testing.T, db DB) {
	ctx := context.TODO()

	book := model.Bookmark{
		URL:   "https://github.com/go-shiori/obelisk",
		Title: "shiori",
		Tags: []model.Tag{
			{
				Name: "test-tag",
			},
		},
	}

	result, err := db.SaveBookmarks(ctx, true, book)

	assert.NoError(t, err, "Save bookmarks must not fail")
	assert.Equal(t, book.URL, result[0].URL)
	assert.Equal(t, book.Tags[0].Name, result[0].Tags[0].Name)
}

func testCreateBookmarkTwice(t *testing.T, db DB) {
	ctx := context.TODO()

	book := model.Bookmark{
		URL:   "https://github.com/go-shiori/shiori",
		Title: "shiori",
	}

	result, err := db.SaveBookmarks(ctx, true, book)
	assert.NoError(t, err, "Save bookmarks must not fail")

	savedBookmark := result[0]
	savedBookmark.Title = "modified"

	_, err = db.SaveBookmarks(ctx, true, savedBookmark)
	assert.Error(t, err, "Save bookmarks must fail")
}

func testCreateTwoDifferentBookmarks(t *testing.T, db DB) {
	ctx := context.TODO()

	book := model.Bookmark{
		URL:   "https://github.com/go-shiori/shiori",
		Title: "shiori",
	}

	_, err := db.SaveBookmarks(ctx, true, book)
	assert.NoError(t, err, "Save first bookmark must not fail")

	book = model.Bookmark{
		URL:   "https://github.com/go-shiori/go-readability",
		Title: "go-readability",
	}
	_, err = db.SaveBookmarks(ctx, true, book)
	assert.NoError(t, err, "Save second bookmark must not fail")
}

func testUpdateBookmark(t *testing.T, db DB) {
	ctx := context.TODO()

	book := model.Bookmark{
		URL:   "https://github.com/go-shiori/shiori",
		Title: "shiori",
	}

	result, err := db.SaveBookmarks(ctx, true, book)
	assert.NoError(t, err, "Save bookmarks must not fail")

	savedBookmark := result[0]
	savedBookmark.Title = "modified"

	result, err = db.SaveBookmarks(ctx, false, savedBookmark)
	assert.NoError(t, err, "Save bookmarks must not fail")

	assert.Equal(t, "modified", result[0].Title)
	assert.Equal(t, savedBookmark.ID, result[0].ID)
}

func testUpdateBookmarkWithContent(t *testing.T, db DB) {
	ctx := context.TODO()

	book := model.Bookmark{
		URL:     "https://github.com/go-shiori/obelisk",
		Title:   "shiori",
		Content: "Some content",
		HTML:    "Some HTML content",
	}

	result, err := db.SaveBookmarks(ctx, true, book)
	assert.NoError(t, err, "Save bookmarks must not fail")

	updatedBook := result[0]
	updatedBook.Content = "Some updated content"
	updatedBook.HTML = "Some updated HTML content"

	_, err = db.SaveBookmarks(ctx, false, updatedBook)
	assert.NoError(t, err, "Save bookmarks must not fail")

	books, err := db.GetBookmarks(ctx, GetBookmarksOptions{
		IDs:         []int{result[0].ID},
		WithContent: true,
	})
	assert.NoError(t, err, "Get bookmarks must not fail")
	assert.Len(t, books, 1)

	assert.Equal(t, 1, books[0].ID, "Saved bookmark must have an ID set")
	assert.Equal(t, updatedBook.Content, books[0].Content, "Saved bookmark must have updated content")
	assert.Equal(t, updatedBook.HTML, books[0].HTML, "Saved bookmark must have updated HTML")
}

func testGetBookmark(t *testing.T, db DB) {
	ctx := context.TODO()

	book := model.Bookmark{
		URL:   "https://github.com/go-shiori/shiori",
		Title: "shiori",
	}

	result, err := db.SaveBookmarks(ctx, true, book)
	assert.NoError(t, err, "Save bookmarks must not fail")

	savedBookmark, exists, err := db.GetBookmark(ctx, result[0].ID, "")
	assert.True(t, exists, "Bookmark should exist")
	assert.NoError(t, err, "Get bookmark should not fail")
	assert.Equal(t, result[0].ID, savedBookmark.ID, "Retrieved bookmark should be the same")
	assert.Equal(t, book.URL, savedBookmark.URL, "Retrieved bookmark should be the same")
}

func testGetBookmarkNotExistent(t *testing.T, db DB) {
	ctx := context.TODO()

	savedBookmark, exists, err := db.GetBookmark(ctx, 1, "")
	assert.NoError(t, err, "Get bookmark should not fail")
	assert.False(t, exists, "Bookmark should not exist")
	assert.Equal(t, model.Bookmark{}, savedBookmark)
}

func testGetBookmarks(t *testing.T, db DB) {
	ctx := context.TODO()

	book := model.Bookmark{
		URL:   "https://github.com/go-shiori/shiori",
		Title: "shiori",
	}

	bookmarks, err := db.SaveBookmarks(ctx, true, book)
	assert.NoError(t, err, "Save bookmarks must not fail")

	savedBookmark := bookmarks[0]

	results, err := db.GetBookmarks(ctx, GetBookmarksOptions{
		Keyword: "go-shiori",
	})

	assert.NoError(t, err, "Get bookmarks should not fail")
	assert.Len(t, results, 1, "results should contain one item")
	assert.Equal(t, savedBookmark.ID, results[0].ID, "bookmark should be the one saved")
}

func testGetBookmarksWithSQLCharacters(t *testing.T, db DB) {
	ctx := context.TODO()

	// _ := 0
	book := model.Bookmark{
		URL:   "https://github.com/go-shiori/shiori",
		Title: "shiori",
	}
	_, err := db.SaveBookmarks(ctx, true, book)
	assert.NoError(t, err, "Save bookmarks must not fail")

	characters := []string{";", "%", "_", "\\", "\"", ":"}

	for _, char := range characters {
		t.Run("GetBookmarks/"+char, func(t *testing.T) {
			_, err := db.GetBookmarks(ctx, GetBookmarksOptions{
				Keyword: char,
			})
			assert.NoError(t, err, "Get bookmarks should not fail")
		})

		t.Run("GetBookmarksCount/"+char, func(t *testing.T) {
			_, err := db.GetBookmarksCount(ctx, GetBookmarksOptions{
				Keyword: char,
			})
			assert.NoError(t, err, "Get bookmarks count should not fail")
		})
	}
}

func testGetBookmarksCount(t *testing.T, db DB) {
	ctx := context.TODO()

	expectedCount := 1
	book := model.Bookmark{
		URL:   "https://github.com/go-shiori/shiori",
		Title: "shiori",
	}

	_, err := db.SaveBookmarks(ctx, true, book)
	assert.NoError(t, err, "Save bookmarks must not fail")

	count, err := db.GetBookmarksCount(ctx, GetBookmarksOptions{
		Keyword: "go-shiori",
	})
	assert.NoError(t, err, "Get bookmarks count should not fail")
	assert.Equal(t, count, expectedCount, "count should be %d", expectedCount)
}

func testCreateTag(t *testing.T, db DB) {
	ctx := context.TODO()
	tag := model.Tag{Name: "shiori"}
	err := db.CreateTags(ctx, tag)
	assert.NoError(t, err, "Save tag must not fail")
}

func testCreateTags(t *testing.T, db DB) {
	ctx := context.TODO()
	err := db.CreateTags(ctx, model.Tag{Name: "shiori"}, model.Tag{Name: "shiori2"})
	assert.NoError(t, err, "Save tag must not fail")
}


code:
CREATE TABLE IF NOT EXISTS account(
		id       INT(11)        NOT NULL AUTO_INCREMENT,
		username VARCHAR(250)   NOT NULL,
		password BINARY(80)     NOT NULL,
		owner    TINYINT(1)     NOT NULL DEFAULT '0',
		PRIMARY KEY (id),
		UNIQUE KEY account_username_UNIQUE (username))
		CHARACTER SET utf8mb4;


code:
CREATE TABLE IF NOT EXISTS bookmark(
		id       INT(11)    NOT NULL AUTO_INCREMENT,
		url      TEXT       NOT NULL,
		title    TEXT       NOT NULL,
		excerpt  TEXT       NOT NULL DEFAULT (''),
		author   TEXT       NOT NULL DEFAULT (''),
		public   BOOLEAN    NOT NULL DEFAULT 0,
		content  MEDIUMTEXT NOT NULL DEFAULT (''),
		html     MEDIUMTEXT NOT NULL DEFAULT (''),
		modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
		PRIMARY KEY(id),
		UNIQUE KEY bookmark_url_UNIQUE (url(255)),
		FULLTEXT (title, excerpt, content))
		CHARACTER SET utf8mb4;


code:
CREATE TABLE IF NOT EXISTS tag(
		id   INT(11)      NOT NULL AUTO_INCREMENT,
		name VARCHAR(250) NOT NULL,
		PRIMARY KEY (id),
		UNIQUE KEY tag_name_UNIQUE (name))
		CHARACTER SET utf8mb4;


code:
CREATE TABLE IF NOT EXISTS bookmark_tag(
		bookmark_id INT(11)      NOT NULL,
		tag_id      INT(11)      NOT NULL,
		PRIMARY KEY(bookmark_id, tag_id),
		KEY bookmark_tag_bookmark_id_FK (bookmark_id),
		KEY bookmark_tag_tag_id_FK (tag_id),
		CONSTRAINT bookmark_tag_bookmark_id_FK FOREIGN KEY (bookmark_id) REFERENCES bookmark (id),
		CONSTRAINT bookmark_tag_tag_id_FK FOREIGN KEY (tag_id) REFERENCES tag (id))
		CHARACTER SET utf8mb4;


code: ALTER TABLE account DROP COLUMN config;


code:
ALTER TABLE account
    ADD COLUMN config JSON NOT NULL DEFAULT '{}';


code:
CREATE TABLE IF NOT EXISTS account(
		id       SERIAL,
		username VARCHAR(250) NOT NULL,
		password BYTEA    NOT NULL,
		owner    BOOLEAN  NOT NULL DEFAULT FALSE,
		PRIMARY KEY (id),
		CONSTRAINT account_username_UNIQUE UNIQUE (username));

CREATE TABLE IF NOT EXISTS bookmark(
		id       SERIAL,
		url      TEXT       NOT NULL,
		title    TEXT       NOT NULL,
		excerpt  TEXT       NOT NULL DEFAULT '',
		author   TEXT       NOT NULL DEFAULT '',
		public   SMALLINT   NOT NULL DEFAULT 0,
		content  TEXT       NOT NULL DEFAULT '',
		html     TEXT       NOT NULL DEFAULT '',
		modified TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
		PRIMARY KEY(id),
		CONSTRAINT bookmark_url_UNIQUE UNIQUE (url));

CREATE TABLE IF NOT EXISTS tag(
		id   SERIAL,
		name VARCHAR(250) NOT NULL,
		PRIMARY KEY (id),
		CONSTRAINT tag_name_UNIQUE UNIQUE (name));

CREATE TABLE IF NOT EXISTS bookmark_tag(
		bookmark_id INT      NOT NULL,
		tag_id      INT      NOT NULL,
		PRIMARY KEY(bookmark_id, tag_id),
		CONSTRAINT bookmark_tag_bookmark_id_FK FOREIGN KEY (bookmark_id) REFERENCES bookmark (id),
		CONSTRAINT bookmark_tag_tag_id_FK FOREIGN KEY (tag_id) REFERENCES tag (id));

CREATE INDEX IF NOT EXISTS bookmark_tag_bookmark_id_FK ON bookmark_tag (bookmark_id);
CREATE INDEX IF NOT EXISTS bookmark_tag_tag_id_FK ON bookmark_tag (tag_id);


code:
ALTER TABLE account
    ADD COLUMN config JSONB NOT NULL DEFAULT '{}';


code:
CREATE TABLE IF NOT EXISTS account(
    id INTEGER NOT NULL,
    username TEXT NOT NULL,
    password TEXT NOT NULL,
    owner INTEGER NOT NULL DEFAULT 0,
    CONSTRAINT account_PK PRIMARY KEY(id),
    CONSTRAINT account_username_UNIQUE UNIQUE(username)
);

CREATE TABLE IF NOT EXISTS bookmark(
    id INTEGER NOT NULL,
    url TEXT NOT NULL,
    title TEXT NOT NULL,
    excerpt TEXT NOT NULL DEFAULT "",
    author TEXT NOT NULL DEFAULT "",
    public INTEGER NOT NULL DEFAULT 0,
    modified TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT bookmark_PK PRIMARY KEY(id),
    CONSTRAINT bookmark_url_UNIQUE UNIQUE(url)
);

CREATE TABLE IF NOT EXISTS tag(
    id INTEGER NOT NULL,
    name TEXT NOT NULL,
    CONSTRAINT tag_PK PRIMARY KEY(id),
    CONSTRAINT tag_name_UNIQUE UNIQUE(name)
);

CREATE TABLE IF NOT EXISTS bookmark_tag(
    bookmark_id INTEGER NOT NULL,
    tag_id INTEGER NOT NULL,
    CONSTRAINT bookmark_tag_PK PRIMARY KEY(bookmark_id, tag_id),
    CONSTRAINT bookmark_id_FK FOREIGN KEY(bookmark_id) REFERENCES bookmark(id),
    CONSTRAINT tag_id_FK FOREIGN KEY(tag_id) REFERENCES tag(id)
);

CREATE VIRTUAL TABLE IF NOT EXISTS bookmark_content
    USING fts5(title, content, html, docid);


code: ALTER TABLE bookmark DROP COLUMN has_content;


code:
ALTER TABLE bookmark
    ADD has_content BOOLEAN DEFAULT FALSE NOT NULL;

UPDATE bookmark
SET has_content = bc.has_content FROM (SELECT docid, content <> '' AS has_content FROM bookmark_content) AS bc
WHERE bookmark.id = bc.docid;


code:
ALTER TABLE account
    ADD config JSON NOT NULL DEFAULT '{}';


code:
package database

import (
	"context"
	"database/sql"
	"strings"
	"time"

	"github.com/go-shiori/shiori/internal/model"
	"github.com/golang-migrate/migrate/v4"
	"github.com/golang-migrate/migrate/v4/database/mysql"
	"github.com/golang-migrate/migrate/v4/source/iofs"
	"github.com/jmoiron/sqlx"
	"github.com/pkg/errors"
	"golang.org/x/crypto/bcrypt"
)

// MySQLDatabase is implementation of Database interface
// for connecting to MySQL or MariaDB database.
type MySQLDatabase struct {
	dbbase
}

// OpenMySQLDatabase creates and opens connection to a MySQL Database.
func OpenMySQLDatabase(ctx context.Context, connString string) (mysqlDB *MySQLDatabase, err error) {
	// Open database and start transaction
	db, err := sqlx.ConnectContext(ctx, "mysql", connString)
	if err != nil {
		return nil, errors.WithStack(err)
	}

	db.SetMaxOpenConns(100)
	db.SetConnMaxLifetime(time.Second) // in case mysql client has longer timeout (driver issue #674)

	mysqlDB = &MySQLDatabase{dbbase: dbbase{*db}}
	return mysqlDB, err
}

// Migrate runs migrations for this database engine
func (db *MySQLDatabase) Migrate() error {
	sourceDriver, err := iofs.New(migrations, "migrations/mysql")
	if err != nil {
		return errors.WithStack(err)
	}

	dbDriver, err := mysql.WithInstance(db.DB.DB, &mysql.Config{})
	if err != nil {
		return errors.WithStack(err)
	}

	migration, err := migrate.NewWithInstance(
		"iofs",
		sourceDriver,
		"mysql",
		dbDriver,
	)
	if err != nil {
		return errors.WithStack(err)
	}

	if err := migration.Up(); err != nil && !errors.Is(err, migrate.ErrNoChange) {
		return err
	}

	return nil
}

// SaveBookmarks saves new or updated bookmarks to database.
// Returns the saved ID and error message if any happened.
func (db *MySQLDatabase) SaveBookmarks(ctx context.Context, create bool, bookmarks ...model.Bookmark) ([]model.Bookmark, error) {
	var result []model.Bookmark

	if err := db.withTx(ctx, func(tx *sqlx.Tx) error {
		// Prepare statement
		stmtInsertBook, err := tx.Preparex(`INSERT INTO bookmark
			(url, title, excerpt, author, public, content, html, modified)
			VALUES(?, ?, ?, ?, ?, ?, ?, ?)`)
		if err != nil {
			return errors.WithStack(err)
		}

		stmtUpdateBook, err := tx.Preparex(`UPDATE bookmark
		SET url      = ?,
			title    = ?,
			excerpt  = ?,
			author   = ?,
			public   = ?,
			content  = ?,
			html     = ?,
			modified = ?
		WHERE id = ?`)
		if err != nil {
			return errors.WithStack(err)
		}

		stmtGetTag, err := tx.Preparex(`SELECT id FROM tag WHERE name = ?`)
		if err != nil {
			return errors.WithStack(err)
		}

		stmtInsertTag, err := tx.Preparex(`INSERT INTO tag (name) VALUES (?)`)
		if err != nil {
			return errors.WithStack(err)
		}

		stmtInsertBookTag, err := tx.Preparex(`INSERT IGNORE INTO bookmark_tag
			(tag_id, bookmark_id) VALUES (?, ?)`)
		if err != nil {
			return errors.WithStack(err)
		}

		stmtDeleteBookTag, err := tx.Preparex(`DELETE FROM bookmark_tag
			WHERE bookmark_id = ? AND tag_id = ?`)
		if err != nil {
			return errors.WithStack(err)
		}

		// Prepare modified time
		modifiedTime := time.Now().UTC().Format(model.DatabaseDateFormat)

		// Execute statements

		for _, book := range bookmarks {
			// Check URL and title
			if book.URL == "" {
				return errors.New("URL must not be empty")
			}

			if book.Title == "" {
				return errors.New("title must not be empty")
			}

			// Set modified time
			if book.Modified == "" {
				book.Modified = modifiedTime
			}

			// Save bookmark
			var err error
			if create {
				var res sql.Result
				res, err = stmtInsertBook.ExecContext(ctx,
					book.URL, book.Title, book.Excerpt, book.Author,
					book.Public, book.Content, book.HTML, book.Modified)
				if err != nil {
					return errors.WithStack(err)
				}
				bookID, err := res.LastInsertId()
				if err != nil {
					return errors.WithStack(err)
				}
				book.ID = int(bookID)
			} else {
				_, err = stmtUpdateBook.ExecContext(ctx,
					book.URL, book.Title, book.Excerpt, book.Author,
					book.Public, book.Content, book.HTML, book.Modified, book.ID)
			}
			if err != nil {
				return errors.WithStack(err)
			}

			// Save book tags
			newTags := []model.Tag{}
			for _, tag := range book.Tags {
				// If it's deleted tag, delete and continue
				if tag.Deleted {
					_, err = stmtDeleteBookTag.ExecContext(ctx, book.ID, tag.ID)
					if err != nil {
						return errors.WithStack(err)
					}

					continue
				}

				// Normalize tag name
				tagName := strings.ToLower(tag.Name)
				tagName = strings.Join(strings.Fields(tagName), " ")

				// If tag doesn't have any ID, fetch it from database
				if tag.ID == 0 {
					if err := stmtGetTag.GetContext(ctx, &tag.ID, tagName); err != nil && err != sql.ErrNoRows {
						return errors.WithStack(err)
					}

					// If tag doesn't exist in database, save it
					if tag.ID == 0 {
						res, err := stmtInsertTag.ExecContext(ctx, tagName)
						if err != nil {
							return errors.WithStack(err)
						}

						tagID64, err := res.LastInsertId()
						if err != nil {
							return errors.WithStack(err)
						}

						tag.ID = int(tagID64)
					}

					if _, err := stmtInsertBookTag.ExecContext(ctx, tag.ID, book.ID); err != nil {
						return errors.WithStack(err)
					}
				}

				newTags = append(newTags, tag)
			}

			book.Tags = newTags
			result = append(result, book)
		}

		return nil
	}); err != nil {
		return result, errors.WithStack(err)
	}

	return result, nil
}

// GetBookmarks fetch list of bookmarks based on submitted options.
func (db *MySQLDatabase) GetBookmarks(ctx context.Context, opts GetBookmarksOptions) ([]model.Bookmark, error) {
	// Create initial query
	columns := []string{
		`id`,
		`url`,
		`title`,
		`excerpt`,
		`author`,
		`public`,
		`modified`,
		`content <> "" has_content`}

	if opts.WithContent {
		columns = append(columns, `content`, `html`)
	}

	query := `SELECT ` + strings.Join(columns, ",") + `
		FROM bookmark WHERE 1`

	// Add where clause
	args := []interface{}{}

	// Add where clause for IDs
	if len(opts.IDs) > 0 {
		query += ` AND id IN (?)`
		args = append(args, opts.IDs)
	}

	// Add where clause for search keyword
	if opts.Keyword != "" {
		query += ` AND (
			url LIKE ? OR
			MATCH(title, excerpt, content) AGAINST (? IN BOOLEAN MODE)
		)`

		args = append(args, "%"+opts.Keyword+"%", opts.Keyword)
	}

	// Add where clause for tags.
	// First we check for * in excluded and included tags,
	// which means all tags will be excluded and included, respectively.
	excludeAllTags := false
	for _, excludedTag := range opts.ExcludedTags {
		if excludedTag == "*" {
			excludeAllTags = true
			opts.ExcludedTags = []string{}
			break
		}
	}

	includeAllTags := false
	for _, includedTag := range opts.Tags {
		if includedTag == "*" {
			includeAllTags = true
			opts.Tags = []string{}
			break
		}
	}

	// If all tags excluded, we will only show bookmark without tags.
	// In other hand, if all tags included, we will only show bookmark with tags.
	if excludeAllTags {
		query += ` AND id NOT IN (SELECT DISTINCT bookmark_id FROM bookmark_tag)`
	} else if includeAllTags {
		query += ` AND id IN (SELECT DISTINCT bookmark_id FROM bookmark_tag)`
	}

	// Now we only need to find the normal tags
	if len(opts.Tags) > 0 {
		query += ` AND id IN (
			SELECT bt.bookmark_id
			FROM bookmark_tag bt
			LEFT JOIN tag t ON bt.tag_id = t.id
			WHERE t.name IN(?)
			GROUP BY bt.bookmark_id
			HAVING COUNT(bt.bookmark_id) = ?)`

		args = append(args, opts.Tags, len(opts.Tags))
	}

	if len(opts.ExcludedTags) > 0 {
		query += ` AND id NOT IN (
			SELECT DISTINCT bt.bookmark_id
			FROM bookmark_tag bt
			LEFT JOIN tag t ON bt.tag_id = t.id
			WHERE t.name IN(?))`

		args = append(args, opts.ExcludedTags)
	}

	// Add order clause
	switch opts.OrderMethod {
	case ByLastAdded:
		query += ` ORDER BY id DESC`
	case ByLastModified:
		query += ` ORDER BY modified DESC`
	default:
		query += ` ORDER BY id`
	}

	if opts.Limit > 0 && opts.Offset >= 0 {
		query += ` LIMIT ? OFFSET ?`
		args = append(args, opts.Limit, opts.Offset)
	}

	// Expand query, because some of the args might be an array
	query, args, err := sqlx.In(query, args...)
	if err != nil {
		return nil, errors.WithStack(err)
	}

	// Fetch bookmarks
	bookmarks := []model.Bookmark{}
	err = db.Select(&bookmarks, query, args...)
	if err != nil && err != sql.ErrNoRows {
		return nil, errors.WithStack(err)
	}

	// Fetch tags for each bookmarks
	stmtGetTags, err := db.PreparexContext(ctx, `SELECT t.id, t.name
		FROM bookmark_tag bt
		LEFT JOIN tag t ON bt.tag_id = t.id
		WHERE bt.bookmark_id = ?
		ORDER BY t.name`)
	if err != nil {
		return nil, errors.WithStack(err)
	}
	defer stmtGetTags.Close()

	for i, book := range bookmarks {
		book.Tags = []model.Tag{}
		err = stmtGetTags.SelectContext(ctx, &book.Tags, book.ID)
		if err != nil && err != sql.ErrNoRows {
			return nil, errors.WithStack(err)
		}

		bookmarks[i] = book
	}

	return bookmarks, nil
}

// GetBookmarksCount fetch count of bookmarks based on submitted options.
func (db *MySQLDatabase) GetBookmarksCount(ctx context.Context, opts GetBookmarksOptions) (int, error) {
	// Create initial query
	query := `SELECT COUNT(id) FROM bookmark WHERE 1`

	// Add where clause
	args := []interface{}{}

	// Add where clause for IDs
	if len(opts.IDs) > 0 {
		query += ` AND id IN (?)`
		args = append(args, opts.IDs)
	}

	// Add where clause for search keyword
	if opts.Keyword != "" {
		query += ` AND (
			url LIKE ? OR
			MATCH(title, excerpt, content) AGAINST (? IN BOOLEAN MODE)
		)`

		args = append(args,
			"%"+opts.Keyword+"%",
			opts.Keyword)
	}

	// Add where clause for tags.
	// First we check for * in excluded and included tags,
	// which means all tags will be excluded and included, respectively.
	excludeAllTags := false
	for _, excludedTag := range opts.ExcludedTags {
		if excludedTag == "*" {
			excludeAllTags = true
			opts.ExcludedTags = []string{}
			break
		}
	}

	includeAllTags := false
	for _, includedTag := range opts.Tags {
		if includedTag == "*" {
			includeAllTags = true
			opts.Tags = []string{}
			break
		}
	}

	// If all tags excluded, we will only show bookmark without tags.
	// In other hand, if all tags included, we will only show bookmark with tags.
	if excludeAllTags {
		query += ` AND id NOT IN (SELECT DISTINCT bookmark_id FROM bookmark_tag)`
	} else if includeAllTags {
		query += ` AND id IN (SELECT DISTINCT bookmark_id FROM bookmark_tag)`
	}

	// Now we only need to find the normal tags
	if len(opts.Tags) > 0 {
		query += ` AND id IN (
			SELECT bt.bookmark_id
			FROM bookmark_tag bt
			LEFT JOIN tag t ON bt.tag_id = t.id
			WHERE t.name IN(?)
			GROUP BY bt.bookmark_id
			HAVING COUNT(bt.bookmark_id) = ?)`

		args = append(args, opts.Tags, len(opts.Tags))
	}

	if len(opts.ExcludedTags) > 0 {
		query += ` AND id NOT IN (
			SELECT DISTINCT bt.bookmark_id
			FROM bookmark_tag bt
			LEFT JOIN tag t ON bt.tag_id = t.id
			WHERE t.name IN(?))`

		args = append(args, opts.ExcludedTags)
	}

	// Expand query, because some of the args might be an array
	query, args, err := sqlx.In(query, args...)
	if err != nil {
		return 0, errors.WithStack(err)
	}

	// Fetch count
	var nBookmarks int
	err = db.GetContext(ctx, &nBookmarks, query, args...)
	if err != nil && err != sql.ErrNoRows {
		return 0, errors.WithStack(err)
	}

	return nBookmarks, nil
}

// DeleteBookmarks removes all record with matching ids from database.
func (db *MySQLDatabase) DeleteBookmarks(ctx context.Context, ids ...int) (err error) {
	if err := db.withTx(ctx, func(tx *sqlx.Tx) error {
		// Prepare queries
		delBookmark := `DELETE FROM bookmark`
		delBookmarkTag := `DELETE FROM bookmark_tag`

		// Delete bookmark(s)
		if len(ids) == 0 {
			_, err := tx.ExecContext(ctx, delBookmarkTag)
			if err != nil {
				return errors.WithStack(err)
			}

			_, err = tx.ExecContext(ctx, delBookmark)
			if err != nil {
				return errors.WithStack(err)
			}
		} else {
			delBookmark += ` WHERE id = ?`
			delBookmarkTag += ` WHERE bookmark_id = ?`

			stmtDelBookmark, _ := tx.Preparex(delBookmark)
			stmtDelBookmarkTag, _ := tx.Preparex(delBookmarkTag)

			for _, id := range ids {
				_, err := stmtDelBookmarkTag.ExecContext(ctx, id)
				if err != nil {
					return errors.WithStack(err)
				}

				_, err = stmtDelBookmark.ExecContext(ctx, id)
				if err != nil {
					return errors.WithStack(err)
				}
			}
		}

		return nil
	}); err != nil {
		return errors.WithStack(err)
	}

	return nil
}

// GetBookmark fetches bookmark based on its ID or URL.
// Returns the bookmark and boolean whether it's exist or not.
func (db *MySQLDatabase) GetBookmark(ctx context.Context, id int, url string) (model.Bookmark, bool, error) {
	args := []interface{}{id}
	query := `SELECT
		id, url, title, excerpt, author, public,
		content, html, modified, content <> '' has_content
		FROM bookmark WHERE id = ?`

	if url != "" {
		query += ` OR url = ?`
		args = append(args, url)
	}

	book := model.Bookmark{}
	if err := db.GetContext(ctx, &book, query, args...); err != nil && err != sql.ErrNoRows {
		return book, false, errors.WithStack(err)
	}

	return book, book.ID != 0, nil
}

// SaveAccount saves new account to database. Returns error if any happened.
func (db *MySQLDatabase) SaveAccount(ctx context.Context, account model.Account) (err error) {
	// Hash password with bcrypt
	hashedPassword, err := bcrypt.GenerateFromPassword([]byte(account.Password), 10)
	if err != nil {
		return errors.WithStack(err)
	}

	// Insert account to database
	_, err = db.ExecContext(ctx, `INSERT INTO account
		(username, password, owner, config) VALUES (?, ?, ?, ?)
		ON DUPLICATE KEY UPDATE
		password = VALUES(password),
		owner = VALUES(owner)`,
		account.Username, hashedPassword, account.Owner, account.Config)

	return errors.WithStack(err)
}

// SaveAccountSettings update settings for specific account  in database. Returns error if any happened
func (db *MySQLDatabase) SaveAccountSettings(ctx context.Context, account model.Account) (err error) {
	// Update account config in database for specific user
	_, err = db.ExecContext(ctx, `UPDATE account
		SET config = ?
		WHERE username = ?`,
		account.Config, account.Username)

	return errors.WithStack(err)
}

// GetAccounts fetch list of account (without its password) based on submitted options.
func (db *MySQLDatabase) GetAccounts(ctx context.Context, opts GetAccountsOptions) ([]model.Account, error) {
	// Create query
	args := []interface{}{}
	query := `SELECT id, username, owner, config FROM account WHERE 1`

	if opts.Keyword != "" {
		query += " AND username LIKE ?"
		args = append(args, "%"+opts.Keyword+"%")
	}

	if opts.Owner {
		query += " AND owner = 1"
	}

	query += ` ORDER BY username`

	// Fetch list account
	accounts := []model.Account{}
	err := db.SelectContext(ctx, &accounts, query, args...)
	if err != nil && err != sql.ErrNoRows {
		return nil, errors.WithStack(err)
	}

	return accounts, nil
}

// GetAccount fetch account with matching username.
// Returns the account and boolean whether it's exist or not.
func (db *MySQLDatabase) GetAccount(ctx context.Context, username string) (model.Account, bool, error) {
	account := model.Account{}
	if err := db.GetContext(ctx, &account, `SELECT
		id, username, password, owner, config FROM account WHERE username = ?`,
		username,
	); err != nil {
		return account, false, errors.WithStack(err)
	}

	return account, account.ID != 0, nil
}

// DeleteAccounts removes all record with matching usernames.
func (db *MySQLDatabase) DeleteAccounts(ctx context.Context, usernames ...string) error {
	if err := db.withTx(ctx, func(tx *sqlx.Tx) error {
		// Delete account
		stmtDelete, _ := tx.Preparex(`DELETE FROM account WHERE username = ?`)
		for _, username := range usernames {
			_, err := stmtDelete.ExecContext(ctx, username)
			if err != nil {
				return errors.WithStack(err)
			}
		}

		return nil
	}); err != nil {
		return errors.WithStack(err)
	}

	return nil
}

// CreateTags creates new tags from submitted objects.
func (db *MySQLDatabase) CreateTags(ctx context.Context, tags ...model.Tag) error {
	query := `INSERT INTO tag (name) VALUES `
	values := []interface{}{}

	for _, t := range tags {
		query += "(?),"
		values = append(values, t.Name)
	}
	query = query[0 : len(query)-1]

	if err := db.withTx(ctx, func(tx *sqlx.Tx) error {
		stmt, err := tx.Preparex(query)
		if err != nil {
			return errors.Wrap(errors.WithStack(err), "error preparing query")
		}

		_, err = stmt.ExecContext(ctx, values...)
		if err != nil {
			return errors.Wrap(errors.WithStack(err), "error executing query")
		}

		return nil
	}); err != nil {
		return errors.Wrap(errors.WithStack(err), "error running transaction")
	}

	return nil
}

// GetTags fetch list of tags and their frequency.
func (db *MySQLDatabase) GetTags(ctx context.Context) ([]model.Tag, error) {
	tags := []model.Tag{}
	query := `SELECT bt.tag_id id, t.name, COUNT(bt.tag_id) n_bookmarks
		FROM bookmark_tag bt
		LEFT JOIN tag t ON bt.tag_id = t.id
		GROUP BY bt.tag_id ORDER BY t.name`

	err := db.SelectContext(ctx, &tags, query)
	if err != nil && err != sql.ErrNoRows {
		return nil, errors.WithStack(err)
	}

	return tags, nil
}

// RenameTag change the name of a tag.
func (db *MySQLDatabase) RenameTag(ctx context.Context, id int, newName string) error {
	err := db.withTx(ctx, func(tx *sqlx.Tx) error {
		_, err := db.ExecContext(ctx, `UPDATE tag SET name = ? WHERE id = ?`, newName, id)
		return errors.WithStack(err)
	})

	return errors.WithStack(err)
}


code:
package database

import (
	"context"
	"log"
	"os"
	"path/filepath"
	"testing"

	"github.com/go-shiori/shiori/internal/model"
	"github.com/golang-migrate/migrate/v4"
	"github.com/jmoiron/sqlx"
	"github.com/pkg/errors"
	"github.com/stretchr/testify/assert"
)

func init() {
	connString := os.Getenv("SHIORI_TEST_MYSQL_URL")
	if connString == "" {
		log.Fatal("mysql tests can't run without a MysQL database, set SHIORI_TEST_MYSQL_URL environment variable")
	}
}

func mysqlTestDatabaseFactory(ctx context.Context) (DB, error) {
	connString := os.Getenv("SHIORI_TEST_MYSQL_URL")
	db, err := OpenMySQLDatabase(ctx, connString)
	if err != nil {
		return nil, err
	}

	var dbname string
	err = db.withTx(ctx, func(tx *sqlx.Tx) error {
		err := tx.QueryRow("SELECT DATABASE()").Scan(&dbname)
		if err != nil {
			return err
		}

		_, err = tx.ExecContext(ctx, "DROP DATABASE IF EXISTS "+dbname)
		if err != nil {
			return err
		}

		_, err = tx.ExecContext(ctx, "CREATE DATABASE "+dbname)
		return err
	})
	if err != nil {
		return nil, err
	}

	if _, err := db.Exec("USE " + dbname); err != nil {
		return nil, err
	}

	if err = db.Migrate(); err != nil && !errors.Is(migrate.ErrNoChange, err) {
		return nil, err
	}

	return db, err
}

func TestMysqlsDatabase(t *testing.T) {
	testDatabase(t, mysqlTestDatabaseFactory)
}

func TestSaveAccountSettingsMySql(t *testing.T) {
	ctx := context.TODO()

	db, err := mysqlTestDatabaseFactory(ctx)
	assert.NoError(t, err)

	// Mock data
	account := model.Account{
		Username: "testuser",
		Config:   model.UserConfig{},
	}

	// Successful case
	err = db.SaveAccountSettings(ctx, account)
	assert.NoError(t, err)

	// Initialize not correct database
	ctx = context.TODO()
	factory := func(ctx context.Context) (DB, error) {
		return OpenSQLiteDatabase(ctx, filepath.Join(os.TempDir(), "shiori_test.db"))
	}
	db, err = factory(ctx)
	assert.Nil(t, err)
	account = model.Account{
		Username: "testuser",
		Config:   model.UserConfig{},
	}
	err = db.SaveAccountSettings(ctx, account)
	assert.NotNil(t, err)
	assert.Contains(t, err.Error(), "SQL logic error: no such table: account (1)")
}

func TestGetAccountsMySql(t *testing.T) {
	ctx := context.TODO()

	db, err := mysqlTestDatabaseFactory(ctx)
	assert.NoError(t, err)

	// Insert test accounts
	testAccounts := []model.Account{
		{Username: "foo", Password: "bar", Owner: false},
		{Username: "hello", Password: "world", Owner: false},
		{Username: "foo_bar", Password: "foobar", Owner: true},
	}
	for _, acc := range testAccounts {
		err := db.SaveAccount(ctx, acc)
		assert.Nil(t, err)
	}

	// Successful case
	// without opt
	accounts, err := db.GetAccounts(ctx, GetAccountsOptions{})
	assert.NoError(t, err)
	assert.Equal(t, 3, len(accounts))
	// with owner
	accounts, err = db.GetAccounts(ctx, GetAccountsOptions{Owner: true})
	assert.NoError(t, err)
	assert.Equal(t, 1, len(accounts))
	// with opt
	accounts, err = db.GetAccounts(ctx, GetAccountsOptions{Keyword: "foo"})
	assert.NoError(t, err)
	assert.Equal(t, 2, len(accounts))
	// with opt and owner
	accounts, err = db.GetAccounts(ctx, GetAccountsOptions{Keyword: "hello", Owner: false})
	assert.NoError(t, err)
	assert.Equal(t, 1, len(accounts))
	// with not result
	accounts, err = db.GetAccounts(ctx, GetAccountsOptions{Keyword: "shiori"})
	assert.NoError(t, err)
	assert.Equal(t, 0, len(accounts))

	// Initialize not correct database
	ctx = context.TODO()
	factory := func(ctx context.Context) (DB, error) {
		return OpenSQLiteDatabase(ctx, filepath.Join(os.TempDir(), "shiori_test.db"))
	}
	db, err = factory(ctx)
	assert.Nil(t, err)
	// with invalid query
	opts := GetAccountsOptions{Keyword: "foo", Owner: true}
	_, err = db.GetAccounts(ctx, opts)
	assert.NotNil(t, err)
	assert.Contains(t, err.Error(), "SQL logic error: no such table: account (1)")
}

func TestGetAccountMySql(t *testing.T) {
	ctx := context.TODO()

	db, err := mysqlTestDatabaseFactory(ctx)
	assert.NoError(t, err)

	// Insert test accounts
	testAccounts := []model.Account{
		{Username: "foo", Password: "bar", Owner: false},
		{Username: "hello", Password: "world", Owner: false},
		{Username: "foo_bar", Password: "foobar", Owner: true},
	}
	for _, acc := range testAccounts {
		err := db.SaveAccount(ctx, acc)
		assert.Nil(t, err)

		// Successful case
		account, exists, err := db.GetAccount(ctx, acc.Username)
		assert.Nil(t, err)
		assert.True(t, exists, "Expected account to exist")
		assert.Equal(t, acc.Username, account.Username)
	}
	// Falid case
	account, exists, err := db.GetAccount(ctx, "foobar")
	assert.NotNil(t, err)
	assert.False(t, exists, "Expected account to exist")
	assert.Empty(t, account.Username)
}