Terse little DB, isn't it? 'preciate it. -- dz On 3/9/06, Keegan Quinn <keegan@thebasement.org> wrote:

On Thu, Mar 09, 2006 at 05:37:06AM -0500, Dan Ziemecki wrote:

...

Can I get one of you postgre aficionados to translate this SQL statement into whatever works on postgre for me?

Yeah.

...

DROP TABLE IF EXISTS subscriptions_holding;

Not sure about that "if exists" clause.

DROP TABLE subscriptions_holding; works though.

...

CREATE TABLE subscriptions_holding ( rid int(11) NOT NULL auto_increment COMMENT 'Unique row ID', content blob NOT NULL COMMENT 'The node array', type tinytext NOT NULL COMMENT 'New node or comment', op tinytext NOT NULL COMMENT 'The operation on the node', uid int(11) NOT NULL default '0' COMMENT 'The person performing the operation', PRIMARY KEY ('rid') );

CREATE TABLE subscriptions_holding ( rid SERIAL, content OID NOT NULL, type TEXT NOT NULL, op TEXT NOT NULL, uid INTEGER NOT NULL DEFAULT 0 ); COMMENT ON COLUMN subscriptions_holding.rid IS 'Unique row ID'; COMMENT ON COLUMN subscriptions_holding.content IS 'The node array'; COMMENT ON COLUMN subscriptions_holding.type IS 'New node or comment'; COMMENT ON COLUMN subscriptions_holding.op IS 'The operation on the node'; COMMENT ON COLUMN subscriptions_holding.uid IS 'The person performing the operation';

Tested on PostgreSQL 7.4.

HTH,

-- Keegan Quinn <keegan@thebasement.org> CEO, Producer the basement productions http://www.thebasement.org

-----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFEEAd1RMUr35HwZXoRAvnhAJ9GplHEFxZllWJxzkklWMdA9kEFegCdHUy+ 9MqSltxpHgT7J5RvSM3c3CE= =mfL1 -----END PGP SIGNATURE-----

I've used 'text' for content, as I think it will contain text only data

(serialized php variables). If you need binary data, then use bytea, but you'll have to use db_{encode,decode}_blob() functions.

If 'text' works for serialized objects, then that's probably what I need. Maybe in mySQL, too. I was just concerned that a text field would top out at 255 characters, and I wasn't sure how many I'd end up with. I've also added default values because iirc mysql will insert '' if

there is no default and no value is provided.

Good point. Shouldn't be an instance where there *isn't* a value, but it's a good habit. I would suggest changing 'uid' name to something else, as it's reserved

name.

Good point. Is 'type" ok? I've seen it elsewhere (e.g. Nodes) but I wasn't sure...

COMMENT ON COLUMN subscriptions_holding.rid IS 'Unique row ID'; ...

...

COMMENT ON COLUMN subscriptions_holding.uid IS 'The person performing the operation';

Those are also good, if you really want comments.

Why wouldn't you? Is there a cost? Otherwise, anything that explains what's going on can only help in later troubleshooting, no? -- Dan Ziemecki Philosopher. Curmudgeon. Nerd.

On Fri, Mar 10, 2006 at 02:15:23PM +0100, Piotr Krukowiecki wrote:

http://www.petefreitag.com/tools/sql_reserved_words_checker/

Peter Gulutzan wrote a great article named "SQL Naming Conventions" that includes all reserved words http://www.dbazine.com/db2/db2-disarticles/gulutzan5 --Dan -- T H E A N A L Y S I S A N D S O L U T I O N S C O M P A N Y data intensive web and database programming http://www.AnalysisAndSolutions.com/ 4015 7th Ave #4, Brooklyn NY 11232 v: 718-854-0335 f: 718-854-0409

On Mon, Mar 13, 2006 at 12:16:05PM -0500, Daniel Convissor wrote:

On Thu, Mar 09, 2006 at 04:09:37PM +0100, Piotr Krukowiecki wrote:

...

I've also added default values because iirc mysql will insert '' if there is no default and no value is provided.

If a default is not specified, the query will fail if the given column is not directly specified by the insert query.

mysql 4.1.11 - either the default is allways added (i.e. you can't specify a column without a default) or if there is no default, the empty string is inserted: mysql> create table foobar (a varchar(10) NOT NULL, b int NOT NULL); Query OK, 0 rows affected (0.02 sec) mysql> describe foobar; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | a | varchar(10) | | | | | | b | int(11) | | | 0 | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> insert into foobar(b) values(10); Query OK, 1 row affected (0.00 sec) mysql> select concat('x', a, 'x'), concat('x', b, 'x') FROM foobar; +---------------------+---------------------+ | concat('x', a, 'x') | concat('x', b, 'x') | +---------------------+---------------------+ | xx | x10x | +---------------------+---------------------+ 1 row in set (0.00 sec) mysql> select * FROM foobar WHERE a IS NULL; Empty set (0.02 sec) -- Piotrek irc: #debian.pl Mors Drosophilis melanogastribus!