sql. Getting SQL-query result as a hash

sql. Getting SQL-query result as a hash

^hash::sql{query}
^hash::sql{query}[$.limit(n) $.offset(o) $.distinct(true/false) $.bind[variables hash] $.type[hash/string/table]]

This constructor creates hash, in which keys' names are the values of fields in the first column of SQL-query's result. Other columns' names become nested keys' names, and their values become respective keys' values.
When the result contains only one column, constructor creates the hash, where values of the column become keys of hash associated with logical value truth .

Optional parameters:

$.limit(n)	get only n records.
$.offset(o)	skip first o records of the query result.
$.bind[hash] *[3.1.4]*	variables to bind, see «Queries with bound variables»
$.distinct(true/false)	false or 0=consider duplicate an error (default); true or 1=get records with unique keys.
$.type[hash/string/table] *[3.3.0]*	hash=each hash item contain hash (default); string=each hash item contain string. You must specify exactly two columns in your SQL query; table=each hash item containing table.

By default, duplicate of a value in key column is considered an error, but if you want the method to get the records with unique keys, set flag $.distinct(true).
Note: such use results in spare data interchange between client and server. You had better change the query so that the desired uniqueness should be the server's responsibility. If you need data as both table and hash, consider using table::sql and table.hash together.

Example: hash of hash
With database containing hash_table…
pet   food   aggressive
cat   milk   very
dog   bone   never

…the code…
^connect[connect string]{
   $hash_of_hash[^hash::sql{
      select
         pet,
         food,
         aggressive
      from
         hash_table
   }]
}

…will result in hash of the following structure:
$hash_of_hash[
   $.cat[
      $.food[milk]
      $.aggressive[very]
   ]
   $.dog[
      $.food[bone]
      $.aggressive[never]
   ]
]

…from which we can effectively retrieve information, e.g. in such a way:
$animal[cat]
$animal likes eating $multi_level_hash.$animal.food

Example: hash of bool
With database containing participants table…
name
Konstantin
Alexander

…the code…
^connect[connect string]{
   $participants[^hash::sql{select name from participants}]
}

…will result in hash of the following structure:
$participants[
   $.Konstantin(true)
   $.Alexander(true)
]

…from which we can effectively retrieve information, e.g. in such a way:
$name[Ivan]
$name ^if($participants.$name){participates}{do not participate} in the project

Last updated: 04.09.2008