Re: [squid-users] access-lists from mysql ?

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.
Received on Sun Feb 03 2013 - 05:42:56 MST