Can't u attach it as a file, so the lines aint fucked up, they are on mine, dont know if any other has the same problem, or maybe a link to a page online where its posible to get it, maybe together with the other phpscript...???
// Just a suggestion // Syska -----Oprindelig meddelelse----- Fra: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] På vegne af Ilja Booij Sendt: 10. oktober 2003 16:44 Til: [email protected] Emne: Re: SV: SV: [Dbmail] php script for copying mailboxes to another db On Friday, Oct 10, 2003, at 15:56 Europe/Amsterdam, Mikael Syska wrote: > Will it be very hard to make it just copy from 1.1 to 1.1 or will the > DBMail > 2.0 be released soon, or is it allready available on the net, I can't > seem > to find it in the www.dbmail.org site..... > I just finished version 1 of the script, tested it on a small database with mysql backend. This python script uses MySQLdb and pyPgSQL, which you may need to install seperately (apt-get install python-mysqldb python-pgsql on Debian Linux). here it comes, hope it works for everybody Ilja #!/usr/bin/env python # dbmail database migration script. Use this script to make the transition # from DBMail 1.x to DBMail 2.x #import sys print """ welcome to the DBMail 1.x -> 2.x migration script ************************************************** Copyright (C) 2003 IC & S [EMAIL PROTECTED] This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA. """ print "***********************************************************" print "These next questions concern your DBMail 1.x database" print "What Database system does your DBMail 1.x run on? (type a number)" dbmail_1_x = {} dbmail_1_x['type'] = input("\t1. MySQL\n\t2. PostgreSQL\n") dbmail_1_x['host'] = raw_input("Database Host:") dbmail_1_x['name'] = raw_input("name of database:") dbmail_1_x['user'] = raw_input("DB user:") dbmail_1_x['pass'] = raw_input("DB password:") if dbmail_1_x['host'] == "": dbmail_1_x['host'] = "localhost" if dbmail_1_x['name'] == "": dbmail_1_x['name'] = "dbmail1_1" if dbmail_1_x['user'] == "": dbmail_1_x['user'] = "ilja" if dbmail_1_x['pass'] == "": dbmail_1_x['pass'] = "pass" print "What Database system does your DBMail 2.0 run on? (type a number)" dbmail_2_0 = {} dbmail_2_0['type'] = input("\t1. MySQL\n\t2. PostgreSQL\n") dbmail_2_0['host'] = raw_input("Database Host:") dbmail_2_0['name'] = raw_input("name of database:") dbmail_2_0['user'] = raw_input("DB user:") dbmail_2_0['pass'] = raw_input("DB password:") if dbmail_2_0['host'] == "": dbmail_2_0['host'] = "localhost" if dbmail_2_0['name'] == "": dbmail_2_0['name'] = "dbmail" if dbmail_2_0['user'] == "": dbmail_2_0['user'] = "ilja" if dbmail_2_0['pass'] == "": dbmail_2_0['pass'] = "pass" print "\nLOADING DATABASE DRIVERS\n" if dbmail_1_x['type'] == 1 or dbmail_1_x['type'] == 1: print "loading MySQL driver" import MySQLdb if dbmail_2_0['type'] == 2 or dbmail_2_0['type'] == 2: print "loading MySQL driver" from pyGresSQL import connect print "connecting to databases" try: conn_1 = connect(user = dbmail_1_x['user'], db=dbmail_1_x['name'], passwd = dbmail_1_x['pass'], host = dbmail_1_x['host']) except MySQLdb.Error, e: print "Error %d: %s" % (e.args[0], e.args[1]) sys.exit(1) try: conn_2 = connect(user = dbmail_2_0['user'], db=dbmail_2_0['name'], passwd = dbmail_2_0['pass'], host = dbmail_2_0['host']) except MySQLdb.Error, e: print "Error %d: %s" % (e.args[0], e.args[1]) sys.exit(1) print "connected to database" # get two database cursors, one for each database cursor_1 = conn_1.cursor() cursor_2 = conn_2.cursor() # copy all records from the aliases table print "copying aliases table" cursor_1.execute("""SELECT alias_idnr, alias, deliver_to, client_idnr FROM aliases""") cursor_2.execute("""DELETE FROM aliases""") i = 0 while 1: record = cursor_1.fetchone() if record == None: break cursor_2.execute("""INSERT INTO aliases (alias_idnr, alias, deliver_to, client_idnr) VALUES (%s, %s, %s, %s)""", record) i += 1 print "copied %d records from aliases table" % (i) # auto_notifications print "copying auto_notifications table" i = 0 cursor_1.execute("""SELECT auto_notify_idnr, user_idnr, notify_address FROM auto_notifications""") cursor_2.execute("""DELETE FROM auto_notifications""") while 1: record = cursor_1.fetchone() if record == None: break cursor_2.execute("""INSERT INTO auto_notifications (auto_notify_idnr, user_idnr, notify_address) VALUES (%s, %s, %s)""", record) i += 1 print "copied %d records from auto_notifications table" % (i) # auto_replies print "copying auto_replies table" i = 0 cursor_1.execute("""SELECT auto_reply_idnr, user_idnr, reply_body FROM auto_replies""") cursor_2.execute("""DELETE FROM auto_replies""") while 1: record = cursor_1.fetchone() if record == None: break cursor_2.execute("""INSERT INTO auto_replies (auto_reply_idnr, user_idnr, reply_body) VALUES (%s, %s, %s)""", record) i += 1 print "copied %d records from auto_reply table" % (i) # config table print "copying config table" i = 0 cursor_1.execute("""SELECT configid, item, value FROM config""") cursor_2.execute("""DELETE FROM config""") while 1: record = cursor_1.fetchone() if record == None: break cursor_2.execute("""INSERT INTO config (configid, item, value) VALUES (%s, %s, %s)""", record) i += 1 print "copied %d records from config table" % (i) # mailboxes print "copying mailboxes table" i = 0 cursor_1.execute("""SELECT mailbox_idnr, owner_idnr, name, seen_flag, answered_flag, deleted_flag, flagged_flag, recent_flag, draft_flag, no_inferiors, no_select, permission, is_subscribed FROM mailboxes""") cursor_2.execute("""DELETE FROM mailboxes""") while 1: record = cursor_1.fetchone() if record == None: break cursor_2.execute("""INSERT INTO mailboxes (mailbox_idnr, owner_idnr, name, seen_flag, answered_flag, deleted_flag, flagged_flag, recent_flag, draft_flag, no_inferiors, no_select, permission, is_subscribed) VALUES (%s, %s, %s, %s, %s, %s, %s, %s,%s,%s,%s,%s,%s)""", record) i += 1 print "copied %d records from mailboxes table" % (i) # messages # this table cannot be simply copied. We need to split each record # between the messages table and the physmessage table i = 0 print "copy messages table to messages and physmessage table" cursor_1.execute("""SELECT messagesize, rfcsize, internal_date, message_idnr, mailbox_idnr, seen_flag, answered_flag, deleted_flag, flagged_flag, recent_flag, draft_flag, unique_id, status FROM messages""") cursor_2.execute("""DELETE FROM messages""") cursor_2.execute("""DELETE FROM physmessage""") while 1: record = cursor_1.fetchone() if record == None: break cursor_2.execute("""INSERT INTO physmessage (messagesize, rfcsize, internal_date) VALUES (%s, %s, %s)""", (record[0], record[1], record[2])) new_id = cursor_2.lastrowid cursor_2.execute("""INSERT INTO messages (physmessage_id, message_idnr, mailbox_idnr, seen_flag, answered_flag, deleted_flag, flagged_flag, recent_flag, draft_flag, unique_id, status) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""", (new_id, record[3], record[4], record[5], record[6], record[7], record[8], record[9], record[10], record[11], record[12])) i += 1 print "copied %d records of messages into messages and physmessage" % i # messageblks table # most of the information from this table can be copied. Only the message_idnr # from the original table has to be replaced by the appropriate physmessage_id i = 0 print "copying records from messageblks table" cursor_1.execute("""SELECT messageblk_idnr, message_idnr, messageblk, blocksize FROM messageblks""") cursor_2.execute("""DELETE FROM messageblks""") while 1: record = cursor_1.fetchone() if record == None: break cursor_2.execute("""SELECT physmessage_id FROM messages WHERE message_idnr = %s""", (record[1])) phys_id_record = cursor_2.fetchone() if phys_id_record == None: print("Oops. Inconsistency in database. run dbmail-maintenance (version 1.x). exiting..") exit(-1) cursor_2.execute("""INSERT INTO messageblks (messageblk_idnr, physmessage_id, messageblk, blocksize) VALUES (%s, %s, %s, %s)""", (record[0], phys_id_record[0], record[2], record[3])) i += 1 print "copied %d records of messageblks into messablks" % i # users table i = 0 print "copying records from users table" cursor_1.execute("""SELECT user_idnr, userid, passwd, client_idnr, maxmail_size, encryption_type, last_login FROM users""") cursor_2.execute("""DELETE FROM users""") while 1: record = cursor_1.fetchone() if record == None: break cursor_2.execute("""INSERT INTO users (user_idnr, userid, passwd, client_idnr, maxmail_size, encryption_type, last_login) VALUES (%s, %s, %s, %s, %s, %s ,%s)""", (record)) cursor_2.execute("""SELECT SUM(pm.messagesize) FROM mailboxes mbx, messages msg, physmessage pm WHERE pm.id = msg.physmessage_id AND msg.mailbox_idnr = mbx.mailbox_idnr AND mbx.owner_idnr = %s AND msg.status < '2'""", (record[0])) size = cursor_2.fetchone()[0] if size == None: size = 0L print "user %ld, size %ld" % (record[0],size) cursor_2.execute("""UPDATE users SET curmail_size = %s WHERE user_idnr = %s""", (size, record[0])) cursor_1.close() cursor_2.close() conn_1.close() conn_2.close() _______________________________________________ Dbmail mailing list [email protected] https://mailman.fastxs.nl/mailman/listinfo/dbmail
