Stefano Rivera (tumbleweed)'s Website, Blog, collected bits of code, cruft and other stuff.

SugarCRM to Mozilla LDAP contact slurper

Seeing as SugarCRM is now truly Open Source, I decided to support them buy using SugarCRM as a contact database for a client.

This script extracts contact data from Sugar, and imports it into an LDAP tree, so that Thunderbird clients can use it as an address book.

It’s written for Sugar 5.0, and a suitable LDAP installation with the Mozilla Schema

A suitable VIEW for printing out a contact directory would look like:

51&q=CREATE&lr=lang_en">CREATE 5.1/en/non-typed-operators.html">OR 51&q=REPLACE&lr=lang_en">REPLACE 51&q=VIEW&lr=lang_en">VIEW sugarab 51&q=AS&lr=lang_en">AS
51&q=SELECT&lr=lang_en">SELECT, c.description, salutation, first_name, last_name, title, department,
do_not_call, phone_home, phone_mobile, phone_work, phone_other, c.phone_fax,
primary_address_street, primary_address_city, primary_address_state,
primary_address_postalcode, primary_address_country, alt_address_street,
alt_address_city, alt_address_state, alt_address_postalcode, alt_address_country,
assistant, assistant_phone, lead_source, birthdate, 51&q=AS&lr=lang_en">AS account_name,
e1.email_address 51&q=AS&lr=lang_en">AS primary_email, e2.email_address 51&q=AS&lr=lang_en">AS secondary_email
51&q=FROM&lr=lang_en">FROM contacts 51&q=AS&lr=lang_en">AS c
5.1/en/string-functions.html">LEFT 51&q=OUTER&lr=lang_en">OUTER 51&q=JOIN&lr=lang_en">JOIN accounts_contacts 51&q=AS&lr=lang_en">AS j 51&q=ON&lr=lang_en">ON ( = j.contact_id 5.1/en/non-typed-operators.html">AND j.deleted = 0)
5.1/en/string-functions.html">LEFT 51&q=OUTER&lr=lang_en">OUTER 51&q=JOIN&lr=lang_en">JOIN accounts 51&q=AS&lr=lang_en">AS a 51&q=ON&lr=lang_en">ON (j.account_id = 5.1/en/non-typed-operators.html">AND a.deleted = 0)
5.1/en/string-functions.html">LEFT 51&q=OUTER&lr=lang_en">OUTER 51&q=JOIN&lr=lang_en">JOIN email_addr_bean_rel 51&q=AS&lr=lang_en">as eb 51&q=ON&lr=lang_en">ON (eb.bean_id = 5.1/en/non-typed-operators.html">AND eb.deleted = 0)
5.1/en/string-functions.html">LEFT 51&q=OUTER&lr=lang_en">OUTER 51&q=JOIN&lr=lang_en">JOIN email_addresses 51&q=AS&lr=lang_en">as e1 51&q=ON&lr=lang_en">ON (eb.email_address_id = 5.1/en/non-typed-operators.html">AND eb.primary_address = 1 5.1/en/non-typed-operators.html">AND e1.deleted = 0)
5.1/en/string-functions.html">LEFT 51&q=OUTER&lr=lang_en">OUTER 51&q=JOIN&lr=lang_en">JOIN email_addresses 51&q=AS&lr=lang_en">as e2 51&q=ON&lr=lang_en">ON (eb.email_address_id = 5.1/en/non-typed-operators.html">AND != 5.1/en/non-typed-operators.html">AND e2.deleted = 0)
51&q=WHERE&lr=lang_en">WHERE c.deleted = 0;

Aggregator noise and growth

For the bloggers on Clug Park, who don’t deign to follow clug-chat or #clug, there have been recent discussions about creating a separate, filtered park for readers with less free time.

The problem is basically that some people post a lot of posts. Sometimes as much as half of the park is dominated by one poster. While this isn’t a problem per se (some people clearly have more blogging time), it means readers have more to wade through, and can feel swamped my the prolific posters. Many would prefer something with a higher signal-to-noise ratio, and lower volume.

As communities grow, the signal-to-noise ratio often suffers, and the higher volume is too much for some readers. Rather than lose the readers, we’d like to provide an alternative, filtered park. It’s currently being prepared here. Personally, I’ll still use the old park, as will many other prolific RSS-feed-followers.

What we need is for all the CLUG Parkers to create a “technical” tag, and tag all relevant posts as such. Then send me the URL of your new tag, and I’ll include it in the “park-tech”. (Or assure me that you don’t post too prolifically, and only tech-related posts, and we’ll carry your entire feed).

Lets see if we can make it work.

Easy home transparent proxy

Everyone in South Africa wants to save a little more bandwidth, as low traffic caps are the rule of the day (esp if you are hanging off an expensive 3G connection).

While the "correct" thing to do is to use wpad autodetection, and thus politely request that users use your proxy, this isn't always an option:

  • Firefox doesn't Autodetect Proxies by default
  • Autodetection doesn't behave well for many roaming users (firefox should talk to network-manager)
  • Many programs simply don't support wpad.
  • Your upstream ISP transparently proxies anyway (the norm in ZA), so it's not like we have any end-to-endness to protect.

So, here's how you do it:

  1. Lets assume your network is, and the squid box is on eth0
  2. Install squid (aptitude install squid), configure it to have a reasonably large storage pool, give it some sane ACLs, etc.
  3. Add http_port 8080 transparent to squid.conf(or http_port transparent if you are using explicit http_port options)
  4. invoke-rc.d squid reload
  5. Add the following to your iptables script:
iptables -t nat -A PREROUTING -i eth0 -s -d ! -p tcp --dport 80 -j REDIRECT --to 8080

If you run squid on your network's default gateway, then you are done. Otherwise, if you have a separate router, you need to do the following on the router:

  1. Add a new transprox table to /etc/iproute2/rt_tables, i.e. 1 transprox
  2. Pick a new netfilter MARK value, i.e. 0x04
  3. Add the following to the router's iptables script:
# Transparent proxy
iptables -t mangle -F PREROUTING
iptables -t mangle -A PREROUTING -i br-lan -s ! -d ! -p tcp --dport 80 -j MARK --set-mark 0x04
ip route del table transprox
ip route add default via table transprox
ip rule del table transprox
ip rule add fwmark 0x04 pref 10 table transprox
  1. Done: test and tail your squid logs

The reason we use iproute rules rather than iptables DNAT is that you lose destination-IP information with a DNAT (like the envelope of an e-mail).

An alternative solution is to run tinyproxy on the router (with the transparent option, enabled in ubuntu but not debian), use the REDIRECT rule above on the router, to redirect to the tinyproxy, and have that upstream to the squid. But tinyproxy requires some RAM, and on a WRT54 or the likes, you don't have any of that to spare...

Should you need to temporarily disable this for any reason:

  • With all-in-one-router: iptables -t nat -F PREROUTING
  • With the separate router: iptables -t mangle -F PREROUTING

On Eskom

Anybody who resides anywhere near the mother city, will know about the horrific load shedding we are suffering at the moment. (Actually, I think the whole country may be affected, but I haven’t read any local news recently).

This means:

  • Massive traffic jams: All traffic lights turn into 4-way stops, and if that wasn’t slow enough, people crash into each other out of anger.
  • At least 2-hours every day of sitting and twiddling your thumbs, while listening to the screech of unhappy UPSs. (Occasionally this overlaps with lunch time)
  • Having to shout over the roar (and cough through through the stench) of generators when you go out to visit any such-equipped businesses.
  • Peaceful, inky-black skys, and no noise of neighbour’s TV sets at night (if you are lucky enough to be load-shed at night).
  • Cold supper.
  • A flat laptop, unless you make sure to keep it fully charged against such emergencies.
  • Breakage in various systems, when UPSs don’t transfer cleanly, and routers / switches decide to disagree.
  • Various networks (like UCT) become unreachable, because while they have gensets and UPSs, the telkom equipment connecting to the outside world don’t.
  • And, generally, a very grumpy tumbleweed.

I’ve been frequenting computer suppliers in the last week, and seen an insane amount of UPSs first piled up at the dispatch desks, and then vanish. Now is the time to be in the UPS and genset -selling business.

To make things worse, this morning, I decided that I’d have to dismantle my gate-motor, to get out of the driveway. (Because nobody knows where the key for the manual-override lever is. After getting half-way, I worked out that it had a backup battery. Duh!

Gate: 1, Eskom: 1, Geek: 0.

MediaWiki extensions

I’ve written several MediaWiki extensions, mainly for the CLUG Wiki. I don’t think any of them are particularly beautiful or maintained, but they are an essential part of our wiki, and might be useful to somebody out there…

Category Gallery:

Written for ClugPark, this extension displays all the images in a category together, as a gallery.


<category-gallery category="PicturesOfPenguins" resizewidth="50" width="60" height="60"><category-gallery>

Mailman subscribe links:

Written for the Mailing Lists page, this extension displays mailman subscribe forms.



Obfuscate e-mail addresses:

Written for the Contact a Committee Member page. I use my own simple Javascript that replaces innerHTML and href when you mouse over an e-mail link. I think it’s spambot-proof.


<email domain="">foobar</email>
Syndicate content