PostgreSQL syntax is wrong
| Project: | Dependent Fields |
| Version: | 5.x-1.4-beta |
| Component: | Code |
| Category: | bug report |
| Priority: | critical |
| Assigned: | Unassigned |
| Status: | active |
Jump to:
This module will not install on a postgresql server.
* warning: pg_query() [function.pg-query]: Query failed: ERROR: syntax error at or near "not" LINE 1: CREATE TABLE if not exists field_dependencies ( ^ in C:\.........\includes\database.pgsql.inc on line 125.
* user warning: query: CREATE TABLE if not exists field_dependencies ( parent_field_name varchar(32) NOT NULL, child_field_name varchar(32) NOT NULL, parent_node_type_name varchar(32) NOT NULL, child_node_type_name varchar(32) NOT NULL, option_text varchar(255), put_after_parent boolean default 0, is_required boolean default 0, PRIMARY KEY(parent_field_name,child_field_name,parent_node_type_name,child_node_type_name) ) TYPE=MyISAM /*!40100 DEFAULT CHARACTER SET utf8 */; in C:\......\includes\database.pgsql.inc on line 144.
#1
The correct code for postgres is as follows:
db_query("CREATE TABLE {field_dependencies} (
parent_field_name varchar(32) NOT NULL,
child_field_name varchar(32) NOT NULL,
parent_node_type_name varchar(32) NOT NULL,
child_node_type_name varchar(32) NOT NULL,
option_text varchar(255),
put_after_parent int default 0,
is_required int default 0,
PRIMARY KEY(parent_field_name,child_field_name,parent_node_type_name,child_node_type_name)
)
");
put_after_parent and is_required need to be int, not bool, since you cannot assign a integer to a boolean.
#2
More problems. SQL should never use double quotes when referencing strings - this works in MySQL but is non-standard and breaks more strict SQL databases like postgreSQL. The code as it currently stands tells postgresql to check for a column named "options_onoff" etc - which doesnt exist and so returns an error. The following lines of code need to be changed as follows to replace all double-quotes as single quotes. This should make both postgreSQL and MySQL happy.
lines 165-173
if (isset($field_name)){
$onoff_field_instances=db_query("SELECT * FROM {node_field_instance} WHERE widget_type='options_onoff' and type_name='%s' and field_name<>'%s'", $content_types['parent'],$field_name );
$options_field_instances=db_query("SELECT nfi.field_name, nf.global_settings FROM {node_field_instance} nfi,{node_field} nf WHERE nfi.widget_type='options_buttons' and nfi.type_name='%s' and nfi.field_name=nf.field_name and nfi.field_name<>'%s'", $content_types['parent'], $field_name);
$selection_field_instances=db_query("SELECT nfi.field_name, nf.global_settings FROM {node_field_instance} nfi,{node_field} nf WHERE nfi.widget_type='options_select' and nfi.type_name='%s' and nfi.field_name=nf.field_name and nfi.field_name<>'%s'", $content_types['parent'], $field_name);
}else{
$onoff_field_instances=db_query("SELECT * FROM {node_field_instance} WHERE widget_type='options_onoff' and type_name='%s'", $content_types['parent']);
$options_field_instances=db_query("SELECT nfi.field_name, nf.global_settings FROM {node_field_instance} nfi,{node_field} nf WHERE nfi.widget_type='options_buttons' and nfi.type_name='%s' and nfi.field_name=nf.field_name", $content_types['parent']);
$selection_field_instances=db_query("SELECT nfi.field_name, nf.global_settings FROM {node_field_instance} nfi,{node_field} nf WHERE nfi.widget_type='options_select' and nfi.type_name='%s' and nfi.field_name=nf.field_name", $content_types['parent']);
}
line 207:
$prev_result=db_query("SELECT * FROM {field_dependencies} WHERE child_field_name='%s' AND parent_node_type_name='%s' AND child_node_type_name='%s'", isset($field_name)?$field_name:"related", $content_types['parent'],$content_types['child']);#3
Great module btw...
#4
Thank you very much. I'll update the code in the next release.
Best wishes,
Ofir.