How to aggregate different tables in one

[gianluca]

Hi,
this is my last dekiscript work. This script extracts data from tables in different pages and aggregates in one tabular table.

This script works if you put in any page exactly the same table that you use to get fields. If you add or remove some fields the script is tolerant, but you miss a piece of information. It is also possible to pass a list of fields that you want to extract.

- Create a template page with a table with 2 columns: column 1 contain fields name, column 2 contain data,
- Use html button tag to identify fields because it's harder (but not impossible) for users to involuntary modify them in the editor (if you want to use TD instead, simply modify xpath expression)
- Mark the first cell of first row of template with a key name (this is required to speed up lucene search)
- extracts fields from the first template column
- query pages (using wiki.getsearch function) that contain key name
- parse pages and extract fields and data
- display all data in an aggregate table
This script use wiki.getsearch function so you must wait that lucene index a new page before it appear in results.
Application: create an address-book and list contacts in an aggregate table or list projects informations

/* ------------------- CONFIG ----------------------------- */
var template = "Template:AggregateTables";
var fields = [];
var key = "AggregateTables";
var search = "path:Wiki/* AND content:"..key;
/* ----------------------------------------------------------- */
if ( string.Length(template) > 0 &&  #fields == 0) {
  /* extract field from first column of template table */
  var page = wiki.page(template);
  var tables = xml.list(page,"//table[normalize-space(tbody/tr/th/button)='".. key .."']", nil, true);
  foreach (var tab in tables) {
        let fields = xml.list(web.xml(tab),"//tr/td/button",nil,false);
  }
}
/* fields; */
var r = wiki.getsearch(search,1000,'title');
var output = "";
let output = output .. "<table border='1' cellpadding='4' cellspacing='0'>";
let output = output .. "<tr bgcolor='#dddddd'><td></td>";
foreach (var i in fields) {
  let output = output .. "<td><strong>" .. i .. "</strong></td>";
}
let output = output .. "</tr>";
foreach (var p in r) { 
  var tables = xml.list(wiki.Page(p.path),"//table[normalize-space(tbody/tr/th/button)='".. key .."']", nil, true);
  foreach (var tab in tables) {
     let output = output .. "<tr>";
     let output = output ..  "<td>" .. web.link(p.uri, "V") .."</td>";
     foreach ( var field in fields) {
     var row = web.list(tab, "//tr[normalize-space(td/button)='".. field .."']",nil,true);
     if ( (string.Length(row[0])) == 0) {        
        let output = output .. "<td></td>";
     } else {
        var cell = web.text(row[0],"/tr/td[2]",nil,true);
        let output = output .. "" .. cell .. "";
     }
   }
   let output = output .. "</tr>";
  }
}
let output = output .. "</table>";
web.html(output);

Script example at http://www.intertesto.com/Wiki/DekiS...te_tables_data

Gianluca

[SteveB]

Wow, this is some pretty amazing coding in DekiScript. Your Deki-Fu is very strong!

Here a couple of tricks to save on typing.

Instead of doing:

string.length(text)

do

#text

Instead of doing:

let output = output .. text

do

let output ..= text

[SteveB]

I have question though. What is the purpose of the first "foreach" loop?

foreach (var tab in tables) {
  let fields = xml.list(web.xml(tab),"//tr/td/button",nil,false);
}

The "tables" variable is initialized to the first element that matches the dynamic xpath expression "//table[normalize-space(tbody/tr/th/button)='some-value'". However, the xml.list then uses another xpath that starts with "//", which means the it will start again from the root of the document. If you want to search inside the matched "tables" variable, use ".//" to limit the search to that table only. Finally, the "fields" variable is being overwritten for each iteration. Was that intentional?

[SteveB]

Another way to generate the HTML is to use DekiScript attributes on HTML instead of building a string and converting it. That's usually my preferred method.

I took a stab at converting your sample to it, but didn't test it, so it likely won't run, but it should give you an idea what it looks like. Note, you'll have to paste it into the editor using the HTML mode.

<div block="
/* ------------------- CONFIG ----------------------------- */
var template = 'Template:AggregateTables';
var fields = [ ];
var key = 'AggregateTables';
var search = 'path:Wiki/* AND content:'..key;
/* ----------------------------------------------------------- */
var xpath = '//table[normalize-space(tbody/tr/th/button)=\''.. key ..'\']';
if (#template > 0 &&  #fields == 0) {
  /* extract field from first column of template table */
  var page = wiki.page(template);
  var tables = xml.list(page,xpath, nil, true);
  foreach (var tab in tables) {
    let fields = xml.list(web.xml(tab),'//tr/td/button',nil,false);
  }
}
/* fields; */
var tables = [ ];
foreach (var p in wiki.getsearch(search,1000,'title')) { 
  let tables ..= [ xml.list(wiki.Page(p.path),xpath, nil, true) ];
}">
  <table border="1" cellpadding="4" cellspacing="0">
    <tr bgcolor="#dddddd">
        <td></td>
          <td foreach="var i in fields"><strong>{{ i }}</strong></td>
    </tr>
    <tr foreach="var tab in tables">
      <td>{{ web.link(p.uri, "V") }}</td>
      <td foreach="var field in fields" block="var row = web.list(tab, '//tr[normalize-space(td/button)=\''.. field ..'\']",nil,true)">{{ #row[0] ? web.text(row[0],"/tr/td[2]",nil,true) : nil }}</td>
    </tr>
  </table>
</div>

[gianluca]

Thanks Steve, your suggestions are really useful, I try to include them and review my dekiscript code

DekiScript Code

/* ------------------- CONFIG ----------------------------- */
    var template = 'Template:AggregateTables';
    var key = 'AggregateTables';
    var search = 'path:Wiki/* AND content:'..key;
    var fields = [];
/* ---------------------------------------------------- */    
var xpath = '//table[normalize-space(tbody/tr/th/button)=\''.. key ..'\']';
if (#fields == 0) {
  var page = wiki.page(template);
  var tables = xml.list(page,xpath, nil, true);
  let fields = xml.list(web.xml(tables[0]),'//tr/td/button',nil,false);
}
var r = wiki.getsearch(search,1000,'title');
var output = "<table border='1' cellpadding='4' cellspacing='0'>";
let output ..= "<tr bgcolor='#dddddd'><td></td>";
foreach (var i in fields) {
  let output ..= "<td><strong>" .. i .. "</strong></td>";
}
let output ..= "</tr>";
foreach (var p in r) { 
  var tables = xml.list(wiki.Page(p.path),"//table[normalize-space(tbody/tr/th/button)='".. key .."']", nil, true);
  foreach (var tab in tables) {
     let output ..= "<tr>";
     let output ..= "<td>" .. web.link(p.uri, "V") .."</td>";
     foreach ( var field in fields) {
     var row = web.list(tab, "//tr[normalize-space(td/button)='".. field .."']",nil,true);
     if ( (string.Length(row[0])) == 0) {        
        let output ..= "<td></td>";
     } else {
        var cell = web.text(row[0],"/tr/td[2]",nil,true);
        let output ..=  "" .. cell .. "";
     }
   }
   let output ..= "</tr>";
  }
}
let output ..=  "</table>";
web.html(output);

Html-DekiScript code

<table border="1" cellpadding="4" cellspacing="0" block="
    var template = 'Template:AggregateTables';
    var fields = [ ];
    var key = 'AggregateTables';
    var search = 'path:Wiki/* AND content:'..key;
    var xpath = '//table[normalize-space(tbody/tr/th/button)=\''.. key ..'\']';
    if (#fields == 0) {
      var page = wiki.page(template);
      var tables = xml.list(page,xpath, nil, true);
      let fields = xml.list(web.xml(tables[0]),'//tr/td/button',nil,false);
    }
    var r = wiki.getsearch(search,1000,'title');
    var tables = [ ];
    var arr = [ ];
    foreach (var p in r) {
      let tables = xml.list(wiki.Page(p.path),xpath, nil, true) ;
      if (#tables) {
        foreach (var tab in tables) {
          let arr ..= [[tab,web.link(p.uri, 'V')  ]];
        }
      }
    }">
    <tbody>
        <tr bgcolor="#dddddd">
            <td>-</td>
            <td foreach="var i in fields"><strong>{{ i }}</strong></td>
        </tr>
        <tr foreach="var a in arr">
            <td>{{web.html(a[1])}}</td>
            <td foreach="var field in fields" block="var row = web.list(a[0], '//tr[normalize-space(td/button)=\''.. field ..'\']',nil,true)">{{ #row[0] ? web.text(row[0],"/tr/td[2]",nil,false) : nil }}</td>
        </tr>
    </tbody>
</table>

[gianluca]

Is there some performance difference in using pure dekiscript or html-dekiscript notation? What about converting dekiscript in an extension?

[craigsivils]

This is really cool stuff, but as a database developer trying to learn front end coding/xml... I'm not making much headway on this one.

I would like to create a wiki page where the top half does some calculations based upon raw data entered in a table on the lower half of the wiki page. Example calculations would be average/min/max of a specific column. Average/min/max/counts of that columns across rows that meet some criteria.

I can traverse through the data sets, my issue is trying to use the xml.list to get the data from the second table. I haven't even been able to get a simple example to work.

Any advice?

[neilw]

This is an interesting piece of code. I've been working on something very similar, and struggled with certain aspects that could have been better solved using some of the ideas here. If nothing else, I learn that I need to become more of an xpath master; it would have saved me some grief.

The idea to read the relevant fields from the template is a good one, and makes the table generator very generic and flexible. Nice.

Two things I did differently, perhaps of interest:

1. Rather than make the visible button with the key, I put the key in a <span style="display:none;"> right before the table. You could also, I suppose, hide it in any element in the first column of the table. This provides the necessary key for Lucene without adding any visual clutter.
2. I don't like the look of the buttons in the left column, and the fact that they invite the user to push them but have no effect. But I was equally concerned about letting users break anything. So I chose an appalling but effective solution: the top level table is only one column wide; each row contains a 2-column table. To that table I assign a unique classname (or ID, either way) identifying the field. The only way a user can muck that up is by deleting a row entirely, and trying to re-add it. Were I a better xpath jockey, I would probably been able to make it work by just tagging the left hand column with the field name, and been satisfied with that (still hard for the user to muck up.) Oh, I have the table styles set up that you can't really tell it isn't one big table, but I don't have quite the fine control over border appearance that I'd otherwise have.

[SteveB]

gianluca,

Using HTML attributes is more efficient than building the HTML as a string and then embedding it into the document via web.html. Also, by using the attributes, you ensure that you always generate a valid document, which is a big plus! There is a performance benefit by moving the code into an extension, because Deki keeps extensions in memory at all time (or until the DekiScript service is restarted). Hence, you don't incur the cost of parsing it again. Howeve, we're working on adding the same optimization to regular pages, so the performance benefits will be short lived.

The bigger gain with XML extensions is how easily you can share it! For example, the default extensions in Deki are hosted at http://scripts.mindtouch.com. If we find a bug or want to add a new function to it, we simply update the script at that location. Then, whenever a Deki restarts, it will get the updates automatically!! That's really nice, imo.

[sborny]

Hi There,

I am completey new to dekiscript and am trying to do exactly what this script says it does, but i can not get it working. I am not sure what I have to put where on both the tempate and the page with this script in. What do I need to change in the actual script. I have been trying all sorts of things to get this working for hours now, and my ignorrance is really getting to me now.

Any help would be very much appreciated.