Scoop || Postgres Scoop

Front Page · Everything · News · Code · Help! · Wishlist · Project · Scoop Sites · Dev Notes · Latest CVS changes · Development Activities

Postgres Scoop
By denshi , Section Code [] Posted on Fri Aug 31, 2001 at 12:00:00 PM PST

I have the beginnings of a Postgres-based Scoop running at http://www.toddbrandlabs.com:8080/scoop Obviously, it will be jumping up and down like a yo-yo over the next few days I need to make it solid, but I'm rather close. It's a fork off the 0.8 tarball so it's at least reasonably current.

Revisions thus far:

DDL changes on about half of the column types, switching from MySQL shorthands to SQL92 types. Similarly, KEY(..,..) defs inside tables move to CREATE INDEX statements.
auto_increment switched to sequences & triggers.
A goodly sized piece of work moving from MySQL date handling to Postgres, which is at least similar to SQL92. Dates in the SQL flavours are horribly incompatible. At least we get Oracle compat for free with Postgres dates.
table 'box' becomes 'scoop_box' since 'box' is a PG built-in type. Good design style would have all DDL objects prefixed with a common identifier, so that they may play nicely in a shared namespace/tablespace.
MySQL's enum is syntactic sugar on check constraints; that's changed.
DATE_FORMAT() is a MySQLism; Postgres & Oracle & others use TO_CHAR()
Obviously the procedural code above the data model is a simpler task. There are some gotchas I wasn't expecting, though:

Only MySQL uses double-quotes to enclose literals. I appeal to all here to stop with the double-quoting and follow the standard.
Dates, dates, dates. Gah!
This is what's done. Over the near future I will be doing some fairly interesting refactoring that should blow the door off kuro5hin's current performance constraints.
Code & patches -- where should I put them?? For the moment they'll accrue on my port 80 site, usually after I write up a bit of documentation.
Anyone have any suggestions, complaints, patches, flames? Post 'em.

< How do you get a provider that will deal with DOS attacks? | New Server >

Menu

· create account
· faq
· search
· report bugs
· Scoop Administrators Guide
· Scoop Box Exchange

Login

Make a new account

Username:

Password:

Poll

Do you want to see Scoop on Postgres?

· Yes, short-term. 30%

· Yes, long-term. 53%

· No, no, a thousand times no. 0%

· I like cheese. 15%

Votes: 13
Results | Other Polls

Related Links

· Scoop
· Kuro5hin
· More on Announcements
· Also by denshi

Story Views

178 Scoop users have viewed this story.

Postgres Scoop | 35 comments (35 topical, 0 hidden)

nested select statements. (none / 0) (#1)
by Defect on Fri Aug 31, 2001 at 06:28:08 AM PST

One thing to probably note, if scoop is going to be supporting other db's, is that there are a decent amount of unquoted values being used in select statements (most notably in the search code), and while mysql doesn't allow nested select statements, postgresql does (iirc).

While this is only a minor problem right now, it could be more of a pain in the ass with databases like postgresql. Just as an example, while searching, i could enter something like:

blah' OR aid IN (SELECT nickname FROM users WHERE perm_group='Admins') OR section='blah

for the topic and i could find out who on the site has Admin privileges (as long as they've submitted a story, regardless of whether or not it was dumped). There are more possibilities, but there's one example.

And that example is just off the top of my head, so it may not work right off the bat, but it's the right idea. Of course, i haven't looked at recent scoop releases so if more data is quoted before entered in statements then it won't be a problem.

We're changing the db_* functions by hurstdog, 08/31/2001 08:58:55 AM PST (none / 0)
Start a Daycare by hunnyleo9, 02/07/2012 07:41:26 AM PST (none / 0)

patches and code (none / 0) (#3)
by hurstdog on Fri Aug 31, 2001 at 09:13:40 AM PST

Leave them on your port 80 site, maybe email scoop-dev when you get a new one in. There is a variable in the example httpd.conf where you can choose the database type. Try to make sure you write everything not so that its just patches that people have to apply if they want to run postgres, but so that all they have to do is change that var in httpd.conf, install another module, and they're set.

On a related note, how does everyone think we should go about generating a scoop.sql file for multiple db's? We've tossed around the idea of a perl script that gets everything from the database and puts it in an xml format, then another perl script that will generate a sql file for the db of your choice from that.

Regarding the new db format that I mentioned in an earlier post, check out the archives Here and let me know what you think (that means everyone that codes scoop and/or has any kind of input, please ;-) Any of the articles on db quoting or vim are on topic with db's.

-hurstdog

done, sort of. (none / 0) (#4)
by denshi on Mon Dec 17, 2001 at 03:20:40 PM PST

You might have noticed that I haven't posted in over three months. In explaining that, I must relate an epic tale of loss, travel, adventure, and small furry mammals that go for your ankles. But I'll just jump to the code -- I opened up my scoop notes for the first time since September, and finished the scoop-0.8.0 port.

Here are some links to a unified diff of the 'lib/' dir of Scoop from my internal CVS tree (which does not match official version numbers, so it may not work immediately, but you can hack around with cvs to work it); also there is a straight tarball of a working site attached as well. If you didn't get the last tarball you should get this one, as the schema (scoop.sql) changes aren't in the diff file.

It's not *entirely* done. I have to fix the RDF system that my schema change broke, and I have patch a few more date issues. But the damn thing works for me. I also have to fix up the docs and add a clause to the setup scripts to install the DBD::Pg and Apache::Session::Postgres modules. But a savvy admin can run this with no trouble.

Oracle and Postgres are very, very close in both syntax and semantics. I can trivially port to Oracle afterwards, but I am prioritizing other things above that right now. First among them is DB independence -- this patch set is non-operable with MySQL.

The public test site has been down since I changed hosting providers, but I might be able to bring another one up soon if there is any need. In the meantime, pg_scoop can be found on www.toddbrandlabs.com/code.

a big scoop of cheers! by sye, 12/21/2001 07:53:36 AM PST (none / 0)
PG Scoop by sye, 12/22/2001 08:20:50 AM PST (none / 0)
Not found by juahonen, 04/16/2002 10:19:09 AM PST (none / 0)

Are you going to sync with 0.9? (none / 0) (#8)
by Jonathan Walther on Sat Dec 27, 2003 at 06:40:24 PM PST

Denshi, congratulations, and thank you! I have been wanting a Postgres version of scoop for two years now.

I really like the changes Rusty made to Kuro5hin in October, where the comment rating system was changed and the whole trusted user/mojo blarney was blasted away.

Will you be syncing with that version of the code soonish?

I applaud your desire for database independance; is that a higher priority for you right now than syncing with -CURRENT?

RV Supplies by hunnyleo9, 02/07/2012 05:46:10 AM PST (none / 0)

Where's updated info (none / 0) (#9)
by mp3desire on Tue Feb 26, 2008 at 12:10:53 PM PST

Where can we find the updated info on this problem? Could you please help me to find it out.

Scoop (none / 0) (#10)
by tiny on Wed Feb 04, 2009 at 01:53:53 AM PST

scoop is cool, free essential software

To Tiny by Alexer, 02/08/2009 10:08:21 PM PST (none / 0)

Reply (none / 0) (#12)
by bynes69 on Mon Oct 24, 2011 at 06:22:26 AM PST

Thanks for sharing. i really appreciate it that you shared with us such a informative post.. Custom Research Paper Custom Term Paper custom essay

Reply (none / 0) (#13)
by bynes69 on Mon Oct 24, 2011 at 07:22:39 AM PST

Custom Thesis Custom Dissertation

hi (none / 0) (#14)
by mrmill on Thu Nov 03, 2011 at 04:50:26 AM PST

Enjoyed every bit of your website.Thanks Again. Great. H-Beam Section Steel

great work (none / 0) (#15)
by jb4522jb on Thu Nov 03, 2011 at 11:30:44 AM PST

I am glad to see the great work going on here. I think this will work so good. I see so much from it. brain injury lawyers orlando

patches (none / 0) (#16)
by evasmith8812 on Thu Nov 10, 2011 at 12:00:48 PM PST

Thanks for all your work on this project. I know that it can be a daunting task. I am not sure where you post the patches, do you know if there is a dedicated server for this purpose? I would also be interested to know how this process works.

Re: (none / 0) (#17)
by rrgan12 on Fri Nov 25, 2011 at 05:36:23 PM PST

Interesting to read this great article indeed because I have known many great and new things from you. Thanks a lot one more time.
-compare van insurance

tnien (none / 0) (#18)
by scooby3 on Sun Jan 01, 2012 at 04:52:20 PM PST

this thread was really useful for me free stuff online

Yeah by reverse02, 01/08/2012 09:36:51 PM PST (none / 0)

love (none / 0) (#19)
by scooby3 on Sun Jan 01, 2012 at 04:53:11 PM PST

I love you guys have a great new year ... buy backlinks - free dating websites

You know (none / 0) (#21)
by superking on Tue Jan 17, 2012 at 05:55:26 AM PST

You know your projects stand out of the herd. There is something special about them. It seems to me all of them are really brilliant! payday UK

This is really (none / 0) (#22)
by superking on Wed Jan 18, 2012 at 02:18:29 AM PST

I think this is an informative post and it is very useful and knowledgeable. therefore, I would like to thank you for the efforts you have made in writing this article. payday advance online

Wonderful (none / 0) (#23)
by superking on Sat Jan 21, 2012 at 06:39:03 AM PST

Wonderful illustrated information. I thank you about that. No doubt it will be very useful for my future projects. Would like to see some other posts on the same subject! Orlando Web Design

fat loss (none / 0) (#26)
by valeika on Tue Feb 07, 2012 at 05:32:55 PM PST

I am a total noob with it but this is really usefu for starters hcg

ejaom (none / 0) (#27)
by valeika on Tue Feb 07, 2012 at 05:33:57 PM PST

hey free hook up sites free internet dating sites free dating sites

Your website (none / 0) (#28)
by tomtim63 on Wed Feb 08, 2012 at 08:27:18 AM PST

Your website is really cool and this is a great inspiring article. bad credit loans

school grants for women (none / 0) (#29)
by schoolgrantsforwomen on Sat Feb 11, 2012 at 04:58:16 AM PST

I like this my friend, keep it up (none / 0) (#30)
by TheGateKeeper on Sat Feb 11, 2012 at 07:53:03 AM PST

Amazingly good blog bro, reminds me of Self Defense Laws, or maybe heavy bag workout.Greetings from stretches for hamstring. Do us a favor and keep this updated and never take this blog offline. Also, check out Muay Thai Camps in Thailand.

The information (none / 0) (#31)
by bigman on Sun Feb 19, 2012 at 08:55:47 AM PST

The information you have posted is very useful. The sites you have referred was good. Thanks for sharing. honda ฮอนด้า

Re: (none / 0) (#32)
by davesteve12 on Mon Feb 20, 2012 at 02:04:12 PM PST

Just what I was looking for and quite thorough as well. Thanks for posting this, I saw a couple other similar posts but yours was the best so far. I hope it stays updated, take care.
-cheap motor trade insurance

duoe (none / 0) (#33)
by jiumei on Tue Feb 21, 2012 at 08:11:03 PM PST

This is a very well written post. Your writing style is outstanding here and I feel like you touched on a bunch of very important points.Cassava Flour Mill

Excellent (none / 0) (#34)
by bigman on Wed Feb 22, 2012 at 07:01:25 AM PST

Excellent article. Very interesting to read. I really love to read such a nice article. Thanks! keep rocking. Dan's Great HGH Site

Thank (none / 0) (#35)
by king on Wed Feb 22, 2012 at 09:24:13 AM PST

Thank you for taking the time to publish this information very useful! EyelashGrowthProducts101.com

Postgres Scoop | 35 comments (35 topical, 0 hidden)

create account | faq | search