Hello ppl!
I'm trying to create a trac plugin to help on the env's hosted projects I18N
management.
I have a lot of ideas for it, yet, I'm stuck with the base of it, the database
layout.
As a general idea, think of it as the rosetta for trac plugin of even the
pootle for trac plugin.
I send attached the current database layout, which was initially meant to work
with foreign keys,
yet sqlite does not support that, duh! Either way I thought I should stick with
that design for
when trac moves to SQLAlchemy ;) which would make my life alot easier :)
Anyway, onto the subject which brought myself to you, the database layout.
The general idea is to import the catalog templates which are on the source
control system,
this is using babel(of course), populate the database with the catalog
template, then allow the
users coming to our trac env to create new translations and populate them.
These would of course need to be managed by an admin.
So, we need to store each version of the translation the users provide(per
msgid), and the admin
then decides which is the best suited. Of course, projects need to get a
manager for each of the
languages being translated as only one with full knowledge of the language will
know what is the
best and most suitable translation. But this is yet another step.
As I said, I have the current database layout attached and also the model being
used. Please, this
is really, really RAW code(and not all of it) since it's the first plug I'm
coding which relies
on the database.
The idea I have now is:
* A repo path to a catalog template has a catalog associated to it, which
itself has the several msgid's.
* Each msgid has user comments, auto comments, locations, flags, translations,
etc
I need to handle plurals so, msgid and msgid_plural and then the necessary
plural translations based on
the language some of them have more than 4 plural forms, way more.
I'd appreciate any comments, improvements or even ppl willing to help on coding
it, but specially,
if this is/should be the db layout to use.
Best Regards,
--
Pedro Algarvio
__ ___ ___ _______ ___ __
| Y .' _| _ .-----.' _| |_ .-----.----.-----.
|. | | _| 1___| _ | _| _|__| _ | _| _ |
|. | |__| |____ |_____|__| |____|__|_____|__| |___ |
|: 1 | |: 1 | |_____|
|::.. . | |::.. . | ufs [AT] ufsoft [DOT] org
`-------' `-------' ufs [AT] sapo [DOT] pt
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Trac
Development" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/trac-dev?hl=en
-~----------~----~----~----~------~----~------~--~---
from trac.db import Table, Column, Index, DatabaseManager
version = 1
tables = [
Table('catalog_path', key='id')[
Column('id', type='int', auto_increment=True),
Column('path', type='text', unique=True),
Column('name', type='unicode', unique=True),
Index(['path', 'name'])
],
Table('catalogs', key=('catalog_path_id', 'revision', 'locale'))[
Column('catalog_path_id', type='int'),
Column('revision', type='int'),
Column('locale'),
Column('header_comment', type='unicode'),
Column('copyright_holder', type='unicode'),
Column('msgid_bugs_address'),
Column('creation_date', type='timestamp'),
Column('revision_date', type='timestamp'),
Column('last_translator', type='unicode'),
Column('language_team', type='unicode'),
Column('charset'),
Column('fuzzy', type='boolean'),
],
Table('catalog_msgids', key=('id'))[
Column('id', type='int', auto_increment=True),
Column('catalogs_id', type='int'),
Column('msgid', type='unicode'),
Column('msgid_plural', type='unicode'),
Column('previous_id', type='unicode'),
Column('previous_id_plural', type='unicode'),
Column('obsolete', type='boolean'),
Index(['catalogs_id', 'msgid'])
],
Table('catalog_msgstrs', key=('catalog_msgid_id', 'idx', 'string'))[
Column('catalog_msgid_id', type='int'),
Column('idx', type='int'),
Column('string', type='unicode'),
Column('sid'),
Column('accepted', type='boolean'),
Index(['catalog_msgid_id', 'idx', 'string'])
],
Table('catalog_msgid_locations', key=('catalog_msgid_id', 'location',
'lineno'))[
Column('catalog_msgid_id', type='int'),
Column('location'),
Column('lineno', type='int'),
],
Table('catalog_message_auto_comments', key='catalog_msgid_id')[
Column('catalog_msgid_id', type='int'),
Column('auto_comment', type='unicode'),
],
Table('catalog_msgid_user_comments', key='catalog_msgid_id')[
Column('catalog_msgid_id', type='int'),
Column('user_comment', type='unicode'),
],
Table('catalog_msgid_flags', key='catalog_msgid_id')[
Column('catalog_msgid_id', type='int'),
Column('flag'),
],
]
def do_upgrade(env, ver, cursor):
db_connector, _ = DatabaseManager(env)._get_connector()
for table in tables:
for stmt in db_connector.to_sql(table):
try:
cursor.execute(stmt)
except Exception, err:
print stmt
raise err
# -*- coding: utf-8 -*-
# vim: sw=4 ts=4 fenc=utf-8
# =============================================================================
# $Id$
# =============================================================================
# $URL$
# $LastChangedDate$
# $Rev$
# $LastChangedBy$
# =============================================================================
# Copyright (C) 2007 UfSoft.org - Pedro Algarvio <[EMAIL PROTECTED]>
#
# Please view LICENSE for additional licensing information.
# =============================================================================
from UserDict import DictMixin
from babel.messages.pofile import read_po
from trac.util.datefmt import to_timestamp
from cStringIO import StringIO
class BaseDBClass(object):
def __init__(self, env):
self.env = env
self.log = env.log
self.db = self.env.get_db_cnx()
print "%s : inittialized" % self.__class__.__name__
def save(self):
raise NotImplementedError
def refresh(self):
raise NotImplementedError
class CatalogPath(BaseDBClass):
id = None
path = _path = None
name = _name = False
def __init__(self, env, name, path=None):
BaseDBClass.__init__(self, env)
self.path = path
self.name = name
self.refresh()
print 'name: %s path:%s' % (name, path)
def refresh(self):
print 'refreshing'
cursor = self.db.cursor()
cursor.execute('SELECT id, path, name from catalog_path '
'WHERE (name=%s AND path=%s) OR name=%s',
(self.name, self.path, self.name))
row = cursor.fetchone()
print 'fetched'
if row:
print 'path row', row
id, path, name = row
self.id = id
self.path = self._path = path
self.name = self._name = name
def save(self):
print 'saving'
cursor = self.db.cursor()
cursor.execute("UPDATE catalog_path SET path=%s, name=%s WHERE "
"id=%s AND path=%s AND name=%s",
(self.path, self.name,
self.id, self._path, self._name))
if not cursor.rowcount:
print 'saving new'
cursor.execute("INSERT INTO catalog_path (path, name) "
"VALUES (%s, %s)",
(self.path, self.name))
self.db.commit()
self.refresh()
def delete(self):
print 'Deleting: %s, %s, %s' % (self.id, self.path, self.name)
cursor = self.db.cursor()
cursor.execute('DELETE FROM catalog_path WHERE name=%s AND id=%s',
(self.name, self.id))
self.db.commit()
class Catalog(BaseDBClass):
catalog_path_id = None
header_comment = None
copyright_holder = None
msgid_bugs_address = None
creation_date = None
revision_date = None
language_team = None
last_translator = None
charset = None
fuzzy = None
content = None
def __init__(self, env, catalog_path, revision, content=None, locale=None):
BaseDBClass.__init__(self, env)
assert isinstance(catalog_path, CatalogPath)
self.catalog_path = catalog_path
self.catalog_path_id = catalog_path.id
self.revision = revision
self.locale = locale
if content:
self.content = StringIO(content.read())
self.refresh()
# setattr(self, 'messages', lambda x: self.get_messages())
# self.messages = self.get_messages()
print 'catalog inited'
def refresh(self):
cursor = self.db.cursor()
cursor.execute('SELECT catalog_path_id, locale, header_comment, '
'copyright_holder, msgid_bugs_address, '
#'creation_date, revision_date, '
'last_translator, language_team, '
'charset, fuzzy FROM catalogs WHERE catalog_path_id=%s '
'AND revision=%s',
(self.catalog_path_id, self.revision,))
print 'select executed'
row = cursor.fetchone()
if row:
print 'we have row'
catalog_path_id, locale, header_comment, copyright_holder, msgid_bugs_address, \
last_translator, language_team, \
charset, fuzzy = row
self.catalog_path_id = catalog_path_id
self.locale = locale
self.header_comment = header_comment
self.copyright_holder = copyright_holder
self.msgid_bugs_address = msgid_bugs_address
#self.creation_date = creation_date
#self.revision_date = revision_date
self.last_translator = last_translator
self.language_team = language_team
self.charset = charset
self.fuzzy = fuzzy
else:
print 'calling parse'
self.parse()
def parse(self):
if not self.content:
return
catalog = read_po(self.content)
self.msgid_bugs_address = catalog.msgid_bugs_address
self.copyright_holder = catalog.copyright_holder
self.charset = catalog.charset
#self.creation_date = catalog.creation_date
#self.revision_date = catalog.revision_date
self.language_team = catalog.language_team
self.last_translator = catalog.last_translator
self.header_comment = catalog.header_comment
self.fuzzy = catalog.fuzzy
print 'importing messages'
# print 'catalog', catalog
# messages =
# print 'messages', messages
messages = list(catalog)[1:]
#print 'len', len(messages)
for msg in messages:
print 11, msg.__dict__
message = MsgID(self, msg.id, msg.previous_id)
message.save()
string = []
if isinstance(msg.string, basestring):
string = [msg.string]
for idx, string in enumerate(string):
if string:
translation = MsgSTR(message, idx, string)
translation.save()
for entry in msg.locations:
location, lineno = entry
msgid_location = Location(message, location, lineno)
msgid_location.save()
def save(self):
cursor = self.db.cursor()
cursor.execute('UPDATE catalogs SET catalog_path_id=%s, revision=%s, '
'locale=%s, header_comment=%s, '
'copyright_holder=%s, msgid_bugs_address=%s, '
'creation_date=%s, revision_date=%s, '
'last_translator=%s, language_team=%s, charset=%s, '
'fuzzy=%s',
(self.catalog_path_id, self.revision,
self.locale, self.header_comment,
self.copyright_holder, self.msgid_bugs_address,
self.creation_date, self.revision_date,
self.last_translator, self.language_team,
self.charset, self.fuzzy))
if not cursor.rowcount:
print 'entry not in db'
cursor.execute('INSERT INTO catalogs (catalog_path_id, revision, '
'locale, header_comment, copyright_holder, '
'msgid_bugs_address, creation_date, revision_date, '
'last_translator, language_team, charset, fuzzy) '
'VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, '
'%s, %s)',
(self.catalog_path_id, self.revision, self.locale,
self.header_comment, self.copyright_holder,
self.msgid_bugs_address, self.creation_date,
self.revision_date, self.last_translator,
self.language_team, self.charset, self.fuzzy))
self.db.commit()
def get_messages(self):
print 'fetching messages by property for catalog_path_id: %s' % self.catalog_path_id
messages = []
cursor = self.db.cursor()
cursor.execute('SELECT * from catalog_msgids WHERE catalogs_id=%s',
(self.catalog_path_id,))
rows = cursor.fetchall()
for row in rows:
id, catalogs_id, msgid, msgid_plural, previous_id, \
previous_id_plural, obsolete = row
msg = MsgID(self,
(msgid, msgid_plural),
(previous_id, previous_id_plural))
#print 'locations', msg.locations
messages.append(msg)
# self.messages = messages
return messages
messages = property(get_messages)
class MsgID(BaseDBClass):
id = None
catalogs_id = None
msgid = _msgid = None
msgid_plural = _msgid_plural = None
previous_id = _previous_id = None
previous_id_plural = _previous_id_plural = None
obsolete = _obsolete = False
def __init__(self, db_catalog, msgid, previous_id):
assert isinstance(db_catalog, Catalog)
BaseDBClass.__init__(self, db_catalog.env)
self.db_catalog = db_catalog
self.catalogs_id = db_catalog.catalog_path_id
if isinstance(msgid, (list, tuple)) and len(msgid) > 1:
self.msgid, self.msgid_plural = msgid
else:
self.msgid = msgid
if previous_id and len(previous_id) > 1:
self.previous_id, self.previous_id_plural = previous_id
else:
self.previous_id = previous_id or ''
self.refresh()
def refresh(self):
print 'looking up msgid on db'
cursor = self.db.cursor()
print 'got cursor'
print '000', self.catalogs_id, self.msgid, self.msgid_plural, \
self.previous_id, self.previous_id_plural, self.obsolete
try:
cursor.execute('SELECT * FROM catalog_msgids WHERE '
'catalogs_id=%s AND msgid=%s',
(self.catalogs_id, self.msgid))
except Exception, err:
print 'ERR', err
print 'select done'
row = cursor.fetchone()
if row:
print 'got row'
id, catalog_id, msgid, msgid_plural, previous_id, \
previous_id_plural, obsolete = row
self.id = id
self.msgid = msgid
self.msgid_plural = msgid_plural
self.previous_id = previous_id
self.previous_id_plural = previous_id_plural
self.obsolete = obsolete
def save(self):
print 'saving msgid'
try:
cursor = self.db.cursor()
cursor.execute('UPDATE catalog_msgids SET catalogs_id=%s, msgid=%s, '
'msgid_plural=%s, previous_id=%s, '
'previous_id_plural=%s, obsolete=%s WHERE '
'catalogs_id=%s AND msgid=%s',
(self.catalogs_id, self.msgid, self.msgid_plural,
self.previous_id, self.previous_id_plural,
self.obsolete, self.catalogs_id, self.msgid))
if not cursor.rowcount:
print 'creating new msgid'
cursor.execute('INSERT INTO catalog_msgids (catalogs_id, msgid, '
'msgid_plural, previous_id, previous_id_plural, '
'obsolete) VALUES (%s, %s, %s, %s, %s, %s)',
(self.catalogs_id, self.msgid, self.msgid_plural,
self.previous_id, self.previous_id_plural,
self.obsolete))
self.db.commit()
except Exception, err:
print err
raise err
self.refresh()
def get_locations(self):
#print 'getting locations for msgid.id: %s' % self.id
locations = []
cursor = self.db.cursor()
cursor.execute('SELECT * FROM catalog_msgid_locations WHERE '
'catalog_msgid_id=%s',
(self.id,))
rows = cursor.fetchall()
for row in rows:
#print 'location row for msgid_id %s' % self.id, row
msgid_id, loc, lineno = row
location = Location(self, loc, lineno)
locations.append(location)
return locations
locations = property(get_locations)
class MsgIDBaseClass(BaseDBClass):
def __init__(self, db_msgid):
assert isinstance(db_msgid, MsgID)
BaseDBClass.__init__(self, db_msgid.env)
self.msgid_id = db_msgid.id
class MsgSTR(MsgIDBaseClass):
idx = _idx = None
string = _string = None
def __init__(self, db_msgid, idx, string):
MsgIDBaseClass.__init__(self, db_msgid)
self.idx = self._idx = idx
self.string = self._string = string
def refresh(self):
cursor = self.db.cursor()
cursor.execute('SELECT * from catalog_msgstrs WHERE catalog_msgid_id=%s'
'AND idx=%s AND string=%s',
(self.msgid_id, self._idx or self.idx,
self._string or self.string))
row = cursor.fetchone()
if row:
msgid_id, idx, string = row
self.msgid_id = msgid_id
self.idx = idx
self.string = string
def save(self):
print "%s: Updating msgid_id: %s ix: %s string: %s" % (
self.__class__.__name__, self.msgid_id, self.idx, self.string)
cursor = self.db.cursor()
cursor.execute('UPDATE catalog_msgstrs SET catalog_msgid_id=%s, '
'idx=%s, string=%s WHERE catalog_msgid_id=%s AND '
'idx=%s AND string=%s',
(self.msgid_id, self.idx, self.string,
self.msgid_id, self._idx, self._string))
if not cursor.rowcount:
print "%s: Not found, creating new entry" % self.__class__.__name__
cursor.execute('INSERT INTO catalog_msgstrs (catalog_msgid_id, '
'idx, string) VALUES (%s, %s, %s)',
(self.msgid_id, self.idx, self.string))
self.db.commit()
self.refresh()
class Location(MsgIDBaseClass):
location = _location = None
lineno = _lineno = None
def __init__(self, db_msgid, location, lineno):
MsgIDBaseClass.__init__(self, db_msgid)
self.location = location
self.lineno = lineno
self.refresh()
def refresh(self):
cursor = self.db.cursor()
cursor.execute('SELECT * from catalog_msgid_locations WHERE '
'catalog_msgid_id=%s AND location=%s AND lineno=%s',
(self.msgid_id, self.location, self.lineno))
row = cursor.fetchone()
if row:
msgid_id, location, lineno = row
self.msgid_id = msgid_id
self.location = self._location = location
self.lineno = self._lineno = lineno
def save(self):
cursor = self.db.cursor()
cursor.execute('UPDATE catalog_msgid_locations SET catalog_msgid_id=%s,'
' location=%s, lineno=%s WHERE catalog_msgid_id=%s AND '
'location=%s AND lineno=%s',
(self.msgid_id, self.location, self.lineno,
self.msgid_id, self._location, self._lineno))
if not cursor.rowcount:
cursor.execute('INSERT INTO catalog_msgid_locations ('
'catalog_msgid_id, location, lineno) VALUES '
'(%s, %s, %s)',
(self.msgid_id, self.location, self.lineno))
self.db.commit()
self.refresh()
