MS SQL Server compatibility [#628]

Drupal has been tested on MySQL and Postgres database servers. But it probably runs on many more, due to its PEAR support. Let's make it work on Microsoft SQL server. Drupal already runs just fine on IIS, and many intranets would pick Drupal if it could run on pure Microsoft software.

The inital step in this process is to convert the database script which creates the tables and indices into a SQL Server compatible format. The model for this new script should be the database.mysql and database.pgsql files in the 'database' directory.

after the tables are created, an working drupal install may be as easy as specifying the right connection URL in conf.php. That URL would start with mssql:// ...

A few tweaks to Drupal would probably be needed later, but this one script could get us very close.

Comments

Comment #1

moshe weitzman commented 8 October 2002 at 02:16

marco posted this helpful URL to the devel list:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/iis/deploy/depovg/mysql.asp

Comment #2

moshe weitzman commented 18 February 2003 at 23:05

I followed the steps indicated in the MSDN link below. So below I submit an MS SQL server database script which creates a 4.1 compliant Drupal database.

After setting up PEAR, enabling the mssql dynamic extension in PHP.ini, and changing my $db_url in conf.php to mssql://, I attempted to run Drupal. Alas I'm stuck now because PHP (v4.3 as ISAPI module & Apache module) is causing Access Violations and generally crashing during session_start(). I know that I can connect to the DB and run queries, because a few test scripts work fine.

I'm hoping someone else will take the script below and complete the SQL server port, or at least get the home page to display.


CREATE TABLE [dbo].[access] (
	[aid] [smallint] NULL ,
	[mask] [varchar] (255) NOT NULL ,
	[type] [varchar] (255) NOT NULL ,
	[status] [smallint] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[accesslog] (
	[nid] [numeric](11, 0) NULL ,
	[url] [varchar] (255) NULL ,
	[hostname] [varchar] (128) NULL ,
	[uid] [numeric](10, 0) NULL ,
	[timestamp] [numeric](11, 0) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[authmap] (
	[aid] [numeric](10, 0) NULL ,
	[uid] [int] NOT NULL ,
	[authname] [varchar] (128) NOT NULL ,
	[module] [varchar] (128) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[blocks] (
	[module] [varchar] (64) NOT NULL ,
	[delta] [smallint] NOT NULL ,
	[status] [smallint] NOT NULL ,
	[weight] [smallint] NOT NULL ,
	[region] [smallint] NOT NULL ,
	[path] [varchar] (255) NOT NULL ,
	[custom] [smallint] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[book] (
	[nid] [numeric](4, 0) NOT NULL ,
	[parent] [int] NOT NULL ,
	[weight] [smallint] NOT NULL ,
	[format] [smallint] NULL ,
	[log] [text] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[boxes] (
	[bid] [smallint] NULL ,
	[title] [varchar] (64) NOT NULL ,
	[body] [text] NULL ,
	[info] [varchar] (128) NOT NULL ,
	[type] [smallint] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[bundle] (
	[bid] [int] NULL ,
	[title] [varchar] (255) NOT NULL ,
	[attributes] [varchar] (255) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[cache] (
	[cid] [varchar] (255) NOT NULL ,
	[data] [text] NULL ,
	[expire] [int] NOT NULL ,
	[created] [int] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[comments] (
	[cid] [int] NULL ,
	[pid] [int] NOT NULL ,
	[nid] [int] NOT NULL ,
	[uid] [int] NOT NULL ,
	[subject] [varchar] (64) NOT NULL ,
	[comment] [text] NOT NULL ,
	[hostname] [varchar] (128) NOT NULL ,
	[timestamp] [int] NOT NULL ,
	[link] [varchar] (16) NOT NULL ,
	[score] [int] NOT NULL ,
	[status] [tinyint] NOT NULL ,
	[users] [text] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[directory] (
	[link] [varchar] (255) NOT NULL ,
	[name] [varchar] (128) NOT NULL ,
	[mail] [varchar] (128) NOT NULL ,
	[slogan] [text] NOT NULL ,
	[mission] [text] NOT NULL ,
	[timestamp] [int] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[feed] (
	[fid] [int] NULL ,
	[title] [varchar] (255) NOT NULL ,
	[url] [varchar] (255) NOT NULL ,
	[refresh] [int] NOT NULL ,
	[timestamp] [int] NOT NULL ,
	[attributes] [varchar] (255) NOT NULL ,
	[link] [varchar] (255) NOT NULL ,
	[description] [text] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[forum] (
	[nid] [numeric](10, 0) NOT NULL ,
	[tid] [numeric](10, 0) NOT NULL ,
	[icon] [varchar] (255) NOT NULL ,
	[shadow] [numeric](10, 0) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[history] (
	[uid] [int] NOT NULL ,
	[nid] [int] NOT NULL ,
	[timestamp] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[item] (
	[iid] [int] NULL ,
	[fid] [int] NOT NULL ,
	[title] [varchar] (255) NOT NULL ,
	[link] [varchar] (255) NOT NULL ,
	[author] [varchar] (255) NOT NULL ,
	[description] [text] NOT NULL ,
	[timestamp] [int] NULL ,
	[attributes] [varchar] (255) NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[locales] (
	[lid] [int] NULL ,
	[location] [varchar] (128) NOT NULL ,
	[string] [text] NOT NULL ,
	[da] [text] NOT NULL ,
	[fi] [text] NOT NULL ,
	[fr] [text] NOT NULL ,
	[en] [text] NOT NULL ,
	[es] [text] NOT NULL ,
	[nl] [text] NOT NULL ,
	[no] [text] NOT NULL ,
	[sw] [text] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[menu] (
	[name] [varchar] (255) NOT NULL ,
	[link] [varchar] (255) NOT NULL ,
	[help] [text] NULL ,
	[title] [varchar] (255) NOT NULL ,
	[parent] [varchar] (255) NOT NULL ,
	[weight] [smallint] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[moderation_filters] (
	[fid] [numeric](10, 0) NULL ,
	[filter] [varchar] (255) NOT NULL ,
	[minimum] [smallint] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[moderation_roles] (
	[rid] [numeric](4, 0) NOT NULL ,
	[mid] [numeric](4, 0) NOT NULL ,
	[value] [smallint] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[moderation_votes] (
	[mid] [numeric](10, 0) NULL ,
	[vote] [varchar] (255) NULL ,
	[weight] [smallint] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[node] (
	[nid] [numeric](10, 0) NULL ,


	[type] [varchar] (16) NOT NULL ,
	[title] [varchar] (128) NOT NULL ,
	[score] [int] NOT NULL ,
	[votes] [int] NOT NULL ,
	[uid] [int] NOT NULL ,
	[status] [int] NOT NULL ,
	[created] [int] NOT NULL ,
	[comment] [int] NOT NULL ,
	[promote] [int] NOT NULL ,
	[moderate] [int] NOT NULL ,
	[users] [text] NOT NULL ,
	[attributes] [varchar] (255) NOT NULL ,
	[teaser] [text] NOT NULL ,
	[body] [text] NOT NULL ,
	[changed] [int] NOT NULL ,
	[revisions] [text] NOT NULL ,
	[static] [int] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[page] (
	[nid] [numeric](10, 0) NOT NULL ,
	[link] [varchar] (128) NOT NULL ,
	[format] [smallint] NOT NULL ,
	[description] [varchar] (128) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[permission] (
	[rid] [numeric](4, 0) NOT NULL ,
	[perm] [text] NULL ,
	[tid] [numeric](4, 0) NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[poll] (
	[nid] [numeric](4, 0) NOT NULL ,
	[runtime] [int] NOT NULL ,
	[voters] [text] NOT NULL ,
	[active] [numeric](4, 0) NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[poll_choices] (
	[chid] [numeric](10, 0) NULL ,
	[nid] [numeric](10, 0) NOT NULL ,
	[chtext] [varchar] (128) NOT NULL ,
	[chvotes] [int] NOT NULL ,
	[chorder] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[role] (
	[rid] [numeric](10, 0) NULL ,
	[name] [varchar] (32) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[search_index] (
	[word] [varchar] (50) NOT NULL ,
	[lno] [numeric](10, 0) NOT NULL ,
	[type] [varchar] (16) NULL ,
	[count] [numeric](10, 0) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[sequences] (
	[name] [varchar] (255) NOT NULL ,
	[id] [numeric](10, 0) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[site] (
	[sid] [numeric](10, 0) NULL ,
	[name] [varchar] (128) NOT NULL ,
	[link] [varchar] (255) NOT NULL ,
	[size] [text] NOT NULL ,
	[timestamp] [int] NOT NULL ,
	[feed] [varchar] (255) NOT NULL ,
	[refresh] [int] NOT NULL ,
	[threshold] [int] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[statistics] (
	[nid] [int] NOT NULL ,
	[totalcount] [numeric](20, 0) NOT NULL ,
	[daycount] [numeric](4, 0) NOT NULL ,
	[timestamp] [numeric](4, 0) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[system] (
	[filename] [varchar] (26) NOT NULL ,
	[name] [varchar] (8) NOT NULL ,
	[type] [varchar] (6) NOT NULL ,
	[description] [varchar] (34) NOT NULL ,
	[status] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[term_data] (
	[tid] [numeric](10, 0) NULL ,
	[vid] [numeric](10, 0) NOT NULL ,
	[name] [varchar] (255) NOT NULL ,
	[description] [text] NULL ,
	[weight] [smallint] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[term_hierarchy] (
	[tid] [numeric](4, 0) NOT NULL ,
	[parent] [numeric](4, 0) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[term_node] (
	[nid] [numeric](4, 0) NOT NULL ,
	[tid] [numeric](4, 0) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[term_relation] (
	[tid1] [numeric](4, 0) NOT NULL ,
	[tid2] [numeric](4, 0) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[term_synonym] (
	[tid] [numeric](10, 0) NOT NULL ,
	[name] [varchar] (255) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[users] (
	[uid] [numeric](10, 0) NULL ,
	[name] [varchar] (60) NOT NULL ,
	[pass] [varchar] (32) NOT NULL ,
	[mail] [varchar] (64) NULL ,
	[homepage] [varchar] (128) NOT NULL ,
	[mode] [smallint] NOT NULL ,
	[sort] [smallint] NULL ,
	[threshold] [smallint] NULL ,
	[theme] [varchar] (255) NOT NULL ,
	[signature] [varchar] (255) NOT NULL ,
	[timestamp] [int] NOT NULL ,
	[hostname] [varchar] (128) NOT NULL ,
	[status] [smallint] NOT NULL ,

	[timezone] [varchar] (8) NULL ,
	[rating] [numeric](8, 2) NULL ,
	[language] [char] (2) NOT NULL ,
	[sid] [varchar] (32) NOT NULL ,
	[init] [varchar] (64) NULL ,
	[session] [text] NULL ,
	[data] [text] NULL ,
	[rid] [numeric](10, 0) NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[variable] (
	[name] [varchar] (13) NOT NULL ,
	[value] [varchar] (18) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[vocabulary] (
	[vid] [numeric](10, 0) NULL ,
	[name] [varchar] (255) NOT NULL ,
	[description] [text] NULL ,
	[relations] [tinyint] NOT NULL ,
	[hierarchy] [tinyint] NOT NULL ,
	[multiple] [tinyint] NOT NULL ,
	[required] [tinyint] NOT NULL ,
	[types] [text] NULL ,
	[weight] [smallint] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[watchdog] (
	[wid] [int] NULL ,
	[uid] [int] NOT NULL ,
	[type] [varchar] (16) NOT NULL ,
	[message] [text] NOT NULL ,
	[link] [varchar] (255) NOT NULL ,
	[location] [varchar] (128) NOT NULL ,
	[hostname] [varchar] (128) NOT NULL ,
	[timestamp] [int] NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Here is a PHP script which you may save to your Drupal root directory and then browse to it. It works for me, and illustrates that my PEAR is generally working fine.


include_once "includes/conf.php";
include_once "includes/database.inc";
include_once "includes/xmlrpc.inc";
include_once "includes/module.inc";
include_once "includes/theme.inc";
include_once "includes/pager.inc";

function variable_init($conf = array()) {
  $result = db_query("SELECT * FROM variable");
  while ($variable = db_fetch_object($result)) {
    if (!isset($conf[$variable->name])) {
      $conf[$variable->name] = unserialize($variable->value);
    }
  }

  return $conf;
}

function variable_get($name, $default, $object = 0) {
  global $conf;

  return isset($conf[$name]) ? $conf[$name] : $default;
}

// initialize configuration variables, using values from conf.php if available:
$conf = variable_init(isset($conf) ? $conf : array());

$result = db_query("SELECT * FROM variable");
while ($item = db_fetch_object($result)) {
    print_r($item);
}

Comment #3

al commented 12 March 2003 at 04:32

Assigned:	Unassigned	» moshe weitzman
Priority:	Major	» Minor

Assigning this to Moshe, as he seems to be actively developing it on drupal-devel.

Comment #4

moshe weitzman commented 22 March 2003 at 14:27

Priority:

Minor

» Critical

this feature request is complete and rolled out to CVS. please open new bugs as needed. closed.

MS SQL Server compatibility

Comments

Comment #1

Comment #2

Comment #3

Comment #4

News items

Our community

Documentation

Drupal code base

Governance of community