Sphinx and Rachinger

Written By: - Date published: 7:18 pm, May 4th, 2015 - 24 comments
Categories: admin, humour, The Standard - Tags: , , ,

Let me see if I can make this clear enough for the non-tech public and jonolists, and for the techheads amongst you.

The search engine on this site is run by Sphinx search. This is an open source general purpose package that every 7 minutes ( a prime number) runs a scan on the databases for this site for any new or amended posts and or comments. It indexes these using an extremely efficient hashing lookup system to provide a very fast, efficient and powerful search system for the site.

Since the site has 16,171 posts and 922,412 comments at present and a database of about 5.2 Gigabytes (a gigabyte is about 1,000,000,000 letters) of text, searching through it by hand is close to impossible. So I put in the most efficient open source package search package that I could find back in 2009. But it had a little flaw.

Having this tool in the site is incredibly valuable for transparency and robust debate between commenters and lurkers. In a site that doesn’t insist that people login, it means that we can tag every handle used so that others may find out what they have previously said.

Search functions1In listing comments, it means that we can provide a list of every comment that a particular person has done. It allows us to provide a function to find out the visible history of someone who responds to your own comments. And it allows investigation of people, topics, and what people have said.

Search functionsBut it is a powerful hand rolled solution. One that at the time it was written didn’t account for a particular  feature of WordPress. That it could have private posts. This was for the simple reason that we’d never used them. When authors wanted to talk to each other, they had emails, cell numbers, and the usual multitude of channels that activists usually talk to each other with.

Search functions2As anyone who follows the site knows, we have had quite a number of authors who have shuffled in and out of the activity on the site. As our About says:-

We’re a collective who saw a gap in the New Zealand political blogosphere and decided that we should have a go at filling it here at The Standard blog site. We write here in our personal capacities and the opinions that are expressed on the blog are individual unless expressly stated otherwise (see the policy). We do not write on behalf of any organization.

It is a completely voluntary organisation. As people’s lives change; changing jobs, having kids, getting sick or well, going off to start their own blogs, or just dropping out of politics or blogging – our regular authors and regular commenters change. There are 71 people with author or higher rights on the site. This reflects the turn over of authors and moderators in the last 7 years. At anyone point there are usually less than 10 of us active.

After the election in 2014, when the usual shuffling of authors and moderators happened, we looked at how we’d run the site into the future. One part of that was that I was that I’d shifted jobs just prior to the election and was going to have a lot less time to admin the site. Another part was that we had picked up and were planning to get more new authors and moderators involved, and most of them would probably be from the ranks of people who comment.

So we decided to take the risk of having a private forum area for authors and above to discuss operational crap in. The italics are there because this is the loosest of all possible organisations. What you get on the site is what you decide to do yourself and what others are willing to let you get away with. And that is a bit moot sometimes, the about again

We come from a variety of backgrounds and our political views don’t always match up but it’d be fair to say that all of us share a commitment to the values and principles that underpin the broad labour movement and we hope that perspective will come through strongly as you read the blog.

With the exception of me (since I set the site mechanics up), we were given author logins because we were known to disagree. This private forum area was going to be the first time that more than a handful of authors had ever had a place where they could sit and talk to each other in a semi-private situation about how the site operated and where it was heading. This proposal is a major improvement in organisation over our previous systems which largely consisted of someone coming up with a good idea  and whoever else felt it was (like me) doing some work to see how it went.

The risk was that we wouldn’t particularly like each other once we started cooperating a bit closer and that the site would fracture and fall apart through lack of agreement. My personal guess was that we’d lose about 3-5 authors from having authors and moderators realising that. Arguably we’ve lost a couple.

But it is often hard to see where the interface between blogger fatigue and disagreement starts and ends. Headstrong opinionated egotists who are the type of people who volunteer to write as authors and commenters eventually run out of things to say or just get worn down like anyone else. I’ve had to take several keyboard vacations over the years myself.

So the private forums were setup just after the 2014 election, and proved to have the flaw that the previous code I’d adapted and rewritten for Sphinx to extract all of the posts and comments from The Standard’s database. It didn’t restrict the comments in the second part of the SQL (structured query language) statement below to the posts selected as being published in the first part. The reason was that previously the only comments were in published posts. That was a bug when we started to put comments into private posts.

The content of these author comments is mundane to the n’th degree. It consist of people pointing out issues, trading post ideas and arguing about moderation policies. I’d say that about half of the comments are in some shape manner of form are about how to write posts or calming authors down enough after some commenter drives them nuts.

It is unlikely that there is anything in them that is anything more embarrassing than some of us creaky antiques explaining to others how the inside of political parties really worked, and why we didn’t let the new tom thumbs have direct banning rights.

But these author private comments leaked into searches. At least until Stephanie Rodgers pointed that out to me, and I spent a hour or so doing the fix in bold below and checked (and fixed) a couple of other potential ways to access the same blind spot flaw in my code, wordpress code, and some plugins. I think that the items were visible for a couple of days on search before they got fixed.

However it appeared to have caused Cameron Slater to cum all over his brain and shut down even the minimal amounts of animal cunning that he usually possesses.

But it has an upside. I am rather glad that a student got the equivalent of four months of our operating costs off him. It also has shown that the Dirty Politics engine that was setup by the immoral arseholes of the right is still operating and is probably still deep within illegal activities.

 

But now avert your eyes if you are technically literate because now I get technical. The code fix is highlighted at the end of the SQL

 

[code language=”sql” highlight=”22,26,27″] SELECT
p.ID*2+1 AS ID, 0 AS comment_ID, p.ID AS post_ID, p.post_title AS title,
u.display_name AS author, p.post_content AS body, t.name AS category,
IF(p.post_type = ‘post’, 1, 0) AS isPost, 0 AS isComment,
IF(p.post_type = ‘page’, 1, 0) AS isPage,
IF(p.post_type = ‘post’, 0, IF(p.post_type = ‘page’, 1, 2)) AS post_type,
UNIX_TIMESTAMP(post_date) AS date_added
FROM
wp_posts AS p
INNER JOIN wp_users u ON (p.post_author = u.ID)
INNER JOIN wp_term_relationships tr ON (p.ID = tr.object_id)
INNER JOIN wp_term_taxonomy tt ON (tt.term_taxonomy_id = tr.term_taxonomy_id AND tt.taxonomy = ‘category’)
INNER JOIN wp_terms t ON (tt.term_id = t.term_id)
WHERE
p.id = ( SELECT max_doc_id FROM wp_sph_counter WHERE counter_id=1 ) AND p.post_status = ‘publish’
UNION ALL
SELECT
c.comment_ID*2 AS ID, c.comment_ID AS comment_ID,
c.comment_post_ID AS post_ID,” AS title, c.comment_author AS author, c.comment_content AS body, ” AS category, 0 AS isPost,
1 AS isComment, 0 AS isPage, 2 AS post_type, UNIX_TIMESTAMP(comment_date) AS date_added
FROM wp_comments AS c,
wp_posts AS pp
WHERE
c.comment_id = ( SELECT max_doc_id FROM wp_sph_counter WHERE counter_id=2 ) AND
c.comment_approved = ‘1’ AND
c.comment_post_ID = pp.ID AND
pp.post_status = ‘publish’
[/code]

Youall understand that fix? Well I am sure that most won’t. And I’d quite sure that most of the pathetic dipsticks who donated me the title “worlds greatest sysop”  (and who keep wanking on it at every available opportunity) are definitely too illiterate and thick to do so.

But surely there are the odd right winger who has enough brains (Barnsley Bill?) could explain it to them. I’d advise them to use simple words and punctuate with grunting at frequent intervals.

24 comments on “Sphinx and Rachinger ”

  • Comments are now closed

  1. te reo putake 1
    4 May 2015 at 8:50 pm

    Sweet post, LP. While clearly Rachinger is no friend of TS (he did take money to hack the site, after all), he’s just one of a long list of people who got close to Cam Slater and got pissed on once they’d outlived their usefulness. That’s the lesson; deal with Slater, get done over.

    You’d have to wonder what the point of the hack was anyway. As far as I know, the majority of TS authors are folk like myself who are just activists or maybe hold low level functions in various political parties such as branch seccy or similar. There are no MP’s, no spin doctors, no ‘professional’ politicos at all. TS is beholden to no one.

    A few months ago a right wing blogger claimed he knew my real life identity (which is no secret in left wing circles anyway) and threatened to out me. The gutless wonder didn’t follow through, but that cowardly threat says to me that the attempted hack is about intimidation. So, up yours, Cameron Slater. Do your worst, loser.

    • weka 1.1
      4 May 2015 at 9:14 pm

      There are also people not as robust as yourself trp, who might prefer that the ante wasn’t upped 🙂

      The hack was for authors and commenters. If Slater is to be believed, the idea was that there was enough there to seriously embarrass the Labour Party mostly from the author list. I think you are right about the intimidation factor, but it’s likely that the DP lot can’t conceive that ts isn’t a front for Labour.

      I agree it’s a great post. Funny, well pitched, and good to see the transparency.

  2. pr0gger 2
    4 May 2015 at 9:34 pm

    If a user account is deleted any post which they have authored will drop out of the result set of that query fyi

    • lprent 2.1
      4 May 2015 at 9:55 pm

      You can’t delete accounts in a wordpress multisite system unless you do it at the database level. That is a complex operation across a number of tables and potentially databases unless you want to leave orphan records.

    • ropata 2.2
      5 May 2015 at 12:54 am

      WP admins do not usually mess about with SQL (esp. a delete!) there are tools to add/remove authors safely.

      I would expect wordpress to do a logical delete, not physical (safer and retains referential integrity).

  3. Not Arandar 3
    4 May 2015 at 9:35 pm

    [lprent: someone hijacking a email address. Adding to permanent bans. By the look of it some kind of arsehole from Lauda Finem astroturfing a post]

    • te reo putake 3.1
      4 May 2015 at 9:50 pm

      Cool? In what way? LF is just a couple of Ak nutters without a clue about anything of substance. Like Rachinger, they’ve been used by Slater. They’re used for publicising stuff Slater is too scared to post on WO because of his previous difficulties with breaching name suppression. Just sad old men who think they’re above the law and no doubt will come a cropper sooner or later. Not trustworthy in any way shape or form.

      • Paul Campbell 3.1.1
        5 May 2015 at 8:24 am

        I had always assumed they were a wholly owned, but deniable, subsidary of Whaleoil enterprises inc

    • mickysavage 3.2
      5 May 2015 at 8:22 am

      It seems that the laudafinem site subscribes to the Cameron Slater maxim of giving back double. But smearing someone’s parents? How cheap.

      It would help if they got their facts right. The article is ridiculed with inaccuracies.

      And they seem to have taken umbrage at a post I did recently (http://thestandard.org.nz/blogger-on-breaching-name-suppression-charges-gets-name-suppression/)

      The repeated use of “Streisand effect” is a bit of a giveaway.

  4. RedLogix 4
    4 May 2015 at 9:45 pm

    And as one of the oldest commenters/author/moderator/retirees at TS I can only absolutely repeat everything Lynne has stated here.

    In all the years I’ve been around I have seen zero – repeat zero – evidence that TS is in any shape or form funded or controlled to the slightest degree by the Labour Party. While of course there have been plenty of people who are members or even one or two who are office holders of various parties – that conferred on them zero special status here.

    Nope – The Standard is exactly what it says it is. A loose collection of opinionated people who are egotistical enough to think their opinion is worth typing up and arguing about – and who barely know how to fly in the loosest formation imaginable. Most of us are most decidedly NOT political insiders – and the few who arguably are tend to be very circumspect in exploiting their knowledge.

    The Standard is most definitely NOT an organisation, it is NOT a movement, it is NOT controlled by any outside entity whatsoever. It is a community, or extended whanau – prone of course to bickering and personality clashes, it’s ups and downs and inglorious moments – but it IS family.

    And all that any ‘hack’ into the author’s private thread could ever possibly reveal is exactly this truth.

    [lprent: Pleeeze – Lynn. ]

  5. adam 5
    5 May 2015 at 12:00 am

    Silly question time – but I’m pretty sure I’m not the only lefty who moans about the Labour party? I’m also pretty sure I’ve argued hard with some Labour party members on here, also some Greens, Mana members, others non-aligned and pot smokers?

    Another silly question – do the folks from the dirty politics click of dirty old men, not read what people say? After the election, I recall not being alone, about being quite frustrated with the labour party. Personally, I am far from being a fan of the Labour party. It won’t stop me from having a go at the concern trolls, commenting about the labour party – yes that’s right, I think Tory idiots need to be reminded – that their message of hate, and greed will be challenged!

    So for all you muppets at WO and the dirty politics click of dirty old men, I’ll leave you with a quote –

    “What we would like to do is change the world–make it a little simpler for people to feed, clothe, and shelter themselves as God intended them to do. And, by fighting for better conditions, by crying out unceasingly for the rights of the workers, the poor, of the destitute–the rights of the worthy and the unworthy poor, in other words–we can, to a certain extent, change the world; we can work for the oasis, the little cell of joy and peace in a harried world. We can throw our pebble in the pond and be confident that its ever widening circle will reach around the world. We repeat, there is nothing we can do but love, and, dear God, please enlarge our hearts to love each other, to love our neighbor, to love our enemy as our friend.”
    ― Dorothy Day

    • mhager 5.1
      5 May 2015 at 5:16 pm

      I’m with Dorothy Day on this! No party preferences here, just well pissed off with the ones in power.

  6. ropata 6
    5 May 2015 at 1:01 am

    I’ve messed around with Oracle and Informix a fair bit.

    You’ve just added a wee filter to ensure that comments are only indexed when attached to a *published* blog post.

    That UNION ALL freaks me out a bit – – bodges anything on to anything! 🙂

    (comment search function = teh aw3some!!11)

  7. Tracey 7
    5 May 2015 at 9:41 am

    OK, I read the post until the code. I have afriend staying with me from Dunedin, who is now well into his 70’s.

    I’ve sent him a couple of posts that I thought would be of particular interest to him over the years.

    Anyway, the following made his coffee come out his nose.

    You know which bit, I am sure you do…

    “However it appeared to have caused Cameron Slater to cum all over his brain and shut down even the minimal amounts of animal cunning that he usually possesses.”

    • lprent 7.1
      5 May 2015 at 5:03 pm

      I thought it was a thoroughly useful visual image (I had a wine in hand at the time I wrote it) that would forever be seared into the heads of others…..