-- ij tip: some of the fortunes get truncated. The max width of the fortunes
-- loaded (so far) is 2145:
--     
--     ij> select max(length(fortune)) from fortunes;
--     1
--     -----------
--     2145
--     
--     1 row selected
--
-- You can set the ij display width to a larger value; for example,
--
--     java -Dij.maximumDisplayWidth=1000 \
--         -Dderby.system.home=DERBY_SYSTEM_HOME org.apache.derby.tools.ij
--

connect 'jdbc:derby:fortunes';

values tutRand(1, 500);
values tutRand(1, (select max(id) from fortunes));
values tutRand( (select min(id) from fortunes where src='pets'),
           (select max(id) from fortunes where src='pets') );

-- If the fortune is offensive, we'll get '0 rows selected':
   
select id, fortune 
from   fortunes 
where  id=tutRand((select min(id) from fortunes where src='pets'),
          (select max(id) from fortunes where src='pets'))
and    offense = 0;


-- Find all fortunes that mention golf
select id, src, fortune 
from   fortunes 
where  tutMatch(fortune, '[Gg][Oo][Ll][Ff]')=1;

-- replace golf with 'G===='
select id, src, tutReplace(fortune, '[Gg][Oo][Ll][Ff]', 'G====')
from   fortunes 
where  tutMatch(fortune, '[Gg][Oo][Ll][Ff]')=1;

-- Add web boldness to the matched expression 
select id, src, tutReplace(fortune, '[Gg][Oo][Ll][Ff]', '  <b>$0</b>')
from   fortunes 
where  tutMatch(fortune, '[Gg][Oo][Ll][Ff]')=1;
