[Zope-DB] Sript to Change zsql connection id

[Dario Lopez-Kästen]

Peter Millar wrote:

> Is there a way that I could create a script that I can run over a plone
> instance and change the connection id for every zsql method.  
> 
> I have several development databases.  I have several ODBC connectors,
> one to each db, you know one for devel, test, production.
> 
> So if I have a bit of code that uses a zsql method and I want to test it
> against a different database I have to change every zsql method that my
> app users.  I hate it.  There has to be a better way. I am thinking a
> little script would do the job, finding all the zsql methods and change
> them to new odbc connector that I specify.
> 
>

You need to create an external method (to put in the Extensions folder 
of your INSTANCE_HOME) with this code:

---
## External method code
# Methods for manipulating Z SQL Methods programatically
def changeConnID(zsql_meth, new_conn_id):
     zsql_meth.connection_id = str(new_conn_id)
---

then create an Exernal method in zope. The script below assumes that the 
external method is called "changeConnID"

---
## Script (Python) "replace_sql_connection"
##bind container=container
##bind context=context
##bind namespace=
##bind script=script
##bind subpath=traverse_subpath
##parameters=
##title=
##
request = container.REQUEST
RESPONSE =  request.RESPONSE
write = RESPONSE.write

old_ids = ['readKA_CI', 'updateKA_CI']
new_id = 'KA_CI'

msg_tpl = '''<p>Changed <a href="%s/manage" target="_blank">[%s]</a>
from %s to %s</p>'''

write('<h2>Z SQL Methods</h2>')
for object in context.ZopeFind(container, obj_metatypes=('Z SQL 
Method',), search_sub=1):

   if object[1].connection_id in old_ids:
     old_id = object[1].connection_id

     context.changeConnID(object[1], new_id)

     msg = msg_tpl%(object[1].absolute_url(), object[1].id, old_id, new_id)
     write(msg)

write('<p>DONE!</p>')
write('</body></html>')
---

The script finds all ZSQL methods starting form the container of the 
script (recursively) and if that ZSQL Meth has a connection id matching 
one in the 'old_ids' list, then it replaces it with the one in the 
'new_id' variable.

To make the scritp search elswhere than in the container of itself, change

for object in context.ZopeFind(container....

and set container so something else.

Hope this helps.

/dario

-- 
-- -------------------------------------------------------------------
Dario Lopez-Kästen, IT Systems & Services Chalmers University of Tech.