web analytics

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″]
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
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)
p.id = ( SELECT max_doc_id FROM wp_sph_counter WHERE counter_id=1 ) AND p.post_status = ‘publish’
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
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’

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 ”

  1. 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

      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

    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

      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

      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

    [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]

    • 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

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

    • mickysavage 3.2

      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

    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

    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

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

  6. ropata 6

    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

    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

      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…..

Recent Comments

Recent Posts

  • Tokelau Language Week reminds us to stay united and strong
    Staying strong in the face of challenges and being true to our heritage and languages are key to preserving our cultural identity and wellbeing, is the focus of the 2020 Tokelau Language Week. Minister for Pacific Peoples, Aupito William Sio, says this year’s theme, ‘Apoapo tau foe, i nā tāfea ...
    BeehiveBy beehive.govt.nz
    4 days ago
  • NZ announces a third P-3 deployment in support of UN sanctions
    The Government has deployed a Royal New Zealand Air Force P-3K2 Orion (P-3) maritime patrol aircraft to support the implementation of United Nations Security Council (UNSC) resolutions imposing sanctions against North Korea, announced Minister of Foreign Affairs Winston Peters and Minister of Defence Ron Mark. “New Zealand has long supported ...
    BeehiveBy beehive.govt.nz
    1 week ago
  • Pacific trade and development agreement a reality
    Pacific regional trade and development agreement PACER Plus will enter into force in 60 days now that the required eight countries have ratified it. Trade and Export Growth Minister David Parker welcomed the announcement that the Cook Islands is the eighth nation to ratify this landmark agreement. “The agreement represents ...
    BeehiveBy beehive.govt.nz
    2 weeks ago
  • Securing a pipeline of teachers
    The Government is changing its approach to teacher recruitment as COVID-19 travel restrictions continue, by boosting a range of initiatives to get more Kiwis into teaching. “When we came into Government, we were faced with a teacher supply crisis,” Education Minister Chris Hipkins said. “Over the past three years, we ...
    BeehiveBy beehive.govt.nz
    2 weeks ago
  • Border exceptions for a small number of international students with visas
    The Government has established a new category that will allow 250 international PhD and postgraduate students to enter New Zealand and continue their studies, in the latest set of border exceptions. “The health, safety and wellbeing of people in New Zealand remains the Government’s top priority. Tight border restrictions remain ...
    BeehiveBy beehive.govt.nz
    2 weeks ago
  • First COVID-19 vaccine purchase agreement signed
    The Government has signed an agreement to purchase 1.5 million COVID-19 vaccines – enough for 750,000 people – from Pfizer and BioNTech, subject to the vaccine successfully completing all clinical trials and passing regulatory approvals in New Zealand, say Research, Science and Innovation Minister Megan Woods and Health Minister Chris Hipkins. ...
    BeehiveBy beehive.govt.nz
    2 weeks ago
  • International statement – End-to-end encryption and public safety
    We, the undersigned, support strong encryption, which plays a crucial role in protecting personal data, privacy, intellectual property, trade secrets and cyber security.  It also serves a vital purpose in repressive states to protect journalists, human rights defenders and other vulnerable people, as stated in the 2017 resolution of the ...
    BeehiveBy beehive.govt.nz
    2 weeks ago
  • Ministry of Defence Biodefence Assessment released
    The Ministry of Defence has today released a Defence Assessment examining Defence’s role across the spectrum of biological hazards and threats facing New Zealand. Biodefence: Preparing for a New Era of Biological Hazards and Threats looks at how the NZDF supports other agencies’ biodefence activities, and considers the context of ...
    BeehiveBy beehive.govt.nz
    2 weeks ago
  • New Approaches to Economic Challenges: Confronting Planetary Emergencies: OECD 9 October 2020
    New Approaches to Economic Challenges: Confronting Planetary Emergencies: OECD 9 October 2020 Hon David Parker’s response following Thomas Piketty and Esther Duflo. Good morning, good afternoon, and good evening, wherever in the world you might be. I first acknowledge the excellent thought provoking speeches of Thomas Piketty and Esther ...
    BeehiveBy beehive.govt.nz
    2 weeks ago
  • Kaipara Moana restoration takes next step
    A Memorandum of Understanding has been signed today at Waihāua Marae between the Crown, local iwi and councils to protect, restore and enhance the mauri of Kaipara Moana in Northland. Environment Minister David Parker signed the document on behalf of the Crown along with representatives from Ngā Maunga Whakahī, Ngāti ...
    BeehiveBy beehive.govt.nz
    3 weeks ago
  • New Zealand and Uruguay unite on reducing livestock production emissions
    Agriculture Minister Damien O’Connor and Uruguayan Minister of Livestock, Agriculture and Fisheries Carlos María Uriarte have welcomed the launch of a three-year project that will underpin sustainable livestock production in Uruguay, Argentina, and Costa Rica.  The project called ‘Innovation for pasture management’ is led by Uruguay’s National Institute of Agricultural ...
    BeehiveBy beehive.govt.nz
    3 weeks ago
  • 3100 jobs created through marae upgrades
    Hundreds of marae throughout the country will be upgraded through investments from the Provincial Growth Fund’s refocused post COVID-19 funding to create jobs and put money into the pockets of local tradespeople and businesses, Regional Economic Development Minister Shane Jones and Māori Development Minister Nanaia Mahuta have announced. “A total ...
    BeehiveBy beehive.govt.nz
    3 weeks ago
  • Health volunteers recognised in annual awards
    Health Minister Chris Hipkins has announced 9 teams and 14 individuals are the recipients of this year’s Minister of Health Volunteer Awards.  “The health volunteer awards celebrate and recognise the thousands of dedicated health sector volunteers who give many hours of their time to help other New Zealanders,” Mr Hipkins ...
    BeehiveBy beehive.govt.nz
    3 weeks ago
  • Community COVID-19 Fund supports Pacific recovery
    The Minister for Pacific Peoples, Aupito William Sio says a total of 264 groups and individuals have successfully applied for the Pacific Aotearoa Community COVID-19 Recovery Fund, that will support Pacific communities drive their own COVID-19 recovery strategies, initiatives, and actions. “I am keen to see this Fund support Pacific ...
    BeehiveBy beehive.govt.nz
    3 weeks ago
  • Community benefits from Māori apprenticeships
    Up to 50 Māori apprentices in Wellington will receive paid training to build houses for their local communities, thanks to a $2.75 million investment from the Māori Trades and Training Fund, announced Employment Minister Willie Jackson today. “This funding will enable Ngāti Toa Rangatira Incorporated to provide its Ngā Kaimahi ...
    BeehiveBy beehive.govt.nz
    3 weeks ago
  • Training fund supports Māori jobseekers
    Rapidly growing sectors will benefit from a $990,000 Māori Trades and Training Fund investment which will see Wellington jobseekers supported into work, announced Employment Minister Willie Jackson today. “This funding will enable Sapphire Consultants Ltd. to help up to 45 Māori jobseekers into paid training initiatives over two years through ...
    BeehiveBy beehive.govt.nz
    3 weeks ago