 |
Tech
GURU
Joined: 18 Oct 2000
Posts: 2733
Location: Atlanta, USA
|
 Posted: Fri Jun 25, 2010 3:30 am
[3.20] What's the best way to put this SQL in a String List
|
I have SQL code I'm trying to store in a string list for a package I'm developing. With the introduction of JSON structures and other nuances... I'm not sure the best way to capture it.
Here are a few sample statements
| Code: |
CREATE TRIGGER fkd_p_o_ownerid BEFORE DELETE ON owner FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'delete on table "owner" violates foreign key constraint "fkd_p_o_ownerid"') WHERE (SELECT owner_id FROM Portfolio WHERE owner_id = OLD.owner_id) IS NOT NULL; END;
DROP TRIGGER fki_ph_s_stockid;
CREATE TRIGGER fki_ph_s_stockid BEFORE INSERT ON [PriceHistory] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'insert on table "PriceHistory" violates foreign key constraint "fki_ph_s_stockid"') WHERE (SELECT stock_id FROM Stock WHERE stock_id = NEW.stock_id) IS NULL; END;
DROP TRIGGER fku_ph_s_stockid;
CREATE TRIGGER fku_ph_s_stockid BEFORE UPDATE ON [PriceHistory] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'update on table "PriceHistory" violates foreign key constraint "fku_ph_s_stockid"') WHERE (SELECT stock_id FROM Stock WHERE stock_id = NEW.stock_id) IS NULL; END;
DROP TRIGGER fkd_ph_s_stockid; |
The equals signs sometimes causes it to be interpreted as a DB record and the [ ] sometimes causes it to be treated as JSON record. Here's a result of one my xml copies.
| Code: |
<?xml version="1.0" encoding="ISO-8859-1" ?>
<cmud>
<var name="testh" type="StringList" copy="yes">
<value>CREATE TABLE Stock (stock_id INTEGER UNIQUE PRIMARY KEY, name VARCHAR(64) UNIQUE NOT NULL, description VARCHAR(255), type VARCHAR(16) NOT NULL, founded DATE, bankrupt DATE); |CREATE TABLE Owner ( owner_id INTEGER UNIQUE PRIMARY KEY, name VARCHAR(64) NOT NULL, description VARCHAR(255), mud VARCHAR(64) NOT NULL);|CREATE TABLE Portfolio ( portfolio_id INTEGER UNIQUE PRIMARY KEY, owner_id INTEGER NOT NULL CONSTRAINT fk_p_o_ownerid REFERENCES owner(owner_id), name VARCHAR(32), description VARCHAR(255));|CREATE TABLE PriceHistory ( price_id INTEGER UNIQUE PRIMARY KEY, stock_id INTEGER NOT NULL CONSTRAINT fk_ph_s_stockid REFERENCES Stock(stock_id), date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, price FLOAT(12) NOT NULL);|CREATE TABLE PortfolioStock ( port_stock_id INTEGER UNIQUE PRIMARY KEY, portfolio_id INTEGER NOT NULL CONSTRAINT fk_ps_p_portfolioid REFERENCES Portfolio(portfolio_id), stock_id INTEGER NOT NULL CONSTRAINT fk_ps_s_stockid REFERENCES Stock(stock_id), amount_held FLOAT(12) NOT NULL, description VARCHAR(255) );|CREATE TABLE PurchaseHistory ( purchase_id INTEGER UNIQUE PRIMARY KEY, amount INTEGER NOT NULL, type CHAR(4) NOT NULL, price_id INTEGER NOT NULL CONSTRAINT fk_pu_ph_priceid REFERENCES PriceHistory(price_id), port_stock_id INTEGER NOT NULL CONSTRAINT fk_pu_ps_portstockid REFERENCES PortfolioStock(port_stock_id));|DROP TRIGGER fki_p_o_ownerid; |CREATE TRIGGER fki_p_o_ownerid BEFORE INSERT ON [Portfolio] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'insert on table "Portfolio" violates foreign key constraint "fki_p_o_ownerid"') WHERE (SELECT owner_id FROM owner WHERE owner_id = NEW.owner_id) IS NULL; END;|DROP TRIGGER fku_p_o_ownerid; |CREATE TRIGGER fku_p_o_ownerid BEFORE UPDATE ON [Portfolio] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'update on table "Portfolio" violates foreign key constraint "fku_p_o_ownerid"') WHERE (SELECT owner_id FROM owner WHERE owner_id = NEW.owner_id) IS NULL; END;|DROP TRIGGER fkd_p_o_ownerid; |CREATE TRIGGER fkd_p_o_ownerid BEFORE DELETE ON owner FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'delete on table "owner" violates foreign key constraint "fkd_p_o_ownerid"') WHERE (SELECT owner_id FROM Portfolio WHERE owner_id =" OLD.owner_id) IS NOT NULL; END;
CREATE TRIGGER fkd_p_o_ownerid BEFORE DELETE ON owner FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'delete on table ""owner"" violates foreign key constraint ""fkd_p_o_ownerid""') WHERE (SELECT owner_id FROM Portfolio WHERE owner_id = OLD.owner_id) IS NOT NULL; END;
CREATE TRIGGER fkd_p_o_ownerid BEFORE DELETE ON owner FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'delete on table ""owner"" violates foreign key constraint ""fkd_p_o_ownerid""') WHERE (SELECT owner_id FROM Portfolio WHERE owner_id = OLD.owner_id) IS NOT NULL; END;"|DROP TRIGGER fki_ph_s_stockid; |CREATE TRIGGER fki_ph_s_stockid BEFORE INSERT ON [PriceHistory] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'insert on table "PriceHistory" violates foreign key constraint "fki_ph_s_stockid"') WHERE (SELECT stock_id FROM Stock WHERE stock_id = NEW.stock_id) IS NULL; END;|DROP TRIGGER fku_ph_s_stockid; |CREATE TRIGGER fku_ph_s_stockid BEFORE UPDATE ON [PriceHistory] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'update on table "PriceHistory" violates foreign key constraint "fku_ph_s_stockid"') WHERE (SELECT stock_id FROM Stock WHERE stock_id = NEW.stock_id) IS NULL; END;|DROP TRIGGER fkd_ph_s_stockid; |CREATE TRIGGER fkd_ph_s_stockid BEFORE DELETE ON Stock FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'delete on table "Stock" violates foreign key constraint "fkd_ph_s_stockid"') WHERE (SELECT stock_id FROM PriceHistory WHERE stock_id = OLD.stock_id) IS NOT NULL; END;|DROP TRIGGER fki_ps_p_portfolioid; |CREATE TRIGGER fki_ps_p_portfolioid BEFORE INSERT ON [PortfolioStock] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'insert on table "PortfolioStock" violates foreign key constraint "fki_ps_p_portfolioid"') WHERE (SELECT portfolio_id FROM Portfolio WHERE portfolio_id = NEW.portfolio_id) IS NULL; END;|DROP TRIGGER fku_ps_p_portfolioid; |CREATE TRIGGER fku_ps_p_portfolioid BEFORE UPDATE ON [PortfolioStock] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'update on table "PortfolioStock" violates foreign key constraint "fku_ps_p_portfolioid"') WHERE (SELECT portfolio_id FROM Portfolio WHERE portfolio_id = NEW.portfolio_id) IS NULL; END;|DROP TRIGGER fkd_ps_p_portfolioid; |CREATE TRIGGER fkd_ps_p_portfolioid BEFORE DELETE ON Portfolio FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'delete on table "Portfolio" violates foreign key constraint "fkd_ps_p_portfolioid"') WHERE (SELECT portfolio_id FROM PortfolioStock WHERE portfolio_id = OLD.portfolio_id) IS NOT NULL; END;|DROP TRIGGER fki_ps_s_stockid; |CREATE TRIGGER fki_ps_s_stockid BEFORE INSERT ON [PortfolioStock] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'insert on table "PortfolioStock" violates foreign key constraint "fki_ps_s_stockid"') WHERE (SELECT stock_id FROM Stock WHERE stock_id = NEW.stock_id) IS NULL; END;|DROP TRIGGER fku_ps_s_stockid; |CREATE TRIGGER fku_ps_s_stockid BEFORE UPDATE ON [PortfolioStock] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'update on table "PortfolioStock" violates foreign key constraint "fku_ps_s_stockid"') WHERE (SELECT stock_id FROM Stock WHERE stock_id = NEW.stock_id) IS NULL; END;|DROP TRIGGER fkd_ps_s_stockid; |CREATE TRIGGER fkd_ps_s_stockid BEFORE DELETE ON Stock FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'delete on table "Stock" violates foreign key constraint "fkd_ps_s_stockid"') WHERE (SELECT stock_id FROM PortfolioStock WHERE stock_id = OLD.stock_id) IS NOT NULL; END;|DROP TRIGGER fki_pu_ph_priceid; |CREATE TRIGGER fki_pu_ph_priceid BEFORE INSERT ON [PurchaseHistory] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'insert on table "PurchaseHistory" violates foreign key constraint "fki_pu_ph_priceid"') WHERE (SELECT price_id FROM PriceHistory WHERE price_id = NEW.price_id) IS NULL; END;|DROP TRIGGER fku_pu_ph_priceid; |CREATE TRIGGER fku_pu_ph_priceid BEFORE UPDATE ON [PurchaseHistory] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'update on table "PurchaseHistory" violates foreign key constraint "fku_pu_ph_priceid"') WHERE (SELECT price_id FROM PriceHistory WHERE price_id = NEW.price_id) IS NULL; END;|DROP TRIGGER fkd_pu_ph_priceid; |CREATE TRIGGER fkd_pu_ph_priceid BEFORE DELETE ON PriceHistory FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'delete on table "PriceHistory" violates foreign key constraint "fkd_pu_ph_priceid"') WHERE (SELECT price_id FROM PurchaseHistory WHERE price_id = OLD.price_id) IS NOT NULL; END;|DROP TRIGGER fki_pu_ps_portstockid; |CREATE TRIGGER fki_pu_ps_portstockid BEFORE INSERT ON [PurchaseHistory] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'insert on table "PurchaseHistory" violates foreign key constraint "fki_pu_ps_portstockid"') WHERE (SELECT port_stock_id FROM PortfolioStock WHERE port_stock_id = NEW.port_stock_id) IS NULL; END;|DROP TRIGGER fku_pu_ps_portstockid; |CREATE TRIGGER fku_pu_ps_portstockid BEFORE UPDATE ON [PurchaseHistory] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'update on table "PurchaseHistory" violates foreign key constraint "fku_pu_ps_portstockid"') WHERE (SELECT port_stock_id FROM PortfolioStock WHERE port_stock_id = NEW.port_stock_id) IS NULL; END;|DROP TRIGGER fkd_pu_ps_portstockid; |CREATE TRIGGER fkd_pu_ps_portstockid BEFORE DELETE ON PortfolioStock FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'delete on table "PortfolioStock" violates foreign key constraint "fkd_pu_ps_portstockid"') WHERE (SELECT port_stock_id FROM PurchaseHistory WHERE port_stock_id = OLD.port_stock_id) IS NOT NULL; END;|CREATE view BuyHistoryForHeldStock as SELECT ps.port_stock_id port_stock_id, ps.stock_id port_stock_id, [date], amount_held, amount, price FROM PurchaseHistory pu1, PriceHistory ph, portfolioStock ps WHERE pu1.port_stock_id =" ps.port_stock_id AND pu1.price_id = ph.price_id AND pu1.type = "" BUY "" AND ps.stock_id = ph.stock_id AND pu1.purchase_id in (SELECT pu2.purchase_id FROM PurchaseHistory pu2, PriceHistory ph2 WHERE ph2.price_id = pu2.price_id AND (SELECT SUM(amount) FROM PurchaseHistory pu3, PriceHistory ph3 WHERE ph3.date between ph2.date AND current_timestamp AND ph3.price_id = pu3.price_id AND pu3.port_stock_id = ps.port_stock_id ) <= (ps.amount_held + pu2.amount) ) ORDER BY date DESC; "</value>
<json>["CREATE TABLE Stock (stock_id INTEGER UNIQUE PRIMARY KEY, name VARCHAR(64) UNIQUE NOT NULL, description VARCHAR(255), type VARCHAR(16) NOT NULL, founded DATE, bankrupt DATE); ","CREATE TABLE Owner ( owner_id INTEGER UNIQUE PRIMARY KEY, name VARCHAR(64) NOT NULL, description VARCHAR(255), mud VARCHAR(64) NOT NULL);","CREATE TABLE Portfolio ( portfolio_id INTEGER UNIQUE PRIMARY KEY, owner_id INTEGER NOT NULL CONSTRAINT fk_p_o_ownerid REFERENCES owner(owner_id), name VARCHAR(32), description VARCHAR(255));","CREATE TABLE PriceHistory ( price_id INTEGER UNIQUE PRIMARY KEY, stock_id INTEGER NOT NULL CONSTRAINT fk_ph_s_stockid REFERENCES Stock(stock_id), date TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, price FLOAT(12) NOT NULL);","CREATE TABLE PortfolioStock ( port_stock_id INTEGER UNIQUE PRIMARY KEY, portfolio_id INTEGER NOT NULL CONSTRAINT fk_ps_p_portfolioid REFERENCES Portfolio(portfolio_id), stock_id INTEGER NOT NULL CONSTRAINT fk_ps_s_stockid REFERENCES Stock(stock_id), amount_held FLOAT(12) NOT NULL, description VARCHAR(255) );","CREATE TABLE PurchaseHistory ( purchase_id INTEGER UNIQUE PRIMARY KEY, amount INTEGER NOT NULL, type CHAR(4) NOT NULL, price_id INTEGER NOT NULL CONSTRAINT fk_pu_ph_priceid REFERENCES PriceHistory(price_id), port_stock_id INTEGER NOT NULL CONSTRAINT fk_pu_ps_portstockid REFERENCES PortfolioStock(port_stock_id));","DROP TRIGGER fki_p_o_ownerid; ",{"CREATE TRIGGER fki_p_o_ownerid BEFORE INSERT ON [Portfolio] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'insert on table \"Portfolio\" violates foreign key constraint \"fki_p_o_ownerid\"') WHERE (SELECT owner_id FROM owner WHERE owner_id ":" NEW.owner_id) IS NULL; END;"},"DROP TRIGGER fku_p_o_ownerid; ",{"CREATE TRIGGER fku_p_o_ownerid BEFORE UPDATE ON [Portfolio] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'update on table \"Portfolio\" violates foreign key constraint \"fku_p_o_ownerid\"') WHERE (SELECT owner_id FROM owner WHERE owner_id ":" NEW.owner_id) IS NULL; END;"},"DROP TRIGGER fkd_p_o_ownerid; ",{"CREATE TRIGGER fkd_p_o_ownerid BEFORE DELETE ON owner FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'delete on table \"owner\" violates foreign key constraint \"fkd_p_o_ownerid\"') WHERE (SELECT owner_id FROM Portfolio WHERE owner_id ":" OLD.owner_id) IS NOT NULL; END;
CREATE TRIGGER fkd_p_o_ownerid BEFORE DELETE ON owner FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'delete on table \"owner\" violates foreign key constraint \"fkd_p_o_ownerid\"') WHERE (SELECT owner_id FROM Portfolio WHERE owner_id = OLD.owner_id) IS NOT NULL; END;
CREATE TRIGGER fkd_p_o_ownerid BEFORE DELETE ON owner FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'delete on table \"owner\" violates foreign key constraint \"fkd_p_o_ownerid\"') WHERE (SELECT owner_id FROM Portfolio WHERE owner_id = OLD.owner_id) IS NOT NULL; END;"},"DROP TRIGGER fki_ph_s_stockid; ",{"CREATE TRIGGER fki_ph_s_stockid BEFORE INSERT ON [PriceHistory] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'insert on table \"PriceHistory\" violates foreign key constraint \"fki_ph_s_stockid\"') WHERE (SELECT stock_id FROM Stock WHERE stock_id ":" NEW.stock_id) IS NULL; END;"},"DROP TRIGGER fku_ph_s_stockid; ",{"CREATE TRIGGER fku_ph_s_stockid BEFORE UPDATE ON [PriceHistory] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'update on table \"PriceHistory\" violates foreign key constraint \"fku_ph_s_stockid\"') WHERE (SELECT stock_id FROM Stock WHERE stock_id ":" NEW.stock_id) IS NULL; END;"},"DROP TRIGGER fkd_ph_s_stockid; ",{"CREATE TRIGGER fkd_ph_s_stockid BEFORE DELETE ON Stock FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'delete on table \"Stock\" violates foreign key constraint \"fkd_ph_s_stockid\"') WHERE (SELECT stock_id FROM PriceHistory WHERE stock_id ":" OLD.stock_id) IS NOT NULL; END;"},"DROP TRIGGER fki_ps_p_portfolioid; ",{"CREATE TRIGGER fki_ps_p_portfolioid BEFORE INSERT ON [PortfolioStock] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'insert on table \"PortfolioStock\" violates foreign key constraint \"fki_ps_p_portfolioid\"') WHERE (SELECT portfolio_id FROM Portfolio WHERE portfolio_id ":" NEW.portfolio_id) IS NULL; END;"},"DROP TRIGGER fku_ps_p_portfolioid; ",{"CREATE TRIGGER fku_ps_p_portfolioid BEFORE UPDATE ON [PortfolioStock] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'update on table \"PortfolioStock\" violates foreign key constraint \"fku_ps_p_portfolioid\"') WHERE (SELECT portfolio_id FROM Portfolio WHERE portfolio_id ":" NEW.portfolio_id) IS NULL; END;"},"DROP TRIGGER fkd_ps_p_portfolioid; ",{"CREATE TRIGGER fkd_ps_p_portfolioid BEFORE DELETE ON Portfolio FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'delete on table \"Portfolio\" violates foreign key constraint \"fkd_ps_p_portfolioid\"') WHERE (SELECT portfolio_id FROM PortfolioStock WHERE portfolio_id ":" OLD.portfolio_id) IS NOT NULL; END;"},"DROP TRIGGER fki_ps_s_stockid; ",{"CREATE TRIGGER fki_ps_s_stockid BEFORE INSERT ON [PortfolioStock] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'insert on table \"PortfolioStock\" violates foreign key constraint \"fki_ps_s_stockid\"') WHERE (SELECT stock_id FROM Stock WHERE stock_id ":" NEW.stock_id) IS NULL; END;"},"DROP TRIGGER fku_ps_s_stockid; ",{"CREATE TRIGGER fku_ps_s_stockid BEFORE UPDATE ON [PortfolioStock] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'update on table \"PortfolioStock\" violates foreign key constraint \"fku_ps_s_stockid\"') WHERE (SELECT stock_id FROM Stock WHERE stock_id ":" NEW.stock_id) IS NULL; END;"},"DROP TRIGGER fkd_ps_s_stockid; ",{"CREATE TRIGGER fkd_ps_s_stockid BEFORE DELETE ON Stock FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'delete on table \"Stock\" violates foreign key constraint \"fkd_ps_s_stockid\"') WHERE (SELECT stock_id FROM PortfolioStock WHERE stock_id ":" OLD.stock_id) IS NOT NULL; END;"},"DROP TRIGGER fki_pu_ph_priceid; ",{"CREATE TRIGGER fki_pu_ph_priceid BEFORE INSERT ON [PurchaseHistory] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'insert on table \"PurchaseHistory\" violates foreign key constraint \"fki_pu_ph_priceid\"') WHERE (SELECT price_id FROM PriceHistory WHERE price_id ":" NEW.price_id) IS NULL; END;"},"DROP TRIGGER fku_pu_ph_priceid; ",{"CREATE TRIGGER fku_pu_ph_priceid BEFORE UPDATE ON [PurchaseHistory] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'update on table \"PurchaseHistory\" violates foreign key constraint \"fku_pu_ph_priceid\"') WHERE (SELECT price_id FROM PriceHistory WHERE price_id ":" NEW.price_id) IS NULL; END;"},"DROP TRIGGER fkd_pu_ph_priceid; ",{"CREATE TRIGGER fkd_pu_ph_priceid BEFORE DELETE ON PriceHistory FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'delete on table \"PriceHistory\" violates foreign key constraint \"fkd_pu_ph_priceid\"') WHERE (SELECT price_id FROM PurchaseHistory WHERE price_id ":" OLD.price_id) IS NOT NULL; END;"},"DROP TRIGGER fki_pu_ps_portstockid; ",{"CREATE TRIGGER fki_pu_ps_portstockid BEFORE INSERT ON [PurchaseHistory] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'insert on table \"PurchaseHistory\" violates foreign key constraint \"fki_pu_ps_portstockid\"') WHERE (SELECT port_stock_id FROM PortfolioStock WHERE port_stock_id ":" NEW.port_stock_id) IS NULL; END;"},"DROP TRIGGER fku_pu_ps_portstockid; ",{"CREATE TRIGGER fku_pu_ps_portstockid BEFORE UPDATE ON [PurchaseHistory] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'update on table \"PurchaseHistory\" violates foreign key constraint \"fku_pu_ps_portstockid\"') WHERE (SELECT port_stock_id FROM PortfolioStock WHERE port_stock_id ":" NEW.port_stock_id) IS NULL; END;"},"DROP TRIGGER fkd_pu_ps_portstockid; ",{"CREATE TRIGGER fkd_pu_ps_portstockid BEFORE DELETE ON PortfolioStock FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'delete on table \"PortfolioStock\" violates foreign key constraint \"fkd_pu_ps_portstockid\"') WHERE (SELECT port_stock_id FROM PurchaseHistory WHERE port_stock_id ":" OLD.port_stock_id) IS NOT NULL; END;"},{"CREATE view BuyHistoryForHeldStock as SELECT ps.port_stock_id port_stock_id, ps.stock_id port_stock_id, [date], amount_held, amount, price FROM PurchaseHistory pu1, PriceHistory ph, portfolioStock ps WHERE pu1.port_stock_id ":" ps.port_stock_id AND pu1.price_id = ph.price_id AND pu1.type = \" BUY \" AND ps.stock_id = ph.stock_id AND pu1.purchase_id in (SELECT pu2.purchase_id FROM PurchaseHistory pu2, PriceHistory ph2 WHERE ph2.price_id = pu2.price_id AND (SELECT SUM(amount) FROM PurchaseHistory pu3, PriceHistory ph3 WHERE ph3.date between ph2.date AND current_timestamp AND ph3.price_id = pu3.price_id AND pu3.port_stock_id = ps.port_stock_id ) <= (ps.amount_held + pu2.amount) ) ORDER BY date DESC; "}]</json>
</var>
</cmud> |
Notice some of it is picked up as JSON code. I tried doing it in the editor, but I also (vaguely) remember there were some hiccups using the editor to do string lists. |
|
_________________
Asati di tempari! |
 |
|
|
Zugg
MASTER
Joined: 25 Sep 2000
Posts: 23379
Location: Colorado, USA
|
Posted: Fri Jun 25, 2010 4:22 pm
|
The JSON syntax should never be impacting your variables. How was your variable shown above created?
The editor in 3.20 has trouble, but in 3.21 you should be able to enter each value into the editor and it should work properly. When adding items to a script, just use #ADDITEM and enclose the value in " quotes (and double-up any embedded quotes). For example:
#additem list "CREATE TRIGGER fki_ph_s_stockid BEFORE INSERT ON [PriceHistory] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'insert on table ""PriceHistory"" violates foreign key constraint ""fki_ph_s_stockid""') WHERE (SELECT stock_id FROM Stock WHERE stock_id = NEW.stock_id) IS NULL; END; "
If you want to use the %json function to create the list, then you should use *strict* json syntax where embedded quotes are escaped with \ and each key, value, or other string value is enclosed in quotes. |
|
|
|
|
Tech
GURU
Joined: 18 Oct 2000
Posts: 2733
Location: Atlanta, USA
|
Posted: Fri Jun 25, 2010 6:38 pm
|
Thanks Zugg.... since this is a one time thing, I'll just wait for 3.21. Thanks.
|
|
_________________
Asati di tempari! |
|
|
|
Zugg
MASTER
Joined: 25 Sep 2000
Posts: 23379
Location: Colorado, USA
|
Posted: Fri Jun 25, 2010 6:44 pm
|
Btw, I've also added an option to the editor in 3.21 called "Nested" that you can disable to prevent CMUD from parsing the contents of your string values. When Nested is off, each item in the string list or database variable is edited as a plain string and does not cause any nested lists or tables to be created. It works like adding quotes around the item with #ADDITEM but doesn't require you to mess around with the quotes.
I'm also planning a new JSON tab (like the XML tab) to edit the JSON code directly, but not sure if that will make 3.21 or not. I'm under some time pressure today because I'm gone at a Drupal conference in Denver this weekend. |
|
|
|
|
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
