// #258 update user last seen
func (lp *License) SaveLogonTime_Bl() {
start := time.Now()
db := ephenationdb.New()
if db == nil {
log.Println("license.SaveLogonTime: Failed to save logon time")
return
}
defer ephenationdb.Release(db) // Defer database close when we are sure that the db is open
// Update last seen online --> Moved from Save() due to issue #87: LastSeen not updated in database
now := time.Now()
nowstring := fmt.Sprintf("%4v-%02v-%02v", now.Year(), int(now.Month()), now.Day())
query := "UPDATE users SET lastseen='" + nowstring + "' WHERE email='" + lp.mail + "'"
//fmt.Printf("%v\n",query)
if *verboseFlag > 0 {
log.Println(query)
}
err := db.Query(query)
if err != nil {
log.Println(err)
return
}
elapsed := time.Now().Sub(start)
// if elapsed > UPPER_TIME_LIMIT && *verboseFlag > 0 {
log.Printf("license.SaveLogonTime %d ms\n", elapsed/1e6)
// }
return
}
// Save the allocated chunks for the specified avatar
func SaveAvatar_Bl(avatar uint32, chunks []CC) bool {
if *disableSave {
// Ignore the save, pretend everything is fine
return true
}
db = ephenationdb.New()
if db == nil {
return false
}
defer ephenationdb.Release(db)
// First delete the current allocation, and replace with the new
query := fmt.Sprintf("DELETE FROM chunkdata WHERE avatarID=%d", avatar)
err := db.Query(query)
if err != nil {
log.Println(err)
return false
}
if len(chunks) == 0 {
return true
}
query = "INSERT INTO chunkdata (x,y,z,avatarID) VALUES "
comma := ""
for _, ch := range chunks {
query += fmt.Sprintf("%s(%d,%d,%d,%d)", comma, ch.X, ch.Y, ch.Z, avatar)
comma = ","
}
err = db.Query(query)
if err != nil {
log.Println(err)
return false
}
return true
}
// Some rudimentary tests to get SQL connections
func DoTestSQL() bool {
db := ephenationdb.New()
DoTestCheck("DoTestSQL Got connection", db != nil)
if db == nil {
// Cancel the other tests
return false
}
ephenationdb.Release(db)
db2 := ephenationdb.New()
DoTestCheck("DoTestSQL Got same (cached) connection again", db2 == db)
db3 := ephenationdb.New()
DoTestCheck("DoTestSQL Got new connection", db3 != db2 && db3 != nil)
ephenationdb.Release(db3)
ephenationdb.Release(db2)
return true
}
// Iterate over the copy of all scores, and save to SQL DB where needed.
func update(list []*territoryScore) {
if *disableSQL {
return
}
db := ephenationdb.New()
if db == nil {
return // Give it up for this time
}
defer ephenationdb.Release(db)
now := time.Now()
for _, ts := range list {
if !ts.initialized {
log.Println("score.update initialize from DB for", ts.uid)
loadFromSQL(db, ts)
}
if !ts.modified {
continue
}
oldScore := ts.Score
oldBalance := ts.ScoreBalance
ts.decay(&now)
if ts.initialized {
// Normally, it is initialized, but stay on the safe side if there was a problem.
saveToSQL(db, ts)
log.Printf("score.update %s to %.1f (diff %f), Balance %.1f (diff %f)\n", ts.name, ts.Score, ts.Score-oldScore, ts.ScoreBalance, ts.ScoreBalance-oldBalance)
}
}
}
// Find the list of all chunks allocated for an avatar
// Return false in case of failure
func ReadAvatar_Bl(avatarID uint32) ([]CC, bool) {
db = ephenationdb.New()
if db == nil {
log.Println("chunkdb.ReadAvatar_Bl failed")
return nil, false
}
defer ephenationdb.Release(db)
// Build a query for the given chunk coordinate as an argument
query := fmt.Sprintf("SELECT * FROM chunkdata WHERE avatarID=%d", avatarID)
err := db.Query(query)
if err != nil {
// Fatal error
log.Println(err)
os.Exit(1)
}
// Store the result
result, err := db.StoreResult()
if err != nil {
log.Println(err)
return nil, false
}
// Fetch all rows
rows := result.FetchRows()
numRows := result.RowCount()
FieldNames := result.FetchFields()
// fmt.Printf("chunkdb.ReadAvatar rows: %v. numRows: %v\n", rows, numRows)
if rows == nil || numRows == 0 {
db.FreeResult()
return nil, true
}
ret := make([]CC, numRows)
for r, row := range rows {
cnt := int(result.FieldCount())
for i := 0; i < cnt; i++ {
switch FieldNames[i].Name {
case "x":
ret[r].X = int32(row[i].(int64))
case "y":
ret[r].Y = int32(row[i].(int64))
case "z":
ret[r].Z = int32(row[i].(int64))
}
}
}
db.FreeResult()
// fmt.Printf("chunkdb.ReadAvatar: Avatar %d (%d,%d,%d)\n", avatarID, x, y, z)
return ret, true
}
func (lp *License) Save_Bl() bool {
start := time.Now()
db := ephenationdb.New()
if db == nil {
return false
}
defer ephenationdb.Release(db) // Defer database close when we are sure that the db is open
// Check if the user exists
query := "SELECT * FROM users WHERE email='" + lp.mail + "'"
err := db.Query(query)
if err != nil {
log.Println(err)
return false
}
// Store the result
result, err := db.StoreResult()
if err != nil {
log.Println(err)
return false
}
if result.RowCount() == 0 {
db.FreeResult()
query = "INSERT INTO users SET email='" + lp.mail + "', password='******', isvalidated=1, "
query = query + "licensekey='" + lp.License + "'"
err = db.Query(query)
} else {
db.FreeResult()
// Update password
// TODO: check if password has changed? Add parameter in struct to know if changed or not?
query = "UPDATE users SET password='******' WHERE email='" + lp.mail + "'"
err = db.Query(query)
if err != nil {
log.Println(err)
return false
}
}
elapsed := time.Now().Sub(start)
// if elapsed > UPPER_TIME_LIMIT && *verboseFlag > 0 {
log.Printf("license.Save %d ms\n", elapsed/1e6)
// }
return true
}
// Load a license. Return nil if error.
func Load_Bl(mail string) *License {
var license License
start := time.Now()
db := ephenationdb.New()
if db == nil {
return nil
}
// Build a query for the user email sent as an argument
query := "SELECT * FROM users WHERE email='" + mail + "'"
if *verboseFlag > 0 {
log.Println(query)
}
err := db.Query(query)
if err != nil {
log.Println(err)
return nil
}
defer ephenationdb.Release(db) // Defer database release when we are sure that the db is open
// Store the result
result, err := db.UseResult()
if err != nil {
log.Println(err)
return nil
}
// Fetch row
row := result.FetchRow()
if row == nil {
if *verboseFlag > 0 {
log.Printf("No result for %v\n", mail)
}
db.FreeResult()
return nil
}
// Assign mail as license.mail
license.mail = mail
idnumfields := 0
const (
ID_MAIL = 1 << iota
ID_LIC = 1 << iota
ID_PASSW = 1 << iota
ID_LAST = 1 << iota
)
// TODO: Read more information: license type, valid until (if license terminates during connection)
for x := 0; x < int(result.FieldCount()); x++ {
FieldName := result.FetchField().Name
switch FieldName {
case "email":
license.mail = fmt.Sprint(row[x])
idnumfields |= ID_MAIL
case "licensekey":
license.License = fmt.Sprint(row[x])
idnumfields |= ID_LIC
case "password":
license.Password = fmt.Sprint(row[x])
idnumfields |= ID_PASSW
case "lastseen":
license.LastSeen = fmt.Sprintf("%s", row[x])
idnumfields |= ID_LAST
}
}
db.FreeResult() // Required to get back in sync
// If all fields have been identified, continue, otherwise return failure
if idnumfields != ID_MAIL|ID_LIC|ID_PASSW|ID_LAST {
if *verboseFlag > 0 {
log.Printf("Not proper content: %v\n", idnumfields)
}
return nil // return fail
}
// Read avatar names
query = "SELECT name FROM avatars WHERE owner='" + license.mail + "'"
if *verboseFlag > 0 {
log.Println(query)
}
err = db.Query(query)
if err != nil {
log.Println(err)
return nil
}
// Store the result
result, err = db.StoreResult()
if err != nil {
log.Println(err)
return nil
}
rowcount := result.RowCount()
temps := make([]string, rowcount)
for x := 0; x < int(rowcount); x++ {
// Fetch row
row = result.FetchRow()
if row == nil {
// TODO: Error handling
}
temps[x] = fmt.Sprint(row[0])
}
db.FreeResult()
license.Names = temps
if *verboseFlag > 0 {
log.Printf("license.Load done: %v\n", license)
elapsed := time.Now().Sub(start)
if elapsed > UPPER_TIME_LIMIT {
log.Printf("license.Load %d ms\n", elapsed/1e6)
}
}
return &license
}
func DumpSQL() {
db := ephenationdb.New()
if db == nil {
return
}
defer ephenationdb.Release(db)
// Build a query for the avatar name sent as an argument
// TODO: Assert that the avatar name is unique and on this server for the current user?
query := "SELECT name,jsonstring,id,PositionX,PositionY,PositionZ,isFlying,isClimbing,isDead,DirHor,DirVert,AdminLevel,Level,Experience,HitPoints,Mana,Kills,HomeX,HomeY,HomeZ,ReviveX,ReviveY,ReviveZ,maxchunks,BlocksAdded,BlocksRemoved,TimeOnline,HeadType,BodyType,inventory,TScoreTotal,TScoreBalance,TScoreTime,TargetX,TargetY,TargetZ FROM avatars"
stmt, err := db.Prepare(query)
if err != nil {
log.Println(err)
return
}
// Execute statement
err = stmt.Execute()
if err != nil {
log.Println(err)
return
}
// Some helper variables
var packedline string
var uid uint32
var packedInv []byte
var terrScore, terrScoreBalance float64
var terrScoreTimestamp uint32
// Booleans doesn't work
var flying, climbing, dead int
var pl player
stmt.BindResult(&pl.name, &packedline, &uid, &pl.coord.X, &pl.coord.Y, &pl.coord.Z, &flying, &climbing, &dead, &pl.dirHor, &pl.dirVert, &pl.adminLevel, &pl.level,
&pl.exp, &pl.hitPoints, &pl.mana, &pl.numKill, &pl.homeSP.X, &pl.homeSP.Y, &pl.homeSP.Z, &pl.reviveSP.X, &pl.reviveSP.Y, &pl.reviveSP.Z, &pl.maxchunks,
&pl.blockAdd, &pl.blockRem, &pl.timeOnline, &pl.head, &pl.body, &packedInv, &terrScore, &terrScoreBalance, &terrScoreTimestamp,
&pl.targetCoor.X, &pl.targetCoor.Y, &pl.targetCoor.Z)
for {
eof, err := stmt.Fetch()
if err != nil {
log.Println(err)
return
}
if eof {
break
}
// Some post processing
if flying == 1 {
pl.flying = true
}
if climbing == 1 {
pl.climbing = true
}
if dead == 1 {
pl.dead = true
}
if pl.maxchunks == -1 {
// This parameter was not initialized.
pl.maxchunks = CnfgMaxOwnChunk
}
DumpSQLPlayer(&pl, packedline, packedInv)
}
}
// Return true if ok, and the uid of the player.
// TODO: Improve error handlng
func (pl *player) Load_WLwBlWLc(name string) (uint32, bool) {
// Connect to database
db := ephenationdb.New()
if db == nil {
return 0, false
}
defer ephenationdb.Release(db)
// Build a query for the avatar name sent as an argument
// TODO: Assert that the avatar name is unique and on this server for the current user?
query := "SELECT jsonstring,id,PositionX,PositionY,PositionZ,isFlying,isClimbing,isDead,DirHor,DirVert,AdminLevel,Level,Experience,HitPoints,Mana,Kills,HomeX,HomeY,HomeZ,ReviveX,ReviveY,ReviveZ,maxchunks,BlocksAdded,BlocksRemoved,TimeOnline,HeadType,BodyType,inventory,TScoreTotal,TScoreBalance,TScoreTime,TargetX,TargetY,TargetZ FROM avatars WHERE name='" + name + "'"
stmt, err := db.Prepare(query)
if err != nil {
log.Println(err)
return 0, false
}
// Execute statement
err = stmt.Execute()
if err != nil {
log.Println(err)
return 0, false
}
// Some helper variables
var packedline string
var uid uint32
var packedInv []byte
var terrScore, terrScoreBalance float64
var terrScoreTimestamp uint32
// Booleans doesn't work
var flying, climbing, dead int
stmt.BindResult(&packedline, &uid, &pl.coord.X, &pl.coord.Y, &pl.coord.Z, &flying, &climbing, &dead, &pl.dirHor, &pl.dirVert, &pl.adminLevel, &pl.level,
&pl.exp, &pl.hitPoints, &pl.mana, &pl.numKill, &pl.homeSP.X, &pl.homeSP.Y, &pl.homeSP.Z, &pl.reviveSP.X, &pl.reviveSP.Y, &pl.reviveSP.Z, &pl.maxchunks,
&pl.blockAdd, &pl.blockRem, &pl.timeOnline, &pl.head, &pl.body, &packedInv, &terrScore, &terrScoreBalance, &terrScoreTimestamp,
&pl.targetCoor.X, &pl.targetCoor.Y, &pl.targetCoor.Z)
for {
eof, err := stmt.Fetch()
if err != nil {
log.Println(err)
return 0, false
}
if eof {
break
}
}
// log.Println(pl.targetCoor)
// Some post processing
pl.name = name
if flying == 1 {
pl.flying = true
}
if climbing == 1 {
pl.climbing = true
}
if dead == 1 {
pl.dead = true
}
if pl.maxchunks == -1 {
// This parameter was not initialized.
pl.maxchunks = CnfgMaxOwnChunk
}
pl.logonTimer = time.Now()
if err = json.Unmarshal([]uint8(packedline), pl); err != nil {
log.Printf("Unmarshal player %s: %v (%v)\n", name, err, packedline)
// TODO: This covers errors when updating the jsonstring, should be handled in a more approperiate way
//return 0, false
}
// If there was data in the inventory "blob", unpack it.
if len(packedInv) > 0 {
err = pl.inventory.Unpack([]byte(packedInv))
if err != nil {
log.Println("Failed to unpack", err, packedInv)
}
// Save what can be saved, and remove unknown objects.
pl.inventory.CleanUp()
}
if *verboseFlag > 1 {
log.Println("Inventory unpacked", pl.inventory)
}
//fmt.Printf("Coord: (%v,%v,%v)\n", pl.coord.X, pl.coord.Y, pl.coord.Z )
if pl.reviveSP.X == 0 && pl.reviveSP.Y == 0 && pl.reviveSP.Z == 0 {
// Check if there is any spawn point defined.
pl.reviveSP = pl.coord
pl.homeSP = pl.coord
}
// Load the allocated territories. This is loaded every time a player logs in, but not at logout or player save.
// It will however be updated immediately when the player changes his allocation.
terr, ok := chunkdb.ReadAvatar_Bl(uint32(uid))
if !ok {
return 0, false
}
pl.territory = terr
score.Initialize(uid, terrScore, terrScoreBalance, terrScoreTimestamp, name, len(terr))
// fmt.Printf("User: %#v\n", pl)
return uint32(uid), true
}
// TODO: Improve error handling
// Return true if the save succeeded.
func (pl *player) Save_Bl() bool {
// Connect to database
start := time.Now()
db := ephenationdb.New()
if db == nil {
return false
}
// Do some preprocessing
b, err := json.Marshal(pl)
if err != nil {
log.Printf("Marshal %s returned %v\n", pl.name, err)
}
inventory, err := pl.inventory.Serialize()
// Update last seen online
now := time.Now()
nowstring := fmt.Sprintf("%4v-%02v-%02v", now.Year(), int(now.Month()), now.Day())
// Update total time online, in seconds
TempTimer := time.Now()
pl.timeOnline += uint32(TempTimer.Sub(pl.logonTimer) / time.Second)
//fmt.Printf("User has been online %v seconds\n", TempTimer - pl.logonTimer)
pl.logonTimer = TempTimer // Use the stored value to avoid mismatch
// Write data on alternative format
// This section makes the following assumptions:
// - Avatar name cannot be changed from the server
// - Avatar looks (head/body) cannot be changed from the server TODO: THIS WILL EVENTUALLY NOT BE TRUE!
// Booleans doesn't work, transform them to numbers
query := "UPDATE avatars SET jsonstring=?,PositionX=?,PositionY=?,PositionZ=?,isFlying=?,isClimbing=?,isDead=?,DirHor=?,DirVert=?,AdminLevel=?" +
",Level=?,Experience=?,HitPoints=?,Mana=?,Kills=?,HomeX=?,HomeY=?,HomeZ=?,ReviveX=?,ReviveY=?,ReviveZ=?" +
",maxchunks=?,BlocksAdded=?,BlocksRemoved=?,TimeOnline=?,HeadType=?,BodyType=?,lastseen=?,inventory=?,TargetX=?,TargetY=?,TargetZ=?" +
" WHERE name='" + pl.name + "'"
stmt, err := db.Prepare(query)
if err != nil {
log.Println(err)
return false
}
stmt.BindParams(b, pl.coord.X, pl.coord.Y, pl.coord.Z, Q(pl.flying), Q(pl.climbing), Q(pl.dead), pl.dirHor, pl.dirVert, pl.adminLevel, pl.level,
pl.exp, pl.hitPoints, pl.mana, pl.numKill, pl.homeSP.X, pl.homeSP.Y, pl.homeSP.Z, pl.reviveSP.X, pl.reviveSP.Y, pl.reviveSP.Z, pl.maxchunks,
pl.blockAdd, pl.blockRem, pl.timeOnline, pl.head, pl.body, nowstring, inventory, pl.targetCoor.X, pl.targetCoor.Y, pl.targetCoor.Z)
err = stmt.Execute()
if err != nil {
log.Println(err)
return false
}
ephenationdb.Release(db)
if *verboseFlag > 1 {
log.Printf("up.Save_Bl saved %v\n", pl.name)
}
elapsed := time.Now().Sub(start)
if *verboseFlag > 0 {
log.Printf("up.Save_Bl elapsed %d ms\n", elapsed/1e6)
}
return true
}