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.com>wrote: > 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. > 2. Add primary keys to all tables in the framework that don't have any. > Should be unit tested as well, perhaps the existing unit tests will > cover parts of this already. > 3. Extend OTRSCodePolicy to require primary keys for all tables in OTRS > 3.4 and higher, including addon modules. This would be my part. > 4. Add primary keys to all modules ported to OTRS 3.4 in future. I guess > this would also be on the OTRS side. > > To get this into OTRS 3.4, 1) and 2) would need to be successfully QA'ed > by July, otherwise it would have to be postponed to a later release. > > What do you think? > > Regards, mg > > Am 29.03.14 02:50, schrieb Sean Killeen: > > *Summary: *I'd like to alter some DB scripts to add clustered indexes so > > they work better for MSSQL (and work at all for SQL Azure). How should I > > go about this? > > > > Hi all, > > > > New to contributing to OTRS and to the dev mailing list so please > > forgive me if I stumble around a bit. > > > > *Background / Issue* > > > > I wanted to set up OTRS on Azure using a SQL Azure database back-end, so > > I thought I'd play around a bit. > > > > SQL Azure apparently chokes when confronted with tables that don't have > > any clustered indexes. Womp womp. > > > > As of v3.3.5, the setup script for OTRS apparently creates 33 tables > > without clustered indexes or primary keys (the list in full is at the > > end of this post). This leads to inoperability and a ton of errors in > > the log when attempting to use OTRS with SQL Azure. > > > > *What I'd like to do about it * > > * > > * > > I'd like to help out with this and submit a pull request for the MSSQL > > setup to deal with this (as well as make things a little more snappy for > > MSSQL users). Was hoping to start this out as a Github issue, but I > > don't believe Issues are enabled for the otrs/otrs project. > > > > However, I'm as of yet completely unfamiliar with the data structure, so > > I would be guessing at best here and I don't think that's wise. :) > > > > Is there a solid reference point for the database architecture available > > that I could reference to deduce where primary keys or clustered indexes > > could be used in the affected tables? Or is there some other approach > > the community would prefer I take to addressing this? > > > > OTRS is a great product, and I'm looking forward to being able to > > hopefully give something back. > > > > Thanks, > > -- > > Sean > > > > *Appendix: The list of tables without clustered indexes* > > > > Found by running: > > > > SELECT DISTINCT [TABLE] = OBJECT_NAME(OBJECT_ID) > > FROM SYS.INDEXES > > WHERE INDEX_ID = 0 > > AND OBJECTPROPERTY(OBJECT_ID,'IsUserTable') = 1 > > ORDER BY [TABLE] > > > > acl_sync > > article_flag > > customer_preferences > > generic_agent_jobs > > gi_object_lock_state > > group_customer_user > > group_role > > group_user > > link_relation > > notification_event_item > > personal_queues > > pm_entity > > pm_entity_sync > > postmaster_filter > > process_id > > queue_preferences > > queue_standard_template > > role_user > > search_profile > > service_customer_user > > service_preferences > > service_sla > > sla_preferences > > support_bench_test > > ticket_flag > > ticket_index > > ticket_lock_index > > ticket_loop_protection > > ticket_watcher > > user_preferences > > virtual_fs_preferences > > web_upload_cache > > xml_storage > > > > > > > > > > _______________________________________________ > > 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 > > > > -- > Martin Gruner > Senior Developer R&D > > 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
