We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
    • 5811
    • 1,717 Posts
    Does it exist somewhere a tool which explore the modx database and renums the IDs of tables (documents, templates, TVs, snippets, ...) in order to fill the gap in the numbering ? And obviously keeps the integrity of the database.
      • 22303 MODX Staff
      • 10,725 Posts
      No, the id’s are auto-increment columns and are assigned by the database engine.
        • 5811
        • 1,717 Posts
        I know it. But the deleting of documents create gaps in the numbering. And I am a little fusspot fussy sad
          • 28042 ☆ A M B ☆
          • 24,524 Posts
          I had a similar issue with the gaps in the numbering, but eventually I got over it. The numbers are completely irrelevant for you and me, but they are used internally, and to change them can cause severe unintended consequences. For example, they are used to connect template variable values to specific documents. So seriously, just try to ignore them except when you need to use them for links or snippet parameters. After a while, you don’t even notice any more.
            Studying MODX in the desert - http://sottwell.com
            Tips and Tricks from the MODX Forums and Slack Channels - http://modxcookbook.com
            Join the Slack Community - http://modx.org
            • 22303 MODX Staff
            • 10,725 Posts
            I personally never delete documents, just re-appropriate them.
              • 7231
              • 4,205 Posts
              Quote from: OpenGeek at Feb 21, 2008, 12:01 PM

              I personally never delete documents, just re-appropriate them.
              But then rather than gaps you get inconsistencies.

              I am with sottwell, I don’t pay much attention to the ids anymore. I pondered this at first when I deleted an entire section of documents and started a new one (lost @100 of the sequence) but once I finished and turned on FURLs it was a mute point.

              re-appropriating the docs seems like a good method as long as you don’t need to maintain the sequence in order.
                [font=Verdana]Shane Sponagle | [wiki] Snippet Call Anatomy | MODx Developer Blog | [nettuts] Working With a Content Management Framework: MODx

                Something is happening here, but you don't know what it is.
                Do you, Mr. Jones? - [bob dylan]
                • 27376
                • 576 Posts
                I’m one of those ’fusspots’ too that you speak of wink So I made a little script to ease some of the work for me; just little snippets of code that I use but don’t want to make a module for:
                <?php
                // Set this to your MODx Directory
                $base_path = '/var/www/modx/';
                
                include($base_path.'manager/includes/config.inc.php');
                
                $action = (string)$_REQUEST['action'];
                
                if (!$rs = mysql_connect($database_server, $database_user, $database_password, true))
                	die('Unable to connect to server');
                if (!mysql_select_db(trim($dbase, '`')))
                	die('Unable to select database');
                
                switch ($action) {
                	case 'doc_groups':
                		$sql = 'SELECT id, document_group AS dg, document, count(*) as gc FROM '.$table_prefix.'document_groups
                			GROUP BY document, dg
                			ORDER BY gc DESC';
                		$q = mysql_query($sql, $rs);
                		if ($q === false)
                			die('Error in query');
                
                		while ($item = mysql_fetch_assoc($q)) {
                			if ($item['gc'] <= 1) {
                				break;
                			}
                			$sql = 'DELETE FROM '.$table_prefix.'document_groups WHERE id='.$item['id'];
                			echo $sql.'<br/>';
                			mysql_query($sql, $rs);
                		}
                		break;
                	case 'tmplvars':
                		$sql = 'SELECT id, tmplvarid, contentid, value, count(*) as tvcount FROM '.$table_prefix.'site_tmplvar_contentvalues GROUP BY tmplvarid, contentid';
                		$q = mysql_query($sql, $rs);
                		while ($item = mysql_fetch_assoc($q)) {
                			if ($item['tvcount'] <= 1) continue;
                			print_r($item);
                			echo "<br/>";
                		}
                		break;
                	case 'doc_id':
                		$table = (string)$_REQUEST['table'];
                		if (!$table) $table = 'site_content';
                		$sql = 'SELECT id FROM '.$table_prefix.$table.' ORDER BY id';
                		$q = mysql_query($sql, $rs);
                		$previd = 0;
                		echo '<pre>';
                		$output = 'Open IDs in '.$table_prefix.$table.':';
                		while ($item = mysql_fetch_assoc($q)) {
                			if ($item['id'] - $previd > 1) {
                				for ($i = ($previd+1); $i < $item['id']; $i++) {
                					$output .= ' '.$i;
                					if (strlen($output) > 140) {
                						echo $output."\n";
                						$output = '';
                					}
                				}
                			}
                			$previd = $item['id'];
                		}
                		echo $output.'</pre>';
                		break;
                	default:
                		echo "Select an action.<br/>";
                		break;
                }
                echo "Query Complete on table <tt>".$dbase."</tt><br/>";
                Access it through a browser like so: [tt]http://www.example.com/cleanup.php?action=doc_id[/tt] Be sure to set the [tt]$base_path[/tt] appropriately in your environment

                It has a couple other actions too:

                • [tt]doc_groups[/tt] will look through the document_groups table for duplicate entries and delete them. I use database synchronization software and sometimes this table gets cluttered with duplicates.
                • [tt]tmplvars[/tt] does the same thing on the site_tmplvar_contentvalues table for the same reason.
                • [li][tt]doc_id[/tt] examines the ’site_content’ table (by default) for non-sequential rows and reports them.
                I use the [tt]doc_id[/tt] action in conjunction with phpMyAdmin to create new documents and recycle the IDs. This is just because I’m picky though, I don’t have any valid reason to be doing this...
                  • 5811
                  • 1,717 Posts
                  Thanks a lot Matthew for this script.