382 lines
9.1 KiB
Go
382 lines
9.1 KiB
Go
package models
|
|
|
|
import (
|
|
"database/sql"
|
|
"errors"
|
|
"html/template"
|
|
"strings"
|
|
"time"
|
|
)
|
|
|
|
// Page type used to map data from DB
|
|
type Page struct {
|
|
ID int
|
|
Name string
|
|
Slug string
|
|
Host int
|
|
Category string
|
|
Summary string
|
|
Body string
|
|
Html template.HTML
|
|
Created time.Time
|
|
Updated time.Time
|
|
Private int
|
|
Menu bool
|
|
Feed bool
|
|
ChildrenMenu []ChildrenMenu
|
|
BreadCrumb []BreadCrumbItem
|
|
}
|
|
|
|
type ChildrenMenu struct {
|
|
ID int
|
|
Name string
|
|
Slug string
|
|
Summary string
|
|
Created time.Time
|
|
Private int
|
|
}
|
|
|
|
type BreadCrumbItem struct {
|
|
ID int
|
|
Name string
|
|
Slug string
|
|
Host int
|
|
Depth int
|
|
}
|
|
|
|
// PageModel type that wraps a DB connection pool
|
|
type PageModel struct {
|
|
DB *sql.DB
|
|
}
|
|
|
|
// Insert new page in DB
|
|
func (m *PageModel) Insert(name string, slug string, host int, category string, summary string, body string, created time.Time, updated time.Time, private int, menu bool, feed bool) error {
|
|
// Write the query in a string format before executing it
|
|
// The ? are placeholders to avoid interpolating data in the SQL query
|
|
stmt := `INSERT INTO pages (name, slug, host, category, summary, body, created, updated, private, menu, feed) VALUES( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`
|
|
|
|
_, err := m.DB.Exec(stmt, name, slug, host, category, summary, body, created, updated, private, menu, feed)
|
|
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
return err
|
|
}
|
|
|
|
// Update page in DB
|
|
func (m *PageModel) Update(name string, slug string, host int, category string, summary string, body string, created time.Time, updated time.Time, private int, menu bool, feed bool, id int) error {
|
|
// Write the query in a string format before executing it
|
|
// The ? are placeholders to avoid interpolating data in the SQL query
|
|
stmt := `UPDATE pages SET name = ?, slug = ?, host = ?, category = ?, summary = ?, body = ?, created = ?, updated = ?, private = ?, menu = ?, feed = ? WHERE id = ?`
|
|
|
|
_, err := m.DB.Exec(stmt, name, slug, host, category, summary, body, created, updated, private, menu, feed, id)
|
|
|
|
if err != nil {
|
|
return err
|
|
}
|
|
|
|
return err
|
|
}
|
|
|
|
func (m *PageModel) Delete(id int) error {
|
|
stmt := `DELETE FROM pages WHERE id = ?`
|
|
_, err := m.DB.Exec(stmt, id)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
return err
|
|
}
|
|
|
|
// Get page based on Slug
|
|
func (m *PageModel) Get(auth bool, slug string) (Page, error) {
|
|
// Write the query and get the row
|
|
var stmt = ""
|
|
if auth {
|
|
stmt = `SELECT id, name, slug, host, category, summary, body, created, updated, private, menu, feed FROM pages WHERE slug = ?`
|
|
} else {
|
|
stmt = `SELECT id, name, slug, host, category, summary, body, created, updated, private, menu, feed FROM pages WHERE private != 2 AND slug = ?`
|
|
}
|
|
row := m.DB.QueryRow(stmt, slug)
|
|
|
|
// Declare the page and map the values from DB
|
|
var p Page
|
|
err := row.Scan(&p.ID, &p.Name, &p.Slug, &p.Host, &p.Category, &p.Summary, &p.Body, &p.Created, &p.Updated, &p.Private, &p.Menu, &p.Feed)
|
|
|
|
if err != nil {
|
|
if errors.Is(err, sql.ErrNoRows) {
|
|
return Page{}, ErrNoRecord
|
|
} else {
|
|
return Page{}, err
|
|
}
|
|
}
|
|
|
|
return p, nil
|
|
}
|
|
|
|
// Get 10 most recently created pages
|
|
func (m *PageModel) Latest() ([]Page, error) {
|
|
stmt := `SELECT id, name, slug, host, category, summary, body, created, updated, private, menu, feed FROM pages WHERE private != 2 ORDER BY created DESC LIMIT 10`
|
|
rows, err := m.DB.Query(stmt)
|
|
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
defer rows.Close()
|
|
|
|
var pages []Page
|
|
|
|
for rows.Next() {
|
|
var p Page
|
|
err := rows.Scan(&p.ID, &p.Name, &p.Slug, &p.Host, &p.Category, &p.Summary, &p.Body, &p.Created, &p.Updated, &p.Private, &p.Menu, &p.Feed)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
pages = append(pages, p)
|
|
}
|
|
|
|
return pages, nil
|
|
}
|
|
|
|
// Check if a page with a specific slug exists and returns a bool
|
|
func (m *PageModel) SlugIsFree(slug string, id int) (bool, error) {
|
|
// Write the query and get the row
|
|
stmt := `SELECT slug, id FROM pages WHERE slug = ?`
|
|
row := m.DB.QueryRow(stmt, slug)
|
|
|
|
var p Page
|
|
err := row.Scan(&p.Slug, &p.ID)
|
|
|
|
if err != nil {
|
|
if errors.Is(err, sql.ErrNoRows) {
|
|
return true, nil
|
|
} else {
|
|
return true, err
|
|
}
|
|
}
|
|
|
|
if id > 0 && p.ID == id {
|
|
return true, nil
|
|
}
|
|
|
|
return false, nil
|
|
}
|
|
|
|
// Get a list of pages with their name and slugs
|
|
func (m *PageModel) NamesAndSlugs() ([]Page, error) {
|
|
stmt := `SELECT id, name, slug FROM pages WHERE private != 2`
|
|
rows, err := m.DB.Query(stmt)
|
|
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
defer rows.Close()
|
|
|
|
var pages []Page
|
|
|
|
for rows.Next() {
|
|
var p Page
|
|
err := rows.Scan(&p.ID, &p.Name, &p.Slug)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
pages = append(pages, p)
|
|
}
|
|
|
|
return pages, nil
|
|
}
|
|
|
|
func (m *PageModel) BuildBreadCrumb(id int) ([]BreadCrumbItem, error) {
|
|
stmt := `WITH RECURSIVE ancestors AS (
|
|
SELECT id, name, host, slug, 0 as depth
|
|
FROM pages
|
|
WHERE id = ?
|
|
|
|
UNION ALL
|
|
|
|
SELECT p.id, p.name, p.host, p.slug, a.depth + 1
|
|
FROM pages p
|
|
JOIN ancestors a ON a.host = p.id AND a.host != 0
|
|
)
|
|
SELECT * FROM ancestors ORDER BY depth DESC;`
|
|
|
|
rows, err := m.DB.Query(stmt, id)
|
|
|
|
if err != nil {
|
|
return []BreadCrumbItem{}, err
|
|
}
|
|
|
|
defer rows.Close()
|
|
|
|
var pages []BreadCrumbItem
|
|
for rows.Next() {
|
|
page := BreadCrumbItem{}
|
|
if err := rows.Scan(&page.ID, &page.Name, &page.Host, &page.Slug, &page.Depth); err != nil {
|
|
return nil, err
|
|
}
|
|
pages = append(pages, page)
|
|
}
|
|
|
|
return pages, rows.Err()
|
|
|
|
}
|
|
|
|
// Insert pages in batch for the migration
|
|
func (m *PageModel) BatchInsertPages(pages []Page) error {
|
|
|
|
stmt := `INSERT INTO pages (ID, name, slug, host, category, summary, body, created, updated, private, menu, feed) VALUES `
|
|
|
|
// Create placeholders for each row
|
|
placeholders := make([]string, len(pages))
|
|
values := make([]interface{}, 0, len(pages)*12)
|
|
|
|
for i, page := range pages {
|
|
placeholders[i] = "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
|
|
values = append(values, page.ID, page.Name, page.Slug, page.Host, page.Category, page.Summary, page.Body, page.Created, page.Updated, page.Private, page.Menu, page.Feed)
|
|
}
|
|
|
|
stmt += strings.Join(placeholders, ", ")
|
|
|
|
_, err := m.DB.Exec(stmt, values...)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
return err
|
|
}
|
|
|
|
func (m *PageModel) GetChildrenMenu(auth bool, parentID int) ([]ChildrenMenu, error) {
|
|
|
|
var stmt = ""
|
|
if auth {
|
|
stmt = `SELECT id, name, slug, host, summary, private, created FROM pages WHERE host = ? ORDER BY created DESC`
|
|
} else {
|
|
stmt = `SELECT id, name, slug, host, summary, private, created FROM pages WHERE private != 2 AND host = ? ORDER BY created DESC`
|
|
}
|
|
pages, err := m.DB.Query(stmt, parentID)
|
|
|
|
if err != nil {
|
|
if errors.Is(err, sql.ErrNoRows) {
|
|
return []ChildrenMenu{}, ErrNoRecord
|
|
} else {
|
|
return []ChildrenMenu{}, err
|
|
}
|
|
}
|
|
|
|
var childrenMenu []ChildrenMenu
|
|
for pages.Next() {
|
|
var cm ChildrenMenu
|
|
err := pages.Scan(&cm.ID, &cm.Name, &cm.Slug, new(int), &cm.Summary, &cm.Private, &cm.Created)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
childrenMenu = append(childrenMenu, cm)
|
|
}
|
|
pages.Close()
|
|
return childrenMenu, nil
|
|
}
|
|
|
|
func (m *PageModel) GetHostSlug(id int) (Page, error) {
|
|
// Write the query and get the row
|
|
stmt := `SELECT slug FROM pages WHERE id = ?`
|
|
row := m.DB.QueryRow(stmt, id)
|
|
|
|
// Declare the page and map the values from DB
|
|
var p Page
|
|
err := row.Scan(&p.Slug)
|
|
|
|
if err != nil {
|
|
if errors.Is(err, sql.ErrNoRows) {
|
|
return Page{}, ErrNoRecord
|
|
} else {
|
|
return Page{}, err
|
|
}
|
|
}
|
|
|
|
return p, nil
|
|
}
|
|
|
|
// Get a list of pages with their id, name, slug and host
|
|
func (m *PageModel) IdNameSlugHost(auth bool) ([]Page, error) {
|
|
var stmt = ""
|
|
if auth {
|
|
stmt = `SELECT id, name, slug, host FROM pages`
|
|
} else {
|
|
stmt = `SELECT id, name, slug, host FROM pages WHERE private == 0`
|
|
}
|
|
rows, err := m.DB.Query(stmt)
|
|
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
defer rows.Close()
|
|
|
|
var pages []Page
|
|
|
|
for rows.Next() {
|
|
var p Page
|
|
err := rows.Scan(&p.ID, &p.Name, &p.Slug, &p.Host)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
pages = append(pages, p)
|
|
}
|
|
|
|
return pages, nil
|
|
}
|
|
|
|
// Get all public pages
|
|
func (m *PageModel) GetRssFeedPages() ([]Page, error) {
|
|
// Write the query and get the row
|
|
stmt := `SELECT name, slug, summary, body, created, updated FROM pages WHERE private != 2 and feed = 1`
|
|
rows, err := m.DB.Query(stmt)
|
|
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
var pages []Page
|
|
|
|
for rows.Next() {
|
|
var p Page
|
|
err := rows.Scan(&p.Name, &p.Slug, &p.Summary, &p.Body, &p.Created, &p.Updated)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
pages = append(pages, p)
|
|
}
|
|
|
|
return pages, nil
|
|
}
|
|
|
|
// Search for something in pages
|
|
func (m *PageModel) SearchInPages(auth bool, search string) ([]ChildrenMenu, error) {
|
|
// Write the query and get the row
|
|
var stmt = ""
|
|
if auth {
|
|
stmt = `SELECT id, name, slug, summary FROM pages WHERE (name LIKE '%' || ? || '%' OR body LIKE '%' || ? || '%')`
|
|
} else {
|
|
stmt = `SELECT id, name, slug, summary FROM pages WHERE (name LIKE '%' || ? || '%' OR body LIKE '%' || ? || '%') AND private == 0`
|
|
}
|
|
|
|
rows, err := m.DB.Query(stmt, search, search)
|
|
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
var searchResults []ChildrenMenu
|
|
|
|
for rows.Next() {
|
|
var p ChildrenMenu
|
|
err := rows.Scan(&p.ID, &p.Name, &p.Slug, &p.Summary)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
searchResults = append(searchResults, p)
|
|
}
|
|
|
|
return searchResults, nil
|
|
}
|