示例#1
0
func (t *Table) SQL(db *DB, b Backend, m driver.Model, name string) (string, error) {
	var lines []string
	var constraints []string
	for _, v := range t.Fields {
		def, cons, err := v.SQL(db, m, t)
		if err != nil {
			return "", err
		}
		lines = append(lines, def)
		constraints = append(constraints, cons...)
	}
	pk, err := t.definePks(db, m)
	if err != nil {
		return "", err
	}
	if pk != "" {
		lines = append(lines, pk)
	}
	lines = append(lines, constraints...)
	if name == "" {
		name = m.Table()
	}
	// Use IF NOT EXISTS, since the DB user might not have
	// the privileges to inspect the database but still
	// be allowed to read and write from the tables (e.g.
	// Postgres only allows superusers and the owner to
	// inspect the database).
	sql := fmt.Sprintf("\nCREATE TABLE IF NOT EXISTS %s (\n\t%s\n)", db.QuoteIdentifier(name), strings.Join(lines, ",\n\t"))
	return sql, nil
}
示例#2
0
func (d *Driver) Update(m driver.Model, q query.Q, data interface{}) (driver.Result, error) {
	_, fields, values, err := d.saveParameters(m, data)
	if err != nil {
		return nil, err
	}
	buf := getBuffer()
	buf.WriteString("UPDATE ")
	buf.WriteByte('"')
	buf.WriteString(m.Table())
	buf.WriteByte('"')
	buf.WriteString(" SET ")
	for ii, v := range fields {
		buf.WriteByte('"')
		buf.WriteString(v)
		buf.WriteByte('"')
		buf.WriteByte('=')
		buf.WriteString(d.backend.Placeholder(ii))
		buf.WriteByte(',')
	}
	// remove last ,
	buf.Truncate(buf.Len() - 1)
	qParams, err := d.where(buf, m, q, len(values))
	if err != nil {
		return nil, err
	}
	params := append(values, qParams...)
	res, err := d.db.Exec(buftos(buf), params...)
	putBuffer(buf)
	return res, err
}
示例#3
0
func (d *Driver) Insert(m driver.Model, data interface{}) (driver.Result, error) {
	_, fields, values, err := d.saveParameters(m, data)
	if err != nil {
		return nil, err
	}
	buf := getBuffer()
	buf.WriteString("INSERT INTO ")
	buf.WriteByte('"')
	buf.WriteString(m.Table())
	buf.WriteByte('"')
	count := len(fields)
	if count > 0 {
		buf.WriteString(" (")
		for _, v := range fields {
			buf.WriteByte('"')
			buf.WriteString(v)
			buf.WriteByte('"')
			buf.WriteByte(',')
		}
		buf.Truncate(buf.Len() - 1)
		buf.WriteString(") VALUES (")
		buf.WriteString(d.backend.Placeholders(count))
		buf.WriteByte(')')
	} else {
		buf.WriteByte(' ')
		buf.WriteString(d.backend.DefaultValues())
	}
	res, err := d.backend.Insert(d.db, m, buftos(buf), values...)
	putBuffer(buf)
	return res, err
}
示例#4
0
func (d *Driver) makeQuery(m driver.Model, q query.Q, opts *driver.QueryOptions) (*datastore.Query, error) {
	if m.Join() != nil {
		return nil, errJoinNotSupported
	}
	dq := datastore.NewQuery(m.Table()).Ancestor(d.parentKey(m))
	var err error
	if dq, err = d.applyQuery(m, dq, q); err != nil {
		return nil, err
	}
	if opts != nil {
		if opts.Distinct {
			dq = dq.Distinct()
		}
		for _, v := range opts.Sort {
			field := v.Field()
			if v.Direction() == driver.DESC {
				field = "-" + field
			}
			dq = dq.Order(field)
		}
		if opts.Limit >= 0 {
			dq = dq.Limit(opts.Limit)
		}
		if opts.Offset > 0 {
			dq = dq.Offset(opts.Offset)
		}
	}
	return dq, nil
}
示例#5
0
func (d *Driver) Operate(m driver.Model, q query.Q, ops []*operation.Operation) (driver.Result, error) {
	buf := getBuffer()
	buf.WriteString("UPDATE ")
	buf.WriteByte('"')
	buf.WriteString(m.Table())
	buf.WriteByte('"')
	buf.WriteString(" SET ")
	var params []interface{}
	for ii, op := range ops {
		if ii > 0 {
			buf.WriteByte(',')
		}
		dbName, _, err := m.Map(op.Field)
		if err != nil {
			return nil, err
		}
		dbName = unquote(dbName)
		buf.WriteByte('"')
		buf.WriteString(dbName)
		buf.WriteByte('"')
		buf.WriteByte('=')
		switch op.Operator {
		case operation.OpAdd, operation.OpSub:
			buf.WriteString(dbName)
			if op.Operator == operation.OpAdd {
				buf.WriteByte('+')
			} else {
				buf.WriteByte('-')
			}
			buf.WriteString(d.backend.Placeholder(len(params)))
			params = append(params, op.Value)
		case operation.OpSet:
			if f, ok := op.Value.(operation.Field); ok {
				fieldName, _, err := m.Map(string(f))
				if err != nil {
					return nil, err
				}
				buf.WriteString(unquote(fieldName))
			} else {
				buf.WriteString(d.backend.Placeholder(len(params)))
				val, err := d.transformOutValue(op.Value)
				if err != nil {
					return nil, err
				}
				params = append(params, val)
			}
		default:
			return nil, fmt.Errorf("operator %d is not supported", op.Operator)
		}
	}
	qParams, err := d.where(buf, m, q, len(params))
	if err != nil {
		return nil, err
	}
	params = append(params, qParams...)
	res, err := d.db.Exec(buftos(buf), params...)
	putBuffer(buf)
	return res, err
}
示例#6
0
func (d *Driver) createTable(m driver.Model, table *Table) error {
	sql, err := table.SQL(d.db, d.backend, m, m.Table())
	if err != nil {
		return err
	}
	_, err = d.db.Exec(sql)
	return err
}
示例#7
0
func (b *Backend) HasIndex(db *sql.DB, m driver.Model, idx *index.Index, name string) (bool, error) {
	rows, err := db.Query("SHOW INDEX FROM ? WHERE Key_name = ?", m.Table(), name)
	if err != nil {
		return false, err
	}
	has := rows.Next()
	rows.Close()
	return has, nil
}
示例#8
0
func (b *Backend) Inspect(db *sql.DB, m driver.Model) (*sql.Table, error) {
	name := db.QuoteString(m.Table())
	rows, err := db.Query(fmt.Sprintf("PRAGMA table_info(%s)", name))
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	fieldsByName := make(map[string]*sql.Field)
	var fields []*sql.Field
	for rows.Next() {
		var cid int
		var f sql.Field
		var notnull int
		var def *string
		var pk int
		if err := rows.Scan(&cid, &f.Name, &f.Type, &notnull, &def, &pk); err != nil {
			return nil, err
		}
		f.Type = strings.ToUpper(f.Type)
		if notnull != 0 {
			f.AddConstraint(sql.ConstraintNotNull)
		}
		if def != nil {
			f.Default = *def
		}
		if pk != 0 {
			f.AddConstraint(sql.ConstraintPrimaryKey)
		}
		fields = append(fields, &f)
		fieldsByName[f.Name] = &f
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	rows, err = db.Query(fmt.Sprintf("PRAGMA foreign_key_list(%s)", name))
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		var id, seq int
		var table, from, to, onUpdate, onDelete, match string
		if err := rows.Scan(&id, &seq, &table, &from, &to, &onUpdate, &onDelete, &match); err != nil {
			return nil, err
		}
		field := fieldsByName[from]
		field.Constraints = append(field.Constraints, &sql.Constraint{Type: sql.ConstraintForeignKey, References: sql.MakeReference(table, to)})
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	if len(fields) > 0 {
		return &sql.Table{Fields: fields}, nil
	}
	return nil, nil
}
示例#9
0
func (d *Driver) Delete(m driver.Model, q query.Q) (driver.Result, error) {
	buf := getBuffer()
	buf.WriteString("DELETE FROM ")
	buf.WriteByte('"')
	buf.WriteString(m.Table())
	buf.WriteByte('"')
	params, err := d.where(buf, m, q, 0)
	if err != nil {
		return nil, err
	}
	res, err := d.db.Exec(buftos(buf), params...)
	putBuffer(buf)
	return res, err
}
示例#10
0
func (b *SqlBackend) AddFields(db *DB, m driver.Model, prevTable *Table, newTable *Table, fields []*Field) error {
	modelFields := m.Fields()
	tableName := db.QuoteIdentifier(m.Table())
	for _, v := range fields {
		idx := modelFields.MNameMap[v.Name]
		field := v
		hasDefault := modelFields.HasDefault(idx)
		if hasDefault && v.HasConstraint(ConstraintNotNull) {
			// ORM level default
			// Must be added as nullable first, then the default value
			// must be set and finally the field has to be altered to be
			// nullable.
			field = field.Copy()
			var constraints []*Constraint
			for _, v := range field.Constraints {
				if v.Type != ConstraintNotNull {
					constraints = append(constraints, v)
				}
			}
			field.Constraints = constraints
		}
		sql, cons, err := field.SQL(db, m, newTable)
		if err != nil {
			return err
		}
		if _, err = db.Exec(fmt.Sprintf("ALTER TABLE %s ADD COLUMN %s", tableName, sql)); err != nil {
			return err
		}
		if hasDefault {
			value := modelFields.DefaultValue(idx)
			fieldName := db.QuoteIdentifier(v.Name)
			if _, err := db.Exec(fmt.Sprintf("UPDATE %s SET %s = ?", tableName, fieldName), value); err != nil {
				return err
			}
			if v.HasConstraint(ConstraintNotNull) {
				if err := db.Backend().AlterField(db, m, newTable, field, v); err != nil {
					return err
				}
			}
		}
		for _, c := range cons {
			if _, err = db.Exec(fmt.Sprintf("ALTER TABLE %s ADD CONSTRAINT %s", tableName, c)); err != nil {
				return err
			}
		}
	}
	return nil
}
示例#11
0
func (b *Backend) AlterField(db *sql.DB, m driver.Model, table *sql.Table, oldField *sql.Field, newField *sql.Field) error {
	fsql, cons, err := newField.SQL(db, m, table)
	if err != nil {
		return err
	}
	tableName := db.QuoteIdentifier(m.Table())
	if _, err = db.Exec(fmt.Sprintf("ALTER TABLE %s CHANGE COLUMN %s %s", tableName, db.QuoteIdentifier(oldField.Name), fsql)); err != nil {
		return err
	}
	for _, c := range cons {
		if _, err = db.Exec(fmt.Sprintf("ALTER TABLE %s ADD CONSTRAINT %s", tableName, c)); err != nil {
			return err
		}
	}
	return err
}
示例#12
0
func (b *Backend) DefineField(db *sql.DB, m driver.Model, table *sql.Table, field *sql.Field) (string, []string, error) {
	def, cons, err := b.SqlBackend.DefineField(db, m, table, field)
	if err != nil {
		return "", nil, err
	}
	if ref := field.Constraint(sql.ConstraintForeignKey); ref != nil {
		if pos := strings.Index(def, " REFERENCES"); pos >= 0 {
			def = def[:pos]
		}
		refTable := ref.References.Table()
		refField := ref.References.Field()
		fkName := db.QuoteIdentifier(fmt.Sprintf("%s_%s_%s_%s", m.Table(), field.Name, refTable, refField))
		cons = append(cons, fmt.Sprintf("FOREIGN KEY %s(%s) REFERENCES %s(%s)", fkName, db.QuoteIdentifier(field.Name),
			db.QuoteIdentifier(refTable), db.QuoteIdentifier(refField)))
	}
	return strings.Replace(def, "AUTOINCREMENT", "AUTO_INCREMENT", -1), cons, nil
}
示例#13
0
func (d *Driver) mergeTable(m driver.Model, prevTable *Table, newTable *Table) error {
	existing := make(map[string]*Field)
	for _, v := range prevTable.Fields {
		existing[v.Name] = v
	}
	var missing []*Field
	for _, v := range newTable.Fields {
		prev := existing[v.Name]
		if prev == nil {
			// Check if we can add the field
			if v.HasConstraint(ConstraintNotNull) && !fieldHasDefault(m, v) {
				return fmt.Errorf("can't add NOT NULL field %q to table %q without a default value", v.Name, m.Table())
			}
			if v.HasConstraint(ConstraintPrimaryKey) {
				return fmt.Errorf("can't add PRIMARY KEY field %q to table %q", v.Name, m.Table())
			}
			missing = append(missing, v)
		} else {
			if prev.Type != v.Type {
				// Check the Kind
				k1, len1 := TypeKind(prev.Type)
				k2, len2 := TypeKind(v.Type)
				if k1 == k2 {
					// Check lengths
					if len1 != len2 {
					}
					continue
				}
				// Check if we can transform the kind
				fields := m.Fields()
				idx := fields.MNameMap[v.Name]
				modelName := fields.QNames[idx]
				modelType := fields.Types[idx]
				return fmt.Errorf("field %q on table %q is of type %s which is not compatible with the model field %q of type %s (%s)",
					v.Name, m.Table(), prev.Type, modelName, v.Type, modelType)
			}
		}
	}
	if len(missing) > 0 {
		if err := d.backend.AddFields(d.db, m, prevTable, newTable, missing); err != nil {
			return err
		}
	}
	return nil
}
示例#14
0
func (b *Backend) AddFields(db *sql.DB, m driver.Model, prevTable *sql.Table, newTable *sql.Table, fields []*sql.Field) error {
	rewrite := false
	for _, v := range fields {
		if !b.canAddField(v) {
			rewrite = true
			break
		}
	}
	if rewrite {
		name := db.QuoteIdentifier(m.Table())
		tmpName := fmt.Sprintf("%s_%s", m.Table(), stringutil.Random(8))
		quotedTmpName := db.QuoteIdentifier(tmpName)
		createSql, err := newTable.SQL(db, b, m, tmpName)
		if err != nil {
			return err
		}
		if _, err := db.Exec(createSql); err != nil {
			return err
		}
		fieldNames := generic.Map(prevTable.Fields, func(f *sql.Field) string { return f.Name }).([]string)
		// The previous table might have fields that we're not part
		// of the new table.
		fieldSet := make(map[string]bool)
		for _, v := range newTable.Fields {
			fieldSet[v.Name] = true
		}
		fieldNames = generic.Filter(fieldNames, func(n string) bool { return fieldSet[n] }).([]string)
		sqlFields := strings.Join(generic.Map(fieldNames, db.QuoteIdentifier).([]string), ", ")
		copySql := fmt.Sprintf("INSERT INTO %s (%s) SELECT %s FROM %s", quotedTmpName, sqlFields, sqlFields, name)
		if _, err := db.Exec(copySql); err != nil {
			return err
		}
		if _, err := db.Exec(fmt.Sprintf("DROP TABLE %s", name)); err != nil {
			return err
		}
		if _, err := db.Exec(fmt.Sprintf("ALTER TABLE %s RENAME TO %s", quotedTmpName, name)); err != nil {
			return err
		}
		return nil
	}
	return b.SqlBackend.AddFields(db, m, prevTable, newTable, fields)
}
示例#15
0
func (d *Driver) createIndex(m driver.Model, idx *index.Index, name string) error {
	has, err := d.backend.HasIndex(d.db, m, idx, name)
	if err != nil {
		return err
	}
	if has {
		return nil
	}

	buf := getBuffer()
	buf.WriteString("CREATE ")
	if idx.Unique {
		buf.WriteString("UNIQUE ")
	}
	buf.WriteString("INDEX ")
	buf.WriteString(name)
	buf.WriteString(" ON \"")
	buf.WriteString(m.Table())
	buf.WriteString("\" (")
	fields := m.Fields()
	for _, v := range idx.Fields {
		name, _, err := fields.Map(v)
		if err != nil {
			return err
		}
		buf.WriteByte('"')
		buf.WriteString(name)
		buf.WriteByte('"')
		if DescField(idx, v) {
			buf.WriteString(" DESC")
		}
		buf.WriteByte(',')
	}
	buf.Truncate(buf.Len() - 1)
	buf.WriteString(")")
	_, err = d.db.Exec(buftos(buf))
	putBuffer(buf)
	return err
}
示例#16
0
func (d *Driver) Insert(m driver.Model, data interface{}) (driver.Result, error) {
	var id int64
	fields := m.Fields()
	var pkVal *reflect.Value
	// TODO: If the PK is supplied by the user rather than auto-assigned, it
	// might conflict with PKs generated by datastore.AllocateIDs().
	if fields.PrimaryKey >= 0 {
		p := d.primaryKey(fields, data)
		if p.IsValid() && types.Kind(p.Kind()) == types.Int {
			id = p.Int()
			if id == 0 {
				// Must assign PK field value after calling AllocateIDs
				pkVal = &p
			}
		}
	}
	name := m.Table()
	// Make all objects of a given kind ancestors of the same key. While
	// this hurts scalability, it makes all reads strongly consistent.
	parent := d.parentKey(m)
	var err error
	if id == 0 {
		id, _, err = datastore.AllocateIDs(d.c, name, parent, 1)
		if err != nil {
			return nil, err
		}
	}
	if fields.AutoincrementPk && pkVal != nil {
		pkVal.SetInt(int64(id))
	}
	key := datastore.NewKey(d.c, name, "", id, parent)
	log.Debugf("DATASTORE: put %s %v", key, data)
	_, err = datastore.Put(d.c, key, data)
	if err != nil {
		return nil, err
	}
	return &result{key: key, count: 1}, nil
}
示例#17
0
func (d *Driver) indexName(m driver.Model, idx *index.Index) (string, error) {
	if len(idx.Fields) == 0 {
		return "", fmt.Errorf("index on %v has no fields", m.Type())
	}
	buf := getBuffer()
	buf.WriteString(m.Table())
	for _, v := range idx.Fields {
		dbName, _, err := m.Map(v)
		if err != nil {
			return "", err
		}
		buf.WriteByte('_')
		// dbName is quoted and includes the table name
		// extract the unquoted field name.
		buf.WriteString(unquote(dbName))
		if DescField(idx, v) {
			buf.WriteString("_desc")
		}
	}
	s := buf.String()
	putBuffer(buf)
	return s, nil
}
示例#18
0
func (d *Driver) selectStmt(buf *bytes.Buffer, params *[]interface{}, fields []string, quote bool, m driver.Model, opts driver.QueryOptions) error {
	buf.WriteString("SELECT ")
	if opts.Distinct {
		buf.WriteString("DISTINCT ")
	}
	if fields != nil {
		if quote {
			for _, v := range fields {
				buf.WriteByte('"')
				buf.WriteString(v)
				buf.WriteByte('"')
				buf.WriteByte(',')
			}
		} else {
			for _, v := range fields {
				buf.WriteString(v)
				buf.WriteByte(',')
			}
		}
	} else {
		// Select all fields for the given model (which might be joined)
		cur := m
		for {
			if !cur.Skip() {
				for _, v := range cur.Fields().QuotedNames {
					buf.WriteString(v)
					buf.WriteByte(',')
				}
			}
			join := cur.Join()
			if join == nil {
				break
			}
			cur = join.Model()
		}
	}
	buf.Truncate(buf.Len() - 1)
	buf.WriteString(" FROM ")
	buf.WriteByte('"')
	buf.WriteString(m.Table())
	buf.WriteByte('"')
	for join := m.Join(); join != nil; {
		jm := join.Model()
		switch join.Type() {
		case driver.OuterJoin:
			buf.WriteString(" FULL OUTER")
		case driver.LeftJoin:
			buf.WriteString(" LEFT OUTER")
		case driver.RightJoin:
			buf.WriteString(" RIGHT OUTER")
		}
		buf.WriteString(" JOIN ")
		buf.WriteByte('"')
		buf.WriteString(jm.Table())
		buf.WriteByte('"')
		buf.WriteString(" ON ")
		if err := d.condition(buf, params, m, join.Query(), len(*params)); err != nil {
			return err
		}
		join = jm.Join()
	}
	return nil
}
示例#19
0
func (b *SqlBackend) Inspect(db *DB, m driver.Model, schema string) (*Table, error) {
	var val int
	name := db.QuoteString(m.Table())
	s := db.QuoteString(schema)
	eq := fmt.Sprintf("SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE "+
		"TABLE_NAME = %s AND TABLE_SCHEMA = %s", name, s)
	err := db.QueryRow(eq).Scan(&val)
	if err != nil {
		if err == ErrNoRows {
			return nil, nil
		}
		return nil, err
	}
	// Select fields with their types
	iq := fmt.Sprintf("SELECT COLUMN_NAME, IS_NULLABLE, DATA_TYPE, "+
		"CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS "+
		"WHERE TABLE_NAME = %s AND TABLE_SCHEMA = %s", name, s)
	rows, err := db.Query(iq)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var fields []*Field
	fieldsByName := make(map[string]*Field)
	for rows.Next() {
		var f Field
		var nullable string
		var maxLength *int
		if err := rows.Scan(&f.Name, &nullable, &f.Type, &maxLength); err != nil {
			return nil, err
		}
		if maxLength != nil {
			f.Type = fmt.Sprintf("%s (%d)", f.Type, *maxLength)
		}
		f.Type = strings.ToUpper(f.Type)
		if nullable != "YES" {
			f.AddConstraint(ConstraintNotNull)
		}
		fields = append(fields, &f)
		fieldsByName[f.Name] = &f
	}
	// Field constraints
	cq := fmt.Sprintf("SELECT C.CONSTRAINT_NAME, CONSTRAINT_TYPE, COLUMN_NAME "+
		"FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS C JOIN "+
		"INFORMATION_SCHEMA.KEY_COLUMN_USAGE K ON C.CONSTRAINT_NAME = "+
		"K.CONSTRAINT_NAME WHERE C.TABLE_NAME = %s AND K.TABLE_NAME = %s "+
		"AND C.TABLE_SCHEMA = %s", name, name, s)
	rows, err = db.Query(cq)
	if err != nil {
		return nil, err
	}
	foreignKeys := make(map[string]string)
	defer rows.Close()
	for rows.Next() {
		var constraintName string
		var constraintType string
		var name string
		if err := rows.Scan(&constraintName, &constraintType, &name); err != nil {
			return nil, err
		}
		field := fieldsByName[name]
		if field == nil {
			return nil, fmt.Errorf("table %s has constraint on non-existing field %s", m.Table(), name)
		}
		switch strings.ToLower(constraintType) {
		case "primary key":
			field.AddConstraint(ConstraintPrimaryKey)
		case "foreign key":
			foreignKeys[constraintName] = name
		case "unique":
			field.AddConstraint(ConstraintUnique)
		default:
			return nil, fmt.Errorf("unknown constraint type %s on field %s in table %s", constraintType, name, m.Table())
		}
	}
	if len(foreignKeys) > 0 {
		// Resolve FKs
		fks := strings.Join(generic.Map(generic.Keys(foreignKeys).([]string), db.QuoteString).([]string), ", ")
		fq := fmt.Sprintf("SELECT CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE CONSTRAINT_NAME IN (%s)", fks)
		rows, err := db.Query(fq)
		if err != nil {
			return nil, err
		}
		defer rows.Close()
		for rows.Next() {
			var constraintName string
			var tableName string
			var columnName string
			if err := rows.Scan(&constraintName, &tableName, &columnName); err != nil {
				return nil, err
			}
			fieldName := foreignKeys[constraintName]
			// Field was validated previously, won't be nil
			field := fieldsByName[fieldName]
			field.Constraints = append(field.Constraints, &Constraint{Type: ConstraintForeignKey, References: MakeReference(tableName, columnName)})
		}
	}
	return &Table{Fields: fields}, nil
}
示例#20
0
func (d *Driver) parentKey(m driver.Model) *datastore.Key {
	return datastore.NewKey(d.c, m.Table(), "", -1, nil)
}