Announcement Announcement Module
Collapse
No announcement yet.
is there mssql server database scripts for jpetstore? Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • is there mssql server database scripts for jpetstore?

    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?

  • #2
    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.

    Comment


    • #3
      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

      Comment


      • #4
        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@spacesy[email protected]
        -- ************************************************** *****

        -- ************************************************** *****
        -- 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

        Comment


        • #5
          Cool. You may want to submit this via JIRA

          Comment

          Working...
          X