X-Git-Url: https://git.donarmstrong.com/?a=blobdiff_plain;f=trunk%2FR%2Fdb.R;h=839adac9a27f00b3d2ed0bb958ae65c6b56fa90e;hb=8480601a6b180cce5dd2d2131170b1b00c469eca;hp=c9688c13ec503ceeaa112d707d8349f2570abf47;hpb=9aa224db9432d46dc349f80ef986b9868423bafe;p=cran2deb.git

diff --git a/trunk/R/db.R b/trunk/R/db.R
index c9688c1..839adac 100644
--- a/trunk/R/db.R
+++ b/trunk/R/db.R
@@ -1,7 +1,9 @@
 
 db_start <- function() {
     drv <- dbDriver('SQLite')
+    if (is.null(drv)) stop("db_start: Could not access driver for SQLite.\n")
     con <- dbConnect(drv, dbname=file.path(cache_root,'cran2deb.db'))
+    if (is.null(con)) stop("db_start: Could open connection to file 'cran2deb.db' in directory",cache_root,".\n")
     if (!dbExistsTable(con,'sysreq_override')) {
         dbGetQuery(con,paste('CREATE TABLE sysreq_override ('
                   ,' depend_alias TEXT NOT NULL'
@@ -60,6 +62,7 @@ db_start <- function() {
                   ,',deb_revision INTEGER NOT NULL'
                   ,',db_version INTEGER NOT NULL'
                   ,',date_stamp TEXT NOT NULL'
+                  ,',time_stamp TEXT NOT NULL'
                   ,',scm_revision TEXT NOT NULL'
                   ,',success INTEGER NOT NULL'
                   ,',log TEXT'
@@ -68,8 +71,14 @@ db_start <- function() {
     }
     if (!dbExistsTable(con,'blacklist_packages')) {
         dbGetQuery(con,paste('CREATE TABLE blacklist_packages ('
-	              ,'package TEXT PRIMARY KEY NOT NULL '
-                  ,'reason TEXT NOT NULL '
+                  ,' package TEXT PRIMARY KEY NOT NULL '
+                  ,',nonfree INTEGER NOT NULL DEFAULT 0'
+                  ,',obsolete INTEGER NOT NULL DEFAULT 0'
+                  ,',broken_dependency INTEGER NOT NULL DEFAULT 0'
+                  ,',unsatisfied_dependency INTEGER NOT NULL DEFAULT 0'
+                  ,',breaks_cran2deb INTEGER NOT NULL DEFAULT 0'
+                  ,',other INTEGER NOT NULL DEFAULT 0'
+                  ,',explanation TEXT NOT NULL '
                   ,')'))
     }
     return(con)
@@ -281,31 +290,62 @@ db_add_license_hash <- function(name,license_sha1) {
 
 
 db_update_package_versions <- function() {
+    # seems like the quickest way of doing this:
+    con <- db_start()
+    dbGetQuery(con, 'DROP TABLE packages')
+    db_stop(con)
+    # db_start re-makes all tables
     con <- db_start()
     for (package in available[,'Package']) {
         dbGetQuery(con, paste('INSERT OR REPLACE INTO packages (package,latest_r_version)'
                              ,'VALUES (',db_quote(package)
                              ,',',db_quote(available[package,'Version']),')'))
     }
+    dbGetQuery(con,'DELETE FROM builds WHERE builds.package NOT IN (SELECT package FROM packages)')
     db_stop(con)
 }
 
+db_date_format <- '%Y-%m-%d'
+db_time_format <- '%H:%M:%OS'
+
 db_record_build <- function(package, deb_version, log, success=F) {
+    # if the log is more than 1kB, only keep the last 1kB.
+    # this is to work around a problem that seems to have appeared in R 2.10 causing calloc errors.
+    # if the log is not pruned then we get the following error:
+    #
+    # Error in gsub("(['\"])", "\\1\\1", text) :
+    #   Calloc could not allocate (-197080581 of 1) memory
+    # Error in dbGetQuery(con, paste("INSERT OR REPLACE INTO builds", "(package,system,r_version,deb_epoch,deb_revision,db_version,success,date_stamp,time_stamp,scm_revision,log)",  :
+    #   error in evaluating the argument 'statement' in selecting a method for function 'dbGetQuery'
+
+    log <- paste(log,collapse='\n')
+    end <- nchar(log)
+    max_log_len <- 10240
+    if (end > max_log_len) {
+        log <- db_quote(substr(log,end-max_log_len,end))
+    } else {
+        log <- db_quote(log)
+    }
     con <- db_start()
-    dbGetQuery(con,paste('INSERT OR REPLACE INTO builds'
-                        ,'(package,system,r_version,deb_epoch,deb_revision,db_version,success,date_stamp,scm_revision,log)'
-                        ,'VALUES'
-                        ,'(',db_quote(package)
-                        ,',',db_quote(which_system)
-                        ,',',db_quote(version_upstream(deb_version))
-                        ,',',db_quote(version_epoch(deb_version))
-                        ,',',db_quote(version_revision(deb_version))
-                        ,',',db_cur_version(con)
-                        ,',',as.integer(success)
-                        ,',',db_quote(format(Sys.time(),'%a, %d %b %Y %H:%M:%S %z'))
-                        ,',',db_quote(scm_revision)
-                        ,',',db_quote(paste(log, collapse='\n'))
-                        ,')'))
+    o <- options(digits.secs = 6)
+    sqlcmd <- paste('INSERT OR REPLACE INTO builds'
+                    ,'(package,system,r_version,deb_epoch,deb_revision,db_version,success,date_stamp,time_stamp,scm_revision,log)'
+                    ,'VALUES'
+                    ,'(',db_quote(package)
+                    ,',',db_quote(which_system)
+                    ,',',db_quote(version_upstream(deb_version))
+                    ,',',db_quote(version_epoch(deb_version))
+                    ,',',db_quote(version_revision(deb_version))
+                    ,',',db_cur_version(con)
+                    ,',',as.integer(success)
+                    ,',',db_quote(format(Sys.time(), db_date_format))
+                    ,',',db_quote(format(Sys.time(), db_time_format))
+                    ,',',db_quote(scm_revision)
+                    ,',',log
+                    ,')')
+    ##print(sqlcmd)
+    try(dbGetQuery(con,sqlcmd))
+    options(o)
     db_stop(con)
 }
 
@@ -320,38 +360,74 @@ db_builds <- function(pkgname) {
     if (length(build) == 0) {
         return(NULL)
     }
+    return(db_cleanup_builds(build))
+}
+
+db_cleanup_builds <- function(build,verbose=FALSE) {
     build$success <- as.logical(build$success)
-    return(build)
+    #o <-options(digits.secs = 6)
+    dt <- as.POSIXct(strptime(paste(as.character(build[,"date_stamp"]), as.character(build[,"time_stamp"])),
+                              paste(db_date_format, db_time_format)))
+    build$time_stamp <- NULL
+    build$date_stamp <- NULL
+    newdf <- data.frame(build, date_stamp=dt)
+    if (verbose) {
+       cat("db_cleanup_builds: newdf")
+       print(newdf)
+    }
+    #print(newdf[, -grep("log", colnames(newdf))])
+    #options(o)
+    #print(newdf[, -grep("log", colnames(newdf))])
+    return(newdf)
 }
 
-db_latest_build <- function(pkgname) {
+db_latest_build <- function(pkgname,verbose=FALSE,debug=FALSE) {
+    if (verbose) {cat("db_latest_build: pkgname:",pkgname,"\n")}
     con <- db_start()
+    if (debug) {
+	cat("       connection was opened\n")
+    }
     build <- dbGetQuery(con, paste('SELECT * FROM builds'
                        ,'NATURAL JOIN (SELECT package,max(id) AS max_id FROM builds'
                        ,              'WHERE system =',db_quote(which_system)
                        ,              'GROUP BY package) AS last'
                        ,'WHERE id = max_id'
                        ,'AND builds.package =',db_quote(pkgname)))
+    if (debug) {
+	cat("       dbGetQuery was executed:\n")
+	cat("       print(build):\n")
+	print(build)
+    }
     db_stop(con)
+    if (debug) {
+	cat("       connection was closed\n")
+    }
     if (length(build) == 0) {
         return(NULL)
+    } else if (0 == nrow(build)) {
+        return(NULL)
     }
-    build$success <- as.logical(build$success)
-    return(build)
+    return(db_cleanup_builds(build))
 }
 
-db_latest_build_version <- function(pkgname) {
+db_latest_build_version <- function(pkgname,verbose=FALSE) {
+    if (verbose) {cat("db_latest_build_version: pkgname:",pkgname,"\n")}
     build <- db_latest_build(pkgname)
     if (is.null(build)) {
         return(NULL)
+    } else if (0 == nrow(build)) {
+        return(NULL)
     }
     return(version_new(build$r_version, build$deb_revision, build$deb_epoch))
 }
 
-db_latest_build_status <- function(pkgname) {
+db_latest_build_status <- function(pkgname,verbose=FALSE) {
+    if (verbose) {cat("db_latest_build_status: pkgname:",pkgname,"\n")}
     build <- db_latest_build(pkgname)
     if (is.null(build)) {
         return(NULL)
+    } else if (0 == nrow(build)) {
+        return(NULL)
     }
     return(list(build$success,build$log))
 }
@@ -385,7 +461,52 @@ db_outdated_packages <- function() {
 
 db_blacklist_packages <- function() {
     con <- db_start()
-    packages <- dbGetQuery(con,'SELECT packages from blacklist_packages')$package
+    packages <- dbGetQuery(con,'SELECT package from blacklist_packages')$package
     db_stop(con)
     return(packages)
 }
+
+db_blacklist_reasons <- function () {
+    con <- db_start()
+    packages <- dbGetQuery(con,'SELECT package,explanation from blacklist_packages group by explanation')
+    db_stop(con)
+    return(packages)
+}
+
+db_todays_builds <- function() {
+    today <- db_quote(format(Sys.time(), db_date_format))
+    con <- db_start()
+    builds <- dbGetQuery(con,paste('select id,success,system,package,
+                                    r_version as version,deb_epoch as epo,
+                                    deb_revision as rev, scm_revision as svnrev,
+                                    db_version as db,date_stamp,time_stamp
+                                    from builds where date_stamp = ',today))
+    db_stop(con)
+    return(builds)
+}
+
+db_successful_builds <- function() {
+    con <- db_start()
+    builds <- dbGetQuery(con,'select system,package,r_version,date_stamp,time_stamp
+                              from builds natural join (select system,package,max(id) as id
+                                                        from builds
+                                                        where package not in
+                                                                (select package from blacklist_packages)
+                                                        group by package,system)
+                              where success = 1')
+    db_stop(con)
+    return(builds)
+}
+
+db_failed_builds <- function() {
+    con <- db_start()
+    builds <- dbGetQuery(con,'select system,package,r_version,date_stamp,time_stamp
+                              from builds natural join (select system,package,max(id) as id
+                                                        from builds
+                                                        where package not in
+                                                                (select package from blacklist_packages)
+                                                        group by package,system)
+                              where success = 0')
+    db_stop(con)
+    return(builds)
+}