viHistory

minor updates to buildings stuff

sarneil — 2013-01-31T19:16:54Z

Mins: 60

Made final tweaks to content and organization of building stuff. Property section now has a building construction documents page with intro/background blurb and link to the search building construction documents data set. Made number of content changes too.

further revision to building construction documents

sarneil — 2013-01-14T21:35:50Z

At PD's request, I rejigged the properties chunk of the site. renamed the folder from ta to properties, took the one existing page (taxassessment.php) and split it into properties.php (landing page with links to tax assessment and building documents), taxassessment.php (intro and linke to 3 tax assessment tables), buildings.php (blurb on background and link to search page).
Also made minor tweaks to index.php page in root and the streetname_changes.php file.

rename folder caused svn problems

sarneil — 2013-01-14T20:13:08Z

Seems like renaming a folder using svn rename is a bad idea. Probably better to create a new folder manually copy the files over then manually add all the new stuff to the svn repository. commit all that and assuming it is working, then delete the unwanted folder from the repo. I.e. manually do the copy and delete that rename supposedly does.

I wanted to rename a folder from "taxes" to "properties". The folder contained one file. I did this:
1)
svn rename taxes properties
no problem. Then I committed the repository and got this:
svn: Commit failed (details follow):
svn: Item '/site_root/content/taxes' is out of date

2) So I tried updating the site and got back a taxes folder.
Then I cd'd to the containing folder and tried this:
svn delete taxes
worked. Tried to commit and got:
svn: Commit failed (details follow):
svn: Aborting commit: '/Users/sarneil/Documents/Projects/history/ViHistory/instances_of_site/svnrepo/trunk/site_root/content/ta' remains in conflict
then tried:
svn delete --force taxes
worked. Tried to commit and got:
svn: Commit failed (details follow):
svn: Aborting commit: '/Users/sarneil/Documents/Projects/history/ViHistory/instances_of_site/svnrepo/trunk/site_root/content/ta' remains in conflict

3) did this:
svn status
and got
D C taxes
> local delete, incoming edit upon update

4) Did a search on stackoverflow, and based on that, then tried
touch taxes
svn revert taxes
worked. I decided to try getting rid of the file inside first:
svn rm ta/taxassessment.php
worked. Then committed the change and now have things in sync with an empty taxes folder.

5) Then tried to delete the pesky folder
svn delete taxes
worked. Tried to commit that and got
svn: Commit failed (details follow):
svn: Item '/vihistory/trunk/site_root/content/ta' is out of date

6) Last resort on stackoverflow was "In a nutshell, the trick is to go to the .svn directory (in the directory that contains the offending file), and delete the "all-wcprops" file."
Did that, followed by
svn update
svn commit
and the taxes folder is now gone in the repository and in my copy.

No idea which of those steps (other than 6) are required.

building construction documents table added to production site

sarneil — 2013-01-11T22:38:02Z

I added the building_documents table to the production db, and added the necessary files in the production front end for the building construction data set.
I updated the tax assessment and documents to include the material on changed street names.

Next will be removing the building_permit stuff from the dev db and dev front end, then from the prod db and prod front end.

building construction documents table added to dev site

sarneil — 2013-01-09T23:05:59Z

I've got a working instance of the building construction documents data set in the dev site. Searchable by name (see previous post), street and year. As some of the dates are yyyy and others are yyyy-mm-dd, I turned the field into a character field and added some code so that in the where clause only the year part of the date gets included.

Assuming PD approves, I'll create an identical instance in the production db and front-end and then clear out the building permits table and front-end stuff from both dev and prod instances, and that has been superceded by the building construction document stuff.

oddity with where clause in search string

sarneil — 2013-01-09T20:04:14Z

I copied the code for the building permit pages to create the building construction document pages (i.e. inc/bcdformbasic.inc, inc/bcdresults.inc and search/searchbcd.php). When I did a search and put Holmes into the owner field in searchbcd.php, I got zero hits, even though I know there are five records. When I do a similar search in searchbp.php, I get the five hits.

Here's the line of code in search/searchbp.php:
$where .= make_where_pattern($surname, 'bp_owner_surname', $where, $form, $fuzzy_func);

Here's the line of code in search/searchbcd.php that returns 0 hits:
$where .= make_where_pattern($holder_name, 'bcd_holder', $where, $form, $fuzzy_func);

Here's the line of code in search/searchbcd.php that returns 5 hits:
$where .= make_where_pattern(('%'.$holder_name.'%'), 'bcd_holder', $where, $form, $fuzzy_func);

I think I've figured it out, based on testing and reading the docs on the postgres similar operator. In the building permits case, the surname is just the surname; in the building construction documents case, the holder is a surname and given names, and that extra information causes the similar function to return false (the docs for pg similar say that the regexp match is across the entire string, which confirms my theory).

The name search in the bcd is now more liberal than in any of the other tables. the bcd finds the searchstring anywhere in the name field, the others have to match much more closely (no sure exactly how closely) but have separate surname and forename fields and only the surname field is searched.

normalizing data for building documents

sarneil — 2013-01-08T20:59:08Z

Did the following so far to normalize the data:
1 delete first column (sequential id number)
2 render rooms as integer
3 replace left double guillemot with 1/2
4 render cost value as integer
5 clear trailing whitespace at end of file
6 provide correct field names in first line
bd_permit_ref,bd_date,bd_notes,bd_holder,bd_street,bd_street_num,bd_sublot,bd_lot,bd_block,bd_section,bd_add_alt_repair,bd_purpose,bd_rooms,bd_cost,bd_arch_builder

In records in which the room number value was something like 2 buildings 4 rooms each, I rendered that as 8 rooms.

Had to stop when I noticed 3000 records have only a year in the date field (8000 have year-month-day). PostGres requires the yyyy-mm-dd for a date data type. I recommended to PD that we change that field to a string and accept less sophisticated comparison operations. Now waiting to see if thinks that's ok, or if he wants to explore more involved solution (namely, split field into 3 separate fields and then add a bunch of complicated code as needed to allow for filtering by dates.

starting on adding building documents table

sarneil — 2013-01-07T23:48:45Z

Updated my dev instance of the front end (in my account) so it's the same as the production instance (minus the huge cache of images for the maps) in the taprhist account.

Still no luck setting up a time with the sysadmins to clear out the huge table logging search queries (6 million records), and to migrate dbs. Will proceed with the dbs I've got.

- production instance is pointing to the vihistory db ; dev instance is pointing to the vihdev db
- added a building_documents table with appropriate fields to the vihdev instance
- added a building_documents_bd_id_seq sequence to the vihdev instance

proceeding with building documentation table

sarneil — 2012-12-18T23:31:14Z

Got answers to questions and data spreadsheet from PD.
The production front end is pointing at the vihdev db. I tried to back that up using the psql command provided by Jaime and I got a file of zero bytes. I then tried to use the export feature in phypgmyadmin (or whatever it's called) and that brought the db service down. Here's the error message, which suggests that the device is full, but we now think is actually a permissions problem:
==================================
SQL error:

PANIC: could not write to file "pg_xlog/xlogtemp.24088": No space left
on device
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
==================================

Sys-admins cleaned things up and restarted the service within a few hours. Thanks.

I tried connecting to the vihistory db running in the same environment, but the connection failed.

So, until I get a successful backup of the data in the server, I'm very hesitant to do any work on it. Here's what I've proposed to the sys-admins:

1) export and clear big log table from db
- write table search_log in db vihdev to file
- clear contents from table search_log in db vihdev
NB When I recently tried it from my Mac, I got a search_log.err file of
size 0 bytes. Since the last time we successfully executed the command
below the db engine has moved to mango.
psql -W -U viAdmin -h pgsql.hcmc.uvic.ca -A vihdev --command "copy
search_log to stdout;" > search_log.err
NB table has 6.5 million records, each about 1K = 6 gigs

2) create a new db to use as the production instance
- clone table vihdev, name clone vihprod
- drop db vihistory
- modify acl on vihprod based on settings for vihdev
NB I have to change connection strings on site

3) set up long-term plan for write-then-clear search_log table
NB cron job vs manual
NB frequency

4) ensure I can migrate future changes in vihdev db to vihprod db

hist : cha : adding banner image

sarneil — 2012-06-26T22:47:20Z

PB sent me a large jpg to use as a banner on the conference site. Took a couple of guesses to get something the correct dimensions. Also modified the bg of the image from white to the taupe colour used on the site - they were OK with that.

Turned out that
- headerTitle div had a number of peculiar styling rules
- #header h1 had odd padding settings which resulted in the banner being noticeably off-center
I overruled by adding a selector in the cha2013styles.css file. Noticed that a lot of elements have a lot of instances of selectors and conflicting style rules applied to them - sometimes 2 or 3 selectors for the same id in the same css file.

Finally, it seems that the CMS doesn't like comments in css files, so I can't include any.

Here's the contents of the modified css file to date (with comments added here by me):

div#navbar ul.menu li:nth-child(1),
div#navbar ul.menu li:nth-child(2),
div#navbar ul.menu li:nth-child(4) {
display:none;
}

div#navbar ul.menu li a[href$=account] {
display:none;
}
div#navbar ul.menu li a[href$=search] {
display:none;
}
div#navbar ul.menu li a[href$=announcement] {
display:none;
}

/* make header have thinner border on bottom and mirror it on top */
#header {
border-bottom: 1px solid #898E79;
border-top: 1px solid #898E79;
}

/* get rid of odd padding around h1 in header to ensure contents are centered */
#header h1 {
padding: 0 0 0 0;
}

/* headerTitle has a barrage of selectors and style rules, over-rule them all
most importantly over-rule the fixed height so the banner image can be contained elegantly */
#headerTitle {
height: 100%;
padding-bottom: 0px;
padding-top: 0px;
margin-bottom: 0px;
margin-left:0;
}