00:14.18 | *** join/#asterisk-doc vexorg (n=vexorg@CPE000021ded913-CM001225419164.cpe.net.cable.rogers.com) |
00:17.43 | *** join/#asterisk-doc MikeJ[Laptop] (n=ircatjer@d14-69-8-30.try.wideopenwest.com) |
03:30.48 | *** join/#asterisk-doc jsmith (n=jsmith@smithfam.dsl.xmission.com) |
03:30.48 | *** mode/#asterisk-doc [+o jsmith] by ChanServ |
03:31.01 | jsmith | blitz[laptop]: Get it all figured out? |
03:31.13 | blitz[laptop] | jsmith: yer back! |
03:31.24 | blitz[laptop] | jsmith: mostly I think... but I forgot one thing |
03:31.30 | jsmith | OK. |
03:32.44 | blitz[laptop] | Each DID can have a different 411 and 911 address for each DID -- so I was working with two foreign keys for the DID, one for the 411, and another for the 911, both attached to the address table, so I can have different address IDs for each. |
03:34.04 | jsmith | OK, so let me get this right... |
03:34.07 | jsmith | Account |
03:34.18 | jsmith | |--Group |
03:34.25 | jsmith | <PROTECTED> |
03:34.32 | jsmith | <PROTECTED> |
03:34.46 | jsmith | <PROTECTED> |
03:34.49 | jsmith | <PROTECTED> |
03:34.54 | jsmith | <PROTECTED> |
03:34.57 | jsmith | Something like that? |
03:35.32 | blitz[laptop] | yep -- but that can also be in Group and Account as well (just as before where addresses can be assigned to Account, Group or DID, and if NULL, then use the one above it) |
03:35.39 | jsmith | Oh, I see... |
03:35.55 | jsmith | So Account has 911_account_id and 411_account_id, same for group and DID. |
03:36.01 | jsmith | I gotcha. |
03:36.33 | jsmith | So does an account just have those two addresses, or a regular address, 911 address, and 411 address? |
03:36.45 | blitz[laptop] | 911 and 411 |
03:36.55 | blitz[laptop] | just those two |
03:37.03 | jsmith | Okie dokie... |
03:37.15 | jsmith | It's just a simple extension of what I did before then :-) |
03:37.23 | blitz[laptop] | thats what I figured... |
03:37.28 | blitz[laptop] | was a foreign key the right way? |
03:37.35 | blitz[laptop] | then I don't even have to change the address table |
03:37.41 | jsmith | Exactly. |
03:37.50 | blitz[laptop] | cool... I might have even done it right! |
03:38.00 | jsmith | Each of the 911_address_id and 411_address_id fields will be a foreign key to the address table. |
03:38.56 | blitz[laptop] | yah.. I think I called them address_da_id and address_em_id |
03:39.14 | blitz[laptop] | da -- directory assistance; em -- emergency |
03:40.01 | blitz[laptop] | ok... tell me if I did the DID right -- I have a fk for both the account and group IDs |
03:40.28 | blitz[laptop] | I couldn't decide if account would cascade down through group, or if I needed a separate fk for it... |
03:40.45 | blitz[laptop] | btw -- what game you watching? |
03:40.48 | jsmith | Yup... the account_id in the groups table references the accounts table, and the group_id in the DIDs table references the group_id in the groups table. |
03:41.05 | jsmith | (Colts vs. Patriots -- American football at its finest) |
03:41.33 | blitz[laptop] | jsmith: darn... so I guess I can remove one of those constraints :0 |
03:41.48 | jsmith | What other constraint did you have in there? |
03:41.59 | blitz[laptop] | DID reference account_id as well |
03:42.11 | blitz[laptop] | figured it was wrong... shoot, should have gone with my gut instinct :) |
03:42.21 | jsmith | Each of the accounts, groups, and DIDs table will have two additional constaints -- foreign keys to the address table (one for 911, one for 411). |
03:42.32 | blitz[laptop] | right |
03:42.37 | blitz[laptop] | I have that right |
03:42.42 | jsmith | Cool :-) |
03:42.57 | jsmith | Oh, did you put the account_id in the DIDs table? |
03:43.04 | blitz[laptop] | I had Account -> address, Group -> address & account, DID -> address, group, account |
03:43.05 | blitz[laptop] | yah :) |
03:43.09 | jsmith | (you don't need it) |
03:43.10 | blitz[laptop] | and I figured I didn't need it |
03:43.12 | blitz[laptop] | ha! |
03:43.23 | jsmith | alter table DIDs drop constraint foo; |
03:43.30 | jsmith | alter table DIDs drop column account_id; |
03:43.50 | blitz[laptop] | haha... yah... I just did that backwards :) |
03:44.04 | blitz[laptop] | re-adds |
03:44.33 | jsmith | Yeah, I know that means you'll have join Accounts to Groups to DIDs to Addresses to Addresses, but it shouldn't be that hard :-) |
03:45.40 | blitz[laptop] | gah! I just screwed up my DB by removing the column first :) |
03:45.53 | blitz[laptop] | nevermind |
03:45.55 | blitz[laptop] | just had to refresh |
03:47.12 | blitz[laptop] | join Accounts to Groups to DIDs to Addresses to Addresses... or is that second to Addresses a dupe? |
03:48.28 | jsmith | SELECT a.account_id, a.account_name, g.group_id, g.group_name, d.did_id, d.did_name, COALESCE(d.address_em_id,g.address_em_id,a.address_name_id) as 911_address_id, COALESCE(d.address_da_id,g.address_da_id,a.address_da_id) as 411_address_id FROM |
03:49.02 | jsmith | accounts AS a JOIN account_groups AS g ON a.account_id = g.account_id |
03:49.34 | blitz[laptop] | haha... nice :) |
03:49.37 | jsmith | JOIN group_dids AS d ON g.group_id = d.group_id |
03:50.58 | jsmith | JOIN address AS addr1 ON COALESCE(d.address_em_id,g.address_em_id,a.address_em_id) = a.address_id |
03:51.50 | jsmith | JOIN address AS addr2 ON COALESCE(d.address_da_id,g.address_da_id,a.address_da_id) = addr2.address_id; |
03:52.07 | blitz[laptop] | lol |
03:52.10 | jsmith | s/name_id/em_id/ |
03:52.20 | jsmith | s/a.address_id/addr1.address_id/ |
03:52.31 | blitz[laptop] | thats one heck of a SELECT statement |
03:52.37 | jsmith | Oh, and you'll wanna match on your existing column names |
03:52.50 | jsmith | No, that's not a large SQL statement. |
03:52.59 | blitz[laptop] | I said SELECT statement ;) |
03:53.03 | jsmith | My boss once wrote one over 200 lines long -- it's the most beautiful thing I'd ever seen. |
03:53.15 | jsmith | (This was a SELECT statement as well) |
03:53.21 | blitz[laptop] | crazy |
03:53.38 | jsmith | No, not crazy. Powerful :-) |
03:53.45 | blitz[laptop] | crazy powerful |
03:54.23 | jsmith | Exactly... it was basically a breakdown of every employee in the company, and whether they were costing the company money or making the company money, and how much. |
03:54.37 | blitz[laptop] | lol |
03:54.42 | blitz[laptop] | neat :) |
03:54.52 | jsmith | Each employee was assigned a Personell Efficiency Rating, and bonuses/pink slips were handed out accordingly :-) |
03:55.17 | jsmith | We had employees that were costing the company $10/hour, and not bringing in any revenue. |
03:55.29 | jsmith | Other employees were making the company several hundred dollars an hour :-) |
03:55.36 | jsmith | (all doing the same job) |
03:55.41 | blitz[laptop] | crazy |
03:55.49 | jsmith | Yup. |
03:55.52 | jsmith | Anyhoo... |
03:56.25 | blitz[laptop] | enjoy the f00tb4ll game |
03:56.35 | blitz[laptop] | I should pack up and get ready to head home in the morning |
03:56.48 | jsmith | Alrighty -- do that, just as soon as your query is working. |
03:57.00 | jsmith | Send me a text message if you need more help :_0 |
03:57.13 | blitz[laptop] | then head to the hotel tomorrow night, meetup with Steve, and get ready for Meet Asterisk on Wed. |
03:57.23 | blitz[laptop] | I always get nervous when I get to speak in public... |
04:00.49 | jsmith | Gah... just takes a little practice |
04:01.04 | jsmith | I get nervous too, but I've spoken enough times that I'm starting to get used to it. |
04:01.34 | blitz[laptop] | yah... just everytime I have to teach asterisk I realize just how little I know :) |
04:05.03 | jsmith | Don't we all :-) |
04:05.12 | blitz[laptop] | exactly :) |
04:05.21 | jsmith | BTW, your chan_local idea got me thinking, and I had a eureka moment the other night. |
04:05.29 | jsmith | Solved a stupid problem for a client w/ it :-) |
04:05.47 | blitz[laptop] | wicked! |
04:05.54 | blitz[laptop] | btw: it was Jim's idea |
04:08.27 | blitz[laptop] | hrmmm... pgadmin doesn't seem to have a "DO NOTHING"... only RESTRICT, CASCASE, SET NULL and SET DEFAULT |
04:09.31 | blitz[laptop] | back to the CLI for me I guess :) |
04:11.41 | jsmith | Well, it has to do something :-) |
04:12.05 | jsmith | Doing nothing is not an option, as you'd end up with a foreign key that referenced a non-existant address :-) |
04:12.59 | jsmith | In your case you want to ON DELETE CASCADE ON UPDATE CASCADE for the account_id and group_id foreign keys, and ON DELETE SET NULL ON UPDATE CASCADE on the address_id foreign keys |
04:53.38 | *** join/#asterisk-doc blitzrage (n=blitzrag@asterisk/documenteur-extraordinaire/blitzrage) |
04:53.38 | *** mode/#asterisk-doc [+o blitzrage] by ChanServ |
06:12.38 | *** join/#asterisk-doc blitzrage (n=blitzrag@asterisk/documenteur-extraordinaire/blitzrage) |
06:12.39 | *** join/#asterisk-doc MatsK (n=mk@55.80-203-80.nextgentel.com) |
06:12.39 | *** join/#asterisk-doc maik (n=maik@bfs.cs.uni-sb.de) |
06:12.39 | *** join/#asterisk-doc locksy (n=nlocksy@mrtg.sisgroup.com.au) |
06:12.39 | *** join/#asterisk-doc asteriskgeeks (n=SIPdawg@pbxtech.com) |
06:12.39 | *** mode/#asterisk-doc [+o blitzrage] by irc.freenode.net |
06:23.00 | *** join/#asterisk-doc blitzrage (n=blitzrag@asterisk/documenteur-extraordinaire/blitzrage) [NETSPLIT VICTIM] |
06:23.00 | *** join/#asterisk-doc MatsK (n=mk@55.80-203-80.nextgentel.com) [NETSPLIT VICTIM] |
06:23.00 | *** join/#asterisk-doc maik (n=maik@bfs.cs.uni-sb.de) [NETSPLIT VICTIM] |
06:23.00 | *** join/#asterisk-doc asteriskgeeks (n=SIPdawg@pbxtech.com) [NETSPLIT VICTIM] |
06:23.00 | *** join/#asterisk-doc locksy (n=nlocksy@mrtg.sisgroup.com.au) [NETSPLIT VICTIM] |
06:23.00 | *** mode/#asterisk-doc [+o blitzrage] by irc.freenode.net |
06:42.46 | *** join/#asterisk-doc MikeJ__ (n=ircatjer@d14-69-8-30.try.wideopenwest.com) |
09:20.53 | *** join/#asterisk-doc vexorg (n=vexorg@CPE000021ded913-CM001225419164.cpe.net.cable.rogers.com) |
10:13.49 | *** join/#asterisk-doc MatsK (n=mk@55.80-203-80.nextgentel.com) |
13:24.18 | *** join/#asterisk-doc maik (n=maik@bfs.cs.uni-sb.de) |
13:24.18 | *** join/#asterisk-doc locksy (n=nlocksy@mrtg.sisgroup.com.au) |
14:09.10 | *** join/#asterisk-doc asteriskgeeks (n=SIPdawg@64.5.53.45) |
14:10.17 | *** join/#asterisk-doc asteriskgeeks (n=SIPdawg@pbxtech.com) |
14:11.22 | *** join/#asterisk-doc asteriskgeeks (n=SIPdawg@pbxtech.com) |
14:11.57 | *** join/#asterisk-doc asteriskgeeks (n=SIPdawg@pbxtech.com) |
14:13.02 | *** join/#asterisk-doc asteriskgeeks (n=SIPdawg@pbxtech.com) |
14:15.39 | *** join/#asterisk-doc asteriskgeeks (n=SIPdawg@pbxtech.com) |
14:16.01 | asteriskgeeks | <PROTECTED> |
15:53.23 | *** join/#asterisk-doc MikeJ[Laptop] (n=ircatjer@mi.origenfinancial.com) |
15:54.55 | *** join/#asterisk-doc jsmith (n=jsmith@64.0.193.247) |
15:54.55 | *** mode/#asterisk-doc [+o jsmith] by ChanServ |
16:08.53 | *** join/#asterisk-doc blitzrage (n=blitzrag@asterisk/documenteur-extraordinaire/blitzrage) |
16:08.53 | *** mode/#asterisk-doc [+o blitzrage] by ChanServ |
16:09.18 | blitzrage | jsmith!!! |
16:13.05 | blitzrage | jsmith: so my servers HD died last night in a power outage |
16:18.45 | jsmith | Ouch. |
16:18.48 | jsmith | You're kidding. |
16:20.27 | blitzrage | nope |
16:20.32 | blitzrage | died last night |
16:20.53 | blitzrage | It comes up... and I did some fsck stuff, but then I was getting like KBDK... something |
16:21.14 | blitzrage | and then the filesystem was read only --then I rebooted, and kernel panic again |
16:21.40 | blitzrage | I'm hoping I can get another HD in there, install Linux again, and copy some stuff over to the new HD (if I can find one lying around here) |
16:22.15 | blitzrage | I get home... and I have to deal with this.... lovley ;) |
16:22.58 | jsmith | Boot off a live-CD and see what you can recover (by copying across the network to your laptop) |
16:23.24 | blitzrage | yah... I'll see if I can do that... |
16:24.10 | jsmith | Is this your development box? |
16:26.12 | blitzrage | I wish :) |
16:26.29 | blitzrage | pbx / router / bugbot / traffic tracker (cacti) |
16:27.35 | jsmith | Eeeeew. |
16:27.40 | jsmith | That hurts. |
16:27.49 | blitzrage | yah |
16:27.56 | blitzrage | I had it working just the way I wanted it to |
16:27.58 | blitzrage | :) |
16:29.26 | blitzrage | ok... I better go have a shower, change, and clean my bedroom -- its trashed... |
16:29.39 | blitzrage | conference call in an hour and a half too |
16:31.07 | jsmith | You able to connect directly in, you you need to bounce through my server? |
16:42.13 | blitzrage | nah, I'm back home now |
16:42.54 | blitzrage | so I have physical access to the HD |
16:43.13 | blitzrage | found an extra HD in the kitchen -- so I'll grab Knoppix and mount the old drive hopefully |
17:10.07 | jsmith | You just had to start bragging, didn't ya :-) |
17:30.14 | blitzrage | you would too :D |
17:32.40 | jsmith | Shut up -- I'm allowed to brag, 'cuz I'm awesome :-) |
18:09.50 | blitzrage | lol |
18:09.52 | blitzrage | I can't deny that |
20:04.13 | jsmith | Back from a tasty lunch... |
20:09.10 | jsmith | You're a Leaf's fan? Man, that explains a lot... |
20:09.32 | blitzrage | Blackhawks?! |
20:09.35 | blitzrage | now that explains a lot |
20:09.37 | blitzrage | :D |
20:09.51 | blitzrage | of course I'm a Leaf's fan -- thats why I live in Toronto :) |
20:19.53 | jsmith | Yeah, or something like that :-) |
20:21.14 | blitzrage | hahaha |
20:21.25 | blitzrage | I cheer for whoever beats Ottawa in the playoffs |
20:21.32 | blitzrage | oh -- and whoever beats Philidelphia too |
20:22.06 | jsmith | At least you didn't say Pittsburg :-) |
20:24.05 | blitzrage | nah... nothing to worry about there :) |
20:25.33 | jsmith | (Yeah, they've been downright awful) |
20:26.01 | jsmith | I did take my son to a minor league hockey game earlier this year, and he won a contest and got an autographed hockey stick. |
20:31.11 | blitzrage | wicked! |
20:31.20 | blitzrage | btw, do you have the link to the asteriskdocs stats? |
20:32.31 | blitzrage | nevermind, found it! |
20:36.25 | blitzrage | daaaaamn we get a lot of hits |
20:36.30 | blitzrage | 18000 unique hits last month |
20:37.44 | jsmith | Yup. |
20:37.58 | blitzrage | nutty |
20:38.02 | blitzrage | 5900 so far this month |
20:38.05 | jsmith | We need some google ads, amazon affililate ads, etc. to buy us lunch :-) |
20:38.13 | blitzrage | and its only been 8 days :) |
20:38.15 | blitzrage | I agree! |
20:38.30 | blitzrage | with a percentage back to Greg for hosting for us |
20:39.07 | jsmith | I'm OK with that |
20:41.32 | blitzrage | Banner ID Impressions Imp. Left Clicks % Clicks Client Name Functions |
20:41.32 | blitzrage | 8 23829 Unlimited 181 0.759% O'Reilly Edit | Delete |
20:42.01 | blitzrage | anyways... 23000 impressions with 181 clicks isn't too bad... |
20:44.54 | jsmith | No, not at all... |
20:46.15 | blitzrage | off to get f00d! |
20:46.29 | blitzrage | then finish cleaning my room, watch the Leafs game, hang a shelf, and head to the hotel |
21:05.58 | *** join/#asterisk-doc blitzrage (n=blitzrag@asterisk/documenteur-extraordinaire/blitzrage) |
21:05.58 | *** mode/#asterisk-doc [+o blitzrage] by ChanServ |