Changes needed for classified ads
public
group: Postgresql
jpolt - Sun, 2007-04-22 12:23
Can anyone suggest what code is needed to enable classiified ads http://drupal.org/project/ed_classified to work on pgsql?
Here is part of the install program which needs updating
switch ($GLOBALS['db_type']) {
case 'mysql':
case 'mysqli':
drupal_set_message(t('Creating classified ads table'));
// the {tablename} syntax is so multisite installs can add a
// prefix to the table name as set in the settings.php file
$success = db_query("CREATE TABLE {edi_classified_nodes} (
nid int(10) unsigned NOT NULL auto_increment,
vid int(10) unsigned NOT NULL default '0',
expires_on int(10) unsigned NOT NULL default '0' COMMENT 'Unix timestamp of expiration',
expiration_notify_last_sent int(10) unsigned NOT NULL default '0',
PRIMARY KEY (nid,vid));"
);
break;
case 'pgsql':
watchdog('classified', 'install does not support postgres (pgsql)', WATCHDOG_ERROR);
drupal_set_message(t('This module does not support postgres at this time.','error'));
$success = FALSE;
break;
}It doesn't look like a lot of code to add - but that's only a guess - I can't code pgsql.
Suggestion for PostgreSQL
The schema is not very good IMNSHO from the beginning but a Postgresql version would be more or less something like :
CREATE TABLE {edi_classified_nodes} ( nid serial4 NOT NULL, vid int4 NOT NULL DEFAULT 0, expires_on int4 NOT NULL DEFAULT 0, expiration_notify_last_sent int4 NOT NULL DEFAULT 0, PRIMARY KEY (nid,vid)); COMMENT ON {edi_classified_nodes}.expires_on IS 'Unix timestamp of expiration';The last two int4 could also be char(10) or varchar(10) or even text. AFAIK PostgreSQL does not support UNSIGNED. If really needed (here I doubt so), it could be done with a constraint.
Thanks
Thanks for this - I'll give it a try, although I'm not sure where the COMMENT ON line should go...
2 queries
You would have one db_query() with the CREATE
and one db_query() with the COMMENT. But to me the COMMENT part is nearly useless, so the second db_query() could be omitted.
In short, something like
case 'pgsql': drupal_set_message(t('Creating classified ads table')); $success = db_query("CREATE TABLE {edi_classified_nodes} ( nid serial4 NOT NULL, vid int4 NOT NULL DEFAULT 0, expires_on int4 NOT NULL DEFAULT 0, expiration_notify_last_sent int4 NOT NULL DEFAULT 0, PRIMARY KEY (nid,vid))" ); db_query("COMMENT ON {edi_classified_nodes}.expires_on IS 'Unix timestamp of expiration'"); break;Errors encountered...:-(
This is what happened when I tried to create a classified ad:-
* warning: pg_query() [function.pg-query]: Query failed: ERROR: invalid input syntax for integer: "" in /usr/local/www/drupal5/includes/database.pgsql.inc on line 125.* user warning: query: SELECT n.nid, n.vid, n.type, n.status, n.created, n.changed, n.comment, n.promote, n.sticky, r.timestamp AS revision_timestamp, r.title, r.body, r.teaser, r.log, r.format, u.uid, u.name, u.picture, u.data FROM node n INNER JOIN users u ON u.uid = n.uid INNER JOIN node_revisions r ON r.vid = n.vid WHERE n.nid = '' in /usr/local/www/drupal5/includes/database.pgsql.inc on line 144.
* warning: pg_query() [function.pg-query]: Query failed: ERROR: invalid input syntax for integer: "" in /usr/local/www/drupal5/includes/database.pgsql.inc on line 125.
* user warning: query: SELECT n.nid, n.vid, n.type, n.status, n.created, n.changed, n.comment, n.promote, n.sticky, r.timestamp AS revision_timestamp, r.title, r.body, r.teaser, r.log, r.format, u.uid, u.name, u.picture, u.data FROM node n INNER JOIN users u ON u.uid = n.uid INNER JOIN node_revisions r ON r.vid = n.vid WHERE n.nid = '' in /usr/local/www/drupal5/includes/database.pgsql.inc on line 144.
* warning: pg_query() [function.pg-query]: Query failed: ERROR: invalid input syntax for integer: "" in /usr/local/www/drupal5/includes/database.pgsql.inc on line 125.
* user warning: query: SELECT n.nid, n.vid, n.type, n.status, n.created, n.changed, n.comment, n.promote, n.sticky, r.timestamp AS revision_timestamp, r.title, r.body, r.teaser, r.log, r.format, u.uid, u.name, u.picture, u.data FROM node n INNER JOIN users u ON u.uid = n.uid INNER JOIN node_revisions r ON r.vid = n.vid WHERE n.nid = '' in /usr/local/www/drupal5/includes/database.pgsql.inc on line 144.
Looks like I'll be giving pgsql a miss for the time being and get things migrated to MySQL.
Thanks for your efforts, but it seems like an uphill struggle to get this working.
Bad code
Your error means the code is trying to use '' (an empty string) for an integer (WHERE n.nid='') : this is authorized in MySQL not in PostgreSQL, and I believe the last one is the correct case, as soon as we have types such as in SQL, the SGBDR should enforce them. An empty string is never a valid integer.
So the code should be corrected not to do WHERE n.nid='' which is wrong from the start in my opinion. Sorry no to be able to help more.
Simple work around
I've solve this in the recipe module, by finding where the null value is coming and adding:
<?phpif(!isset($data)) {
$data=0;
}
?>
This gives it a 0 values, not the correct way, but works... Depends on the implementation.