|
From: John M. <jo...@ea...> - 2002-03-06 20:47:20
|
First, I would like to thank anyone for trying to help me with this problem.
I am only guessing as to how to code this and need some clarification on how
using a linking table is accomplished.
I'm following the DemoInit.java example of how to register tables and have
the following:
MODEL SQL:
-- Table: stock
CREATE TABLE "stock" (
"stock_pkey" int4 NOT NULL,
"symbol" varchar(5) NOT NULL,
"name" varchar(50) NOT NULL,
"price" numeric(5, 3),
CONSTRAINT "stock_symbol_key" UNIQUE ("symbol"),
CONSTRAINT "stock_pkey" PRIMARY KEY ("stock_pkey")
);
-- Table: portfolio
CREATE TABLE "portfolio" (
"portfolio_pkey" int4 NOT NULL,
"name" varchar(50),
"investor_pkey" int4,
CONSTRAINT "portfolio_pkey" PRIMARY KEY ("portfolio_pkey"),
CONSTRAINT "FKinvestor" FOREIGN KEY ("investor_pkey") REFERENCES
"investor" ("investor_pkey") ON DELETE NO ACTION ON UPDATE NO ACTION NOT
DEFERRABLE INITIALLY IMMEDIATE
);
-- Table: portfolio_stock - The Linking Table
CREATE TABLE "portfolio_stock" (
"portfolio_stock_pkey" int4 NOT NULL,
"stock_pkey" int4 NOT NULL,
"portfolio_pkey" int4 NOT NULL,
CONSTRAINT "portfolio_stock_pkey" PRIMARY KEY ("portfolio_stock_pkey"),
CONSTRAINT "FKportfolio" FOREIGN KEY ("portfolio_pkey") REFERENCES
"portfolio" ("portfolio_pkey") ON DELETE NO ACTION ON UPDATE NO ACTION NOT
DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT "FKstock" FOREIGN KEY ("stock_pkey") REFERENCES "stock"
("stock_pkey") ON DELETE NO ACTION ON UPDATE NO ACTION NOT DEFERRABLE
INITIALLY IMMEDIATE
);
TABLE REGISTRATIONS
// Register the stock table, and all it's queries
simper.registerTable("stock", "stock_pkey", null, new
PKGenIDTable("next_id_table"));
simper.registerQuery("stock", "all", null, "name", true);
// Register the portfolio table, and all it's queries
simper.registerTable("portfolio", "portfolio_pkey", null, new
PKGenIDTable("next_id_table"));
simper.registerQuery("portfolio", "all", null, "name", true);
Here is where it gets tricky, I'm not sure how to code this
// Register the linking table portfolio_stock table, and all it's queries
simper.registerTable("portfolio_stock", "portfolio_stock_pkey", null, new
PKGenIDTable("next_id_table"));
simper.registerQuery("portfolio_stock", "by_portfolio_pkey",
"portfolio_pkey=?", "portfolio_pkey", true);
simper.registerQuery("portfolio_stock", "by_stock_pkey", "stock_pkey=?",
"stock_pkey", true);
Query syntax for a relation is:
// simper.registerRelation( source-table-name, source-relation-name,
source-key, destination-table, destionation-table-query-name)
From what I understand:
source-table-name = in a linking relation this would be one side of a many
to many
source-relation-name = this is used in the jsp to specify the fk
source-key = the primary key of one side of the many to many
destination-table = the linking table in a many to many relationship
destination-table-query-name = links source pk to query based on source pk
for the linking table?
// the Relations for the linking table
simper.registerRelation("stock", "stock_fk", "stock_pkey",
"portfolio_stock", "by_stock_pkey");
simper.registerRelation("portfolio", "portfolio_fk", "portfolio_pkey",
"portfolio_stock", "by_portfolio_pkey");
Is this correct syntax?
In the example jsp a foreign key is used like this:
<bean:write name="oneBook" property="author.name"/>
Going through a linking table what would the property syntax be?
My foreign key relations are stock_fk and portfolio_fk
So I'm guessing
property = "stock_fk.portfolio_fk.name" to retrieve the name in the
portfolio table
and
property = "portfolio_fk.stock_fk.name" to retrieve the name in the stock
table
Any help would be greatly appreciated.
-john
|