Question about default NULL and NOT NULL

pokorra

Hello Christian (@[deleted]) and Wolfgang (@[deleted]),

I tried to run the server tests against mysql again, and found this situation, which does not fail on Postgresql, but on Mysql:

https://dbdoc.openpetra.org/index.html?table=p_partner_reminder&group=partner#p_contact_id_i

in the createtables-PostgreSQL.sql it looks like this:

-- ID of Contact to which reminder relates. Null means that reminder relates just to a Partner
p_contact_id_i integer DEFAULT NULL NOT NULL,

Is this what we want?
or do should it default to 0 instead of null, more like p_reminder_id_i.

Same situation in https://dbdoc.openpetra.org/index.html?table=s_group_partner_reminder&group=sysman#p_contact_id_i

Thanks,
Timo

pokorra

I justed tested this with Postgresql:

openpetra=# select * from p_partner_reminder;
 p_partner_key_n | p_contact_id_i | p_reminder_id_i | s_user_id_c | p_category_code_c | p_action_type_c | p_reminder_reason_c 
| p_comment_c | p_event_date_d | p_first_reminder_date_d | p_reminder_frequency_i | p_last_reminder_sent_d | p_next_reminder_d
ate_d | p_reminder_active_l | p_email_address_c | p_restricted_l | s_module_id_c | s_user_restriction_c | s_date_created_d | s
_created_by_c | s_date_modified_d | s_modified_by_c | s_modification_id_t 
-----------------+----------------+-----------------+-------------+-------------------+-----------------+---------------------
+-------------+----------------+-------------------------+------------------------+------------------------+------------------
------+---------------------+-------------------+----------------+---------------+----------------------+------------------+--
--------------+-------------------+-----------------+---------------------
(0 rows)

openpetra=# insert into p_partner_reminder(p_partner_key_n) values (0);
ERROR:  null value in column "p_contact_id_i" violates not-null constraint
DETAIL:  Failing row contains (0, null, 0, null, null, null, null, null, null, null, 0, null, null, t, null, f, null, null, 2016-10-26, null, null, null, null).

so it seems the DEFAULT NULL does not have any effect. It fails anyway on the NOT NULL.

pokorra

another issue related to this:

MySQL has problems with p_partner_action_fk6 of table p_partner_action to table p_partner_reminder (https://dbdoc.openpetra.org/index.html?table=p_partner_action&group=partner), because p_partner_action.p_contact_id_i is of type integer and p_partner_reminder.p_contact_id_i is of type bigint aka number(10).

I suggest to fix both places where p_contact_id_i is typed with integer: https://github.com/tpokorra/openpetra/commit/a311087ae8d44bcf0795c8cd0ae5c06bc6f2e166

pokorra

and another issue with a foreign link, where the type does not match:

https://dbdoc.openpetra.org/index.html?table=p_partner_attribute&group=partner#p_value_country_c
has varchar(400), but https://dbdoc.openpetra.org/index.html?table=p_country&group=common#p_country_code_c only has 8 characters

I guess I will file a merge request for Launchpad, once I have all fixes to petra.xml ready?

Dave

DEFAULT NULL is redundant anyway, since this is what PG automatically gives you if you don't specify a value at INSERT/UPDATE:

petraserver=> create table t(a integer, b integer);
CREATE TABLE

petraserver=> insert into t(a) values(1);
INSERT 0 1

petraserver=> select coalesce(a,-1),coalesce(b,-2) from t;
coalesce | coalesce
----------+----------
1 | -2
(1 row)

If you specify NOT NULL this means it will throw an exception:

petraserver=> create table t1(a integer,b integer not null);
CREATE TABLE

petraserver=> insert into t1(a) values(1);
ERROR: null value in column "b" violates not-null constraint
DETAIL: Failing row contains (1, null).

pokorra

I have fixed all these issues in the Open Source OpenPetra and in the ICT OpenPetra.

nant test-without-display runs now fine on a MySQL Database, see nightly test job: https://lbs.solidcharity.com/package/tpokorra/openpetra/openpetra-test-server-mysql