2011-10-22T00:48:29 *** tater-laptop has joined #evergreen 2011-10-22T00:53:52 *** Callender has joined #evergreen 2011-10-22T01:00:00 *** tater-laptop has quit IRC 2011-10-22T01:08:31 *** fredericd has quit IRC 2011-10-22T01:08:39 *** fredericd has joined #evergreen 2011-10-22T01:17:49 *** tater-laptop has joined #evergreen 2011-10-22T05:39:13 *** Callender has quit IRC 2011-10-22T05:51:36 *** Callender has joined #evergreen 2011-10-22T08:31:14 *** _bott_ has quit IRC 2011-10-22T08:31:44 *** _bott_ has joined #evergreen 2011-10-22T09:06:53 *** Callender has quit IRC 2011-10-22T09:21:37 *** Callender has joined #evergreen 2011-10-22T09:48:53 *** Callender has quit IRC 2011-10-22T09:52:47 *** jeffdavis has quit IRC 2011-10-22T09:55:07 *** jeffdavis has joined #evergreen 2011-10-22T10:06:37 *** Callender has joined #evergreen 2011-10-22T10:08:53 *** shadowspar has quit IRC 2011-10-22T10:48:58 for anyone around on a Saturday... has anyone actually made it through the 1.6.1-2.0-upgrade-script.sql? 2011-10-22T10:49:13 I'm thinking at this point that my issue is a bug 2011-10-22T10:49:28 but I wanted experienced eyes on it 2011-10-22T10:50:01 pre-upgrade script, the actor.usr table has 39 columns, auditor.actor_usr_history has 41 2011-10-22T10:50:24 the 2 additional are (appropriately), "audit_time" and "audit_action" 2011-10-22T10:50:46 the script adds the "claims_never_checked_out" column to each 2011-10-22T10:51:08 so the number of columns would be 40 and 42 respectively 2011-10-22T10:52:28 shouldn't you have an audit id too? 2011-10-22T10:52:30 if it's not a bug, then how does the UNION ALL command within the "create_auditor_lifecycle" function get around the two extra columns? 2011-10-22T10:53:32 csharp: The lifecycle function adds dummy values for the three extra columns from the audit table to the non-audit table, before using the * to include the rest of the table. 2011-10-22T10:54:31 ah 2011-10-22T10:55:04 tsbere: there is no audit id 2011-10-22T10:55:11 on the pre-upgrade table 2011-10-22T10:55:53 That might be a problem. Not sure. 2011-10-22T10:56:03 my next experiment is to build a 1.6.1.8(9a) cluster and run the script against it to see if the same thing happens, or if this is something PINES specific 2011-10-22T10:56:08 Could be the old audit table creation scripts didn't include the id 2011-10-22T11:57:10 very interesting... the 1.6.1.8 install process created an extra column named ?column? (type bigint) 2011-10-22T11:57:26 so it looks like I'm missing a column in PINES 2011-10-22T11:57:39 * csharp curses 2011-10-22T11:59:15 I suspect that ?column? should be audit_id if it is on an audit table 2011-10-22T12:02:23 yeah - that's my assumption 2011-10-22T12:02:58 * csharp investigates how involved it will be to add the column to the auditor table(s) 2011-10-22T12:13:47 "alter table add column audit_id bigintl primary key" *might* work....but I suspect it won't auto-populate. So creating it as non-primary and nullable, then filling with values, then marking as primary.............doesn't sound like fun. 2011-10-22T12:14:01 csharp: How important is the data? Drop the tables and re-run the creation commands. :P 2011-10-22T12:18:20 tsbere: that's a thought 2011-10-22T12:19:05 we could back it up somewhere and clear the slate 2011-10-22T14:05:35 tsbere++ # that worked 2011-10-22T14:05:44 :D 2011-10-22T14:05:56 I dropped the auditor schema and used the create script from 1.6.1.8 to rebuild it 2011-10-22T14:06:10 And here I have never touched doing a major version upgrade. <_< 2011-10-22T14:06:13 don't know how that's not in our production schema though :-/ 2011-10-22T14:06:32 I suspect that older versions of the tables didn't have an id column 2011-10-22T14:06:33 tsbere: me either ;-) 2011-10-22T14:06:47 csharp: You have done *more* of one than I have. ;) 2011-10-22T14:06:51 csharp: We ran 1.6.1 to 2.0 back in February without much troubles, but I think we jumped from 1.6.1.4 up, don't know what happened after that. 2011-10-22T14:07:27 And of course we don't have pre-1.6.0 to deal with 2011-10-22T14:07:36 bshum: I suspect that it's PINES "cruft" - since our system has been a series of in-place upgrades since 1.0 2011-10-22T14:08:46 csharp: The rel_1_0 audit creation functions don't include the audit id. 2011-10-22T14:08:56 rel_1_2 does 2011-10-22T14:09:17 *** shadowspar has joined #evergreen 2011-10-22T14:15:52 *** sndwrx has joined #evergreen 2011-10-22T14:43:05 very interesting... 2011-10-22T15:00:46 *** lisah___ has joined #evergreen 2011-10-22T15:07:03 *** lisah___ has quit IRC 2011-10-22T15:57:00 moodaepo++ 2011-10-22T16:55:19 *** tater-laptop has quit IRC 2011-10-22T18:21:40 *** sndwrx has quit IRC 2011-10-22T19:32:35 *** sndwrx has joined #evergreen 2011-10-22T19:36:32 *** robbat2 has joined #evergreen 2011-10-22T19:36:49 actor.hours_of_operation has the open hours for a branch, but no timezones 2011-10-22T19:37:13 i'm trying to construct a query that tells me overall, when our libraries are open/closed 2011-10-22T19:39:07 but the timezone of a given library doesn't seem to be tracked somewhere 2011-10-22T19:39:22 i know we (sitka) have libraries in multiple timezones 2011-10-22T19:39:46 I believe they are assumed to be in the timezone of the server. 2011-10-22T19:39:52 But I could be wrong 2011-10-22T19:40:38 I haven't looked too closely at everywhere that information is used, but the few places I know use it only check if the library is closed *all day*, and otherwise don't use the times themselves. 2011-10-22T19:43:29 i was told that the content of it should be mostly correct for us, so just trying to use it to work out the overall open/close of service hours 2011-10-22T19:44:19 basically, for each dow (earliest any library opens on that day, to the latest any library closes on that day) both corrected to a common TZ, 2011-10-22T19:46:07 Can you make up a list of those in the other timezone (or other timezones, as the case may be), say by aou ID or shortname? 2011-10-22T19:46:38 i could, but i was hoping that data already had a column somewhere 2011-10-22T19:47:18 i don't find it anywhere in the schema, hence asking 2011-10-22T19:47:23 It does not, to my knowledge 2011-10-22T19:48:48 The only other complication, for the record, is if the two (or more) time zones do daylight savings time differently 2011-10-22T19:49:10 yup, i need to evaluate the times for multiple dates 2011-10-22T19:51:23 Your select for the times could be written as such: dow_#_open + CASE WHEN id IN (list,of,ids) THEN interval '1 hour' + interval '1 hour' ELSE 0 END AS dow_#_open with elsif chunks if you have a second timezone to deal with. And equiv _close variants. 2011-10-22T19:52:01 for my dataset, spread of 3 timezones, and a mix of locations some of which do NOT follow DST 2011-10-22T19:52:02 "with" queries could possibly make the list of ids easier to enter by splitting them out to a query of their own 2011-10-22T19:52:35 Possibly including the time zone differences. Would need to play with that a little. 2011-10-22T19:53:13 But you will likely need to run it for each variation of DST you need info for. :( 2011-10-22T19:54:30 * tsbere could probably write up the query, thinking about it 2011-10-22T19:56:06 i've got most of the calc done, just i realized timezones bit me 2011-10-22T20:04:09 http://paste.pocoo.org/show/496699/ 2011-10-22T20:04:13 there's the majority of it 2011-10-22T20:05:37 I didn't go with "check valid" so much as "check not midnight"... http://pastie.org/2742867 2011-10-22T20:06:30 Although thinking about it 2011-10-22T20:06:54 The "check valid" is probably safer, as it will catch when the open/close were both adjusted off of midnight. <_< 2011-10-22T20:07:05 So combine our two approaches? :P 2011-10-22T20:10:42 yeah, one sec 2011-10-22T20:33:25 http://paste.pocoo.org/show/496706/ <--- combined version 2011-10-22T20:42:06 If it works, great. 2011-10-22T20:42:15 * tsbere has no actual use for that kind of thing, honestly ;) 2011-10-22T20:57:52 http://paste.pocoo.org/show/496711/ <--- query to show which libraries are open the longest 2011-10-22T21:00:02 Heh, doing all the time zone stuff for that one makes little sense. :P 2011-10-22T21:02:14 true, but it's been useful now to see that adding libraries 2 timezones ahead of us hasn't actually impacted our full service hours, as they open late enough that they open on par with our earliest libraries 2011-10-22T21:04:12 glad I could be of help there 2011-10-22T21:43:06 *** sndwrx has quit IRC 2011-10-22T21:49:53 *** Callender has quit IRC 2011-10-22T22:57:33 *** Callender has joined #evergreen