pyt

package module
v0.0.0-...-f6c962b Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: May 25, 2024 License: MIT Imports: 8 Imported by: 3

README

P.Y.T.

pretty young thing

An opinionated Go SQLite graph database based on simple-graph.

Opinions

  1. All data is typed
    • There is a way to use a map[string]any for properties if you really wanted to
  2. All querying is done via a transaction
  3. Both the node and edge tables have common columns:
    • id -- unique and must be explictly defined and unique to the table. I've been using a uuid but any unique string should work
    • type indexed -- the type of entity that is stored. This is a easy way to classify and segement data
    • active -- easy way to soft delete
    • properties indexed -- a json string of the key => val pairs for the entity
    • time_created and time_updated indexed -- automatically updated when its respective action is taken on the record
    • All database columns are explicit, no virtual columns whose values are derived from the properties
  4. While entities (Node[T] Edge[T]) can be manually created, it is easier to use the constructor functions (NewNode NewEdge). The only reason they arent private is to allow for extendability
  5. Create your own sqlite instance. Just make sure that you add ?_foreign_keys=true when creating it.

Tests are coming, I wanted to make sure that I liked the api before writing them

Quickstart

I'm going to show you how to build Twitter using P.Y.T. (see twitter in examples)

all error handling is omitted

  1. Set up your project
mkdir twitter
cd twitter
go mod init github.com/username/twitter
  1. Install P.Y.T.
go get github.com/emehrkay/pyt
go mod tidy
  1. Connect to sqlite and build the schema
db, err := sql.Open("sqlite3", "./twitter.db?_foreign_keys=true")
err = pyt.BuildSchema(db)
  1. Given this basic schema, we'll define some types for nodes and edges (the json tag will be the property name in the database)
  (follows)
   |     |
   |     |
   |     |
   V     |
+--------+----+                     +------------+
|             |                     |            |
|             |                     |            |
|    user     +------(wrote)------->|   tweet    |
|             |                     |            |
|             |                     |            |
+-------------+                     +------------+               

// nodes
type User struct {
    Username string `json:"username"`
}

type Tweet struct {
    Body string `json:"body"`
}

// edges
type Follows struct {}

type Wrote struct {}
  1. Add some users
mark := pyt.NewNode(uuid.NewString(), "user", User{
    Username: "mark",
})
kram := pyt.NewNode(uuid.NewString(), "user", User{
    Username: "kram",
})
you := pyt.NewNode(uuid.NewString(), "user", User{
    Username: "you",
})
users, err := pyt.NodesCreate(tx, *mark, *kram, *you)
  1. Create some follower connections
mk := pyt.NewEdge(uuid.NewString(), "follows", mark.ID, kram.ID, Follows{})
km := pyt.NewEdge(uuid.NewString(), "follows", kram.ID, mark.ID, Follows{})
yk := pyt.NewEdge(uuid.NewString(), "follows", you.ID, kram.ID, Follows{})
ym := pyt.NewEdge(uuid.NewString(), "follows", you.ID, mark.ID, Follows{})
_, err = pyt.EdgesCreate(tx, *mk, *km, *yk, *ym)
  1. Add some tweets for all of the users and add a wrote edge between the user and the tweet
for x, user := range *users {
    total := 50

    if x == 1 {
        total = 20
    } else if x == 2 {
        total = 10
    }

    for i := 0; i < total; i++ {
        mt := pyt.NewNode(uuid.NewString(), "tweet", Tweet{
            Body: fmt.Sprintf("%s tweeted item #%v", user.Properties.Username, i),
        })
        _, err := pyt.NodeCreate(tx, *mt)

        // arbitary sleep
        time.Sleep(time.Millisecond * 1)
        wrote := pyt.NewEdge(uuid.NewString(), "wrote", user.ID, mt.ID, Wrote{})
        _, err = pyt.EdgeCreate(tx, *wrote)
    }
}
  1. Now that we have the tables seeded with some data, lets pull it out. We can accomplish this by selecting from the edge table and joining on the node and edge tables as a way to walk the graph
SELECT
	json_extract(follows.properties, '$.username') as author,
	follows.id as author_id,
	tweet.id as tweet_id,
	json_extract(tweet.properties, '$.body') as tweet,
	tweet.time_created as date
FROM
	edge e
JOIN
	node follows ON follows.id = e.out_id
JOIN
	edge wrote ON wrote.in_id = follows.id
JOIN
	node tweet ON tweet.id = wrote.out_id
WHERE
	e.in_id = '10a9a97d-2a07-441f-bfcb-70177fcc25c7'
AND
	e.type = 'follows'
AND
	wrote.type = 'wrote'
ORDER BY
	tweet.time_created DESC

There is a lot going on here, but it isnt too bad. First we're starting with our user's (10a9a97d-2a07-441f-bfcb-70177fcc25c7) edges. We limit the edges based on follows type. We then join aginst node, alised as follows on it's id and the edge's out_id. Join on edge, alias as wrote and we limit those in the where clause wrote.type = 'wrote' and finally we get the tweet by joing wrote edge on the node table again. Finally we order the results by the time it was created

type FollowersTweet struct {
	author    string
	author_id string
	tweet_id  string
	tweet     string
	date      time.Time
}

type FollowersTweets []FollowersTweet

func (ft FollowersTweets) WriteTable() {
	tw := tabwriter.NewWriter(os.Stdout, 1, 1, 1, ' ', 0)
	fmt.Fprintln(tw, "author\ttweet\ttime")

	for _, f := range ft {
		row := fmt.Sprintf("%v\t%v\t%v", f.author, f.tweet, f.date)
		fmt.Fprintln(tw, row)
	}

	fmt.Printf("found %d tweets\n\n", len(ft))
	tw.Flush()
	fmt.Println("\n ")
}

func getFollingTweets(tx *sql.Tx, userID string) (*FollowersTweets, error) {
	query := `
	SELECT
		json_extract(follows.properties, '$.username') as author,
		follows.id as author_id,
		tweet.id as tweet_id,
		json_extract(tweet.properties, '$.body') as tweet,
		tweet.time_created as date
	FROM
		edge e
	JOIN
		node follows ON follows.id = e.out_id
	JOIN
		edge wrote ON wrote.in_id = follows.id
	JOIN
		node tweet ON tweet.id = wrote.out_id
	WHERE
		e.in_id = ?
	AND
		wrote.type = 'wrote'
	ORDER BY
		wrote.time_created DESC
	`
	rows, err := tx.Query(query, userID)

	var resp FollowersTweets

	for rows.Next() {
		rec := FollowersTweet{}
		err := rows.Scan(
			&rec.author,
			&rec.author_id,
			&rec.tweet_id,
			&rec.tweet,
			&rec.date,
		)
		if err != nil {
			return nil, err
		}

		resp = append(resp, rec)
	}

	return &resp, nil
}
  1. Get a timeline of tweets from the users that you is following
timeline, err := getFollingTweets(tx, you.ID)
timeline.WriteTable()

found 70 tweets

author tweet                 time
kram   kram tweeted item #19 2024-02-22 17:03:59.693 +0000 UTC
kram   kram tweeted item #18 2024-02-22 17:03:59.691 +0000 UTC
kram   kram tweeted item #17 2024-02-22 17:03:59.69 +0000 UTC
kram   kram tweeted item #16 2024-02-22 17:03:59.689 +0000 UTC
kram   kram tweeted item #15 2024-02-22 17:03:59.688 +0000 UTC
...
mark   mark tweeted item #4  2024-02-22 17:03:59.6 +0000 UTC
mark   mark tweeted item #3  2024-02-22 17:03:59.598 +0000 UTC
mark   mark tweeted item #2  2024-02-22 17:03:59.596 +0000 UTC
mark   mark tweeted item #1  2024-02-22 17:03:59.594 +0000 UTC
mark   mark tweeted item #0  2024-02-22 17:03:59.592 +0000 UTC

Documentation

Index

Constants

This section is empty.

Variables

View Source 
var (
	DefaultNodeTableName string = "node"
	DefaultEdgeTableName string = "edge"

	ErrBadUpsertQuery error = errors.New("bad upsert query")
)

Functions

func BuildSchema 

func BuildSchema(db *sql.DB) error

BuildSchema does the work of scaffoling the database and should be called when the connection is created.

func BuildSchemaWithTableNames 

func BuildSchemaWithTableNames(db *sql.DB, edgeTableName, nodeTableName string) error

func EdgeDeleteByIDs 

func EdgeDeleteByIDs(tx *sql.Tx, edgeIDs ...string) (int64, error)

func EdgeDeleteByIDsWithTableName 

func EdgeDeleteByIDsWithTableName(tx *sql.Tx, edgeTableName string, edgeIDs ...string) (int64, error)

func EdgeDeleteByNodeIDs 

func EdgeDeleteByNodeIDs(tx *sql.Tx, inIDs []string, outIDs []string) (int64, error)

func EdgeDeleteByNodeIDsWithTableName 

func EdgeDeleteByNodeIDsWithTableName(tx *sql.Tx, edgeTableName string, inIDs []string, outIDs []string) (int64, error)

func NewFilter 

func NewFilter(field string, value any, subFilters ...*filter) *filter

NewFilter will create a filter with a default equal (=) Comparision and a default "and" SubFilterComparision

func NewFilterFull 

func NewFilterFull(field, comparison string, value any, subFilterComparision string, subFilters ...*filter) *filter

NewFilterFull builds a filter

func NewOrFilter 

func NewOrFilter(field string, value any, subFilters ...*filter) *filter

NewOrFilter will create a filter with a default equal (=) Comparision and a default "or" SubFilterComparision

func NodeDeleteByIDs 

func NodeDeleteByIDs(tx *sql.Tx, nodeIDs ...string) (int64, error)

func NodeDeleteByIDsWithTableName 

func NodeDeleteByIDsWithTableName(tx *sql.Tx, nodeTableName string, nodeIDs ...string) (int64, error)

func PropertiesToType 

func PropertiesToType[T any](properties []byte) (*T, error)

PropertiesToType will take a byte of properties and unmarshal it into the provided type

Types

type Edge 

type Edge[T any] struct {
	InID       string `json:"in_id"`
	OutID      string `json:"out_id"`
	Properties T
	// contains filtered or unexported fields
}

func EdgeCreate 

func EdgeCreate[T any](tx *sql.Tx, newEdge Edge[T]) (*Edge[T], error)

EdgeCreate will add an edge to the database. The InID and OutID nodes must already exist in the database or are apart of the current transaction

func EdgeCreateWithTableName 

func EdgeCreateWithTableName[T any](tx *sql.Tx, edgeTableName string, newEdge Edge[T]) (*Edge[T], error)

func EdgeGetBy 

func EdgeGetBy[T any](tx *sql.Tx, filters FilterSet) (*Edge[T], error)

EdgeGetByID will return a single typed edge by its id

func EdgeGetByID 

func EdgeGetByID[T any](tx *sql.Tx, id string) (*Edge[T], error)

EdgeGetByID will return a typed edge by its id

func EdgeGetByIDWithTableName 

func EdgeGetByIDWithTableName[T any](tx *sql.Tx, edgeTableName, id string) (*Edge[T], error)

func EdgeGetByWithTableName 

func EdgeGetByWithTableName[T any](tx *sql.Tx, edgeTableName string, filters FilterSet) (*Edge[T], error)

func EdgeUpdate 

func EdgeUpdate[T any](tx *sql.Tx, updatedEdge Edge[T], withReturn bool) (*Edge[T], error)

EdgeUpdate will update the properties on an existing edge

func EdgeUpdateWithTableName 

func EdgeUpdateWithTableName[T any](tx *sql.Tx, edgeTableName string, updatedEdge Edge[T], withReturn bool) (*Edge[T], error)

func EdgeUpsert 

func EdgeUpsert[T any](tx *sql.Tx, conflictColumns string, conflictClause string, newEdge Edge[T]) (*Edge[T], error)

func EdgeUpsertWithTableName 

func EdgeUpsertWithTableName[T any](tx *sql.Tx, edgetTableName, conflictColumns, conflictClause string, newEdge Edge[T]) (*Edge[T], error)

func GenericEdgeToType 

func GenericEdgeToType[T any](edgeInstance GenericEdge) (*Edge[T], error)

GenericEdgeToType will convert a GenericEdge to the provided typed Edge

func NewEdge 

func NewEdge[T any](id, edgeType, inID, outID string, properties T) *Edge[T]

NewEdge creates a typed Edge

type EdgeSet 

type EdgeSet[T any] []Edge[T]

func EdgesCreate 

func EdgesCreate[T any](tx *sql.Tx, newEdges ...Edge[T]) (*EdgeSet[T], error)

EdgesCreate will add mulitple edges to the database. The InID and OutID nodes for each edge must already exist in the database or are apart of the current transaction

func EdgesCreateWithTableName 

func EdgesCreateWithTableName[T any](tx *sql.Tx, edgeTableName string, newEdges ...Edge[T]) (*EdgeSet[T], error)

func EdgesGetBy 

func EdgesGetBy[T any](tx *sql.Tx, filters *FilterSet) (*EdgeSet[T], error)

EdgesGetBy will return a typed EdgeSet and can be extended using a FilterSet

func EdgesGetByWithTableName 

func EdgesGetByWithTableName[T any](tx *sql.Tx, edgeTableName string, filters *FilterSet) (*EdgeSet[T], error)

func EdgesUpsert 

func EdgesUpsert[T any](tx *sql.Tx, conflictColumns, conflictClause string, newEdges ...Edge[T]) (*EdgeSet[T], error)

EdgesUpsert will execute an upsert query based on the conflictColumns and the conflictCluase values

func EdgesUpsertWithTableName 

func EdgesUpsertWithTableName[T any](tx *sql.Tx, edgeTableName, conflictColumns, conflictClause string, newEdges ...Edge[T]) (*EdgeSet[T], error)

func RowsToEdge 

func RowsToEdge[T any](rows *sql.Rows, tx *sql.Tx) (*EdgeSet[T], error)

RowsToEdge is a utility method that is used to convert an sql.Rows instance into a typed EdgeSet

func (EdgeSet[T]) First 

func (es EdgeSet[T]) First() *Edge[T]

func (EdgeSet[T]) IDs 

func (es EdgeSet[T]) IDs() []string

func (EdgeSet[T]) InIDs 

func (es EdgeSet[T]) InIDs() []string

func (EdgeSet[T]) Last 

func (es EdgeSet[T]) Last() *Edge[T]

func (EdgeSet[T]) OutIDs 

func (es EdgeSet[T]) OutIDs() []string

type FilterSet 

type FilterSet []*filter

func (FilterSet) Build 

func (fs FilterSet) Build(params *[]any) string

Build does the work of converting all of the filter instances into a where clause. It will also append any values to the params slice that is used in the final query

type GenericEdge 

type GenericEdge Edge[GenericProperties]

type GenericEdgeNode 

type GenericEdgeNode struct {
	GenericEdge
	GenericNode
}

type GenericEdgeNodeSet 

type GenericEdgeNodeSet []GenericEdgeNode

func NodesGetRelatedBy 

func NodesGetRelatedBy(tx *sql.Tx, nodeID, direction, edgeType string, filters *FilterSet) (*GenericEdgeNodeSet, error)

NodesGetRelatedBy will do a single in or out hop from nodeID via the edgeType can be extended with a FilterSet the edge table is aliased as e, and the node table is aliased as n

func NodesGetRelatedByWithTableName 

func NodesGetRelatedByWithTableName(tx *sql.Tx, nodeTableName, edgeTableName, nodeID, direction, edgeType string, filters *FilterSet) (*GenericEdgeNodeSet, error)

func NodesInRelatedBy 

func NodesInRelatedBy(tx *sql.Tx, nodeID, edgeType string, filters *FilterSet) (*GenericEdgeNodeSet, error)

NodesInRelatedBy will do a single in hop from nodeID via the edgeType can be extended with a FilterSet the edge table is aliased as e, and the node table is aliased as n

func NodesInRelatedByWithTableName 

func NodesInRelatedByWithTableName(tx *sql.Tx, nodeID, edgeType string, filters *FilterSet) (*GenericEdgeNodeSet, error)

func NodesOutRelatedBy 

func NodesOutRelatedBy(tx *sql.Tx, nodeID, edgeType string, filters *FilterSet) (*GenericEdgeNodeSet, error)

NodesOutRelatedBy will do a single out hop from nodeID via the edgeType can be extended with a FilterSet the edge table is aliased as e, and the node table is aliased as n

func NodesOutRelatedByWithTableName 

func NodesOutRelatedByWithTableName(tx *sql.Tx, nodeTableName, edgeTableName, nodeID, edgeType string, filters *FilterSet) (*GenericEdgeNodeSet, error)

type GenericNode 

type GenericNode Node[GenericProperties]

type GenericProperties 

type GenericProperties map[string]any

func (*GenericProperties) Scan 

func (g *GenericProperties) Scan(src any) error

Scan does the work of translating the value stored in the database back into GenericProperties

func (GenericProperties) Value 

func (g GenericProperties) Value() (driver.Value, error)

Values converts the GenericProperties to be saved in the db

type Node 

type Node[T any] struct {
	Properties T
	// contains filtered or unexported fields
}

func GenericNodeToType 

func GenericNodeToType[T any](nodeInstance GenericNode) (*Node[T], error)

GenericNodeToType will convert a GenericNode to the provided typed Node

func NewNode 

func NewNode[T any](id, nodeType string, properties T) *Node[T]

NewNode creates a typed Node

func NodeCreate 

func NodeCreate[T any](tx *sql.Tx, newNode Node[T]) (*Node[T], error)

NodeCreate will add a node to the database

func NodeCreateWithTableName 

func NodeCreateWithTableName[T any](tx *sql.Tx, nodeTableName string, newNode Node[T]) (*Node[T], error)

func NodeGetBy 

func NodeGetBy[T any](tx *sql.Tx, filters FilterSet) (*Node[T], error)

NodeGetBy retuns a single typed node by filters

func NodeGetByID 

func NodeGetByID[T any](tx *sql.Tx, id string) (*Node[T], error)

NodeGetByID retrieves and typed node by its id

func NodeGetByIDWithTableName 

func NodeGetByIDWithTableName[T any](tx *sql.Tx, nodeTableName, id string) (*Node[T], error)

func NodeGetByWithTableName 

func NodeGetByWithTableName[T any](tx *sql.Tx, nodeTableName string, filters FilterSet) (*Node[T], error)

func NodeUpdate 

func NodeUpdate[T any](tx *sql.Tx, updatedNode Node[T], withReturn bool) (*Node[T], error)

NodeUpdate updates a node's properties. updatedNode.ID must exist in the database

func NodeUpdateWithTableName 

func NodeUpdateWithTableName[T any](tx *sql.Tx, nodeTableName string, updatedNode Node[T], withReturn bool) (*Node[T], error)

func NodeUpsert 

func NodeUpsert[T any](tx *sql.Tx, conflictColumns string, conflictClause string, newNode Node[T]) (*Node[T], error)

NodeUpsert will execute an upsert query based on the conflictColumns and the conflictCluase values

ex: you have unique constraint on a "user" node's username that looks like:

CREATE UNIQUE INDEX IF NOT EXISTS user_username_idx ON node(type, properties->'username') WHERE type = 'user'

you would pass in "type, properties->'username'" as the conflictColumns and, in this case, "type='user'" as the conflictClause

func NodeUpsertWithTableName 

func NodeUpsertWithTableName[T any](tx *sql.Tx, nodeTableName, conflictColumns, conflictClause string, newNode Node[T]) (*Node[T], error)

func ResultToNode 

func ResultToNode[T any](row *sql.Row, tx *sql.Tx) (*Node[T], error)

ResultToNode is a utility function that will convert an sql.Row into a typed Node

type NodeSet 

type NodeSet[T any] []Node[T]

func NodesCreate 

func NodesCreate[T any](tx *sql.Tx, newNodes ...Node[T]) (*NodeSet[T], error)

NodesCreate will add mulitple nodes to the database

func NodesCreateWithTableName 

func NodesCreateWithTableName[T any](tx *sql.Tx, nodeTableName string, newNodes ...Node[T]) (*NodeSet[T], error)

func NodesGetBy 

func NodesGetBy[T any](tx *sql.Tx, filters *FilterSet) (*NodeSet[T], error)

NodesGetBy will return a typed NodeSet and can be extended using a FilterSet

func NodesGetByWithTableName 

func NodesGetByWithTableName[T any](tx *sql.Tx, nodeTableName string, filters *FilterSet) (*NodeSet[T], error)

func NodesUpsert 

func NodesUpsert[T any](tx *sql.Tx, conflictColumns, conflictClause string, newNodes ...Node[T]) (*NodeSet[T], error)

NodesUpsert will execute an upsert query based on the conflictColumns and the conflictCluase values

ex: you have unique constraint on a "user" node's username that looks like:

CREATE UNIQUE INDEX IF NOT EXISTS user_username_idx ON node(type, properties->'username') WHERE type = 'user'

you would pass in "type, properties->'username'" as the conflicedColumns and, in this case, "type='user'" as the conflictClause

func NodesUpsertWithTableName 

func NodesUpsertWithTableName[T any](tx *sql.Tx, nodeTableName, conflictColumns, conflictClause string, newNodes ...Node[T]) (*NodeSet[T], error)

func RowsToNode 

func RowsToNode[T any](rows *sql.Rows, tx *sql.Tx) (*NodeSet[T], error)

RowsToNode is a utility method that is used to convert an sql.Rows instance into a typed NodeSet

func (NodeSet[T]) First 

func (ns NodeSet[T]) First() *Node[T]

func (NodeSet[T]) IDs 

func (ns NodeSet[T]) IDs() []string

func (NodeSet[T]) Last 

func (ns NodeSet[T]) Last() *Node[T]

type Time 

type Time struct {
	T time.Time
}

Custom time taken from https://www.golang.dk/articles/go-and-sqlite-in-the-cloud

func (*Time) Scan 

func (t *Time) Scan(src any) error

Scan satisfies sql.Scanner interface.

func (*Time) Value 

func (t *Time) Value() (driver.Value, error)

Value satisfies driver.Valuer interface.

type TypedNodeEdge 

type TypedNodeEdge[NodeType any, EdgeType any] struct {
	Node *Node[NodeType]
	Edge *Edge[EdgeType]
}

type TypedNodeEdgeSet 

type TypedNodeEdgeSet[NodeType any, EdgeType any] []TypedNodeEdge[NodeType, EdgeType]

func GenericEdgeNodeSetToTypes 

func GenericEdgeNodeSetToTypes[NodeType any, EdgeType any](set GenericEdgeNodeSet) (*TypedNodeEdgeSet[NodeType, EdgeType], error)

func (TypedNodeEdgeSet[NodeType, EdgeType]) Edges 

func (ty TypedNodeEdgeSet[NodeType, EdgeType]) Edges() *EdgeSet[EdgeType]

func (TypedNodeEdgeSet[NodeType, EdgeType]) Nodes 

func (ty TypedNodeEdgeSet[NodeType, EdgeType]) Nodes() *NodeSet[NodeType]

Source Files

View all Source files

Directories

Path Synopsis
examples
twitter

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.