Re: [dev] OTRS on SQL Azure: Question regarding DB Scripts
Hi Sean, if you find a simpler way that will work in the OTRS architecture, I'm absolutely open. OTRS defines database schemas and schema changes in XML, please see scripts/database/otrs-schema.xml. From this, the DBObject creates the SQL files for the different DB types with the help of our drivers, mssql for example. So there is one common XML source that feeds all the different database types (see also http://doc.otrs.org/developer/3.3/en/html/ch02s02.html for a summary). This means we have no options to perform exceptions for particular databases, which is why I believe adding primary keys to all tables would be the right thing to do. If you have another proposal, please explain it. Regards, mg Am 07.04.14 17:56, schrieb Sean Killeen: Hi Martin, Thanks for being so receptive to the idea and continuing the conversation. I'm wondering if there might be a simpler answer than what you've proposed. SQL Azure doesn't require a primary key on the tables -- rather, it only requires a clustered index so that it has an indication of how to store the data. I believe it's very possible that if we create the appropriate clustered index to instruct SQL Server how to store data at the leaf level, we don't necessarily need to create a primary key. Additionally, if these tables were previously small enough to be heaps, we may not need to create the clustered indexes perfectly. Given my understanding of the above, I was wondering if this couldn't be accomplished with changes to the MSSQL database schema setup transformation scripts alone, saving us the necessary work on the drivers, etc. since they'd continue to operate normally. Unlike a primary key, adding a clustered index wouldn't introduce constraints -- though I might be ignorant of problems it would cause elsewhere somehow. Is there something I'm missing that would require us to do more work than that? Happy to put in the work, of course, but I'm thinking we might be able to accomplish this with less ripple effect and I'm all for that. Thanks again, -- Sean On Mon, Apr 7, 2014 at 7:30 AM, Martin Gruner martin.gru...@otrs.comwrote: Hi Sean, hi Mike, this is a very interesting, but also challenging project. Essentially this boils down to adding primary keys to tables that don't have them yet. There are a few obstacles though: - The database drivers of OTRS don't support adding of primary keys to existing tables (yet). - Once this is done for the framework, we will also need to enforce primary keys for all OTRS modules. So the steps to achieve this would be: 1. Add the possibility to add primary keys to all database drivers in OTRS (mysql, postgresql, mssql and oracle). Maybe Mike could help here with the non-mssql databases. This must be covered well with unit tests and should be merged as a separate pull request before the work continues= -- Martin Gruner Senior Developer RD OTRS AG Bahnhofplatz 1a 94315 Straubing T: +49 (0)6172 681988 0 F: +49 (0)9421 56818 18 I: www.otrs.com/ Geschäftssitz: Bad Homburg, Amtsgericht: Bad Homburg, HRB 10751, USt-Nr.: DE256610065 Aufsichtsratsvorsitzender: Burchard Steinbild, Vorstand: André Mindermann (Vorsitzender), Christopher Kuhn, Sabine Riedel Einfache Planung, bessere Übersicht - Mit OTRS 3.3 einfach besseres Service Management - Jetzt downloaden und testen ___ OTRS mailing list: dev - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/dev To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev
Re: [dev] OTRS on SQL Azure: Question regarding DB Scripts
Martin, I think I may have misled myself but would like your confirmation. I got the impression from browsing https://github.com/OTRS/otrs/tree/rel-3_3/scripts/database. Here, it appears that there are database scripts for different providers, one of which was an MSSQL provider. My thought was that if we could edit the otrs-initial_insert.mssql.sql to include clustered indexes on some of the tables it creates, that this would solve the problem without altering the Schema from the perspective of the XML file. But it sounds from your explanation like these files may not be used and the XML file / driver may be used now instead. Is that the case? Thanks for clarifying, Sean — Sean E-mauled from my mobile device; please excuse typos and brevity. On Tue, Apr 8, 2014 at 3:07 AM, Martin Gruner martin.gru...@otrs.com wrote: Hi Sean, if you find a simpler way that will work in the OTRS architecture, I'm absolutely open. OTRS defines database schemas and schema changes in XML, please see scripts/database/otrs-schema.xml. From this, the DBObject creates the SQL files for the different DB types with the help of our drivers, mssql for example. So there is one common XML source that feeds all the different database types (see also http://doc.otrs.org/developer/3.3/en/html/ch02s02.html for a summary). This means we have no options to perform exceptions for particular databases, which is why I believe adding primary keys to all tables would be the right thing to do. If you have another proposal, please explain it. Regards, mg Am 07.04.14 17:56, schrieb Sean Killeen: Hi Martin, Thanks for being so receptive to the idea and continuing the conversation. I'm wondering if there might be a simpler answer than what you've proposed. SQL Azure doesn't require a primary key on the tables -- rather, it only requires a clustered index so that it has an indication of how to store the data. I believe it's very possible that if we create the appropriate clustered index to instruct SQL Server how to store data at the leaf level, we don't necessarily need to create a primary key. Additionally, if these tables were previously small enough to be heaps, we may not need to create the clustered indexes perfectly. Given my understanding of the above, I was wondering if this couldn't be accomplished with changes to the MSSQL database schema setup transformation scripts alone, saving us the necessary work on the drivers, etc. since they'd continue to operate normally. Unlike a primary key, adding a clustered index wouldn't introduce constraints -- though I might be ignorant of problems it would cause elsewhere somehow. Is there something I'm missing that would require us to do more work than that? Happy to put in the work, of course, but I'm thinking we might be able to accomplish this with less ripple effect and I'm all for that. Thanks again, -- Sean On Mon, Apr 7, 2014 at 7:30 AM, Martin Gruner martin.gru...@otrs.comwrote: Hi Sean, hi Mike, this is a very interesting, but also challenging project. Essentially this boils down to adding primary keys to tables that don't have them yet. There are a few obstacles though: - The database drivers of OTRS don't support adding of primary keys to existing tables (yet). - Once this is done for the framework, we will also need to enforce primary keys for all OTRS modules. So the steps to achieve this would be: 1. Add the possibility to add primary keys to all database drivers in OTRS (mysql, postgresql, mssql and oracle). Maybe Mike could help here with the non-mssql databases. This must be covered well with unit tests and should be merged as a separate pull request before the work continues= -- Martin Gruner Senior Developer RD OTRS AG Bahnhofplatz 1a 94315 Straubing T: +49 (0)6172 681988 0 F: +49 (0)9421 56818 18 I: www.otrs.com/ Geschäftssitz: Bad Homburg, Amtsgericht: Bad Homburg, HRB 10751, USt-Nr.: DE256610065 Aufsichtsratsvorsitzender: Burchard Steinbild, Vorstand: André Mindermann (Vorsitzender), Christopher Kuhn, Sabine Riedel Einfache Planung, bessere Übersicht - Mit OTRS 3.3 einfach besseres Service Management - Jetzt downloaden und testen ___ OTRS mailing list: dev - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/dev To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev___ OTRS mailing list: dev - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/dev To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/dev
Re: [dev] OTRS on SQL Azure: Question regarding DB Scripts
Hi Sean, exactly. These files are generated from the XML (with bin/otrs.xml2sql.pl) and will be overwritten whenever there are changes to the XML and/or the drivers. Regards, mg Am 08.04.14 12:04, schrieb Sean Killeen: Martin, I think I may have misled myself but would like your confirmation. I got the impression from browsing https://github.com/OTRS/otrs/tree/rel-3_3/scripts/database. Here, it appears that there are database scripts for different providers, one of which was an MSSQL provider. My thought was that if we could edit the otrs-initial_insert.mssql.sql to include clustered indexes on some of the tables it creates, that this would solve the problem without altering the Schema from the perspective of the XML file. But it sounds from your explanation like these files may not be used and the XML file / driver may be used now instead. Is that the case? Thanks for clarifying, Sean — Sean E-mauled from my mobile device; please excuse typos and brevity. On Tue, Apr 8, 2014 at 3:07 AM, Martin Gruner martin.gru...@otrs.com mailto:martin.gru...@otrs.com wrote: Hi Sean, if you find a simpler way that will work in the OTRS architecture, I'm absolutely open. OTRS defines database schemas and schema changes in XML, please see scripts/database/otrs-schema.xml. From this, the DBObject creates the SQL files for the different DB types with the help of our drivers, mssql for example. So there is one common XML source that feeds all the different database types (see also http://doc.otrs.org/developer/3.3/en/html/ch02s02.html for a summary). This means we have no options to perform exceptions for particular databases, which is why I believe adding primary keys to all tables would be the right thing to do. If you have another proposal, please explain it. Regards, mg Am 07.04.14 17:56, schrieb Sean Killeen: Hi Martin, Thanks for being so receptive to the idea and continuing the conversation. I'm wondering if there might be a simpler answer than what you've proposed. SQL Azure doesn't require a primary key on the tables -- rather, it only requires a clustered index so that it has an indication of how to store the data. I believe it's very possible that if we create the appropriate clustered index to instruct SQL Server how to store data at the leaf level, we don't necessarily need to create a primary key. Additionally, if these tables were previously small enough to be heaps, we may not need to create the clustered indexes perfectly. Given my understanding of the above, I was wondering if this couldn't be accomplished with changes to the MSSQL database schema setup transformation scripts alone, saving us the necessary work on the drivers, etc. since they'd continue to operate normally. Unlike a primary key, adding a clustered index wouldn't introduce constraints -- though I might be ignorant of problems it would cause elsewhere somehow. Is there something I'm missing that would require us to do more work than that? Happy to put in the work, of course, but I'm thinking we might be able to accomplish this with less ripple effect and I'm all for that. Thanks again, -- Sean On Mon, Apr 7, 2014 at 7:30 AM, Martin Gruner martin.gru...@otrs.comwrote: Hi Sean, hi Mike, this is a very interesting, but also challenging project. Essentially this boils down to adding primary keys to tables that don't have them yet. There are a few obstacles though: - The database drivers of OTRS don't support adding of primary keys to existing tables (yet). - Once this is done for the framework, we will also need to enforce primary keys for all OTRS modules. So the steps to achieve this would be: 1. Add the possibility to add primary keys to all database drivers in OTRS (mysql, postgresql, mssql and oracle). Maybe Mike could help here with the non-mssql databases. This must be covered well with unit tests and should be merged as a separate pull request before the work continues= -- Martin Gruner Senior Developer RD OTRS AG Bahnhofplatz 1a 94315 Straubing T: +49 (0)6172 681988 0 F: +49 (0)9421 56818 18 I: www.otrs.com/ Geschäftssitz: Bad Homburg, Amtsgericht: Bad Homburg, HRB 10751, USt-Nr.: DE256610065 Aufsichtsratsvorsitzender: Burchard Steinbild, Vorstand: André Mindermann (Vorsitzender), Christopher Kuhn, Sabine Riedel Einfache Planung, bessere Übersicht - Mit OTRS 3.3 einfach besseres Service Management - Jetzt downloaden und testen ___ OTRS mailing
