BIBLIOTECA DE PROYECTOS DE CLASE

Esta es la descripcion de la configuracion de base de datos de projectos para badat11. vea http://iush.gitlab.io

A continuacion se detalla el resumen la transformacion de la data para la poblacion de datos de el projecto.

Projects entitty

El sigiente es el proceso para poblacion de la tabla principal del sistema.

projects <- data.frame()
sql_projects_t <- 
    "CREATE TABLE `projects` ( 
    `id` VARCHAR(36) NOT NULL PRIMARY KEY, 
    `org_id` VARCHAR(36), 
    `Teacher_Project_Posted_Sequence` INTEGER, 
    `Project_Type` TEXT, 
    `title` TEXT, 
    `Project_Essay` TEXT, 
    `Project_Subject_Category_Tree` TEXT, 
    `Project_Subject_Subcategory_Tree` TEXT, 
    `Project_Grade_Level_Category` TEXT, 
    `Project_Resource_Category` TEXT, 
    `Project_Cost` REAL, 
    `Project_Posted_Date` TEXT, 
    `Project_Current_Status` TEXT, 
    `Project_Fully_Funded_Date` TEXT,
        `created` DATETIME, 
    `modified` DATETIME,
       CONSTRAINT fk_org
           FOREIGN KEY (org_id)
           REFERENCES orgs(id)       
    )"

con <- dbConnect(SQLite(), "../app/webroot/project_grid.sqlite3")

if ( debug_db ){
   dbGetQuery(con,"DROP TABLE IF EXISTS projects;")
   dbGetQuery(con,sql_projects_t) 

   projects <- read.csv("/pgrid/projects/projects_cleaned_2100.csv")
   projects <- tail(projects,150)
   projects <- mutate(projects, created = "2018-09-11 00:38:54", modified = "2018-09-12 00:38:54")
   projects$Teacher_ID <- NULL
   names(projects) <- c("id" ,
                        "org_id" ,
                        "Teacher_Project_Posted_Sequence" ,
                        "Project_Type" ,
                        "title" ,
                        "Project_Essay" ,
                        "Project_Subject_Category_Tree" ,
                        "Project_Subject_Subcategory_Tree" ,
                        "Project_Grade_Level_Category" ,
                        "Project_Resource_Category" ,
                        "Project_Cost" ,
                        "Project_Posted_Date" ,
                        "Project_Current_Status" ,
                        "Project_Fully_Funded_Date",
            "created",
            "modified")
   
   p_dic <- c("id de tabla proyectos" ,
                        "Llave externa apuntadnod a organizacions" ,
                        "Numero de impresion de secuencia" ,
                        "Tipo de proyecto" ,
                        "Titulos del prouecto" ,
                        "Descripcion larga del proyecto" ,
                        "Categoria" ,
                        "Sub categoria" ,
                        "Nivel de categoria" ,
                        "Recursos" ,
                        "Costo general del proyecto" ,
                        "Fecha de post" ,
                        "Project_Current_Status" ,
                        "Project_Fully_Funded_Date",
            "created",
            "modified")

   dbWriteTable(con, "projects", projects, append=TRUE)
}
## Warning in result_fetch(res@ptr, n = n): Don't need to call dbFetch() for
## statements, only for queries

## Warning in result_fetch(res@ptr, n = n): Don't need to call dbFetch() for
## statements, only for queries
projects <- dbGetQuery(con,"SELECT * FROM projects")

#summary(projects)
dbDisconnect(con)
CREATE TABLE `projects` ( 
    `id` VARCHAR(36) NOT NULL PRIMARY KEY, 
    `org_id` VARCHAR(36), 
    `Teacher_Project_Posted_Sequence` INTEGER, 
    `Project_Type` TEXT, 
    `title` TEXT, 
    `Project_Essay` TEXT, 
    `Project_Subject_Category_Tree` TEXT, 
    `Project_Subject_Subcategory_Tree` TEXT, 
    `Project_Grade_Level_Category` TEXT, 
    `Project_Resource_Category` TEXT, 
    `Project_Cost` REAL, 
    `Project_Posted_Date` TEXT, 
    `Project_Current_Status` TEXT, 
    `Project_Fully_Funded_Date` TEXT,
        `created` DATETIME, 
    `modified` DATETIME,
       CONSTRAINT fk_org
           FOREIGN KEY (org_id)
           REFERENCES orgs(id)       
    )
kable(data.frame(names(projects),p_dic))
names.projects. p_dic
id id de tabla proyectos
org_id Llave externa apuntadnod a organizacions
Teacher_Project_Posted_Sequence Numero de impresion de secuencia
Project_Type Tipo de proyecto
title Titulos del prouecto
Project_Essay Descripcion larga del proyecto
Project_Subject_Category_Tree Categoria
Project_Subject_Subcategory_Tree Sub categoria
Project_Grade_Level_Category Nivel de categoria
Project_Resource_Category Recursos
Project_Cost Costo general del proyecto
Project_Posted_Date Fecha de post
Project_Current_Status Project_Current_Status
Project_Fully_Funded_Date Project_Fully_Funded_Date
created created
modified modified

collaborators

A continuacion se detalla el proceso de poblacion para la tabla collaborators.

## Warning in result_fetch(res@ptr, n = n): Don't need to call dbFetch() for
## statements, only for queries

## Warning in result_fetch(res@ptr, n = n): Don't need to call dbFetch() for
## statements, only for queries

## Warning in result_fetch(res@ptr, n = n): Don't need to call dbFetch() for
## statements, only for queries
CREATE TABLE `collaborators` ( 
       `id` VARCHAR(36) NOT NULL PRIMARY KEY, 
       `Teacher_Prefix` TEXT, 
       `name` Varchar(45),
       `First_Project_Posted_Date` DATETIME,
       `created` DATETIME, 
       `modified` DATETIME
       )
kable(data.frame(names(collaborators),c_dic))
names.collaborators. c_dic
id id de colaboradores
Teacher_Prefix Prefijo de persona (Sr o Sra)
First_Project_Posted_Date Trabajo en primer proyecto.
name Nobmre
created created
modified modified

Project roles

sql_proj_colls_t <-
       "CREATE TABLE `proj_colls` (
       `id` VARCHAR(36) NOT NULL PRIMARY KEY,
       `name` VARCHAR(45),
       `project_id` VARCHAR(36),
       `collaborator_id` VARCHAR(36),
       `created` DATETIME, 
       `modified` DATETIME,
       CONSTRAINT fk_projects
           FOREIGN KEY (project_id)
           REFERENCES projects(id),
       CONSTRAINT fk_collaborators
           FOREIGN KEY (collaborator_id)
       REFERENCES collaborators(id)
       )"
sql_proj_colls_pkc <-
       "CREATE INDEX `proj_colls_collaborators_id_IDX` ON `proj_colls` ( `collaborator_id` )"
sql_proj_colls_pkp <-
       "CREATE INDEX `proj_colls_projects_id_IDX` ON `proj_colls` ( `project_id` )"

con <- dbConnect(SQLite(), "../app/webroot/project_grid.sqlite3")
if ( debug_db ){
        dbGetQuery(con,"DROP TABLE IF EXISTS proj_colls;")
        dbGetQuery(con,sql_proj_colls_t)
#   dbGetQuery(con,sql_proj_colls_pkc)
#   dbGetQuery(con,sql_proj_colls_pkp)
}
## Warning in result_fetch(res@ptr, n = n): Don't need to call dbFetch() for
## statements, only for queries

## Warning in result_fetch(res@ptr, n = n): Don't need to call dbFetch() for
## statements, only for queries
## data frame with 0 columns and 0 rows
dbDisconnect(con)
CREATE TABLE `proj_colls` (
       `id` VARCHAR(36) NOT NULL PRIMARY KEY,
       `name` VARCHAR(45),
       `project_id` VARCHAR(36),
       `collaborator_id` VARCHAR(36),
       `created` DATETIME, 
       `modified` DATETIME,
       CONSTRAINT fk_projects
           FOREIGN KEY (project_id)
           REFERENCES projects(id),
       CONSTRAINT fk_collaborators
           FOREIGN KEY (collaborator_id)
       REFERENCES collaborators(id)
       )
#kable(data.frame(names(proj_colls)))

Organizations

sql_orgs_t <-
    "CREATE TABLE `orgs` ( 
    `id` VARCHAR(36) NOT NULL PRIMARY KEY, 
    `name` TEXT, 
    `type` TEXT, 
    `percentage_Free_Lunch` INTEGER, 
    `state` TEXT, 
    `zip` INTEGER, 
    `city` TEXT, 
    `county` TEXT, 
    `district` TEXT 
    )"
con <- dbConnect(SQLite(), "../app/webroot/project_grid.sqlite3")
if ( debug_db ){
        dbGetQuery(con,"DROP TABLE IF EXISTS orgs;")
        dbGetQuery(con,sql_orgs_t)

        orgs <- read.csv("/pgrid/projects/Schools.csv")
        orgs <- tail(orgs,21000)

    names(orgs) <- c(
        "id" , 
        "name" , 
        "type" , 
        "percentage_Free_Lunch" , 
        "state" , 
        "zip" , 
        "city" , 
        "county" , 
        "district"
    )
    
    o_dic <- c(
        "id de organizaciones" , 
        "Nombre de la organizacion" , 
        "Tipo de organizacion" , 
        "Porcentaje de almeszo" , 
        "EStado" , 
        "zip" , 
        "city" , 
        "county" , 
        "district"
    )

        dbWriteTable(con, "orgs", orgs, append=TRUE)
}
## Warning in result_fetch(res@ptr, n = n): Don't need to call dbFetch() for
## statements, only for queries

## Warning in result_fetch(res@ptr, n = n): Don't need to call dbFetch() for
## statements, only for queries
dbDisconnect(con)
CREATE TABLE `proj_colls` (
       `id` VARCHAR(36) NOT NULL PRIMARY KEY,
       `name` VARCHAR(45),
       `project_id` VARCHAR(36),
       `collaborator_id` VARCHAR(36),
       `created` DATETIME, 
       `modified` DATETIME,
       CONSTRAINT fk_projects
           FOREIGN KEY (project_id)
           REFERENCES projects(id),
       CONSTRAINT fk_collaborators
           FOREIGN KEY (collaborator_id)
       REFERENCES collaborators(id)
       )
kable(data.frame(names(orgs),names(orgs) ))
names.orgs. names.orgs..1
id id
name name
type type
percentage_Free_Lunch percentage_Free_Lunch
state state
zip zip
city city
county county
district district