%  aRT : API R - TerraLib                                                
%  Copyright (C) 2003-2008  LEG                                          
%                                                                        
%  This program is free software; you can redistribute it and/or modify  
%  it under the terms of the GNU General Public License as published by  
%  the Free Software Foundation; either version 2 of the License, or     
%  (at your option) any later version.                                   
%                                                                        
%  This program is distributed in the hope that it will be useful,       
%  but WITHOUT ANY WARRANTY; without even the implied warranty of        
%  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the         
%  GNU General Public License for more details.                          
%                                                                        
%  You should have received a copy of the GNU Lesser General Public      
%  License along with this library.

% \VignetteIndexEntry{Connecting and Managing Databases with aRT}

\documentclass{article}

\usepackage{Sweave}
\usepackage{hyperref}
\usepackage{aRT}

\title{Connecting and Managing Databases with \aRT}
\author{Pedro Ribeiro de Andrade\\
Paulo Justiniano Ribeiro Junior}
\date{September 19, 2008}

\begin{document}
\maketitle
\tableofcontents


\section{Introduction}
\aRT\ is an \R\ package for manipulating spatial data using \tl.
\tl\ manipulates data stored in DataBase Management Systems (DBMS), 
to which it is necessary to establish connections.
Connecting to a DBMS is aways the first action when using \aRT. 
This short document introduces the primary
class of the package, named \aRTconn. This class implements a virtual 
connection to a DBMS.
In the initial examples, we consider the user already has permissions
in the DBMS. Later, in Section~\ref{addPermission}, we explain how \aRT\ functions
can change the permissions in the DBMS.

\begin{Schunk}
\begin{Sinput}
> require(aRT)
\end{Sinput}
\begin{Soutput}
-------------------------------------
R-TERRALIB API
http://www.leg.ufpr.br/aRT
TerraLib 3.3.0 is now loaded 
aRT 1.7-1 (2009-12-15) is now loaded
-------------------------------------
\end{Soutput}
\begin{Sinput}
> aRTsilent(FALSE)
\end{Sinput}
\begin{Soutput}
[1] FALSE
\end{Soutput}
\end{Schunk}

\section{Connections}
After loading the package, it is necessary to establish a DBMS connection.
The function \code{openConn()} is designed for such task.
Currently, this function takes four arguments (the defaults values are indicated within parenthesis):
\code{user} (the current user as given by \$USER), 
\code{password} (no password), 
\code{host} (`localhost'), and \code{port} (3306).
Future versions will include  a fifth argument,
\code{DBMS}, with options to supported
BDMS such as ``MySQL'', ``Postgres'', ``PostGIS''.

\begin{Schunk}
\begin{Sinput}
> con = openConn(user = "root", host = "localhost", pass = "")
> con
\end{Sinput}
\end{Schunk}


When a connection is successfully stablished, this function returns an object of the class \aRTconn. 
It is important to notice that the elements in the object \code{con}  cannot be changed. 
The only possible way to set/change the connection parameters
is creating another object. calling \code{openConn()} with the new options.
This happens because its data is stored in an external pointer.
If the connection cannot be established, the function stops with an error,
as in the following example:
\begin{Schunk}
\begin{Sinput}
> err = try(conn2 <- openConn(user = "root", pass = "abc321"))
\end{Sinput}
\begin{Soutput}
Trying to connect ... no
\end{Soutput}
\begin{Sinput}
> strsplit(err[1], " : ")
\end{Sinput}
\begin{Soutput}
[[1]]
[1] "Error in .local(.Object, ...)"                                        
[2] "\n  Access denied for user 'root'@'localhost' (using password: YES)\n"
\end{Soutput}
\end{Schunk}

An \aRTconn\ object stores a \emph{virtual} connection to a DBMS, that is, 
every time a database access is required,
the object connects with the DBMS, performs the task, 
and then disconnects.
\aRTconn\ objects allow for some basic queries and operations in the DBMS.
The function \code{showDbs()} lists the databases available 
(the ones which the user has some permission):
\begin{Schunk}
\begin{Sinput}
> showDbs(con)
\end{Sinput}
\begin{Soutput}
 [1] "information_schema" "auckland"           "bodmin"            
 [4] "ca20"               "catarina"           "image"             
 [7] "meso"               "mysql"              "parana"            
[10] "pol3"               "recife"             "saudavel"          
[13] "sp"                 "tabletest"         
\end{Soutput}
\end{Schunk}


New databases can be created with \code{createDb()}:
\begin{Schunk}
\begin{Sinput}
> dbintro = createDb(con, "intro")
\end{Sinput}
\begin{Soutput}
Creating database 'intro' ... yes
Creating conceptual model of database 'intro' ... yes
Creating application theme table 'intro' ... yes
Loading layer set of database 'intro' ... yes
Loading 'root' view set of database 'intro' ... yes
\end{Soutput}
\begin{Sinput}
> dbintro
\end{Sinput}
\begin{Soutput}
Object of class aRTdb

Database: "intro"
Layers: (none)
Themes: (none)
External tables: (none)
\end{Soutput}
\end{Schunk}
and existing databases can be opened with \code{openDb()},
\begin{Schunk}
\begin{Sinput}
> db = openDb(con, "intro")
\end{Sinput}
\begin{Soutput}
Connecting with database 'intro' ... yes
Loading layer set of database 'intro' ... yes
Loading 'root' view set of database 'intro' ... yes
\end{Soutput}
\begin{Sinput}
> db
\end{Sinput}
\begin{Soutput}
Object of class aRTdb

Database: "intro"
Layers: (none)
Themes: (none)
External tables: (none)
\end{Soutput}
\end{Schunk}

In these examples, both \code{dbintro} and \code{db} belong to the 
class \aRTdb, that stores a real connection to a particular database.
As a consequence, at this point the virtual connection is no longer needed
and these objects are independent from \code{con}.
Note that an \aRTdb\ object can turn inconsistent if the database is
removed from the DBMS, possibly generating a \textit{core dump}. 
This cannot be avoided because the database can be removed from any other 
connection to the DBMS, which can be from another
\aRTconn\ object, directly from MySQL, or by another \tl-based application.

\section{Deleting databases}
Whenever the user removes the object that stores a database connection, the connection
is not removed yet. To successfuly remove a database connection, 
you have to to call the garbage collector using \code{gc()}.
\begin{Schunk}
\begin{Sinput}
> rm(db)
> rm(dbintro)
> invisible(gc())
\end{Sinput}
\end{Schunk}

To physically remove a database, the user needs a special permission.
If you have such permission, it is possible to remove databases using \code{deleteDb()}.
The argument \code{force=TRUE} can be used to avoid a keyboard confirmation,
because this a dangerous operation, and cannot be undone.
\begin{Schunk}
\begin{Sinput}
> if (any(showDbs(con) == "intro")) deleteDb(con, "intro", 
+     force = TRUE)
\end{Sinput}
\begin{Soutput}
Checking for database 'intro' ... yes
Deleting database 'intro' ... yes
\end{Soutput}
\end{Schunk}

\section{Managing users from \aRT}\label{addPermission}

The function \code{addPermission()} provides a way to 
add users to the DBMS, with corresponding permissions.
The first step is to start a session with \textit{root} permissions.
\begin{Schunk}
\begin{Sinput}
> con = openConn(u = "root")
\end{Sinput}
\end{Schunk}
Using this conection, some types of permissions can be set\footnote{Henceforth,
the \R\ code will not be executed for security reasons.}:
\begin{itemize}
\item  To create an user with access from localhost, 
without or with password use, respectively:
\begin{Schunk}
\begin{Sinput}
> addPermission(con, "elias")
> addPermission(con, "elias", pass = "password")
\end{Sinput}
\end{Schunk}
\item To create an user with access from a specific host, specified either by
an IP number or hostname, 
without or with password use, respectively:
\begin{Schunk}
\begin{Sinput}
> addPermission(con, "elias", host = "est.ufmg.br")
> addPermission(con, "elias", host = "est.ufmg.br", pass = "senha")
\end{Sinput}
\end{Schunk}
\item Finally, to create an user from any host the password is compulsory:
\begin{Schunk}
\begin{Sinput}
> addPermission(con, "elias", remote = TRUE, pass = "senha")
\end{Sinput}
\end{Schunk}
\end{itemize}

This function, when called as before, provides \textbf{full access} 
to all the DBMS databases.
It is also possible to restrict the permissions to some privileges
or some specific databases. For instance:

\begin{Schunk}
\begin{Sinput}
> addPermission(con, "elias", db = "citrus")
> addPermission(con, "elias", privilege = "select", db = "saudavel")
> addPermission(con, "elias", privilege = "update", db = "parana")
\end{Sinput}
\end{Schunk}

In order to see the permissions of the DBMS, use \code{getPermissions}:
\begin{Schunk}
\begin{Sinput}
> getPermissions(con)[1:5, ]
\end{Sinput}
\begin{Soutput}
                host             user password select insert update
1          localhost             root      Yes    Yes    Yes    Yes
2             pataxo             root       No    Yes    Yes    Yes
3          localhost debian-sys-maint      Yes    Yes    Yes    Yes
4        150.163.2.5         saudavel      Yes    Yes    Yes    Yes
5 pataxo.est.ufpr.br             root       No    Yes    Yes    Yes
  delete create
1    Yes    Yes
2    Yes    Yes
3    Yes    Yes
4    Yes    Yes
5    Yes    Yes
\end{Soutput}
\end{Schunk}

Finally, for removing the permissions of a given user, use \code{dropUser}:

\begin{Schunk}
\begin{Sinput}
> dropUser(con, user = "elias")
> dropUser(con, user = "elias", remote = TRUE)
> dropUser(con, user = "elias", host = "pataxo.est.ufpr.br")
\end{Sinput}
\end{Schunk}

\begin{thebibliography}{99}
\bibitem{chambers} Chambers, J.M., 1998, Programming with data, a guide to the S language.
Springer, New York.
\bibitem{mysql} MySQL Documentation. \url{http://dev.mysql.com/doc/}. Last access: 08/14/2008
\end{thebibliography}

\end{document}

% vim:syntax=tex
