/**
 * Walkthrough of MusicDB stored procedure functionality.
 *
 * This files describes and exemplifies how to use the
 * various stored procedures in MusicDB. No users are 
 * allowed to do INSERTs, UPDATES, DELETES and similar.
 * This is all handled through the procedures.
 *
 * Generally procedures are executed like this:
 *		exec procedure_name arg1, arg2, arg3;
 *
 * Procedures return an error code, signifying if the procedure
 * executed succesfully. Retrieve it by declaring and using a 
 * variable thus:
 *		declare @status int;
 *		exec @status = procedure_name arg1, arg2, arg3;
 *
 * In most cases, we want the procedure to return an actual
 * value, as a result of what it changes in the database, for
 * example the id of a new row added to the database. In
 * this case, one of the argument are specified as 'output',
 * a 'bucket' the procedure can put a value into. You'll need
 * to declare a variable and send it into the procedure as
 * an argument, using the output keyword, for example:
 *		declare @nodeid int;
 *		exec make_node @cookie, @nodetype, @nodeid output;
 *
 * In this case, we supply the @cookie and @nodetype values,
 * and an empty variable @nodeid. The procedure will store
 * the nodeid of the new node in the @nodeid output variable.
 *
 * Take a look at 
 * Yukon > Databases > musicdb > 
 * Programmability > Stored Procedures
 * for a list of the procedures and their arguments.
 * The API will also be available from the course website.
 *
 * Many arguments are optional, meaning they have default
 * values. Thus they can be skipped in the argument list
 * when calling a procedure.
 * If you need to use the tenth argument of a procedure, 
 * and the five proceding arguments are optional, you must 
 * supply five NULLs before supplying the value of the tenth 
 * argument.
 * Similarly, you don't have to use an output variable to
 * retrieve the results of the procedure call. 
 */

/**
 * Encapsulate everyting in a transaction.
 * Undo everything when done, using rollback. 
 *
 * Use transactions when either:
 * 1. You need several statements to succeed as a whole, or
 * 2. When you only want to test stuff, and not leave "garbage" 
 * in the database. 
 * Make sure to always use 'commit' or 'rollback' with a begin 
 * transaction, or the transaction will still be running when 
 * you login later.
 *
 * If you abort the running of a transaction, open a separate
 * window and execute a single 'rollback' statement to make
 * sure the transaction is ended correctly.
 *
 * Generally: Use transactions sparringly!
 */
begin transaction;


/*** Create a new user ***/

/**
 * The make_user procedure takes a username
 * and a password as arguments. It returns the
 * id of the new user node.
 * Notice that we don't supply the output
 * argument in this example.
 */
exec make_user 'test', 'foobar';

/* Check the results */
select usrname from usrname;


/*** Login to the database ***/

declare @cookie varchar(16);

/** 
 * Login to the database using the test user.
 * The login procedure generates a cookie, a
 * session identifier, in the @cookie output 
 * variable. It creates a new row in the
 * session table. The cookie value is hidden,
 * in order to keep it secure.
 *
 * The cookie must be supplied when calling any 
 * procedure that manipulates the database, 
 * except for session_login and make_user
 *
 * The cookie can be stored in the client browser
 * and check for example when a user visits a page
 * to see if the user is logged in.
 */
exec session_login 'test', 'foobar', @cookie output;

/* Check the results */
select sessionid, usrid, started, ended from session;


/*** Get a test node ***/

/* 
A test node to play with. 
This variable will be used throughout the examples 
*/
declare @nodeid int;
set @nodeid = (select top(1) nodeid from name 
   where lower(name) = 
      'another brick in the wall (part 2)')


/*** Create a new tag ***/

declare @tagid int;

/**
 * The make_tag procedure takes a tag name
 * and returns the id of the new tag in the
 * @tagid output variable.
 * Retrieve it from the name or node tables.
 * The new node has a nodetype 'a'
 */
exec make_tag @cookie, 'Education', @tagid output;

/* Check the results */
select * from name where nodeid = @tagid;


/*** Attach the tag to a node ***/

declare @nodetagid int;

/**
 * This procedure will create a new property with
 * the member-tag property type, pointing to the tag
 * node with the specified tagid, and attach it to the
 * node with the specified nodeid.
 * It returns the id of the new member-tag property (propid).
 * Retrieve it from the property and/or tag tables.
 */
exec tag_node @cookie, @nodeid, @tagid, @nodetagid output;

/* Check the results */
select * from tag where propid = @nodetagid;


/*** Add a property to a node ***/

/**
 * Add a general property to a node.
 * The property must be identified with a nodeid
 * it is attached to, a proptypeid it is a type
 * of and any values added to the property.
 * 
 * Notice that the property in this case, is also
 * an attribute as it takes a value.
 */

/* Get the id of the origin-country property type */
declare @origin_country int;
set @origin_country = 
  (select proptypeid from proptype
  where name = 'origin-country');
select @origin_country;

declare @propid int;

/**
 * The make_property procedure takes the following
 * arguments: @cookie, @nodeid, @proptypename (if @proptypeid is NULL), 
 * @propid output, @proptypeid (if @proptypename is NULL).
 * Then follows a series of value arguments, containing the
 * value if it is an attribute: @lang, @sortstring, @value,
 * @number, etc etc. See procedure declaration.
 */
exec make_property @cookie, @nodeid, 'origin-country', 
  @propid output, NULL, NULL, NULL, 'United Kingdom';

/**
 * Note that origin-country takes a -value-, but no lang
 * or sortstring arguments. These are NULL. The rest of
 * the arguments are skipped. Instead of supplying a
 * proptypeid, I supply a proptypename, setting the
 * proptypeid argument to NULL (the first of the three)
 */

/* Check results */
select * from property where propid = @propid;
select * from attribute where propid = @propid;
select propid, nodeid, attribute.proptypeid, name, 
  attribute.value
from attribute, proptype
where attribute.proptypeid = proptype.proptypeid
  and propid = @propid;


/*** Connect the song to a genre ***/

/* Get the id of the Rock genre node */
declare @rockid int;
set @rockid = (select nodeid from name 
  where name = 'Rock' and nodetype = 'g');

/**
 * The procedure creates a member-genre property,
 * pointing to the specified genre node (in this 
 * case Rock), and attaches it to the node with 
 * the specified nodeid. It returns the id of the
 * new member-genre property (propid).
 * You'll find it in the property and attribute 
 * tables
 */
exec member_genre @cookie, @nodeid, @rockid, @propid output;

/* Check results */
select * from attribute where propid = @propid;
select attribute.propid, attribute.nodeid, refid, name 
from attribute, name
where attribute.propid = @propid and refid = name.nodeid;


/*** Make the node a member of the community ***/

/* Get the id of the community of the currently logged in user */
declare @communityid int;
set @communityid = (select dbo.this_communityid());

/**
 * The member_community procedure creates a member-community
 * property, pointing to the community with the specified
 * communityid, and attaches it to the node with the specified
 * nodeid. It returns the if of the new member-community
 * property (propid).
 * You'll find it in the property and attribute tables
 */
exec member_community @cookie, @nodeid, @communityid, @propid output;

/* Check results */
select * from attribute where propid = @propid;
select propid, attribute.nodeid, refid, shortname
from attribute, community
where propid = @propid and attribute.refid = community.nodeid;


/*** Logout of the database. ***/

/**
 * The session_logout procedure will end the session
 * idenfitied by the specified cookie value.
 * A session will stay open until the user either logs out
 * or logs in again. In the last case, the ended time of
 * the session will be the same as the startet time of the
 * new session.
 */
exec session_logout @cookie;

/* Clean up after you, your momma doesn't work here! */

/**
 * Undo all changes made thus far. 
 * Matches the 'begin transaction' statement.
 */
rollback;