#!/bin/sh

DBNAME=ci_testdb
export DBNAME

dropdb ${DBNAME}
createdb ${DBNAME}

psql ${DBNAME} <<_EOF_

--
-- Case insensitive comparision functions
--
create function varchar_cieq(varchar, varchar) returns bool
as '
begin
	return varchareq(lower(\$1), lower(\$2));
end;
' language plpgsql;

create function varchar_cine(varchar, varchar) returns bool
as '
begin
	return varcharne(lower(\$1), lower(\$2));
end;
' language plpgsql;

create function varchar_cilt(varchar, varchar) returns bool
as '
begin
	return varcharlt(lower(\$1), lower(\$2));
end;
' language plpgsql;

create function varchar_cile(varchar, varchar) returns bool
as '
begin
	return varcharle(lower(\$1), lower(\$2));
end;
' language plpgsql;

create function varchar_cigt(varchar, varchar) returns bool
as '
begin
	return varchargt(lower(\$1), lower(\$2));
end;
' language plpgsql;

create function varchar_cige(varchar, varchar) returns bool
as '
begin
	return varcharge(lower(\$1), lower(\$2));
end;
' language plpgsql;

create function varchar_cicmp(varchar, varchar) returns int4
as '
begin
	return varcharcmp(lower(\$1), lower(\$2));
end;
' language plpgsql;


--
-- Case insensitive operators
--
create operator *< (
	procedure = varchar_cilt,
	leftarg = varchar, rightarg = varchar,
	commutator = *>, negator = *>=,
	restrict = scalarltsel, join = scalarltjoinsel
);
create operator *= (
	procedure = varchar_cieq,
	leftarg = varchar, rightarg = varchar,
	commutator = *=, negator = *<>,
	restrict = eqsel, join = eqjoinsel,
	sort1 = *<, sort2 = *<,
	hashes
);
create operator *<> (
	procedure = varchar_cine,
	leftarg = varchar, rightarg = varchar,
	commutator = *<>, negator = *=,
	restrict = neqsel, join = neqjoinsel
);
create operator *!= (
	procedure = varchar_cine,
	leftarg = varchar, rightarg = varchar,
	commutator = *<>, negator = *=,
	restrict = neqsel, join = neqjoinsel
);
create operator *> (
	procedure = varchar_cigt,
	leftarg = varchar, rightarg = varchar,
	commutator = *<, negator = *<=,
	restrict = scalargtsel, join = scalargtjoinsel
);
create operator *<= (
	procedure = varchar_cile,
	leftarg = varchar, rightarg = varchar,
	commutator = *>=, negator = *>,
	restrict = scalarltsel, join = scalarltjoinsel
);
create operator *>= (
	procedure = varchar_cige,
	leftarg = varchar, rightarg = varchar,
	commutator = *<=, negator = *<,
	restrict = scalargtsel, join = scalargtjoinsel
);

--
-- And the operator class for case insensitive indexes
--
create operator class varchar_ciops
	for type varchar using btree as
	operator 1 *<  (varchar, varchar),
	operator 2 *<= (varchar, varchar),
	operator 3 *=  (varchar, varchar),
	operator 4 *>= (varchar, varchar),
	operator 5 *>  (varchar, varchar),
	function 1 varchar_cicmp(varchar, varchar);

create table citest_t1 (
	id		varchar(10),
	data	text
);
create unique index citest_t1_idx on citest_t1 (id varchar_ciops);

insert into citest_t1 values ('hello', 'world');
insert into citest_t1 values ('goodbye', 'world');
insert into citest_t1 values ('Hello', 'World');

set enable_seqscan to off;
set enable_indexscan to on;

explain select * from citest_t1 where id *= 'hello';
explain select * from citest_t1 where id = 'hello';

select * from citest_t1 where id *= 'HELLO';
select * from citest_t1 where id *!= 'HELLO';
select * from citest_t1 where id = 'HELLO';
_EOF_