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.
Tuesday, 08-May-12 20:22:28 UTC from web-
@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
Tuesday, 08-May-12 20:34:16 UTC from web-
@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.
Tuesday, 08-May-12 20:37:22 UTC from web-
@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)
Tuesday, 08-May-12 20:39:22 UTC from web-
@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!
Tuesday, 08-May-12 20:40:43 UTC from web
-
-
-
@toksyuryel Wouldn't creating a unique key on group names be sufficient to ensure uniqueness? Is that sort function really necessary? o.o
Tuesday, 08-May-12 20:38:08 UTC from web-
@sleekrush @toksyuryel Wait nevermind, now I see what's going on. I'll just go back to !lurking #notadba
Tuesday, 08-May-12 20:42:39 UTC from web-
@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.
Tuesday, 08-May-12 20:47:02 UTC from web
-
-
-
@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).
Tuesday, 08-May-12 20:40:19 UTC from web-
@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.
Tuesday, 08-May-12 20:42:27 UTC from web-
@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.
Tuesday, 08-May-12 20:43:47 UTC from web
-
-
@bitshift tags however should stay text[] as these will always be referenced by names not ids (tag names are always static)
Tuesday, 08-May-12 20:44:18 UTC from web-
@toksyuryel @bitshift Man I really should look into PostgreSQL. :/
Tuesday, 08-May-12 20:46:56 UTC from web-
Tuesday, 08-May-12 20:47:52 UTC from web
-
-
@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).
Tuesday, 08-May-12 20:47:18 UTC from web-
@bitshift I forgot groups could have multiple admins... crap.
Tuesday, 08-May-12 20:47:56 UTC from web -
@bitshift http://dpaste.com/745125/ here's what it looks like now !coderpony #SQL
Tuesday, 08-May-12 20:50:47 UTC from web-
@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. :)
Tuesday, 08-May-12 20:53:57 UTC from web-
@bitshift I seriously got that replyto constraint *right*? Awesome :D
Tuesday, 08-May-12 20:58:03 UTC from web-
@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.
Tuesday, 08-May-12 20:59:20 UTC from web-
@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!
Tuesday, 08-May-12 21:03:45 UTC from web-
@toksyuryel Fun!
Tuesday, 08-May-12 21:06:58 UTC from web-
@bitshift Let's see how horribly I botched this one up http://dpaste.com/745169/ !coderpony #SQL
Tuesday, 08-May-12 22:09:18 UTC from web-
@toksyuryel Hm, well, everything certainly looks okay. :)
Tuesday, 08-May-12 22:22:58 UTC from web-
@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
Tuesday, 08-May-12 22:24:44 UTC from web-
@toksyuryel I'll keep my eyes peeled for any "horrific server explosion devastates Seattle home" news stories. :P
Tuesday, 08-May-12 22:26:09 UTC from web
-
-
-
-
@bitshift Also it seems like trigger functions can be used to constrain those arrays I've been having trouble with.
Tuesday, 08-May-12 22:13:58 UTC from web-
@toksyuryel Excellent. Will take a look over the newest paste in a second, then. :)
-
@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
Tuesday, 08-May-12 23:05:16 UTC from web-
@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
Tuesday, 08-May-12 23:10:58 UTC from web
-
-
@bitshift So how'd I do? :) http://dpaste.com/745233/ !coderpony #SQL
Wednesday, 09-May-12 00:47:20 UTC from web
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
