Initially, the script DB is a copy of the web application’s DB (possibly with less tables and columns or records). For each logical step, the script triggers an operation in the browser and it also updates its own DB to reflect that operation. Updates in script DB don’t need to be exactly same as updates in app DB. The script only updates columns/tables that we use for validation. The script compares the result page of the web app to the updated script data.
If the app or the script fails, their databases can get out-of-sync. That causes non-obvious knock-on errors later. That should normally happen only if
the script was incorrect (likely in the design phase), or
the web application is incorrect (this is what you want to detect by testing), or
there are errors outside both the script system and the web app (e.g. connectivity/infrastructure down…)
Re-loading the data
If the script and web app data is out of sync, you we want to re-load it. SeLite doesn’t automate this. If we (ever) automate it, then we want it to
periodically take synchronised snapshots of script and app DB
back up the script state (e.g. Selenium variables), both script and web app DB and take a screenshot, so that you can identify the problem later
If the error was outside of both the app and the script, and the script can detect that, it could
reload app DB and script DB (if needed - if the failed operation is likely to apply some incomplete modifications, or modifications different between the app and the script)
sleep (ideally for a period that increases exponentially on each re-try) and re-try (repeating this for up to a limited number of times).
Otherwise, the error is in the app or in the script. Re-loading the data won’t fix the script neither the application. It is beneficial, however, when user needs to use other parts (before the failed part, whether of the app or the script, gets fixed).
There will also be a need to reload both DBs, even if everything is healthy (app and script are OK and both DBs in sync). That’s when we reach designed limits of the application. For example, the app may limit the number of submissions per user per day without a way to remove them through the application’s web interface.
Loose script data
Some fields are difficult to replicate from app DB to script DB during the runs. Those are
IDs fields of inserted records
non-trivial references
create/update/delete timestamps
IDs of new records
Many of the following notes are for Postgres or SQLite. However, some notes and most of the ideas are not DB-specific.
Navigating by record IDs is needed e.g. for security testing to ensure that a user can’t get inappropriate access. In such a case the script can’t navigate through the web app to the forbidden page, so it needs to know the ID value to generate the link (to verify that the access is denied).
When the script creates a new record in its DB, it needs to set its ID to reflect the ID in the app DB. Methods:
Generate/assume
the last used values are the same between script DB (SQLite) and app DB, and script DB (SQLite) uses same method to generate the new IDs as app DB
this is optimistic - it breaks if one side gets broken temporarily and the last used values get out of sync
script SQLite tables can use following options (as relevant to SeLite)
INTEGER PRIMARY KEY (but not INT PRIMARY KEY), which
generates an ID one larger than the largest ID currently present (unless the IDs reach 64bit signed limit)
reuses IDs of deleted rows (but only if they had the highest IDs, i.e. there are no existing rows with a higher ID)
INTEGER PRIMARY KEY AUTOINCREMENT
it generates an ID one larger than any ID ever present
it won’t generate a new ID once there has been an ID reaching 64bit signed limit (OK in real world)
the import filter can set set next values of SQLite INTEGER PRIMARY KEY AUTOINCREMENT columns in special table sqlite_sequence (which is present only after there is at least one table with INTEGER PRIMARY KEY AUTOINCREMENT column).
both options reuse IDs from rolled back transaction
(both options require the key to be INTEGER, not INT)
if a transaction dies/is rolled back, it increases the sequence anyway, and any next transaction will use the next value. That’s incompatible with SQLite INTEGER PRIMARY KEY and SQLite INTEGER PRIMARY KEY AUTOINCREMENT.
when importing/loading app DB, last values of all (relevant) ID sequences must reflect the highest IDs in the respective tables. I.e. either
the last created record in each table still exists and it didn’t get deleted. Otherwise Postgres sequence would use a subsequent ID for the next new record, while SQLite would use the ID of the deleted record (since the data dump doesn’t contain the deleted record, and SQLite doesn’t know that it has existed in past), or
when/just after the data dump from Postgres to SQLite, reset Postgres sequences to return the next available id for each relevant table. E.g. SELECT setval('your_table_id_seq', (SELECT MAX(id) FROM your_table)+1)
simplified approach
on app success: The script creates new SQLite record(s) and commits transaction(s), only after it detects that the web app processed the request successfully.
on app failure: If the web app failed and it didn’t commit the INSERT transaction, it would increase the sequence. The script doesn’t know that, its next available ID in that table will get out of sync, and next time the app & script insert a new record to the same table (in their respective DBs) the script will have knock on errors
ideal approach (not easy)
on app success
script creates a new record (commiting the transaction)
on failure where the app starts an INSERT within a transaction, but it doesn’t commit
script could mark the affected table as unsure; then the next time there is a successful insert by the app, it captures its ID and compares it
script creates a new record (commiting the transaction), then it deletes the new record
this app failure is not easy to differentiate from the following one
on app failure without an INSERT
detected e.g. as a session/single-sign out timeout, app DB down etc.
script doesn’t create a new record
if the web app is not tied to Postgres, an alternative is to export the app data into a different DB that doesn’t increase sequences by uncommitted transactions and run the app against that different DB
the simplest situation is if the web app can work with SQLite; then the DB import process means just copying the file
other types of DB may need to reset their sequences
Capture
after submitting it shows a view page of the new submission. Its URL contains the new ID. Selenium IDE can extract it and save it in script DB.
otherwise (if the application/module doesn’t present the view page after submission) the script
navigates the application to a page that lists the records
navigates the application to sort those submissions so that the latest is shown first
identifies a link to the first submission on the list (XPath is good for that)
if the link is nice (i.e. it contains the ID of the submission)
extracts the ID from the link
or (e.g. the link is complex or a Javascript link, or it doesn’t contain the ID in an obvious form)
navigate to the linked page to show the submission
extract the ID somehow
otherwise: use timestamp-based and list item position-based navigation and comparison
useful for testing security: the script logs on to the application as the other user (security victim), gets a listing of the forbidden records, captures id/link of the record(s) and stores it (in the script session or its DB). Then it logs out, logs in as the other user (security intruder) and tries to access the stolen URL(s).
Non-trivial references
Non-trivial references are based on
random-based values, or
system-specific values not replicable in the script (e.g. univeraly unique IDs), or
data/configuration values not represented in script DB.
If you want to validate them, you need to make the script capture those values and store them in script DB whenever they get created/updated in the app.
Reloading databases and creating/updating user passwords
then have the script use that data (via a back door). That’s fairly easy between SeLite and webservices. However, if the data source is a of some other type, then you’ll need either (in order of complexity)
a webservice adapter on the server-side, or
a plugin/extension for Firefox (possibly a binary XPCOM component), or
custom-compiled Firefox with a target data source client and a Core extension of Selenium IDE in Javascript
If the app writes to the external data source (directly or indirectly), then treat it like another app DB. Have your script use (read and update) a writable copy of that external data (transformed to SQLite). You’ll need script(s) to populate those table(s), so you can change and re-load the copy in future.
No column name toString
You must not have a DB column with name toString. That’s because Javascript objects representing DB records have method toString() for convenience.