My HMG Apps

Discuss anything else that does not suite other forums.

Moderator: Rathinagiri

User avatar
sudip
Posts: 1454
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India

Re: My HMG Apps

Post by sudip »

Thanks a lot Rathi, FOREIGN KEY constraint works!!! :D

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;")
With best regards,
Sudip
User avatar
Rathinagiri
Posts: 5471
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Contact:

Re: My HMG Apps

Post 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?
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
User avatar
sudip
Posts: 1454
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India

Re: My HMG Apps

Post by sudip »

Rathi, Excellent!!! :D

Can you please send one small example with stock update trigger? Hope this can be helpful in Trial Balance also :)

Thanks in advance :)
With best regards,
Sudip
User avatar
Rathinagiri
Posts: 5471
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Contact:

Re: My HMG Apps

Post 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
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
User avatar
sudip
Posts: 1454
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India

Re: My HMG Apps

Post by sudip »

Excellent, Rathi :D
Thanks a lot.
With best regards,
Sudip
User avatar
Rathinagiri
Posts: 5471
Joined: Tue Jul 29, 2008 6:30 pm
DBs Used: MariaDB, SQLite, SQLCipher and MySQL
Location: Sivakasi, India
Contact:

Re: My HMG Apps

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

Code: Select all

select * from item_txn
With foreign keys, triggers and views we can effectively use SQLite and also reduce the coding.
East or West HMG is the Best.
South or North HMG is worth.
...the possibilities are endless.
User avatar
sudip
Posts: 1454
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India

Re: My HMG Apps

Post by sudip »

Thank you again, Rathi :D
Views will be great helpful :D

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 :roll:
With best regards,
Sudip
User avatar
sudip
Posts: 1454
Joined: Sat Mar 07, 2009 11:52 am
Location: Kolkata, WB, India

Re: My HMG Apps

Post 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 :lol: (too small amount, but something is better than nothing :))

Thank you all.
With best regards,
Sudip
Post Reply