Understanding database timelines in Lianja

Under Construction

See Also

Database Timelines Commands and Functions, Database Timelines (Video), Timeline Section Attributes,

What are Database Timelines?

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 they did it and what they changed.

Using Database Timelines

To enable database timelines all you need to do is issue the set timeline on command in your Lianja configuration file.

set timeline on

The Lianja configuration file is a text file called config.db located in the conf sub-directory of the lianja directory.

Note that set systimeline must also be on. This is on by default. To disable timelines globally, set systimeline off.

set systimeline off

App Settings: Enable timelines

Timelines can be enabled on a per App basis using the Timelines Enabled setting in the App Settings.

Section Attributes: Enable timelines

Individual sections also have a Timelines Enabled attribute in their Attributes. The Timelines Enabled App Setting (as above) must be checked (True) for the section Timelines Enabled attribute to be applied.

Viewing a timeline

Timeline data can be viewed in the following ways:

• In the Data Workspace
• Using a Timeline Section
• Using the List Timeline command
• Using SQL Select command

Data Workspace

Data Workspace: Timeline View

With a table open in the Data Editor, click the Timeline View toolbutton in the header to open the Timeline View.

The set commands set systimeline and set timeline must both be on.

In the Timeline View header the date range to be viewed can be selected: 'Today', 'Yesterday', 'This week', 'This month' or 'From' date and 'To' date.

The header also has a 'Refresh' option.

Timeline Section

Timeline Section

With a data-bound Section (e.g. Form, Grid, Canvas) on the Page, add a Timeline Section from the Form Tools.

Relate the parent Section to the child Timeline Section.

In the Timeline header the date range to be viewed can be selected: 'Today', 'Yesterday', 'This week', 'This month' or 'From' date and 'To' date.

The Section can be refreshed using the icon in the Section header.

List Timeline

list timeline
 [range <begin as string-date> [, <end as string-date>]]
 [for <condition as logical>]
 [to file <filename as character>]

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

list timeline for table = "customers"

To view a timeline since a certain date use the range keyword. Notice that the date range is 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 range "20171001" 

// list the timeline between the 1st and 31st of October 2017 
list timeline range "20171001","20171031" 

SQL Select

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

echo "Timeline report<br>"
results = rdo_query("SELECT * FROM systimeline WHERE between(timestamp, '20091001', '20091007')")
foreach results as row
    echo "Table " + row["TABLE"] + " changed by " + row["USER"] + " on " + row["TIMESTAMP"]
    echo ", command was " + row["COMMAND"] + "<br>"
endfor
results = null

Undoing database changes

• In the Data Workspace
• Using a Timeline Section
• Using the Rollback Timeline command

Data Workspace

Data Workspace: Undo individual change

Click the undo icon at the far right of the row to undo that individual change.

Data Workspace: Undo changes for date range

Select the data range in the Timeline View header.

Click the undo icon in the date sub-header to undo all the changes for that date range.

Timeline Section

Timeline Section: Undo individual change

Click the undo icon at the far right of the row to undo that individual change.

Timeline Section: Undo changes for date range

Select the data range in the Timeline header.

Click the undo icon in the date sub-header to undo all the changes for that date range.

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>]

Clearing a timeline

The clear timeline command will reset a timeline.

clear timeline