My HMG Apps

[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;")

[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?

[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

[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

[sudip]

Excellent, Rathi
Thanks a lot.

[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...

Code: Select all

select * from item_txn

With foreign keys, triggers and views we can effectively use SQLite and also reduce the coding.

[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

[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.