15 February 2012

Mixing Landscape and Portrait rendering in a Wicked PDF document

Wicked is an awesome ruby library for generating PDF documents from plain old HTML/CSS.

HTML and CSS are not just another language; they provide a model for representing a document, and if you were obliged to use something else you would eventually end up representing your documents using this model, even if your syntax layer differed from HTML (if you're really smart, you would have ended up with HAML/SASS, for example; same model, different prettiness).

This is why other libraries fail (where "fail" means "I don't like them") - they oblige you to learn a whole new model for representing documents.

Anyway, the point is that you might believe from the Wicked README that you can create a document in landscape mode, or in portrait mode, but you're out of luck if you want both in the same doc.

It turns out you're not out of luck; Wicked, as its name non-obviously suggests, ultimately relies on WebKit (via wkhtmltopdf) to render html pages. With WebKit, you have access to a whole bunch of modern CSS properties, including those that rotate your document. You don't even care that they're WebKit specific, because you don't have to care about cross-browser support: you're using a known webkit version running on your own server which you control.

Here's the CSS:

.page {
  width:            195mm
  height:           270mm
  page-break-after: always
  overflow:         hidden
}

.page .landscape {
  position:                  relative
  margin:                    270mm 0
  width:                     270mm
  height:                    195mm
  -webkit-transform:         rotate(-90deg)
  -webkit-transform-origin:  0mm 0mm
}

The .page rules simply define an A4 page (after margins), and guarantee a page-break at the end of each page, just in case your printer didn't understand. The CSS assumes that you print in portrait by default. When you want landscape, nest a <landscape> element inside your <page>. Here's an example:

<page>
  This is the first page. It gets printed in portrait mode
</page>

<page>
  <landscape>
    This is the second page. It gets printed in landscape mode.
    You will have to twist your head to read it.
  </landscape>
</page>

Not so bad, no? Good luck!

06 February 2012

MySQL "LOAD DATA INFILE" with no primary key

I'm migrating a whole bunch of legacy data into mysql for a client. I export from their system, use sed to patch the nonconforming unparseable CSV it outputs, convert from macroman to UTF8, then tell MySQL to load it all into a series of tables from which the data will later be integrated into the standard system.

Today I realised the error of my ways: the column that I was using as a primary key isn't a primary key at all (at least not in the modern sense - of having a unique value - that we've become accustomed to since Codd invented the relational model in 1969).

I could have realised this earlier by just declaring it a primary key and watching MySQL spit at me, and therefore have fixed the problem sooner ... but no, who needs tests and constraints when assumptions are so much faster?

Enough about me ... the problem now is to assign each row in the exported data a unique id, and the easiest, obviousest way to do that is to include an id integer primary key auto_increment column in each table definition, let LOAD DATA INFILE etc etc shove the data into the table, and the primary key takes care of itself.

But it wasn't obvious how to make this work without warnings about mismatched columns. Here's the clever bit: what I did in the end was to add the id column after loading the data. In other words, I imported the data into a table with no id primary key column; and added that column afterwards. It's easy to script, it does exactly what I want, and it produces no spurious warnings. Perfecto!

Here's the concept in code:

> create table legacy_stuff(leg_col_1 varchar(255), leg_col_2 varchar(255), et_cetera_1 varchar(255), et_cetera_2 varchar(255));

> load data infile 'legacy_stuff.csv' into table legacy_stuff; -- plus all your favourite options;

> show warnings;

> alter table legacy_stuff add column id integer primary key auto_increment;

Good luck...

Update: for another way to look at this, see the stackoverflow page on this topic. It turns out you can use load data infile with the list of columns you want to import to, in which case MySQL will match the columns of your CSV to your specified column list. This way you can let your auto-increment primary key column simply manage itself. This solution does not suit my particular situation as I'm importing tables with hundreds of columns (they didn't believe in normalisation where this data comes from), and I don't want to have to maintain the column list in multiple places.