Re: [sqlite] What could be the cause of a zero length database file?

[Rowan Worth]

On 25 May 2016 at 19:48, dandl  wrote:

> This particular program is run as part of a series of
> test cases, and the setup for the test involves deleting the database file
> and then running the program in a batch file. Since about Windows 7 the
> shell does not wait for a file to be deleted before launching the next
> process, and if that process uses a file that is being deleted there is a
> race condition to see what happens next. Specifically it seems to be
> possible to open a "shadow copy" of the file which can persist while the
> original file is deleted. I don't understand exactly what happens, but I
> suspect this might be my problem.
>

Having an sqlite connection open on a deleted file can also happen in posix
land... Section 2.4 of "How to Corrupt" claims it can't happen on windows,
I guess that's technically true as in this scenario the file was deleted
before being opened, but it might be worth mentioning anyway (assuming the
mechanism can be understood).

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

Re: [sqlite] Messages posted on Nabble not getting to list

[Jean-Christophe Deschamps]

At 03:46 27/05/2016, you wrote:
If SQLite goes this route, I will probably (as with the others) stop 
reading it too.


Seconded.

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

Re: [sqlite] Messages posted on Nabble not getting to list

[Balaji Ramanathan]

Interesting.  I have no idea what a facebook-style interface looks like
since I don't have a facebook account.  The main advantage of forums, and I
follow a bunch of them, is that I choose when I want to stop my regular day
job and be distracted by them rather than emails coming in and distracting
me all the time.  And a forum can be set up to email you every time someone
posts something if you want to follow along in real-time.  But if the main
contributors prefer a mailing list then I can get used to it too.

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

[sqlite] DB Partitioning and Performance

[Michael Hari]

I have a database that contains 2014,2015 and 2016’s worth of data broken down 
by year and quarter. In total, this db was 2.36 GB. Because of a replication 
requirement where the DB has to be under 2 GB, I’ve split the DB into 3 smaller 
databases by year (2014.db,2015.db,2016.db).

I would need to access at most two databases in one query for a year over year 
calculation over 6 quarters. Do I have the same performance as having one table 
with all the data if I write my sql this way?

Attach 2014.db as 2014
Attach 2015.db as 2015

(..Calculation..) FROM (SELECT * FROM 2014.datatable  UNION ALL SELECT * FROM 
2015.datatable)

You can assume the separate databases have the same indexing as the single db, 
where it’s indexed by year and quarter.

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

Re: [sqlite] Messages posted on Nabble not getting to list

[R Smith]

On 2016/05/27 1:48 AM, Balaji Ramanathan wrote:

Thank you, Simon and Igor.  I have put in a support request at Nabble, let
us see what happens.

Why does SQLite use a mailing list instead of a proper web-hosted
forum/bulletin board type setup?  That way it would be possible to set up
sub-forums for different interests (SQL, Adiministratioin, Bug Reports,
etc., etc.) and readers don't have to wade through all the stuff they are
not interested in just to participate in the stuff they are interested in.
I understand that mailing lists were the norm in the 1990's, but times have
changed.


We have only really one interest, and that is SQLite - there are no 
sub-interests, though some threads have been known to expand into quite 
a conversation.


An e-mail based list is by far preferable to most developers. It's only 
normal users who prefer facebook style interfaces. Also, we read and 
answer (or at least pay attention to) most questions, if it were that we 
only saw the questions inside our interest groups, it would defeat the 
purpose. I wouldn't even bother to log on. Now I get messages in my 
inbox - much more convenient.


That said, I am not sure this is the reason the forum remains as is, it 
is just my opinion on the matter, if a rather strongly held opinion. In 
fact, I will say that I used to be on a few mailing lists that 
"upgraded" to web-interfacy forums. If SQLite goes this route, I will 
probably (as with the others) stop reading it too.



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

Re: [sqlite] Messages posted on Nabble not getting to list

[Stephen Chrzanowski]

Times have changed, because people get 'bright ideas' that just don't work.

I don't want to look at 30 different forums that might claim "The official
forum of SQLite" or whatever.  Also, I don't have to worry about security
different forum services offer.  Everything comes right to my email, and I
don't have to go out to a forum to sign in and then do my post.  If I'm not
interested in an email, I'll just hit the NEXT button.

On Thu, May 26, 2016 at 7:48 PM, Balaji Ramanathan <
balaji.ramanat...@gmail.com> wrote:

> Thank you, Simon and Igor.  I have put in a support request at Nabble, let
> us see what happens.
>
> Why does SQLite use a mailing list instead of a proper web-hosted
> forum/bulletin board type setup?  That way it would be possible to set up
> sub-forums for different interests (SQL, Adiministratioin, Bug Reports,
> etc., etc.) and readers don't have to wade through all the stuff they are
> not interested in just to participate in the stuff they are interested in.
> I understand that mailing lists were the norm in the 1990's, but times have
> changed.
>
> Balaji Ramanathan
>
> On Thu, May 26, 2016 at 4:53 PM, Balaji Ramanathan <
> balaji.ramanat...@gmail.com> wrote:
>
> > Hi,
> >
> > I keep getting bounce messages from the sqlite mailing list when I
> try
> > to post replies from nabble (http://sqlite.1065341.n5.nabble.com/).  The
> > message shows up on nabble, but never seems to get to the mailing list
> and
> > is not included in the next day's digest of messages sent to the list.
> It
> > looks like Nabble is sending the messages to sqlite-us...@sqlite.org
> > rather than sqlite-users@mailinglists.sqlite.org.  Does somebody need to
> > change settings in nabble to fix this or do I have to do something
> > different to get this to work correctly?
> >
> >
> > My latest bounce message is as below:
> >
> > This is the mail system at host mbob.nabble.com.
> >
> > I'm sorry to have to inform you that your message could not
> > be delivered to one or more recipients. It's attached below.
> >
> > For further assistance, please send mail to postmaster.
> >
> > If you do so, please include this problem report. You can
> > delete your own text from the attached returned message.
> >
> >The mail system
> >
> > : host mail.sqlite.org[67.18.92.124] said: 554
> > 5.7.1
> > : Recipient address rejected: Access denied
> > (in
> > reply to RCPT TO command)
> >
> > Final-Recipient: rfc822; sqlite-us...@sqlite.org
> > Original-Recipient: rfc822;sqlite-us...@sqlite.org
> > Action: failed
> > Status: 5.7.1
> > Remote-MTA: dns; mail.sqlite.org
> > Diagnostic-Code: smtp; 554 5.7.1 : Recipient
> > address
> > rejected: Access denied
> >
> >
> > -- Forwarded message --
> > From: Balaji Ramanathan 
> > To: sqlite-us...@sqlite.org
> > Cc:
> > Date: Thu, 26 May 2016 13:18:14 -0700 (MST)
> > Subject: Re: I need to merge tables from two databases
> > Try this:
> >
> > insert into Customer(id)
> > select case when b.id = id then b.id||'A' else b.id end from customerb b
> >
> > Balaji Ramanathan
> >
> >
> >
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] I need to merge tables from two databases

[Balaji Ramanathan]

Your case statement syntax is completely off.  Also, the concatenation
operator in sqlite is ||, not +.

Try this:

insert into Customer(id)
select case when b.id = id then b.id||'A' else b.id end from customerb b

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

Re: [sqlite] Messages posted on Nabble not getting to list

[Balaji Ramanathan]

Thank you, Simon and Igor.  I have put in a support request at Nabble, let
us see what happens.

Why does SQLite use a mailing list instead of a proper web-hosted
forum/bulletin board type setup?  That way it would be possible to set up
sub-forums for different interests (SQL, Adiministratioin, Bug Reports,
etc., etc.) and readers don't have to wade through all the stuff they are
not interested in just to participate in the stuff they are interested in.
I understand that mailing lists were the norm in the 1990's, but times have
changed.

Balaji Ramanathan

On Thu, May 26, 2016 at 4:53 PM, Balaji Ramanathan <
balaji.ramanat...@gmail.com> wrote:

> Hi,
>
> I keep getting bounce messages from the sqlite mailing list when I try
> to post replies from nabble (http://sqlite.1065341.n5.nabble.com/).  The
> message shows up on nabble, but never seems to get to the mailing list and
> is not included in the next day's digest of messages sent to the list.  It
> looks like Nabble is sending the messages to sqlite-us...@sqlite.org
> rather than sqlite-users@mailinglists.sqlite.org.  Does somebody need to
> change settings in nabble to fix this or do I have to do something
> different to get this to work correctly?
>
>
> My latest bounce message is as below:
>
> This is the mail system at host mbob.nabble.com.
>
> I'm sorry to have to inform you that your message could not
> be delivered to one or more recipients. It's attached below.
>
> For further assistance, please send mail to postmaster.
>
> If you do so, please include this problem report. You can
> delete your own text from the attached returned message.
>
>The mail system
>
> : host mail.sqlite.org[67.18.92.124] said: 554
> 5.7.1
> : Recipient address rejected: Access denied
> (in
> reply to RCPT TO command)
>
> Final-Recipient: rfc822; sqlite-us...@sqlite.org
> Original-Recipient: rfc822;sqlite-us...@sqlite.org
> Action: failed
> Status: 5.7.1
> Remote-MTA: dns; mail.sqlite.org
> Diagnostic-Code: smtp; 554 5.7.1 : Recipient
> address
> rejected: Access denied
>
>
> -- Forwarded message --
> From: Balaji Ramanathan 
> To: sqlite-us...@sqlite.org
> Cc:
> Date: Thu, 26 May 2016 13:18:14 -0700 (MST)
> Subject: Re: I need to merge tables from two databases
> Try this:
>
> insert into Customer(id)
> select case when b.id = id then b.id||'A' else b.id end from customerb b
>
> Balaji Ramanathan
>
>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sync journal's directory per transaction? or until database closed? in PERSIST mode

[Howard Chu]

Graham Holden wrote:



 Original message 
From: Dan Kennedy 
Date: 26/05/2016  18:04  (GMT+00:00)
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Sync journal's directory per transaction? or until 
database closed? in PERSIST mode


On UNIX, it's possible to delete a file from the file-system while

another process has it open. In this case the other process can continue
reading and writing its file-descriptor as normal, but the data is
stored in memory only, not on disk (since the directory entry has been
deleted). Once the process exits or closes the file-descriptor, the data
is lost.

It probably doesn't affect the argument of why SQLite does what it does,
but I thought (but it's been a long time since I poked around UNIX
file-systems) that data can still be written (or read) to disk because the
i-node chain is still present, it's just that there's no name by which
another proces can access it. Presumably, the final close will release the
disk blocks of a marked-as-deleted file.


Correct. "The data is stored in memory only" is completely impractical - you 
can still write much more data than will fit in RAM. One common mistake of new 
Unix admins is to delete large log files from /var/spool when they're low on 
disk space; if they do this without telling syslog to close the files first, 
the space remains in use and can't be freed until syslog is killed and restarted.


--
  -- Howard Chu
  CTO, Symas Corp.   http://www.symas.com
  Director, Highland Sun http://highlandsun.com/hyc/
  Chief Architect, OpenLDAP  http://www.openldap.org/project/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fwd: Messages posted on Nabble not getting to list

[Igor Tandetnik]

On 5/26/2016 5:53 PM, Balaji Ramanathan wrote:

I keep getting bounce messages from the sqlite mailing list when I try
to post replies from nabble (http://sqlite.1065341.n5.nabble.com/).  The
message shows up on nabble, but never seems to get to the mailing list and
is not included in the next day's digest of messages sent to the list.  It
looks like Nabble is sending the messages to sqlite-us...@sqlite.org rather
than sqlite-users@mailinglists.sqlite.org.  Does somebody need to change
settings in nabble to fix this or do I have to do something different to
get this to work correctly?


You may want to ask in http://support.nabble.com/ . Someone there should 
have the power to update the email address. Explain the situation, point 
to http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 
which shows the new address.


I had the same problem with GMane. Emailed them, and their support fixed 
it. Like you, I'm not an admin or anything, just a regular lurker.

--
Igor Tandetnik

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

Re: [sqlite] Messages posted on Nabble not getting to list

[Simon Slavin]

On 26 May 2016, at 10:53pm, Balaji Ramanathan  
wrote:

>I keep getting bounce messages from the sqlite mailing list when I try
> to post replies from nabble 

Please post to the list itself, which is not hosted by nabble.  The address to 
join the mailing list is



If you want nabble to get things right you'll have to contact nabble.

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

Re: [sqlite] Fastest way to add many simple rows to a table?

[Dominique Pellé]

Richard Hipp  wrote:

> On 5/26/16, Eric Grange  wrote:
>>
>> I am looking for the fastest way to insert many rows to a simple table.
>
> (1) Create a prepared statement:  "INSERT INTO xyzzy VALUES(?1,?2,...)"
> (2) Run "BEGIN"
> (3) Loop over each row you want to insert, bind values to the prepared
> statement, then call sqlite3_step() and sqlite3_reset().
> (4) Run "COMMIT"
> (5) Call sqlite3_finalize() on the prepared statement to avoid a memory leak.


Additionally, "PRAGMA synchronous=off;" gives significant
speed up for insertions, if you don't mind a corrupted database
in case of system crash. See:

https://www.sqlite.org/pragma.html#pragma_synchronous

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

Re: [sqlite] Fastest way to add many simple rows to a table?

[Donald Shepherd]

On Fri, 27 May 2016 at 00:16 Eric Grange  wrote:

> Hi,
>
> I am looking for the fastest way to insert many rows to a simple table.
>
> By "simple" I mean a relation table (with just a couple integer/key fields)
> or even a single-column (temp table used for filtering as an alternative to
> a "in" literal), but I want to insert hundreds of thousandths of them as
> fast as possible.
>
> The fastest I have found so far is to use an insert from a select with
> json_each() used to provide the data.
> Using multiple "insert into", even within a prepared statement within a
> transaction is quite slower.
>
> The json_each approach would satisfy my need, but I am curious is there is
> another approach that could be suggested? (without involving a custom
> function)
>
> Eric
>

http://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite
has
a useful comparison of various techniques.  I used its comparison when
developing our product using SQLite with large numbers of records getting
inserted.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fastest way to add many simple rows to a table?

[Richard Hipp]

On 5/26/16, Eric Grange  wrote:
>
> I am looking for the fastest way to insert many rows to a simple table.

(1) Create a prepared statement:  "INSERT INTO xyzzy VALUES(?1,?2,...)"
(2) Run "BEGIN"
(3) Loop over each row you want to insert, bind values to the prepared
statement, then call sqlite3_step() and sqlite3_reset().
(4) Run "COMMIT"
(5) Call sqlite3_finalize() on the prepared statement to avoid a memory leak.

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

Re: [sqlite] Fastest way to add many simple rows to a table?

[Keith Medcalf]

Take a look at the test_intarray.c/h extension located in the main source 
directory, it may just do what you want (the array of integers is stored in 
memory, but is accessed as a table).

https://www.sqlite.org/src/artifact/870124b95ec4c645


> -Original Message-
> From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-
> boun...@mailinglists.sqlite.org] On Behalf Of Eric Grange
> Sent: Thursday, 26 May, 2016 08:15
> To: General Discussion of SQLite Database
> Subject: [sqlite] Fastest way to add many simple rows to a table?
> 
> Hi,
> 
> I am looking for the fastest way to insert many rows to a simple table.
> 
> By "simple" I mean a relation table (with just a couple integer/key
> fields)
> or even a single-column (temp table used for filtering as an alternative
> to
> a "in" literal), but I want to insert hundreds of thousandths of them as
> fast as possible.
> 
> The fastest I have found so far is to use an insert from a select with
> json_each() used to provide the data.
> Using multiple "insert into", even within a prepared statement within a
> transaction is quite slower.
> 
> The json_each approach would satisfy my need, but I am curious is there is
> another approach that could be suggested? (without involving a custom
> function)
> 
> Eric
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



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

[sqlite] Fwd: Messages posted on Nabble not getting to list

[Balaji Ramanathan]

Hi,

I keep getting bounce messages from the sqlite mailing list when I try
to post replies from nabble (http://sqlite.1065341.n5.nabble.com/).  The
message shows up on nabble, but never seems to get to the mailing list and
is not included in the next day's digest of messages sent to the list.  It
looks like Nabble is sending the messages to sqlite-us...@sqlite.org rather
than sqlite-users@mailinglists.sqlite.org.  Does somebody need to change
settings in nabble to fix this or do I have to do something different to
get this to work correctly?

My latest bounce message is as below:

This is the mail system at host mbob.nabble.com.

I'm sorry to have to inform you that your message could not
be delivered to one or more recipients. It's attached below.

For further assistance, please send mail to postmaster.

If you do so, please include this problem report. You can
delete your own text from the attached returned message.

   The mail system

: host mail.sqlite.org[67.18.92.124] said: 554
5.7.1
: Recipient address rejected: Access denied (in
reply to RCPT TO command)

Final-Recipient: rfc822; sqlite-us...@sqlite.org
Original-Recipient: rfc822;sqlite-us...@sqlite.org
Action: failed
Status: 5.7.1
Remote-MTA: dns; mail.sqlite.org
Diagnostic-Code: smtp; 554 5.7.1 : Recipient
address
rejected: Access denied


-- Forwarded message --
From: Balaji Ramanathan 
To: sqlite-us...@sqlite.org
Cc:
Date: Thu, 26 May 2016 13:18:14 -0700 (MST)
Subject: Re: I need to merge tables from two databases
Try this:

insert into Customer(id)
select case when b.id = id then b.id||'A' else b.id end from customerb b

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

Re: [sqlite] Attach Database and Database pooling

[Michael Hari]

Never mind, there was an error in my code.




On 5/26/16, 11:52 AM, "sqlite-users-boun...@mailinglists.sqlite.org on behalf 
of Michael Hari"  wrote:

>Hi All,
>
>
>
>I’m trying to preallocate about 20 sqlite connections each with the same 
>“ATTACH DB” command. I’m getting “database x already in use” errors. Is this 
>not possible to do? Can each independent X connection not have the same 
>attached DB command? If not, what’s the preferred approach?
>
>
>
>Thanks,
>
>Michael
>
>___
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers=CwIGaQ=fa_WZs7nNMvOIDyLmzi2sMVHyyC4hN9WQl29lWJQ5Y4=Za210bkAr8T8OGv7-RSux2dLtMfHgyYbTEN9AO6LXXg=HhCYpnNqJbSVCUsukMrpMTa_qXf1gNz3onTzaC3UKsQ=7BA0OPqKASv6CXn2c9tmtmY8AXKQ3Ye2cuwdz6tIYR0=
> 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sql

[avritya]

On 05/26/2016 11:59 AM, Jim Wang wrote:

hi all
a table as follow:
  id   score
  210
  3 20
  5 10
  3 20
  2 30
  2 30
how could I select the table as follow  and the count can tell me: the id 2 
hava 3,the id 3 have 2 the id 5 have 1.
   count  id   score
  3 210
  2   3 20
  15 10
   2   3 20
   3   2 30
3  2 30
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



Another solution

select b.count, a.id, a.score from t as a join (select id, count(score) 
as count from t group by id) as b on a.id=b.id;

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

[sqlite] Attach Database and Database pooling

[Michael Hari]

Hi All,

I’m trying to preallocate about 20 sqlite connections each with the same 
“ATTACH DB” command. I’m getting “database x already in use” errors. Is this 
not possible to do? Can each independent X connection not have the same 
attached DB command? If not, what’s the preferred approach?

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

Re: [sqlite] Sync journal's directory per transaction? or until database closed? in PERSIST mode

[Simon Slavin]

On 26 May 2016, at 6:04pm, Dan Kennedy  wrote:

> On UNIX, it's possible to delete a file from the file-system while another 
> process has it open. In this case the other process can continue reading and 
> writing its file-descriptor as normal, but the data is stored in memory only, 
> not on disk (since the directory entry has been deleted). Once the process 
> exits or closes the file-descriptor, the data is lost.

This is not how it's normally described.  I wouldn't normally be this finicky 
but the difference plays a part in answering the question.  The file itself 
always exists on disk, even if you have deleted the last directory entry that 
points to it (hard links under unix are additional pointers to the same file).

When a file is closed, or the process holding it open quits or crashes, Unix 
removes the entry for that file from a list of open file descriptors in memory. 
 Each time it does so, it checks to see if there are any pointers to that file 
elsewhere in the file descriptor list or on disk.  If there are no pointers 
remaining then it deletes the file by returning the list of data blocks it 
occupied to the list of free data blocks.

Sometimes the whole computer can crash while a file is in the 'no directory 
entry but a process still has it open' situation.  Clearing up after this 
happens is what makes files appear in the "lost+found" directory.

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

Re: [sqlite] [System.Data.SQLite.DLL] Cannot access sqlite database on remote computer

[Chris Locke]

Aah, thats handy.  Will try that.  Thanks for that - much appreciated.  :-)

Thanks,
Chris

On Thu, May 26, 2016 at 5:38 PM, Joe Mistachkin 
wrote:

>
> Chris Locke wrote:
> >
> > Have used system.data.sqlite.dll (1.0.67.1) for ages with no problems.
> > Recently thought, "wonder if there is a new version?" but on using it
> > (1.0.101.0), I can't access a database remotely - comes up with "unable
> to
> > open database file".  DB Browser for Sqlite opens it fine, so its not
> > corrupt.  In fact, if I go back to 1.0.67.1, it opens fine.
> >
> > Wondered if this was either 'by design' (something added since .67) or
> > something I was doing wrong?!
> >
>
> Due to the revised connection string parsing algorithm (which was revised
> to fix other issues), opening a database on a UNC path requires that the
> initial backslashes in the file name be doubled, e.g.:
>
> mypc\db\Mydb.db
>
> --
> Joe Mistachkin
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sync journal's directory per transaction? or until database closed? in PERSIST mode

[Graham Holden]

 Original message 
From: Dan Kennedy  
Date: 26/05/2016  18:04  (GMT+00:00) 
To: sqlite-users@mailinglists.sqlite.org 
Subject: Re: [sqlite] Sync journal's directory per transaction? or until 
database closed? in PERSIST mode 
 
> On UNIX, it's possible to delete a file from the file-system while 
another process has it open. In this case the other process can continue 
reading and writing its file-descriptor as normal, but the data is 
stored in memory only, not on disk (since the directory entry has been 
deleted). Once the process exits or closes the file-descriptor, the data 
is lost.

It probably doesn't affect the argument of why SQLite does what it does, but I 
thought (but it's been a long time since I poked around UNIX file-systems) that 
data can still be written (or read) to disk because the i-node chain is still 
present, it's just that there's no name by which another proces can access it. 
Presumably, the final close will release the disk blocks of a marked-as-deleted 
file.

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

Re: [sqlite] Version of the database

[Richard Hipp]

On 5/26/16, John G  wrote:
> I don't see  the '.dbinfo' command in the shell in the version supplied
> with MacOS X 10.10 (Yosemite) - 3.8.8.3.
> Was this introduced after this.

Added in 3.8.9 (2015-04-08)


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

Re: [sqlite] Sync journal's directory per transaction? or until database closed? in PERSIST mode

[Dan Kennedy]

On 05/25/2016 10:54 PM, 박병언 wrote:

First of all, my English skill is not good. So please forgive me if my
sentences are rude.

I saw this is for anonymous user to report bug. I'm not sure If I need to
write down my personal information. So I didn't write my name. If I need
to, I'll send this information.

I am a Computer Science Master student in South Korea. I'm doing a research
to enhance the SQLite performance.



In PERSIST mode, journal is still remain. But the red code is always
generated in each transaction. That is, journal file descriptor is always
closed at the end of transaction and reopen at the start of next
transaction(I think this is a bug).
Do you think sync journal's directory per transaction is correct?
If this opinion is wrong, would you mind if I know why
 1. the directory must sync per transaction?
 2. PERSIST mode must close the journal file descriptor?




On UNIX, it's possible to delete a file from the file-system while 
another process has it open. In this case the other process can continue 
reading and writing its file-descriptor as normal, but the data is 
stored in memory only, not on disk (since the directory entry has been 
deleted). Once the process exits or closes the file-descriptor, the data 
is lost.


This means that if a connection using "PRAGMA journal_mode = PERSIST" 
keeps the journal file open while the database is unlocked (i.e. between 
transactions), some other process using "PRAGMA journal_mode = DELETE" 
might come along and delete the journal file while our "journal_mode = 
PERSIST" process still has it open. In this case if the "journal_mode = 
PERSIST" process then tries to execute another transaction and there is 
a power failure halfway through, there will be no journal file on disk 
following system recovery. Database corruption.


For this reason SQLite always closes the journal file at the end of a 
transaction on UNIX. On windows, where it is not possible to delete a 
file if another process has it open, SQLite holds the journal file open 
between transactions.


Dan.



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

Re: [sqlite] [System.Data.SQLite.DLL] Cannot access sqlite database on remote computer

[Joe Mistachkin]

Chris Locke wrote:
>
> Have used system.data.sqlite.dll (1.0.67.1) for ages with no problems.
> Recently thought, "wonder if there is a new version?" but on using it
> (1.0.101.0), I can't access a database remotely - comes up with "unable to
> open database file".  DB Browser for Sqlite opens it fine, so its not
> corrupt.  In fact, if I go back to 1.0.67.1, it opens fine.
> 
> Wondered if this was either 'by design' (something added since .67) or
> something I was doing wrong?!
>  

Due to the revised connection string parsing algorithm (which was revised
to fix other issues), opening a database on a UNC path requires that the
initial backslashes in the file name be doubled, e.g.:

mypc\db\Mydb.db

--
Joe Mistachkin

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

Re: [sqlite] Version of the database

[Simon Slavin]

On 26 May 2016, at 5:31pm, John G  wrote:

> Was this introduced after this.

Yes, it's a very recent new thing.

> I know Apple is a bit slow in updating, but I can't use a personally
> compiled verion.

Download the precompiled version from the "Precompiled Binaries" section on the 
download page.

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

Re: [sqlite] Version of the database

[John G]

I don't see  the '.dbinfo' command in the shell in the version supplied
with MacOS X 10.10 (Yosemite) - 3.8.8.3.
Was this introduced after this.
I know Apple is a bit slow in updating, but I can't use a personally
compiled verion.
John G



On 5 May 2016 at 23:42, R Smith  wrote:

> The file header contains the SQLite version that most recently modified
> the schema. You can see this using the cli, but not a pragma.
>
> If you are willing to dig a bit, you can retrieve it by reading the first
> 100 bytes or so from the file and examining the 4 bytes at offset 96. It's
> a big-endian 32-bit integer containing the version, and also another 32-bit
> integer (4-byte) value just prior at offset 92 is a counter of how many
> changes were made since using that library version. Together these can be
> quite useful information.
>
> The Integer value of the version would be a number like this: 3012034 -
> starting with a 3 always for SQLite3 and then the next 3 digits the minor
> version (12 in the example) and the last 3 the release (34 in the example).
>
> For more information on values stored in the header - see here:
> https://www.sqlite.org/fileformat2.html#database_header
>
> Cheers,
> Ryan
>
>
> On 2016/05/05 6:22 PM, Cecil Westerhof wrote:
>
>> 2016-05-05 18:15 GMT+02:00 Cecil Westerhof :
>>
>> I know how to get the version of the running version of SQLite, but is
>>> there a way to get the version with which the database was created?
>>>
>>> ​With the command line program I can get it with:
>>  .dbinfo
>> one of the things it gives is:
>>  software version:3008010
>> so it is 3.8.10,but would it also be possible to get in my Java program?
>>
>>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Fastest way to add many simple rows to a table?

[r . a . nagy]

Use transactions.

Or batch mode.

Indeed, when we .dump a table from the sqlite3 console, one will see a very 
fast way to use the former.



Sent from my iPhone

> On May 26, 2016, at 10:15 AM, Eric Grange  wrote:
> 
> Hi,
> 
> I am looking for the fastest way to insert many rows to a simple table.
> 
> By "simple" I mean a relation table (with just a couple integer/key fields)
> or even a single-column (temp table used for filtering as an alternative to
> a "in" literal), but I want to insert hundreds of thousandths of them as
> fast as possible.
> 
> The fastest I have found so far is to use an insert from a select with
> json_each() used to provide the data.
> Using multiple "insert into", even within a prepared statement within a
> transaction is quite slower.
> 
> The json_each approach would satisfy my need, but I am curious is there is
> another approach that could be suggested? (without involving a custom
> function)
> 
> Eric
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL / SQLite for Beginners

[r . a . nagy]

Loved the interview. Thanks for the offset.

I used to be a principle at Informix. ... Tiz a small world!

FWIW, since it's inception, S.Q.L has been pronounced allot like CICS. 

In as much as master Hipp says that the Ite is the most hip way to say things 
(pun intended) - and that using is more important than saying, I think that we 
will stick with that classic "kicks" Tao by pronouncing SQL - as classically 
pronounced - by tenured S.Q.L professionals. 

I just don't want students to be laughed at by db folks - Will explain more on 
the video-up.


Sent from my iPhone

> On May 26, 2016, at 10:05 AM, jungle Boogie  wrote:
> 
>> On 26 May 2016 at 05:49, R.A. Nagy  wrote:
>> Would this be the preferred pronunciation?
>> 
>> https://youtu.be/hB54p_Xh37M
> 
> That goes a bit too fast but it sounds right to my ears.
> 
> Listen to this interview:
> https://changelog.com/201/
> 
> If you tune in around 17:20 seconds, they'll talk about the name and
> it's pronunciation.
> 
> keep up the great work with the tutorials and I'll agree with the
> others--narrate the videos as you did very well the first time.
> 
> 
>> 
>> Thanks in advance,
>> 
>> -Rn
> 
> 
> 
> 
> -- 
> ---
> inum: 883510009027723
> sip: jungleboo...@sip2sip.info
> xmpp: jungle-boo...@jit.si
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sync journal's directory per transaction? or until database closed? in PERSIST mode

[박병언]

First of all, my English skill is not good. So please forgive me if my
sentences are rude.

I saw this is for anonymous user to report bug. I'm not sure If I need to
write down my personal information. So I didn't write my name. If I need
to, I'll send this information.

I am a Computer Science Master student in South Korea. I'm doing a research
to enhance the SQLite performance.



=
static int unixSync(sqlite3_file *id, int flags){
  int rc;
  unixFile *pFile = (unixFile*)id;

  int isDataOnly = (flags_SYNC_DATAONLY);
  int isFullsync = (flags&0x0F)==SQLITE_SYNC_FULL;

  /* Check that one of SQLITE_SYNC_NORMAL or FULL was passed */
  assert((flags&0x0F)==SQLITE_SYNC_NORMAL
  || (flags&0x0F)==SQLITE_SYNC_FULL
  );

  /* Unix cannot, but some systems may return SQLITE_FULL from here. This
  ** line is to test that doing so does not cause any problems.
  */
  SimulateDiskfullError( return SQLITE_FULL );

  assert( pFile );
  OSTRACE(("SYNC%-3d\n", pFile->h));
  rc = full_fsync(pFile->h, isFullsync, isDataOnly);
  SimulateIOError( rc=1 );
  if( rc ){
storeLastErrno(pFile, errno);
return unixLogError(SQLITE_IOERR_FSYNC, "full_fsync", pFile->zPath);
  }

  /* Also fsync the directory containing the file if the DIRSYNC flag
  ** is set.  This is a one-time occurrence.  Many systems (examples: AIX)
  ** are unable to fsync a directory, so ignore errors on the fsync.
  */
  if( pFile->ctrlFlags & UNIXFILE_DIRSYNC ){
int dirfd;
OSTRACE(("DIRSYNC %s (have_fullfsync=%d fullsync=%d)\n", pFile->zPath,
HAVE_FULLFSYNC, isFullsync));
rc = osOpenDirectory(pFile->zPath, );
if( rc==SQLITE_OK ){
  full_fsync(dirfd, 0, 0);
  robust_close(pFile, dirfd, __LINE__);
}else{
  assert( rc==SQLITE_CANTOPEN );
  rc = SQLITE_OK;
}
pFile->ctrlFlags &= ~UNIXFILE_DIRSYNC;
  }
  return rc;
}
=


In PERSIST mode, journal is still remain. But the red code is always
generated in each transaction. That is, journal file descriptor is always
closed at the end of transaction and reopen at the start of next
transaction(I think this is a bug).

=
rc = sqlite3_open("test.db", );
  if( rc ){
fprintf(stderr, "Can't open db: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
return(1);
  }

  sqlite3_exec(db, "PRAGMA journal_mode=PERSIST", NULL, NULL, );
  for(i=0;i<100;i++){
sprintf(buf1, "insert into t1 values(%d, 't1-data-%d')", i, i);
sqlite3_exec(db, buf1, NULL, 0, );
  }
  sqlite3_close(db);
=
This is the part of my test file. This code insert 0 to 100 values in the
table t1 with PERSIST mode. Until the "test.db" closed, each insert
statement open the journal file descriptor(each insert command pass
through the red code).

>From the red code documentation, the code is one time occurrence for fsync
directory. I am not sure if this one time occurrence means one time per one
transaction(one insert command) or one time until test.db close.

*I think one time journal directory sync and no close journal file
descriptor until the test.db close is enough.*

Even though there is an accident like power disconnection, sqlite code can
recognize the directory information loss and reopen the database file and
the journal file. I get this result after I remove the red code and run
with this test code in Linux. I supposed ctrl + c during program running is
transaction interruption. After interruption and rerun the program, I
checked the database can call the table t1 contents by select statement.

Do you think sync journal's directory per transaction is correct?
If this opinion is wrong, would you mind if I know why
1. the directory must sync per transaction?
2. PERSIST mode must close the journal file descriptor?

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

[sqlite] ":memory:" path does not seem to work on Windows anymore

[Kirill Müller]

Hi


In the R interface to SQLite [1], we observe that opening a database 
with ":memory:" does not work anymore on Windows (both 32- and 64-bit 
versions), possibly related to an upgrade from sqlite 3.8.6 to 3.11.1. 
Using "file::memory:" works on 3.11.1, but not for 3.8.6 . The 
sqlite3_open_v2() function returns SQLITE_CANTOPEN, and is called with 
(":memory:", a valid pointer to a sqlite3*, SQLITE_RWC, NULL) according 
to code inspection. Please advise.



Best regards

Kirill


[1] https://github.com/rstats-db/RSQLite
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Fastest way to add many simple rows to a table?

[Eric Grange]

Hi,

I am looking for the fastest way to insert many rows to a simple table.

By "simple" I mean a relation table (with just a couple integer/key fields)
or even a single-column (temp table used for filtering as an alternative to
a "in" literal), but I want to insert hundreds of thousandths of them as
fast as possible.

The fastest I have found so far is to use an insert from a select with
json_each() used to provide the data.
Using multiple "insert into", even within a prepared statement within a
transaction is quite slower.

The json_each approach would satisfy my need, but I am curious is there is
another approach that could be suggested? (without involving a custom
function)

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

[sqlite] [system.data.sqlite.dll] Old versions required (between 1.0.67 and 1.0.101)

[Chris Locke]

Has anyone got an older copy / know of where I can download versions of
system.data.sqlite.dll from version 1.0.67?  1.0.67 worked perfectly, but
after using the latest 1.0.101, I can't open remote databases.  I'd like to
use the latest 'working' version, but don't know if there is a 'historical'
archive...


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

[sqlite] [System.Data.SQLite.DLL] Can not access sqlite database on remote computer

[Chris Locke]

After a bit of advice / 'it might be fixed in the next version' help.

Have used system.data.sqlite.dll (1.0.67.1) for ages with no problems.
Recently thought, "wonder if there is a new version?" but on using it
(1.0.101.0), I can't access a database remotely - comes up with "unable to
open database file".  DB Browser for Sqlite opens it fine, so its not
corrupt.  In fact, if I go back to 1.0.67.1, it opens fine.

Wondered if this was either 'by design' (something added since .67) or
something I was doing wrong?!


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

Re: [sqlite] SQL / SQLite for Beginners

[jungle Boogie]

On 26 May 2016 at 05:49, R.A. Nagy  wrote:
> Would this be the preferred pronunciation?
>
>  https://youtu.be/hB54p_Xh37M
>

That goes a bit too fast but it sounds right to my ears.

Listen to this interview:
https://changelog.com/201/

If you tune in around 17:20 seconds, they'll talk about the name and
it's pronunciation.

keep up the great work with the tutorials and I'll agree with the
others--narrate the videos as you did very well the first time.


>
> Thanks in advance,
>
> -Rn




-- 
---
inum: 883510009027723
sip: jungleboo...@sip2sip.info
xmpp: jungle-boo...@jit.si
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL / SQLite for Beginners

[R.A. Nagy]

Would this be the preferred pronunciation?

 https://youtu.be/hB54p_Xh37M


Thanks in advance,

-Rn


On Wed, May 25, 2016 at 1:55 PM, R.A. Nagy  wrote:

> Excellent suggestion.
>
> I have an SQLite forJava eLearning title presently under way (JDBC &
> Hibernate) - can salvage a section we have on Views for re-use on the
> YouTube playlist, as well.
>
> Thanks for the inspiration,
>
> -Rn
>
>
> On Wed, May 25, 2016 at 1:49 PM, Chris Brody 
> wrote:
>
>> Another piece of feedback: I think it would be people to have access to
>> the
>> presentation slides and samples.
>>
>> Having JOIN for beginners is great. I think it would be cool to have VIEWs
>> for beginners as well. These are major benefits over NoSQL.
>> On May 22, 2016 1:29 PM, "R.A. Nagy"  wrote:
>>
>> > All,
>> >
>> > A recent convert to using SQLite, I have put together a series of
>> videos on
>> > learning SQL, as well as covering the basics of using SQLite.
>> >
>> >
>> https://www.youtube.com/playlist?list=PLItP5KoawLqkPV2jqAVCH79fZGO5k0Uzy
>> >
>> > Would this be the proper mailing list / way to let new-users learn more
>> > about these videos? If so - and in as much as forum members here field
>> > allot of new-user questions - then any suggestions for improving the
>> > presentations would also be welcome.
>> >
>> >
>> > Thanks in advance,
>> >
>> > Randall Nagy
>> > President, Soft9000.com
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@mailinglists.sqlite.org
>> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> >
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Typo in the docs

[Igor Korot]

Hi,
Page https://www.sqlite.org/see/doc/trunk/www/readme.wiki

[quote]

Do decrypt a database do this:

   .rekey old-key ""
[/quote]

I think it should be "To decrypt..."

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

Re: [sqlite] Data visible in clear text when db file is opened in notepad++

[Simon Slavin]

On 26 May 2016, at 12:52pm, Agarwal, Rajesh  wrote:

> Is there a way to avoid showing text columns in clear text when the database 
> file is opened in notepad++?

Encrypt the database.



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

Re: [sqlite] Finding a single row

[Simon Slavin]

On 26 May 2016, at 12:17pm, Ertan Küçükoğlu  wrote:

> Two or more rows return is an error on user definition. I will popup a
> message in this case.

Actually you can test for that just before you're about to insert a new row 
into the table.  So rather than return an error when someone is searching for a 
code you can return an error instead of corrupting your FIYATKODLARI table.

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

[sqlite] Data visible in clear text when db file is opened in notepad++

[Agarwal, Rajesh]

Is there a way to avoid showing text columns in clear text when the database 
file is opened in notepad++?

Thanks
Rajesh Agarwal


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

Re: [sqlite] Finding a single row

[R Smith]

On 2016/05/26 1:17 PM, Ertan Küçükoğlu wrote:

Hi Simon,

Two or more rows return is an error on user definition. I will popup a
message in this case. Thanks for the example, I didn't know <= can be used
also for strings.


I think Simon's example said LIMIT 1 at the end, did you use that?
The ORDER BY and LIMIT is very important to the success of that query. 
If used correctly, it is impossible to get more than 1 row.





Regards,
Ertan Küçükoğlu

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon
Slavin
Sent: Thursday, May 26, 2016 1:55 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Finding a single row


On 26 May 2016, at 11:44am, Ertan Küçükoğlu 
wrote:


I need to find a single row in my table which begins as the complete
card number, or I need to know no match exists in my table.

What happens if two rows match ?

Ignoring that question for a while, what you're looking for is the row which
sorts immediately before the complete card code you're searching for.

SELECT Kodu FROM FIYATKODLARI WHERE Kodu <= '' ORDER BY Kodu
DESC LIMIT 1

In your programming langauge take a look at the value returned and see if it
is the same as the first n characters of the value you're searching for.  If
it is, you have a match.  If not, you don't.

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

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


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

Re: [sqlite] Finding a single row

[Ertan Küçükoğlu]

Hi Simon,

Two or more rows return is an error on user definition. I will popup a
message in this case. Thanks for the example, I didn't know <= can be used
also for strings.

Regards,
Ertan Küçükoğlu

-Original Message-
From: sqlite-users-boun...@mailinglists.sqlite.org
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Simon
Slavin
Sent: Thursday, May 26, 2016 1:55 PM
To: SQLite mailing list 
Subject: Re: [sqlite] Finding a single row


On 26 May 2016, at 11:44am, Ertan Küçükoğlu 
wrote:

> I need to find a single row in my table which begins as the complete 
> card number, or I need to know no match exists in my table.

What happens if two rows match ?

Ignoring that question for a while, what you're looking for is the row which
sorts immediately before the complete card code you're searching for.

SELECT Kodu FROM FIYATKODLARI WHERE Kodu <= '' ORDER BY Kodu
DESC LIMIT 1

In your programming langauge take a look at the value returned and see if it
is the same as the first n characters of the value you're searching for.  If
it is, you have a match.  If not, you don't.

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

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

Re: [sqlite] Finding a single row

[Simon Slavin]

On 26 May 2016, at 11:44am, Ertan Küçükoğlu  wrote:

> I need to find a single row in my table which begins as
> the complete card number, or I need to know no match exists in my table.

What happens if two rows match ?

Ignoring that question for a while, what you're looking for is the row which 
sorts immediately before the complete card code you're searching for.

SELECT Kodu FROM FIYATKODLARI WHERE Kodu <= '' ORDER BY Kodu DESC 
LIMIT 1

In your programming langauge take a look at the value returned and see if it is 
the same as the first n characters of the value you're searching for.  If it 
is, you have a match.  If not, you don't.

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

[sqlite] Finding a single row

[Ertan Küçükoğlu]

Hi,

I have a table where I keep some part from the beginning of discount card
number. Length of beginning is variable. User provides a complete card
number at runtime, I need to find a single row in my table which begins as
the complete card number, or I need to know no match exists in my table.

I might do it coding in my application, but that would be way too slow
compared to an SQL statement I assume.

Table is as follows.
CREATE TABLE IF NOT EXISTS FIYATKODLARI(
 Kodu   Char(30) COLLATE NOCASE NOT NULL PRIMARY KEY
)WITHOUT ROWID;

It contains something like:
Kodu

SA
SB
SSC
SAA
SBDE
8694
125

Complete card number may be something like:
SB1
SA25
SA325
SB1054
SAA23
86941005
12504

Below didn't work for me because complete card number is longer, and I do
lack SQL knowledge:
select * from FIYATKODLARI where Kodu like 'SB1%'

Any help is appreciated.

Thanks.
Ertan Küçükoğlu



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

Re: [sqlite] sql

[nomad]

On Thu May 26, 2016 at 02:29:50PM +0800, Jim Wang wrote:
> hi all
>a table as follow:
>  id   score
>  210
>  3 20
>  5 10
>  3 20
>  2 30
>  2 30

Converting that into SQL we have:

CREATE TABLE a(
id INTEGER,
score INTEGER
);

INSERT INTO a VALUES(2,10);
INSERT INTO a VALUES(3,20);
INSERT INTO a VALUES(5,10);
INSERT INTO a VALUES(3,20);
INSERT INTO a VALUES(2,30);
INSERT INTO a VALUES(2,30);

> how could I select the table as follow  and the count can tell me:
> the id 2 hava 3,the id 3 have 2 the id 5 have 1.

To calculate the number of occurances of each id you can use the
following:

SELECT
id,
COUNT(*) AS id_count
FROM
a
GROUP BY
id
ORDER BY
id
;

id  id_count  
--  --
2   3 
3   2 
5   1 

>   count  id   score
>  3210
>  2320
>  1510
>  2320
>  3230
>  3230

I do not see any possibility of ordering rows the same way you have.
However to obtain the equivalent set you could use a sub query or a
common table expression (CTE):

CTE:

WITH x
AS (
SELECT
a.id,
COUNT(*) AS id_count
FROM
a
GROUP BY
a.id
)
SELECT
x.id_count,
a.id,
a.score
FROM
a
INNER JOIN
x
ON
x.id = a.id
ORDER BY
a.id,
a.score
;

id_countid  score 
--  --  --
3   2   10
3   2   30
3   2   30
2   3   20
2   3   20
1   5   10

Sub query:

SELECT
x.id_count,
a.id,
a.score
FROM
a
INNER JOIN
(SELECT
a.id,
COUNT(*) AS id_count
FROM
a
GROUP BY
a.id
) x
ON
x.id = a.id
ORDER BY
a.id,
a.score
;

Someone with greater knowledge than mine could probably provide a
correlated sub-query as well.

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

Re: [sqlite] sql

[R Smith]

On 2016/05/26 8:29 AM, Jim Wang wrote:

hi all
a table as follow:
  id   score
  210
  3 20
  5 10
  3 20
  2 30
  2 30
how could I select the table as follow  and the count can tell me: the id 2 
hava 3,the id 3 have 2 the id 5 have 1.
   count  id   score
  3 210
  2   3 20
  15 10
   2   3 20
   3   2 30
3  2 30


Hi Jim, this can work as an example:

  -- SQLite version 3.9.2  [ Release: 2015-11-02 ] on  SQLitespeed
   version 2.0.2.4.

  -- Script Items: 4  Parameter Count: 0
  -- 2016-05-26 10:54:28.833  |  [Info]   Script Initialized,
   Started executing...
  --
   


   CREATE TABLE t(id INT,score INT);

   INSERT INTO t(id, score) VALUES
 (2, 10)
   ,(3, 20)
   ,(5, 10)
   ,(3, 20)
   ,(2, 30)
   ,(2, 30)
   ;

   SELECT (SELECT count(*) FROM t AS I WHERE I.id=O.id) AS idcnt, id, score
  FROM t AS O
   ;


  -- idcnt |  id | score
  -- - | --- | -
  --   3   |  2  |   10
  --   2   |  3  |   20
  --   1   |  5  |   10
  --   2   |  3  |   20
  --   3   |  2  |   30
  --   3   |  2  |   30

   SELECT (SELECT count(*) FROM t AS I WHERE I.id=O.id) as idcnt, id, score
  FROM t AS O
 WHERE O.id>2
 ORDER by score DESC
   ;


  -- idcnt |  id | score
  -- - | --- | -
  --   2   |  3  |   20
  --   2   |  3  |   20
  --   1   |  5  |   10

  --   Script Stats: Total Script Execution Time: 0d 00h 00m
   and 00.025s
  -- Total Script Query Time: -- --- ---
   --- --.
  -- Total Database Rows Changed: 6
  -- Total Virtual-Machine Steps: 508
  -- Last executed Item Index:4
  -- Last Script Error:
  --
   



HTH,
Ryan

10 points to anyone for spotting the indeterminate sort operation :D
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sql

[Jim Wang]

hi all
   a table as follow:
 id   score
 210
 3 20
 5 10
 3 20
 2 30
 2 30
how could I select the table as follow  and the count can tell me: the id 2 
hava 3,the id 3 have 2 the id 5 have 1.
  count  id   score
 3 210
 2   3 20
 15 10
  2   3 20
  3   2 30
   3  2 30
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users