*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
