[sqlite] database design question

Hi All, I am working on sample database application. I want to store book names and authors. Tables: 1. Authors: columns(AuthorId_primarykey, Name, SSN) 2. Books : columns(BookId_primarykey, Title)//Title is unique 3. Author_Books: columns(AuthorId_primarykey, BookId_primarykey) Here,

Re: [sqlite] database design question

dd wrote: > I am working on sample database application. I want to store book names > and authors. > > 1. Authors: columns(AuthorId_primarykey, Name, SSN) > 2. Books : columns(BookId_primarykey, Title)//Title is unique Book titles are not unique. > 3. Author_Books:

[sqlite] finding records where a field only contains numbers

Hi all, is there a way to find all records where a field only contains non-alpha numeric characters, or numbers? thanks gert ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database design question

dd wrote: > my app need to delete author record from author table when author > doesn't have any books. How to handle this? (I can verify in author > table, whether this author belongs to any other book when book > deletion. You mean the Author_Books table. > If no, delete from author table.

Re: [sqlite] finding records where a field only contains numbers

Gert Van Assche wrote: > is there a way to find all records where a field only contains non-alpha > numeric characters, or numbers? This description is rather vague. The following finds values that do not contain alphabetical characters: SELECT * FROM MyTable WHERE AField NOT GLOB '*[^A-Za-z]*'

Re: [sqlite] finding records where a field only contains numbers

Thanks Clements, that does the trick indeed! 2013/6/12 Clemens Ladisch > Gert Van Assche wrote: > > is there a way to find all records where a field only contains non-alpha > > numeric characters, or numbers? > > This description is rather vague. > The following finds

Re: [sqlite] database design question

Thanks for your response. It will delete from Author_Books when book deleted. I am trying minimize queries on Authors table. For every deletion of record from Books, it needs to check in Authors table. One extra query execution is there. I am trying minimize that. On Wed, Jun 12, 2013 at 1:00

Re: [sqlite] database design question

On 12 Jun 2013, at 9:49am, dd wrote: >>> Book titles are not unique. > I agree. 2. Books : columns(BookId_primarykey, > Book_id_in_string_format_like_guid) I’m not sure why you would need a GUID. You can store 2. Books : columns(BookId_primarykey, Title)

Re: [sqlite] database design question

Hi there, May I ask, if you have a one-to-one relation of Books vs. Authors and wish to maintain it as such, why do you not just have one table with both fields and appropriate indexes? Searches wouldn't really be much faster, but all this deletion maintenance would go right down to near

Re: [sqlite] database design question

Yes Simon. I am looking for it. ON DELETE RESTRICT. I got the answer. Should I enable foreign key support to use on delete restrict?( http://www.sqlite.org/foreignkeys.html) I have x databases without enabling foreign key support. Can I enable foreign key support for x+1 database only? On

Re: [sqlite] database design question

Foreign Key constraints are enabled or disabled for a connection to a database using the "PRAGMA foreign_keys = ON" SQL on the connection that should be doing the constraining. After that, only DBs that have tables with foreign key constraints in one or more Tables will be affected by the setting

Re: [sqlite] database design question

On 12 Jun 2013, at 11:14am, dd wrote: > Yes Simon. I am looking for it. ON DELETE RESTRICT. > > I got the answer. > > Should I enable foreign key support to use on delete restrict?( > http://www.sqlite.org/foreignkeys.html) I think that that fits with your earlier

Re: [sqlite] database design question

I got it. Thank you RSmith and Simon Slavin. On Wed, Jun 12, 2013 at 2:54 PM, Simon Slavin wrote: > > On 12 Jun 2013, at 11:14am, dd wrote: > > > Yes Simon. I am looking for it. ON DELETE RESTRICT. > > > > I got the answer. > > > > Should I enable

[sqlite] Fine tuning SQLite performance with WAL mode

Hello users, My application uses 3 SQLite DBs with varying degrees of usage (which I will explain below). After reading about WAL mode, switched all my DBs from delete to wal. I'm however disappointed with the outcome and do not see any significant increase in performance levels (or in some

[sqlite] getting transactions working with prepare, step & finallise

Tbank you in anticipation for any forthcoming advice I include my program below in Powerbasic (not disimilar to C) and wonder what I need to do in order to "wrap" multiple statements (which use prepare,step & finalise) as transactions? #COMPILE EXE #DIM ALL #INCLUDE "sqlite3.inc" 'created 09 May

Re: [sqlite] getting transactions working with prepare, step & finallise

On 12 June 2013 16:50, e-mail mgbg25171 wrote: > Tbank you in anticipation for any forthcoming advice > I include my program below in Powerbasic (not disimilar to C) > and wonder what I need to do in order to "wrap" > multiple statements (which use prepare,step &

Re: [sqlite] getting transactions working with prepare, step & finallise

Simon Thanks very much for the direction On 12 June 2013 17:32, Simon Davies wrote: > On 12 June 2013 16:50, e-mail mgbg25171 > wrote: > > Tbank you in anticipation for any forthcoming advice > > I include my program below in

[sqlite] ALWAYS/NEVER and assert()

I note that there are some assert() statements spread through the SQLite code base and not isolated just to the ALWAYS and NEVER macros. Why did the authors choose to write, for example, assert( pName==0 ); in sqlite3CreateIndex, rather than ALWAYS( pName==0 ); ? For the purposes of

Re: [sqlite] ALWAYS/NEVER and assert()

On Wed, Jun 12, 2013 at 1:26 PM, Eric Rubin-Smith wrote: > I note that there are some assert() statements spread through the SQLite > code base and not isolated just to the ALWAYS and NEVER macros. Why did > the authors choose to write, for example, > > assert( pName==0 );

Re: [sqlite] database design question

On Wed, 12 Jun 2013 13:55:25 +0400 dd wrote: > I am trying minimize queries on Authors table. For every deletion of > record from Books, it needs to check in Authors table. One extra > query execution is there. I am trying minimize that. You might not be aware of what you

[sqlite] Different result set of query in examples of SQLite book

Dear all, Following select query returns different result data between v3.7.11 and v3.7.13~. CREATE TABLE foods( id integer primary key, type_id integer, name text ); CREATE TABLE foods_episodes( food_id integer, episode_id integer ); [Insert some data] SELECT name, (SELECT

Re: [sqlite] Different result set of query in examples of SQLite book

On 6/13/2013 12:24 AM, Yongil Jang wrote: SELECT name, (SELECT COUNT(id) FROM foods_episodes WHERE food_id=f.id) count FROM foods f ORDER BY count DESC LIMIT 10; Result from v3.7.6 and v3.7.11. Hot Dog|5 Kasha|4 Ketchup|4 .. Result from v3.7.13 and v3.7.17. Wax Beans (Generic

Re: [sqlite] Different result set of query in examples of SQLite book

Thank you, Igor. :) 2013/6/13 Igor Tandetnik > On 6/13/2013 12:24 AM, Yongil Jang wrote: > >> SELECT >>name, >>(SELECT COUNT(id) FROM foods_episodes WHERE food_id=f.id) count >> FROM >>foods f >> ORDER BY count DESC LIMIT 10; >> >> >> Result from v3.7.6 and