On 09/10/11 15:23 +0200, Cédric Krier wrote: > On 09/10/11 08:36 +0200, Cédric Krier wrote: > > On 30/09/11 21:48 +0200, Cédric Krier wrote: > > > On 27/08/11 18:55 +0200, Cédric Krier wrote: > > > > On 27/08/11 12:17 -0400, Douglas Morato wrote: > > > > > > So I'm looking for some help to work on this migration. I think we > > > > > > will use a > > > > > > repositories to store all the customizations of each softwares. So > > > > > > the > > > > > > development could be distributed and I could provide some > > > > > > "annonymized data" > > > > > > from the bugtracker and codereview. > > > > > I would love to help, just tell me how could I assist > > > > > > > > So the first step is to have a script to convert a roundup sqlite > > > > database > > > > into a postgresql. Perhaps there is already existing script if so it > > > > needs to be > > > > tested. I can give you a database dump (user password are crypted with > > > > SHA :-) > > > > > > Have you done any progress on this topic? > > > > I check it. I think I could write a script that fill the PostgreSQL database > > with the SQLite data. > > Here is my script. > It seems to work, I will make some more tests and if everything works I will > migrate roundup. > > > By the way, I will try to make it generic like that it could be used to > > migrate Tryton database also. > > There is some hack in the script that are specific to roundup schema but the > basis should work for Tryton database. I did not test it but if someone wants > to test it, I will appreciate feedback.
I forgot to update the sequences. -- Cédric Krier B2CK SPRL Rue de Rotterdam, 4 4000 Liège Belgium Tel: +32 472 54 46 59 Email/Jabber: [email protected] Website: http://www.b2ck.com/
#!/usr/bin/env python
import optparse
import psycopg2
import sqlite3
import datetime
from roundup.date import Date
def _convert_date(value):
if value is None:
return value
return datetime.datetime(*Date(str(value)).get_tuple()[:6])
def _convert_boolean(value):
return bool(value)
_to_convert = {
'_msg': (('_date', _convert_date),),
'_user': (('_newissuecopy', _convert_boolean),),
}
def _convert(cursor, table, columns):
for row in cursor:
row = list(row)
extra_columns = ()
if table.startswith('_'):
extra_columns = (('_activity', _convert_date),
('_creation', _convert_date))
elif table.endswith('__journal'):
extra_columns = (('date', _convert_date),)
for column, fnct in (_to_convert.get(table, ()) + extra_columns):
if column in columns:
i = columns.index(column)
row[i] = fnct(row[i])
row = tuple(row)
yield row
def main(sqlite_cursor, pg_cursor):
sqlite_cursor.execute('SELECT name FROM sqlite_master '
'WHERE type = ?', ('table',))
for table, in sqlite_cursor.fetchall():
pg_cursor.execute('SELECT relname FROM pg_class '
'WHERE relkind = %s AND relname = %s', ('r', table,))
if not bool(pg_cursor.rowcount):
continue
sqlite_cursor.execute('SELECT * from "%s"' % table)
columns = [x[0] for x in sqlite_cursor.description]
query = ('INSERT INTO "%s" (%s) VALUES (%s)'
% (table, ','.join('"%s"' % x for x in columns),
','.join(('%s',) * len(columns))))
pg_cursor.executemany(query, _convert(sqlite_cursor, table, columns))
sqlite_cursor.execute('SELECT name, num FROM ids')
for name, num in sqlite_cursor:
pg_cursor.execute('ALTER SEQUENCE "_%s_ids" RESTART WITH %%s'
% name, (num,))
if __name__ == '__main__':
parser = optparse.OptionParser(version='0.1')
parser.add_option('--sqlite', dest='file', help='SQLite database file')
parser.add_option('--pg', dest='dsn', help='dsn for PostgreSQL')
opt, args = parser.parse_args()
pg_conn, sqlite_conn = None, None
pg_cursor, sqlite_cursor = None, None
try:
pg_conn = psycopg2.connect(opt.dsn)
pg_cursor = pg_conn.cursor()
sqlite_conn = sqlite3.connect(opt.file,
detect_types=sqlite3.PARSE_DECLTYPES)
sqlite_cursor = sqlite_conn.cursor()
main(sqlite_cursor, pg_cursor)
pg_conn.commit()
finally:
if pg_cursor:
pg_cursor.close()
if pg_conn:
pg_conn.close()
if sqlite_cursor:
sqlite_cursor.close()
if sqlite_conn:
sqlite_conn.close()
pgp8jyErefrn4.pgp
Description: PGP signature
