Had an unusual sort of query where we had a stream of sales data that was cumulative for the day but sent through in updates, so had to total them for a given store, for all users, but for the last entry on the day. And it was stored in Postgres in a JSON blob.

Here's how I ultimately solved it in postgres:

SELECT
  CAST(data ->> 'boxes' AS INTEGER) as boxes,
  data ->> 'storeid' AS storeid,
  data ->> 'username' AS username,
  CAST(inserted_at AS date) AS inserted_date
FROM
  external_records
WHERE
  api_id = 6
  AND data ->> 'transactiondate' IS NOT NULL
GROUP BY
  data ->> 'storeid',
  data ->> 'username',
  CAST(inserted_at AS date),
  inserted_at,
  data
ORDER BY
  inserted_at DESC;

I've been using Beekeeper Studio for running SQL queries. It's so much nicer than PGAdmin.

And here it is in ecto:

  def last_for_store_on_day(api_id, storeid, date_string) do
    date = Date.from_iso8601!(date_string)
    IO.puts(date)

    query =
      from(ex in ExternalRecord,
        where: ex.api_id == ^api_id,
        where: fragment("CAST(inserted_at AS DATE)") == ^date,
        where: fragment("data ->> 'storeid'") == ^storeid,
        group_by:
          fragment("""
          data ->> 'storeid',
          data ->> 'username',
          CAST(inserted_at AS date),
          inserted_at,
          data,
          id
          """),
        order_by: [desc: ex.inserted_at]
      )
    Repo.all(query)
  end

The fragment expressions are a little odd at first, but I like this a lot more than just doing the entire block as SQL, if only for security purposes.

Discuss...

Canonical XSP page

An XSP page is an XML page with the following requirements:

• It must have a cocoon processing instruction that invokes the XSP processor <?cocoon-process type="xsp"?>

• The document root element must be <xsp:page>

• It must also have any language and Taglib declarations as attributes in the <xsp:page> tag (e.g. <xsp:page language="java" xmlns:xsp="http://www.apache.org/1999/XSP/Core">

• In order for the XSP page to be useful, it will also require usually at least an xsp:logic element and an xsp:expr element.

Usually you will also require further processing of the resulting page, with a stylesheet for instance, to transform the results of your code into a readable form for the viewer (typically to HTML).

You should remember that cocoon processing instructions act upon a whole document, then generate a result document, which will in turn be passed to the next cocoon processor, rather like a pipeline.

So, here is a minimal XSP page:

<?xml version="1.0"?>  
<?cocoon-process type="xsp"?>  
<?cocoon-process type="xslt"?>  
<?xml-stylesheet href="page-html.xsl" type="text/xsl"?>  

<xsp:page language="java" xmlns:xsp="http://www.apache.org/1999/XSP/Core">  
<xsp:logic>  
  static private int counter = 0;  
  private synchronized int count() {  
    return counter++;  
  }  
</xsp:logic>  

<page>
  <p>I've been requested <xsp:expr>count()</xsp:expr> times.</p>  
</page>  
</xsp:page>

Let's look at each part of this, one at a time:

<?xml version="1.0"?>

required for every XML page

<?cocoon-process type="xsp"?>

first process this page with the Cocoon XSP Processor

<?cocoon-process type="xslt"?>

then run the results through the XSLT processor

<?xml-stylesheet href="page-html.xsl" type="text/xsl"?>

This is the stylesheet that the XSLT processor should use

<xsp:page language="java" xmlns:xsp="http://www.apache.org/1999/XSP/Core">

This is required for an XSP page.

The document root must be xsp:page.

The language attribute is optional.

You can also specify optional Taglibs here by including their XML namespace references. You will definitely need at least the xmlns:xsp definition.

Other Taglib namespaces are available and should be added here as attributes... see Built-in Taglibs below.

<xsp:logic>
  static private int counter = 0;  
  private synchronized int count() {  
    return counter++;  
  }  
</xsp:logic>

<xsp:logic> tags specify definitions of things in the language that is used on this XSP page... usually functions and class-wide variables.
In this case, we are using Java.

<page>  
  <p>I've been requested <xsp:expr>count()</xsp:expr> times.</p>  
</page>  

Here is the bulk of the page content specified as general XML.

The first element that you place here (in this case <page>) will become the document root element of the xml document that will be generated after the XSP processor has finished.

The <xsp:expr>count()</xsp:expr> element here is saying “call the function 'count()' and convert the result into a text node”. <xsp:expr> is used wherever you need to insert live data into the output document.

The output of the XSP processor will produce the following page (obviously the value of count() will change over time:

<?xml version="1.0"?>  
<?cocoon-process type="xslt"?>  
<?xml-stylesheet href="page-html.xsl" type="text/xsl"?>  
<page>  
  <p>I've been requested 0 times.</p>  
</page>

3 types of XSP pages

Embedded Logic

In this situation, we place the code directly in the XML page. This is pretty bad style and only really useful for trivial examples, but it's useful to run through what happens when an XSP page “does stuff”.

So what happens?

First, the XSP processor parses the XML file and turns the whole page into an XSP servlet. Sounds strange, but true? All of the static XML gets turned into Java commands that insert XML elements, attributes and entities into a DOM tree. It can be very educational to take a look through your servlet engine's “repository” directory: there you will usually find a java file corresponding to each XSP page. You'll be surprised to see just how much a simple XSP page expands out to...

So, the static XML elements turn into function calls with constant parameters, whereas the dynamic logic bits (the whole point of this) get turned into... you guessed it... function calls with variables as parameters.

In this scenario, the XML page IS an XSP page... it has the canonical format for an XSP page. The root element of an XSP page must be xsp:page. The resulting XML page is then transformed using the relevant XSLT stylesheet, which (in this case) results in a HTML page.

Included logicsheet

This is a much more preferable way to be working, whereby parts of the original XML document that are matched by the stylesheet are replaced with sections of logic code.

In this scenario, an XSP page is generated from the original XML page, rather than the original XML page BEING an XSP page.

This is an example of a “logicsheet”. The original XML file's elements are replaced with pieces of logic (or plain ole XML, as needed). The transformation is done by an XSL stylesheet, the results of which are pieces of XSP logic or expressions.

In the diagram we see that the <item/> element will be substituted with the contents of the <template match="item></template> element. In this case, it's an <xsp:expr></xsp:expr> element which will presumably contain some kind of Java code, which, when executed will insert a text node into the XML result tree.

More importantly than the specific contents of the page, once again it is important that the root element of the resulting XSP page is an <xsp:page> element.

By this stage, the result of this transformation (produced by the logicsheet) is a canonical XSP page. This will then be “executed” and the result will be an XML page, with all the variable bits replaced with actual values. Once again, the XSL stylesheet transforms the final outcome into a HTML page.

XSP page with taglib logicsheet

In this instance we are could be dealing with either of the two previous scenarios, the only difference is where the logicsheet lies. The taglibs are central libraries that can be referred to from anywhere within an XSP page. The most important aspect of the taglib is that it has an XML namespace declaration, declared in the original logicsheet, and matched in the <xsp:page> xmlns attribute.

A namespace declaration is a URI (usually, something like: “http://www.suranyami.com/MyImportantLibrary"). Apparently, there doesn't actually have to be anything at that address... just that it's a uniquely identifiable name.

This namespace declaration then matches to the logicsheet referred to in the cocoon.properties file. Here is a typical situation:

XSP page

<xsp:page xmlns:**utilities**="_http://www.suranyami.com/myUtilities_">  
...  
</xsp:page>

#### Logicsheet file

```xml
<xsl:stylesheet version="1.0" xmlns:**utilities**="_http://www.suranyami.com/myUtilities_">  
...  
</xsl:stylesheet>

cocoon.properties

processor.xsp.logicsheet.**utilities**.java = file:///home/dayv/myUtilities.xsl

XSP core tags & meanings

<xsp:page>

The root level element of an XSP page.

<xsp:logic>

Defines a section of code such as functions and variable declarations.

<xsp:expr>

Inserts a text node into the output tree of the XML file.

---

2001-12-06 David Parry

Thanks to The Wayback Machine for having a copy of this. My blog stats were showing that people still referred to this. Sorry it was missing for so long!

Discuss...

I had a dream that Lady Gaga was judging an industrial design competition. My entry was a gigantic version of the iPod Shuffle with a turntable inside it for vinyl records.

It had a huge clip that you could use to clamp it to your arm (but it tended to cut off your circulation), or your head. She liked it so much she agreed to help me find a manufacturer for it “because it was just so ridiculous”.

Then we all went for rides down the water slides.

2011-07-02 18:02  by David Parry

Discuss...

Add this to ~/.zshrc:

    skip_global_compinit=1

2013-09-11 14:36

Discuss...

Whenever, as is the usual course of events during the day, I push to a remote git repo with git push origin master, I have this annoying habit of singing the words to this song that I heard in my collection ages ago:

“Original master: him dead!”

It’s from a collection of West African funk, a quite groovy song, of which they were virtually the only lyrics.

Eventually, my coworkers, er, noticed this habit of mine, responding with:

“What the hell is that? That thing you keep saying.”

To which I then immediately responded:

“it’s a song, let me play it for you, it’s called… ummm… hang on, I’ll look for it… ummm… I don’t know what it’s called. Or who it’s by. Or what album it’s from. I know it's from a compilation of West African funk…”

Cue rolling eyes, and mutterings of “of course it is…”.

And things stayed in that nebulous state of affairs, with the added surreallity of everyone else that works with git in my office singing the same thing each push, despite none of us having the slightest clue what it was.

Until yesterday, when pure luck and random-play caused it to pop up! Yay.

It’s called:

“Natural Points”, by Smahila & the SBs, on the album “The Danque!!!”

2011-10-22 18:47  by David Parry

Discuss...

I was chatting with Tony from work the other day about the Buddhist idea of Right Speech, which is one of the items in the Noble Eightfold Path. It turned out I could only remember 2 of the 4 requirements, so I had to look them up:

Right speech is the first principle of ethical conduct in the eightfold path. Ethical conduct is viewed as a guideline to moral discipline, which supports the other principles of the path. This aspect is not self-sufficient, however, essential, because mental purification can only be achieved through the cultivation of ethical conduct. The importance of speech in the context of Buddhist ethics is obvious: words can break or save lives, make enemies or friends, start war or create peace.

Buddha explained right speech as follows:

1. to abstain from false speech, especially not to tell deliberate lies and not to speak deceitfully
2. to abstain from slanderous speech and not to use words maliciously against others
3. to abstain from harsh words that offend or hurt others, and
4. to abstain from idle chatter that lacks purpose or depth.

Positively phrased, this means to tell the truth, to speak friendly, warm, and gently and to talk only when necessary.

So, in summary:

1. speak the truth
2. speak kindly
3. speak gently
4. speak only when necessary

October 14 2011, 9:40 PM  by David Parry

Discuss...

Because I always forget this, I’m putting it here:

UPDATE persons
SET
  address='Nissestien 67', city='Sandnes'
WHERE
  last_name='Tjessem' AND first_name='Jakob'

October 4 2011, 11:10 PM  by David Parr

Discuss...

1. Make important things big and unimportant things small or remove them.
2. Keep removing unnecessary stuff until you’ve got a simpler message.
3. Group things together that are related.
4. If something needs to stand out from the rest, make it different somehow.
5. Pick a typeface that’s appropriate for the task at hand. If you don’t know how to do that, then choose an undecorated sans-serif. If you don’t know what that is, use Helvetica.
6. Group things in categories.
7. Put space around things that need to be apart from the rest

I think I’ll keep adding to this. They seem like good principles.

November 1 2011, 10:27 PM  by David Parry

Discuss...