 |
samio1221@gmail.com
Newbie
Joined: 02 Mar 2020
Posts: 4
|
 Posted: 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("----------------------------------------------------------------------------------------------------------------")
|
|
|
|
|
|
samio1221@gmail.com
Newbie
Joined: 02 Mar 2020
Posts: 4
|
Posted: 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("----------------------------------------------------------------------------------------------------------------")
|
|
|
|
|
|
samio1221@gmail.com
Newbie
Joined: 02 Mar 2020
Posts: 4
|
Posted: 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("----------------------------------------------------------------------------------------------------------------")
|
|
|
|
|
|
Tharius
Beginner
Joined: 15 Apr 2013
Posts: 24
|
Posted: Mon Apr 20, 2020 1:46 pm
|
Thank you for sharing!
|
|
|
|
|
|
|
|
|
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
|
|
