Mantis on LA~P, MSSQL 2k8 on Windows

[piete]

My situation:
I'd like to run MantisBT here at work, and integrate it against some of our other tools (all bespoke), this will include hacking the authentication mechanisms eventually, but for now, I'm just after proof of concept. Eventually, I may get around to looking at Sharepoint integration, but that's another problem. I'm deliberately keeping this off Active Directory, so it's a fairly stock set up from that point of view, where it gets gnarly is that it would be beneficial (for integration) to have it running from the database as the rest of the tools. MS SQL. On Windows.

The components:

* Freshly baked download of MantisBT 1.2.10
* Ubuntu 11.10 box with Apache/2.2.20 + PHP 5.3.6-13ubuntu3.6
* FreeTDS 0.91 with the data source set up to talk to the MSSQL server
* unixODBC to connect FreeTDS with php (using odbc_connect)
* MSSQL Server 2008 (x64) running on some Windows Server

The setup:

Code: Select all

WinLand  |    NixWorld
MSSQL <--|--> FreeTDS <--> unixODBC <--> PHP


Independantly I can assert that this setup 'works', in the sense that if I write valid T-SQL and push it through odbc_connect, I get the correct results on the database (permissions and connections notwithstanding). I'm also using it in several production setups without error, either using 100% custom php or through the Kohana framework.

After some install.php hacking (added in odbc_mssql as a database option), I managed to get it to spit out the database script that needed to be applied.

Because of the way constraints are handled with MSSQL, I had to manually delete the columns that were set to drop as part of the upgrade, but everything has gone into the DB just fine. I can see the administrator and the version records, and after adding another ODBC source that explicitly points to the bugtracker database, I can get install.php to recognise the version.

I realise that what I've done is basically the definition of "unsupported", but I've run into a curiousity that I can't get over without massive patching.

When I run up the application, it immediately reports query failures of the kind:

Code: Select all

Database query failed. Error received from database was #0: [unixODBC][Driver Manager]Driver does not support this function for the query:
UPDATE mantis_user_table SET login_count=login_count+1 WHERE id=?.

Pasting that into the SQL Management Console tool thing, it will cheerfully inform you of the same.

The problem is WHERE id=? - ? is an invalid wildcard for MSSQL. Combing through the source, following the SQL that's failing from places like plugin_api.php@801:

Code: Select all

   $t_query = "SELECT basename, priority, protected FROM $t_plugin_table WHERE enabled=" . db_param() . ' ORDER BY priority DESC';


Gives you db_param(), which leads to adodb.inc.php@786:

Code: Select all

   /*
       Returns placeholder for parameter, eg.
       $DB->Param('a')
      
       will return ':a' for Oracle, and '?' for most other databases...
      
       For databases that require positioned params, eg $1, $2, $3 for postgresql,
          pass in Param(false) before setting the first parameter.
   */
   function Param($name,$type='C')
   {
      return '?';
   }

So two questions for curiousity, really:

1. I don't actually understand how Param(...) works, according to the comments.
2. Why are the queries, which seem to return everything from a table, formed like this: "SELECT a,b FROM tbl WHERE c=?"; I can see no benefit to using that wildcard technique over "SELECT a,b FROM tbl".

Ultimately, if this is a non-starter, I'll find another technical solution to getting this integrated. I feel so tantalisingly close, however, that if I could just understand the purpose of db_param(), I'd be away!

Many thanks for your thoughts,
- Piete.

[cas]

For understanding db_param you better check out the database _api, here is as sample from bug_api how it actually works:
$query = "SELECT COUNT(*) FROM $t_bugnote_table WHERE bug_id =" . db_param() . " $t_restriction";
$result = db_query_bound( $query, Array( $this->bug_id ) );

So there is some understanding left for you

[piete]

Thanks for your comment cas, I was about to rant about how your example was not helpful, but I saw the light just in time It was just the pointer I needed to take another look at the workflow, and after some poking around in the adodb library code, I'm pleased to report that everything now seems to be working, so thank you!

For reference, to get Mantis to run in this configuration:

* Hook up MSSQL through freetds and make sure that works.
* Add an odbc DSN that points directly to the mantis DB via freetds.
* Edit admin/install.php@443, add 'odbc_mssql' => 'ODBC MSSQL' to the available DB types array.
* Copy the sample config_inc.php into place, and add the relevant information about your database. For me, this was:

Code: Select all

$g_hostname = 'MSSQL_bugtracker'; // This is my odbc DSN
$g_db_username="<user>";
$g_db_password="<pass>";

$g_database_name = 'bugtracker';
$g_db_type       = 'odbc_mssql';

This'll let you get the SQL to set up the databases, but it makes it look like you're upgrading rather than performing a fresh install.

* Tick the "generate SQL instead of installing" button, to get the SQL, copy it into a text file and keep it safe.
* Run the SQL in the management studio, performing failed operations by hand if necessary (dropping constraints particularly). This was a bit time consuming and if I was going to do it again, I'd probably knock up a parser to sort this out for me.

At this point you should be where I was a couple of weeks ago. The final step is to get the db_param() function to actually do what it's supposed to do ...

* Edit library/adodb/drivers/adodb-odbc_mssql.inc.php@27, commenting out the _bindInputArray function.

Code: Select all

   var $fmtTimeStamp = "'Y-m-d H:i:s'";
+   //var $_bindInputArray = true;
-   var $_bindInputArray = true;


And for future me who comes looking for this information, the general concept is thus:

1. db_param() generates a fixed wildcard (? in this case) to insert into SQL statements, so they're generated like "SELECT * FROM a WHERE b=? and c=?" - it needs to be noted that this wildcard isn't intended to be parsed by the database ...
2. Calling db_query_bound( $sql, Array( 7,8 ) ) effectively performs a find and replace on ? with the array values, so the above becomes: "SELECT * FROM a WHERE b=7 and c=8"
3. If _bindInputArray is true, when Execute() is called in library/adodb/adodb.inc.php, it skips the find/replace step (see line 958 for the logical check on this), therefore calling literally "SELECT * FROM a WHERE b=? and c=?", instead of a well formed statement.

My confusion came because b=? looks like valid SQL if you're running MySQL, so I figured that was intentional. Doesn't look like it is, however.

If it turns out my installation is horribly horribly broken, I'll report back, but for now, I can at least get some testing done to see if it works okay.

Cheers,
- Piete.

PS: As always, standard forum disclaimers apply - provided without warrenty(c), works for me(tm), if it breaks you get to keep both halves(r).