Hey Matthew,
I wrote a more complex solution like that in ruby with MYSQL,
TOKYOCABINET, REDIS and some others.
I have just seen couple days ago another a very nice interface with a
very nice api called "moneta" for ruby.
It uses one interface for couple DB such as mentioned above.
Until now I have used BDB, MYSQL, POSTGRESQL and the mentioned above.
The problem with SQL DB are the size of the DB and speed compared to the
others.
TOKYOCABINET takes about 50% from MYSQL in any store there is for a
simple HASH DB.
I know people like sql queries but sql DB are very slow for computing stuff.
The more accurate you are the more speed you get.
Eliezer
On 2/3/2013 7:27 AM, Matthew Goff wrote:
I didn't find that anyone has created a flexible solution for use with
MySQL, so I wrote a small C++ program that will execute an specified
query with token replacement. You will need the MySQL development
libraries installed to compile it, but otherwise nothing special. If
no result set is found ERR is returned, if a result set is found OK is
returned.
GitHub:https://github.com/Kline-/tools/tree/master/c++/mysquid
Example usage with only one token passed, %DST=test.com:
external_acl_type mysquid1 %DST /path/to/mysquid "SELECT `url` FROM
`blocked_domains` WHERE INSTR('##TOK##',url);"
Which would result in MySQL executing the following:
SELECT `url` FROM `blocked_domains` WHERE INSTR('test.com',url);
##TOK## will be updated in each query with whatever Squid passes along
as %DST. Any number of tokens are supported and you can name them
whatever you want as long as they are ##enclosed##.
Example usage with two tokens passed, %SRC=192.168.1.8, %DST=test.com:
external_acl_type mysquid2 %SRC %DST /path/to/mysquid "SELECT * FROM
`blocked_src_dst` WHERE `ip` LIKE '##source##%' AND
INSTR('##destination##',url);"
Which would result in MySQL executing the following:
SELECT * FROM `blocked_src_dst` WHERE `ip` LIKE '192.168.1.8%' AND
INSTR('test.com',url);
I only use this on my home LAN, so I have no data on how well it may
or may not scale. With a low ttl I can now update the ACLs I use for
blocking websites in my home via any number of different SQL tools
rather than having to login to my proxy box, su, update acl files, and
reload Squid. Comments or improvements are welcome, I hope some others
will find this useful.
