Showing posts with label third year. Show all posts
Introduction to Software Architecture
Quality Attributes
Availability
Modifiability
Performance
Security
Usability
Testability
This is an ios application which demonstrates accessing data tier for common data manipulation tasks. Today we are going to build a simple book list application.
This is the application interface.
First add sqlite3 framework to your project.
BookListViewController.h
BookListViewController.h
// // BookListViewController.h // BookList // // Created by Snow Leopard User on 19/04/2012. // Copyright 2012 __MyCompanyName__. All rights reserved. // #importBookListViewController.m#import "/usr/include/sqlite3.h" @interface BookListViewController : UIViewController { UITextField *txtBid; UITextField *txtBname; UITextField *txtBauthor; UIButton *btnSave; UIButton *btnFind; UILabel *status; NSString *databasepath; sqlite3 *booksDB; } @property (nonatomic,retain) IBOutlet UITextField *txtBid; @property (nonatomic,retain) IBOutlet UITextField *txtBname; @property (nonatomic,retain) IBOutlet UITextField *txtBauthor; @property (nonatomic,retain) IBOutlet UIButton *btnSave; @property (nonatomic,retain) IBOutlet UIButton *btnFind; @property (nonatomic,retain) IBOutlet UILabel *status; -(IBAction) Save; -(IBAction) Find; @end
// // BookListViewController.m // BookList // // Created by Snow Leopard User on 19/04/2012. // Copyright 2012 __MyCompanyName__. All rights reserved. // #import "BookListViewController.h" @implementation BookListViewController @synthesize txtBauthor,txtBid,txtBname,btnFind,btnSave,status; -(IBAction) Save { sqlite3_stmt *statement; const char *dbpath=[databasepath UTF8String]; if(sqlite3_open(dbpath, &booksDB)==SQLITE_OK) { NSString *insertSQL=[NSString stringWithFormat:@"INSERT INTO BOOKS(BookID,BookName,Author) VALUES(\"%@\",\"%@\",\"%@\")",txtBid.text,txtBname.text,txtBauthor.text]; const char *insert_stmt=[insertSQL UTF8String]; sqlite3_prepare_v2(booksDB, insert_stmt, -1, &statement, NULL); if(sqlite3_step(statement)==SQLITE_DONE) { status.text=@"Book Added"; txtBid.text=@""; txtBauthor.text=@""; txtBname.text=@""; }else { status.text=@"Failed to add Book"; } sqlite3_finalize(statement); sqlite3_close(booksDB); } } -(IBAction) Find { const char *dbpath=[databasepath UTF8String]; sqlite3_stmt *statement; if(sqlite3_open(dbpath, &booksDB)==SQLITE_OK) { NSString *querySQL=[NSString stringWithFormat:@"SELECT BookID,BookName FROM books WHERE BookID=\"%@\"",txtBid.text]; const char *query_stmt=[querySQL UTF8String]; if(sqlite3_prepare_v2(booksDB, query_stmt, -1, &statement, NULL)==SQLITE_OK) { if(sqlite3_step(statement)==SQLITE_ROW) { NSString *addressField=[[NSString alloc] initWithUTF8String:(const char*)sqlite3_column_text(statement, 0)]; txtBname.text=addressField; NSString *phoneField=[[NSString alloc] initWithUTF8String:(const char*)sqlite3_column_text(statement, 1)]; txtBname.text=phoneField; status.text=@"Match Found"; txtBname.text=@""; txtBauthor.text=@""; } sqlite3_finalize(statement); } sqlite3_close(booksDB); } } - (void)dealloc { [txtBid release]; [txtBname release]; [txtBauthor release]; [super dealloc]; } - (void)didReceiveMemoryWarning { // Releases the view if it doesn't have a superview. [super didReceiveMemoryWarning]; // Release any cached data, images, etc that aren't in use. } #pragma mark - View lifecycle // Implement viewDidLoad to do additional setup after loading the view, typically from a nib. - (void)viewDidLoad { NSString *docsDir; NSArray *dirPaths; dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES); docsDir =[dirPaths objectAtIndex:0]; databasepath = [[NSString alloc]initWithString:[docsDir stringByAppendingPathComponent:@"books.db"]]; NSFileManager *filemgr=[NSFileManager defaultManager]; if([filemgr fileExistsAtPath:databasepath]==NO) { const char *dbpath=[databasepath UTF8String]; if(sqlite3_open(dbpath, &booksDB)==SQLITE_OK) { char *errMsg; const char *sql_stmt="CREATE TABLE IF NOT EXISTS BOOKS(ID INTEGER PRIMARY KEY AUTOINCREMENT,BookID TEXT,BookName TEXT,Author TEXT)"; if(sqlite3_exec(booksDB, sql_stmt, NULL, NULL, &errMsg)!=SQLITE_OK) { status.text=@"Failed to create table"; } sqlite3_close(booksDB); }else { status.text=@"Failed to Open/Create Database"; } } [filemgr release]; [super viewDidLoad]; } - (void)viewDidUnload { [super viewDidUnload]; self.txtBid=NULL; self.txtBname=NULL; self.txtBauthor=NULL; self.status=NULL; // Release any retained subviews of the main view. // e.g. self.myOutlet = nil; } - (BOOL)shouldAutorotateToInterfaceOrientation:(UIInterfaceOrientation)interfaceOrientation { // Return YES for supported orientations return (interfaceOrientation == UIInterfaceOrientationPortrait); } @end
--1 --a) ALTER TYPE stock3_t ADD MEMBER FUNCTION getYield RETURN FLOAT CASCADE; --B) ALTER TYPE stock3_t ADD MEMBER FUNCTION toUSD(rate IN FLOAT) RETURN FLOAT CASCADE; --c) ALTER TYPE stock3_t ADD MEMBER FUNCTION countExchanges RETURN INTEGER CASCADE; --d) ALTER TYPE client3_t ADD MEMBER FUNCTION totalPurchase RETURN FLOAT CASCADE; --e) ALTER TYPE client3_t ADD MEMBER FUNCTION totalProfit RETURN FLOAT CASCADE; CREATE OR REPLACE TYPE BODY stock3_t AS MEMBER FUNCTION getYield RETURN FLOAT IS BEGIN RETURN ( (SELF.dividend / SELF.currprice)*100 ); END getYield; MEMBER FUNCTION toUSD(rate IN FLOAT) RETURN FLOAT IS BEGIN RETURN (SELF.currprice * rate); END toUSD; MEMBER FUNCTION countExchanges RETURN INTEGER IS countEx INTEGER; BEGIN SELECT COUNT(e.COLUMN_VALUE) INTO countEx FROM TABLE(SELF.exchangeS)e ; RETURN countEx; END countExchanges; END; / CREATE OR REPLACE TYPE BODY client3_t AS MEMBER FUNCTION totalPurchase RETURN FLOAT IS total FLOAT; BEGIN SELECT SUM(s.purchaseprice*s.qty) INTO total FROM TABLE(SELF.investments)s; RETURN total; END totalPurchase; MEMBER FUNCTION totalProfit RETURN FLOAT IS profit FLOAT; BEGIN SELECT SUM(s.qty *(s.company.currprice - s.purchaseprice) ) INTO profit FROM TABLE (SELF.investments)s; RETURN profit; END totalProfit; END; / --2) --a) SELECT s.company AS CNAME,s.exchangeS AS EXCHANGES ,s.toUSD(0.74) AS USDPRICE ,s.getYield() AS YIELD FROM stock3 s; --b) SELECT s.company AS CNAME ,s.currprice AS CPRICE ,s.countExchanges() AS CNT_eXCHANGES FROM stock3 s WHERE s.countExchanges() > 1 ; --c) SELECT DISTINCT c.name AS NAME ,I.company.company AS STK_NAME,I.company.getYield() AS YIELD,I.company.currprice AS CPRICE,I.company.eps AS EPS FROM client3 c, TABLE(c.investments)I ; --d) SELECT DISTINCT c.name AS NAME ,c.totalPurchase() AS PVALUE FROM client3 c; --e) SELECT DISTINCT c.name AS NAME ,c.totalProfit() AS PROFIT FROM client3 c;
CREATE TYPE manufacture_ty as OBJECT( name varchar(20), country varchar(20) ) / CREATE TYPE product_ty AS OBJECT( modelno number, type varchar(10), price number(8,2), name ref manufacture_ty )NOT FINAL; / CREATE TYPE computer_ty UNDER product_ty ( speed number, ram number, hdisksize number, rdspeed varchar2(3), rdtype varchar2(3) )NOT FINAL; / CREATE TYPE printer_ty UNDER product_ty( PRType varchar2(8), ifcolour varchar2(5) ) / CREATE TYPE pc_ty UNDER computer_ty( monitorsize number(2), casetype varchar2(5) ) / CREATE TYPE laptop_ty UNDER computer_ty( screen number(8,2), weight number(8,2) ) / CREATE TABLE manufacture_tab of manufacture_ty( constraint pk_manufacture_tabl primary key(name) ) / CREATe TABLE product_tab of product_ty ( constraint pk_product_tab primary key (modelno), constraint fk_procut_tab foreign key(name) references manufacture_tab ) / insert into manufacture_tab values('ACER','SINGAPORE'); insert into manufacture_tab values('COMPAQ','USA'); insert into manufacture_tab values('IBM','USA'); insert into manufacture_tab values('TOSHIBA','JAPAN'); insert into manufacture_tab values('HP','USA'); insert into manufacture_tab values('CANON','JAPAN'); insert into product_tab values( pc_ty(1001,'PC',1799,(select ref(b) from manufacture_tab b where b.name='ACER'),2700,256,40,'48x','CD',15,'tower')) / insert into product_tab values( pc_ty(1002,'PC',2499,(select ref(b) from manufacture_tab b where b.name='ACER'),3500,512,60,'64x','DVD',17,'tower')) / insert into product_tab values( pc_ty(1003,'PC',1999,(select ref(b) from manufacture_tab b where b.name='ACER'),1800,512,30,'48x','DVD',15,'flat')) / insert into product_tab values( pc_ty(1004,'PC',1999,(select ref(b) from manufacture_tab b where b.name='IBM'),1800,256,40,'64x','DVD',15,'flat')) / insert into product_tab values( pc_ty(1005,'PC',2499,(select ref(b) from manufacture_tab b where b.name='IBM'),4000,512,60,'64x','DVD',15,'flat')) / insert into product_tab values( pc_ty(1006,'PC',2119,(select ref(b) from manufacture_tab b where b.name='IBM'),2300,1024,40,'96x','DVD',17,'tower')) / insert into product_tab values( pc_ty(1007,'PC',2299,(select ref(b) from manufacture_tab b where b.name='COMPAQ'),2400,512,80,'64x','DVD',17,'tower')) / insert into product_tab valueS( pc_ty(1008,'PC',1999,(select ref(b) from manufacture_tab b where b.name='COMPAQ'),2700,256,30,'96x','CD',15,'tower')) / insert into product_tab values( pc_ty(1009,'PC',1699,(select ref(b) from manufacture_tab b where b.name='TOSHIBA'),3200,512,80,'64x','DVD',17,'tower')) / --------------- insert into product_tab values( laptop_ty(2001,'LP',1448,(select ref(b) from manufacture_tab b where b.name='ACER'),1700,256,40,'48x','CD',12.1,3.5)) / ----------- insert into product_tab values( laptop_ty(2002,'LP',2559,(select ref(b) from manufacture_tab b where b.name='ACER'),2200,512,30,'64x','DVD',15.1,3.1)) / insert into product_tab values( laptop_ty(2003,'LP',2799,(select ref(b) from manufacture_tab b where b.name='ACER'),1866,512,60,'48x','DVD',15.1,2.8)) / insert into product_tab values( laptop_ty(2004,'LP',1999,(select ref(b) from manufacture_tab b where b.name='IBM'),1866,256,40,'64x','DVD',12.1,3.5)) / insert into product_tab values( laptop_ty(2005,'LP',1499,(select ref(b) from manufacture_tab b where b.name='IBM'),1700,256,60,'64x','DVD',12.1,3.1)) / insert into product_tab values( laptop_ty(2006,'LP',2119,(select ref(b) from manufacture_tab b where b.name='IBM'),3700,1024,80,'64x','DVD',15.7,3.1)) / insert into product_tab values( laptop_ty(2007,'LP',2229,(select ref(b) from manufacture_tab b where b.name='COMPAQ'),2900,512,80,'48x','DVD',15.1,3.5)) / insert into product_tab values( laptop_ty(2008,'LP',999,(select ref(b) from manufacture_tab b where b.name='COMPAQ'),1700,256,30,'96x','DVD',12.1,3.6)) / insert into product_tab values( laptop_ty(2009,'LP',699,(select ref(b) from manufacture_tab b where b.name='TOSHIBA'),1800,128,40,'64x','DVD',15.1,2.9)) / insert into product_tab values( laptop_ty(2010,'LP',1699,(select ref(b) from manufacture_tab b where b.name='TOSHIBA'),2750,512,30,'96x','CD',12.1,3.0)) / ------------ insert into product_tab values( printer_ty(3001,'PR',231,(select ref(b) from manufacture_tab b where b.name='HP'),'ink-jet','True')) / ------------- insert into product_tab values( printer_ty(3002,'PR',267,(select ref(b) from manufacture_tab b where b.name='CANON'),'ink-jet','True')) / insert into product_tab values( printer_ty(3003,'PR',390,(select ref(b) from manufacture_tab b where b.name='HP'),'laser','False')) / insert into product_tab values( printer_ty(3004,'PR',439,(select ref(b) from manufacture_tab b where b.name='CANON'),'ink-jet','True')) / insert into product_tab values( printer_ty(3005,'PR',200,(select ref(b) from manufacture_tab b where b.name='CANON'),'bubble','True')) / insert into product_tab values( printer_ty(3006,'PR',1999,(select ref(b) from manufacture_tab b where b.name='IBM'),'laser','True')) / insert into product_tab values( printer_ty(3007,'PR',350,(select ref(b) from manufacture_tab b where b.name='HP'),'laser','False')) / --Q4. --a select p.name.name,treat (value(p) as pc_ty).speed from product_tab p where value(p) IS OF(pc_ty) and treat(value(p) as pc_ty).hdisksize>=60 / --b select p.modelno,p.price from product_tab p where p.name.name like 'C%' / --c select distinct p.name.name from product_tab p where value(p) is of (only pc_ty) / --OR --C- select distinct p.name.name from product_tab p where value(p) IS OF (pc_ty) and p.name.name NOT IN (select p1.name.name from product_tab p1 where value(p) IS OF(printer_ty)) --d select p.name.name from product_tab p where value(p) is of (pc_ty) group by p.name.name having count(p.modelno)>=3 / --e select p.name.name,avg(treat(value(p) as laptop_ty).screen) from product_tab p where value(p) IS OF (laptop_ty) group by p.name.name / --Q5-A) alter type product_ty add member function print return integer cascade / create or replace type body product_ty AS MEMBER function print return integer is begin return self.modelno; end print; end; / --b alter type computer_ty add overriding member function print return integer cascade / create or replace type body computer-Ty as overriding mbmerber function print return integer is begin return 'Model NO:'||self.modelno||'SPEED:'||self.speed||'RAM:'||self.ram||'HDSIZE'||self.hdiskskize end print; end; / select p.print() from product_tab p select p.print() from product_tab p where value(p) is of (pc_ty) select p.print() from product_tab p where value(p) IS OF (computer_ty)
create table client( clno char(3), name varchar(12), address varchar(30), constraint client_pk primary key(clno) ) / create table stock( company char(7), price number(6,2), divident number(4,2), eps number(4,2), constraint stock_pk primary key(company) ) / create table trading( company char(7), exchange varchar(12), constraint trading_pk primary key(company,exchange), constraint trading_fk foreign key(company) references stock(company) ) / create table purchase( clno char(3), company char(7), pdate date, qty number(6), price number(6,2), constraint purchase_pk primary key(clno,company,pdate), constraint purchase_fk1 foreign key(clno) references client(clno), constraint purchase_fk2 foreign key(company) references stock(company) ) / insert into client values('c01','John Smith','3 East Av Bentley WA|6102') / insert into client values('c02','Jill Brody','42 Bent St Perth WA 6001') / insert into stock values('BHP',10.50,1.50,3.20) / insert into stock values('IBM',70.00,4.25,10.00) / insert into stock values('INTEL',76.50,5.00,12.40) / insert into stock values('FORD',40.00,2.00,8.50) / insert into stock values('GM',60.00,2.50,9.20) / insert into stock values('INFOSYS',45.00,3.00,7.80) / insert into trading values('BHP','Sydney NY') / insert into trading values('IBM','NY,Lon,Tok') / insert into trading values('INTEL','NY,London') / insert into trading values('FORD','NY') / insert into trading values('GM','NY') / insert into trading values('INFOSYS','NY') / insert into purchase values('c01','BHP','2/oct/01',1000,12.00) / insert into purchase values('c01','BHP','08-Jun-01',2000,10.50) / insert into purchase values('c01','IBM','12-Feb-00',500,58.00) / insert into purchase values('c01','IBM','10-Apr-01',1200,65.00) / insert into purchase values('c01','INFOSYS','11-Aug-01',1000,64.00) / insert into purchase values('c02','INTEL','30-Jan-00',300,35.00) / insert into purchase values('c02','INTEL','30-Jan-01',400,54.00) / insert into purchase values('c02','INTEL','02-Oct-01',200,60.00) / insert into purchase values('c02','FORD','05-Oct-99',300,40.00) / insert into purchase values('c02','GM','12-Dec-00',500,55.50) / c) select c.name,p.company,sum(p.qty) total_qty, sum(p.qty * s.price) c from client c, purchase p, stock s, trading t where c.clno = p.clno and p.company = s.company and s.company = t.company and t.exchange = 'New York' group by p.company, c.name; d) select c.name, sum(p.qty * p.price) total_pprice from client c,purchase p where c.clno = p.clno griup by c.name e) select c.name, sum(p.qty *(s.price - p.price)) book_profit from client c, purchase p, stock s where c.clno = p.clno and p.company = s.company group by c.name;
create table client( CREATE TYPE exchanges_t AS VARRAY(5) OF VARCHAR2(12) / CREATE TYPE stock_t AS OBJECT( company char(7), price number(6,2), exchanges exchanges_t, dividend number(4,2), eps number(4,2) ) / CREATE TYPE address_t AS OBJECT( street_number CHAR(10), street_name CHAR(20), suburb VARCHAR2(20), state VARCHAR2(15), pin CHAR(15) ) / CREATE TYPE investment_t AS OBJECT( company REF stock_t, pprice NUMBER(6,2), pdate DATE, qty NUMBER(6) ) / CREATE TYPE investment_ntt AS TABLE OF investment_t / CREATE TYPE client_t AS OBJECT( name varchar2(12), address address_t, investments investment_ntt ) / CREATE TABLE stock_tab OF stock_t( company PRIMARY KEY ) / CREATE TABLE client_tab OF client_t (name primary key) NESTED TABLE investments STORE AS investments_nttab / ALTER TABLE investments_nttab ADD SCOPE FOR (company) IS stock_tab / INSERT INTO stock_tab VALUES (stock_t('BHP',10.5,exchanges_t('Sydney','New York'),1.5,3.2)) / INSERT INTO stock_tab VALUES (stock_t('IBM',70.00,exchanges_t('New York','London','Tokyo'),4.25,10.00)) / INSERT INTO stock_tab VALUES (stock_t('INTEL',76.5,exchanges_t('Sydney','New York'),5,12.4)) / INSERT INTO stock_tab VALUES (stock_t('FORD',40,exchanges_t('New York'),2,8.5)) / INSERT INTO stock_tab VALUES (stock_t('GM',60,exchanges_t('New York'),2.5,9.2)) / INSERT INTO stock_tab VALUES (stock_t('INFOSYS',45,exchanges_t('New York'),3,7.8)) / INSERT INTO client_tab VALUES (client_t('John Smith', address_t ('3','East Av','Bently','WA','6102'), investment_ntt( investment_t((SELECT REF(s) FROM Stock_tab s WHERE s.company='BHP'), 12.00,'02-OCT-2001',1000), investment_t((SELECT REF(s) FROM Stock_tab s WHERE s.company='BHP'), 10.50,'08-JUN-2002',2000), investment_t((SELECT REF(s) FROM Stock_tab s WHERE s.company='IBM'), 58.00,'12-FEB-2000',500), investment_t((SELECT REF(s) FROM Stock_tab s WHERE s.company='IBM'), 65.00,'10-APR-2001',1200), investment_t((SELECT REF(s) FROM Stock_tab s WHERE s.company='INFOSYS'), 64.00,'11-AUG-2001',1000) ) ) ) / INSERT INTO client_tab VALUES (client_t('Jill Brody', address_t ('42','Bent st','Perth','WA','6001'), investment_ntt( investment_t((SELECT REF(s) FROM Stock_tab s WHERE s.company='INTEL'), 35.00,'30-JAN-2000',300), investment_t((SELECT REF(s) FROM Stock_tab s WHERE s.company='INTEL'), 54.00,'30-JAN-2001',400), investment_t((SELECT REF(s) FROM Stock_tab s WHERE s.company='INTEL'), 60.00,'02-OCT-2001',200), investment_t((SELECT REF(s) FROM Stock_tab s WHERE s.company='FORD'), 40.00,'05-OCT-1999',300), investment_t((SELECT REF(s) FROM Stock_tab s WHERE s.company='GM'), 55.50,'12-DEC-2000',500) ) ) ) / --1 SELECT DISTINCT c.name, i.company.company, i.company.price, i.company.dividend,i.company.eps FROM client_tab c, TABLE(c.investments) i ORDER BY c.name / --2 SELECT c.name, i.company.company, SUM(i.qty) tot,AVG(i.company.price) Avgpprice FROM client_tab c, TABLE(c.investments) i GROUP BY c.name, i.company.company ORDER BY c.name / SELECT c.name, i.company.company, SUM(i.qty) tot,SUM(i.qty*i.pprice)/SUM(i.qty) FROM client_tab c, TABLE(c.investments) i GROUP BY c.name, i.company.company ORDER BY c.name / --3 SELECT c.name, SUM(i.qty), (SUM(i.qty)*i.company.price) FROM client_tab c, TABLE(SELECT investments FROM client_tab WHERE name=c.name) i, TABLE(SELECT exchanges FROM stock_tab WHERE company=i.company.company) e WHERE e.COLUMN_VALUE='New York' GROUP BY c.name,i.company.company,i.company.price / --4 SELECT c.name, (SUM(i.qty*i.pprice)) tot FROM client_tab c, TABLE(c.investments) i GROUP BY c.name / --5 SELECT c.name, SUM(i.company.price*i.qty)-SUM(i.qty*i.pprice) FROM client_tab c, TABLE(c.investments) i GROUP BY c.name /
Subscribe to:
Posts (Atom)