--  ***************************************************************************
--  *   Copyright (C) 2012, Paul Lutus                                        *
--  *                                                                         *
--  *   This program is free software; you can redistribute it and/or modify  *
--  *   it under the terms of the GNU General Public License as published by  *
--  *   the Free Software Foundation; either version 2 of the License, or     *
--  *   (at your option) any later version.                                   *
--  *                                                                         *
--  *   This program is distributed in the hope that it will be useful,       *
--  *   but WITHOUT ANY WARRANTY; without even the implied warranty of        *
--  *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the         *
--  *   GNU General Public License for more details.                          *
--  *                                                                         *
--  *   You should have received a copy of the GNU General Public License     *
--  *   along with this program; if not, write to the                         *
--  *   Free Software Foundation, Inc.,                                       *
--  *   59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.             *
--  ***************************************************************************

use movies;

drop function if exists setup;

drop function if exists row_index;

drop function if exists get_org;  

delimiter |

CREATE function setup(org text) returns text
 BEGIN
   set @org = org;
   set @rowindex = -1;
   return org;
 END|

-- return a row index for page and pocket calculations

 CREATE function row_index(mode int) returns int
 BEGIN
   if(mode = 0)
   then -- need increment and page value
     set @rowindex = @rowindex + 1;
     return floor(@rowindex/4) + 1;
   else -- need pocket value
     return mod(@rowindex,4) + 1;
   end if;
 END|
 
-- return the current org definition

 CREATE function get_org() returns text
 BEGIN
   return @org;
 END|
 
 delimiter ;
 
 -- the argument "algorithm = temptable" retrieves the entire table
 -- before applying any WHERE conditions to the result, which preserves
 -- the correct page and pocket number for a subset of records

create or replace algorithm = temptable view dvd_generic_view as
  select Title,Comment,BluRay, row_index(0) as Page,row_index(1) as Pocket,
  LastModified from master_dvd_list where Org = get_org() order by Title;