This is what writeSchema() does with a compound key (the last index is a FULLTEXT key). Am I likely to run into problems with fulltext queries (MATCH AGAINST) using this schema?
<?xml version="1.0" encoding="UTF-8"?>
<model package="mypackage" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM">
<object class="Quotation" table="quotation" extends="xPDOSimpleObject">
<field key="quote" dbtype="mediumtext" phptype="string" null="false" index="index" />
<field key="author" dbtype="varchar" precision="200" phptype="string" null="false" default="" />
<field key="topic" dbtype="varchar" precision="20" phptype="string" null="false" default="" index="index" />
<index alias="PRIMARY" name="PRIMARY" primary="true" unique="true">
<column key="id" collation="A" null="false" />
</index>
<index alias="topic" name="topic" primary="false" unique="false">
<column key="topic" collation="A" null="false" />
</index>
<index alias="quote" name="quote" primary="false" unique="false">
<column key="quote" collation="" null="false" />
<column key="author" collation="" null="false" />
<column key="topic" collation="" null="false" />
</index>
</object>
</model>
-
MODX Staff
- 10,725 Posts
Until the table creation process is updated, you’ll need to add your fulltext indices the old way with index="fulltext" and indexgrp="nameofindex". And yes, there will likely need to be some adjustments to support full-text indicies in the new index format, since these are not standard across database implementations (of course neither is MATCH AGAINST).
Quote from: OpenGeek at Oct 18, 2010, 06:56 PM
Until the table creation process is updated, you’ll need to add your fulltext indices the old way with index="fulltext" and indexgrp="nameofindex". And yes, there will likely need to be some adjustments to support full-text indicies in the new index format, since these are not standard across database implementations (of course neither is MATCH AGAINST).
Is this correct, then?
<index="fulltext" alias="quote" name="quote" primary="false" unique="false">
<column key="quote" collation="" null="false" indexgrp="quote" />
<column key="author" collation="" null="false" indexgrp="quote"/>
<column key="topic" collation="" null="false" indexgrp="quote"/>
</index>
I guess my tutorial uses a bad example in that case.
I’m not sure I’m getting you completely. I get that, at present, if xPDO creates the table, it won’t have a fulltext index, but will xPDO do a fulltext search on that table if I create it with the following SQL so it’s defined as fulltext in the DB table itself?
CREATE TABLE IF NOT EXISTS `bobs_quotation` (
`id` int(25) unsigned NOT NULL auto_increment,
`quote` mediumtext NOT NULL,
`author` varchar(200) NOT NULL default '',
`topic` varchar(20) NOT NULL default '',
PRIMARY KEY (`id`),
KEY `topic` (`topic`),
KEY `author` (`author`),
FULLTEXT KEY `quote` (`quote`,`author`,`topic`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;
Quote from: OpenGeek at Oct 18, 2010, 11:39 PM
But I see now that you also want the regular indexes on author and topic as well and this cannot be done until the new implementation is done. But now at least you see why index needed to be introduced as a separate element in the schema. 
But I did get those in the schema file when I ran writeSchema(), unless you’re referring to createObjectContainer() again. I’ve never actually used that for anything.
BTW, is there an xPDO equvalent for this? I can’t work it out.
<?php
$sql = "SELECT *,
MATCH(author, quote, topic) AGAINST ('$searchstring' IN BOOLEAN MODE) AS score
FROM modx_quotations
WHERE MATCH(author, quote, topic) AGAINST('$searchstring' IN BOOLEAN MODE)
ORDER BY id DESC";
$result = $modx->query($sql) or die (mysql_error());
while ($row = $result->fetch(PDO_FETCH_ASSOC)) {
$output .= "<div class=\"quotation\">".stripslashes($row['quote'])."</div>";
$output .= "<div class=\"author\">— " . stripslashes(htmlspecialchars($row['author'])). "</div>";
It works perfectly for Google-like search strings with " ", "+" and "-" tokens on the keywords. (Someone should implement it for the Forums.)
Hi all,
I’m looking closer at indexes definition, and need a (probably very basic) clarification, in the examples given in the previous posts, you (Bob) define a collation for your indexes, in one case the collation has a value of "A", and in other the collation has a value of "" (empty).
Would you be kind to explain me what is this ’collation="A" ’ (what A?) and the impact to define the collation with a value or not... I guess this has something to do with the performance (ie. : for searches) ...
It would be great to have a bit more informations on what is what and how it have to be implemented ... (I don’t complain, I know how looooooong it is to write a "good" documentation) just put me on the right direction I’ll try to find my way, and continue to ask some stupid questions...
Thanks in advance
Cheers
-
MODX Staff
- 10,725 Posts
First, there is no collation="A" in the old schema, and I wouldn’t worry about it (yet) unless you know what it means already. This is a new feature for the xPDO mysql schemas which let’s you set the sort order of the index (A = ASC, blank is DESC). But this is only available in 2.0.0-rc3 or later (which was released yesterday) and you should not use this new index scheme in components for MODx 2.0.x -- these features will be in MODx 2.0.5, but you will need to make your components targeting 2.0.x use the old index format in the schema if you wish for your component to create tables with indexes in releases prior to 2.0.5. IOW, the features will be in the core for 2.0.5 as a preview, but adoption of this and a couple of other new xPDO features should not be adopted for MODx Extras until the next minor release, i.e. 2.1.x.
I just implemented these features and will be documenting them in the appropriate places as soon as possible, as they become relevant to current xPDO and MODx releases.
