Conversation
Notices
-
Did I do this right? ` replyto bigint CHECK (replyto IS NOT postid AND replyto IS CASE WHEN conversation IS NOT postid THEN NOT NULL),` !coderpony #SQL
Tuesday, 08-May-12 20:19:16 UTC from web-
@toksyuryel For a second there I thought you were trying to do an SQL injection attack on RDN.
-
@bitshift I hope it's not possible to do one of those in a post, it would make it rather difficult to post examples of what I'm trying to do to get help with them! But this is statusnet we're talking about here... hm. Maybe I'll start using dpaste for these instead.
-
@toksyuryel I _think_ even status.net shouldn't be vulnerable to SQL injection, but I've learned not to make assumptions when it comes to SN.
-
@bitshift As long as I'm using dpaste I might as well just post the entire file. Let me know how much I screwed everything up! http://dpaste.com/745106/ !coderpony #SQL
-
@toksyuryel Well, one possible issue I spotted so far: your users foreign key in posts should be userid, not username, since a) you have ID as your primary key in users, and b) you might well want to make name changes possible, and userid will be the thing that remains constant through such changes.
-
@toksyuryel Wouldn't creating a unique key on group names be sufficient to ensure uniqueness? Is that sort function really necessary? o.o
-
@bitshift Good catch, lemme go fix that. It already worked as you described, but I used the wrong name for it (it was orignally just named 'user' but the syntax highlighter highlighted it so I assumed it had some special meaning and changed it)
-
@toksyuryel And for the same general reason, mentions, tags, and groups should probably be bigserial[] instead of text[] (again, reference the IDs, not the names - especially for groups, since they have aliases, but all aliases should still all point to the same thing).
-
@toksyuryel Actually it didn't work that way, because it was a text type... I guess I just completely blanked on that XD so, yes thanks very much for the catch @bitshift!
-
@bitshift bigint[] actually, but yes you are correct. Considering my intent was always to reference the ids I do not know how I blanked on this. I guess I was just planning to put the numbers in as strings and compared them that way? I did most of this while I was half-asleep so I guess that's why.
-
@sleekrush @toksyuryel Wait nevermind, now I see what's going on. I'll just go back to !lurking #notadba
-
@toksyuryel Hm, wait, no. Tags _should_ be text[] (because tags don't _have_ IDs, and shouldn't), but the rest could still do wit the change.
-
@bitshift tags however should stay text[] as these will always be referenced by names not ids (tag names are always static)
-
@toksyuryel @bitshift Man I really should look into PostgreSQL. :/
-
@sleekrush It would be if those worked on array elements =/ unfortunately it is not so simple. Need to make sure each names[] element does not appear in any other names[] in the groups table and the only way to do that is by constructing a unique index on each element.
-
@toksyuryel Ah, one last one. Admin on groups should either reference userid, or a list of user IDs (the latter would allow multiple group admins, much like status.net does, but would be more code, and would be of potentially dubious benefit).
-
@minti You really should.
-
@bitshift I forgot groups could have multiple admins... crap.
-
@bitshift http://dpaste.com/745125/ here's what it looks like now !coderpony #SQL
-
@toksyuryel Apart from the need to constrain the arrays of IDs (which you've already noted, and which I'm unsure how to solve anyway), looks good to me. :)
-
@bitshift I seriously got that replyto constraint *right*? Awesome :D
-
@toksyuryel Well, it at least looks like it should be right. There might be some subtle syntax weirdness there, but I'm pretty sure it's fine.
-
@bitshift I believe trigger functions might be usable to create those dynamic indices at the bottom. Gonna have to learn how to make those now!
-
@toksyuryel Fun!
-
@bitshift Let's see how horribly I botched this one up http://dpaste.com/745169/ !coderpony #SQL
-
@bitshift Also it seems like trigger functions can be used to constrain those arrays I've been having trouble with.
-
@toksyuryel Excellent. Will take a look over the newest paste in a second, then. :)
-
@toksyuryel Hm, well, everything certainly looks okay. :)
-
@bitshift Either I'm better at this than I thought or we're both learning it together :D we'll know which it is for sure as soon as I get around to finishing my system reinstall process so I can actually test all of this
-
@toksyuryel I'll keep my eyes peeled for any "horrific server explosion devastates Seattle home" news stories. :P
-
@bitshift I have stubbed out several functions and created the triggers to demonstrate how I plan to constrain the arrays http://dpaste.com/745194/ !coderpony #SQL
-
@toksyuryel @bitshift I realized I forgot some conditionals in one of the stubbed functions, here's a fixed version of that http://dpaste.com/745201/ !coderpony #SQL
-
@bitshift So how'd I do? :) http://dpaste.com/745233/ !coderpony #SQL
-
