The AnnDbObj class is the most general container for storing any kind
of SQLite-based annotation data.
Details
Many classes in AnnotationDbi inherit directly or indirectly
from the AnnDbObj class. One important particular case is the
AnnDbBimap class which is the lowest class in the AnnDbObj
hierarchy to also inherit the Bimap interface.
Accessor-like methods
In the code snippets below,
x is an AnnDbObj object.
dbconn(x):
Return a connection object to the SQLite DB containing x's data.
dbfile(x):
Return the path (character string) to the SQLite DB (file) containing
x's data.
dbmeta(x, name):
Print the value of metadata whose name is 'name'.
Also works if x is a DBIConnection object.
dbschema(x, file="", show.indices=FALSE):
Print the schema definition of the SQLite DB.
Also works if x is a DBIConnection object.
The file argument must be a connection, or a character string
naming the file to print to (see the file argument of the
cat function for the details).
The CREATE INDEX statements are not shown by default.
Use show.indices=TRUE to get them.
dbInfo(x):
Prints other information about the SQLite DB.
Also works if x is a DBIConnection object.
library("hgu95av2.db")
dbconn(hgu95av2ENTREZID) # same as hgu95av2_dbconn()
dbfile(hgu95av2ENTREZID) # same as hgu95av2_dbfile()
dbmeta(hgu95av2_dbconn(), "ORGANISM")
dbmeta(hgu95av2_dbconn(), "DBSCHEMA")
dbmeta(hgu95av2_dbconn(), "DBSCHEMAVERSION")
library("DBI")
dbListTables(hgu95av2_dbconn()) #lists all tables on connection
## If you use dbSendQuery instead of dbGetQuery
## (NOTE: for ease of use, this is defintitely NOT reccomended)
## Then you may need to know how to list results objects
dbListResults(hgu95av2_dbconn()) #for listing results objects
## You can also list the fields by using this connection
dbListFields(hgu95av2_dbconn(), "probes")
dbListFields(hgu95av2_dbconn(), "genes")
dbschema(hgu95av2ENTREZID) # same as hgu95av2_dbschema()
## According to the schema, the probes._id column references the genes._id
## column. Note that in all tables, the "_id" column is an internal id with
## no biological meaning (provided for allowing efficient joins between
## tables).
## The information about the probe to gene mapping is in probes:
dbGetQuery(hgu95av2_dbconn(), "SELECT * FROM probes LIMIT 10")
## This mapping is in fact the ENTREZID map:
toTable(hgu95av2ENTREZID)[1:10, ] # only relevant columns are retrieved
dbInfo(hgu95av2GO) # same as hgu95av2_dbInfo()
##Advanced example:
##Sometimes you may wish to join data from across multiple databases at
##once:
## In the following example we will attach the GO database to the
## hgu95av2 database, and then grab information from separate tables
## in each database that meet a common criteria.
library(hgu95av2.db)
library("GO.db")
attachSql <- paste('ATTACH "', GO_dbfile(), '" as go;', sep = "")
dbGetQuery(hgu95av2_dbconn(), attachSql)
sql <- 'SELECT DISTINCT a.go_id AS "hgu95av2.go_id",
a._id AS "hgu95av2._id",
g.go_id AS "GO.go_id", g._id AS "GO._id",
g.term, g.ontology, g.definition
FROM go_bp_all AS a, go.go_term AS g
WHERE a.go_id = g.go_id LIMIT 10;'
data <- dbGetQuery(hgu95av2_dbconn(), sql)
data
## For illustration purposes, the internal id "_id" and the "go_id"
## from both tables is included in the output. This makes it clear
## that the "go_ids" can be used to join these tables but the internal
## ids can NOT. The internal IDs (which are always shown as _id) are
## suitable for joins within a single database, but cannot be used
## across databases.
Results
R version 3.3.1 (2016-06-21) -- "Bug in Your Hair"
Copyright (C) 2016 The R Foundation for Statistical Computing
Platform: x86_64-pc-linux-gnu (64-bit)
R is free software and comes with ABSOLUTELY NO WARRANTY.
You are welcome to redistribute it under certain conditions.
Type 'license()' or 'licence()' for distribution details.
R is a collaborative project with many contributors.
Type 'contributors()' for more information and
'citation()' on how to cite R or R packages in publications.
Type 'demo()' for some demos, 'help()' for on-line help, or
'help.start()' for an HTML browser interface to help.
Type 'q()' to quit R.
> library(AnnotationDbi)
Loading required package: stats4
Loading required package: BiocGenerics
Loading required package: parallel
Attaching package: 'BiocGenerics'
The following objects are masked from 'package:parallel':
clusterApply, clusterApplyLB, clusterCall, clusterEvalQ,
clusterExport, clusterMap, parApply, parCapply, parLapply,
parLapplyLB, parRapply, parSapply, parSapplyLB
The following objects are masked from 'package:stats':
IQR, mad, xtabs
The following objects are masked from 'package:base':
Filter, Find, Map, Position, Reduce, anyDuplicated, append,
as.data.frame, cbind, colnames, do.call, duplicated, eval, evalq,
get, grep, grepl, intersect, is.unsorted, lapply, lengths, mapply,
match, mget, order, paste, pmax, pmax.int, pmin, pmin.int, rank,
rbind, rownames, sapply, setdiff, sort, table, tapply, union,
unique, unsplit
Loading required package: Biobase
Welcome to Bioconductor
Vignettes contain introductory material; view with
'browseVignettes()'. To cite Bioconductor, see
'citation("Biobase")', and for packages 'citation("pkgname")'.
Loading required package: IRanges
Loading required package: S4Vectors
Attaching package: 'S4Vectors'
The following objects are masked from 'package:base':
colMeans, colSums, expand.grid, rowMeans, rowSums
> png(filename="/home/ddbj/snapshot/RGM3/R_BC/result/AnnotationDbi/AnnDbObj-class.Rd_%03d_medium.png", width=480, height=480)
> ### Name: AnnDbObj-objects
> ### Title: AnnDbObj objects
> ### Aliases: AnnDbObj-objects AnnDbObj class:AnnDbObj AnnDbObj-class dbconn
> ### dbconn,SQLiteConnection-method dbconn,environment-method
> ### dbconn,AnnDbObj-method dbfile dbfile,SQLiteConnection-method
> ### dbfile,environment-method dbfile,AnnDbObj-method dbmeta
> ### dbmeta,DBIConnection-method dbmeta,environment-method
> ### dbmeta,AnnDbObj-method dbschema dbschema,DBIConnection-method
> ### dbschema,environment-method dbschema,AnnDbObj-method dbInfo
> ### dbInfo,DBIConnection-method dbInfo,environment-method
> ### dbInfo,AnnDbObj-method
> ### Keywords: classes methods
>
> ### ** Examples
>
> library("hgu95av2.db")
Loading required package: org.Hs.eg.db
>
> dbconn(hgu95av2ENTREZID) # same as hgu95av2_dbconn()
<SQLiteConnection>
> dbfile(hgu95av2ENTREZID) # same as hgu95av2_dbfile()
[1] "/home/ddbj/local/lib64/R/library/hgu95av2.db/extdata/hgu95av2.sqlite"
>
> dbmeta(hgu95av2_dbconn(), "ORGANISM")
[1] "Homo sapiens"
> dbmeta(hgu95av2_dbconn(), "DBSCHEMA")
[1] "HUMANCHIP_DB"
> dbmeta(hgu95av2_dbconn(), "DBSCHEMAVERSION")
[1] "2.1"
>
> library("DBI")
> dbListTables(hgu95av2_dbconn()) #lists all tables on connection
[1] "accessions" "map_counts" "map_metadata" "metadata" "probes"
[6] "sqlite_stat1"
>
> ## If you use dbSendQuery instead of dbGetQuery
> ## (NOTE: for ease of use, this is defintitely NOT reccomended)
> ## Then you may need to know how to list results objects
> dbListResults(hgu95av2_dbconn()) #for listing results objects
[[1]]
<SQLiteResult>
>
>
> ## You can also list the fields by using this connection
> dbListFields(hgu95av2_dbconn(), "probes")
[1] "probe_id" "gene_id" "is_multiple"
> dbListFields(hgu95av2_dbconn(), "genes")
[1] "_id" "gene_id"
> dbschema(hgu95av2ENTREZID) # same as hgu95av2_dbschema()
CREATE TABLE metadata (name VARCHAR(80) PRIMARY KEY, value VARCHAR(255) );
CREATE TABLE map_metadata (
map_name VARCHAR(80) NOT NULL,
source_name VARCHAR(80) NOT NULL,
source_url VARCHAR(255) NOT NULL,
source_date VARCHAR(20) NOT NULL
);
CREATE TABLE map_counts (
map_name VARCHAR(80) PRIMARY KEY,
count INTEGER NOT NULL
);
CREATE TABLE sqlite_stat1(tbl,idx,stat);
CREATE TABLE accessions (probe_id VARCHAR(80),accession VARCHAR(20));
CREATE TABLE probes (probe_id VARCHAR(80), gene_id VARCHAR(10) NULL, is_multiple SMALLINT NOT NULL);
> ## According to the schema, the probes._id column references the genes._id
> ## column. Note that in all tables, the "_id" column is an internal id with
> ## no biological meaning (provided for allowing efficient joins between
> ## tables).
> ## The information about the probe to gene mapping is in probes:
> dbGetQuery(hgu95av2_dbconn(), "SELECT * FROM probes LIMIT 10")
probe_id gene_id is_multiple
1 1000_at 5595 0
2 1001_at 7075 0
3 1002_f_at 1557 0
4 1003_s_at 643 0
5 1004_at 643 0
6 1005_at 1843 0
7 1006_at 4319 0
8 1007_s_at 780 1
9 1007_s_at 100616237 1
10 1008_f_at 5610 0
> ## This mapping is in fact the ENTREZID map:
> toTable(hgu95av2ENTREZID)[1:10, ] # only relevant columns are retrieved
probe_id gene_id
1 1000_at 5595
2 1001_at 7075
3 1002_f_at 1557
4 1003_s_at 643
5 1004_at 643
6 1005_at 1843
7 1006_at 4319
8 1008_f_at 5610
9 1009_at 3094
10 100_g_at 5875
>
> dbInfo(hgu95av2GO) # same as hgu95av2_dbInfo()
name
1 DBSCHEMAVERSION
2 Db type
3 Supporting package
4 DBSCHEMA
5 ORGANISM
6 SPECIES
7 MANUFACTURER
8 CHIPNAME
9 MANUFACTURERURL
10 EGSOURCEDATE
11 EGSOURCENAME
12 EGSOURCEURL
13 CENTRALID
14 TAXID
15 GOSOURCENAME
16 GOSOURCEURL
17 GOSOURCEDATE
18 GOEGSOURCEDATE
19 GOEGSOURCENAME
20 GOEGSOURCEURL
21 KEGGSOURCENAME
22 KEGGSOURCEURL
23 KEGGSOURCEDATE
24 GPSOURCENAME
25 GPSOURCEURL
26 GPSOURCEDATE
27 ENSOURCEDATE
28 ENSOURCENAME
29 ENSOURCEURL
30 UPSOURCENAME
31 UPSOURCEURL
32 UPSOURCEDATE
value
1 2.1
2 ChipDb
3 AnnotationDbi
4 HUMANCHIP_DB
5 Homo sapiens
6 Human
7 Affymetrix
8 Human Genome U95 Set
9 http://www.affymetrix.com/support/technical/byproduct.affx?product=hgu95
10 2015-Sep27
11 Entrez Gene
12 ftp://ftp.ncbi.nlm.nih.gov/gene/DATA
13 ENTREZID
14 9606
15 Gene Ontology
16 ftp://ftp.geneontology.org/pub/go/godatabase/archive/latest-lite/
17 20150919
18 2015-Sep27
19 Entrez Gene
20 ftp://ftp.ncbi.nlm.nih.gov/gene/DATA
21 KEGG GENOME
22 ftp://ftp.genome.jp/pub/kegg/genomes
23 2011-Mar15
24 UCSC Genome Bioinformatics (Homo sapiens)
25 ftp://hgdownload.cse.ucsc.edu/goldenPath/hg19
26 2010-Mar22
27 2015-Jul16
28 Ensembl
29 ftp://ftp.ensembl.org/pub/current_fasta
30 Uniprot
31 http://www.uniprot.org/
32 Thu Oct 1 23:31:58 2015
>
> ##Advanced example:
> ##Sometimes you may wish to join data from across multiple databases at
> ##once:
> ## In the following example we will attach the GO database to the
> ## hgu95av2 database, and then grab information from separate tables
> ## in each database that meet a common criteria.
> library(hgu95av2.db)
> library("GO.db")
> attachSql <- paste('ATTACH "', GO_dbfile(), '" as go;', sep = "")
> dbGetQuery(hgu95av2_dbconn(), attachSql)
> sql <- 'SELECT DISTINCT a.go_id AS "hgu95av2.go_id",
+ a._id AS "hgu95av2._id",
+ g.go_id AS "GO.go_id", g._id AS "GO._id",
+ g.term, g.ontology, g.definition
+ FROM go_bp_all AS a, go.go_term AS g
+ WHERE a.go_id = g.go_id LIMIT 10;'
> data <- dbGetQuery(hgu95av2_dbconn(), sql)
> data
hgu95av2.go_id hgu95av2._id GO.go_id GO._id
1 GO:0000002 120 GO:0000002 30
2 GO:0000002 246 GO:0000002 30
3 GO:0000002 1435 GO:0000002 30
4 GO:0000002 1539 GO:0000002 30
5 GO:0000002 3251 GO:0000002 30
6 GO:0000002 3432 GO:0000002 30
7 GO:0000002 3546 GO:0000002 30
8 GO:0000002 3996 GO:0000002 30
9 GO:0000002 4358 GO:0000002 30
10 GO:0000002 5764 GO:0000002 30
term ontology
1 mitochondrial genome maintenance BP
2 mitochondrial genome maintenance BP
3 mitochondrial genome maintenance BP
4 mitochondrial genome maintenance BP
5 mitochondrial genome maintenance BP
6 mitochondrial genome maintenance BP
7 mitochondrial genome maintenance BP
8 mitochondrial genome maintenance BP
9 mitochondrial genome maintenance BP
10 mitochondrial genome maintenance BP
definition
1 The maintenance of the structure and integrity of the mitochondrial genome; includes replication and segregation of the mitochondrial chromosome.
2 The maintenance of the structure and integrity of the mitochondrial genome; includes replication and segregation of the mitochondrial chromosome.
3 The maintenance of the structure and integrity of the mitochondrial genome; includes replication and segregation of the mitochondrial chromosome.
4 The maintenance of the structure and integrity of the mitochondrial genome; includes replication and segregation of the mitochondrial chromosome.
5 The maintenance of the structure and integrity of the mitochondrial genome; includes replication and segregation of the mitochondrial chromosome.
6 The maintenance of the structure and integrity of the mitochondrial genome; includes replication and segregation of the mitochondrial chromosome.
7 The maintenance of the structure and integrity of the mitochondrial genome; includes replication and segregation of the mitochondrial chromosome.
8 The maintenance of the structure and integrity of the mitochondrial genome; includes replication and segregation of the mitochondrial chromosome.
9 The maintenance of the structure and integrity of the mitochondrial genome; includes replication and segregation of the mitochondrial chromosome.
10 The maintenance of the structure and integrity of the mitochondrial genome; includes replication and segregation of the mitochondrial chromosome.
> ## For illustration purposes, the internal id "_id" and the "go_id"
> ## from both tables is included in the output. This makes it clear
> ## that the "go_ids" can be used to join these tables but the internal
> ## ids can NOT. The internal IDs (which are always shown as _id) are
> ## suitable for joins within a single database, but cannot be used
> ## across databases.
>
>
>
>
>
> dev.off()
null device
1
>