Changes between Version 11 and Version 12 of DatabaseBasedAnalysis/rootifysql

Timestamp:

08/05/18 19:59:48 (7 years ago)

Author:

tbretz

Comment:

--

DatabaseBasedAnalysis/rootifysql

@@ -289 +289 @@
 
 
-
-
-
-
-
-
-
-
-
-
-== A simple request ==
-
-
-
-rootifysql - Converts the result of a mysql query into a root file
-
-For convenience, this documentation uses the extended version of the options, refer to the output below to get the abbreviations.
-
-Writes the result of a mysql query into a root file. For each column, a branch is created of type double with the field name as name. This is usually the column name if not specified otherwise by the AS mysql directive.
-
-Columns with CHAR or VARCHAR as field type are ignored. DATETIME, DATE and TIME columns are converted to unix time (time_t). Rows containing any file which is NULL are skipped if not suppressed by the --ignore-null option. Ideally, the query is compiled in a way that no NULL field is returned. With the --display option the result of the request is printed on the screen (NULL skipping still in action). This can be useful to create an ascii file or to show results as 'SHOW DATABASES' or 'EXPLAIN table'. To redirect the contents into an ascii file, the option -v0 is useful. To suppredd writing to an output file --null can be used.
-
-The default is to read the query from a file called rootify.sql. Except if a different filename is specified by the --file option or a query is given with --query.
-
-As a trick, the rootify.sql file can be made excutable (chmod u+x rootify.sql). If the first line contains '#!rootifysql', the script can be executed directly.
-
-Columns whose name start with @ are skipped. If you want them in your output file give them a name using AS, e.g. 'SELECT @A:=5 AS A'.
-
-You can use variables in your sql query like @MyVar and define them on the command line. In this example with --var.MyVar=5
-
-You can use environment definitions for substitutions in your SQL query. For example --env.TEST=5 would replace $TEST or ${TEST} in your query by 5.If you specifiy one environmentvariable more than once, a list is created. For example --env.TEST=1 --env.TEST=2 --env.TEST=3 would substitute $TEST or ${TEST} by '1, 2, 3'. This is useful for the SQL `IN` keyword. You can also read the values for an enviroment substitution from a file (one element per line), e.g. --env.TEST=file.txt. Empty lines and lines starting with a # are skipped.
-
-Comments in the query-file can be placed according to the SQL standard inline /*comment*/ or introduced with # (shell script style) or -- (SQL style).
-
-In case of succes, 0 is returned, a value>0 otherwise.
+=== Ignoring NULL ===
+
+Datanse tables can contain field without an entry (or with '''NULL''' as an entry). A root-branch is not able to represent ''no entry''. Therefore, rows which contain any field with NULL are skipped as a default.
+
+{{{
+user@machine> rootifysql -n -d --query="SELECT NULL AS Col0, 1 AS Col1, 2 AS Col2" --ignore Col0
+[...]
+------------------------ Rootify SQL -------------------------
+[...]
+Trying to setup 3 branches...
+1 branches skipped due to ignore list.
+Configured 2 branches.
+Filling branches...
+
+# Col0 Col1 Col2
+
+
+1 rows fetched.
+1 rows skipped due to NULL field.
+0 rows filled into tree.
+0 B written to disk.
+File closed.
+Execution time: 0.0586302s (58.6 ms/row)
+--------------------------------------------------------------
+}}}
+
+Note that this is even true for columns which contain NULL in a field which is only requested but not written to the file later on as you can see in the example above. Skipping rows with NULL fields can be turned off by the {{{-i}}} (short for {{{--ignore-null}}}) option.
+
+{{{
+user@machine> rootifysql -n -d -i --query="SELECT NULL AS Col0, 1 AS Col1, 2 AS Col2" 
+[...]
+------------------------ Rootify SQL -------------------------
+[...]
+Opening file '/dev/null' [compression=1]...
+Writing data to tree 'Result'
+Trying to setup 3 branches...
+Configured 3 branches.
+Filling branches...
+
+# Col0 Col1 Col2
+NULL    1       2
+
+
+1 rows fetched.
+1 rows filled into tree.
+0 B written to disk.
+File closed.
+Execution time: 0.0561399s (56.1 ms/row)
+--------------------------------------------------------------
+}}}
+
+All fields containing NULL will be written with a 0-value.
+
+== User Variables ==
+
+MySQL supports user variables which can be used in a query. They can be set using the {{{--var.*}}} option, for example
+
+{{{
+user@machine> rootifysql -n -d -v 3 --query="SELECT @myvar" --var.myvar=42
+[...]
+------------------------ Rootify SQL -------------------------
+[...]
+Setting user defined variables...
+
+SET
+   @myvar:=42
+
+Requesting data...
+
+SELECT @myvar
+
+Opening file '/dev/null' [compression=1]...
+Writing data to tree 'Result'
+Trying to setup 1 branches...
+
+ - @myvar [BIGINT NULL] {@}
+
+1 branches skipped due to name starting with @.
+Configured 0 branches.
+Filling branches...
+
+# @myvar
+42
+
+
+1 rows fetched.
+1 rows filled into tree.
+0 B written to disk.
+File closed.
+Execution time: 0.0547669s (54.8 ms/row)
+--------------------------------------------------------------
+}}}
+
+The {{{-var.*}}} option can be specified more than once for different variables. This shows another feature. Setting the verbosity level to 3 prints the user queries (not the internal ones).
+
+== Environment variables ==
+
+Instead of using SQL user variables, a replacement can be done. Instead of {{{--var.*}}}, this is done with {{{--env.*}}}. Replaced are $ENV and ${ENV}. Note that the replacement is done linear, so recursive replacement is not foreseen. Here is an example:
+
+{{{
+user@machine> rootifysql -n -d -v 3 --query="SELECT \$myvar1, \${myvar2}, '\$myvar3'" --env.myvar1=1 --env.myvar2=2 --env.myvar3=3 --env.myvar3=4 
+[...]
+------------------------ Rootify SQL -------------------------
+[...]
+Requesting data...
+
+SELECT 1, 2, '3, 4'
+
+Opening file '/dev/null' [compression=1]...
+Writing data to tree 'Result'
+Trying to setup 3 branches...
+
+ + 1 [BIGINT NOT NULL] {n}
+ + 2 [BIGINT NOT NULL] {n}
+ - 3, 4 [VARCHAR NOT NULL] {V}
+
+Configured 2 branches.
+Filling branches...
+
+# 1 2 3, 4
+1       2       3, 4
+
+
+1 rows fetched.
+1 rows filled into tree.
+0 B written to disk.
+File closed.
+Execution time: 0.055017s (55.0 ms/row)
+--------------------------------------------------------------
+}}}
+
+This example also illustrates that if one environment variable is specified more than once, a comma-separated list is created. This is especially useful together with the SQL {{{IN}}} statement. Please be aware that if you specify a query on the command line, you have to escape the {{{$}}} to {{{\$}}}, otherwise they will be replaced already by the shell with the shell's environment variables.
+
+An alternative to specifying environment variables on the command line is to read them from a file with {{{--list.*}}}. Each line (except empty lines and lines starting with {{{#}}}) are considered a single value. Let's assume the file '''input.txt''' contains the following
+{{{
+# This is input.txt
+42
+21
+10.5
+}}}
+
+you can do
+
+{{{
+user@machine> rootifysql -n -d -v 3 --query="SELECT \$myvar" --list.myvar=input.txt 
+[...]
+------------------------ Rootify SQL -------------------------
+Found 3 list element(s) for ${myvar}
+[...]
+Requesting data...
+
+SELECT 42, 21, 10.5
+
+Opening file '/dev/null' [compression=1]...
+Writing data to tree 'Result'
+Trying to setup 3 branches...
+
+ + 42 [BIGINT NOT NULL] {n}
+ + 21 [BIGINT NOT NULL] {n}
+ + 10.5 [DECIMAL NOT NULL] {n}
+
+Configured 3 branches.
+Filling branches...
+
+# 42 21 10.5
+42      21      10.5
+
+
+1 rows fetched.
+1 rows filled into tree.
+0 B written to disk.
+File closed.
+Execution time: 0.0551879s (55.2 ms/row)
+--------------------------------------------------------------
+}}}
+
+Note that {{{--env.*}}} and {{{--list.*}}} can be mixed.
+
+
+== Returns ==
+
+In case of success, {{{rootifysql}}} returns 0, a value>0 otherwise.