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
Summary
Actions Summary Table
Actions | Description |
---|---|
Close() | This action closes the active connection to the database. |
Commit() | Any changes made through Inserts, Updates, or Deletes that occur when auto-commit is disabled will not be saved unless Commit() is used. |
Compare(Libraries.Language.Object object) | This action compares two object hash codes and returns an integer. |
Connect(Libraries.Data.Database.Connection connection) | Takes a connection object used internally to connect to a database server. |
Connect(text location, text database, text username, text password) | This action attempts to create a connection to a database using the given parameters. |
ConvertResultToDataFrame(Libraries.Data.Database.QueryResult result) | This action takes a QueryResult and converts it to a DataFrame. |
CreateTable(Libraries.Data.Database.CreateTable creation) | This action takes a CreateTable query and sends it to the database. |
CreateView(Libraries.Data.Database.Find query, text name) | This action will create a view on the database from the passed Find query. |
CreateView(Libraries.Data.Database.CreateView view) | This action takes a CreateView query and sends it to the database. |
Delete(Libraries.Data.Database.Delete query) | This takes an Delete query and sends it to the database and returns the result. |
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. |
DropTable(Libraries.Data.Database.DropTable drop) | This action takes a DropTable query and sends it to the database. |
EditTable(Libraries.Data.Database.EditTable edit) | This action takes a EditTable query and sends it to the database. |
EmptyTable(text table) | This action removes all rows in a table. |
Equals(Libraries.Language.Object object) | This action determines if two objects are equal based on their hash code values. |
Find(text table, text filter) | This action runs a find based on the given parameters and returns the QueryResult. |
Find(text table) | This action runs a find based on the given parameters and returns the QueryResult. |
Find(Libraries.Containers.Array<text> columns, text table, text filter) | This action runs a find based on the given parameters and returns the QueryResult. |
Find(Libraries.Containers.Array<text> columns, text table) | This action runs a find based on the given parameters and returns the QueryResult. |
Find(Libraries.Data.Database.Find query) | This takes a Find query object and builds a query to send to the database. |
FindAsDataFrame(text table) | This action runs a find based on the given parameter and returns the result as a DataFrame. |
FindAsDataFrame(Libraries.Data.Database.Find query) | This action runs a find query and returns the result as a DataFrame. |
FindAsDataFrame(text table, text filter) | This action runs a find based on the given parameters and returns the result as a DataFrame. |
FindCrossJoin(Libraries.Containers.Array<text> columns, text table, text joinedTable, text filter) | This action runs a Find query using a join. |
FindCrossJoin(text table, text joinedTable, text filter) | This action runs a Find query using a join. |
FindCrossJoin(text table, text joinedTable) | This action runs a Find query using a join. |
FindInnerJoin(Libraries.Containers.Array<text> columns, text table, text joinedTable, text joinCondition, text filter) | This action runs a Find query using a join. |
FindInnerJoin(text table, text joinedTable, text joinCondition) | This action runs a Find query using a join. |
FindInnerJoin(text table, text joinedTable, text joinCondition, text filter) | This action runs a Find query using a join. |
FindInnerJoinUsingColumn(text table, text joinedTable, text column) | This action runs a Find query using a join. |
FindInnerJoinUsingColumn(Libraries.Containers.Array<text> columns, text table, text joinedTable, text column) | This action runs a Find query using a join. |
FindInnerJoinUsingColumn(text table, text joinedTable, text column, text filter) | This action runs a Find query using a join. |
FindInnerJoinUsingColumn(Libraries.Containers.Array<text> columns, text table, text joinedTable, text column, text filter) | This action runs a Find query using a join. |
FindLeftJoin(text table, text joinedTable, text joinCondition, text filter) | This action runs a Find query using a join. |
FindLeftJoin(text table, text joinedTable, text joinCondition) | This action runs a Find query using a join. |
FindLeftJoin(Libraries.Containers.Array<text> columns, text table, text joinedTable, text joinCondition, text filter) | This action runs a Find query using a join. |
FindLeftJoinUsingColumn(text table, text joinedTable, text column) | This action runs a Find query using a join. |
FindLeftJoinUsingColumn(Libraries.Containers.Array<text> columns, text table, text joinedTable, text column) | This action runs a Find query using a join. |
FindLeftJoinUsingColumn(Libraries.Containers.Array<text> columns, text table, text joinedTable, text column, text filter) | This action runs a Find query using a join. |
FindLeftJoinUsingColumn(text table, text joinedTable, text column, text filter) | This action runs a Find query using a join. |
FindRightJoin(text table, text joinedTable, text joinCondition) | This action runs a Find query using a join. |
FindRightJoin(Libraries.Containers.Array<text> columns, text table, text joinedTable, text joinCondition, text filter) | This action runs a Find query using a join. |
FindRightJoin(text table, text joinedTable, text joinCondition, text filter) | This action runs a Find query using a join. |
FindRightJoinUsingColumn(Libraries.Containers.Array<text> columns, text table, text joinedTable, text column, text filter) | This action runs a Find query using a join. |
FindRightJoinUsingColumn(Libraries.Containers.Array<text> columns, text table, text joinedTable, text column) | This action runs a Find query using a join. |
FindRightJoinUsingColumn(text table, text joinedTable, text column, text filter) | This action runs a Find query using a join. |
FindRightJoinUsingColumn(text table, text joinedTable, text column) | This action runs a Find query using a join. |
GetAutoCommit() | This action returns whether or not the database will automatically commit with every transaction on the database. |
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. |
GetHashCode() | This action gets the hash code for an object. |
Insert(Libraries.Data.Database.Insert query) | This takes an Insert query and sends it to the database and returns the result. |
IsConnected() | Returns whether or not this database object is currently connected to a database |
RemoveTable(text table) | This action removes a table on the database. |
RenameTable(text table, text newName) | This action renames a table on the database. |
Rollback() | This action will remove any nonpermanent changes currently on the database and revert the database to a previous state. |
SetAutoCommit(boolean commit) | This action sets the auto-commit behavior of the database. |
Update(Libraries.Data.Database.Update query) | This takes an Update query and sends it to the database and returns the result. |
Actions Documentation
Close()
This action closes the active connection to the database. If there are any uncommited changes they will be commited.
Example Code
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 Code
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.
Example Code
Object o
Object t
integer result = o:Compare(t) //1 (larger), 0 (equal), or -1 (smaller)
Parameters
- Libraries.Language.Object: The object to compare to.
Return
integer: The Compare result, Smaller, Equal, or Larger.
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.
Example Code
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)
Parameters
- Libraries.Data.Database.Connection: The Connection object containing the information needed to establish a 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.
Example Code
use Libraries.Data.Database.Database
Database db
db:Connect("localhost","myDB","root","superSecretPassword")
output db:IsConnected()
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
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.
Example Code
use Libraries.Data.Database.Database
Database db
db:Connect("localhost","myDB","root","superSecretPassword")
db:ConvertResultToDataFrame(db:Find("myDB_table"))
Parameters
Return
Libraries.Compute.Statistics.DataFrame: Returns a DataFrame containing the results of the query
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.
Example Code
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)
Parameters
- Libraries.Data.Database.CreateTable: The query to run
CreateView(Libraries.Data.Database.Find query, text name)
This action will create a view on the database from the passed Find query.
Example Code
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")
Parameters
- Libraries.Data.Database.Find: The find query to build a view from
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.
Example Code
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)
Parameters
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.
Example Code
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)
Parameters
- Libraries.Data.Database.Delete: The query to run
Return
integer: Returns the number of rows affected by the 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.
Example Code
use Libraries.Data.Database.Database
Database db
db:Connect("localhost","myDB","root","superSecretPassword")
integer rowsAffected = db:Delete("myDB_table", "id = 100")
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
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().
Example Code
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)
Parameters
- Libraries.Data.Database.DropTable: The query to run
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.
Example Code
use Libraries.Data.Database.Database
use Libraries.Data.Database.EditTable
Database db
db:Connect("localhost","myDB","root","superSecretPassword")
EditTable edit
db:EditTable(edit)
Parameters
- Libraries.Data.Database.EditTable: The query to run
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.
Example Code
use Libraries.Data.Database.Database
Database db
db:Connect("localhost","myDB","root","superSecretPassword")
db:EmptyTable("myDB_table")
Parameters
- text table: The table to empty
Equals(Libraries.Language.Object object)
This action determines if two objects are equal based on their hash code values.
Example Code
use Libraries.Language.Object
use Libraries.Language.Types.Text
Object o
Text t
boolean result = o:Equals(t)
Parameters
- Libraries.Language.Object: The to be compared.
Return
boolean: True if the hash codes are equal and false if they are not equal.
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.
Example Code
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")
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
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.
Example Code
use Libraries.Data.Database.Database
use Libraries.Data.Database.QueryResult
Database db
db:Connect("localhost","myDB","root","superSecretPassword")
QueryResult result = db:Find("myDB_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
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.
Example Code
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")
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
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.
Example Code
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")
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
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.
Example Code
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)
Parameters
- Libraries.Data.Database.Find: The query to run
Return
Libraries.Data.Database.QueryResult: Returns a query result which for a Find will be the rows that matched with the 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.
Example Code
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()
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
FindAsDataFrame(Libraries.Data.Database.Find query)
This action runs a find query and returns the result as a DataFrame.
Example Code
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()
Parameters
- Libraries.Data.Database.Find: The QueryResult to convert. Typically this QueryResult will come from running a Find
Return
Libraries.Compute.Statistics.DataFrame: Returns a DataFrame containing the results of the query
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.
Example Code
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()
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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.
Example Code
use Libraries.Data.Database.Database
Database db
db:Connect("localhost","myDB","root","superSecretPassword")
output db:GetAutoCommit()
Return
boolean: Returns true if auto commit is on and false otherwise.
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.
Example Code
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()
Return
Libraries.Data.Database.DatabaseMetaData: Returns a DatabaseMetaData object filled with information from the connected database.
GetHashCode()
This action gets the hash code for an object.
Example Code
Object o
integer hash = o:GetHashCode()
Return
integer: The integer hash code of the object.
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.
Example Code
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)"
Parameters
- Libraries.Data.Database.Insert: The query to run
Return
integer: Returns the number of rows that were affected
IsConnected()
Returns whether or not this database object is currently connected to a database
Example Code
use Libraries.Data.Database.Database
Database db
output db:IsConnected()
Return
boolean: Returns true if currently connected to a database
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.
Example Code
use Libraries.Data.Database.Database
Database db
db:Connect("localhost","myDB","root","superSecretPassword")
db:RemoveTable("myDB_table")
Parameters
- text table: The table to remove
RenameTable(text table, text newName)
This action renames a table on the database.
Example Code
use Libraries.Data.Database.Database
Database db
db:Connect("localhost","myDB","root","superSecretPassword")
db:RenameTable("myDB_table", "tableWithNewName")
Parameters
- text table: The table to rename
- text newName: The new name to give to the table
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 Code
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.
Example Code
use Libraries.Data.Database.Database
Database db
db:Connect("localhost","myDB","root","superSecretPassword")
db:SetAutoCommit(false)
Parameters
- boolean commit: If true the database will automatically commit with every query.
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.
Example Code
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"
Parameters
- Libraries.Data.Database.Update: The query to run
Return
integer: Returns the number of rows affected by the Update