Re: [sqlite] Please help me fix the SQLite Git mirror

[Jens Alfke]

> On Apr 23, 2019, at 9:17 AM, Wout Mertens  wrote:
> 
> Note that many git clients (https://git-scm.com/downloads/guis/ 
> ) do allow
> you to see the entire DAG, just not GitHub. If you use a Mac, a
> particularly great one is https://gitup.co 

My preference is Fork > (Mac/Win, 
free), which has a slightly more traditional DAG view, i.e. a table view with 
the graph displayed in the left column. (GitUp has some great features, but 
most of the time I want to look at more than one commit message at a time.)

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

Re: [sqlite] Please help me fix the SQLite Git mirror

[Wout Mertens]

On Tue, Apr 23, 2019 at 1:22 PM Richard Hipp  wrote:

> The inability to see the entire DAG on a single screen in GitHub is a
> persistent source of annoyance
> to users like me who are accustomed to Fossil.
>

Note that many git clients (https://git-scm.com/downloads/guis/) do allow
you to see the entire DAG, just not GitHub. If you use a Mac, a
particularly great one is https://gitup.co

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

Re: [sqlite] Please help me fix the SQLite Git mirror

[Richard Hipp]

On 4/23/19, Rowan Worth  wrote:
>
> How can you justify the claim that history was unchanged on trunk between
> time (1) and time (2)?

Short answer:  I look at the entire DAG, not individual branches.

Long answer:

From prior discussion and feedback on this thread, I've come to
realize that there is a philosophical difference between Fossil and
Git.  Both systems use a Directed Acyclic Graph of check-ins (the DAG)
as their underlying data structure.  But in Git, the fundamental unit
of management is a branch, which Git defines as a linear sequence of
check-ins from a leaf back to the root, whereas in Fossil, the
fundamental unit of management is the entire DAG.

Git-ers think in terms of branches.  And so if you move a node from
one branch to another, that changes history.  But Fossil-ers think in
terms of the entire DAG.  Relabeling a node to be on a different
branch is just a notational convenience and does not change the DAG in
any way, and is hence not a history change.

Additional observations that support of this dichotomy:

(1) The default synchronization unit in Git is a single branch.  You
can sync the entire DAG using the --mirror option, but that is rarely
done in practice it seems.  In Fossil, the default and only
synchronization option is the entire DAG.

(2) The primary history display in Git/GitHub shows only a single
branch (ex: https://github.com/sqlite/sqlite/commits/master) whereas
the primary history display in Fossil shows the entire DAG (ex:
https://www.sqlite.org/src/timeline). The inability to see the entire
DAG on a single screen in GitHub is a persistent source of annoyance
to users like me who are accustomed to Fossil.

(3) Git puts special emphasis on rebase, which is used to transfer or
replicate a span of check-ins from one branch into another.  Having
all the important check-ins on a single branch is important if your
focus is on that one branch.  Fossil, in contrast, keeps the entire
DAG in view all at once, and so is no issue with keeping span of
check-ins remain on a separate arm of the DAG and merely merge in the
changes.

The Git approach makes sense in a highly distributed project like the
Linux Kernel, where there are many thousands of developers each
working on their own branches and where it is desirable to prune the
DAG down to a more manageable size by omitting extraneous arms.  The
Fossil approach works better for cathedral-style development where
there is a small team of contributors, all of which know each other
and work together daily, and where developers want to keep track of
everything that is going on, for improved situational awareness and
project coherence.

SQLite Git Mirror Update:

I made enhancements last night so that any future branch relabelings
that occur in SQLite should automatically be mirrored into the Git
repository on GitHub.  (Aside: See the interesting 5-way join used to
determine which "git update-ref" commands are needed on each
incremental export here:
https://www.fossil-scm.org/fossil/artifact/b0ace47d4c6a?ln=1492-1505)
There are definite management advantages to relabeling branches, and
so I will not preclude such actions in SQLite moving forward, though I
will try to keep relabelings to a minimum. Nevertheless, users of the
Git mirror should keep in mind that I think in terms of the entire
DAG, not individual branches, and so if you are tracking SQLite
development in a Git clone, you should to take steps to ensure that
you do not find yourself stalled on a side-tracked branch.

-- 
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] Please help me fix the SQLite Git mirror

[Rowan Worth]

Richard Hipp wrote (quoting from several emails):

> The problem is that Git now thinks that 9b888fcc is the HEAD of master
> and that the true continuation of master (check-in 4f35b3b7 and
> beyond) are disconnected check-ins
>

Because from the git perspective it _is_ still the HEAD -- there's been no
further changes made on top of that commit. The "true" changes are in a
separate branch hanging off some historic fork point.

I don't understand this part.  From the Fossil perspective, moving a
> check-in from one branch to another is just adding a new tag to that
> check-in.  No history is changed.  The DAG of check-ins (the block-chain)
> is unmodified.


Hm. Initially, the commits on the primary branch looked like this:

1. HISTORY - FORK - MISTAKE

Then you changed it to this:

2. HISTORY - FORK - FIXED - BEYOND

How can you justify the claim that history was unchanged on trunk between
time (1) and time (2)? You haven't just added a new check-in to the branch
in this situation (which git is more than happy to do via cherry-pick),
you've also erased the MISTAKE check-in.

What happens to fossil users who updated trunk while MISTAKE was the head?
Does the next update somehow pathfind to the new BEYOND head, backtracking
via FORK?

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

Re: [sqlite] Please help me fix the SQLite Git mirror

[Carl Edquist]

I would like to better understand how rewiring the refs this way 
constitutes "changing history".  The refs/heads entries are all 
ephemeral - they are constantly changing on their own, and no historical 
record of their past values is retained.


The key bit here is that in git, every commit references its parent commit 
hash, and thus implies the entire past history for that commit.  And local 
git repos contain that entire history.  So when a branch head is updated 
upstream, git can tell if history has moved forward (ie, the new head 
commit contains the previous head commit in its history), and if not, it's 
considered a "forced update", which is to say, history was re-written -- 
since the last previously know state of history is nolonger part of 
history.


Carl

On Mon, 22 Apr 2019, Carl Edquist wrote:


Hi Richard,

As Jonathan mentioned, in git land, if you have already published a 
"mistake" commit publicly, the proper way to revert it is to make another 
commit to reverse/undo the change.


By removing a commit from the public history of the published 'master' 
branch, it forces everyone downstream to manually fix their history.


If they do a normal "git pull", git will attempt to merge their master 
(the mistake commit) with the latest upstream master, which is not 
actually your intention.


But if you make an "revert" commit to undo the change, history will 
continue forward for the master branch from the downstream perspective.




I fixed the recent breakage of the SQLite Git mirror as follows:

(1) cd into the refs/heads directory
(2) run "cat master >mistake"
(3) run "echo a9a5465eb44d0d8f1c3c9d288b7f23f628ddb50b >master"
(4) run "git push --mirror https://github.com/sqlite/sqlite.git;


Not that you want to do it this way again if you can avoid it, but the 
safe git way to do (2),(3) is:


2) git update-ref refs/heads/mistake refs/heads/master
3) git update-ref refs/heads/master a9a5465eb44d0d8f1c3c9d288b7f23f628ddb50b


Carl

On Mon, 22 Apr 2019, Richard Hipp wrote:


Thanks for the help.  See additional questions and remarks below

On 4/22/19, Jonathan Brandmeyer  wrote:

```
# Construct the matching branch name
git branch mistake 9b888fc
# Push the name alone to the remote
git push -u origin mistake
# Move the name of master
git checkout master && git reset --hard 
# Push the new name of master
git push --force
```

Git reset --hard will move the name of the current working branch to
another branch SHA, which is why you need to first check out the
branch being moved: Its context sensitive.  You are re-writing
history, though.


I don't understand this part.  From the Fossil perspective, moving a
check-in from one branch to another is just adding a new tag to that
check-in.  No history is changed.  The DAG of check-ins (the
block-chain) is unmodified.

Subsequent to your message, I fixed the recent breakage of the SQLite
Git mirror as follows:

(1) cd into the refs/heads directory
(2) run "cat master >mistake"
(3) run "echo a9a5465eb44d0d8f1c3c9d288b7f23f628ddb50b >master"
(4) run "git push --mirror https://github.com/sqlite/sqlite.git;

This was a one-time fix.  I have not yet enhanced the mirroring
mechanism to make this happen automatically, but probably I will soon.

But before I proceed, I would like to better understand how rewiring
the refs this way constitutes "changing history".  The refs/heads
entries are all ephemeral - they are constantly changing on their own,
and no historical record of their past values is retained.  So if I
modify the refs to synchronize with the canonical Fossil repository,
how is that changing history, exactly?

Any further explanation is appreciated.

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

___
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] Please help me fix the SQLite Git mirror

[Carl Edquist]

Hi Richard,

As Jonathan mentioned, in git land, if you have already published a 
"mistake" commit publicly, the proper way to revert it is to make another 
commit to reverse/undo the change.


By removing a commit from the public history of the published 'master' 
branch, it forces everyone downstream to manually fix their history.


If they do a normal "git pull", git will attempt to merge their master 
(the mistake commit) with the latest upstream master, which is not 
actually your intention.


But if you make an "revert" commit to undo the change, history will 
continue forward for the master branch from the downstream perspective.




I fixed the recent breakage of the SQLite Git mirror as follows:

(1) cd into the refs/heads directory
(2) run "cat master >mistake"
(3) run "echo a9a5465eb44d0d8f1c3c9d288b7f23f628ddb50b >master"
(4) run "git push --mirror https://github.com/sqlite/sqlite.git;


Not that you want to do it this way again if you can avoid it, but the 
safe git way to do (2),(3) is:


2) git update-ref refs/heads/mistake refs/heads/master
3) git update-ref refs/heads/master a9a5465eb44d0d8f1c3c9d288b7f23f628ddb50b


Carl

On Mon, 22 Apr 2019, Richard Hipp wrote:


Thanks for the help.  See additional questions and remarks below

On 4/22/19, Jonathan Brandmeyer  wrote:

```
# Construct the matching branch name
git branch mistake 9b888fc
# Push the name alone to the remote
git push -u origin mistake
# Move the name of master
git checkout master && git reset --hard 
# Push the new name of master
git push --force
```

Git reset --hard will move the name of the current working branch to
another branch SHA, which is why you need to first check out the
branch being moved: Its context sensitive.  You are re-writing
history, though.


I don't understand this part.  From the Fossil perspective, moving a
check-in from one branch to another is just adding a new tag to that
check-in.  No history is changed.  The DAG of check-ins (the
block-chain) is unmodified.

Subsequent to your message, I fixed the recent breakage of the SQLite
Git mirror as follows:

(1) cd into the refs/heads directory
(2) run "cat master >mistake"
(3) run "echo a9a5465eb44d0d8f1c3c9d288b7f23f628ddb50b >master"
(4) run "git push --mirror https://github.com/sqlite/sqlite.git;

This was a one-time fix.  I have not yet enhanced the mirroring
mechanism to make this happen automatically, but probably I will soon.

But before I proceed, I would like to better understand how rewiring
the refs this way constitutes "changing history".  The refs/heads
entries are all ephemeral - they are constantly changing on their own,
and no historical record of their past values is retained.  So if I
modify the refs to synchronize with the canonical Fossil repository,
how is that changing history, exactly?

Any further explanation is appreciated.

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

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

Re: [sqlite] Please help me fix the SQLite Git mirror

[Jonathan Brandmeyer]

On Mon, Apr 22, 2019 at 12:22 PM Richard Hipp  wrote:

> But before I proceed, I would like to better understand how rewiring
> the refs this way constitutes "changing history".  The refs/heads
> entries are all ephemeral - they are constantly changing on their own,
> and no historical record of their past values is retained.  So if I
> modify the refs to synchronize with the canonical Fossil repository,
> how is that changing history, exactly?
>

Certainly no new SHA's were created, so this is much less obvious of a
re-write than if you had performed a rebase of some kind.
Nonetheless, I claim that this constitutes rewriting history because
it has a similar impact to downstream users.  Some user-visible
symptoms, after a user had already synchronized to the master which
was later abandoned:

- From a context of master, `git pull` alone would construct a merge
commit between the abandoned branch and the new master.  `git pull
--ff-only` would fail.

- From a context of a custom patch series, `git rebase master` has
unexpected effects, in that it also rebases the mistake you tried to
orphan.

- `git fetch` shows a forced update to origin/master.

- A user who was using a merge-based workflow and had merged to your
mistake branch would have a rough time following the change in branch
name.  One method would be to construct a new merge to the newly
corrected master and then rebase any of their subsequent changes onto
the new merge commit.  Their workflow is no longer strictly
merge-based and they still have to deal with the impacts of re-writing
their history.  Alternatively, they could construct the inverse of the
mistake via `git revert` onto their own working branch and then merge
again against the new master.

These user-visible impacts and the recovery actions are almost the
same as what a Git user would see if you had initially constructed (A,
B, C, D) and re-written it to be (A, C', D') instead via a rebase.

IMO, the proper corrective action after pushing the commit with a
mistake in it would have been to commit the inverse of the mistake and
then merge it to the alternate path.  Yes, it would have constructed a
merge commit in the history, which is unfortunate when you are trying
to maintain a clean and linear history.  But the impact to downstream
users would have been negligible.  `git pull --ff-only` would have
Just Worked, `git rebase master` from a patch series would have Just
Worked, and a merge-based workflow would have Just Worked, too.

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

Re: [sqlite] Please help me fix the SQLite Git mirror

[Richard Hipp]

Thanks for the help.  See additional questions and remarks below

On 4/22/19, Jonathan Brandmeyer  wrote:
> ```
> # Construct the matching branch name
> git branch mistake 9b888fc
> # Push the name alone to the remote
> git push -u origin mistake
> # Move the name of master
> git checkout master && git reset --hard 
> # Push the new name of master
> git push --force
> ```
>
> Git reset --hard will move the name of the current working branch to
> another branch SHA, which is why you need to first check out the
> branch being moved: Its context sensitive.  You are re-writing
> history, though.

I don't understand this part.  From the Fossil perspective, moving a
check-in from one branch to another is just adding a new tag to that
check-in.  No history is changed.  The DAG of check-ins (the
block-chain) is unmodified.

Subsequent to your message, I fixed the recent breakage of the SQLite
Git mirror as follows:

(1) cd into the refs/heads directory
(2) run "cat master >mistake"
(3) run "echo a9a5465eb44d0d8f1c3c9d288b7f23f628ddb50b >master"
(4) run "git push --mirror https://github.com/sqlite/sqlite.git;

This was a one-time fix.  I have not yet enhanced the mirroring
mechanism to make this happen automatically, but probably I will soon.

But before I proceed, I would like to better understand how rewiring
the refs this way constitutes "changing history".  The refs/heads
entries are all ephemeral - they are constantly changing on their own,
and no historical record of their past values is retained.  So if I
modify the refs to synchronize with the canonical Fossil repository,
how is that changing history, exactly?

Any further explanation is appreciated.

-- 
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] Please help me fix the SQLite Git mirror

[Jonathan Brandmeyer]

```
# Construct the matching branch name
git branch mistake 9b888fc
# Push the name alone to the remote
git push -u origin mistake
# Move the name of master
git checkout master && git reset --hard 
# Push the new name of master
git push --force
```

Git reset --hard will move the name of the current working branch to
another branch SHA, which is why you need to first check out the
branch being moved: Its context sensitive.  You are re-writing
history, though.  It shouldn't construct any new SHA's, but the impact
on a downstream user's workflow is rough.  Once it got published to
public git the least impactful way forward would be to construct the
inverse of the mistake and push that as its own commit instead of
orphaning it.  `git revert` does this in git-land.

If I'm maintaining some patches against your master, then my normal
workflow might be to rebase them against the current master every once
in a while, with just `git rebase master`.  If I did that once to
rebase against the SHA which was is currently named `master`, and then
invoke `git rebase master` again after your change to history, then
the second rebase will also attempt to rebase your mistake onto the
corrected master.  User's would need to perform a one-time `git rebase
--onto master mistake ` instead.


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

Re: [sqlite] Please help me fix the SQLite Git mirror

[Richard Hipp]

On 4/22/19, Jeffrey Schiller  wrote:
> So if I understand you correctly, you just want to make "master" point to a
> particular known commit. To do this, you can issue the commands (in a local
> copy):
>
> git branch -m master oldmaster # Move it out of the way
> git branch master 4f35b3b7
>

This is to be done via automation.  I don't want to have to write,
debug, and test the code to detect whether or not there is an existing
"master" that needs to be moved out of the way.  I'd rather do the
equivalent of REPLACE INTO - overwriting the existing ref it exists
and create a new one if it does not.  How might that be done in Git?


-- 
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] Please help me fix the SQLite Git mirror

[Jeffrey Schiller]

So if I understand you correctly, you just want to make "master" point to a
particular known commit. To do this, you can issue the commands (in a local
copy):

git branch -m master oldmaster # Move it out of the way
git branch master 4f35b3b7

Then do a "git push -f origin master" (assuming that the github repo is
defined as "origin", replace that with whatever name you use for the remote
if it isn't origin).

-Jeff

On Mon, Apr 22, 2019 at 12:05 PM Richard Hipp  wrote:

> The Git mirror of SQLite found at https://github.com/sqlite/sqlite is
> busted.  I don't know how to fix it and would appreciate advice from
> people who have more experience with Git internals.
>
> To describe the problem, consider this excerpt from the check-in
> sequence for SQLite:
>
> https://www.sqlite.org/src/timeline?d=6de980a09c3a7adf=5
>
> Notes to Git-ers:  (1) Graph arrows in Fossil point forwards in time,
> not backwards as Git does.  In other words, the arrows point from
> parent to child, not from child to parent.  (2) The main branch is
> called "trunk" in Fossil instead of "master".  The name is changed
> automatically during the mirroring process.
>
> What happened here is that the 9b888fcc check-in was originally on
> trunk/master.  But after it was checked in, I discovered a problem
> with it.  So I diverted that check-in off into the "mistake" branch
> (which you can do in Fossil by adding a special tag.)  Then the
> check-in sequence for trunk/master continued with 6cf16703 and
> 4f35b3b7 and so forth.
>
> The problem is that Git now thinks that 9b888fcc is the HEAD of master
> and that the true continuation of master (check-in 4f35b3b7 and
> beyond) are disconnected check-ins, awaiting garbage collection.
> There is no "ref" pointing to the HEAD of the true continuation.
>
> I think what I need to do is change refs/heads/master to point to
> 4f35b3b7 (or whatever check-ins come afterwards - the snippet shown is
> not the complete graph).  Then create a new entry refs/heads/mistake
> that points to 9b888fcc.
>
> Question 1:  Does my analysis seem correct.  Or have I misinterpreted
> the malfunction?
>
> Question 2:  Assuming that my analysis is correct, what is the
> preferred way of rewiring the refs in Git?
>
> --
> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Please help me fix the SQLite Git mirror

[Richard Hipp]

The Git mirror of SQLite found at https://github.com/sqlite/sqlite is
busted.  I don't know how to fix it and would appreciate advice from
people who have more experience with Git internals.

To describe the problem, consider this excerpt from the check-in
sequence for SQLite:

https://www.sqlite.org/src/timeline?d=6de980a09c3a7adf=5

Notes to Git-ers:  (1) Graph arrows in Fossil point forwards in time,
not backwards as Git does.  In other words, the arrows point from
parent to child, not from child to parent.  (2) The main branch is
called "trunk" in Fossil instead of "master".  The name is changed
automatically during the mirroring process.

What happened here is that the 9b888fcc check-in was originally on
trunk/master.  But after it was checked in, I discovered a problem
with it.  So I diverted that check-in off into the "mistake" branch
(which you can do in Fossil by adding a special tag.)  Then the
check-in sequence for trunk/master continued with 6cf16703 and
4f35b3b7 and so forth.

The problem is that Git now thinks that 9b888fcc is the HEAD of master
and that the true continuation of master (check-in 4f35b3b7 and
beyond) are disconnected check-ins, awaiting garbage collection.
There is no "ref" pointing to the HEAD of the true continuation.

I think what I need to do is change refs/heads/master to point to
4f35b3b7 (or whatever check-ins come afterwards - the snippet shown is
not the complete graph).  Then create a new entry refs/heads/mistake
that points to 9b888fcc.

Question 1:  Does my analysis seem correct.  Or have I misinterpreted
the malfunction?

Question 2:  Assuming that my analysis is correct, what is the
preferred way of rewiring the refs in Git?

-- 
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] Please help to create a connection to SQLite database!

[Clemens Ladisch]

Evgeniy Buzin wrote:
> "Settings "Prism.Mvvm.ViewModelLocator.AutoWireViewModel" property has
> called exception...".

That "..." contains relevant information.

> System.Windows.Markup.XamlParseException
>
> What is the reason of this error?

Something related with XAML parsing.  So not related with SQLite.


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

[sqlite] Please help to create a connection to SQLite database!

[Evgeniy Buzin]

 

Hello. I develop C# WPF MVVM Prism modular application in MS VS 2015
Professional in Windows 10 OS. My application has Shell project and many
Prism Module projects. Also my application contains CommonClassLibrary
class library project where I define ConnectionService class that is
applicable Prism service. ConnectionService class implements
IConnectionService interface which define in CommonClassLibrary class
library too. In the application bootstrapper I do (in particularly) the
following C#-code: 

class FcBootstrapper : UnityBootstrapper 

{ 

 . . . . . 

 protected override void ConfigureContainer()
 {
 Container.RegisterType(new
ContainerControlledLifetimeManager());
 base.ConfigureContainer();
 } 

 . . . . . 

} 

ConnectionService has (in particularly) the following methods: 

public bool CheckIfDatabaseExists()
{
 if (!Directory.Exists(@"C:UsersMy_Data"))
 Directory.CreateDirectory(@"C:UsersMy_Data");
 if (File.Exists(@"C:UsersMy_DataMy_Database.sqlite"))
 return true;
 else
 return false;
} 

public void CreateDatabase()
{
 SQLiteConnection.CreateFile(@"C:UsersKTM_DataKTM_Flowmeter.sqlite");
} 

public bool CheckIfTableExists(string tableName)
{
 using (SQLiteConnection connection = new SQLiteConnection("Data
Source=C:\Users\KTM_Flowmeter.sqlite;Version=3;"))
 {
 connection.Open();
 var command = connection.CreateCommand();
 command.CommandText = @"SELECT COUNT(*) FROM sqlite_master WHERE
name=@TableName";
 var p_tableName = command.CreateParameter();
 p_tableName.DbType = DbType.String;
 p_tableName.ParameterName = "TableName";
 p_tableName.Value = tableName;
 command.Parameters.Add(p_tableName);

 var result = command.ExecuteScalar();
 return ((long)result) == 1;
 }
} 

In Shell project I have ViewModels folder containing ShellViewModel
class. In ShellViewModel class constructor I try to execute the
following C#-code: 

public ShellViewModel(IConnectionService connectionService) 

{ 

 . . . . . . . 

 if (!this._connectionService.CheckIfDatabaseExists())
 this._connectionService.CreateDatabase();
 this._connectionService.CheckIfTableExists("AddedDevices"); 

 . . . . . . . 

} 

And when CheckIfDatabaseExists() method is called and its
'Directory.CreateDirectory(@"C:UsersMy_Data");' line is executed then I
have the following exception: 

"System.Windows.Markup.XamlParseException" in PresentationFramework.dll
"Settings "Prism.Mvvm.ViewModelLocator.AutoWireViewModel" property has
called exception...". 

If I try to execute 'Directory.CreateDirectory(@"C:UsersMy_Data");' line
of code directly in ShellViewModel class constructor then the same error
has place. 

If I change CheckIfDatabaseExists method as the following: 

public bool CheckIfDatabaseExists()
{
 if (File.Exists(@"My_Database.sqlite"))
 return true;
 else
 return false;
} 

and change CreateDatabase method as 

public void CreateDatabase()
{
 SQLiteConnection.CreateFile("My_Database.sqlite");
} 

and call 

if (!this._connectionService.CheckIfDatabaseExists())
 this._connectionService.CreateDatabase(); 

then if My_Database.sqlite database is not existed ofcourse it is
created. But when I after it call CheckIfTableExists method then on 

'using (SQLiteConnection connection = new SQLiteConnection("Data
Source=My_Database.sqlite;Version=3;"))' line of code I also have
"System.Windows.Markup.XamlParseException" in PresentationFramework.dll
"Settings "Prism.Mvvm.ViewModelLocator.AutoWireViewModel" property has
called exception..." error. 

What is the reason of this error? Please help. 

-- 

С уважением, 

Евгений Бузин
Инженер программист
ООО КТМ-Сервис 

Тел.: +7 846 202 00 65 доб. 151 
Факс: +7 846 229 55 52 
E-Mail: evgeniy.bu...@ktkprom.com
www.ktkprom.ru [1]

--
Best regard,

Software Engineer
Evgeniy Buzin
JSC KTM-Service

Tel.: +7 846 202 00 65 add 151
Fax: +7 846 229 55 52
E-Mail: evgeniy.bu...@ktkprom.com
www.ktkprom.ru [1]
 

Links:
--
[1] http://www.ktkprom.ru
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple data conversion in SQLite - please help

[Klaas V]

On Jan 22, 2014, at 8:21 AM, Richard Hipp  wrote:

> I seem to recall seeing some SMS databases off of an iPhone that used unix
> timestamps for the date/time.  That would be seconds since 1970.  You can
> use the 'unixepoch' modifier on the date functions within SQLite to do
> the conversion, if you want.

That's the normal epoch point zero. There are systems starting on January 1, 
1904
Perhaps other dates are used as well.

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

Re: [sqlite] Simple data conversion in SQLite - please help

[Ward Willats]

On Jan 22, 2014, at 8:21 AM, Richard Hipp  wrote:

> I seem to recall seeing some SMS databases off of an iPhone that used unix
> timestamps for the date/time.  That would be seconds since 1970.  You can
> use the 'unixepoch' modifier on the date functions within SQLite to do
> the conversion, if you want.


Yeah, that's what we do in our iOS apps. Try  "select datetime( field, 
'unixepoch' ) from table"

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

Re: [sqlite] Simple data conversion in SQLite - please help

[Richard Hipp]

On Wed, Jan 22, 2014 at 10:46 AM, Kees Nuyt  wrote:

> On Wed, 22 Jan 2014 12:42:27 -, "DJ Small Paul" <
> i...@djsmallpaul.co.uk> wrote:
>
> >Hi everyone,
> >
> >
> >
> >I hope this is an easy one for you!
> >
> >
> >
> >I've got an SQLite 3 database from an iphone app. I've pulled a table out
> >and the "date" column is in double binary - How do I see it as the actual
> >"date"??
>
> It is probably in Julianday format:
>
> http://sqlite.org/lang_datefunc.html
>
> perhaps with an Apple specific offset.
> Aplle sometimes uses its own perception of day zero,
> however, I forgot what date that was.
> Some simple experiments will tell you.
>
> I hope this helps.
>
>
I seem to recall seeing some SMS databases off of an iPhone that used unix
timestamps for the date/time.  That would be seconds since 1970.  You can
use the 'unixepoch' modifier on the date functions within SQLite to do
the conversion, if you want.

-- 
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] Simple data conversion in SQLite - please help

[Kees Nuyt]

On Wed, 22 Jan 2014 12:42:27 -, "DJ Small Paul"  
wrote:

>Hi everyone,
>
> 
>
>I hope this is an easy one for you!
>
> 
>
>I've got an SQLite 3 database from an iphone app. I've pulled a table out
>and the "date" column is in double binary - How do I see it as the actual
>"date"??

It is probably in Julianday format:

http://sqlite.org/lang_datefunc.html

perhaps with an Apple specific offset.
Aplle sometimes uses its own perception of day zero,
however, I forgot what date that was. 
Some simple experiments will tell you.

I hope this helps.

-- 
Groet, Cordialement, Pozdrawiam, Regards,

Kees Nuyt

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

[sqlite] Simple data conversion in SQLite - please help

[DJ Small Paul]

Hi everyone,

 

I hope this is an easy one for you!

 

I've got an SQLite 3 database from an iphone app. I've pulled a table out
and the "date" column is in double binary - How do I see it as the actual
"date"??

 

Thank in advance for your help,

 

Paul

 

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

Re: [sqlite] Please help me optimize this LEFT JOIN query.

[Petite Abeille]

On Sep 5, 2013, at 9:45 PM, Yuzem  wrote:

> It is incredibly fast but it gives me the wrong result:

Yeah… you forgot the 'where' clause in the subquery… you need to restrict it on 
the genre 


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

Re: [sqlite] Please help me optimize this LEFT JOIN query.

[Yuzem]

Thanks Petite Abeille, I translated your code to this:
SELECT genres.genres,
   (
SELECT  substr(group_concat(name,' '),1,60)
FROM
(
  SELECTname
  FROM  movies

  JOIN  genres
  ONmovies.movies = genres.movies

  ORDER BY  movies.name

  LIMIT 6
)
) AS sample

FROM  genres
GROUP BY  genres
ORDER BY  genres.genres;
time: 0.028s

It is incredibly fast but it gives me the wrong result:
Action|American Graffiti American Graffiti American Graffiti Mulhol
Adventure|American Graffiti American Graffiti American Graffiti Mulhol
Animation|American Graffiti American Graffiti American Graffiti Mulhol
Biography|American Graffiti American Graffiti American Graffiti Mulhol
Etc...

Am I doing something wrong?



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Please-help-me-optimize-this-LEFT-JOIN-query-tp71000p71035.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] Please help me optimize this LEFT JOIN query.

[Petite Abeille]

On Sep 5, 2013, at 11:27 PM, Yuzem  wrote:

> Any clue on why LEFT JOIN is so slow when used with genres but not with
> larger tables?

Sure. But your conclusion is a most likely a red herring. The crux of the 
matter is that inner and outer join have a wildly different semantic. And 
therefore execution plan. That's all. Seems all very reasonable from an 
optimizer point of view.

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

Re: [sqlite] Please help me optimize this LEFT JOIN query.

[Yuzem]

Petite Abeille-2 wrote
> Sure. But your conclusion is a most likely a red herring. The crux of the
> matter is that inner and outer join have a wildly different semantic. And
> therefore execution plan. That's all. Seems all very reasonable from an
> optimizer point of view.

But I have no conclusion, I acknowledge that inner and outer join are
different but I don't know why LEFT JOIN works very fast on larger tables
and very slow on smaller tables at least in those cases. I would like to
have a conclusion on that matter.



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Please-help-me-optimize-this-LEFT-JOIN-query-tp71000p71042.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] Please help me optimize this LEFT JOIN query.

[Petite Abeille]

On Sep 5, 2013, at 10:28 PM, Yuzem  wrote:

> Ok, wonderful, now it is working correctly but how do I select multiple
> columns from table movies?
> Should I add another sub query?

Nope. You have now changed the problem definition, so scalars will not be a 
good fit. Blindly copy & paste them will not get you anywhere.

The key to success in your case is to access the movie_genre table only once, 
as selectively as possible. 

So, if, for a given genre you want 4 movies, you could try something along 
these lines:

selectgenre.code,
  movie.title
from  (
selectmovie_genre.movie_id,
  movie_genre.genre_id
from  movie_genre

where movie_genre.genre_id = 30

order by  movie_genre.id

limit 4
  )
asmovie_genre

join  movie
onmovie.id = movie_genre.movie_id

join  genre
ongenre.id = movie_genre.genre_id

Which gives you 4 rows:

Western|"26 Men" (1957)
Western|"A Man Called Shenandoah" (1965)
Western|"ABC Weekend Specials" (1977) {The Winged Colt (#1.5)}
Western|"Action in the Afternoon" (1953)





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

Re: [sqlite] Please help me optimize this LEFT JOIN query.

[Yuzem]

Thanks but 4 rows is not what I am looking for.
I found a solution, concatenation:
SELECT genres.genres,
   (
SELECT  group_concat(movie,' ')
FROM
(
  SELECTmovies.movies||','||name movie
  FROM  movies

  JOIN  genres AS movie_genres
  ONmovies.movies = movie_genres.movies

  WHERE movie_genres.genres = genres.genres

  ORDER BY  movies.name

  LIMIT 3
)
)

FROM  genres
GROUP BY  genres
ORDER BY  genres.genres;

Any clue on why LEFT JOIN is so slow when used with genres but not with
larger tables?



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Please-help-me-optimize-this-LEFT-JOIN-query-tp71000p71040.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] Please help me optimize this LEFT JOIN query.

[Yuzem]

Ok, wonderful, now it is working correctly but how do I select multiple
columns from table movies?
Should I add another sub query?
Example:

SELECT genres.genres,
   (
SELECT  substr(group_concat(name,' '),1,60)
FROM
(
  SELECTname
  FROM  movies

  JOIN  genres AS movie_genres
  ONmovies.movies = movie_genres.movies

  WHERE movie_genres.genres = genres.genres

  ORDER BY  movies.name

  LIMIT 6
)
),
(
SELECT  substr(group_concat(movies,' '),1,60)
FROM
(
  SELECTname
  FROM  movies

  JOIN  genres AS movie_genres
  ONmovies.movies = movie_genres.movies

  WHERE movie_genres.genres = genres.genres

  ORDER BY  movies.name

  LIMIT 6
)
)

FROM  genres
GROUP BY  genres
ORDER BY  genres.genres;

If I add another sub query it takes almost twice the time than using only
one sub query. It is still faster than before.

 
Petite Abeille-2 wrote
> Yeah… the inner join vs out join will produce a significantly different
> access plan.

Yes but if that's the case don't you think that the difference in time is a
bit much?
And why there is almost no difference between JOIN and LEFT JOIN when using
tasks and people which are larger tables?



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Please-help-me-optimize-this-LEFT-JOIN-query-tp71000p71038.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] Please help me optimize this LEFT JOIN query.

[Petite Abeille]

On Sep 5, 2013, at 8:56 PM, Yuzem  wrote:

> SELECT genres FROM genres LEFT JOIN movies ON genres.movies = movies.movies
> GROUP BY genres ORDER BY genres;
> time: 2.475s
> 
> SELECT genres FROM genres JOIN movies ON genres.movies = movies.movies GROUP
> BY genres ORDER BY genres;
> time: 0.035s

Yeah… the inner join vs out join will produce a significantly different access 
plan.

For example, using a different schema:

(1) inner join
0|0|1|SCAN TABLE movie_genre USING COVERING INDEX sqlite_autoindex_movie_genre_1
0|1|0|SEARCH TABLE genre USING INTEGER PRIMARY KEY (rowid=?)

(2) outer join
0|0|0|SCAN TABLE genre USING COVERING INDEX sqlite_autoindex_genre_1
0|1|1|SCAN TABLE movie_genre USING COVERING INDEX sqlite_autoindex_movie_genre_1

In this case, the first plan is much better in terms of throughput as 
movie_genre is much larger table.

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

Re: [sqlite] Please help me optimize this LEFT JOIN query.

[Yuzem]

I did some testing and found some strange results.

SELECT genres FROM genres LEFT JOIN movies ON genres.movies = movies.movies
GROUP BY genres ORDER BY genres;
time: 2.475s

SELECT genres FROM genres JOIN movies ON genres.movies = movies.movies GROUP
BY genres ORDER BY genres;
time: 0.035s

SELECT tasks FROM tasks LEFT JOIN people ON tasks.people = people.people
GROUP BY tasks ORDER BY tasks"
time: 0.164s

SELECT tasks FROM tasks JOIN people ON tasks.people = people.people GROUP BY
tasks ORDER BY tasks"
time: 0.163s

The strange thing is that tasks and people are much larger tables than
genres and movies:
SELECT count() from genres;
3998
SELECT count() from movies;
1529
SELECT count() from tasks;
24964
SELECT count() from people;
19626



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Please-help-me-optimize-this-LEFT-JOIN-query-tp71000p71031.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] Please help me optimize this LEFT JOIN query.

[Yuzem]

I did an ANALYZE but I'm getting the same results.
I do have indexes:
CREATE TABLE movies (
  movies UNIQUE,
  name,
  icon_modified
);

CREATE TABLE genres (
  genres,
  movies,
  UNIQUE(genres,movies)
);

people has an index on people (people UNIQUE) and tasks is a view:
CREATE VIEW tasks AS
SELECT 'creators'  tasks,movies,people,'' details FROM creators
UNION ALL
SELECT 'directors' tasks,movies,people,'' details FROM directors
UNION ALL
SELECT 'writers'   tasks,movies,people,'' details FROM writers
UNION ALL
SELECT 'actors'tasks,movies,people,characters details FROM actors;

The tables creators, directors, etc, have similar indexes as genres:
CREATE TABLE directors (
  directors,
  people,
  UNIQUE(directors,people)
);



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Please-help-me-optimize-this-LEFT-JOIN-query-tp71000p71033.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] Please help me optimize this LEFT JOIN query.

[Simon Slavin]

On 5 Sep 2013, at 7:56pm, Yuzem  wrote:

> I did some testing and found some strange results.

Please do an ANALYZE and try the same things again.

Also, do you have any indexes on those tables (apart from the primary keys, of 
course) ?

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

Re: [sqlite] Please help me optimize this LEFT JOIN query.

[Petite Abeille]

On Sep 4, 2013, at 4:21 PM, Yuzem  wrote:

> I want to construct genres icons and each icon must display 4 movies.

Assuming this is IMDB… what about a scalar subquery?

For example, assuming a slightly different schema from yours:

selectgenre.code as genre,
  (
select  group_concat( title )
from
(
  selectmovie.title as title
  from  movie

  join  movie_genre
  onmovie_genre.movie_id = movie.id

  where movie_genre.genre_id = genre.id

  order by  movie.title

  limit 4
) 
  ) as sample
from  genre

where genre.code = 'Western'

order by  genre.code

> Western|"26 Men" (1957),"A Man Called Shenandoah" (1965),"ABC Weekend 
> Specials" (1977) {The Winged Colt (#1.5)},"Action in the Afternoon" (1953)

CPU Time: user 0.083246 sys 0.000443

This is for:

select count( * ) from genre;
30

select count( * ) from movie;
2545331

select count( * ) from movie_genre;
1545196


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

Re: [sqlite] Please help me optimize this LEFT JOIN query.

[Yuzem]

Thanks Igor but now it is taking even more time: 3.139s
Any other idea?



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Please-help-me-optimize-this-LEFT-JOIN-query-tp71000p71003.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] Please help me optimize this LEFT JOIN query.

[Igor Tandetnik]

On 9/4/2013 10:21 AM, Yuzem wrote:

SELECT genres name,count(genres.movies)
count,substr(group_concat(grouped.movies,' '),1,40)
src,substr(group_concat(grouped.icon_modified,' '),1,80) icon_modified FROM
genres LEFT JOIN (SELECT movies,icon_modified FROM movies WHERE
icon_modified != '') grouped on grouped.movies = genres.movies  GROUP BY
genres ORDER BY name"


Try

FROM genres LEFT JOIN movies grouped ON (genres.movies = movies.movies 
AND icon_modified != '')


The use of nested SELECT likely prevents SQLite from using the index on 
movies(movies).

--
Igor Tandetnik

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

[sqlite] Please help me optimize this LEFT JOIN query.

[Yuzem]

I have 2 tables:
CREATE TABLE movies (
  movies UNIQUE,
  name,
  icon_modified
);

CREATE TABLE genres (
  genres,
  movies,
);

I want to construct genres icons and each icon must display 4 movies.
I need to get: genres from genres and movies,icon_modified from movies.
This is the query I have:
SELECT genres name,count(genres.movies)
count,substr(group_concat(grouped.movies,' '),1,40)
src,substr(group_concat(grouped.icon_modified,' '),1,80) icon_modified FROM
genres LEFT JOIN (SELECT movies,icon_modified FROM movies WHERE
icon_modified != '') grouped on grouped.movies = genres.movies  GROUP BY
genres ORDER BY name"

The query works correctly but it is very very slow, it takes 2.474s on my
machine, if I run the same query but using JOIN instead of LEFT JOIN it only
takes 0.039s

Is there any way of optimizing these query?
Thanks in advance.



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Please-help-me-optimize-this-LEFT-JOIN-query-tp71000.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] please help me; i need sqlite-netFx35-setup-bundle-x64-2008-1.0.84.0.exe

[Kevin Benson]

On Thu, Aug 15, 2013 at 12:00 PM, 木与萱ip/  wrote:

> sqlite-netFx35-setup-bundle-x64
>

http://system.data.sqlite.org/downloads/1.0.84.0/sqlite-netFx35-setup-bundle-x64-2008-1.0.84.0.exe

--
   --
  --
 --Ô¿Ô--
K e V i N
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] please help me; i need sqlite-netFx35-setup-bundle-x64-2008-1.0.84.0.exe

[??????ip/]

hi :
please help me

 i need "sqlite-netFx35-setup-bundle-x64-2008-1.0.84.0.exe" ,but i did not find 
the file from the internet ?? please send it to me .


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

Re: [sqlite] Please help. Read only process being blocked by writer process.

[Howard Chu]

Alejandro Martínez wrote:

Thanks Richard, that makes perfect sense.

Thanks Howard, but i don't know what you are talking about, so i will
google "copy-on-write".


See the papers and presentations here:
   http://www.symas.com/mdb

Source code for SQLite is here:
   http://gitorious.org/mdb


Григорий Григоренко, Interesting! I'll consider this approach if at some
point i'm able to go "scorched earth" and start this from scratch, but at
this point i would have to change too much stuff.

I will go with the WAL solution for now. I'm just worried a buggy process
could hang while having a prepared statement open and cause the wal file to
grow forever, then causing errors in other processes.


--
  -- 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@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please help. Read only process being blocked by writer process.

[Alejandro Martínez]

Thanks Richard, that makes perfect sense.

Thanks Howard, but i don't know what you are talking about, so i will
google "copy-on-write".

Григорий Григоренко, Interesting! I'll consider this approach if at some
point i'm able to go "scorched earth" and start this from scratch, but at
this point i would have to change too much stuff.

I will go with the WAL solution for now. I'm just worried a buggy process
could hang while having a prepared statement open and cause the wal file to
grow forever, then causing errors in other processes.


On Wed, Nov 28, 2012 at 3:02 PM, Григорий Григоренко wrote:

> Hi,
>
>
> CREATE TABLE rev(current); INSERT INTO rev VALUES(0);
> CREATE TABLE data(..., revision);
>
> Readers:
> SELECT * FROM data JOIN rev ON revision = current WHERE ... ;
> // or "SELECT current FROM rev" into var and passing it value in "SELECT *
> FROM data WHERE revision=?"
>
> Writer:
>
> // insert new records, old records remains
>
> SELECT current + 1 FROM rev; // into some variable
>
> BEGIN;
> INSERT INTO data(..., revision) VALUES (..., current );
> INSERT INTO data(..., revision) VALUES (..., current );
> ...
> ... repeat 1000 times
> ...
> COMMIT;
>
> BEGIN;
> INSERT INTO data(..., revision) VALUES (..., current );
> INSERT INTO data(..., revision) VALUES (..., current );
> ...
> ... repeat 1000 times
> ...
> COMMIT;
>
> // repeat inserting until all records are inserted; 1000 is a number of
> records taken by a wild guess )
>
> // now switch readers to new records
> BEGIN; UPDATE rev SET current = current + 1; COMMIT; // no need for a
> begin/commit just to point that this runs inside its own transaction
>
> // now delete old records again incrementally
>
> // repeat this block until records stop deleting from table
> BEGIN;
> SELECT Max(rowid) - 1000 FROM data; // into variable MaxId
> DELETE FROM data WHERE revision = current - 1 AND rowid > MaxId;
> COMMIT;
>
> // done, there are only new records in a table, repeat above steps to
> insert new bunch of records
>
>
>
>
> Regads,
> GG
>
>
> Wed 28 Nov 2012 09:47:50 от Alejandro Martínez :
> >
> >
> >
>
>
> >
>
>
>
> >I have one process that each 30 minutes refills several tables in this
> >
> manner:
> >
>
> >
> sqlite3_open_v2(CACHEDB_PATH, _conn, SQLITE_OPEN_CREATE |
> >
> SQLITE_OPEN_READWRITE, NULL)
> >
>
> >
> - For each table:
> >
>
> >
> begin deferred transaction; delete from [table];
> >
> insert into table ...
> >
> insert into table ...
> >
> insert into table ...
> >
> insert into table ...
> >
> [up to 180.000 rows]
> >
> commit;
> >
>
> >
> and sometimes the commit fails, so it is retried. (why would it fail? its
> >
> the only  writter)
> >
>
> >
> And then i have many other processes that open that sqlite database read
> >
> only. sqlite3_open_v2(_dbfile, _conn, SQLITE_OPEN_READONLY, NULL)
> >
> and sqlite3_busy_timeout(sqcache_conn, 5000)
> >
>
> >
> These processes create very simple prepared statements to query that
> tables.
> >
>
> >
> And the big problem i'm having, is that when i step these prepared
> >
> statements, they lock for 5 seconds and then fail.
> >
>
> >
> And i put that busy timeout just for completeness, cause i wasn't expecting
> >
> any locking because for being a read only query.
> >
>
> >
> I really need these queries not to lock or fail.
> >
>
> >
> What am i doing wrong?
> >
> Any suggestions?
> >
>
> >
> Thank you,
> >
> Alejandro
> >
> ___
> >
> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please help. Read only process being blocked by writer process.

[Григорий Григоренко]

Hi,


CREATE TABLE rev(current); INSERT INTO rev VALUES(0);
CREATE TABLE data(..., revision);

Readers:
SELECT * FROM data JOIN rev ON revision = current WHERE ... ; 
// or "SELECT current FROM rev" into var and passing it value in "SELECT * FROM 
data WHERE revision=?"

Writer:

// insert new records, old records remains

SELECT current + 1 FROM rev; // into some variable

BEGIN;
INSERT INTO data(..., revision) VALUES (..., current );
INSERT INTO data(..., revision) VALUES (..., current );
...
... repeat 1000 times
...
COMMIT;

BEGIN;
INSERT INTO data(..., revision) VALUES (..., current );
INSERT INTO data(..., revision) VALUES (..., current );
...
... repeat 1000 times
...
COMMIT;

// repeat inserting until all records are inserted; 1000 is a number of records 
taken by a wild guess )

// now switch readers to new records
BEGIN; UPDATE rev SET current = current + 1; COMMIT; // no need for a 
begin/commit just to point that this runs inside its own transaction

// now delete old records again incrementally

// repeat this block until records stop deleting from table
BEGIN;
SELECT Max(rowid) - 1000 FROM data; // into variable MaxId
DELETE FROM data WHERE revision = current - 1 AND rowid > MaxId;
COMMIT;

// done, there are only new records in a table, repeat above steps to insert 
new bunch of records




Regads,
GG


Wed 28 Nov 2012 09:47:50 от Alejandro Martínez :
>   
>
>


>



>I have one process that each 30 minutes refills several tables in this
>
manner:
>

>
sqlite3_open_v2(CACHEDB_PATH, _conn, SQLITE_OPEN_CREATE |
>
SQLITE_OPEN_READWRITE, NULL)
>

>
- For each table:
>

>
begin deferred transaction; delete from [table];
>
insert into table ...
>
insert into table ...
>
insert into table ...
>
insert into table ...
>
[up to 180.000 rows]
>
commit;
>

>
and sometimes the commit fails, so it is retried. (why would it fail? its
>
the only  writter)
>

>
And then i have many other processes that open that sqlite database read
>
only. sqlite3_open_v2(_dbfile, _conn, SQLITE_OPEN_READONLY, NULL)
>
and sqlite3_busy_timeout(sqcache_conn, 5000)
>

>
These processes create very simple prepared statements to query that tables.
>

>
And the big problem i'm having, is that when i step these prepared
>
statements, they lock for 5 seconds and then fail.
>

>
And i put that busy timeout just for completeness, cause i wasn't expecting
>
any locking because for being a read only query.
>

>
I really need these queries not to lock or fail.
>

>
What am i doing wrong?
>
Any suggestions?
>

>
Thank you,
>
Alejandro
>
___
>
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] Please help. Read only process being blocked by writer process.

[Howard Chu]

Richard Hipp wrote:

On Wed, Nov 28, 2012 at 9:58 AM, Alejandro Martínez wrote:


And wouldn't PRAGMA read_uncommitted achieve the effect i was expecting? Or
does that cause other problems?



read_uncommitted only works if both the read and writer are in the same
process and are using shared cache.




Reading "old" or inconsistent data would not be a problem for me. (as long
as it is not corrupted data).



That's really the crux of the problem.  Suppose the writer has needing to
split a btree node, for example.  To do this, the writer would typically
write two new child pages and then overwrite the old b-tree page with the
parent page.  If the writer were part way through this sequence when the
reader comes along, the reader would see corrupt data, since the btree
structure would be only partially updated and hence not well-formed.


That's one of the fundamental problems with update-in-place DB designs. (Aside 
from their crash vulnerability and expensive crash recovery...) MVCC via 
copy-on-write has none of these issues.






On Wed, Nov 28, 2012 at 11:20 AM, Richard Hipp  wrote:


On Wed, Nov 28, 2012 at 7:09 AM, Alejandro Martínez  wrote:


On Wed, Nov 28, 2012 at 6:56 AM, Alejandro Martínez <

elpeq...@gmail.com

wrote:



Is that the only way?

When i had done that in the past, the wal file grew constantly and

i

am

afraid it could fill the hard disk.

That could happen if say... one of the reading processes doesn't
properly sqlite3_reset a prepared statement after stepping it.

right?




Correct.  The WAL file will grow until a checkpoint resets it.  And a
checkpoint cannot reset the WAL file while there is a pending

transaction.

So if you have a statement holding a transaction open, the WAL file

will

grow without bound.

The solution there is to not hold read transactions open

indefinitely.

Call sqlite3_reset() when you are done with a statement so that its

implied

read transaction will close.




Thank you for your quick answer.



On Wed, Nov 28, 2012 at 9:52 AM, Richard Hipp 

wrote:



PRAGMA journal_mode=WAL




--
  -- 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@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please help. Read only process being blocked by writer process.

[Richard Hipp]

On Wed, Nov 28, 2012 at 9:58 AM, Alejandro Martínez wrote:

> And wouldn't PRAGMA read_uncommitted achieve the effect i was expecting? Or
> does that cause other problems?
>

read_uncommitted only works if both the read and writer are in the same
process and are using shared cache.


>
> Reading "old" or inconsistent data would not be a problem for me. (as long
> as it is not corrupted data).
>

That's really the crux of the problem.  Suppose the writer has needing to
split a btree node, for example.  To do this, the writer would typically
write two new child pages and then overwrite the old b-tree page with the
parent page.  If the writer were part way through this sequence when the
reader comes along, the reader would see corrupt data, since the btree
structure would be only partially updated and hence not well-formed.


>
>
> On Wed, Nov 28, 2012 at 11:20 AM, Richard Hipp  wrote:
>
> > On Wed, Nov 28, 2012 at 7:09 AM, Alejandro Martínez  > >wrote:
> >
> > > Ok, i will probably do that. Thank you.
> > >
> > > But i'd like to know. Why doesn't this work without wal? A read only
> > > operation shouldn't block, right?
> > >
> >
> > If you are not running WAL, then the database is updated directly.  That
> > means that there can be no readers active when a write is in progress
> > because then the readers would see an incomplete and uncommitted
> > transaction.
> >
> >
> >
> > >
> > > And regarding the commit failing, does that need a busy timeout handler
> > > too? From documentation i though it would just wait until all readers
> are
> > > done reading and then write. And that further incoming readers would
> wait
> > > for those 5 seconds. And i was expecting not to really wait, as the
> > commit
> > > should be pretty quick, right?
> > >
> > > I'm puzzled.
> > >
> > >
> > >
> > > On Wed, Nov 28, 2012 at 10:01 AM, Richard Hipp  wrote:
> > >
> > > > On Wed, Nov 28, 2012 at 6:56 AM, Alejandro Martínez <
> > elpeq...@gmail.com
> > > > >wrote:
> > > >
> > > > > Is that the only way?
> > > > >
> > > > > When i had done that in the past, the wal file grew constantly and
> i
> > am
> > > > > afraid it could fill the hard disk.
> > > > >
> > > > > That could happen if say... one of the reading processes doesn't
> > > > > properly sqlite3_reset a prepared statement after stepping it.
> right?
> > > > >
> > > >
> > > > Correct.  The WAL file will grow until a checkpoint resets it.  And a
> > > > checkpoint cannot reset the WAL file while there is a pending
> > > transaction.
> > > > So if you have a statement holding a transaction open, the WAL file
> > will
> > > > grow without bound.
> > > >
> > > > The solution there is to not hold read transactions open
> indefinitely.
> > > > Call sqlite3_reset() when you are done with a statement so that its
> > > implied
> > > > read transaction will close.
> > > >
> > > >
> > > > >
> > > > > Thank you for your quick answer.
> > > > >
> > > > >
> > > > >
> > > > > On Wed, Nov 28, 2012 at 9:52 AM, Richard Hipp 
> > wrote:
> > > > >
> > > > > > PRAGMA journal_mode=WAL
> > > > > ___
> > > > > sqlite-users mailing list
> > > > > sqlite-users@sqlite.org
> > > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > > > >
> > > >
> > > >
> > > >
> > > > --
> > > > D. Richard Hipp
> > > > d...@sqlite.org
> > > > ___
> > > > sqlite-users mailing list
> > > > sqlite-users@sqlite.org
> > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > > >
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> >
> >
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
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] Please help. Read only process being blocked by writer process.

[Alejandro Martínez]

And wouldn't PRAGMA read_uncommitted achieve the effect i was expecting? Or
does that cause other problems?

Reading "old" or inconsistent data would not be a problem for me. (as long
as it is not corrupted data).


On Wed, Nov 28, 2012 at 11:20 AM, Richard Hipp  wrote:

> On Wed, Nov 28, 2012 at 7:09 AM, Alejandro Martínez  >wrote:
>
> > Ok, i will probably do that. Thank you.
> >
> > But i'd like to know. Why doesn't this work without wal? A read only
> > operation shouldn't block, right?
> >
>
> If you are not running WAL, then the database is updated directly.  That
> means that there can be no readers active when a write is in progress
> because then the readers would see an incomplete and uncommitted
> transaction.
>
>
>
> >
> > And regarding the commit failing, does that need a busy timeout handler
> > too? From documentation i though it would just wait until all readers are
> > done reading and then write. And that further incoming readers would wait
> > for those 5 seconds. And i was expecting not to really wait, as the
> commit
> > should be pretty quick, right?
> >
> > I'm puzzled.
> >
> >
> >
> > On Wed, Nov 28, 2012 at 10:01 AM, Richard Hipp  wrote:
> >
> > > On Wed, Nov 28, 2012 at 6:56 AM, Alejandro Martínez <
> elpeq...@gmail.com
> > > >wrote:
> > >
> > > > Is that the only way?
> > > >
> > > > When i had done that in the past, the wal file grew constantly and i
> am
> > > > afraid it could fill the hard disk.
> > > >
> > > > That could happen if say... one of the reading processes doesn't
> > > > properly sqlite3_reset a prepared statement after stepping it. right?
> > > >
> > >
> > > Correct.  The WAL file will grow until a checkpoint resets it.  And a
> > > checkpoint cannot reset the WAL file while there is a pending
> > transaction.
> > > So if you have a statement holding a transaction open, the WAL file
> will
> > > grow without bound.
> > >
> > > The solution there is to not hold read transactions open indefinitely.
> > > Call sqlite3_reset() when you are done with a statement so that its
> > implied
> > > read transaction will close.
> > >
> > >
> > > >
> > > > Thank you for your quick answer.
> > > >
> > > >
> > > >
> > > > On Wed, Nov 28, 2012 at 9:52 AM, Richard Hipp 
> wrote:
> > > >
> > > > > PRAGMA journal_mode=WAL
> > > > ___
> > > > sqlite-users mailing list
> > > > sqlite-users@sqlite.org
> > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > > >
> > >
> > >
> > >
> > > --
> > > D. Richard Hipp
> > > d...@sqlite.org
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please help. Read only process being blocked by writerprocess.

[Igor Tandetnik]

Alejandro Martínez  wrote:
> But i'd like to know. Why doesn't this work without wal? A read only
> operation shouldn't block, right?

Which piece of documentation has led you to believe so?

In a rollback journal mode, a database may have multiple readers OR a single 
writer at any point in time. In WAL mode, a database may have multiple readers 
AND a single writer at any point in time.
-- 
Igor Tandetnik

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

Re: [sqlite] Please help. Read only process being blocked by writer process.

[Richard Hipp]

On Wed, Nov 28, 2012 at 7:09 AM, Alejandro Martínez wrote:

> Ok, i will probably do that. Thank you.
>
> But i'd like to know. Why doesn't this work without wal? A read only
> operation shouldn't block, right?
>

If you are not running WAL, then the database is updated directly.  That
means that there can be no readers active when a write is in progress
because then the readers would see an incomplete and uncommitted
transaction.



>
> And regarding the commit failing, does that need a busy timeout handler
> too? From documentation i though it would just wait until all readers are
> done reading and then write. And that further incoming readers would wait
> for those 5 seconds. And i was expecting not to really wait, as the commit
> should be pretty quick, right?
>
> I'm puzzled.
>
>
>
> On Wed, Nov 28, 2012 at 10:01 AM, Richard Hipp  wrote:
>
> > On Wed, Nov 28, 2012 at 6:56 AM, Alejandro Martínez  > >wrote:
> >
> > > Is that the only way?
> > >
> > > When i had done that in the past, the wal file grew constantly and i am
> > > afraid it could fill the hard disk.
> > >
> > > That could happen if say... one of the reading processes doesn't
> > > properly sqlite3_reset a prepared statement after stepping it. right?
> > >
> >
> > Correct.  The WAL file will grow until a checkpoint resets it.  And a
> > checkpoint cannot reset the WAL file while there is a pending
> transaction.
> > So if you have a statement holding a transaction open, the WAL file will
> > grow without bound.
> >
> > The solution there is to not hold read transactions open indefinitely.
> > Call sqlite3_reset() when you are done with a statement so that its
> implied
> > read transaction will close.
> >
> >
> > >
> > > Thank you for your quick answer.
> > >
> > >
> > >
> > > On Wed, Nov 28, 2012 at 9:52 AM, Richard Hipp  wrote:
> > >
> > > > PRAGMA journal_mode=WAL
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@sqlite.org
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> >
> >
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
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] Please help. Read only process being blocked by writer process.

[Alejandro Martínez]

Ok, i will probably do that. Thank you.

But i'd like to know. Why doesn't this work without wal? A read only
operation shouldn't block, right?

And regarding the commit failing, does that need a busy timeout handler
too? From documentation i though it would just wait until all readers are
done reading and then write. And that further incoming readers would wait
for those 5 seconds. And i was expecting not to really wait, as the commit
should be pretty quick, right?

I'm puzzled.



On Wed, Nov 28, 2012 at 10:01 AM, Richard Hipp  wrote:

> On Wed, Nov 28, 2012 at 6:56 AM, Alejandro Martínez  >wrote:
>
> > Is that the only way?
> >
> > When i had done that in the past, the wal file grew constantly and i am
> > afraid it could fill the hard disk.
> >
> > That could happen if say... one of the reading processes doesn't
> > properly sqlite3_reset a prepared statement after stepping it. right?
> >
>
> Correct.  The WAL file will grow until a checkpoint resets it.  And a
> checkpoint cannot reset the WAL file while there is a pending transaction.
> So if you have a statement holding a transaction open, the WAL file will
> grow without bound.
>
> The solution there is to not hold read transactions open indefinitely.
> Call sqlite3_reset() when you are done with a statement so that its implied
> read transaction will close.
>
>
> >
> > Thank you for your quick answer.
> >
> >
> >
> > On Wed, Nov 28, 2012 at 9:52 AM, Richard Hipp  wrote:
> >
> > > PRAGMA journal_mode=WAL
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please help. Read only process being blocked by writer process.

[Richard Hipp]

On Wed, Nov 28, 2012 at 6:56 AM, Alejandro Martínez wrote:

> Is that the only way?
>
> When i had done that in the past, the wal file grew constantly and i am
> afraid it could fill the hard disk.
>
> That could happen if say... one of the reading processes doesn't
> properly sqlite3_reset a prepared statement after stepping it. right?
>

Correct.  The WAL file will grow until a checkpoint resets it.  And a
checkpoint cannot reset the WAL file while there is a pending transaction.
So if you have a statement holding a transaction open, the WAL file will
grow without bound.

The solution there is to not hold read transactions open indefinitely.
Call sqlite3_reset() when you are done with a statement so that its implied
read transaction will close.


>
> Thank you for your quick answer.
>
>
>
> On Wed, Nov 28, 2012 at 9:52 AM, Richard Hipp  wrote:
>
> > PRAGMA journal_mode=WAL
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
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] Please help. Read only process being blocked by writer process.

[Alejandro Martínez]

Is that the only way?

When i had done that in the past, the wal file grew constantly and i am
afraid it could fill the hard disk.

That could happen if say... one of the reading processes doesn't
properly sqlite3_reset a prepared statement after stepping it. right?

Thank you for your quick answer.



On Wed, Nov 28, 2012 at 9:52 AM, Richard Hipp  wrote:

> PRAGMA journal_mode=WAL
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please help. Read only process being blocked by writer process.

[Richard Hipp]

On Wed, Nov 28, 2012 at 6:47 AM, Alejandro Martínez wrote:

> I have one process that each 30 minutes refills several tables in this
> manner:
>
> sqlite3_open_v2(CACHEDB_PATH, _conn, SQLITE_OPEN_CREATE |
> SQLITE_OPEN_READWRITE, NULL)
>
> - For each table:
>
> begin deferred transaction; delete from [table];
> insert into table ...
> insert into table ...
> insert into table ...
> insert into table ...
> [up to 180.000 rows]
> commit;
>
> and sometimes the commit fails, so it is retried. (why would it fail? its
> the only  writter)
>
> And then i have many other processes that open that sqlite database read
> only. sqlite3_open_v2(_dbfile, _conn, SQLITE_OPEN_READONLY, NULL)
> and sqlite3_busy_timeout(sqcache_conn, 5000)
>
> These processes create very simple prepared statements to query that
> tables.
>
> And the big problem i'm having, is that when i step these prepared
> statements, they lock for 5 seconds and then fail.
>
> And i put that busy timeout just for completeness, cause i wasn't expecting
> any locking because for being a read only query.
>
> I really need these queries not to lock or fail.
>

Enable WAL mode.  "PRAGMA journal_mode=WAL".  http://www.sqlite.org/wal.html



>
> What am i doing wrong?
> Any suggestions?
>
> Thank you,
> Alejandro
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
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] Please help. Read only process being blocked by writer process.

[Alejandro Martínez]

Extra info.

Its running on this:

SunOS 5.10 Generic_144488-11 sun4u sparc SUNW,Sun-Fire-V490


On Wed, Nov 28, 2012 at 9:47 AM, Alejandro Martínez wrote:

> I have one process that each 30 minutes refills several tables in this
> manner:
>
> sqlite3_open_v2(CACHEDB_PATH, _conn, SQLITE_OPEN_CREATE |
> SQLITE_OPEN_READWRITE, NULL)
>
> - For each table:
>
> begin deferred transaction; delete from [table];
> insert into table ...
> insert into table ...
> insert into table ...
> insert into table ...
> [up to 180.000 rows]
> commit;
>
> and sometimes the commit fails, so it is retried. (why would it fail? its
> the only  writter)
>
> And then i have many other processes that open that sqlite database read
> only. sqlite3_open_v2(_dbfile, _conn, SQLITE_OPEN_READONLY, NULL)
> and sqlite3_busy_timeout(sqcache_conn, 5000)
>
> These processes create very simple prepared statements to query that
> tables.
>
> And the big problem i'm having, is that when i step these prepared
> statements, they lock for 5 seconds and then fail.
>
> And i put that busy timeout just for completeness, cause i wasn't
> expecting any locking because for being a read only query.
>
> I really need these queries not to lock or fail.
>
> What am i doing wrong?
> Any suggestions?
>
> Thank you,
> Alejandro
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Please help. Read only process being blocked by writer process.

[Alejandro Martínez]

I have one process that each 30 minutes refills several tables in this
manner:

sqlite3_open_v2(CACHEDB_PATH, _conn, SQLITE_OPEN_CREATE |
SQLITE_OPEN_READWRITE, NULL)

- For each table:

begin deferred transaction; delete from [table];
insert into table ...
insert into table ...
insert into table ...
insert into table ...
[up to 180.000 rows]
commit;

and sometimes the commit fails, so it is retried. (why would it fail? its
the only  writter)

And then i have many other processes that open that sqlite database read
only. sqlite3_open_v2(_dbfile, _conn, SQLITE_OPEN_READONLY, NULL)
and sqlite3_busy_timeout(sqcache_conn, 5000)

These processes create very simple prepared statements to query that tables.

And the big problem i'm having, is that when i step these prepared
statements, they lock for 5 seconds and then fail.

And i put that busy timeout just for completeness, cause i wasn't expecting
any locking because for being a read only query.

I really need these queries not to lock or fail.

What am i doing wrong?
Any suggestions?

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

Re: [sqlite] please help beginner on install issue

[zeal]

I have fixed the problem with google's help:
just _sqlite3.so is not installed correctly.
i reinstall python, the problem is gone.
thanks.

--
From: "zeal" <zealx...@hotmail.com>
Sent: Wednesday, May 26, 2010 2:59 PM
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Subject: [sqlite] please help beginner on install issue

> Hi,
>
>   beginner question: i installed python, sqlite3 in linux with step:
> 1cd sqlite-3.6.22
>
> 2./configure --prefix=/home/omc/daisy/python/
>
> 3make
>
> 4make install
>
> when i run the python, and want to import sqlite3, error at:
>
> Python 2.5.2 (r252:60911, May 26 2010, 09:38:53)
> [GCC 3.4.6 20060404 (Red Hat 3.4.6-11)] on linux2
> Type "help", "copyright", "credits" or "license" for more information.
>>>> import sqlite3
> Traceback (most recent call last):
>  File "", line 1, in 
>  File "/home/omc/daisy/python/lib/python2.5/sqlite3/__init__.py", line 24, 
> in 
>from dbapi2 import *
>  File "/home/omc/daisy/python/lib/python2.5/sqlite3/dbapi2.py", line 27, 
> in 
>from _sqlite3 import *
> ImportError: No module named _sqlite3
>
> thanks for your help
> daisy.
> ___
> 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] please help beginner on install issue

[zeal]

Hi, 

   beginner question: i installed python, sqlite3 in linux with step:
1cd sqlite-3.6.22

2./configure --prefix=/home/omc/daisy/python/

3make

4make install

when i run the python, and want to import sqlite3, error at:

Python 2.5.2 (r252:60911, May 26 2010, 09:38:53)
[GCC 3.4.6 20060404 (Red Hat 3.4.6-11)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
Traceback (most recent call last):
  File "", line 1, in 
  File "/home/omc/daisy/python/lib/python2.5/sqlite3/__init__.py", line 24, in 

from dbapi2 import *
  File "/home/omc/daisy/python/lib/python2.5/sqlite3/dbapi2.py", line 27, in 

from _sqlite3 import *
ImportError: No module named _sqlite3

thanks for your help
daisy.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please help test the latest query planner changes

[Edzard Pasma]

Op 16-apr-2010, om 19:51 heeft D. Richard Hipp het volgende geschreven:

>
> On Apr 16, 2010, at 11:52 AM, Max Vlasov wrote:
>>
>> SELECT StihiAuthors.Id As AuthId, StihiAuthCandidates.Date as Date,
>> StihiAuthCandidates.Num as Num FROM StihiAuthors
>>  INNER JOIN StihiAuthCandidates ON
>> StihiAuthors.Id=StihiAuthCandidates.AuthorId
>>  LEFT JOIN StihiPoems ON Date=StihiPoems.PoemDate AND
>> Num=StihiPoems.PoemNum
>>  WHERE StihiAuthors.IsFav=1 AND StihiPoems.rowid Is Null
>>
>> sqlite3-amalgamation-3_6_23_1.dll
>>  reported 747 milliseconds returning 22,642 rows
>>
>> sqlite3-20100415132938.dll
>>  reported 563 milliseconds
>
> Thanks for the report!
>
> It is reassuring to know that the recent changes actually did some  
> good!
>
>
> D. Richard Hipp
> d...@hwaci.com

There are however cases where the change makes things slower. For  
instance, if a program does only fetch the first row from a query.  
Possibly this is also the case in the above example if you add a  
'LIMIT 1' condition. It my test it was three times slower, comparing  
the two SQLite versions. But this likely also depends on the test  
data and indexes. The good news is that I observed a more spectacular  
improvement, when fetching all rows. This was four ot five times faster!

Another query that appears slower is a mega-union like this

SELECT  ...
UNION ALL
SELECT ...
UNION ALL
SELECT ..
UNION ALL
SELECT ..

where each select includes joins and where only one of the branches  
of the union yields a row. It looks like the join in each branch  
causes some extra overhead in the new version. This is at execution  
time, using pre-compiled SQL statements. The difference is relatively  
big, a factor 10 or so.

Though the differences are relatively big, they are absolutely in the  
order of mili seconds. So this is likely not an issus. Still it may  
be in particular applications, when queries are repeated very often.

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

Re: [sqlite] Please help test the latest query planner changes

[D. Richard Hipp]

On Apr 16, 2010, at 11:52 AM, Max Vlasov wrote:
>
> SELECT StihiAuthors.Id As AuthId, StihiAuthCandidates.Date as Date,
> StihiAuthCandidates.Num as Num FROM StihiAuthors
>  INNER JOIN StihiAuthCandidates ON
> StihiAuthors.Id=StihiAuthCandidates.AuthorId
>  LEFT JOIN StihiPoems ON Date=StihiPoems.PoemDate AND
> Num=StihiPoems.PoemNum
>  WHERE StihiAuthors.IsFav=1 AND StihiPoems.rowid Is Null
>
> sqlite3-amalgamation-3_6_23_1.dll
>  reported 747 milliseconds returning 22,642 rows
>
> sqlite3-20100415132938.dll
>  reported 563 milliseconds

Thanks for the report!

It is reassuring to know that the recent changes actually did some good!


D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] Please help test the latest query planner changes

[Max Vlasov]

> We are looking forward to your feedback, both positive and negative.
>
>
Mr D. Richard Hipp

found some time to compare this variant with the direct predecessor
(3.6.23_1)
Both dlls were compiled with Borland command-line compiler with identical
options

I tested a simple multiply lookup driven query (SELECT .. LEFT JOIN .. LEFT
JOIN.. ) and did not find any significant difference.

In another test from real program when I have a master-detail relation
between authors and their poems, the query forms a pool of not yet read
poems your new planner seems to show real improvements.

With the query

SELECT StihiAuthors.Id As AuthId, StihiAuthCandidates.Date as Date,
StihiAuthCandidates.Num as Num FROM StihiAuthors
  INNER JOIN StihiAuthCandidates ON
StihiAuthors.Id=StihiAuthCandidates.AuthorId
  LEFT JOIN StihiPoems ON Date=StihiPoems.PoemDate AND
 Num=StihiPoems.PoemNum
  WHERE StihiAuthors.IsFav=1 AND StihiPoems.rowid Is Null

sqlite3-amalgamation-3_6_23_1.dll
  reported 747 milliseconds returning 22,642 rows

sqlite3-20100415132938.dll
  reported 563 milliseconds

Both measurements are made several times and for a db that at least once was
used before in order to exclude windows system cache as a player.

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

[sqlite] Please help test the latest query planner changes

[D. Richard Hipp]

We've been tweaking of the SQLite query planner in an effort to help  
joins run faster.  But whenever you tweak the query planner, there is  
always a risk that some query plans might become significantly slower.

Your help in identifying any performance regressions is greatly  
appreciated.  Check-in f538d759beda67 of SQLite should be a drop-in  
replacement for SQLite versions 3.6.22 or 3.6.23.  Please test out  
check-in f538d759beda67 in your applications as you are able and let  
us know if you see any issues.  You can download the f538d759beda67  
check-in directly from

   http://www.sqlite.org/src/info/f538d759be

Or, you can get a prebuilt amalgamation at

  http://www.sqlite.org/sqlite3-20100415132938.zip

We are looking forward to your feedback, both positive and negative.

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] please help with NULL and NOTHING?

[aivars]

Thnaks, Mohd and Dan,

Dan, Your suggestion worked OK! Both on sqlite and MS SQL SERVER 2005.
The whole select statement as an argument to coalesce function.

Thanks

Aivars


2008/12/1 Dan <[EMAIL PROTECTED]>:
> If you are sure there is at most one entry in bilance1 where the account
> and year match then you could do this:
>
>   SELECT coalesce(
> (SELECT dbs from bilance1 where account='13100' and pYear=?),
> 0
>   ) AS summadeb;
>
>
>
>
>
> On Dec 1, 2008, at 3:26 PM, aivars wrote:
>
>> Hello,
>>
>> The simple query is like this:
>> SELECT dbs as summadeb from bilance1 where account='13100' and
>> pYear=?;
>>
>> Account number 13100 is not present in the table bilance1 when
>> pYear=2005 and it should be like this and therefore dbs is also not
>> present. Other years account number 13100 is present and query works
>> OK.
>>
>> When the query is run with a parameter pYear='2005' it returns nothing
>> - the resultset is empty or nothing? (I am doing it from python25)
>>
>> Even if I change the query to :
>> SELECT coalesce(dbs,0) as summadeb from bilance1 where account='13100'
>> and pYear='2005'; it still returns NOTHING, not 0.
>>
>> The same happens also on MS SQLServer 2005 so I think it should be
>> like that according to sql standards. If there is no account number
>> 13100 in the table then the result is NOTHING, not NULL or 0.
>> Strictly speaking I think also the value of 0 is not correct in this
>> case but I would like to have it.
>> It seams that coalesce can handle NULL not NOTHING.
>>
>> My question is:
>> Is there an SQL way to handle above query to return 0 or should I
>> handle this in client program (python)? (presently I get TypeError:
>> 'NoneType' object is unsubscriptable)
>>
>> Using sqlite 3.6.2, python2.5 and Windows XP
>>
>> Thanks in advance
>>
>> Aivars
>> ___
>> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] please help with NULL and NOTHING?

[Dan]

If you are sure there is at most one entry in bilance1 where the account
and year match then you could do this:

   SELECT coalesce(
 (SELECT dbs from bilance1 where account='13100' and pYear=?),
 0
   ) AS summadeb;





On Dec 1, 2008, at 3:26 PM, aivars wrote:

> Hello,
>
> The simple query is like this:
> SELECT dbs as summadeb from bilance1 where account='13100' and  
> pYear=?;
>
> Account number 13100 is not present in the table bilance1 when
> pYear=2005 and it should be like this and therefore dbs is also not
> present. Other years account number 13100 is present and query works
> OK.
>
> When the query is run with a parameter pYear='2005' it returns nothing
> - the resultset is empty or nothing? (I am doing it from python25)
>
> Even if I change the query to :
> SELECT coalesce(dbs,0) as summadeb from bilance1 where account='13100'
> and pYear='2005'; it still returns NOTHING, not 0.
>
> The same happens also on MS SQLServer 2005 so I think it should be
> like that according to sql standards. If there is no account number
> 13100 in the table then the result is NOTHING, not NULL or 0.
> Strictly speaking I think also the value of 0 is not correct in this
> case but I would like to have it.
> It seams that coalesce can handle NULL not NOTHING.
>
> My question is:
> Is there an SQL way to handle above query to return 0 or should I
> handle this in client program (python)? (presently I get TypeError:
> 'NoneType' object is unsubscriptable)
>
> Using sqlite 3.6.2, python2.5 and Windows XP
>
> Thanks in advance
>
> Aivars
> ___
> 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] please help with NULL and NOTHING?

[Mohd Radzi Ibrahim]

You should handle this in your client program. Even in MS SQL or Oracle, it 
will not return any resultset.
If you were to use left join, you may get it as NULL for any missing links.

rgd,
Radzi.

- Original Message - 
From: "aivars" <[EMAIL PROTECTED]>
To: <sqlite-users@sqlite.org>
Sent: Monday, December 01, 2008 4:26 PM
Subject: [sqlite] please help with NULL and NOTHING?


> Hello,
>
> The simple query is like this:
> SELECT dbs as summadeb from bilance1 where account='13100' and pYear=?;
>
> Account number 13100 is not present in the table bilance1 when
> pYear=2005 and it should be like this and therefore dbs is also not
> present. Other years account number 13100 is present and query works
> OK.
>
> When the query is run with a parameter pYear='2005' it returns nothing
> - the resultset is empty or nothing? (I am doing it from python25)
>
> Even if I change the query to :
> SELECT coalesce(dbs,0) as summadeb from bilance1 where account='13100'
> and pYear='2005'; it still returns NOTHING, not 0.
>
> The same happens also on MS SQLServer 2005 so I think it should be
> like that according to sql standards. If there is no account number
> 13100 in the table then the result is NOTHING, not NULL or 0.
> Strictly speaking I think also the value of 0 is not correct in this
> case but I would like to have it.
> It seams that coalesce can handle NULL not NOTHING.
>
> My question is:
> Is there an SQL way to handle above query to return 0 or should I
> handle this in client program (python)? (presently I get TypeError:
> 'NoneType' object is unsubscriptable)
>
> Using sqlite 3.6.2, python2.5 and Windows XP
>
> Thanks in advance
>
> Aivars
> ___
> 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] please help with NULL and NOTHING?

[aivars]

Hello,

The simple query is like this:
SELECT dbs as summadeb from bilance1 where account='13100' and pYear=?;

Account number 13100 is not present in the table bilance1 when
pYear=2005 and it should be like this and therefore dbs is also not
present. Other years account number 13100 is present and query works
OK.

When the query is run with a parameter pYear='2005' it returns nothing
- the resultset is empty or nothing? (I am doing it from python25)

Even if I change the query to :
SELECT coalesce(dbs,0) as summadeb from bilance1 where account='13100'
and pYear='2005'; it still returns NOTHING, not 0.

The same happens also on MS SQLServer 2005 so I think it should be
like that according to sql standards. If there is no account number
13100 in the table then the result is NOTHING, not NULL or 0.
Strictly speaking I think also the value of 0 is not correct in this
case but I would like to have it.
It seams that coalesce can handle NULL not NOTHING.

My question is:
Is there an SQL way to handle above query to return 0 or should I
handle this in client program (python)? (presently I get TypeError:
'NoneType' object is unsubscriptable)

Using sqlite 3.6.2, python2.5 and Windows XP

Thanks in advance

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

Re: [sqlite] Please help me locking/transaction? logic !

[John Stanton]

If you do not rely on Posix file locks at all and use a mutex then all 
your problems are gone.  Sqlite users are always getting their knickers 
in a knot using file locking.


Martin Alfredsson wrote:

Sorry, no.

The problem is that BEGIN TRANSACTION or _prepare/_step does
NOT set a SHARED lock when the SELECT statement is run.

I think this is a bug (since the doc says it should) so I have
added a ticket. There is a workaround though but since SQLite
does not support nested transactions it might be hard to use.

/Martin


 >From: John Stanton <[EMAIL PROTECTED]>
 >Subject: Re: Please help me locking/transaction? logic !
 >Newsgroups: gmane.comp.db.sqlite.general
 >Date: 2006-09-25 20:32:48 GMT (18 minutes ago)

 >Only one thread or process may be writing, but two of them have a lock.

 >What I do to make writing efficient and the logic simple is to use a
 >mutex to synchronize the writes.

 >Martin Alfredsson wrote:
 >> Hi !
 >>
 >> Only one process IS writing at once. The problem is that if
 >> the writing is able to happen after _step and before _finalize
 >> it locks the database,
 >>
 >> What my code does:
 >> sqlite3_exec(... "BEGIN TRANSACTION" ...)
 >> sqlite3_prepare(... "SELECT * FROM A_TABLE" ...)
 >> sqlite3_step(...)
 >> <= Another process does an UPDATE
 >> sqlite3_finalize(...)
 >> sqlite3_exec(... "COMMIT TRANSACTION" ...)
 >>



- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Please help me locking/transaction? logic !

[drh]

Martin Alfredsson <[EMAIL PROTECTED]> wrote:
> Sorry, no.
> 
> The problem is that BEGIN TRANSACTION or _prepare/_step does
> NOT set a SHARED lock when the SELECT statement is run.
> 
> I think this is a bug (since the doc says it should) so I have
> added a ticket. There is a workaround though but since SQLite
> does not support nested transactions it might be hard to use.
> 

I am unable to reproduce the behavior your describe.  All
of my test cases behave has documented.

Please check your test program to make sure you have not made
some mistake there.  Also, let us know what version of SQLite
you are using, how you compiled it, and what OS you are running
on.
--
D. Richard Hipp   <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Please help me locking/transaction? logic !

[Martin Alfredsson]

Sorry, no.

The problem is that BEGIN TRANSACTION or _prepare/_step does
NOT set a SHARED lock when the SELECT statement is run.

I think this is a bug (since the doc says it should) so I have
added a ticket. There is a workaround though but since SQLite
does not support nested transactions it might be hard to use.

/Martin


>From: John Stanton <[EMAIL PROTECTED]>
>Subject: Re: Please help me locking/transaction? logic !
>Newsgroups: gmane.comp.db.sqlite.general
>Date: 2006-09-25 20:32:48 GMT (18 minutes ago)

>Only one thread or process may be writing, but two of them have a lock.

>What I do to make writing efficient and the logic simple is to use a
>mutex to synchronize the writes.

>Martin Alfredsson wrote:
>> Hi !
>>
>> Only one process IS writing at once. The problem is that if
>> the writing is able to happen after _step and before _finalize
>> it locks the database,
>>
>> What my code does:
>> sqlite3_exec(... "BEGIN TRANSACTION" ...)
>> sqlite3_prepare(... "SELECT * FROM A_TABLE" ...)
>> sqlite3_step(...)
>> <= Another process does an UPDATE
>> sqlite3_finalize(...)
>> sqlite3_exec(... "COMMIT TRANSACTION" ...)
>>



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Please help me locking/transaction? logic !

[John Stanton]

Only one thread or process may be writing, but two of them have a lock.

What I do to make writing efficient and the logic simple is to use a 
mutex to synchronize the writes.


Martin Alfredsson wrote:

Hi !

Only one process IS writing at once. The problem is that if
the writing is able to happen after _step and before _finalize
it locks the database,

What my code does:
sqlite3_exec(... "BEGIN TRANSACTION" ...)
sqlite3_prepare(... "SELECT * FROM A_TABLE" ...)
sqlite3_step(...)
<= Another process does an UPDATE
sqlite3_finalize(...)
sqlite3_exec(... "COMMIT TRANSACTION" ...)

Reading the documentation make me think this should not happen:

http://www.sqlite.org/lockingv3.html

SHARED The database may be read but not written. Any number of 
processes can hold SHARED locks at the same time, hence there can be 
many simultaneous readers. But no other thread or process is allowed to 
write to the database file while one or more SHARED locks are active.


The SQL command "BEGIN TRANSACTION" (the TRANSACTION keyword is 
optional) is used to take SQLite out of autocommit mode. Note that the 
BEGIN command does not acquire any locks on the database. After a BEGIN 
command, a SHARED lock will be acquired when the first SELECT statement 
is executed. A RESERVED lock will be acquired when the first INSERT, 
UPDATE, or DELETE statement is executed. No EXCLUSIVE lock is acquired 
until either the memory cache fills up and must be spilled to disk or 
until the transaction commits. In this way, the system delays blocking 
read access to the file file until the last possible moment.



 >Only one user can write to an Sqlite database at any time.  The logic >of
 >your application has to enforce that "serialization" or "single
 >streaming" in some way.


 >From: John Stanton <[EMAIL PROTECTED]>
 >Subject: Re: Please help me locking/transaction? logic !
 >Newsgroups: gmane.comp.db.sqlite.general
 >Date: 2006-09-25 19:52:57 GMT (14 minutes ago)

 >Martin Alfredsson wrote:
 >> Is this correct ?
 >>
 >> My program calls:
 >> sqlite3_prepare(...)
 >> sqlite3_step(...)
 >>
 >> Here another process writes to the database sqlite3_exec(...)
 >>
 >> sqlite3_finalize(...)
 >>
 >> Now my program will fail with SQLITE_BUSY (5) when
 >> accessing the database.
 >>
 >> If I dont terminate the other process restaring my program
 >> will not help since it will get SQLITE_BUSY (5) all the time.
 >>
 >> Is there a way to avoid this,
 >> Can I stop another process from being able to write to the
 >> db while my program is between a _step/_finalize ?
 >> (Avoiding "long" SELECTs is hard due to lots of data).
 >>
 >> /Martin
 >> ma1999ATjmaDOTse
 >


- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 






-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Please help me locking/transaction? logic !

[Martin Alfredsson]

Hi !

Only one process IS writing at once. The problem is that if
the writing is able to happen after _step and before _finalize
it locks the database,

What my code does:
sqlite3_exec(... "BEGIN TRANSACTION" ...)
sqlite3_prepare(... "SELECT * FROM A_TABLE" ...)
sqlite3_step(...)
<= Another process does an UPDATE
sqlite3_finalize(...)
sqlite3_exec(... "COMMIT TRANSACTION" ...)

Reading the documentation make me think this should not happen:

http://www.sqlite.org/lockingv3.html

SHARED 	The database may be read but not written. Any number of 
processes can hold SHARED locks at the same time, hence there can be 
many simultaneous readers. But no other thread or process is allowed to 
write to the database file while one or more SHARED locks are active.


The SQL command "BEGIN TRANSACTION" (the TRANSACTION keyword is 
optional) is used to take SQLite out of autocommit mode. Note that the 
BEGIN command does not acquire any locks on the database. After a BEGIN 
command, a SHARED lock will be acquired when the first SELECT statement 
is executed. A RESERVED lock will be acquired when the first INSERT, 
UPDATE, or DELETE statement is executed. No EXCLUSIVE lock is acquired 
until either the memory cache fills up and must be spilled to disk or 
until the transaction commits. In this way, the system delays blocking 
read access to the file file until the last possible moment.



>Only one user can write to an Sqlite database at any time.  The logic >of
>your application has to enforce that "serialization" or "single
>streaming" in some way.


>From: John Stanton <[EMAIL PROTECTED]>
>Subject: Re: Please help me locking/transaction? logic !
>Newsgroups: gmane.comp.db.sqlite.general
>Date: 2006-09-25 19:52:57 GMT (14 minutes ago)

>Martin Alfredsson wrote:
>> Is this correct ?
>>
>> My program calls:
>> sqlite3_prepare(...)
>> sqlite3_step(...)
>>
>> Here another process writes to the database sqlite3_exec(...)
>>
>> sqlite3_finalize(...)
>>
>> Now my program will fail with SQLITE_BUSY (5) when
>> accessing the database.
>>
>> If I dont terminate the other process restaring my program
>> will not help since it will get SQLITE_BUSY (5) all the time.
>>
>> Is there a way to avoid this,
>> Can I stop another process from being able to write to the
>> db while my program is between a _step/_finalize ?
>> (Avoiding "long" SELECTs is hard due to lots of data).
>>
>> /Martin
>> ma1999ATjmaDOTse
>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Please help me locking/transaction? logic !

[John Stanton]

Martin Alfredsson wrote:

Is this correct ?

My program calls:
sqlite3_prepare(...)
sqlite3_step(...)

Here another process writes to the database sqlite3_exec(...)

sqlite3_finalize(...)

Now my program will fail with SQLITE_BUSY (5) when
accessing the database.

If I dont terminate the other process restaring my program
will not help since it will get SQLITE_BUSY (5) all the time.

Is there a way to avoid this,
Can I stop another process from being able to write to the
db while my program is between a _step/_finalize ?
(Avoiding "long" SELECTs is hard due to lots of data).

/Martin
ma1999ATjmaDOTse

- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 



Only one user can write to an Sqlite database at any time.  The logic of 
your application has to enforce that "serialization" or "single 
streaming" in some way.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Please help me locking/transaction? logic !

[Martin Alfredsson]

Is this correct ?

My program calls:
sqlite3_prepare(...)
sqlite3_step(...)

Here another process writes to the database sqlite3_exec(...)

sqlite3_finalize(...)

Now my program will fail with SQLITE_BUSY (5) when
accessing the database.

If I dont terminate the other process restaring my program
will not help since it will get SQLITE_BUSY (5) all the time.

Is there a way to avoid this,
Can I stop another process from being able to write to the
db while my program is between a _step/_finalize ?
(Avoiding "long" SELECTs is hard due to lots of data).

/Martin
ma1999ATjmaDOTse

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Please help, am I doing something wrong?

[ed nospam]

 Hi,
 I know that sqlite3 uses reader-writer locks and I've read the part
of FAQ that describes the multiple client access. But I'm still having
a lot of problem.

 I use sqlite3-ruby to access the database file with Rails/ActiveRecord.
 I also use sqlite3 C API to access the database file using C in my
other client.
 I have another program that uses regular ruby code and sqlite3-ruby
to access the same database file.
 I kept on getting database is locked error. I have busy-handler
registered for all of them and the write should take only milliseconds
(very simple write). All the programs do some writing.
 Is there a way to only lock the whole file when writing and not when reading?
 Actually, for anyone experienced in sqlite3, what's the correct way
of using it in my situation?

 Thank you very much.
--ed

[sqlite] *** Please help ***

[EzTools Support]

Hello.  Can someone please help with this issue.  It may turn out to be 
a bug in Sqlite 3.

I have the following fairly basic SQL View that works fine with Sqlite 2.x:
CREATE VIEW Invoices AS
SELECT Customers.CustomerID, Customers.CompanyName,Orders.OrderID, 
OrderDetails.ProductID, OrderDetails.Quantity FROM Customers INNER JOIN 
Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN 
OrderDetails ON Orders.OrderID = OrderDetails.OrderID

When I create and run this view in 3.x, I get all of the rows back, but 
all values are NULL!

If I run the SELECT statement apart from the view, it I get the data 
back fine.  But if I select * FROM Invoices, I get all of the rows back, 
but all values are NULL.

Another interesting thing is that when selecting from the View, the 
column names are without the table prefix.  That is, CustomerID, 
CompanyName, OrderID, et.  But if I select from the tables directly, the 
column names have the table prefix, Customers.CustomerID, 
Customers.CompanyName, Orders.OrderID

I have put the database up on my website here (Zip file):
   www.eztools-software.com/downloads/northwind.zip
This is happening in all version 3.0.5-3.0.7.  Can someone please 
download this DB and investigate.

If you want to test the same view and data with a v2.x database, you can 
get the SqlitePlus28 download from here:

   www.eztools-software.com/downloads/sqliteplus.exe
TIA
-Brett Goodman