Differences

This shows you the differences between two versions of the page.

--- admin:indexing:powersearch:sql [2011/01/24 00:17]
els
+++ admin:indexing:powersearch:sql [2016/06/28 22:38] (current)
@@ Line 1: / Line 1: @@
+~~NOTOC~~
 {{page>:top_add&nofooter&noeditbtn}}
@@ Line 9: / Line 11: @@
 **[[admin:indexing:powersearch:sql|SQL]]** |
 [[admin:indexing:powersearch:optimization|Optimization]] |
+[[admin:indexing:powersearch:custom|Customization]] |
 [[admin:indexing:powersearch:tips|Tips]]
@@ Line 17: / Line 20: @@
 ==== The SQL Behind PowerSearch ====
-In most scenarios, the key to implementing PowerSearch in an application is the $CONTAINS function in Omnidex SQL.  This function provides access to the many different search capabilities that are shown in the example on the previous page.
+=== The POWERSEARCH Option ===
-The $CONTAINS function is a powerful function with a lot of options.  Consult the Omnidex SQL documentation on the [[dev:sql:functions:contains:home|$CONTAINS function]] for instruction on this function.
+In most scenarios, the key to implementing PowerSearch is to simply use the POWERSEARCH option on a SELECT statement.  Alternatively, the SET POWERSEARCH ON command can be used to automatically activate PowerSearch for all queries on that connection.
-In the previous example, a succession of SQL statements provided the PowerSearch capabilities:
+<code>
+> select name from individuals where name = 'William Cooke' with powersearch;
+NAME
+--------------------------------------------------
+BILLIE E COOK
+MR. WILL COOK
+</code>
-=== First Name ===
+=== The USAGE Clause ===
-  select        count(*)
+The POWERSEARCH option works hand-in-hand with the data stored in the Omnidex Environment File.  To use POWERSEARCH, administrators add a context to columns telling Omnidex what they contain.  For example, a column named NAME may have a character datatype, but that alone is not enough to tell Omnidex what the column contains.  Moreover, Omnidex cannot look to the column name, as there are many different conventions for naming columns.  Administrators add a USAGE clause that identifies the contents as given names, as shown in this example:
-    from        LIST
-    where       $contains(FNAME, 'Bill', 'SYNONYMS=ALL_GIVEN_NAMES');
-=== Last Name ===
+<code>
-  select        count(*)
+create table          "INDIVIDUALS"
-    from        LIST
+ physical             "dat\individuals.dat"
-    where       $contains(FNAME, 'Bill', 'SYNONYMS=ALL_GIVEN_NAMES') and
+ (
-                $contains(LNAME, 'Meyers', 'PHONETIC');
+  "INDIVIDUAL"        CHARACTER(12)     omnidex,
+  "HOUSEHOLD"         CHARACTER(12)     omnidex ,
+  "NAME"              CHARACTER(50)     quicktext          usage "NAME",
+  "GENDER"            CHARACTER(1)      omnidex bitmap,
+  "BIRTHDATE"         ANSI DATE         omnidex,
+  "PHONE"             CHARACTER(14)     omnidex            usage "PHONE",
+  "EMAIL"             CHARACTER(60)     quicktext          usage "EMAIL",
+  constraint INDIVIDUALS_INDIVIDUAL_PK primary ("INDIVIDUAL"),
+  constraint INDIVIDUALS_HOUSEHOLD_FK foreign ("HOUSEHOLD") references "HOUSEHOLDS",
+  constraint INDIVIDUALS_GENDER_FK foreign ("GENDER") references "GENDERS",
+ )
+ in                   "simple.xml";
+</code>
-=== Address ===
+Omnidex provides many different usages to meet the general needs of most applications; however, administrators can add custom usages to meet the specific needs of their application.  The general usages provided by Omnidex include:
-  select        count(*)
-    from        LIST
-    where       $contains(FNAME, 'Bill', 'SYNONYMS=ALL_GIVEN_NAMES') and
-                $contains(LNAME, 'Meyers', 'PHONETIC') and
-                $contains(ADDRESS1, '825 Fifth Avenue',
-                'SYNONYMS=ALL_ADDRESS_LINES,MISSPELLINGS');
-=== City ===
-  select        count(*)
-    from        LIST
-    where       $contains(FNAME, 'Bill', 'SYNONYMS=ALL_GIVEN_NAMES') and
-                $contains(LNAME, 'Meyers', 'PHONETIC') and
-                $contains(ADDRESS1, '825 Fifth Avenue',
-                'SYNONYMS=ALL_ADDRESS_LINES,MISSPELLINGS') and
-                $contains(CITY, 'NY', 'SYNONYMS=CITY_ABBR,MISSPELLINGS');
-=== State ===
-  select        count(*)
-    from        LIST
-    where       $contains(FNAME, 'Bill', 'SYNONYMS=ALL_GIVEN_NAMES') and
-                $contains(LNAME, 'Meyers', 'PHONETIC') and
-                $contains(ADDRESS1, '825 Fifth Avenue',
-                'SYNONYMS=ALL_ADDRESS_LINES,MISSPELLINGS') and
-                $contains(CITY, 'NY', 'SYNONYMS=CITY_ABBR,MISSPELLINGS') and
-                LIST.STATE = 'NY';
-=== Zip Code ===
-  select        count(*)
-    from        LIST
-    where       $contains(FNAME, 'Bill', 'SYNONYMS=ALL_GIVEN_NAMES') and
-                $contains(LNAME, 'Meyers', 'PHONETIC') and
-                $contains(ADDRESS1, '825 Fifth Avenue',
-                'SYNONYMS=ALL_ADDRESS_LINES,MISSPELLINGS') and
-                $contains(CITY, 'NY', 'SYNONYMS=CITY_ABBR,MISSPELLINGS') and
-                LIST.STATE = 'NY' and
-                ZIP in
-                (select        ZIP
-                   from        ZIPCODES
-                   where       $distance((select LATITUDE,
-                                              LONGITUDE
-                                  from        ZIPCODES
-                                  where       ZIP = '10022'),
-                               LATITUDE,
-                               LONGITUDE) <= 5);
-=== Phone Area Code ===
-  select        count(*)
-    from        LIST
-    where       $contains(FNAME, 'Bill', 'SYNONYMS=ALL_GIVEN_NAMES') and
-                $contains(LNAME, 'Meyers', 'PHONETIC') and
-                $contains(ADDRESS1, '825 Fifth Avenue',
-                'SYNONYMS=ALL_ADDRESS_LINES,MISSPELLINGS') and
-                $contains(CITY, 'NY', 'SYNONYMS=CITY_ABBR,MISSPELLINGS') and
-                LIST.STATE = 'NY' and
-                ZIP in
-                (select        ZIP
-                   from        ZIPCODES
-                   where       $distance((select LATITUDE,
-                                              LONGITUDE
-                                  from        ZIPCODES
-                                  where       ZIP = '10022'),
-                               LATITUDE,
-                               LONGITUDE) <= 5) and
-                $contains(PHONE1_AREA, '917', 'SYNONYMS=AREACODES');
-=== Phone Prefix ===
-  select        count(*)
-    from        LIST
-    where       $contains(FNAME, 'Bill', 'SYNONYMS=ALL_GIVEN_NAMES') and
-                $contains(LNAME, 'Meyers', 'PHONETIC') and
-                $contains(ADDRESS1, '825 Fifth Avenue',
-                'SYNONYMS=ALL_ADDRESS_LINES,MISSPELLINGS') and
-                $contains(CITY, 'NY', 'SYNONYMS=CITY_ABBR,MISSPELLINGS') and
-                LIST.STATE = 'NY' and
-                ZIP in
-                (select        ZIP
-                   from        ZIPCODES
-                   where       $distance((select LATITUDE,
-                                              LONGITUDE
-                                  from        ZIPCODES
-                                  where       ZIP = '10022'),
-                               LATITUDE,
-                               LONGITUDE) <= 5) and
-                $contains(PHONE1_AREA, '917', 'SYNONYMS=AREACODES') and
-                $contains(PHONE1_PREFIX, '755', 'MISSPELLINGS MIN_SCORE=70');
-=== Phone Suffix ===
-  select        count(*)
-    from        LIST
-    where       $contains(FNAME, 'Bill', 'SYNONYMS=ALL_GIVEN_NAMES') and
-                $contains(LNAME, 'Meyers', 'PHONETIC') and
-                $contains(ADDRESS1, '825 Fifth Avenue',
-                'SYNONYMS=ALL_ADDRESS_LINES,MISSPELLINGS') and
-                $contains(CITY, 'NY', 'SYNONYMS=CITY_ABBR,MISSPELLINGS') and
-                LIST.STATE = 'NY' and
-                ZIP in
-                (select        ZIP
-                   from        ZIPCODES
-                   where       $distance((select LATITUDE,
-                                              LONGITUDE
-                                  from        ZIPCODES
-                                  where       ZIP = '10022'),
-                               LATITUDE,
-                               LONGITUDE) <= 5) and
-                $contains(PHONE1_AREA, '917', 'SYNONYMS=AREACODES') and
-                $contains(PHONE1_PREFIX, '755', 'MISSPELLINGS MIN_SCORE=70') and
-                $contains(PHONE1_SUFFIX, '4686', 'MISSPELLINGS MIN_SCORE=70');
-=== Email ===
-<code sql>
-  select        count(*)
-    from        LIST
-    where       $contains(FNAME, 'Bill', 'SYNONYMS=ALL_GIVEN_NAMES') and
-                $contains(LNAME, 'Meyers', 'PHONETIC') and
-                $contains(ADDRESS1, '825 Fifth Avenue',
-                'SYNONYMS=ALL_ADDRESS_LINES,MISSPELLINGS') and
-                $contains(CITY, 'NY', 'SYNONYMS=CITY_ABBR,MISSPELLINGS') and
-                LIST.STATE = 'NY' and
-                ZIP in
-                (select        ZIP
-                   from        ZIPCODES
-                   where       $distance((select LATITUDE,
-                                              LONGITUDE
-                                  from        ZIPCODES
-                                  where       ZIP = '10022'),
-                               LATITUDE,
-                               LONGITUDE) <= 5) and
-                $contains(PHONE1_AREA, '917', 'SYNONYMS=AREACODES') and
-                $contains(PHONE1_PREFIX, '755', 'MISSPELLINGS MIN_SCORE=70') and
-                $contains(PHONE1_SUFFIX, '4686', 'MISSPELLINGS MIN_SCORE=70') and
-                $contains(EMAIL, 'wmeyers@med.cornell.edu', 'MISSPELLINGS
-                MIN_SCORE=80');
-</code>
+  * SALUTATION
+  * FIRST_NAME
+  * MIDDLE_NAME
+  * LAST_NAME
+  * NAME
+  * COMPANY_NAME
+  * STREET_NUMBER
+  * STREET_DIRECTION
+  * STREET_NAME
+  * STREET_TYPE
+  * UNIT_TYPE
+  * UNIT_NUMBER
+  * CITY
+  * STATE
+  * COUNTRY
+  * ZIPCODE
+  * STREET_ADDRESS
+  * CITY_STATE_POSTAL
+  * ADDRESS
+  * PHONE_AREACODE
+  * PHONE_PREFIX
+  * PHONE_SUFFIX
+  * PHONE
+  * EMAIL
+  * ... plus, you can add any custom usages needed for your application.
@@ Line 194: / Line 97: @@
 {{page>:bottom_add&nofooter&noeditbtn}}

Back to top

admin/indexing/powersearch/sql.1295828224.txt.gz · Last modified: 2016/06/28 22:38 (external edit)

Sitemap Recent Changes