Light and fast messages outbound limit

This section contains scripts that hMailServer has contributed with. hMailServer 5 is needed to use these.
Post Reply
agserna
Normal user
Normal user
Posts: 69
Joined: 2011-10-05 23:43

Light and fast messages outbound limit

Post by agserna » 2014-07-10 15:44

Hello everybody.

This script limits number of outbound messages and has been developed keeping in mind speed and reliability.

The system does the followings:

1) Data for outbound messages is stored in a database table (use a modified version of this http://www.hmailserver.com/forum/viewto ... 20&t=13890)
2) A trigger in the database does all checks.

You can set default and per user limits, at the moment this is made "manually" acting on database table. Working on a web interface that does live monitoring and managements but will be published in a couple of weeks.

Download the attached file and read the readme.txt file

Also feel free to give youre personal contribution to improve it
HmsLimit.zip
everything you need
(6 KiB) Downloaded 207 times
Also last but not least, i am sorry if some tipyng/grammar mistakes. I am from Italy and try to do my best with English :mrgreen:
Last edited by agserna on 2014-07-10 16:09, edited 1 time in total.

agserna
Normal user
Normal user
Posts: 69
Joined: 2011-10-05 23:43

Re: Light and fast messages outbound limit

Post by agserna » 2014-07-10 16:00

Update:
If you use HMS older than 5.4 and experience problems in executing provided script for OnAcceptMessage event please see the "iID = obDatabase.ExecuteSQLWithReturn(sSQL)" row and try to change it with "Call obDatabase.ExecuteSQL(sSQL)" cause i am not sure "ExecuteSQLWithReturn" it is supported in older versions of HMS (no documentation found).

agserna
Normal user
Normal user
Posts: 69
Joined: 2011-10-05 23:43

Re: Light and fast messages outbound limit

Post by agserna » 2014-07-10 16:29

Here some useful queryes
QueryData.zip
(414 Bytes) Downloaded 168 times

whurlston
New user
New user
Posts: 6
Joined: 2012-03-07 07:00

Re: Light and fast messages outbound limit

Post by whurlston » 2015-04-14 23:33

MySQL version of the database table creation scripts (change first line to the name of your database):

Code: Select all

USE `databaseName`;

CREATE TABLE `hm_deliverylockedaccounts` (
  `LockedAccount_ID` int(11) NOT NULL AUTO_INCREMENT,
  `LockedAccount_AccountName` varchar(255) DEFAULT NULL,
  `LockedAccount_MessagesCount` int(11) DEFAULT NULL,
  `LockedAccount_DateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`LockedAccount_ID`)
);

CREATE TABLE `hm_deliverylog` (
  `DeliveryID` int(11) NOT NULL AUTO_INCREMENT,
  `DeliveryUser` varchar(255) NOT NULL COMMENT 'The user name of account (match hm_accounts > accountaddress)',
  `DeliveryFrom` varchar(255) DEFAULT NULL COMMENT 'Address used in "From" header of email',
  `DeliveryRecipientsCount` int(11) DEFAULT NULL COMMENT 'Number of recipient for the message',
  `DeliveryDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'DateTime of message',
  PRIMARY KEY (`DeliveryID`)
);

CREATE TABLE `hm_deliverysettings` (
  `DS_ID` int(11) NOT NULL AUTO_INCREMENT,
  `DS_AccountName` varchar(255) DEFAULT NULL,
  `DS_AccountLimit` int(11) NOT NULL DEFAULT '0',
  `DS_ExpiryTime` tinyint(3) unsigned DEFAULT NULL,
  PRIMARY KEY (`DS_ID`)
);

Trigger creation script (change first line to the name of your database):

Code: Select all

USE `databaseName`;

DELIMITER $$
CREATE TRIGGER `DisableAccount` 
   AFTER INSERT
   ON  `hm_deliverylog` 
FOR each row 
BEGIN

-- Set up Default limit here
DECLARE v_DefaultLimit int default 200;
-- End Settings

-- filled with total messages sent today, see below
DECLARE v_MessagesCount int default 0;

-- Get account address from new inserted row
DECLARE v_User varchar(255) default new.DeliveryUser;

-- Account Exception. Dont edit this.  Set the value in hm_deliverySettings table along with a specific account name (must match dbo.hm_accounts > accountaddress)
-- And keep in mind that exception value must be greater than @DefaultLimit, see below in check exception section
DECLARE v_AccountException int default 0;


	-- Calculate total messages sent today for given username/date
	
	   SELECT SUM(DeliveryRecipientsCount) into v_MessagesCount
       FROM hm_deliverylog
	   WHERE (DeliveryUser = v_User AND CONVERT(DeliveryDateTime, date) = CONVERT(NOW(), date));
	
	-- Check Exception
	-- For performance reasons delivery exceptions is checked only if default limit is reached

	IF v_MessagesCount > v_DefaultLimit
	THEN
		-- check if there is a exception limit for this user
	    SELECT DS_AccountLimit into v_AccountException FROM hm_deliverySettings WHERE DS_AccountName=v_User;
		-- Exception valid only if greater than Default limit
		IF v_AccountException > v_DefaultLimit 
		Then
			SELECT v_AccountException into v_DefaultLimit;
		END IF;
	END IF;
	
	-- if messages sent > default limits or exceptions limit then lock account
	IF v_MessagesCount > v_DefaultLimit
	THEN
	   -- lock the account
	   UPDATE hm_accounts SET accountactive = 0 WHERE accountaddress = v_User;
	   -- Log disabled accounts with account name and number of sent messages before locking, date and time are inserted as default values in table
	   -- To enable it uncomment the following
	   INSERT INTO dbo.hm_deliverylockedaccounts (LockedAccount_AccountName,LockedAccount_MessagesCount) VALUES (@User, @MessagesCount);
	END IF;    
END

Post Reply