Need to consider foreign tables for PostgreSQL
489207
def get_table_names(cls, inspector, schema):
        """"""
        tables = inspector.get_table_names(schema)
        tables.extend(inspector.get_foreign_table_names(schema))
        return sorted(tables)
184186
def _connect(dbapi_connection, connection_record):
    """"""

    # If back end is sqlite
    if type(dbapi_connection) is sqlite3.Connection:

        # Respect foreign key constraints by default
        cursor = dbapi_connection.cursor()
        cursor.execute("PRAGMA foreign_keys=ON")
        cursor.close()
1077222
def get_post_alter_table_index_foreign_key_sql(self, diff):
        
        if not isinstance(diff.from_table, Table):
            raise DBALException(
                "Sqlite platform requires for alter table the table"
                "diff with reference to original table schema"
            )

        sql = []

        if diff.new_name:
            table_name = diff.get_new_name()
        else:
            table_name = diff.get_name(self)

        for index in self._get_indexes_in_altered_table(diff).values():
            if index.is_primary():
                continue

            sql.append(
                self.get_create_index_sql(index, table_name.get_quoted_name(self))
            )

        return sql
521365
def declare(full_table_name, definition, context):
    

    table_name = full_table_name.strip('`').split('.')[1]
    if len(table_name) > MAX_TABLE_NAME_LENGTH:
        raise DataJointError(
            'Table name `{name}` exceeds the max length of {max_length}'.format(
                name=table_name,
                max_length=MAX_TABLE_NAME_LENGTH))
    # split definition into lines
    definition = re.split(r'\s*\n\s*', definition.strip())
    # check for optional table comment
    table_comment = definition.pop(0)[1:].strip() if definition[0].startswith('#') else ''
    in_key = True  # parse primary keys
    primary_key = []
    attributes = []
    attribute_sql = []
    foreign_key_sql = []
    index_sql = []
    uses_external = False

    for line in definition:
        if line.startswith('#'):  # additional comments are ignored
            pass
        elif line.startswith('---') or line.startswith('___'):
            in_key = False  # start parsing dependent attributes
        elif is_foreign_key(line):
            compile_foreign_key(line, context, attributes,
                                primary_key if in_key else None,
                                attribute_sql, foreign_key_sql, index_sql)
        elif re.match(r'^(unique\s+)?index[^:]*$', line, re.I):   # index
            compile_index(line, index_sql)
        else:
            name, sql, is_external = compile_attribute(line, in_key, foreign_key_sql)
            uses_external = uses_external or is_external
            if in_key and name not in primary_key:
                primary_key.append(name)
            if name not in attributes:
                attributes.append(name)
                attribute_sql.append(sql)
    # compile SQL
    if not primary_key:
        raise DataJointError('Table must have a primary key')

    return (
        'CREATE TABLE IF NOT EXISTS %s (\n' % full_table_name +
        ',\n'.join(attribute_sql + ['PRIMARY KEY (`' + '`,`'.join(primary_key) + '`)'] + foreign_key_sql + index_sql) +
        '\n) ENGINE=InnoDB, COMMENT "%s"' % table_comment), uses_external
364974
def pre_sql_setup(self):
        
        super(MultilingualSQLCompiler, self).pre_sql_setup()

        if not self.query.include_translation_data:
            return

        opts = self.query.model._meta
        qn = self.quote_name_unless_alias
        qn2 = self.connection.ops.quote_name

        if hasattr(opts, 'translation_model'):
            master_table_name = self.query.join((None, opts.db_table, None, None))
            translation_opts = opts.translation_model._meta
            trans_table_name = translation_opts.db_table
            for language_code in get_language_code_list():
                table_alias = get_translation_table_alias(trans_table_name,
                                                          language_code)
                trans_join = ("LEFT JOIN %s AS %s ON ((%s.master_id = %s.%s) AND (%s.language_code = '%s'))"
                           % (qn2(translation_opts.db_table),
                           qn2(table_alias),
                           qn2(table_alias),
                           qn(master_table_name),
                           qn2(opts.pk.column),
                           qn2(table_alias),
                           language_code))
                self.query.extra_join[table_alias] = trans_join
1045795
private static <T, ID> void addCreateTableStatements(DatabaseType databaseType, TableInfo<T, ID> tableInfo,
			List<String> statements, List<String> queriesAfter, boolean ifNotExists, boolean logDetails)
			throws SQLException {
		StringBuilder sb = new StringBuilder(256);
		if (logDetails) {
			logger.info("creating table '{}'", tableInfo.getTableName());
		}
		sb.append("CREATE TABLE ");
		if (ifNotExists && databaseType.isCreateIfNotExistsSupported()) {
			sb.append("IF NOT EXISTS ");
		}
		databaseType.appendEscapedEntityName(sb, tableInfo.getTableName());
		sb.append(" (");
		List<String> additionalArgs = new ArrayList<String>();
		List<String> statementsBefore = new ArrayList<String>();
		List<String> statementsAfter = new ArrayList<String>();
		// our statement will be set here later
		boolean first = true;
		for (FieldType fieldType : tableInfo.getFieldTypes()) {
			// skip foreign collections
			if (fieldType.isForeignCollection()) {
				continue;
			} else if (first) {
				first = false;
			} else {
				sb.append(", ");
			}
			String columnDefinition = fieldType.getColumnDefinition();
			if (columnDefinition == null) {
				// we have to call back to the database type for the specific create syntax
				databaseType.appendColumnArg(tableInfo.getTableName(), sb, fieldType, additionalArgs, statementsBefore,
						statementsAfter, queriesAfter);
			} else {
				// hand defined field
				databaseType.appendEscapedEntityName(sb, fieldType.getColumnName());
				sb.append(' ').append(columnDefinition).append(' ');
			}
		}
		// add any sql that sets any primary key fields
		databaseType.addPrimaryKeySql(tableInfo.getFieldTypes(), additionalArgs, statementsBefore, statementsAfter,
				queriesAfter);
		// add any sql that sets any unique fields
		databaseType.addUniqueComboSql(tableInfo.getFieldTypes(), additionalArgs, statementsBefore, statementsAfter,
				queriesAfter);
		for (String arg : additionalArgs) {
			// we will have spat out one argument already so we don't have to do the first dance
			sb.append(", ").append(arg);
		}
		sb.append(") ");
		databaseType.appendCreateTableSuffix(sb);
		statements.addAll(statementsBefore);
		statements.add(sb.toString());
		statements.addAll(statementsAfter);
		addCreateIndexStatements(databaseType, tableInfo, statements, ifNotExists, false, logDetails);
		addCreateIndexStatements(databaseType, tableInfo, statements, ifNotExists, true, logDetails);
	}
1215207
private void createForeignKeys(Set<ForeignKey> allForeignKeys, Map<Column, ColumnDescriptor> allColumns, Store store) {
        // Foreign keys
        for (ForeignKey foreignKey : allForeignKeys) {
            ForeignKeyDescriptor foreignKeyDescriptor = store.create(ForeignKeyDescriptor.class);
            foreignKeyDescriptor.setName(foreignKey.getName());
            foreignKeyDescriptor.setDeferrability(foreignKey.getDeferrability().name());
            foreignKeyDescriptor.setDeleteRule(foreignKey.getDeleteRule().name());
            foreignKeyDescriptor.setUpdateRule(foreignKey.getUpdateRule().name());
            for (ForeignKeyColumnReference columnReference : foreignKey.getColumnReferences()) {
                ForeignKeyReferenceDescriptor keyReferenceDescriptor = store.create(ForeignKeyReferenceDescriptor.class);
                // foreign key table and column
                Column foreignKeyColumn = columnReference.getForeignKeyColumn();
                ColumnDescriptor foreignKeyColumnDescriptor = allColumns.get(foreignKeyColumn);
                keyReferenceDescriptor.setForeignKeyColumn(foreignKeyColumnDescriptor);
                // primary key table and column
                Column primaryKeyColumn = columnReference.getPrimaryKeyColumn();
                ColumnDescriptor primaryKeyColumnDescriptor = allColumns.get(primaryKeyColumn);
                keyReferenceDescriptor.setPrimaryKeyColumn(primaryKeyColumnDescriptor);
                foreignKeyDescriptor.getForeignKeyReferences().add(keyReferenceDescriptor);
            }
        }
    }
200773
def add_pk_if_required(db, table, name):
    
    db.metadata.reflect(bind=db.engine)
    cls_dict = {'__tablename__': name}
    if not table.primary_key:
        for column in table.columns:
            column.primary_key = True
        Table(name, db.metadata, *table.columns, extend_existing=True)
        cls_dict['__table__'] = table
        db.metadata.create_all(bind=db.engine)

    return type(str(name), (sandman_model, db.Model), cls_dict)
1286269
function(r) {
					var rr = {};
					if (typeof r[col.columnid] === 'undefined') {
						return true;
					}
					rr[fk.columnid] = r[col.columnid];
					var addr = fktable.pk.onrightfn(rr);
					//						console.log(r, rr, addr);
					//						console.log(fktable.uniqs[fktable.pk.hh][addr]);
					if (!fktable.uniqs[fktable.pk.hh][addr]) {
						throw new Error(
							'Foreign key "' +
								r[col.columnid] +
								'" is not found in table ' +
								fktable.tableid
						);
					}
					return true;
				}
1131070
def select_join(self, table, field, join_table, join_field):
        
        sql = "select {table}.{field} from {table} left join {join_table} "\
              "on {table}.{field}={join_table}.{join_field} "\
              "where {join_table}.{join_field} is null;".format(table=table,
                                                                field=field,
                                                                join_table=join_table,
                                                                join_field=join_field)
        return super(PGWrapper, self).execute(sql, result=True).results
877557
def _get_fields(self, table_name, **kwargs):
        """"""
        ret = {}
        query_str = []
        query_args = ['f', table_name]

        # I had to brush up on my join knowledge while writing this query
        # https://en.wikipedia.org/wiki/Join_(SQL)
        #
        # other helpful links
        # https://wiki.postgresql.org/wiki/Retrieve_primary_key_columns
        # https://www.postgresql.org/docs/9.4/static/catalog-pg-attribute.html
        # https://www.postgresql.org/docs/9.3/static/catalog-pg-type.html
        # 
        # another approach
        # http://dba.stackexchange.com/questions/22362/how-do-i-list-all-columns-for-a-specified-table
        # http://gis.stackexchange.com/questions/94049/how-to-get-the-data-type-of-each-column-from-a-postgis-table
        query_str.append('SELECT')
        query_str.append(',  '.join([
            'a.attnum',
            'a.attname',
            'a.attnotnull',
            't.typname',
            'i.indisprimary',
            #'s.conname',
            #'pg_get_constraintdef(s.oid, true) as condef',
            'c.relname AS confrelname',
        ]))
        query_str.append('FROM')
        query_str.append('  pg_attribute a')
        query_str.append('JOIN pg_type t ON a.atttypid = t.oid')
        query_str.append('LEFT JOIN pg_index i ON a.attrelid = i.indrelid')
        query_str.append('  AND a.attnum = any(i.indkey)')
        query_str.append('LEFT JOIN pg_constraint s ON a.attrelid = s.conrelid')
        query_str.append('  AND s.contype = {} AND a.attnum = any(s.conkey)'.format(self.val_placeholder))
        query_str.append('LEFT JOIN pg_class c ON s.confrelid = c.oid')
        query_str.append('WHERE')
        query_str.append('  a.attrelid = {}::regclass'.format(self.val_placeholder))
        query_str.append('  AND a.attisdropped = False')
        query_str.append('  AND a.attnum > 0')
        query_str.append('ORDER BY a.attnum ASC')

        query_str = os.linesep.join(query_str)
        fields = self.query(query_str, *query_args, **kwargs)

        pg_types = {
            "float8": float,
            "timestamp": datetime.datetime,
            "int2": int,
            "int4": int,
            "int8": long,
            "numeric": decimal.Decimal,
            "text": str,
            "bpchar": str,
            "varchar": str,
            "bool": bool,
            "date": datetime.date,
            "blob": bytearray,
        }

        # the rows we can set: field_type, name, field_required, min_size, max_size,
        #   size, unique, pk, <foreign key info>
        # These keys will roughly correspond with schema.Field
        for row in fields:
            field = {
                "name": row["attname"],
                "field_type": pg_types[row["typname"]],
                "field_required": row["attnotnull"],
                "pk": bool(row["indisprimary"]),
            }

            if row["confrelname"]:
                # TODO -- I can't decide which name I like
                field["schema_table_name"] = row["confrelname"]
                field["ref_table_name"] = row["confrelname"]

            ret[field["name"]] = field

        return ret
338243
def construct_has_many_or_belongs_to_attributes(reflection, association_class, table_ids)
      # Determine the alias used for remote_table_name, if any. In all cases this will already
      # have been assigned an ID in table_ids (either through being involved in a previous join,
      # or - if it's the first table in the query - as the default value of table_ids)
      remote_table_alias = remote_table_name = association_class.table_name
      remote_table_alias += "_#{table_ids[remote_table_name]}" unless table_ids[remote_table_name] == 1

      # Assign a new alias for the local table.
      local_table_alias = local_table_name = reflection.active_record.table_name
      if table_ids[local_table_name]
        table_id = table_ids[local_table_name] += 1
        local_table_alias += "_#{table_id}"
      else
        table_ids[local_table_name] = 1
      end

      conditions = ''
      # Add type_condition, if applicable
      conditions += " AND #{association_class.send(:type_condition).to_sql}" if association_class.finder_needs_type_condition?
      # Add custom conditions
      conditions += " AND (#{association_class.send(:sanitize_sql, reflection.options[:conditions])})" if reflection.options[:conditions]

      if reflection.macro == :belongs_to
        if reflection.options[:polymorphic]
          conditions += " AND #{local_table_alias}.#{reflection.options[:foreign_type]} = #{reflection.active_record.quote_value(association_class.base_class.name.to_s)}"
        end
        {
          :joins => reflection.options[:joins],
          :remote_key => "#{remote_table_alias}.#{association_class.primary_key}",
          :local_key => reflection.primary_key_name,
          :conditions => conditions
        }
      else
        # Association is has_many (without :through)
        if reflection.options[:as]
          conditions += " AND #{remote_table_alias}.#{reflection.options[:as]}_type = #{reflection.active_record.quote_value(reflection.active_record.base_class.name.to_s)}"
        end
        {
          :joins => "#{reflection.options[:joins]}",
          :remote_key => "#{remote_table_alias}.#{reflection.primary_key_name}",
          :local_key => reflection.klass.primary_key,
          :conditions => conditions
        }
      end
    end
1130266
def compile_foreign(self, blueprint, command, _):
        
        table = self.wrap_table(blueprint)

        on = self.wrap_table(command.on)

        columns = self.columnize(command.columns)

        on_columns = self.columnize(command.references
                                    if isinstance(command.references, list)
                                    else [command.references])

        sql = 'ALTER TABLE %s ADD CONSTRAINT %s ' % (table, command.index)

        sql += 'FOREIGN KEY (%s) REFERENCES %s (%s)' % (columns, on, on_columns)

        if command.get('on_delete'):
            sql += ' ON DELETE %s' % command.on_delete

        if command.get('on_update'):
            sql += ' ON UPDATE %s' % command.on_update

        return sql
1036170
protected final function hasMany($tableCls, $foreign_key = '', $value_key = '') {
        if (!$tableCls instanceof View) {
            $tableCls = new SimpleTable($tableCls, $this->dbconnection);
        }
        if (!$foreign_key) {
            $foreign_key = $this->foreignKey;
        }

        if (!$value_key) {
            $value_key = $this->primaryKey;
        }

        $sql = $tableCls->select();

        return [$sql, $foreign_key, $value_key, false, 'hasMany'];
    }
1009495
def import_tables(self, only_tables=None, exclude_tables=None):
        
        for table in self.tables:
            if only_tables is not None and table.name not in only_tables:
                continue

            if exclude_tables is not None and table.name in exclude_tables:
                continue
            self.import_table(table)
519032
function createDatabase_other(tx) {
    var schema = "clicks_other (id integer primary key, other text)"
    var sql = "create table if not exists " + schema

    tx.executeSql(sql, null, null, sqlError);
}
518698
public void addForeignkey(String relationName, String remoteTable, List localColumns, List remoteColumns)
    {
        ForeignkeyDef foreignkeyDef = new ForeignkeyDef(relationName, remoteTable);

        // the field arrays have the same length if we already checked the constraints
        for (int idx = 0; idx < localColumns.size(); idx++)
        {
            foreignkeyDef.addColumnPair((String)localColumns.get(idx),
                                        (String)remoteColumns.get(idx));
        }

        // we got to determine whether this foreignkey is already present 
        ForeignkeyDef def = null;

        for (Iterator it = getForeignkeys(); it.hasNext();)
        {
            def = (ForeignkeyDef)it.next();
            if (foreignkeyDef.equals(def))
            {
                return;
            }
        }
        foreignkeyDef.setOwner(this);
        _foreignkeys.add(foreignkeyDef);
    }