Basecamp -> AC 1.x Importer
Page: 1, 2
Recent posts in this topic
Pro
Searched through the numerous threads and noticed a lot of discussion (and a few scripts) regarding importing Basecamp XML data into AC. Unfortunately, it all seems to be centered around the .6 and .7 releases.
Having recently decided to make the move to AC 1.x, I need to find an easy way to import 1.5 years or so of data from Basecamp to AC before totally making the switch.
Anything available?
Having recently decided to make the move to AC 1.x, I need to find an easy way to import 1.5 years or so of data from Basecamp to AC before totally making the switch.
Anything available?
Pro
Nothing yet, although it is on the development agenda (without date). I'm waiting for the same before we purchase.
www.synergetics.be | www.davor.be
Pro
digitalmark
on Nov 9. 2007. 9:32 pm
Hopefully since aC users are now the type who are willing to pay for SW value, a savvy developer will build one, even for a small but worthwhile price. Only problem is that the current site and resource setup don't highlight contributions or extensions. Would be wise to setup a Developers or contributor section (aside from the "topic" in the forum) to invite activity. RIght?
Pro
I dumped the XML file from Basecamp and have imported it into Excel to clean up into tables.
Problem I've seen with AC is a little thing called data normalization. Not sure what the thought process was for the AC database - but it made me cringe when I looked at it and has me considering a return. My boss who is a DB Admin almost smacked me in the head.
No doubt it works (for the most part) and fills some needs that Basecamp wasn't doing at this point but I'm not sure it would be my 1st choice for long term.
Anyhow, if AC had a cleaner backend I could build something but it doesn't and my day job is not PHP/MySQL dev (unfortunately).
Problem I've seen with AC is a little thing called data normalization. Not sure what the thought process was for the AC database - but it made me cringe when I looked at it and has me considering a return. My boss who is a DB Admin almost smacked me in the head.
No doubt it works (for the most part) and fills some needs that Basecamp wasn't doing at this point but I'm not sure it would be my 1st choice for long term.
Anyhow, if AC had a cleaner backend I could build something but it doesn't and my day job is not PHP/MySQL dev (unfortunately).
Pro somwicked:
Problem I've seen with AC is a little thing called data normalization. Not sure what the thought process was for the AC database - but it made me cringe when I looked at it and has me considering a return. My boss who is a DB Admin almost smacked me in the head.
Could you elaborate on this?
www.synergetics.be | www.davor.be
Pro
Data normalization to me is about breaking down the data into as distinct as possible tables without taking too much of a hit on performance due to an increased number of queries. It also about keeping like data with like data and relating it to other tables of like data as necessary.
For instance, if I was building something similar to AC - I would have a table for projects, another for tickets, another for tasks, another for time entries and so on since most of these are a 1-to-many relationship and the type of data described by each is fairly unique. Foreign keys in each table would provide the reference to the other related tables.
AC seems to have taken a different approach at times. The biggest 'problem' I see is with a table called acx_project_objects. This table contains most everything - categories, tasks, comments, tickets, time entries, files, attachments, pages, .. - with a type and module field used to identify each item. There are an ungodly number of fields in the table that have NULL values for many, many items. There are also many generic fields labelled integer_field_1, integer_field_2, and so on used to hold who knows what (quite often nothing (NULL)).
While I understand they were trying to limit the number of queries to the database, I think there are (potential) problems due to the db structure.
The first one I see is related to table size. Since most everything is in this one table, what happens in 2 years when this table becomes extremely large - how long is the query going to take to run through 1,000,000+ records? Compared to 2 queries against relatively smaller tables?
The second problem I see is in performing queries against the database becoming unnecessarily complex. Rather than querying against 1 or 2 tables with a simple join somewhere in the middle - it almost seems like they are querying the db and then querying the results again just to sort through garbage and get something meaningful.
Like I said, I'm not a dedicated db admin, nor a developer, its just something I noticed while trying to work with the database to add functionality that is not present currently. Maybe there are good reasons for how it is structured but it looks like a mess to me and gave me not-so-good of a feeling (an ulcer?).
That being said, we (the boss and I) do like AC and how it differentiates itself from Basecamp and others and may be using it until something better comes along or I brush up on some dev skills. We are still on the fence about what to do.
For instance, if I was building something similar to AC - I would have a table for projects, another for tickets, another for tasks, another for time entries and so on since most of these are a 1-to-many relationship and the type of data described by each is fairly unique. Foreign keys in each table would provide the reference to the other related tables.
AC seems to have taken a different approach at times. The biggest 'problem' I see is with a table called acx_project_objects. This table contains most everything - categories, tasks, comments, tickets, time entries, files, attachments, pages, .. - with a type and module field used to identify each item. There are an ungodly number of fields in the table that have NULL values for many, many items. There are also many generic fields labelled integer_field_1, integer_field_2, and so on used to hold who knows what (quite often nothing (NULL)).
While I understand they were trying to limit the number of queries to the database, I think there are (potential) problems due to the db structure.
The first one I see is related to table size. Since most everything is in this one table, what happens in 2 years when this table becomes extremely large - how long is the query going to take to run through 1,000,000+ records? Compared to 2 queries against relatively smaller tables?
The second problem I see is in performing queries against the database becoming unnecessarily complex. Rather than querying against 1 or 2 tables with a simple join somewhere in the middle - it almost seems like they are querying the db and then querying the results again just to sort through garbage and get something meaningful.
Like I said, I'm not a dedicated db admin, nor a developer, its just something I noticed while trying to work with the database to add functionality that is not present currently. Maybe there are good reasons for how it is structured but it looks like a mess to me and gave me not-so-good of a feeling (an ulcer?).
That being said, we (the boss and I) do like AC and how it differentiates itself from Basecamp and others and may be using it until something better comes along or I brush up on some dev skills. We are still on the fence about what to do.
Staff
Ilija Studen
on Nov 11. 2007. 10:48 am
There are two reasons why it's made like that: extensibility and data aggregation. Most of the things in activeCollab share the same properties and behavior - they have name, full description or content, sometimes due date, priority, time of completion, tags etc. Types are not that different.
Lets have an example - we have 5 types that we support: tickets, pages, discussions, milestones and subtasks and we are storing them in separate tables - something you proposed as a good solution. How would you return all the tasks assigned to a single user that are due today or late? You need to go through tickets, milestones and subtasks table because they can have due date! Would you be able to implement trash or starring functionality easily? How about making a filter plugin that could give you an interface similar to Mail Rules or Smart Folders:
And the killer questions: how would you support plugins and new data types in existing views? You want to install events plugin. Going through the entire system and changeling all the queries is not acceptable! System somehow needs to know about this type, that it has a due date etc and incorporate it in all the views that include data extraction based on due date. It's a requirement.
Current design is not perfect, but it gives us more opportunities to develop useful and cool features than the one you are proposing. Maybe it seams like a hack or dirty, but we actually spent days thinking about it and discussing it with other developers. Conclusion: normalization is desirable, but highly overrated and not always the best way to go.
Fact is that most of the features that make activeCollab different from other apps and that gives us opportunity to offer more than other apps is the fact that we have a good data aggregation system from the day one. We are not using 10% of what it can offer at this point. I think it's cool what we can do with it :)
Lets have an example - we have 5 types that we support: tickets, pages, discussions, milestones and subtasks and we are storing them in separate tables - something you proposed as a good solution. How would you return all the tasks assigned to a single user that are due today or late? You need to go through tickets, milestones and subtasks table because they can have due date! Would you be able to implement trash or starring functionality easily? How about making a filter plugin that could give you an interface similar to Mail Rules or Smart Folders:
And the killer questions: how would you support plugins and new data types in existing views? You want to install events plugin. Going through the entire system and changeling all the queries is not acceptable! System somehow needs to know about this type, that it has a due date etc and incorporate it in all the views that include data extraction based on due date. It's a requirement.
Current design is not perfect, but it gives us more opportunities to develop useful and cool features than the one you are proposing. Maybe it seams like a hack or dirty, but we actually spent days thinking about it and discussing it with other developers. Conclusion: normalization is desirable, but highly overrated and not always the best way to go.
Fact is that most of the features that make activeCollab different from other apps and that gives us opportunity to offer more than other apps is the fact that we have a good data aggregation system from the day one. We are not using 10% of what it can offer at this point. I think it's cool what we can do with it :)
activeCollab Team Member
Pro
Ilija,
I wasn't attacking how it was built and stated there is probably a reason for it and that I am not a dedicated dev or db admin. I tried to make that clear. I was expressing my discomfort with how I perceived the database. You've answered and provided some of the thoughts behind why it is the way it is. Thanks.
First, I guess the difference would be in the definition of a task (milestone, page, etc). I don't see a task as a ticket, a milestone, or a page. A ticket is more or less an issue, a task list or parent task container. A task is a discrete item that needs to be done. A page is similar to a blog post. A discussion is a forum post. And so on.
If items are so similar and have all of or most of the same properties, I would more than likely think something could be simplified in the design prior to database development. But this is just my opinion - like my thoughts on the db structure. I could go into my thoughts on it more but it really doesn't make too much of a difference, I'm not the developer. And I'm not sure how I would map something like a database structure, relations and workflow in a forum post to fully answer your question.
But for tasks, they have 2 potential parents: checklists and tickets. (I don't see subtasks anywhere). I would query the tasks table looking for the assigned to field, limiting by a where clause for date, and dumping whatever fields I believed were necessary in the view.
I just don't see - and this is more than likely related to not being the developer - the similarity between all the various datatypes or the level of abstraction you discuss. I don't see why time entries are in the same table as tasks, milestones and pages since they are discrete items.
Honestly, I haven't given plugins and new data types much thought at this point. If I were to develop plugins, I would more than likely have them use a separate table in the database and through the use of parent IDs relate the items back to the core tables. As for the code portion and the queries, I'm not sure since I am not a full time dev and would need to think about it more. I would do whatever I could to keep plugins/modules/hacks away from the core code in order to maintain modularity.
Not sure I was proposing anything. But if there are more 'cool' - preferably useful - tools and features coming, great! I'm all for it.
Hope to see more in the future.
I wasn't attacking how it was built and stated there is probably a reason for it and that I am not a dedicated dev or db admin. I tried to make that clear. I was expressing my discomfort with how I perceived the database. You've answered and provided some of the thoughts behind why it is the way it is. Thanks.
Ilija Studen:.. we have 5 types that we support: tickets, pages, discussions, milestones and subtasks and we are storing them in separate tables - something you proposed as a good solution. How would you return all the tasks assigned to a single user that are due today or late? You need to go through tickets, milestones and subtasks table because they can have due date!
First, I guess the difference would be in the definition of a task (milestone, page, etc). I don't see a task as a ticket, a milestone, or a page. A ticket is more or less an issue, a task list or parent task container. A task is a discrete item that needs to be done. A page is similar to a blog post. A discussion is a forum post. And so on.
If items are so similar and have all of or most of the same properties, I would more than likely think something could be simplified in the design prior to database development. But this is just my opinion - like my thoughts on the db structure. I could go into my thoughts on it more but it really doesn't make too much of a difference, I'm not the developer. And I'm not sure how I would map something like a database structure, relations and workflow in a forum post to fully answer your question.
But for tasks, they have 2 potential parents: checklists and tickets. (I don't see subtasks anywhere). I would query the tasks table looking for the assigned to field, limiting by a where clause for date, and dumping whatever fields I believed were necessary in the view.
I just don't see - and this is more than likely related to not being the developer - the similarity between all the various datatypes or the level of abstraction you discuss. I don't see why time entries are in the same table as tasks, milestones and pages since they are discrete items.
Ilija Studen:And the killer questions: how would you support plugins and new data types in existing views? You want to install events plugin. Going through the entire system and changeling all the queries is not acceptable! System somehow needs to know about this type, that it has a due date etc and incorporate it in all the views that include data extraction based on due date. It's a requirement.
Honestly, I haven't given plugins and new data types much thought at this point. If I were to develop plugins, I would more than likely have them use a separate table in the database and through the use of parent IDs relate the items back to the core tables. As for the code portion and the queries, I'm not sure since I am not a full time dev and would need to think about it more. I would do whatever I could to keep plugins/modules/hacks away from the core code in order to maintain modularity.
Ilija Studen:Current design is not perfect, but it gives us more opportunities to develop useful and cool features than the one you are proposing. Maybe it seams like a hack or dirty, but we actually spent days thinking about it and discussing it with other developers. Conclusion: normalization is desirable, but highly overrated and not always the best way to go.
Not sure I was proposing anything. But if there are more 'cool' - preferably useful - tools and features coming, great! I'm all for it.
Ilija Studen:Fact is that most of the features that make activeCollab different from other apps and that gives us opportunity to offer more than other apps is the fact that we have a good data aggregation system from the day one. We are not using 10% of what it can offer at this point. I think it's cool what we can do with it :)
Hope to see more in the future.
Staff
Ilija Studen
on Nov 12. 2007. 8:42 am
Sorry if I sounded defensive or anything like that. All I wanted to say is that this design is not made by accident, we put a lot of though behind it and have really good reasons for introducing it. Sure, it can be done differently, but this is the way we did it and we are convinced it is good design for problems we are facing.
Thank you :)
Thank you :)
activeCollab Team Member
