Changes between Version 6 and Version 7 of DatabaseBasedAnalysis/rootifysql

Timestamp:

08/05/18 18:11:04 (7 years ago)

Author:

tbretz

Comment:

--

DatabaseBasedAnalysis/rootifysql

@@ -140 +140 @@
 == Writing to a root-file ==
 
+=== Basics ===
+
+Let's try our previous example
+{{{
+fact@ihp-pc45:~/FACT++/build> rootifysql -f -v 2 --query="SELECT 'Hello World\!'"
+[...]
+------------------------ Rootify SQL -------------------------
+[...]
+Opening file 'rootify.root' [compression=1]...
+Writing data to tree 'Result'
+Trying to setup 1 branches...
+
+ - Hello World! [VARCHAR NOT NULL] {V}
+
+Configured 0 branches.
+Filling branches...
+1 rows fetched.
+1 rows filled into tree.
+4 kB written to disk.
+File closed.
+Execution time: 0.0775619s (77.6 ms/row)
+--------------------------------------------------------------
+}}}
+
+The option {{{-f}}} (which is a shortcut for {{{--force}}}) actually overwrites an existing root file (''RECREATE''). The verbosity option {{{-v 2}}} prints some information on how the table columns are translated. As the output suggests, 1 branch could have been setup (one column was returned), but no branch was configured. Our "''Hello World!''" string is returned as a VARCHAR (or CHAR), but only values are supported. The leading {{{-}}} tells us that the column was skipped, the {{{V}}} is an internal abbreviation for the action (and {{{C}}} for {{{CHAR}}}). So second try:
+
+{{{
+fact@ihp-pc45:~/FACT++/build> rootifysql -f -v 2 --query="SELECT 'Hello World\!', 1"
+[...]
+------------------------ Rootify SQL -------------------------
+[...]
+Opening file 'rootify.root' [compression=1]...
+Writing data to tree 'Result'
+Trying to setup 2 branches...
+
+ - Hello World! [VARCHAR NOT NULL] {V}
+ + 1 [BIGINT NOT NULL] {n}
+
+Configured 1 branches.
+Filling branches...
+1 rows fetched.
+1 rows filled into tree.
+5 kB written to disk.
+File closed.
+Execution time: 0.064065s (64.1 ms/row)
+--------------------------------------------------------------
+}}}
+
+Now one branch was setup successfully (indicated by the {{{+}}}) because it of a basic (numerical) type (indicated by the {{{n}}}).
+
+All columns of a numerical type ({{{n}}}) get converted to a branch of type double in the root-file. This keeps reading the root-file simple. All SQL columns of types {{{DATE}}}, {{{DATETIME}}} and {{{TIME}}} are converted to UNIX time and also written as a double-branch.
+
+To have another way to skip columns, all variables defined in a query are skipped:
+{{{
+fact@ihp-pc45:~/FACT++/build> rootifysql -f -v 2 --query="SELECT 'Hello World\!', 1, @a:=5"
+[...]
+------------------------ Rootify SQL -------------------------
+[...]
+Trying to setup 3 branches...
+
+ - Hello World! [VARCHAR NOT NULL] {V}
+ + 1 [BIGINT NOT NULL] {n}
+ - @a:=5 [BIGINT NOT NULL] {@}
+
+1 branches skipped due to name starting with @.
+Configured 1 branches.
+[...]
+--------------------------------------------------------------
+}}}
+
+This can be circumvented by giving it a name
+{{{
+fact@ihp-pc45:~/FACT++/build> rootifysql -f -v 2 --query="SELECT 'Hello World\!', 1, @a:=5 AS MyName"
+[...]
+------------------------ Rootify SQL -------------------------
+[...]
+Trying to setup 3 branches...
+
+ - Hello World! [VARCHAR NOT NULL] {V}
+ + 1 [BIGINT NOT NULL] {n}
+ + MyName [BIGINT NOT NULL] {n}
+
+Configured 2 branches.
+[...]
+--------------------------------------------------------------
+}}}
+
+Usually the name of the root-branch coincides with the name of the column, but it can easily be overwritten in the query with the {{{AS}}} directive as illustrated above.
+
+Now assuming you want to create a very general query but then skip some column in the root-file (usually, it is more efficient to just don't query them, because they are transferred from the server nevertheless). In this case you can do:
+{{{
+fact@ihp-pc45:~/FACT++/build> rootifysql -f -v 2 --query="SELECT 'Hello World\!', 1, @a:=5 AS MyName" --ignore MyName
+[...]
+------------------------ Rootify SQL -------------------------
+[...]
+Trying to setup 3 branches...
+
+ - Hello World! [VARCHAR NOT NULL] {V}
+ + 1 [BIGINT NOT NULL] {n}
+ - MyName [BIGINT NOT NULL] {-}
+
+1 branches skipped due to ignore list.
+Configured 1 branches.
+[...]
+--------------------------------------------------------------
+}}}
+
+The {{{--ignore}}} option can be specified more than once and it takes regular expression. So {{{--ignore M.*}}} would skip all columns starting with '''My'''.
+
+
+
+
+
+
+
+