Skip to content

Instantly share code, notes, and snippets.

@mcwhitaker
Last active August 12, 2016 20:53
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save mcwhitaker/03cab8a63bf80d431fa915b235522814 to your computer and use it in GitHub Desktop.
Save mcwhitaker/03cab8a63bf80d431fa915b235522814 to your computer and use it in GitHub Desktop.

Exporting Variations Bookmarks to Avalon

Here is MySQL code to export Variations Bookmarks to be imported into Avalon. The output of this code is a json file with the Variations bookmark information. This json file is structured to easily map to the playlist and marker structure in Avalon:

[ { "username": "username",
    "playlist_ name": "default",
    "playlist_item": [ { "name": "value",  "container_string": "value", "comment": "value"
                       "bookmark": [ { "name": "value", "start_time": value } ] 
                       } 
                     ]  
  }  
]

Important Notes

  • The code was tested using MySql server version 5.1.73.
  • There are 3 .sql files as part of this export tool:
    • bookmark.sql - creates the staging tables and the stored procedures needed by the export.
    • runEverything.sql - calls the procedures in the correct order to execute the export. You should ensure your access to the output directory.
    • dropEverything.sql - drops all the database items created by bookmark.sql. Use it if you would like to cleanup after the export.
  • Recursion is used to navigate the Bookmark trees and the container structures. File runEverything.sql sets the MySql max_sp_recursion_depth parameter to 10. Ensure this is set to the recursion depth needed by your data.
  • The tables and procedures declared in bookmarks.sql assume the Variations bookmark structure data can be found in the same database where they exist. If this is not true in your case, the references to tables in the code need to have the database prefix added.
  • The Variations table for container information is read from this code. Look for IU_METADATA and replace that with the name of the database where that table is to be found in your installation.
  • In procedure allBookmarks, cursor users_c has logic specific to IU to limit the result set to current Variations users via the where clause. Change it to your specific needs.
  • At IU, the mediaRefLabel attribute of a Variations container item chunk can be used to map that media reference to the corresponding MediaObject in Avalon. Thus, we include this label in the json output. If this is not true for your implementation, you will need to address this disconnect.
  • To run this code use (add password to the parms if required):

    mysql -sN -u username database < runEverything.sql > output.json

Implementation Notes

Only bookmarks of type = "player" can be imported into Avalon, so Variations bookmarks of other types are ignored.

Variations allows the user to create bookmarks associated with a playlist item and bookmarks independent of playlists. This tool deals with the independent bookmarks. Those associated with playlists can be imported via an import tool available in Avalon v 5.1.

Variations Bookmark offsets are relative to the beginning of a Container. Each container may include multiple items and each item refers to a media file. In Avalon, offsets are relative to the beginning of a media file. Thus, the code deals with converting offsets.

Whereas in Variations bookmarks are saved in a tree of folders and subfolders, in Avalon bookmarks only exist in the context of a playlist item. As a consequence, the tree is flattened. In Avalon a playlist item to hold relevant Variations bookmarks will be named after the folder where the Variations bookmarks reside. When a Variations folder holds bookmarks for multiple media files, the names of the resulting playlist items (one per media file) need to be differentiated. Procedure groupBookmarks deals with this issue.

We use two staging tables to hold the information saved in XML strings in the database. The bookmark information goes to staging_bookmarks, and the first step is to read the bookmarks into this table. The Container information goes to staging_items, which is populated only for containers that are referenced by bookmarks to be converted. Once staging_items is populated, the conversion of data occurs and the staging_bookmarks columns avalon_offset and label get populated.

The code respects the Variations order of the bookmarks. Given limitations in the native MySql functions to extract values from XML, this order is occasionally not followed. More specifically, in the bookmark tree, whenever divs and chunks are at the same level, the chunks are processed first followed by the divs. Other than this situation, the order of the bookmarks in the json output reflect the order in Variations. Given that this occurs sparsely in the IU data, it was not deemed worth it to write code to resolve this minor inconvenience. Users can always reorganize their bookmarks in Avalon.

-- 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 ;
drop table if exists staging_bookmarks ;
drop table if exists staging_items;
drop procedure if exists processChunks;
drop procedure if exists processDivs;
drop procedure if exists updateItems;
drop procedure if exists groupBookmarks;
drop procedure if exists saveBookmarks;
drop procedure if exists saveFolders;
drop procedure if exists allBookmarks;
drop procedure if exists generateJson;
drop procedure if exists checkFolderName;
drop procedure if exists writeBookmark;
-- ensure you allow the recursion depth needed by your data. This affects both the bookmark XML and the Container XML.
set max_sp_recursion_depth = 10;
-- per BKeese's comment, set the character set
set character_set_results = 'utf8';
call allBookmarks();
call groupBookmarks();
call generateJson();
@bkeese
Copy link

bkeese commented Jul 27, 2016

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';

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment