#  THIS IS WHAT GOES IN SQLWorkbench
#  jdbc:postgresql://orcd-dw-prod-03.ccvpswrgleoi.us-east-1.redshift.amazonaws.com:5439/dev
#  These are the older versions
#  jdbc:postgresql://testcluster.ccvpswrgleoi.us-east-1.redshift.amazonaws.com:5439/dev
#  jdbc:postgresql://orcd-dw-prod-01.ccvpswrgleoi.us-east-1.redshift.amazonaws.com:5439/dev

## OLD: 
## dbhost <- "testcluster.ccvpswrgleoi.us-east-1.redshift.amazonaws.com"
## dbhost <- "orcd-dw-prod-01.ccvpswrgleoi.us-east-1.redshift.amazonaws.com"
## dbhost <- "orcd-dw-prod-03.ccvpswrgleoi.us-east-1.redshift.amazonaws.com"
##                        ^^^
## CURRENT:              \/
dbhost <- "orcd-dw-prod-04.ccvpswrgleoi.us-east-1.redshift.amazonaws.com"
UID <- "rsaporta"
PW <- .Stwek
PT <- 5439
DB <- "dev"
> con <- dbConnect(drv, dsn=WW, uid=UID, pwd=PW, port=PT)    

library(RPostgreSQL) 
drv <- dbDriver("PostgreSQL") 
con <- dbConnect(drv, host=dbhost, port=PT, dbname=DB, user=UID, password=PW) 


dbGetQuery(con, "SELECT * FROM pg_catalog.pg_tables")

TABLES.ALL <- data.table(dbGetQuery(con, "SELECT * FROM pg_catalog.pg_tables"))


## FROM:  
#  http://docs.aws.amazon.com/redshift/latest/dg/c_join_PG_examples.html

## This is now in DB Utils as `qShowTables()`
QRY.TablesAll <- "SELECT * FROM pg_catalog.pg_tables"

# List the number of columns per Amazon Redshift table

QRY.NumberOfColsPerTable <-
"select nspname, relname, max(attnum) as num_cols
from pg_attribute a, pg_namespace n, pg_class c
where n.oid = c.relnamespace and  a.attrelid = c.oid
and c.relname not like '%pkey'
and n.nspname not like 'pg%'
and n.nspname not like 'information%'
group by 1, 2
order by 1, 2;"

# List the schemas and tables in a database
QRY.DescribeAllTables <- 
"select datname, nspname, relname, sum(rows) as rows
from pg_class, pg_namespace, pg_database, stv_tbl_perm
where pg_namespace.oid = relnamespace
and pg_class.oid = stv_tbl_perm.id
and pg_database.oid = stv_tbl_perm.db_id
and datname ='tickit'
group by datname, nspname, relname
order by datname, nspname, relname;"


# List table IDs, data types, column names, and table names
"select distinct attrelid, rtrim(name), attname, typname
from pg_attribute a, pg_type t, stv_tbl_perm p
where t.oid=a.atttypid and a.attrelid=p.id
and a.attrelid between 100100 and 110000
and typname not in('oid','xid','tid','cid')
order by a.attrelid asc, typname, attname;"



findtbl <- function(pat, AND=FALSE, ignore.case=TRUE) {
  if (AND){} 
  TABLES.ALL[grep(regOr(pat), tablename, ignore.case=ignore.case)]
}

.Q <- function(qry) {
  dbGetQuery(con, qry)
}

( QRes.ColsPerTable <- .Q(QRY.NumberOfColsPerTable) )

getAllColumnNames <- function(tblName, assignTo, pos=1, verbose=TRUE) {
  if (missing(assignTo))
    assignTo <- make.names(paste0(substitute(tblName), ".nms"))
  nms <- names(dbGetQuery(con, paste("Select * from", tblName, " Limit 1;")))
  if (!is.null(assignTo)) {
    assign(assignTo, nms, envir=parent.frame(pos))
    if (verbose) cat("\nAssigned to:  `", assignTo, "`\n", sep="")
  }

  return(nms)
}