Can't delete rows from table wit GUID primary key

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Can't delete rows from table wit GUID primary key

Erik Heemskerk
Hi,

I'm a happy user of flamerobin, but I have a small problem now:
I have a few tables in which GUID's are used as primary keys. For some
reason I am unable to delete rows from these tables with flamerobin
(0.9.3.2091). I can select them and mark them for removal, but after a
commit the rows are still present. I don't have this issue on tables with
integer primary keys. Also writing and executing the delete statement
manually will remove the rows.

Is this a feature or a bug?

I use flamerobin against Firebird 2.5.1.


Script which creates a table that shows the problem:
/********************* ROLES **********************/

/********************* UDFS ***********************/

/****************** GENERATORS ********************/

/******************** DOMAINS *********************/

/******************* PROCEDURES ******************/

/******************** TABLES **********************/

CREATE TABLE GUIDDEMO
(
  GUIDID char(16) CHARACTER SET OCTETS NOT NULL,
  SOMECOLUMN1 integer,
  SOMECOLUMN2 char(1),
  CONSTRAINT INTEG_7 PRIMARY KEY (GUIDID)
);

/********************* VIEWS **********************/

/******************* EXCEPTIONS *******************/

/******************** TRIGGERS ********************/

SET TERM ^ ;
CREATE TRIGGER INSERTDEMO FOR GUIDDEMO ACTIVE
BEFORE INSERT POSITION 0
AS
BEGIN
        /* enter trigger code here */
        new.GUIDID = gen_uuid();
END^
SET TERM ; ^

GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
 ON GUIDDEMO TO  SYSDBA WITH GRANT OPTION;


------------------------------------------------------------------------------
All the data continuously generated in your IT infrastructure contains a
definitive record of customers, application performance, security
threats, fraudulent activity and more. Splunk takes this data and makes
sense of it. Business sense. IT sense. Common sense.
http://p.sf.net/sfu/splunk-d2d-oct
_______________________________________________
Flamerobin-devel mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/flamerobin-devel
Reply | Threaded
Open this post in threaded view
|

Re: Can't delete rows from table wit GUID primary key

Michael Hieke
On 14.10.2011 11:44, Erik Heemskerk wrote:

> I have a few tables in which GUID's are used as primary keys. For
> some reason I am unable to delete rows from these tables with
> flamerobin (0.9.3.2091).
> Is this a feature or a bug?

A bug.  The problem lies with the StringColumnDef class that holds the
string data for tables - it is smart enough to treat character data with
the character set octets in a special way (formatting each byte as a two
digit hexadecimal value), but not smart enough to remember the character
set and apply the reverse conversion when the field value is read back
to create the delete statement for the row.  So the delete statement
doesn't correctly identify the row.

The delete statements are currently created as strings but it looks like
prepared statements with parameters would work much better for UUIDs.
Conversions to string and back are just too dangerous when we take
embedded null characters and character set conversions into account.

A UUIDColumnDef class would be handy, possibly even with different data
presentation, similar to the UUID_TO_CHAR() function.  It could keep the
octet string data as-is and convert it only for display.

Thanks BTW for the report, nice catch.

--
Michael Hieke

------------------------------------------------------------------------------
All the data continuously generated in your IT infrastructure contains a
definitive record of customers, application performance, security
threats, fraudulent activity and more. Splunk takes this data and makes
sense of it. Business sense. IT sense. Common sense.
http://p.sf.net/sfu/splunk-d2d-oct
_______________________________________________
Flamerobin-devel mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/flamerobin-devel
Reply | Threaded
Open this post in threaded view
|

Re: Can't delete rows from table wit GUID primary key

Erik Heemskerk
> Thanks BTW for the report, nice catch.

No problem. I'll just keep doing it manually untill a fix is made/released.
Thanks for all the effort you all put in.

E.Heemskerk


------------------------------------------------------------------------------
All the data continuously generated in your IT infrastructure contains a
definitive record of customers, application performance, security
threats, fraudulent activity and more. Splunk takes this data and makes
sense of it. Business sense. IT sense. Common sense.
http://p.sf.net/sfu/splunk-d2d-oct
_______________________________________________
Flamerobin-devel mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/flamerobin-devel