Libraries.Data.Database.Database Documentation

The Database class is used to connect to a live database and either get information about the Database or run queries on the database and return results. After calling Connect running a query or using a designated helper function will send a SQL query to that database.

Example Code

use Libraries.Data.Database.Database
use Libraries.Data.Database.Find

Database database
database:Connect("localhost", "myDatabase", "user", "SuperSecret456")

Find query
query:SetTable("employees")
query:AddColumn("lastName")
query:AddColumn("firstName")

QueryResult results = database:Find(query)

Inherits from: Libraries.Language.Object

Actions Documentation

Close()

This action closes the active connection to the database. If there are any uncommited changes they will be commited.

Example

use Libraries.Data.Database.Database

Database db
db:Connect("localhost","myDB","root","superSecretPassword")

output db:IsConnected()

db:Close()

output db:IsConnected()

Commit()

Any changes made through Inserts, Updates, or Deletes that occur when auto-commit is disabled will not be saved unless Commit() is used. A commit is like saving changes on a document. If auto commit is set to true this action does not need to be used.

Example

use Libraries.Data.Database.Database
use Libraries.Data.Database.Update

Database db
db:Connect("localhost","myDB","root","superSecretPassword")

db:SetAutoCommit(false)

Update update
update:SetTable("tasks")
update:Add("priority", 1)
update:Filter("priority = 4")

db:Update(update)

db:Commit()

Compare(Libraries.Language.Object object)

This action compares two object hash codes and returns an integer. The result is larger if this hash code is larger than the object passed as a parameter, smaller, or equal. In this case, -1 means smaller, 0 means equal, and 1 means larger. This action was changed in Quorum 7 to return an integer, instead of a CompareResult object, because the previous implementation was causing efficiency issues.

Parameters

Return

integer: The Compare result, Smaller, Equal, or Larger.

Example

Object o
Object t
integer result = o:Compare(t) //1 (larger), 0 (equal), or -1 (smaller)

Connect(Libraries.Data.Database.Connection connection)

Takes a connection object used internally to connect to a database server. A connection object holds the user and location information. If the port of the database server is 3306 this action does not need to used an the other Connect action with more parameter removes the need to create a Connection object.

Parameters

Example

use Libraries.Data.Database.Database
use Libraries.Data.Database.Connection

Database db

Connection connection
connection:SetLocation("localhost")
connection:SetDatabase("myDB")
connection:SetUsername("user2")
connection:SetPassword("myPassword")
db:Connect(connection)

Connect(text location, text database, text username, text password)

This action attempts to create a connection to a database using the given parameters. If the connection is made successfully IsConnected will return true.

Parameters

  • text location: The location of the database. A common location is "localhost."
  • text database: The name of the database
  • text username: The name of the user
  • text password: The password for the user

Example

use Libraries.Data.Database.Database

Database db
db:Connect("localhost","myDB","root","superSecretPassword")

output db:IsConnected()

ConvertResultToDataFrame(Libraries.Data.Database.QueryResult result)

This action takes a QueryResult and converts it to a DataFrame. The conversion will try to have the columns of the DataFrame be the same type as returned by the result.

Parameters

Return

Libraries.Compute.Statistics.DataFrame: Returns a DataFrame containing the results of the query

Example

use Libraries.Data.Database.Database

Database db
db:Connect("localhost","myDB","root","superSecretPassword")

db:ConvertResultToDataFrame(db:Find("myDB_table"))

CreateTable(Libraries.Data.Database.CreateTable creation)

This action takes a CreateTable query and sends it to the database. If successful the metadata will be updated to include the new table. The CreateTable query adds new tables to the database. For more information on creating tables there is documentation in the CreateTable class.

Parameters

Example

use Libraries.Data.Database.Database
use Libraries.Data.Database.CreateTable

Database db
db:Connect("localhost","myDB","root","superSecretPassword")

CreateTable create
create:SetTable("newTable")
create:AddIntegerColumn("id")
db:CreateTable(create)

CreateView(Libraries.Data.Database.Find query, text name)

This action will create a view on the database from the passed Find query.

Parameters

Example

use Libraries.Data.Database.Database
use Libraries.Data.Database.Find

Database db
db:Connect("localhost","myDB","root","superSecretPassword")

Find find
find:SetTable("myDB_table")
find:AddColumn("id")

db:CreateView(findQuery, "id_view")

CreateView(Libraries.Data.Database.CreateView view)

This action takes a CreateView query and sends it to the database. If successful the metadata will be updated to include the new view. Creating a view typically consists of setting a Find query so the other helper function can simplify adding a view.

Parameters

Example

use Libraries.Data.Database.Database
use Libraries.Data.Database.CreateView
use Libraries.Data.Database.Find

Database db
db:Connect("localhost","myDB","root","superSecretPassword")

Find find
find:SetTable("myDB_table")
find:AddColumn("id")

CreateView view
view:SetQuery(find)
view:SetName("id_view")

db:CreateView(view)

Delete(Libraries.Data.Database.Delete query)

This takes an Delete query and sends it to the database and returns the result. A Delete removes rows from a database table based on a given filter and then returns the number of rows deleted. A delete at minimum needs a table and a filter so there are helper functions in this Database class to make it easier to run a delete but using an object is also accepted. For more information on the deleting there is documentation on the Delete query.

Parameters

Return

integer: Returns the number of rows affected by the Delete

Example

use Libraries.Data.Database.Database
use Libraries.Data.Database.Delete

Database db
db:Connect("localhost","myDB","root","superSecretPassword")

Delete delete
delete:SetTable("tasks")
delete:Filter("priority = 4")
db:Delete(delete)

Delete(text table, text filter)

This action runs a delete query where rows are removed from a table if the columns of the row make the filter true.

Parameters

  • text table: The database table to run the query on
  • text filter: A filter to that will affect what will be deleted

Return

integer: Returns the number of rows affected by this delete

Example

use Libraries.Data.Database.Database

Database db
db:Connect("localhost","myDB","root","superSecretPassword")

integer rowsAffected = db:Delete("myDB_table", "id = 100")

DropTable(Libraries.Data.Database.DropTable drop)

This action takes a DropTable query and sends it to the database. DropTable is used to remove tables or to empty the data out of a table. If successful the metadata will be updated to reflect the changes. Most of what can be through a DropTable query can be done through other actions in the class such as RemoveTable().

Parameters

Example

use Libraries.Data.Database.Database
use Libraries.Data.Database.DropTable

Database db
db:Connect("localhost","myDB","root","superSecretPassword")

DropTable drop
drop:SetTable("oldTable")
db:DropTable(drop)

EditTable(Libraries.Data.Database.EditTable edit)

This action takes a EditTable query and sends it to the database. The EditTable query allows for changes such as table renaming, or the adding and modifying of columns in a table. If successful the metadata will be updated to reflect any changes.

Parameters

Example

use Libraries.Data.Database.Database
use Libraries.Data.Database.EditTable

Database db
db:Connect("localhost","myDB","root","superSecretPassword")

EditTable edit
db:EditTable(edit)

EmptyTable(text table)

This action removes all rows in a table. The table will still appear in the database but all rows of data will be gone.

Parameters

  • text table: The table to empty

Example

use Libraries.Data.Database.Database

Database db
db:Connect("localhost","myDB","root","superSecretPassword")

db:EmptyTable("myDB_table")

Equals(Libraries.Language.Object object)

This action determines if two objects are equal based on their hash code values.

Parameters

Return

boolean: True if the hash codes are equal and false if they are not equal.

Example

use Libraries.Language.Object
use Libraries.Language.Types.Text
Object o
Text t
boolean result = o:Equals(t)

Find(text table, text filter)

This action runs a find based on the given parameters and returns the QueryResult. This action will return all columns and rows from a database table where the filter expression is true.

Parameters

  • text table: The database table to run the query on
  • text filter: A filter to that will affect what will be returned by the query

Return

Libraries.Data.Database.QueryResult: Returns a QueryResult containing the results of the query

Example

use Libraries.Data.Database.Database
use Libraries.Data.Database.QueryResult

Database db
db:Connect("localhost","myDB","root","superSecretPassword")

QueryResult result = db:Find("myDB_table", "id > 200")

Find(text table)

This action runs a find based on the given parameters and returns the QueryResult. This action will return all columns and rows from a database table.

Parameters

  • text table: The database table to run the query on

Return

Libraries.Data.Database.QueryResult: Returns a QueryResult containing the results of the query

Example

use Libraries.Data.Database.Database
use Libraries.Data.Database.QueryResult

Database db
db:Connect("localhost","myDB","root","superSecretPassword")

QueryResult result = db:Find("myDB_table")

Find(Libraries.Containers.Array<text> columns, text table, text filter)

This action runs a find based on the given parameters and returns the QueryResult. This action will return all columns and rows from a database table that match an expression if one is given and are of the column specified.

Parameters

  • Libraries.Containers.Array: An array containing the list of columns that will be returned from the result.
  • text table: The database table to run the query on
  • text filter: A filter to that will affect what will be returned by the query

Return

Libraries.Data.Database.QueryResult: Returns a DataFrame containing the results of the query

Example

use Libraries.Data.Database.Database
use Libraries.Data.Database.QueryResult
use Libraries.Containers.Array

Database db
db:Connect("localhost","myDB","root","superSecretPassword")

Array<text> columns
columns:Add("id")
columns:Add("roomNumber")
QueryResult result = db:Find(columns, "myDB_table", "id > 100")

Find(Libraries.Containers.Array<text> columns, text table)

This action runs a find based on the given parameters and returns the QueryResult. This action will return all columns and rows from a database table that match an expression if one is given and are of the column specified.

Parameters

  • Libraries.Containers.Array: An array containing the list of columns that will be returned from the result.
  • text table: The database table to run the query on

Return

Libraries.Data.Database.QueryResult: Returns a QueryResult containing the results of the query

Example

use Libraries.Data.Database.Database
use Libraries.Data.Database.QueryResult
use Libraries.Containers.Array

Database db
db:Connect("localhost","myDB","root","superSecretPassword")

Array<text> columns
columns:Add("id")
columns:Add("roomNumber")
QueryResult result = db:Find(columns, "myDB_table")

Find(Libraries.Data.Database.Find query)

This takes a Find query object and builds a query to send to the database. A Find query is used to grab data from the database. When interacting with a database a Find is usually the most common of type of query to use.

Parameters

Return

Libraries.Data.Database.QueryResult: Returns a query result which for a Find will be the rows that matched with the query.

Example

use Libraries.Data.Database.Database
use Libraries.Data.Database.Find

Database db
db:Connect("localhost","myDB","root","superSecretPassword")

Find query
query:SetTable("employees")
query:AddColumn("id")
QueryResult result = db:Find(query)

FindAsDataFrame(text table)

This action runs a find based on the given parameter and returns the result as a DataFrame. This action will return all columns and rows from a database table.

Parameters

  • text table: The database table to run the query on

Return

Libraries.Compute.Statistics.DataFrame: Returns a DataFrame containing the results of the query

Example

use Libraries.Data.Database.Database
use Libraries.Compute.Statistics.DataFrame

Database db
db:Connect("localhost","myDB","root","superSecretPassword")

DataFrame frame = db:FindAsDataFrame("myDB_table")

output frame:ToText()

FindAsDataFrame(Libraries.Data.Database.Find query)

This action runs a find query and returns the result as a DataFrame.

Parameters

Return

Libraries.Compute.Statistics.DataFrame: Returns a DataFrame containing the results of the query

Example

use Libraries.Data.Database.Database
use Libraries.Data.Database.Find
use Libraries.Compute.Statistics.DataFrame

Database db
db:Connect("localhost","myDB","root","superSecretPassword")

Find find
find:SetTable("myDB_table")

DataFrame frame = db:FindAsDataFrame(find)

output frame:ToText()

FindAsDataFrame(text table, text filter)

This action runs a find based on the given parameters and returns the result as a DataFrame. This action will return all columns and rows from a database table where the filter expression is true.

Parameters

  • text table: The database table to run the query on
  • text filter: A filter to that will affect what will be returned by the query

Return

Libraries.Compute.Statistics.DataFrame: Returns a DataFrame containing the results of the query

Example

use Libraries.Data.Database.Database
use Libraries.Compute.Statistics.DataFrame

Database db
db:Connect("localhost","myDB","root","superSecretPassword")

DataFrame frame = db:FindAsDataFrame("myDB_table", "roomNumber = 1001")
output frame:ToText()

FindCrossJoin(Libraries.Containers.Array<text> columns, text table, text joinedTable, text filter)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. A Cross Join is different from the other joins in that there is no join condition. A Cross Join will return the Cartesian product of rows from the two tables. In other words, every row from the first table will be combined with every row from the second table and that will be returned. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") Array columns columns:Add("StoreName") columns:Add("ProductName") QueryResult result = db:FindCrossJoin(columns, "Products", "Stores", "price >= 4.99")

Parameters

Return

Libraries.Data.Database.QueryResult:

FindCrossJoin(text table, text joinedTable, text filter)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. A Cross Join is different from the other joins in that there is no join condition. A Cross Join will return the Cartesian product of rows from the two tables. In other words, every row from the first table will be combined with every row from the second table and that will be returned. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") QueryResult result = db:FindCrossJoin("Products", "Stores", "price >= 4.99")

Parameters

  • text table
  • text joinedTable
  • text filter

Return

Libraries.Data.Database.QueryResult:

FindCrossJoin(text table, text joinedTable)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. A Cross Join is different from the other joins in that there is no join condition. A Cross Join will return the Cartesian product of rows from the two tables. In other words, every row from the first table will be combined with every row from the second table and that will be returned. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") QueryResult result = db:FindCrossJoin("Products", "Stores")

Parameters

  • text table
  • text joinedTable

Return

Libraries.Data.Database.QueryResult:

FindInnerJoin(Libraries.Containers.Array<text> columns, text table, text joinedTable, text joinCondition, text filter)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. An Inner Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult use Libraries.Containers.Array Database db db:Connect("localhost","myDB","root","superSecretPassword") Array columns columns:Add("OrderID") columns:Add("CustomerName") QueryResult result = db:FindInnerJoin(columns, "Orders", "Customers", "Orders:CustomerID = Customers:CustomerID", "OrderNumber = 55")

Parameters

Return

Libraries.Data.Database.QueryResult:

FindInnerJoin(text table, text joinedTable, text joinCondition)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. An Inner Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") QueryResult result = db:FindInnerJoin("Orders", "Customers", "Orders:CustomerID = Customers:CustomerID")

Parameters

  • text table
  • text joinedTable
  • text joinCondition

Return

Libraries.Data.Database.QueryResult:

FindInnerJoin(text table, text joinedTable, text joinCondition, text filter)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. An Inner Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") QueryResult result = db:FindInnerJoin("Orders", "Customers", "Orders:CustomerID = Customers:CustomerID", "OrderNumber = 55")

Parameters

  • text table
  • text joinedTable
  • text joinCondition
  • text filter

Return

Libraries.Data.Database.QueryResult:

FindInnerJoinUsingColumn(text table, text joinedTable, text column)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. This action instead of taking a condition it takes the name of a column. It is common to join to tables that share a column and only join them if values of those columns are equal. This action simplifies writing that specific join condition. An Inner Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") QueryResult result = db:FindInnerJoinUsingColumn("Orders", "Customers", "CustomerID")

Parameters

  • text table
  • text joinedTable
  • text column

Return

Libraries.Data.Database.QueryResult:

FindInnerJoinUsingColumn(Libraries.Containers.Array<text> columns, text table, text joinedTable, text column)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. This action instead of taking a condition it takes the name of a column. It is common to join to tables that share a column and only join them if values of those columns are equal. This action simplifies writing that specific join condition. An Inner Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult use Libraries.Containers.Array Database db db:Connect("localhost","myDB","root","superSecretPassword") Array columns columns:Add("OrderID") columns:Add("CustomerName") QueryResult result = db:FindInnerJoinUsingColumn(columns, "Orders", "Customers", "CustomerID")

Parameters

Return

Libraries.Data.Database.QueryResult:

FindInnerJoinUsingColumn(text table, text joinedTable, text column, text filter)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. This action instead of taking a condition it takes the name of a column. It is common to join to tables that share a column and only join them if values of those columns are equal. This action simplifies writing that specific join condition. An Inner Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") QueryResult result = db:FindInnerJoinUsingColumn("Orders", "Customers", "CustomerID", "OrderNumber = 55")

Parameters

  • text table
  • text joinedTable
  • text column
  • text filter

Return

Libraries.Data.Database.QueryResult:

FindInnerJoinUsingColumn(Libraries.Containers.Array<text> columns, text table, text joinedTable, text column, text filter)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. This action instead of taking a condition takes the name of a column. It is common to join to tables that share a column and only join them if values of those columns are equal. This action simplifies writing that specific join condition. An Inner Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult use Libraries.Containers.Array Database db db:Connect("localhost","myDB","root","superSecretPassword") Array columns columns:Add("OrderID") columns:Add("CustomerName") QueryResult result = db:FindInnerJoinUsingColumn(columns, "Orders", "Customers", "CustomerID", "OrderNumber = 55")

Parameters

Return

Libraries.Data.Database.QueryResult:

FindLeftJoin(text table, text joinedTable, text joinCondition, text filter)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. A Left Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. But if the condition fails then a new row is still added containing all the rows from the first table. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") QueryResult result = db:FindLeftJoin("Orders", "Customers", "Orders:CustomerID = Customers:CustomerID", "OrderNumber = 55")

Parameters

  • text table
  • text joinedTable
  • text joinCondition
  • text filter

Return

Libraries.Data.Database.QueryResult:

FindLeftJoin(text table, text joinedTable, text joinCondition)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. A Left Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. But if the condition fails then a new row is still added containing all the rows from the first table. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") QueryResult result = db:FindLeftJoin("Orders", "Customers", "Orders:CustomerID = Customers:CustomerID")

Parameters

  • text table
  • text joinedTable
  • text joinCondition

Return

Libraries.Data.Database.QueryResult:

FindLeftJoin(Libraries.Containers.Array<text> columns, text table, text joinedTable, text joinCondition, text filter)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. A Left Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. But if the condition fails then a new row is still added containing all the rows from the first table. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") Array columns columns:Add("OrderID") columns:Add("CustomerName") QueryResult result = db:FindLeftJoin(columns, "Orders", "Customers", "Orders:CustomerID = Customers:CustomerID", "OrderNumber = 55")

Parameters

Return

Libraries.Data.Database.QueryResult:

FindLeftJoinUsingColumn(text table, text joinedTable, text column)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. This action instead of taking a conditions takes the name of a column. It is common to join to tables that share a column and only join them if values of those columns are equal. This action simplifies writing that specific join condition. A Left Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. But if the condition fails then a new row is still added containing all the rows from the first table. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") QueryResult result = db:FindLeftJoinUsingColumn("Orders", "Customers", "CustomerID")

Parameters

  • text table
  • text joinedTable
  • text column

Return

Libraries.Data.Database.QueryResult:

FindLeftJoinUsingColumn(Libraries.Containers.Array<text> columns, text table, text joinedTable, text column)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. This action instead of taking a conditions takes the name of a column. It is common to join to tables that share a column and only join them if values of those columns are equal. This action simplifies writing that specific join condition. A Left Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. But if the condition fails then a new row is still added containing all the rows from the first table. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") Array columns columns:Add("OrderID") columns:Add("CustomerName") QueryResult result = db:FindLeftJoinUsingColumn(columns, "Orders", "Customers", "CustomerID")

Parameters

Return

Libraries.Data.Database.QueryResult:

FindLeftJoinUsingColumn(Libraries.Containers.Array<text> columns, text table, text joinedTable, text column, text filter)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. This action instead of taking a conditions takes the name of a column. It is common to join to tables that share a column and only join them if values of those columns are equal. This action simplifies writing that specific join condition. A Left Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. But if the condition fails then a new row is still added containing all the rows from the first table. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") Array columns columns:Add("OrderID") columns:Add("CustomerName") QueryResult result = db:FindLeftJoinUsingColumn(columns, "Orders", "Customers", "CustomerID", "OrderNumber = 55")

Parameters

Return

Libraries.Data.Database.QueryResult:

FindLeftJoinUsingColumn(text table, text joinedTable, text column, text filter)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. This action instead of taking a conditions takes the name of a column. It is common to join to tables that share a column and only join them if values of those columns are equal. This action simplifies writing that specific join condition. A Left Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. But if the condition fails then a new row is still added containing all the rows from the first table. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") QueryResult result = db:FindLeftJoinUsingColumn("Orders", "Customers", "CustomerID", "OrderNumber = 55")

Parameters

  • text table
  • text joinedTable
  • text column
  • text filter

Return

Libraries.Data.Database.QueryResult:

FindRightJoin(text table, text joinedTable, text joinCondition)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. A Right Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. But if the condition fails then a new row is still added containing all the rows from the second table. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") QueryResult result = db:FindRightJoin("Orders", "Customers", "Orders:CustomerID = Customers:CustomerID")

Parameters

  • text table
  • text joinedTable
  • text joinCondition

Return

Libraries.Data.Database.QueryResult:

FindRightJoin(Libraries.Containers.Array<text> columns, text table, text joinedTable, text joinCondition, text filter)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. A Right Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. But if the condition fails then a new row is still added containing all the rows from the second table. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") Array columns columns:Add("OrderID") columns:Add("CustomerName") QueryResult result = db:FindRightJoin(columns, "Orders", "Customers", ""Orders:CustomerID = Customers:CustomerID"", "OrderNumber = 55")

Parameters

Return

Libraries.Data.Database.QueryResult:

FindRightJoin(text table, text joinedTable, text joinCondition, text filter)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. A Right Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. But if the condition fails then a new row is still added containing all the rows from the second table. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") QueryResult result = db:FindRightJoin("Orders", "Customers", "Orders:CustomerID = Customers:CustomerID", "OrderNumber = 55")

Parameters

  • text table
  • text joinedTable
  • text joinCondition
  • text filter

Return

Libraries.Data.Database.QueryResult:

FindRightJoinUsingColumn(Libraries.Containers.Array<text> columns, text table, text joinedTable, text column, text filter)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. This action instead of taking a conditions takes the name of a column. It is common to join to tables that share a column and only join them if values of those columns are equal. This action simplifies writing that specific join condition. A Right Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. But if the condition fails then a new row is still added containing all the rows from the second table. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") Array columns columns:Add("OrderID") columns:Add("CustomerName") QueryResult result = db:FindRightJoinUsingColumn("Orders", "Customers", "CustomerID", "OrderNumber = 55")

Parameters

Return

Libraries.Data.Database.QueryResult:

FindRightJoinUsingColumn(Libraries.Containers.Array<text> columns, text table, text joinedTable, text column)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. This action instead of taking a conditions takes the name of a column. It is common to join to tables that share a column and only join them if values of those columns are equal. This action simplifies writing that specific join condition. A Right Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. But if the condition fails then a new row is still added containing all the rows from the second table. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") Array columns columns:Add("OrderID") columns:Add("CustomerName") QueryResult result = db:FindRightJoinUsingColumn(columns, "Orders", "Customers", "CustomerID")

Parameters

Return

Libraries.Data.Database.QueryResult:

FindRightJoinUsingColumn(text table, text joinedTable, text column, text filter)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. This action instead of taking a conditions takes the name of a column. It is common to join to tables that share a column and only join them if values of those columns are equal. This action simplifies writing that specific join condition. A Right Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. But if the condition fails then a new row is still added containing all the rows from the second table. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") QueryResult result = db:FindRightJoinUsingColumn("Orders", "Customers", "CustomerID", "OrderNumber = 55")

Parameters

  • text table
  • text joinedTable
  • text column
  • text filter

Return

Libraries.Data.Database.QueryResult:

FindRightJoinUsingColumn(text table, text joinedTable, text column)

This action runs a Find query using a join. A Find query with a join will check the rows of multiple tables and the type of join determines what columns will appear in the final result set. This action instead of taking a conditions takes the name of a column. It is common to join to tables that share a column and only join them if values of those columns are equal. This action simplifies writing that specific join condition. A Right Join compares every row from the first table to every row on the second table. If the values from the rows satisfy the condition then a new row is added containing the combined columns from both tables. But if the condition fails then a new row is still added containing all the rows from the second table. use Libraries.Data.Database.Database use Libraries.Data.Database.QueryResult Database db db:Connect("localhost","myDB","root","superSecretPassword") QueryResult result = db:FindRightJoinUsingColumn("Orders", "Customers", "CustomerID")

Parameters

  • text table
  • text joinedTable
  • text column

Return

Libraries.Data.Database.QueryResult:

GetAutoCommit()

This action returns whether or not the database will automatically commit with every transaction on the database. An example of a "transaction" would be an Insert or Update. If the database has auto commit set to true then an insert will immediately take effect after being sent to the database. Auto-commit can be thought like an auto save feature for documents but for databases.

Return

boolean: Returns true if auto commit is on and false otherwise.

Example

use Libraries.Data.Database.Database

Database db
db:Connect("localhost","myDB","root","superSecretPassword")

output db:GetAutoCommit()

GetConnectionError()

If the connection has an error, this action returns the stack trace for the problem.

Return

text:

GetDatabaseMetaData()

Returns the DatabaseMetaData object which contains more information about the currently connected database such as what tables exist and how many tables there are.

Return

Libraries.Data.Database.DatabaseMetaData: Returns a DatabaseMetaData object filled with information from the connected database.

Example

use Libraries.Data.Database.Database
use Libraries.Data.Database.DatabaseMetaData

Database db
db:Connect("localhost","myDB","root","superSecretPassword")

DatabaseMetaData meta = db:GetDatabaseMetaData()
output "Number of tables: " + meta:GetSize()

GetHashCode()

This action gets the hash code for an object.

Return

integer: The integer hash code of the object.

Example

Object o
integer hash = o:GetHashCode()

GetLastInsertedID()

This action closes the active connection to the database. If there are any uncommited changes they will be commited.

Return

integer:

Example

use Libraries.Data.Database.Database

Database db
db:Connect("localhost","myDB","root","superSecretPassword")

output db:IsConnected()

db:Close()

output db:IsConnected()

HasTable(text table)

Returns the true if the Database has a specified table

Parameters

  • text table

Return

boolean: Returns true if the table exists on the database false otherwise.

Example

use Libraries.Data.Database.Database
use Libraries.Data.Database.DatabaseMetaData

Database db
db:Connect("localhost","myDB","root","superSecretPassword")

output db:HasTable("myTable")

Insert(Libraries.Data.Database.Insert query)

This takes an Insert query and sends it to the database and returns the result. An Insert allows for adding more rows to a table and if the insert was successful the database will return the number of rows affected. For more information and how to do an insert look into the documentation of the Insert query class.

Parameters

Return

integer: Returns the number of rows that were affected

Example

use Libraries.Data.Database.Database
use Libraries.Data.Database.Insert

Database db
db:Connect("localhost","myDB","root","superSecretPassword")

Insert insert
insert:SetTable("tasks")
insert:Add("title", "Come up with Resolution")
insert:Add("priority", 4)
insert:Add("start_date", "2021-12-31")
integer rowsAffected = db:Insert(insert)

output "Inserted " + rowsAffected + "row(s)"

IsConnected()

Returns whether or not this database object is currently connected to a database

Return

boolean: Returns true if currently connected to a database

Example

use Libraries.Data.Database.Database

Database db
output db:IsConnected()

RemoveTable(text table)

This action removes a table on the database. The table will no longer appear on the database along with any data that was on it.

Parameters

  • text table: The table to remove

Example

use Libraries.Data.Database.Database

Database db
db:Connect("localhost","myDB","root","superSecretPassword")

db:RemoveTable("myDB_table")

RenameTable(text table, text newName)

This action renames a table on the database.

Parameters

  • text table: The table to rename
  • text newName: The new name to give to the table

Example

use Libraries.Data.Database.Database

Database db
db:Connect("localhost","myDB","root","superSecretPassword")

db:RenameTable("myDB_table", "tableWithNewName")

Rollback()

This action will remove any nonpermanent changes currently on the database and revert the database to a previous state. This action will only work if auto-commit is set to false. If auto-commit is not enabled a query such as an Update can be undone before a Commit() is called.

Example

use Libraries.Data.Database.Database
use Libraries.Data.Database.Update

Database db
db:Connect("localhost","myDB","root","superSecretPassword")

db:SetAutoCommit(false)

Update update
update:SetTable("tasks")
update:Add("priority", 1)
update:Filter("priority = 4")

integer rows = db:Update(update)

if rows > 1
    db:Rollback()
else
    db:Commit()
end

SetAutoCommit(boolean commit)

This action sets the auto-commit behavior of the database. Auto-commit can be thought like an auto save feature for documents but for databases. Auto-commit is where transactions on the database are automatically saved on the database. This can be compared to having a document automatically save every change made so the user does not have to press the save button. If this is set to false in order to save changes to the database the Commit() action needs to be used.

Parameters

  • boolean commit: If true the database will automatically commit with every query.

Example

use Libraries.Data.Database.Database

Database db
db:Connect("localhost","myDB","root","superSecretPassword")

db:SetAutoCommit(false)

Update(Libraries.Data.Database.Update query)

This takes an Update query and sends it to the database and returns the result. An Update allows for changing the data in rows that already exist on the database. For more information and how to do an Update look into the documentation of the Update class.

Parameters

Return

integer: Returns the number of rows affected by the Update

Example

use Libraries.Data.Database.Database
use Libraries.Data.Database.Update

Database db
db:Connect("localhost","myDB","root","superSecretPassword")

Update update
update:SetTable("tasks")
update:AddExpression("priority", "priority + 1")
update:Filter("priority >= 4 ")

integer rowsAffected = db:Update(update)
output rowsAffected + "row(s) updated"