Leandro Guimarães Faria Corcete DUTRA wrote:
>> On Wed, 08 Nov 2006 18:51:20 -0800, Rares Vernica wrote:
>>
>>
>>> Is it possible to access the Full-Text Index structures from SQL?
>>>
>> What do you mean exactly? SQL is not intended for physical structures.
>>
>>
I started writing a little PHP shell script to import the data from the
myisam_ftdump into a table, but unfortunately this a side project at
work and I have to effectively steal time to work on it so it's not
going very far at the moment. The table structure I was using was...
CREATE TABLE `SEARCH_STATS` (
`ID` int(11) NOT NULL auto_increment,
`Parent_Table` varchar(255) NOT NULL,
`Name` varchar(255) NOT NULL,
`Occurances` int(11) NOT NULL default '0',
`Search_Count` int(11) NOT NULL default '0',
`Global_Weight` float(10,7) NOT NULL default '0.0000000',
`Manual_Weight` float(10,7) NOT NULL default '1.0000000',
`Date_deleted` timestamp NOT NULL default '0000-00-00 00:00:00',
`Date_modified` timestamp NOT NULL default CURRENT_TIMESTAMP on
update CURRENT_TIMESTAMP,
`Date_created` timestamp NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`ID`),
UNIQUE KEY `Name` (`Name`),
KEY `Occurances` (`Occurances`),
KEY `Search_Count` (`Search_Count`),
KEY `Global_Weight` (`Global_Weight`),
KEY `Manual_Weight` (`Manual_Weight`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
...and a rough bit of psuedo-code for you...
shell( myisam_ftdump /home/databases/mysql/{database}/{tables}4 -c >
global_weights_and_counts.index );
shell( myisam_ftdump /home/databases/mysql/{database}/{tables}4 -d >
local_weights.index );
$databaseWrapperObject_ = new DatabaseWrapper($includePath);
$handle = @fopen("global_weights_and_counts.index", "r");
if ($handle)
{
while (!feof($handle))
{
$buffer = fgets($handle);
$data = explode(" ",$buffer);
$sql = 'INSERT INTO SEARCH_STATS (Parent_Table, Name, Occurances, Global_Weight,
Date_created) VALUES ("WD_NAMES", "'.trim($data[20]).'", '.$data[8].', '.$data[19].',
NOW())';
mysql_query($sql);
}
fclose($handle);
}
$handle = @fopen("local_weights.index", "r");
if ($handle)
{
while (!feof($handle))
{
$buffer = fgets($handle);
$data = explode(" ",$buffer);
//8, 19,20
// $sql = 'INSERT INTO SEARCH_STATS (Parent_Table, Name, Occurances,
Global_Weight, Date_created) VALUES ("WD_NAME
// mysql_query($sql);
}
fclose($handle);
}
...that last loop needs sorting.
Regards,
Phil
