Timelines [examples]

Clears database timeline transaction information 

clear timeline

Check if timelines are enabled for the current or specified cursor/table
set timeline on
open database southwind
use orders
// check if timelines are enabled for the current or specified cursor/table 
? timeline()
.T.

https://www.lianja.com/doc/index.php/TIMELINE()


Lists database timeline transaction information 
list timeline range dtos(date()-10), dtos(date()) for table = "example"

https://www.lianja.com/doc/index.php/LIST_TIMELINE


Displays database timeline transaction information
display timeline range dtos(date()-10), dtos(date()) for table = "example"

https://www.lianja.com/doc/index.php/DISPLAY_TIMELINE


Rolls back transactions stored in the database timeline
rollback timeline range dtos(date()-10), dtos(date()) for systimeline.table = "example"

To see how many transactions would be rolled back prefix the rollback timeline command with explain.

explain rollback timeline range dtos(date()-10), dtos(date()) for systimeline.table = "example"

https://www.lianja.com/doc/index.php/ROLLBACK_TIMELINE


Enable global database timeline override 
set systimeline on
set timeline on

https://www.lianja.com/doc/index.php/SET_SYSTIMELINE


Enable database timelines for row versioning 
set timeline on

https://www.lianja.com/doc/index.php/SET_TIMELINE


Stores database timelines row versioning records 
SELECT * FROM system!systimeline WHERE between(timestamp, '20171001', '20171007')

https://www.lianja.com/doc/index.php/SYSTIMELINE


Database timelines can be enabled for all the tables in a database:

alter database southwind metadata "timeline=on"

or for individual tables:

open database southwind
alter table customers metadata "timeline=on"
alter table orders metadata "timeline=on"

and to disable timelines:

alter database southwind metadata "timeline=off"
open database southwind
alter table customers metadata "timeline=off"
alter table orders metadata "timeline=off"

 

To view a timeline for a particular table e.g.

list timeline for table = "customers"

To view a timeline for a particular date or date range, use the range or since keywords. Notice that the dates are encoded as a string in the format “YYYYMMDDHH:MM:SS:”. This can be abbreviated e.g.

// list the timeline since 1st October 2017
list timeline since "20171001"
 
// list the timeline for 1st October 2017 only
list timeline range "20171001" 
 
// list the timeline between the 1st and 31st of October 2017 
list timeline range "20171001","20171031" 
 
// send the listing to a text file
list timeline range "20171001","20171031" to file thismomth.txt

SQL Select

SQL SELECT can be used to query the system systimeline table directly:

select * from system!systimeline

SQL SELECT can also be used with the Lianja Data Object functions (rdo_xxx() functions) to traverse the timeline and generate html to populate a WebView Section if required e.g.

<%@ Language=VFP %>
<html>
<head>
<style>
table {
    font-family: arial, sans-serif;
    border-collapse: collapse;
    width: 100%;}
td, th {
    border: 1px solid #dddddd;
    text-align: left;
    padding: 8px;}
tr:nth-child(even) {
    background-color: #dddddd;}
</style>
</head>
<body>
<%
	results = rdo_query("SELECT * FROM system!systimeline WHERE between(timestamp, '20171001', '20171031')")
	? "<h3>Timeline report</h3>"
        ? "<table>"
        ? "<tr><th>Table</th><th>User</th><th>Date</th><th>Command</th></tr>"
	foreach results as row
    	  ? "<tr><td>" + row["TABLE"] + "</td><td>" + row["USER"] + "</td><td>";
          + left(row["TIMESTAMP"],8) + "  " + right(row["TIMESTAMP"],8)+ "</td><td>" + row["COMMAND"] + "</td></tr>"
	endfor
	? "</table>"
	results = null
%>
</body>
</html>

Rollback Timeline

You can undo database changes with the rollback timeline command. The range and for clauses can also be specified in the same way as the list timeline command e.g.

rollback timeline [range <begin as string-date> [, <end as string-date>]]
 [for <condition as logical>]

To see how many transactions would be rolled back prefix the rollback timeline command with explain.

explain rollback timeline [range <begin as string-date> [, <end as string-date>]]
 [for <condition as logical>]

Clearing a timeline

The clear timeline command will reset a timeline.

clear timeline

 

Advertisements

Timelines

Audit trails and data security go hand in hand and it is highly desirable that this functionality is built-in.

Lianja database timelines provide row versioning for database tables for all CRUD operations performed on data. Whenever a change is made to a table that is timeline enabled then delta changes are automatically recorded for each transaction. Changes made to any tables that are timeline enabled can be undone much like you would undo changes to program code that you edit in a text editor.

Database timelines record who did what from where, when did they do it and what did they change. An invaluable feature for SaaS applications.In Lianja 4.1 you can enable timelines globally for a database.

Code:
alter database yourdatabase metadata "timeline=on"

This will enable timelines for all non temporary tables contained within the database. You can disable timelines for individual tables like this:

Code:
alter table yourtable metadata "timeline=off"