Overview
Examples
Screenshots
Comparisons
Applications
Download
Documentation
Tutorials
Bazaar
Status & Roadmap
FAQ
Authors & License
Forums
Funding Ultimate++
Search on this site
Search in forums












SourceForge.net Logo
Home » U++ Library support » U++ SQL » Please help the SQL-challenged
Please help the SQL-challenged [message #9566] Thu, 17 May 2007 15:25 Go to next message
guido is currently offline  guido
Messages: 169
Registered: April 2006
Experienced Member
Hi,

I'm writing a sqlite-based recipe database.
Now I'm stuck a bit due to upp's total lack of documentation in this regard and this being my first contact with SQL.
I studied the sql samples, but none shows how to do what I need.

Please somebody have a look at the schema I came up with:

TABLE_(RECIPES)
	INT_ 	(ID) PRIMARY_KEY AUTO_INCREMENT
	STRING_ (TITLE, 1024)
	STRING_ (DESCRIPTION, 4096)
	STRING_ (SOURCE, 1024)
	STRING_ (PREPARATION, 30000)
	INT_	(RATING)
	DOUBLE_	(SERVINGS)
	INT_	(PREPTIME)
	INT_	(COOKTIME)	
	BLOB_	(PHOTO)
	BLOB_	(THUMB)
END_TABLE

TABLE_(INGREDIENTS)
	INT     (ID) PRIMARY_KEY AUTO_INCREMENT
	STRING_ (HEADER, 1024)
	INT_	(AMOUNT)
	INT_	(POSITION)
	INT     (INGREDIENT_ID) REFERENCES(INGREDIENT_DICT)
	INT	(UNIT_ID) REFERENCES(UNIT_DICT)
END_TABLE

TABLE_(CATEGORIES)
	INT     (ID) PRIMARY_KEY AUTO_INCREMENT
	INT	(CAT_ID) REFERENCES(CATEGORIES_DICT) UNIQUE
END_TABLE

TABLE_(CATEGORY_DICT)
	INT_	(CAT_ID) PRIMARY_KEY
	STRING_ (CATEGORY, 64) UNIQUE
END_TABLE

TABLE_(UNIT_DICT)
	INT_ 	(UNIT_ID) PRIMARY_KEY
	STRING_ (UNIT, 64) UNIQUE
END_TABLE

TABLE_(INGREDIENT_DICT)
	INT_ 	(INGREDIENT_ID) PRIMARY_KEY
	STRING_ (INGREDIENT, 1024) UNIQUE
END_TABLE


First I wanted to know if the schema makes sense to you.
Further, could someone please draft the upp sql statements for adding/fetching CATEGORIES and INGREDIENTS.
I figured out a dialog for the main RECIPES table columns, but struggling badly with the latter.

Guido
Re: Please help the SQL-challenged [message #9585 is a reply to message #9566] Fri, 18 May 2007 10:16 Go to previous message
mirek is currently offline  mirek
Messages: 12066
Registered: November 2005
Ultimate Member
Sounds quite OK to me.


SQL * Insert(CATEGORIES)
           (CAT_ID, some_value);

SQL * Update(CATEGORIES)(CAT_ID, update_value).Where(ID == key);

Sql sql;
sql * Select(ID, CAT_ID).From(CATEGORIES).Where(... some condition ...);

now the loop can look like

int id, cat_id;
while(sql.Fetch(id, cat_id)) ...

or

while(sql.Fetch()) { .. sql[ID] .. sql[0] .. }

(you can use either ids or indexes).

Alternatively, you can use S_CATEGORIES structure to work with the whole record.

Previous Topic: Sql::Execute()
Next Topic: SQL "AS" for PostgreSQL fixed
Goto Forum:
  


Current Time: Wed Oct 23 17:58:30 CEST 2019

Total time taken to generate the page: 0.01611 seconds