-- Description: 	This script creates the full-text search catalog, 
--              	and also addes the function used when executing full-text 
--              	search (fnTextSearch)
-- 
-- Creation Date: 29/9-2006
-- Created By: 		Tellef Ormstad
--
-- Updated Date: 	12/12-2006
-- Updated By: 		Tellef Ormstad
-- 
-- NOTE! 					Check in the services list for "Microsoft Search." 
-- 								If not listed, insert the SQL Server media, and select 
-- 								"Upgrade, Add or Remove Components." Click next for 
-- 								"Add Components." On the next screen, select 
-- 								"Server Components," Full-Text Search, and then 
-- 								continue with the rest of installation.
-- 								
--  Compatibility: MS SQL Server 2005
-- 

---------------------------------------
-- Create Full-Text Catalog            
--------------------------------------- 
USE [Consignor]
GO

CREATE FULLTEXT CATALOG [ConsignorFullText]
WITH ACCENT_SENSITIVITY = ON
GO

CREATE FULLTEXT INDEX ON [dbo].[Actor] KEY INDEX [PK_Actor] ON [ConsignorFullText] WITH CHANGE_TRACKING AUTO
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Actor] ADD ([act_name])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Actor] ENABLE
GO
USE [Consignor]
GO
CREATE FULLTEXT INDEX ON [dbo].[Address] KEY INDEX [PK_Address] ON [ConsignorFullText] WITH CHANGE_TRACKING AUTO
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Address] ADD ([add_streetAddress2])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Address] ADD ([add_streetAddress1])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Address] ADD ([add_state])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Address] ADD ([add_postOfficeBoxPostCode])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Address] ADD ([add_postOfficeBoxCity])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Address] ADD ([add_postOfficeBox])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Address] ADD ([add_postCode])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Address] ADD ([add_phone])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Address] ADD ([add_openingHours])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Address] ADD ([add_name2])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Address] ADD ([add_name1])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Address] ADD ([add_mobile])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Address] ADD ([add_fax])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Address] ADD ([add_email])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Address] ADD ([add_city])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Address] ADD ([add_attention])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Address] ENABLE
GO
USE [Consignor]
GO
CREATE FULLTEXT INDEX ON [dbo].[Carrier] KEY INDEX [PK_Carrier] ON [ConsignorFullText] WITH CHANGE_TRACKING AUTO
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Carrier] ADD ([car_shortName])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Carrier] ADD ([car_fullName])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Carrier] ENABLE
GO
USE [Consignor]
GO
CREATE FULLTEXT INDEX ON [dbo].[CountryName] KEY INDEX [PK_CountryName] ON [ConsignorFullText] WITH CHANGE_TRACKING AUTO
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[CountryName] ADD ([con_name])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[CountryName] ENABLE
GO
USE [Consignor]
GO
CREATE FULLTEXT INDEX ON [dbo].[Currency] KEY INDEX [PK_Currency] ON [ConsignorFullText] WITH CHANGE_TRACKING AUTO
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Currency] ADD ([cur_name])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Currency] ADD ([cur_code])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Currency] ENABLE
GO
USE [Consignor]
GO
CREATE FULLTEXT INDEX ON [dbo].[CurrencyName] KEY INDEX [PK_CurrencyName] ON [ConsignorFullText] WITH CHANGE_TRACKING AUTO
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[CurrencyName] ADD ([cun_name])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[CurrencyName] ENABLE
GO
USE [Consignor]
GO
CREATE FULLTEXT INDEX ON [dbo].[LineRef] KEY INDEX [PK_LineRef] ON [ConsignorFullText] WITH CHANGE_TRACKING AUTO
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[LineRef] ADD ([lir_value])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[LineRef] ENABLE
GO
USE [Consignor]
GO
CREATE FULLTEXT INDEX ON [dbo].[Location] KEY INDEX [PK_Location] ON [ConsignorFullText] WITH CHANGE_TRACKING AUTO
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Location] ADD ([loc_name])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Location] ENABLE
GO
USE [Consignor]
GO
CREATE FULLTEXT INDEX ON [dbo].[Message] KEY INDEX [PK_Message] ON [ConsignorFullText] WITH CHANGE_TRACKING AUTO
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Message] ADD ([mes_messageText])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Message] ENABLE
GO
USE [Consignor]
GO
CREATE FULLTEXT INDEX ON [dbo].[Package] KEY INDEX [PK_Package] ON [ConsignorFullText] WITH CHANGE_TRACKING AUTO
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Package] ADD ([pac_parcelNumber])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Package] ADD ([pac_barcode2])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Package] ADD ([pac_barcode1])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Package] ENABLE
GO
USE [Consignor]
GO
CREATE FULLTEXT INDEX ON [dbo].[PackageRef] KEY INDEX [PK_PackageRef] ON [ConsignorFullText] WITH CHANGE_TRACKING AUTO
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[PackageRef] ADD ([par_value])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[PackageRef] ENABLE
GO
USE [Consignor]
GO
CREATE FULLTEXT INDEX ON [dbo].[PackageStatus] KEY INDEX [PK_PackageStatus] ON [ConsignorFullText] WITH CHANGE_TRACKING AUTO
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[PackageStatus] ADD ([pas_user])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[PackageStatus] ENABLE
GO
USE [Consignor]
GO
CREATE FULLTEXT INDEX ON [dbo].[Product] KEY INDEX [PK_Product] ON [ConsignorFullText] WITH CHANGE_TRACKING AUTO
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Product] ADD ([pro_name])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Product] ENABLE
GO
USE [Consignor]
GO
CREATE FULLTEXT INDEX ON [dbo].[RecycleKind] KEY INDEX [PK_RecycleKind] ON [ConsignorFullText] WITH CHANGE_TRACKING AUTO
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[RecycleKind] ADD ([ryk_name])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[RecycleKind] ENABLE
GO
USE [Consignor]
GO
CREATE FULLTEXT INDEX ON [dbo].[Reference] KEY INDEX [PK_Reference] ON [ConsignorFullText] WITH CHANGE_TRACKING AUTO
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Reference] ADD ([ref_value])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Reference] ENABLE
GO
USE [Consignor]
GO
CREATE FULLTEXT INDEX ON [dbo].[Shipment] KEY INDEX [PK_Shipment] ON [ConsignorFullText] WITH CHANGE_TRACKING AUTO
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Shipment] ADD ([shi_printInitiator])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Shipment] ADD ([shi_originTerminal])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Shipment] ADD ([shi_originPostcode])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Shipment] ADD ([shi_number])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Shipment] ADD ([shi_barcode2])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Shipment] ADD ([shi_barcode1])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Shipment] ADD ([shi_ref_OrderNumber])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Shipment] ENABLE
GO
USE [Consignor]
GO
CREATE FULLTEXT INDEX ON [dbo].[ShipmentStatus] KEY INDEX [PK_ShipmentStatus] ON [ConsignorFullText] WITH CHANGE_TRACKING AUTO
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[ShipmentStatus] ADD ([shs_user])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[ShipmentStatus] ENABLE
GO
USE [Consignor]
GO
CREATE FULLTEXT INDEX ON [dbo].[Stack] KEY INDEX [PK_Stack] ON [ConsignorFullText] WITH CHANGE_TRACKING AUTO
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Stack] ADD ([sta_name])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Stack] ENABLE
GO
USE [Consignor]
GO
CREATE FULLTEXT INDEX ON [dbo].[Subcarrier] KEY INDEX [PK_Subcarrier] ON [ConsignorFullText] WITH CHANGE_TRACKING AUTO
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Subcarrier] ADD ([sub_name])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Subcarrier] ENABLE
GO
USE [Consignor]
GO
CREATE FULLTEXT INDEX ON [dbo].[Temperature] KEY INDEX [PK_Temperature] ON [ConsignorFullText] WITH CHANGE_TRACKING AUTO
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Temperature] ADD ([tem_name])
GO
USE [Consignor]
GO
ALTER FULLTEXT INDEX ON [dbo].[Temperature] ENABLE
GO

----------------------------------------------------
-- Add Full-Text search function (fnTextSearch)
----------------------------------------------------
USE [Consignor]
GO
/****** Object:  UserDefinedFunction [dbo].[fnTextSearch]    Script Date: 12/12/2006 14:36:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fnTextSearch] (@searchPhrase VARCHAR (50))
RETURNS TABLE
AS
RETURN
(
	SELECT DISTINCT shi_ID
		FROM Shipment
		WHERE CONTAINS (Shipment.*, @searchPhrase)

	UNION

	SELECT shm_fk_shi AS shi_ID
		FROM ShipmentMessage
		INNER JOIN Message ON shm_fk_mes=mes_ID
		WHERE CONTAINS (Message.*, @searchPhrase)

	UNION

	SELECT ref_fk_shi AS shi_ID
		FROM Reference
		WHERE CONTAINS (Reference.*, @searchPhrase)

	UNION

	SELECT shs_fk_shi AS shi_ID
		FROM ShipmentStatus
		WHERE CONTAINS (ShipmentStatus.*, @searchPhrase)

	UNION

	SELECT lin_fk_shi AS shi_ID
		FROM Line
		INNER JOIN LineRef ON lir_fk_lin=lin_ID
		WHERE CONTAINS (LineRef.*, @searchPhrase)

	UNION

	SELECT lin_fk_shi AS shi_ID
		FROM Line
		INNER JOIN RecycleItem ON ryi_fk_lin=lin_ID
		INNER JOIN RecycleKind ON ryi_fk_ryk=ryk_ID
		WHERE CONTAINS (RecycleKind.*, @searchPhrase)

	UNION

	SELECT lin_fk_shi AS shi_ID
		FROM Line
		INNER JOIN Package ON pac_fk_lin=lin_ID
		WHERE CONTAINS (Package.*, @searchPhrase)

	UNION

	SELECT lin_fk_shi AS shi_ID
		FROM Line
		INNER JOIN Package ON pac_fk_lin=lin_ID
		INNER JOIN PackageRef ON par_fk_pac=pac_ID
		WHERE CONTAINS (PackageRef.*, @searchPhrase)

	UNION

	SELECT lin_fk_shi AS shi_ID
		FROM Line
		INNER JOIN Package ON pac_fk_lin=lin_ID
		INNER JOIN PackageStatus ON pas_fk_pac=pac_ID
		WHERE CONTAINS (PackageStatus.*, @searchPhrase)

	UNION

	SELECT shi_ID
		FROM Shipment
		INNER JOIN Temperature ON shi_fk_tem=tem_ID
		WHERE CONTAINS (Temperature.*, @searchPhrase)

	UNION

	SELECT shi_ID
		FROM Shipment
		INNER JOIN Stack ON shi_fk_sta=sta_ID
		WHERE CONTAINS (Stack.*, @searchPhrase)

	UNION

	SELECT shi_ID
		FROM Shipment
		INNER JOIN Stack ON shi_fk_sta=sta_ID
		INNER JOIN Actor ON sta_fk_act=act_ID
		WHERE CONTAINS (Actor.*, @searchPhrase)

	UNION

	SELECT shi_ID
		FROM Shipment
		INNER JOIN Stack ON shi_fk_sta=sta_ID
		INNER JOIN Actor ON sta_fk_act=act_ID
		INNER JOIN Location ON act_fk_loc=loc_ID
		WHERE CONTAINS (Location.*, @searchPhrase)

	UNION

	SELECT shi_ID
		FROM Shipment
		INNER JOIN Product ON shi_fk_pro=pro_ID
		WHERE CONTAINS (Product.*, @searchPhrase)

	UNION

	SELECT shi_ID
		FROM Shipment
		INNER JOIN Product ON shi_fk_pro = pro_ID
		INNER JOIN Subcarrier ON pro_fk_sub = sub_ID
		INNER JOIN Carrier ON sub_fk_car = car_ID
		WHERE CONTAINS (Carrier.*, @searchPhrase)

	UNION

	SELECT shi_ID
		FROM Shipment
		INNER JOIN Product ON shi_fk_pro=pro_ID
		INNER JOIN Subcarrier ON pro_fk_sub=sub_ID
		WHERE CONTAINS (Subcarrier.*, @searchPhrase)

	UNION

	SELECT add_fk_shi AS shi_ID
		FROM Address
		WHERE CONTAINS (Address.*, @searchPhrase)

	UNION

	SELECT add_fk_shi AS shi_ID
		FROM Address
		INNER JOIN CountryName ON add_fk_cou=con_fk_cou
		WHERE CONTAINS (CountryName.*, @searchPhrase)

	UNION

	SELECT amo_fk_shi AS shi_ID
		FROM Amount
		INNER JOIN Currency ON amo_fk_cur=cur_ID
		WHERE CONTAINS (Currency.*, @searchPhrase)

	UNION

	SELECT amo_fk_shi AS shi_ID
		FROM Amount
		INNER JOIN CurrencyName ON cun_fk_cur=amo_fk_cur
		WHERE CONTAINS (CurrencyName.*, @searchPhrase)
)

GO