1.t #1

=comment

Copyright (c) 2019, Perforce Software, Inc.  All rights reserved.

Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions are met:

1.  Redistributions of source code must retain the above copyright
    notice, this list of conditions and the following disclaimer.

2.  Redistributions in binary form must reproduce the above copyright
    notice, this list of conditions and the following disclaimer in the
    documentation and/or other materials provided with the distribution.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
ARE DISCLAIMED. IN NO EVENT SHALL PERFORCE SOFTWARE, INC. BE LIABLE FOR ANY
DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
(INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
(INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF
THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

User contributed content on the Perforce Public Depot is not supported
by Perforce, although it may be supported by its author. This applies
to all contributions even those submitted by Perforce employees.

=cut

use P4::test { PREREQS => { PROGS => 'bindeltXtrig.pl,bsdiff,bspatch,sqlite3' } };
use P4::TM { DATA_DIR => 'self' };
use P4::util 'which';
use File::Copy;
use Cwd;
use IPC::Run3;

=comment

test locking

run-from-depot, normal
oblit, delete, retype, out of order oblit,
todo: handle obliterate,delete,retype

help output, logs

no delta test, 100% diff test

test utility program prereqs

db locked test

test file name escaping

unsubmit, resubmit, dvcs stuff

different -ndelta values

todo: can retype cause #head==$rev? +X->normal->+X?

=cut


P4::TM::add_mask_func sub {
  s/\d{10}/TSMASK/g;
  s/line \d+/line NNN/g;
  s/(ARRAY|CODE|HASH)\(.*\)/$1(...)/g;
  s, at /.*/([^ ]+) (line.*), at PATHMASK/$1 $2,g;
  s/"PID"\d+"/"PID"PIDMASK"/g;
  s/\.pl\@\d+  MD5:  [A-F0-9]{32}/.pl\@CLMASK  MD5:  MD5MASK/g;
  $_
};

copy which( 'bindeltXtrig.pl' ), '.';
copy 'bindeltXtrig.pl', $server->{ serverRoot };

{
  my $perl = which 'perl';
  # No sqlite3
  local $ENV{ PATH } = undef;
  tm `"$perl" ./bindeltXtrig.pl -v`;
}

`p4 configure set serverlog.file.1=triggers.csv`;
`p4 configure set serverlog.file.2=errors.csv`;

$stdin = `perl bindeltXtrig.pl --help=trigger`;
tm `p4 triggers -i`;

# Permute the file
sub build {
  state $n;
  state $dat = '0' x 100_000;
  $dat .= $n++
}

my $dbb = Cwd::abs_path "$server->{serverRoot}/bindeltXtrig";
my $dbp = "$dbb.pl";
my $dbf = "$dbb.db";

################################################################################

tm `perl "$dbp" --init`;
tm `sqlite3 bindeltXtrig.db .dump`;

my ( $si, $so, $se );

$si = << 'EOSQL';
PRAGMA foreign_keys = ON;
.separator ' | '
.header on
.echo on
-- .eqp on

SELECT rev FROM revs WHERE rev <= 1 AND rev >= ( SELECT rev FROM revs WHERE lbr = '//depot/foo' AND rev <= 1 AND rev = src ORDER BY rev DESC LIMIT 1 ) ORDER BY rev ASC;

BEGIN TRANSACTION;

--INSERT INTO revs ( lbr, rev, src ) values ( "foo", 1, 1 );
INSERT INTO revs ( lbr, rev, src ) values ( "//depot/foo", 1, 1 );
SELECT last_insert_rowid();
INSERT INTO data ( id, data ) values ( 10, "bar" );
INSERT INTO data ( id, data ) values ( last_insert_rowid(), "rev1" );
INSERT INTO data ( id, data ) values ( last_insert_rowid(), "rev1" );
SELECT * FROM data;

INSERT INTO data ( id, data ) values ( last_insert_rowid(), "rev1" );
INSERT INTO revs ( lbr, rev, src ) values ( "//depot/foo", 1, 1 );
INSERT INTO revs ( lbr, rev, src ) values ( "//depot/foo", 2, 1 );
SELECT last_insert_rowid();
INSERT INTO data ( data ) values ( "rev2" );
INSERT INTO data ( data ) values ( "rev2" );
INSERT INTO revs ( lbr, rev, src ) values ( "//depot/foo", 4, 1 );
INSERT INTO data ( data ) values ( "rev4" );

SELECT * FROM data;

--DELETE FROM revs WHERE id = 2;

COMMIT;

INSERT INTO revs ( lbr, rev, src ) VALUES ( "//depot/'foo'", 100, 100 );
INSERT INTO revs ( lbr, rev, src ) VALUES ( '//depot/"foo"', 101, 101 );
INSERT INTO revs ( lbr, rev, src ) VALUES ( '//depot/,foo,', 102, 102 );

SELECT * FROM revs;
SELECT * FROM data;

SELECT rev FROM revs;

INSERT INTO revs ( lbr, rev, src ) VALUES ( "//depot/foo", 5, 4 );
INSERT INTO data ( id, data ) VALUES ( last_insert_rowid(), "rev5" );
INSERT INTO revs ( lbr, rev, src ) VALUES ( "//depot/foo", 6, 5 );
INSERT INTO data ( id, data ) VALUES ( last_insert_rowid(), "rev6" );
INSERT INTO revs ( lbr, rev, src ) VALUES ( "//depot/foo", 7, 6 );
INSERT INTO data ( id, data ) VALUES ( last_insert_rowid(), "rev7" );
INSERT INTO revs ( lbr, rev, src ) VALUES ( "//depot/foo", 8, 8 );
INSERT INTO data ( id, data ) VALUES ( last_insert_rowid(), "rev8" );

INSERT INTO revs ( lbr, rev, src ) VALUES ( "//depot/foo", 9, 8 );
INSERT INTO data ( id, data ) VALUES ( last_insert_rowid(), "rev9" );
INSERT INTO revs ( lbr, rev, src ) VALUES ( "//depot/foo", 10, 9 );
INSERT INTO data ( id, data ) VALUES ( last_insert_rowid(), "rev10" );
INSERT INTO revs ( lbr, rev, src ) VALUES ( "//depot/foo", 11, 10 );
INSERT INTO data ( id, data ) VALUES ( last_insert_rowid(), "rev11" );

INSERT INTO revs ( lbr, rev, src ) VALUES ( "//depot/foo", 12, 12 );
INSERT INTO data ( id, data ) VALUES ( last_insert_rowid(), "rev12" );
INSERT INTO revs ( lbr, rev, src ) VALUES ( "//depot/foo", 13, 12 );
INSERT INTO data ( id, data ) VALUES ( last_insert_rowid(), "rev13" );
INSERT INTO revs ( lbr, rev, src ) VALUES ( "//depot/foo", 14, 13 );
INSERT INTO data ( id, data ) VALUES ( last_insert_rowid(), "rev14" );

SELECT rev FROM revs WHERE rev <= 1 AND rev >= ( SELECT rev FROM revs WHERE lbr = '//depot/foo' AND rev <= 1 AND rev = src ORDER BY rev DESC LIMIT 1 ) ORDER BY rev ASC;

SELECT rev FROM revs WHERE rev <= 8 AND rev >= ( SELECT rev FROM revs WHERE lbr = '//depot/foo' AND rev <= 8 AND rev = src ORDER BY rev DESC LIMIT 1 ) ORDER BY rev ASC;

SELECT rev FROM revs WHERE rev <= 11 AND rev >= ( SELECT rev FROM revs WHERE lbr = '//depot/foo' AND rev <= 11 AND rev = src ORDER BY rev DESC LIMIT 1 ) ORDER BY rev ASC;

-- SELECT rev FROM revs WHERE lbr = ${\quote $lbr} AND rev = src ORDER BY rev ASC;

-- update_data trigger
BEGIN TRANSACTION;
INSERT INTO revs ( lbr, rev, src ) VALUES ( 'update_data_test', 1, 1 );
INSERT INTO data ( id, data ) VALUES ( last_insert_rowid(), 'thisisjustright' );
UPDATE data SET data = 'abc' WHERE id = ( SELECT id FROM revs WHERE lbr = 'update_data_test' );
COMMIT;

-- delete_rev_delta trigger
BEGIN TRANSACTION;
INSERT INTO revs ( lbr, rev, src ) VALUES ( 'update_data_test', 1, 1 );
INSERT INTO revs ( lbr, rev, src ) VALUES ( 'update_data_test', 2, 1 );
DELETE FROM revs WHERE lbr = 'update_data_test' AND rev = 1;
COMMIT;

-- delete_data trigger
BEGIN TRANSACTION;
INSERT INTO revs ( lbr, rev, src ) VALUES ( 'delete_rev_test', 1, 1 );
INSERT INTO data ( id, data ) VALUES ( last_insert_rowid(), 'the data' );
DELETE FROM data WHERE id = ( SELECT id FROM revs WHERE lbr = 'delete_rev_test' );
COMMIT;

-- update_rev trigger 1
BEGIN TRANSACTION;
INSERT INTO revs ( lbr, rev, src ) VALUES ( 'update_rev_test1', 1, 1 );
INSERT INTO revs ( lbr, rev, src ) VALUES ( 'update_rev_test1', 3, 1 );
UPDATE revs SET src = 2 WHERE lbr = 'update_rev_test1' AND rev = 3;
COMMIT;
SELECT lbr, rev, src FROM revs WHERE lbr = 'update_rev_test1';

-- update_rev trigger 2
BEGIN TRANSACTION;
INSERT INTO revs ( lbr, rev, src ) VALUES ( 'update_rev_test2', 1, 1 );
UPDATE revs SET src = 1 WHERE lbr = 'update_rev_test2' AND rev = 1;
COMMIT;

-- update_rev trigger 3
BEGIN TRANSACTION;
INSERT INTO revs ( lbr, rev, src ) VALUES ( 'update_rev_test3', 1, 1 );
UPDATE revs SET lbr = 'update_rev_test3-a' WHERE lbr = 'update_rev_test3' AND rev = 1;
--SELECT changes() FROM revs;
COMMIT;
SELECT lbr, rev, src FROM revs WHERE lbr LIKE 'update_rev_test3%';

EOSQL
;

run3 [ qw / sqlite3 bindeltXtrig.db -batch / ], \ $si, \ $so, \ $so;
$so //= '';
$se //= '';
tm $so;
tm $se;

sub get_revs {
# todo: test spaces in path etc
( $si, $so, $se ) = ( '' x 3 );
run3 [ 'perl', $_[ 0 ], "--db=$_[1]", '--eval=my $rs = run_sql "SELECT * FROM revs;"; die "errors:  $rs->{ error }" if $rs->{ error }; say uDumper [ parse_csv $rs->{ results } ]' ], \ $si, \ $so, \ $so;
$so //= '';
$se //= '';
 "$so$se"
}

sub get_full_revs {
# todo: test spaces in path etc
( $si, $so, $se ) = ( '' x 3 );
run3 [ 'perl', $_[ 0 ], "--db=$_[1]", '--eval=my $rs = run_sql "SELECT rev FROM revs WHERE rev = src;"; die "errors:  $rs->{ error }" if $rs->{ error }; say uDumper [ parse_csv $rs->{ results } ]' ], \ $si, \ $so, \ $so;
$so //= '';
$se //= '';
 "$so$se"
}

tm get_revs 'bindeltXtrig.pl', 'bindeltXtrig.db'; # parse test

tm `perl bindeltXtrig.pl --eval=say\\\"foo\\\"`; # output test
tm `perl bindeltXtrig.pl --eval=die\\\"foo\\\"`; # error test

( $si, $so, $se ) = ( '' x 3 );
run3 [ 'perl', 'bindeltXtrig.pl', '--db=bindeltXtrig.db', '--eval=say uDumper get_prev_rev "//depot/foo", 11' ], \ $si, \ $so, \ $se;
$so //= '';
$se //= '';
tm "$so$se";

#unlink glob 'bindeltXtrig*';

################################################################################


$stdin = `perl bindeltXtrig.pl --help=trigger` =~ s/--op/--flop/gr;
#diag $stdin;
tm `p4 triggers -i`;

write_file 'file', build;
#tm `ls -lahs file`;
`p4 client -o | p4 client -i`;
tm `p4 add -t binary+X file`;
tm `p4 submit -d rev1-add`;
$stdin = `perl bindeltXtrig.pl --help=trigger`;
tm `p4 triggers -i`;
tm `p4 add -t binary+X -c 1 missing`;
tm `p4 submit -c 1`;
( $si, $so, $se ) = ( '' x 3 );
run3 [ 'perl', $_[ 0 ], "--db=$dbf", '--eval=my $rs = run_sql "SELECT lbr, rev, src FROM revs; SELECT id FROM data;"; die "errors:  $rs->{ error }" if $rs->{ error }; say uDumper $rs->{ results }' ], \ $si, \ $so, \ $so;
$so //= '';
$se //= '';
tm "$so$se";
`p4 revert missing`;
chmod '+w', 'file';
write_file 'file', build; # Client files can change between submits.
chmod '-w', 'file';
tm `p4 submit -c 1`;
tm `p4 verify -q //...`;
#diag `p4 triggers -o`;
#exit;
#tm `p4 triggers -o`;
#`p4 configure set lbr.verify.out=0`;
tm `p4 print -o file.1 file`;
#tm `md5sum file.1`;
#tm `sqlite3 "$dbf" .dump`;

#$stdin = `p4 triggers -o` =~ s///gr;
#tm `p4 triggers -i`;
`p4 edit file`;
write_file 'file', build;
tm `p4 submit -d rev2-edit`;
tm `p4 print -o file.2 file`;
#tm `md5sum file.2`;
#tm `cp file file.2src`;
#tm `diff file file.2`;
tm `p4 verify -q //...`;
tm get_revs $dbp, $dbf; # parse test

`p4 edit file`;
write_file 'file', build;
tm `p4 submit -d rev3-edit`;
tm `p4 print -o file.1a file#1`;
tm `p4 print -o file.2a file#2`;
tm `p4 print -o file.3 file`;
#tm `diff file.2src file.2a`;
#tm `md5sum file.2a`;
#tm `md5sum file.3`;
tm `p4 verify -q //...`;
tm get_revs $dbp, $dbf; # parse test

`p4 edit file`;
write_file 'file', build;
tm `p4 submit -d rev3-edit`;
tm `p4 verify -q //...`;
tm get_revs $dbp, $dbf; # parse test

`p4 edit file`,
  write_file( 'file', build ),
    tm `p4 submit -d rev$_-edit` for 4 .. 40;

#tm `sqlite3 "$dbf" .dump`;

tm `p4 verify -q //...`;

tm get_full_revs $dbp, $dbf; # pre-oblit rev overview
# write full revs: [[1],[12],[23],[34],[40],[41]]
tm `p4 retype -l -t text file#12`;
tm `p4 verify -q //...`;
tm `p4 retype -l -t text file#13`;
tm `p4 verify -q //...`;
tm `p4 retype -l -t text file#11`;
tm `p4 verify -q //...`;
tm `p4 obliterate -y file#22`;
tm `p4 verify -q //...`;
tm `p4 obliterate -y file#21`;
tm `p4 verify -q //...`;
tm `p4 obliterate -y file#23`;
tm `p4 verify -q //...`;
tm `p4 obliterate -y file#24`;
tm `p4 verify -q //...`;
tm get_revs $dbp, $dbf; # parse test x

tm `p4 obliterate -y file#1`;
tm `p4 verify -q //...`;

tm `p4 delete file`;
tm `p4 submit -d delete`;
tm `p4 verify -q //...`;

tm get_revs $dbp, $dbf; # last rev overview
tm get_full_revs $dbp, $dbf; # last full rev overview

# todo: read log file
#tm read_file "bindeltXtrig.log";
#tm read_file "$server->{serverRoot}/triggers.csv";
#tm read_file "$server->{serverRoot}/errors.csv";
#tm read_file "$server->{serverRoot}/track.log";
#tm read_file "$server->{serverRoot}/bindeltXtrig.log";
#tm read_file "$server->{serverRoot}/bindeltXtrig.jnl";
#tm glob "$dbb*";

__DATA__
[[52,59,74,75,202,203,223,225,226,232,241,246,247,249,250,251,256,261,262,267,275,276,280,281,285,286,287,288,292,293,297,298,299,303,'303.1','303.2','303.3','303.4','303.5','303.6','303.7','303.8','303.9','303.10','303.11','303.12','303.13','303.14','303.15','303.16','303.17','303.18','303.19','303.20','303.21','303.22','303.23','303.24','303.25','303.26','303.27','303.28','303.29','303.30','303.31','303.32','303.33','303.34','303.35','303.36',307,309,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,327,328,330,331,332,334,335],
[
  '"" ./bindeltXtrig.pl -v',
  '

trigger_bindeltXtrig //depot/dev/jgibson/utils/trig-bindeltX/bindeltXtrig.pl@CLMASK  MD5:  MD5MASK

Exiting unexpectedly: 
./bindeltXtrig.pl line NNN: bsdiff/bspatch missing


 at ./bindeltXtrig.pl line NNN, <GEN4> line NNN.
	main::ERR("bsdiff/bspatch missing") called at ./bindeltXtrig.pl line NNN

',
  1,
  'p4 triggers -i',
  'Triggers saved.
',
  0,
  'perl "" --init',
  '',
  1,
  'sqlite3 bindeltXtrig.db .dump',
  '
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE revs ( id INTEGER PRIMARY KEY NOT NULL,
				  lbr TEXT NOT NULL,
				  rev INTEGER NOT NULL CHECK ( rev > 0 ),
				  src INTEGER NOT NULL CHECK ( src > 0 ),
				  UNIQUE( lbr, rev ) );
CREATE TABLE data ( id INTEGER PRIMARY KEY NOT NULL
                                  REFERENCES revs ( id ) ON DELETE CASCADE,
				  data BLOB NOT NULL,
				  UNIQUE ( id, data ) );
CREATE INDEX rr_revs on revs ( rev desc, rev asc );
CREATE INDEX dr_revs on revs ( lbr, rev desc );
CREATE TRIGGER update_data BEFORE UPDATE ON data
  BEGIN
    SELECT CASE WHEN
      ( SELECT id FROM revs WHERE id = NEW.id AND src = rev ) AND
      length( NEW.data ) < length( OLD.data )
      AND ( SELECT id FROM revs WHERE id = OLD.id AND rev = src )
    THEN
      RAISE( ROLLBACK, \'update_data:  shrinking full rev!\' )
    END;
  END;
CREATE TRIGGER delete_rev_delta BEFORE DELETE ON revs
  BEGIN
    SELECT CASE WHEN
      ( SELECT id FROM revs WHERE OLD.lbr = lbr AND rev <> src AND
      rev = ( SELECT rev FROM revs WHERE rev > OLD.rev AND OLD.lbr = lbr
              ORDER BY REV ASC LIMIT 1 ) )
    THEN
      RAISE( ROLLBACK, \'delete_rev1:  deleting `rev` where `rev`+1 is a delta!\' )
    END;
  END;
CREATE TRIGGER delete_data BEFORE DELETE ON data
  BEGIN
    SELECT CASE WHEN
      ( SELECT id FROM revs WHERE id = OLD.id )
    THEN
      RAISE( ROLLBACK, \'delete_data:  delete from data with existing rev!\' )
    END;
  END;
CREATE TRIGGER update_rev BEFORE UPDATE ON revs
  BEGIN
    SELECT CASE WHEN
      NEW.rev <> NEW.src AND
          NOT ( SELECT count( id ) FROM revs WHERE lbr = OLD.lbr AND rev = NEW.src )
    THEN
      RAISE( ROLLBACK, \'update_rev:  new `src` revision does not exist!\' )
    END;
    SELECT CASE WHEN
      NEW.rev = NEW.src AND OLD.rev = OLD.src
    THEN
      RAISE( ROLLBACK, \'update_rev:  old/new `src` same?!\' )
    END;
    SELECT CASE WHEN
      ( select OLD.lbr <> NEW.lbr )
    THEN
      RAISE( ROLLBACK, \'update_rev:  tried to change the `lbr`!\' )
    END;
  END;
COMMIT;
',
  0,
  '$so',
  '
-- .eqp on

SELECT rev FROM revs WHERE rev <= 1 AND rev >= ( SELECT rev FROM revs WHERE lbr = \'//depot/foo\' AND rev <= 1 AND rev = src ORDER BY rev DESC LIMIT 1 ) ORDER BY rev ASC;

BEGIN TRANSACTION;

--INSERT INTO revs ( lbr, rev, src ) values ( "foo", 1, 1 );
INSERT INTO revs ( lbr, rev, src ) values ( "//depot/foo", 1, 1 );
SELECT last_insert_rowid();
last_insert_rowid()
1
Error: near line NNN: FOREIGN KEY constraint failed
INSERT INTO data ( id, data ) values ( 10, "bar" );
INSERT INTO data ( id, data ) values ( last_insert_rowid(), "rev1" );
Error: near line NNN: UNIQUE constraint failed: data.id
INSERT INTO data ( id, data ) values ( last_insert_rowid(), "rev1" );
SELECT * FROM data;
id | data
1 | rev1

Error: near line NNN: UNIQUE constraint failed: data.id
INSERT INTO data ( id, data ) values ( last_insert_rowid(), "rev1" );
Error: near line NNN: UNIQUE constraint failed: revs.lbr, revs.rev
INSERT INTO revs ( lbr, rev, src ) values ( "//depot/foo", 1, 1 );
INSERT INTO revs ( lbr, rev, src ) values ( "//depot/foo", 2, 1 );
SELECT last_insert_rowid();
last_insert_rowid()
2
INSERT INTO data ( data ) values ( "rev2" );
Error: near line NNN: FOREIGN KEY constraint failed
INSERT INTO data ( data ) values ( "rev2" );
INSERT INTO revs ( lbr, rev, src ) values ( "//depot/foo", 4, 1 );
INSERT INTO data ( data ) values ( "rev4" );

SELECT * FROM data;
id | data
1 | rev1
2 | rev2
3 | rev4

--DELETE FROM revs WHERE id = 2;

COMMIT;

INSERT INTO revs ( lbr, rev, src ) VALUES ( "//depot/\'foo\'", 100, 100 );
INSERT INTO revs ( lbr, rev, src ) VALUES ( \'//depot/"foo"\', 101, 101 );
INSERT INTO revs ( lbr, rev, src ) VALUES ( \'//depot/,foo,\', 102, 102 );

SELECT * FROM revs;
id | lbr | rev | src
1 | //depot/foo | 1 | 1
2 | //depot/foo | 2 | 1
3 | //depot/foo | 4 | 1
4 | //depot/\'foo\' | 100 | 100
5 | //depot/"foo" | 101 | 101
6 | //depot/,foo, | 102 | 102
SELECT * FROM data;
id | data
1 | rev1
2 | rev2
3 | rev4

SELECT rev FROM revs;
rev
102
101
100
4
2
1

INSERT INTO revs ( lbr, rev, src ) VALUES ( "//depot/foo", 5, 4 );
INSERT INTO data ( id, data ) VALUES ( last_insert_rowid(), "rev5" );
INSERT INTO revs ( lbr, rev, src ) VALUES ( "//depot/foo", 6, 5 );
INSERT INTO data ( id, data ) VALUES ( last_insert_rowid(), "rev6" );
INSERT INTO revs ( lbr, rev, src ) VALUES ( "//depot/foo", 7, 6 );
INSERT INTO data ( id, data ) VALUES ( last_insert_rowid(), "rev7" );
INSERT INTO revs ( lbr, rev, src ) VALUES ( "//depot/foo", 8, 8 );
INSERT INTO data ( id, data ) VALUES ( last_insert_rowid(), "rev8" );

INSERT INTO revs ( lbr, rev, src ) VALUES ( "//depot/foo", 9, 8 );
INSERT INTO data ( id, data ) VALUES ( last_insert_rowid(), "rev9" );
INSERT INTO revs ( lbr, rev, src ) VALUES ( "//depot/foo", 10, 9 );
INSERT INTO data ( id, data ) VALUES ( last_insert_rowid(), "rev10" );
INSERT INTO revs ( lbr, rev, src ) VALUES ( "//depot/foo", 11, 10 );
INSERT INTO data ( id, data ) VALUES ( last_insert_rowid(), "rev11" );

INSERT INTO revs ( lbr, rev, src ) VALUES ( "//depot/foo", 12, 12 );
INSERT INTO data ( id, data ) VALUES ( last_insert_rowid(), "rev12" );
INSERT INTO revs ( lbr, rev, src ) VALUES ( "//depot/foo", 13, 12 );
INSERT INTO data ( id, data ) VALUES ( last_insert_rowid(), "rev13" );
INSERT INTO revs ( lbr, rev, src ) VALUES ( "//depot/foo", 14, 13 );
INSERT INTO data ( id, data ) VALUES ( last_insert_rowid(), "rev14" );

SELECT rev FROM revs WHERE rev <= 1 AND rev >= ( SELECT rev FROM revs WHERE lbr = \'//depot/foo\' AND rev <= 1 AND rev = src ORDER BY rev DESC LIMIT 1 ) ORDER BY rev ASC;
rev
1

SELECT rev FROM revs WHERE rev <= 8 AND rev >= ( SELECT rev FROM revs WHERE lbr = \'//depot/foo\' AND rev <= 8 AND rev = src ORDER BY rev DESC LIMIT 1 ) ORDER BY rev ASC;
rev
8

SELECT rev FROM revs WHERE rev <= 11 AND rev >= ( SELECT rev FROM revs WHERE lbr = \'//depot/foo\' AND rev <= 11 AND rev = src ORDER BY rev DESC LIMIT 1 ) ORDER BY rev ASC;
rev
8
9
10
11

-- SELECT rev FROM revs WHERE lbr = ${\\quote $lbr} AND rev = src ORDER BY rev ASC;

-- update_data trigger
BEGIN TRANSACTION;
INSERT INTO revs ( lbr, rev, src ) VALUES ( \'update_data_test\', 1, 1 );
INSERT INTO data ( id, data ) VALUES ( last_insert_rowid(), \'thisisjustright\' );
Error: near line NNN: update_data:  shrinking full rev!
UPDATE data SET data = \'abc\' WHERE id = ( SELECT id FROM revs WHERE lbr = \'update_data_test\' );
Error: near line NNN: cannot commit - no transaction is active
COMMIT;

-- delete_rev_delta trigger
BEGIN TRANSACTION;
INSERT INTO revs ( lbr, rev, src ) VALUES ( \'update_data_test\', 1, 1 );
INSERT INTO revs ( lbr, rev, src ) VALUES ( \'update_data_test\', 2, 1 );
Error: near line NNN: delete_rev1:  deleting `rev` where `rev`+1 is a delta!
DELETE FROM revs WHERE lbr = \'update_data_test\' AND rev = 1;
Error: near line NNN: cannot commit - no transaction is active
COMMIT;

-- delete_data trigger
BEGIN TRANSACTION;
INSERT INTO revs ( lbr, rev, src ) VALUES ( \'delete_rev_test\', 1, 1 );
INSERT INTO data ( id, data ) VALUES ( last_insert_rowid(), \'the data\' );
Error: near line NNN: delete_data:  delete from data with existing rev!
DELETE FROM data WHERE id = ( SELECT id FROM revs WHERE lbr = \'delete_rev_test\' );
Error: near line NNN: cannot commit - no transaction is active
COMMIT;

-- update_rev trigger 1
BEGIN TRANSACTION;
INSERT INTO revs ( lbr, rev, src ) VALUES ( \'update_rev_test1\', 1, 1 );
INSERT INTO revs ( lbr, rev, src ) VALUES ( \'update_rev_test1\', 3, 1 );
Error: near line NNN: update_rev:  new `src` revision does not exist!
UPDATE revs SET src = 2 WHERE lbr = \'update_rev_test1\' AND rev = 3;
Error: near line NNN: cannot commit - no transaction is active
COMMIT;
SELECT lbr, rev, src FROM revs WHERE lbr = \'update_rev_test1\';

-- update_rev trigger 2
BEGIN TRANSACTION;
INSERT INTO revs ( lbr, rev, src ) VALUES ( \'update_rev_test2\', 1, 1 );
Error: near line NNN: update_rev:  old/new `src` same?!
UPDATE revs SET src = 1 WHERE lbr = \'update_rev_test2\' AND rev = 1;
Error: near line NNN: cannot commit - no transaction is active
COMMIT;

-- update_rev trigger 3
BEGIN TRANSACTION;
INSERT INTO revs ( lbr, rev, src ) VALUES ( \'update_rev_test3\', 1, 1 );
Error: near line NNN: update_rev:  old/new `src` same?!
UPDATE revs SET lbr = \'update_rev_test3-a\' WHERE lbr = \'update_rev_test3\' AND rev = 1;
--SELECT changes() FROM revs;
Error: near line NNN: cannot commit - no transaction is active
COMMIT;
SELECT lbr, rev, src FROM revs WHERE lbr LIKE \'update_rev_test3%\';

',
  0,
  '^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^',
  '$se',
  '',
  0,
  'get_revs \'bindeltXtrig.pl\', \'bindeltXtrig.db\'; # parse test',
  '[[[1,"//depot/foo",1,1],[2,"//depot/foo",2,1],[3,"//depot/foo",4,1],[4,"//depot/\'foo\'",100,100],[5,"//depot/\\"foo\\"",101,101],[6,"//depot/,foo,",102,102],[7,"//depot/foo",5,4],[8,"//depot/foo",6,5],[9,"//depot/foo",7,6],[10,"//depot/foo",8,8],[11,"//depot/foo",9,8],[12,"//depot/foo",10,9],[13,"//depot/foo",11,10],[14,"//depot/foo",12,12],[15,"//depot/foo",13,12],[16,"//depot/foo",14,13]]]
',
  0,
  'perl bindeltXtrig.pl --eval=say\\\\"foo\\\\"`; # output test',
  'foo
',
  1,
  'perl bindeltXtrig.pl --eval=die\\\\"foo\\\\"`; # error test',
  '

trigger_bindeltXtrig //depot/dev/jgibson/utils/trig-bindeltX/bindeltXtrig.pl@CLMASK  MD5:  MD5MASK

Exiting unexpectedly: 
foo at (eval 10) line NNN.


 at (eval 10) line NNN, <GEN4> line NNN.
	eval \'die"foo"\' called at bindeltXtrig.pl line NNN

',
  1,
  '""',
  '10
',
  1,
  'p4 triggers -i',
  'Triggers saved.
',
  0,
  'p4 add -t binary+X file',
  '//depot/file#1 - opened for add
',
  0,
  'p4 submit -d rev1-add',
  '
Submitting change 1.
Locking 1 files ...
add //depot/file#1
Librarian checkin depot/file failed.
bin_delta write: 
bindeltXtrig //depot/dev/jgibson/utils/trig-bindeltX/bindeltXtrig.pl@CLMASK  MD5:  MD5MASK

Exiting unexpectedly: 
Unknown option: flop


 at PATHMASK/Long.pm line NNN, <GEN0> line NNN.
	Getopt::Long::FindOption(ARRAY(...) called at PATHMASK/Long.pm line NNN
	Getopt::Long::GetOptionsFromArray(undef, undef, undef, undef, undef, undef, undef, undef, undef, ...) called at bindeltXtrig.pl line NNN

Submit aborted -- fix problems then use \'p4 submit -c 1\'.
Some file(s) could not be transferred from client.
',
  1,
  'p4 triggers -i',
  'Triggers saved.
',
  0,
  'p4 add -t binary+X -c 1 missing',
  '//depot/missing#1 - opened for add
',
  0,
  'p4 submit -c 1',
  '
Submitting change 1.
Locking 1 files ...
add //depot/file#1
add //depot/missing#1
open for read: /*/missing: No such file or directory
Submit aborted -- fix problems then use \'p4 submit -c 1\'.
Some file(s) could not be transferred from client.
',
  1,
  '""',
  '',
  1,
  'p4 submit -c 1',
  '
Submitting change 1.
add //depot/file#1
Change 1 submitted.
',
  0,
  'p4 verify -q //...',
  '',
  0,
  'p4 print -o file.1 file',
  '//depot/file#1 - add change 1 (binary+X)
',
  0,
  'p4 submit -d rev2-edit',
  '
Submitting change 2.
Locking 1 files ...
edit //depot/file#2
Change 2 submitted.
',
  0,
  'p4 print -o file.2 file',
  '//depot/file#2 - edit change 2 (binary+X)
',
  0,
  'p4 verify -q //...',
  '',
  0,
  'get_revs , ; # parse test',
  '[[[1,"depot/file",1,1],[2,"depot/file",2,2]]]
',
  0,
  'p4 submit -d rev3-edit',
  '
Submitting change 3.
Locking 1 files ...
edit //depot/file#3
Change 3 submitted.
',
  0,
  'p4 print -o file.1a file#1',
  '//depot/file#1 - add change 1 (binary+X)
',
  0,
  'p4 print -o file.2a file#2',
  '//depot/file#2 - edit change 2 (binary+X)
',
  0,
  'p4 print -o file.3 file',
  '//depot/file#3 - edit change 3 (binary+X)
',
  0,
  'p4 verify -q //...',
  '',
  0,
  'get_revs , ; # parse test',
  '[[[1,"depot/file",1,1],[2,"depot/file",2,1],[3,"depot/file",3,3]]]
',
  0,
  'p4 submit -d rev3-edit',
  '
Submitting change 4.
Locking 1 files ...
edit //depot/file#4
Change 4 submitted.
',
  0,
  'p4 verify -q //...',
  '',
  0,
  'get_revs , ; # parse test',
  '[[[1,"depot/file",1,1],[2,"depot/file",2,1],[3,"depot/file",3,2],[4,"depot/file",4,4]]]
',
  0,
  'p4 submit -d rev4-edit` for 4 .. 40',
  '
Submitting change 5.
Locking 1 files ...
edit //depot/file#5
Change 5 submitted.
',
  0,
  'p4 submit -d rev5-edit` for 4 .. 40',
  '
Submitting change 6.
Locking 1 files ...
edit //depot/file#6
Change 6 submitted.
',
  0,
  'p4 submit -d rev6-edit` for 4 .. 40',
  '
Submitting change 7.
Locking 1 files ...
edit //depot/file#7
Change 7 submitted.
',
  0,
  'p4 submit -d rev7-edit` for 4 .. 40',
  '
Submitting change 8.
Locking 1 files ...
edit //depot/file#8
Change 8 submitted.
',
  0,
  'p4 submit -d rev8-edit` for 4 .. 40',
  '
Submitting change 9.
Locking 1 files ...
edit //depot/file#9
Change 9 submitted.
',
  0,
  'p4 submit -d rev9-edit` for 4 .. 40',
  '
Submitting change 10.
Locking 1 files ...
edit //depot/file#10
Change 10 submitted.
',
  0,
  'p4 submit -d rev10-edit` for 4 .. 40',
  '
Submitting change 11.
Locking 1 files ...
edit //depot/file#11
Change 11 submitted.
',
  0,
  'p4 submit -d rev11-edit` for 4 .. 40',
  '
Submitting change 12.
Locking 1 files ...
edit //depot/file#12
Change 12 submitted.
',
  0,
  'p4 submit -d rev12-edit` for 4 .. 40',
  '
Submitting change 13.
Locking 1 files ...
edit //depot/file#13
Change 13 submitted.
',
  0,
  'p4 submit -d rev13-edit` for 4 .. 40',
  '
Submitting change 14.
Locking 1 files ...
edit //depot/file#14
Change 14 submitted.
',
  0,
  'p4 submit -d rev14-edit` for 4 .. 40',
  '
Submitting change 15.
Locking 1 files ...
edit //depot/file#15
Change 15 submitted.
',
  0,
  'p4 submit -d rev15-edit` for 4 .. 40',
  '
Submitting change 16.
Locking 1 files ...
edit //depot/file#16
Change 16 submitted.
',
  0,
  'p4 submit -d rev16-edit` for 4 .. 40',
  '
Submitting change 17.
Locking 1 files ...
edit //depot/file#17
Change 17 submitted.
',
  0,
  'p4 submit -d rev17-edit` for 4 .. 40',
  '
Submitting change 18.
Locking 1 files ...
edit //depot/file#18
Change 18 submitted.
',
  0,
  'p4 submit -d rev18-edit` for 4 .. 40',
  '
Submitting change 19.
Locking 1 files ...
edit //depot/file#19
Change 19 submitted.
',
  0,
  'p4 submit -d rev19-edit` for 4 .. 40',
  '
Submitting change 20.
Locking 1 files ...
edit //depot/file#20
Change 20 submitted.
',
  0,
  'p4 submit -d rev20-edit` for 4 .. 40',
  '
Submitting change 21.
Locking 1 files ...
edit //depot/file#21
Change 21 submitted.
',
  0,
  'p4 submit -d rev21-edit` for 4 .. 40',
  '
Submitting change 22.
Locking 1 files ...
edit //depot/file#22
Change 22 submitted.
',
  0,
  'p4 submit -d rev22-edit` for 4 .. 40',
  '
Submitting change 23.
Locking 1 files ...
edit //depot/file#23
Change 23 submitted.
',
  0,
  'p4 submit -d rev23-edit` for 4 .. 40',
  '
Submitting change 24.
Locking 1 files ...
edit //depot/file#24
Change 24 submitted.
',
  0,
  'p4 submit -d rev24-edit` for 4 .. 40',
  '
Submitting change 25.
Locking 1 files ...
edit //depot/file#25
Change 25 submitted.
',
  0,
  'p4 submit -d rev25-edit` for 4 .. 40',
  '
Submitting change 26.
Locking 1 files ...
edit //depot/file#26
Change 26 submitted.
',
  0,
  'p4 submit -d rev26-edit` for 4 .. 40',
  '
Submitting change 27.
Locking 1 files ...
edit //depot/file#27
Change 27 submitted.
',
  0,
  'p4 submit -d rev27-edit` for 4 .. 40',
  '
Submitting change 28.
Locking 1 files ...
edit //depot/file#28
Change 28 submitted.
',
  0,
  'p4 submit -d rev28-edit` for 4 .. 40',
  '
Submitting change 29.
Locking 1 files ...
edit //depot/file#29
Change 29 submitted.
',
  0,
  'p4 submit -d rev29-edit` for 4 .. 40',
  '
Submitting change 30.
Locking 1 files ...
edit //depot/file#30
Change 30 submitted.
',
  0,
  'p4 submit -d rev30-edit` for 4 .. 40',
  '
Submitting change 31.
Locking 1 files ...
edit //depot/file#31
Change 31 submitted.
',
  0,
  'p4 submit -d rev31-edit` for 4 .. 40',
  '
Submitting change 32.
Locking 1 files ...
edit //depot/file#32
Change 32 submitted.
',
  0,
  'p4 submit -d rev32-edit` for 4 .. 40',
  '
Submitting change 33.
Locking 1 files ...
edit //depot/file#33
Change 33 submitted.
',
  0,
  'p4 submit -d rev33-edit` for 4 .. 40',
  '
Submitting change 34.
Locking 1 files ...
edit //depot/file#34
Change 34 submitted.
',
  0,
  'p4 submit -d rev34-edit` for 4 .. 40',
  '
Submitting change 35.
Locking 1 files ...
edit //depot/file#35
Change 35 submitted.
',
  0,
  'p4 submit -d rev35-edit` for 4 .. 40',
  '
Submitting change 36.
Locking 1 files ...
edit //depot/file#36
Change 36 submitted.
',
  0,
  'p4 submit -d rev36-edit` for 4 .. 40',
  '
Submitting change 37.
Locking 1 files ...
edit //depot/file#37
Change 37 submitted.
',
  0,
  'p4 submit -d rev37-edit` for 4 .. 40',
  '
Submitting change 38.
Locking 1 files ...
edit //depot/file#38
Change 38 submitted.
',
  0,
  'p4 submit -d rev38-edit` for 4 .. 40',
  '
Submitting change 39.
Locking 1 files ...
edit //depot/file#39
Change 39 submitted.
',
  0,
  'p4 submit -d rev39-edit` for 4 .. 40',
  '
Submitting change 40.
Locking 1 files ...
edit //depot/file#40
Change 40 submitted.
',
  0,
  'p4 submit -d rev40-edit` for 4 .. 40',
  '
Submitting change 41.
Locking 1 files ...
edit //depot/file#41
Change 41 submitted.
',
  0,
  'p4 verify -q //...',
  '',
  0,
  'get_full_revs , ; # pre-oblit rev overview',
  '[[[1],[12],[23],[34],[41]]]
',
  0,
  'p4 retype -l -t text file#12',
  '//depot/file#12 - binary+X now binary+D
',
  0,
  'p4 verify -q //...',
  '',
  0,
  'p4 retype -l -t text file#13',
  '//depot/file#13 - binary+X now binary+D
',
  0,
  'p4 verify -q //...',
  '',
  0,
  'p4 retype -l -t text file#11',
  '//depot/file#11 - binary+X now binary+D
',
  0,
  'p4 verify -q //...',
  '',
  0,
  'p4 obliterate -y file#22',
  '
//depot/file#22 - purged
Deleted 1 revision record(s).
',
  0,
  'p4 verify -q //...',
  '',
  0,
  'p4 obliterate -y file#21',
  '
//depot/file#21 - purged
Deleted 1 revision record(s).
',
  0,
  'p4 verify -q //...',
  '',
  0,
  'p4 obliterate -y file#23',
  '
//depot/file#23 - purged
Deleted 1 revision record(s).
',
  0,
  'p4 verify -q //...',
  '',
  0,
  'p4 obliterate -y file#24',
  '
//depot/file#24 - purged
Deleted 1 revision record(s).
',
  0,
  'p4 verify -q //...',
  '',
  0,
  'get_revs , ; # parse test x',
  '[[[1,"depot/file",1,1],[2,"depot/file",2,1],[3,"depot/file",3,2],[4,"depot/file",4,3],[5,"depot/file",5,4],[6,"depot/file",6,5],[7,"depot/file",7,6],[8,"depot/file",8,7],[9,"depot/file",9,8],[10,"depot/file",10,9],[11,"depot/file",11,10],[12,"depot/file",12,12],[13,"depot/file",13,12],[14,"depot/file",14,13],[15,"depot/file",15,14],[16,"depot/file",16,15],[17,"depot/file",17,16],[18,"depot/file",18,17],[19,"depot/file",19,18],[20,"depot/file",20,19],[25,"depot/file",25,25],[26,"depot/file",26,25],[27,"depot/file",27,26],[28,"depot/file",28,27],[29,"depot/file",29,28],[30,"depot/file",30,29],[31,"depot/file",31,30],[32,"depot/file",32,31],[33,"depot/file",33,32],[34,"depot/file",34,34],[35,"depot/file",35,34],[36,"depot/file",36,35],[37,"depot/file",37,36],[38,"depot/file",38,37],[39,"depot/file",39,38],[40,"depot/file",40,39],[41,"depot/file",41,41]]]
',
  0,
  'p4 obliterate -y file#1',
  '
//depot/file#1 - purged
Deleted 1 revision record(s).
',
  0,
  'p4 verify -q //...',
  '',
  0,
  'p4 delete file',
  '//depot/file#41 - opened for delete
',
  0,
  'p4 submit -d delete',
  '
Submitting change 42.
Locking 1 files ...
delete //depot/file#42
Change 42 submitted.
',
  0,
  'p4 verify -q //...',
  '',
  0,
  'get_revs , ; # last rev overview',
  '[[[2,"depot/file",2,2],[3,"depot/file",3,2],[4,"depot/file",4,3],[5,"depot/file",5,4],[6,"depot/file",6,5],[7,"depot/file",7,6],[8,"depot/file",8,7],[9,"depot/file",9,8],[10,"depot/file",10,9],[11,"depot/file",11,10],[12,"depot/file",12,12],[13,"depot/file",13,12],[14,"depot/file",14,13],[15,"depot/file",15,14],[16,"depot/file",16,15],[17,"depot/file",17,16],[18,"depot/file",18,17],[19,"depot/file",19,18],[20,"depot/file",20,19],[25,"depot/file",25,25],[26,"depot/file",26,25],[27,"depot/file",27,26],[28,"depot/file",28,27],[29,"depot/file",29,28],[30,"depot/file",30,29],[31,"depot/file",31,30],[32,"depot/file",32,31],[33,"depot/file",33,32],[34,"depot/file",34,34],[35,"depot/file",35,34],[36,"depot/file",36,35],[37,"depot/file",37,36],[38,"depot/file",38,37],[39,"depot/file",39,38],[40,"depot/file",40,39],[41,"depot/file",41,41]]]
',
  0,
  'get_full_revs , ; # last full rev overview',
  '[[[2],[12],[25],[34],[41]]]
',
  0
]
]