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"`
	Name        string
	Content     string
	Updated     time.Time
	Children    []Node
	Crumbs      []Node
	Complete    bool
	Level       int
}

func (session Session) NodeTree(startNodeID int) (nodes []Node, err error) {// {{{
	var rows *sqlx.Rows
	rows, err = db.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
		`,
		session.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
		if err = rows.StructScan(&node); err != nil {
			return
		}
		nodes = append(nodes, node)
	}

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

	node.Name = "Start"
	node.UserID = session.UserID
	node.Complete = true
	node.Children = []Node{}
	node.Crumbs   = []Node{}
	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 (session Session) Node(nodeID int) (node Node, err error) {// {{{
	if nodeID == 0 {
		return session.RootNode()
	}

	var rows *sqlx.Rows
	rows, err = db.Queryx(`
		WITH RECURSIVE recurse AS (
			SELECT
				id,
				user_id,
				COALESCE(parent_id, 0) AS parent_id,
				name,
				content,
				0 AS level
			FROM node
			WHERE
				user_id = $1 AND
				id = $2
			
			UNION
			
			SELECT
				n.id,
				n.user_id,
				n.parent_id,
				n.name,
				'' AS content,
				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
		`,
		session.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.Name     = row.Name
			node.Content  = row.Content
			node.Complete = true
		}

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

	node.Crumbs, err = session.NodeCrumbs(node.ID)

	return
}// }}}
func (session Session) NodeCrumbs(nodeID int) (nodes []Node, err error) {// {{{
	var rows *sqlx.Rows
	rows, err = db.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 (session Session) CreateNode(parentID int, name string) (node Node, err error) {// {{{
	var rows *sqlx.Rows

	rows, err = db.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
		`,
		session.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.Complete = true
	}

	node.Crumbs, err = session.NodeCrumbs(node.ID)
	return
}// }}}
func (session Session) UpdateNode(nodeID int, content string) (err error) {// {{{
	_, err = db.Exec(`
		UPDATE node SET content = $1 WHERE user_id = $2 AND id = $3
		`,
		content,
		session.UserID,
		nodeID,
	)
	return
}// }}}
func (session Session) RenameNode(nodeID int, name string) (err error) {// {{{
	_, err = db.Exec(`
		UPDATE node SET name = $1 WHERE user_id = $2 AND id = $3
		`,
		name,
		session.UserID,
		nodeID,
	)
	return
}// }}}
func (session Session) DeleteNode(nodeID int) (err error) {// {{{
	_, err = db.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
		)`,
		session.UserID,
		nodeID,
	)
	return
}// }}}

// vim: foldmethod=marker