| 1 | # mysql lookups |
| 2 | # The mysql-devel (or equivalent) package will be need for Exim to build, and |
| 3 | # the mysql-server (or mariadb-server or equivalent) package for this test to run. |
| 4 | # |
| 5 | # first, populate a DB to test against |
| 6 | sudo rm -fr DIR/mysql |
| 7 | echo Installing DB server dir |
| 8 | perl |
| 9 | system 'mysql_install_db --no-defaults --datadir=DIR/mysql --user=CALLER'; |
| 10 | **** |
| 11 | sudo rm test-stdout test-stderr |
| 12 | # |
| 13 | # start a db server |
| 14 | echo Starting DB server |
| 15 | background |
| 16 | DIR/bin.sys/mysqld --datadir=DIR/mysql --log-error=DIR/mysql/log --bind-address=* --port=PORT_N --socket=DIR/mysql/sock --pid-file=DIR/mysql/pidfile |
| 17 | **** |
| 18 | # |
| 19 | # wait for db startup, insert some data |
| 20 | echo Waiting for DB server startup |
| 21 | perl |
| 22 | system 'mysqladmin --protocol=TCP -P PORT_N --connect_timeout=2 --wait=5 -u root create test'; |
| 23 | my $fh; |
| 24 | open($fh, '-|', 'mysql --protocol=TCP -P PORT_N -u root -D test -e "select 1 from mysql.user where User = \'root\' and Host = \'HOSTIPV4\'"'); |
| 25 | my $line = <$fh>; |
| 26 | if (length($line) == 0) { |
| 27 | system 'mysql --protocol=TCP -P PORT_N -u root -D test -e "create user \'root\'@\'HOSTIPV4\'"'; |
| 28 | } |
| 29 | open($fh, '-|', 'mysql --protocol=TCP -P PORT_N -u root -D test -e "select 1 from mysql.user where User = \'CALLER\' and Host = \'HOSTIPV4\'"'); |
| 30 | $line = <$fh>; |
| 31 | if (length($line) == 0) { |
| 32 | system 'mysql --protocol=TCP -P PORT_N -u root -D test -e "create user \'CALLER\'@\'HOSTIPV4\'"'; |
| 33 | } |
| 34 | system 'mysql --protocol=TCP -P PORT_N -u root -D test \ |
| 35 | -e "CREATE TABLE them ( name text, id text ); \ |
| 36 | INSERT INTO them VALUES ( \'Philip Hazel\', \'ph10\' ); \ |
| 37 | INSERT INTO them VALUES ( \'Aristotle\', \'aaaa\' ); \ |
| 38 | INSERT INTO them VALUES ( \'\', \'nothing\' ); \ |
| 39 | INSERT INTO them VALUES ( \'\"stquot\', \'quote2\' ); \ |
| 40 | INSERT INTO them VALUES ( CONCAT(\'before\', CHAR(13), CHAR(10), \'after\'), \'newline\' ); \ |
| 41 | INSERT INTO them VALUES ( CONCAT(\'x\', CHAR(9), \'x\'), \'tab\' ); \ |
| 42 | INSERT INTO them VALUES ( CONCAT(CHAR(39), \'stquot\'), \'quote1\' ); \ |
| 43 | GRANT ALL ON *.* TO \'root\'@\'HOSTIPV4\'; \ |
| 44 | GRANT ALL ON *.* TO \'CALLER\'@\'HOSTIPV4\'; \ |
| 45 | "'; |
| 46 | **** |
| 47 | echo Populated DB |
| 48 | sudo rm test-stderr-server |
| 49 | # |
| 50 | # |
| 51 | exim -d-all+lookup -be |
| 52 | ${lookup mysql {select name from them where id='ph10';}} |
| 53 | ${lookup mysql {select name from them where id='ph10';}} |
| 54 | ${lookup mysql {select name from them where id='xxxx';}} |
| 55 | ${lookup mysql {select name from them where id='nothing';}} |
| 56 | ${lookup mysql {select id,name from them where id='nothing';}} |
| 57 | ${lookup mysql {delete from them where id='nonexist';}} |
| 58 | ${lookup mysql {select * from them where id='quote';}} |
| 59 | ${lookup mysql {select * from them where id='filter';}} |
| 60 | ${lookup mysql {select * from them where id='quote2';}} |
| 61 | ${lookup mysql {select * from them where id='nlonly';}} |
| 62 | ${lookup mysql {servers=x:127.0.0.1::PORT_N; select name from them where id='ph10';}} |
| 63 | ${lookup mysql {servers=127.0.0.1::PORT_N:x; select name from them where id='ph10';}} |
| 64 | ${lookup mysql {servers=127.0.0.1::PORT_N/test/root/:x; select name from them where id='ph10';}} |
| 65 | ${lookup mysql {servers=HOSTIPV4::PORT_N/test/root/:127.0.0.1::PORT_N; select name from them where id='ph10';}} |
| 66 | ${lookup mysql {servers=localhost(DIR/mysql/sock)/test/root/; select name from them where id='ph10';}} |
| 67 | x |
| 68 | ${lookup mysql {SELECT name FROM them WHERE id IN ('ph10', 'aaaa');}} |
| 69 | ${lookup mysql {SELECT * FROM them WHERE id IN ('ph10', 'aaaa');}} |
| 70 | ${lookup mysql {delete from them where id='aaaa'}} |
| 71 | **** |
| 72 | exim -d -bh 10.0.0.0 |
| 73 | mail from:<a@b> |
| 74 | rcpt to:<c@d> |
| 75 | quit |
| 76 | **** |
| 77 | exim -odi -d ph10 |
| 78 | Test message |
| 79 | . |
| 80 | **** |
| 81 | # |
| 82 | perl |
| 83 | system 'mysqladmin --protocol=TCP -P PORT_N -u root shutdown'; |
| 84 | **** |
| 85 | killdaemon |
| 86 | sudo rm -fr DIR/mysql |