LSSTApplications  1.1.2+25,10.0+13,10.0+132,10.0+133,10.0+224,10.0+41,10.0+8,10.0-1-g0f53050+14,10.0-1-g4b7b172+19,10.0-1-g61a5bae+98,10.0-1-g7408a83+3,10.0-1-gc1e0f5a+19,10.0-1-gdb4482e+14,10.0-11-g3947115+2,10.0-12-g8719d8b+2,10.0-15-ga3f480f+1,10.0-2-g4f67435,10.0-2-gcb4bc6c+26,10.0-28-gf7f57a9+1,10.0-3-g1bbe32c+14,10.0-3-g5b46d21,10.0-4-g027f45f+5,10.0-4-g86f66b5+2,10.0-4-gc4fccf3+24,10.0-40-g4349866+2,10.0-5-g766159b,10.0-5-gca2295e+25,10.0-6-g462a451+1
LSSTDataManagementBasePackage
Classes | Functions | Variables
lsst.datarel.schema Namespace Reference

Classes

class  DbMappingConfig
 

Functions

def _pk
 
def _k
 
def makeMysqlCsvConfig
 
def genericTableSql
 
def _sourceIndexes
 
def _colToField
 
def _getMappingKw
 
def sourceTableSql
 
def objectTableSql
 
def coaddSourceTableSql
 
def forcedSourceTableSql
 

Variables

list __all__
 
dictionary _dbType
 
list _sourceMappings
 
list _objectMappings
 
list _filterMappings
 
list _coaddSourceMappings
 
list _forcedSourceMappings
 

Function Documentation

def lsst.datarel.schema._colToField (   col)
private 
Turn a database column name back into a C++ table field name

Definition at line 316 of file schema.py.

316 
317 def _colToField(col):
318  """Turn a database column name back into a C++ table field name"""
319  i = col.rfind('_')
320  if i == -1:
321  return col
322  field = col[:i].replace('_', '.')
323  suffix = col[i+1:]
324  if suffix in ['x', 'y', 'Ixx', 'Iyy', 'Ixy', 'ra', 'decl']:
325  return field
326  if suffix.endswith('Var') or suffix.endswith('Cov'):
327  return field + '.err'
328  # TODO: generic Cov<T> or Array<T> fields not supported yet,
329  # but so far no such fields are mapped to canonical
330  # Source/Object table columns.
331  return col.replace('_', '.')
def lsst.datarel.schema._getMappingKw (   slots,
  sourceProcessingConfig,
  measPrefix = None 
)
private 
Return substitution parameters for mapping table entries.

Definition at line 332 of file schema.py.

332 
333 def _getMappingKw(slots, sourceProcessingConfig, measPrefix=None):
334  """Return substitution parameters for mapping table entries.
335  """
336  kw = dict()
337  kw['measPrefix'] = (measPrefix or '').replace('.', '_')
338  kw['exposurePrefix'] = sourceProcessingConfig.exposurePrefix.replace('.', '_')
339  kw['clusterPrefix'] = sourceProcessingConfig.clusterPrefix.replace('.', '_')
340  kw['centroid'] = slots.centroid.replace('.', '_') if slots.centroid else '__X__'
341  kw['shape'] = slots.shape.replace('.', '_') if slots.shape else '__X__'
342  kw['psfFlux'] = slots.psfFlux.replace('.', '_') if slots.psfFlux else '__X__'
343  kw['apFlux'] = slots.apFlux.replace('.', '_') if slots.apFlux else '__X__'
344  kw['modelFlux'] = slots.modelFlux.replace('.', '_') if slots.modelFlux else '__X__'
345  kw['instFlux'] = slots.instFlux.replace('.', '_') if slots.instFlux else '__X__'
346  return kw
def lsst.datarel.schema._k (   cols)
private

Definition at line 51 of file schema.py.

51 
52 def _k(cols):
53  if not isinstance(cols, basestring):
54  idx = '_'.join(cols)
55  cols = ', '.join(cols)
56  else:
57  idx = cols
58  return str.format('KEY IDX_{0} ({0})', idx, cols)
59 
def lsst.datarel.schema._pk (   cols)
private

Definition at line 46 of file schema.py.

46 
47 def _pk(cols):
48  if not isinstance(cols, basestring):
49  cols = ', '.join(colName)
50  return str.format('PRIMARY KEY ({0})', cols)
def lsst.datarel.schema._sourceIndexes (   sourceProcessingConfig)
private 
Return the list of C++ Source field names to create indexes on.

@param sourceProcessingConfig     lsst.ap.cluster.SourceProcessingConfig;
                                  describes source processing performed by
                                  SourceAssoc.

Definition at line 236 of file schema.py.

236 
237 def _sourceIndexes(sourceProcessingConfig):
238  """Return the list of C++ Source field names to create indexes on.
239 
240  @param sourceProcessingConfig lsst.ap.cluster.SourceProcessingConfig;
241  describes source processing performed by
242  SourceAssoc.
243  """
244  indexes = set()
245  indexes.add("parent")
246  if sourceProcessingConfig.exposurePrefix:
247  indexes.add(sourceProcessingConfig.exposurePrefix + ".id")
248  if not sourceProcessingConfig.multiBand:
249  indexes.add(sourceProcessingConfig.exposurePrefix + ".filter.id")
250  if sourceProcessingConfig.clusterPrefix:
251  indexes.add(sourceProcessingConfig.clusterPrefix + ".id")
252  return indexes
253 
254 
# mappings from run-specific table column names to canonical Source columns
def lsst.datarel.schema.coaddSourceTableSql (   coaddName,
  schema,
  sourceConversionConfig,
  asView,
  sourceProcessingConfig,
  slotConfig,
  measPrefix 
) 
Return a tuple of SQL statements (createStmt, loadStmt, sourceStmt)
for a coadd source table. The canonical table name is obtained by
captilizing the first letter of coaddName and appending 'Source'. The
run specific table name is derived from the former by prepending 'Run'.

createStmt :    CREATE TABLE statement for the Run<CoaddName>Source table,
                which includes all fields from the run-specific
                lsst.afw.table.Schema for source tables output by the
                pipelines.

loadStmt :      LOAD DATA statement for the Run<CoaddName>Source table.
                This is a format string; to generate valid SQL a fileName
                must be supplied, e.g.:

                loadStmt.format(fileName='source.csv')

sourceStmt :    Map the Run<CoaddName>Source table to the canonical
                <CoaddName>Source schema. This will either create a VIEW,
                or INSERT into the materialized equivalent.

@param coaddName
    Coadd name (camel-case), e.g. 'deep' or 'goodSeeing'.
@param schema
    lsst.afw.table.Schema for coadd-sources.   
@param sourceConversionConfig
    lsst.ap.utils.CsvConversionConfig - parameters used for
    C++ to CSV conversion.
@param asView
    True if the canonical table should be constructed as a VIEW on
    top of the run-specific table.
@param sourceProcessingConfig
    lsst.ap.cluster.SourceProcessingConfig - parameters used to
    denormalize the C++ schema produced by the pipeline.
@param slotConfig
    lsst.meas.algorithms.SlotConfig - pipeline slot mappings.
@param measPrefix
    Prefix for measurement field names.

Definition at line 630 of file schema.py.

630 
631  measPrefix):
632  """Return a tuple of SQL statements (createStmt, loadStmt, sourceStmt)
633  for a coadd source table. The canonical table name is obtained by
634  captilizing the first letter of coaddName and appending 'Source'. The
635  run specific table name is derived from the former by prepending 'Run'.
636 
637  createStmt : CREATE TABLE statement for the Run<CoaddName>Source table,
638  which includes all fields from the run-specific
639  lsst.afw.table.Schema for source tables output by the
640  pipelines.
641 
642  loadStmt : LOAD DATA statement for the Run<CoaddName>Source table.
643  This is a format string; to generate valid SQL a fileName
644  must be supplied, e.g.:
645 
646  loadStmt.format(fileName='source.csv')
647 
648  sourceStmt : Map the Run<CoaddName>Source table to the canonical
649  <CoaddName>Source schema. This will either create a VIEW,
650  or INSERT into the materialized equivalent.
651 
652  @param coaddName
653  Coadd name (camel-case), e.g. 'deep' or 'goodSeeing'.
654  @param schema
655  lsst.afw.table.Schema for coadd-sources.
656  @param sourceConversionConfig
657  lsst.ap.utils.CsvConversionConfig - parameters used for
658  C++ to CSV conversion.
659  @param asView
660  True if the canonical table should be constructed as a VIEW on
661  top of the run-specific table.
662  @param sourceProcessingConfig
663  lsst.ap.cluster.SourceProcessingConfig - parameters used to
664  denormalize the C++ schema produced by the pipeline.
665  @param slotConfig
666  lsst.meas.algorithms.SlotConfig - pipeline slot mappings.
667  @param measPrefix
668  Prefix for measurement field names.
669  """
670  # Generate SQL for run specific table
671  createStmt, loadStmt = genericTableSql(
672  schema,
673  sourceConversionConfig,
674  _sourceIndexes(sourceProcessingConfig))
675  # build substitution parameters for mapping table
676  kw = _getMappingKw(
677  slotConfig,
678  sourceProcessingConfig,
679  measPrefix)
680  # build selection/output column lists
681  selcols = []
682  outcols = []
683  CoaddName = coaddName[0].upper() + coaddName[1:]
684  for runFmt, srcCol in _coaddSourceMappings:
685  runCol = runFmt.format(**kw)
686  srcCol = srcCol.format(coaddName=coaddName, CoaddName=CoaddName)
687  if sourceProcessingConfig.multiBand and srcCol == 'filterId':
688  continue # multi-band source has no filterId
689  field = _colToField(runCol)
690  isFlag = srcCol.startswith('flag')
691  if isFlag and not sourceConversionConfig.flagsAsBits:
692  continue
693  if field in schema or runCol == 'coord_htmId20':
694  selcols.append(runCol)
695  elif isFlag:
696  selcols.append("b'0'")
697  else:
698  selcols.append('NULL')
699  outcols.append(srcCol)
700  if not sourceConversionConfig.flagsAsBits:
701  # Deal with canonical flags packed into BIGINTs
702  n = (len(sourceConversionConfig.canonicalFlags) + 62) / 63
703  if n == 1:
704  selcols.append('flags')
705  outcols.append('flags')
706  else:
707  for i in xrange(1, n + 1):
708  c = 'flags{}'.format(i)
709  selcols.append(c)
710  outcols.append(c)
711  tableName = CoaddName + 'Source'
712  runTableName = 'Run' + tableName
713  if asView:
714  # Replace the official version of <CoaddName>Source with an equivalent VIEW
715  sourceStmt = 'CREATE OR REPLACE VIEW {} AS SELECT\n\t'.format(tableName)
716  sourceStmt += ',\n\t'.join(a + ' AS ' + b for a,b in zip(selcols, outcols))
717  sourceStmt += '\nFROM {};'.format(runTableName)
718  else:
719  # Use the definition of Source from cat (i.e. the one used by the
720  # schema browser for documentation purposes). This should cause
721  # ingest to fail if this code and the canonical schema are not in sync.
722  sourceStmt = 'INSERT INTO {} (\n\t'.format(tableName)
723  sourceStmt += ',\n\t'.join(outcols)
724  sourceStmt += ')\nSELECT\n\t'
725  sourceStmt += ',\n\t'.join(selcols)
726  sourceStmt += '\nFROM {};\n'.format(runTableName)
727  return (createStmt.format(tableName=runTableName),
728  loadStmt.format(tableName=runTableName, fileName='{fileName}'),
729  sourceStmt)
730 
# mappings from run-specific table column names to canonical ForcedSource columns
def lsst.datarel.schema.forcedSourceTableSql (   coaddName,
  schema,
  sourceConversionConfig,
  asView,
  sourceProcessingConfig,
  slotConfig,
  measPrefix 
) 
Return a tuple of SQL statements (createStmt, loadStmt, sourceStmt)
for a forced source table. The canonical table name is obtained by
capitalizing the first letter of coaddName and appending 'ForcedSource'.
The run specific table name is derived from the former by prepending 'Run'.

createStmt :    CREATE TABLE statement for the Run<CoaddName>ForcedSource
                table, which includes all fields from the run-specific
                lsst.afw.table.Schema for source tables output by the
                pipelines.

loadStmt :      LOAD DATA statement for the Run<CoaddName>ForcedSource
                table.  This is a format string; to generate valid SQL a
                fileName must be supplied, e.g.:

                loadStmt.format(fileName='source.csv')

sourceStmt :    Map the Run<CoaddName>ForcedSource table to the canonical
                <CoaddName>ForcedSource schema. This will either create a
                VIEW, or INSERT into the materialized equivalent.

@param coaddName
    Coadd name (camel-case), e.g. 'deep' or 'goodSeeing'.
@param schema
    lsst.afw.table.Schema for forced sources.   
@param sourceConversionConfig
    lsst.ap.utils.CsvConversionConfig - parameters used for
    C++ to CSV conversion.
@param asView
    True if the canonical table should be constructed as a VIEW on
    top of the run-specific table.
@param sourceProcessingConfig
    lsst.ap.cluster.SourceProcessingConfig - parameters used to
    denormalize the C++ schema produced by the pipeline.
@param slotConfig
    lsst.meas.algorithms.SlotConfig - pipeline slot mappings.
@param measPrefix
    Prefix for measurement field names.

Definition at line 795 of file schema.py.

795 
796  measPrefix):
797  """Return a tuple of SQL statements (createStmt, loadStmt, sourceStmt)
798  for a forced source table. The canonical table name is obtained by
799  capitalizing the first letter of coaddName and appending 'ForcedSource'.
800  The run specific table name is derived from the former by prepending 'Run'.
801 
802  createStmt : CREATE TABLE statement for the Run<CoaddName>ForcedSource
803  table, which includes all fields from the run-specific
804  lsst.afw.table.Schema for source tables output by the
805  pipelines.
806 
807  loadStmt : LOAD DATA statement for the Run<CoaddName>ForcedSource
808  table. This is a format string; to generate valid SQL a
809  fileName must be supplied, e.g.:
810 
811  loadStmt.format(fileName='source.csv')
812 
813  sourceStmt : Map the Run<CoaddName>ForcedSource table to the canonical
814  <CoaddName>ForcedSource schema. This will either create a
815  VIEW, or INSERT into the materialized equivalent.
816 
817  @param coaddName
818  Coadd name (camel-case), e.g. 'deep' or 'goodSeeing'.
819  @param schema
820  lsst.afw.table.Schema for forced sources.
821  @param sourceConversionConfig
822  lsst.ap.utils.CsvConversionConfig - parameters used for
823  C++ to CSV conversion.
824  @param asView
825  True if the canonical table should be constructed as a VIEW on
826  top of the run-specific table.
827  @param sourceProcessingConfig
828  lsst.ap.cluster.SourceProcessingConfig - parameters used to
829  denormalize the C++ schema produced by the pipeline.
830  @param slotConfig
831  lsst.meas.algorithms.SlotConfig - pipeline slot mappings.
832  @param measPrefix
833  Prefix for measurement field names.
834  """
835  # Generate SQL for run specific table
836  createStmt, loadStmt = genericTableSql(
837  schema,
838  sourceConversionConfig,
839  _sourceIndexes(sourceProcessingConfig))
840  # build substitution parameters for mapping table
841  if sourceProcessingConfig.clusterPrefix is None:
842  sourceProcessingConfig.clusterPrefix = ""
843  kw = _getMappingKw(
844  slotConfig,
845  sourceProcessingConfig,
846  measPrefix)
847  # build selection/output column lists
848  selcols = []
849  outcols = []
850  CoaddName = coaddName[0].upper() + coaddName[1:]
851  for runFmt, srcCol in _forcedSourceMappings:
852  runCol = runFmt.format(**kw)
853  srcCol = srcCol.format(coaddName=coaddName, CoaddName=CoaddName)
854  if sourceProcessingConfig.multiBand and srcCol == 'filterId':
855  continue # multi-band source has no filterId
856  field = _colToField(runCol)
857  isFlag = srcCol.startswith('flag')
858  if isFlag and not sourceConversionConfig.flagsAsBits:
859  continue
860  if field in schema or runCol == 'coord_htmId20':
861  selcols.append(runCol)
862  elif isFlag:
863  selcols.append("b'0'")
864  else:
865  selcols.append('NULL')
866  outcols.append(srcCol)
867  if not sourceConversionConfig.flagsAsBits:
868  # Deal with canonical flags packed into BIGINTs
869  n = (len(sourceConversionConfig.canonicalFlags) + 62) / 63
870  if n == 1:
871  selcols.append('flags')
872  outcols.append('flags')
873  else:
874  for i in xrange(1, n + 1):
875  c = 'flags{}'.format(i)
876  selcols.append(c)
877  outcols.append(c)
878  tableName = CoaddName + 'ForcedSource'
879  runTableName = 'Run' + tableName
880  if asView:
881  # Replace the official version of <CoaddName>ForcedSource with an equivalent VIEW
882  sourceStmt = 'CREATE OR REPLACE VIEW {} AS SELECT\n\t'.format(tableName)
883  sourceStmt += ',\n\t'.join(a + ' AS ' + b for a,b in zip(selcols, outcols))
884  sourceStmt += '\nFROM {};'.format(runTableName)
885  else:
886  # Use the definition of Source from cat (i.e. the one used by the
887  # schema browser for documentation purposes). This should cause
888  # ingest to fail if this code and the canonical schema are not in sync.
889  sourceStmt = 'INSERT INTO {} (\n\t'.format(tableName)
890  sourceStmt += ',\n\t'.join(outcols)
891  sourceStmt += ')\nSELECT\n\t'
892  sourceStmt += ',\n\t'.join(selcols)
893  sourceStmt += '\nFROM {};\n'.format(runTableName)
894  return (createStmt.format(tableName=runTableName),
895  loadStmt.format(tableName=runTableName, fileName='{fileName}'),
896  sourceStmt)
def lsst.datarel.schema.genericTableSql (   schema,
  csvConversionConfig,
  indexedFields 
) 
Return a pair of SQL template strings (createStmt, loadStmt).

createStmt : a format string for the CREATE TABLE statement corresponding
             to the given schema and desired indexes. To generate valid
             SQL, tableName must be supplied, e.g.:

             str.format(create, tableName='MyTable')

loadStmt   : a format string for the corresponding LOAD DATA statement.
             To generate valid SQL, tableName and fileName must be supplied,
             e.g.:

             str.format(load, tableName='MyTable', fileName='MyTable.csv')

Note that the generated LOAD statement will never REPLACE data, and assumes
that CSV files conform to the format returned by makeMysqlCsvConfig().

@param schema               lsst.afw.table.Schema describing the C++ table
                            to map to a MySQL database table.
@param csvConversionConfig  lsst.ap.utils.CsvConversionConfig describing
                            the C++ table to CSV file conversion options. 
@param indexedFields        List or set of C++ field names to create indexes on

Definition at line 91 of file schema.py.

91 
92 def genericTableSql(schema, csvConversionConfig, indexedFields):
93  """Return a pair of SQL template strings (createStmt, loadStmt).
94 
95  createStmt : a format string for the CREATE TABLE statement corresponding
96  to the given schema and desired indexes. To generate valid
97  SQL, tableName must be supplied, e.g.:
98 
99  str.format(create, tableName='MyTable')
100 
101  loadStmt : a format string for the corresponding LOAD DATA statement.
102  To generate valid SQL, tableName and fileName must be supplied,
103  e.g.:
104 
105  str.format(load, tableName='MyTable', fileName='MyTable.csv')
106 
107  Note that the generated LOAD statement will never REPLACE data, and assumes
108  that CSV files conform to the format returned by makeMysqlCsvConfig().
109 
110  @param schema lsst.afw.table.Schema describing the C++ table
111  to map to a MySQL database table.
112  @param csvConversionConfig lsst.ap.utils.CsvConversionConfig describing
113  the C++ table to CSV file conversion options.
114  @param indexedFields List or set of C++ field names to create indexes on
115  """
116  dbkeys = []
117  coldefs = []
118  columns = []
119  setexprs = []
120 
121  def _append(dbcol, dbty, suffixes):
122  for suffix in suffixes:
123  coldefs.append(str.format('{}{} {} NULL', dbcol, suffix, dbty))
124  columns.append(dbcol + suffix)
125 
126  for item in schema.asList():
127  name = item.field.getName()
128  #replace all non-word characters with underscore
129  dbcol = re.sub('[\W]','_',name)
130  ty = item.key.getTypeString()
131  if ty in _dbType:
132  dbty = _dbType[ty]
133  constraint = 'NULL'
134  if name == 'id':
135  dbkeys.append(_pk(dbcol))
136  constraint = 'NOT NULL'
137  elif ty == 'Flag':
138  if not csvConversionConfig.flagsAsBits:
139  continue # we will deal with flags later
140  constraint = 'NOT NULL'
141  elif ty == 'I' or ty == 'L':
142  if name in csvConversionConfig.nullableIntegers:
143  constraint = 'NULL'
144  else:
145  constraint = 'NOT NULL'
146  else:
147  constraint = 'NULL'
148  coldefs.append(' '.join([dbcol, dbty, constraint]))
149  if ty == 'Flag':
150  columns.append('@' + dbcol)
151  setexprs.append(str.format('{0} = CAST(@{0} AS UNSIGNED)', dbcol))
152  else:
153  columns.append(dbcol)
154  if name != 'id' and name in indexedFields:
155  dbkeys.append(_k(dbcol))
156  elif ty == 'Coord':
157  if name == "coord":
158  # the Coord slot field
159  coldefs.append('coord_ra DOUBLE NULL')
160  coldefs.append('coord_decl DOUBLE NULL')
161  coldefs.append('coord_htmId20 BIGINT NULL')
162  columns.append('coord_ra')
163  columns.append('coord_decl')
164  setexprs.append('coord_htmId20 = scisql_s2HtmId(coord_ra, coord_decl, 20)')
165  dbkeys.append(_k('coord_htmId20'))
166  dbkeys.append(_k('coord_decl'))
167  else:
168  _append(dbcol, 'DOUBLE', ['_ra', '_decl'])
169  elif ty == 'ArrayF' or ty == 'ArrayD':
170  dbty = _dbType[ty[-1]]
171  for i in xrange(1, item.key.getSize() + 1):
172  coldefs.append(str.format('{}_{} {} NULL', dbcol, i, dbty))
173  columns.append(str.format('{}_{}', dbcol, i))
174  elif ty == 'PointI' or ty == 'PointF' or ty == 'PointD':
175  dbty = _dbType[ty[-1]]
176  _append(dbcol, dbty, ['_x', '_y'])
177  elif ty == 'MomentsF' or ty == 'MomentsD':
178  dbty = _dbType[ty[-1]]
179  _append(dbcol, dbty, ['_Ixx', '_Iyy', '_Ixy'])
180  elif ty == 'CovF' or ty == 'CovD':
181  dbty = _dbType[ty[-1]]
182  sz = item.key.getSize()
183  for i in xrange(1, sz + 1):
184  for j in xrange(i, sz + 1):
185  coldefs.append(str.format('{}_{}_{} {} NULL', dbcol, i, j, dbty))
186  columns.append(str.format('{}_{}_{}', dbcol, i, j))
187  elif ty == 'CovPointF' or ty == 'CovPointD':
188  dbty = _dbType[ty[-1]]
189  if name.endswith('.err'):
190  dbcol = dbcol[:-4]
191  if item.field.getUnits() == 'rad^2':
192  # HACK: this is a coordinate covariance matrix
193  _append(dbcol, dbty, ['_raVar', '_radeclCov',
194  '_declVar'])
195  continue
196  _append(dbcol, dbty, ['_xVar', '_xyCov',
197  '_yVar'])
198  elif ty == 'CovMomentsF' or ty == 'CovMomentsD':
199  dbty = _dbType[ty[-1]]
200  if name.endswith('.err'):
201  dbcol = dbcol[:-4]
202  _append(dbcol, dbty, ['_IxxVar', '_IxxIyyCov', '_IxxIxyCov',
203  '_IyyVar', '_IyyIxyCov',
204  '_IxyVar'])
205  else:
206  raise RuntimeError(ty + ' is not a recognized AFW field type string!')
207  if not csvConversionConfig.flagsAsBits:
208  # add BIGINT flag columns
209  n = (schema.getFlagFieldCount() + 62) / 63
210  for i in xrange(1, n + 1):
211  coldefs.append(str.format('runFlags{} BIGINT NOT NULL', i))
212  columns.append(str.format('runFlags{}', i))
213  # add BIGINT flag columns for canonical flags in canonical order
214  n = (len(csvConversionConfig.canonicalFlags) + 62) / 63
215  for i in xrange(1, n + 1):
216  coldefs.append(str.format('flags{} BIGINT NOT NULL', i))
217  columns.append(str.format('flags{}', i))
218  # Finally, create schema SQL and LOAD DATA templates
219  createStmt = 'CREATE TABLE IF NOT EXISTS {tableName} (\n\t'
220  createStmt += ',\n\t'.join(coldefs + dbkeys)
221  createStmt += '\n) ENGINE=MyISAM;\n'
222 
223  loadStmt = ("LOAD DATA LOCAL INFILE '{fileName}'\n"
224  "\tINTO TABLE {tableName}\n"
225  "\tFIELDS TERMINATED BY ','\n"
226  "(\n\t")
227  loadStmt += ',\n\t'.join(columns)
228  if len(setexprs) == 0:
229  loadStmt += '\n);'
230  else:
231  loadStmt += '\n) SET\n\t'
232  loadStmt += ',\n\t'.join(setexprs)
233  loadStmt += ';'
234  return createStmt, loadStmt
235 
def lsst.datarel.schema.makeMysqlCsvConfig ( ) 
Return the lsst.ap.utils.CsvConfig to use when writing out CSV files
that must be loaded into MySQL.

Definition at line 60 of file schema.py.

60 
61 def makeMysqlCsvConfig():
62  """Return the lsst.ap.utils.CsvConfig to use when writing out CSV files
63  that must be loaded into MySQL.
64  """
65  cfg = lsst.ap.utils.CsvConfig()
66  cfg.quoting = 'QUOTE_NONE' # C++ tables cannot contain strings (yet)
67  cfg.delimiter = ','
68  cfg.escapeChar = '\\'
69  cfg.quoteChar = ''
70  cfg.skipInitialSpace = False
71  cfg.doubleQuote = False
72  cfg.standardEscapes = True
73  cfg.trailingDelimiter = False
74  cfg.nonfiniteAsNull = True
75  return cfg
76 
lsst.datarel.schema.makeMysqlCsvConfig
def makeMysqlCsvConfig
Definition: schema.py:60
def lsst.datarel.schema.objectTableSql (   schema,
  dbMappingConfig,
  sourceAssocConfig,
  filters 
) 
Return a tuple of SQL statements (createStmt, loadStmt, objectStmt)
for the Object table.

createStmt :    CREATE TABLE statement for the RunObject table, which
                includes all fields from the run-specific
                lsst.afw.table.Schema for source cluster tables output
                by the SourceAssoc pipeline.

loadStmt :      LOAD DATA statement for the RunObject table. This is
                a format string; to generate valid SQL a fileName must
                be supplied, e.g.:

                loadStmt.format(fileName='object.csv')

objectStmt :    Map the RunObject table to the canonical Object schema.
                This will either create a VIEW, or INSERT into its
                materialized equivalent.

@param schema               lsst.afw.table.Schema for objects (source clusters)
@param dbMappingConfig      lsst.datarel.DbMappingConfig
@param sourceAssocConfig    lsst.ap.tasks.sourceAssoc.SourceAssocConfig
@param filters              Iterable over the filter names included in the
                            canonical Object table.

Definition at line 473 of file schema.py.

473 
474 def objectTableSql(schema, dbMappingConfig, sourceAssocConfig, filters):
475  """Return a tuple of SQL statements (createStmt, loadStmt, objectStmt)
476  for the Object table.
477 
478  createStmt : CREATE TABLE statement for the RunObject table, which
479  includes all fields from the run-specific
480  lsst.afw.table.Schema for source cluster tables output
481  by the SourceAssoc pipeline.
482 
483  loadStmt : LOAD DATA statement for the RunObject table. This is
484  a format string; to generate valid SQL a fileName must
485  be supplied, e.g.:
486 
487  loadStmt.format(fileName='object.csv')
488 
489  objectStmt : Map the RunObject table to the canonical Object schema.
490  This will either create a VIEW, or INSERT into its
491  materialized equivalent.
492 
493  @param schema lsst.afw.table.Schema for objects (source clusters)
494  @param dbMappingConfig lsst.datarel.DbMappingConfig
495  @param sourceAssocConfig lsst.ap.tasks.sourceAssoc.SourceAssocConfig
496  @param filters Iterable over the filter names included in the
497  canonical Object table.
498  """
499  # Generate SQL for run specific table
500  createStmt, loadStmt = genericTableSql(
501  schema, dbMappingConfig.objectConversion, set())
502  # build substitution parameters for mapping table
503  kw = _getMappingKw(
504  sourceAssocConfig.measSlots,
505  sourceAssocConfig.sourceProcessing,
506  sourceAssocConfig.measPrefix)
507  # build selection/output column lists
508  selcols = []
509  outcols = []
510  for runFmt, objCol in _objectMappings:
511  runCol = runFmt.format(**kw)
512  field = _colToField(runCol)
513  isFlag = objCol.startswith('flag')
514  if isFlag and not dbMappingConfig.objectConversion.flagsAsBits:
515  continue
516  if field in schema or runCol == 'coord_htmId20':
517  selcols.append(runCol)
518  elif isFlag:
519  selcols.append("b'0'")
520  else:
521  selcols.append('NULL')
522  outcols.append(objCol)
523  for filter in filters:
524  kw['filter'] = filter
525  for runFmt, objFmt in _filterMappings:
526  runCol = runFmt.format(**kw)
527  objCol = objFmt.format(filter=filter)
528  field = _colToField(runCol)
529  isFlag = objCol.startswith('flag')
530  if isFlag and not dbMappingConfig.objectConversion.flagsAsBits:
531  continue
532  if field in schema:
533  selcols.append(runCol)
534  elif isFlag:
535  selcols.append("b'0'")
536  else:
537  selcols.append('NULL')
538  outcols.append(objCol)
539  if not dbMappingConfig.objectConversion.flagsAsBits:
540  # Deal with canonical flags packed into BIGINTs
541  n = (len(dbMappingConfig.objectConversion.canonicalFlags) + 62) / 63
542  if n == 1:
543  selcols.append('flags')
544  outcols.append('flags')
545  else:
546  for i in xrange(1, n + 1):
547  c = 'flags{}'.format(i)
548  selcols.append(c)
549  outcols.append(c)
550  if dbMappingConfig.asView:
551  # Replace the official version of Object with an equivalent VIEW
552  objectStmt = 'CREATE OR REPLACE VIEW Object AS SELECT\n\t'
553  objectStmt += ',\n\t'.join(a + ' AS ' + b for a,b in zip(selcols, outcols))
554  objectStmt += '\nFROM RunObject;'
555  else:
556  # Use the definition of Object from cat (i.e. the one used by the
557  # schema browser for documentation purposes). This should cause
558  # ingest to fail if this code and the canonical schema are not in sync.
559  objectStmt = 'INSERT INTO Object (\n\t'
560  objectStmt += ',\n\t'.join(outcols)
561  objectStmt += ')\nSELECT\n\t'
562  objectStmt += ',\n\t'.join(selcols)
563  objectStmt += '\nFROM RunObject;'
564  return (createStmt.format(tableName='RunObject'),
565  loadStmt.format(tableName='RunObject', fileName='{fileName}'),
566  objectStmt)
567 
def lsst.datarel.schema.sourceTableSql (   schema,
  dbMappingConfig,
  sourceAssocConfig 
) 
Return a tuple of SQL statements (createStmt, loadStmt, sourceStmt)
for the Source table.

createStmt :    CREATE TABLE statement for the RunSource table, which
                includes all fields from the run-specific
                lsst.afw.table.Schema for source tables output by the
                pipelines.

loadStmt :      LOAD DATA statement for the RunSource table. This is
                a format string; to generate valid SQL a fileName must
                be supplied, e.g.:

                loadStmt.format(fileName='source.csv')

sourceStmt :    Map the RunSource table to the canonical Source schema.
                This will either create a VIEW, or INSERT into the
                materialized equivalent.

@param schema               lsst.afw.table.Schema for sources
@param dbMappingConfig      lsst.datarel.DbMappingConfig
@param sourceAssocConfig    lsst.ap.tasks.sourceAssoc.SourceAssocConfig

Definition at line 347 of file schema.py.

347 
348 def sourceTableSql(schema, dbMappingConfig, sourceAssocConfig):
349  """Return a tuple of SQL statements (createStmt, loadStmt, sourceStmt)
350  for the Source table.
351 
352  createStmt : CREATE TABLE statement for the RunSource table, which
353  includes all fields from the run-specific
354  lsst.afw.table.Schema for source tables output by the
355  pipelines.
356 
357  loadStmt : LOAD DATA statement for the RunSource table. This is
358  a format string; to generate valid SQL a fileName must
359  be supplied, e.g.:
360 
361  loadStmt.format(fileName='source.csv')
362 
363  sourceStmt : Map the RunSource table to the canonical Source schema.
364  This will either create a VIEW, or INSERT into the
365  materialized equivalent.
366 
367  @param schema lsst.afw.table.Schema for sources
368  @param dbMappingConfig lsst.datarel.DbMappingConfig
369  @param sourceAssocConfig lsst.ap.tasks.sourceAssoc.SourceAssocConfig
370  """
371  # Generate SQL for run specific table
372  createStmt, loadStmt = genericTableSql(
373  schema,
374  dbMappingConfig.sourceConversion,
375  _sourceIndexes(sourceAssocConfig.sourceProcessing))
376  # build substitution parameters for mapping table
377  kw = _getMappingKw(
378  sourceAssocConfig.measSlots,
379  sourceAssocConfig.sourceProcessing,
380  sourceAssocConfig.measPrefix)
381  # build selection/output column lists
382  selcols = []
383  outcols = []
384  for runFmt, srcCol in _sourceMappings:
385  runCol = runFmt.format(**kw)
386  field = _colToField(runCol)
387  isFlag = srcCol.startswith('flag')
388  if isFlag and not dbMappingConfig.sourceConversion.flagsAsBits:
389  continue
390  if field in schema or runCol == 'coord_htmId20':
391  selcols.append(runCol)
392  elif isFlag:
393  selcols.append("b'0'")
394  else:
395  selcols.append('NULL')
396  outcols.append(srcCol)
397  if not dbMappingConfig.sourceConversion.flagsAsBits:
398  # Deal with canonical flags packed into BIGINTs
399  n = (len(dbMappingConfig.sourceConversion.canonicalFlags) + 62) / 63
400  if n == 1:
401  selcols.append('flags')
402  outcols.append('flags')
403  else:
404  for i in xrange(1, n + 1):
405  c = 'flags{}'.format(i)
406  selcols.append(c)
407  outcols.append(c)
408  if dbMappingConfig.asView:
409  # Replace the official version of Source with an equivalent VIEW
410  sourceStmt = 'CREATE OR REPLACE VIEW Source AS SELECT\n\t'
411  sourceStmt += ',\n\t'.join(a + ' AS ' + b for a,b in zip(selcols, outcols))
412  sourceStmt += '\nFROM RunSource;'
413  else:
414  # Use the definition of Source from cat (i.e. the one used by the
415  # schema browser for documentation purposes). This should cause
416  # ingest to fail if this code and the canonical schema are not in sync.
417  sourceStmt = 'INSERT INTO Source (\n\t'
418  sourceStmt += ',\n\t'.join(outcols)
419  sourceStmt += ')\nSELECT\n\t'
420  sourceStmt += ',\n\t'.join(selcols)
421  sourceStmt += '\nFROM RunSource;\n'
422  return (createStmt.format(tableName='RunSource'),
423  loadStmt.format(tableName='RunSource', fileName='{fileName}'),
424  sourceStmt)

Variable Documentation

list lsst.datarel.schema.__all__
Initial value:
1 = ['makeMysqlCsvConfig',
2  'DbMappingConfig',
3  'genericTableSql',
4  'sourceTableSql',
5  'objectTableSql',
6  'coaddSourceTableSql',
7  ]

Definition at line 29 of file schema.py.

list lsst.datarel.schema._coaddSourceMappings

Definition at line 568 of file schema.py.

dictionary lsst.datarel.schema._dbType
Initial value:
1 = {
2  'I': 'INTEGER',
3  'L': 'BIGINT',
4  'F': 'FLOAT',
5  'D': 'DOUBLE',
6  'Angle': 'DOUBLE',
7  'Flag': 'BIT(1)',
8 }

Definition at line 37 of file schema.py.

list lsst.datarel.schema._filterMappings
Initial value:
1 = [
2  ("{filter}_obs_count", "{filter}ObsCount"),
3  ("{filter}_obs_time_min", "{filter}ObsTimeMin"),
4  ("{filter}_obs_time_max", "{filter}ObsTimeMax"),
5  ("{filter}_{measPrefix}{psfFlux}", "{filter}PsfFlux"),
6  ("{filter}_{measPrefix}{psfFlux}_err", "{filter}PsfFluxSigma"),
7  ("{filter}_{measPrefix}{psfFlux}_count", "{filter}PsfFluxCount"),
8  ("{filter}_{measPrefix}{apFlux}", "{filter}ApFlux"),
9  ("{filter}_{measPrefix}{apFlux}_err", "{filter}ApFluxSigma"),
10  ("{filter}_{measPrefix}{apFlux}_count", "{filter}ApFluxCount"),
11  ("{filter}_{measPrefix}{modelFlux}", "{filter}ModelFlux"),
12  ("{filter}_{measPrefix}{modelFlux}_err", "{filter}ModelFluxSigma"),
13  ("{filter}_{measPrefix}{modelFlux}_count", "{filter}ModelFluxCount"),
14  ("{filter}_{measPrefix}{instFlux}", "{filter}InstFlux"),
15  ("{filter}_{measPrefix}{instFlux}_err", "{filter}InstFluxSigma"),
16  ("{filter}_{measPrefix}{instFlux}_count", "{filter}InstFluxCount"),
17  ("{filter}_{measPrefix}{shape}_Ixx", "{filter}ShapeIxx"),
18  ("{filter}_{measPrefix}{shape}_Iyy", "{filter}ShapeIyy"),
19  ("{filter}_{measPrefix}{shape}_Ixy", "{filter}ShapeIxy"),
20  ("{filter}_{measPrefix}{shape}_IxxVar", "{filter}ShapeIxxVar"),
21  ("{filter}_{measPrefix}{shape}_IyyVar", "{filter}ShapeIyyVar"),
22  ("{filter}_{measPrefix}{shape}_IxyVar", "{filter}ShapeIxyVar"),
23  ("{filter}_{measPrefix}{shape}_IxxIyyCov", "{filter}ShapeIxxIyyCov"),
24  ("{filter}_{measPrefix}{shape}_IxxIxyCov", "{filter}ShapeIxxIxyCov"),
25  ("{filter}_{measPrefix}{shape}_IyyIxyCov", "{filter}ShapeIyyIxyCov"),
26  ("{filter}_{measPrefix}{shape}_count", "{filter}ShapeCount"),
27 ]

Definition at line 445 of file schema.py.

list lsst.datarel.schema._forcedSourceMappings

Definition at line 731 of file schema.py.

list lsst.datarel.schema._objectMappings
Initial value:
1 = [
2  ("id", "objectId"),
3  ("coord_ra", "ra"),
4  ("coord_decl", "decl"),
5  ("coord_raVar", "raVar"),
6  ("coord_declVar", "declVar"),
7  ("coord_radeclCov", "radeclCov"),
8  ("coord_htmId20", "htmId20"),
9  ("coord_weightedmean_ra", "wmRa"),
10  ("coord_weightedmean_decl", "wmDecl"),
11  ("coord_weightedmean_raVar", "wmRaVar"),
12  ("coord_weightedmean_declVar", "wmDeclVar"),
13  ("coord_weightedmean_radeclCov", "wmRadeclCov"),
14  ("obs_count", "obsCount"),
15  ("obs_time_min", "obsTimeMin"),
16  ("obs_time_max", "obsTimeMax"),
17  ("obs_time_mean", "obsTimeMean"),
18  ("flag_noise", "flagNoise"),
19 ]

Definition at line 425 of file schema.py.

list lsst.datarel.schema._sourceMappings

Definition at line 255 of file schema.py.

Generated on Tue Apr 21 2015 10:05:07 for LSSTApplications by   doxygen 1.8.5