Re: [sqlite] Building an SQLite Extension: How to check if a table exists in the database?

[Igor Tandetnik]

Wei Song  wrote:
> I'm developing an SQLite extension which uses a function to set data into a 
> table. I'd like to know how to check if a table
> exists in a database? 

Just prepare the statement that you are going to use to write the data, and 
handle any errors.
-- 
Igor Tandetnik

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

Re: [sqlite] SIGBUS error in case of disk full with WAL mode

[Yongil Jang]

Dear all,

Thank you for your help.

I tried -DSQLITE_SHM_DIRECTORY="/dev/shm" option for make file.
It works very well.
(One line should be added to make "/dev/shm" directory before calling file
open)

But, I didn't tried to use locking mode because of multiple processes can
use same sqlite database at a same time.

I have one more question.

Following sentences are found on sqlite source.
And, there is a comment about database corruption.
Could I get more detailed explanation about this?


** can be enabled at compile-time using -DSQLITE_SHM_DIRECTORY="/dev/shm"
** or the equivalent.  The use of the SQLITE_SHM_DIRECTORY compile-time
** option results in an incompatible build of SQLite;  *builds of SQLite*
*** that with differing SQLITE_SHM_DIRECTORY settings attempt to use the*
*** same database file at the same time, database corruption will likely*
*** result*. The SQLITE_SHM_DIRECTORY compile-time option is considered
** "unsupported" and may go away in a future SQLite release.


Best wishes,
Jang.

2012/3/9 Scott Hess 

> On Thu, Mar 8, 2012 at 9:28 AM, Pavel Ivanov  wrote:
> >> Question:  Does anybody know of a better way to get memory shared among
> >> processes other than to create a fake file and mmap() it?  Are there
> some
> >> magic options to mmap() (perhaps Linux-only options) that prevent it
> from
> >> actually writing to disk?
> >
> > Why don't you use shm_open() instead of a real file? I'm not sure
> > though how it behaves with chroot jail.
>
> I do not recall the full semantics of shm_open(), but I _think_ that
> it shares the sysv shared-memory problem where the memory sticks
> around until explicitly deleted.  Using a fake file with mmap() will
> create a segment which only exists until the last process using it
> goes away.  This can become a resource issue on some systems.  Also,
> the name would have to be carefully constructed to prevent conflicts,
> as the namespace may not be the same as the file path namespace (even
> path-like names may have different length restrictions).  Like maybe
> base64(hash(canonical_path(dbpath))).
>
> BTW, the SQLite docs indicate that as of 3.7.4, you can arrange to use
> an exclusive mode which allows WAL to work without shm.  That may be a
> reasonable approach for some subset of users with this kind of
> problem.
>
> -scott
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best LINQ provider for SQLite?

[Gour]

On Fri, 9 Mar 2012 19:49:32 +
Rob Richardson 
wrote:

> If there is a way to search the archives, could someone please show
> me?

http://dir.gmane.org/gmane.comp.db.sqlite.general


Sincerely,
Gour


-- 
Whatever action a great man performs, common men follow. And 
whatever standards he sets by exemplary acts, all the world pursues.

http://atmarama.net | Hlapicina (Croatia) | GPG: 52B5C810


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

Re: [sqlite] Building an SQLite Extension: How to check if a table exists in the database?

[Peter Aronson]

Er, what do you mean by C Syntax -- SQL isn't C?  If you meant ANSI SQL syntax, 
you could use:

select count(*) from sqlite_master where type='table' and 
lower(name)=lower('tablename');

Instead.  But since you're accessing a metadata table that only exists in 
SQLite, this isn't particularly more standard.

Peter




From: Wei Song 2 
To: sqlite-users@sqlite.org
Sent: Fri, March 9, 2012 12:36:58 PM
Subject: Re: [sqlite] Building an SQLite Extension: How to check if a table 
exists in the database?


I'd like get the result in C Syntax. How can I do it?


Peter Aronson-3 wrote:
> 
> You got to be a bit careful there, SQLite isn't case-sensitive about table 
> names, but sqlite_master will preserve the case from the CREATE TABLE 
> statement.  Instead of 
> 
> 
> select count(*) from sqlite_master where type='table' and
> name='tablename';
> 
> You need something like 
> 
> select count(*) from sqlite_master where type='table' and name='tablename' 
> collate nocase;
> 
> Peter
> 
> 
> 
> 
> From: Roger Andersson 
> To: sqlite-users@sqlite.org
> Sent: Fri, March 9, 2012 11:52:31 AM
> Subject: Re: [sqlite] Building an SQLite Extension: How to check if a
> table 
> exists in the database?
> 
> On 03/09/12 19:39, Wei Song wrote:
>> Hello,
>>
>> I'm developing an SQLite extension which uses a function to set data into
>> a 
>>table. I'd like to know how to check if a table exists in a database?
>>
> It's hard to say what you need but maybe
> select count(*) from sqlite_master where type='table' and
> name='tablename';
> /Roger
> ___
> 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
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Building-an-SQLite-Extension%3A-How-to-check-if-a-table-exists-in-the-database--tp33473784p33474119.html

Sent from the SQLite mailing list archive at Nabble.com.

___
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

[sqlite] Best LINQ provider for SQLite?

[Rob Richardson]

Greetings!

What is the best LINQ provider for SQLite?

I'm sorry for posting a question that must have been asked several times in the 
past, but the archives of this group do not appear to be searchable.  If there 
is a way to search the archives, could someone please show me?

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

Re: [sqlite] Building an SQLite Extension: How to check if a table exists in the database?

[Wei Song 2]

I'd like get the result in C Syntax. How can I do it?


Peter Aronson-3 wrote:
> 
> You got to be a bit careful there, SQLite isn't case-sensitive about table 
> names, but sqlite_master will preserve the case from the CREATE TABLE 
> statement.  Instead of 
> 
> 
> select count(*) from sqlite_master where type='table' and
> name='tablename';
> 
> You need something like 
> 
> select count(*) from sqlite_master where type='table' and name='tablename' 
> collate nocase;
> 
> Peter
> 
> 
> 
> 
> From: Roger Andersson 
> To: sqlite-users@sqlite.org
> Sent: Fri, March 9, 2012 11:52:31 AM
> Subject: Re: [sqlite] Building an SQLite Extension: How to check if a
> table 
> exists in the database?
> 
> On 03/09/12 19:39, Wei Song wrote:
>> Hello,
>>
>> I'm developing an SQLite extension which uses a function to set data into
>> a 
>>table. I'd like to know how to check if a table exists in a database?
>>
> It's hard to say what you need but maybe
> select count(*) from sqlite_master where type='table' and
> name='tablename';
> /Roger
> ___
> 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
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Building-an-SQLite-Extension%3A-How-to-check-if-a-table-exists-in-the-database--tp33473784p33474119.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Building an SQLite Extension: How to check if a table exists in the database?

[Wei Song 2]

Yes, thank you.
/Wei


Roger Andersson-2 wrote:
> 
> On 03/09/12 19:39, Wei Song wrote:
>> Hello,
>>
>> I'm developing an SQLite extension which uses a function to set data into
>> a table. I'd like to know how to check if a table exists in a database?
>>
> It's hard to say what you need but maybe
> select count(*) from sqlite_master where type='table' and
> name='tablename';
> /Roger
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/Building-an-SQLite-Extension%3A-How-to-check-if-a-table-exists-in-the-database--tp33473784p33474051.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Building an SQLite Extension: How to check if a table exists in the database?

[Peter Aronson]

You got to be a bit careful there, SQLite isn't case-sensitive about table 
names, but sqlite_master will preserve the case from the CREATE TABLE 
statement.  Instead of 


select count(*) from sqlite_master where type='table' and name='tablename';

You need something like 

select count(*) from sqlite_master where type='table' and name='tablename' 
collate nocase;

Peter




From: Roger Andersson 
To: sqlite-users@sqlite.org
Sent: Fri, March 9, 2012 11:52:31 AM
Subject: Re: [sqlite] Building an SQLite Extension: How to check if a table 
exists in the database?

On 03/09/12 19:39, Wei Song wrote:
> Hello,
>
> I'm developing an SQLite extension which uses a function to set data into a 
>table. I'd like to know how to check if a table exists in a database?
>
It's hard to say what you need but maybe
select count(*) from sqlite_master where type='table' and name='tablename';
/Roger
___
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] Building an SQLite Extension: How to check if a table exists in the database?

[Roger Andersson]

On 03/09/12 19:39, Wei Song wrote:

Hello,

I'm developing an SQLite extension which uses a function to set data into a 
table. I'd like to know how to check if a table exists in a database?


It's hard to say what you need but maybe
select count(*) from sqlite_master where type='table' and name='tablename';
/Roger
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Building an SQLite Extension: How to check if a table exists in the database?

[Wei Song]

Hello,

I'm developing an SQLite extension which uses a function to set data into a 
table. I'd like to know how to check if a table exists in a database?

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

Re: [sqlite] INSERT OR REPLACE

[Alek Paunov]

On 09.03.2012 17:39, Nico Williams wrote:

Perhaps a Google summer of code project or something?


Perhaps.


Unfortunately not :-(. Google people have made a mistake in the past - 
looking for formal criteria to guarantee the openness of the initiative 
they included the rule that mentoring organizations are required to 
release code under the OSI [1] approved license:


"As long as your project can provide mentors and is releasing code under 
an Open Source Initiative approved license" [2]


which in principle is OK, but leaves the projects releasing code as 
Public Domain out of account :-(. (confirmed by Cat Allman, Open Source 
Programs Office, Google, 2012-02-13).


[1] http://www.opensource.org/licenses/alphabetical
[2] http://www.google-melange.com/document/show/gsoc_program/google
/gsoc2012/faqs#mentoring_org_type
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] import a txt into sqlite

[Larry Brasfield]

On March 9, YAN HONG YE wrote:

I have a txt file
myteam.txt:

ID;name;sex;match;chinese;english;phy;chem;total
CY001;cred;male;54;101;101;55;99;695
CY002;rose;female;55;105;95;119;101;693
CY003;helen;male;110;95;65;50;50;613
CY004;jack;female;55;65;110;51;50;641
CY005;tonny;male;55;55;111;55;55;514

when I import the txt to my sqlite table, could I import the the first row 
contains columnnames, not data.
like odbc "HDR=Yes"


You posted this 8 days ago, and got a useful reply then.  Do you expect 
more?


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

Re: [sqlite] Strange difference between sqlite 3.7.3 and 3.7.10

[Richard Hipp]

On Fri, Mar 9, 2012 at 1:51 AM, Alan Chandler wrote:

>
>  Can you scrub the data (replace numbers with values from random(), and all
>> strings with random text?)  If not, email the database directly to me.
>>
>>
>>  I have done some randomisation and sent it to you privately
>

Based on your data, I have devised a small self-contained test case here:
http://www.sqlite.org/src/tktview/b7c8682cc17f3290


-- 
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] INSERT OR REPLACE

[Nico Williams]

On Fri, Mar 9, 2012 at 6:05 AM, Ryan Johnson
 wrote:
> Don't forget stored procedures.

I didn't need to pollute the language with procedural extensions in
order to make my case, which is why I didn't mention stored procedures
:)   I figured using stored procs in my argument was going to detract
from or at least distract people.  Also, I don't care for stored
procedures -- at least nowhere near as much as I care about the issue
in this thread.

As for stored procedures, I may not need them but you can emulate them
via INSTEAD OF triggers on VIEWs.  Stored procedure parameters then
are just the columns of NEW :)  and local variables can be rows in
TEMP tables.  You don't need IF ... THEN ... ELSE since you can
achieve the same result with suitable WHERE ... AND  on all
the statements that go in the THEN/ELSE bodies.  Looping can be
achieved via recursive triggers or by using a table source to drive
the equivalent of a loop in a SELECT.  Calling stored procedures then
becomes a matter of INSERTing into the corresponding VIEWs.  A true
IF..THEN..ELSE would make execution more efficient, but you can always
cache the results of complex condition expressions in a TEMP table.

This is the reason that I don't need stored procedures: if/when I find
myself needing them I can just emulate them and it's only a slight
inconvenience to have to do that.

> I'd dearly love to have even a simple stored procedure language in
> sqlite3... the VM supports *everything* it needs, even function call/return.

And recursion, since it needs to in order to support recursive
triggers.  This means that recursive queries are also feasible with
the SQLite3 VM.  And you can emulate recursive queries via emulated
stored procedures as described above :)

> Triggers already connect multiple statements, joins open and iterate over
> cursors, etc. There's just no parser support to expose the functionality in
> a nice way.

One could take the grammar for SQL from SQLite3 and write a re-writer
that maps stored procedures onto SQLite3 as I describe above.  This
would require no changes to SQLite3.  It would require changes to
apps, but you might create wrappers around sqlite3_prepare*() and
sqlite3_exec*() to avoid changing apps.

> Perhaps a Google summer of code project or something?

Perhaps.

>> But, really, it's quite a hole that I can INSERT INTO table SELECT
>>   but I can't UPDATE table.
>
> ++, though I don't know of a non-kludgy syntax that would make this happen

Allowing JOINs would be nice.  But yes, INSERT..SELECT is much nicer
than UPDATE, which leads me to:

>
>> INSERT OR REPLACE is almost a very good alternative.
>
> The semantics are subtle enough to worry me, just based on what we've
> discussed so far.

If it had the semantics that I proposed earlier then I think I'd
prefer INSERT OR REPLACE to enhancing UPDATE, if that were the choice.
 The reason is that INSERT ... SELECT has much nicer syntax than
UPDATE, and also, if you're generating code then having fewer grammars
to generate code for would be nice.  But ideally we get both, enhanced
INSERT OR REPLACE semantics and enhanced UPDATE.

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

Re: [sqlite] INSERT OR REPLACE

[Marc L. Allen]

> That's why you don't make a DBMS (SQL) do the job of a programming
> language.  Use your programming language to to retrieve the values you
> need to make your calculations.  Then use your programming language to
> figure out the new values and execute the UPDATE commands needed to
> make the changes.
> 
> SQL is not a programming language.  Trying to use it like one leads to
> ridiculous contortions which take longer to run than doing it all in
> SQL would take.  There's nothing magically fast about SQL, it still has
> to do the same amount of work in the end.

While I tend to side with others that have responding to this, there is some 
sense to what you're saying. SQL isn't a particularly good procedural language, 
but for set manipulation, it's excellent.
 
Updating records *is* one of SQL's primary jobs.  The idea that I cannot update 
multiple fields on one record based on the values of another record without 
going through several non-SQL programming hoops or including the same 
sub-select for every field is a little absurd.  A decent query optimizer can 
create far more efficient code with an UPDATE FROM or Oracle syntax.

Marc

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

Re: [sqlite] What's the best way to organize this database?

[Larry Knibb]

One thing to note is that there is an implicit "rowid" for each row
already. Making an explicit ID column just adds an alias to it.

http://www.sqlite.org/lang_createtable.html#rowid

Cheers,
Larry


On 9 March 2012 21:29, Simon Slavin  wrote:
>
> On 8 Mar 2012, at 5:25pm, John Salerno  wrote:
>
>> On Mar 8, 7:46 am, Simon Slavin  wrote:
>>
>>> Yes, this would be the 'normalised' form.  Something like
>>>
>>> artists: id, name, sortOrder
>>> songs: id, artistID, title
>>> plays: id, songID, playDate, playTime
>>
>> Ok, between you and Larry's last post, it's starting to make a lot
>> more sense (except I don't intend to track "listeners"). I see now I
>> need separate tables for the artists and songs, but just a few
>> questions:
>>
>> 1. What is the sortOrder entry in the artists table you created?
>
> Sooner or later you're going to want to make a list which is sorted in artist 
> order.  And you're going to want to list 'The Beatles' (if you have any taste 
> at all).  But if you list artists in name order they'd come in the Ts, not 
> the Bs because 'The' is part of the band name.  So having a "sortOrder" field 
> lets you enter 'The Beatles' as the band name but 'Beatles, The' in the 
> sortOrder field.  Similarly, 'Elvis Presley' should be sorted under 'P', not 
> 'E'.  Of course you could enter 'Presley, Elvis' instead of the name in the 
> order humans would say it, but I consider that a way of saying "The computer 
> is more important that you so humans must adapt to the computer way.", which 
> is an attitude I despise.
>
>> 2. Would there need to be an extra entries I should be aware of before
>> I begin, or is the table outline you created complete?
>
> I'm not quite sure what you wanted all this for to start with, but the above 
> structure has enough in it to let you get started.  You can add more tables 
> and/or columns later.
>
>> 3. What is the purpose of the id entry for the plays table? Is it used
>> much, or is it just for completeness?
>
> It's more a habit than anything.  Having an independent "id" column lets you 
> refer to individual rows by something which should never, under any 
> circumstances, need to change or refer to anything outside the database.  For 
> instance, Suppose you started off listing playTime as local current time of a 
> play, and later you needed to convert all your times to a specific TimeZone, 
> and remove any change for Summer Time.  You would be changing existing data 
> in the table in a way that meant any reference to that 'play' would have to 
> be changed too.  But by giving every table an 'id' column defined as INTEGER 
> PRIMARY KEY (see
>
> http://sqlite.org/faq.html#q1
>
> ) you know you will always have a consistent reference for any piece of data, 
> even if one of the fields you care about needs to be changed.  So people who 
> work a lot with SQL databases tend to do this, or something like it, out of 
> experience without even thinking 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] What's the best way to organize this database?

[Simon Slavin]

On 8 Mar 2012, at 5:25pm, John Salerno  wrote:

> On Mar 8, 7:46 am, Simon Slavin  wrote:
> 
>> Yes, this would be the 'normalised' form.  Something like
>> 
>> artists: id, name, sortOrder
>> songs: id, artistID, title
>> plays: id, songID, playDate, playTime
> 
> Ok, between you and Larry's last post, it's starting to make a lot
> more sense (except I don't intend to track "listeners"). I see now I
> need separate tables for the artists and songs, but just a few
> questions:
> 
> 1. What is the sortOrder entry in the artists table you created?

Sooner or later you're going to want to make a list which is sorted in artist 
order.  And you're going to want to list 'The Beatles' (if you have any taste 
at all).  But if you list artists in name order they'd come in the Ts, not the 
Bs because 'The' is part of the band name.  So having a "sortOrder" field lets 
you enter 'The Beatles' as the band name but 'Beatles, The' in the sortOrder 
field.  Similarly, 'Elvis Presley' should be sorted under 'P', not 'E'.  Of 
course you could enter 'Presley, Elvis' instead of the name in the order humans 
would say it, but I consider that a way of saying "The computer is more 
important that you so humans must adapt to the computer way.", which is an 
attitude I despise.

> 2. Would there need to be an extra entries I should be aware of before
> I begin, or is the table outline you created complete?

I'm not quite sure what you wanted all this for to start with, but the above 
structure has enough in it to let you get started.  You can add more tables 
and/or columns later.

> 3. What is the purpose of the id entry for the plays table? Is it used
> much, or is it just for completeness?

It's more a habit than anything.  Having an independent "id" column lets you 
refer to individual rows by something which should never, under any 
circumstances, need to change or refer to anything outside the database.  For 
instance, Suppose you started off listing playTime as local current time of a 
play, and later you needed to convert all your times to a specific TimeZone, 
and remove any change for Summer Time.  You would be changing existing data in 
the table in a way that meant any reference to that 'play' would have to be 
changed too.  But by giving every table an 'id' column defined as INTEGER 
PRIMARY KEY (see

http://sqlite.org/faq.html#q1

) you know you will always have a consistent reference for any piece of data, 
even if one of the fields you care about needs to be changed.  So people who 
work a lot with SQL databases tend to do this, or something like it, out of 
experience without even thinking about it.

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

[sqlite] Database not readable anymore

[Dirk Geppert]

Dear Ladies and gentlemen, I am sending you an sqlite file,
that is just not readable anymore.

All the tools like the shell (sqlite3.exe) or the sqlite3_analyzer.exe
cannot open the database. The error is: disk image is malformed
So I cant dump or restore something.

Using an hex viewer, the image looks quit good.

So I guess there is only a small inconsistency that is not
handled correctly, and therefore leads to a complete stop.

I hope you can help me?

Best regards from Dresden, Germany.

Dirk Geppert

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

Re: [sqlite] What's the best way to organize this database?

[John Salerno]

On Mar 8, 7:46 am, Simon Slavin  wrote:

> Yes, this would be the 'normalised' form.  Something like
>
> artists: id, name, sortOrder
> songs: id, artistID, title
> plays: id, songID, playDate, playTime

Ok, between you and Larry's last post, it's starting to make a lot
more sense (except I don't intend to track "listeners"). I see now I
need separate tables for the artists and songs, but just a few
questions:

1. What is the sortOrder entry in the artists table you created?

2. Would there need to be an extra entries I should be aware of before
I begin, or is the table outline you created complete?

3. What is the purpose of the id entry for the plays table? Is it used
much, or is it just for completeness?

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

Re: [sqlite] Doing an UPDATE after an INSERT

[Simon Slavin]

On 9 Mar 2012, at 11:27am, Christoph P.U. Kukulies  wrote:

> I'm using System.Data.SQLite and the following problem comes up:
> 
> I'm doing an INSERT of some columns in a TABLE. I have to determine now if 
> the INSERT was successful. I'm doing this by putting the INSERT in a try { do 
> the insert...and try the UPDATE } catch { do nothing};
> 
> In case the INSERT fails because of a KEY clash the UPDATE isn't executed 
> either.
> But in case the INSERT is successful I would like to do the UPDATE right 
> afterwards on the same ROW that has been inserted last.
> You may argue, why I don't try the full INSERT right away. Reason: a large 
> BLOB has to be read inand the statement be prepared.
> And I could safe that, if the INSERT would fail anyway.
> 
> Is there something like UPDATE into last ROW that has  been inserted?

This function:

http://www.sqlite.org/c3ref/last_insert_rowid.html

will give you the rowid of the most recent row inserted.  So you could use it 
before your INSERT, then use it again after your INSERT.  If it didn't change, 
you know the INSERT failed.  And if it did change, you already have the value 
of the rowid you need for your UPDATE command.

If the System.Data.SQLite library doesn't implement this function, then you can 
access the same value using the "last_insert_rowid()" SQL function as 
documented here:

http://www.sqlite.org/lang_corefunc.html#last_insert_rowid

but this will of course, be slower.

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

Re: [sqlite] INSERT OR REPLACE

[Ryan Johnson]

On 08/03/2012 6:15 PM, Nico Williams wrote:

On Thu, Mar 8, 2012 at 4:37 PM, Simon Slavin  wrote:

That's why you don't make a DBMS (SQL) do the job of a programming language.  
Use your programming

language to to retrieve the values you need to make your calculations.
  Then use your programming language to figure out the new values and
execute the UPDATE commands needed to make the changes.

Lots of people want to and do use SQL to do a lot of what a regular
programming language can do.

Some are even taking SQL and applying it to new problems.  See ql.io
for example.

One very nice thing about using SQL to do more is that you can do a
lot of prototyping from a SQL shell and then start on the scaffolding
around it in whatever other language, and if you do it this way you'll
probably find yourself doing a lot of programming in SQL.


SQL is not a programming language.  Trying to use it like one leads to 
ridiculous contortions which take longer to run than doing it all in SQL would 
take.  There's nothing magically fast about SQL, it still has to do the same 
amount of work in the end.

SQL was not, originally, a Turing complete language (unless one were
to consider cross self joins of large tables for driving iteration as
good enough), but nowadays it pretty much is, therefore it  is a
programming language.  The language supported by SQLite3 is Turing
complete, FYI, since you have conditionals (WHERE, WHEN, CASE, ...)
and iteration (e.g., via recursive triggers).  And the SQLite3 VM most
assuredly is Turing complete.

Don't forget stored procedures.

I'd dearly love to have even a simple stored procedure language in 
sqlite3... the VM supports *everything* it needs, even function 
call/return. Triggers already connect multiple statements, joins open 
and iterate over cursors, etc. There's just no parser support to expose 
the functionality in a nice way.


Perhaps a Google summer of code project or something?


But, really, it's quite a hole that I can INSERT INTO table SELECT
  but I can't UPDATE table.

++, though I don't know of a non-kludgy syntax that would make this happen


INSERT OR REPLACE is almost a very good alternative.
The semantics are subtle enough to worry me, just based on what we've 
discussed so far.


Ryan

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

Re: [sqlite] Doing an UPDATE after an INSERT

[Taleeb Anwar]

In Sql Server 2005+ you can retrieve the primary key of the last inserted
record by using
SELECT @@IDENTITY

This can then be used to update the row

Thanks & Regards
Taleeb Anwar

*Hum Mashriq Ke "Miskeenon" Ka Dil Maghrib men Ja Atka Hai!!*




On Fri, Mar 9, 2012 at 4:57 PM, Christoph P.U. Kukulies
wrote:

> I'm using System.Data.SQLite and the following problem comes up:
>
> I'm doing an INSERT of some columns in a TABLE. I have to determine now if
> the INSERT was successful. I'm doing this by putting the INSERT in a try {
> do the insert...and try the UPDATE } catch { do nothing};
>
> In case the INSERT fails because of a KEY clash the UPDATE isn't executed
> either.
> But in case the INSERT is successful I would like to do the UPDATE right
> afterwards on the same ROW that has been inserted last.
> You may argue, why I don't try the full INSERT right away. Reason: a large
> BLOB has to be read inand the statement be prepared.
> And I could safe that, if the INSERT would fail anyway.
>
> Is there something like UPDATE into last ROW that has  been inserted?
>
> --
> Christoph
>
>
>
> __**_
> 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

[sqlite] Doing an UPDATE after an INSERT

[Christoph P.U. Kukulies]

I'm using System.Data.SQLite and the following problem comes up:

I'm doing an INSERT of some columns in a TABLE. I have to determine now 
if the INSERT was successful. I'm doing this by putting the INSERT in a 
try { do the insert...and try the UPDATE } catch { do nothing};


In case the INSERT fails because of a KEY clash the UPDATE isn't 
executed either.
But in case the INSERT is successful I would like to do the UPDATE right 
afterwards on the same ROW that has been inserted last.
You may argue, why I don't try the full INSERT right away. Reason: a 
large BLOB has to be read inand the statement be prepared.

And I could safe that, if the INSERT would fail anyway.

Is there something like UPDATE into last ROW that has  been inserted?

--
Christoph



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

Re: [sqlite] Side-by-side with checked out content

[Jos Groot Lipman]

Sorry, wrong group. Reposted in the Fossil group 

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

Re: [sqlite] TEXT PRIMARY KEY

[Christoph P.U. Kukulies]

Am 04.03.2012 15:22, schrieb Kit:

2012/3/4 Christoph P.U. Kukulies:

CREATE TABLE dir(filename text, md5sum text, size int, content blob,
PRIMARY KEY(filename,md5sum,size));

Coming back to your suggestion using PRIMARY KEY(filename,md5sum,size), how
would I address this PRIMARY KEY, example:

I'm building a table
CREATE TABLE candidate (?,client TEXT, md5sum TEXT, basename TEXT, size INT,
...some other stuff )
The ? should stand for the - is that FOREIGN KEY? - key in the dir-TABLE
which is formed of filename,md5sum,size.
Christoph

Foreign key is (basename,md5sum,size).

Your example shows that a composite key in this case is possible, but
it is not appropriate. Use simple key md5sum. Make two tables.

Attribute "size" (part of primary key) is redundant.


Kit,

I'm coming back again on your suggestion. Maybe you can elaborate, why
size is redundant and of what I should make two tables?

Let me describe the problem again:

I'm going recursively through a directory tree collection information on 
all files, that is,


CREATE TABLE instance  (
  path TEXT,
  md5sum TEXT,
  basename TEXT,
  size INT,
  creation_date TEXT,
  last_write_time TEXT,
  );

CREATE TABLE resource (
  size INT,
  name TEXT,
  md5sum TEXT,
  data BLOB,
  primary key(basename,md5sum,size)
);

What makes the contents of two files equal (so that their contents can 
be represented by the same resource) ?


md5sum = md5sum
size = size
name = name (allowing different names sharing the same resource is 
another thing - I might do that but do

not want at the moment).

So when I'm now visiting every file I will check whether the file with 
its contents has already been entered into the

TABLE resource.

My problem: what do I have to change in TABLE instance so that I can use 
it to determine whether the key is already

in the resource TABLE?

Would that be a FOREIGN KEY? And how would I do that in syntax?
If I need FOREIGN KEY, would I have to enable that in SQLite somehow (at 
compile time)?


Thanks,

Christoph








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

Re: [sqlite] Side-by-side with checked out content

[Jos Groot Lipman]

Is it possible to see a side-by-side difference between the last checkin and
the currently changed file on disk? It would be a great alternative to
fossil diff and fossil gdiff
 
This would be much like the wiki preview using /doc/ckout/
--
Jos Groot Lipman
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ANN: SQLite Code Factory 12.3 released

[SQL Maestro Group]

Hi!

SQL Maestro Group announces the release of SQLite Code Factory 12.3, a 
powerful Windows GUI solution aimed at the SQLite queries and scripts 
development.


The new version is immediately available for download at
http://www.sqlmaestro.com/products/sqlite/codefactory/

Please also note that before March 18 you can get an additional 20% discount 
on purchasing any our product suite including recently launched Web 
Developer bundles.

http://www.sqlmaestro.com/purchase/

Top 5 New features
=

1. SQL Editor: unicode support, text change markers, code snippets, and 
improved code folding appearance.

2. Creation of views from Visual Query Builder.
3. Data import from any database accessible via ODBC driver / OLE DB 
provider.

4. Managing BLOB data as PDF.
5. Spanish translation.

In addition to this, several bugs have been fixed and some other minor 
improvements and corrections have been made. Full press-release is available 
at:

http://www.sqlmaestro.com/news/company/code_factory_advanced_to_12_3/

Background information:
---
SQL Maestro Group offers complete database admin, development and management 
tools for MySQL, SQL Server, PostgreSQL, Oracle, DB2, SQLite, SQL Anywhere, 
Firebird and MaxDB providing the highest performance, scalability and 
reliability to meet the requirements of today's database applications.


Sincerely yours,
The SQL Maestro Group Team
http://www.sqlmaestro.com

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

Re: [sqlite] A possible bug probably partially fixed before

[Max Vlasov]

On Fri, Mar 9, 2012 at 12:04 PM, Max Vlasov  wrote:

> 3.6.10 -
> ... actualrndid never gets higher than 900 (wrong)
>

Sorry, correction, never gets lower than 900

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

[sqlite] A possible bug probably partially fixed before

[Max Vlasov]

Hi,

I experimented with random numbers recently (with an older version of
sqlite) and notice a strange behavior, I tried to reproduce this with the
latest version and noticed that it was probably partially fixed, but still
returns wrong results for my query.

The simplest table possible

CREATE TABLE [TestTable] (
[Id] INTEGER PRIMARY KEY AUTOINCREMENT
)

Let's fill it with numbers from 1 to 1000. For example by repeating
  INSERT INTO TestTable (id) values ((Select max(id) from TestTable) + 1)



The following query is the problem one

Select
  id,
  MinId,
  MaxId,
  abs(random() % (MaxId-MinId + 1)) as goodRangeRnd,
  (MinId + abs(random() % (MaxId-MinId + 1))) as actualrndid
from
  TestTable
left join
(Select
  (select Min(Id) from TestTable) as MinId,
  (select Max(Id) from TestTable) as MaxId
) StatTable
where
  id<=actualrndid
order by
  id desc
limit
 10


So actualrndid here should produce random values between Min(Id) and
Max(Id) and the query should return 10 rows prior or equal to this value.
But the results are different depending on the version
(goodRangeRnd is here just for testing purposes and to show the difference
with "problem" actualrndid. It's similar to actualrndid but without (MinId
+) member )

3.6.10 -
 goodRangeRnd is good in every row (jumps in the full range (1..1000))
 actualrndid never gets higher than 900 (wrong)
 the query returns correct results if actualrndid is considered correct

3.7.10
 goodRangeRnd is good in every row
 actualrndid is good this time (jumps in the full range (1..1000))
 the query returns bad results similar to the version above, but this time
it is wrong if we take (id<=actualrndid) into account. So for example,
there's id=911 and actualrndid=3 in the same row

So looks on the way from 3.6.10 to 3.7.10 something was really fixed
related to this issue, but seems like not everything.

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