Page 2 of 2
Re: My HMG Apps
Posted: Sat May 29, 2010 4:56 am
by sudip
Thanks a lot Rathi, FOREIGN KEY constraint works!!!
Only a small tip for users like me. (taken from SQLite documentation)
Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection separately. (Note, however, that future releases of SQLite might change so that foreign key constraints enabled by default. Careful developers will not make any assumptions about whether or not foreign keys are enabled by default but will instead enable or disable them as necessary.) The application can can also use a PRAGMA foreign_keys statement to determine if foreign keys are currently enabled.
I use following code just after connecting SQLite database:
Code: Select all
miscsql(mdb, "PRAGMA foreign_keys = ON;")
Re: My HMG Apps
Posted: Sat May 29, 2010 5:22 am
by Rathinagiri
Yes. I had forgotten to tell you.
I had found that out only after a deep research!
Also, for immediate transaction to transaction stock update, we can use triggers. Whenever a transaction is affected (inserted/updated/deleted), the trigger will be fired and a series of sql queries can be run automatically to update the stocks. So cool. Isn't it?
Re: My HMG Apps
Posted: Sat May 29, 2010 5:49 am
by sudip
Rathi, Excellent!!!
Can you please send one small example with stock update trigger? Hope this can be helpful in Trial Balance also
Thanks in advance
Re: My HMG Apps
Posted: Sat May 29, 2010 6:43 am
by Rathinagiri
Yes. This can be used in various places like stock maintenance, trial balance, order processing etc.,
Here is a small sample. Create a database and run these queries. After this if you insert/update/delete any row in txn table, the stock in the item table is updated automatically.
Code: Select all
CREATE TABLE "item" ("itemcode" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "name" VARCHAR NOT NULL UNIQUE DEFAULT "", "stock" DOUBLE NOT NULL DEFAULT 0.0);
CREATE TABLE "txn" ("date" DATE NOT NULL DEFAULT '0000-00-00', "itemcode" INTEGER NOT NULL DEFAULT 0, "receipt" DOUBLE NOT NULL DEFAULT 0.0, "issued" DOUBLE NOT NULL DEFAULT 0.0, foreign key (itemcode) references item(itemcode));
CREATE TRIGGER "delete_txn" AFTER DELETE ON txn BEGIN update item set stock = stock + old.issued - old.receipt where itemcode = old.itemcode; END;
CREATE TRIGGER "insert_txn" AFTER INSERT ON txn BEGIN update item set stock = stock + new.receipt - new.issued where itemcode = new.itemcode; END;
CREATE TRIGGER "update_txn" AFTER UPDATE ON txn BEGIN update item set stock = stock + new.receipt - old.receipt - new.issued + old.issued where itemcode = old.itemcode and old.itemcode = new.itemcode;
update item set stock = stock - old.receipt + old.issued where itemcode = old.itemcode and not old.itemcode = new.itemcode;
update item set stock = stock + new.receipt - new.issued where itemcode = new.itemcode and not old.itemcode = new.itemcode;
END;
Now run these queries:
Code: Select all
insert into item values ("Pen",0)
insert into item values ("Pencil",0)
insert into item values ("Rubber",0)
insert into txn values ('2010-05-29',1,10,0)
insert into txn values ('2010-05-29',2,10,0)
insert into txn values ('2010-05-29',3,10,0)
insert into txn values ('2010-05-29',1,0,5)
insert into txn values ('2010-05-29',2,,4)
update txn set itemcode = 2 where itemcode = 1
Re: My HMG Apps
Posted: Sat May 29, 2010 7:18 am
by sudip
Excellent, Rathi
Thanks a lot.
Re: My HMG Apps
Posted: Sat May 29, 2010 7:32 am
by Rathinagiri
Also, complex sql select statements (for report purposes) along with all the necessary foreign and primary key relations can be saved as a simple select statement using "create view"!
Then, this 'view' can be read whenever we wish to use the complex statement.
For example, consider this statement for the above mentioned database:
Code: Select all
CREATE VIEW "item_txn" AS select txn.date,item.name,txn.receipt,txn.issued from item,txn where txn.itemcode = item.itemcode order by txn.date
Now we can use...
With foreign keys, triggers and views we can effectively use SQLite and also reduce the coding.
Re: My HMG Apps
Posted: Sat May 29, 2010 10:03 am
by sudip
Thank you again, Rathi
Views will be great helpful
Can we start a special Topic/Forum for SQLite with HMG? What do you think?
I am not sure how many HMG users are using SQLite
Re: My HMG Apps
Posted: Thu Jun 10, 2010 5:32 am
by sudip
I upgraded my app with different suggestion from my client and Rathi
My client is now using the app. And most important thing is that I got the cheque
(too small amount, but something is better than nothing
)
Thank you all.