Flourish PHP Unframework
This is an archived copy of the forum for reference purposes

fValidation throws an exception when inserting a new object in Oracle (index is filled by a trigger + sequence)

posted by aurelien 9 years ago

Hello,

I surely have missed something in the documentation but I don't find what :)

I am inserting a new object in an Oracle Database.

It fails on an fValidation exception because the "user_id" column "cannot be null" (among other columns). Indeed. But I do not give the user_id on purpose, because it is automatically filled by a trigger + sequence, like mysql autoincrement feature.

What should I do to avoid this kind of behaviour?

I am using the last version of flourishlib.

BTW, other colums, which HAVE a default value, are also reported as requesting a value by fValidation.

Table is the following in Oracle : 


  CREATE TABLE "ITPNET"."USERS" 
   (	"USER_ID" NUMBER(10,0) NOT NULL ENABLE, 
	"USERNAME" VARCHAR2(45 BYTE) NOT NULL ENABLE, 
	"FIRSTNAME" VARCHAR2(45 BYTE) DEFAULT 'User' NOT NULL ENABLE, 
	"LASTNAME" VARCHAR2(45 BYTE) DEFAULT 'User' NOT NULL ENABLE, 
	"EMAIL" VARCHAR2(255 BYTE), 
	"ARCHIVED" NUMBER(3,0) DEFAULT 0 NOT NULL ENABLE, 
	"CREATED_AT" DATE DEFAULT SYSDATE NOT NULL ENABLE, 
	"MODIFIED_AT" DATE DEFAULT SYSDATE NOT NULL ENABLE, 
	"LOCATION" VARCHAR2(30 BYTE) DEFAULT 'HIDE' NOT NULL ENABLE, 
	"NUMLOGINS" NUMBER(10,0) DEFAULT 0, 
	 CONSTRAINT "PRIMARY_8" PRIMARY KEY ("USER_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "ITPNET"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "ITPNET" ;
 
  CREATE OR REPLACE TRIGGER "ITPNET"."USERS_USER_ID_TRG" BEFORE INSERT OR UPDATE ON users
FOR EACH ROW
BEGIN
  if inserting and :new.user_id is NULL then
  SELECT users_user_id_SEQ.nextval into :new.user_id FROM DUAL;
  end if;
END;
/
ALTER TRIGGER "ITPNET"."USERS_USER_ID_TRG" ENABLE;

Thanks for your help !

Can you paste the output of the following? 

fCore::expose(fORMSchema::retrieve()->getColumnInfo('users'));

There is code in fSchema that should detect USER_ID as being an "auto-incrementing" primary key since there is a trigger with the right structure.

Also, can you paste the output of: 

SELECT
	TRIGGER_BODY
FROM
	ALL_TRIGGERS
WHERE
	TRIGGERING_EVENT = 'INSERT' AND
	STATUS = 'ENABLED' AND
	TRIGGER_NAME NOT LIKE 'BIN$%' AND
	TABLE_NAME = 'USERS' AND
	OWNER = 'ITPNET'

This is the SQL query that fSchema uses to detect the triggers.

posted by wbond 9 years ago

First of all thanks for beeing so quick at helping :)

1st request, the columninfo : 

Array
(
    [user_id] => Array
        (
            [type] => integer
            [not_null] => {true}
            [placeholder] => %i
            [default] => {null}
            [valid_values] => {null}
            [max_length] => {null}
            [max_value] => {null}
            [min_value] => {null}
            [decimal_places] => {null}
            [auto_increment] => {false}
        )
  
    [username] => Array
        (
            [type] => varchar
            [max_length] => 45
            [not_null] => {true}
            [placeholder] => %s
            [default] => {null}
            [valid_values] => {null}
            [max_value] => {null}
            [min_value] => {null}
            [decimal_places] => {null}
            [auto_increment] => {false}
        )
  
    [firstname] => Array
        (
            [type] => varchar
            [max_length] => 45
            [default] => User
            [not_null] => {true}
            [placeholder] => %s
            [valid_values] => {null}
            [max_value] => {null}
            [min_value] => {null}
            [decimal_places] => {null}
            [auto_increment] => {false}
        )
  
    [lastname] => Array
        (
            [type] => varchar
            [max_length] => 45
            [default] => User
            [not_null] => {true}
            [placeholder] => %s
            [valid_values] => {null}
            [max_value] => {null}
            [min_value] => {null}
            [decimal_places] => {null}
            [auto_increment] => {false}
        )
  
    [email] => Array
        (
            [type] => varchar
            [max_length] => 255
            [not_null] => {false}
            [placeholder] => %s
            [default] => {null}
            [valid_values] => {null}
            [max_value] => {null}
            [min_value] => {null}
            [decimal_places] => {null}
            [auto_increment] => {false}
        )
  
    [archived] => Array
        (
            [type] => integer
            [default] => 0
            [not_null] => {true}
            [placeholder] => %i
            [valid_values] => {null}
            [max_length] => {null}
            [max_value] => {null}
            [min_value] => {null}
            [decimal_places] => {null}
            [auto_increment] => {false}
        )
  
    [created_at] => Array
        (
            [type] => date
            [default] => SYSDATE 
            [not_null] => {true}
            [placeholder] => %d
            [valid_values] => {null}
            [max_length] => {null}
            [max_value] => {null}
            [min_value] => {null}
            [decimal_places] => {null}
            [auto_increment] => {false}
        )
  
    [modified_at] => Array
        (
            [type] => date
            [default] => SYSDATE 
            [not_null] => {true}
            [placeholder] => %d
            [valid_values] => {null}
            [max_length] => {null}
            [max_value] => {null}
            [min_value] => {null}
            [decimal_places] => {null}
            [auto_increment] => {false}
        )
  
    [location] => Array
        (
            [type] => varchar
            [max_length] => 30
            [default] => HIDE
            [not_null] => {true}
            [placeholder] => %s
            [valid_values] => {null}
            [max_value] => {null}
            [min_value] => {null}
            [decimal_places] => {null}
            [auto_increment] => {false}
        )
  
    [numlogins] => Array
        (
            [type] => integer
            [default] => 0
            [not_null] => {false}
            [placeholder] => %i
            [valid_values] => {null}
            [max_length] => {null}
            [max_value] => {null}
            [min_value] => {null}
            [decimal_places] => {null}
            [auto_increment] => {false}
        )
  
)

The question is, about the above data : Won't there be an issue by trimming the quotes from the oracle default values? For example, Firstname default value in oracle is not "(string)User", it is actually "(string)'User'". A contrario, created_at default value is "(string)SYSDATE" because this is a function, and need not to be quoted when put in a SQL sentence.

Second one, the SQL, returned no rows. After investigating a bit, it appears that the triggers wher put on TRIGGER_EVENT = 'INSERT OR UPDATE', thus failing to meet the criterion. After modifying the SQL with TRIGGERING_EVENT LIKE '%INSERT%' , the result was OK.

posted by aurelien 9 years ago

Thanks for the info, that should help me to get this fixed.

In terms of the string, the stripping of the quotes is correct, since fSchema treats the default value as a constant. It does appear that I am going to have to find a way to handle dynamic values such as SYSDATE. Similar constructs exist for other databases too, so it is something I should be handling anyway. Perhaps I can provide either a native PHP datatype, or a callback. That way for SYSDATE I can provide a PHP callback for time(), or something like that.

I will also need to change that SQL statement to look for both INSERT and INSERT OR UPDATE.

posted by wbond 9 years ago

Both of these issues should be fixed in r946. The triggers should be detected properly, both in fDatabase and fSchema.

For SYSDATE support, I remembered that fDate, fTime and fTimestamp support the CURRENT_DATE, CURRENT_TIME and CURRENT_TIMESTAMP constants as aliases for time(). I just tweaked fSchema to convert SYSDATE to CURRENT_DATE, and it look like you should be all set!

posted by wbond 9 years ago