Writing to a Database
Writing to a database equates to manipulating data within a database. Writing allows you to modify the contents of your database. This can mean gathering and storing new data, updating existing information, or deleting, among other things. This can be useful if through your testing you are gathering data from your application under test for further analysis.
One scenario where this could be useful is in the case of running a hotel, where you have a database containing information about guests, rooms, and beds that you want to manipulate by associating guests with appropriately-sized rooms. It can also be useful if you are using a database to gather data from your application under test, including the Eggplant Functional test results themselves, to store for future analysis (possibly via further automated testing).
Records vs. Property Lists
A record is different from an ordinary property list. It is a type of property list (see Property Lists) that has an active connection to a database. When a property list that corresponds to a row in a database is retrieved, it is known as a record. In addition to its visible properties, a record also contains hidden information about the database table from which it was fetched.
Simply put, a record is a property list that has a connection to a row in a database table. Changing the value of a property in a record directly updates the corresponding value in the database. A record may be assigned properties that do not correspond to columns in the database table, but if a property is assigned or changed that is not a column in the database, that value is only changed in the record object and the database is not affected.
Example:
This example accesses a record in a database table and then logs it to demonstrate what a record may look like in practice:
set myDB to {type:"odbc", DSN:"qaDB", user:"Ellen", password:"securePassword"} -- ODBC database connection definition
put table "film" of myDB into myTable -- Stores a reference to the "film" database table in the variable myTable
set Member to the record of myTable where title is "ACADEMY DINOSAUR" -- Accesses a specific record of the "film" table and stores it in the variable Member
Log Member -- Logs: {description:"A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies", film_id:"1", language_id:"1", last_update:"2017-06-28 15:47:10 -0700", length:"86", original_language_id:<missing value>, rating:"PG", release_year:"2006", rental_duration:"6", rental_rate:"3.99", replacement_cost:"20.99", special_features:"Deleted Scenes,Behind the Scenes", title:"ACADEMY DINOSAUR"}
Copying Records Between Tables
When a record is assigned to another variable, all of its properties are copied, but the copy does not keep a connection to the database. This is by design, to avoid unexpected consequences. The database connection can be retained in the copy by using the as record operator to indicate that the new copy should also be a record.
The same effect could also be achieved for a single record by making a reference to the original record, but this technique will not work for a list of records. Using the as record operator is the preferred approach.
Example:
set member to the record of memberTable where memberNumber is desiredMemNum -- The member variable HAS a connection to the database
put member into newMember -- newMember has NO connection to the database
put member as record into member2 -- member2 HAS a connection to the database
The Is a Record Operator
The is a record, operator can be used to test whether a value is an actual record with a connection to a database, or simply a property list. This can be useful when troubleshooting SenseTalk scripts that work with a database connection.
Example:
Set myDB to {type:"Excel", file:"~/Desktop/ClubData.xlsx", Writable:yes} -- Define the database connection, whether Excel or ODBC.
put table "Members" of myDB into MemberTable-- Pull the reference information for the table you need to modify
put {NameFirst:"Heidi", NameLast:"Mullers", StartDate:"2019-01-02"} into MemberToAdd -- This could also be done recursively using Iterators, if adding numerous members to the database table
add record MemberToAdd to table "Members" of myDB -- Add the record to the table in the database
Put the Record of MemberTable where "NameFirst" is "Heidi" into member3-- Retrieve the record you just added and store it in a variable
if member3 is a record then log "Success!" -- Verify that the record is an actual record with an active database connection
Updating Values in a Database Record
To update any value within a database table, simply change the value in the record corresponding to that row in the table, and that value in the database gets updated automatically.
This feature of automatically updating the database when values in a record are changed is turned on by default, and can be disabled if manual updates are preferred. See Manual Database Updates for more information. This feature may not work with some databases (such as MSSQL). In that case, disable manual updates.
Example:
Using a Where Expression to select a record, and then modifying one of the values (expirationDate) in that record:
set memberTable to table "member" of myDB -- Access the database and pull a table of data into a variable called memberTable
set member to the record of memberTable where memberNumber is 3152134 -- Using the where clause to select a record based on specific values, in this case the memberNumber
add 30 days to member's expirationDate -- This will update the value in the "expirationDate" column of that specific member record in the database
Example:
This example shows how you might make database table changes for member expiration dates using a repeat loop to iterate over multiple records:
set memberExpirationDates to table "memberInfo" of myDB -- Accesses a database and pulls the data from table "memberInfo" into a variable called memberExpirationDates
put [4260,1059,1685] into expirationstoUpdate -- Stores a list of member numbers in a variable to be iterated over
repeat with each Num in expirationstoUpdate -- Iterates over the list of member numbers using a repeat loop
set member to the record of memberExpirationDates where memberNumber is Num -- Retrieve the record for the particular member number being worked with each iteration
add 1 year to member's expirationDate -- Extend the expiration date for that member by one year
end repeat
Example:
This more detailed example shows how to update a database table with changes to colors and brightness:
set palette to table "colorpalette" of myDB -- Sets the variable 'palette' to a table object representing database table 'colorpalette'
set myColor to the record of palette where ID is 3 -- Sets the variable 'myColor' a record object for the corresponding record from the database
put "black" into myColor's color -- Updates the database record, changing the color value to "black"
log MyColor -- Logs {brightness:"9", color:"black", ID:"3"}
set myColor to the record of palette where Color is "green" and brightness is "14"
add 10 to myColor's brightness -- Updates the database record, adding 10 to the current brightness value
log myColor -- Logs {brightness:"24", color:"green", ID:"4"}
Adding New Records to a Database Table
To add records to a database table, first construct one or more property lists containing properties that match the columns in the table. Then use the add record or add records command to add those records to the table.
Add Record/ Add Records Command
Behavior: Adds a specified record to a specified table in the database. For adding multiple records, use the Add Records variation.
Parameters: A property list to be added to the database as a record, and the name of a table in the database where that record should be added.
Example:
set myTable to table "Members" of myDB -- Reads the table Members into a variable in the script, called myTable
set newMember to {firstName:"Fritz", lastName:"Geisler", memberNumber:4307}
add record newMember to myTable -- newMember must have all required values. myTable refers to the table Members of the myDB database.
Example:
The below example uses the result to obtain Eggplant Functional test result details and store them in a database using the Add Record command. For more on the commands and functions used below, see RunWithNewResults and the result.
//The below code writes the result of RunWithNewResults to a database table
set myDB to {type:"odbc", DSN:"mySQLDB", user:"carrie", password:"password123"}
RunWithNewResults "CreateWorkspaceTestCase"
put the result into myResult -- Stores the result property list in a variable to make referencing its different properties possible later
set newResult to {ExecutionTime:myResult.RunDate, TestName:"CreateWorkspaceTestCase", Result:myResult.status} -- Constructs the new record based on "the result." ExecutionTime, TestName, and Result are column names in the database table.
Add record newResult to table "results" of myDB -- Adds the new record to the table
Add Records Variation
Using the add records command, a long list of records may be added to the table with a single command.
A database table may have various rules associated with it. For example, some columns may be optional and others be required to have a value assigned in every record. Or a column such as memberNumber might be required to be unique meaning that each record in the table must have a different memberNumber value. If you attempt to add a record to the database that violates one of these rules, it will be rejected, and an exception will be thrown in the script.
Deleting Records from a Database Table
Records can be deleted using either the Delete Record command (can also be plural , i.e. Delete Records). This command can be modified using a Where expression to specify qualities of the records to be deleted.
Delete Record/ Delete Records Command
Behavior: This command deletes a previously-fetched record from a database table.
Parameters: The name of the variable containing the previously fetched record or records to be deleted.
Example:
The delete record command is used to delete a previously-fetched record from a database table.
put the records of table "colorpalette" of myDB where ID is 2 into colortoDelete
delete record colortoDelete
Specifying Records Using a Where Expression
Where Expressions allow you to select desired records from a table when using the record or records function, the number of records function, or the delete recordscommand.
Example:
This example uses a where clause to specify which records need to be deleted , and then uses the delete records command to delete those records..
put table "colorpalette" of myDB into myColors
delete records from myColors where brightness is greater than 10
Example:
This example reads multiple records from the database using Where, and then uses the delete records command to delete those records.
put the records of myTable where state is "Colorado" into doomedRecords
delete records doomedRecords
Automatic vs. Manual Updates
Database updates are saved automatically by default. However, using the autoSaveDatabaseUpdates global property, you have the ability to change this setting so that database updates are manual. See Automatic vs Manual Updates for more information.