================================================================================
Release Notes - SQL plugin for Taxonomy Editor / Instance Creator and
                Instance Dashboard
All Rights Reserved, Copyright (c) FUJITSU 2008-2009
================================================================================

This is a plugin to Taxonomy Editor / Instance Creator and Instance Dashboard,
which is used for configuring the application table display by specifying an
SQL-like query.

[usage]
When you start the application, "Query Table" pane appears.
Fill with a query the text Area on the top of this pane and
click "Execute" button (or CTRL + Enter),
the result will be shown in the display table.

[query syntax]
The query syntax is as follows:

select [distinct] <expression>[, <expression>...] from <table_name> [, <table_name>...]
[where <condition>]
[group by <expression> [, <expression>...]]
[having <condition>]
[order by <expression> [asc | desc], [<expression> [asc | desc]]..]

If you specify "distinct", duplicated columns (if any) will be removed.

As for <table_name>, you can specify one of the following:

table name                  data content                   note
------------------------------------------------------------------------------------------
file                        files in DTS/instance
element                     element list                   -
presentation                presentation link              this table contains only resolved links.
calculation                 calculation link               this table contains only resolved links.
definition                  definition link                this table contains only resolved links.
label                       label link                     this table contains only resolved links.
reference                   reference link                 this table contains only resolved links.
unresolvedpresentation      presentation link              this table contains all links.
unresolvedcalculation       calculation link               this table contains all links.
unresolveddefinition        definition link                this table contains all links.
unresolvedlabel             label link                     this table contains all links.
unresolvedreference         reference link                 this table contains all links.
roletype                    role type
arcroletype                 arcrole type
context                     context                        Not available with Taxonomy Editor.
unit                        unit                           Not available with Taxonomy Editor.
fact                        item                           Not available with Taxonomy Editor.
footnote                    footnote link                  Not available with Taxonomy Editor.
calculationerror            facts with calculation error   Not available with Taxonomy Editor.
namespacedecl               namespace declarations
filerelationship            file relationships
inlinefact                  inline XBRL tags               available when opening inline XBRL files

As for "select" clause, some notes are in the following:
- You can specify multiple items. In such a case, specify them by
  separating them with comma (",").

- When you specify "*", it will imply all the columns supported in a given table.
  By trying "*" once anyway, you will see which columns are available in a given table.

As for <expression>, some notes are in the following:

- You can specify column names, operators(ex. +, -, *, /), functions,
  and number/string literals.
  Example:

    select value / 1000000, length(elementname), 100, "foo" from fact

  The list of supported functions will be described below.

- You can specify an alias column name using the "as" keyword.
  Example:

    select value as v from fact where v > 0

As for "from" clause, some notes are in the following:

- You can use inner join by following syntax:

    <table name1> join <table name2> [on <condition>]

  If you want to join more than two tables, use parenthesis as follows:

    (<table name1> join <table name2> [on <condition>]) join <table name3> [on <condition>]

  In a <condition>, equal operator("=") and "and" operator are only allowed.

- You can use a subquery in the "from" clause.
  Example:

    select element from (select * from fact where value < 0)

- You can specify an alias table name using the "as" keyword as follows:

    select f.value, l.label from fact as f join label as l on f.element = l.element

As for "where" clause and <condition>, some notes are in the following:

- In a "where" clause, you can specify formula of
  "<expression> <comparison_operator> <expression>" and "not", "and", and "or".
- <comparison_operator> is any one of {"=", "!=", "<", ">", "<=", ">="}.
- You can use a regular expression(embraced with slash (/) or backslash(\))
  with the operator "=" or "!=".
- You can use "in" conditional operator by following syntax :
  <column name> "in" "(" <select statement> ")"
  Return true when selected column value exists In the result of select statement
  following to "in".

- As we support regular expressions, "LIKE" is not available.

As for "order by" clause, some notes are in the following:

- In a "order by" clause, you should specify a expression to be sorted accompanied by
  either "asc" or "desc".
- When you want to sort by ascending order, specify a column name and "asc".
  When you want to sort by descending order, specify a column name and "desc".
  When "asc" / "desc" is omitted, it will be sorted by ascending order.

When "element" is included in <table_name> and when you double-click a row of
the table, the corresponding element declaration will be selected in other panes
in the application.

Similarly, when "Value" column is included, the corresponding item of that row
will be selected in other panes.

Also, you can use union/union all/except/intersect operators.

<select statement> <"union" ["all"]| "except" | "intersect"> <select statement>

Both select statement must specify same columns.

[supported functions]
- String functions

  concat(str1, str2 [, str3...])  ... Returns concatenated string

  length(str)                     ... Returns the length of a string

  lower(str)                      ... Returns the argument in lowercase

  substr(str, startIndex[, length])
                                  ... Return the substring of a string

  upper(str)                      ... Returns the argument in uppercase

  instr(str, searchstr, [,position [, occurrence]])
                                  ... Returns the start position of searchstr within str.
                                      Returns 0 if searchstr is not found.

- Number functions

  abs(num)                        ... Returns the absolute value

  ceil(num)                       ... Returns the smallest integer value not less than the argument

  floor(num)                      ... Returns the largest integer value not greater than the argument

  round(num)                      ... Rounds the argument

- aggregate functions

  avg(expr)                       ... Returns the average value

  count("*" | expr)               ... Returns a count of values

  max(expr)                       ... Returns the maximum value

  min(expr)                       ... Returns the minimum value

  sum(expr)                       ... Return the sum

- XBRL functions

  xbrl_label(element, [lang [, labelRole [, extendedLinkRole]]])
                                  ... Returns the label of the element.
                                      The argument "element" must be the column "element" or "parentelement"

  xbrl_explicitmember(dimItemNsURI, dimItemLocalName)
                                  ... Returns the dimension member QName of the dimension item
                                      with the specified namespace URI and local name.

  xbrl_roletype_label([lang])
                                  ... Returns the generic label of the role type element.


- Other functions

  if(condition, expr1, expr2)     ... If condition is true, returns expr1. Otherwise returns expr2.

[sample queries]

select * from element
  ... For all the columns, gets all the element declarations from the table.

select distinct StartDate, EndDate from context
  ... Gets all the start dates and end dates from the context table,
      duplication being removed.

select Value from fact where ElementLabel = 'Sales'
  ... Returns value of an item whose element label matches 'Sales'.
      (Label language etc. are in synchronization with the application display.)

select ElementLabel, Value from fact where ElementLabel = /Sales/ and
Value < 0
  ... Returns labels and values, where 'Sales' is contained (whole or part) in
      the element label and the value is less than 0.

select element, elementlabel, value from fact
  where element in (select element from fact except select element from presentation)
  ... Returns element names, element labels, and values of facts
      which will not be in the presentation link.

select element, label, value from fact join label
    on fact.element = label.element

  ... This will join fact table and label link table with element column.

select element, label, value from fact, label
    where fact.element = label.element

  ... Same as above, but this query is slower than the query using "join".

select label, count(*) from label group by label having count(*) > 1

  ... Returns the count of each label that appears more than once.

[saving query itself]

"Register" button gives you to save the current inputted query
with a name.

If you press the button on the right of input field, you will see
the registered query then call one of the list.

Note that saved queries and views will be cleared if you close
the editor. If you want to reuse these queries and views,
you have to export them. See [import/export queries and views].

[saving views]

Function to handle current inputted query as a table.

"Create View" button pops up dialog box to name it.

Once the query is named, you can use it in the following
query.

[edit/delete registered queries and views]

  "Setting" button pops up dialog to manage them.

[import/export queries and views]

  Menu "File" -> "Export Configuration" then check on
  "Query Table". The tool will export registered Queries
  and views.

  Menu "File" -> "Import Configuration" then check on
  "Query Table". The tool will import registered Queries
  and views.

