Notes2/node.go

package main

import (
	// External
	"github.com/jmoiron/sqlx"

	// Standard
	"database/sql"
	"time"
)

type ChecklistItem struct {
	ID      int
	GroupID int `db:"checklist_group_id"`
	Order   int
	Label   string
	Checked bool
}

type ChecklistGroup struct {
	ID     int
	NodeID int `db:"node_id"`
	Order  int
	Label  string
	Items  []ChecklistItem
}

type TreeNode struct {
	UUID       string
	ParentUUID string `db:"parent_uuid"`
	Name       string
	Created    time.Time
	Updated    time.Time
	Deleted    bool
	CreatedSeq uint64        `db:"created_seq"`
	UpdatedSeq uint64        `db:"updated_seq"`
	DeletedSeq sql.NullInt64 `db:"deleted_seq"`
}

type Node struct {
	UUID             string
	UserID           int    `db:"user_id"`
	ParentUUID       string `db:"parent_uuid"`
	Name             string
	Created          time.Time
	Updated          time.Time
	Deleted          bool
	CreatedSeq       uint64        `db:"created_seq"`
	UpdatedSeq       uint64        `db:"updated_seq"`
	DeletedSeq       sql.NullInt64 `db:"deleted_seq"`
	Content          string
	ContentEncrypted string `db:"content_encrypted" json:"-"`
	Markdown         bool

	// CryptoKeyID    int    `db:"crypto_key_id"`
	//Files           []File
	//ChecklistGroups []ChecklistGroup
}

func NodeTree(userID, offset int, synced uint64) (nodes []TreeNode, maxSeq uint64, moreRowsExist bool, err error) { // {{{
	const LIMIT = 100
	var rows *sqlx.Rows
	rows, err = db.Queryx(`
		SELECT
			uuid,
			COALESCE(parent_uuid, '') AS parent_uuid,
			name,
			created,
			updated,
			deleted IS NOT NULL AS deleted,
			created_seq,
			updated_seq,
			deleted_seq
		FROM
			public.node
		WHERE
			user_id = $1 AND 
			NOT history AND (
				created_seq > $4 OR
				updated_seq > $4 OR
				deleted_seq > $4
			)
		ORDER BY
			created ASC
		LIMIT $2 OFFSET $3
		`,
		userID,
		LIMIT+1,
		offset,
		synced,
	)
	if err != nil {
		return
	}
	defer rows.Close()

	nodes = []TreeNode{}
	numNodes := 0
	for rows.Next() {
		// Query selects up to one more row than the decided limit.
		// Saves one SQL query for row counting.
		// Thus if numNodes is larger than the limit, more rows exist for the next call.
		numNodes++
		if numNodes > LIMIT {
			moreRowsExist = true
			return
		}

		node := TreeNode{}
		if err = rows.StructScan(&node); err != nil {
			return
		}
		nodes = append(nodes, node)

		// DeletedSeq will be 0 if invalid, and thus not be a problem for the max function.
		maxSeq = max(maxSeq, node.CreatedSeq, node.UpdatedSeq, uint64(node.DeletedSeq.Int64))
	}

	return
} // }}}
func Nodes(userID, offset int, synced uint64, clientUUID string) (nodes []Node, maxSeq uint64, moreRowsExist bool, err error) { // {{{
	var rows *sqlx.Rows
	rows, err = db.Queryx(`
		SELECT
			uuid,
			COALESCE(parent_uuid, '') AS parent_uuid,
			name,
			created,
			updated,
			deleted IS NOT NULL AS deleted,
			created_seq,
			updated_seq,
			deleted_seq,
			content,
			content_encrypted,
			markdown
		FROM
			public.node
		WHERE
			user_id = $1 AND
			client != $5 AND
			NOT history AND (
				created_seq > $4 OR
				updated_seq > $4 OR
				deleted_seq > $4
			)
		ORDER BY
			id ASC
		LIMIT $2 OFFSET $3
		`,
		userID,
		SYNC_PAGINATION+1,
		offset,
		synced,
		clientUUID,
	)
	if err != nil {
		return
	}
	defer rows.Close()

	nodes = []Node{}
	numNodes := 0
	for rows.Next() {
		// Query selects up to one more row than the decided limit.
		// Saves one SQL query for row counting.
		// Thus if numNodes is larger than the limit, more rows exist for the next call.
		numNodes++
		if numNodes > SYNC_PAGINATION {
			moreRowsExist = true
			return
		}

		node := Node{}
		if err = rows.StructScan(&node); err != nil {
			return
		}
		nodes = append(nodes, node)

		// DeletedSeq will be 0 if invalid, and thus not be a problem for the max function.
		maxSeq = max(maxSeq, node.CreatedSeq, node.UpdatedSeq, uint64(node.DeletedSeq.Int64))
	}

	return
} // }}}
func RetrieveNode(userID int, nodeUUID string) (node Node, err error) { // {{{
	var rows *sqlx.Row
	rows = db.QueryRowx(`
		SELECT
			uuid,
			user_id,
			COALESCE(parent_uuid, '') AS parent_uuid,
			/*COALESCE(crypto_key_id, 0) AS crypto_key_id,*/
			name,
			content,
			content_encrypted,
			markdown,
			0 AS level
		FROM node
		WHERE
			user_id = $1 AND
			uuid = $2
		
		`,
		userID,
		nodeUUID,
	)
	node = Node{}
	if err = rows.StructScan(&node); err != nil {
		return
	}

	return
} // }}}
func NodeCrumbs(nodeUUID string) (nodes []Node, err error) { // {{{
	var rows *sqlx.Rows
	rows, err = db.Queryx(`
		WITH RECURSIVE nodes AS (
			SELECT
				uuid,
				COALESCE(parent_uuid, '') AS parent_uuid,
				name
			FROM node
			WHERE
				uuid = $1

			UNION
			
			SELECT
				n.uuid,
				COALESCE(n.parent_uuid, 0) AS parent_uuid,
				n.name
			FROM node n
			INNER JOIN nodes nr ON n.uuid = nr.parent_uuid
		)
		SELECT * FROM nodes
	`, nodeUUID)
	if err != nil {
		return
	}
	defer rows.Close()

	nodes = []Node{}
	for rows.Next() {
		node := Node{}
		if err = rows.StructScan(&node); err != nil {
			return
		}
		nodes = append(nodes, node)
	}
	return
} // }}}

func TestData() (err error) {
	for range 10 {
		hash1, name1, _ := generateOneTestNode("", "G")
		for range 10 {
			hash2, name2, _ := generateOneTestNode(hash1, name1)
			for range 10 {
				hash3, name3, _ := generateOneTestNode(hash2, name2)
				for range 10 {
					generateOneTestNode(hash3, name3)
				}
			}

		}
	}
	return
}

func generateOneTestNode(parentUUID, parentPath string) (hash, name string, err error) {
	var sqlParentUUID sql.NullString
	if parentUUID != "" {
		sqlParentUUID.String = parentUUID
		sqlParentUUID.Valid = true
	}
	query := `
		INSERT INTO node(user_id, parent_uuid, name)
		VALUES(
			1,
			$1,
			CONCAT(
				$2::text,
				'-',
				LPAD(nextval('test_data')::text, 4, '0')
			)
		)
		RETURNING uuid, name`

	var row *sql.Row
	row = db.QueryRow(query, sqlParentUUID, parentPath)
	err = row.Scan(&hash, &name)
	if err != nil {
		return
	}

	return
}