|
-- This script should be run in the schema where the bookmarks exist (USER_PROFILE table). |
|
-- For IU, that is IU_DMLLIB. |
|
-- |
|
-- We use two staging tables to hold the information saved in XML strings in the database. |
|
-- The bookmark information goes to staging_bookmarks. The Container information goes to |
|
-- staging_items. Once staging items is populated, some conversion of data occurs and new |
|
-- columns get populated in staging_bookmarks. |
|
-- |
|
drop table if exists staging_bookmarks ; |
|
|
|
create table staging_bookmarks |
|
( id integer, |
|
username varchar( 128 ), |
|
foldername varchar( 255 ), |
|
foldercomment varchar( 255 ), |
|
container varchar( 255 ), |
|
bookmarkname varchar( 255 ), |
|
bookmarkcomment varchar( 255 ), |
|
offset integer, |
|
avalon_offset integer, |
|
label varchar( 255 ) |
|
); |
|
|
|
create index staging_bookmarks_idx on staging_bookmarks (username, foldername); |
|
create index staging_bookmarks_idx2 on staging_bookmarks (id); |
|
|
|
drop table if exists staging_items; |
|
|
|
create table staging_items |
|
( container varchar( 255 ) |
|
, containerLabel varchar( 255 ) |
|
, itemLabel varchar( 255 ) |
|
, begin_offset integer |
|
, end_offset integer |
|
, mediaRef integer |
|
, mediaRefLabel varchar(255) |
|
); |
|
|
|
create index staging_items_idx on staging_items (container); |
|
create index staging_items_idx2 on staging_items (begin_offset, end_offset); |
|
-- |
|
-- |
|
-- The code in processChunks, processDivs, updateItems, groupBookmarks |
|
-- pertains to the logic of converting Variations bookmarks to Avalon markers. |
|
-- The data related to the Variations chunks within Containers need to be identified by |
|
-- their mediaRef label, which in turn can be used in Avalon to identify the Media Object. |
|
-- |
|
-- The procedures processDivs and processChunks are called from updateItems, which addresses |
|
-- one container at a time. |
|
-- |
|
-- The procedure groupBookmarks, which calls updateItems, assumes the table staging_bookmarks |
|
-- has been populated via the allBookmarks procedure. It looks at staging_bookmarks to decide |
|
-- which containers to process. It only processes containers that are referenced in |
|
-- bookmarks being converted (already being staged). |
|
-- |
|
-- The schema IU_METADATA is referenced. You should find/replace that reference with the |
|
-- name of the schema that contains the Variations Containers and MediaRefs information |
|
-- in your installation of Variations. |
|
-- |
|
-- |
|
|
|
drop procedure if exists processChunks; |
|
DELIMITER | |
|
|
|
create procedure processChunks( itemXml mediumtext, itemXpath varchar(255)) |
|
begin |
|
-- |
|
-- By inspecting each chumk of an item of the current container, we can get two pieces of information that |
|
-- will be important for the bookmark conversion process: |
|
-- 1. the mediaRef for the item. Later this enables us to get the mediaRef label, which in turn allows |
|
-- us to identify the MediaObject in Avalon. |
|
-- 2. the lenght of the item. Variations bookmarks are offset within the container, not within the item. |
|
-- To be able to convert that to an offset within each item (since in Avalon that is what we need), |
|
-- we need to know for each item what its begin and end offset are in the context of the container. |
|
-- This is accomplished with the @itemMaxEnd variable here and the variable itemBegin and |
|
-- curr_length in updateItems. |
|
-- |
|
-- |
|
DECLARE J INT UNSIGNED DEFAULT 1; |
|
DECLARE totalChunks INT UNSIGNED DEFAULT 1; |
|
declare chunkEnd int unsigned default 0; |
|
|
|
set totalChunks = EXTRACTVALUE(itemXml, concat('COUNT(', itemXpath, '/Chunk)' )); |
|
While (J <= totalChunks ) DO |
|
|
|
set chunkEnd = EXTRACTVALUE(itemXml, concat( itemXpath, '/Chunk[', J, ']/ContentInterval/@end[1]')); |
|
|
|
if chunkEnd > @itemMaxEnd then |
|
set @itemMaxEnd = chunkEnd; |
|
end if; |
|
|
|
if @getMediaRef = 1 then |
|
-- |
|
set @mediaRef = EXTRACTVALUE(itemXml, concat( itemXpath, '/Chunk[', J, ']/ContentInterval/@mediaRef[1]')); |
|
set @mediaRef = replace( @mediaRef, 'IU/MediaObject/', ''); |
|
-- |
|
set @getMediaRef = 0; |
|
end if; |
|
-- |
|
set J := J+1; |
|
|
|
END WHILE; |
|
end | |
|
|
|
DELIMITER ; |
|
|
|
drop procedure if exists processDivs; |
|
DELIMITER | |
|
|
|
create procedure processDivs( itemXml mediumtext, itemXpath varchar(255) ) |
|
begin |
|
-- |
|
-- this procedure is recursive to go through a tree of Divs after processing |
|
-- the chunks in the current Div. |
|
-- |
|
DECLARE J INT UNSIGNED DEFAULT 1; |
|
DECLARE totalDivs INT UNSIGNED DEFAULT 1; |
|
|
|
set totalDivs = EXTRACTVALUE(itemXml, concat('COUNT(', itemXpath, '/Div)' )); |
|
While (J <= totalDivs ) DO |
|
call processChunks( itemXml, concat( itemXpath, '/Div[', J, ']')); |
|
call processDivs ( itemXml, concat( itemXpath, '/Div[', J, ']')); |
|
set J := J+1; |
|
END WHILE; |
|
end | |
|
|
|
DELIMITER ; |
|
|
|
|
|
drop procedure if exists updateItems; |
|
DELIMITER | |
|
|
|
CREATE PROCEDURE updateItems( containerId integer ) |
|
BEGIN |
|
-- |
|
-- This procedure populates the staging_items table |
|
declare nestedCount int unsigned default 1; |
|
DECLARE I INT UNSIGNED DEFAULT 1; |
|
DECLARE J INT UNSIGNED DEFAULT 1; |
|
declare containerXml mediumtext; |
|
declare itemLabel varchar(255); |
|
declare xpathString varchar(255); |
|
declare itemXpath varchar(255); |
|
declare containerLabel varchar(255); |
|
|
|
declare currLength int UNSIGNED default 0; |
|
declare itemBegin int UNSIGNED default 0; |
|
|
|
delete from staging_items |
|
where container = containerId; |
|
commit; |
|
|
|
-- get the data related to the container. The data is in XML format; information needs to be extracted from this XML. |
|
select structure |
|
into containerXml |
|
from IU_METADATA.Container |
|
where sql_id = containerId; |
|
|
|
set containerLabel = EXTRACTVALUE(containerXml, '/ContainerStructure/@label[1]'); |
|
set xpathString = '/ContainerStructure/Item'; |
|
|
|
-- how many items are there in this Container? |
|
SET nestedCount = EXTRACTVALUE(containerXml, concat('COUNT(', xpathString, ')' )); |
|
|
|
-- loop through each item to extract the chunks (which may be inside Divs). |
|
WHILE (I <= nestedCount) DO |
|
set itemLabel = EXTRACTVALUE(containerXml, concat( xpathString, '[', I, ']/@label[1]')); |
|
set itemXpath = concat( xpathString, '[', I, ']'); |
|
|
|
-- see comment on "processChunks" related to the following two variables |
|
set itemBegin = currLength; |
|
set @itemMaxEnd = 0; |
|
set @mediaRef = ''; |
|
set @getMediaRef = 1; -- flag set for us to get the container ID for the media object for this item at the first opportunity. |
|
|
|
call processChunks( containerXml, itemXpath ); |
|
call processDivs( containerXml, itemXpath ); |
|
|
|
-- see comment on "processChunks" related to the following two variables. |
|
set currLength = currLength + @itemMaxEnd; |
|
|
|
insert into staging_items( container, containerLabel, itemLabel, begin_offset, end_offset, mediaRef ) |
|
values ( containerId, containerLabel, itemLabel, itemBegin, currLength, @mediaRef ); |
|
|
|
set I = I+1; |
|
end while; |
|
|
|
-- the label will identify the MediaObject in Avalon. |
|
update staging_items |
|
set mediaRefLabel = ( select label from IU_METADATA.MediaObject where sql_id = staging_items.mediaRef ); |
|
commit; |
|
|
|
end | |
|
|
|
DELIMITER ; |
|
|
|
|
|
drop procedure if exists groupBookmarks; |
|
DELIMITER | |
|
|
|
CREATE PROCEDURE groupBookmarks() |
|
BEGIN |
|
-- When flattening the bookmark structure, we decided that: |
|
-- 1. the Folder name in Variations will become the name of a playlist item in Avalon. |
|
-- 2. the bookmark in Variations will become the marker in Avalon. |
|
-- Thus, we need to ensure that, when flattening the bookmark structure, if a folder name |
|
-- is associated with more than one MediaObject, we group the bookmarks for different |
|
-- MediaObjects under different playlist items. We do this by adding information to the |
|
-- folder names. |
|
-- |
|
declare curr_username varchar(128); |
|
declare curr_folder varchar(255); |
|
declare curr_cnt integer; |
|
declare curr_container varchar(255); |
|
declare curr_cnt2 integer; |
|
|
|
DECLARE done INTEGER DEFAULT 0; |
|
declare displayTitle varchar(255); |
|
-- |
|
declare containers_c cursor for |
|
select distinct container |
|
from staging_bookmarks; |
|
|
|
declare bookmarks_c cursor for |
|
Select username, foldername, count(*) |
|
from staging_bookmarks |
|
group by username, foldername |
|
having count(*) > 1; |
|
|
|
declare foldernames_c cursor for |
|
select username, foldername, count(distinct label) |
|
from staging_bookmarks |
|
group by username, foldername |
|
having count(distinct label) > 1; |
|
|
|
DECLARE CONTINUE HANDLER |
|
FOR NOT FOUND SET done = 1; |
|
-- |
|
-- our first step is to map Variations files/offset to avalon files/offset |
|
-- |
|
OPEN containers_c; |
|
-- |
|
-- populate the staging_items table |
|
-- |
|
while done = 0 do |
|
fetch containers_c into curr_container; |
|
if done = 0 then |
|
call updateItems( curr_container ); |
|
end if; |
|
end while; |
|
|
|
update staging_bookmarks, staging_items |
|
set avalon_offset = staging_bookmarks.offset - staging_items.begin_offset |
|
, staging_bookmarks.label = staging_items.mediaRefLabel |
|
where staging_bookmarks.offset >= begin_offset |
|
and staging_bookmarks.offset < end_offset |
|
and staging_bookmarks.container = staging_items.container; |
|
commit; |
|
-- |
|
-- now try adding info to foldernames that correspond to multiple bookmarks to make the playlist item |
|
-- in Avalon more meaningful. This also ensures one mediaObject per playlist item (the differentiated |
|
-- foldernames will become the names of the playlist items). |
|
-- |
|
OPEN bookmarks_c; |
|
-- |
|
set done = 0; |
|
while done = 0 do |
|
fetch bookmarks_c into curr_username, curr_folder, curr_cnt; |
|
if done = 0 then |
|
update staging_bookmarks, staging_items |
|
set staging_bookmarks.foldername = |
|
case when instr( staging_bookmarks.foldername, staging_items.containerLabel ) = 0 |
|
then concat_ws( ' - ', staging_bookmarks.foldername, staging_items.containerlabel, staging_items.itemLabel ) |
|
else concat_ws( ' - ', staging_bookmarks.foldername, staging_items.itemLabel ) |
|
end |
|
where staging_bookmarks.username = curr_username |
|
and staging_bookmarks.foldername = curr_folder |
|
and staging_bookmarks.offset >= begin_offset |
|
and staging_bookmarks.offset < end_offset |
|
and staging_bookmarks.container = staging_items.container; |
|
end if; |
|
end while; |
|
|
|
close bookmarks_c; |
|
-- |
|
-- as our last attempt to differentiate foldernames that correspond to different mediaRef files, we add the mediaRef |
|
-- label to the folder name. The foldernames_c cursor only selects foldernames that are currently associated with |
|
-- more than on mediaRef. |
|
-- |
|
OPEN foldernames_c; |
|
-- |
|
set done = 0; |
|
while done = 0 do |
|
fetch foldernames_c into curr_username, curr_folder, curr_cnt; |
|
if done = 0 then |
|
update staging_bookmarks, staging_items |
|
set staging_bookmarks.foldername = concat_ws( ' - ', staging_bookmarks.foldername, staging_items.mediaRefLabel ) |
|
where staging_bookmarks.username = curr_username |
|
and staging_bookmarks.foldername = curr_folder |
|
and staging_bookmarks.offset >= begin_offset |
|
and staging_bookmarks.offset < end_offset |
|
and staging_bookmarks.container = staging_items.container; |
|
end if; |
|
end while; |
|
|
|
close foldernames_c; |
|
|
|
end | |
|
|
|
DELIMITER ; |
|
|
|
-- |
|
-- Procedure allBookmarks calls saveBookmarks and saveFolders to process the XML where the bookmark structure is saved. |
|
-- wW only export bookmarks of type "player" since these are the only ones that can be imported into Avalon. |
|
-- |
|
|
|
drop procedure if exists saveBookmarks; |
|
DELIMITER | |
|
|
|
create procedure saveBookmarks( xpathString varchar(255), fname varchar(255), fcomment varchar(255), I int, inout cnt int ) |
|
begin |
|
-- |
|
DECLARE J INT UNSIGNED DEFAULT 1; |
|
DECLARE markerCount INT UNSIGNED DEFAULT 1; |
|
DECLARE containerID varchar(20); |
|
DECLARE bookmark_type varchar(20); |
|
|
|
set markerCount := EXTRACTVALUE(@xml, concat('Count(', xpathString, '/Bookmark)')); |
|
set J := 1; |
|
While (J <= markerCount ) DO |
|
|
|
set cnt := cnt+1; |
|
|
|
set bookmark_type = EXTRACTVALUE(@xml, concat( xpathString, '/Bookmark[$J]/@type[1]')); |
|
|
|
if bookmark_type = 'player' then |
|
set containerId := replace( EXTRACTVALUE(@xml, concat( xpathString, '/Bookmark[$J]/ContainerRef')), 'IU/Container/', '' ); |
|
|
|
insert into staging_bookmarks( id, username, foldername, foldercomment, bookmarkname, bookmarkcomment, container, offset ) |
|
values( cnt, @avalon_user, fname, fcomment |
|
, EXTRACTVALUE(@xml, concat( xpathString, '/Bookmark[$J]/@name[1]')) |
|
, EXTRACTVALUE(@xml, concat( xpathString, '/Bookmark[$J]/Annotation')) |
|
, containerID |
|
, EXTRACTVALUE(@xml, concat( xpathString, '/Bookmark[$J]/ContainerRef/@offset')) |
|
); |
|
end if; |
|
set J := J+1; |
|
END WHILE; |
|
end | |
|
|
|
DELIMITER ; |
|
|
|
drop procedure if exists saveFolders; |
|
DELIMITER | |
|
|
|
create procedure saveFolders( inout cnt int, xpathString varchar(255), folderName varchar(255) ) |
|
begin |
|
-- |
|
-- this is a recursive procedure that processes the bookmarks inside a folder and then subfolders. |
|
-- note that by processing bookmarks first then folders, we may end up taking bookmarks out of |
|
-- the original order if bookmarks are interspersed with folders. At IU, this was rare and thus |
|
-- deemed acceptable. In Avalon the user can later sort their playlist at will. |
|
-- |
|
declare fcomment varchar(255); |
|
declare nestedCount int unsigned default 1; |
|
declare fname varchar(255) default ''; |
|
DECLARE I INT UNSIGNED DEFAULT 1; |
|
declare concatFolderName varchar(255); |
|
-- |
|
set xpathString = concat( xpathString, '/Folder' ); |
|
SET nestedCount:=EXTRACTVALUE(@xml, concat('COUNT(', xpathString, ')' )); |
|
|
|
WHILE (I <= nestedCount) DO |
|
set fname := EXTRACTVALUE(@xml, concat( xpathString, '[', I, ']/@name[1]')); |
|
if length(folderName) = 0 then |
|
set concatFolderName = fname; |
|
else |
|
set concatFolderName := concat_ws( ' - ', folderName, fname); |
|
end if; |
|
-- |
|
set fcomment := EXTRACTVALUE(@xml, concat( xpathString, '[', I, ']/Annotation')); |
|
call saveBookmarks( concat(xpathString, '[', I, ']'), concatFolderName, fcomment, I, cnt); |
|
call saveFolders( cnt, concat(xpathString, '[', I, ']'), concatFolderName ); |
|
SET I:= I+1; |
|
END WHILE; |
|
end | |
|
|
|
DELIMITER ; |
|
|
|
|
|
DROP PROCEDURE IF EXISTS allBookmarks; |
|
|
|
DELIMITER | |
|
|
|
CREATE PROCEDURE allBookmarks() |
|
BEGIN |
|
-- |
|
DECLARE foldercount INT UNSIGNED; |
|
DECLARE I INT UNSIGNED DEFAULT 1; |
|
declare cnt int unsigned default 0; |
|
declare fname varchar(255) default ''; |
|
-- |
|
declare curr_user varchar(128); |
|
declare bookmark_xml mediumtext; |
|
-- |
|
DECLARE done INTEGER DEFAULT 0; |
|
-- |
|
-- This cursor has logic specific to IU to limit the cursor to our current users via the "where clause". |
|
-- |
|
declare users_c cursor for |
|
Select USERNAME |
|
from USER_PROFILE |
|
where username in ( select distinct memberid |
|
FROM GROUP_MEMBERS T1 JOIN GROUPS T2 ON (T2.SQL_ID = T1.GROUPID) |
|
WHERE (T2.SQL_ID IN ( 1, 5, 11, 15, 16, 12, 535, 1573) OR T2.NAME LIKE 'BL%') |
|
AND T2.EXPIRATION_DT > NOW() ); |
|
-- |
|
declare user_bookmarks_c cursor for |
|
Select bookmark |
|
from USER_PROFILE |
|
where username = curr_user; |
|
-- |
|
DECLARE CONTINUE HANDLER |
|
FOR NOT FOUND SET done = 1; |
|
-- |
|
-- we start by cleaning up past work; |
|
-- |
|
delete from staging_bookmarks; |
|
delete from staging_items; |
|
commit; |
|
-- |
|
OPEN users_c; |
|
-- |
|
while done = 0 do |
|
fetch users_c into curr_user; |
|
if done = 0 then |
|
open user_bookmarks_c; |
|
-- |
|
set @avalon_user = substr( curr_user, 1, instr( curr_user, '\@')-1 ); |
|
while done = 0 do |
|
fetch user_bookmarks_c into bookmark_xml; |
|
if done = 0 then |
|
set @xml = bookmark_xml; |
|
call saveBookmarks( '/BookmarkTree', '/BookmarkTree', '', 1, cnt); |
|
set cnt = 0; |
|
call saveFolders( cnt, '/BookmarkTree', '' ); |
|
end if; |
|
end while; |
|
-- |
|
set done = 0; |
|
close user_bookmarks_c; |
|
end if; |
|
commit; |
|
end while; |
|
-- |
|
close users_c; |
|
-- |
|
END | |
|
|
|
DELIMITER ; |
|
|
|
-- |
|
-- generateJson reads staging_bookmarks and, via select statements, outputs the data in the JSON format. |
|
-- one should call this routine by silencing the headers and grid in the output. Create a file that can |
|
-- serve as input to mySql to call this routine. Then run: |
|
-- |
|
-- mysql -sN -u username dataqbase < input.sql > output.json |
|
-- |
|
-- |
|
drop procedure if exists generateJson; |
|
DELIMITER | |
|
|
|
CREATE PROCEDURE generateJson() |
|
BEGIN |
|
|
|
declare curr_username varchar(128) default ''; |
|
declare prev_username varchar(128) default ''; |
|
declare curr_folder varchar(255) default ''; |
|
declare prev_folder varchar(255) default ''; |
|
declare good_folder varchar(255) default ''; |
|
declare curr_id INT UNSIGNED; |
|
declare container_string varchar(255); |
|
declare fcomment varchar(255); |
|
declare bname varchar(255); |
|
declare start_time INT UNSIGNED; |
|
declare firstBookmark int UNSIGNED default 1; |
|
|
|
DECLARE done INTEGER DEFAULT 0; |
|
declare defaultPlayListName varchar(255) default "Variations Bookmarks"; |
|
-- |
|
declare bookmarks_c cursor for |
|
select id, username, foldername, label, foldercomment, bookmarkname, avalon_offset |
|
from staging_bookmarks |
|
where avalon_offset is not null |
|
and label is not null |
|
order by username, id; |
|
|
|
DECLARE CONTINUE HANDLER |
|
FOR NOT FOUND SET done = 1; |
|
|
|
open bookmarks_c; |
|
|
|
while done = 0 do |
|
fetch bookmarks_c into curr_id, curr_username, curr_folder, container_string, fcomment, bname, start_time; |
|
|
|
if done = 0 then |
|
|
|
-- deal with change of username |
|
if prev_username <> curr_username then |
|
if prev_username = '' then |
|
-- this is the first fetched line |
|
select concat( '[{ "username": "', curr_username, '", "playlist_name": "', defaultPlayListName, '", "playlist_item": [ { "name": "', curr_folder, '","container_string": "', container_string, '", "comment": "', fcomment, '", "bookmark": ['); |
|
set firstBookmark = 1; |
|
call writeBookmark( bname, start_time, firstBookmark); |
|
set firstBookmark = 0; |
|
else -- this is the first line for another user. Need to end the json for the prior user. |
|
select ']}]},'; |
|
-- now start the json for the new user |
|
select concat( '{ "username": "', curr_username, '", "playlist_name": "', defaultPlayListName, '", "playlist_item": [ { "name": "', curr_folder, '","container_string": "', container_string, '", "comment": "', fcomment, '", "bookmark": ['); |
|
set firstBookmark = 1; |
|
call writeBookmark( bname, start_time, firstBookmark); |
|
set firstBookmark = 0; |
|
end if; |
|
else -- another line for the same user |
|
if prev_folder <> curr_folder then |
|
-- end playlist item and start the next one |
|
-- We found a case where, within a folder, a user jumped from one media object to another and back. |
|
-- Our code to this point would create multiple playlist items with the same name. The easiest place to disambiguate these is here |
|
set good_folder = curr_folder; |
|
call checkFolderName( curr_id, curr_username, good_folder, bname ); |
|
select concat( ']}, { "name": "', good_folder, '","container_string": "', container_string, '", "comment": "', fcomment, '", "bookmark": ['); |
|
set firstBookmark = 1; |
|
end if; |
|
call writeBookmark( bname, start_time, firstBookmark); |
|
set firstBookmark = 0; |
|
end if; |
|
set prev_username = curr_username; |
|
set prev_folder = curr_folder; |
|
end if; |
|
end while; |
|
-- finish up the last user |
|
select ']}]}]'; |
|
END | |
|
|
|
DELIMITER ; |
|
|
|
drop procedure if exists checkFolderName; |
|
DELIMITER | |
|
|
|
CREATE PROCEDURE checkFolderName( curr_id int unsigned, curr_username varchar(255), inout curr_folder varchar(255), curr_bname varchar(255) ) |
|
BEGIN |
|
|
|
declare cnt int unsigned; |
|
declare done int unsigned default 0; |
|
declare turns int unsigned default 1; |
|
declare tmpFolderName varchar(255); |
|
|
|
select count(*) into cnt |
|
from staging_bookmarks |
|
where username = curr_username |
|
and foldername = curr_folder |
|
and id < curr_id; |
|
|
|
set tmpFolderName = curr_folder; |
|
|
|
while done = 0 do |
|
if cnt = 0 then |
|
-- rename the item to reflect what goes into the file and so that |
|
-- it is there next time we need to compare the same string. |
|
update staging_bookmarks |
|
set foldername = tmpFolderName |
|
where username = curr_username |
|
and foldername = curr_folder |
|
and id = curr_id; |
|
|
|
set curr_folder = tmpFolderName; |
|
set done = 1; |
|
else |
|
if turns < 1000 then |
|
set turns = turns+1; |
|
set tmpFolderName = concat( curr_folder, ' - #', turns ); |
|
select count(*) into cnt |
|
from staging_bookmarks |
|
where username = curr_username |
|
and foldername = tmpFolderName |
|
and id < curr_id; |
|
else |
|
-- in this case we fail badly |
|
-- the json will contain this line and not be well formed. |
|
select concat( 'Could not disambiguate ', curr_folder, ' for user ', curr_username ); |
|
end if; |
|
end if; |
|
end while; |
|
END | |
|
|
|
DELIMITER ; |
|
|
|
drop procedure if exists writeBookmark; |
|
DELIMITER | |
|
|
|
CREATE PROCEDURE writeBookmark( bname varchar(255), start_time INT UNSIGNED, firstBookmark int UNSIGNED ) |
|
BEGIN |
|
if firstBookmark = 0 then |
|
select ','; |
|
end if; |
|
select concat( '{ "name": "', bname, '", "start_time": ', start_time, '}' ); |
|
END | |
|
|
|
DELIMITER ; |
|
|
I needed to add this line to the top of runEverything.sql so that results didn't come back as latin1:
set character_set_results = 'utf8';