View Issue Details

WikiJump to Notes
IDProjectCategoryView StatusDate SubmittedLast Update
0006559mantisbtdb mssqlpublic2006-01-05 02:472014-05-16 15:00
Reporterdrewr Assigned Tovboctor  
PrioritynormalSeverityminorReproducibilityalways
Status closedResolutionfixed 
Product Version1.0.0rc3 
Fixed in Version1.1.0a1 
Summary0006559: "Prune Accounts" function doesn't work with MS SQL
Description

I am running Mantis with MS SQL (because my manager says so, not out of choice :-), and testing has revealed that the "Prune Accounts" button on the "Manage Users" page doesn't work.

After looking at the code, this is because the SELECT statement for this function uses the MySQL-specific DB function "TO_DAYS".

I have edited the code to use the db_helper_compare_days function, which I found in the database_api.php file, and the Prune function now works correctly on MySQL and MS SQL systems (I have testing version of both).

There is also a note above the db_helper_compare_days function saying "Check if there is a way to do this using ADODB rather than implementing it here." (around line 329)

There is a way to do this using ADODB. Code follows. Which types of diff file would you like?

Drew

Additional Information

The original manage_user_prune.php code:

    # Delete the users who have never logged in and are older than 1 week
$days_old = 7;
$days_old = (integer)$days_old;
$query = "SELECT id
        FROM $t_user_table
        WHERE login_count=0 AND TO_DAYS(".db_now().") - '$days_old' > TO_DAYS(date_created)";
$result = db_query($query);

$count = db_num_rows( $result );

The newer version using db_helper_compare_days:

    # Delete the users who have never logged in and are older than 1 week
$days_old = 7;
$days_old = (integer)$days_old;
$date_calc = db_helper_compare_days(db_now(),"date_created","> $days_old");     
$query = "SELECT id
        FROM $t_user_table
            WHERE login_count=0 AND $date_calc";

$result = db_query($query);

$count = db_num_rows( $result );

The latest version using ADODB:

    # Delete the users who have never logged in and are older than 1 week
$days_old = 7;
$days_old = (integer)$days_old;
global $g_db;
$date_calc = $g_db->OffsetDate(-7) . "> date_created";

$query = "SELECT id
        FROM $t_user_table
                WHERE login_count=0 AND $date_calc";

$result = db_query($query);

$count = db_num_rows( $result );
TagsNo tags attached.
Attached Files
manage_user_prune-diff1.diff (791 bytes)    
--- C:\Documents and Settings\robinsond\My Documents\mantis-1.0.0rc3\manage_user_prune.php	Sun Feb 13 04:01:06 2005
+++ C:\Documents and Settings\robinsond\My Documents\mantis-devel\manage_user_prune.php	Thu Jan 05 15:53:03 2006
@@ -16,13 +16,14 @@
 	$t_user_table = config_get( 'mantis_user_table' );
 
 	# Delete the users who have never logged in and are older than 1 week
 	$days_old = 7;
 	$days_old = (integer)$days_old;
+	$date_calc = db_helper_compare_days(db_now(),"date_created","> $days_old");	
 	$query = "SELECT id
 			FROM $t_user_table
-			WHERE login_count=0 AND TO_DAYS(".db_now().") - '$days_old' > TO_DAYS(date_created)";
+			WHERE login_count=0 AND $date_calc";
 	$result = db_query($query);
 
 	$count = db_num_rows( $result );
 
 	if ( $count > 0 ) {
manage_user_prune-diff1.diff (791 bytes)   
manage_user_prune-diff2.diff (784 bytes)    
--- C:\Documents and Settings\robinsond\My Documents\mantis-1.0.0rc3\manage_user_prune.php	Sun Feb 13 04:01:06 2005
+++ C:\Documents and Settings\robinsond\My Documents\mantis-devel\manage_user_prune.php	Thu Jan 05 15:54:12 2006
@@ -16,13 +16,15 @@
 	$t_user_table = config_get( 'mantis_user_table' );
 
 	# Delete the users who have never logged in and are older than 1 week
 	$days_old = 7;
 	$days_old = (integer)$days_old;
+	global $g_db;
+	$date_calc = $g_db->OffsetDate(-7)."> date_created";
 	$query = "SELECT id
 			FROM $t_user_table
-			WHERE login_count=0 AND TO_DAYS(".db_now().") - '$days_old' > TO_DAYS(date_created)";
+			WHERE login_count=0 AND $date_calc";
 	$result = db_query($query);
 
 	$count = db_num_rows( $result );
 
 	if ( $count > 0 ) {
manage_user_prune-diff2.diff (784 bytes)   

Relationships

parent of 0007028 closedvboctor Port: "Prune Accounts" function doesn't work with MS SQL 

Activities

drewr

drewr

2006-01-05 03:01

reporter   ~0011870

The two files are the two fixes, respectively, in Unified Diff format.

Drew
grangeway

grangeway

2014-05-16 15:00

reporter   ~0040425

MantisBT currently supports Mysql and has support for other database engines.

The support for other databases is known to be problematic.

Having implemented the current database layer into Mantis 10 years ago, I'm currently working on replacing the current layer.

If you are interested in using Mantis with non-mysql databases - for example, Oracle, PGSQL or MSSQL, and would be willing to help out testing the new database layer, please drop me an email at paul@mantisforge.org

In the meantime, I'd advise running Mantis with Mysql Only to avoid issues.

Thanks
Paul