CREATE COLUMN TABLE TWITTER_HASHTAGS(
USERNAME NVARCHAR(10)
) WITH SCHEMA FLEXIBILITY;

CREATE TYPE T_COL_NAMES AS TABLE(
COL_NAMES NVARCHAR(1000)
);
 
CREATE TYPE T_COL_VALUES AS TABLE(
COL_VALUES NVARCHAR(1000)
);

CREATE PROCEDURE GET_HASHTAGS(IN twittername TWITTER_USERS,IN first_hash FIRST_HASH,
                              OUT out_col_names T_COL_NAMES, OUT out_col_values T_COL_VALUES)
LANGUAGE RLANG AS
BEGIN
UserName = twittername$USERNAME
hashline = first_hash$HASH_LINE
hashvalues = first_hash$HASH_VALUES
 
Get_Twitter<-function(p_source,p_pattern){
  datalines = grep(p_pattern,web_page,value=TRUE)
  getexpr = function(s,g)substring(s,g,g+attr(g,'match.length')-1)
  g_list = gregexpr(p_pattern,datalines)
  matches = mapply(getexpr,datalines,g_list)
  result = gsub(p_pattern,'\\1',matches)
  names(result) = NULL
  return(result)
}
 
Get_Hashtags<-function(p_source){
  check<-!length(grep('\\"([^,\\"]+)\\"', as.character(p_source)))
  if(!check){
    mypattern = '\\"([^,\\"]+)\\"'
    datalines = grep(mypattern,p_source,value=TRUE)
    getexpr = function(s,g)substring(s,g,g+attr(g,'match.length')-1)
    g_list = gregexpr(mypattern,datalines)
    matches = mapply(getexpr,datalines,g_list)
    result = gsub(mypattern,'\\1',matches)
    names(result) = NULL
    return(result)
  }else{
    result<-p_source
    return(result)
  }
}
 
url<-paste("http://api.twitter.com/1/statuses/user_timeline.xml?count=200&screen_name=",UserName,sep="")
mypattern = '<text>([^<]*)</text>'
web_page<-readLines(url)
tweets<-Get_Twitter(web_page,mypattern)
mypattern = '[^\\&]#(\\.?\\w+)'
hash_list<-Get_Twitter(tweets,mypattern)
hashtags<-sapply(hash_list,Get_Hashtags)
hashtags<-as.vector(unlist(hashtags))
hashtags<-toupper(hashtags)
 
dt.hashtags<-data.frame(UserName,hashtags)
tab.hashtags<-table(dt.hashtags)
dt.hashtags<-as.data.frame.matrix(tab.hashtags)
hashtags_names<-names(dt.hashtags)
hashtags_names<-gsub("^\\.",'',hashtags_names)
 
if(length(hashline>=1)){
          hash_line<-gsub("^(\\w)+\\,",'',hashline)
          hash_line<-unlist(strsplit(hash_line, split=","))
          hash_values<-gsub("^(\\'+\\w+\\')+\\,",'',hashvalues)
          hash_values<-as.numeric(unlist(strsplit(hash_values, split=",")))
          hash_frame<-data.frame(names=hash_line,values=hash_values)
          hash_frame["values"]<-0
 
          Col_Names<-""
          Col_Values<-""
 
          for(i in 1:length(hashtags_names)){
                      Col_Names<-paste(Col_Names,hashtags_names[i],sep=",")
                      Col_Values<-paste(Col_Values,dt.hashtags[,i],sep=",")
          }
 
           Col_Names<-gsub("^\\,|\\.",'',Col_Names)
          Col_Values<-gsub("^\\,|\\.",'',Col_Values)
          Col_Names<-unlist(strsplit(Col_Names, split=","))
          Col_Values<-as.numeric(unlist(strsplit(Col_Values, split=",")))
          new_hash_frame<-data.frame(names=Col_Names,values=Col_Values)
          new_hash_frame<-rbind(hash_frame,new_hash_frame)
          new_hash_frame<-aggregate(values ~ names, FUN = "sum", data = new_hash_frame)
          new_hash_names<-new_hash_frame$names
          new_hash_values<-new_hash_frame$values
 
          Col_Names<-"USERNAME"
          Col_Values<-paste("'",UserName,"'",sep="")
 
           for(i in 1:length(new_hash_names)){
                      Col_Names<-paste(Col_Names,new_hash_names[i],sep=",")
                      Col_Values<-paste(Col_Values,new_hash_values[i],sep=",")
          }
}else{
          Col_Names<-"USERNAME"
          Col_Values<-paste("'",UserName,"'",sep="")
 
          for(i in 1:length(hashtags_names)){
                      Col_Names<-paste(Col_Names,hashtags_names[i],sep=",")
                      Col_Values<-paste(Col_Values,dt.hashtags[,i],sep=",")
          }
}
 
col_names<-gsub("^\\,\\.?",'',Col_Names)
col_values<-gsub("^\\,",'',Col_Values)
 
out_col_names<-data.frame(COL_NAMES=col_names)
out_col_values<-data.frame(COL_VALUES=col_values)
END;

CREATE PROCEDURE SAVE_HASHTAGS(IN in_col_names T_COL_NAMES, IN in_col_values T_COL_VALUES)
LANGUAGE SQLSCRIPT AS
v_select VARCHAR(2000);
v_col_names_char NVARCHAR(1000);
v_col_values_char NVARCHAR(1000);
CURSOR c_cursor1 FOR
SELECT COL_NAMES FROM :in_col_names;
CURSOR c_cursor2 FOR
SELECT COL_VALUES FROM :in_col_values;
BEGIN
                    OPEN c_cursor1;
                    FETCH c_cursor1 into v_col_names_char;
                    CLOSE c_cursor1;
                    OPEN c_cursor2;
                    FETCH c_cursor2 into v_col_values_char;
                    CLOSE c_cursor2;
                    DELETE FROM FIRST_HASH;
                    INSERT INTO FIRST_HASH VALUES(:v_col_names_char,:v_col_values_char);
                    v_select := 'INSERT INTO TWITTER_HASHTAGS (' || v_col_names_char || ') 
                                VALUES (' || v_col_values_char || ')';
                    EXEC v_select;
END;

CREATE PROCEDURE GET_TWITTER_USERS(UserName NVARCHAR(10))
LANGUAGE SQLSCRIPT AS
BEGIN
          Twitter_Users = SELECT USERNAME FROM TWITTER_USERS WHERE USERNAME = :UserName;
          First_Hash = SELECT HASH_LINE, HASH_VALUES FROM FIRST_HASH;
          CALL GET_HASHTAGS(:Twitter_Users,:First_Hash,T_COL_NAMES,T_COL_VALUES);
          CALL SAVE_HASHTAGS(:T_COL_NAMES,:T_COL_VALUES);
END;

CALL GET_TWITTER_USERS('Blag');
CALL GET_TWITTER_USERS('Schmerdy');
CALL GET_TWITTER_USERS('ggread');