Register to post in forums, or Log in to your existing account
 

Play RetroMUD
Post new topic  Reply to topic     Home » Forums » CMUD Beta Forum
samio1221@gmail.com
Newbie


Joined: 02 Mar 2020
Posts: 4

PostPosted: Mon Mar 02, 2020 6:46 am   

CMUD Map Zone Export / Import Script
 
I made a script that extracts and imports zones (zone info, room/objects, and exitlinks) into another map. I used this when remapping the grid on my map.
It is a python script, so you need to have python installed and/or be somewhat familiar with how to run it. I would also recommend installing sqlite3 or some sqlite3 browser so you can confirm the data.
And of course always backup your data and confirm the contents in between transfers.

It takes 3 params in the __main__:
1. old map db
2. new map db
3. list of zone nums to import from old map db

I just had it import about 20 or 30 zones consisting of about 10,000 rooms, and it took about 5 or 10 minutes.
If you mark placeholder zones on the new map named as "Available", it will prioritize using those zones and recycle the rooms/links in those zones first before creating new unique entries.
I've pasted the code here if you want to try it.

Code:


import sqlite3
from sqlite3 import Error

def ZoneTbl_EXE():
global zoneName
global newZoneID
zoneDict={}
roomDict={}
exitDict={}
""" Create database connection """
selectItems="*"
ZoneQuery = "SELECT "+selectItems+" FROM ZoneTbl WHERE ZoneID='"+str(ZoneID)+"'"
print("oldDB: ZoneQuery: ",ZoneQuery)

"""Grabbing oldDB ZoneTbl info"""
try:
oldDBCon = sqlite3.connect(oldDB)
cur = oldDBCon.cursor()
cur.execute(ZoneQuery)
rows = cur.fetchall()
header = [description[0] for description in cur.description]
for rownum in range(len(rows)):
for i in range(len(rows[rownum])):
zoneDict[header[i]]=rows[rownum][i]
zoneName=zoneDict['Name']
#print(zoneDict)
except Error:
print(Error)
finally:
oldDBCon.close()

"""Checking if zoneName exists. If not, create it"""
try:
ZoneQuery = 'Select * from ZoneTbl WHERE Name = "'+zoneName+'"'
print("Zone Check: '"+zoneName+"'. NewDB Query: "+ZoneQuery)
zoneCon = sqlite3.connect(newDB)
objCon = sqlite3.connect(newDB)
exitCon = sqlite3.connect(newDB)
zoneCur = zoneCon.cursor()
zoneCur.execute(ZoneQuery)
rows = zoneCur.fetchall()

"""if (len(rows)<=) then the zone doesn't exist: proceed to create it"""
if (int(len(rows))<=0):
"""If newDB has 'Available' placeholder zones, use that zone instead of creating new zone"""
ZoneQuery = 'Select [ZoneId] FROM ZoneTbl WHERE Name = "'+"Available"+'"'
print("Zone Check: '"+zoneName+"'. Doesn't exist. Checking for unused 'Available' zones. NewDB Query: "+ZoneQuery)
zoneCur.execute(ZoneQuery)
zones = zoneCur.fetchall()
zoneHeader = [description[0] for description in zoneCur.description]
if (int(len(zones))>0):
print("Available zones detected...")
#print("zoneHeader: ",zoneHeader)
#print("zones: ",zones)
for zone in zones:
zoneDict={}
#print("Zone: ",zone)
for z in range(len(zone)):
zoneDict[zoneHeader[z]]=zone[z]
newZoneID=zoneDict['ZoneId']

#Update all associated entries in ObjectTbl to have Name='Available'
ObjectQuery = 'SELECT [ObjId] FROM ObjectTbl WHERE ZoneId = "'+str(zoneDict['ZoneId'])+'"'
print("ObjectQuery: ",ObjectQuery)
objCur = objCon.cursor()
objCur.execute(ObjectQuery)
rooms=objCur.fetchall()
roomHeader = [description[0] for description in objCur.description]
for room in rooms:
for r in range(len(room)):
roomDict[roomHeader[r]]=room[r]
ObjectQuery='UPDATE ObjectTbl SET Name="'+"Available"+'" WHERE ObjId = "'+str(roomDict['ObjId'])+'"'
print("ObjectQuery: ",ObjectQuery)
objCur.execute(ObjectQuery)
objCon.commit()

#Update all associated entries in ExitTbl to have Name='Available'
ExitQuery='SELECT [ExitId] FROM ExitTbl WHERE FromID="'+str(roomDict['ObjId'])+'"'
print("ExitQuery: ",ExitQuery)
exitCur = exitCon.cursor()
exitCur.execute(ExitQuery)
exits=exitCur.fetchall()
exitHeader = [description[0] for description in exitCur.description]
for exit in exits:
exitDict={}
for e in range(len(exit)):
exitDict[exitHeader[e]]=exit[e]
ExitQuery='UPDATE ExitTbl SET Name="'+"Available"+'" WHERE FromID="'+str(roomDict['ObjId'])+'" AND ExitId="'+str(exitDict['ExitId'])+'"'
print("ExitQuery: ",ExitQuery)
exitCur.execute(ExitQuery)
exitCon.commit()

ZoneQuery = 'UPDATE ZoneTBL SET Name="'+str(zoneName)+'" WHERE ZoneId="'+str(zoneDict['ZoneId'])+'"'
print("ZoneQuery : ",ZoneQuery)
zoneCur.execute(ZoneQuery)
zoneCon.commit()
#break
else:
InsertStatement = 'INSERT INTO ZoneTBL (Name) VALUES ("'+zoneName+'")'
print("Creating new zone into newDB: ",InsertStatement)
zoneCur.execute(InsertStatement)
zoneCon.commit()
newZoneID=zoneCur.lastrowid
print("New Zone created. NewZoneID:",newZoneID)
print("New ZoneID: ",newZoneID)
else:
print("ZoneName: ",zoneName," already exists!")

except Error:
print(Error)
finally:
oldDBCon.close()
zoneCon.close()
objCon.close()
exitCon.close()


def ObjectTbl_EXE():
selectItems="ZoneID,ObjId,Name,IDName,X,Y,Z,Dx,Dy,ExitX,ExitY,ExitZ,Cost,Color,LabelDir"
itemDict={}
items=[]
header=[]
newRoomNumsNeeded=0
newRoomNums=[]
nextNewObjID=0

#Retrieve data from oldDB
ObjectQuery = "SELECT "+selectItems+" FROM ObjectTbl WHERE ZoneID='"+str(ZoneID)+"'"
try:
objCon = sqlite3.connect(oldDB)
objCur = objCon.cursor()
objCur.execute(ObjectQuery)
rows = objCur.fetchall()
ObjectTblHeader = [description[0] for description in objCur.description]

#Create dictionary for each row result, and add to items list
for row in rows:
#maintain list of old room nums
for i in range(len(row)):
itemDict[ObjectTblHeader[i]]=row[i]
oldRoomNums.append(itemDict['ObjId'])
items.append(itemDict.copy())
itemDict.clear()
#print("--------------------------------------------------------")
except Error:
print(Error)
finally:
objCon.close()

#Identify Available ObjId's
#Available ObjId's in newRoomNums list. If not enough, then nextNewObjID will be >0
newRoomNumsNeeded=len(oldRoomNums)
try:
ObjectQuery = 'Select [ObjId] FROM ObjectTbl WHERE Name = "'+"Available"+'"'
print("ObjectTbl Check: Checking for unused 'Available' objects. NewDB Query: "+ObjectQuery)
objCon = sqlite3.connect(newDB)
objCur = objCon.cursor()
objCur.execute(ObjectQuery)
availRooms = objCur.fetchall()
roomHeader = [description[0] for description in objCur.description]

if (int(len(availRooms))>0):
print("roomHeader: ",roomHeader)
print("rooms: ",availRooms)
for room in availRooms:
itemDict={}
#print("Zone: ",zone)
for r in range(len(room)):
itemDict[roomHeader[r]]=room[r]
newRoomNums.append(itemDict['ObjId'])
newRoomNumsNeeded=newRoomNumsNeeded-1
if newRoomNumsNeeded==0:
break
if newRoomNumsNeeded>0:
"""Create dummy entry in newDB's ObjectTbl, without db.commit(), to identify next autoincrement ID keynum"""
ObjectQuery="INSERT INTO OBJECTTBL (Name) VALUES ("+'"'+"DummyName"+'"'+")"
objCur.execute(ObjectQuery)
nextNewObjID=objCur.lastrowid
print(zoneName + " | " + str(newZoneID) + " | " + "NextNewObjID: " + str(nextNewObjID))
except Error:
print(Error)
finally:
objCon.close()


#Create dictionary for old/new room lookup
AvailRooms=newRoomNums.copy()
for i in range(len(oldRoomNums)):
oldRoomNum=oldRoomNums[i]
if len(newRoomNums):
newRoomNum=newRoomNums.pop(0)
else:
newRoomNum=nextNewObjID+i-len(AvailRooms)
roomLookup_byOldRoom[str(oldRoomNum)] = newRoomNum
print("List of oldRoomNums:",oldRoomNums)
print("RoomLookup_byOldRoom Dict:",roomLookup_byOldRoom)
print("----------------------------------------------------------------------------------------------------------------")


"""Create SQL INSERT/UPDATE statements"""
newRoomNums=AvailRooms.copy()
print("AvailRooms: ",AvailRooms)
Query=""
for item in items:
#print("Item: ",item," || ObjId: ",item['ObjId'])
if roomLookup_byOldRoom[str(item['ObjId'])] in AvailRooms:
Query = "UPDATE ObjectTbl SET "
else:
Query = "INSERT INTO ObjectTbl (" + ",".join(ObjectTblHeader) + ")"
Query = Query + " VALUES ("
#comma separated

for key in item:
#comma separated
if "UPDATE" in Query:
if Query[-1]!=" ":
Query=Query+","
else:
if Query[-1]!="(":
Query=Query+","

if "UPDATE" in Query:
Query = Query + str(key) + '='

"""Insert using new values"""
if key=="ZoneID":
Query=Query+str(newZoneID)
elif key=="ObjId":
oldRoomNum=item[key]
Query=Query+str(roomLookup_byOldRoom[str(oldRoomNum)])
elif key in ["Name","IDName"]:
Query=Query+'"'+str(item[key])+'"'
elif item[key]:
Query=Query+str(item[key])
elif key in ['X','Y','Z','Dx','Dy','ExitX','ExitY','ExitZ']:
Query=Query+"0"
else:
Query=Query+'""'

if "UPDATE" in Query:
Query=Query+' WHERE ObjId="'+str(roomLookup_byOldRoom[str(item['ObjId'])])+'"'
else:
Query = Query + ");"

print(Query)
try:
objCon = sqlite3.connect(newDB)
objCur = objCon.cursor()
objCur.execute(Query)
objCon.commit()
except Error:
print(Error)
finally:
objCon.close()

print("----------------------------------------------------------------------------------------------------------------")



def ExitTbl_EXE():
selectItems="ExitId,ExitIdTo,FromID,ToID,ExitKindID,Name,DirType,DirToType,Flags,UserID"
#selectItems="*"
"""list of dictionaries containing results"""
exits=[]
"""dictionary for individual row results"""
exitItem={}
exitDict={}
queryValues=[]
newExitNums=[]

#Calculate how many exits are needed
for fromID in roomLookup_byOldRoom:
ExitQuery = "SELECT "+selectItems+" FROM ExitTbl WHERE FromID='"+fromID+"'"
try:
exitCon = sqlite3.connect(oldDB)
exitCur = exitCon.cursor()
exitCur.execute(ExitQuery)
rows = exitCur.fetchall()
exitHeader = [description[0] for description in exitCur.description]

#Create dictionary for each row result, and add to items list
for row in rows:
for i in range(len(row)):
exitItem[exitHeader[i]]=row[i]
oldExitNums.append(exitItem['ExitId'])
exits.append(exitItem.copy())
exitItem.clear()
#print("----------------------------------------------------------------------------------------------------------------")
except Error:
print(Error)
finally:
exitCon.close()
numExits = len(exits)
numExitsNeeded=numExits
#Calculate how many 'Available' exitid's there are.
#Identify Available ObjId's
#Available ObjId's in newRoomNums list. If not enough, then nextNewObjID will be >0
try:
ExitQuery = 'Select [ExitId] FROM ExitTbl WHERE Name = "'+"Available"+'"'
print("ExitTbl Check: Checking for unused 'Available' exits. NewDB Query: "+ExitQuery)
exitCon = sqlite3.connect(newDB)
exitCur = exitCon.cursor()
exitCur.execute(ExitQuery)
availExits = exitCur.fetchall()
header = [description[0] for description in exitCur.description]

if (int(len(availExits))>0):
print("header: ",header)
print("availExits: ",availExits)
for exit in availExits:
exitDict={}
for e in range(len(exit)):
exitDict[header[e]]=exit[e]
newExitNums.append(exitDict['ExitId'])
numExitsNeeded=numExitsNeeded-1
if numExitsNeeded==0:
break
if numExitsNeeded>0:
ExitQuery="INSERT INTO ExitTbl (Name) VALUES ("+'"'+"DummyName"+'"'+")"
exitCon = sqlite3.connect(newDB)
exitCur = exitCon.cursor()
exitCur.execute(ExitQuery)
nextNewExitID=exitCur.lastrowid
print(zoneName + " | " + str(newZoneID) + " | " + "nextNewExitID: " + str(nextNewExitID))
except Error:
print(Error)
finally:
exitCon.close()

#Create dictionary for old/new room lookup
AvailExits=newExitNums.copy()
for i in range(len(oldExitNums)):
oldExitNum=oldExitNums[i]
if len(newExitNums):
newExitNum=newExitNums.pop(0)
else:
newExitNum=nextNewExitID+i-len(AvailExits)
exitLookup_byOldRoom[str(oldExitNum)] = newExitNum
print("List of oldExitNums:",oldExitNums)
print("exitLookup_byOldRoom Dict:",exitLookup_byOldRoom)
print("----------------------------------------------------------------------------------------------------------------")


"""
#Create old/new lookup dictionary
for i in range(len(oldExitNums)):
oldExit=oldExitNums[i]
exitLookup_byOldRoom[str(oldExit)]=int(nextNewExitID)+i
exitLookup_byNewRoom[int(nextNewExitID)+i]=str(oldExit)
"""

"""Create SQL INSERT statements"""

"""Create SQL INSERT/UPDATE statements"""
newExitNums=AvailExits.copy()
print("AvailExits: ",AvailExits)
Query=""
for exit in exits:
#print("Item: ",item," || ObjId: ",item['ObjId'])
if exitLookup_byOldRoom[str(exit['ExitId'])] in AvailExits:
Query = "UPDATE ExitTbl SET "
else:
Query = "INSERT INTO ExitTbl (" + ",".join(exitHeader) + ")"
Query = Query + " VALUES ("
#comma separated

for key in exit:
#comma separated
if "UPDATE" in Query:
if Query[-1]!=" ":
Query=Query+","
else:
if Query[-1]!="(":
Query=Query+","

if "UPDATE" in Query:
Query = Query + str(key) + '='

#Insert using new values
if key=="ExitId":
Query=Query+str(exitLookup_byOldRoom[str(exit[key])])
elif key=="ExitIdTo" and str(exit[key]) in exitLookup_byOldRoom.keys():
Query=Query+str(exitLookup_byOldRoom[str(exit[key])])
elif key=="ExitIdTo" and str(exit[key]) not in exitLookup_byOldRoom.keys():
Query=Query+str(exitLookup_byOldRoom[str(exit['ExitId'])])
elif key=="FromID":
Query=Query+str(roomLookup_byOldRoom[str(exit[key])])
elif key=="ToID" and str(exit[key]) in roomLookup_byOldRoom.keys():
Query=Query+str(roomLookup_byOldRoom[str(exit[key])])
elif key=="ToID" and str(exit[key]) not in roomLookup_byOldRoom.keys():
Query=Query+str(roomLookup_byOldRoom[str(exit['FromID'])])
elif key=="Name":
Query=Query+'"'+str(exit[key])+'"'
else:
Query=Query+str(exit[key])

if "UPDATE" in Query:
Query=Query+' WHERE ExitId="'+str(exitLookup_byOldRoom[str(exit['ExitId'])])+'"'
else:
Query = Query + ");"

print(Query)
try:
exitCon = sqlite3.connect(newDB)
exitCur = exitCon.cursor()
exitCur.execute(Query)
exitCon.commit()
except Error:
print(Error)
finally:
exitCon.close()
print("----------------------------------------------------------------------------------------------------------------")



if __name__ == '__main__':
oldDB="Full path to /MyMudOld.dbm"
newDB="Full path to /MyMudNew.dbm"
zoneList=[]
param="print"
zoneName=""
newZoneID=0
oldRoomNums=[]
roomLookup_byOldRoom={}
roomLookup_byNewRoom={}

oldExitNums=[]
exitLookup_byOldRoom={}
exitLookup_byNewRoom={}

#List of Zones
#zoneList.extend([51,533,496,528])


print("----------------------------------------------------------------------------------------------------------------")
print("Transferring zones: ",zoneList)
for ZoneID in zoneList:
zoneName=""
newZoneID=0
oldRoomNums=[]
roomLookup_byOldRoom={}
roomLookup_byNewRoom={}

oldExitNums=[]
exitLookup_byOldRoom={}
exitLookup_byNewRoom={}

ZoneTbl_EXE()
print("Global newZoneID: ",newZoneID)

#"""
print("----------------------------------------------------------------------------------------------------------------")
if newZoneID>0:
ObjectTbl_EXE()
print("----------------------------------------------------------------------------------------------------------------")
ExitTbl_EXE()
print("----------------------------------------------------------------------------------------------------------------")
else:
print("ObjectTbl & ExitTbl Queries skipped because ZoneName already exists!> "+zoneName)
print("----------------------------------------------------------------------------------------------------------------")
#"""

print("Zone Transfer Complete!")
print("----------------------------------------------------------------------------------------------------------------")
Reply with quote
samio1221@gmail.com
Newbie


Joined: 02 Mar 2020
Posts: 4

PostPosted: Tue Mar 03, 2020 2:03 am   Code Updates
 
Cleaned the code up a bit, and fixed some bugs where it was not properly vacuuming and re-using old 'Available' keys.

Code:

import sqlite3
from sqlite3 import Error

def Tables_MarkAvailable():
    zoneDict={}
    roomDict={}
    exitDict={}
    zoneCon = sqlite3.connect(newDB)
    objCon = sqlite3.connect(newDB)
    exitCon = sqlite3.connect(newDB)
    try:
        ZoneQuery = 'Select [ZoneId] FROM ZoneTbl WHERE Name = "'+"Available"+'"'
        print("Zone Check: '"+zoneName+"'. Doesn't exist. Checking for unused 'Available' zones. NewDB Query: "+ZoneQuery)
        zoneCur = zoneCon.cursor()
        zoneCur.execute(ZoneQuery)
        zones = zoneCur.fetchall()
        zoneHeader = [description[0] for description in zoneCur.description]
        if (int(len(zones))>0):
            print("Available zones detected...")
            #print("zoneHeader: ",zoneHeader)
            #print("zones: ",zones)
            for zone in zones:
                zoneDict={}
                #print("Zone: ",zone)
                for z in range(len(zone)):
                    zoneDict[zoneHeader[z]]=zone[z]

                #Update all associated entries in ObjectTbl to have Name='Available'
                ObjectQuery = 'SELECT [ObjId] FROM ObjectTbl WHERE ZoneId = "'+str(zoneDict['ZoneId'])+'"'
                print("ObjectQuery: ",ObjectQuery)
                objCur = objCon.cursor()
                objCur.execute(ObjectQuery)
                rooms=objCur.fetchall()
                roomHeader = [description[0] for description in objCur.description]
                for room in rooms:
                    for r in range(len(room)):
                        roomDict[roomHeader[r]]=room[r]
                    ObjectQuery='UPDATE ObjectTbl SET Name="'+"Available"+'",ZoneID="0",RefNum="0",fKey="0",X="0",Y="0" WHERE ObjId = "'+str(roomDict['ObjId'])+'"'
                    print("ObjectQuery: ",ObjectQuery)
                    objCur.execute(ObjectQuery)
                    objCon.commit()

                    #Update all associated entries in ExitTbl to have Name='Available'
                    ExitQuery='SELECT [ExitId] FROM ExitTbl WHERE FromID="'+str(roomDict['ObjId'])+'"'
                    print("ExitQuery: ",ExitQuery)
                    exitCur = exitCon.cursor()
                    exitCur.execute(ExitQuery)
                    exits=exitCur.fetchall()
                    exitHeader = [description[0] for description in exitCur.description]
                    for exit in exits:
                        exitDict={}
                        for e in range(len(exit)):
                            exitDict[exitHeader[e]]=exit[e]
                        ExitQuery='UPDATE ExitTbl SET Name="Available",ExitIdTo="0",FromID="0",ToID="0",ExitKindID="0",DirType="0",DirToType="0" WHERE ExitId="'+str(exitDict['ExitId'])+'"'
                        print("ExitQuery: ",ExitQuery)
                        exitCur.execute(ExitQuery)
                        exitCon.commit()
    except Error:
        print(Error)
    finally:
        zoneCon.close()
        objCon.close()
        exitCon.close()


def ZoneTbl_EXE():
    global zoneName
    global newZoneID
    zoneDict={}
    availZoneDict={}
    oldDBCon = sqlite3.connect(oldDB)
    zoneCon = sqlite3.connect(newDB)
    objCon = sqlite3.connect(newDB)
    exitCon = sqlite3.connect(newDB)
    selectItems="Name,X,Y,Z,Dx,Dy,MinX,MinY,MinZ,MaxX,MaxY,MaxZ,XScale,YScale,XOffset,YOffset"
    ZoneQuery = "SELECT "+selectItems+" FROM ZoneTbl WHERE ZoneID='"+str(ZoneID)+"'"
    print("oldDB: ZoneQuery: ",ZoneQuery)

    #Grabbing oldDB ZoneTbl info
    try:
        oldCur = oldDBCon.cursor()
        oldCur.execute(ZoneQuery)
        rows = oldCur.fetchall()
        zoneHeader = [description[0] for description in oldCur.description]
        for rownum in range(len(rows)):
            for i in range(len(rows[rownum])):
                zoneDict[zoneHeader[i]]=rows[rownum][i]
        zoneName=zoneDict['Name']
        #print(zoneDict)
    except Error:
        print(Error)
    finally:
        oldDBCon.close()

    #Checking if zoneName exists. If not, create it
    try:
        ZoneQuery = 'Select * from ZoneTbl WHERE Name = "'+zoneName+'"'
        print("Zone Check: '"+zoneName+"'. NewDB Query: "+ZoneQuery)
        zoneCur = zoneCon.cursor()
        zoneCur.execute(ZoneQuery)
        rows = zoneCur.fetchall()

        #if (len(rows)<=0) then the zone doesn't exist: proceed to create it
        if (int(len(rows))<=0):
            #If newDB has 'Available' placeholder zones, use that zone instead of creating new zone
            ZoneQuery = 'Select [ZoneId] FROM ZoneTbl WHERE Name = "'+"Available"+'"'
            print("Zone Check: '"+zoneName+"'. Doesn't exist. Checking for unused 'Available' zones. NewDB Query: "+ZoneQuery)
            zoneCur.execute(ZoneQuery)
            zones = zoneCur.fetchall()
            header = [description[0] for description in zoneCur.description]
            if (int(len(zones))>0):
                print("Available zones detected...")
                #print("zoneHeader: ",zoneHeader)
                #print("zones: ",zones)
                for zone in zones:
                    availZoneDict={}
                    #print("Zone: ",zone)
                    for z in range(len(zone)):
                        availZoneDict[header[z]]=zone[z]
                    newZoneID=availZoneDict['ZoneId']
                    Query = "UPDATE ZoneTbl SET "
                    break
            else:
                newZoneID=zoneCur.lastrowid
                Query = "INSERT INTO ZoneTbl (" + ",".join(zoneHeader) + ")"
                Query = Query + " VALUES ("
            print("New ZoneID assigned: ",newZoneID)

            #-------
            for key in zoneDict:
                if "UPDATE" in Query:
                    if Query[-1]!=" ":
                        Query=Query+","
                else:
                    if Query[-1]!="(":
                        Query=Query+","
                if "UPDATE" in Query:
                    Query = Query + str(key) + '='

                """Insert using new values"""
                #selectItems="Name,X,Y,Z,Dx,Dy,MinX,MinY,MinZ,MaxX,MaxY,MaxZ,XScale,YScale,XOffset,YOffset"
                if key == "Name":
                    Query=Query+'"'+str(zoneDict[key])+'"'
                elif key=="fKey":
                    Query=Query+"0"
                elif zoneDict[key] is not None:
                    Query=Query+str(zoneDict[key])
                elif key in ['X','Y','Z','Dx','Dy','MinX','MinY','MinZ','MaxX','MaxY','MaxZ','XScale','YScale','XOffset','YOffset']:
                    Query=Query+"0"
                else:
                    Query=Query+'""'

            if "UPDATE" in Query:
                Query=Query+' WHERE ZoneId="'+str(newZoneID)+'"'
            else:
                Query = Query + ");"

            print("ZoneQuery : ",Query)
            zoneCur.execute(Query)
            zoneCon.commit()
            #--------
        else:
            print("ZoneName: ",zoneName," already exists!")

    except Error:
        print(Error)
    finally:
        oldDBCon.close()
        zoneCon.close()
        objCon.close()
        exitCon.close()


def ObjectTbl_EXE():
    selectItems="ZoneID,ObjId,Name,IDName,fKey,X,Y,Z,Dx,Dy,ExitX,ExitY,ExitZ,Color,LabelDir"
    itemDict={}
    items=[]
    header=[]
    newRoomNumsNeeded=0
    newRoomNums=[]
    nextNewObjID=0

    #Retrieve data from oldDB
    ObjectQuery = "SELECT "+selectItems+" FROM ObjectTbl WHERE ZoneID='"+str(ZoneID)+"'"
    try:
        objCon = sqlite3.connect(oldDB)
        objCur = objCon.cursor()
        objCur.execute(ObjectQuery)
        rows = objCur.fetchall()
        ObjectTblHeader = [description[0] for description in objCur.description]

        #Create dictionary for each row result, and add to items list
        for row in rows:
            #maintain list of old room nums
            for i in range(len(row)):
                itemDict[ObjectTblHeader[i]]=row[i]
            oldRoomNums.append(itemDict['ObjId'])
            items.append(itemDict.copy())
            itemDict.clear()
        #print("--------------------------------------------------------")
    except Error:
        print(Error)
    finally:
        objCon.close()

    #Identify Available ObjId's
    #Available ObjId's in newRoomNums list. If not enough, then nextNewObjID will be >0
    newRoomNumsNeeded=len(oldRoomNums)
    try:
        ObjectQuery = 'Select [ObjId] FROM ObjectTbl WHERE Name = "'+"Available"+'"'
        print("ObjectTbl Check: Checking for unused 'Available' objects. NewDB Query: "+ObjectQuery)
        objCon = sqlite3.connect(newDB)
        objCur = objCon.cursor()
        objCur.execute(ObjectQuery)
        availRooms = objCur.fetchall()
        roomHeader = [description[0] for description in objCur.description]

        if (int(len(availRooms))>0):
            #print("roomHeader: ",roomHeader)
            #print("rooms: ",availRooms)
            for room in availRooms:
                itemDict={}
                #print("Zone: ",zone)
                for r in range(len(room)):
                    itemDict[roomHeader[r]]=room[r]
                newRoomNums.append(itemDict['ObjId'])
                newRoomNumsNeeded=newRoomNumsNeeded-1
                if newRoomNumsNeeded==0:
                    break
        if newRoomNumsNeeded>0:
            #Create dummy entry in newDB's ObjectTbl, without db.commit(), to identify next autoincrement ID keynum
            ObjectQuery="INSERT INTO OBJECTTBL (Name) VALUES ("+'"'+"DummyName"+'"'+")"
            objCur.execute(ObjectQuery)
            nextNewObjID=objCur.lastrowid
            print(zoneName + " | " + str(newZoneID) + " | " + "NextNewObjID: " + str(nextNewObjID))
    except Error:
        print(Error)
    finally:
        objCon.close()


    #Create dictionary for old/new room lookup
    AvailRooms=newRoomNums.copy()
    for i in range(len(oldRoomNums)):
        oldRoomNum=oldRoomNums[i]
        if len(newRoomNums):
            newRoomNum=newRoomNums.pop(0)
        else:
            newRoomNum=nextNewObjID+i-len(AvailRooms)
        roomLookup_byOldRoom[str(oldRoomNum)] = newRoomNum
    #print("List of oldRoomNums:",oldRoomNums)
    #print("RoomLookup_byOldRoom Dict:",roomLookup_byOldRoom)
    print("----------------------------------------------------------------------------------------------------------------")


    """Create SQL INSERT/UPDATE statements"""
    newRoomNums=AvailRooms.copy()
    #print("AvailRooms: ",AvailRooms)
    Query=""
    for item in items:
        #print("Item: ",item," || ObjId: ",item['ObjId'])
        if roomLookup_byOldRoom[str(item['ObjId'])] in AvailRooms:
            Query = "UPDATE ObjectTbl SET "
        else:
            Query = "INSERT INTO ObjectTbl (" + ",".join(ObjectTblHeader) + ")"
            Query = Query + " VALUES ("
            #comma separated

        for key in item:
            #comma separated
            if "UPDATE" in Query:
                if Query[-1]!=" ":
                    Query=Query+","
            else:
                if Query[-1]!="(":
                    Query=Query+","

            if "UPDATE" in Query:
                Query = Query + str(key) + '='

            """Insert using new values"""
            if key=="ZoneID":
                Query=Query+str(newZoneID)
            elif key=="ObjId":
                oldRoomNum=item[key]
                Query=Query+str(roomLookup_byOldRoom[str(oldRoomNum)])
            elif key in ["Name","IDName"]:
                Query=Query+'"'+str(item[key])+'"'
            elif key=="fKey":
                Query=Query+"0"
            elif item[key] is not None:
                Query=Query+str(item[key])
            elif key in ['X','Y','Z','Dx','Dy','ExitX','ExitY','ExitZ']:
                Query=Query+"0"
            else:
                Query=Query+'""'

        if "UPDATE" in Query:
            Query=Query+' WHERE ObjId="'+str(roomLookup_byOldRoom[str(item['ObjId'])])+'"'
        else:
            Query = Query + ");"

        print(Query)
        try:
            objCon = sqlite3.connect(newDB)
            objCur = objCon.cursor()
            objCur.execute(Query)
            objCon.commit()
        except Error:
            print(Error)
        finally:
            objCon.close()

    print("----------------------------------------------------------------------------------------------------------------")



def ExitTbl_EXE():
    selectItems="ExitId,ExitIdTo,FromID,ToID,ExitKindID,Name,DirType,DirToType,Flags,UserID"
    #selectItems="*"
    """list of dictionaries containing results"""
    exits=[]
    """dictionary for individual row results"""
    exitItem={}
    exitDict={}
    queryValues=[]
    newExitNums=[]

    #Calculate how many exits are needed
    for fromID in roomLookup_byOldRoom:
        ExitQuery = "SELECT "+selectItems+" FROM ExitTbl WHERE FromID='"+fromID+"'"
        try:
            exitCon = sqlite3.connect(oldDB)
            exitCur = exitCon.cursor()
            exitCur.execute(ExitQuery)
            rows = exitCur.fetchall()
            exitHeader = [description[0] for description in exitCur.description]

            #Create dictionary for each row result, and add to items list
            for row in rows:
                for i in range(len(row)):
                    exitItem[exitHeader[i]]=row[i]
                oldExitNums.append(exitItem['ExitId'])
                exits.append(exitItem.copy())
                exitItem.clear()
            #print("----------------------------------------------------------------------------------------------------------------")
        except Error:
            print(Error)
        finally:
            exitCon.close()
    numExits = len(exits)
    numExitsNeeded=numExits
    print("numExitsNeeded: ",numExitsNeeded)
    #Calculate how many 'Available' exitid's there are.
    #Identify Available ObjId's
    #Available ObjId's in newRoomNums list. If not enough, then nextNewObjID will be >0
    try:
        ExitQuery = 'Select [ExitId] FROM ExitTbl WHERE Name = "'+"Available"+'"'
        print("ExitQuery: "+ExitQuery)
        exitCon = sqlite3.connect(newDB)
        exitCur = exitCon.cursor()
        exitCur.execute(ExitQuery)
        availExits = exitCur.fetchall()
        header = [description[0] for description in exitCur.description]

        if (int(len(availExits))>0):
            print("'Available' exits found in ExitTbl...")
            #print("header: ",header)
            #print("availExits: ",availExits)
            for exit in availExits:
                exitDict={}
                for e in range(len(exit)):
                    exitDict[header[e]]=exit[e]
                newExitNums.append(exitDict['ExitId'])
                numExitsNeeded=numExitsNeeded-1
                if numExitsNeeded==0:
                    break
        if numExitsNeeded>0:
            ExitQuery="INSERT INTO ExitTbl (Name) VALUES ("+'"'+"DummyName"+'"'+")"
            exitCon = sqlite3.connect(newDB)
            exitCur = exitCon.cursor()
            exitCur.execute(ExitQuery)
            nextNewExitID=exitCur.lastrowid
            print(zoneName + " | " + str(newZoneID) + " | " + "nextNewExitID: " + str(nextNewExitID))
    except Error:
        print(Error)
    finally:
        exitCon.close()

    #Create dictionary for old/new room lookup
    AvailExits=newExitNums.copy()
    for i in range(len(oldExitNums)):
        oldExitNum=oldExitNums[i]
        if len(newExitNums):
            newExitNum=newExitNums.pop(0)
        else:
            newExitNum=nextNewExitID+i-len(AvailExits)
        exitLookup_byOldRoom[str(oldExitNum)] = newExitNum
    #print("List of oldExitNums:",oldExitNums)
    #print("exitLookup_byOldRoom Dict:",exitLookup_byOldRoom)
    print("----------------------------------------------------------------------------------------------------------------")

    """Create SQL INSERT/UPDATE statements"""
    newExitNums=AvailExits.copy()
    #print("AvailExits: ",AvailExits)
    Query=""
    for exit in exits:
        #print("Item: ",item," || ObjId: ",item['ObjId'])
        if exitLookup_byOldRoom[str(exit['ExitId'])] in AvailExits:
            Query = "UPDATE ExitTbl SET "
        else:
            Query = "INSERT INTO ExitTbl (" + ",".join(exitHeader) + ")"
            Query = Query + " VALUES ("
            #comma separated

        for key in exit:
            #comma separated
            if "UPDATE" in Query:
                if Query[-1]!=" ":
                    Query=Query+","
            else:
                if Query[-1]!="(":
                    Query=Query+","

            if "UPDATE" in Query:
                Query = Query + str(key) + '='

            #Insert using new values
            if key=="ExitId":
                Query=Query+str(exitLookup_byOldRoom[str(exit[key])])
            elif key=="ExitIdTo" and str(exit[key]) in exitLookup_byOldRoom.keys():
                Query=Query+str(exitLookup_byOldRoom[str(exit[key])])
            elif key=="ExitIdTo" and str(exit[key]) not in exitLookup_byOldRoom.keys():
                Query=Query+str(exitLookup_byOldRoom[str(exit['ExitId'])])
            elif key=="FromID":
                Query=Query+str(roomLookup_byOldRoom[str(exit[key])])
            elif key=="ToID" and str(exit[key]) in roomLookup_byOldRoom.keys():
                Query=Query+str(roomLookup_byOldRoom[str(exit[key])])
            elif key=="ToID" and str(exit[key]) not in roomLookup_byOldRoom.keys():
                Query=Query+str(roomLookup_byOldRoom[str(exit['FromID'])])
            elif key=="Name":
                Query=Query+'"'+str(exit[key])+'"'
            else:
                Query=Query+str(exit[key])

        if "UPDATE" in Query:
            Query=Query+' WHERE ExitId="'+str(exitLookup_byOldRoom[str(exit['ExitId'])])+'"'
        else:
            Query = Query + ");"

        print(Query)
        try:
            exitCon = sqlite3.connect(newDB)
            exitCur = exitCon.cursor()
            exitCur.execute(Query)
            exitCon.commit()
        except Error:
            print(Error)
        finally:
            exitCon.close()
    print("----------------------------------------------------------------------------------------------------------------")



if __name__ == '__main__':
    oldDB="/home/samio/Documents/samioshare/T2T_Info_Files/Database/T2TMap/TheTwoTowers_oldDB.dbm"
    newDB="/home/samio/Documents/samioshare/T2T_Info_Files/Database/T2TMap/TheTwoTowers_NewGrid_BeforeImport.dbm"
    zoneList=[]
    zoneName=""
    newZoneID=0
    oldRoomNums=[]
    roomLookup_byOldRoom={}
    roomLookup_byNewRoom={}

    oldExitNums=[]
    exitLookup_byOldRoom={}
    exitLookup_byNewRoom={}

    #List of small zones for testing
    #zoneList.extend([51,533,496,528])


    #Adornas
    zoneList.extend([174])
    #zoneList.extend([174,177,20,502,545,224,19,110,802,526,198,781,782,199,519,516,181,527,25,578,22])
    #East Arda Cities
    #zoneList.extend([18,189,190,191,70,64,740,35,88,27,562,135,8,57,58,6,15,51,114,98,49,132,119,223,623,555,134,808,934,894,117,787,83])
    #Moria
    #zoneList.extend([124,125,126,219,890,889,888,891,884,232,882,546,964,250])
    #Minas Tirith
    #zoneList.extend([127,967,234,722])
    #Mordor Cities
    #zoneList.extend([663,166,120,1012,958,1013])
    #Shelobs Lair
    #zoneList.extend([244,245,246,893])
    #Far Harad Cities
    #zoneList.extend([745,757,746,749,750,752,848,758,760,761,829,762])
    #West Arda Mini Zones
    #zoneList.extend([109,533,496,528,910,530,820,568,524,566,799,573,821,506,544,518,508,536,538,499,520,522,517,537,572,776,503,511,540,504,512,535,521,775,541,565,534,523,233,509,195,510,577,225,779,780,505,911,655,936,579,543,576,564,547,794,515,514,513,507,197,194,532,570,531,567,529,965,206,137,569,186,205,175,193,575,670,574,571,203,182,202,176])
    """
    #Voids
    zoneList.extend([992,773,962,254])
    #West Arda Rivendell
    zoneList.extend([823,582,542,774])
    #East Arda: Rhun
    zoneList.extend([192,800,806,184,942,165,961,877,943])
    #East Arda: Misty Mountains
    zoneList.extend([1051,1052,21,908,59,686,16,90,93,142,92,89,91,139,30])
    #East Arda: Northeast Rhovanion
    zoneList.extend([65,108,217,82,76,106,105,107,253,131,141])
    #East Arda: Rhovanion
    zoneList.extend([718,80,216,163,729,700,67,200,786,103,156,201,164,630,86,113,62,79,785,78,236,157,252,692,83,60,28,29,143])
    #East Arda: South Rhovanion
    zoneList.extend([95,251,155,672,47,66,77,154,72,75,73,74,188,215,48,81,96,69,147])
    #East Arda: Rohan
    zoneList.extend([56,622,621,807,784,220,183,209,41,44,31,39,136,42,38,63,40,37,159,43])
    #East Arda: Gondor
    zoneList.extend([145,146,144,741,563,55,498,53,5,129,933,580,128,771,52,2,221,792,133,68,162])
    #East Arda: Harondor
    zoneList.extend([788,552,787,172,158,561,550,549,826,104,827,117,99,560,130,112,770,118,551,187,945,173,948,737,772,796,797,852,738,954])
    #East Arda: Near Harad
    zoneList.extend([932,149,210,211,212,795,735,121,558,123,213,801,798,150,629,554,101,556,783,628,557,553,559])
    #East Arda: Umbar
    zoneList.extend([732,725,726,937,151,731,724,946,723])
    """

    #Problems with zone ,1052,1026, 773,718,962,254
    #Gypsy, Event, Misty Mtns, Orcbrew, Caktash

    print("----------------------------------------------------------------------------------------------------------------")
    print("Transferring zones: ",zoneList)
    for ZoneID in zoneList:
        zoneName=""
        newZoneID=0
        oldRoomNums=[]
        roomLookup_byOldRoom={}
        roomLookup_byNewRoom={}

        oldExitNums=[]
        exitLookup_byOldRoom={}
        exitLookup_byNewRoom={}

        Tables_MarkAvailable()
        ZoneTbl_EXE()
        print("Global newZoneID: ",newZoneID)

        #"""
        print("----------------------------------------------------------------------------------------------------------------")
        if newZoneID>0:
            ObjectTbl_EXE()
            print("----------------------------------------------------------------------------------------------------------------")
            ExitTbl_EXE()
            print("----------------------------------------------------------------------------------------------------------------")
        else:
            print("ObjectTbl & ExitTbl Queries skipped because ZoneName already exists!> "+zoneName)
        print("----------------------------------------------------------------------------------------------------------------")
        #"""

    print("Zone Transfer Complete!")
    print("----------------------------------------------------------------------------------------------------------------")
Reply with quote
samio1221@gmail.com
Newbie


Joined: 02 Mar 2020
Posts: 4

PostPosted: Tue Mar 03, 2020 11:18 am   Update: Latest patch
 
Code:

import sqlite3
from sqlite3 import Error

def ZoneTbl_EXE():
    zoneName=""
    newZoneID=0
    zoneDict={}
    availZoneDict={}
    ZoneQuery=""
    Query=""
    oldDBCon = sqlite3.connect(oldDB)
    zoneCon = sqlite3.connect(newDB)
    objCon = sqlite3.connect(newDB)
    exitCon = sqlite3.connect(newDB)
    selectItems="Name,X,Y,Z,Dx,Dy,MinX,MinY,MinZ,MaxX,MaxY,MaxZ,XScale,YScale,XOffset,YOffset,Multiplier,DefSize,DefSizeY".upper()
    ZoneQuery = "SELECT "+selectItems+" FROM [ZONETBL] WHERE [ZONEID]='"+str(ZoneID)+"'"
    print(ZoneQuery)

    #Grabbing oldDB ZoneTbl info
    try:
        oldCur = oldDBCon.cursor()
        oldCur.execute(ZoneQuery)
        rows = oldCur.fetchall()
        zoneHeader = [description[0] for description in oldCur.description]
        for rownum in range(len(rows)):
            for i in range(len(rows[rownum])):
                zoneDict[zoneHeader[i]]=rows[rownum][i]
        zoneName=zoneDict['Name']
        #print(zoneDict)
    except Error:
        print(Error)
    finally:
        oldDBCon.close()

    #Checking if zoneName exists. If not, create it
    try:
        ZoneQuery = 'SELECT * FROM [ZONETBL] WHERE [NAME] = "'+zoneName+'"'
        print(ZoneQuery)
        zoneCur = zoneCon.cursor()
        zoneCur.execute(ZoneQuery)
        rows = zoneCur.fetchall()

        #if (len(rows)<=0) then the zone doesn't exist: proceed to create it
        if (int(len(rows))<=0):
            #If newDB has 'Available' placeholder zones, use that zone instead of creating new zone
            ZoneQuery = 'SELECT [ZONEID] FROM [ZONETBL] WHERE [NAME] = "'+"Available"+'"'
            print(ZoneQuery)
            zoneCur = zoneCon.cursor()
            zoneCur.execute(ZoneQuery)
            zones = zoneCur.fetchall()
            header = [description[0] for description in zoneCur.description]
            if (int(len(zones))>0):
                newZoneID=zones[0][0]
                print("Available zone detected:",newZoneID)
                ZoneQuery = "UPDATE [ZONETBL] SET "
            else:
                TempQuery="INSERT INTO [ZONETBL] (Name) VALUES ("+'"'+"DummyName"+'"'+")"
                zoneCur.execute(TempQuery)
                newZoneID=zoneCur.lastrowid
                TempQuery='DELETE FROM [ZONETBL] WHERE [NAME]="DummyName"'
                zoneCur.execute(TempQuery)
                zoneCon.commit()
                print("Next ZoneID assigned:",newZoneID)
                ZoneQuery = "INSERT INTO [ZONETBL] (" + ",".join(zoneHeader) + ")"
                ZoneQuery = ZoneQuery + " VALUES ("

            for key in zoneDict:
                if "UPDATE" in ZoneQuery and ZoneQuery[-1]!=' ':
                    ZoneQuery=ZoneQuery+","
                elif "INSERT" in ZoneQuery and ZoneQuery[-1]!="(":
                    ZoneQuery=ZoneQuery+","
                if "UPDATE" in ZoneQuery:
                    ZoneQuery = ZoneQuery + '[' + str(key).upper() + ']' + '='

                """Insert using new values"""
                #selectItems="Name,X,Y,Z,Dx,Dy,MinX,MinY,MinZ,MaxX,MaxY,MaxZ,XScale,YScale,XOffset,YOffset"
                if key.upper() == "NAME":
                    ZoneQuery=ZoneQuery+'"'+str(zoneDict[key])+'"'
                elif key.upper() == "FKEY":
                    ZoneQuery=ZoneQuery+"0"
                elif zoneDict[key] is not None:
                    ZoneQuery=ZoneQuery+str(zoneDict[key])
                elif zoneDict[key].upper() == "MULTIPLIER":
                    ZoneQuery=ZoneQuery+str("100")
                elif zoneDict[key].upper() in ["DEFSIZE","DEFSIZEY"]:
                    ZoneQuery=ZoneQuery+str("120")
                elif key.upper() in ['X','Y','Z','DX','DY','MINX','MINY','MINZ','MAXX','MAXY','MAXZ','XSCALE','YSCALE','XOFFSET','YOFFSET']:
                    ZoneQuery=ZoneQuery+"0"
                else:
                    ZoneQuery=ZoneQuery+'""'
            if "UPDATE" in ZoneQuery:
                ZoneQuery=ZoneQuery+' WHERE [ZONEID]="'+str(newZoneID)+'"'
            else:
                ZoneQuery = ZoneQuery + ")"

            print(ZoneQuery)
            zoneCur = zoneCon.cursor()
            zoneCur.execute(ZoneQuery)
            zoneCon.commit()
        else:
            print("ZoneName: ",zoneName," already exists! Skipping...")

    except Error:
        print(Error)
    finally:
        oldDBCon.close()
        zoneCon.close()
        objCon.close()
        exitCon.close()
        return newZoneID,zoneName


def ObjectTbl_EXE():
    selectItems="ZoneID,ObjId,Name,IDName,fKey,X,Y,Z,Dx,Dy,ExitX,ExitY,ExitZ,Color,LabelDir".upper()
    itemDict={}
    items=[]
    header=[]
    newRoomNumsNeeded=0
    newRoomNums=[]
    nextNewObjID=0
    #Retrieve data from oldDB
    ObjectQuery = "SELECT "+selectItems+" FROM [OBJECTTBL] WHERE [ZONEID]='"+str(ZoneID)+"'"
    print(ObjectQuery)
    try:
        objCon = sqlite3.connect(oldDB)
        objCur = objCon.cursor()
        objCur.execute(ObjectQuery)
        rows = objCur.fetchall()
        ObjectTblHeader = [description[0] for description in objCur.description]

        #Create dictionary for each row result, and add to items list
        for row in rows:
            #maintain list of old room nums
            for i in range(len(row)):
                itemDict[ObjectTblHeader[i]]=row[i]
            oldRoomNums.append(itemDict['ObjId'])
            items.append(itemDict.copy())
            itemDict.clear()
        #print("--------------------------------------------------------")
    except Error:
        print(Error)
    finally:
        objCon.close()

    #Available ObjId's in newRoomNums list. If not enough, then nextNewObjID will be >0
    newRoomNumsNeeded=len(oldRoomNums)
    try:
        ObjectQuery = 'SELECT [OBJID] FROM [OBJECTTBL] WHERE [NAME] = "'+"Available"+'"'
        print(ObjectQuery)
        objCon = sqlite3.connect(newDB)
        objCur = objCon.cursor()
        objCur.execute(ObjectQuery)
        availRooms = objCur.fetchall()
        roomHeader = [description[0] for description in objCur.description]

        if (int(len(availRooms))>0):
            #print("roomHeader: ",roomHeader)
            #print("rooms: ",availRooms)
            for room in availRooms:
                itemDict={}
                #print("Zone: ",zone)
                for r in range(len(room)):
                    itemDict[roomHeader[r]]=room[r]
                newRoomNums.append(itemDict['ObjId'])
                newRoomNumsNeeded=newRoomNumsNeeded-1
                if newRoomNumsNeeded==0:
                    break
        if newRoomNumsNeeded>0:
            #Create dummy entry in newDB's ObjectTbl, without db.commit(), to identify next autoincrement ID keynum
            ObjectQuery="INSERT INTO [OBJECTTBL] (Name) VALUES ("+'"'+"DummyName"+'"'+")"
            objCur.execute(ObjectQuery)
            nextNewObjID=objCur.lastrowid
            ObjectQuery='DELETE FROM [OBJECTTBL] WHERE [NAME]="DummyName"'
            objCur.execute(ObjectQuery)
            objCon.commit()
            print(zoneName + " | " + str(newZoneID) + " | " + "NextNewObjID: " + str(nextNewObjID))
    except Error:
        print(Error)
    finally:
        objCon.close()

    #Create dictionary for old/new room lookup
    AvailRooms=newRoomNums.copy()
    for i in range(len(oldRoomNums)):
        oldRoomNum=oldRoomNums[i]
        if len(newRoomNums):
            newRoomNum=newRoomNums.pop(0)
        else:
            newRoomNum=nextNewObjID+i-len(AvailRooms)
        roomLookup_byOldRoom[str(oldRoomNum)] = newRoomNum

    #Create SQL INSERT/UPDATE statements
    newRoomNums=AvailRooms.copy()
    #print("AvailRooms: ",AvailRooms)
    Query=""
    for item in items:
        #print("Item: ",item," || ObjId: ",item['ObjId'])
        if roomLookup_byOldRoom[str(item['ObjId'])] in AvailRooms:
            Query = "UPDATE [OBJECTTBL] SET "
        else:
            Query = "INSERT INTO [OBJECTTBL] (" + ",".join(ObjectTblHeader) + ")"
            Query = Query + " VALUES ("
            #comma separated
        for key in item:
            #comma separated
            if "UPDATE" in Query:
                if Query[-1]!=" ":
                    Query=Query+","
            else:
                if Query[-1]!="(":
                    Query=Query+","

            if "UPDATE" in Query:
                Query = Query + '[' + str(key).upper() + ']' + '='

            """Insert using new values"""
            if key=="ZoneID":
                Query=Query+str(newZoneID)
            elif key=="ObjId":
                oldRoomNum=item[key]
                Query=Query+str(roomLookup_byOldRoom[str(oldRoomNum)])
            elif key in ["Name","IDName"]:
                Query=Query+'"'+str(item[key])+'"'
            elif key=="fKey":
                Query=Query+"0"
            elif item[key] is not None:
                Query=Query+str(item[key])
            elif key in ['X','Y','Z','Dx','Dy','ExitX','ExitY','ExitZ']:
                Query=Query+"0"
            else:
                Query=Query+'""'

        if "UPDATE" in Query:
            Query=Query+' WHERE [OBJID]="'+str(roomLookup_byOldRoom[str(item['ObjId'])])+'"'
        else:
            Query = Query + ");"

        print(Query)
        try:
            objCon = sqlite3.connect(newDB)
            objCur = objCon.cursor()
            objCur.execute(Query)
            objCon.commit()
        except Error:
            print(Error)
        finally:
            objCon.close()
    print("----------------------------------------------------------------------------------------------------------------")

def ExitTbl_EXE():
    selectItems="ExitId,ExitIdTo,FromID,ToID,ExitKindID,Name,DirType,DirToType,Flags,UserID".upper()
    #selectItems="*"
    """list of dictionaries containing results"""
    exits=[]
    """dictionary for individual row results"""
    exitItem={}
    exitDict={}
    queryValues=[]
    newExitNums=[]

    #Calculate how many exits are needed
    for fromID in roomLookup_byOldRoom:
        ExitQuery = "SELECT "+selectItems+" FROM [EXITTBL] WHERE [FROMID]='"+fromID+"'"
        try:
            exitCon = sqlite3.connect(oldDB)
            exitCur = exitCon.cursor()
            exitCur.execute(ExitQuery)
            rows = exitCur.fetchall()
            exitHeader = [description[0] for description in exitCur.description]

            #Create dictionary for each row result, and add to items list
            for row in rows:
                for i in range(len(row)):
                    exitItem[exitHeader[i]]=row[i]
                oldExitNums.append(exitItem['ExitId'])
                exits.append(exitItem.copy())
                exitItem.clear()
            #print("----------------------------------------------------------------------------------------------------------------")
        except Error:
            print(Error)
        finally:
            exitCon.close()
    numExits = len(exits)
    numExitsNeeded=numExits
    print("numExitsNeeded: ",numExitsNeeded)
    #Calculate how many 'Available' exitid's there are.
    #Identify Available ObjId's
    #Available ObjId's in newRoomNums list. If not enough, then nextNewObjID will be >0
    try:
        ExitQuery = 'SELECT [EXITID] FROM [EXITTBL] WHERE [NAME] = "'+"Available"+'"'
        print("ExitQuery: "+ExitQuery)
        exitCon = sqlite3.connect(newDB)
        exitCur = exitCon.cursor()
        exitCur.execute(ExitQuery)
        availExits = exitCur.fetchall()
        header = [description[0] for description in exitCur.description]

        if (int(len(availExits))>0):
            print("'Available' exits found in ExitTbl...")
            #print("header: ",header)
            #print("availExits: ",availExits)
            for exit in availExits:
                exitDict={}
                for e in range(len(exit)):
                    exitDict[header[e]]=exit[e]
                newExitNums.append(exitDict['ExitId'])
                numExitsNeeded=numExitsNeeded-1
                if numExitsNeeded==0:
                    break
        if numExitsNeeded>0:
            ExitQuery="INSERT INTO [EXITTBL] (Name) VALUES ("+'"'+"DummyName"+'"'+")"
            exitCon = sqlite3.connect(newDB)
            exitCur = exitCon.cursor()
            exitCur.execute(ExitQuery)
            nextNewExitID=exitCur.lastrowid
            ExitQuery='DELETE FROM [EXITTBL] WHERE [NAME]="DummyName"'
            exitCur.execute(ExitQuery)
            exitCon.commit()
            print(zoneName + " | " + str(newZoneID) + " | " + "nextNewExitID: " + str(nextNewExitID))
    except Error:
        print(Error)
    finally:
        exitCon.close()

    #Create dictionary for old/new room lookup
    AvailExits=newExitNums.copy()
    for i in range(len(oldExitNums)):
        oldExitNum=oldExitNums[i]
        if len(newExitNums):
            newExitNum=newExitNums.pop(0)
        else:
            newExitNum=nextNewExitID+i-len(AvailExits)
        exitLookup_byOldRoom[str(oldExitNum)] = newExitNum
    #print("List of oldExitNums:",oldExitNums)
    #print("exitLookup_byOldRoom Dict:",exitLookup_byOldRoom)
    #print("----------------------------------------------------------------------------------------------------------------")

    """Create SQL INSERT/UPDATE statements"""
    newExitNums=AvailExits.copy()
    #print("AvailExits: ",AvailExits)
    Query=""
    for exit in exits:
        #print("Item: ",item," || ObjId: ",item['ObjId'])
        if exitLookup_byOldRoom[str(exit['ExitId'])] in AvailExits:
            Query = "UPDATE [EXITTBL] SET "
        else:
            Query = "INSERT INTO [EXITTBL] (" + ",".join(exitHeader) + ")"
            Query = Query + " VALUES ("
            #comma separated

        for key in exit:
            #comma separated
            if "UPDATE" in Query:
                if Query[-1]!=" ":
                    Query=Query+","
            else:
                if Query[-1]!="(":
                    Query=Query+","

            if "UPDATE" in Query:
                Query = Query + '[' + str(key).upper() + ']' + '='

            #Insert using new values
            if key=="ExitId":
                Query=Query+str(exitLookup_byOldRoom[str(exit[key])])
            elif key=="ExitIdTo" and str(exit[key]) in exitLookup_byOldRoom.keys():
                Query=Query+str(exitLookup_byOldRoom[str(exit[key])])
            elif key=="ExitIdTo" and str(exit[key]) not in exitLookup_byOldRoom.keys():
                Query=Query+str(exitLookup_byOldRoom[str(exit['ExitId'])])
            elif key=="FromID":
                Query=Query+str(roomLookup_byOldRoom[str(exit[key])])
            elif key=="ToID" and str(exit[key]) in roomLookup_byOldRoom.keys():
                Query=Query+str(roomLookup_byOldRoom[str(exit[key])])
            elif key=="ToID" and str(exit[key]) not in roomLookup_byOldRoom.keys():
                Query=Query+str(roomLookup_byOldRoom[str(exit['FromID'])])
            elif key=="Name":
                Query=Query+'"'+str(exit[key])+'"'
            else:
                Query=Query+str(exit[key])

        if "UPDATE" in Query:
            Query=Query+' WHERE [EXITID]="'+str(exitLookup_byOldRoom[str(exit['ExitId'])])+'"'
        else:
            Query = Query + ");"

        print(Query)
        try:
            exitCon = sqlite3.connect(newDB)
            exitCur = exitCon.cursor()
            exitCur.execute(Query)
            exitCon.commit()
        except Error:
            print(Error)
        finally:
            exitCon.close()
    print("----------------------------------------------------------------------------------------------------------------")


if __name__ == '__main__':
    oldDB="path to old database"
    newDB="path to new database"
    zoneList=[]
    zoneName=""
    newZoneID=0
    oldRoomNums=[]
    roomLookup_byOldRoom={}
    roomLookup_byNewRoom={}
    oldExitNums=[]
    exitLookup_byOldRoom={}
    exitLookup_byNewRoom={}
    #List of small zones for testing
    #zoneList.extend([1,2,3])

    #Tables_MarkAvailable()
    print("----------------------------------------------------------------------------------------------------------------")
    print("Transferring zones: ",zoneList)
    for ZoneID in zoneList:
        zoneName=""
        newZoneID=0
        oldRoomNums=[]
        roomLookup_byOldRoom={}
        roomLookup_byNewRoom={}

        oldExitNums=[]
        exitLookup_byOldRoom={}
        exitLookup_byNewRoom={}

        newZoneID,zoneName=ZoneTbl_EXE()
        print("Global Variables Updated: ZoneID: "+str(newZoneID)+" | ZoneName: "+str(zoneName))
        #"""
        print("----------------------------------------------------------------------------------------------------------------")
        if newZoneID>0:
            ObjectTbl_EXE()
            print("----------------------------------------------------------------------------------------------------------------")
            ExitTbl_EXE()
            print("----------------------------------------------------------------------------------------------------------------")
        else:
            print("ObjectTbl & ExitTbl Queries skipped because ZoneName already exists!> "+zoneName)
        print("----------------------------------------------------------------------------------------------------------------")
        #"""

    print("Zone Transfer Complete!")
    print("----------------------------------------------------------------------------------------------------------------")
Reply with quote
Tharius
Beginner


Joined: 15 Apr 2013
Posts: 24

PostPosted: Mon Apr 20, 2020 1:46 pm   
 
Thank you for sharing!
Reply with quote
Display posts from previous:   
Post new topic   Reply to topic     Home » Forums » CMUD Beta Forum All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

© 2009 Zugg Software. Hosted by Wolfpaw.net