.. _row-functions-list: Row functions list =========================================== .. automodule:: functions.row.__init__ :mod:`boolean` functions ------------------------ .. module:: functions.row.boolean **ifthenelse function** .. function:: ifthenelse(condition, x, y) Returns *x* if *condition* is true, else returns *y*. .. templateforparams Parameters: :condition: exception type :x: exception value :y: traceback object :returns: true or false .. note:: The difference with the *if* construct in most programming languages is that *x* and *y* expressions will always be evaluated. Examples: >>> sql("select ifthenelse(1>0,'yes','no') as answer") answer ------ yes :mod:`date` functions --------------------- .. module:: functions.row.date **activityindex function** .. function:: activityIndex(date, c1, c2) -> int Specialized function that classifies the provided date argument into a 6-point scale (0 to 5) Examples: >>> table1(''' ... '2009-01-01T01:32:03Z' ... '2010-01-01T00:03:13Z' ... '2010-12-31T00:03:13Z' ... '2011-04-01T00:03:13Z' ... ''') >>> sql("select activityIndex(a) from table1") activityIndex(a) ---------------- 0 1 3 5 **cleantimezone function** .. function:: cleantimezone(date) -> date Specialized function that removes timezone information from date string Examples: >>> table1(''' ... '2009-01-01T01:03:13+0100' ... '2009-01-01T01:03:13-0100' ... '2009-01-01T01:03:13+01:00' ... '2009-01-01T01:03:13-01:00' ... '2009-01-01T01:03:13+01' ... '2009-01-01T01:03:13-01' ... ''') >>> sql("select cleantimezone(a) from table1") cleantimezone(a) ------------------- 2009-01-01 01:03:13 2009-01-01 01:03:13 2009-01-01 01:03:13 2009-01-01 01:03:13 2009-01-01 01:03:13 2009-01-01 01:03:13 **date2iso function** .. function:: date2iso(sec) -> ISO Datetime Converts an input date to ISO-8601 date format. It tries to autodetect, the input date format. Examples: >>> table1(''' ... 2007-12-31 ... 2010-01-01 ... 2010W06 ... "18/Jan/2011:11:13:00 +0100" ... ''') >>> sql("select date2iso(a) from table1") date2iso(a) ------------------------- 2007-12-31T00:00:00+00:00 2010-01-01T00:00:00+00:00 2010-02-05T00:00:00+00:00 2011-01-18T11:13:00+01:00 **datestrf2isoweek function** .. function:: dateisoweek2week52(sec) -> isoweek Converts an ISOweek (having weeks in range [0,53]) to an ISOweek format which has weeks in range [1,53]. This function is usefull for producing week statistics which do not have incomplete weeks. Examples: >>> table1(''' ... 2007-12-31 ... 2010-01-01 ... ''') >>> sql("select strftime('%YW%W',a) from table1") strftime('%YW%W',a) ------------------- 2007W53 2010W00 >>> sql("select datestrf2isoweek(strftime('%YW%W',a)) from table1") datestrf2isoweek(strftime('%YW%W',a)) ------------------------------------- 2007W53 2009W53 **sectohuman function** .. function:: sectohuman(sec) -> human readable format Converts a number of seconds to human readable format. Examples: >>> table1(''' ... 3 ... 63 ... 10000 ... 100000 ... 1000000 ... ''') >>> sql("select sectohuman(a) from table1") sectohuman(a) ------------------------------ 3 sec 1 min 3 sec 2 hours 46 min 40 sec 1 day 3 hours 46 min 40 sec 11 days 13 hours 46 min 40 sec :mod:`evals` functions ---------------------- .. module:: functions.row.evals **pyeval function** .. function:: pyeval(expression) Evaluates with Python the expression/s given and returns the result >>> sql("pyeval '1+1'") pyeval('1+1') ------------- 2 >>> sql("select var('test')") # doctest: +NORMALIZE_WHITESPACE Traceback (most recent call last): ... OperatorError: Madis SQLError: Operator VAR: Variable 'test' does not exist >>> sql("select var('test', pyeval('1+1'))") var('test', pyeval('1+1')) -------------------------- 2 >>> sql("select var('test')") var('test') ----------- 2 >>> sql('''pyeval '1+1' '"-"' '3+1' ''') pyeval('1+1','"-"','3+1') ------------------------- 2-4 >>> sql("var 'testvar' of select 5") var('testvar',(select 5)) ------------------------- 5 >>> sql("pyeval 'testvar+5'") pyeval('testvar+5') ------------------- 10 >>> sql('''pyeval keywords('lala') ''') pyeval('keywords(''lala'')') ---------------------------- lala **pyfun function** .. function:: pyfun(pyfunction, parameters) Calls a python function and returns the result. If an error occurs, it throws an exception. >>> sql("select pyfun('math.sqrt', 25)") pyfun('math.sqrt', 25) ---------------------- 5.0 >>> sql("select pyfun('math.log10', 100)") pyfun('math.log10', 100) ------------------------ 2.0 >>> sql("select pyfun('math.log10', -1)") # doctest: +NORMALIZE_WHITESPACE Traceback (most recent call last): ... OperatorError: Madis SQLError: Operator PYFUN: math.log10: math domain error **pyfunerrtonul function** .. function:: pyfunerrtonul(pyfunction, parameters) Calls a python function and returns the result. If an error occurs it returns *null*. >>> sql("select pyfunerrtonul('math.sqrt', -1)") pyfunerrtonul('math.sqrt', -1) ------------------------------ None >>> sql("select pyfunerrtonul('math.log10', -1)") pyfunerrtonul('math.log10', -1) ------------------------------- None **query function** **subst function** .. function:: subst(str, variables) Substitutes the special text markers with the variables values. >>> sql('''subst 'Variable %s has value %s' 'var1' '5' ''') subst('Variable %s has value %s','var1','5') -------------------------------------------- Variable var1 has value 5 >>> sql('''select subst('Variable %s has value %d','var2',5) ''') subst('Variable %s has value %d','var2',5) ------------------------------------------ Variable var2 has value 5 >>> sql('''var 'testvar' 'testvalue' ''') var('testvar','testvalue') -------------------------- testvalue >>> sql('''select subst('Variable %{testvar}1 %{testvar1} has value %s', 5) ''') subst('Variable testvalue1 %{testvar1} has value %s', 5) -------------------------------------------------------- Variable testvalue1 %{testvar1} has value %s :mod:`fileops` functions ------------------------ .. module:: functions.row.fileops **fileextension function** .. function:: fileextension(text) -> text Returns the extension of a given text argument. Examples: >>> table1(''' ... "http://www.test.com/lalala.gif" ... "http://www.test.com/lalala.GIF" ... ''') >>> sql("select fileextension(a) from table1") fileextension(a) ---------------- .gif .gif **filetext function** .. function:: filetext(filename) -> text Returns the contents of the file in a single value Examples: >>> sql("select filetext('testing/sales.tsv')") #doctest:+ELLIPSIS +NORMALIZE_WHITESPACE filetext('testing/sales.tsv') ---------------------------------- Cars 2010-01 Athens 200 Cars 2010-02 Athens 130 Bikes 2010-01 NY 10 Bikes 2010-02 NY 30 Cars 2010-01 NY 100 Cars 2010-02 NY 160 Cars 2010-01 Paris 70 Cars 2010-02 Paris 20 Bikes 2010-01 Paris 100 Bikes 2010-02 Paris 20 Boats 2010-01 Paris 200 :mod:`formating` functions -------------------------- .. module:: functions.row.formating **dateformat function** .. function:: dateformat(date[,inpformat,outformat]) Returns *date* formatted with *outformat*. Default *inpformat* is %d-%m-%y and default *outformat* is %Y-%m-%d. To change *inpformat* just provide as second parameter the input format. To change *outformat*, both format parameters must be provided. If date is not formatted according to *inpformat* null value is returned. Check `formatting pattern `_ details Examples: >>> sql("select dateformat('28-01-09') as date") date ---------- 2009-01-28 When date doesn't meet the format null is returned >>> sql("select dateformat('32-01-09') as date") date ---- None >>> sql("select dateformat('Thu, 28 Jun 2001 14:17:15 +0000','%a, %d %b %Y %H:%M:%S +0000') as date") date ---------- 2001-06-28 >>> sql("select dateformat('28-01-09','%d-%m-%y','%x') as date") date -------- 01/28/09 **strjoin function** .. function:: strjoin(value1,value2,....[,'params',formatting options]) Returns a string with *value1,value2,....* formatted according to :ref:`formatting options`. Literal parameter 'params' must precede *formating options*. Default formatting is comma separated values. Examples: >>> sql("select strjoin('First','Second','Third',100) as joinedvals") joinedvals ----------------------- First,Second,Third,100 >>> sql("select strjoin('First','Second','Third',100,'params','delimiter: ') as joinedvals") joinedvals ----------------------- First Second Third 100 >>> sql("select strjoin('lola',2,10,'llalallall','params','delimiter:%','quoting:QUOTE_ALL') as joinedvals") joinedvals ----------------------------- "lola"%"2"%"10"%"llalallall" >>> sql("select strjoin('value, with delimiter ',2,10,'new','params','dialect:csv') as joinedvals") joinedvals ---------------------------------- "value, with delimiter ",2,10,new **strsplit function** .. function:: strsplit(str[,formatting options]) -> [C1,C2,....] Splits *str* according to *formatting options*, default behavior is to split on space. It is a multiset operator that returns one row. .. seealso:: * :ref:`tutmultiset` functions .. _formattingopts: Formatting options: :dialect: *tsv/csv* Formats field as tab/comma separated values with minimal quoting :delimiter: A string used to separate fields. It defaults to ' ' :doublequote: *t/f* Controls how instances of quotechar appearing inside a field should be themselves be quoted. When True, the character is doubled. When False, the escapechar is used as a prefix to the quotechar. It defaults to True. On output, if doublequote is False and no escapechar is set, Error is raised if a quotechar is found in a field :escapechar: A one-character string used by the writer to escape the delimiter if quoting is set to QUOTE_NONE and the quotechar if doublequote is False. On reading, the escapechar removes any special meaning from the following character. It defaults to None, which disables escaping. :quotechar: A one-character string used to quote fields containing special characters, such as the delimiter or quotechar, or which contain new-line characters. It defaults to '"'. :quoting: Controls when quotes should be generated by the writer and recognized by the reader. It can take on any of the QUOTE_* constants and defaults to QUOTE_MINIMAL. Possible values are QUOTE_ALL, QUOTE_NONE, QUOTE_MINIMAL, QUOTE_NONNUMERIC :skipinitialspace: *t/f* When True, whitespace immediately following the delimiter is ignored. The default is False :Returned multiset schema: - *C1,C2...* Text values produced from splitting. Examples: >>> sql("select strsplit('First,Second,Third', 'dialect:csv')") C1 | C2 | C3 ---------------------- First | Second | Third >>> sql("select strsplit('-First-%Second%-Third-','delimiter:%','quotechar:-')") C1 | C2 | C3 ---------------------- First | Second | Third .. doctest:: :hide: >>> sql("select strsplit('-First-%Second%-Third-','quotechar:-p')") #doctest:+ELLIPSIS +NORMALIZE_WHITESPACE Traceback (most recent call last): ... MadisError: Madis SQLError: "quotechar" must be an 1-character string **strsplitv function** .. function:: strsplitv(str[,formatting options]) -> [C1] Splits in rows *str* according to *formatting options*, default behavior is to split on space. It is a multiset operator that returns one column but many rows. :ref:`Formatting options` are like in :func:`strsplit` function. .. seealso:: * :ref:`tutmultiset` functions >>> sql("select strsplitv('First,Second,Third','dialect:csv')") C1 ------ First Second Third >>> sql("select strsplitv('First Second Third')") C1 ------ First Second Third :mod:`htmlops` functions ------------------------ .. module:: functions.row.htmlops **htmladdbreaks function** .. function:: url(href, linktext) Returns the a url pointing to *href* and having the link text *linktext*. Examples: >>> sql("select htmladdbreaks('very-long/string') as brokenhtml") brokenhtml -------------------------- very-long/string **htmldecode function** .. function:: htmldecode(str) Returns the html decoded *str*. Examples: >>> sql("select htmldecode('("die+wunderbaren+jahre")') as query") query ------------------------- ("die+wunderbaren+jahre") >>> sql("select htmldecode(null) as query") query ----- None **htmlencode function** .. function:: htmldecode(str) Returns the html decoded *str*. Examples: >>> sql("select htmldecode('("die+wunderbaren+jahre")') as query") query ------------------------- ("die+wunderbaren+jahre") >>> sql("select htmldecode(null) as query") query ----- None **htmllink function** .. function:: htmllink(href, linktext) Returns the an html link pointing to *href* and having the link text *linktext*. Examples: >>> sql("select htmllink('http://somewhere.org') as url") #doctest:+ELLIPSIS +NORMALIZE_WHITESPACE url ----------------------------------------------------------------- http://somewhere.org >>> sql("select htmllink('somewhere.org') as url") url ----------------------------------------------------- somewhere.org >>> sql("select htmllink('somewhere.org', 'go somewhere') as url") url ----------------------------------------------- go somewhere **htmlstriptags function** .. function:: htmlstriptags(str, default_tag_conversion) Strips the html tags of input. It also converts "
" tags to new lines. If a default_tag_conversion is provided then tags that would have been erased are converted to *default_tag_conversion*. Examples: >>> sql("select htmlstriptags('asdf<>as< br>df

spacedparagraph

anotherline w3') as query") query ------------------------------------------- asdfas df spaced paragraph anotherline w_3 >>> sql("select htmlstriptags('asdf<>as< br>df

spacedparagraph

anotherline w3', '***') as query") query ---------------------------------------------------- ***asdf***as df spaced paragraph anotherline*** w_3 >>> sql("select htmlstriptags(null) as query") query ----- **urldecode function** .. function:: urldecode(str) Returns the url decoded *str*. Examples: >>> sql("select urldecode('where%2Ccollid%3Dcolid+and+u%3D%27val%27') as query") query ------------------------------ where,collid=colid and u='val' >>> sql("select urldecode(null) as query") query ----- None **urlencode function** .. function:: urlescape(str) Returns the escaped URL. Examples: >>> sql("select urlencode('where, collid=colid') as query") query ----------------------- where%2C+collid%3Dcolid **urllocation function** .. function:: urllocation(str) -> str Returns the location part of provided URL. Examples: >>> table1(''' ... http://www.test.com/apath/bpath/fname.pdf ... http://www.test.com/search.csv;p=5?q=test#hl=en ... ''') >>> sql("select urllocation(a) from table1") urllocation(a) ----------------------------------------- http://www.test.com/apath/bpath/fname.pdf http://www.test.com/search.csv **urlquery2jdict function** .. function:: urlquery2jdict(URL or URL_query_part) -> JDICT Converts the query part of a URL into a JSON associative array. Examples: >>> table1(''' ... 'url_ver=ver1&url_tim=2011-01-01T00%3A02%3A40Z' ... 'url_tim=2011-01-01T00%3A02%3A40Z&url_ver=ver1' ... http://www.test.com/search.csv;p=5?lang=test&ver=en ... ''') >>> sql("select urlquery2jdict(a) from table1") urlquery2jdict(a) --------------------------------------------------- {"url_tim":"2011-01-01T00:02:40Z","url_ver":"ver1"} {"url_tim":"2011-01-01T00:02:40Z","url_ver":"ver1"} {"lang":"test","ver":"en"} **urlsplit function** .. function:: urlsplit(text1, [text2,...]) -> multiset Breaks a given URL into multiple fields. The returned table schema is: :scheme: What type the URL is (e.g. http, ftp ...) :netloc: Network location of URL (e.g. www.text.com) :path: Path part of URL (e.g. /data/2010/). It always has a slash at the end :filename: Filename part of URL :type: Mime type of URL, or if not a mime type exists, the extension part of filename. :subtype: Mime subtype of URL. :params: All parameters following ';' in URL. :query: All parameters following '?' in URL. :fragment: All parameters following '#' in URL. Examples: >>> table1(''' ... http://www.test.com/apath/bpath/fname.pdf ... http://www.test.com/search.csv;p=5?q=test#hl=en ... ''') >>> sql("select urlsplit(a) from table1") scheme | netloc | path | filename | type | subtype | params | query | fragment ------------------------------------------------------------------------------------------------------- http | www.test.com | /apath/bpath/ | fname.pdf | application | pdf | | | http | www.test.com | / | search.csv | csv | | p=5 | q=test | hl=en :mod:`iptools` functions ------------------------ .. module:: functions.row.iptools **ip2long function** .. function:: ip2long(ip) -> int Converts a decimal dotted quad IP string to long integer IP format. It can take either one column of IP strings or 4 columns each having one part of the IP address. Examples: >>> sql("select ip2long('123.123.123.123')") ip2long('123.123.123.123') -------------------------- 2071690107 >>> sql("select ip2long(123,123,123,123)") ip2long(123,123,123,123) ------------------------ 2071690107 **ip_prefix function** .. function:: ip_prefix(ip, class_number) -> ip Returns the subnetwork class of an IP address. Examples: >>> sql("ip_prefix '123.34.24.54' ") ip_prefix('123.34.24.54') ------------------------- 4 >>> sql("ip_prefix '123.34.24.54' '3'") ip_prefix('123.34.24.54','3') ----------------------------- 123.34.24 >>> sql("ip_prefix '123.34.24.54' '2'") ip_prefix('123.34.24.54','2') ----------------------------- 123.34 **long2ip function** .. function:: long2ip(int) -> ip Convert longint IP to dotted quad string Examples: >>> sql("select long2ip('2071690107')") long2ip('2071690107') --------------------- 123.123.123.123 >>> sql("select long2ip(2071690107)") long2ip(2071690107) ------------------- 123.123.123.123 :mod:`jpacks` functions ----------------------- .. module:: functions.row.jpacks **j2nl function** .. function:: j2nl(jpack) -> text Converts multiple input jpacks to a newline separated text. Examples: >>> sql("select j2nl('[1,2,3]')") # doctest: +NORMALIZE_WHITESPACE j2nl('[1,2,3]') --------------- 1 2 3 >>> sql("select j2nl('[1,2,3]','a')") # doctest: +NORMALIZE_WHITESPACE j2nl('[1,2,3]','a') ------------------- 1 2 3 a >>> sql("select j2nl('a', 'b')") # doctest: +NORMALIZE_WHITESPACE j2nl('a', 'b') -------------- a b **j2s function** .. function:: j2s(jpack) -> space separated string Converts multiple input jpacks to a space separated string. Newlines are converted to spaces. Examples: >>> sql("select j2s('[1,2,3]')") # doctest: +NORMALIZE_WHITESPACE j2s('[1,2,3]') -------------- 1 2 3 >>> sql("select j2s('[1,2,3]','a')") # doctest: +NORMALIZE_WHITESPACE j2s('[1,2,3]','a') ------------------ 1 2 3 a >>> sql("select j2s('a', 'b')") # doctest: +NORMALIZE_WHITESPACE j2s('a', 'b') ------------- a b **j2t function** .. function:: j2t(jpack) -> tabpack Converts multiple input jpacks to a tab separated pack (tab separated values). If tab or newline characters are found in the source jpack they are converted to spaces. Examples: >>> sql("select j2t('[1,2,3]')") # doctest: +NORMALIZE_WHITESPACE j2t('[1,2,3]') -------------- 1 2 3 >>> sql("select j2t('[1,2,3]','a')") # doctest: +NORMALIZE_WHITESPACE j2t('[1,2,3]','a') ------------------ 1 2 3 a >>> sql("select j2t('a', 'b')") # doctest: +NORMALIZE_WHITESPACE j2t('a', 'b') ------------- a b **jchars function** .. function:: jletters(text) -> character jpack Splits an input text into its composing characters. Examples: >>> sql("select jchars('this is a text')") jchars('this is a text') --------------------------------------------------------- ["t","h","i","s"," ","i","s"," ","a"," ","t","e","x","t"] >>> sql("select jchars('another', 'text')") jchars('another', 'text') --------------------------------------------- ["a","n","o","t","h","e","r","t","e","x","t"] **jcombinations function** .. function:: jcombinations(jpack, r) -> multiset Returns all length r combinations of jpack. Examples: >>> sql('''select jcombinations('["t1","t2","t3"]',2)''') C1 | C2 ------- t1 | t2 t1 | t3 t2 | t3 >>> sql('''select jcombinations('["t1","t2",["t3","t4"]]',2)''') C1 | C2 ---------------- t1 | t2 t1 | ["t3","t4"] t2 | ["t3","t4"] >>> sql('''select jcombinations(null,2)''') >>> sql('''select jcombinations('["t1","t2","t3","t4"]')''') C1 -- t1 t2 t3 t4 **jdict function** .. function:: jdict(key, value, key1, value1) -> jdict Returns a jdict of the keys and value pairs. Examples: >>> sql(''' select jdict('key1', 'val1', 'key2', 'val2') ''') # doctest: +NORMALIZE_WHITESPACE jdict('key1', 'val1', 'key2', 'val2') ------------------------------------- {"key1":"val1","key2":"val2"} >>> sql(''' select jdict('key', '{"k1":1,"k2":2}') ''') # doctest: +NORMALIZE_WHITESPACE jdict('key', '{"k1":1,"k2":2}') ------------------------------- {"key":{"k1":1,"k2":2}} >>> sql(''' select jdict('key', '["val1", "val2"]') ''') # doctest: +NORMALIZE_WHITESPACE jdict('key', '["val1", "val2"]') -------------------------------- {"key":["val1","val2"]} >>> sql(''' select jdict('1') ''') # doctest: +NORMALIZE_WHITESPACE Traceback (most recent call last): ... OperatorError: Madis SQLError: Operator JDICT: At least two arguments required **jdictgroupkey function** .. function:: jdictgroupkey(list_of_jdicts, groupkey1, groupkey2, ...) It groups an array of jdicts into a hierarchical structure. The grouping is done first by groupkey1 then by groupkey2 and so on. If no groupkeys are provided, then the first key of array's first jdict is used as a groupkey. Examples: >>> sql('''select jdictgroupkey('[{"gkey":"v1", "b":1},{"gkey":"v1","b":2},{"gkey":"v2","b":1, "c":2}]') as j''') j --------------------------------------------- {"v1":[{"b":1},{"b":2}],"v2":[{"b":1,"c":2}]} >>> sql('''select jdictgroupkey('[{"gkey":"v1", "b":1},{"gkey":"v1","b":2},{"gkey":"v2","b":1, "c":2}]', "gkey") as j''') j --------------------------------------------- {"v1":[{"b":1},{"b":2}],"v2":[{"b":1,"c":2}]} >>> sql('''select jdictgroupkey('[{"gkey":"v1", "gkey2":"f1", "b":1},{"gkey":"v1", "gkey2":"f2", "b":2},{"gkey":"v1", "gkey2":"f2", "b":1, "c":2}]', "gkey", "gkey2") as j''') j -------------------------------------------------------------- {"v1":{"gkey2":{"f1":[{"b":1}],"f2":[{"b":2},{"b":1,"c":2}]}}} **jdictkeys function** .. function:: jdictkeys(jdict) -> jpack Returns a jpack of the keys of input jdict Examples: >>> sql(''' select jdictkeys('{"k1":1,"k2":2}', '{"k1":1,"k3":2}') ''') # doctest: +NORMALIZE_WHITESPACE jdictkeys('{"k1":1,"k2":2}', '{"k1":1,"k3":2}') ----------------------------------------------- ["k1","k2","k3"] >>> sql(''' select jdictkeys('{"k1":1,"k2":2}') ''') # doctest: +NORMALIZE_WHITESPACE jdictkeys('{"k1":1,"k2":2}') ---------------------------- ["k1","k2"] >>> sql(''' select jdictkeys('test') ''') # doctest: +NORMALIZE_WHITESPACE jdictkeys('test') ----------------- [] >>> sql(''' select jdictkeys(1) ''') # doctest: +NORMALIZE_WHITESPACE jdictkeys(1) ------------ [] **jdictsplit function** .. function:: jdictsplit(jdict, [key1, key2,..]) -> columns If only the first argument (jdict) is provided, it returns a row containing the values of input jdict (sorted by the jdict keys). If key values are also provided, it returns only the columns of which the keys have been provided. Examples: >>> sql(''' select jdictsplit('{"k1":1,"k2":2}') ''') # doctest: +NORMALIZE_WHITESPACE k1 | k2 ------- 1 | 2 >>> sql(''' select jdictsplit('{"k1":1,"k2":2, "k3":3}', 'k3', 'k1', 'k4') ''') # doctest: +NORMALIZE_WHITESPACE k3 | k1 | k4 -------------- 3 | 1 | None **jdictsplitv function** .. function:: jdictsplitv(jdict, [key1, key2,..]) -> columns If only the first argument (jdict) is provided, it returns rows containing the values of input jdict. If key values are also provided, it returns only the columns of which the keys have been provided. Examples: >>> sql(''' select jdictsplitv('{"k1":1,"k2":2}') ''') # doctest: +NORMALIZE_WHITESPACE key | val --------- k1 | 1 k2 | 2 >>> sql(''' select jdictsplitv('{"k1":1,"k2":2, "k3":3}', 'k3', 'k1', 'k4') ''') # doctest: +NORMALIZE_WHITESPACE key | val --------- k3 | 3 k1 | 1 **jdictvals function** .. function:: jdictvals(jdict, [key1, key2,..]) -> jpack If only the first argument (jdict) is provided, it returns a jpack of the values of input jdict (sorted by the jdict keys). If key values are also provided, it returns only the keys that have been provided. Examples: >>> sql(''' select jdictvals('{"k1":1,"k2":2}') ''') # doctest: +NORMALIZE_WHITESPACE jdictvals('{"k1":1,"k2":2}') ---------------------------- [1,2] >>> sql(''' select jdictvals('{"k1":1,"k2":2, "k3":3}', 'k3', 'k1', 'k4') ''') # doctest: +NORMALIZE_WHITESPACE jdictvals('{"k1":1,"k2":2, "k3":3}', 'k3', 'k1', 'k4') ------------------------------------------------------ [3,1,null] >>> sql(''' select jdictvals('{"k1":1}') ''') # doctest: +NORMALIZE_WHITESPACE jdictvals('{"k1":1}') --------------------- 1 >>> sql(''' select jdictvals('{"k1":1}') ''') # doctest: +NORMALIZE_WHITESPACE jdictvals('{"k1":1}') --------------------- 1 >>> sql(''' select jdictvals(1) ''') # doctest: +NORMALIZE_WHITESPACE jdictvals(1) ------------ 1 **jexcept function** .. function:: jexcept(jpackA, jpackB) -> jpack Returns the items of jpackA except the items that appear on jpackB. Examples: >>> sql("select jexcept('[1,2,3]', '[1,2,3]')") # doctest: +NORMALIZE_WHITESPACE jexcept('[1,2,3]', '[1,2,3]') ----------------------------- [] >>> sql("select jexcept('[1,2,3]', '[1,3]')") # doctest: +NORMALIZE_WHITESPACE jexcept('[1,2,3]', '[1,3]') --------------------------- 2 **jfilterempty function** .. function:: jfilterempty(jpacks.) -> jpack Removes from input jpacks all empty elements. Examples: >>> sql("select jfilterempty('a', '', '[]')") jfilterempty('a', '', '[]') --------------------------- a >>> sql("select jfilterempty('a','[null]',3)") jfilterempty('a','[null]',3) ---------------------------- ["a",3] >>> sql("select jfilterempty('[3]', jpack('b', ''))") jfilterempty('[3]', jpack('b', '')) ----------------------------------- [3,"b"] **jflatten function** .. function:: jflattten(jpacks) -> jpack Flattens all nested sub-jpacks. Examples: >>> sql(''' select jflatten('1', '[2]') ''') # doctest: +NORMALIZE_WHITESPACE jflatten('1', '[2]') -------------------- ["1",2] >>> sql(''' select jflatten('[["word1", 1], ["word2", 1], [["word3", 2], ["word4", 2]], 3]') ''') # doctest: +NORMALIZE_WHITESPACE jflatten('[["word1", 1], ["word2", 1], [["word3", 2], ["word4", 2]], 3]') ------------------------------------------------------------------------- ["word1",1,"word2",1,"word3",2,"word4",2,3] **jfrequentwords function** .. function:: jfrequentwords(args...) -> jpack Returns the frequent words of a text in a jpack **jintersection function** .. function:: jintersection(jpackA, jpackB) -> jpack Returns the items of jpackA except the items that appear on jpackB. Examples: >>> sql("select jintersection('[1,2,3]', '[1,2,3]')") # doctest: +NORMALIZE_WHITESPACE jintersection('[1,2,3]', '[1,2,3]') ----------------------------------- [1,2,3] >>> sql("select jintersection('[1,2,3]', '[1,3]', 1)") # doctest: +NORMALIZE_WHITESPACE jintersection('[1,2,3]', '[1,3]', 1) ------------------------------------ 1 **jlen function** .. function:: jlen(args...) -> int Returns the total length in elements of the input jpacks. Examples: >>> sql("select jlen('abc')") jlen('abc') ----------- 1 >>> sql("select jlen('a','b',3)") jlen('a','b',3) --------------- 3 >>> sql("select jlen('a', jpack('b',3))") jlen('a', jpack('b',3)) ----------------------- 3 >>> sql("select jlen('[1,2,3]')") jlen('[1,2,3]') --------------- 3 **jlengthiest function** .. function:: jlengthiest(jpacks.) -> jpack Returns the string with the greatest length contained in the jpacks. Examples: >>> sql("select jlengthiest('a', '', '[]')") jlengthiest('a', '', '[]') -------------------------- a >>> sql("select jlengthiest('a','longer',3)") jlengthiest('a','longer',3) --------------------------- longer >>> sql("select jlengthiest('[3]', jpack('b', ''))") jlengthiest('[3]', jpack('b', '')) ---------------------------------- 3 **jmerge function** .. function:: jmerge(jpacks) -> jpack Merges multiple jpacks into one jpack. Examples: >>> sql("select jmerge('[1,2,3]', '[1,2,3]', 'a', 3 )") # doctest: +NORMALIZE_WHITESPACE jmerge('[1,2,3]', '[1,2,3]', 'a', 3 ) ------------------------------------- [1,2,3,1,2,3,"a",3] **jmergeregexp function** .. function:: jmergeregexp(jpacks) -> jpack Creates a regular expression that matches all of the jpack's contents. If the input jpack contains keyword pairs, then jmergeregexp returns a regular expression with named groups. Examples: >>> sql(''' select jmergeregexp('["abc", "def"]') ''') # doctest: +NORMALIZE_WHITESPACE jmergeregexp('["abc", "def"]') ------------------------------ (?:abc)|(?:def) >>> sql(''' select jmergeregexp('[["pos", "p1"], ["neg", "n1"], ["pos", "p2"]]') ''') # doctest: +NORMALIZE_WHITESPACE jmergeregexp('[["pos", "p1"], ["neg", "n1"], ["pos", "p2"]]') ------------------------------------------------------------- (?Pn1)|(?Pp1|p2) >>> sql(''' select jmergeregexp('[]') ''') # doctest: +NORMALIZE_WHITESPACE jmergeregexp('[]') ------------------ _^ >>> sql(''' select jmergeregexp('["ab",""]') ''') # doctest: +NORMALIZE_WHITESPACE jmergeregexp('["ab",""]') ------------------------- (?:ab) **jmergeregexpnamed function** .. function:: jmergeregexpnamed(jpacks) -> jpack Creates a regular expression that matches all of the jpack's contents with named groups. If the number of named groups in a regular expression is greater than 99, then the output will be a jpack of regular expressions. Examples: >>> sql(''' select jmergeregexpnamed('["abc", "def"]') ''') # doctest: +NORMALIZE_WHITESPACE jmergeregexpnamed('["abc", "def"]') ----------------------------------- (abc)|(def) **jngrams function** .. function:: jngrams(n,text) -> jpack Converts multiple input arguments into a jpack of ngrams. Examples: >>> sql("select jngrams(1,'This is a test phrase')") jngrams(1,'This is a test phrase') ------------------------------------------- [["This"],["is"],["a"],["test"],["phrase"]] >>> sql("select jngrams(2,'This is a test phrase')") jngrams(2,'This is a test phrase') --------------------------------------------------------- [["This","is"],["is","a"],["a","test"],["test","phrase"]] **jpack function** .. function:: jpack(args...) -> jpack Converts multiple input arguments into a single string. Jpacks preserve the types of their inputs and are based on JSON encoding. Single values are represented as themselves where possible. Examples: >>> sql("select jpack('a')") jpack('a') ---------- a >>> sql("select jpack('a','b',3)") jpack('a','b',3) ---------------- ["a","b",3] >>> sql("select jpack('a', jpack('b',3))") jpack('a', jpack('b',3)) ------------------------ ["a",["b",3]] **jpermutations function** .. function:: jpermutations(jpack, r) -> multiset Returns all length r permutations of jpack. Examples: >>> sql('''select jpermutations('["t1","t2","t3"]',2)''') C1 | C2 ------- t1 | t2 t1 | t3 t2 | t1 t2 | t3 t3 | t1 t3 | t2 >>> sql('''select jpermutations('["t1","t2",["t3","t4"]]',2)''') C1 | C2 ------------------------- t1 | t2 t1 | ["t3","t4"] t2 | t1 t2 | ["t3","t4"] ["t3","t4"] | t1 ["t3","t4"] | t2 >>> sql('''select jpermutations(null,2)''') >>> sql('''select jpermutations('["t1","t2","t3","t4"]')''') C1 -- t1 t2 t3 t4 **jrange function** .. function:: jrange(num) -> jrange Returns a jrange of integer numbers. Examples: >>> sql("select jrange(5)") jrange('a') ----------------- ["0","1","2","3"] **jset function** .. function:: jset(jpacks) -> jpack Returns a set representation of a jpack, unifying duplicate items. Examples: >>> sql("select jset('[1,2,3]', '[1,2,3]', 'b', 'a', 3 )") # doctest: +NORMALIZE_WHITESPACE jset('[1,2,3]', '[1,2,3]', 'b', 'a', 3 ) ---------------------------------------- [1,2,3,"a","b"] **jsonpath function** .. function:: jsonpath(JSON, jsonpathexpr1, jsonpathexpr2) -> multiset Uses jsonpath expressions to pick values from inside a JSON input. If the outputs of all JSONpath expressions have the same number of elements in them, it splits the output into multiple rows. .. note:: For more on JSONpath see: http://goessner.net/articles/JsonPath/ Examples: >>> sql('''select jsonpath('{"d1":[{"name":"n1", "value":"v1"}, {"name":"n2", "value":"v2"}]}', '$.d1') ''') C1 ------------------------------------------------------- [{"name":"n1","value":"v1"},{"name":"n2","value":"v2"}] >>> sql('''select jsonpath('{"d1":[{"name":"n1", "value":"v1"}, {"name":"n2", "value":"v2"}]}', '$.d1[*].name') ''') C1 -- n1 n2 >>> sql('''select jsonpath('{"d1":[{"name":"n1", "value":"v1"}, {"name":"n2", "value":"v2"}]}', '$.d1[*].name', '$.d1[*].value') ''') C1 | C2 ------- n1 | v1 n2 | v2 >>> sql('''select jsonpath('{"d1":[{"name":"n1", "value":"v1"}, {"name":"n2", "value":"v2"}]}', '$.d1[*].name', '$.d1[*].nonexisting') ''') C1 | C2 --------- n1 | None n2 | None >>> sql('''select jsonpath('{"d1":[{"name":"n1", "value":"v1"}, {"name":"n2"}]}', '$.d1[*].name', '$.d1[*].value') ''') C1 | C2 ---------------- ["n1","n2"] | v1 >>> sql('''select jsonpath('{"d1":[{"name":"n1", "value":"v1"}, {"name":"n2", "value":"v2"}]}', '$.nonexisting') ''') **jsonstrict function** .. function:: jsonstrict(args...) -> json string Sometimes we wish to process json lists from another application. Jsonstrict function tries to always create json compatible lists. So it always returns json lists. Examples: >>> sql("select jsonstrict('a')") jsonstrict('a') --------------- ["a"] >>> sql("select jsonstrict('a','b',3)") jsonstrict('a','b',3) --------------------- ["a","b",3] >>> sql("select jsonstrict('a', jpack('b',3))") jsonstrict('a', jpack('b',3)) ----------------------------- ["a",["b",3]] **jsort function** .. function:: jsort(jpacks) -> jpack Sorts the input jpacks. Examples: >>> sql("select jsort('[1,2,3]', '[1,2,3]', 'b', 'a', 3 )") # doctest: +NORMALIZE_WHITESPACE jsort('[1,2,3]', '[1,2,3]', 'b', 'a', 3 ) ----------------------------------------- [1,1,2,2,3,3,3,"a","b"] **jsplice function** .. function:: jsplice(jpack, range1_start, range1_end, ...) -> jpack Splices input jpack. If only a single range argument is provided, it returns input jpack's element in provided position. If defined position index is positive, then it starts counting from the beginning of input jpack. If defined position is negative it starts counting from the end of input jpack. If more than one range arguments are provided, then the arguments are assumed to be provided in pairs (start, end) that define ranges inside the input jpack that should be joined together in output jpack. Examples: >>> sql(''' select jsplice('[1,2,3,4,5]',0) ''') # doctest: +NORMALIZE_WHITESPACE jsplice('[1,2,3,4,5]',0) ------------------------ 1 >>> sql(''' select jsplice('[1,2,3,4,5]',-1) ''') # doctest: +NORMALIZE_WHITESPACE jsplice('[1,2,3,4,5]',-1) ------------------------- 5 >>> sql(''' select jsplice('[1,2,3,4,5]',10) ''') # doctest: +NORMALIZE_WHITESPACE jsplice('[1,2,3,4,5]',10) ------------------------- None >>> sql(''' select jsplice('[1,2,3,4,5]', 0, 3, 0, 2) ''') # doctest: +NORMALIZE_WHITESPACE jsplice('[1,2,3,4,5]', 0, 3, 0, 2) ---------------------------------- [1,2,3,1,2] >>> sql(''' select jsplice('[1,2,3,4,5]', 2, -1) ''') # doctest: +NORMALIZE_WHITESPACE jsplice('[1,2,3,4,5]', 2, -1) ----------------------------- [3,4] **jsplit function** .. function:: jsplit(jpacks) -> [C1, C2, ...] Splits horizontally a jpack. Examples: >>> sql("select jsplit('[1,2,3]', '[3,4,5]')") # doctest: +NORMALIZE_WHITESPACE C1 | C2 | C3 | C4 | C5 | C6 --------------------------- 1 | 2 | 3 | 3 | 4 | 5 **jsplitv function** .. function:: jsplitv(jpacks) -> [C1] Splits vertically a jpack. Examples: >>> sql("select jsplitv(jmerge('[1,2,3]', '[1,2,3]', 'b', 'a', 3 ))") # doctest: +NORMALIZE_WHITESPACE C1 -- 1 2 3 1 2 3 b a 3 **jzip function** .. function:: jzip(args...) -> json string It combines the corresponding elements of input jpacks. Examples: >>> sql('''select jzip('["a", "b"]', '[1,2]','[4,5]')''') jzip('["a", "b"]', '[1,2]','[4,5]') ----------------------------------- [["a",1,4],["b",2,5]] **jzipdict function** .. function:: jzipdict(args...) -> json string It combines the correspinding elements of input jpacks into a jdict. Examples: >>> sql('''select jzipdict('["a", "b"]', '[1,2]','[4,5]')''') jzipdict('["a", "b"]', '[1,2]','[4,5]') --------------------------------------- {"a":[1,4],"b":[2,5]} **nl2j function** .. function:: nl2j(text) -> jpack Converts a text with newlines to a jpack. **s2j function** .. function:: s2j(tabpack) -> jpack Converts a space separated pack to a jpack. Examples: >>> sql("select s2j('1 2 3 ')") # doctest: +NORMALIZE_WHITESPACE s2j('1 2 3 ') -------------- ["1","2","3"] **t2j function** .. function:: t2j(tabpack) -> jpack Converts a tab separated pack to a jpack. Examples: >>> sql("select t2j(j2t('[1,2,3]'))") # doctest: +NORMALIZE_WHITESPACE t2j(j2t('[1,2,3]')) ------------------- ["1","2","3"] >>> sql("select t2j('asdfasdf')") # doctest: +NORMALIZE_WHITESPACE t2j('asdfasdf') --------------- ["asdfasdf"] :mod:`langtools` functions -------------------------- .. module:: functions.row.langtools **detectlang function** .. function:: detectlang(text1, text2,...) -> text Detects the language of a snippet of text by analysing its statistical properties. Examples: >>> sql("detectlang 'ελληνικά'") detectlang('ελληνικά') ------------------------------ greek-utf >>> sql("detectlang this is in english") detectlang('this is in english') -------------------------------- english >>> sql("detectlang ceci est en français") detectlang('ceci est en français') ----------------------------------- french >>> sql("detectlang este es el español") detectlang('este es el español') --------------------------------- spanish **stem function** .. function:: stem(text1, text2,...) -> text Does stemming according to the porter algorithm. Examples: >>> sql("stem 'cutting and creating'") stem('cutting and creating') ---------------------------- cut and creat >>> sql("stem ceci est en français cutting") stem('ceci est en français cutting') ------------------------------------- ceci est en françai cut **stem_en function** .. function:: stem_en(text1, text2,...) -> text Detects if the input is in english and only then does the porter stemming else it returns the input arguments concatenated Examples: >>> sql("stem_en 'cutting and creating'") stem_en('cutting and creating') ------------------------------- cut and creat >>> sql("stem_en ceci est en français cutting") stem_en('ceci est en français cutting') ---------------------------------------- ceci est en français cutting :mod:`logparse` functions ------------------------- .. module:: functions.row.logparse **apachelogsplit function** .. function:: apachelogsplit(apache_log_line) -> [ip, ident, authuser, date, request, status, bytes, referrer, useragent] Breaks a single apache log row into multiple fields. Examples: >>> table1(''' ... '1.1.1.1 - - [01/Feb/2001:01:02:03 +0001] "HEAD /test.com HTTP/1.1" 200 - "-" "reftest"' ... ''') >>> sql("select apachelogsplit(a) from table1") ip | ident | authuser | date | method | uri | httpver | status | bytes | referrer | useragent ---------------------------------------------------------------------------------------------------------------------------- 1.1.1.1 | None | None | 2001-02-01T01:02:03+0001 | HEAD | /test.com | 1.1 | 200 | None | None | reftest :mod:`mathops` functions ------------------------ .. module:: functions.row.mathops **farith function** .. function:: farith(calc) -> float or Fraction Takes as input a mathematical expression in polish notation and computes the result using fractional computation Examples: >>> sql("select farith('+',5,7)" ) farith('+',5,7) --------------- 12 >>> sql("select farith('-','*','/',15,'-',7,'+',1,1,3,'+',2,'+',1,1)" ) farith('-','*','/',15,'-',7,'+',1,1,3,'+',2,'+',1,1) ---------------------------------------------------- 5 **gaussdistribution function** .. function:: gaussdistribution(mean, sigma) -> float Returns a gaussian distribution. Sigma is the standard deviation of the distribution Examples: >>> sql("select gaussdistribution(10,5)") # doctest: +ELLIPSIS gaussdistribution(10,5) ----------------------- ... **randomrange function** .. function:: randomrange(start, end, step) -> int Returns a random number in the defined range Examples: >>> sql("select randomrange(0, 68, 1)") # doctest: +ELLIPSIS randomrange(0, 68, 1) --------------------- ... >>> sql("select randomrange(0, 68)") # doctest: +ELLIPSIS randomrange(0, 68) ------------------ ... **safediv function** .. function:: safediv(int, int, int) -> int Returns the first argument, when the division of the two subsequent numbers includes zero in denominator (i.e. in third argument) Examples: >>> sql("select safeDiv(1,5,0)") safeDiv(1,5,0) -------------- 1 **sqroot function** .. function:: sqroot(int) -> int Returns the square root of a given argument. Examples: >>> table1(''' ... 25 ... ''') >>> sql("select sqroot(a) from table1") sqroot(a) --------- 5.0 **tonumber function** .. function:: tonumber(variable) -> int or float Convert variable, whose type is str or unicode, to int or float, if it is feasible Examples: >>> sql("select tonumber('12.3') as val") val ---- 12.3 >>> sql("select tonumber(12.3) as val") val ---- 12.3 >>> sql("select tonumber('not a number') as val") val ------------ not a number >>> sql("select tonumber(null) as val") val ---- None :mod:`settings` functions ------------------------- .. module:: functions.row.settings **setlog function** .. function:: setlog(filename) Sets the log file path/filename for exec operator **setting function** .. function:: setting(setting_name, [value]) Sets and returns a setting's value Examples: >>> sql("setting 'tracing' 0") setting('tracing','0') ---------------------- False >>> sql("setting 'tracing'") setting('tracing') ------------------ False **toggle function** .. function:: toggle(setting_name) Toggles a boolean global setting Examples: >>> sql("toggle lala") toggle('lala') -------------- lala not found >>> sql("toggle tracing") toggle('tracing') -------------------- tracing is now: True :mod:`similarity` functions --------------------------- .. module:: functions.row.similarity **jaccard function** .. function:: jaccard(jpack1,jpack2) Return jaccard similarity value of two jpacks. Example: >>> table1(''' ... user1 movie1 20 ... user1 movie2 30 ... user2 movie1 40 ... user2 movie3 90 ... user2 movie4 90 ... user3 movie1 40 ... user3 movie3 80 ... user4 movie1 70 ... user4 movie2 10 ... ''') NOTE that only column b is jgrouped because *jaccard* operates on packs as sets, not weighted values, So packing also column c would not make any difference. >>> sql("""select u1.userid,u2.userid, jaccard(u1.pk, u2.pk) as similarity ... from ... (select a as userid, jgroup(b) as pk from table1 group by a) as u1, ... (select a as userid, jgroup(b) as pk from table1 group by a) as u2 ... where u1.userid>> table1(''' ... user1 movie1 20 ... user1 movie2 30 ... user2 movie1 40 ... user2 movie3 90 ... user2 movie4 90 ... user3 movie1 40 ... user3 movie3 80 ... user4 movie1 70 ... user4 movie2 10 ... ''') NOTE that only column b is jgrouped because *jaccard* operates on packs as sets, not weighted values, So packing also column c would not make any difference. >>> sql("""select u1.userid,u2.userid, sorensendice(u1.pk, u2.pk) as similarity ... from ... (select a as userid, jgroup(b) as pk from table1 group by a) as u1, ... (select a as userid, jgroup(b) as pk from table1 group by a) as u2 ... where u1.userid str Returns the input text with the stopwords removed. The case of the first letter matters. Examples: >>> table1(''' ... 'this and wood' 'NO more No words' ... 'No more stop words' 'more free time' ... ''') >>> sql("select filterstopwords(a,b) from table1") filterstopwords(a,b) -------------------- wood NO words stop words free time :mod:`stringdist` functions --------------------------- .. module:: functions.row.stringdist **damlevendist function** .. function:: damlevendist(str1, str2) Returns *int* which is the damerau-levenshtein distance between str1 and str2 Examples: >>> sql("select damlevendist('HURQBOHP','QKHOZ') ") damlevendist('HURQBOHP','QKHOZ') -------------------------------- 6 **levendist function** .. function:: levendist(str1, str2) Returns *int* which is the levenshtein distance between str1 and str2 Examples: >>> sql("select levendist('HURQBOHP','QKHOZ') ") levendist('HURQBOHP','QKHOZ') ----------------------------- 7 **quickstrdist function** .. function:: damlevendist(str1, str2) Returns *int* which is a string distance between str1 and str2, based on Python's difflib library. It is a lot faster than levendist or damlevendist. Examples: >>> sql("select quickstrdist('HURQBOHP','QKHOZ') ") quickstrdist('HURQBOHP','QKHOZ') -------------------------------- 8 :mod:`termsetops` functions --------------------------- .. module:: functions.row.termsetops **tset function** .. function:: termsetdiff(termset1, termset2) -> termset Returns the termset that is the difference of sets of termset1 - termset2. Examples: >>> table1(''' ... 't1 t2 t3' 't2 t3' ... 't3 t2 t1' 't3 t4' ... ''') >>> sql("select tset(a,b) from table1") tset(a,b) ----------- t1 t2 t3 t1 t2 t3 t4 **tsetcombinations function** .. function:: tsetcombinations(termset, r) -> termset Returns all the termset combinations of length r. It is a multiset operator that returns one column but many rows. .. seealso:: * :ref:`tutmultiset` functions >>> sql("select tsetcombinations('t1 t2 t3 t4',2)") C1 ----- t1 t2 t1 t3 t1 t4 t2 t3 t2 t4 t3 t4 **tsetdiff function** .. function:: termsetdiff(termset1, termset2) -> termset Returns the termset that is the difference of sets of termset1 - termset2. Examples: >>> table1(''' ... 't1 t2 t3' 't2 t3' ... 't3 t2 t1' 't3 t4' ... ''') >>> sql("select tsetdiff(a,b) from table1") tsetdiff(a,b) ------------- t1 t1 t2 :mod:`text` functions --------------------- .. module:: functions.row.text **comprspaces function** .. function:: comprspaces(text1, [text2,...]) -> text This function strips (from the beginning and the end) and compresses the spaces in its input. Examples: >>> table1(''' ... ' an example with spaces ' 'another example with spaces ' ... ''') >>> sql("select comprspaces(a,b) from table1") comprspaces(a,b) -------------------------------------------------- an example with spaces another example with spaces **contains function** .. function:: contains(str1,str2) -> bool Returns true if string *str1* contains *str2*. Examples: >>> sql("select contains('test string', 'str') as test ") test ---- 1 >>> sql("select contains('test string', 'nostr') as test ") test ---- 0 **cqlfields function** This functions returns the keywords inside a single column or aggregated from multiple columns. It plays well with Unicode. The difference of cqlkeywords to keywords is that cqlkeywords also strips cql syntax like "title all" or "author all". >>> table1(''' ... '(title all "scrieri") and (creator all "arghezi") and (title all "other")' ... '("maschinenschreiben") and (language all "ger")' ... '("sauer") and ("übungsbuch")' ... ''') >>> sql("select cqlfields(a) from table1") cqlfields(a) ------------------- title creator title language **cqlkeywords function** .. function:: cqlkeywords(text1, [text2,...]) -> text Returns the keywords inside a single column (text1) or aggregated from multiple columns. The difference of cqlkeywords to keywords is that cqlkeywords also strips cql syntax like "title all" or "author all" and plain cql directives like 'creator', 'title'... Examples: >>> table1(''' ... first(second) third+fourth ... πρωτο(δευτερο) τριτο_τέταρτο ... 'πέμπτο all' 'έκτο title all τεστ' ... 'title all and something' 'other' ... 'title and something' 'other' ... ''') >>> sql("select cqlkeywords(a,b) from table1") cqlkeywords(a,b) --------------------------------------------------- first second third fourth πρωτο δευτερο τριτο_τέταρτο έκτο τεστ something other and something other **crc32 function** .. function:: crc32(args) -> int Returns the CRC32 of args. Numbers are converted to text before hashing is performed. Examples: >>> sql("select crc32(65)") crc32(65) ---------- 2658551721 >>> sql("select crc32(6,5)") crc32(6,5) ---------- 1565899724 >>> sql("select crc32(5)") crc32(5) ---------- 2226203566 >>> sql("select crc32('5')") crc32('5') ---------- 1201448970 **escapechars function** **hashmd5 function** .. function:: hashmd5(args) Returns an MD5 hash of args. Numbers are converted to text before hashing is performed. Examples: >>> sql("select hashmd5(65)") hashmd5(65) -------------------------------- fc490ca45c00b1249bbe3554a4fdf6fb >>> sql("select hashmd5(6,5)") hashmd5(6,5) -------------------------------- f0d95c20cde50e3ca03cab53f986b6c3 >>> sql("select hashmd5(5)") hashmd5(5) -------------------------------- e4da3b7fbbce2345d7772b0674a318d5 >>> sql("select hashmd5('5')") hashmd5('5') -------------------------------- 7000aaf68ca7a93da0af3d03850571c2 **hashmd5mod function** .. function:: hashmd5mod(args, divisor) -> int Returns the *modulo* with divisor number of the MD5 hash of args. Numbers are converted to text before hashing is performed. Examples: >>> sql("select hashmd5mod(65, 3)") hashmd5mod(65, 3) ----------------- 0 >>> sql("select hashmd5mod(6,5, 4)") hashmd5mod(6,5, 4) ------------------ 2 >>> sql("select hashmd5mod(5, 5)") hashmd5mod(5, 5) ---------------- 3 >>> sql("select hashmd5mod('5', 5)") hashmd5mod('5', 5) ------------------ 4 **hashmodarchdep function** .. function:: hashmodarchdep(args, divisor) -> int Returns a hash of the args. .. note:: This hash function is architecture dependent (32bit vs 64bit). Examples: >>> sql("select hashmodarchdep(65,5)") #doctest:+ELLIPSIS hashmodarchdep(65,5) -------------------- ... >>> sql("select hashmodarchdep(6,5)") #doctest:+ELLIPSIS hashmodarchdep(6,5) ------------------- ... >>> sql("select hashmodarchdep(5,5)") #doctest:+ELLIPSIS hashmodarchdep(5,5) ------------------- ... >>> sql("select hashmodarchdep('5',5)") #doctest:+ELLIPSIS hashmodarchdep('5',5) --------------------- ... **hashmodarchdep2 function** .. function:: hashmodarchdep2(arg, divisor) -> int Returns a hash of the args. .. note:: This hash function is architecture dependent (32bit vs 64bit). It is specialized for 2 parameters Examples: >>> sql("select hashmodarchdep2(65,5)") #doctest:+ELLIPSIS hashmodarchdep2(65,5) --------------------- ... >>> sql("select hashmodarchdep2(6,5)") #doctest:+ELLIPSIS hashmodarchdep2(6,5) -------------------- ... >>> sql("select hashmodarchdep2(5,5)") #doctest:+ELLIPSIS hashmodarchdep2(5,5) -------------------- ... >>> sql("select hashmodarchdep2('5',5)") #doctest:+ELLIPSIS hashmodarchdep2('5',5) ---------------------- ... **isvalidutf8 function** .. function:: isvalidutf8(text) -> 1/0 Returns 1 if the input text is in valid UTF-8 format, or 0 if not. This function is used to find corrupted UTF-8 strings with a heuristic based on non common characters. Examples: >>> table1(''' ... test ... δοκιμή! ... sévignà ... évezred ... ''') >>> sql("select isvalidutf8(a) from table1") isvalidutf8(a) -------------- 1 1 1 1 **keywords function** .. function:: keywords(text1, [text2,...]) -> text Returns the keywords inside a single column (text1) or aggregated multiple columns. Examples: >>> table1(''' ... first(second) third+fourth ... πρωτο(δευτερο) τριτο+τέταρτο ... 'πέμπτο all' 'qwer.zxcv' ... ''') >>> sql("select keywords(a,b) from table1") keywords(a,b) --------------------------------------------------- first second third fourth πρωτο δευτερο τριτο τέταρτο πέμπτο all qwer zxcv **kwnum function** .. function:: kwnum(text1, [text2,...]) -> int Returns the number of simple keywords in a string. Its input should be words separated by spaces, as returned by cqlkeywords or keywords. Examples: >>> table1(''' ... 'word1 word2 word3' ... 'word1 word2' ... 'word' ... ''') >>> sql("select kwnum(a) from table1") kwnum(a) -------- 3 2 1 **normalizetext function** .. function:: normalizetext(text1, [text2,...]) -> text Normalizes a text by replacing all the non-words except \s ,.; with '_' Examples: >>> table1(''' ... first(second) third+fourth ... πρωτο(δευτερο) τριτο+τέταρτο ... 'πέμπτο all' 'έκτο title all τεστ' ... ''') >>> sql("select normalizetext(a,b) from table1") normalizetext(a,b) ---------------------------------------------------- first_second_ third_fourth πρωτο_δευτερο_ τριτο_τέταρτο πέμπτο all έκτο title all τεστ **normuni function** .. function:: normuni(str) Returns *str* normalised in the composed unicode normal form without replacing same look characters. For example this 'À' character can be encoded with one or two different characters, :func:`normuni` returns an one-character encoded version. This function is important to check true strings equality. Functions :func:`sunitouni` and :func:`unitosuni` are used in the examples to make it more comprehensive. Examples: .. note:: Returned results in the next two examples should look the same, if not that is a bug at the combined characters rendering of the shell that the documentation was created. >>> sql("select sunitouni('C\u0327') as test ") test ---- Ç >>> sql("select normuni(sunitouni('C\u0327')) as test ") test ---- Ç >>> sql("select unitosuni(normuni(sunitouni('C\u0327'))) as test ") test ------ \u00c7 **regexpcountuniquematches function** .. function:: regexpcountuniquematches(pattern, expression) Returns the number of matches of pattern in expression. Examples: >>> sql("regexpcountuniquematches 'start' 'start end start' ") regexpcountuniquematches('start','start end start') --------------------------------------------------- 1 >>> sql("regexpcountuniquematches 'start end' 'start end start' ") regexpcountuniquematches('start end','start end start') ------------------------------------------------------- 1 **regexpcountwithpositions function** .. function:: regexpcountwithpositions(pattern, expression,start = 0,min = 0.5,multiply = 1,) Returns a score of positioned matches of pattern in expression. Examples: >>> sql("regexpcountwithpositions 'start' 'start end start' ") regexpcountwithpositions('start','start end start') --------------------------------------------------- 1.75 >>> sql("regexpcountwithpositions 'start' 'start end start' ") regexpcountwithpositions('start','start end start') --------------------------------------------------- 1.75 >>> sql("regexpcountwithpositions 'first' 'first second third fourth'") regexpcountwithpositions('first','first second third fourth') ------------------------------------------------------------- 0.75 >>> sql("regexpcountwithpositions 'fourth' 'first second third fourth'") regexpcountwithpositions('fourth','first second third fourth') -------------------------------------------------------------- 1.5 >>> sql("regexpcountwithpositions 'fourth' 'first second third fourth' 1") regexpcountwithpositions('fourth','first second third fourth','1') ------------------------------------------------------------------ 0.5 **regexpcountwords function** .. function:: regexpcountwords(pattern, expression) Returns the number of matches of pattern in expression. If a match includes more than one words then it returns the number of the words. Examples: >>> sql("regexpcountwords 'start' 'start end start' ") regexpcountwords('start','start end start') ------------------------------------------- 2 >>> sql("regexpcountwords 'start end' 'start end start' ") regexpcountwords('start end','start end start') ----------------------------------------------- 2 **regexpr function** .. function:: regexp(pattern,expression[,replacestr]) This function returns a match to the first parenthesis of *pattern* or replaces the matches of *pattern* in *expression* with *replacestr*. `Pattern Syntax `_ is according to python's re module. Examples use `inversion`. Examples: >>> table1(''' ... 25 ... ''') >>> sql("regexpr 'start\s(\w+)\send' 'start otherword end' ") regexpr('start\s(\w+)\send','start otherword end') -------------------------------------------------- otherword >>> sql("regexpr '\W+' '@#$%@$#% tobereplaced @#$%@#$%' 'nonword' ") regexpr('\W+','@#$%@$#% tobereplaced @#$%@#$%','nonword') --------------------------------------------------------- nonwordtobereplacednonword >>> sql("select regexpr('(\w+).*?(\w+)', 'one two three')") regexpr('(\w+).*?(\w+)', 'one two three') ----------------------------------------- ["one","two"] **regexprfindall function** .. function:: regexprfindall(pattern,text) This function returns *all* matches of *pattern* in text. Examples: >>> sql("select regexprfindall('\w+', 'one')") regexprfindall('\w+', 'one') ---------------------------- ["one"] >>> sql("select regexprfindall('\w+', 'one two three')") regexprfindall('\w+', 'one two three') -------------------------------------- ["one","two","three"] **regexprmatches function** .. function:: regexprmatches(pattern, arg) This function returns true if the pattern matches arg or false otherwise. Examples use `inversion`. Examples: >>> sql("regexprmatches '(a)' 'qwer a qwer' ") regexprmatches('(a)','qwer a qwer') ----------------------------------- 1 **stripchars function** .. function:: stripchars(str[,stripchars]) Returns *str* removing leading and trailing whitespace characters or *stripchars* characters if given. Works like python's `strip function `_. Examples: >>> sql("select stripchars(' initial and final spaces ') as test ") test ------------------------ initial and final spaces >>> sql("select stripchars(' ',' <>') as test ") test ------------------------ initial and final spaces >>> sql("select stripchars(null)") stripchars(null) ---------------- None **sunitouni function** .. function:: sunitouni(str) Returns *str* replacing literal unicode code points to their string representation. Examples: >>> sql("select sunitouni('br\u00fbl\u00e9') as test ") test ------- brûlé >>> sql("select sunitouni('\u that is not a unicode code point') as test ") test ----------------------------------- \u that is not a unicode code point >>> sql("select sunitouni(null)") sunitouni(null) --------------- None >>> sql("select sunitouni(9)") sunitouni(9) ------------ 9 **textreferences function** .. function:: textreferences(text, maxlen = 5, pattern = (|_)(1|2)\d{3,3}(|_)) Returns the "Reference" section of documents. To find it, it searches for parts of the document that have a high density of pattern matches. .. parameters:: txt,maxlen,pattern txt: input text. maxlen: the size of the scrolling window over the text in which the density is calculated. pattern: regular expression that is matched against the lines of the text. By default the pattern matches year occurences so as to extract sections that look like references. Examples: >>> table1(''' ... eeeeeeeeeeeeee ... gggggggggggggg ... aaaaaaaaaaaaaa ... bbbbbbbbbbbbbb ... aaa_1914_ccccc ... bbb_2014_bbbbb ... dddd_2008_ddddddd ... cccc_2005_ccccc ... ccccc_2014_ccccc ... dddddd_2009_ddddd ... gggggggggggggg ... ''') >>> sql("select textreferences(group_concat(a,'\n'),1,'(|_)(1|2)\d{3,3}(|_)') as a from table1") a -------------------------------------------------------------------------------------------------- aaa_1914_ccccc bbb_2014_bbbbb dddd_2008_ddddddd cccc_2005_ccccc ccccc_2014_ccccc dddddd_2009_ddddd If an inadequate amount of newlines is found, it returns the text as is. >>> sql("select textreferences(group_concat(a,'.')) from table1") textreferences(group_concat(a,'.')) ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- eeeeeeeeeeeeee.gggggggggggggg.aaaaaaaaaaaaaa.bbbbbbbbbbbbbb.aaa_1914_ccccc.bbb_2014_bbbbb.dddd_2008_ddddddd.cccc_2005_ccccc.ccccc_2014_ccccc.dddddd_2009_ddddd.gggggggggggggg >>> sql("select textreferences('')") textreferences('') ------------------ **textwindow function** .. function:: textwindow(text, previous_word_count = 0, next_word_count = 0, middle_word_count = 1, pattern = None) Returns a rolling window over the text. The window includes *previous_word_count* words before the middle word and next_word_count words after the middleword. Optionally you may choose more than one words to be in the middle, and filter your window with a regular expression pattern If the value of prev_word_count or next_word_count is negative, and a pattern exists then all matches of the pattern are filtered out from prev and next output. Examples: >>> sql("select textwindow('This is a test phrase') ") middle ------ This is a test phrase >>> sql("select textwindow('This is a test phrase',1,1) ") prev1 | middle | next1 ----------------------- | This | is This | is | a is | a | test a | test | phrase test | phrase | >>> sql("select textwindow('This is a test phrase',1,1,2) ") prev1 | middle | next1 ---------------------------- | This is | a This | is a | test is | a test | phrase a | test phrase | test | phrase | >>> sql("select textwindow('This is a test phrase with pdb codes: 1abc 2bcd 3cde 4bde ',-2,1,2,'\d\w{3}' ) ") prev1 | prev2 | middle | next1 ---------------------------------- pdb | codes: | 1abc 2bcd | 3cde pdb | codes: | 2bcd 3cde | 4bde pdb | codes: | 3cde 4bde | pdb | codes: | 4bde | >>> sql("select textwindow('This is a test phrase (123) for filtering middle with a number',1,1,'\d+') ") prev1 | middle | next1 ----------------------- phrase | (123) | for **textwindow2s function** .. function:: textwindow2s(text, prev_word_count, middle_word_count, next_word_count, pattern) Returns a rolling window in the text. The window includes numberofprev words before the middle word and numberofnext words after the middleword. You may filter your window using a pattern. Examples: >>> sql("select textwindow2s('This is a test phrase',2,1,1) ") prev | middle | next ------------------------- | This | is This | is | a This is | a | test is a | test | phrase a test | phrase | >>> sql("select textwindow2s('This is a test phrase',2,1,1, '\w{4}') ") prev | middle | next ------------------------ | This | is is a | test | phrase a test | phrase | **uniqueterms function** .. function:: uniqueterms(text1, [text2,...]) -> text Returns the unique terms of an input string. Examples: >>> table1(''' ... 'word1 word2 word2' ... 'word1 word2 word1' ... 'word' ... ''') >>> sql("select uniqueterms(a) from table1") uniqueterms(a) -------------- word1 word2 word1 word2 word **unitosuni function** .. function:: unitosuni(str) Returns *str* replacing non-ascii characters with their equivalent unicode code point literal at the \u00 format. Examples: >>> sql("select unitosuni('brûlé') as test ") test --------------- br\u00fbl\u00e9 >>> sql("select sunitouni(null)") sunitouni(null) --------------- None >>> sql("select unitosuni(9)") unitosuni(9) ------------ 9 **utf8clean function** .. function:: utf8clean(text) -> text Removes control characters from input utf-8 text. Examples: >>> table1(''' ... test ... δοκιμή! ... sévignà ... évezred ... ''') >>> sql("select utf8clean(a) from table1") utf8clean(a) ------------- test δοκιμή! sévignà évezred :mod:`tzconverter` functions ---------------------------- .. module:: functions.row.tzconverter **tzconverter function** .. function:: tzconverter(timestamp,offset) Returns timestamps converted from UTC to target timezone, indicated by the offset parameter. Example:: >>> table1(''' ... "2010-12-05T00:00:00+00:00" ... "2010-12-05T00:01:00+00:00" ... "2010-12-05T00:02:00+00:00" ... ''') ... ''') >>> sql("select a, tzconverter(a,'-01:00') from table1 ") a | tzconverter(a,'-01:00') ----------------------------------------------------- 2010-12-05T00:00:00+00:00 | 2010-12-04T23:00:00-01:00 2010-12-05T00:01:00+00:00 | 2010-12-04T23:01:00-01:00 2010-12-05T00:02:00+00:00 | 2010-12-04T23:02:00-01:00 ... ''') >>> sql("select a, tzconverter(a,'-01') from table1 ") a | tzconverter(a,'-01') -------------------------------------------------- 2010-12-05T00:00:00+00:00 | 2010-12-04T23:00:00-01 2010-12-05T00:01:00+00:00 | 2010-12-04T23:01:00-01 2010-12-05T00:02:00+00:00 | 2010-12-04T23:02:00-01 >>> sql("select a, tzconverter(a,'-0100') from table1 ") a | tzconverter(a,'-0100') ---------------------------------------------------- 2010-12-05T00:00:00+00:00 | 2010-12-04T23:00:00-0100 2010-12-05T00:01:00+00:00 | 2010-12-04T23:01:00-0100 2010-12-05T00:02:00+00:00 | 2010-12-04T23:02:00-0100 >>> sql("select a, tzconverter(a,'+00:30') from table1 ") a | tzconverter(a,'+00:30') ----------------------------------------------------- 2010-12-05T00:00:00+00:00 | 2010-12-05T00:30:00+00:30 2010-12-05T00:01:00+00:00 | 2010-12-05T00:31:00+00:30 2010-12-05T00:02:00+00:00 | 2010-12-05T00:32:00+00:30 :mod:`util` functions --------------------- .. module:: functions.row.util **execprogram function** .. function:: execprogram(stdin=null, program_name, parameters, [raise_error]) -> text or blob Function *execprogram* executes a shell command and returns its output. If the value of the first argument is not *null*, the arguments value will be pushed in program's Standard Input. If the program doesn't return a *0* return code, then a madIS error will be raised, containing the contents of the program's error stream. If the last argument of *execprogram* is set to *null*, then all program errors will be returned as *null* (see "cat non_existent_file" examples below). Every one of the program's parameters must be provided as different arguments of the *execprogram* call (see "cat -n" example below). .. note:: Function *execprogram* tries by default to convert the program's output to UTF-8. If the conversion isn't succesfull, then it returns the output as a binary blob. Examples: >>> table1(''' ... echo test ... echo 1 ... ''') >>> sql("select execprogram(null, a, b) from table1") execprogram(null, a, b) ----------------------- test 1 >>> sql("select execprogram(null, null, '-l')") #doctest:+ELLIPSIS +NORMALIZE_WHITESPACE Traceback (most recent call last): ... OperatorError: Madis SQLError: Operator EXECPROGRAM: Second parameter should be the name of the program to run >>> sql("select execprogram(null, null, '-l', null)") #doctest:+ELLIPSIS +NORMALIZE_WHITESPACE execprogram(null, null, '-l', null) ----------------------------------- None >>> sql("select execprogram('test', 'cat')") execprogram('test', 'cat') -------------------------- test >>> sql('''select execprogram('test', 'cat', '-n')''') #doctest:+ELLIPSIS +NORMALIZE_WHITESPACE execprogram('test', 'cat', '-n') -------------------------------- 1 test >>> sql("select execprogram(null, 'NON_EXISTENT_PROGRAM')") #doctest:+ELLIPSIS +NORMALIZE_WHITESPACE Traceback (most recent call last): ... OperatorError: Madis SQLError: Operator EXECPROGRAM: [Errno 2] No such file or directory >>> sql("select execprogram(null, 'cat', 'non_existent_file')") #doctest:+ELLIPSIS +NORMALIZE_WHITESPACE Traceback (most recent call last): ... OperatorError: Madis SQLError: Operator EXECPROGRAM: cat: non_existent_file: No such file or directory >>> sql("select execprogram(null, 'cat', 'non_existent_file', null)") #doctest:+ELLIPSIS +NORMALIZE_WHITESPACE execprogram(null, 'cat', 'non_existent_file', null) --------------------------------------------------- None **failif function** .. function:: failif(condition [, messsage]) If condition is true, raises an error. If message is provided, the message is included in raised error. Examples: >>> sql("select failif(1=1,'exception') as answer") #doctest:+ELLIPSIS +NORMALIZE_WHITESPACE Traceback (most recent call last): ... OperatorError: Madis SQLError: Operator FAILIF: exception >>> sql("select failif(1=0,'exception') as answer") #doctest:+ELLIPSIS +NORMALIZE_WHITESPACE answer ------ 0 >>> sql("select failif(1=1) as answer") #doctest:+ELLIPSIS +NORMALIZE_WHITESPACE Traceback (most recent call last): ... OperatorError: Madis SQLError: Operator FAILIF: an error was found **gz function** .. function:: gz(text) -> gzip compressed blob Function *gz* compresses its input with gzip's maximum compression level. Examples: >>> table1(''' ... "qwerqewrqwerqwerqwerqwerqwer" ... "asdfasdfasdfasdfasdfasdfsadf" ... ''') >>> sql("select length(a), length(gz(a)) from table1") length(a) | length(gz(a)) ------------------------- 28 | 20 28 | 18 **sleep function** .. function:: sleep(seconds) This function waits for the given number of seconds before returning. The *seconds* parameters can be fractional (e.g. *0.1* will sleep for 100 milliseconds). Examples: >>> sql("select sleep(0.1)") sleep(0.1) ---------- 0.1 **ungz function** .. function:: ungz(blob) -> text Function *ungz* decompresses gzip blobs. If the input blobs aren't gzip compressed, then it just returns them as they are. Examples: >>> table1(''' ... "qwerqwerqwer" ... "asdfasdfasdf" ... ''') >>> sql("select ungz(gz(a)) from table1") ungz(gz(a)) ------------ qwerqwerqwer asdfasdfasdf >>> sql("select ungz('string'), ungz(123)") ungz('string') | ungz(123) -------------------------- string | 123 **urlrequest function** .. function:: urlrequest([null], url) -> response This functions connects to the *url* (via GET HTTP method) and returns the request's result. If first parameter is *null*, then in case of errors *null* will be returned. Examples: >>> sql("select urlrequest('http://www.google.com/not_existing')") Traceback (most recent call last): ... HTTPError: HTTP Error 404: Not Found >>> sql("select urlrequest(null, 'http://www.google.com/not_existing') as result") result ------ None **urlrequestpost function** .. function:: urlrequestpost(data_jdict, [null], url) -> response This functions connects to the *url* (via POST HTTP method), submits the *data_jdict*, and returns the request's result. If second parameter is *null*, then in case of errors *null* will be returned. Examples: >>> sql('''select urlrequestpost('{"POST_param_name":"data"}', 'http://www.google.com/not_existing')''') Traceback (most recent call last): ... HTTPError: HTTP Error 404: Not Found >>> sql('''select urlrequestpost('["POST_param_name","data"]', null, 'http://www.google.com/not_existing') as result''') result ------ None >>> sql("select urlrequestpost(jdict('param1','value1'), null, 'http://www.google.com/not_existing') as result") result ------ None >>> sql("select urlrequestpost(jpack('param1','value1'), null, 'http://www.google.com/not_existing') as result") result ------ None :mod:`variables` functions -------------------------- .. module:: functions.row.variables **flowname function** .. function:: flowname([str]) Sets and retrieves, 'flowname' variable Examples: >>> sql("flowname test flow ") flowname('test flow') --------------------- test flow >>> sql("flowname") flowname() ---------- test flow >>> sql("flowname 'arg1' arg2") # doctest: +NORMALIZE_WHITESPACE Traceback (most recent call last): ... OperatorError: Madis SQLError: Operator FLOWNAME: Flowname accepts only 1 argument **getvar function** .. function:: getvar(varname) -> value Returns the value of varname. This function exists so as to be able to force SQLite's engine to always evaluate the *getvar* function. This can be achieved by givine to the function an always changing second parameter. Examples: >>> sql("var 't' 5") var('t','5') ------------ 5 >>> sql("getvar 't'") getvar('t') ----------- 5 >>> sql("select getvar('t', random())") getvar('t', random()) --------------------- 5 **requirevars function** .. function:: requirevars(varname1, [varname2,...]) Checks if all variables (varname1,...) exist. If not it throws an exception. Examples: >>> sql("var 'cv1' 5") var('cv1','5') -------------- 5 >>> sql("var 'cv2' 10") var('cv2','10') --------------- 10 >>> sql("requirevars 'cv1' 'cv2'") requirevars('cv1','cv2') ------------------------ 1 >>> sql("requirevars cv1 cv2") requirevars('cv1 cv2') ---------------------- 1 >>> sql("requirevars 'cv1' 'testvar'") # doctest: +NORMALIZE_WHITESPACE Traceback (most recent call last): ... OperatorError: Madis SQLError: Operator REQUIREVARS: Variable testvar isn't initialized **setexecdb function** .. function:: setexecdb(str) Sets the database path/filename for exec operator. **var function** .. function:: var(varname[, value]) -> value Sets (if both varname and value are given) or returns (if only varname is given) the contents of a variable. Examples: >>> sql("var 'v'") # doctest: +NORMALIZE_WHITESPACE Traceback (most recent call last): ... OperatorError: Madis SQLError: Operator VAR: Variable 'v' does not exist >>> sql("var 'v' 5") var('v','5') ------------ 5 >>> sql("var 'v'") var('v') -------- 5 >>> sql("select var('v')") var('v') -------- 5