#RB 11/02/2010
# setwd("~") # répertoire utilisateur
setwd(choose.dir())

#.3
# fichier texte
fich <- file.choose() # Personnes.txt
scan(file=fich, sep="\n", what = "character",nlines=5) # voir début fichier
(pers <- read.table(file=fich,header=TRUE,sep="\t" ) )
read.csv2(file=file.choose()) # Habitants.csv
# fichier .xls
library (xlsReadWrite)
data <- read.xls(file="Habitants.xls",colClasses="character",string=FALSE)
str(data)

#.5
pers
str(pers)

#.6
(loc <- read.table(file="localisation.txt",header=TRUE,sep="\t" ) )

#.10
library(sqldf)

#.11
# sélectionner "SeminR.db" : base SQLite
setwd(dirname(dbase <- file.choose())) # change le répertoire
dbase = iconv(dbase,Encoding(dbase),"latin1")# conversion code caractère
dbase <- substring(dbase,max(unlist(gregexpr("\\",dbase,fixed=TRUE)))+1)
( con <- sqldf (dbname = dbase) )
dbListTables(con) # affiche les tables de la base connectée

#.12
data <- sqldf ("SELECT * FROM localisation ;", method = "auto") # table SQLite
str(data)
data <- sqldf ("SELECT * FROM iris ;", method = "auto", row.names = TRUE) # data.frame
str(data)

#.13 -3
(con <- sqldf ()) # Attention à bien avoir une connexion <SQLiteConnection:...
fich <- file(file.choose()) 	# ouvre une connexion sur un fichier texte au choix
attr(fich , "file.format") <- list(sep = "\t" , header = FALSE) #Habitants_sansHeaRN.txt
sqldf ("SELECT * FROM fich;", method = "auto", row.names = FALSE)
sqldf ("SELECT * FROM fich;", method = "auto", row.names = TRUE)
sqldf ("SELECT * FROM fich;", method = "raw", row.names = FALSE)
sqldf ("SELECT * FROM fich;", method = "raw", row.names = TRUE)
dbListTables(con) # affiche les tables
dbRemoveTable(con,"fich") # supprime la table "fich"
#Refaire avec Habitants.txt et Habitants.csv
attr(fich , "file.format") <- list(sep = "\t" , header = TRUE) # Habitants.txt
attr(fich , "file.format") <- list(sep = ";" , header = TRUE) # Habitants.csv

#.13 -4
read.csv2.sql(file.choose(), sql = "SELECT * FROM file")

# SELECT SQL
#.15
dbase <- "SeminR.db"
con <- sqldf(dbname=dbase)
sqldf (sql)
# Alias
sql <- "SELECT Adresse AS Voie, Code AS [Code postal], Ville AS Commune FROM localisation;"
# Distinct
sql <- "SELECT DISTINCT Nom FROM Personnes;" 
sql <- "SELECT DISTINCT Nom, Prenom FROM Personnes;" 
# count
sql <- "SELECT count(*) FROM Personnes;" # nombre d'enregistements de la table
sql <- "SELECT count(Distinct Nom) FROM Personnes;"

#.16
# where
sql <- "SELECT * FROM Localisation WHERE ville = 'VANVES' ;"
sql <- "SELECT * FROM Localisation WHERE Code < 92200 ;"
sql <- "SELECT * FROM Localisation WHERE Ville LIKE '%N%' ;"
sql <- "SELECT * FROM Localisation WHERE Ville IN ('CHATILLON','CLAMART');"
sql <- "SELECT * FROM Localisation WHERE Code BETWEEN 92140 AND 92200 ;"
sql <- "SELECT * FROM Localisation WHERE Ville LIKE 'c%' AND Code > 92200 ;"

#.17
# group by
sql <- "SELECT Ville, count(*) AS Nb_personnes FROM Habitants  GROUP BY Ville ;"
# having
sql <- "SELECT Nom, Prenom, Count(Telephone) AS NbTéléphone
  FROM Habitants WHERE Code > 92150
  GROUP BY Nom, Prenom
  HAVING Count (Telephone) >1 ;"

#.18
# jointure interne
sql <- "SELECT Nom, Prenom, Ville FROM Localisation, Personnes 
        WHERE Localisation.id = id_localisation AND Ville='VANVES';"
sql <- "SELECT Nom, Prenom, Ville FROM Localisation INNER JOIN Personnes
        ON Localisation.id = id_localisation
        WHERE Ville='VANVES';"
# auto
sql <- "SELECT a.Nom, a.Prenom, a.Telephone FROM Habitants a, Habitants b
  WHERE a.Telephone = b.Telephone AND a.pk <> b.pk;"

#.19
# SELECT imbriqués
sql <- "SELECT DISTINCT Nom, Prenom, numero, Ville FROM Localisation, Personnes
        WHERE Localisation.id = id_localisation
        AND numero > (SELECT max(numero) FROM Habitants WHERE Ville = 'CHATILLON')
        AND Ville != 'CHATILLON';"
                                            
sql <- "SELECT Adresse, Ville FROM Localisation
        WHERE id IN (SELECT id_localisation FROM Personnes WHERE Telephone ='NA');"
#-------------------
library(RSQLite)
library(RODBC)

#.24 - exemples connexion
# a une base sqlite "SeminR.db via DBI"
getwd()
setwd(choose.dir()) # changement de répertoire obligatoire
db <- "SeminR.db"
(drv <- dbDriver("SQLite"))
(con <- dbConnect(drv, dbname=db))
dbListConnections(drv )
dbGetInfo(con)
# a une base sqlite "SeminR.db" via ODBC
(c_sqlite <- odbcDriverConnect() )
# a une base access
(conn <- file.choose())
(channel <- odbcConnectAccess (conn) )

#.25 - accès structures
dbListTables(con)
  sqtable = "localisation"
dbExistsTable (con, sqtable)
sqlTables (c_sqlite)
sqlTables (channel)

dbListFields( con, sqtable)
sqlColumns (c_sqlite, sqtable)[,3:6]
sqlColumns (channel, sqtable)[,3:6]

#.26 - accès données sans sélection de colonne possible
(dDBI <- dbReadTable(con, sqtable))
str(dDBI)
(dODBC <- sqlFetch (c_sqlite, sqtable))
str(dODBC)
(dODBC <- sqlFetch (c_sqlite, sqtable,string=FALSE))
str(dODBC)
#
sqlFetch (c_sqlite, "personnes") # ts les enr.
sqlFetch (c_sqlite, "personnes",max=3) # 3 premiers, curseur = 3
sqlFetchMore (c_sqlite,max=2) # curseur + 2
sqlFetchMore (c_sqlite,max=2) # curseur + 2
sqlFetchMore (c_sqlite) # Jusqu'à la fin
sqlFetchMore (c_sqlite) # -1 indique la fin de lecture

#.27 - accès données via SELECT SQL
sql <- "SELECT * FROM localisation;"
dbGetQuery(con, sql)  # 1
sqlQuery(c_sqlite,sql)

res <- dbSendQuery(con, sql) # 2
fetch(res, n = 2)
dbGetRowCount(res)  # nb lignes lues
dbHasCompleted(res) # Oui/Non
fetch(res, n = -1)  # -1 toutes les lignes

dbClearResult(res) # libère le lien à la table. A faire si dbHasCompleted(res)=FALSE
dbColumnInfo(res)
dbGetStatement(res) # 1]  "SELECT * FROM localisation"

# Fermeture connexion
dbDisconnect(con)
dbUnloadDriver(drv)
odbcCloseAll()

# ------------------
# En cas problème de codage caractère avec une table (data.frame)
CUTF8 <- function(x) {
for ( i in 1:ncol(x) ){
  if (mode(x[,i])=="character") Encoding(x[,i]) <- rep("UTF-8",nrow(x)) }
return (x) }
# dDBI <- CUTF8(dDBI)