Difference between revisions of "Bdata"

From Bcontrol
Line 15: Line 15:
 
A mySQL database contains many different "schemas". By default, bdata uses the "bdata" schema (although you can access other schemas also.) Each schema can contain many different tables. For example, the bdata schema contains the "sessions" table, the "protocol_info" table, and others. Each table 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.
 
A mySQL database contains many different "schemas". By default, bdata uses the "bdata" schema (although you can access other schemas also.) Each schema can contain many different tables. For example, the bdata schema contains the "sessions" table, the "protocol_info" table, and others. Each table 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 of looking at schemas and tables:
+
===Examples of looking at schemas and tables:===
 
:bdata('show schemas')
 
:bdata('show schemas')
 
:: will list all the schemas
 
:: will list all the schemas
Line 25: Line 25:
 
:: will list all the columns in the samedifferent table from the protocol scheme
 
:: will list all the columns in the samedifferent table from the protocol scheme
  
 +
===Examples of extracting data from a table:===
 
:bdata('select * from sessions')
 
:bdata('select * from sessions')
 
:: Will return the entire database within the sessions table
 
:: Will return the entire database within the sessions table
Line 42: Line 43:
 
:: and mySQL will even do some computations for you-- the example above calculates an average for each rat.
 
:: 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)
 +
 +
===Read the select and mym documentation:===
 
The most useful thing to do is to understand mySQL's '''select''' syntax. Click '''[http://dev.mysql.com/doc/refman/5.0/en/select.html here for select syntax documentation].''' We also highly recommend reading the documentation for [[mym]].
 
The most useful thing to do is to understand mySQL's '''select''' syntax. Click '''[http://dev.mysql.com/doc/refman/5.0/en/select.html here for select syntax documentation].''' We also highly recommend reading the documentation for [[mym]].
  
  
In general the syntax is:
 
;[varargout]=bdata(sql_query)
 
  
  

Revision as of 16:25, 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. Usually you don't need to connect explicitly; if you try to access the database and you aren't connected, you'll get connected. The default connection will give you permissions to read the database or insert new rows into it, but you cannot modify existing rows (i.e., you cannot update or delete). In other words, bdata by default connects to the bdata schema within the mysql database with select, show, insert, explain but NOT update or delete privileges.

If you really want to connect with different privileges, you would use:

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

To explicitly connect (which you don't need to do), or if you are already connected and want to know the current connection ID:

connid=bdata
returns the current connection id.

Of course, what you mostly want it for is to access and query the database.

A mySQL database contains many different "schemas". By default, bdata uses the "bdata" schema (although you can access other schemas also.) Each schema can contain many different tables. For example, the bdata schema contains the "sessions" table, the "protocol_info" table, and others. Each table 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 of looking at schemas and tables:

bdata('show schemas')
will list all the schemas
bdata('show tables')
will list all the tables in the bdata schema
bdata('explain sessions')
will list all the columns in the bdata.sessions table
bdata('explain protocol.samedifferent')
will list all the columns in the samedifferent table from the protocol scheme

Examples of extracting data from a table:

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)

Read the select and mym documentation:

The most useful thing to do is to understand mySQL's select syntax. Click here for select syntax documentation. We also highly recommend reading the documentation for mym.



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.