The following issue has been CLOSED
======================================================================
http://www.dbmail.org/mantis/view.php?id=505
======================================================================
Reported By: jasb
Assigned To: aaron
======================================================================
Project: DBMail
Issue ID: 505
Category: Command-Line programs (dbmail-users, dbmail-util)
Reproducibility: always
Severity: feature
Priority: normal
Status: closed
target:
Resolution: no change required
Fixed in Version:
======================================================================
Date Submitted: 09-Feb-07 23:25 CET
Last Modified: 13-Mar-07 23:23 CET
======================================================================
Summary: When deleting a user with sieve scripts, the sieve
script should be erased also.
Description:
When i delete a user with dbmail-users, i think that if the user has one or
more sieve script, the values should be deleted along the user.
I had a situation today with this, for example:
I use the free numbers in owner_idnr so, imagine this:
I have a user created with owner_idnr=80, and created a sieve script with
some rule, later this i've created some more users, and some time ahead
i've deleted the user with owner_idnr=80, and the sieve script just stayed
intact in the DB, and the new user is going to take place of the
owner_idnr=80 that has a script from the older user with onwer_idnr=80
that should have been deleted.
What do you think about this? should it be deleted also? i think so, give
some feedback :P
Jorge
======================================================================
----------------------------------------------------------------------
aaron - 10-Feb-07 00:58
----------------------------------------------------------------------
There's a foreign key relationship that should remove entries from the
dbmail_sievescripts table when the corresponding useridnr is removed
dbmail_users. Is that not working for you?
There's also functionality in dbmail-sievecmd to delete sieve scripts for
a user.
There isn't a command in dbmail-util to check for unowned sieve scripts.
I'll add that.
----------------------------------------------------------------------
jasb - 10-Feb-07 01:21
----------------------------------------------------------------------
Hum... going to check my table defenitions, maybe i've missed that in some
where.
An aditional argument for dbmail-util would be nice sinse, at least me,
run dbmail-util once per day.
But what i've thinked was, that dbmail-users could remove that info even
if there's no foreign relation, what do you think?
below there's the show create table of table dbmail_sievescripts, i don't
see nothing related to foreign keys...
CREATE TABLE `dbmail_sievescripts` (
`id` bigint(20) NOT NULL auto_increment,
`owner_idnr` bigint(20) NOT NULL default '0',
`active` tinyint(1) NOT NULL default '0',
`name` varchar(100) NOT NULL,
`script` text NOT NULL,
PRIMARY KEY (`id`),
KEY `dbmail_sievescripts_1` (`owner_idnr`,`name`),
KEY `dbmail_sievescripts_2` (`owner_idnr`,`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
I was checking create_tables.sql from 2.2.2 version and i see that it has
a reference on that, i think that when i started using dbmail this doesn't
exist, ok it didn't because i started with dbmail 2.0x, but when i upgraded
to 2.1x maybe the upgrade script of mysql was missing this and was added
after i upgraded.
3 possible solutions here
1-add check on dbmail-users to delete this manually
2-add the foreign key to the table
3-add this option to dbmail-util
my best opinion is, add the new option to check this to dbmail-util, and
i'll update the table defenition to make it work from now on.
(you already said that were going to add this to dbmail-util :) )
Do you agree Aaron?
----------------------------------------------------------------------
jasb - 10-Feb-07 13:10
----------------------------------------------------------------------
Hum... Aaron, checking the create_tables.mysql from 2.2.2 version i see
that the table structure is completly diferent, and i have plus a column,
the "id", and the other column aren't in the same order.
Wich one is the correct?
create_tables.mysql has:
CREATE TABLE dbmail_sievescripts (
owner_idnr bigint(21) DEFAULT '0' NOT NULL,
name varchar(100) NOT NULL,
script text,
active tinyint(1) default '0' not null,
INDEX (name),
INDEX (owner_idnr),
INDEX (owner_idnr, name),
FOREIGN KEY owner_idnr_fk (owner_idnr)
REFERENCES dbmail_users (user_idnr) ON DELETE CASCADE ON UPDATE
CASCADE
) ENGINE=InnoDB;
I have:
CREATE TABLE `dbmail_sievescripts` (
`id` bigint(20) NOT NULL auto_increment,
`owner_idnr` bigint(20) NOT NULL default '0',
`active` tinyint(1) NOT NULL default '0',
`name` varchar(100) NOT NULL,
`script` text NOT NULL,
PRIMARY KEY (`id`),
KEY `dbmail_sievescripts_1` (`owner_idnr`,`name`),
KEY `dbmail_sievescripts_2` (`owner_idnr`,`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
----------------------------------------------------------------------
aaron - 10-Feb-07 20:03
----------------------------------------------------------------------
Those two definitions are not significantly different, but if you ALTER the
table to put in that foreign key line, you'll get the auto deletion of
scripts when a user is deleted.
About the order of columns, it doesn't matter; we never rely on the result
order of columns returned from something like 'select * from...'.
----------------------------------------------------------------------
jasb - 10-Feb-07 20:37
----------------------------------------------------------------------
I already added the foreign key.
Ok if you are accessing filds not by the order, then i can leave the table
like this.
If correct you can close this Aaron.
Anyway, you're gonna add that option to dbmail-utils?
Issue History
Date Modified Username Field Change
======================================================================
09-Feb-07 23:25 jasb New Issue
09-Feb-07 23:25 jasb Issue Monitored: jasb
10-Feb-07 00:55 aaron Status new => assigned
10-Feb-07 00:55 aaron Assigned To => aaron
10-Feb-07 00:58 aaron Note Added: 0001821
10-Feb-07 01:21 jasb Note Added: 0001822
10-Feb-07 13:09 jasb Note Added: 0001823
10-Feb-07 13:10 jasb Note Edited: 0001823
10-Feb-07 20:03 aaron Note Added: 0001824
10-Feb-07 20:37 jasb Note Added: 0001825
13-Mar-07 23:23 paul Severity major => feature
13-Mar-07 23:23 paul Status assigned => closed
13-Mar-07 23:23 paul Resolution open => no change
required
======================================================================
_______________________________________________
Dbmail-dev mailing list
[email protected]
http://twister.fastxs.net/mailman/listinfo/dbmail-dev
