Ira,
Thanks for getting a sqlite datatype into galaxy. I was wanting to subclass a
sqlite datatype for another application: cistrome CEAS
I had made a sqlite datatype that captured a little metadata that I thought
could be useful for the display peek, and perhaps for filtering.
Is this worth considering?
The peek for a CEAS DB would include table names, column names, and row count:
SQLite Database
GeneTable
(chrom,name,strand,txStart,txEnd,cdsStart,cdsEnd,exonCount,exonStarts,exonEnds,name2)
[24892]
GenomeBGP
(chrom,promoter,bipromoter,downstream,gene,rel_loc,rel_loc_cds,roi,chroms) [7]
GenomeBGS
(chrom,promoter,bipromoter,downstream,gene,rel_loc,rel_loc_cds,roi,Ns) [7]
GenomePieP (chrom,promoter,downstream,gene,enhancer,chroms) [7]
GenomePieS (chrom,promoter,downstream,gene,enhancer,total) [7]
$ hg diff /Users/jj/gxt/gxt/lib/galaxy/datatypes/binary.py
diff -r f002131cb905 lib/galaxy/datatypes/binary.py
--- a/lib/galaxy/datatypes/binary.py Fri Jul 25 12:01:34 2014 -0400
+++ b/lib/galaxy/datatypes/binary.py Fri Jul 25 12:13:05 2014 -0500
@@ -20,7 +20,7 @@
from bx.seq.twobit import TWOBIT_MAGIC_NUMBER, TWOBIT_MAGIC_NUMBER_SWAP,
TWOBIT_MAGIC_SIZE
-from galaxy.datatypes.metadata import MetadataElement
+from galaxy.datatypes.metadata import
MetadataElement,ListParameter,DictParameter
from galaxy.datatypes import metadata
from galaxy.datatypes.sniff import *
import dataproviders
@@ -550,8 +550,36 @@
@dataproviders.decorators.has_dataproviders
class SQlite ( Binary ):
+ """Class describing a Sqlite database """
+ MetadataElement( name="tables", default=[], param=ListParameter, desc="Database
Tables", readonly=True, visible=True, no_value=[] )
+ MetadataElement( name="table_columns", default={}, param=DictParameter,
desc="Database Table Columns", readonly=True, visible=True, no_value={} )
+ MetadataElement( name="table_row_count", default={}, param=DictParameter,
desc="Database Table Row Count", readonly=True, visible=True, no_value={} )
file_ext = "sqlite"
+ def init_meta( self, dataset, copy_from=None ):
+ Binary.init_meta( self, dataset, copy_from=copy_from )
+
+ def set_meta( self, dataset, overwrite = True, **kwd ):
+ try:
+ tables = []
+ columns = dict()
+ rowcounts = dict()
+ conn = sqlite3.connect(dataset.file_name)
+ c = conn.cursor()
+ tables_query = "SELECT name,sql FROM sqlite_master WHERE type='table'
ORDER BY name"
+ rslt = c.execute(tables_query).fetchall();
+ for table,sql in rslt:
+ tables.append(table)
+ columns[table] = re.sub('^.*\((.*)\)$','\\1',sql).split(',')
+ for table in tables:
+ row_query = "SELECT count(*) FROM %s" % table
+ rowcounts[table] = c.execute(row_query).fetchone()[0];
+ dataset.metadata.tables = tables
+ dataset.metadata.table_columns = columns
+ dataset.metadata.table_row_count = rowcounts
+ except Exception, exc:
+ pass
+
# Connects and runs a query that should work on any real database
# If the file is not sqlite, an exception will be thrown and the sniffer
will return false
def sniff( self, filename ):
@@ -567,7 +595,14 @@
def set_peek( self, dataset, is_multi_byte=False ):
if not dataset.dataset.purged:
- dataset.peek = "SQLite Database"
+ lines = ['SQLite Database']
+ if dataset.metadata.tables:
+ for table in dataset.metadata.tables:
+ try:
+ lines.append('%s (%s) [%s]' %
(table,','.join(dataset.metadata.table_columns.get(table,[])),dataset.metadata.table_row_count[table]))
+ except:
+ continue
+ dataset.peek = '\n'.join(lines)
dataset.blurb = data.nice_size( dataset.get_size() )
else:
dataset.peek = 'file does not exist'
Thanks,
JJ
--
James E. Johnson, Minnesota Supercomputing Institute, University of Minnesota
___________________________________________________________
Please keep all replies on the list by using "reply all"
in your mail client. To manage your subscriptions to this
and other Galaxy lists, please use the interface at:
http://lists.bx.psu.edu/
To search Galaxy mailing lists use the unified search at:
http://galaxyproject.org/search/mailinglists/
