# Row Numbers for fact_analytics 20150721 -- straight serial.r
# ~/git/orch/src/Looker/Row Numbers for fact_analytics 20150721 -- straight serial.r

#  ## SEE seq8
#  https://documentation.snowflakecomputing.com/manuals/sql-reference/functions/seq1.html


setScience("Snowflake_Scratch", subProj="row_index", create=FALSE, subl=FALSE, load=FALSE)
.us()

wh <- ifelse(sfIsWarehouseOn("LOOKER_WH_LARGE"), "LOOKER_WH_LARGE", "SCIENCE")
dbname <- "prod"
schema <- "production"


for (tbl in c("fact_analytics", "fact_sales")) {
  tbl_clone <- paste0(tbl, "_with_rows")
  setSnowflake(wh=wh, dbname=dbname, schema=schema, start=TRUE)

  comment     <- sprintf("Copy of %s with row_number column added %s", tbl, getSFCommentFooter())
  dstbl       <- dbschematbl(dbname=dbname, schema=schema, tbl=tbl)
  dstbl_clone <- dbschematbl(dbname=dbname, schema=schema, tbl=tbl_clone)
  if (!qTableExists(tbl=tbl_clone, schema=schema, dbname=dbname, msg_sf=FALSE, showWarnings=FALSE))
    sfQry(sprintf("CREATE TRANSIENT TABLE %s CLONE %s\ncomment='%s'", dstbl_clone, dstbl, comment))
  else
    try({
      sprintf("ALTER TABLE %s DROP COLUMN row_number", dstbl_clone) %>% sfQry()
    }, silent=TRUE)

  sprintf("ALTER TABLE %s ADD COLUMN row_number NUMERIC (17, 0)", dstbl_clone) %>% sfQry()
  sprintf("UPDATE %s SET row_number = seq8();", dstbl_clone) %>% sfQry
}