Developer mailing list for the Trac Project

Terje S. | 4 Jul 2010 16:30

[Trac-dev] Refactoring the Trac data model - relational approach

Hello list,

I've been observing since I deployed .11 for a small team last year.

I think the normalization topic raised by Felix in the look beyond 0.12 
thread is an important area to focus on; I ran into so many issues with 
this, I seriously considered abandoning half way. The problem manifests as 
difficulty to change the core systems; anything *slightly* outside the 
feature scope requires a lot of work to complete, even if the change is 
trivial in principle.

Christian replies to Felix:
> I'm not sure if I understand what you mean by "denormalized database". 
> Are you refering to the duplication of data in ticket related tables, as 
> discussed in #1890, or to the fact we don't use yet surrogate 
> keys except for the new repository table? Or the fact that relationships 
> between tables rely on "implicit" foreign keys (e.g. ticket -> milestone)?

I have no trouble relating to the original statement, it refers to all of 
the above and more; the general state of the data model, if you like. We 
are using the database to store rather complex information, but without 
the benefits of constraints(relations), triggers, views, checks or locking 
(to say nothing of procedures and other modern features). In the words of 
Joe Celko, Trac is in the magnetic tape mindset, using the database as if 
it was a serial I/O device. Consider the Association pattern in context:

     .- - - - - - - - - - - - - -.
  .< : "Resource Storage System" :
  :  '- - -+- - - - - - - - - - -'                           ____
  :        V                                                 V  |

(Continue reading)

headers

Remy Blank | 4 Jul 2010 21:27

Re: [Trac-dev] Refactoring the Trac data model - relational approach

Terje S. wrote:
> All that said, I hope I did not offend or kill anyone with the lengthy 
> amounts of text in this post. I realize it may be outside the Trac 
> philosophy, scope, or simply beating a dead horse.. So be it! 

Thank you for your constructive criticism. No, you didn't offend anyone,
and your comments are certainly valid.

My guess (I wasn't there when it started) is that none of the project
initiators was a database expert, and so they did the simplest thing
that would work, which retrospectively wasn't so bad but still not ideal.

> I would be happy to contribute to modelling as best I can, but I'm not sure 
> when the team can consider such fundamental philosophical and architectural 
> changes, if at all? (it does imply a more or less complete rewrite)

I'm sure your input would be highly appreciated. None of the current
developers is a DB expert either (AFAIK, I'm certainly not one, quite
the opposite), so any help is welcome.

I have two concerns with such a restructuring:

 - Even if a complete migration requires a complete rewrite, is it
possible to migrate progressively from the current to the new structure?
I don't believe in big, monolithic rewrites, as they are a sure way to
stall the project for a long time. Say, we introduce the concept of a
"project" in 0.13, we should design it according to the new structure,
and integrate it with the old structure. Or migrate one subsystem (e.g.
milestones) to the new structure, at the same time as we add a new
functionality (e.g. milestone versioning).

(Continue reading)

headers

Noah Kantrowitz | 4 Jul 2010 21:35

Re: [Trac-dev] Refactoring the Trac data model - relational approach


On Jul 4, 2010, at 12:27 PM, Remy Blank wrote:

> Terje S. wrote:
>> All that said, I hope I did not offend or kill anyone with the lengthy 
>> amounts of text in this post. I realize it may be outside the Trac 
>> philosophy, scope, or simply beating a dead horse.. So be it! 
> 
> Thank you for your constructive criticism. No, you didn't offend anyone,
> and your comments are certainly valid.
> 
> My guess (I wasn't there when it started) is that none of the project
> initiators was a database expert, and so they did the simplest thing
> that would work, which retrospectively wasn't so bad but still not ideal.
> 
>> I would be happy to contribute to modelling as best I can, but I'm not sure 
>> when the team can consider such fundamental philosophical and architectural 
>> changes, if at all? (it does imply a more or less complete rewrite)
> 
> I'm sure your input would be highly appreciated. None of the current
> developers is a DB expert either (AFAIK, I'm certainly not one, quite
> the opposite), so any help is welcome.
> 
> I have two concerns with such a restructuring:
> 
> - Even if a complete migration requires a complete rewrite, is it
> possible to migrate progressively from the current to the new structure?
> I don't believe in big, monolithic rewrites, as they are a sure way to
> stall the project for a long time. Say, we introduce the concept of a
> "project" in 0.13, we should design it according to the new structure,

(Continue reading)

headers

Steffen Hoffmann | 5 Jul 2010 11:48

Re: [Trac-dev] Refactoring the Trac data model - relational approach

Noah Kantrowitz wrote:
> On Jul 4, 2010, at 12:27 PM, Remy Blank wrote:
> 
>> Terje S. wrote:
[...]
>> - I have read a few times that although a normalized database
>> schema makes it very flexible and general, it also has a
>> performance impact. From a layman point of view, having to join
>> multiple tables has to be slower than not having to join tables,
>> hasn't it? Again, I'm a noob in DB technology, so don't laugh too
>> much if the question is stupid.
> 
> Also remember that 1) most advanced features like triggers are not
> standardized between server implementations enough to be useful and
> 2) SQLite supports only the most basic of these features and is still
> the default backend for Trac. IIRC SQLite parses but ignores FKs, so
> we can't even depend on those. Also because the reports system
> depends on humans being able to write simple queries, it is very nice
> to have a more human-friendly schema.

I'm not an expert in the DB field, but wouldn't it be possible to have
an abstraction layer to do needed table joining in background and not
expect it from the admin or user setting up custom queries?

IMHO this abstraction could become handy for migration to another DB
schema as well. If it would be possible to do such an abstraction layer
one could transparently replace the DB schema and proceed with migration
to direct use of the new schema later and in steps.

(Continue reading)

headers

Terje S. | 5 Jul 2010 20:47

Re: [Trac-dev] Refactoring the Trac data model - relational approach

On Sun, Jul 04, 2010 at 12:35:36PM -0700, Noah Kantrowitz wrote:
> Also remember that 1) most advanced features like triggers are not 
> standardized between server implementations enough to be useful and 

I absolutely agree that vendor-specific solutions are a big no-no, however
triggers do not always fall in this category. Granted, the syntax is 
different, but they react to events on the data in all cases. I'm not 
proposing any major use of them for the standard model, but there may 
be a few cases where they prove useful down the line.

> 2) SQLite supports only the most basic of these features and is still 
> the default backend for Trac. IIRC SQLite parses but ignores FKs, so we 
> can't even depend on those. 

Fortunally as of 3.6.19 this is no longer the case, foreign keys are
supported! That's been out for over a year, so conceivably we will not 
have to worry about older SQLite at 1.0 release time anyway  So by now, 
SQLite supports all the major requiremens, plus can be tweaked 
with use of attach, memory databases etc if it should prove neccessary. 
Supporting older SQLite will require to rewrite the most important 
foreign keys as triggers for those versions.

On a sidenote, I would argue for a general reccommendation of postgres 
for the future versions. It is a much better fit for the multi-user, 
database-centric solution required to support the multiproject, 
distributed and such capabilities.

> Also because the reports system depends on humans being able to write 
> simple queries, it is very nice to have a more human-friendly schema.

(Continue reading)

headers

Noah Kantrowitz | 5 Jul 2010 21:24

Re: [Trac-dev] Refactoring the Trac data model - relational approach


On Jul 5, 2010, at 11:47 AM, Terje S. wrote:

> On Sun, Jul 04, 2010 at 12:35:36PM -0700, Noah Kantrowitz wrote:
>> Also remember that 1) most advanced features like triggers are not
>> standardized between server implementations enough to be useful and
>
> I absolutely agree that vendor-specific solutions are a big no-no,  
> however
> triggers do not always fall in this category. Granted, the syntax is
> different, but they react to events on the data in all cases. I'm not
> proposing any major use of them for the standard model, but there may
> be a few cases where they prove useful down the line.
>
>> 2) SQLite supports only the most basic of these features and is still
>> the default backend for Trac. IIRC SQLite parses but ignores FKs,  
>> so we
>> can't even depend on those.
>
> Fortunally as of 3.6.19 this is no longer the case, foreign keys are
> supported! That's been out for over a year, so conceivably we will not
> have to worry about older SQLite at 1.0 release time anyway  So  
> by now,
> SQLite supports all the major requiremens, plus can be tweaked
> with use of attach, memory databases etc if it should prove  
> neccessary.
> Supporting older SQLite will require to rewrite the most important
> foreign keys as triggers for those versions.
>
> On a sidenote, I would argue for a general reccommendation of postgres

(Continue reading)

headers

Terje S. | 5 Jul 2010 21:23

Re: [Trac-dev] Refactoring the Trac data model - relational approach

On Mon, Jul 05, 2010 at 12:24:57PM -0700, Noah Kantrowitz wrote:
> Name one such system that expects users to write direct SQL? Not to
> be rude, but this all sounds very much like it is coming directly
> from a book about DB theory, not from a practical assessment of what

I've had specific experience with at least a few commercial products 
that use this principle, and I'm sure there are more.

http://www.protonic-software.com/
http://www.patrix.com/

We are not expecting mere mortals to touch these reports in any more than 
they do today, and we are not abandoning a query mechanism. Reports will 
have to be created one way or the other, and personallyI prefer a 
standard approach using SQL to the system du jour.

> would help Trac improve. The only real bonus anyone has come up with
> to "fix" our schema is that it would be easier to transition to
> SQLAlchemy as an ORM. That has been a rather contested feature, and

I think there are huge advantages in getting rid of anamolies in the 
system, the effectiveness of different approaches are certainly debatable.

> The current system hasn't particularly limited us or plugin devs
> that I know of, so I'm not sure why this is even being discussed, it

I think there is a fundamental connection with this and the difficulty 
of adding features, like say using custom fields on milestones, or drawing 
an accurate gantt chart of the project. I'm not neccesarily right, but 
that's left for you guys to argue against, or support..

(Continue reading)

headers

Josh Godsiff | 6 Jul 2010 05:40

Re: [Trac-dev] Refactoring the Trac data model - relational approach

On 6/7/2010 5:24 AM, Noah Kantrowitz wrote:
> Name one such system that expects users to write direct SQL? Not to be 
> rude, but this all sounds very much like it is coming directly from a 
> book about DB theory, not from a practical assessment of what would 
> help Trac improve. The only real bonus anyone has come up with to 
> "fix" our schema is that it would be easier to transition to 
> SQLAlchemy as an ORM. That has been a rather contested feature, and as 
> of yet I don't think any work has been done on it (SQLAlchemy as a 
> connection broker is a different thing entirely). DB performance has 
> never really been of major importance for Trac one way or the other, 
> mostly because our storage needs are rather simple in nature. The 
> current system hasn't particularly limited us or plugin devs that I 
> know of, so I'm not sure why this is even being discussed, it seems 
> like a pretty cut and dry issue to me. If you want to talk about 
> moving Trac to an ORM (SQLAlchemy, Storm, etc) thats a different 
> issue, but don't drag relational modeling and normalization into it 
> because those are really non-issues.
>
> --Noah
>

Just my two cents, but my company has a fairly in-depth real-world case 
where we wanted to extend the Ticket model, system, and a couple of the 
related subsystems (specifically the changelog) in order to meet our own 
requirements. We concluded that as Trac's codebase currently stands, 
this would take too much time to be cost-effective, and that the main 
reason for that was non-normalised tables. (Specifically, the 
ticket_change table, and the complete lack of a users table). 
Normalisation would have allowed me to roll out a good solution in the

(Continue reading)

headers

Noah Kantrowitz | 6 Jul 2010 05:46

Re: [Trac-dev] Refactoring the Trac data model - relational approach


On Jul 5, 2010, at 8:40 PM, Josh Godsiff wrote:

>
> On 6/7/2010 5:24 AM, Noah Kantrowitz wrote:
>> Name one such system that expects users to write direct SQL? Not to  
>> be rude, but this all sounds very much like it is coming directly  
>> from a book about DB theory, not from a practical assessment of  
>> what would help Trac improve. The only real bonus anyone has come  
>> up with to "fix" our schema is that it would be easier to  
>> transition to SQLAlchemy as an ORM. That has been a rather  
>> contested feature, and as of yet I don't think any work has been  
>> done on it (SQLAlchemy as a connection broker is a different thing  
>> entirely). DB performance has never really been of major importance  
>> for Trac one way or the other, mostly because our storage needs are  
>> rather simple in nature. The current system hasn't particularly  
>> limited us or plugin devs that I know of, so I'm not sure why this  
>> is even being discussed, it seems like a pretty cut and dry issue  
>> to me. If you want to talk about moving Trac to an ORM (SQLAlchemy,  
>> Storm, etc) thats a different issue, but don't drag relational  
>> modeling and normalization into it because those are really non- 
>> issues.
>>
>> --Noah
>>
>
> Just my two cents, but my company has a fairly in-depth real-world  
> case where we wanted to extend the Ticket model, system, and a  
> couple of the related subsystems (specifically the changelog) in  
> order to meet our own requirements. We concluded that as Trac's

(Continue reading)

headers

Josh Godsiff | 6 Jul 2010 06:02

Re: [Trac-dev] Refactoring the Trac data model - relational approach


On 6/7/2010 1:46 PM, Noah Kantrowitz wrote:
>
> On Jul 5, 2010, at 8:40 PM, Josh Godsiff wrote:
>
>>
>> On 6/7/2010 5:24 AM, Noah Kantrowitz wrote:
>>> Name one such system that expects users to write direct SQL? Not to 
>>> be rude, but this all sounds very much like it is coming directly 
>>> from a book about DB theory, not from a practical assessment of what 
>>> would help Trac improve. The only real bonus anyone has come up with 
>>> to "fix" our schema is that it would be easier to transition to 
>>> SQLAlchemy as an ORM. That has been a rather contested feature, and 
>>> as of yet I don't think any work has been done on it (SQLAlchemy as 
>>> a connection broker is a different thing entirely). DB performance 
>>> has never really been of major importance for Trac one way or the 
>>> other, mostly because our storage needs are rather simple in nature. 
>>> The current system hasn't particularly limited us or plugin devs 
>>> that I know of, so I'm not sure why this is even being discussed, it 
>>> seems like a pretty cut and dry issue to me. If you want to talk 
>>> about moving Trac to an ORM (SQLAlchemy, Storm, etc) thats a 
>>> different issue, but don't drag relational modeling and 
>>> normalization into it because those are really non-issues.
>>>
>>> --Noah
>>>
>>
>> Just my two cents, but my company has a fairly in-depth real-world 
>> case where we wanted to extend the Ticket model, system, and a couple 
>> of the related subsystems (specifically the changelog) in order to

(Continue reading)

headers

Noah Kantrowitz | 6 Jul 2010 06:16

Re: [Trac-dev] Refactoring the Trac data model - relational approach


On Jul 5, 2010, at 9:02 PM, Josh Godsiff wrote:

>
>
> On 6/7/2010 1:46 PM, Noah Kantrowitz wrote:
>>
>> On Jul 5, 2010, at 8:40 PM, Josh Godsiff wrote:
>>
>>>
>>> On 6/7/2010 5:24 AM, Noah Kantrowitz wrote:
>>>> Name one such system that expects users to write direct SQL? Not  
>>>> to be rude, but this all sounds very much like it is coming  
>>>> directly from a book about DB theory, not from a practical  
>>>> assessment of what would help Trac improve. The only real bonus  
>>>> anyone has come up with to "fix" our schema is that it would be  
>>>> easier to transition to SQLAlchemy as an ORM. That has been a  
>>>> rather contested feature, and as of yet I don't think any work  
>>>> has been done on it (SQLAlchemy as a connection broker is a  
>>>> different thing entirely). DB performance has never really been  
>>>> of major importance for Trac one way or the other, mostly because  
>>>> our storage needs are rather simple in nature. The current system  
>>>> hasn't particularly limited us or plugin devs that I know of, so  
>>>> I'm not sure why this is even being discussed, it seems like a  
>>>> pretty cut and dry issue to me. If you want to talk about moving  
>>>> Trac to an ORM (SQLAlchemy, Storm, etc) thats a different issue,  
>>>> but don't drag relational modeling and normalization into it  
>>>> because those are really non-issues.
>>>>
>>>> --Noah

(Continue reading)

headers

Josh Godsiff | 6 Jul 2010 08:43

Re: [Trac-dev] Refactoring the Trac data model - relational approach

On 6/7/2010 2:16 PM, Noah Kantrowitz wrote:
> I will grant you that one, but I don't see that as a normalization 
> concern. I don't see how the schema itself caused a problem, it is the 
> entire design of how comments work (they aren't a top-level entity, 
> unversioned, etc). The schema reflects the system design, and does so 
> quite well. Normalizing the schema will do nothing without fixing the 
> rest of the system, and fixing the system doesn't require any systemic 
> changes to how our schema works. On top of this there is secondary 
> problem of how to allow plugins to extend core Trac tables with 
> additional metadata. This, again, isn't related to the schema, but is 
> a structural question that as of yet I've not heard a good answer to.

I'm not arguing that doing it the way I suggested wouldn't require 
rewriting more than the model. What I'm saying is that if the system 
were implemented using a normalised, relational approach (in-so-far as 
possible), it would make changes like the one I suggested far more easy, 
and probably make development in general far more fluid.

I also think we possibly differ on design philosophies - I would never 
design a /schema/ to reflect the /system/. System design and 
requirements should inform what is stored in the db and the relationship 
between different data structures, certainly. However I don't believe it 
should ever inform /how/ that data is stored. In my experience, it is 
almost always better to design your data-structures in the best possible 
way, and then build your system around that.

On the point about Plugins extending core tables, normalisation again 
makes this easier - with a normalised structure, it's basically as 
simple as adding a column directly via SQL. If you need/want some sort

(Continue reading)

headers

Carsten Klein | 9 Jul 2010 17:36

Re: [Trac-dev] Refactoring the Trac data model - relational approach


> I assume I don't need to point out why having a Users table would make
> it easier to cross-reference whether a user had the requisite
> permissions and/or was in the requisite group.

What is wrong with the self recursive approach of the permission table?

The information is all in there.

Extracting information from that table into different tables would mean
introduction of extra reduncancy with not benefit in overall processing,
as you would still be required to iterate over the permissions/actions
granted for a given group or user thereof.

-- Carsten

--

-- 
You received this message because you are subscribed to the Google Groups "Trac Development" group.
To post to this group, send email to trac-dev <at> googlegroups.com.
To unsubscribe from this group, send email to trac-dev+unsubscribe <at> googlegroups.com.
For more options, visit this group at http://groups.google.com/group/trac-dev?hl=en.

headers

Josh Godsiff | 12 Jul 2010 04:20

Re: [Trac-dev] Refactoring the Trac data model - relational approach

On 10/7/2010 1:36 AM, Carsten Klein wrote:
>    
>> I assume I don't need to point out why having a Users table would make
>> it easier to cross-reference whether a user had the requisite
>> permissions and/or was in the requisite group.
>>      
> What is wrong with the self recursive approach of the permission table?
>
> The information is all in there.
>
> Extracting information from that table into different tables would mean
> introduction of extra reduncancy with not benefit in overall processing,
> as you would still be required to iterate over the permissions/actions
> granted for a given group or user thereof.
>
> -- Carsten
>
>    

I'll concede there's nothing intrinsically wrong with it in the context 
of it's own self contained purpose. I guess my actual beef with it is 
there's no mechanism (that I know of) to check whether a User is in a 
group, as opposed to has a permission, but that's more of a software 
problem. The argument for having an actual User table is that things 
start to fall down when you start integrating all these different tables 
that have their own use for 'User' into a broader system.

For instance, a User has permissions, and may belong to a Group, which 
also has Permissions. Indeed, I believe that there is currently no 
distinction between User and Group as far as that table is concerned.(?)

(Continue reading)

headers

Remy Blank | 12 Jul 2010 17:11

Re: [Trac-dev] Refactoring the Trac data model - relational approach

Josh Godsiff wrote:
> My personal beef is with the 'ticket_change' table and its use for 
> storing comments. From what I can tell, it's a fair mess that could be 
> neatened up quite a bit, especially since as of 0.12 we're now storing 
> revisions to comments as well. (Seriously, who's bright idea was it to 
> do /all/ that in the same table?)

The comment revisions were my fault. The driving forces that led to that
result were:

 - Do not break any existing code.
 - Avoid a database upgrade, as (I thought) we were close to releasing.
 - As the comments were in that table already, having the comment
revisions there didn't seem like a stretch.
 - Initially, we didn't want to keep older comment revisions at all, but
in the end we found that it would be useful and we found a "cheap" hack
to keep them.

Looking back, it may have been easier (and certainly cleaner) to just
add a table "ticket_comment_revisions" and store them there. Or anything
else, really.

I'd be really happy to see what a clean DB schema for the ticket
functionality should look like. No new functionality, just the same as
today but in a clean structure.

-- Remy

headers

Carsten Klein | 12 Jul 2010 22:07

Re: [Trac-dev] Refactoring the Trac data model - relational approach


> I'd be really happy to see what a clean DB schema for the ticket
> functionality should look like. No new functionality, just the same as
> today but in a clean structure.

Well, you asked for it. Here would be my (incomplete) approach to it.
See also the attached class diagram.

It basically centers around the idea of generic trac, but keeping a slow
transition in mind, so that not too much of the existing code must be
changed, namely:

table ticket
  represents an object, having an id and a few additional fields like for
example edit_date and creation_date, creator and editor.
  additionally, intrinsic properties of the ticket, for example
  milestone, component, and so on will remain with the ticket object,
  also the assignee and the status.
  once created, ticket ids must never change.
  IMPORTANT: not so the description, it will be made a system defined
  ticket_field.

table ticket_field
  represents a field of the ticket, either system defined or customly
  defined (field_type).
  it will have a value_type and multiple attributes where the actual value
  then will be stored in (see for example JBPM, variable instances)
  so we will have attributes for int_value, date_value, datetime_value,
  string_value and so on
  in this table, always the latest revision of the field will be stored,

(Continue reading)

headers

Christian Boos | 13 Jul 2010 12:29

Ticket storage refactoring (was Re: [Trac-dev] Refactoring the Trac data model - relational approach)

On 7/12/2010 10:07 PM, Carsten Klein wrote:
>> I'd be really happy to see what a clean DB schema for the ticket
>> functionality should look like. No new functionality, just the same as
>> today but in a clean structure.
>>
> Well, you asked for it. Here would be my (incomplete) approach to it.
> See also the attached class diagram.
>
> It basically centers around the idea of generic trac, but keeping a slow
> transition in mind, so that not too much of the existing code must be
> changed, namely:
>
> table ticket
>    represents an object, having an id and a few additional fields like for
> example edit_date and creation_date, creator and editor.
>    additionally, intrinsic properties of the ticket, for example
>    milestone, component, and so on will remain with the ticket object,
>    also the assignee and the status.
>    once created, ticket ids must never change.
>    IMPORTANT: not so the description, it will be made a system defined
>    ticket_field.
>
> table ticket_field
>    represents a field of the ticket, either system defined or customly
>    defined (field_type).
>    it will have a value_type and multiple attributes where the actual value
>    then will be stored in (see for example JBPM, variable instances)
>    so we will have attributes for int_value, date_value, datetime_value,
>    string_value and so on
>    in this table, always the latest revision of the field will be stored,

(Continue reading)

headers

Carsten Klein | 13 Jul 2010 22:05

Re: Ticket storage refactoring (was Re: [Trac-dev] Refactoring the Trac data model - relational approach)


> Ok, so a `ticket_field` is a kind of "union". I suppose columns not
> relevant for a row are set to NULL.

Yes, that is basically it. And what is more, this, in the long term, could
be moved over to a more generic approach, as in GenericTrac, where we have
multiple subsystems reusing the field table for their own purposes.

> IIUC, with this approach, reconstructing the ticket state at revision
> `i` is a bit cumbersome: if you have 1, ..., i, ..., n, ... revisions,
> in order to find the value for field `f` at revision `i`, you have to
> find the minimum revision `n`, with `(n > i)` storing a change for `f`.
> If there's no such `n`, take the current value of `f` in `ticket_field`.

Yes that is true, so eventually you will have to select all the revisions
inbetween and programmatically setup a view, just like the context object
in genshi, where the last revision n represents the first object to be
looked up for a specific ticket field. Of course, this will load
additional data into the system that is unwanted, so you'd also might just
iterate over the individual revisions found in the database in a
descending order, most recent revision first.

fields = select tr.*, tf.* from ticket_revision tr left join
ticket_field_revision tfr on tr.id == tfr.rev_id where tr.ticket_id = :id
order by tr.creation_date desc

dict = {}
for field in fields:
  if field.name not in dict:
    dict[field.name] = field.value

(Continue reading)

headers

Carsten Klein | 14 Jul 2010 00:06

Re: Ticket storage refactoring (was Re: [Trac-dev] Refactoring the Trac data model - relational approach)


Hotfix:

> fields = select tr.*, tf.* from ticket_revision tr left join
> ticket_field_revision tfr on tr.id == tfr.rev_id where tr.ticket_id = :id
> order by tr.creation_date desc

dict = {}
for field in fields:
  dict[field.name] = field.value

-- Carsten

--

-- 
You received this message because you are subscribed to the Google Groups "Trac Development" group.
To post to this group, send email to trac-dev <at> googlegroups.com.
To unsubscribe from this group, send email to trac-dev+unsubscribe <at> googlegroups.com.
For more options, visit this group at http://groups.google.com/group/trac-dev?hl=en.

headers

Carsten Klein | 12 Jul 2010 20:54

Re: [Trac-dev] Refactoring the Trac data model - relational approach


> I'll concede there's nothing intrinsically wrong with it in the context
> of it's own self contained purpose. I guess my actual beef with it is
> there's no mechanism (that I know of) to check whether a User is in a
> group, as opposed to has a permission, but that's more of a software

Here would be some queries for it, if you need them:

All defined groups

select distinct username from permission where username not in (select
distinct action from permission);

All defined users

select distinct username from permission where username in (select
distinct action from permission);

Of course this will fail when you have users that also represent groups,
or groups that are subgroups of other groups. Such users and groups will
then be reported as users.

As such, yes, a separate users table with an extra field used as a flag
whether this is a group would be nice to have.

> For instance, a User has permissions, and may belong to a Group, which
> also has Permissions. Indeed, I believe that there is currently no
> distinction between User and Group as far as that table is concerned.(?)

Yes, you are absolutely correct.

(Continue reading)

headers

Noah Kantrowitz | 12 Jul 2010 22:47

RE: [Trac-dev] Refactoring the Trac data model - relational approach

> -----Original Message-----
> From: trac-dev <at> googlegroups.com [mailto:trac-dev <at> googlegroups.com] On
> Behalf Of Carsten Klein
> Sent: Monday, July 12, 2010 11:54 AM
> To: trac-dev <at> googlegroups.com
> Subject: Re: [Trac-dev] Refactoring the Trac data model - relational
> approach
> 
> 
> > I'll concede there's nothing intrinsically wrong with it in the
> context
> > of it's own self contained purpose. I guess my actual beef with it is
> > there's no mechanism (that I know of) to check whether a User is in a
> > group, as opposed to has a permission, but that's more of a software
> 
> Here would be some queries for it, if you need them:
> 
> All defined groups
> 
> select distinct username from permission where username not in (select
> distinct action from permission);

Remember that groups can be provided by things other than the default
backend, for example from LDAP. You can never rely on the DB to know these
things in anything related to reusable code.

--Noah

--

-- 
You received this message because you are subscribed to the Google Groups "Trac Development" group.

(Continue reading)

headers

Josh Godsiff | 13 Jul 2010 05:10

Re: [Trac-dev] Refactoring the Trac data model - relational approach


On 13/7/2010 4:54 AM, Carsten Klein wrote:
>> However, a User may also have Preferences, Session information, and
>> probably many other kinds of data attached to them which is not a
>> Permission, which a group may not (does not?) have.
>>      
> One could suggest that a group might provide default settings for a user.
>
>    
One could suggest that. Assuming you continue the trend of not 
distinguishing between User and Groups, however, such a suggestion would 
still be more easily facilitated - if not downright require - 
Users/Groups to be moved to their own table.
>
>>    From a purely normalisation basis, I need only point to the diagram on
>> http://trac.edgewall.org/wiki/TracDev/DatabaseSchema, and the
>> /ridiculous/ number of tables that have some sort of User field of type
>> 'text'. In terms of scalability, creating a User table and having other
>> tables reference an integer ID in it could /significantly/ improve
>> performance and use of space on large-scale installations.
>>      
> Hm, I do not think so. Yes, the redundant book keeping of the username can
> be a problem with backends that do not optimize on string storage. But
> since the username is a unique id, I think that it is correct in using it
> this way. Btw. what if a user deletes his or her account? As of now, the
> username will still be available, and no personal data whatsoever must be
> retained.
>
>

(Continue reading)

headers

Terje S. | 6 Jul 2010 20:32

Re: [Trac-dev] Refactoring the Trac data model - relational approach

On Tue, Jul 06, 2010 at 01:40:21PM +1000, Josh Godsiff wrote:
> I'll also add that proper normalisation would monumentally help our
> company in achieving the level of multi-project support we'd like,
> and greatly increase the chance of us being able to submit those

Hi Josh, thank you for speaking up as, I guess, the first person here 
to support my argument. IMO, you're spot-on in your assessments here and 
further down the thread.

What is your opinion on the issue of major vs 12-step rewrite?

I have a core "patch" that basically hacks together subsystems. Pass 
the env through a callchain to render wikitext somewhere, clone the 
custom field system here, hardcode a bunch of special cases there, 
hack in some poor date logic in model, middleware and javascript and 
debug until it appears to work etc. You simply cannot solve a problem 
of any signifficant proportions without changing large amounts of the 
existing codebase. This worries me, in terms of a 12-step approach to a
system that is much more complex than what we have already.

And, as you and I now seem to agree, there *is* a connection between this
class of problems and the approach to data modelling, specifically whether 
the model is normalized or not. Was feeling very alone up until now

A key benefit of moving to a relational model, is that we attract a much
wider audience, and thus get more help. This happens because people like
Josh and myself and doubtless many others, can eventually reccommend the 
product for use in production environments, without a major up-front
(re)development cost.

(Continue reading)

headers

Noah Kantrowitz | 6 Jul 2010 21:16

RE: [Trac-dev] Refactoring the Trac data model - relational approach

> -----Original Message-----
> From: trac-dev <at> googlegroups.com [mailto:trac-dev <at> googlegroups.com] On
> Behalf Of Terje S.
> Sent: Tuesday, July 06, 2010 11:33 AM
> To: trac-dev <at> googlegroups.com
> Subject: Re: [Trac-dev] Refactoring the Trac data model - relational
> approach
> 
> On Tue, Jul 06, 2010 at 01:40:21PM +1000, Josh Godsiff wrote:
> > I'll also add that proper normalisation would monumentally help our
> > company in achieving the level of multi-project support we'd like,
> > and greatly increase the chance of us being able to submit those
> 
> Hi Josh, thank you for speaking up as, I guess, the first person here
> to support my argument. IMO, you're spot-on in your assessments here
> and
> further down the thread.
> 
> What is your opinion on the issue of major vs 12-step rewrite?
> 
> I have a core "patch" that basically hacks together subsystems. Pass
> the env through a callchain to render wikitext somewhere, clone the
> custom field system here, hardcode a bunch of special cases there,
> hack in some poor date logic in model, middleware and javascript and
> debug until it appears to work etc. You simply cannot solve a problem
> of any signifficant proportions without changing large amounts of the
> existing codebase. This worries me, in terms of a 12-step approach to a
> system that is much more complex than what we have already.
> 
> And, as you and I now seem to agree, there *is* a connection between

(Continue reading)

headers

Terje S. | 6 Jul 2010 21:30

Re: [Trac-dev] Refactoring the Trac data model - relational approach

On Tue, Jul 06, 2010 at 12:16:37PM -0700, Noah Kantrowitz wrote:
> Again, you are talking about large-scale redesigns of all of Trac, not just

I have stated this unambigiously from my first post onward. If you don't
like the posts feel free to score the thread or me down in your end.

> the DB schema. This is neither here nor there, and unlikely to be something
> that "Trac-proper" will consider without some pretty major justification. If

From my point of view, most of the ticket history on t.e.o is a testament
that this is important to consider, if we want the product to flourish
long term. Most of the pages on the wiki that outline the future of Trac 
touch on very fundamental issues related to the modelling approach. 
Understand that I am not attacking anyone, I am actually trying to provide 
some insight into *why* I think there already *is* major justification to
change (is that hard to see?)

> this is your goal, I would recommend forking (somewhat easier with the git
> and hg mirrors running now) and start work on your changes. When you have

This is my goal, and I have already proposed a research codebase and offered
my time. The question I'm trying to find an answer to, is whether Trac is
a place I can contribute in a meaningful way. Now I know for sure you are
not going to help out directly on a remodelling effort, but I'm still 
hoping to gather support elsewhere on the list. You're probably right in 
that it's not going to work, but that's not stopping me from having a go 
at it. If you don't like it, score me down. If "Trac-proper" does not like 
it, I will shut up permanently.

> something finished, come back and show us. This sounds like it is more a

(Continue reading)

headers

Noah Kantrowitz | 6 Jul 2010 22:05

RE: [Trac-dev] Refactoring the Trac data model - relational approach


> -----Original Message-----
> From: trac-dev <at> googlegroups.com [mailto:trac-dev <at> googlegroups.com] On
> Behalf Of Terje S.
> Sent: Tuesday, July 06, 2010 12:31 PM
> To: trac-dev <at> googlegroups.com
> Subject: Re: [Trac-dev] Refactoring the Trac data model - relational
> approach
> 
> On Tue, Jul 06, 2010 at 12:16:37PM -0700, Noah Kantrowitz wrote:
> > Again, you are talking about large-scale redesigns of all of Trac,
> not just
> 
> I have stated this unambigiously from my first post onward. If you
> don't
> like the posts feel free to score the thread or me down in your end.
> 
> > the DB schema. This is neither here nor there, and unlikely to be
> something
> > that "Trac-proper" will consider without some pretty major
> justification. If
> 
> From my point of view, most of the ticket history on t.e.o is a
> testament
> that this is important to consider, if we want the product to flourish
> long term. Most of the pages on the wiki that outline the future of
> Trac
> touch on very fundamental issues related to the modelling approach.
> Understand that I am not attacking anyone, I am actually trying to
> provide

(Continue reading)

headers

Terje S. | 6 Jul 2010 22:39

Re: [Trac-dev] Refactoring the Trac data model - relational approach

On Tue, Jul 06, 2010 at 01:05:40PM -0700, Noah Kantrowitz wrote:
> This is neither required nor encouraged. I like seeing crazy new ideas tried

You sure had me thinking you wanted me to shut up...

> out. I think it should just be done somewhere outside trunk. Heck, Trac

No doubt, again, I have said the same thing on several occasions.
(the exact same arguments are equally valid against GenericTrac, if not more,
though but nobody seems to appreciate this point.)

> necessarily evil, but the onus of proof falls on you to demonstrate you have
> a better way, not us to say you don't. I don't mean you should fork Trac as

Ah yes but this is why I am spilling my guts, and offering time on
*modelling* as it is the part where I can possibly contribute something the
team may not already possess. I am in principle not interested in the 
approach taken in code at all, just that you offload to the database those
parts it was designed to do, and that will, by definition, simplify your
code, whatever it does. 

> in start a whole new project, just copy the repo in github (which is called
> forking there) and start hacking away. If in a few weeks you think you have
> a much more elegant codebase, show us. You don't need to convince us of your
> design with words, you need to do it with code, and I am happy to be so
> convinced.

And in the context of what I am really proposing (again, the damn model), 
I have already put up what I think is the correct core for the "ticket
system", as it should be stored in a relational model (in code form), as

(Continue reading)

headers

Noah Kantrowitz | 6 Jul 2010 23:19

RE: [Trac-dev] Refactoring the Trac data model - relational approach

> -----Original Message-----
> From: trac-dev <at> googlegroups.com [mailto:trac-dev <at> googlegroups.com] On
> Behalf Of Terje S.
> Sent: Tuesday, July 06, 2010 1:39 PM
> To: trac-dev <at> googlegroups.com
> Subject: Re: [Trac-dev] Refactoring the Trac data model - relational
> approach
> 
> It is not the design I am selling you, it is the approach.

This is not how FOSS works. We are an economy of time. If the idea is
your's, you have to own it. Like I said, code >>> words.

--Noah

--

-- 
You received this message because you are subscribed to the Google Groups "Trac Development" group.
To post to this group, send email to trac-dev <at> googlegroups.com.
To unsubscribe from this group, send email to trac-dev+unsubscribe <at> googlegroups.com.
For more options, visit this group at http://groups.google.com/group/trac-dev?hl=en.

headers

Remy Blank | 6 Jul 2010 21:55

Re: [Trac-dev] Refactoring the Trac data model - relational approach

I'm a bit sad that this discussion turned so quickly into a shootout,
even though it started so well.

> You simply cannot solve a problem 
> of any signifficant proportions without changing large amounts of the 
> existing codebase.

Sure, you change large portions of the existing code, but you do it
*incrementally*, in the open, and taking feedback from the existing
community into account. You may want to read the following article and
its references, which describes the situation quite well:

http://darkforge.blogspot.com/2010/02/code-bomb-or-newbie-with-big-ideas.html

For me, this means:

 - A big, monolithic rewrite gets a -1.
 - Infrastructure changes that don't bring a concrete advantage to
either the users, administrators or plugins developers get a -1.

> A key benefit of moving to a relational model, is that we attract a much
> wider audience, and thus get more help.

I doubt this. Just having a normalized database won't magically attract
flocks of new, motivated developers.

> This happens because people like
> Josh and myself and doubtless many others, can eventually reccommend the 
> product for use in production environments, without a major up-front
> (re)development cost.

(Continue reading)

headers

Terje S. | 7 Jul 2010 21:18

Re: [Trac-dev] Refactoring the Trac data model - relational approach

On Tue, Jul 06, 2010 at 09:55:13PM +0200, Remy Blank wrote:
> I'm a bit sad that this discussion turned so quickly into a shootout,
> even though it started so well.
> Sure, you change large portions of the existing code, but you do it
> *incrementally*, in the open, and taking feedback from the existing
> community into account. You may want to read the following article and
> its references, which describes the situation quite well:

The shooting continues..? 

I fully agree that iterative is the best approach. My comment, that you 
replied to here, was from the perspective of a poor, newbie end-user 
attempting to add a "simple" local feature, and not regarding core 
development process *at all*. Sorry if that was unclear.

Your link was an ok read. However, I think you must be misunderstanding
me on several levels.

The team has already planned the major changes, I am not directly proposing 
a rewrite or anything of the sorts. The question is what end-goal we should 
have for "1.0". Maybe that point has been watered down by all the 
sidetracking. The philosophy currently deployed, is:

"We don't care if a large portion of the code reinvents the underlying
database technology"

I can live with that, it's deployed, it works just fine for a small team.
Note that I didn't start this thread for the duration of one year on the
list, even though I could have argued the exact same points much earlier.

(Continue reading)

headers

John Hampton | 7 Jul 2010 22:27

Re: [Trac-dev] Refactoring the Trac data model - relational approach

On 7/7/10 12:18 PM, Terje S. wrote:
<mucho grande snippo>

> I only intended to provoke serious consideration of the approach to data
> modelling, and I did it *only* because you guys asked for it, not
> because I was prepared to fork the codebase or create a new product
> from scrach to prove that relational systems really do serve a purpose.
>
> I thought it would be nice to offer my help on the specific issue along
> with the criticism too, but as Noah accurately predicted, "Trac-proper"
> is indeed not very interested unless it's incremental code.

I'm piping up late here.  I am interested in your ideas.  I agree with 
Noah, and I think yourself as well, that before it's really considered 
for inclusion in "core" it need be proved in a branch first.

I am willing to help you work on said branch (we'll use github), though 
I can't promise lots of time.  As anyone can attest to here, I haven't 
been active as of late.

> And so, I guess we leave it at that to save banddwidth on all layers.
>
> Thanks everyone for your time, I have gathered the information I was looking
> for. I am sad that noone spoke up in favour, and will now stop beating the
> topic further to death. Let me know if any modelling work is needed.

Let me know off-list if you're interested in pursuing your ideas further.

-John

(Continue reading)

headers

Remy Blank | 7 Jul 2010 23:04

Re: [Trac-dev] Refactoring the Trac data model - relational approach

Terje S. wrote:
> I fully agree that iterative is the best approach. My comment, that you 
> replied to here, was from the perspective of a poor, newbie end-user 
> attempting to add a "simple" local feature, and not regarding core 
> development process *at all*. Sorry if that was unclear.

I did indeed misunderstand. Please accept my apologies.

-- Remy

headers

Steffen Hoffmann | 8 Jul 2010 00:41

Re: [Trac-dev] Refactoring the Trac data model - relational approach

Terje S. wrote:
[...]
> Thanks everyone for your time, I have gathered the information I was looking
> for. I am sad that noone spoke up in favour, and will now stop beating the 
> topic further to death. Let me know if any modelling work is needed.
> 
> At least I tried..

Don't let this be your last word on this topic.

I consider myself a small contributor, being a Trac admin and user for
about a year now as well. And I did not even dare to wrap my mind around
 Trac's data storage structure after having a first look, because I
thought it would take too much time and I had no pressing need to spend
that time either. So by now I just know enough to get some plugin stuff
to work with ticket and ticket_custom table, and that's all.

That said I like your idea about bringing Trac's database to a (more)
relational structure. Even if I'm not remotely trained to do i/o related
programming with Python, I wouldn't mind lending a helping hand for testing.

I'm maintaining and further developing my Trac applications to (ab?)use
Trac far away from software development for issue tracking in
non-software project management and even as a highly configurable
distributed enterprise logbook. I don't speak of a couple of issues and
some dozen comments per day but something like around 200 tickets per
hour(!) and searching the ticket summary of the whole archive for at
least 10 years back. Will test this with 0.12 soon. But I guess that
afterwards I'll be not less eager to push the idea here at least up to

(Continue reading)

headers

Terje S. | 8 Jul 2010 13:28

Re: [Trac-dev] Refactoring the Trac data model - relational approach

On Thu, Jul 08, 2010 at 12:41:21AM +0200, Steffen Hoffmann wrote:
> Don't let this be your last word on this topic.

With John kindly proposing his time, maybe it's not! Time will tell if me
and him have enough common interest to do the world any good. I will contact
you off-list in the coming weeks, John, thanks for your support.

> That said I like your idea about bringing Trac's database to a (more)
> relational structure. Even if I'm not remotely trained to do i/o related
> programming with Python, I wouldn't mind lending a helping hand for testing.

That's great news, thank you for offering :)

> Let me add a strong BUT now, since the initial post was not that strong
> about the foreseeable migration path. I wouldn't even think about any
> new Trac version that I couldn't upgrade to as I did by now. And I guess
> there is a considerable number of Trac application that do as I do:

I think you would reconsider this if the competing branch was on the market, 
and a viable migration path was possible. That's not true for all users, 
granted, but again the key is to serve for the *majority* long-term (who 
are not here), and not the minority (who are by definition quite happy 
with what they have already).

> stick with default SQLite database since the upgrade to next Trac
> version is guaranteed to work like a charm, so no need to become a real
> DB admin just to handle Trac. The automatic upgrade should be a
> must-have for any change and I urge you to not underestimate that point.

I would not underestimate that point in a million years, but in order to

(Continue reading)

headers

Steffen Hoffmann | 8 Jul 2010 15:02

Re: [Trac-dev] Refactoring the Trac data model - relational approach


Terje S. wrote:
> On Thu, Jul 08, 2010 at 12:41:21AM +0200, Steffen Hoffmann wrote:
>> Don't let this be your last word on this topic.
> 
> With John kindly proposing his time, maybe it's not! [...]

Right, so let the "shooting" end and the coding begin. 

>> That said I like your idea about bringing Trac's database to a (more)
>> relational structure. Even if I'm not remotely trained to do i/o related
>> programming with Python, I wouldn't mind lending a helping hand for testing.
> 
> That's great news, thank you for offering :)

Yeah, since I'm a Mercurial fellow I'll have to see how to interface
with Git, once you branch at Github, but this should be doable.

[...]
> Can anyone demonstrate prior art of a project that successfully acheived such 
> a feat with an incremental approach?
> 
> Rewrite is the only cost-effective way, in terms of reaching the stated 
> final goals of the project. If you are not doing the rewrite, you must 
> change the goals (or knowingly spend your time with very low ROI).
> 
> I dispute your case that this functionality is realistically present today at 
> all. Please show 20 different lists, each containing a combo of 5 or more 
> plugins from trac-hacks or elsewhere, that will work as flawlessly as you 
> describe in a move from 0.11 to 0.12 in a production environment today

(Continue reading)

headers

Carsten Klein | 9 Jul 2010 15:21

Re: [Trac-dev] Refactoring the Trac data model - relational approach


Hi Terje,

>> stick with default SQLite database since the upgrade to next Trac
>> version is guaranteed to work like a charm, so no need to become a real
>> DB admin just to handle Trac. The automatic upgrade should be a
>> must-have for any change and I urge you to not underestimate that point.
>
> I would not underestimate that point in a million years, but in order to
> acheive this feat you must defy all sorts of universal laws. The core
> issue
> with the current codebase is a fatal design flaw, that has been present
> for
> a long time. Each and every single line of core and plugin code, is placed
> exactly where it is, and written exactly as it is, partly to compensate
> for that fact. The system is *HIGHLY* complex, and since there is no
> actual
> underlying data model, we *cannot* know what exists in the deployments in
> the field (that we are upgrading from).

Besides that trac definitely needs an ORM (something of which Christian is
strongly against), I do not think that the design is flawed.

As to complexity, yes, but given a few day's hours, one can easily
understand the request processing model inherent to trac.

And, as far as my understanding goes, it is rather straight forward,
compared to for example Java based JEE servers.

And, besides that, database upgrades are actually handled very well. This,

(Continue reading)

headers

Felix Schwarz | 9 Jul 2010 15:38

Re: [Trac-dev] Refactoring the Trac data model - relational approach

Am 09.07.2010 15:21, schrieb Carsten Klein:
> Ah now, I begin to understand your rambling more clearly.
> Afaik, this is already the case with trac, and if it is not it can be
> implemented very easily. Bitten also features or rather will feature such
> an atomic upgrade mechanism.
>
> For my own plugins, which have not been released yet, I have implemented a
> very similar mechanism that will update the data model and the schema
> deployed in the database and the data therein incrementally.
> And, hopefully, even on failure, it will be able to continue upgrading the
> system from where it last failed.
> Basically, this is just an attribute in the system table, which gets
> incrementally updated, for each upgrade available.

Actually for plugin upgrades, there is also a generic patch from myself 
rotting in trac: http://trac.edgewall.org/ticket/8172
(just if someone is interested)

fs

--

-- 
You received this message because you are subscribed to the Google Groups "Trac Development" group.
To post to this group, send email to trac-dev <at> googlegroups.com.
To unsubscribe from this group, send email to trac-dev+unsubscribe <at> googlegroups.com.
For more options, visit this group at http://groups.google.com/group/trac-dev?hl=en.

headers

Carsten Klein | 9 Jul 2010 15:59

Re: [Trac-dev] Refactoring the Trac data model - relational approach


Hi Felix,

>> For my own plugins, which have not been released yet, I have implemented
>> a
>> very similar mechanism that will update the data model and the schema
>> deployed in the database and the data therein incrementally.
>> And, hopefully, even on failure, it will be able to continue upgrading
>> the
>> system from where it last failed.
>> Basically, this is just an attribute in the system table, which gets
>> incrementally updated, for each upgrade available.
>
> Actually for plugin upgrades, there is also a generic patch from myself
> rotting in trac: http://trac.edgewall.org/ticket/8172
> (just if someone is interested)

Thanks for posting this. I am missing the atomic (transactional) approach
in your solution, but it seems a good basis to improve upon. I will post
my solution for comparison to that ticket as well.

-- Carsten

--

-- 
You received this message because you are subscribed to the Google Groups "Trac Development" group.
To post to this group, send email to trac-dev <at> googlegroups.com.
To unsubscribe from this group, send email to trac-dev+unsubscribe <at> googlegroups.com.
For more options, visit this group at http://groups.google.com/group/trac-dev?hl=en.

(Continue reading)

headers

Terje S. | 13 Jul 2010 01:10

Re: [Trac-dev] Refactoring the Trac data model - relational approach

On Fri, Jul 09, 2010 at 04:14:15PM +0200, Carsten Klein wrote:

> Hi Terje,

Hi Carsten, list

I thought I would find time to post yesterday, but life did not permit.

> This might seem a bit rude, but I must get your intentions right.

Not at all, and certainly not anywhere near my own rudeness-levels...

I will reply merged, as this *IS* my conclusive post.

Followups off-list please, if any, I am *not* reviving.

I post some conclusive thoughts, alas, much has been raised by Carsten
and Christian and otherwise that I will not find time to answer, sorry. 

If anyone has outstanding issues, please contact me, I want them gone.

If the list in general has issues with me, make sure this is communicated,
or I will assume it not to be the case.

I am sorry, if I have stirred up in your community, whether it was my 
stupid words or a sensitive topic or a dangerous combination that caused it.

I will try to not ramble & keep it short.

(Nope, I didn't buy it myself either.)

(Continue reading)

headers

Carsten Klein | 13 Jul 2010 20:57

Re: [Trac-dev] Refactoring the Trac data model - relational approach


> If anyone has outstanding issues, please contact me, I want them gone.

Please, do not feal that I for my part reject your ideas, however, I do
feal that much of that can already be achieved by implementing a plugin or
multiple plugins thereof. In there you can define your own data model. In
fact, you could also provide a new ticket system that would implement what
you think would be best for the ticket system in trac.
Since the original ticket system itself is merely a collection of
extensions, each of them can easily be replaced.

> If the list in general has issues with me, make sure this is communicated,
> or I will assume it not to be the case.

No issues with you, really. And I hope that no offense was taken on your
behalf, if I sounded somewhat rude - like I said, I am also one who falls
in the door, head first with lots of noise and then people will eventually
come and stop me right at the entrance.

> Quite possibly because I did not submit a detailed enough explanation
> of the envisioned system from the start, but rather assuming that its
> position in the core would be assessed by the readers given SQL code.

Yeah, merely displaying things in just words is difficult, even in our
native language we will find problems communicating things as clearly as
possible without the help of additional media.

> War was *NOT* my intention in starting the topic, it was love 

:D War and Hate is was keeps going the machinery of man. As such,

(Continue reading)

headers

Felix Schwarz | 8 Jul 2010 13:16

Re: [Trac-dev] Refactoring the Trac data model - relational approach

Am 06.07.2010 21:55, schrieb Remy Blank:
>> You simply cannot solve a problem
>> of any signifficant proportions without changing large amounts of the
>> existing codebase.
>
> Sure, you change large portions of the existing code, but you do it
> *incrementally*, in the open, and taking feedback from the existing
> community into account.

I really like to second that approach! And I think it is pretty much 
possible.

One thing that hold me back of doing that with the ticket system so far 
was that I had the impression that my patches would be rejected anyway 
as core trac does not *want* to go in this direction.

As Noah said, we're living in an economy of time. I'm willing to spend 
some time to implement something incrementally and for some core ticket 
parts I pretty much know how this could be done as I've already done that.

However I need at least the commitment that a functionality will be 
included into trac core provided that it meets trac's architecture and 
the quality standards. I can't risk spending a lot of time (a week of 
free time is a lot for me currently) when the feature can not go into 
trac anyway.

fs

--

-- 
You received this message because you are subscribed to the Google Groups "Trac Development" group.

(Continue reading)

headers

Terje S. | 8 Jul 2010 13:40

Re: [Trac-dev] Refactoring the Trac data model - relational approach

On Thu, Jul 08, 2010 at 01:16:36PM +0200, Felix Schwarz wrote:
> One thing that hold me back of doing that with the ticket system so
> far was that I had the impression that my patches would be rejected
> anyway as core trac does not *want* to go in this direction.

And here is a key point, of course. My objection is that there is a huge
collision with this policy and the planned feature scope.

> As Noah said, we're living in an economy of time. I'm willing to
> spend some time to implement something incrementally and for some
> core ticket parts I pretty much know how this could be done as I've
> already done that.

As I just outlined in previous post, I really don't think the system can 
be replaced as a whole, with the best solution, using the incremental 
approach.

Granted, many optimizations can be done at much lower cost, but will take
much longer time to reach the value of a rewrite (speaking long-terms).

This is the critical part for the core team, of course.

IMO, you should consider the relational approach. You can *certainly* do
incremental improvements with it, without doing a complete rewrite.

But it's probably not something I am interested in contributing to,
unless, of course you are looking for concrete model design work for
particular subsystem planned for replacement, in which case I will gladly
help out! :)

(Continue reading)

headers

Carsten Klein | 9 Jul 2010 16:39

Re: [Trac-dev] Refactoring the Trac data model - relational approach


> Am 06.07.2010 21:55, schrieb Remy Blank:

> One thing that hold me back of doing that with the ticket system so far
> was that I had the impression that my patches would be rejected anyway
> as core trac does not *want* to go in this direction.
>
> As Noah said, we're living in an economy of time. I'm willing to spend
> some time to implement something incrementally and for some core ticket
> parts I pretty much know how this could be done as I've already done that.
>
> However I need at least the commitment that a functionality will be
> included into trac core provided that it meets trac's architecture and
> the quality standards. I can't risk spending a lot of time (a week of
> free time is a lot for me currently) when the feature can not go into
> trac anyway.

Putting up Felix's statement, that one cannot be sure of integration of a
proposal being made.

How about moving the trac development process further along the road by
having something like for example PEPs, e.g. TEP?

In addition, one could initiate a TEP by first proposing an idea, and if
that idea takes off (voting), one could propose a solution for that TEP
that is then to be integrated into trac based on code review etc.

-- Carsten

--

--

(Continue reading)

headers

Noah Kantrowitz | 12 Jul 2010 04:46

Re: [Trac-dev] Refactoring the Trac data model - relational approach


On Jul 9, 2010, at 7:39 AM, Carsten Klein wrote:

>
>> Am 06.07.2010 21:55, schrieb Remy Blank:
>
>> One thing that hold me back of doing that with the ticket system so  
>> far
>> was that I had the impression that my patches would be rejected  
>> anyway
>> as core trac does not *want* to go in this direction.
>>
>> As Noah said, we're living in an economy of time. I'm willing to  
>> spend
>> some time to implement something incrementally and for some core  
>> ticket
>> parts I pretty much know how this could be done as I've already  
>> done that.
>>
>> However I need at least the commitment that a functionality will be
>> included into trac core provided that it meets trac's architecture  
>> and
>> the quality standards. I can't risk spending a lot of time (a week of
>> free time is a lot for me currently) when the feature can not go into
>> trac anyway.
>
>
> Putting up Felix's statement, that one cannot be sure of integration  
> of a
> proposal being made.

(Continue reading)

headers

Felix Schwarz | 12 Jul 2010 11:05

Re: [Trac-dev] Refactoring the Trac data model - relational approach

Am 12.07.2010 04:46, schrieb Noah Kantrowitz:
> I said it before on this thread, but it is apparently worth repeating;
> if you have an idea you do not need our (core commiters) permission for
> it. Get some people excited, write some code, see what happens. If you
> write it nicely modularized chances are it can be released as a plugin
> even if it is decided it is too [big, complex, specific, etc etc] for core.

Honestly, I wrote some code for smaller features (like functional 
testing infrastructure for plugins, db upgrade for plugins, 
notifications use ticket change listeners and some more) where I 
believed that these changes would be met with approval. Some of them 
were actually mentioned as a good idea to start by some "core devs".

However in the end, all of them were either dismissed because of changed 
plans, being to invasive or just rot on t.e.o. My time is so limited 
that I can't justify spending this time without more coordination.

Note: I don't object the 'code first' idea. I think we're pretty much on 
the same line as far as this is concerned. I learnt from my ventures 
that I need to invest more time (which I spent coding previously) in 
coordination and private chats before.

As someone who contributed about a dozen patches to trac so far all I 
can say is that sometimes it would be helpful if there would be more 
guidance and mentoring by trac core developers so that outside authors 
could get their changes committed.

fs

--

--

(Continue reading)

headers

Remy Blank | 12 Jul 2010 17:20

Re: [Trac-dev] Refactoring the Trac data model - relational approach

Felix Schwarz wrote:
> My time is so limited 
> that I can't justify spending this time without more coordination.

(snip)

> As someone who contributed about a dozen patches to trac so far all I 
> can say is that sometimes it would be helpful if there would be more 
> guidance and mentoring by trac core developers so that outside authors 
> could get their changes committed.

We hear you, Felix. And we're in the same situation as you are, having
only limited time and lots of things to do. So some kind of choice has
to be made, and speaking for myself only, I'm rather a coder than a
manager, so I prefer spending my time coding than reviewing, testing and
integrating. I do some of the latter, mind you, but probably not enough
to satisfy all contributors.

(That reminds me that I have a big patch for "date/time custom fields"
to review. I'll get to that, eventually.)

-- Remy

headers

Carsten Klein | 12 Jul 2010 20:57

Re: [Trac-dev] Refactoring the Trac data model - relational approach


>> In addition, one could initiate a TEP by first proposing an idea,
>> and if
>> that idea takes off (voting), one could propose a solution for that
>> TEP
>> that is then to be integrated into trac based on code review etc.
>
> I said it before on this thread, but it is apparently worth repeating;
> if you have an idea you do not need our (core commiters) permission
> for it. Get some people excited, write some code, see what happens. If
> you write it nicely modularized chances are it can be released as a
> plugin even if it is decided it is too [big, complex, specific, etc
> etc] for core.

Of course, what can be done in a plugin should be done in a plugin. But
what I meant, was of course, a forum where one could propose more
fundamental changes to track and put them up for vote, first, describing
the change verbally, and, after that some interest was stirred (votes),
provide a patch that could then be reviewed by none, one or all of the
voters :)

That way, perhaps, evolving trac would be more than simply just act on
behalf of open tickets and feature requests in the ticket database.

And this system could also be used to for example propose individual
plugins which have been found useful, to be incorporated into the standard
distribution of trac.

-- Carsten Klein

(Continue reading)

headers

Christian Boos | 13 Jul 2010 10:49

TEPs (was Re: [Trac-dev] Refactoring the Trac data model - relational approach)

On 7/9/2010 4:39 PM, Carsten Klein wrote:
>> Am 06.07.2010 21:55, schrieb Remy Blank:
>>
>> One thing that hold me back of doing that with the ticket system so far
>> was that I had the impression that my patches would be rejected anyway
>> as core trac does not *want* to go in this direction.
>>
>> As Noah said, we're living in an economy of time. I'm willing to spend
>> some time to implement something incrementally and for some core ticket
>> parts I pretty much know how this could be done as I've already done that.
>>
>> However I need at least the commitment that a functionality will be
>> included into trac core provided that it meets trac's architecture and
>> the quality standards. I can't risk spending a lot of time (a week of
>> free time is a lot for me currently) when the feature can not go into
>> trac anyway.
>>
>
> Putting up Felix's statement, that one cannot be sure of integration of a
> proposal being made.
>
> How about moving the trac development process further along the road by
> having something like for example PEPs, e.g. TEP?
>

We already have TracDev/Proposals/... and the mailing list. The former 
is more for long term development of an idea, the latter is more for 
short term discussions, presenting the idea, gathering initial feedback 
or additional feedback after an evolution of the proposal. If the 
discussion is not summarized in one form or another into a wiki page,

(Continue reading)

headers

Terje S. | 5 Jul 2010 20:16

Re: [Trac-dev] Refactoring the Trac data model - relational approach

On Sun, Jul 04, 2010 at 09:27:33PM +0200, Remy Blank wrote:

> I'm sure your input would be highly appreciated. None of the current
> developers is a DB expert either (AFAIK, I'm certainly not one, quite
> the opposite), so any help is welcome.

Sounds fun, but to clarify: I'm just a hobbyist too, though I have a 
book collection on the topic, and love to play around when time permits, 
but that's about it. You get what you pay for, I guess! ;)

>  - Even if a complete migration requires a complete rewrite, is it
> possible to migrate progressively from the current to the new structure?

Tough question. My gut feeling is no, because I think it will add a 
large amount of work overall. This change will touch virtually all 
aspects of the system. We have to rethink a lot of the elementary units,
layers and interfaces throughout, and continually evolving this in an 
active branch will be tough.

IMO it deserves a completely new, isolated research codebase. The new 
model and core middleware architecture is laid out, and at some point 
the reusable parts of the existing codebase are ported based on 
decisions to be made along the way. The first alpha is built from that 
foundation, and it is marketed as a new product with a (partial) 
upgrade path possible (or we gather enough information to decide for 
gradual implementation to main code)

> I don't believe in big, monolithic rewrites, as they are a sure way to
> stall the project for a long time. [...]

(Continue reading)

headers

Christian Boos | 5 Jul 2010 17:12

Re: [Trac-dev] Refactoring the Trac data model - relational approach

On 7/4/2010 4:30 PM, Terje S. wrote:
> Hello list,
>    

Hello,

> I've been observing since I deployed .11 for a small team last year.
>
> I think the normalization topic raised by Felix in the look beyond 0.12
> thread is an important area to focus on; I ran into so many issues with
> this, I seriously considered abandoning half way. The problem manifests as
> difficulty to change the core systems; anything *slightly* outside the
> feature scope requires a lot of work to complete, even if the change is
> trivial in principle.
>
>    

Agreed up to this point.

> Christian replies to Felix:
>    
>> I'm not sure if I understand what you mean by "denormalized database".
>> Are you refering to the duplication of data in ticket related tables, as
>> discussed in #1890, or to the fact we don't use yet surrogate
>> keys except for the new repository table? Or the fact that relationships
>> between tables rely on "implicit" foreign keys (e.g. ticket ->  milestone)?
>>      
> I have no trouble relating to the original statement, it refers to all of
> the above and more; the general state of the data model, if you like.

(Continue reading)

headers

Terje S. | 5 Jul 2010 23:27

Re: [Trac-dev] Refactoring the Trac data model - relational approach

On Mon, Jul 05, 2010 at 05:12:31PM +0200, Christian Boos wrote:
> Well, I actually think that the term "denormalization" isn't a very
> good match for any of the above. These were things that I could
> remotely relate to the notion, with only #1890 talking about the
> redundancy problem.

To be clear, I used it only because of Felix's original statement of
"completely denormalized", and intended it as "not currently modelled 
in the third or any other normal form", and not to describe a process 
of denormalization as you outline. In this sense, denormalization is 
not accurate, but I still think "not normalized" applies well.

> And there's probably a reason for that, as none of the constraints,
> triggers, checks or advanced locking features, not even something as
> simple as a proper date field, can be used today in a reasonably
> database neutral way... Even with the restricted feature set of SQL
> we're using, we would have trouble porting to Oracle, for example.

I don't dispute this at all, except for the constraints and views. They 
are *the* critical elements of a data model, and their core feature set 
is available in one way or another across engines, including SQLite, so we
can build a model that is fundamentally equal. Later, specific optimizations 
may also be possible for any given engine, but that's a bonus and not a 
requirement. In any case, a locking mechanism can easily and uniformly be 
implemented in the model without using the vendor-specific mechanisms 
(compare and swap or other approach).

> I suppose this is just for illustration, as the above doesn't really
> fit well to Trac. For example the "work effort" central in the above
> example is defined by something "that represent work with a

(Continue reading)

headers

Christian Boos | 8 Jul 2010 15:44

Re: [Trac-dev] Refactoring the Trac data model - relational approach

On 7/5/2010 11:27 PM, Terje S. wrote:
> On Mon, Jul 05, 2010 at 05:12:31PM +0200, Christian Boos wrote:
>> And there's probably a reason for that, as none of the constraints,
>> triggers, checks or advanced locking features, not even something as
>> simple as a proper date field, can be used today in a reasonably
>> database neutral way... Even with the restricted feature set of SQL
>> we're using, we would have trouble porting to Oracle, for example.
>
> I don't dispute this at all, except for the constraints and views. They
> are *the* critical elements of a data model, and their core feature set
> is available in one way or another across engines, including SQLite, so we
> can build a model that is fundamentally equal.

"Prior to version 3.6.19, SQLite did not support foreign key constraints"
(http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers)

For example, Python 2.5.4 is bundled with SQLite 3.3.4, Python 2.6.5 has 
3.5.9, and only Python 2.7 comes with 3.6.21. So this effectively rules 
out relying on FK for the couple of years to come. Note that we can 
decorate the statements used to create the tables, for better 
documentation, but we can't expect those constraints to be actually 
functional. And no, the eventual benefits workarounds with triggers 
described in the ForeignKeyTriggers wiki page cited above are not worth 
their complexity.

As for views, I don't see an use case yet, but if there's one, there 
wouldn't be problems to use them, except for updates, which is also not 
something consistently supported.

>> I suppose this is just for illustration, as the above doesn't really

(Continue reading)

headers

Terje S. | 9 Jul 2010 07:14

Re: [Trac-dev] Refactoring the Trac data model - relational approach

Good morning.

Christian, I am really glad you followed up and did not leave the topic 
dead, as I was beginning to suspect you had. 

Your reply is, as was your last, exactly the type of discussion I hoped
to raise when I first posted. Thank you for staying on topic as relates to
the proposal itself, I sincerely regret any and all insults communicated due
to getting carried away on several oocasions.

(I did post a message apologizing unconditionally to you Steffen yesterday, 
it does not appear to have made it through.)

On Thu, Jul 08, 2010 at 03:44:02PM +0200, Christian Boos wrote:

> "Prior to version 3.6.19, SQLite did not support foreign key constraints"
> (http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers)

Granted, this is true, but joins, views and triggers are still 
supported, and triggers can emulate the keys and cascades for the 
critical relationships in these older versions. See:

http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers

> As for views, I don't see an use case yet, but if there's one, there
> wouldn't be problems to use them, except for updates, which is also
> not something consistently supported.

The use case for views in data modelling without update support is 
absolutely present! In this context, you could have Work Efforts at

(Continue reading)

headers

Christian Boos | 9 Jul 2010 16:06

Re: [Trac-dev] Refactoring the Trac data model - relational approach

On 7/9/2010 7:14 AM, Terje S. wrote:
> On Thu, Jul 08, 2010 at 03:44:02PM +0200, Christian Boos wrote
>> As for views, I don't see an use case yet, but if there's one, there
>> wouldn't be problems to use them, except for updates, which is also
>> not something consistently supported.
>>
> The use case for views in data modelling without update support is
> absolutely present! In this context, you could have Work Efforts at
> the bottom ("ticket" being a row in effort type). The view would then
> have the name Tickets, and join these systems together so they can be
> reached in a uniform way from the users perspective (for reporting).
>

You're overestimating this use case. We still support SQL reports, but 
they're "deprecated" since 0.10 and the introduction of custom queries. 
We're probably not going to remove that until we have a flexible query 
language (a la FishEye's EyeQL or Mingle's MQL or JIRA's JQL, etc.), but 
at least we've always warned people that if they use reports, they have 
to be ready to adapt and maintain them.

So we don't really want our users to have to fiddle with SQL, it's an 
advanced and use at your own risk feature.

> Either it is a feature request that must be considered (then discarded,
> or implemented which is all work), or patch (review/commit/reject it),
> support request (help submitter) etc. This entity also, unambigiously,
> is handled by a *WORK*flow system, does that not sound like a hint?
>

Not really, no. A workflow is more about evolution of state; a wiki page

(Continue reading)

headers

Terje S. | 14 Jul 2010 20:54

Re: [Trac-dev] Refactoring the Trac data model - relational approach

On Fri, Jul 09, 2010 at 04:06:48PM +0200, Christian Boos wrote:

> [good post I should have replied to]

Well, what do you know, here I am again

I could not leave this message unanswered.

(however I have cut it *all* out, so you don't know what it said.)

Christian, there are many reasons I am not supplying a full model design
up-front. I know you understand it, it's a HUGE task.

Even if you all don't want to graph all your resourcess as I do 
(I *DONT* GET IT), maybe there still is something I can contribute to.. 

I *FULLY* admit there are BIG challenges in refactoring the data model,
but also keep in mind that I have been promoting a long-term perspective
with research as the primary goal. I will happily discuss any part of the 
future database design with you on e-mail, including the intricate details 
of storage mechanisms, to the very best of my knowledge. Heck we'll discuss 
it over a $drink some day before you know it   

In the mean time, here is a slightly further developed version of previously 
mentioned model-level revisioning, to show use cases for views and triggers.

I thought that might be cool to try.

--------------------------------------------------------------------------
                 I wrote and "tested" for **SQLite**

(Continue reading)

headers

Carsten Klein | 15 Jul 2010 19:13

Re: [Trac-dev] Refactoring the Trac data model - relational approach


Hi Terje,

> Even if you all don't want to graph all your resourcess as I do
> (I *DONT* GET IT), maybe there still is something I can contribute to..

The main problem with not having such a graph right now is that each
subsystem declares its very own data model, something which the generic
trac approach tries to overcome, by unifying the resource representation
in the underlying data model.

As for the rest: sure, having triggers doing the work, i.e. making
revision entries, and views for getting the fields for a given resource,
would surely speed things up a bit, considering heavy load sites where
many updates to the data model are to be expected.

So, views and triggers for a more generic resource representation incl.
versioning seems to be the way to go for, +1 for that.

To be frank, you do not get 1k+/s new issues nor do you modify 1k+/s
issues at the same time, neither do you get 1k+ page edits or page adds in
the wiki, so this seems not a direct use case for trac since updates to
the data model are rather infrequent.
What is more important is, at least in my opinion, reading from the
database in a most efficient way and rendering the pages to the user as
fast as possible.

What I do not understand, however, is why you reinvent the row lock
mechanisms already available in the system. Of course, SQLITE might not
support a SELECT ... FOR UPDATE statement.

(Continue reading)

headers

Terje S. | 19 Jul 2010 16:14

Re: [Trac-dev] Refactoring the Trac data model - relational approach

Hi list \o/

Hope you all had a good week-end.

On Thu, Jul 15, 2010 at 07:13:47PM +0200, Carsten Klein wrote:

> To be frank, you do not get 1k+/s new issues nor do you modify 1k+/s
> issues at the same time, neither do you get 1k+ page edits or page adds in

I agree.

However, this is not a full solution, it will require much more. But *from* 
those early numbers, we can deduct that such system will probably perform 
OK when all the other triggers are in place (we have quite a good 
margin before hitting any realistic limit, as you seem to agree.)

Remember that the tested numbers are not simultaneous edits by users. They 
are "atomic changes" at storage level. One edit by a user is NOT one 
change, it maybe 0 or 5 or 20 or 100 depending entirely on what was done, 
how many custom fields are present etc. What happens if you rename a 
milestone with 1000 associated tickets, for example? First, one update 
to the milestone table is done, and then all associated tickets are 
updated; total 1001 revisioned changes in a nanosecond.

> What I do not understand, however, is why you reinvent the row lock
> mechanisms already available in the system. [...]
> [..post merge..]
> After having thought about its purpose for I while, I came to the
> conclusion that you want to lock a resource when a user starts for example
> editing a page or a ticket.

(Continue reading)

headers

Carsten Klein | 15 Jul 2010 19:42

Re: [Trac-dev] Refactoring the Trac data model - relational approach


Additional note on the resource locking mechanism you propose.
After having thought about its purpose for I while, I came to the
conclusion that you want to lock a resource when a user starts for example
editing a page or a ticket.

Editing a page is fine and can be tracked, since you must click on the
Edit button. However, users might exist that will use this mechanism for
indefinitely locking individual resources, so that eventually the
administrator of the system will have to remove these locks again,
manually.

For tickets, this is even more complicated, since you can both view and
modify the ticket using a single request. So the modification will be
instantaneous upon submit.

In that case, a previous change to the same ticket can only be detected
through a separate select on the ticket table in order to find out the
last modification date, by which then the new modification will be either
permitted or prohibited.

-- Carsten

--

-- 
You received this message because you are subscribed to the Google Groups "Trac Development" group.
To post to this group, send email to trac-dev <at> googlegroups.com.
To unsubscribe from this group, send email to trac-dev+unsubscribe <at> googlegroups.com.
For more options, visit this group at http://groups.google.com/group/trac-dev?hl=en.

(Continue reading)

headers

Carsten Klein | 9 Jul 2010 16:14

Re: [Trac-dev] Refactoring the Trac data model - relational approach


Hi Terje,

I have been watching this thread for some time now, and I would like to
further investigate your intentions.

This might seem a bit rude, but I must get your intentions right.

> CREATE VIEW Tickets AS
> 	SELECT * FROM WorkEfforts WHERE effort_type = [ticket type id]
> 	JOIN [more relevant information];

Now, work effort type by ticket type is clearly a domain idiom.
The underlying trac data model does not know about such idioms.

Perhaps you are best to go off and implement your own context provider,
e.g. /workeffort where you can implement such things using available
extension points? The possibility is there, you simply have to discover it
and make use of it.

For example you could introduce custom ticket fields, and with the use of
the [custom] ticket plugin present at track-hacks, you could also limit
their use for specific ticket types only.

In your custom solution, lets call it a plugin or a collection of plugins
thereof, you would then be able to implement what you are seeking for.

Hint: ITicketManipulator, IRequestHandler, IResourceManager, trac-hacks,
trac.ini.

(Continue reading)