Difference between revisions of "Understanding database timelines in Lianja"

From Lianjapedia
Jump to: navigation, search
(→‎SQL Select)
 
(47 intermediate revisions by 2 users not shown)
Line 1: Line 1:
−
==See Also==
+
{{DISPLAYTITLE:Understanding Database Timelines}}
−
[[:Category:Database Timelines|Database Timelines Commands]]
+
  
−
==What are Database Timelines?==
+
=See Also=
−
Database timelines provide row versioning for Lianja database tables. 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.
+
[[ALTER DATABASE]], [[CLEAR TIMELINE]], [[:Category:Database Timelines|Database Timelines Commands and Functions]], [[Database Timelines (Video)]], [[LIST TIMELINE]], [[MetaData Editor]], [[ROLLBACK TIMELINE]], [[SET SYSTIMELINE]], [[SET TIMELINE]], [[SET TIMESTAMP]], [[SYSTIMELINE|Systimeline System Table]], [[TIMELINE()]], [[Timeline Section Attributes]]
  
−
==Using Database Timelines==
+
=What are Database Timelines?=
−
To enable database timelines all you need to do is issue the [[SET TIMELINE|set timeline on]] command in your Lianja configuration file.
+
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.
  
−
<pre>
+
Database timelines record who did what from where, when they did it and what they changed.
−
set timeline on
+
−
</pre>
+
  
−
Timelines can be enabled on a per App basis using the '''Timelines Enabled''' setting in the [[App Settings]].  Individual sections also have a '''Timelines Enabled''' attribute in their [[:Category:Attributes#Sections|Attributes]].  The '''Timelines Enabled''' App Setting must be checked (True) for the section '''Timelines Enabled''' attribute to be applied.
+
<div style="height:80px;margin-bottom:5px;padding:5px;border:0px solid orange;border-left:5px solid orange;background:#fff8dc;vertical-align:middle;position:relative;">
 +
[[File:bm-noteicon.png|top|40px|link=]]<div style="position:absolute;top:3px;margin-bottom;bottom:5px;margin-left:50px;"><b> Quick Start Guide</b>
 +
Database timelines can be enabled for all the tables in a database:<br/>
 +
''alter database southwind metadata "timeline=on"''
 +
</div>
 +
<span style="height:6px;"> </span>
 +
</div>
  
−
===Viewing a timeline===
+
=Using Database Timelines=
−
There are 2 ways to view a timeline.
+
Database timelines can be enabled for all the tables in a database:
 +
<code lang="recital">
 +
alter database southwind metadata "timeline=on"
 +
</code>
 +
or for individual tables:
 +
<code lang="recital">
 +
open database southwind
 +
alter table customers metadata "timeline=on"
 +
alter table orders metadata "timeline=on"
 +
</code>
  
−
* The [[LIST TIMELINE|list timeline]] command
+
and to disable timelines:
−
* The [[SYSTIMELINE|select * from systimeline]] SQL command
+
<code lang="recital">
 +
alter database southwind metadata "timeline=off"
 +
</code>
 +
<code lang="recital">
 +
open database southwind
 +
alter table customers metadata "timeline=off"
 +
alter table orders metadata "timeline=off"
 +
</code>
  
−
<pre>
+
==Viewing a timeline==
−
list timeline [range <begin as string-date> [, <end as string-date>]] [for <condition as logical>] [to file <filename as character>]
+
Timeline data can be viewed in the following ways:
−
</pre>
+
 
 +
* In the [[#Data Workspace|Data Workspace]]
 +
* Using a [[#Timeline Section|Timeline Section]]
 +
* Using the [[#List Timeline|List Timeline]] command
 +
* Using [[#SQL Select|SQL Select]] command
 +
 
 +
===Data Workspace===
 +
[[{{ns:file}}:dataws1.png|450px|thumb|left|link={{filepath:dataws1.png}}|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.
 +
 
 +
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.
 +
 
 +
<br clear=all>
 +
 
 +
===Timeline Section===
 +
[[{{ns:file}}:section1.png|450px|thumb|left|link={{filepath:section1.png}}|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.
 +
 
 +
<br clear=all>
 +
 
 +
===List Timeline===
 +
<code lang="recital">
 +
list timeline
 +
[range <begin as string-date> [, <end as string-date>]] | [since <begin as string-date>]
 +
[for <condition as logical>]
 +
[to file <filename as character>]
 +
</code>
  
 
To view a timeline for a particular table e.g.
 
To view a timeline for a particular table e.g.
  
−
<pre>
+
<code lang="recital">
 
list timeline for table = "customers"
 
list timeline for table = "customers"
−
</pre>
+
</code>
  
−
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.
+
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.
  
−
<pre>
+
<code lang="recital">
−
// list the timeline since 1st October 2009
+
// list the timeline since 1st October 2017
−
list timeline range "20091001"  
+
list timeline since "20171001"
  
−
// list the timeline between the 1st and 7th of October 2009
+
// list the timeline for 1st October 2017 only
−
list timeline range "20091001","20091007"
+
list timeline range "20171001"  
−
</pre>
+
  
−
[[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.
+
// 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
 +
</code>
 +
 +
===SQL Select===
 +
[[SQL SELECT]] can be used to query the [[SYSTIMELINE|system systimeline]] table directly:
 
<code lang="recital">
 
<code lang="recital">
−
echo "Timeline report<br>"
+
select * from system!systimeline
−
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
+
 
</code>
 
</code>
  
−
===Undoing database changes===
+
[[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.
 +
 
 +
<code lang="recital">
 +
<%@ 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(left(timestamp,8), '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>
 +
</code>
 +
 
 +
==Undoing database changes==
 +
* In the [[#Data Workspace_2|Data Workspace]]
 +
* Using a [[#Timeline Section_2|Timeline Section]]
 +
* Using the [[#Rollback Timeline|Rollback Timeline]] command
 +
 
 +
===Data Workspace===
 +
[[{{ns:file}}:dataws2.png|450px|thumb|left|link={{filepath:dataws2.png}}|Data Workspace: Undo individual change]]
 +
 
 +
 
 +
 
 +
 
 +
 
 +
Click the undo icon at the far right of the row to undo that individual change.
 +
 
 +
You will be prompted to confirm the undo action.
 +
 
 +
<br clear=all>
 +
 
 +
[[{{ns:file}}:dataws3.png|450px|thumb|left|link={{filepath:dataws3.png}}|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.
 +
 
 +
You will be prompted to confirm the undo action.
 +
 
 +
<br clear=all>
 +
 
 +
===Timeline Section===
 +
[[{{ns:file}}:section2.png|450px|thumb|left|link={{filepath:section2.png}}|Timeline Section: Undo individual change]]
 +
 
 +
 
 +
 
 +
Click the undo icon at the far right of the row to undo that individual change.
 +
 
 +
You will be prompted to confirm the undo action.
 +
 
 +
<br clear=all>
 +
 
 +
[[{{ns:file}}:section3.png|450px|thumb|left|link={{filepath:section3.png}}|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.
 +
 
 +
You will be prompted to confirm the undo action.
 +
 
 +
<br clear=all>
 +
 
 +
===Rollback Timeline===
 
You can undo database changes with the [[ROLLBACK TIMELINE|rollback timeline]] command. The '''range''' and '''for''' clauses can also be specified in the same way as the [[LIST TIMELINE|list timeline]] command e.g.
 
You can undo database changes with the [[ROLLBACK TIMELINE|rollback timeline]] command. The '''range''' and '''for''' clauses can also be specified in the same way as the [[LIST TIMELINE|list timeline]] command e.g.
  
−
<pre>
+
<code lang="recital">
−
rollback timeline [range <begin as string-date> [, <end as string-date>]] [for <condition as logical>]
+
rollback timeline [range <begin as string-date> [, <end as string-date>]]
−
</pre>
+
[for <condition as logical>]
 +
</code>
 +
 
 +
To see how many transactions would be rolled back prefix the '''rollback timeline''' command with '''explain'''.
 +
 
 +
<code lang="recital">
 +
explain rollback timeline [range <begin as string-date> [, <end as string-date>]]
 +
[for <condition as logical>]
 +
</code>
  
−
===Clearing a  timeline===
+
==Clearing a  timeline==
 
The [[CLEAR TIMELINE|clear timeline]] command will reset a timeline.
 
The [[CLEAR TIMELINE|clear timeline]] command will reset a timeline.
  
−
<pre>
+
<code lang="recital">
 
clear timeline
 
clear timeline
−
</pre>
+
</code>
  
−
[[Category:Lianja Scripting Essentials]]
 
 
[[Category:Database Timelines]]
 
[[Category:Database Timelines]]

Latest revision as of 17:52, 27 March 2020


See Also

ALTER DATABASE, CLEAR TIMELINE, Database Timelines Commands and Functions, Database Timelines (Video), LIST TIMELINE, MetaData Editor, ROLLBACK TIMELINE, SET SYSTIMELINE, SET TIMELINE, SET TIMESTAMP, Systimeline System Table, TIMELINE(), 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.

Bm-noteicon.png
Quick Start Guide

Database timelines can be enabled for all the tables in a database:
alter database southwind metadata "timeline=on"

Using Database Timelines

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"

Viewing a timeline

Timeline data can be viewed in the following ways:

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.

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>]] | [since <begin 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 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(left(timestamp,8), '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>

Undoing database changes

Data Workspace

Data Workspace: Undo individual change



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

You will be prompted to confirm the undo action.


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.

You will be prompted to confirm the undo action.


Timeline Section

Timeline Section: Undo individual change


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

You will be prompted to confirm the undo action.


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.

You will be prompted to confirm the undo action.


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
Retrieved from "https://www.lianja.com/doc/index.php?title=Understanding_database_timelines_in_Lianja&oldid=23170"