[Simper-Users] registering a linking table and relations

[John M. <jo...@ea...>]

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