Difference between revisions of "Database Timelines"

From Recital Documentation Wiki
Jump to: navigation, search
(Viewing a timeline)
(What are Database Timelines?)
 
(6 intermediate revisions by 2 users not shown)
Line 1: Line 1:
 
==Database Timelines==
 
==Database Timelines==
 
===What are Database Timelines?===
 
===What are Database Timelines?===
 
+
Database timelines provide row versioning for Recital 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.
Database timelines provide row versioning for Recital database applications. 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.
+
  
 
===Using Database Timelines===
 
===Using Database Timelines===
 
+
To enable database timelines all you need to do is issue the [[SET TIMELINE|set timeline on]] command in your Recital configuration file.
To enable database timelines all you need to do is issue the '''SET TIMELINE ON''' command in your Recital configuration file.
+
  
 
<pre>
 
<pre>
SET TIMELINE ON
+
set timeline on
 
</pre>
 
</pre>
  
 
====Viewing a timeline====
 
====Viewing a timeline====
 
 
There are 2 ways to view a timeline.
 
There are 2 ways to view a timeline.
  
* The '''LIST TIMELINE''' command
+
* The [[LIST TIMELINE|list timeline]] command
* The '''SELECT * FROM SYSTIMELINE''' command
+
* The [[SYSTIMELINE|select * from systimeline]] SQL command
  
 
<pre>
 
<pre>
LIST TIMELINE [RANGE begin [,end]] [FOR condition] [TO FILE filename]
+
list timeline [range <begin as string-date> [, <end as string-date>]] [for <condition as logical>] [to file <filename as character>]
 
</pre>
 
</pre>
  
Line 26: Line 23:
  
 
<pre>
 
<pre>
LIST TIMELINE FOR table = "customers"
+
list timeline for table = "customers"
 
</pre>
 
</pre>
  
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:"  e.g.
+
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.
  
 
<pre>
 
<pre>
// list the timeline since 1st october 2009
+
// list the timeline since 1st October 2009
LIST TIMELINE RANGE "20091001"  
+
list timeline range "20091001"  
  
// list the timeline between the 1st and 7th of october 2009  
+
// list the timeline between the 1st and 7th of October 2009  
LIST TIMELINE RANGE "20091001","20091007"  
+
list timeline range "20091001","20091007"  
 
</pre>
 
</pre>
  
SQL SELECT can be used with the Recital Data Object functions (rdo_xxx() functions) to traverse the timeline and generate html if required e.g.
+
[[SQL SELECT]] can be used with the Recital Data Object functions (rdo_xxx() functions) to traverse the timeline and generate html if required e.g.
  
<pre>
+
<code lang="recital">
echo "Timeline report\n"
+
echo "Timeline report<br>"
 
results = rdo_query("SELECT * FROM systimeline WHERE between(timestamp, '20091001', '20091007')")
 
results = rdo_query("SELECT * FROM systimeline WHERE between(timestamp, '20091001', '20091007')")
 
foreach results as row
 
foreach results as row
     echo "Table " + row["TABLE"] + " changed by " + row["USER"] + " on " + ROW["TIMESTAMP"]
+
     echo "Table " + row["TABLE"] + " changed by " + row["USER"] + " on " + row["TIMESTAMP"]
 
     echo ", command was " + row["COMMAND"] + "<br>"
 
     echo ", command was " + row["COMMAND"] + "<br>"
 
endfor
 
endfor
</pre>
+
results = null
 +
</code>
  
 
====Undoing database changes====
 
====Undoing database changes====
 
+
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''' command. The '''RANGE''' and '''FOR''' clauses can also be specified in the same way as the '''LIST TIMELINE''' command e.g.
+
  
 
<pre>
 
<pre>
ROLLBACK TIMELINE [RANGE begin [, end]] [FOR condition]
+
rollback timeline [range <begin as string-date> [, <end as string-date>]] [for <condition as logical>]
 
</pre>
 
</pre>
  
 
====Clearing a  timeline====
 
====Clearing a  timeline====
 
+
The [[CLEAR TIMELINE|clear timeline]] command will reset a timeline.
The '''CLEAR TIMELINE''' command will reset a timeline.
+
  
 
<pre>
 
<pre>
CLEAR TIMELINE
+
clear timeline
 
</pre>
 
</pre>

Latest revision as of 12:42, 25 October 2011

Database Timelines

What are Database Timelines?

Database timelines provide row versioning for Recital 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.

Using Database Timelines

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

set timeline on

Viewing a timeline

There are 2 ways to view a 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 2009
list timeline range "20091001" 

// list the timeline between the 1st and 7th of October 2009 
list timeline range "20091001","20091007" 

SQL SELECT can be used with the Recital 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

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