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 = "******" + 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
}
func (d *Datamart) CreateDimensionTables(dimDefs map[string]*DimensionDefinition,
conn *sqlite.Conn) error {
var queryStr string
var err error
for name, dim := range dimDefs {
// "integer primary key" creates as auto incrementing column
queryStr = fmt.Sprintf(
"create table %s (%s integer primary key, %s %s",
name,
dim.IndexColumn,
dim.UniqueColumn,
d.findDatatype(dim.UniqueColumn),
)
extraStr := ""
for _, extra := range dim.ExtraColumns {
queryStr += fmt.Sprintf(", %s %s", extra, d.findDatatype(extra))
extraStr += "," + extra
}
queryStr += "); "
err = conn.Exec(queryStr)
if err != nil {
goto Error
}
queryStr = fmt.Sprintf(
`insert into %s
select null, %s %s
from (select %s, %s sort %s
from source
where %s is not null
group by %s, sort %s) a
order by sort %s;`,
name,
dim.UniqueColumn,
extraStr,
dim.UniqueColumn,
dim.SortExpr,
extraStr,
dim.UniqueColumn,
dim.UniqueColumn,
extraStr,
dim.SortDirection,
)
err = conn.Exec(queryStr)
if err != nil {
goto Error
}
queryStr = fmt.Sprintf(
"create index %s_idx on %s (%s);",
dim.UniqueColumn,
name,
dim.UniqueColumn,
)
err = conn.Exec(queryStr)
if err != nil {
goto Error
}
}
return nil
Error:
return fmt.Errorf(
"CreateDimensionTables() error, sqlite error: %s\nquery:\n%s\n",
err.Error(),
queryStr,
)
}
// Populate channel with groups by reading the SQLite database.
func populateChannelGroupsFromDatabase(server *Server, c *Channel, db *sqlite.Conn) os.Error {
stmt, err := db.Prepare("SELECT group_id, name, inherit, inheritable FROM groups WHERE server_id=? AND channel_id=?")
if err != nil {
return err
}
if err := stmt.Exec(server.Id, c.Id); err != nil {
return err
}
groups := make(map[int64]*Group)
for stmt.Next() {
var (
GroupId int64
Name string
Inherit bool
Inheritable bool
)
if err := stmt.Scan(&GroupId, &Name, &Inherit, &Inheritable); err != nil {
return err
}
g := NewGroup(c, Name)
g.Inherit = Inherit
g.Inheritable = Inheritable
c.Groups[g.Name] = g
groups[GroupId] = g
}
stmt, err = db.Prepare("SELECT user_id, addit FROM group_members WHERE server_id=? AND group_id=?")
if err != nil {
return err
}
for gid, grp := range groups {
if err = stmt.Exec(server.Id, gid); err != nil {
return err
}
for stmt.Next() {
var (
UserId int64
Add bool
)
if err := stmt.Scan(&UserId, &Add); err != nil {
return err
}
if Add {
grp.Add[int(UserId)] = true
} else {
grp.Remove[int(UserId)] = true
}
}
}
return nil
}
