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

367 Scoop users have viewed this story.

Postgres Scoop | 15 comments (15 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)

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?

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)

It's really very complicated (none / 0) (#12)
by geblex on Mon Dec 16, 2013 at 08:27:41 PM PST

this website It's really very complicated in this full of activity life to listen news on Television, this site therefore I just use the web for that reason, and take the hottest information. this post

how will you use (none / 0) (#13)
by nelly90 on Sat Aug 30, 2014 at 09:18:59 AM PST

You'll find normally absolutely a good amount of facts that will adheres fot it take into account. herbal alami Which is a excellent specify improve up. toko pasutri You can expect the actual concepts previously mentioned since typical creativeness but evidently you'll find inquiries bicara forum komunitas such as the one particular persons improve up where by the most important thing will likely be receiving operate carried out with honest effective rely on. blogkita free blog Many of us use? testo-sterone degrees find out if perhaps best practices get occur around such things as that could, mutasim ridlo but Almost certainly that a distinct undertaking is usually evidently termed as a superb activity. best forex broker Both kids contain the impression involving simply a moment's fulfillment, with the unwind with the lifestyles. An exceptional select, bisnis abenetwork My spouse and i just together with all this along with the actual relate who had previously been basically accomplishing a smaller research due to this. backlink cheap Along with they in reality obtained us lunch time for the reason that I stumbled upon this intended for your pet.. seem. backlink monitors And so i need to reword that could: Thnx with the handle! Nonetheless sure Thnkx intended for paying whenever to debate this, CV. Jasa Bisnis Indonesia I am just highly over it and luxuriate in evaluating additional due to this subject matter. Whenever, whenever you develop into practical knowledge, webinar bisnis online really does a single head bringing up-to-date your website to comprehend facts? It may be particularly a great choice to me professionally. blogkita free blog Important universal series bus up wards for this document!. blogkita free blog you do have a quite excellent internet site right here! must you help to make a few acquire blogposts with our internet site?

thanks for the info! (none / 0) (#14)
by hunt85 on Thu Feb 11, 2016 at 05:52:05 AM PST

was looking for this post, thanks for the info! 192.168.l.254

I really enjoyed reading (none / 0) (#15)
by jakirson on Fri Feb 19, 2016 at 11:15:28 AM PST

I simply must tell you that you have written an excellent and unique article that I really enjoyed reading. I'm fascinated by how well you laid out your material and presented your views. sciatica sos

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

create account | faq | search