Installation and Basic Usage • lbugr

This vignette provides a guide to lbugr and performing basic operations, including creating a database, defining a schema, loading data from various sources, and executing Cypher queries.

1. Connecting to a Database

First, load the lbugr package and create a connection to a Ladybug database. You can create an in-memory database or specify a path to a database file on disk.

library(lbugr)
con <- lb_connection(":memory:")

2. Data Types and Schema Definition

Ladybug supports a rich set of data types. When creating a schema, you need to map your R data types to the corresponding Ladybug LogicalTypeID.

Ladybug to R Data Type Mapping

Ladybug `LogicalTypeID`	R Type Equivalent	Description
`BOOL`	`logical`	`TRUE`/`FALSE` values
`INT64`	`integer`	64-bit signed integer
`DOUBLE`	`numeric`	Double-precision floating-point number
`STRING`	`character`	UTF-8 encoded string
`TIMESTAMP`	`POSIXct`	Date and time with timezone, stored as microseconds since epoch
`DATE`	`Date`	Date (year, month, day)
`INTERVAL`	`difftime`	Time interval (e.g., “1 year 2 months 3 days”)
`UUID`	`character`	Universally Unique Identifier, stored as a string
`LIST`	`list`	Ordered collection of values of the same type
`MAP`	`list` (named list)	Unordered collection of key-value pairs

Creating a Complex Schema

You can define a schema with node and relationship tables using lb_execute(). Here’s an example of a more complex schema:

# Create a node table for users with various data types
lb_execute(con, paste("CREATE NODE TABLE User(userID UUID, name STRING,",
                        "age INT64, is_active BOOL, created_at TIMESTAMP,",
                        "last_login DATE, notes STRING[],",
                        "PRIMARY KEY (userID))"))

# Create a node table for products
lb_execute(con, "CREATE NODE TABLE Product(productID INT64, name STRING, PRIMARY KEY (productID))")

# Create a relationship table for user purchases
lb_execute(con, "CREATE REL TABLE Buys(FROM User TO Product, purchase_date DATE)")

3. Loading Data

You can load data into your Ladybug tables from R data frames or external files like CSV.

Loading from a Data Frame

Use lb_copy_from_df() to load data from an R data.frame.

library(jsonlite)
# Create data frames for nodes and relationships
users <- data.frame(
  userID = c("a1b2c3d4-e5f6-7890-1234-567890abcdef", "b2c3d4e5-f6a7-8901-2345-67890abcdef0"),
  name = c("Alice", "Bob"),
  age = c(35, 45),
  is_active = c(TRUE, FALSE),
  created_at = as.POSIXct(c("2023-01-15 10:30:00", "2022-11-20 14:00:00")),
  last_login = as.Date(c("2023-10-25", "2023-09-30")),
  stringsAsFactors = FALSE
)
# LIST types should be formatted as JSON strings
users$notes <- c(toJSON(c("note1", "note2")), toJSON("note3"))

products <- data.frame(
  productID = c(101, 102),
  name = c("Laptop", "Mouse")
)

buys <- data.frame(
  from_user = c("a1b2c3d4-e5f6-7890-1234-567890abcdef", "b2c3d4e5-f6a7-8901-2345-67890abcdef0"),
  to_product = c(101, 102),
  purchase_date = as.Date(c("2023-02-20", "2023-03-15"))
)

# Load data into Ladybug
lb_copy_from_df(con, users, "User")
lb_copy_from_df(con, products, "Product")
lb_copy_from_df(con, buys, "Buys")

Loading from a CSV File

Use lb_copy_from_csv() to load data from a CSV file. For this to work, the file should be in the current working directory.

# Create a CSV file in the project's root directory
csv_filename <- "products.csv"
write.csv(data.frame(productID = c(103, 104), name = c("Keyboard", "Monitor")),
          csv_filename, row.names = FALSE)

# Load data from the CSV file using just the filename
lb_copy_from_csv(con, csv_filename, "Product")

# Clean up the created file
unlink(csv_filename)

4. Executing Queries and Converting Results

You can execute Cypher queries using lb_execute() and convert the results into various R formats.

# Execute a query to get users and their purchases
query_result <- lb_execute(con, "MATCH (u:User)-[b:Buys]->(p:Product) RETURN u.name, p.name, b.purchase_date")

A Note on Query Results

The QueryResult object returned by lb_execute() acts as an iterator over the results. This means it can only be consumed once. Functions like as.data.frame(), as_tibble(), lb_get_all(), and the graph conversion functions will exhaust this iterator.

Convert to Data Frame or Tibble

# Convert to a data frame
df_result <- as.data.frame(query_result)
print(df_result)

# Convert to a tibble
library(tibble)
query_result <- lb_execute(con, "MATCH (u:User)-[b:Buys]->(p:Product) RETURN u.name, p.name, b.purchase_date")
tibble_result <- as_tibble(query_result)
print(tibble_result)

Use Query Results returned as list

query_result <- lb_execute(con, "MATCH (u:User)-[b:Buys]->(p:Product) RETURN u.name, p.name, b.purchase_date")

result <- lb_get_all(query_result)
print(result)

# only fetch 1. result 
query_result <- lb_execute(con, "MATCH (u:User)-[b:Buys]->(p:Product) RETURN u.name, p.name, b.purchase_date")

result <- lb_get_n(query_result, 1)
print(result)

#Fetch next result
result <- lb_get_next(query_result)
print(result)

Convert to Graph Objects

For queries that return graph structures, you can convert the results into graph objects from packages like igraph or tidygraph. To do this, the query must return the node and relationship variables themselves, not just their properties.

# Execute a query that returns a graph structure 
graph_query_result <- lb_execute(con, "MATCH (u:User)-[b:Buys]->(p:Product) RETURN u, p, b")
igraph_obj <- as_igraph(graph_query_result)
print(igraph_obj)

plot(igraph_obj,
     vertex.color = "#dc2626",
     vertex.label.color = "#f3f4f6",
     vertex.label.font = 2,
     edge.color = "#9ca3af",
     edge.arrow.size = 0.8,
     edge.arrow.width = 0.5,
     bg = "#030712",
     main = "igraph: User Purchases")

# Convert to a tidygraph object
tidygraph_obj <- as_tidygraph(graph_query_result)
print(tidygraph_obj)

ggraph::ggraph(tidygraph_obj, layout = "kk") +
  ggraph::geom_edge_arrow(color = "#9ca3af", arrow.fill = "#9ca3af", end_cap = ggraph::arrow(angle = 30, length = grid::unit(3, "mm"))) +
  ggraph::geom_node_point(color = "#dc2626", size = 8) +
  ggraph::geom_node_text(ggplot2::aes(label = name), color = "#f3f4f6", size = 4, vjust = -1) +
  ggplot2::theme_void() +
  ggplot2::theme(plot.background = ggplot2::element_rect(fill = "#030712", color = NA))