[sqlite] Where to look for SQLite coder?

[SongbookDB]

Hi - I need a small amount of coding done, and am wondering if someone can
recommend a good place to post for an SQLite coder?

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

Re: [sqlite] Ghost index?

[Simon Slavin]

On 12 Nov 2013, at 4:00am, SongbookDB  wrote:

> Ahh - ok. I was typing sqlite3 db.3sdb IN sqlite3, not command prompt.
> 
> I can load and search the db now.
> 
> So to drop the index 'index1' for the table 'table1', do I just type DROP
> INDEX IF EXISTS 'index1'; ? Doing so still shows the index when I load the
> database in SQLite Administrator.

No need to use single quotes around entity names.  In fact, it's incorrect to 
do so.  Single quotes are for strings.

Apart from that, yes, you can type any SQL command, end it with a semi-colon, 
then hit return and the shell tool will execute the command.  This includes 
SELECT commands.  You can split a command onto many lines.  The shell tool is 
looking for that semi-colon before it thinks you've finished the command.

The SQLite shell tool is written by the SQLite team and if it disagrees with 
any other SQLite program, the shell tool is probably the correct one.  As I've 
proved with my own programming errors many times.

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

Re: [sqlite] Ghost index?

[SongbookDB]

I've notices if I make a mistake, the prompt turns to ...>, and then
legitimate commands no longer work. Is there a way to get back to the
normal prompt?


On Tue, Nov 12, 2013 at 2:42 PM, Simon Slavin  wrote:

>
> On 12 Nov 2013, at 2:49am, SongbookDB 
> wrote:
>
> > I've named it db.s3db, run the shell, type sqlite3 db, then select * from
> > songs; and it gives the error "Error: near "sqlite3": syntax error.
>
> as a command to your operating system
>
> sqlite3 db.s3db
>
> then once you're in the program type SQL commands or things like
>
> .schema
> SELECT * FROM sqlite_master;
> DROP INDEX fred;
> SELECT * FROM sqlite_master;
> .quit
>
> .quit to exit.  For further information
>
> 
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

[sqlite] Never mind Simon - Re: Ghost index?

[SongbookDB]

Please disregard my last email - I was loading an incorrect copy of the
database into SQLite Administrator.


On Tue, Nov 12, 2013 at 2:42 PM, Simon Slavin  wrote:

>
> On 12 Nov 2013, at 2:49am, SongbookDB 
> wrote:
>
> > I've named it db.s3db, run the shell, type sqlite3 db, then select * from
> > songs; and it gives the error "Error: near "sqlite3": syntax error.
>
> as a command to your operating system
>
> sqlite3 db.s3db
>
> then once you're in the program type SQL commands or things like
>
> .schema
> SELECT * FROM sqlite_master;
> DROP INDEX fred;
> SELECT * FROM sqlite_master;
> .quit
>
> .quit to exit.  For further information
>
> 
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Ghost index?

[SongbookDB]

Ahh - ok. I was typing sqlite3 db.3sdb IN sqlite3, not command prompt.

I can load and search the db now.

So to drop the index 'index1' for the table 'table1', do I just type DROP
INDEX IF EXISTS 'index1'; ? Doing so still shows the index when I load the
database in SQLite Administrator.


On Tue, Nov 12, 2013 at 2:42 PM, Simon Slavin  wrote:

>
> On 12 Nov 2013, at 2:49am, SongbookDB 
> wrote:
>
> > I've named it db.s3db, run the shell, type sqlite3 db, then select * from
> > songs; and it gives the error "Error: near "sqlite3": syntax error.
>
> as a command to your operating system
>
> sqlite3 db.s3db
>
> then once you're in the program type SQL commands or things like
>
> .schema
> SELECT * FROM sqlite_master;
> DROP INDEX fred;
> SELECT * FROM sqlite_master;
> .quit
>
> .quit to exit.  For further information
>
> 
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Ghost index?

[Simon Slavin]

On 12 Nov 2013, at 2:49am, SongbookDB  wrote:

> I've named it db.s3db, run the shell, type sqlite3 db, then select * from
> songs; and it gives the error "Error: near "sqlite3": syntax error.

as a command to your operating system

sqlite3 db.s3db

then once you're in the program type SQL commands or things like

.schema
SELECT * FROM sqlite_master;
DROP INDEX fred;
SELECT * FROM sqlite_master;
.quit

.quit to exit.  For further information



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

Re: [sqlite] Ghost index?

[Simon Slavin]

On 12 Nov 2013, at 2:46am, SongbookDB  wrote:

> Does the shell expect a certain filename extension on the database file? I
> can't get mine to open.

It will open whatever file you tell it to.  You must type the full name 
including the extension, but you can use any extension you like, or none.

sqlite3 filename.ext

Simon.

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

Re: [sqlite] Ghost index?

[SongbookDB]

I've named it db.s3db, run the shell, type sqlite3 db, then select * from
songs; and it gives the error "Error: near "sqlite3": syntax error.


On Tue, Nov 12, 2013 at 1:26 PM, Simon Slavin  wrote:

>
> On 12 Nov 2013, at 1:50am, SongbookDB 
> wrote:
>
> > I've downloaded and run the shell tool - re opening a database, where do
> > base dir paths start from? I have the sqlite3.exe in a dir called test in
> > my downloads folder, and the test database is called db.sbdb - typing
> > sqlite3 db.sbdb doesn't open it.
>
> Paths start from your current directory.  The one you'd see if you typed
> DIR or ls.
>
> Or you could try specifying a full path.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Ghost index?

[SongbookDB]

Does the shell expect a certain filename extension on the database file? I
can't get mine to open. Thanks Simon.


On Tue, Nov 12, 2013 at 1:26 PM, Simon Slavin  wrote:

>
> On 12 Nov 2013, at 1:50am, SongbookDB 
> wrote:
>
> > I've downloaded and run the shell tool - re opening a database, where do
> > base dir paths start from? I have the sqlite3.exe in a dir called test in
> > my downloads folder, and the test database is called db.sbdb - typing
> > sqlite3 db.sbdb doesn't open it.
>
> Paths start from your current directory.  The one you'd see if you typed
> DIR or ls.
>
> Or you could try specifying a full path.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] How to cancel the operation?

[Igor Tandetnik]

On 11/11/2013 9:23 PM, Igor Korot wrote:

I downloaded and installed the command line SQLite3 tool on my Windows box.
What I don't understand now is to how to cancel the long operation.

Suppose I need to do this:
sqlite3> CREATE TRIGGER
> mytabe_insert AFTER INSERT ON
>

now at this point I need to cancel the trigger creation.

Hitting Ctrl+C will push me to the command prompt and not back to sqlite3.
Hitting ESC will not work.

Is there a way?


Type semicolon then Enter. The statement is malformed - you'll get an 
error message to that effect, and will be back at sqlite prompt.

--
Igor Tandetnik

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

Re: [sqlite] Ghost index?

[Simon Slavin]

On 12 Nov 2013, at 1:50am, SongbookDB  wrote:

> I've downloaded and run the shell tool - re opening a database, where do
> base dir paths start from? I have the sqlite3.exe in a dir called test in
> my downloads folder, and the test database is called db.sbdb - typing
> sqlite3 db.sbdb doesn't open it.

Paths start from your current directory.  The one you'd see if you typed DIR or 
ls.

Or you could try specifying a full path.

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

[sqlite] How to cancel the operation?

[Igor Korot]

Hi, ALL,
I downloaded and installed the command line SQLite3 tool on my Windows box.
What I don't understand now is to how to cancel the long operation.

Suppose I need to do this:
sqlite3> CREATE TRIGGER
> mytabe_insert AFTER INSERT ON
>

now at this point I need to cancel the trigger creation.

Hitting Ctrl+C will push me to the command prompt and not back to sqlite3.
Hitting ESC will not work.

Is there a way?

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

Re: [sqlite] Ghost index?

[SongbookDB]

Thanks Simon

I'm not sure how to set up logging when using SQLite with as3 AIR - I'll
check in with the Adobe community.

I've downloaded and run the shell tool - re opening a database, where do
base dir paths start from? I have the sqlite3.exe in a dir called test in
my downloads folder, and the test database is called db.sbdb - typing
sqlite3 db.sbdb doesn't open it.


On Tue, Nov 12, 2013 at 12:14 PM, Simon Slavin  wrote:

>
> On 12 Nov 2013, at 1:07am, Simon Slavin  wrote:
>
> > On 12 Nov 2013, at 12:51am, SongbookDB 
> wrote:
> >
> >> The process works fine at first - first time around, the index is
> dropped
> >> (this takes about 20 seconds on my test database). On subsequent loads,
> no
> >> dropping occurs.
> >
> > Are you logging the result code returned from your "DROP INDEX" command
> ?  Does it always return SQLITE_OK ?
>
> I forgot to mention that 20 seconds for any SQLite command is very
> unusual.  I suspect database locking, database corruption, or some other
> weirdness.  Therefore make sure your operations are doing what you think
> they're doing.
>
> You can check what's happening using the SQLite shell tool which you can
> download from the SQLite site.  Try the DROP INDEX command with that
> instead.  If it doesn't take 20 seconds then there's something wrong with
> your other method.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Ghost index?

[Simon Slavin]

On 12 Nov 2013, at 1:07am, Simon Slavin  wrote:

> On 12 Nov 2013, at 12:51am, SongbookDB  wrote:
> 
>> The process works fine at first - first time around, the index is dropped
>> (this takes about 20 seconds on my test database). On subsequent loads, no
>> dropping occurs.
> 
> Are you logging the result code returned from your "DROP INDEX" command ?  
> Does it always return SQLITE_OK ?

I forgot to mention that 20 seconds for any SQLite command is very unusual.  I 
suspect database locking, database corruption, or some other weirdness.  
Therefore make sure your operations are doing what you think they're doing.

You can check what's happening using the SQLite shell tool which you can 
download from the SQLite site.  Try the DROP INDEX command with that instead.  
If it doesn't take 20 seconds then there's something wrong with your other 
method.

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

Re: [sqlite] Ghost index?

[Simon Slavin]

On 12 Nov 2013, at 12:51am, SongbookDB  wrote:

> The process works fine at first - first time around, the index is dropped
> (this takes about 20 seconds on my test database). On subsequent loads, no
> dropping occurs.

Are you logging the result code returned from your "DROP INDEX" command ?  Does 
it always return SQLITE_OK ?

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

[sqlite] Ghost index?

[SongbookDB]

Hi

I have a desktop app that loads an SQLite database into a datagrid.

Before loading data, it goes through series of checks to make sure that the
user's database has the correct indexes set up. The very last check is to
test for the existence of the index I originally used on the database a
while back, and if there, drop it, using "DROP INDEX IF EXISTS index1" -

Once dropped, the index should NEVER have to be dropped again, as it's not
there, right?

The process works fine at first - first time around, the index is dropped
(this takes about 20 seconds on my test database). On subsequent loads, no
dropping occurs.

However, if you restart your computer, the dropping process occurs again
the first time the app loads the database!!!

I tested for the existence of the index by restarting, and loading the
database in SQLite Administrator, and it's definitely NOT there.

Any idea what could be causing this?

Thanks for taking a look.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Combining two databases into one

[Dave Wellman]

Simon, Many thanks for the clarification.
Dave


Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: 11 November 2013 14:35
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Combining two databases into one


On 10 Nov 2013, at 8:27pm, David Wellman 
wrote:

> Can I just check something. When you say " Don't forget to DELETE FROM 
> before you insert your new rows " I assume that you're saying to make 
> sure that the target table is empty before I do the insert. Yes?

Yep, that's it.  Common mistake we've all made.  Well, okay, I made it.  And
I'm still embarrassed about it.

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] Combining two databases into one

[Dave Wellman]

HI Ulrich,
Sorry I should have made that clearer. I want to save all the data in one
database, not just see it.
Thanks for the idea though.
Cheers,
Dave


Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ulrich Goebel
Sent: 10 November 2013 20:28
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Combining two databases into one

May be there is a simple way depending of what means 'combine all the data'.
If that means just _see_ all that data (without the oportunity to
manipulate), there could be defined a VIEW which does a compound SELECT like

CREATE VIEW combined_all as
SELECT ... from db1.T1
UNION SELECT ... from db2.T1
UNION SELECT ... from db3.T1
...

Then it would not be necessary to insert the data from some databases into
another 'from time to time', but you would have all the data 'just when you
need it'.
For details see http://www.sqlite.org/lang_select.html#compound

Ulrich


Am 10.11.2013 18:49, schrieb Dave Wellman:
> Hi,
>
>
>
> I'm looking at a use case for SQLITE within one of our applications. 
> One potential scenario would be for multiple, asynchronous processes 
> to build their own database. Each one would be populating a different 
> table. At some point it would be 'really useful' to combine all the 
> data into a single SQLITE database. We'd still be using multiple tables.
>
>
>
> Obviously our application can open multiple databases, select all rows 
> from
> T1 in one database and insert them into T1 in another database and 
> continue doing that until we're complete. I can't see any reason why 
> that shouldn't technically work and it will probably be fast enough 
> from a performance perspective.
>
>
>
> Is there a 'smart' way of doing this using built-in functaionality of 
> sqlite? I looked for a 'merge databases' command or something similar 
> but couldn't find anything.
>
>
>
> Ah! I may have just found the answer. If I've got databases db1 and 
> db2 attached, can I use something like 'insert into db1.t1 select * 
> from db2.t1;' (assuming that the 't1' definitions are the same !)?
>
>
>
> Cheers,
>
> Dave
>
>
>
>
>
> Ward Analytics Ltd - information in motion
>
> Tel: +44 (0) 118 9740191
>
> Fax: +44 (0) 118 9740192
>
> www:   http://www.ward-analytics.com
>
>
>
> Registered office address: The Oriel, Sydenham Road, Guildford, 
> Surrey, United Kingdom, GU1 3SR
>
> Registered company number: 3917021 Registered in England and Wales.
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

--
Ulrich Goebel
Paracelsusstr. 120, 53177 Bonn
___
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] Feature request: Better support for database file moving

[Stephan Beal]

On Mon, Nov 11, 2013 at 5:31 PM, Stephen Chrzanowski wrote:

> a Linux developer (Unless you count BASH scripting) but I deal with the OS
> enough (My job for 6 years) to know enough low level stuff that at the file
> system level, programs attach to files at the INODE level, so files can be
> renamed, or moved, with a file handle open against it on the same device.
>

Just to re-stress that point: on the same device. If you move it across
devices, it is basically a copy+delete operation and all bets are off. The
inode will change in that case because inodes are device (or volume)
specific.


> OS/File System is concerned.  Since the INODE never changes on a move or
> rename


(on the same device)


> , file operations continue to happen against the contents of that
> file.  You can even DELETE a file and file operations will continue usually
> uninterrupted while that file handle is open.


In fact, some apps use that to hide temp files and ensure they are cleaned
up even in the case of an app crash: open() the file, then immediately
unlink() it, giving the file handle back to the user. When the app
ends/crashes resp. when close() is called on the handle, the FS can then
free up that space.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Since tyranny's the only guaranteed byproduct of those who insist on a
perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] executing queries on normalized database

[Stephen Chrzanowski]

I agree with Simon, but I also twist things up a bit.

Databases are just data storage containers, at the most low level, raw
definition.  A text file could be a "database" for all intents and
purposes.  There should be things in there to protect the data within, but
there also needs to be some kind of filter outside that container to make
sure that the data is logical and meaningful.  If you were to throw motor
oil in a maple syrup bottle may LOOK right... Depending on the weight of
the oil, it'd be the same color and texture and all...  But.. well... you
know.  I'll pass on those waffles.

Simon says (heh) that his applications manage foreign keys, and is
flexible, and this is absolutely true.  However, what I do is rely on both
managing the foreign keys at the business logic level (in my code), and at
the database level.  I see nothing wrong with throwing a constraint at the
database to make sure that I'm not inserting information into the database,
parent information already exists.  If it doesn't, I screwed something up
code wise that would have allowed an unknown entity to place dirty data in
my otherwise pristine database.  What I DON'T agree with is that EVERYTHING
should be thrown at the database.  I try to stay away from triggers, for
example.  If there is something I need to have managed alongside a previous
statement I sent to the database, I'll let the code handle it.


On Mon, Nov 11, 2013 at 10:27 AM, Simon Slavin  wrote:

>
> On 11 Nov 2013, at 2:35pm, dd  wrote:
>
> > Got it.
> >
> > Can I conclude this way: Foreign keys works pretty well when
> > application deals with parent keys only. But, application may need to
> > execute more queries when dealing with child key/tables.
>
> My feeling is that a lot of this logic needs to be done at the application
> level because at that level you have more flexibility and can interact
> better with the user.  Foreign keys can be useful to enforce
> business-logic.  But not a substitute for programming which should check
> these things first.
>
> However, that's just my own view.  I've seen rational articles written
> that argue that every part of business logic possible should be included in
> your database schema: constraints, foreign keys, triggers, and all.  And
> that your app should rely on those doing their jobs rather than having each
> app perform its own checks.  It's a matter of opinion.
>
> 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] Feature request: Better support for database file moving

[Stephen Chrzanowski]

Two questions;

- Outside of SQLite, how would you handle "better checking" if a file
randomly moves stuff around when a transaction is happening?
- Why would a user move a database file while the program is in use?

You can plan for every kind of problem, but you can't prevent every
problem.  Some even subscribe to "You can't plan for every problem because
some dumb user will think of something new we smart developers haven't
thought of yet".  There is a cost/benefit ratio you have to ponder.  The
cost portion is consider to how much bloat your program has to deal with
protecting itself from the user and your time invested to code the bloat,
and then compare that value to "for what gain"?

If the value of the data the user is working on is THAT precious to them,
they shouldn't be playing with the file structure to begin with.  I'm on
the side of "You move files around, you deal with the consequences.  The
program and data lives where it is for a reason, so paws off.".  The thing
I've not had to consider in my coding life is the movement of files during
program operations, and that reason is in the next paragraph.  With any
program I've written with SQLite, the VERY FIRST THING my code does is open
a file handle to the main database file, and the VERY LAST THING my code
does is close the database and release the file handle.  I have a unit
written SPECIFICALLY for handling SQLite file access.  I open the database
connection even before the GUI forms are even considered.

Linux/EXT3+ handles file handles differently compared to Windows.  I'm not
a Linux developer (Unless you count BASH scripting) but I deal with the OS
enough (My job for 6 years) to know enough low level stuff that at the file
system level, programs attach to files at the INODE level, so files can be
renamed, or moved, with a file handle open against it on the same device.
Details such as file name, path, size, attributes, permissions, etc, etc,
are all stored somewhere else but references the INODE.  Giving a program a
path to a file is only a "reference" and not an absolute as far as the
OS/File System is concerned.  Since the INODE never changes on a move or
rename, file operations continue to happen against the contents of that
file.  You can even DELETE a file and file operations will continue usually
uninterrupted while that file handle is open.  Windows handles things so
that if ANYTHING has their hand on it, NOTHING can be done to the "external
container" itself, meaning, the file cannot be renamed and cannot be moved.
I'm not sure if its the file system that blocks this kind of behavior and
tells the OS "Nope, not gonna happen", or if its the OS that is checking if
any handles are attached to the file in question, but this kind of blocking
behavior has been around since early MS DOS days.

Basically, with SQLite anyways, I think that if you have a file that is
moved during a transaction, your results are going to fall under the
"UNDEFINED" category.  I've not looked at the code to see how the creation
of the "-journal" files are done but I would suspect that SQLite internally
holds the old path when the initial database file was created/opened, and
just creates work files there.  If that directory structure no longer
exists and the -journal file has to be recreated, the program may fault
out, or, the directory structure may be recreated on the fly.

Lets consider the weights on your program and this particular situation;
- the length of time a transaction is actively running
- the time a user has to move a file during the transaction
- the volume of the data
- user expertise

If the transaction time is short, then a user moving files during a
transaction shouldn't be a concern.
If the transaction is long, the volume of data is probably high, so users
are probably busy waiting for things to happen or have other pressing
concerns rather than random file management.
If user expertise is high, user is probably not going to be moving files
around anyways since they know the application is running and corruption
could happen
If user expertise is low, they shouldn't know how to move files around, or
don't care to move files around, or wouldn't know what to do with said
files.

If you want a suggestion, before EVERY action on the database, validate
that the database physically exists in the directory required.  If it
doesn't, "abort, fail, don't retry" as your user is monkeying with things
they shouldn't be and its on them for the data corruption.



On Mon, Nov 11, 2013 at 9:01 AM, L. Wood  wrote:

> Here is my situation and why SQLite doesn't seem to satisfy it.
> It would be great to hear what some of the SQLite developers think about
> this.
>
> * My Mac OS X program has a document file format of packages (which are
> nothing but glorified directories). My database file is always inside of a
> package. So, it can be assumed that all the extra files (such as -journal)
> will always move with the main database file if 

Re: [sqlite] Feature request: Better support for database file moving

[Tim Streater]

On 11 Nov 2013 at 15:36, Simon Slavin  wrote: 

> On 11 Nov 2013, at 2:01pm, L. Wood  wrote:
>
>> * A user could move the file during a transaction, i.e., between
>> _prepare_v2(); and _finalize();
>>
>> It seems that SQLite needs support for this situation. Would you agree?
>>
>> If so, the question is how. Maybe through callback functions requesting an
>> updated directory when needed? Or by internally following the changed path of
>> the main database file instead of storing it in memory at the outset?
>>
>> Until then, I guess I just have to live with the risk of either an error or
>> data corruption when a user moves a document (a package).
>
> First, a user has to be pretty into Macs to even know where to find data files
> for a sandboxed app, and how to delete/move them.  It's really not easy.
>
> Second, every other kind of data file from every other database has the same
> problem.  Even if you were keeping your database in a text file, someone could
> still move that text file while your app had it open, meaning that the next
> time your app was run it would say it had no data at all.
>
> As for your remedy, yes I agree that OS X's ability to notify your app when
> one of its files is moved is pretty neat.  But Mac users are a tiny minority
> of the billions of installations of SQLite and such a feature isn't important
> enough to put in a 'lite' database.  And also, what do you expect SQLite to do
> if it notes such a movement ?  Return an error message ?  It already does
> that.
>
> Besides which, you can write it yourself externally to SQLite: use FSEvent on
> the folder your database is in checking for
> kFSEventStreamEventFlagItemRemoved, and act accordingly.  No need to put that
> facility inside SQLite.  If you get it working neatly, it could be released
> for the Mac/SQLite community at large.  Personally, I agree with Stephen: if
> my users want to mess with my app's data files they deserve everything they
> get.

My app is not designed as an OS X package and the user's files (SQLite 
databases) are visible to the user. In fact the user might want to move the 
whole app (including the user's files) elsewhere in the file system (such as 
onto a memory stick so they can work with it on another machine). However, the 
user is expected to quit the app while doing this.

It's unclear if the OP wants to do this or expects to be able to move the whole 
lot while the app is running, which is not something I'd expect to be able to 
do. My app just notes where in the file system it is when it starts and uses 
that to build paths when opening databases.

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

Re: [sqlite] sqlite db getting corrupt on power outage scenarios

[j . merrill]

Consumer-grade flash drives are notoriously unreliable. I suggest that (at a 
minimum) you test the drive with the software described at

http://sosfakeflash.wordpress.com/2008/09/02/h2testw-14-gold-standard-in-detecting-usb-counterfeit-drives/

Have you what happens to your flash drive (and its file system) when 
non-database applications are writing to files on the flash drive when a power 
outage occurs?

J. Merrill

Date: Fri, 8 Nov 2013 06:41:15 +
From: "Mayank Kumar (mayankum)" 
To: General Discussion of SQLite Database 
Subject: Re: [sqlite] sqlite db getting corrupt on power outage
scenarios

[[small subset only]]
[MK] I am using a flash drive running on linux kernel 2.6.27.10(SMP). The 
corruption is sometimes seen on sqlite db on the flash drive and sometimes seen 
on sqlite db in nvram. Network file system is not involved.


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

Re: [sqlite] Feature request: Better support for database file moving

[Simon Slavin]

On 11 Nov 2013, at 2:01pm, L. Wood  wrote:

> * A user could move the file during a transaction, i.e., between 
> _prepare_v2(); and _finalize();
> 
> It seems that SQLite needs support for this situation. Would you agree?
> 
> If so, the question is how. Maybe through callback functions requesting an 
> updated directory when needed? Or by internally following the changed path of 
> the main database file instead of storing it in memory at the outset?
> 
> Until then, I guess I just have to live with the risk of either an error or 
> data corruption when a user moves a document (a package). 

First, a user has to be pretty into Macs to even know where to find data files 
for a sandboxed app, and how to delete/move them.  It's really not easy.

Second, every other kind of data file from every other database has the same 
problem.  Even if you were keeping your database in a text file, someone could 
still move that text file while your app had it open, meaning that the next 
time your app was run it would say it had no data at all.

As for your remedy, yes I agree that OS X's ability to notify your app when one 
of its files is moved is pretty neat.  But Mac users are a tiny minority of the 
billions of installations of SQLite and such a feature isn't important enough 
to put in a 'lite' database.  And also, what do you expect SQLite to do if it 
notes such a movement ?  Return an error message ?  It already does that.

Besides which, you can write it yourself externally to SQLite: use FSEvent on 
the folder your database is in checking for kFSEventStreamEventFlagItemRemoved, 
and act accordingly.  No need to put that facility inside SQLite.  If you get 
it working neatly, it could be released for the Mac/SQLite community at large.  
Personally, I agree with Stephen: if my users want to mess with my app's data 
files they deserve everything they get.

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

Re: [sqlite] executing queries on normalized database

[Simon Slavin]

On 11 Nov 2013, at 2:35pm, dd  wrote:

> Got it.
> 
> Can I conclude this way: Foreign keys works pretty well when
> application deals with parent keys only. But, application may need to
> execute more queries when dealing with child key/tables.

My feeling is that a lot of this logic needs to be done at the application 
level because at that level you have more flexibility and can interact better 
with the user.  Foreign keys can be useful to enforce business-logic.  But not 
a substitute for programming which should check these things first.

However, that's just my own view.  I've seen rational articles written that 
argue that every part of business logic possible should be included in your 
database schema: constraints, foreign keys, triggers, and all.  And that your 
app should rely on those doing their jobs rather than having each app perform 
its own checks.  It's a matter of opinion.

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

[sqlite] sqlite 3.8.X query planner bug with r_tree joins(r_tree utilization not working on joins & cross join has no affect on plan with r_tree joins)?

[Martin Riša]

Hello,
We are facing performance regression on queries over r_tree tables with any new 
3.8.X sqlite version and we are sure it is connected with new versions of 
sqlite especially with new query planner and r_tree module.
We have distincted by now two bugs:

First one:
We are convienced that new version of sqlite query planner does not utilize 
queries with joins over r_tree tables in the way the r_tree module is intended 
to.
I will try to prove it on this example:

we have table T of nodes with their 2-D coordinates(X,Y) and R_tree virtual 
table R_TREE of 2-D bounding rectangles (MIN_X/Y,MIN_X/Y)
we want to select for every node from T its boung rectangles it lies in from 
R_TREE
we do it by running this statement:
select *  from T
join R_TREE on
  T.X  >= R_TREE.MIN_X  and
  T.X <= R_TREE.MAX_X and
 T.Y  >= R_TREE.MIN_Y  and
  T.Y <= R_TREE.MAX_Y

Explain query plan of such query returns different results in 3.7.X versions 
and 3.8.X  and we think that this difference is responsible for huge 
performance drops on such queries(actually our performance drops are very 
costly from minutes to days of execution times on large tables)

3.7.X explain query plan result:
ORDER DETAIL
1.SCAN TABLE T USING INTEGER PRIMARY KEY (~100 
rows)
2.SCAN TABLE R_TREE VIRTUAL TABLE INDEX 2:BaDbBc 
(~0 rows)

word interpretation : For all nodes find all rectangles where node lies in 
rectangle.

3.8.X explain query plan results:
ORDER DETAIL
1.SCAN TABLE R_TREE VIRTUAL TABLE INDEX 2:
2.SCAN TABLE T

word interpretation: For all rectangles find nodes where node lies in 
rectangle..

Actually our and everybody‘s intention using r_tree in similiar way, is to have 
3.7.X plan.

According to documentation of r_tree module
“R*Tree index is used to narrow a search down to a list of candidate objects 
and then more detailed and expensive computations are done on each candidate to 
find if the candidate truly meets the search criteria.“(source: 
http://www.sqlite.org/rtree.html )

This example is the case where sqlite 3.8.X query plan scans r_tree before any 
criteria could have been chosen.  What is in conflict with documentation 
citation and makes R_tree module unusable efficiently. Can this be fixed to 
make r-tree module usable efficiently like in prior sqlite versions?

Second one:
Cross join has no effect on query plan on join over r_tree.

In previous example when used with cross join instead of join , has no effect 
on plan in contrast to cross join on common(not virtual) table. There must be 
some bug whether in documentation(not mentioning that cross join has no effect 
over r_tree tables) or in implementation of cross join functionality in query 
planning. Can this be fixed too?

Thank you for any reply.
Best regards
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Raheel Gupta]

Ok. Thank you for your explanation everyone and for being patient with me.
I look forward to Sqlite4 in whatever features you implement :)


On Mon, Nov 11, 2013 at 8:17 PM, Richard Hipp  wrote:

> On Mon, Nov 11, 2013 at 1:40 AM, Raheel Gupta  wrote:
>
> >
> > I guess a Row level locking could be difficult but a Page Level locking
> > could be not that difficult.
> >
>
> In an anomaly-free system, page level locking is not difficult.  The
> difficulty comes when you have to recover from an application crash (due to
> a bug or a "kill -9" or a power loss or some other cause) that occurs in
> the middle of updating the page.
>
> In a client/server database engine, you have a central server that can keep
> track of page (or row) locks efficiently.  Doing the same in a serverless
> system like SQLite is rather more difficult and (as far as I can determine)
> requires debilitating run-time overhead.
> --
> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Row Level Locking as in InnoDB

[Richard Hipp]

On Mon, Nov 11, 2013 at 1:40 AM, Raheel Gupta  wrote:

>
> I guess a Row level locking could be difficult but a Page Level locking
> could be not that difficult.
>

In an anomaly-free system, page level locking is not difficult.  The
difficulty comes when you have to recover from an application crash (due to
a bug or a "kill -9" or a power loss or some other cause) that occurs in
the middle of updating the page.

In a client/server database engine, you have a central server that can keep
track of page (or row) locks efficiently.  Doing the same in a serverless
system like SQLite is rather more difficult and (as far as I can determine)
requires debilitating run-time overhead.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] executing queries on normalized database

[dd]

Got it.

Can I conclude this way: Foreign keys works pretty well when
application deals with parent keys only. But, application may need to
execute more queries when dealing with child key/tables.

Is it?

Thanks,
dd

On Mon, Nov 11, 2013 at 5:35 PM, Simon Slavin  wrote:
>
> On 11 Nov 2013, at 12:38pm, dd  wrote:
>
>> Scenario:
>>
>> sqlite> pragma foreign_keys = on;
>> sqlite> CREATE TABLE artist(artistidINTEGER PRIMARY KEY
>> AUTOINCREMENT, artistname  TEXT);
>> sqlite> CREATE TABLE track(trackid INTEGER PRIMARY KEY
>> AUTOINCREMENT, trackname   TEXT, trackartist INTEGER REFERENCES
>> artist(artistid) ON UPDATE CASCADE ON DELETE CASCADE);
>>
>> for ex: Five tracks are pointing to one artist. it needs to delete
>> track by track. For first track to four tracks, it should not delete
>> artist information from artist table. For fifth track, it should
>> delete track information along with artist information. Is it possible
>> to do with single query?
>
> You can't do this with foreign keys.  You could do it with triggers: when you 
> delete a track, check to see whether that artist still has any tracks, and if 
> not, delete the artist.
>
> But you probably shouldn't.  For example, in my own database an artist has 
> both tracks and albums.  Even if I delete the last track by an artist, they 
> may still have albums under their name.  Think of, for example, the Jools 
> Holland album where every track is a collaboration between him and someone 
> else.  And also, even if I don't have any music by a particular artist, the 
> artists still exists.
>
> So I would do clearing up of artists in a separate part of the system, 
> perhaps a monthly maintenance task, rather than having it happen 
> automatically.
>
> 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] Combining two databases into one

[Simon Slavin]

On 10 Nov 2013, at 8:27pm, David Wellman  
wrote:

> Can I just check something. When you say " Don't forget to DELETE FROM
> before you insert your new rows " I assume that you're saying to make sure
> that the target table is empty before I do the insert. Yes?

Yep, that's it.  Common mistake we've all made.  Well, okay, I made it.  And 
I'm still embarrassed about it.

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

Re: [sqlite] Row Level Locking as in InnoDB

[RSmith]

On 2013/11/11 08:40, Raheel Gupta wrote:

I guess a Row level locking could be difficult but a Page Level locking
could be not that difficult.

ATM "db level locking" :
If DB locked throw busy error
In not locked lock db, let the writer do its thing

For Page level locking (I think you could allow something like) :
Let writer write / modify pages IF not locked
ONLY If writer comes across a locked page wait for it to be released

In this way, multiple threads could do writes. Again I am not an expert but
from my little understanding this might not remove the leaness. You are the
expert.

And even I agree that Sqlite must be "lite"


It's rare to see a drum banged this hard, so let me commend you for persistance and explain a bit more what Simon and others were 
trying to enlighten you about.


SQLite does not have row-level locking because when it was conceived at the onset, and then again every time it was updated to a 
newer major version (We are at 3 now), the people who designed it sat around their desks and brainstormed about which features can 
be included, which cannot and why. Whether it be space costs or costs paid to the processing cycles or for whatever reason, they 
have decided every time that row-level locking comes at too high a price for this kind of database - much in the same way the 
F1-McLaren racing team does not add airconditioning or CD-players to their cars, even though some F1 drivers would no-doubt enjoy a 
bit of luxury.  This is not dissimilar to MySQL ISAM tables which, for much the same reasons do not offer row-level locking. Anyone 
who uses MySQL seriously would have their large Databases contain a mix of ISAM and Inno Tables - whichever tool is best for the job.


SQLite is a tool, one of the many available, one of the best, but it has a niche and a specific job. If you truly cannot live 
without row-level locking, you could well use MySQL or PostGRES etc - these are equally free. But if you need speed, local-file 
access and embeddibility (if that's a word) then you need SQLite.


I know it is easy to sit on the sideline and imagine that row-locking would be easy, or as your newest post suggests, page-level 
locking - but you have an entire working and tested structure designed in ways that specifically is NOT row/page-locking, to even 
simply change that to be able to do so, then test it and risk all the backward compatibility issues that surround it, and then 
explain to everyone how it all works a bit slower now, but at least you can lock pages... just won't work. It's like trying to 
convince the mentioned F1 team that they should really run Diesel-Engines because it is more efficient (which isn't a lie), or 
simply add 36-inch wheels to their cars, like buses have, because it lasts longer (again, not a lie).  I am using wayward examples 
to try and make it clear how your suggestions look to the engineers making the engine.


Tell you what though, if you can download a copy of the code, and implement working and stable row or page-level locking with minor 
increases in size and negligible decreases in performance, I am willing to bet they will consider implementing it (at least in 
SQLIte4) and probably make you famous for it - but I must confess, there is not a lot of anticipation.


Please know I'm not scolding in any way (if it might seem like it), only telling you this because we all fall into the trap of 
thinking things can work better easily - me too.

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

Re: [sqlite] Feature request: Better support for database file moving

[Stephan Beal]

On Mon, Nov 11, 2013 at 3:01 PM, L. Wood  wrote:

> * The program runs in sandbox mode (a requirement for all programs on
> Apple's Mac App Store), meaning that if a user moves a package, the program
> will gain read/write access to the new path but LOSE read/write access to
> the old path.
>
> This poses a problem:
>
> * A user could move the file during a transaction, i.e., between
> _prepare_v2(); and _finalize();
>

If the app is sandboxed, how does the user possibly get a chance to move
the files?


>
> It seems that SQLite needs support for this situation. Would you agree?
>

FWIW, i don't. Until you asked about this last week i had never heard of a
use case where any app needs to account for its data files being moved
while it is running.

Also note that moving works differently depending on whether the move is
across the same device or not. If it's on the same device it's just a
rename and the inode stays the same. If it's across devices then it's
basically a copy+delete.


> Until then, I guess I just have to live with the risk of either an error
> or data corruption when a user moves a document (a package).
>

How about telling your users that moving the data files while the app is
using them is "not supported"? i would think that this goes without saying,
but maybe some users need it to be explicit.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Since tyranny's the only guaranteed byproduct of those who insist on a
perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can i change the primary key?

[Igor Tandetnik]

On 11/10/2013 8:12 PM, David M. Cotter wrote:

what i did before SQL was to just tell the new song (which may have updated / corrected 
meta data) to have the old song ID (and tell the old one to have the new one), then i'd 
just delete all the "new" song ids (since they had been swapped, i'd be really 
deleting all the old songs).

so, can i do this with SQLite?


You can INSERT and UPDATE primary key fields the same way you can any 
other field - subject to uniqueness constraint, of course (at no point 
in time could there be two records with the same key, no matter how this 
key was arrived at).

--
Igor Tandetnik

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

[sqlite] Feature request: Better support for database file moving

[L. Wood]

Here is my situation and why SQLite doesn't seem to satisfy it.
It would be great to hear what some of the SQLite developers think about this.

* My Mac OS X program has a document file format of packages (which are nothing 
but glorified directories). My database file is always inside of a package. So, 
it can be assumed that all the extra files (such as -journal) will always move 
with the main database file if the user moves the document (the package).

* The program runs in sandbox mode (a requirement for all programs on Apple's 
Mac App Store), meaning that if a user moves a package, the program will gain 
read/write access to the new path but LOSE read/write access to the old path.

This poses a problem:

* A user could move the file during a transaction, i.e., between _prepare_v2(); 
and _finalize();

It seems that SQLite needs support for this situation. Would you agree?

If so, the question is how. Maybe through callback functions requesting an 
updated directory when needed? Or by internally following the changed path of 
the main database file instead of storing it in memory at the outset?

Until then, I guess I just have to live with the risk of either an error or 
data corruption when a user moves a document (a package).   
  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Can i change the primary key?

[David M. Cotter]

i've got say a music database

the unique "song ID" is the "integer primary key", used to look up the song and 
all it's data

sometimes an update of the meta data comes along, so i want to update to the 
new list, but preserve the old song IDs (so playlists that refer to them still 
link up)

what i did before SQL was to just tell the new song (which may have updated / 
corrected meta data) to have the old song ID (and tell the old one to have the 
new one), then i'd just delete all the "new" song ids (since they had been 
swapped, i'd be really deleting all the old songs).

so, can i do this with SQLite?  it was efficient because i could simply swap 
ONE integer between these records.

or, must i instead keep the song IDs the same, and swap all the other data? 

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

Re: [sqlite] Combining two databases into one

[David Wellman]

Hi Simon,
Thanks for that.
Can I just check something. When you say " Don't forget to DELETE FROM
before you insert your new rows " I assume that you're saying to make sure
that the target table is empty before I do the insert. Yes?

Cheers,
Dave


Ward Analytics Ltd - information in motion
Tel: +44 (0) 118 9740191
Fax: +44 (0) 118 9740192
www: http://www.ward-analytics.com

Registered office address: The Oriel, Sydenham Road, Guildford, Surrey,
United Kingdom, GU1 3SR
Registered company number: 3917021 Registered in England and Wales.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: 10 November 2013 18:32
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Combining two databases into one


On 10 Nov 2013, at 5:49pm, Dave Wellman  wrote:

> Ah! I may have just found the answer. If I've got databases db1 and 
> db2 attached, can I use something like 'insert into db1.t1 select * 
> from db2.t1;' (assuming that the 't1' definitions are the same !)?

That's the best way I know of to do it, bearing in mind your entire
requirements.  I don't see anything else wrong with your proposed procedure.
Don't forget to DELETE FROM before you insert your new rows.

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] executing queries on normalized database

[Simon Slavin]

On 11 Nov 2013, at 12:38pm, dd  wrote:

> Scenario:
> 
> sqlite> pragma foreign_keys = on;
> sqlite> CREATE TABLE artist(artistidINTEGER PRIMARY KEY
> AUTOINCREMENT, artistname  TEXT);
> sqlite> CREATE TABLE track(trackid INTEGER PRIMARY KEY
> AUTOINCREMENT, trackname   TEXT, trackartist INTEGER REFERENCES
> artist(artistid) ON UPDATE CASCADE ON DELETE CASCADE);
> 
> for ex: Five tracks are pointing to one artist. it needs to delete
> track by track. For first track to four tracks, it should not delete
> artist information from artist table. For fifth track, it should
> delete track information along with artist information. Is it possible
> to do with single query?

You can't do this with foreign keys.  You could do it with triggers: when you 
delete a track, check to see whether that artist still has any tracks, and if 
not, delete the artist.

But you probably shouldn't.  For example, in my own database an artist has both 
tracks and albums.  Even if I delete the last track by an artist, they may 
still have albums under their name.  Think of, for example, the Jools Holland 
album where every track is a collaboration between him and someone else.  And 
also, even if I don't have any music by a particular artist, the artists still 
exists.

So I would do clearing up of artists in a separate part of the system, perhaps 
a monthly maintenance task, rather than having it happen automatically.

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

Re: [sqlite] executing queries on normalized database

[dd]

I understand parent table to child table modifications with ON
UPDATE/ON DELETE CASCADS. It's very good. Now, I stuck with child to
parent tables query optimization after normalization.

Scenario:

sqlite> pragma foreign_keys = on;
sqlite> CREATE TABLE artist(artistidINTEGER PRIMARY KEY
AUTOINCREMENT, artistname  TEXT);
sqlite> CREATE TABLE track(trackid INTEGER PRIMARY KEY
AUTOINCREMENT, trackname   TEXT, trackartist INTEGER REFERENCES
artist(artistid) ON UPDATE CASCADE ON DELETE CASCADE);

for ex: Five tracks are pointing to one artist. it needs to delete
track by track. For first track to four tracks, it should not delete
artist information from artist table. For fifth track, it should
delete track information along with artist information. Is it possible
to do with single query?

On Mon, Nov 11, 2013 at 3:08 PM, dd  wrote:
> Hi,
>
> Thanks Simon.
>
> sqlite> pragma foreign_keys = on;
> sqlite> CREATE TABLE artist(artistidINTEGER PRIMARY KEY
> AUTOINCREMENT, artistname  TEXT);
> sqlite> CREATE TABLE track(trackid INTEGER PRIMARY KEY
> AUTOINCREMENT, trackname   TEXT, trackartist INTEGER REFERENCES
> artist(artistid) ON UPDATE CASCADE ON DELETE CASCADE);
>
> How do I insert  trackname as "That's Amore" and artistname as "Dean
> Martin" with single query in artist and track tables?
>
> dd
>
>
>
>
> On Sun, Nov 10, 2013 at 6:45 PM, Simon Slavin  wrote:
>>
>> On 10 Nov 2013, at 10:54am, dd  wrote:
>>
>>>  I have two tables in my database.
>>>
>>>  After applying normalization, there are twelve tables with foreign
>>> key support.
>>>
>>>  For insert/delete operations, it has to execute twelve queries
>>> instead of two. Is it recommended way?
>>
>> You should not have to write twelve queries in your own programming.  If you 
>> have set up your FOREIGN KEYs correctly, SQLite should be doing that for 
>> you.  The foreign keys facility should have allowed you to move some of your 
>> business model from your own programming into SQLite, which should be 
>> dealing with it in one place in a consistent way.
>>
>>>  In delete case, do always need to check in parent table whether
>>> child key refers to any other rows in parent table?
>>
>> Again, if you have set up your foreign keys correctly, SQLite should be 
>> doing the checking or deleting for you.  Whether SQLite will prevent you 
>> from deleting, or delete rows from other tables, depends which of these 
>> actions you have set up:
>>
>> 
>>
>> 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] executing queries on normalized database

[Michael Schlenker]

Am 11.11.2013 12:57, schrieb Simon Slavin:
> 
> On 11 Nov 2013, at 11:08am, dd  wrote:
> 
>> How do I insert  trackname as "That's Amore" and artistname as
>> "Dean Martin" with single query in artist and track tables?
> 
> Sorry, I don't know a way to do that.  I hope someone else has a way
> but it's normal to have your software figure out that you need to
> create artist/publisher/whatever rows first.

C.J. Date offers a way to do it, if I'm remembering right, in his
'Tutorial D' language, (as mentioned in 'Database in Depth'), but it is
usually not possible to do it in simple standard SQL.

One way to do it in SQL with some upfront preparations, would be to
define a special writeable view and arrange a complex INSERT triggers to
just insert part of the data into one table and part of the table into a
second table. But that kind of defeats the normalization...
Might work for your simple structures, but gets pretty ugly if things
are more complex.

Michael


-- 
Michael Schlenker
Software Architect

CONTACT Software GmbH   Tel.:   +49 (421) 20153-80
Wiener Straße 1-3   Fax:+49 (421) 20153-41
28359 Bremen
http://www.contact.de/  E-Mail: m...@contact.de

Sitz der Gesellschaft: Bremen
Geschäftsführer: Karl Heinz Zachries, Ralf Holtgrefe
Eingetragen im Handelsregister des Amtsgerichts Bremen unter HRB 13215
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] executing queries on normalized database

[Simon Slavin]

On 11 Nov 2013, at 11:08am, dd  wrote:

> How do I insert  trackname as "That's Amore" and artistname as "Dean
> Martin" with single query in artist and track tables?

Sorry, I don't know a way to do that.  I hope someone else has a way but it's 
normal to have your software figure out that you need to create 
artist/publisher/whatever rows first.

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

Re: [sqlite] executing queries on normalized database

[dd]

Hi,

Thanks Simon.

sqlite> pragma foreign_keys = on;
sqlite> CREATE TABLE artist(artistidINTEGER PRIMARY KEY
AUTOINCREMENT, artistname  TEXT);
sqlite> CREATE TABLE track(trackid INTEGER PRIMARY KEY
AUTOINCREMENT, trackname   TEXT, trackartist INTEGER REFERENCES
artist(artistid) ON UPDATE CASCADE ON DELETE CASCADE);

How do I insert  trackname as "That's Amore" and artistname as "Dean
Martin" with single query in artist and track tables?

dd




On Sun, Nov 10, 2013 at 6:45 PM, Simon Slavin  wrote:
>
> On 10 Nov 2013, at 10:54am, dd  wrote:
>
>>  I have two tables in my database.
>>
>>  After applying normalization, there are twelve tables with foreign
>> key support.
>>
>>  For insert/delete operations, it has to execute twelve queries
>> instead of two. Is it recommended way?
>
> You should not have to write twelve queries in your own programming.  If you 
> have set up your FOREIGN KEYs correctly, SQLite should be doing that for you. 
>  The foreign keys facility should have allowed you to move some of your 
> business model from your own programming into SQLite, which should be dealing 
> with it in one place in a consistent way.
>
>>  In delete case, do always need to check in parent table whether
>> child key refers to any other rows in parent table?
>
> Again, if you have set up your foreign keys correctly, SQLite should be doing 
> the checking or deleting for you.  Whether SQLite will prevent you from 
> deleting, or delete rows from other tables, depends which of these actions 
> you have set up:
>
> 
>
> 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] report bugs-update

[Luís Simão]

Where did you get Windows version?

Did you compile it using SQLITE_ENABLE_UPDATE_DELETE_LIMIT?

From Compilation Options For
SQLite
:

"*SQLITE_ENABLE_UPDATE_DELETE_LIMIT*

This option enables an optional ORDER BY and LIMIT clause on
UPDATE
 and DELETE  statements.

If this option is defined, then it must also be defined when using the
'lemon' tool to generate a parse.c file. Because of this, this option may
only be used when the library is built from source, not from the
amalgamation  or from the
collection of pre-packaged C files provided for non-Unix like platforms on
the website."


 Com os melhores cumprimentos,
 Luís Simão


2013/11/10 BULUSLI 

>hello Sir,I don't Know this isn't a bug
>In my windows machine I used "update test set id=0 limit 0,1" and In my
> linux machine use"update test set id=0 limit 0,1" answer is different.
>In windows,he is tell me"Error: near "limit": syntax error" But in
> linux is right! So this is aBug?  (my mother tongue isn't English,so)
>
> Version:
>   my Windows: SQLite 3.8.1 2013-10-17 12:57:35
> c78be6d786c19073b3a6730dfe3fb1be54f5657a
>   my Linux :  SQLite 3.7.15.2 2013-01-09 11:53:05
> c0e09560d26f0a6456be9dd3447f5311eb4f238f
> ___
> 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