Notes/node.go

package main

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

	// Standard
	"time"
)

type Node struct {
	ID          int
	UserID      int `db:"user_id"`
	ParentID    int `db:"parent_id"`
	CryptoKeyID int `db:"crypto_key_id"`
	Name        string
	Content     string
	Updated     time.Time
	Children    []Node
	Crumbs      []Node
	Files       []File
	Complete    bool
	Level       int

	ContentEncrypted string `db:"content_encrypted" json:"-"`
	Markdown         bool
}

func NodeTree(userID, startNodeID int) (nodes []Node, err error) { // {{{
	var rows *sqlx.Rows
	rows, err = service.Db.Conn.Queryx(`
		WITH RECURSIVE nodetree AS (
			SELECT
				*,
				array[name::text] AS path,
				0 AS level
			FROM node
			WHERE
				user_id = $1 AND
				CASE $2::int
					WHEN 0 THEN parent_id IS NULL
					ELSE        parent_id = $2
				END
			
			UNION ALL			

			SELECT
				n.*,
				path||n.name::text AS path,
				nt.level + 1 AS level
			FROM node n
			INNER JOIN nodetree nt ON n.parent_id = nt.id
		)

		SELECT
			id,
			user_id,
			COALESCE(parent_id, 0) AS parent_id,
			name,
			updated,
			level
		FROM nodetree
		ORDER BY
			path ASC
		`,
		userID,
		startNodeID,
	)
	if err != nil {
		return
	}
	defer rows.Close()

	type resultRow struct {
		Node
		Level int
	}

	nodes = []Node{}
	for rows.Next() {
		node := Node{}
		node.Complete = false
		node.Crumbs = []Node{}
		node.Children = []Node{}
		node.Files = []File{}
		if err = rows.StructScan(&node); err != nil {
			return
		}
		nodes = append(nodes, node)
	}

	return
} // }}}
func RootNode(userID int) (node Node, err error) { // {{{
	var rows *sqlx.Rows
	rows, err = service.Db.Conn.Queryx(`
			SELECT
				id,
				user_id,
				0 AS parent_id,
				name
			FROM node
			WHERE
				user_id = $1 AND
				parent_id IS NULL
		`,
		userID,
	)
	if err != nil {
		return
	}
	defer rows.Close()

	node.Name = "Start"
	node.UserID = userID
	node.Complete = true
	node.Children = []Node{}
	node.Crumbs = []Node{}
	node.Files = []File{}
	for rows.Next() {
		row := Node{}
		if err = rows.StructScan(&row); err != nil {
			return
		}

		node.Children = append(node.Children, Node{
			ID:       row.ID,
			UserID:   row.UserID,
			ParentID: row.ParentID,
			Name:     row.Name,
		})
	}

	return
} // }}}
func RetrieveNode(userID, nodeID int) (node Node, err error) { // {{{
	if nodeID == 0 {
		return RootNode(userID)
	}

	var rows *sqlx.Rows
	rows, err = service.Db.Conn.Queryx(`
		WITH RECURSIVE recurse AS (
			SELECT
				id,
				user_id,
				COALESCE(parent_id, 0) AS parent_id,
				COALESCE(crypto_key_id, 0) AS crypto_key_id,
				name,
				content,
				content_encrypted,
				markdown,
				0 AS level
			FROM node
			WHERE
				user_id = $1 AND
				id = $2
			
			UNION
			
			SELECT
				n.id,
				n.user_id,
				n.parent_id,
				COALESCE(n.crypto_key_id, 0) AS crypto_key_id,
				n.name,
				'' AS content,
				'' AS content_encrypted,
				false AS markdown,
				r.level + 1 AS level
			FROM node n
			INNER JOIN recurse r ON n.parent_id = r.id AND r.level = 0
			WHERE
				n.user_id = $1
		)

		SELECT * FROM recurse ORDER BY level ASC
		`,
		userID,
		nodeID,
	)
	if err != nil {
		return
	}
	defer rows.Close()

	type resultRow struct {
		Node
		Level int
	}

	node = Node{}
	node.Children = []Node{}
	for rows.Next() {
		row := resultRow{}
		if err = rows.StructScan(&row); err != nil {
			return
		}

		if row.Level == 0 {
			node.ID = row.ID
			node.UserID = row.UserID
			node.ParentID = row.ParentID
			node.CryptoKeyID = row.CryptoKeyID
			node.Name = row.Name
			node.Complete = true
			node.Markdown = row.Markdown

			if node.CryptoKeyID > 0 {
				node.Content = row.ContentEncrypted
			} else {
				node.Content = row.Content
			}
		}

		if row.Level == 1 {
			node.Children = append(node.Children, Node{
				ID:          row.ID,
				UserID:      row.UserID,
				ParentID:    row.ParentID,
				CryptoKeyID: row.CryptoKeyID,
				Name:        row.Name,
			})
		}
	}

	node.Crumbs, err = NodeCrumbs(node.ID)
	node.Files, err = Files(userID, node.ID, 0)

	return
} // }}}
func NodeCrumbs(nodeID int) (nodes []Node, err error) { // {{{
	var rows *sqlx.Rows
	rows, err = service.Db.Conn.Queryx(`
		WITH RECURSIVE nodes AS (
			SELECT
				id,
				COALESCE(parent_id, 0) AS parent_id,
				name
			FROM node
			WHERE
				id = $1

			UNION
			
			SELECT
				n.id,
				COALESCE(n.parent_id, 0) AS parent_id,
				n.name
			FROM node n
			INNER JOIN nodes nr ON n.id = nr.parent_id
		)
		SELECT * FROM nodes
	`, nodeID)
	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 CreateNode(userID, parentID int, name string) (node Node, err error) { // {{{
	var rows *sqlx.Rows

	rows, err = service.Db.Conn.Queryx(`
		INSERT INTO node(user_id, parent_id, name)
		VALUES($1, NULLIF($2, 0)::integer, $3)
		RETURNING
			id,
			user_id,
			COALESCE(parent_id, 0) AS parent_id,
			name,
			content
		`,
		userID,
		parentID,
		name,
	)
	if err != nil {
		return
	}
	defer rows.Close()

	for rows.Next() {
		node = Node{}
		if err = rows.StructScan(&node); err != nil {
			return
		}
		node.Children = []Node{}
		node.Files = []File{}
		node.Complete = true
	}

	node.Crumbs, err = NodeCrumbs(node.ID)
	return
} // }}}
func UpdateNode(userID, nodeID int, content string, cryptoKeyID int, markdown bool) (err error) { // {{{
	if cryptoKeyID > 0 {
		_, err = service.Db.Conn.Exec(`
			UPDATE node
			SET
				content = '',
				content_encrypted = $1,
				markdown = $5,
				crypto_key_id = CASE $2::int
					WHEN 0 THEN NULL
					ELSE $2
				END
			WHERE
				id = $3 AND
				user_id = $4
			`,
			content,
			cryptoKeyID,
			nodeID,
			userID,
			markdown,
		)
	} else {
		_, err = service.Db.Conn.Exec(`
			UPDATE node
			SET
				content = $1,
				content_encrypted = '',
				markdown = $5,
				crypto_key_id = CASE $2::int
					WHEN 0 THEN NULL
					ELSE $2
				END
			WHERE
				id = $3 AND
				user_id = $4
			`,
			content,
			cryptoKeyID,
			nodeID,
			userID,
			markdown,
		)
	}

	return
} // }}}
func RenameNode(userID, nodeID int, name string) (err error) { // {{{
	_, err = service.Db.Conn.Exec(`
		UPDATE node SET name = $1 WHERE user_id = $2 AND id = $3
		`,
		name,
		userID,
		nodeID,
	)
	return
} // }}}
func DeleteNode(userID, nodeID int) (err error) { // {{{
	_, err = service.Db.Conn.Exec(`
		WITH RECURSIVE nodetree AS (
			SELECT
				id, parent_id
			FROM node
			WHERE
				user_id = $1 AND id = $2
				
			UNION
			
			SELECT
				n.id, n.parent_id
			FROM node n
			INNER JOIN nodetree nt ON n.parent_id = nt.id
		)

		DELETE FROM node WHERE id IN (
			SELECT id FROM nodetree
		)`,
		userID,
		nodeID,
	)
	return
} // }}}
func SearchNodes(userID int, search string) (nodes []Node, err error) { // {{{
	nodes = []Node{}
	var rows *sqlx.Rows
	rows, err = service.Db.Conn.Queryx(`
		SELECT
			id,
			user_id,
			COALESCE(parent_id, 0) AS parent_id,
			name,
			updated
		FROM node
		WHERE
			user_id = $1 AND
			crypto_key_id IS NULL AND
			(
				content ~* $2 OR
				name ~* $2
			)
		ORDER BY
			updated DESC
	`, userID, search)
	if err != nil {
		return
	}
	defer rows.Close()

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

	return
} // }}}

// vim: foldmethod=marker