Golang sqlite.Conn类代码示例

func (self *BundleManager) bundleFileValidateNolock(conn *sqlite.Conn, user string, bundle_id int, file_id int) error {
	if conn == nil {
		conn = self.conn
	}
	//FIXME better way to encode username and column?
	s, err := conn.Prepare("select 1 from files, bundles where bundles.user = ? and bundles.id = ? and bundles.id = files.bundle_id and files.id = ?;")
	if err != nil {
		log.Printf("%v\n", err)
		return err
	}
	defer s.Finalize()
	err = s.Exec(user, bundle_id, file_id)
	if err != nil {
		log.Printf("%v\n", err)
		return err
	}
	for {
		if !s.Next() {
			break
		}
		var value int
		err = s.Scan(&value)
		if err != nil {
			log.Printf("%v\n", err)
			return err
		}
		if value == 1 {
			return nil
		}
	}
	return errors.New("The specified file is not owned by the user and bundle specified.")
}

func (self *BundleManager) BundleFileGroupsSet(conn *sqlite.Conn, user string, bundle_id int, file_id int, groups [][2]string) error {
	self.mutex.Lock()
	if self.shutdown {
		return errors.New("BundleManager is shutdown.")
	}
	defer self.mutex.Unlock()
	state, err := self.bundleStateGetNoLock(conn, user, bundle_id)
	if err != nil {
		return err
	}
	if state != BundleState_Unsubmitted {
		return errors.New("The bundle specified is not editable.")
	}
	err = self.bundleFileValidateNolock(conn, user, bundle_id, file_id)
	if err != nil {
		return nil
	}
	err = conn.Exec("delete from groups where file_id = ?;", file_id)
	if err != nil {
		log.Printf("%v\n", err)
		return err
	}
	for _, item := range groups {
		err = conn.Exec("insert into groups(file_id, type, name) values(?, ?, ?);", file_id, item[0], item[1])
		if err != nil {
			log.Printf("%v\n", err)
			return err
		}
	}
	return nil
}

// Link a Server's channels together
func populateChannelLinkInfo(server *Server, db *sqlite.Conn) (err os.Error) {
	stmt, err := db.Prepare("SELECT channel_id, link_id FROM channel_links WHERE server_id=?")
	if err != nil {
		return err
	}

	if err := stmt.Exec(server.Id); err != nil {
		return err
	}

	for stmt.Next() {
		var (
			ChannelId int
			LinkId    int
		)
		if err := stmt.Scan(&ChannelId, &LinkId); err != nil {
			return err
		}

		channel, exists := server.Channels[ChannelId]
		if !exists {
			return os.NewError("Attempt to perform link operation on non-existant channel.")
		}

		other, exists := server.Channels[LinkId]
		if !exists {
			return os.NewError("Attempt to perform link operation on non-existant channel.")
		}

		server.LinkChannels(channel, other)
	}

	return nil
}

func (self *BundleManager) BundleFileBoolSet(conn *sqlite.Conn, user string, bundle_id int, file_id int, column string, value bool) error {
	self.mutex.Lock()
	if conn == nil {
		conn = self.conn
	}
	if self.shutdown {
		return errors.New("BundleManager is shutdown.")
	}
	defer self.mutex.Unlock()
	if column != "disable_on_error" {
		old_state, err := self.bundleStateGetNoLock(conn, user, bundle_id)
		if err != nil {
			log.Printf("%v\n", err)
			return err
		}
		if old_state != BundleState_Unsubmitted {
			return errors.New("Can't set string on submitted bundle.")
		}
	}
	//FIXME check for bundle->file.
	err := conn.Exec("update files set "+column+" = ? where id = ?;", value, file_id)
	if err != nil {
		log.Printf("%v\n", err)
		return err
	}
	return nil
}

func (self *BundleManager) bundleStateGetNoLock(conn *sqlite.Conn, user string, bundle_id int) (BundleState, error) {
	if conn == nil {
		conn = self.conn
	}
	s, err := conn.Prepare("select state from bundles where bundles.user = ? and bundles.id = ?;")
	if err != nil {
		log.Printf("%v\n", err)
		return BundleState_Error, err
	}
	defer s.Finalize()
	err = s.Exec(user, bundle_id)
	if err != nil {
		log.Printf("%v\n", err)
		return BundleState_Error, err
	}
	for {
		if !s.Next() {
			break
		}
		var value int
		err = s.Scan(&value)
		if err != nil {
			log.Printf("%v\n", err)
			return BundleState_Error, err
		}
		return BundleState(value), nil
	}
	return BundleState_Error, errors.New("Unknown error.")
}

func StoreTableToSqlite(conn *sqlite.Conn, name string, tinfo *Table) error {
	queryStr := fmt.Sprintf("create table %s (", name)
	for i := 0; i < len(tinfo.ColumnNames); i++ {
		if i != 0 {
			queryStr += ","
		}
		queryStr +=
			tinfo.ColumnNames[i] +
				" " +
				mapDatatypeToSqlite[tinfo.ColumnTypes[i]]
	}
	queryStr += ");"

	err := conn.Exec(queryStr)
	if err != nil {
		return fmt.Errorf("StoreTableToSqlite(): %s , %s,", err.Error(), queryStr)
	}

	row := make([]*interface{}, len(tinfo.ColumnTypes))
	for i := 0; i < len(row); i++ {
		row[i] = new(interface{})
	}
	for i := 0; i < tinfo.rowCount(); i++ {
		queryStr = fmt.Sprintf("insert into %s values (", name)
		err := tinfo.readRow(i, row)
		if err != nil {
			return err
		}
		for j := 0; j < len(row); j++ {
			if j != 0 {
				queryStr += ","
			}
			value := *(row[j])
			if value == nil {
				queryStr += "null"
				continue
			}
			switch tinfo.ColumnTypes[j] {
			case IntegerDatatype:
				queryStr += fmt.Sprintf("%d", value)
			case StringDatatype:
				re, err := regexp.Compile("'")
				if err != nil {
					return err
				}
				escaped := re.ReplaceAllString(value.(string), "''")
				queryStr += fmt.Sprintf("'%s'", escaped)
			case FloatDatatype:
				queryStr += fmt.Sprintf("%f", value)
			}
		}
		queryStr += ");"
		err = conn.Exec(queryStr)
		if err != nil {
			return fmt.Errorf("StoreTableToSqlite(): %s , %s,", err.Error(), queryStr)
		}
	}

	return nil
}

// Populate channel with its ACLs by reading the SQLite databse.
func populateChannelACLFromDatabase(server *Server, c *Channel, db *sqlite.Conn) os.Error {
	stmt, err := db.Prepare("SELECT user_id, group_name, apply_here, apply_sub, grantpriv, revokepriv FROM acl WHERE server_id=? AND channel_id=? ORDER BY priority")
	if err != nil {
		return err
	}

	if err := stmt.Exec(server.Id, c.Id); err != nil {
		return err
	}

	for stmt.Next() {
		var (
			UserId    string
			Group     string
			ApplyHere bool
			ApplySub  bool
			Allow     int64
			Deny      int64
		)
		if err := stmt.Scan(&UserId, &Group, &ApplyHere, &ApplySub, &Allow, &Deny); err != nil {
			return err
		}

		acl := NewChannelACL(c)
		acl.ApplyHere = ApplyHere
		acl.ApplySubs = ApplySub
		if len(UserId) > 0 {
			acl.UserId, err = strconv.Atoi(UserId)
			if err != nil {
				return err
			}
		} else if len(Group) > 0 {
			acl.Group = Group
		} else {
			return os.NewError("Invalid ACL: Neither Group or UserId specified")
		}

		acl.Deny = Permission(Deny)
		acl.Allow = Permission(Allow)
		c.ACL = append(c.ACL, acl)
	}

	return nil
}

// Add channel metadata (channel_info table from SQLite) by reading the SQLite database.
func populateChannelInfoFromDatabase(server *Server, c *Channel, db *sqlite.Conn) os.Error {
	stmt, err := db.Prepare("SELECT value FROM channel_info WHERE server_id=? AND channel_id=? AND key=?")
	if err != nil {
		return err
	}

	// Fetch description
	if err := stmt.Exec(server.Id, c.Id, ChannelInfoDescription); err != nil {
		return err
	}
	for stmt.Next() {
		var description string
		err = stmt.Scan(&description)
		if err != nil {
			return err
		}

		key, err := globalBlobstore.Put([]byte(description))
		if err != nil {
			return err
		}
		c.DescriptionBlob = key
	}

	if err := stmt.Reset(); err != nil {
		return err
	}

	// Fetch position
	if err := stmt.Exec(server.Id, c.Id, ChannelInfoPosition); err != nil {
		return err
	}
	for stmt.Next() {
		var pos int
		if err := stmt.Scan(&pos); err != nil {
			return err
		}

		c.Position = pos
	}

	return nil
}

func getRowCount(name string, conn *sqlite.Conn) (int, error) {

	stmt, err := conn.Prepare(fmt.Sprintf("select count(1) from %s;", name))
	if err != nil {
		return -1, err
	}
	err = stmt.Exec()
	if err != nil {
		return -1, err
	}
	stmt.Next()
	var count int
	err = stmt.Scan(&count)
	if err != nil {
		return -1, err
	}
	stmt.Finalize()
	return count, nil
}

func (self *BundleManager) bundleFileGet(conn *sqlite.Conn, bundle *BundleMD, user string, bundle_id int, file_id int) (*BundleFileMD, error) {
	self.mutex.Lock()
	if self.shutdown {
		return nil, errors.New("BundleManager is shutdown.")
	}
	defer self.mutex.Unlock()
	if bundle != nil {
		bundle_id = bundle.id
		user = bundle.user
	} else {
		bundle = &BundleMD{id: bundle_id, user: user, bm: self}
	}
	s, err := conn.Prepare("select 1 from files, bundles where bundles.user = ? and bundles.id = ? and files.bundle_id = bundles.id and files.id = ?;")
	if err != nil {
		log.Printf("%v\n", err)
		return nil, err
	}
	defer s.Finalize()
	err = s.Exec(user, bundle_id, file_id)
	if err != nil {
		log.Printf("%v\n", err)
		return nil, err
	}
	for {
		if !s.Next() {
			break
		}
		var value int
		err = s.Scan(&value)
		if err != nil {
			log.Printf("%v\n", err)
			return nil, err
		}
		if value == 1 {
			if conn == self.conn {
				conn = nil
			}
			return &BundleFileMD{id: file_id, bundle: bundle, conn: conn}, nil
		}
	}
	return nil, errors.New("File Id " + strconv.Itoa(file_id) + " not found.")
}

func (self *BundleMD) FileAdd(pacifica_filename string, local_filename string, commit bool) (*BundleFileMD, error) {
	var conn *sqlite.Conn
	if commit == false {
		var err error
		conn, err = self.bm.connGet()
		if err != nil {
			return nil, err
		}
		err = conn.Exec("begin transaction")
		if err != nil {
			return nil, err
		}
	} else {
		conn = self.bm.conn
	}
	id, err := self.bm.bundleFileIdAdd(conn, self.user, self.id, pacifica_filename, local_filename)
	if err != nil {
		if commit == false {
			conn.Close()
		}
		return nil, err
	}
	return self.bm.bundleFileGet(conn, self, "", -1, id)
}

// Create fact table by joing source table to dimension tables
// Three cases for source data column:
// 1. Matches dimension column, fact row value is id in matching dimesnion table row
// 2. Is null, and dimension table for column has > 0 rows, fact row value is -1
// 3. Is null, and dimension table for column has 0 rows, fact row value is -1
func (d *Datamart) CreateFactTable(dimDefs map[string]*DimensionDefinition,
	conn *sqlite.Conn) error {

	rowCountCache := make(map[string]int)

	// make list of non-zero dim tables
	nzDim := make(map[string]*DimensionDefinition)
	for name, dim := range dimDefs {
		count, err := getRowCount(name, conn)
		if err != nil {
			return err
		}
		rowCountCache[name] = count
		if count > 0 {
			nzDim[name] = dim
		}
	}

	// make fact table
	query := "create table fact as select"
	i := 0
	for name, dim := range dimDefs {
		if i != 0 {
			query += ","
		}
		if rowCountCache[name] == 0 {
			query += " -1 " + dim.IndexColumn
		} else {
			// dim.IndexColumn value - 1 because want ids to start at 0
			query += " case when source." + dim.UniqueColumn +
				" is null then -1 else " + dim.IndexColumn + " - 1 end " +
				dim.IndexColumn
		}
		i++
	}

	query += "\nfrom source"
	for name, dim := range nzDim {
		query += " left outer join " + name + " on "
		query += "source." + dim.UniqueColumn + " = " +
			name + "." + dim.UniqueColumn
	}

	// if all dimension tables a empty return emtpy fact table
	if i == 0 {
		query += "\nwhere "
		query += "1 = 0"
	}

	query += ";"

	err := conn.Exec(query)
	if err != nil {
		return fmt.Errorf(
			"CreateFactTable() error, sqlite error: %s\nquery:\n%s\n",
			err.Error(),
			query,
		)
	}

	return nil
}

// Populate the Server with Channels from the database.
func populateChannelsFromDatabase(server *Server, db *sqlite.Conn, parentId int) os.Error {
	parent, exists := server.Channels[parentId]
	if !exists {
		return os.NewError("Non-existant parent")
	}

	stmt, err := db.Prepare("SELECT channel_id, name, inheritacl FROM channels WHERE server_id=? AND parent_id=?")
	if err != nil {
		return err
	}

	err = stmt.Exec(server.Id, parentId)
	if err != nil {
		return err
	}

	for stmt.Next() {
		var (
			name    string
			chanid  int
			inherit bool
		)
		err = stmt.Scan(&chanid, &name, &inherit)
		if err != nil {
			return err
		}

		c := server.NewChannel(chanid, name)
		c.InheritACL = inherit
		parent.AddChild(c)
	}

	// Add channel_info
	for _, c := range parent.children {
		err = populateChannelInfoFromDatabase(server, c, db)
		if err != nil {
			return err
		}
	}

	// Add ACLs
	for _, c := range parent.children {
		err = populateChannelACLFromDatabase(server, c, db)
		if err != nil {
			return err
		}
	}

	// Add groups
	for _, c := range parent.children {
		err = populateChannelGroupsFromDatabase(server, c, db)
		if err != nil {
			return err
		}
	}

	// Add subchannels
	for id, _ := range parent.children {
		err = populateChannelsFromDatabase(server, db, id)
		if err != nil {
			return err
		}
	}

	return nil
}

func (self *BundleManager) bundleFileIdAdd(conn *sqlite.Conn, user string, bundle_id int, pacifica_filename string, local_filename string) (int, error) {
	self.mutex.Lock()
	if self.shutdown {
		return -1, errors.New("BundleManager is shutdown.")
	}
	defer self.mutex.Unlock()
	sql := `
insert into files(bundle_id, local_filename, myemsl_filename) values(
	(select case when (state == ?) then ? else null end from bundles where user = ? and id = ?),
	?,
	?
);`
	err := conn.Exec(sql, int(BundleState_Unsubmitted), bundle_id, user, bundle_id, local_filename, pacifica_filename)
	if err != nil {
		old_state, terr := self.bundleStateGetNoLock(conn, user, bundle_id)
		if terr != nil {
			log.Printf("%v\n", terr)
			return -1, err
		}
		if old_state != BundleState_Unsubmitted {
			return -1, errors.New("Can't add files to submitted bundle.")
		}
		s, terr := conn.Prepare("select id, local_filename from files where bundle_id = ? and myemsl_filename = ?;")
		if terr != nil {
			log.Printf("%v\n", terr)
			return -1, err
		}
		defer s.Finalize()
		terr = s.Exec(bundle_id, pacifica_filename)
		if terr != nil {
			log.Printf("%v\n", terr)
			return -1, err
		}
		for {
			if !s.Next() {
				break
			}
			var tid int
			var tlocal_filename string
			terr = s.Scan(&tid, &tlocal_filename)
			if terr != nil {
				log.Printf("%v\n", terr)
				return -1, err
			}
			if tlocal_filename == local_filename {
				return -1, errors.New(fmt.Sprintf("The file with the specified Server filename (%s) already exists in the bundle (%d)!", pacifica_filename, bundle_id))
			} else {
				return -1, errors.New(fmt.Sprintf("The file with the specified Server filename (%s) already exists in the bundle (%d) but with a different local filename (%s != %s)!", pacifica_filename, bundle_id, local_filename, tlocal_filename))
			}
		}
		log.Printf("%v\n", err)
		log.Printf("SQL: %s, %d, %d, %s, %d, %s, %s\n", sql, int(BundleState_Unsubmitted), bundle_id, user, bundle_id, local_filename, pacifica_filename)
		return -1, err
	}
	s, err := conn.Prepare("select last_insert_rowid();")
	if err != nil {
		log.Printf("%v\n", err)
		return -1, err
	}
	defer s.Finalize()
	for {
		if !s.Next() {
			break
		}
		var value int
		err = s.Scan(&value)
		if err != nil {
			log.Printf("%v\n", err)
			return -1, err
		}
		return value, nil
	}
	return -1, errors.New("Unknown error.")
}

func populateUsers(server *Server, db *sqlite.Conn) (err os.Error) {
	// Populate the server with regular user data
	stmt, err := db.Prepare("SELECT user_id, name, pw, lastchannel, texture, strftime('%s', last_active) FROM users WHERE server_id=?")
	if err != nil {
		return
	}

	err = stmt.Exec(server.Id)
	if err != nil {
		return
	}

	for stmt.Next() {
		var (
			UserId       int64
			UserName     string
			SHA1Password string
			LastChannel  int
			Texture      []byte
			LastActive   int64
		)

		err = stmt.Scan(&UserId, &UserName, &SHA1Password, &LastChannel, &Texture, &LastActive)
		if err != nil {
			continue
		}

		user, err := NewUser(uint32(UserId), UserName)
		if err != nil {
			return err
		}

		user.Password = "sha1$$" + SHA1Password

		key, err := globalBlobstore.Put(Texture)
		if err != nil {
			return err
		}
		user.TextureBlob = key

		user.LastActive = uint64(LastActive)
		user.LastChannelId = LastChannel

		server.Users[user.Id] = user
	}

	stmt, err = db.Prepare("SELECT key, value FROM user_info WHERE server_id=? AND user_id=?")
	if err != nil {
		return
	}

	// Populate users with any new-style UserInfo records
	for uid, user := range server.Users {
		err = stmt.Reset()
		if err != nil {
			return err
		}

		err = stmt.Exec(server.Id, uid)
		if err != nil {
			return err
		}

		for stmt.Next() {
			var (
				Key   int
				Value string
			)

			err = stmt.Scan(&Key, &Value)
			if err != nil {
				return err
			}

			switch Key {
			case UserInfoEmail:
				user.Email = Value
			case UserInfoComment:
				key, err := globalBlobstore.Put([]byte(Value))
				if err != nil {
					return err
				}
				user.CommentBlob = key
			case UserInfoHash:
				user.CertHash = Value
			case UserInfoLastActive:
				// not a kv-pair (trigger)
			case UserInfoPassword:
				// not a kv-pair
			case UserInfoName:
				// not a kv-pair
			}
		}
	}

	return
}