\name{insertIntoTable}
\alias{insertIntoTable}
\title{Adding new data to a database table}
\description{
  \code{insertIntoTable} With this function new data rows can be inserted into an existant database table. Automatically generated Primary keys will be updated (more in detail, if the table was created with the \code{createDBTable} function and the argument \code{auto.pk=TRUE}, in the primary key attribute in the database table a new (unique) number will be inserted (using a PostgreSQL specific SEQUENCE table)). Foreign keys, that are references to other tables, will also be inserted if needed. The referencial integrity will always be guaranteed, as the function allows no insertion of data with a reference to another entry in another table, that does not exist.
}
\usage{
insertIntoTable(con, name=NULL, data=NULL, attributes=NULL, references=NULL, no.transaction=FALSE)
}
\arguments{
  \item{con}{A connection object (create it with the dbConnect.PgSQL.conn function from the package RdbiPgSQL)}
  \item{name}{The table name.}
  \item{data}{The data that should be inserted, in form of a single value, a vector, matrix, data.frame...}
  \item{attributes}{a vector containing the attribute names for the table. This parameter can be omitted, if as data argument a matrix or data.frame with the corresponding column names is submitted.}
  \item{references}{a list, or a single object of the type AutoReference, that represents the references to other tables, so that foreign keys can be updated/generated. If the entry should reference to more then one table, create a \code{list} of your \code{AutoReference} objects and submit this list.}
  \item{no.transaction}{if the whole call should be performed into a TRANSACTION, so if any error occurrs during the processing of the function, the database will not be touched. The default value is FALSE, so the function is quite robust.}
}
\details{
This function allows a simple filling of a database table with a single value or a whole table, as well as some more sophisticated operations, like the auto incrementing of a primary key (this will be automatically done if the table was created with the \code{createDBTable} function )).
 Another important feature is the possibility to resolve references to guarantee for a referential integrity. To use this option submit one or more \code{AutoReference} object(s) with the \code{references} argument. Remember that you have to insert on or more (depending of the number of references) columns in your data table, in which you have to define the values from the referenced table. From the corresponding rows in the referenced table the primary key will then be taken as foreign key in the table where the values are gone to be written into. More in detail the row to be inserted will be linked to the row in the referenced table, where the value in the <ref.table.column> attribute of the \code{AutoReference} object is the same as the value in the according column of the <data> object, that is specified in the <source.table.column> attribute of the \code{AutoReference} object.
}
\references{}
\author{Johannes Rainer}

\seealso{
	\code{\link{AutoReference-class}},
	\code{\link{createDBTable}}
}

\examples{
## creating a connection
\dontrun{Con <- dbConnect(PgSQL(),user="postgres",host="localhost",dbname="template1")}

## creating a table with two attributes name and value. as third attribute
## a primary key with the name atable_pk will automatically generated.
\dontrun{createDBTable(Con,name="atable",attributes=c("name","value"),data.types=c("TEXT"))}

## fill some data into the table, remember that not all cells have to be
## filled.
\dontrun{insertIntoTable(Con,name="atable",data=data.frame(species=c("hobbit","human"),value=c("good","bad")))}

## now create a table that references this table.
\dontrun{createDBTable(Con,name="btable",attributes=c("name","age"),data.types=c("TEXT"),references="atable")}

## before inserting any values to this table we have to create a reference.
## the source.table.column specifies the column in the submitted data, which
## values are equal to the values in the ref.table.column attribute in the 
## referenced table.
\dontrun{ref <- new("AutoReference",source.table="btable",ref.table="atable",source.table.column="aref",ref.table.column="species")}

\dontrun{insertIntoTable(Con,name="btable",data=data.frame(name=c("frodo","fred"),aref=c("hobbit","human"))),references=ref)}

## summary:
## the function inserts in the table btable "frodo" in the attribute <name>, and
## links this row to the referenced table btable, by setting in the attribute 
## <atable_fk> (in table <btable>) the value from the primary key of the row in
## the table <atable>, where the attribute <species> has the value "hobbit".
 
## if we had submitted in the aref column a value that is not present in the
## atable, a error would be thrown and nothing is going to be written into
## the table. So the referential integrity is guaranteed. Naturally we can insert into the btable values without any reference to the atable.

}

\keyword{data}