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

[Yongil Jang]

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 v3.7.11.
>>
>> Hot Dog|5
>> Kasha|4
>> Ketchup|4
>> ..
>>
>>
>> Result from v3.7.13 and v3.7.17.
>>
>> Wax Beans (Generic brand)|412
>>
>>
>> Which result set is correct?
>> When I changed statement from COUNT(id) to COUNT(food_id) or COUNT(*), it
>> shows same result with older version.
>>
>
> Looks like a bug in the newer versions. id resolves to f.id, which within
> the context of the sub-select is a constant. So COUNT(id) should work no
> differently than, say, COUNT(1), which in turn should be equivalent to
> COUNT(*).
>
> Of course, there is really no good reason to write COUNT(id) here.
> --
> Igor Tandetnik
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

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

Hot Dog|5
Kasha|4
Ketchup|4
..


Result from v3.7.13 and v3.7.17.

Wax Beans (Generic brand)|412


Which result set is correct?
When I changed statement from COUNT(id) to COUNT(food_id) or COUNT(*), it
shows same result with older version.


Looks like a bug in the newer versions. id resolves to f.id, which 
within the context of the sub-select is a constant. So COUNT(id) should 
work no differently than, say, COUNT(1), which in turn should be 
equivalent to COUNT(*).


Of course, there is really no good reason to write COUNT(id) here.
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Yongil Jang]

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 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 brand)|412


Which result set is correct?
When I changed statement from COUNT(id) to COUNT(food_id) or COUNT(*), it
shows same result with older version.

I've got this examples from following links of "The Definitive Guide to
SQLite"

http://www.apress.com/9781430232254

Regards,
Yongil.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database design question

[James K. Lowden]

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 can do in a single query

delete from authors
where Name = 'Hamilton'
and AuthorId not in (select AuthorId from Author_Books)

That query will avoid deletion of authors represented among the books
without running afoul of referential integrity constraint enforcement.  

HTH.  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Richard Hipp]

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 );
>
> in sqlite3CreateIndex, rather than
>
>   ALWAYS( pName==0 );
>
> ?
>

They do different things - similar things but different all the same.

Why would you want to use ALWAYS() in place of assert()?  What would that
accomplish?



>
> For the purposes of testing coverage, do the developers compile SQLite with
> NDEBUG?
>

Of course.  Otherwise every assert() statement would raise alarms about
untested branches.


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ALWAYS/NEVER and assert()

[Eric Rubin-Smith]

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 testing coverage, do the developers compile SQLite with
NDEBUG?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[e-mail mgbg25171]

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 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?
> >
> .
> .
> .
> .
> >
> > FUNCTION exec_sql( _
> >hDB AS DWORD, _
> >hD AS DWORD, _
> >BYVAL pzSql AS ASCIZ PTR, _
> >BYVAL pzErr_msg AS ASCIZ PTR _
> >) AS LONG
> >
> >LOCAL lresult   AS LONG
> >LOCAL pzTailAS ASCIZ PTR
> >LOCAL pzPreparedAS ASCIZ PTR
> >LOCAL pzStmtAS ASCIZ PTR
> >LOCAL azcols()  AS ASCIZ PTR  ' array of dword pointers to
> > column name strings
> >LOCAL azvals()  AS ASCIZ PTR  ' array of dword pointers to
> > column values
> >LOCAL ncols AS LONG   ' the column count
> >LOCAL nErmsgAS LONG
> >LOCAL szErmsg   AS ASCIZ * 512
> >LOCAL nRetryAS LONG
> >LOCAL s AS STRING
> >LOCAL i, l  AS LONG
> >
> >lresult = %SQLITE_OK
> >
> >IF @pzSql = "" THEN
> >   FUNCTION = %SQLITE_OK
> >   EXIT FUNCTION ' called with null SQL statement
> >END IF
> >
> >lresult = sqlite3_prepare( hDB, @pzSql, -1, pzPrepared, pzTail )
>
> Your exec_sql function is passed multiple sql statements in a single
> string, but you are only ever preparing the first statement.
>
> Reread http://www.sqlite.org/c3ref/prepare.html with particular focus on
> pzTail
>
> .
> .
> .
>
> Regards,
> Simon
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Simon Davies]

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 & finalise) as transactions?
>
.
.
.
.
>
> FUNCTION exec_sql( _
>hDB AS DWORD, _
>hD AS DWORD, _
>BYVAL pzSql AS ASCIZ PTR, _
>BYVAL pzErr_msg AS ASCIZ PTR _
>) AS LONG
>
>LOCAL lresult   AS LONG
>LOCAL pzTailAS ASCIZ PTR
>LOCAL pzPreparedAS ASCIZ PTR
>LOCAL pzStmtAS ASCIZ PTR
>LOCAL azcols()  AS ASCIZ PTR  ' array of dword pointers to
> column name strings
>LOCAL azvals()  AS ASCIZ PTR  ' array of dword pointers to
> column values
>LOCAL ncols AS LONG   ' the column count
>LOCAL nErmsgAS LONG
>LOCAL szErmsg   AS ASCIZ * 512
>LOCAL nRetryAS LONG
>LOCAL s AS STRING
>LOCAL i, l  AS LONG
>
>lresult = %SQLITE_OK
>
>IF @pzSql = "" THEN
>   FUNCTION = %SQLITE_OK
>   EXIT FUNCTION ' called with null SQL statement
>END IF
>
>lresult = sqlite3_prepare( hDB, @pzSql, -1, pzPrepared, pzTail )

Your exec_sql function is passed multiple sql statements in a single
string, but you are only ever preparing the first statement.

Reread http://www.sqlite.org/c3ref/prepare.html with particular focus on pzTail

.
.
.

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[e-mail mgbg25171]

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 2013, 15:34:42

GLOBAL ghDb AS DWORD
GLOBAL gDBOPEN AS LONG

SUB Open_Database (fl_nm AS ASCIZ * 512, hd AS DWORD )
   IF gDBOPEN = 1 THEN
  sqlite3_close(ghDb)
   END IF
   IF sqlite3_open(fl_nm, BYREF ghDb) <> %SQLITE_OK THEN
   ? "can't open database",,"error"
   gDBOPEN = 0
   EXIT SUB
   END IF
   gDBOPEN = 1
END SUB


FUNCTION exec_sql( _
   hDB AS DWORD, _
   hD AS DWORD, _
   BYVAL pzSql AS ASCIZ PTR, _
   BYVAL pzErr_msg AS ASCIZ PTR _
   ) AS LONG

   LOCAL lresult   AS LONG
   LOCAL pzTailAS ASCIZ PTR
   LOCAL pzPreparedAS ASCIZ PTR
   LOCAL pzStmtAS ASCIZ PTR
   LOCAL azcols()  AS ASCIZ PTR  ' array of dword pointers to
column name strings
   LOCAL azvals()  AS ASCIZ PTR  ' array of dword pointers to
column values
   LOCAL ncols AS LONG   ' the column count
   LOCAL nErmsgAS LONG
   LOCAL szErmsg   AS ASCIZ * 512
   LOCAL nRetryAS LONG
   LOCAL s AS STRING
   LOCAL i, l  AS LONG

   lresult = %SQLITE_OK

   IF @pzSql = "" THEN
  FUNCTION = %SQLITE_OK
  EXIT FUNCTION ' called with null SQL statement
   END IF

   lresult = sqlite3_prepare( hDB, @pzSql, -1, pzPrepared, pzTail )

   IF lresult <> %SQLITE_OK THEN
  GOTO finish
   END IF

   IF pzPrepared = 0 THEN
  GOTO finish
   END IF

   ncols = sqlite3_column_count(pzPrepared)
   REDIM azcols(0 TO ncols-1)
   FOR i = 0 TO ncols -1
   azcols(i) = sqlite3_column_name(pzPrepared, i)
   ? @azcols(i),,"col heading"
   NEXT

   REDIM azvals(0 TO ncols-1)
   DO WHILE 1
  lresult = sqlite3_step(pzPrepared)
s = ""
  SELECT CASE lresult
 CASE %SQLITE_ROW
FOR i = 0 TO ncols - 1
   azvals(i) = sqlite3_column_text(pzPrepared, i)
   s = s + @azvals(i) + ","
NEXT
? s,,"row"
 CASE %SQLITE_DONE
GOTO Finish
CASE ELSE
   ? "unexpected result = " + STR$(lresult)
   GOTO finish
END SELECT
LOOP
Finish:
   IF pzPrepared <> 0 THEN
  sqlite3_finalize(pzPrepared)
   END IF
   IF lresult <> %SQLITE_OK THEN
  lresult = sqlite3_errcode(hDB)
  pzErr_msg = sqlite3_errmsg(hDB)
   END IF
   FUNCTION = lresult
END FUNCTION


FUNCTION PBMAIN()
   LOCAL sSQL AS STRING
   LOCAL pzErr_msg AS ASCIZ PTR

'I don't seem to be able to make transactions work
'ie I was expecting to see a msgbox for each data row inserted
'but I don't
   sSQL =   "begin transaction;" + $CRLF +  _
"create table if not exists membership (member, date,
annualsubs, payments);" +  $CRLF + _
"insert into membership values ('Fred','10-APR-2010', 123,
54);" + $CRLF +  _
"insert into membership values ('James',   '10-APR-2010', 123,
0);" + $CRLF +  _
"insert into membership values ('Molly',   '10-MAY-2010', 123,
0);" + $CRLF +  _
"insert into membership values ('Angus',   '10-APR-2010',  90,
10);" + $CRLF +  _
"insert into membership values ('Patrick', '10-JUN-2010', 123,
0);" + $CRLF +  _
"select member, date ""date due"", annualsubs - payments
""amount due"" from membership;"  + _
"commit transaction;"

   open_database("test1.db",ghDb)

   sSQL =   "create table if not exists membership (member, date,
annualsubs, payments);"
   exec_sql( ghDb, ghDb, BYVAL STRPTR(sSQL), pzErr_msg )

'no this doesn't seem to work either
'   sSQL =   "begin transaction; " & "insert into membership values
('Fred','10-APR-2010', 123, 54);" & " commit transaction;"
'if I replace the above line with this one...it's fine
'I don't understand what it is that's missing to make transactions work

'btw...this replacement line works fine
   sSQL =   "insert into membership values ('Fred','10-APR-2010', 123,
54);"
   exec_sql( ghDb, ghDb, BYVAL STRPTR(sSQL), pzErr_msg )

   sSQL =   "select member, date ""date due"", annualsubs - payments
""amount due"" from membership;"
   exec_sql( ghDb, ghDb, BYVAL STRPTR(sSQL), pzErr_msg )

   sqlite3_free(BYVAL pzErr_msg)

   sqlite3_close(ghDb)
END FUNCTION
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Fine tuning SQLite performance with WAL mode

[Runcy Oommen]

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 cases less performance 
as before).
 
These are the two PRAGMA statements I executed to switch to WAL mode:
PRAGMA journal_mode = wal;
PRAGMA wal_autocheckpoint = 10;
 
Now I know that the default wal_autocheckpoint is 1000, does it affect either 
positive or negatively with my current value of 10?
 
I'm also giving some usage statistics on the DB which will help you guide me 
with the mode and settings:
1. DB-1
About 190+ SELECT, INSERT, DELETE statements executed every 10 seconds  
(whether in idle or use)
- of which 80% are SELECT statements
- remaining 20% DELETE and INSERT statements
 
2. DB-2
- about 20+ SELECT statements per minute when idle
- about 60+ SELECT, DELETE and INSERT statements per minute when use
(of which 90% are SELECT, 10% are DELETE\INSERT)
 
3. DB-3
- about 380+ SELECT, INSERT, DELETE statements executed every 24 hours (whether 
in idle or use)
- of which 95% are SELECT statements
- remaining 5% DELETE and INSERT statements
 
After analyzing the above DBs, can you please suggest me the best approach per 
DB?
Should I be using WAL in the first instance for all the DBs?
What should be the ideal settings for WAL usage per DB?
Any suggestions in this regard is highly appreciated.
 
Cheers,
Runcy
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database design question

[dd]

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 foreign key support to use on delete restrict?(
> > http://www.sqlite.org/foreignkeys.html)
>
> I think that that fits with your earlier description: it will make SQLite
> refuse to delete an author if they have books in the database.
>
> > I have x databases without enabling foreign key support. Can I enable
> > foreign key support for x+1 database only?
>
> Turning on FOREIGN KEY support on a database that has no foreign keys does
> no harm.  It might slow some things down a millisecond perhaps.
>
> However, if you have separate applications for separate databases then you
> can turn on FOREIGN KEY support just in your 'books’ application.  In that
> application, just execute the SQL command
>
> PRAGMA foreign_keys = ON
>
> before you open the database file.  Then everything will work as expected.
>
> You must, of course, be using SQLite version 3.6.19 or later.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database design question

[Simon Slavin]

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 description: it will make SQLite 
refuse to delete an author if they have books in the database.

> I have x databases without enabling foreign key support. Can I enable
> foreign key support for x+1 database only?

Turning on FOREIGN KEY support on a database that has no foreign keys does no 
harm.  It might slow some things down a millisecond perhaps.

However, if you have separate applications for separate databases then you can 
turn on FOREIGN KEY support just in your 'books’ application.  In that 
application, just execute the SQL command

PRAGMA foreign_keys = ON

before you open the database file.  Then everything will work as expected.

You must, of course, be using SQLite version 3.6.19 or later.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database design question

[RSmith]

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 and only 
for the connection which enabled it.


This means that opening the Database from another application might ignore your foreign key constraints if the connection did not 
have it set to ON.


I don't think there exists a way to force constraining from the DB file itself... but I might be wrong, anyone else with insight on 
this would be appreciated.



On 2013/06/12 12:14, 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 have x databases without enabling foreign key support. Can I enable
foreign key support for x+1 database only?



On Wed, Jun 12, 2013 at 2:01 PM, Simon Slavin  wrote:



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database design question

[dd]

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 Wed, Jun 12, 2013 at 2:01 PM, Simon Slavin  wrote:

>
> 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)
>
> Just don’t put a restriction on it that the title must be unique.  The
> BookId is unique and that is enough.
>
> >
> >>> Why is there a problem when there is an author without any books?
> >  No problem. But, 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. If no, delete from author table. I am not happy with this
> > solution )
>
> The normal way to do this in a SQLite database would be to use a FOREIGN
> KEY relationship:
>
> 
>
> You would have something like this:
>
> 1. Authors:   columns(AuthorId, Name, SSN)
> 2. Books   :  columns(BookId, Title, AuthorId,
> FOREIGN KEY(AuthorId) REFERENCES Authors(AuthorId))
>
> You could set the database up in two ways:
>
> (A) ON DELETE CASCADE would allow you to delete an author with books, and
> would delete their books too.
> (B) ON DELETE RESTRICT would make SQLite issue an error message if you
> tried to delete an author who still had books in the Books table.
>
> SQLite will do this itself automatically.  You do not need to write your
> own code to look at the other table.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database design question

[RSmith]

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


On 2013/06/12 11:55, dd wrote:

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 PM, Clemens Ladisch  wrote:


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database design question

[Simon Slavin]

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)

Just don’t put a restriction on it that the title must be unique.  The BookId 
is unique and that is enough.

> 
>>> Why is there a problem when there is an author without any books?
>  No problem. But, 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. If no, delete from author table. I am not happy with this
> solution )

The normal way to do this in a SQLite database would be to use a FOREIGN KEY 
relationship:



You would have something like this:

1. Authors:   columns(AuthorId, Name, SSN)
2. Books   :  columns(BookId, Title, AuthorId,
FOREIGN KEY(AuthorId) REFERENCES Authors(AuthorId))

You could set the database up in two ways:

(A) ON DELETE CASCADE would allow you to delete an author with books, and would 
delete their books too.
(B) ON DELETE RESTRICT would make SQLite issue an error message if you tried to 
delete an author who still had books in the Books table.

SQLite will do this itself automatically.  You do not need to write your own 
code to look at the other table.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database design question

[dd]

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 PM, Clemens Ladisch  wrote:

> 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.
>
> That's how it's done.
>
> > I am not happy with this solution
>
> Why not?
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Gert Van Assche]

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 values that do not contain alphabetical characters:
>
> SELECT * FROM MyTable WHERE AField NOT GLOB '*[^A-Za-z]*'
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 values that do not contain alphabetical characters:

SELECT * FROM MyTable WHERE AField NOT GLOB '*[^A-Za-z]*'


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database design question

[Clemens Ladisch]

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.

That's how it's done.

> I am not happy with this solution

Why not?


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] finding records where a field only contains numbers

[Gert Van Assche]

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

[Clemens Ladisch]

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: columns(AuthorId_primarykey, BookId_primarykey)
>
>   I am facing an issue with deletion of records from Books table. One
> author may belongs to morethan one book. How to handle this scenario? Is
> there technique like shared_ptr.

Why is there a problem when there is an author without any books?


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] database design question

[dd]

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, I am able to avoid rendandent data (author names).

  I am facing an issue with deletion of records from Books table. One
author may belongs to morethan one book. How to handle this scenario? Is
there technique like shared_ptr.

Thanks in advance.

Regards,
d
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users