is there mssql server database scripts for jpetstore?

[nxliu]

I download the springframework 1.2.3 and installed the jpetstore for hsqldb database successfully, when I try to change the datasource, i only found the database scripts for mysql, hsqldb, oracle and postgres in sample directory, no mssql scripts. I would like to use the mssql section in config files for jpetstore sample, can somebody tell me where to get the scripts?

[katentim]

can somebody tell me where to get the scripts?

Well they're not in CVS yet
http://cvs.sourceforge.net/viewcvs.p.../jpetstore/db/

Not sure if they're elsewhere - maybe you could contribute them. It shouldn't take much tweaking from the existing scripts.

[nxliu]

I found following section:
<!-- OrderDao definition for MS SQL Server -->
<!-- (to be used instead of the default orderDao) -->
<!--
<bean id="orderDao" class="org.springframework.samples.jpetstore.dao.i batis.MsSqlOrderDao">
<property name="sqlMapClient" ref="sqlMapClient"/>
<property name="sequenceDao" ref="sequenceDao"/>
</bean>
-->
in the sample's config file dataAccessContext-local.xml

so I guess there is mssql scripts exists somewhere, i will try to contribute one later if no

[nxliu]

I have created the mssql server's database schema for jpetstore sample, for the data loading for mssql server, you can use the mysql or oracle's scripts which come with the jpetstore sample;

i use microsoft's jdbc driver to connect: com.microsoft.jdbc.sqlserver.SQLServerDriver

the sample works well on mssql server

following is the schema scripts for mssql:

-- ************************************************** *****
-- database schema scripts for jpetstore on SpringFramework
-- by nxliu@spacesys.net
-- ************************************************** *****

-- ************************************************** *****
-- Create database
-- ************************************************** *****
USE [master]

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'jpetstore')
BEGIN
DECLARE @spid smallint
DECLARE @sql varchar(4000)

DECLARE crsr CURSOR FAST_FORWARD FOR
SELECT spid FROM sysprocesses p INNER JOIN sysdatabases d ON d.[name] = 'jpetstore' AND p.dbid = d.dbid

OPEN crsr
FETCH NEXT FROM crsr INTO @spid

WHILE @@FETCH_STATUS != -1
BEGIN
SET @sql = 'KILL ' + CAST(@spid AS varchar)
EXEC(@sql)
FETCH NEXT FROM crsr INTO @spid
END

CLOSE crsr
DEALLOCATE crsr

DROP DATABASE [jpetstore]
END
GO

CREATE DATABASE [jpetstore]
GO

Use jpetstore

-- ************************************************** *****
-- Create tables
-- ************************************************** *****

--supplier

IF OBJECT_ID('dbo.supplier') IS NOT NULL
DROP TABLE [dbo].[supplier]
GO

create table [dbo].[supplier] (
[suppid] int not null,
[name] varchar(80) null,
[status] varchar(2) not null,
[addr1] varchar(80) null,
[addr2] varchar(80) null,
[city] varchar(80) null,
[state] varchar(80) null,
[zip] varchar(5) null,
[phone] varchar(80) null,
constraint [pk_supplier] primary key
(
[suppid]
)
)
GO


--signon

IF OBJECT_ID('dbo.signon') IS NOT NULL
DROP TABLE [dbo].[signon]
GO

create table [dbo].[signon] (
[username] varchar(25) not null,
[password] varchar(25) not null,
constraint pk_signon primary key
(
[username]
)
)
GO


--account

IF OBJECT_ID('dbo.account') IS NOT NULL
DROP TABLE [dbo].[account]
GO

create table [dbo].[account] (
[userid] varchar(80) not null,
[email] varchar(80) not null,
[firstname] varchar(80) not null,
[lastname] varchar(80) not null,
[status] varchar(2) null,
[addr1] varchar(80) not null,
[addr2] varchar(40) null,
[city] varchar(80) not null,
[state] varchar(80) not null,
[zip] varchar(20) not null,
[country] varchar(20) not null,
[phone] varchar(80) not null,
constraint pk_account primary key
(
[userid]
)
)
GO


--profile

IF OBJECT_ID('dbo.profile') IS NOT NULL
DROP TABLE [dbo].[profile]
GO

create table [dbo].[profile] (
[userid] varchar(80) not null,
[langpref] varchar(80) not null,
[favcategory] varchar(30),
[mylistopt] bit,
[banneropt] bit,
constraint pk_profile primary key
(
[userid]
)
)
GO


--bannerdata

IF OBJECT_ID('dbo.bannerdata') IS NOT NULL
DROP TABLE [dbo].[bannerdata]
GO

create table [dbo].[bannerdata] (
[favcategory] varchar(80) not null,
[bannername] varchar(255) null,
constraint pk_bannerdata primary key
(
[favcategory]
)
)
GO


--orders

IF OBJECT_ID('dbo.orders') IS NOT NULL
DROP TABLE [dbo].[orders]
GO

create table [dbo].[orders] (
[orderid] int not null,
[userid] varchar(80) not null,
[orderdate] datetime not null,
[shipaddr1] varchar(80) not null,
[shipaddr2] varchar(80) null,
[shipcity] varchar(80) not null,
[shipstate] varchar(80) not null,
[shipzip] varchar(20) not null,
[shipcountry] varchar(20) not null,
[billaddr1] varchar(80) not null,
[billaddr2] varchar(80) null,
[billcity] varchar(80) not null,
[billstate] varchar(80) not null,
[billzip] varchar(20) not null,
[billcountry] varchar(20) not null,
[courier] varchar(80) not null,
[totalprice] decimal(10,2) not null,
[billtofirstname] varchar(80) not null,
[billtolastname] varchar(80) not null,
[shiptofirstname] varchar(80) not null,
[shiptolastname] varchar(80) not null,
[creditcard] varchar(80) not null,
[exprdate] varchar(7) not null,
[cardtype] varchar(80) not null,
[locale] varchar(80) not null,
constraint pk_orders primary key
(
[orderid]
)
)
GO


--orderstatus

IF OBJECT_ID('dbo.orderstatus') IS NOT NULL
DROP TABLE [dbo].[orderstatus]
GO

create table [dbo].[orderstatus] (
[orderid] int not null,
[linenum] int not null,
[timestamp] datetime not null,
[status] varchar(2) not null,
constraint pk_orderstatus primary key
(
[orderid], [linenum]
)
)
GO


--lineitem

IF OBJECT_ID('dbo.lineitem') IS NOT NULL
DROP TABLE [dbo].[lineitem]
GO

create table [dbo].[lineitem] (
[orderid] int not null,
[linenum] int not null,
[itemid] varchar(10) not null,
[quantity] int not null,
[unitprice] decimal(10,2) not null,
constraint pk_lineitem primary key
(
[orderid], [linenum]
)
)
GO


--category

IF OBJECT_ID('dbo.category') IS NOT NULL
DROP TABLE [dbo].[category]
GO

create table [dbo].[category] (
[catid] varchar(10) not null,
[name] varchar(80) null,
[descn] varchar(255) null,
constraint pk_category primary key
(
[catid]
)
)
GO


--product

IF OBJECT_ID('dbo.product') IS NOT NULL
DROP TABLE [dbo].[product]
GO

create table [dbo].[product] (
[productid] varchar(10) not null,
[category] varchar(10) not null,
[name] varchar(80) null,
[descn] varchar(255) null,
constraint pk_product primary key
(
[productid]
)
)
GO


--item

IF OBJECT_ID('dbo.item') IS NOT NULL
DROP TABLE [dbo].[item]
GO

create table [dbo].[item] (
[itemid] varchar(10) not null,
[productid] varchar(10) not null,
[listprice] decimal(10,2) null,
[unitcost] decimal(10,2) null,
[supplier] int null,
[status] varchar(2) null,
[attr1] varchar(80) null,
[attr2] varchar(80) null,
[attr3] varchar(80) null,
[attr4] varchar(80) null,
[attr5] varchar(80) null,
constraint pk_item primary key
(
[itemid]
)
)
GO


--inventory

IF OBJECT_ID('dbo.inventory') IS NOT NULL
DROP TABLE [dbo].[inventory]
GO

create table [dbo].[inventory] (
[itemid] varchar(10) not null,
[qty] int not null,
constraint pk_inventory primary key
(
[itemid]
)
)
GO


--sequence

IF OBJECT_ID('dbo.sequence') IS NOT NULL
DROP TABLE [dbo].[sequence]
GO

create table [dbo].[sequence] (
[name] varchar(30) not null,
[nextid] int not null,
constraint pk_sequence primary key
(
[name]
)
)
GO


-- ************************************************** *****
-- Create index
-- ************************************************** *****

IF EXISTS (SELECT [name] FROM sysindexes
WHERE [name] = 'productCat')
DROP INDEX [dbo].[product].[productCat]
GO

CREATE INDEX [productCat]
ON [dbo].[product] ([category])
GO


IF EXISTS (SELECT [name] FROM sysindexes
WHERE [name] = 'productName')
DROP INDEX [dbo].[product].[productName]
GO

CREATE INDEX [productName]
ON [dbo].[product] ([name])
GO


IF EXISTS (SELECT [name] FROM sysindexes
WHERE [name] = 'itemProd')
DROP INDEX [dbo].[item].[itemProd]
GO

CREATE INDEX [itemProd]
ON [dbo].[item] ([productid])
GO



-- ************************************************** *****
-- Create foreign key
-- ************************************************** *****

--product

IF OBJECT_ID('fk_product_category') IS NOT NULL
ALTER TABLE [dbo].[product] DROP CONSTRAINT [fk_product_category]

GO

ALTER TABLE [dbo].[product]
ADD
CONSTRAINT [fk_product_category] FOREIGN KEY
(
[category]
) REFERENCES [dbo].[category] (
[catid]
)

GO


--item

IF OBJECT_ID('fk_item_product') IS NOT NULL
ALTER TABLE [dbo].[item] DROP CONSTRAINT [fk_item_product]

GO

IF OBJECT_ID('fk_item_supplier') IS NOT NULL
ALTER TABLE [dbo].[item] DROP CONSTRAINT [fk_item_supplier]

GO

ALTER TABLE [dbo].[item]
ADD
CONSTRAINT [fk_item_product] FOREIGN KEY
(
[productid]
) REFERENCES [dbo].[product] (
[productid]
)

GO

ALTER TABLE [dbo].[item]
ADD
CONSTRAINT [fk_item_supplier] FOREIGN KEY
(
[supplier]
) REFERENCES [dbo].[supplier] (
[suppid]
)

GO

[katentim]

Cool. You may want to submit this via JIRA