Using MSSQL with resourcespace

[sapna]

Hi,

Can I use mssql database rather than mysql with resourcespace.

Thanks

[Tom Gleason]

No, only mysql is supported at this time.

> --
> You received this message because you are subscribed to the Google Groups "ResourceSpace" group.
> To post to this group, send email to resour...@googlegroups.com.
> To unsubscribe from this group, send email to resourcespac...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/resourcespace?hl=en.
>

[Dan Huby]

It isn't supported, but it shouldn't be necessary, because:

1) MySQL administration skills are not required to use MySQL with
ResourceSpace - it will create the necessary database tables and
upgrade them automatically. You just need to install MySQL and the
instructions for that are on the wiki.

2) MySQL is free and MSSQL is not.

3) MySQL is lightweight compared to MSSQL and uses fewer system
resources.

The database should really be considered an integral part of the
ResourceSpace package rather than a separate entity that will need a
skilled DBA to maintain.

Out of interest, what advantages are you hoping to gain by using MSSQL
over MySQL?

I used MSSQL/ASP for several years prior two switching to MySQL/PHP -
and I find MySQL to be much more suited to web application development
than MSSQL.

Dan

[sapna]

Hi,

Thanks a lot for replying , actually my client is insisting on using
MSSQL with resourcespace.

Thanks
Sapna

[Dan Huby]

On 30 Oct, 12:38, sapna <sapna...@gmail.com> wrote:
> Thanks a lot for replying , actually my client is insisting on using
> MSSQL with resourcespace.

Hopefully the points I made will be useful. I would be interested to
hear their response.

Dan

[JohnBB]

Tom and Dan are over-stating the case here - resourcespace is designed
to work with MySQL, but it works through an abstratction layer which
means it does not talk directly to the database but always goes
through the sql_query group of functions. To quote from the comments:

sql_query(sql) - execute a query and return the results as an array.
# Database functions are wrapped in this way so supporting a database
server other than MySQL is easier.

This means if your client insists on MS SQL Server, they can have it -
but they need to understand there is in initial cost in rewriting the
abstraction layer and ongoing costs in supporting and maintaining this
change. If your client is prepared to accept these costs and
associated delays then they obviously have a good reason to want to
avoid MySQL - we would all be interested to hear there reasons!

If they decide to go this way and need help finding a developer, let
us know.

Regards
John

[Tom Gleason]

Just be aware that this is not necessarily just a matter of changing
mysql_query to mssql_query and fixing up a DB configuration for SQL
Server.
Also don't forget you've got to make it work with dbstruct as well,
make it set up tables and indexes correctly using current dbstruct
files, and take advantage of any strengths of the new database server.

I did some work changing the abstraction layer to support PostgreSQL
in this way. There were data types that didn't translate well, and
many SQL idioms that are acceptable in MySQL but not in PostgreSQL.

Ideally, this would be an opportunity to standardize the SQL to
provide more compatibility. And I'm still a little interested in how
that might work out.

However, much of this article (however offensive it may be to some)
rang true with me after my initial attempts to support multiple
databases:
http://jeremy.zawodny.com/blog/archives/002194.html

"The cost of switching databases after an application is developed and
deployed is quite high. You have possible schema and index changes,
syntax changes, optimization and tuning work to re-do, hints to adjust
or remove, and so on. Changing mysql_foo() to oracle_foo() is really
the least of your problems. You're gonna touch most, if not all, of
your SQL--or you'll at least need to verify it."

ResourceSpace really is designed to work with MySQL. My thought would
be that any DAM which claims to support all databases in a
hot-swappable fashion probably hasn't gone into deep detail on the
issue of optimization. While RS still has room for optimization, much
of the code is the product of ongoing and wide-spread developer and
community experience with the specific behaviors of MySQL.

> --
> You received this message because you are subscribed to the Google Groups "ResourceSpace" group.
> To post to this group, send email to resour...@googlegroups.com.
> To unsubscribe from this group, send email to resourcespac...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/resourcespace?hl=en.
>
>

--
Tom Gleason, PHP Developer
DBA Impressive Design

Exploring ResourceSpace at:
http://resourcespace.blogspot.com

[David Dwiggins]

I'll second this. Speaking for myself, I have added code in at least two places which uses the MySQL temporary table feature to address performance issues in MySQL with certain types of queries. MS SQL theoretically supports temporary tables, but I'm not sure if the behavior is exactly the same. And, more to the point, MS SQL may have other (perhaps better) ways to deal with the performance issues I was trying to address. The temporary tables feature is optional, so theoretically disabling it would solve this specific problem. But the larger point is that there many be countless other places where embedded relies on certain behavior that works differently in MySQL vs. MSSQL. These would all need to be tested and addressed.

In a perfect world, perhaps database activity would be so abstracted from the procedural code that it really would simply be a matter of swapping out the database functions. But I think that is seldom the case in most applications, particularly those as complex as ResourceSpace. And there is definitely additional work for all developers involved in supporting multiple database platforms. Testing alone becomes harder. Speaking for myself, I do not currently have MSSQL or Oracle servers set up to test against, which means that it would be difficult for me to guarantee that my changes will work correctly on all database platforms.

I understand why some IT departments like the idea of keeping all enterprise data on one database platform. And I do think database neutrality can work in some applications. (I'm aware of one java project, Archivist's Toolkit, that I believe was designed to work on MySQL, MS SQL, and Oracle.) But supporting three databases definitely increases the workload for all developers, and, realistically, certifying ResourceSpace on additional database platforms would involve a fairly intensive project of recoding and testing parts of the system. It's not impossible, but neither would it be a simple matter of replacing a function or two.

-David

[JohnBB]

So would anybody like to put a ballpark cost on this? If Sapna goes
back and says it's not possible, the customer looks for another
product. If the answer is 'it is possible but will cost you £20,000
and take six months to deliver', the customer has a basis for making a
decision.

I think we have given Sapna a lot of arguments about why it is not a
good idea, but at the end of the day it's the customers decision.


Regards
John

> > resourcespac...@googlegroups.com<resourcespace%2Bunsu...@googlegroups.com>
> > .

> > > For more options, visit this group at
> >http://groups.google.com/group/resourcespace?hl=en.
>
> > --
> > Tom Gleason, PHP Developer
> > DBA Impressive Design
>
> > Exploring ResourceSpace at:
> >http://resourcespace.blogspot.com
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "ResourceSpace" group.
> > To post to this group, send email to resour...@googlegroups.com.
> > To unsubscribe from this group, send email to

> > resourcespac...@googlegroups.com<resourcespace%2Bunsu...@googlegroups.com>
> > .

[Dan Huby]

> I think we have given Sapna a lot of arguments about why it is not a
> good idea, but at the end of the day it's the customers decision.

Not necessarily - if it's just a bad move then sometimes it's better
to educate the customer. A good developer won't implement a bad
solution just because a customer is paying for it.

Anyway I don't think this is entirely a bad move - it's possible as
has been mentioned that the customer has a series of other
applications running on MSSQL and wishes to use it purely for
consistency and easy of support. I used MSSQL for several years and
it's not bad although probably quite bloated for ResourceSpace's
needs. MySQL is better for this purpose.

I would say this is a big piece of work, at least 20 man days if not
more, for the reasons Tom and David give. It's certainly not a
straightforward port. And there are serious onward implications for
the software - everything developed has to be both MSSQL/MySQL
compliant and who is going to pay for the extra development / testing
time that would be part of all future modifications? I find it
irritating enough to have to run a Windows environment for testing on
Internet Explorer... having to run a copy of Microsoft SQL Server is
going to be a pain and will seriously put off developers from joining
the project.

Or are we talking about developing a MSSQL branch?

Dan

[JohnBB]

Like you, I worked with MS SQL for several years and made the move to
MySQL when I started working with RS. I cannot speak for all the plug-
ins and peripheral code, but the core RS system uses sql queries that
would plug straight into MS SQL (as long as you add a semi-colon on
the end). There are a couple of gotchas that need resolving, like
getting the ID of the last added record, but nothing overly
complicated and all can be accomodated by rewriting the abstraction
layer, so there should be minimal changes to the rest of the code.

I was not contemplating this as something that would automatically
become part of the main RS portfolio, more as a custom modification.
The client would have to accept that any plugins or patches to the
main branch would need testing against SQL Server and they would need
to accept the cost and delays this introduces. Meanwhile, our
developers need to acknowledge that their code may be run against a
different back end and make any MySQL optimisation optional. In
future, if other clients insist on MS SQL they can join together and
share the costs, but I do not seeing it ever being a large proportion
of our users.

I think my main point here though was that many organisations have
been trapped in the Microsoft net and have passed policies that say
'all bought-in software must run on our standard server platform -
Windows Server, MS SQL, IIS, etc.'. Say your software does not comply
with this policy and it will not be accepted. Say it can comply but
the compliant version costs a lot more, and the accountants start
taking over from the IT people in making decisions.

Incidentally, I was thinking rather more than 20 days - but my daily
rate is lower than yours, so it probably comes out about the same!
Say £15,000 to £20,000 for the first year + £5,000 per year ongoing -
it's still very cheap for what you are getting!

Regards
John

[Tom Gleason]

Meanwhile, our
developers need to acknowledge that their code may be run against a
different back end and make any MySQL optimisation optional.

I think it's a worthwhile project, but you can't expect RS devs in general to concern themselves with this compatibility in any way.

Even if the customization was released as a plugin, or worked into the base unobtrusively, compatibility is expected to be addressed only by the developer of the mod (who is presumably being paid to do so).

[Crispin Flower]

Can I ask if anything has changed since this thread, regarding support for MSSQL?

Apologies if this is covered in other threads - my search didn't find them!

thanks

Crispin