/*
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!															!!
!!	 oracle-xmpp for Oracle 11g (JSDK 1.5 required for XMPP and same JDK as Oracle )   	!!
!!															!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!	
*/

/*
This script is the install script for oracle-xmpp project, to send xmpp and google
messages from PL/SQL, on Oracle 11g.
For more details/questions, or event just to say "Thank you ;-p", you can contact me
at Adrien.Sales@gmail.com, or by visiting the sourceforge site.


   Copyright (C) 2008  <Adrien Sales>

    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with this program.  If not, see <http://www.gnu.org/licenses/>.
*/

/*++++++++++++++++++++++++++++++++++++++++++++++++++
	Install the XMPP schema with minimal rights
+++++++++++++++++++++++++++++++++++++++++++++++++++*/
sqlplus /nolog
conn / as sysdba

PROMPT Creating dedicated tablespace to store jars
CREATE TABLESPACE TS_XMPP
DATAFILE 'H:\app\asales\oradata\DIDI\TS_XMPP.dbf' size 50M;

PROMPT Creating XMPP/XMPP schema
drop user XMPP cascade;
create user XMPP identified by XMPP
default tablespace TS_XMPP
temporary tablespace TEMP
quota unlimited on TS_XMPP;

PROMPT Performing grants to XMPP
grant connect to xmpp;
grant create session to xmpp;
grant resource to xmpp;
grant create public synonym to xmpp;
call dbms_java.grant_permission('XMPP', 'java.net.SocketPermission', '*', 'accept, connect,listen, resolve');
call dbms_java.grant_permission('XMPP', 'java.net.NetPermission', '*', 'accept, connect,listen, resolve');
call dbms_java.grant_permission( 'XMPP', 'SYS:java.lang.RuntimePermission', 'getClassLoader', '' );
exit

/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
	Load jars in the XMPP schema
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/

-- Go into OracleXMPP/dist directory

loadjava -u XMPP/XMPP -resolve lib/smack.jar
loadjava -u XMPP/XMPP -resolve lib/smackx.jar
loadjava -u XMPP/XMPP -resolve OracleXMPP.jar

/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
	Create DBMS_XMPP package
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/

sqlplus XMPP/XMPP
drop package dbms_xmpp
/
drop TYPE ROSTER_SET
/
drop type ROSTER_TYPE
/
drop table roster_table
/
CREATE OR REPLACE TYPE ROSTER_TYPE AS OBJECT (ID NUMBER,
ROSTER_NAME VARCHAR2(30),
ROSTER_USER VARCHAR2(30),
ROSTER_TYPE VARCHAR2(30),
ROSTER_STATUS VARCHAR2(35))
/
CREATE OR REPLACE TYPE ROSTER_SET AS TABLE OF ROSTER_TYPE;
/

create table roster_table(ID NUMBER UNIQUE,
ROSTER_NAME VARCHAR2(30),
ROSTER_USER VARCHAR2(30),
ROSTER_TYPE VARCHAR2(30),
ROSTER_STATUS VARCHAR2(35)
constraint check_roster_status check (roster_status in ('ITEM_STATUS_NULL',
	'SUBSCRIPTION_PENDING',
	'UNSUBCRIPTION_PENDING',
	'UNEXPECTED_STATUS_CODE')))
/


-- create DBMS_XMPP package

create or replace package dbms_xmpp as
-- constants

-- Error codes
XMPP_SUCCESS CONSTANT NUMBER := 0;
XMPP_STATUS_UNEXPECTED CONSTANT NUMBER := -1;
XMPP_STATUS_UNABLE_CONNECT CONSTANT NUMBER := -2;
XMPP_STATUS_UNABLE_LOGIN CONSTANT NUMBER := -3;
XMPPP_CONTACT_UNAVAILABLE CONSTANT NUMBER := -4;

-- Google XMPP default constants
GOOGLE_HOST CONSTANT VARCHAR2(100) := 'talk.google.com';
GOOGLE_PORT CONSTANT NUMBER := 5222;
GOOGLE_SERVICE CONSTANT VARCHAR2(200) := 'gmail.com';

FUNCTION Split (PC$Chaine IN VARCHAR2,         -- input string
	PN$Pos IN PLS_INTEGER,         -- token number
	PC$Sep IN VARCHAR2 DEFAULT ';' -- separator character
)
RETURN VARCHAR2;

/*
The more genral XMPP function.
Purpose : Send a plain text message through XMPP to one or more recipient (recipient1;recipient2;...)
Returns : see XMPP return codes
Example :
select dbms_xmpp.send_plain_text_message('talk.google.com',
	5222,
	'gmail.com',
	'YOUR_GOOGLE_LOGIN',
	'YOUR_GOOGLE_PASSWORD',
	'Adrien.Sales@gmail.com', 'Subject : dbms_xmpp rocks !',
	'I have ve just tested Oracle dbms_xmpp package and i find it really fun and nice to use : thank you for your work and making this freely available ;-p') xmpp_return_code from dual;
*/
function send_plain_text_message (iHost IN VARCHAR2, -- the host where XMPP service is hosted
	iPort IN NUMBER,-- the XMPP service port
	iService IN VARCHAR2,-- the XMPP service
	iLogin IN VARCHAR2,-- the login of the account you will use to send messages
	iPasswd IN VARCHAR2,-- the password of the account that sends mesage
	iRecipient IN VARCHAR2,--the recipient(s) adress, for example toto@gmail.com;titi@gmail.com
	iSubject IN VARCHAR2, -- the Subject
	iMessage IN VARCHAR2)-- the message
RETURN NUMBER;
/*
Send Google
*/
function send_gtalk_plain_text_message (iLogin IN VARCHAR2,-- the login of the account you will use to send messages
	iPasswd IN VARCHAR2,-- the password of the account that sends mesage
	iRecipient IN VARCHAR2,--the recipient(s) adress, for example toto@gmail.com;titi@gmail.com
	iSubject IN VARCHAR2, -- the Subject
	iMessage IN VARCHAR2)-- the message
RETURN NUMBER;
function test_gtalk(iLogin IN VARCHAR2,-- the login of the account you will use to send messages
	iPasswd IN VARCHAR2)
RETURN NUMBER;

/*
Pure test function that performs XMPP login/logout : no message will be sent, see XMPP
return codes for more details. However, you should have 0 (success)
*/
function test_connection(iHost IN VARCHAR2,
	iPort IN NUMBER,
	iService IN VARCHAR2,
	iLogin IN VARCHAR2,
	iPasswd IN VARCHAR2)
return number;


function get_rosters_varchar2(iHost IN VARCHAR2,
	iPort IN NUMBER,
	iService IN VARCHAR2,
	iLogin IN VARCHAR2,
	iPasswd IN VARCHAR2)
return VARCHAR2;

function get_gtalk_rosters_varchar2(iLogin IN VARCHAR2,
	iPasswd IN VARCHAR2)
return VARCHAR2;


function get_rosters_csv(iHost IN VARCHAR2,
	iPort IN NUMBER,
	iService IN VARCHAR2,
	iLogin IN VARCHAR2,
	iPasswd IN VARCHAR2)
return VARCHAR2;

function get_rosters_xmltype(iHost IN VARCHAR2,
	iPort IN NUMBER,
	iService IN VARCHAR2,
	iLogin IN VARCHAR2,
	iPasswd IN VARCHAR2)
return xmltype;



function get_gtalk_rosters_xmltype(iLogin IN VARCHAR2,
	iPasswd IN VARCHAR2) return xmltype;
	
FUNCTION rosters_table(iHost IN VARCHAR2,
	iPort IN NUMBER,
	iService IN VARCHAR2,
	iLogin IN VARCHAR2,
	iPasswd IN VARCHAR2)
RETURN ROSTER_SET PIPELINED;

FUNCTION gtalk_rosters_table(iLogin IN VARCHAR2,
	iPasswd IN VARCHAR2)
RETURN ROSTER_SET PIPELINED;
end dbms_xmpp;
/



create or replace package body dbms_xmpp as
FUNCTION Split (PC$Chaine IN VARCHAR2,         -- input string
	PN$Pos IN PLS_INTEGER,         -- token number
	PC$Sep IN VARCHAR2 DEFAULT ';' -- separator character
)
RETURN VARCHAR2
as
  LC$Chaine VARCHAR2(32767) := PC$Sep || PC$Chaine ;
  LI$I      PLS_INTEGER ;
  LI$I2     PLS_INTEGER ;
BEGIN
  LI$I := INSTR( LC$Chaine, PC$Sep, 1, PN$Pos ) ;
  IF LI$I > 0 THEN
    LI$I2 := INSTR( LC$Chaine, PC$Sep, 1, PN$Pos + 1) ;
    IF LI$I2 = 0 THEN LI$I2 := LENGTH( LC$Chaine ) + 1 ; END IF ;
    RETURN( SUBSTR( LC$Chaine, LI$I+1, LI$I2 - LI$I-1 ) ) ;
  ELSE
    RETURN NULL ;
  END IF ;
END;

FUNCTION send_plain_text_message (iHost IN VARCHAR2,
	iPort IN NUMBER,
	iService IN VARCHAR2,
	iLogin IN VARCHAR2,
	iPasswd IN VARCHAR2,
	iRecipient IN VARCHAR2,
	iSubject IN VARCHAR2,
	iMessage IN VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA
NAME 'net.sourceforge.oracle.xmpp.SmackMapper.sendPlainTextMessage(java.lang.String, int, java.lang.String,java.lang.String,java.lang.String, java.lang.String, java.lang.String, java.lang.String) return int';


function get_rosters_varchar2(iHost IN VARCHAR2,
	iPort IN NUMBER,
	iService IN VARCHAR2,
	iLogin IN VARCHAR2,
	iPasswd IN VARCHAR2) return VARCHAR2
as LANGUAGE JAVA
NAME 'net.sourceforge.oracle.xmpp.SmackMapper.getXmlRosters(java.lang.String, int, java.lang.String,java.lang.String,java.lang.String) return int';

function get_gtalk_rosters_varchar2(iLogin IN VARCHAR2,
	iPasswd IN VARCHAR2) return VARCHAR2
as
begin
	return get_rosters_varchar2(GOOGLE_HOST, GOOGLE_PORT, GOOGLE_SERVICE, iLogin, iPasswd);
end;


function get_rosters_csv(iHost IN VARCHAR2,
	iPort IN NUMBER,
	iService IN VARCHAR2,
	iLogin IN VARCHAR2,
	iPasswd IN VARCHAR2) return VARCHAR2
as LANGUAGE JAVA
NAME 'net.sourceforge.oracle.xmpp.SmackMapper.getFlatRosters(java.lang.String, int, java.lang.String,java.lang.String,java.lang.String) return int';

function get_rosters_xmltype(iHost IN VARCHAR2,
	iPort IN NUMBER,
	iService IN VARCHAR2,
	iLogin IN VARCHAR2,
	iPasswd IN VARCHAR2) return xmltype
	as
	begin
		return xmltype(get_rosters_varchar2(iHost, iPort, iService, iLogin, iPasswd));
end;
	
function get_gtalk_rosters_xmltype(iLogin IN VARCHAR2,
	iPasswd IN VARCHAR2) return xmltype
	as
	begin
		return xmltype(get_rosters_varchar2(GOOGLE_HOST, GOOGLE_PORT, GOOGLE_SERVICE, iLogin, iPasswd));
end;


function test_connection(iHost IN VARCHAR2,
	iPort IN NUMBER,
	iService IN VARCHAR2,
	iLogin IN VARCHAR2,
	iPasswd IN VARCHAR2) return number
AS LANGUAGE JAVA
NAME 'net.sourceforge.oracle.xmpp.SmackMapper.testConnection(java.lang.String, int, java.lang.String,java.lang.String,java.lang.String) return int';
function test_gtalk(iLogin IN VARCHAR2,-- the login of the account you will use to send messages
	iPasswd IN VARCHAR2) return number
as
begin
	return test_connection(GOOGLE_HOST, GOOGLE_PORT, GOOGLE_SERVICE, iLogin, iPasswd);
end;


function send_gtalk_plain_text_message (iLogin IN VARCHAR2,-- the login of the account you will use to send messages
	iPasswd IN VARCHAR2,-- the password of the account that sends mesage
	iRecipient IN VARCHAR2,--the recipient(s) adress, for example toto@gmail.com;titi@gmail.com
	iSubject IN VARCHAR2, -- the Subject
	iMessage IN VARCHAR2)-- the message
RETURN NUMBER
as
begin
return send_plain_text_message (GOOGLE_HOST,
	GOOGLE_PORT,
	GOOGLE_SERVICE,
	iLogin,
	iPasswd,
	iRecipient,
	iSubject,
	iMessage);
end;

FUNCTION rosters_table(iHost IN VARCHAR2,
	iPort IN NUMBER,
	iService IN VARCHAR2,
	iLogin IN VARCHAR2,
	iPasswd IN VARCHAR2)
RETURN ROSTER_SET PIPELINED IS
OUT_ROSTER  ROSTER_TYPE := ROSTER_TYPE(NULL,NULL,NULL,NULL, NULL);
LC$String  VARCHAR2(4000);
LC$Token   VARCHAR2(100);
LC$Token2   VARCHAR2(100) ;
i          PLS_INTEGER := 1 ; 
l_roster_id		VARCHAR2(100);
l_roster_id2		NUMBER;
l_roster_name		VARCHAR2(100);
l_roster_user		VARCHAR2(100);
l_roster_type		VARCHAR2(100);
l_roster_status	VARCHAR2(100);
BEGIN
	LC$String := get_rosters_csv(iHost, iPort, iService, iLogin, iPasswd);
	LOOP
		-- here we are on a specific roster
		LC$Token := Split( LC$String, i , ';') ;
		
		-- loop on current roster datas
			-- get the roster id
			dbms_output.put_line(LC$Token);
			l_roster_id := Split(LC$Token, 1 , '|');
			l_roster_id2 := cast(l_roster_id as NUMBER);
			l_roster_name := Split(LC$Token, 2 , '|') ;
			l_roster_user := Split(LC$Token, 3 , '|') ;
			l_roster_type := Split(LC$Token, 4 , '|') ;
			l_roster_status := Split(Split(LC$Token, 5 , '|'),1, ';') ;
			
			dbms_output.put_line('Id : ' || l_roster_id2);
			dbms_output.put_line('Name : ' || l_roster_name);
			dbms_output.put_line('User : ' || l_roster_user);
			dbms_output.put_line('type : ' || l_roster_type);
			dbms_output.put_line('Status : ' || l_roster_status);
		EXIT WHEN LC$Token IS NULL;
		PIPE ROW(ROSTER_TYPE(l_roster_id2,l_roster_name,l_roster_user,l_roster_type,l_roster_status));
		dbms_output.put_line( LC$Token );
		
		i := i + 1 ;
		END LOOP ;
	return;
END;

FUNCTION gtalk_rosters_table(iLogin IN VARCHAR2,
	iPasswd IN VARCHAR2)
RETURN ROSTER_SET PIPELINED IS
OUT_ROSTER  ROSTER_TYPE := ROSTER_TYPE(NULL,NULL,NULL,NULL, NULL);
LC$String  VARCHAR2(4000);
LC$Token   VARCHAR2(100);
LC$Token2   VARCHAR2(100) ;
i          PLS_INTEGER := 1 ; 
l_roster_id		VARCHAR2(100);
l_roster_id2		NUMBER;
l_roster_name		VARCHAR2(100);
l_roster_user		VARCHAR2(100);
l_roster_type		VARCHAR2(100);
l_roster_status	VARCHAR2(100);
BEGIN
	LC$String := get_rosters_csv(GOOGLE_HOST, GOOGLE_PORT, GOOGLE_SERVICE, iLogin, iPasswd);
	LOOP
		-- here we are on a specific roster
		LC$Token := Split( LC$String, i , ';') ;
		
		-- loop on current roster datas
			-- get the roster id
			dbms_output.put_line(LC$Token);
			l_roster_id := Split(LC$Token, 1 , '|');
			l_roster_id2 := cast(l_roster_id as NUMBER);
			l_roster_name := Split(LC$Token, 2 , '|') ;
			l_roster_user := Split(LC$Token, 3 , '|') ;
			l_roster_type := Split(LC$Token, 4 , '|') ;
			l_roster_status := Split(Split(LC$Token, 5 , '|'),1, ';') ;
			
			dbms_output.put_line('Id : ' || l_roster_id2);
			dbms_output.put_line('Name : ' || l_roster_name);
			dbms_output.put_line('User : ' || l_roster_user);
			dbms_output.put_line('type : ' || l_roster_type);
			dbms_output.put_line('Status : ' || l_roster_status);
		EXIT WHEN LC$Token IS NULL;
		PIPE ROW(ROSTER_TYPE(l_roster_id2,l_roster_name,l_roster_user,l_roster_type,l_roster_status));
		dbms_output.put_line( LC$Token );
		
		i := i + 1 ;
		END LOOP ;
	return;
END;
end dbms_xmpp;
/
PROMPT Create public synonym
create public synonym DBMS_XMPP for DBMS_XMPP;

PROMPT Grant XMPP execution rights to scott
grant execute on BMS_XMPP to scott;

PROMPT That s it, DBMS_XMPP is ready for use


/*++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
		Test and examples of use
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/		
-- let's test !
set long 1000000

--------------------------------------------------------------------------------------------------------------------------
--						Google test								--
--
-- Test connection
select dbms_xmpp.test_gtalk('YOUR_GOOGLE_LOGIN', -- Your Google Login
	'YOUR_GOOGLE_PASSWORD') -- Your Google password
as GOOGLE_RC -- The return code
from dual;
--Send a google message
select dbms_xmpp.send_gtalk_plain_text_message('YOUR_GOOGLE_LOGIN',-- your google login
	'YOUR_GOOGLE_PASSWORD',-- your google password
	'Adrien.Sales@gmail.com', -- the recipient(s) of your message (for many recipients, put ; between contacts)
	'test',-- Message subjects
	'hello gtalk from Oracle !') as GOOGLE_RC-- the message itself
from dual;
-- Get Google Rosters (ie. contacts) in xmltype
set pagesize 10000
select dbms_xmpp.get_gtalk_rosters_xmltype('YOUR_GOOGLE_LOGIN', -- Your Google Login
	'YOUR_GOOGLE_PASSWORD') -- Your Google Password
	as xml_rosters
from dual;

-- get GTalk rosters  : get contact list in table format
col  ID for 99
col ROSTER_NAME for a30
col ROSTER_USER for a30
col ROSTER_TYPE for a5
col ROSTER_STATUS for a30
set linesize 135
select ID as "Id",
	ROSTER_NAME as "Name",
	ROSTER_USER as "User", 
	ROSTER_TYPE as "Type",
	ROSTER_STATUS as "Status"
from table(dbms_xmpp.gtalk_rosters_table('YOUR_GOOGLE_LOGIN', 
	'YOUR_GOOGLE_PASSWORD'));
	
--															--
--------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------
--						XMPP test								--
--
-- Test connection
select dbms_xmpp.test_connection('talk.google.com',
	5222,
	'gmail.com',
	'YOUR_GOOGLE_LOGIN',
	'YOUR_GOOGLE_PASSWORD') xmpp_return_code from dual;



select dbms_xmpp.get_rosters_xmltype('talk.google.com',
	5222,
	'gmail.com',
	'YOUR_GOOGLE_LOGIN',
	'YOUR_GOOGLE_PASSWORD') from dual;
	
select dbms_xmpp.get_rosters_csv('talk.google.com',
	5222,
	'gmail.com',
	'YOUR_GOOGLE_LOGIN',
	'YOUR_GOOGLE_PASSWORD') csv
from dual;

-- send to xmpp to multiple recipients
select dbms_xmpp.send_plain_text_message('talk.google.com',
	5222,
	'gmail.com',
	'YOUR_GOOGLE_LOGIN',
	'YOUR_GOOGLE_PASSWORD',
	'mbierlaire@gmail.com;Adrien.Sales@gmail.com',
	'Dummy subject',
	'Hello from Oracle') xmpp_return_code
from dual;

-- get XMPP rosters
col  ID for 99
col ROSTER_NAME for a30
col ROSTER_USER for a30
col ROSTER_TYPE for a5
col ROSTER_STATUS for a30
set linesize 135
select ID as "Id",
	ROSTER_NAME as "Name",
	ROSTER_USER as "User", 
	ROSTER_TYPE as "Type",
	ROSTER_STATUS as "Status"
from table(dbms_xmpp.rosters_table('talk.google.com',
	5222,
	'gmail.com',
	'YOUR_GOOGLE_LOGIN',
	'YOUR_GOOGLE_PASSWORD'));



