/** * Constructor takes Db() instance which it uses for all database access. You should pass an instance in * because all the database accesses need to be in the same transactional context as whatever else you're doing. In * the case of BaseModel::insert(), BaseModel::update() and BaseModel::delete()they're always in a transactional context * so this is critical. If you don't pass an Db() instance then the constructor creates a new one, which is useful for * cases where you're reindexing and not in a transaction. */ public function __construct($opo_db = null, $ps_engine = null) { require_once __CA_MODELS_DIR__ . '/ca_metadata_elements.php'; parent::__construct($opo_db, $ps_engine); $this->opo_metadata_element = new ca_metadata_elements(); $this->opo_search_indexing_queue = new ca_search_indexing_queue(); }
public function __construct($opo_db = null, $ps_tablename = null) { parent::__construct($opo_db); if ($ps_tablename != null) { $this->ops_tablename = $ps_tablename; } $this->opa_options = array(); $this->opa_result_filters = array(); $this->opn_tablenum = $this->opo_datamodel->getTableNum($this->ops_tablename); $this->opa_tables = array(); }
/** * @param Db $po_db A database client object to use rather than creating a new connection. [Default is to create a new database connection] * @param string $ps_engine Name of the search engine to use. [Default is the engine configured using "search_engine_plugin" in app.conf] */ public function __construct($po_db = null, $ps_engine = null) { $this->opo_datamodel = Datamodel::load(); $this->opo_app_config = Configuration::load(); $this->opo_search_config = Configuration::load($this->opo_app_config->get("search_config")); $this->opo_search_indexing_config = Configuration::load($this->opo_search_config->get("search_indexing_config")); // load search engine plugin as configured by the 'search_engine_plugin' directive in the main app config file if (!($this->opo_engine = SearchBase::newSearchEngine($ps_engine, $po_db))) { die("Couldn't load configured search engine plugin. Check your application configuration and make sure 'search_engine_plugin' directive is set properly."); } $this->opo_db = $po_db ? $po_db : new Db(); }
/** * Generate mappings for ElasticSearch based upon currently configured search indexing */ public static function generate_elasticSearch_configuration($po_opts = null) { require_once __CA_LIB_DIR__ . "/core/Search/SearchBase.php"; require_once __CA_LIB_DIR__ . "/core/Configuration.php"; require_once __CA_LIB_DIR__ . "/core/Datamodel.php"; require_once __CA_LIB_DIR__ . "/core/Zend/Http/Client.php"; $vo_app_conf = Configuration::load(); $vo_search_conf = Configuration::load($vo_app_conf->get("search_config")); $vo_search_indexing_conf = Configuration::load($vo_search_conf->get("search_indexing_config")); $o_db = new Db(); $o_datamodel = Datamodel::load(); // delete and create index $vo_http_client = new Zend_Http_Client(); $vo_http_client->setUri($vo_search_conf->get('search_elasticsearch_base_url') . "/" . $vo_search_conf->get('search_elasticsearch_index_name')); try { $vo_http_client->request('DELETE'); $vo_http_client->request('PUT'); } catch (Zend_Http_Client_Adapter_Exception $e) { CLIUtils::addError(_t('Couldn\'t connect to ElasticSearch. Is the service running?')); return; } $va_tables = $vo_search_indexing_conf->getAssocKeys(); $vo_search_base = new SearchBase(); foreach ($va_tables as $vs_table) { // get fields to index for this table if (!is_array($va_table_fields = $vo_search_base->getFieldsToIndex($vs_table))) { $va_table_fields = array(); } $t_instance = $o_datamodel->getTableInstance($vs_table); $vn_table_num = $o_datamodel->getTableNum($vs_table); $va_attributes = null; $va_opts = array(); if (is_array($va_table_fields)) { $va_rewritten_fields = array(); foreach ($va_table_fields as $vs_field_name => $va_field_options) { if (preg_match('!^_ca_attribute_([\\d]*)$!', $vs_field_name, $va_matches)) { $va_rewritten_fields['A' . $va_matches[1]] = $va_field_options; $qr_type_restrictions = $o_db->query(' SELECT DISTINCT came.* FROM ca_metadata_type_restrictions camtr INNER JOIN ca_metadata_elements as came ON came.element_id = camtr.element_id WHERE camtr.table_num = ? AND came.element_code = ? ', (int) $vn_table_num, (string) $va_matches[1]); while ($qr_type_restrictions->nextRow()) { $vn_element_id = $qr_type_restrictions->get('element_id'); $va_attributes[$vn_element_id] = array('element_id' => $vn_element_id, 'element_code' => $qr_type_restrictions->get('element_code'), 'datatype' => $qr_type_restrictions->get('datatype')); } } else { $va_rewritten_fields[$vs_field_name] = $va_field_options; } } $va_table_fields = $va_rewritten_fields; } if (is_array($va_attributes)) { foreach ($va_attributes as $vn_element_id => $va_element_info) { if (!preg_match("!^_ca_attribute_([\\d]+)\$!", $va_element_info['element_code'], $va_matches)) { continue; } $vs_element_code = $vs_table . ".A" . $va_matches[1]; $va_element_opts = array(); switch ($va_element_info['datatype']) { case 1: // text // text case 3: // list // list case 5: // url // url case 6: // currency // currency case 8: // length // length case 9: // weight // weight case 13: // LCSH // LCSH case 14: // geonames // geonames case 15: // file // file case 16: // media // media case 19: // taxonomy // taxonomy case 20: // information service $va_element_opts['properties']['type'] = 'string'; break; case 2: // daterange $va_element_opts['properties']['type'] = 'date'; $va_element_opts['properties']["format"] = 'dateOptionalTime'; $va_element_opts['properties']["ignore_malformed"] = false; $va_table_fields[$vs_element_code . '_text'] = array_merge($va_opts, array('properties' => array('type' => 'string'))); break; case 4: // geocode $va_element_opts['properties']['type'] = 'geo_point'; $va_table_fields[$vs_element_code . '_text'] = array_merge($va_opts, array('properties' => array('type' => 'string'))); break; case 10: // timecode // timecode case 12: // numeric/float $va_element_opts['properties']['type'] = 'double'; break; case 11: // integer $va_element_opts['properties']['type'] = 'long'; break; default: $va_element_opts['properties']['type'] = 'string'; break; } $va_table_fields[$vs_element_code] = array_merge($va_opts, $va_element_opts); } } if (is_array($va_table_fields)) { foreach ($va_table_fields as $vs_field_name => $va_field_options) { $va_field_options['properties']["store"] = in_array("STORE", $va_field_options) ? 'yes' : 'no'; if ($va_field_options["BOOST"]) { $va_field_options['properties']["boost"] = floatval($va_field_options["BOOST"]); } if (in_array("DONT_TOKENIZE", $va_field_options)) { // TODO: maybe do something? } // "intrinsic" fields if (!isset($va_field_options['properties']['type']) && $t_instance->hasField($vs_field_name)) { switch ($t_instance->getFieldInfo($vs_field_name, "FIELD_TYPE")) { case FT_TEXT: case FT_MEDIA: case FT_FILE: case FT_PASSWORD: case FT_VARS: $va_field_options['properties']['type'] = 'string'; break; case FT_NUMBER: case FT_TIME: case FT_TIMERANGE: case FT_TIMECODE: if ($t_instance->getFieldInfo($vs_field_name, "LIST_CODE")) { // list-based intrinsics get indexed with both item_id and label text $va_field_options['properties']['type'] = 'string'; } else { $va_field_options['properties']['type'] = 'double'; } break; case FT_TIMESTAMP: case FT_DATETIME: case FT_HISTORIC_DATETIME: case FT_DATE: case FT_HISTORIC_DATE: case FT_DATERANGE: case FT_HISTORIC_DATERANGE: $va_field_options['properties']['type'] = 'date'; break; case FT_BIT: $va_field_options['properties']['type'] = 'boolean'; break; default: $va_field_options['properties']['type'] = "string"; break; } } if (!$va_field_options['properties']['type']) { $va_field_options['properties']['type'] = "string"; } $vo_http_client = new Zend_Http_Client(); $vo_http_client->setUri($vo_search_conf->get('search_elasticsearch_base_url') . "/" . $vo_search_conf->get('search_elasticsearch_index_name') . "/" . $vs_table . "/" . "_mapping"); $va_mapping = array(); $va_mapping[$vs_table]["properties"][$vs_table . "." . $vs_field_name] = $va_field_options["properties"]; $vo_http_client->setRawData(json_encode($va_mapping))->setEncType('text/json')->request('POST'); try { $vo_http_response = $vo_http_client->request(); $va_response = json_decode($vo_http_response->getBody(), true); if (!$va_response["ok"]) { CLIUtils::addError(_t("Something went wrong at %1 with message: %2", "{$vs_table}.{$vs_field_name}", $va_response["error"])); CLIUtils::addError(_t("Mapping sent to ElasticSearch was: %1", json_encode($va_mapping))); return; } } catch (Exception $e) { CLIUtils::addError(_t("Something went wrong at %1", "{$vs_table}.{$vs_field_name}")); CLIUtils::addError(_t("Response body was: %1", $vo_http_response->getBody())); return; } } } /* related tables */ $va_related_tables = $vo_search_base->getRelatedIndexingTables($vs_table); foreach ($va_related_tables as $vs_related_table) { $va_related_table_fields = $vo_search_base->getFieldsToIndex($vs_table, $vs_related_table); foreach ($va_related_table_fields as $vs_related_table_field => $va_related_table_field_options) { $va_related_table_field_options['properties']["store"] = in_array("STORE", $va_related_table_field_options) ? 'yes' : 'no'; $va_related_table_field_options['properties']['type'] = "string"; if (in_array("DONT_TOKENIZE", $va_related_table_field_options)) { // TODO: do something? } $vo_http_client = new Zend_Http_Client(); $vo_http_client->setUri($vo_search_conf->get('search_elasticsearch_base_url') . "/" . $vo_search_conf->get('search_elasticsearch_index_name') . "/" . $vs_table . "/" . "_mapping"); $va_mapping = array(); $va_mapping[$vs_table]["properties"][$vs_related_table . '.' . $vs_related_table_field] = $va_related_table_field_options["properties"]; $vo_http_client->setRawData(json_encode($va_mapping))->setEncType('text/json')->request('POST'); try { $vo_http_response = $vo_http_client->request(); $va_response = json_decode($vo_http_response->getBody(), true); if (!$va_response["ok"]) { CLIUtils::addError(_t("Something went wrong at %1 with message: %2", "{$vs_table}/{$vs_related_table}.{$vs_related_table_field}", $va_response["error"])); CLIUtils::addError(_t("Mapping sent to ElasticSearch was: %1", json_encode($va_mapping))); return; } } catch (Exception $e) { CLIUtils::addError(_t("Something went wrong at %1", "{$vs_table}/{$vs_related_table}.{$vs_related_table_field}")); CLIUtils::addError(_t("Response body was: %1", $vo_http_response->getBody())); return; } } } /* created and modified fields */ $va_mapping = array(); $va_mapping[$vs_table]["properties"]["created"] = array('type' => 'date', 'format' => 'dateOptionalTime', 'ignore_malformed' => false); $va_mapping[$vs_table]["properties"]["modified"] = array('type' => 'date', 'format' => 'dateOptionalTime', 'ignore_malformed' => false); $va_mapping[$vs_table]["properties"]["created_user_id"] = array('type' => 'double'); $va_mapping[$vs_table]["properties"]["modified_user_id"] = array('type' => 'double'); $vo_http_client = new Zend_Http_Client(); $vo_http_client->setUri($vo_search_conf->get('search_elasticsearch_base_url') . "/" . $vo_search_conf->get('search_elasticsearch_index_name') . "/" . $vs_table . "/" . "_mapping"); $vo_http_client->setRawData(json_encode($va_mapping))->setEncType('text/json')->request('POST'); try { $vo_http_response = $vo_http_client->request(); $va_response = json_decode($vo_http_response->getBody(), true); if (!$va_response["ok"]) { CLIUtils::addError(_t("Something went wrong at %1 with message: %2", "{$vs_table}.created/modified", $va_response["error"])); CLIUtils::addError(_t("Mapping sent to ElasticSearch was: %1", json_encode($va_mapping))); return; } } catch (Exception $e) { CLIUtils::addError(_t("Something went wrong at %1", "{$vs_table}.created")); CLIUtils::addError(_t("Response body was: %1", $vo_http_response->getBody())); return; } } CLIUtils::addMessage(_t('ElasticSearch schema was created successfully!'), array('color' => 'bold_green')); CLIUtils::addMessage(_t("Note that all data has been wiped from the index so you must issue a full reindex now, either using caUtils rebuild-search-index or the web-based tool under Manage > Administration > Maintenance."), array('color' => 'red')); }
public static function updateSolrConfiguration($pb_invoked_from_command_line = false) { /* get search and search indexing configuration */ $po_app_config = Configuration::load(); $po_search_config = Configuration::load($po_app_config->get("search_config")); $po_search_indexing_config = Configuration::load($po_search_config->get("search_indexing_config")); $ps_solr_home_dir = $po_search_config->get('search_solr_home_dir'); $po_datamodel = Datamodel::load(); $po_search_base = new SearchBase(); global $o_db; if (!is_object($o_db)) { /* catch command line usage */ $o_db = new Db(); } $t_element = new ca_metadata_elements(); /* parse search indexing configuration to see which tables are indexed */ $va_tables = $po_search_indexing_config->getAssocKeys(); /* create solr.xml first to support multicore */ $vs_solr_xml = ""; $vs_solr_xml .= '<?xml version="1.0" encoding="UTF-8" ?>' . SolrConfiguration::nl(); $vs_solr_xml .= '<solr persistent="true">' . SolrConfiguration::nl(); $vs_solr_xml .= SolrConfiguration::tabs(1) . '<cores adminPath="/admin/cores">' . SolrConfiguration::nl(); foreach ($va_tables as $vs_table) { /* I don't like tablenums, so we use the table name to name the cores */ $vs_solr_xml .= SolrConfiguration::tabs(2) . '<core name="' . $vs_table . '" instanceDir="' . $vs_table . '" />' . SolrConfiguration::nl(); } $vs_solr_xml .= SolrConfiguration::tabs(1) . '</cores>' . SolrConfiguration::nl(); $vs_solr_xml .= '</solr>' . SolrConfiguration::nl(); /* try to write configuration file */ $vr_solr_xml_file = fopen($ps_solr_home_dir . "/solr.xml", 'w+'); // overwrite old one if (!is_resource($vr_solr_xml_file)) { die("Couldn't write to solr.xml file in Solr home directory. Please check the permissions.\n"); } fprintf($vr_solr_xml_file, "%s", $vs_solr_xml); fclose($vr_solr_xml_file); /* configure the cores */ foreach ($va_tables as $vs_table) { $t_instance = $po_datamodel->getTableInstance($vs_table); /* create core directory */ if (!file_exists($ps_solr_home_dir . "/" . $vs_table)) { if (!mkdir($ps_solr_home_dir . "/" . $vs_table, 0777)) { /* TODO: think about permissions */ die("Couldn't create directory in Solr home. Please check the permissions.\n"); } } /* create conf directory */ if (!file_exists($ps_solr_home_dir . "/" . $vs_table . "/conf")) { if (!mkdir($ps_solr_home_dir . "/" . $vs_table . "/conf", 0777)) { die("Couldn't create directory in core directory. Please check the permissions.\n"); } } /* create solrconfig.xml for this core */ $vr_solrconfig_xml_file = fopen($ps_solr_home_dir . "/" . $vs_table . "/conf/solrconfig.xml", 'w+'); if (!is_resource($vr_solrconfig_xml_file)) { die("Couldn't write to solrconfig.xml file for core {$vs_table}. Please check the permissions.\n"); } /* read template and copy it */ $va_solrconfig_xml_template = file(__CA_LIB_DIR__ . "/core/Search/Solr/solrplugin_templates/solrconfig.xml"); if (!is_array($va_solrconfig_xml_template)) { die("Couldn't read solrconfig.xml template."); } foreach ($va_solrconfig_xml_template as $vs_line) { fprintf($vr_solrconfig_xml_file, "%s", $vs_line); } fclose($vr_solrconfig_xml_file); /* create schema.xml for this core */ $vr_schema_xml_file = fopen($ps_solr_home_dir . "/" . $vs_table . "/conf/schema.xml", 'w+'); if (!is_resource($vr_schema_xml_file)) { die("Couldn't write to schema.xml file for core {$vs_table}. Please check the permissions.\n"); } /* read template, modify it, add table-specific fields and write to schema.xml configuration for this core */ $va_schema_xml_template = file(__CA_LIB_DIR__ . "/core/Search/Solr/solrplugin_templates/schema.xml"); if (!is_array($va_schema_xml_template)) { die("Couldn't read solrconfig.xml template."); } foreach ($va_schema_xml_template as $vs_line) { /* 1st replacement: core name */ if (strpos($vs_line, "CORE_NAME") !== false) { fprintf($vr_schema_xml_file, "%s", str_replace("CORE_NAME", $vs_table, $vs_line)); continue; } /* 2nd replacement: fields - the big part */ if (strpos($vs_line, "<!--FIELDS-->") !== false) { $vs_field_schema = ""; $vs_subject_table_copyfields = ""; /* the schema is very very hardcoded, so we have to create a design that still fits * when new metadata elements are created or sth like that. for now, we're just considering * the "straightforward" fields */ $va_schema_fields = array(); /* list of all fields created - is used for copyField directives after field block */ /* subject table */ /* we add the PK - this is used for incremental indexing */ $vs_field_schema .= SolrConfiguration::tabs(2) . '<field name="' . $vs_table . '.' . $t_instance->primaryKey() . '" type="int" indexed="true" stored="true" />' . SolrConfiguration::nl(); $vs_field_schema .= SolrConfiguration::tabs(2) . '<field name="' . $t_instance->primaryKey() . '" type="int" indexed="true" stored="true" />' . SolrConfiguration::nl(); $vs_subject_table_copyfields .= SolrConfiguration::tabs(1) . '<copyField source="' . $vs_table . '.' . $t_instance->primaryKey() . '" dest="' . $t_instance->primaryKey() . '" />' . SolrConfiguration::nl(); /* get fields-to-index from search indexing configuration */ if (!is_array($va_table_fields = $po_search_base->getFieldsToIndex($vs_table))) { $va_table_fields = array(); } $vn_table_num = $po_datamodel->getTableNum($vs_table); $va_attributes = null; if (is_array($va_table_fields)) { foreach ($va_table_fields as $vs_field_name => $va_field_options) { if (preg_match('!^_ca_attribute_(\\d+)$!', $vs_field_name, $va_matches)) { $t_element->load($va_matches[1]); $va_attributes[$t_element->getPrimaryKey()] = array('element_id' => $t_element->get('element_id'), 'element_code' => $t_element->get('element_code'), 'datatype' => $t_element->get('datatype')); } } } if (is_array($va_attributes)) { $va_metadata_fields = array(); foreach ($va_attributes as $vn_element_id => $va_element_info) { $va_metadata_fields += SolrConfiguration::getElementType($va_element_info); } /*set datatype for metadata elements in $va_table_fields array*/ foreach ($va_metadata_fields as $key => $value) { if (array_key_exists($key, $va_table_fields)) { unset($va_table_fields[$key]); } $va_table_fields[$key] = $value; } } /* we now have the current configuration */ /* since Solr supports live updates only if changes are 'backwards-compatible' * (i.e. no fields are deleted), we have to merge the current configuration with the * cached one, create the new configuration based upon that and cache it. * * Invocation of the command-line script support/utils/createSolrConfiguration.php, * however, creates a completely fresh configuration and caches it. */ $va_frontend_options = array('lifetime' => null, 'logging' => false, 'write_control' => true, 'automatic_cleaning_factor' => 0, 'automatic_serialization' => true); $vs_cache_dir = __CA_APP_DIR__ . '/tmp'; $va_backend_options = array('cache_dir' => $vs_cache_dir, 'file_locking' => true, 'read_control' => false, 'file_name_prefix' => 'ca_cache', 'cache_file_perm' => 0777); $vo_cache = Zend_Cache::factory('Core', 'File', $va_frontend_options, $va_backend_options); if (!($va_cache_data = $vo_cache->load('ca_search_indexing_info_' . $vs_table))) { $va_cache_data = array(); } if (!$pb_invoked_from_command_line) { $va_table_fields = array_merge($va_cache_data, $va_table_fields); } $vo_cache->save($va_table_fields, 'ca_search_indexing_info_' . $vs_table); if (is_array($va_table_fields)) { foreach ($va_table_fields as $vs_field_name => $va_field_options) { if (in_array("STORE", $va_field_options)) { $vb_field_is_stored = true; } else { $vb_field_is_stored = false; } if (in_array("DONT_TOKENIZE", $va_field_options)) { $vb_field_is_tokenized = false; } else { $vb_field_is_tokenized = true; } $va_schema_fields[] = $vs_table . '.' . SolrConfiguration::adjustFieldsToIndex($vs_field_name); if (in_array($va_field_options['type'], array('text', 'string'))) { $vs_type = $vb_field_is_tokenized ? 'text' : 'string'; } else { if (!isset($va_field_options['type']) && $t_instance->hasField($vs_field_name)) { // if the primary key is configured to be indexed in search_indexing.conf, ignore it here // (we add it anyway and solr doesn't like duplicate fields!) if ($t_instance->primaryKey() == $vs_field_name) { continue; } switch ($t_instance->getFieldInfo($vs_field_name, "FIELD_TYPE")) { case FT_TEXT: case FT_MEDIA: case FT_FILE: case FT_PASSWORD: case FT_VARS: $va_field_options['type'] = 'text'; break; case FT_NUMBER: case FT_TIME: case FT_TIMERANGE: case FT_TIMECODE: $va_field_options['type'] = 'float'; break; case FT_TIMESTAMP: case FT_DATETIME: case FT_HISTORIC_DATETIME: case FT_DATE: case FT_HISTORIC_DATE: case FT_DATERANGE: case FT_HISTORIC_DATERANGE: $va_field_options['type'] = 'daterange'; break; case FT_BIT: $va_field_options['type'] = 'bool'; break; default: $va_field_options['type'] = null; break; } } $vs_type = isset($va_field_options['type']) && $va_field_options['type'] ? $va_field_options['type'] : 'text'; } $vs_field_schema .= SolrConfiguration::tabs(2) . '<field name="' . $vs_table . '.' . SolrConfiguration::adjustFieldsToIndex($vs_field_name) . '" type="' . $vs_type; $vs_field_schema .= '" indexed="true" '; $vb_field_is_stored ? $vs_field_schema .= 'stored="true" ' : ($vs_field_schema .= 'stored="false" '); $vs_field_schema .= '/>' . SolrConfiguration::nl(); } } /* related tables */ $va_related_tables = $po_search_base->getRelatedIndexingTables($vs_table); foreach ($va_related_tables as $vs_related_table) { $va_related_table_fields = $po_search_base->getFieldsToIndex($vs_table, $vs_related_table); foreach ($va_related_table_fields as $vs_related_table_field => $va_related_table_field_options) { if (in_array("STORE", $va_related_table_field_options)) { $vb_field_is_stored = true; } else { $vb_field_is_stored = false; } if (in_array("DONT_TOKENIZE", $va_related_table_field_options)) { $vb_field_is_tokenized = false; } else { $vb_field_is_tokenized = true; } $va_schema_fields[] = $vs_related_table . '.' . $vs_related_table_field; $vs_field_schema .= SolrConfiguration::tabs(2) . '<field name="' . $vs_related_table . '.' . $vs_related_table_field . '" type="'; $vb_field_is_tokenized ? $vs_field_schema .= 'text' : ($vs_field_schema .= 'string'); $vs_field_schema .= '" indexed="true" '; $vb_field_is_stored ? $vs_field_schema .= 'stored="true" ' : ($vs_field_schema .= 'stored="false" '); $vs_field_schema .= '/>' . SolrConfiguration::nl(); } } /* copyfield directives * we use a single field in each index (called "text") where * all other fields are copied. the text field is the default * search field. it is used if a field name specification is * omitted in a search query. */ $vs_copyfields = ""; foreach ($va_schema_fields as $vs_schema_field) { $vs_copyfields .= SolrConfiguration::tabs(1) . '<copyField source="' . $vs_schema_field . '" dest="text" />' . SolrConfiguration::nl(); } // // Get access points // if (!is_array($va_access_points = $po_search_base->getAccessPoints($vs_table))) { $va_access_points = array(); } foreach ($va_access_points as $vs_access_point => $va_access_point_info) { foreach ($va_access_point_info['fields'] as $vn_i => $vs_schema_field) { $vs_copyfields .= SolrConfiguration::tabs(1) . '<copyField source="' . $vs_schema_field . '" dest="' . $vs_access_point . '" />' . SolrConfiguration::nl(); } $vs_field_schema .= SolrConfiguration::tabs(2) . '<field name="' . $vs_access_point . '" type="text" indexed="true" stored="true" multiValued="true"/>' . SolrConfiguration::nl(); } /* write field indexing config into file */ fprintf($vr_schema_xml_file, "%s", $vs_field_schema); continue; } /* 3rd replacement: uniquekey */ if (strpos($vs_line, "<!--KEY-->") !== false) { $vs_pk = $t_instance->primaryKey(); fprintf($vr_schema_xml_file, "%s", str_replace("<!--KEY-->", $vs_table . "." . $vs_pk, $vs_line)); continue; } /* 4th replacement: copyFields */ if (strpos($vs_line, "<!--COPYFIELDS-->") !== false) { /* $vs_copyfields *should* be set, otherwise the template has been messed up */ fprintf($vr_schema_xml_file, "%s", $vs_copyfields); // add copyField for the subject table fields so that the pk can be adressed in 2 ways: // "objects.object_id" or "object.id" fprintf($vr_schema_xml_file, "%s", $vs_subject_table_copyfields); continue; } /* "normal" line */ fprintf($vr_schema_xml_file, "%s", $vs_line); } fclose($vr_schema_xml_file); } }
private function _doQueriesForSqlSearch($po_rewritten_query, $pn_subject_tablenum, $ps_dest_table, $pn_level = 0, $pa_options = null) { // query is always of type Zend_Search_Lucene_Search_Query_Boolean $vn_i = 0; switch (get_class($po_rewritten_query)) { case 'Zend_Search_Lucene_Search_Query_MultiTerm': $va_elements = $po_rewritten_query->getTerms(); break; default: $va_elements = $po_rewritten_query->getSubqueries(); break; } $o_base = new SearchBase(); $va_old_signs = $po_rewritten_query->getSigns(); foreach ($va_elements as $o_lucene_query_element) { $vb_is_blank_search = false; if (is_null($va_old_signs)) { // if array is null then according to Zend Lucene all subqueries should be "are required"... so we AND them $vs_op = "AND"; } else { if (is_null($va_old_signs[$vn_i])) { // is the sign for a particular query is null then OR is (it is "neither required nor prohibited") $vs_op = 'OR'; } else { $vs_op = $va_old_signs[$vn_i] === false ? 'NOT' : 'AND'; // true sign indicated "required" (AND) operation, false indicated "prohibited" (NOT) operation } } if ($vn_i == 0) { $vs_op = 'OR'; } $va_direct_query_temp_tables = array(); // List of temporary tables created by direct search queries; tables listed here are dropped at the end of processing for the query element $pa_direct_sql_query_params = null; // set to array with values to use with direct SQL query placeholders or null to pass single standard table_num value as param (most queries just need this single value) $vs_direct_sql_query = null; $vn_direct_sql_target_table_num = $pn_subject_tablenum; switch ($vs_class = get_class($o_lucene_query_element)) { case 'Zend_Search_Lucene_Search_Query_Boolean': case 'Zend_Search_Lucene_Search_Query_MultiTerm': $this->_createTempTable('ca_sql_search_temp_' . $pn_level); if ($vs_op == 'AND' && $vn_i == 0) { $this->_doQueriesForSqlSearch($o_lucene_query_element, $pn_subject_tablenum, $ps_dest_table, $pn_level + 1); } else { $this->_doQueriesForSqlSearch($o_lucene_query_element, $pn_subject_tablenum, 'ca_sql_search_temp_' . $pn_level, $pn_level + 1); } // merge with current destination switch ($vs_op) { case 'AND': if ($vn_i > 0) { $this->_createTempTable("{$ps_dest_table}_acc"); $vs_sql = "\n\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO {$ps_dest_table}_acc\n\t\t\t\t\t\t\t\t\tSELECT mfs.row_id, SUM(mfs.boost)\n\t\t\t\t\t\t\t\t\tFROM {$ps_dest_table} mfs\n\t\t\t\t\t\t\t\t\tINNER JOIN ca_sql_search_temp_{$pn_level} AS ftmp1 ON ftmp1.row_id = mfs.row_id\n\t\t\t\t\t\t\t\t\tGROUP BY mfs.row_id\n\t\t\t\t\t\t\t\t"; $qr_res = $this->opo_db->query($vs_sql); $qr_res = $this->opo_db->query("TRUNCATE TABLE {$ps_dest_table}"); $qr_res = $this->opo_db->query("INSERT INTO {$ps_dest_table} SELECT row_id, boost FROM {$ps_dest_table}_acc"); $this->_dropTempTable("{$ps_dest_table}_acc"); } break; case 'NOT': $qr_res = $this->opo_db->query("SELECT row_id FROM ca_sql_search_temp_{$pn_level}"); if (is_array($va_ids = $qr_res->getAllFieldValues()) && sizeof($va_ids)) { $vs_sql = "\n\t\t\t\t\t\t\t\t\tDELETE FROM {$ps_dest_table} WHERE row_id IN (?)\n\t\t\t\t\t\t\t\t"; $qr_res = $this->opo_db->query($vs_sql, array($va_ids)); } break; default: case 'OR': $vs_sql = "\n\t\t\t\t\t\t\t\tINSERT IGNORE INTO {$ps_dest_table}\n\t\t\t\t\t\t\t\tSELECT row_id, SUM(boost)\n\t\t\t\t\t\t\t\tFROM ca_sql_search_temp_{$pn_level}\n\t\t\t\t\t\t\t\tGROUP BY row_id\n\t\t\t\t\t\t\t"; $qr_res = $this->opo_db->query($vs_sql); break; } $vn_i++; $this->_dropTempTable('ca_sql_search_temp_' . $pn_level); break; case 'Zend_Search_Lucene_Search_Query_Term': case 'Zend_Search_Lucene_Index_Term': case 'Zend_Search_Lucene_Search_Query_Phrase': case 'Zend_Search_Lucene_Search_Query_Range': $va_ft_terms = array(); $va_ft_like_terms = array(); $va_ft_stem_terms = array(); $vs_access_point = ''; $va_raw_terms = array(); switch (get_class($o_lucene_query_element)) { case 'Zend_Search_Lucene_Search_Query_Range': $va_lower_term = $o_lucene_query_element->getLowerTerm(); $va_upper_term = $o_lucene_query_element->getUpperTerm(); $va_element = $this->_getElementIDForAccessPoint($pn_subject_tablenum, $va_lower_term->field); $vn_direct_sql_target_table_num = $va_element['table_num']; $va_indexed_fields = $o_base->getFieldsToIndex($pn_subject_tablenum, $vn_direct_sql_target_table_num); $vn_root_element_id = $va_element['element_info']['hier_element_id']; if (!isset($va_indexed_fields['_ca_attribute_' . $va_element['element_id']]) && (!$vn_root_element_id || $vn_root_element_id && !isset($va_indexed_fields['_ca_attribute_' . $vn_root_element_id]))) { break 2; } // skip if not indexed switch ($va_element['datatype']) { case __CA_ATTRIBUTE_VALUE_GEOCODE__: $t_geocode = new GeocodeAttributeValue(); $va_parsed_value = $t_geocode->parseValue('[' . $va_lower_term->text . ']', $va_element['element_info']); $vs_lower_lat = $va_parsed_value['value_decimal1']; $vs_lower_long = $va_parsed_value['value_decimal2']; $va_parsed_value = $t_geocode->parseValue('[' . $va_upper_term->text . ']', $va_element['element_info']); $vs_upper_lat = $va_parsed_value['value_decimal1']; $vs_upper_long = $va_parsed_value['value_decimal2']; // mysql BETWEEN always wants the lower value first ... BETWEEN 5 AND 3 wouldn't match 4 ... So we swap the values if necessary if ($vs_upper_lat < $vs_lower_lat) { $tmp = $vs_upper_lat; $vs_upper_lat = $vs_lower_lat; $vs_lower_lat = $tmp; } if ($vs_upper_long < $vs_lower_long) { $tmp = $vs_upper_long; $vs_upper_long = $vs_lower_long; $vs_lower_long = $tmp; } $vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = " . intval($va_element['element_id']) . ") AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 BETWEEN " . floatval($vs_lower_lat) . " AND " . floatval($vs_upper_lat) . ")\n\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal2 BETWEEN " . floatval($vs_lower_long) . " AND " . floatval($vs_upper_long) . ")\t\n\t\t\t\t\t\t\t\t\t"; break; case __CA_ATTRIBUTE_VALUE_CURRENCY__: $t_cur = new CurrencyAttributeValue(); $va_parsed_value = $t_cur->parseValue($va_lower_term->text, $va_element['element_info']); $vs_currency = preg_replace('![^A-Z0-9]+!', '', $va_parsed_value['value_longtext1']); $vn_lower_val = $va_parsed_value['value_decimal1']; $va_parsed_value = $t_cur->parseValue($va_upper_term->text, $va_element['element_info']); $vn_upper_val = $va_parsed_value['value_decimal1']; $vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = " . intval($va_element['element_id']) . ") AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 BETWEEN " . floatval($vn_lower_val) . " AND " . floatval($vn_upper_val) . ")\n\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t(cav.value_longtext1 = '" . $this->opo_db->escape($vs_currency) . "')\n\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t"; break; case __CA_ATTRIBUTE_VALUE_TIMECODE__: $t_timecode = new TimecodeAttributeValue(); $va_parsed_value = $t_timecode->parseValue($va_lower_term->text, $va_element['element_info']); $vn_lower_val = $va_parsed_value['value_decimal1']; $va_parsed_value = $t_timecode->parseValue($va_upper_term->text, $va_element['element_info']); $vn_upper_val = $va_parsed_value['value_decimal1']; break; case __CA_ATTRIBUTE_VALUE_LENGTH__: $t_len = new LengthAttributeValue(); $va_parsed_value = $t_len->parseValue($va_lower_term->text, $va_element['element_info']); $vn_lower_val = $va_parsed_value['value_decimal1']; $va_parsed_value = $t_len->parseValue($va_upper_term->text, $va_element['element_info']); $vn_upper_val = $va_parsed_value['value_decimal1']; break; case __CA_ATTRIBUTE_VALUE_WEIGHT__: $t_weight = new WeightAttributeValue(); $va_parsed_value = $t_weight->parseValue($va_lower_term->text, $va_element['element_info']); $vn_lower_val = $va_parsed_value['value_decimal1']; $va_parsed_value = $t_weight->parseValue($va_upper_term->text, $va_element['element_info']); $vn_upper_val = $va_parsed_value['value_decimal1']; break; case __CA_ATTRIBUTE_VALUE_INTEGER__: $vn_lower_val = intval($va_lower_term->text); $vn_upper_val = intval($va_upper_term->text); $vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = " . intval($va_element['element_id']) . ") AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t(cav.value_integer1 BETWEEN " . floatval($vn_lower_val) . " AND " . floatval($vn_upper_val) . ")\n\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t"; break; case __CA_ATTRIBUTE_VALUE_NUMERIC__: $vn_lower_val = floatval($va_lower_term->text); $vn_upper_val = floatval($va_upper_term->text); break; } if (!$vs_direct_sql_query) { $vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t(cav.element_id = " . intval($va_element['element_id']) . ") AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 BETWEEN " . floatval($vn_lower_val) . " AND " . floatval($vn_upper_val) . ")\n\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t"; } break; case 'Zend_Search_Lucene_Search_Query_Phrase': if ($this->getOption('strictPhraseSearching')) { $vs_search_tokenizer_regex = $this->opo_search_config->get('search_tokenizer_regex'); $va_words = array(); foreach ($o_lucene_query_element->getQueryTerms() as $o_term) { if (!$vs_access_point && ($vs_field = $o_term->field)) { $vs_access_point = $vs_field; } $va_terms = preg_split("![{$vs_search_tokenizer_regex}]+!u", (string) $o_term->text); $va_raw_terms[] = (string) $o_term->text; foreach ($va_terms as $vs_term) { if (strlen($vs_escaped_text = $this->opo_db->escape($vs_term))) { $va_words[] = $vs_escaped_text; } } } if (!sizeof($va_words)) { continue 3; } $va_ap_tmp = explode(".", $vs_access_point); $vn_fld_table = $vn_fld_num = null; if (sizeof($va_ap_tmp) >= 2) { $va_element = $this->_getElementIDForAccessPoint($pn_subject_tablenum, $vs_access_point); if ($va_element) { $vs_fld_num = $va_element['field_num']; $vs_fld_table_num = $va_element['table_num']; $vs_fld_limit_sql = " AND (swi.field_table_num = {$vs_fld_table_num} AND swi.field_num = '{$vs_fld_num}')"; if (is_array($va_element['relationship_type_ids']) && sizeof($va_element['relationship_type_ids'])) { $vs_fld_limit_sql .= " AND (swi.rel_type_id IN (" . join(",", $va_element['relationship_type_ids']) . "))"; } } } $va_temp_tables = array(); $vn_w = 0; foreach ($va_words as $vs_word) { $vn_w++; $vs_temp_table = 'ca_sql_search_phrase_' . md5($pn_subject_tablenum . "/" . $vs_word . "/" . $vn_w); $this->_createTempTable($vs_temp_table); $vs_sql = "\n\t\t\t\t\t\t\t\t\tINSERT INTO {$vs_temp_table}\n\t\t\t\t\t\t\t\t\tSELECT swi.index_id + 1, 1\n\t\t\t\t\t\t\t\t\tFROM ca_sql_search_words sw \n\t\t\t\t\t\t\t\t\tINNER JOIN ca_sql_search_word_index AS swi ON sw.word_id = swi.word_id \n\t\t\t\t\t\t\t\t\t" . (sizeof($va_temp_tables) ? " INNER JOIN " . $va_temp_tables[sizeof($va_temp_tables) - 1] . " AS tt ON swi.index_id = tt.row_id" : "") . "\n\t\t\t\t\t\t\t\t\tWHERE \n\t\t\t\t\t\t\t\t\t\tsw.word = ? AND swi.table_num = ? {$vs_fld_limit_sql}\n \t\t\t\t\t\t\t\t\t\t" . ($this->getOption('omitPrivateIndexing') ? " AND swi.access = 0" : '') . "\n\t\t\t\t\t\t\t\t"; $qr_res = $this->opo_db->query($vs_sql, $vs_word, (int) $pn_subject_tablenum); $qr_count = $this->opo_db->query("SELECT count(*) c FROM {$vs_temp_table}"); if (!$qr_count->nextRow() || !(int) $qr_count->get('c')) { foreach ($va_temp_tables as $vs_temp_table) { $this->_dropTempTable($vs_temp_table); } break 2; } $va_temp_tables[] = $vs_temp_table; } $vs_results_temp_table = array_pop($va_temp_tables); $this->opo_db->query("UPDATE {$vs_results_temp_table} SET row_id = row_id - 1"); $va_direct_query_temp_tables[$vs_results_temp_table] = true; $vs_direct_sql_query = "SELECT swi.row_id, ca.boost \n\t\t\t\t\t\t\t\t\t\t\t\t\tFROM {$vs_results_temp_table} ca\n\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_sql_search_word_index AS swi ON swi.index_id = ca.row_id \n\t\t\t\t\t\t\t"; $pa_direct_sql_query_params = array(); // don't pass any params foreach ($va_temp_tables as $vs_temp_table) { $this->_dropTempTable($vs_temp_table); } break; } default: switch ($vs_class) { case 'Zend_Search_Lucene_Search_Query_Phrase': $va_term_objs = $o_lucene_query_element->getQueryTerms(); break; case 'Zend_Search_Lucene_Index_Term': $va_term_objs = array($o_lucene_query_element); break; default: $va_term_objs = array($o_lucene_query_element->getTerm()); break; } foreach ($va_term_objs as $o_term) { $va_access_point_info = $this->_getElementIDForAccessPoint($pn_subject_tablenum, $o_term->field); $vs_access_point = $va_access_point_info['access_point']; $vs_term = $o_term->text; if ($vs_access_point && mb_strtoupper($vs_term) == _t('[BLANK]')) { $t_ap = $this->opo_datamodel->getInstanceByTableNum($va_access_point_info['table_num'], true); if (is_a($t_ap, 'BaseLabel')) { // labels have the literal text "[Blank]" indexed to "blank" to indicate blank-ness $vb_is_blank_search = false; $vs_term = _t('blank'); } else { $vb_is_blank_search = true; break; } } $va_terms = array($vs_term); //$this->_tokenize($vs_term, true, $vn_i); $vb_has_wildcard = (bool) preg_match('!\\*$!', $vs_term); $vb_output_term = false; foreach ($va_terms as $vs_term) { if ($vb_has_wildcard) { $vs_term .= '*'; } if (in_array(trim(mb_strtolower($vs_term, 'UTF-8')), WLPlugSearchEngineSqlSearch::$s_stop_words)) { continue; } $vs_stripped_term = preg_replace('!\\*+$!u', '', $vs_term); if ($vb_has_wildcard) { $va_ft_like_terms[] = $vs_stripped_term; } else { // do stemming $vb_do_stemming = $this->opb_do_stemming; if (mb_substr($vs_term, -1) == '|') { $vs_term = mb_substr($vs_term, 0, mb_strlen($vs_term) - 1); $vb_do_stemming = false; } if ($vb_do_stemming) { $vs_to_stem = preg_replace('!\\*$!u', '', $vs_term); if (!preg_match('!y$!u', $vs_to_stem) && !preg_match('![0-9]+!', $vs_to_stem)) { // don't stem things ending in 'y' as that can cause problems (eg "Bowery" becomes "Boweri") if (!($vs_stem = trim($this->opo_stemmer->stem($vs_to_stem)))) { $vs_stem = (string) $vs_term; } $va_ft_stem_terms[] = "'" . $this->opo_db->escape($vs_stem) . "'"; } else { $va_ft_terms[] = '"' . $this->opo_db->escape($vs_term) . '"'; } } else { $va_ft_terms[] = '"' . $this->opo_db->escape($vs_term) . '"'; } } $vb_output_term = true; } if ($vb_output_term) { $va_raw_terms[] = $vs_term; } } break; } $vs_fld_num = $vs_table_num = $t_table = null; $vb_ft_bit_optimization = false; if ($vs_access_point) { list($vs_table, $vs_field, $vs_sub_field) = explode('.', $vs_access_point); if (in_array($vs_table, array('created', 'modified'))) { $o_tep = new TimeExpressionParser(); $vs_date = join(' ', $va_raw_terms); if (!$o_tep->parse($vs_date)) { break; } $va_range = $o_tep->getUnixTimestamps(); $vn_user_id = null; if ($vs_field = trim($vs_field)) { if (!is_int($vs_field)) { $t_user = new ca_users(); if ($t_user->load(array("user_name" => $vs_field))) { $vn_user_id = (int) $t_user->getPrimaryKey(); } } else { $vn_user_id = (int) $vs_field; } } $vs_user_sql = $vn_user_id ? " AND (ccl.user_id = " . (int) $vn_user_id . ")" : ""; switch ($vs_table) { case 'created': $vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\tSELECT ccl.logged_row_id row_id, 1\n\t\t\t\t\t\t\t\t\t\t\tFROM ca_change_log ccl\n\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t(ccl.log_datetime BETWEEN " . (int) $va_range['start'] . " AND " . (int) $va_range['end'] . ")\n\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t(ccl.logged_table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t(ccl.changetype = 'I')\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_user_sql}\n\t\t\t\t\t\t\t\t\t\t"; break; case 'modified': $vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\tSELECT ccl.logged_row_id row_id, 1\n\t\t\t\t\t\t\t\t\t\t\tFROM ca_change_log ccl\n\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t(ccl.log_datetime BETWEEN " . (int) $va_range['start'] . " AND " . (int) $va_range['end'] . ")\n\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t(ccl.logged_table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t(ccl.changetype = 'U')\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_user_sql}\n\t\t\t\t\t\t\t\t\t\tUNION\n\t\t\t\t\t\t\t\t\t\t\tSELECT ccls.subject_row_id row_id, 1\n\t\t\t\t\t\t\t\t\t\t\tFROM ca_change_log ccl\n\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_change_log_subjects AS ccls ON ccls.log_id = ccl.log_id\n\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t(ccl.log_datetime BETWEEN " . (int) $va_range['start'] . " AND " . (int) $va_range['end'] . ")\n\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t(ccls.subject_table_num = {$pn_subject_tablenum})\n\t\t\t\t\t\t\t\t\t\t\t\t{$vs_user_sql}\n\t\t\t\t\t\t\t\t\t\t"; break; } } else { if ($vs_table && $vs_field && ($t_table = $this->opo_datamodel->getInstanceByTableName($vs_table, true))) { $vs_table_num = $t_table->tableNum(); if (is_numeric($vs_field)) { $vs_fld_num = 'I' . $vs_field; $vn_fld_num = (int) $vs_field; } else { $vn_fld_num = $this->getFieldNum($vs_table, $vs_field); $vs_fld_num = 'I' . $vn_fld_num; $vn_direct_sql_target_table_num = $vs_table_num; if (!strlen($vn_fld_num)) { $t_element = new ca_metadata_elements(); if ($t_element->load(array('element_code' => $vs_sub_field ? $vs_sub_field : $vs_field))) { $va_indexed_fields = $o_base->getFieldsToIndex($pn_subject_tablenum, $vn_direct_sql_target_table_num); $vn_fld_num = $t_element->getPrimaryKey(); $vn_root_element_id = $t_element->get('hier_element_id'); if (!isset($va_indexed_fields['_ca_attribute_' . $vn_fld_num]) && (!$vn_root_element_id || $vn_root_element_id && !isset($va_indexed_fields['_ca_attribute_' . $vn_root_element_id]))) { break 2; } // skip if not indexed $vs_fld_num = 'A' . $vn_fld_num; if (!$vb_is_blank_search) { // // For certain types of attributes we can directly query the // attributes in the database rather than using the full text index // This allows us to do "intelligent" querying... for example on date ranges // parsed from natural language input and for length dimensions using unit conversion // switch ($t_element->get('datatype')) { case __CA_ATTRIBUTE_VALUE_DATERANGE__: $vb_all_numbers = true; foreach ($va_raw_terms as $vs_term) { if (!is_numeric($vs_term)) { $vb_all_numbers = false; break; } } $vs_raw_term = join(' ', $va_raw_terms); $vb_exact = $vs_raw_term[0] == "#" ? true : false; // dates prepended by "#" are considered "exact" or "contained - the matched dates must be wholly contained by the search term if ($vb_exact) { $vs_raw_term = substr($vs_raw_term, 1); if ($this->opo_tep->parse($vs_raw_term)) { $va_dates = $this->opo_tep->getHistoricTimestamps(); $vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 BETWEEN " . floatval($va_dates['start']) . " AND " . floatval($va_dates['end']) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal2 BETWEEN " . floatval($va_dates['start']) . " AND " . floatval($va_dates['end']) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t"; } } else { if ($this->opo_tep->parse($vs_raw_term)) { $va_dates = $this->opo_tep->getHistoricTimestamps(); $vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 BETWEEN " . floatval($va_dates['start']) . " AND " . floatval($va_dates['end']) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal2 BETWEEN " . floatval($va_dates['start']) . " AND " . floatval($va_dates['end']) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 <= " . floatval($va_dates['start']) . " AND cav.value_decimal2 >= " . floatval($va_dates['end']) . ")\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t"; } } break; case __CA_ATTRIBUTE_VALUE_GEOCODE__: // At this point $va_raw_terms has been tokenized by Lucene into oblivion // and is also dependent on the search_tokenizer_regex so we can't really do anything with it. // We now build our own un-tokenized term array instead. caParseGISSearch() can handle it. $va_gis_terms = array(); foreach ($o_lucene_query_element->getQueryTerms() as $o_term) { $va_gis_terms[] = trim((string) $o_term->text); } if ($va_coords = caParseGISSearch(join(' ', $va_gis_terms))) { $vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 BETWEEN {$va_coords['min_latitude']} AND {$va_coords['max_latitude']})\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal2 BETWEEN {$va_coords['min_longitude']} AND {$va_coords['max_longitude']})\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t"; } break; case __CA_ATTRIBUTE_VALUE_CURRENCY__: $t_cur = new CurrencyAttributeValue(); $va_parsed_value = $t_cur->parseValue(join(' ', $va_raw_terms), $t_element->getFieldValuesArray()); $vn_amount = $va_parsed_value['value_decimal1']; $vs_currency = preg_replace('![^A-Z0-9]+!', '', $va_parsed_value['value_longtext1']); $vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 = " . floatval($vn_amount) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_longtext1 = '" . $this->opo_db->escape($vs_currency) . "')\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t"; break; case __CA_ATTRIBUTE_VALUE_LENGTH__: // If it looks like a dimension that has been tokenized by Lucene // into oblivion rehydrate it here. try { switch (sizeof($va_raw_terms)) { case 2: $vs_dimension = $va_raw_terms[0] . caGetDecimalSeparator() . $va_raw_terms[1]; break; case 3: $vs_dimension = $va_raw_terms[0] . caGetDecimalSeparator() . $va_raw_terms[1] . " " . $va_raw_terms[2]; break; default: $vs_dimension = join(' ', $va_raw_terms); } $vo_parsed_measurement = caParseLengthDimension($vs_dimension); $vn_len = $vo_parsed_measurement->convertTo('METER', 6, 'en_US'); } catch (Exception $e) { $vs_direct_sql_query = null; break; } $vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 = " . floatval($vn_len) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t"; break; case __CA_ATTRIBUTE_VALUE_WEIGHT__: // If it looks like a weight that has been tokenized by Lucene // into oblivion rehydrate it here. try { switch (sizeof($va_raw_terms)) { case 2: $vs_dimension = $va_raw_terms[0] . caGetDecimalSeparator() . $va_raw_terms[1]; break; case 3: $vs_dimension = $va_raw_terms[0] . caGetDecimalSeparator() . $va_raw_terms[1] . " " . $va_raw_terms[2]; break; default: $vs_dimension = join(' ', $va_raw_terms); } $vo_parsed_measurement = caParseWeightDimension($vs_dimension); $vn_weight = $vo_parsed_measurement->convertTo('KILOGRAM', 6, 'en_US'); } catch (Exception $e) { $vs_direct_sql_query = null; break; } $vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 = " . floatval($vn_weight) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t"; break; case __CA_ATTRIBUTE_VALUE_TIMECODE__: $t_timecode = new TimecodeAttributeValue(); $va_parsed_value = $t_timecode->parseValue(join(' ', $va_raw_terms), $t_element->getFieldValuesArray()); $vn_timecode = $va_parsed_value['value_decimal1']; $vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 = " . floatval($vn_timecode) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t"; break; case __CA_ATTRIBUTE_VALUE_INTEGER__: $vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_integer1 = " . intval(array_shift($va_raw_terms)) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t"; break; case __CA_ATTRIBUTE_VALUE_NUMERIC__: $vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT ca.row_id, 1\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM ca_attribute_values cav\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN ca_attributes AS ca ON ca.attribute_id = cav.attribute_id\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.element_id = {$vn_fld_num}) AND (ca.table_num = ?)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t(cav.value_decimal1 = " . floatval(array_shift($va_raw_terms)) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t"; break; } } } else { // neither table fields nor elements, i.e. 'virtual' fields like _count should $vn_fld_num = false; $vs_fld_num = $vs_field; } } } if (($vs_intrinsic_field_name = $t_table->fieldName($vn_fld_num)) && ($vn_intrinsic_type = $t_table->getFieldInfo($vs_intrinsic_field_name, 'FIELD_TYPE')) == FT_BIT) { $vb_ft_bit_optimization = true; } elseif ($vn_intrinsic_type == FT_HISTORIC_DATERANGE) { $vb_all_numbers = true; foreach ($va_raw_terms as $vs_term) { if (!is_numeric($vs_term)) { $vb_all_numbers = false; break; } } $vs_date_start_fld = $t_table->getFieldInfo($vs_intrinsic_field_name, 'START'); $vs_date_end_fld = $t_table->getFieldInfo($vs_intrinsic_field_name, 'END'); $vs_raw_term = join(' ', $va_raw_terms); $vb_exact = $vs_raw_term[0] == "#" ? true : false; // dates prepended by "#" are considered "exact" or "contained - the matched dates must be wholly contained by the search term if ($vb_exact) { $vs_raw_term = substr($vs_raw_term, 1); if ($this->opo_tep->parse($vs_raw_term)) { $va_dates = $this->opo_tep->getHistoricTimestamps(); $vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $t_table->primaryKey() . ", 1\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $t_table->tableName() . "\n\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t({$vs_date_start_fld} BETWEEN " . floatval($va_dates['start']) . " AND " . floatval($va_dates['end']) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t({$vs_date_end_fld} BETWEEN " . floatval($va_dates['start']) . " AND " . floatval($va_dates['end']) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t"; } } else { if ($this->opo_tep->parse($vs_raw_term)) { $va_dates = $this->opo_tep->getHistoricTimestamps(); $vs_direct_sql_query = "\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT " . $t_table->primaryKey() . ", 1\n\t\t\t\t\t\t\t\t\t\t\t\tFROM " . $t_table->tableName() . "\n\t\t\t\t\t\t\t\t\t\t\t\t^JOIN\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t({$vs_date_start_fld} BETWEEN " . floatval($va_dates['start']) . " AND " . floatval($va_dates['end']) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t({$vs_date_end_fld} BETWEEN " . floatval($va_dates['start']) . " AND " . floatval($va_dates['end']) . ")\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tOR\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t({$vs_date_start_fld} <= " . floatval($va_dates['start']) . " AND {$vs_date_end_fld} >= " . floatval($va_dates['end']) . ")\t\n\t\t\t\t\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\t"; } } $pa_direct_sql_query_params = array(); } } } } // // If we're querying on the fulltext index then we need to construct // the query here... if we already have a direct SQL query to run then we can skip this // if ($vb_is_blank_search) { $va_sql_where[] = "((swi.field_table_num = " . intval($vs_table_num) . ") AND (swi.field_num = '{$vs_fld_num}') AND (swi.word_id = 0))"; if (!sizeof($va_sql_where)) { continue; } $vs_sql_where = join(' OR ', $va_sql_where); } elseif (!$vs_direct_sql_query) { $va_sql_where = array(); if (sizeof($va_ft_terms)) { if ($t_table && strlen($vs_fld_num) > 1) { $va_sql_where[] = "((swi.field_table_num = " . intval($vs_table_num) . ") AND (swi.field_num = '{$vs_fld_num}') AND (sw.word IN (" . join(',', $va_ft_terms) . ")))"; } else { if (sizeof($va_ft_terms) == 1) { $va_sql_where[] = "(sw.word = " . $va_ft_terms[0] . ")"; } else { $va_sql_where[] = "(sw.word IN (" . join(',', $va_ft_terms) . "))"; } } } if (sizeof($va_ft_like_terms)) { $va_tmp = array(); foreach ($va_ft_like_terms as $vs_term) { if ($vb_ft_bit_optimization) { $va_tmp[] = '(sw.word = \' ' . $this->opo_db->escape(trim($vs_term)) . ' \')'; } else { $va_tmp[] = '(sw.word LIKE \'' . $this->opo_db->escape(trim($vs_term)) . '%\')'; } } if ($t_table && strlen($vs_fld_num) > 1) { $va_sql_where[] = "((swi.field_table_num = " . intval($vs_table_num) . ") AND (swi.field_num = '{$vs_fld_num}') AND (" . join(' AND ', $va_tmp) . "))"; } else { $va_sql_where[] = "(" . join(' AND ', $va_tmp) . ")"; } } if (sizeof($va_ft_stem_terms)) { if ($t_table && strlen($vs_fld_num) > 1) { $va_sql_where[] = "((swi.field_table_num = " . intval($vs_table_num) . ") AND (swi.field_num = '{$vs_fld_num}') AND (sw.stem IN (" . join(',', $va_ft_stem_terms) . ")))"; } else { $va_sql_where[] = "(sw.stem IN (" . join(',', $va_ft_stem_terms) . "))"; } } if (!sizeof($va_sql_where)) { continue; } $vs_sql_where = join(' OR ', $va_sql_where); } else { $va_ft_terms = $va_ft_like_terms = $va_ft_like_terms = array(); } $vs_rel_type_id_sql = null; if (is_array($va_access_point_info['relationship_type_ids']) && sizeof($va_access_point_info['relationship_type_ids'])) { $vs_rel_type_id_sql = " AND (swi.rel_type_id IN (" . join(",", $va_access_point_info['relationship_type_ids']) . "))"; } if (!$vs_fld_num && is_array($va_restrict_to_fields = caGetOption('restrictSearchToFields', $pa_options, null)) && sizeof($va_restrict_to_fields)) { $va_field_restrict_sql = array(); foreach ($va_restrict_to_fields as $va_restrict) { $va_field_restrict_sql[] = "((swi.field_table_num = " . intval($va_restrict['table_num']) . ") AND (swi.field_num = '" . $va_restrict['field_num'] . "'))"; } $vs_sql_where .= " AND (" . join(" OR ", $va_field_restrict_sql) . ")"; } $va_join = array(); if ($vn_direct_sql_target_table_num != $pn_subject_tablenum) { // We're doing direct queries on metadata in a related table, fun! // Now let's rewrite the direct query to work... if ($t_target = $this->opo_datamodel->getInstanceByTableNum($vn_direct_sql_target_table_num, true)) { // First we create the join from the related table to our subject $vs_target_table_name = $t_target->tableName(); $va_path = array_keys($this->opo_datamodel->getPath($vn_direct_sql_target_table_num, $pn_subject_tablenum)); $vs_left_table = array_shift($va_path); $vn_cj = 0; foreach ($va_path as $vs_right_table) { if (sizeof($va_rels = $this->opo_datamodel->getRelationships($vs_left_table, $vs_right_table)) > 0) { $va_join[] = "INNER JOIN {$vs_right_table} ON {$vs_right_table}." . $va_rels[$vs_left_table][$vs_right_table][0][1] . " = " . ($vn_cj == 0 ? 'ca.row_id' : "{$vs_left_table}." . $va_rels[$vs_left_table][$vs_right_table][0][0]); } $vs_left_table = $vs_right_table; $vn_cj++; } // Next we rewrite the key we're pulling to be from our subject $vs_direct_sql_query = str_replace("SELECT ca.row_id", "SELECT " . $this->opo_datamodel->primaryKey($pn_subject_tablenum, true), $vs_direct_sql_query); // Finally we pray } } if ($vn_i == 0) { if ($vs_direct_sql_query) { $vs_direct_sql_query = str_replace('^JOIN', join("\n", $va_join), $vs_direct_sql_query); $vs_sql = "INSERT IGNORE INTO {$ps_dest_table} {$vs_direct_sql_query}"; if (strpos($vs_sql, '?') !== false && (!is_array($pa_direct_sql_query_params) || sizeof($pa_direct_sql_query_params) == 0)) { $pa_direct_sql_query_params = array($vn_direct_sql_target_table_num != $pn_subject_tablenum ? $vn_direct_sql_target_table_num : (int) $pn_subject_tablenum); } } else { $vs_sql = "\n\t\t\t\t\t\t\t\tINSERT IGNORE INTO {$ps_dest_table}\n\t\t\t\t\t\t\t\tSELECT swi.row_id, SUM(swi.boost)\n\t\t\t\t\t\t\t\tFROM ca_sql_search_word_index swi\n\t\t\t\t\t\t\t\t" . (!$vb_is_blank_search ? "INNER JOIN ca_sql_search_words AS sw ON sw.word_id = swi.word_id" : '') . "\n\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t{$vs_sql_where}\n\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\tswi.table_num = ?\n\t\t\t\t\t\t\t\t\t{$vs_rel_type_id_sql}\n\t\t\t\t\t\t\t\t\t" . ($this->getOption('omitPrivateIndexing') ? " AND swi.access = 0" : '') . "\n\t\t\t\t\t\t\t\tGROUP BY swi.row_id\n\t\t\t\t\t\t\t"; $pa_direct_sql_query_params = array((int) $pn_subject_tablenum); } if (($vn_num_terms = sizeof($va_ft_terms) + sizeof($va_ft_like_terms) + sizeof($va_ft_stem_terms)) > 1 && !$vs_direct_sql_query) { $vs_sql .= " HAVING count(distinct sw.word_id) = {$vn_num_terms}"; } $t = new Timer(); $pa_direct_sql_query_params = is_array($pa_direct_sql_query_params) ? $pa_direct_sql_query_params : array(); if (strpos($vs_sql, '?') === false) { $pa_direct_sql_query_params = array(); } $this->opo_db->query($vs_sql, $pa_direct_sql_query_params); $vn_i++; if ($this->debug) { Debug::msg('FIRST: ' . $vs_sql . " [{$pn_subject_tablenum}] " . $t->GetTime(4)); } } else { switch ($vs_op) { case 'AND': if ($vs_direct_sql_query) { if ($vn_direct_sql_target_table_num != $pn_subject_tablenum) { array_push($va_join, "INNER JOIN {$ps_dest_table} AS ftmp1 ON ftmp1.row_id = " . $this->opo_datamodel->primaryKey($pn_subject_tablenum, true)); } else { array_unshift($va_join, "INNER JOIN {$ps_dest_table} AS ftmp1 ON ftmp1.row_id = ca.row_id"); } $vs_direct_sql_query = str_replace('^JOIN', join("\n", $va_join), $vs_direct_sql_query); $pa_direct_sql_query_params = array($vn_direct_sql_target_table_num != $pn_subject_tablenum ? $vn_direct_sql_target_table_num : (int) $pn_subject_tablenum); } $vs_sql = $vs_direct_sql_query ? "{$vs_direct_sql_query}" : "\n\t\t\t\t\t\t\t\t\tSELECT swi.row_id\n\t\t\t\t\t\t\t\t\tFROM ca_sql_search_word_index swi\n\t\t\t\t\t\t\t\t\tINNER JOIN ca_sql_search_words AS sw ON sw.word_id = swi.word_id\n\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t{$vs_sql_where}\n\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\tswi.table_num = ?\n\t\t\t\t\t\t\t\t\t\t{$vs_rel_type_id_sql}\n\t\t\t\t\t\t\t\t\t\t" . ($this->getOption('omitPrivateIndexing') ? " AND swi.access = 0" : '') . "\n\t\t\t\t\t\t\t\t\tGROUP BY swi.row_id\n\t\t\t\t\t\t\t\t"; if (($vn_num_terms = sizeof($va_ft_terms) + sizeof($va_ft_like_terms) + sizeof($va_ft_stem_terms)) > 1) { $vs_sql .= " HAVING count(distinct sw.word_id) = {$vn_num_terms}"; } $t = new Timer(); $pa_direct_sql_query_params = is_array($pa_direct_sql_query_params) ? $pa_direct_sql_query_params : array((int) $pn_subject_tablenum); if (strpos($vs_sql, '?') === false) { $pa_direct_sql_query_params = array(); } $qr_res = $this->opo_db->query($vs_sql, $pa_direct_sql_query_params); if ($this->debug) { Debug::msg('AND: ' . $vs_sql . ' ' . $t->GetTime(4) . ' ' . $qr_res->numRows()); } if (is_array($va_ids = $qr_res->getAllFieldValues($vs_direct_sql_query && $vn_direct_sql_target_table_num != $pn_subject_tablenum ? $this->opo_datamodel->primaryKey($pn_subject_tablenum) : 'row_id')) && sizeof($va_ids)) { $vs_sql = "DELETE FROM {$ps_dest_table} WHERE row_id NOT IN (?)"; $qr_res = $this->opo_db->query($vs_sql, array($va_ids)); if ($this->debug) { Debug::msg('AND DELETE: ' . $vs_sql . ' ' . $t->GetTime(4)); } } else { // we don't have any results left, ie. our AND query should yield an empty result $this->opo_db->query("DELETE FROM {$ps_dest_table}"); } $vn_i++; break; case 'NOT': if ($vs_direct_sql_query) { $vs_direct_sql_query = str_replace('^JOIN', join("\n", $va_join), $vs_direct_sql_query); $pa_direct_sql_query_params = array($vn_direct_sql_target_table_num != $pn_subject_tablenum ? $vn_direct_sql_target_table_num : (int) $pn_subject_tablenum); } $vs_sql = "\n\t\t\t\t\t\t\t\t\tSELECT row_id\n\t\t\t\t\t\t\t\t\tFROM ca_sql_search_words sw\n\t\t\t\t\t\t\t\t\tINNER JOIN ca_sql_search_word_index AS swi ON sw.word_id = swi.word_id\n\t\t\t\t\t\t\t\t\tWHERE \n\t\t\t\t\t\t\t\t\t\t" . ($vs_sql_where ? "{$vs_sql_where} AND " : "") . " swi.table_num = ? \n\t\t\t\t\t\t\t\t\t\t{$vs_rel_type_id_sql}\n\t\t\t\t\t\t\t\t\t\t" . ($this->getOption('omitPrivateIndexing') ? " AND swi.access = 0" : ''); $pa_direct_sql_query_params = is_array($pa_direct_sql_query_params) ? $pa_direct_sql_query_params : array((int) $pn_subject_tablenum); if (strpos($vs_sql, '?') === false) { $pa_direct_sql_query_params = array(); } $qr_res = $this->opo_db->query($vs_sql, $pa_direct_sql_query_params); $va_ids = $qr_res->getAllFieldValues($vs_direct_sql_query && $vn_direct_sql_target_table_num != $pn_subject_tablenum ? $this->opo_datamodel->primaryKey($pn_subject_tablenum) : 'row_id'); if (sizeof($va_ids) > 0) { $vs_sql = "\n\t\t\t\t\t\t\t\t\t\tDELETE FROM {$ps_dest_table} \n\t\t\t\t\t\t\t\t\t\tWHERE \n\t\t\t\t\t\t\t\t\t\t\trow_id IN (?)\n\t\t\t\t\t\t\t\t\t"; if ($this->debug) { Debug::msg('NOT ' . $vs_sql); } $qr_res = $this->opo_db->query($vs_sql, array($va_ids)); } $vn_i++; break; default: case 'OR': if ($vs_direct_sql_query) { $vs_direct_sql_query = str_replace('^JOIN', join("\n", $va_join), $vs_direct_sql_query); $pa_direct_sql_query_params = array($vn_direct_sql_target_table_num != $pn_subject_tablenum ? $vn_direct_sql_target_table_num : (int) $pn_subject_tablenum); } $vs_sql = $vs_direct_sql_query ? "INSERT IGNORE INTO {$ps_dest_table} {$vs_direct_sql_query}" : "\n\t\t\t\t\t\t\t\t\tINSERT IGNORE INTO {$ps_dest_table}\n\t\t\t\t\t\t\t\t\tSELECT swi.row_id, SUM(swi.boost)\n\t\t\t\t\t\t\t\t\tFROM ca_sql_search_word_index swi\n\t\t\t\t\t\t\t\t\tINNER JOIN ca_sql_search_words AS sw ON sw.word_id = swi.word_id\n\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t{$vs_sql_where}\n\t\t\t\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\t\t\t\tswi.table_num = ?\n\t\t\t\t\t\t\t\t\t\t{$vs_rel_type_id_sql}\n\t\t\t\t\t\t\t\t\t\t" . ($this->getOption('omitPrivateIndexing') ? " AND swi.access = 0" : '') . "\n\t\t\t\t\t\t\t\t\tGROUP BY\n\t\t\t\t\t\t\t\t\t\tswi.row_id\n\t\t\t\t\t\t\t\t"; if ($this->debug) { Debug::msg('OR ' . $vs_sql); } $vn_i++; $pa_direct_sql_query_params = is_array($pa_direct_sql_query_params) ? $pa_direct_sql_query_params : array((int) $pn_subject_tablenum); if (strpos($vs_sql, '?') === false) { $pa_direct_sql_query_params = array(); } $qr_res = $this->opo_db->query($vs_sql, $pa_direct_sql_query_params); break; } } // Drop any temporary tables created by direct search queries foreach (array_keys($va_direct_query_temp_tables) as $vs_temp_table_to_drop) { $this->_dropTempTable($vs_temp_table_to_drop); } break; default: //print get_class($o_lucene_query_element); break; } } }
public function getPage() { $sbase = new SearchBase(); $arrFilter = $sbase->buildFilter(); $filter = $arrFilter["where"]; $column = $arrFilter["extra_column"]; $join = $arrFilter["extra_join"]; $isSearchAttachment = isset($_REQUEST["bulk_resume"]) && $_REQUEST["bulk_resume"]; if ($isSearchAttachment) { $sql = sprintf("SELECT\r\n attachment.attachment_id AS attachmentID,\r\n attachment.data_item_id AS candidateID,\r\n attachment.title AS title,\r\n attachment.text AS text,\r\n candidate.first_name AS firstName,\r\n candidate.last_name AS lastName,\r\n candidate.city AS city,\r\n candidate.state AS state,\r\n DATE_FORMAT(\r\n candidate.date_created, '%%m-%%d-%%y'\r\n ) AS dateCreated,\r\n candidate.date_created AS dateCreatedSort,\r\n DATE_FORMAT(\r\n candidate.date_modified, '%%m-%%d-%%y'\r\n ) AS dateModified,\r\n candidate.date_modified AS dateModifiedSort,\r\n owner_user.first_name AS ownerFirstName,\r\n owner_user.last_name AS ownerLastName,\r\n CONCAT(owner_user.last_name, owner_user.first_name) AS ownerSort\r\n %s\r\n FROM\r\n attachment\r\n LEFT JOIN candidate\r\n ON attachment.data_item_id = candidate.candidate_id\r\n AND attachment.site_id = candidate.site_id\r\n LEFT JOIN user AS owner_user\r\n ON candidate.owner = owner_user.user_id\r\n %s\r\n WHERE\r\n resume = 1\r\n AND\r\n %s\r\n AND\r\n (attachment.data_item_type = %s OR attachment.data_item_type = %s)\r\n AND\r\n attachment.site_id = %s\r\n AND\r\n (ISNULL(candidate.is_admin_hidden) OR (candidate.is_admin_hidden = 0))\r\n AND\r\n (ISNULL(candidate.is_active) OR (candidate.is_active = 1))\r\n %s\r\n ORDER BY\r\n %s %s\r\n LIMIT %s, %s", empty($column) ? "" : ",{$column}", $join, $this->_WHERE, DATA_ITEM_CANDIDATE, DATA_ITEM_BULKRESUME, $this->_siteID, $filter, $this->_sortBy, $this->_sortDirection, $this->_thisPageStartRow, $this->_rowsPerPage); } else { $sql = sprintf("SELECT\r\n attachment.attachment_id AS attachmentID,\r\n attachment.data_item_id AS candidateID,\r\n attachment.title AS title,\r\n attachment.text AS text,\r\n candidate.first_name AS firstName,\r\n candidate.last_name AS lastName,\r\n candidate.city AS city,\r\n candidate.state AS state,\r\n DATE_FORMAT(\r\n candidate.date_created, '%%m-%%d-%%y'\r\n ) AS dateCreated,\r\n candidate.date_created AS dateCreatedSort,\r\n DATE_FORMAT(\r\n candidate.date_modified, '%%m-%%d-%%y'\r\n ) AS dateModified,\r\n candidate.date_modified AS dateModifiedSort,\r\n owner_user.first_name AS ownerFirstName,\r\n owner_user.last_name AS ownerLastName,\r\n CONCAT(owner_user.last_name, owner_user.first_name) AS ownerSort\r\n %s\r\n FROM\r\n attachment\r\n LEFT JOIN candidate\r\n ON attachment.data_item_id = candidate.candidate_id\r\n AND attachment.site_id = candidate.site_id\r\n LEFT JOIN user AS owner_user\r\n ON candidate.owner = owner_user.user_id\r\n %s\r\n WHERE\r\n resume = 1\r\n AND\r\n %s\r\n AND\r\n (attachment.data_item_type = %s)\r\n AND\r\n attachment.site_id = %s\r\n AND\r\n (ISNULL(candidate.is_admin_hidden) OR (candidate.is_admin_hidden = 0))\r\n AND\r\n (ISNULL(candidate.is_active) OR (candidate.is_active = 1))\r\n %s\r\n ORDER BY\r\n %s %s\r\n LIMIT %s, %s", empty($column) ? "" : ",{$column}", $join, $this->_WHERE, DATA_ITEM_CANDIDATE, $this->_siteID, $filter, $this->_sortBy, $this->_sortDirection, $this->_thisPageStartRow, $this->_rowsPerPage); } return $this->_db->getAllAssoc($sql); }
public function __construct($siteID) { parent::__construct("contact"); $this->_siteID = $siteID; $this->_db = DatabaseConnection::getInstance(); //FIXME: Library code Session dependencies suck. $this->_userID = $_SESSION['CATS']->getUserID(); }