Jump to:
| Project: | Links Package |
| Version: | master |
| Component: | Code |
| Category: | task |
| Priority: | normal |
| Assigned: | mok000 |
| Status: | reviewed & tested by the community |
Issue Summary
I am playing with the links module on my postgresql installation of Drupal. I ported the links.mysql script to links.pgsql. Here it is:
BEGIN;
DROP TABLE links;
DROP TABLE links_node;
DROP TABLE links_monitor;
-- lid, the link ID, is linked to the sequence links_lid
CREATE TABLE links (
lid INT4 NOT NULL DEFAULT '0',
url_md5 varchar(32) NOT NULL DEFAULT '',
url TEXT DEFAULT '' NOT NULL,
link_title varchar(255) NOT NULL DEFAULT '',
last_click_time INT4 NOT NULL DEFAULT '0',
PRIMARY KEY (lid)
);
-- lid is a foreign key to links
CREATE TABLE links_monitor (
lid INT4 NOT NULL DEFAULT '0',
check_interval INT4 NOT NULL DEFAULT '0',
last_check INT4 NOT NULL DEFAULT '0',
fail_count INT4 NOT NULL DEFAULT '0',
alternate_monitor_url text,
redirect_propose_url text,
redirect_saved_url text,
change_threshold INT4 NOT NULL DEFAULT '0',
change_flag INT4 NOT NULL DEFAULT '0',
change_last_data text,
PRIMARY KEY (lid)
);
-- lid is a foreign key to links
-- The interval defaults to 1 week
CREATE TABLE links_node (
lid INT4 NOT NULL DEFAULT '0',
nid INT4 NOT NULL DEFAULT '0',
link_title varchar(255) NOT NULL DEFAULT '',
weight INT4 NOT NULL DEFAULT '0',
clicks INT4 NOT NULL DEFAULT '0',
module varchar(100) NOT NULL DEFAULT '',
PRIMARY KEY (lid,nid,module)
);
COMMIT;
Comments
#1
Thanks! I'll get this committed to CVS within the next day or so.
Scott
#2
Committed into CVS. Thanks, mok000!
Scott
#3
In the meantime, I have made a few improvements on the pgsql code. Mainly, lid in links is now
declared SERIAL, which also generates a sequence for it, and second, the FOREIGN KEYs
have been defined properly in links_node and links_monitor.
I have not yet tested the schema in extremes, as I have yet to figure out how to use the links module
for my purpose -- basically, I need a way to input/delete/modify links w/o using the links_related module.
BEGIN;
DROP TABLE links;
DROP TABLE links_node;
DROP TABLE links_monitor;
-- lid, the link ID, is linked to the sequence links_lid
CREATE TABLE links (
lid SERIAL NOT NULL,
url_md5 varchar(32) NOT NULL DEFAULT '',
url TEXT DEFAULT '' NOT NULL,
link_title varchar(255) NOT NULL DEFAULT '',
last_click_time INTEGER NOT NULL DEFAULT '0',
PRIMARY KEY (lid)
);
-- lid is a foreign key to links
CREATE TABLE links_monitor (
lid INTEGER NOT NULL DEFAULT '0',
check_interval INTEGER NOT NULL DEFAULT '0',
last_check INTEGER NOT NULL DEFAULT '0',
fail_count INTEGER NOT NULL DEFAULT '0',
alternate_monitor_url text,
redirect_propose_url text,
redirect_saved_url text,
change_threshold INTEGER NOT NULL DEFAULT '0',
change_flag INTEGER NOT NULL DEFAULT '0',
change_last_data text,
PRIMARY KEY (lid),
CONSTRAINT links_exists
FOREIGN KEY (lid) REFERENCES links(lid) ON UPDATE CASCADE
);
-- lid is a foreign key to links
CREATE TABLE links_node (
lid INTEGER NOT NULL DEFAULT '0',
nid INTEGER NOT NULL DEFAULT '0',
link_title varchar(255) NOT NULL DEFAULT '',
weight INTEGER NOT NULL DEFAULT '0',
clicks INTEGER NOT NULL DEFAULT '0',
module varchar(100) NOT NULL DEFAULT '',
PRIMARY KEY (lid,nid,module),
CONSTRAINT links_exists
FOREIGN KEY (lid) REFERENCES links(lid) ON UPDATE CASCADE
);
COMMIT;
#4
Good evening!
Thanks for another good patch. :-)
I am curious about one aspect of your updated patch. How does the automatic increment of the ID number interact with Drupal's use of sequences that are implemented in code? MySQL can do auto_increment integer columns, which is equivalent to what you've done. So it would be a simple matter to use that feature in both the MySQL and PostgreSQL versions. So far so good; we just remove the use of Drupal's sequence function from the links module code.
But what about supporting other databases that may not have automatic increment support for integers? I'm not sure if MS SQL Server, DB2, or Oracle support this feature currently (I've used all three of these, but not recently). I know from reading the developer list that there are a few who use these. As an Open Source advocate, I think we should try to be as open as possible to freedom of choice of database engine. :-)
The remainder of your patch, i.e., the FOREIGN KEYS and so on, is a definite "go" as far as I'm concerned. I'm not against the auto increment patch, either. This question is meant to seek information because I'm not familiar with PSQL's implementation of this feature, and not to criticize what you've done.
Please let me know the rationale for your decision to do this. I'll definitely accept the other changes, and probably this one as well, but simply want to make sure I understand it completely because I'm not a PSQL expert.
Thanks again for your contribs.
Scott