Difference between revisions of "Bdata"

From Bcontrol
(q)
Line 1: Line 1:
bdata is a wrapper for [http://sourceforge.net/projects/mym mym], which is a C++ function for accessing [http://www.mysql.com] from matlab.
+
bdata is a wrapper for [http://sourceforge.net/projects/mym mym], which is a C++ function for accessing [http://www.mysql.com mysql] from matlab.
 +
 
 
==Usage==
 
==Usage==
There are three usages of bdata:
+
To use bdata, you need to (1) connect to the mySQL server; and (2) access the database.
 +
 
 +
To connect, you would use:
 +
;connid=bdata('connect',host,user,pass)
 +
 
 +
If you are already connected, you can ask for the current connection ID:
 
;connid=bdata
 
;connid=bdata
 
:returns the current connection id.
 
:returns the current connection id.
;connid=bdata('connect',host,user,pass)
+
 
:bdata , by default connects to the mysql database with select, show, insert, explain but NOT update or delete privileges.  This usage is for the rare instance when you need to connect with extra privileges.   
+
Finally, you can access and query the database. The default is that you cannot ''modify'' the database (i.e., you cannot update or delete). You can, however, insert new items into the database. In other words, bdata by default connects to the mysql database with select, show, insert, explain but NOT update or delete privileges.   
;[varargout]=bdata(sql_query)
+
 
:Examples
+
A mySQL database contains many different "schemas". Each schema is composed of rows and columns; the columns define the type of entries (for example, rat_name, rat_weight, etc.) and each row is a particular entry (for example, 'Charlie', '345 grams').  When you query a mySQL database, you need to specify which table you are interested in and what items from the table you want to see.
 +
 
 +
:Examples:
 +
:bdata('select * from sessions')
 +
:: Will return the entire database within the sessions table
 +
:bdata('select * from sessions where ratname="C184"')
 +
:: Will return entries from sessions but only for rat C184
 +
:bdata('select * from sessions where ratname="C184" and n_done_trials>100')
 +
:: Will return entries from sessions but only for rat C184 and only those sessions with more than 100 trials
 +
:bdata('select n_done_trials,starttime from sessions where ratname="C184"')
 +
:: Will return only the n_done_trials column and the starttime column from sessions but for rat C184.
 +
: >> mysessid=19881; bdata('select n_done_trials from sessions where sessid="{S}"', mysessid)
 +
:: Using the "{S}" syntax allows using variables to describe what you want.
 +
: >> mysessid=19881; myrat='Charlie'; bdata('select * from sessions where sessid="{S}" and ratname="{S}"', mysessid, myrat)
 +
:: You can use consecutive "{S}" to refer to consecutive variables that go at the end of the query.
 
:sessid=bdata('select sessid from sessions where hostname like "%15"')
 
:sessid=bdata('select sessid from sessions where hostname like "%15"')
:bdata('select * from sessions limit 10')
+
:: mySQL also allows "like" to find all things that string match-- the above query would find all hostnames that end in "15"
 
:[rat, avg_hits]=bdata('select ratname, avg(percent_correct) from sessions group by ratname')
 
:[rat, avg_hits]=bdata('select ratname, avg(percent_correct) from sessions group by ratname')
:bdata('select * from sessions where sessid="{S}"',19881)
+
:: and mySQL will even do some computations for you-- the example above calculates an average for each rat.
 +
 
 +
In general the syntax is:
 +
;[varargout]=bdata(sql_query)
  
'''This third use, is the most common.  I highly recommend reading the documentation for [[mym]] and for the [http://dev.mysql.com/doc/refman/5.0/en/select.html select] syntax.'''
+
'''We highly recommend reading the documentation for [[mym]] and for the [http://dev.mysql.com/doc/refman/5.0/en/select.html select] syntax.'''
  
 
==Content==
 
==Content==

Revision as of 15:57, 22 April 2014

bdata is a wrapper for mym, which is a C++ function for accessing mysql from matlab.

Usage

To use bdata, you need to (1) connect to the mySQL server; and (2) access the database.

To connect, you would use:

connid=bdata('connect',host,user,pass)

If you are already connected, you can ask for the current connection ID:

connid=bdata
returns the current connection id.

Finally, you can access and query the database. The default is that you cannot modify the database (i.e., you cannot update or delete). You can, however, insert new items into the database. In other words, bdata by default connects to the mysql database with select, show, insert, explain but NOT update or delete privileges.

A mySQL database contains many different "schemas". Each schema is composed of rows and columns; the columns define the type of entries (for example, rat_name, rat_weight, etc.) and each row is a particular entry (for example, 'Charlie', '345 grams'). When you query a mySQL database, you need to specify which table you are interested in and what items from the table you want to see.

Examples:
bdata('select * from sessions')
Will return the entire database within the sessions table
bdata('select * from sessions where ratname="C184"')
Will return entries from sessions but only for rat C184
bdata('select * from sessions where ratname="C184" and n_done_trials>100')
Will return entries from sessions but only for rat C184 and only those sessions with more than 100 trials
bdata('select n_done_trials,starttime from sessions where ratname="C184"')
Will return only the n_done_trials column and the starttime column from sessions but for rat C184.
>> mysessid=19881; bdata('select n_done_trials from sessions where sessid="{S}"', mysessid)
Using the "{S}" syntax allows using variables to describe what you want.
>> mysessid=19881; myrat='Charlie'; bdata('select * from sessions where sessid="{S}" and ratname="{S}"', mysessid, myrat)
You can use consecutive "{S}" to refer to consecutive variables that go at the end of the query.
sessid=bdata('select sessid from sessions where hostname like "%15"')
mySQL also allows "like" to find all things that string match-- the above query would find all hostnames that end in "15"
[rat, avg_hits]=bdata('select ratname, avg(percent_correct) from sessions group by ratname')
and mySQL will even do some computations for you-- the example above calculates an average for each rat.

In general the syntax is:

[varargout]=bdata(sql_query)

We highly recommend reading the documentation for mym and for the select syntax.

Content

MySql is an open-source implementation of an SQL relational database. SQL stands for Structured Query Language.

The content accessible through bdata is organized into different databases or schemas.

bdata('show schemas');

Database           
+------------------+
 solodata           
 bdata              
 protocol           
 ratinfo            
 udata     

To learn about the kind of information you can access using bdata, you can Commands to get you started:

bdata('show tables from <schema>')
shows all the tables available to search in bdata
bdata('explain <tablename>')
explain tells you about the table, e.g. column names and types.