Compare the structures of two databases.

        Analysis's and compares the column definitions of each table
        in both databases's.  Identifies differences in column names,
        data types and keys.
401
def compare_schemas(self, db_x, db_y, show=True):
        
        # TODO: Improve method
        self._printer("\tComparing database schema's {0} and {1}".format(db_x, db_y))

        # Run compare_dbs_getter to get row counts
        x = self._schema_getter(db_x)
        y = self._schema_getter(db_y)
        x_count = len(x)
        y_count = len(y)

        # Check that database does not have zero tables
        if x_count == 0:
            self._printer('\tThe database {0} has no tables'.format(db_x))
            self._printer('\tDatabase differencing was not run')
            return None
        elif y_count == 0:
            self._printer('\tThe database {0} has no tables'.format(db_y))
            self._printer('\tDatabase differencing was not run')
            return None

        # Print comparisons
        if show:
            uniques_x = diff(x, y, x_only=True)
            if len(uniques_x) > 0:
                self._printer('\nUnique keys from {0} ({1} of {2}):'.format(db_x, len(uniques_x), x_count))
                self._printer('------------------------------')
                # print(uniques)
                for k, v in sorted(uniques_x):
                    self._printer('{0:25} {1}'.format(k, v))
                self._printer('\n')

            uniques_y = diff(x, y, y_only=True)
            if len(uniques_y) > 0:
                self._printer('Unique keys from {0} ({1} of {2}):'.format(db_y, len(uniques_y), y_count))
                self._printer('------------------------------')
                for k, v in sorted(uniques_y):
                    self._printer('{0:25} {1}'.format(k, v))
                self._printer('\n')

            if len(uniques_y) == 0 and len(uniques_y) == 0:
                self._printer("Databases's {0} and {1} are identical:".format(db_x, db_y))
                self._printer('------------------------------')

        return diff(x, y)
763338
def db_tables(name, **connection_args):
    '''
    
    '''
    if not db_exists(name, **connection_args):
        log.info('Database \'%s\' does not exist', name)
        return False

    dbc = _connect(**connection_args)
    if dbc is None:
        return []
    cur = dbc.cursor()
    s_name = quote_identifier(name)
    # identifiers cannot be used as values
    qry = 'SHOW TABLES IN {0}'.format(s_name)
    try:
        _execute(cur, qry)
    except MySQLdb.OperationalError as exc:
        err = 'MySQL Error {0}: {1}'.format(*exc.args)
        __context__['mysql.error'] = err
        log.error(err)
        return []

    ret = []
    results = cur.fetchall()
    for table in results:
        ret.append(table[0])
    log.debug(ret)
    return ret
391250
def schema(ds):
    
    tables, ref_tables = {}, {}
    table_lookup = {t.url.string: t for t in ds.tables if ds.get_tabletype(t)}
    for table in table_lookup.values():
        spec = TableSpec(ds.get_tabletype(table))
        spec.primary_key = [
            c for c in table.tableSchema.columns if
            c.propertyUrl and c.propertyUrl.uri == term_uri('id')][0].name
        # Map the column name to the default:
        if spec.name in PROPERTY_URL_TO_COL:
            spec.primary_key = PROPERTY_URL_TO_COL[spec.name][term_uri('id')]
        for c in table.tableSchema.columns:
            if c.propertyUrl and c.propertyUrl.uri == term_uri('source'):
                # A column referencing sources is replaced by an association table.
                otype = ds.get_tabletype(table).replace('Table', '')
                ref_tables[ds.get_tabletype(table)] = TableSpec(
                    '{0}Source'.format(otype),  # The name of the association table.
                    [ColSpec(otype + '_ID'), ColSpec('Source_ID'), ColSpec('Context')],
                    [
                        (  # The foreign key to the referencing object:
                            ['dataset_ID', otype + '_ID'],
                            ds.get_tabletype(table),
                            ['dataset_ID', spec.primary_key]),
                        (  # The foreign key to the referenced source:
                            ['dataset_ID', 'Source_ID'],
                            'SourceTable',
                            ['dataset_ID', 'ID']),
                    ],
                    c.name)
            else:
                cname = c.header
                if c.propertyUrl and spec.name in PROPERTY_URL_TO_COL:
                    if c.propertyUrl.uri in PROPERTY_URL_TO_COL[spec.name]:
                        cname = PROPERTY_URL_TO_COL[spec.name][c.propertyUrl.uri]
                spec.columns.append(ColSpec(
                    cname,
                    c.datatype.base if c.datatype else c.datatype,
                    c.separator,
                    cname == spec.primary_key,
                    cldf_name=c.header))
        for fk in table.tableSchema.foreignKeys:
            if fk.reference.schemaReference:
                # We only support Foreign Key references between tables!
                continue  # pragma: no cover
            ref = table_lookup[fk.reference.resource.string]
            ref_type = ds.get_tabletype(ref)
            if ref_type:
                colRefs = sorted(fk.columnReference)
                if spec.name in PROPERTY_URL_TO_COL:
                    # Must map foreign keys
                    colRefs = []
                    for c in sorted(fk.columnReference):
                        c = ds[spec.name, c]
                        if c.propertyUrl and c.propertyUrl.uri in PROPERTY_URL_TO_COL[spec.name]:
                            colRefs.append(PROPERTY_URL_TO_COL[spec.name][c.propertyUrl.uri])
                        else:
                            colRefs.append(c.header)
                rcolRefs = sorted(fk.reference.columnReference)
                if ref_type in PROPERTY_URL_TO_COL:
                    # Must map foreign key targets!
                    rcolRefs = []
                    for c in sorted(fk.reference.columnReference):
                        c = ds[ref_type, c]
                        if c.propertyUrl and c.propertyUrl.uri in PROPERTY_URL_TO_COL[ref_type]:
                            rcolRefs.append(PROPERTY_URL_TO_COL[ref_type][c.propertyUrl.uri])
                        else:
                            rcolRefs.append(c.header)
                spec.foreign_keys.append((
                    tuple(['dataset_ID'] + colRefs),
                    ds.get_tabletype(table_lookup[fk.reference.resource.string]),
                    tuple(['dataset_ID'] + rcolRefs)))
        tables[spec.name] = spec

    # must determine the order in which tables must be created!
    ordered = OrderedDict()
    i = 0
    #
    # We loop through the tables repeatedly, and whenever we find one, which has all
    # referenced tables already in ordered, we move it from tables to ordered.
    #
    while tables and i < 100:
        i += 1
        for table in list(tables.keys()):
            if all(ref[1] in ordered for ref in tables[table].foreign_keys):
                # All referenced tables are already created.
                ordered[table] = tables.pop(table)
                break
    if tables:  # pragma: no cover
        raise ValueError('there seem to be cyclic dependencies between the tables')

    return list(ordered.values()), ref_tables
313893
def extract_db_info(self, obj, keys):
        """"""
        result = {}
        if isinstance(obj, dict):
            try:
                qc = obj['quality_control']
            except KeyError:
                qc = QC.UNKNOWN
        elif isinstance(obj, DataFrame):
            with obj.open() as hdulist:
                qc = self.datamodel.get_quality_control(hdulist)
        else:
            qc = QC.UNKNOWN

        result['quality_control'] = qc
        other = super(DataProductMixin, self).extract_db_info(obj, keys)
        result.update(other)
        return result
346643
def calc_changes(db, ignore_tables=None):
  migrator = None # expose eventually?
  if migrator is None:
    migrator = auto_detect_migrator(db)

  existing_tables = [unicode(t) for t in db.get_tables()]
  existing_indexes = {table:get_indexes_by_table(db, table) for table in existing_tables}
  existing_columns_by_table = get_columns_by_table(db)
  foreign_keys_by_table = get_foreign_keys_by_table(db)

  table_names_to_models = {_table_name(cls): cls for cls in all_models.keys()}

  to_run = []

  table_adds, add_fks, table_deletes, table_renames = calc_table_changes(existing_tables, ignore_tables=ignore_tables)
  table_renamed_from = {v: k for k, v in table_renames.items()}
  for tbl in table_adds:
    to_run += create_table(table_names_to_models[tbl])
  for field in add_fks:
    if hasattr(field, '__pwdbev__not_deferred') and field.__pwdbev__not_deferred:
      field.deferred = False
    to_run += create_foreign_key(field)
  for k, v in table_renames.items():
    to_run += rename_table(migrator, k, v)


  rename_cols_by_table = {}
  deleted_cols_by_table = {}
  for etn, ecols in existing_columns_by_table.items():
    if etn in table_deletes: continue
    ntn = table_renames.get(etn, etn)
    model = table_names_to_models.get(ntn)
    if not model: continue
    defined_fields = model._meta.sorted_fields
    defined_column_name_to_field = {unicode(_column_name(f)):f for f in defined_fields}
    existing_fks_by_column = {fk.column:fk for fk in foreign_keys_by_table[etn]}
    adds, deletes, renames, alter_statements = calc_column_changes(db, migrator, etn, ntn, ecols, defined_fields, existing_fks_by_column)
    for column_name in adds:
      field = defined_column_name_to_field[column_name]
      to_run += alter_add_column(db, migrator, ntn, column_name, field)
      if not field.null:
        # alter_add_column strips null constraints
        # add them back after setting any defaults
        if field.default is not None:
          to_run += set_default(db, migrator, ntn, column_name, field)
        else:
          to_run.append(('-- adding a not null column without a default will fail if the table is not empty',[]))
        to_run += add_not_null(db, migrator, ntn, column_name, field)

    for column_name in deletes:
      fk = existing_fks_by_column.get(column_name)
      if fk:
        to_run += drop_foreign_key(db, migrator, ntn, fk.name)
      to_run += drop_column(db, migrator, ntn, column_name)
    for ocn, ncn in renames.items():
      field = defined_column_name_to_field[ncn]
      to_run += rename_column(db, migrator, ntn, ocn, ncn, field)
    to_run += alter_statements
    rename_cols_by_table[ntn] = renames
    deleted_cols_by_table[ntn] = deletes

  for ntn, model in table_names_to_models.items():
    etn = table_renamed_from.get(ntn, ntn)
    deletes = deleted_cols_by_table.get(ntn,set())
    existing_indexes_for_table = [i for i in existing_indexes.get(etn, []) if not any([(c in deletes) for c in i.columns])]
    to_run += calc_index_changes(db, migrator, existing_indexes_for_table, model, rename_cols_by_table.get(ntn, {}))

  '''
  
  '''
  for tbl in table_deletes:
    to_run += drop_table(migrator, tbl)
  return to_run
763621
def compare_dict(da, db):
    
    sa = set(da.items())
    sb = set(db.items())
    
    diff = sa & sb
    return dict(sa - diff), dict(sb - diff)
1049659
private void createTables(Connection conn) throws DatabaseException {
        LOGGER.debug("Creating database structure");

        try (InputStream is = FileUtils.getResourceAsStream(DB_STRUCTURE_RESOURCE)) {
            final String dbStructure = IOUtils.toString(is, StandardCharsets.UTF_8);

            Statement statement = null;
            try {
                statement = conn.createStatement();
                statement.execute(dbStructure);
            } catch (SQLException ex) {
                LOGGER.debug("", ex);
                throw new DatabaseException("Unable to create database statement", ex);
            } finally {
                DBUtils.closeStatement(statement);
            }
        } catch (IOException ex) {
            throw new DatabaseException("Unable to create database schema", ex);
        }
    }
292117
def db_schema(db, table):
  
  typedict = [('int', int), ('double', float),
              ('timestamp', datetime),
              ('date', date), ('time', dttime),
              ('text', str), ('char', str)]
  ret = {}
  q = '''select column_name, data_type
          from information_schema.columns
          where table_name = %s;'''
  #q = """select c.name, c.type from sys.columns as c, sys.tables as t where table_id = t.id and t.name = %s;"""
  # and data_type != 'date' and position('time' in data_type) =
  # 0 and column_name != 'humidity'
  for row in query(db, q, (table,)):
      name, dtype = tuple( row[:2] )
      name = str(name)
      for tn, tt in typedict:
          if tn in dtype:
              ret[name] = tt
              break
      if name not in ret:
          msg = "can't find type of %s\t%s"
          raise RuntimeError(msg % (name, dtype))
  return ret
439399
void compareTypes(Schema oldSchema,
                                           Schema newSchema,
                                           List<Message> messages,
                                           String name) {
        oldSchema = stripOptionalTypeUnion(oldSchema);
        newSchema = stripOptionalTypeUnion(newSchema);
        Schema.Type oldType = oldSchema.getType();
        Schema.Type newType = newSchema.getType();

        if(oldType != Type.UNION && newType == Type.UNION) {
            boolean compatibleTypeFound = false;
            for(Schema s: newSchema.getTypes()) {
                if((oldType != Type.RECORD && oldType == s.getType())
                   || (oldType == Type.RECORD && s.getType() == Type.RECORD && oldSchema.getName()
                                                                                        .equals(s.getName()))) {
                    compareTypes(oldSchema, s, messages, name);
                    compatibleTypeFound = true;
                    break;
                }
            }

            if(compatibleTypeFound) {
                messages.add(new Message(Level.INFO,
                                         "Type change from " + oldType + " to " + newType
                                                 + " for field " + name
                                                 + ". The new union includes the original type."));
            } else {
                messages.add(new Message(Level.ERROR,
                                         "Incompatible type change from "
                                                 + oldType
                                                 + " to "
                                                 + newType
                                                 + " for field "
                                                 + name
                                                 + ". The new union does not include the original type."));
            }
        } else if(oldType == Type.RECORD) {
            if(!_recordStack.contains(oldSchema.getName())) {
                _recordStack.add(oldSchema.getName());
                compareRecordTypes(oldSchema, newSchema, messages, name);
                _recordStack.remove(oldSchema.getName());
            }
        } else if(oldType == Type.ENUM) {
            compareEnumTypes(oldSchema, newSchema, messages, name);
        } else if(oldType == Type.ARRAY) {
            compareArrayTypes(oldSchema, newSchema, messages, name);
        } else if(oldType == Type.MAP) {
            compareMapTypes(oldSchema, newSchema, messages, name);
        } else if(oldType == Type.UNION) {
            compareUnionTypes(oldSchema, newSchema, messages, name);
        } else if(oldType == Type.FIXED) {
            compareFixedTypes(oldSchema, newSchema, messages, name);
        } else {
            comparePrimitiveTypes(oldSchema, newSchema, messages, name);
        }
    }
785719
def analyze(self):
        
        key = '/library/sections/%s/analyze' % self.key
        self._server.query(key, method=self._server._session.put)
501520
private static function _loadDatabaseDefinition()
	{
		$db = self::getAdapter();
	
		//Get table columns
		$dbData = array();
		$tables = $db->query("SHOW TABLES")->execute();
		foreach($tables as $tableListRow)
		{
			$table = current($tableListRow);
			$columns = $db->query("DESCRIBE {$table}")->execute();
			$columnData = array();
			foreach($columns as $columnListRow)
			{
				$columnData[$columnListRow['Field']] = $columnListRow['Type'];
			}
			$dbData['tables'][$table]['columns'] = $columnData;
		}
		
		//Get relationships
		$schema = $db->getDriver()->getConnection()->getCurrentSchema();
		$relations = $db->query("
			SELECT table_name, column_name, referenced_table_name, referenced_column_name
			FROM information_schema.key_column_usage
			WHERE table_schema='{$schema}' AND referenced_table_schema IS NOT NULL;
		")->execute();
		$relationData = array();
		foreach($relations as $relation)
		{
			$table = $relation['table_name'];
			$column = $relation['column_name'];
			$otherTable = $relation['referenced_table_name'];
			
			$dbData['tables'][$table]['owners'][$column] = $otherTable;
			$dbData['tables'][$otherTable]['children'][$table][] = $column;

			$table = $relation['table_name'];
			unset($relation['table_name']);
			$relationData[$table][$relation['column_name']] = $relation['referenced_table_name'];
			
		}
		$dbData['relations'] = $relationData;
		
		self::$_databaseDef = $dbData;
	}
782619
function compareObjects(schema, version1, version2) {

      var changes = [];

      _.each(version1, function(val, key) {
        var change;
        var field = getField(schema, key, val);
        if (!field) {
          // Current schema can't describe this field
          return;
        }
        if (!_.has(version2, key)) {
          change = { action: 'remove', key: key, field: field, old: version1[key] };
        } else if (!_.isEqual(version2[key], version1[key])) {
          change = { action: 'change', key: key, field: field, old: version1[key], current: version2[key] };
        }
        if (change) {
          change.changes = compareField(field, change.old, change.current);
          changes.push(change);
        }
      });

      _.each(version2, function(val, key) {
        var change;
        var field = getField(schema, key, val);
        if (!field) {
          // Current schema can't describe this field
          return;
        }
        if (!_.has(version1, key)) {
          change = { action: 'add', key: key, field: field, current: val };
          change.changes = compareField(field, change.old, change.current);
          changes.push(change);
        }
      });

      return changes;

      function compareField(field, old, current) {

        if (current && (!old)) {
          old = synth(field.type, current);
        } else if (old && (!current)) {
          current = synth(field.type, old);
        }

        if (field.type === 'array') {

          return compareArrays(schemaIdentifier, schemaDecoratorGenerator(field.schema), old, current);

        } else if ((field.type === 'area') || (field.type === 'singleton')) {

          return compareAreas(old, current);

        } else if (field.type === 'joinByArray') {

          return compareArrays(joinByArrayIdentifier, joinByArrayDecoratorGenerator(field.withType), old, current);

        } else if (field.type === 'joinByOne') {

          return compareArrays(joinByArrayIdentifier, joinByArrayDecoratorGenerator(field.withType), old ? [ old ] : [], current ? [ current ] : []);

        } else {

          // Take advantage of Apostrophe's support for boiling fields
          // down to search text to generate a basis for a text diff.
          // If a special "diffable" function is available use it;
          // that allows us to compare things that don't make
          // good search text but are human readable, like URLs. -Tom

          var oldLines = [];
          var oldText = '';
          var currentLines = [];
          var currentText = '';
          var fieldType = self.apos.schemas.getFieldType(field.type);
          var diffable = fieldType.diffable;
          if (diffable) {
            oldText = diffable(old);
            currentText = diffable(current);
          } else {
            var indexer = fieldType.index;
            if (indexer) {
              indexer(old, field, oldLines);
              indexer(current, field, currentLines);
              oldText = _.filter(_.pluck(oldLines, 'text'), function(line) {
                return line !== undefined;
              }).join("\n");
              currentText = _.filter(_.pluck(currentLines, 'text'), function(line) {
                return line !== undefined;
              }).join("\n");
            }
          }

          var diff = [];
          if (oldText !== undefined && currentText !== undefined) {
            diff = jsDiff.diffSentences(oldText, currentText, { ignoreWhitespace: true });
          }

          var changes = _.map(_.filter(diff, function(diffChange) {
            return diffChange.added || diffChange.removed;
          }), function(diffChange) {
            // Convert a jsDiff change object to an
            // apos versions change object
            if (diffChange.removed) {
              return {
                action: 'remove',
                text: diffChange.value,
                field: {
                  type: 'string',
                  label: 'Content'
                }
              };
            } else {
              return {
                action: 'add',
                text: diffChange.value,
                field: {
                  type: 'string',
                  label: 'Content'
                }
              };
            }
          });
          return changes;
        }

        // If "old" or "current" doesn't exist, make sure
        // we compare the other one to a reasonable value.
        // This is currently only an issue for areas, arrays and objects.
        // TODO: should probably be something we ask schemas to
        // generate for us so it's extensible. -Tom

        function synth(type, existing) {
          var value;
          if (existing && (existing.type === 'area')) {
            value = { type: 'area', items: [] };
          } else if (Array.isArray(existing)) {
            value = [];
          } else if (typeof (existing) === 'object') {
            value = {};
          }
          return value;
        }

      }
    }
939407
def show_tables():
    
    _State.connection()
    _State.reflect_metadata()
    metadata = _State.metadata

    response = select('name, sql from sqlite_master where type="table"')

    return {row['name']: row['sql'] for row in response}
542995
def get_database_rows(self, tables=None, database=None):
        """"""
        # Get table data and columns from source database
        source = database if database else self.database
        tables = tables if tables else self.tables

        # Get database select queries
        commands = self._get_select_commands(source, tables)

        # Execute select commands
        return self._execute_select_commands(source, commands)
1199062
def _analyze(self):
        
        if self.value is None or self.value == self.previous:
            pass

        elif self._operation == "append":
            self._append = self.value

        elif self._operation == "prepend":
            self._prepend = self.value

        elif self.previous is None:
            self._assignments = self.value

        elif len(self.value) < len(self.previous):
            # if elements have been removed,
            # rewrite the whole list
            self._assignments = self.value

        elif len(self.previous) == 0:
            # if we're updating from an empty
            # list, do a complete insert
            self._assignments = self.value
        else:

            # the max start idx we want to compare
            search_space = len(self.value) - max(0, len(self.previous) - 1)

            # the size of the sub lists we want to look at
            search_size = len(self.previous)

            for i in range(search_space):
                # slice boundary
                j = i + search_size
                sub = self.value[i:j]
                idx_cmp = lambda idx: self.previous[idx] == sub[idx]
                if idx_cmp(0) and idx_cmp(-1) and self.previous == sub:
                    self._prepend = self.value[:i] or None
                    self._append = self.value[j:] or None
                    break

            # if both append and prepend are still None after looking
            # at both lists, an insert statement will be created
            if self._prepend is self._append is None:
                self._assignments = self.value

        self._analyzed = True
307199
def diff_foreign_key(self, key1, key2):
        
        key1_unquoted_local_columns = [
            c.lower() for c in key1.get_unquoted_local_columns()
        ]
        key2_unquoted_local_columns = [
            c.lower() for c in key2.get_unquoted_local_columns()
        ]

        if key1_unquoted_local_columns != key2_unquoted_local_columns:
            return True

        key1_unquoted_foreign_columns = [
            c.lower() for c in key1.get_unquoted_foreign_columns()
        ]
        key2_unquoted_foreign_columns = [
            c.lower() for c in key2.get_unquoted_foreign_columns()
        ]

        if key1_unquoted_foreign_columns != key2_unquoted_foreign_columns:
            return True

        if (
            key1.get_unqualified_foreign_table_name()
            != key2.get_unqualified_foreign_table_name()
        ):
            return True

        if key1.on_update() != key2.on_update():
            return True

        if key1.on_delete() != key2.on_delete():
            return True

        return False
1141925
def get_column_info(connection, table_name):
	
	cursor = connection.cursor()
	cursor.execute("SELECT sql FROM sqlite_master WHERE type == 'table' AND name == ?", (table_name,))
	statement, = cursor.fetchone()
	coldefs = re.match(_sql_create_table_pattern, statement).groupdict()["coldefs"]
	return [(coldef.groupdict()["name"], coldef.groupdict()["type"]) for coldef in re.finditer(_sql_coldef_pattern, coldefs) if coldef.groupdict()["name"].upper() not in ("PRIMARY", "UNIQUE", "CHECK")]