Empty fields are single whitespace " ", should be NULL

[richardlehane]

Many of the views rely on "if not NULL" type checks so that empty fields are ignored. This worked in SQLITE DB but fails in SQL Server since empty fields now come through as fields with a single whitespace. (e.g. http://api.records.nsw.gov.au/activities/10 - "Abolition" should not show but does because of this single space).

As a workaround I added this new has_content? method to Application.rb helper: https://github.com/srnsw/api.records.nsw.gov.au/commit/43b9589872c7ce4622541ddb65024a562a40c799

This is a bit of a hack however and would be better to fix this at the DB/adapter level if possible so all our XML/JSON serialisations don't include this single space.

Also a number of other non-HTML views will need to be updated to include this has_content? check. For example: https://github.com/srnsw/api.records.nsw.gov.au/commit/dcaa2eb5512e23fd70fd4974ebac1af43d914fb2

[wisanup]

This can be fixed at the views level by forcing the empty value to be null i.e.

select Activities.[Activity number] as Activity_number, NULLIF(Activities.[Activity title],'') as Activity_title, Activities.[Start date qualifier] as Start_date_qualifier, Activities.[Start date] as Start_date, Activities.[End date qualifier] as End_date_qualifier, Activities.[End date] as End_date, Activities.[Creation] as Creation, NULLIF(Activities.[Abolition],'') as Abolition, Activities.[Descriptive note] as Descriptive_note, Activities.[Amendments] as Amendments, Activities.[Last amendment date] as Last_amendment_date from Activities

Note: The NULLIF can be applied to varchar data type.

[richardlehane]

Excellent, thanks Nott!

P.s. Just pushed some small changes to entity controllers - can use format.any rather than format.html and then any other arbitrary views we have templates for will also be rendered (e.g. other formats such as .mods, .rdf_zotero, .oai_dc, .eac_cpf).

-----Original Message----- From: wisanup [mailto:reply@reply.github.com] Sent: Tuesday, 5 July 2011 10:28 AM To: Lehane, Richard Subject: Re: [api.records.nsw.gov.au] Empty fields are single whitespace " ", should be NULL (#16)

This can be fixed at the views level by forcing the empty value to be null i.e.

select Activities.[Activity number] as Activity_number, NULLIF(Activities.[Activity title],'') as Activity_title, Activities.[Start date qualifier] as Start_date_qualifier, Activities.[Start date] as Start_date, Activities.[End date qualifier] as End_date_qualifier, Activities.[End date] as End_date, Activities.[Creation] as Creation, NULLIF(Activities.[Abolition],'') as Abolition, Activities.[Descriptive note] as Descriptive_note, Activities.[Amendments] as Amendments, Activities.[Last amendment date] as Last_amendment_date from Activities

Note: The NULLIF can be applied to varchar data type.

Reply to this email directly or view it on GitHub: https://github.com/srnsw/api.records.nsw.gov.au/issues/16#issuecomment-1500758

---

This email and any files transmitted with it are intended solely for the use of the addressee(s) and may contain information that is confidential or subject to legal privilege. If you receive this email and you are not the addressee (or responsible for delivery of the email to the addressee), please note that any copying, distribution or use of this email is prohibited and as such, please disregard the contents of the email, delete the email and notify the sender immediately.

State Records advises that this email and any attached files should be scanned to detect viruses and accepts no liability for loss or damage (whether caused by negligence or not) resulting from the use of any attached files.

---

[wisanup]

messed with comment & close button

[wisanup]

more views table need to be done

[wisanup]

OK done

[richardlehane]

Alternative name field for persons coming thru even if empty e.g. http://api.records.nsw.gov.au/persons/4

[richardlehane]

Abolition field for activity coming thru even if empty e.g. http://api.records.nsw.gov.au/activities/2

Creation too I think.

Looking at XML it looks like these aren't " " fields they are "" fields - i.e. empty string rather than just string with whitespace

[wisanup]

done