[sqlite] Database sybchronisation

[Jean-Christophe Deschamps]

simon,

>This is normally resolved by logging the time each command was issued

Not "when it was issued" but when exactly it was finally commited. When 
one or more update statements are part of a transcation, you now have 
to use sqlite3_profile to log when exactly the transaction ended (i.e. 
can be considered actually stored). That implies both sqlite3_trace and 
sqlite3_profile need to be monitored to keep track of what constitutes 
an update transaction and when precisely it was commited.

If machines A and B (or more) run independantly, I don't believe you 
need a central "untouched" copy: you can very well carefully merge logs 
and determine for each machine what change took place after every of 
its own updates for every row in every table.

Another tough issue is how to re-sync machine times down to a very 
small time window in a common time frame. Whatever small "vulnerable" 
time window you achieve, you can be assured that it will cause in a 
problem someday, resulting in updates merged out of real-world order.

Note that merging can also violate constraints in the merged database. 
Say a rule (enforced by constraint or at applicative level) limits the 
number, volume or nature of the updates permitted in a given timeframe, 
you have no guarantee that this rule will be enforced if independant 
(albeit individually conformal) changes are made but only later merged. 
Several changes can very well have taken place independantly without 
breaking local rules (to the knowledge of the local machine), but 
discover only at merge time that some of the last operations would have 
been rejected if they have been submitted to a centralized machine/DB.

For instance you have an export restriction (available stock or 
manufacturing capability) which allows no more than N given items XYZ 
to be exported (or sold) per day/week/month. Even if independant 
machines+DBs don't violate the rule from their own point of view, the 
merged dataset may be in violation of the limit(s) or simply rejected 
by SQL constraints at merge time.

While I agree that this is not exactly in the realm of raw data merge 
in the right order, this still is a serious problem to deal with, not 
solvable with such simple merging operations. 

[sqlite] Database sybchronisation

[Simon Slavin]

On 13 Aug 2015, at 5:28pm, Tim Streater  wrote:

> Presumably all the OP needs to do (in the future, at any rate) is:
> 
>  open first db
>  attach second db
>  start transaction
>  do updates to first db;
>  do identical updates to second db
>  commit transaction
>  close connection
> 
> or some facsimile thereof.

One 'solution' to this problem is just to take any node's copy of the database, 
execute all other node's changes against it, then to distribute this copy to 
each node.  This increases the number of bits that need to get from one node to 
another, but means that you don't need to do lots of processing on all nodes.

It's not a good solution and it's easy to find problems with it.  But it can be 
done without defining a 'master node' and without having to design a 
client/server solution.  And, for most installations, where (for example) only 
one salesman works on each account, it works fine.

Simon.

[sqlite] Database sybchronisation

[Rob Willett]

Simon,

Your example is a very simple, yet elegant example of why database 
synchronisation is a ?difficult? problem to solve. 

As you say at the bottom of your e-mail, for the database to be correct, it has 
to determine the intentions of two sets of users to work out the solution. Even 
if somehow ?the database? manages to know what the users intended, using your 
example what is the correct answer, should town be ?Cambridge? or should town 
be ?Camberley?? One user would say Cambridge and one would say Camberley. So 
even knowing the users intentions would not be enough to work out the correct 
answer. A timing log simply shows who was first and second and has to ignore 
intentions as the database has no other information to work with.

This problem has been going on for years and I am not aware of a solution to 
the problem, however I do not claim to be an expert (or even a knowledgable 
user), I suspect that the problem has been proven to insolvable if we make the 
assumption the databases are equal in status. if we have a master database and 
a slave database then the master always wins (kind of why they are the master) 
and the problem is simple. 

As a simple example of a large company who could not solve this, consider IBM 
and Lotus Notes. Lotus Notes has been running for around 25 years, it has a 
client database on the users machine and a remote database on a server. IBM 
could not guarantee that the client database and the remote server database 
were always synced correctly and would produce replication conflict errors 
(sync errors really) when your databases did not match. It presented the sync 
errors in time order (as Simon has suggested) and allowed the user to choose 
which was the correct version to keep and which to delete. IBM never resolved 
this problem after 25 years and I suspect nobody else has or will. 

I?d be interested if anybody has a counter example to this to show how syncing 
can be made to work, as I have said I do not consider myself an expert here, 
but I have  sold  DB2 to customers and have more than once stood 
up in presentations to talk about our DB2 technical solution. Its amazing how 
much one can cram in the evening before the client briefing ?. :)

Rob

> On 13 Aug 2015, at 17:20, Simon Slavin  wrote:
> 
> 
> On 13 Aug 2015, at 4:03pm, sqlite-mail  wrote:
> 
>> With that would be trivial to log the statements that change the database to
>> replicate elsewhere. 
> 
> As Jean-Christophe wrote, it's not that simple.  There are huge books written 
> on the problems involved in synchronising two copies of a database, and none 
> of them end with a chapter called "Solution".
> 
> Here's an example of one of many problems.
> 
> Two copies of a database are maintained.  In one copy, the following command 
> is issued:
> 
> UPDATE contacts SET town = "Cambridge" WHERE town = "Grantebrycge"
> 
> In the other copy the following command is issued:
> 
> UPDATE contacts SET town = "Camberley" WHERE town = "Cambridge"
> 
> So when the databases are synchronised, one copy has command 1 then command 
> 2, whereas the other copy has command 2 then command 1.  Here's the result 
> after the synchronisation:
> 
> Original  Copy A  Copy B
>   --  --
> Grantebrycge  Camberley   Cambridge
> 
> As you can see, the two copies are not identical.  And the resolution of the 
> problem (deciding what /should/ happen) depends on the intentions of the 
> users, which is something the software can't know.
> 
> This is normally resolved by logging the time each command was issued and 
> keeping a central copy of the database which remains unchanged when changes 
> are made to the live copies.  Upon synchronisation the change logs for all 
> copies are merged in time order, and the resulting log is applied to the 
> central unchanged copy.  Then copies of this new version of the database are 
> copied to each client.
> 
> So now you need some sort of 'boss' node which does extra work.  And even 
> this still doesn't please users, some of whom will see their changes be 
> ignored or changed again by other people.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Database sybchronisation

[Tim Streater]

On 13 Aug 2015 at 16:20, Simon Slavin  wrote: 

> On 13 Aug 2015, at 4:03pm, sqlite-mail  wrote:
>
>> With that would be trivial to log the statements that change the database to
>> replicate elsewhere.
>
> As Jean-Christophe wrote, it's not that simple.  There are huge books written
> on the problems involved in synchronising two copies of a database, and none
> of them end with a chapter called "Solution".

Presumably all the OP needs to do (in the future, at any rate) is:

  open first db
  attach second db
  start transaction
  do updates to first db;
  do identical updates to second db
  commit transaction
  close connection

or some facsimile thereof.

--
Cheers  --  Tim

[sqlite] Database sybchronisation

[sqlite-mail]

Hello !  

?  

This request is a common requirement and in my opinion would be better solved
by an extension to the actual sqlite3 api functions.  

?  

Actually there is sqlite3_trace that can be used to watch all sql statements
executed on a given session.  

?  

One possible way would be to add another parameter that could be a bitwise
flag to indicate wich kind of statements we want to watch.  

?  

With that would be trivial to log the statements that change the database to
replicate elsewhere.  

?  

?  

?  

#define SQLITE_TRACE_DML 0x0001  

?  

#define SQLITE_TRACE_SELECT 0x0002  

?  

#define SQLITE_TRACE_INSERT 0x0004  

?  

#define SQLITE_TRACE_UPDATE 0x0008  

?  

#define SQLITE_TRACE_DELETE 0x0100  

?  

void *sqlite3_trace(sqlite3*, void(*xTrace)(void*,const char*), void*, int
what_to_trace);  

?  

?  

?  

Cheers !  

?

[sqlite] Database sybchronisation

[Simon Slavin]

On 13 Aug 2015, at 4:03pm, sqlite-mail  wrote:

> With that would be trivial to log the statements that change the database to
> replicate elsewhere. 

As Jean-Christophe wrote, it's not that simple.  There are huge books written 
on the problems involved in synchronising two copies of a database, and none of 
them end with a chapter called "Solution".

Here's an example of one of many problems.

Two copies of a database are maintained.  In one copy, the following command is 
issued:

UPDATE contacts SET town = "Cambridge" WHERE town = "Grantebrycge"

In the other copy the following command is issued:

UPDATE contacts SET town = "Camberley" WHERE town = "Cambridge"

So when the databases are synchronised, one copy has command 1 then command 2, 
whereas the other copy has command 2 then command 1.  Here's the result after 
the synchronisation:

OriginalCopy A  Copy B
--  --
GrantebrycgeCamberley   Cambridge

As you can see, the two copies are not identical.  And the resolution of the 
problem (deciding what /should/ happen) depends on the intentions of the users, 
which is something the software can't know.

This is normally resolved by logging the time each command was issued and 
keeping a central copy of the database which remains unchanged when changes are 
made to the live copies.  Upon synchronisation the change logs for all copies 
are merged in time order, and the resulting log is applied to the central 
unchanged copy.  Then copies of this new version of the database are copied to 
each client.

So now you need some sort of 'boss' node which does extra work.  And even this 
still doesn't please users, some of whom will see their changes be ignored or 
changed again by other people.

Simon.

[sqlite] Database sybchronisation

[Chris Parsonson]

Thanks TIm

On 13 August 2015 at 15:31, Tim Streater  wrote:

> On 13 Aug 2015 at 11:34, Chris Parsonson  wrote:
>
> > Has anybody ever used this ATTTACH command?
>
> Here's what I do to move a row from one database to another (same
> table/column defs). I have to go via a temporary db (I use the :memory:
> one) since the primary key needs a new value in the second db. The original
> row in question has for example a value of 27 for its primary key (absid)
> in the following:
>
>   
>   attach database ':memory:' as mem;
>   create table mem.messages as select * from main.messages where absid=27;
>   update mem.messages set absid=null;
>   attach database '/path/to/second/db' as dst;
>   insert into dst.messages select * from mem.messages;
>   delete from main.messages where absid=27;
>   
>
> I developed this approach (for my use case) using the sqlite3 CLI program
> to ensure the general approach worked before trying it with my programming
> language (PHP). You might benefit from doing the same.
>
> --
> Cheers  --  Tim
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>


-- 
Chris Parsonson
083 777 9261

[sqlite] Database sybchronisation

[Tim Streater]

On 13 Aug 2015 at 11:34, Chris Parsonson  wrote:

> Has anybody ever used this ATTTACH command?

Here's what I do to move a row from one database to another (same table/column 
defs). I have to go via a temporary db (I use the :memory: one) since the 
primary key needs a new value in the second db. The original row in question 
has for example a value of 27 for its primary key (absid) in the following:

  
  attach database ':memory:' as mem;
  create table mem.messages as select * from main.messages where absid=27;
  update mem.messages set absid=null;
  attach database '/path/to/second/db' as dst;
  insert into dst.messages select * from mem.messages;
  delete from main.messages where absid=27;
  

I developed this approach (for my use case) using the sqlite3 CLI program to 
ensure the general approach worked before trying it with my programming 
language (PHP). You might benefit from doing the same.

--
Cheers  --  Tim

[sqlite] Database sybchronisation

[Chris Parsonson]

Has anybody ever used this ATTTACH command?
Still doesn't work

Dim dbConnection As New SQLiteConnection
Dim dbDataSet As New DataSet
Dim SQLStmt As String
Dim sDBPath As String = Application.StartupPath & "\wolfpro.db3"
Dim sUpdateDBPath As String =
"C:\Users\Chris\Documents\UpdateWolfpro.db3"
Try
If dbConnection.State = ConnectionState.Closed Then
dbConnection.ConnectionString = "Data Source=" & sDBPath &
";New=True;Compress=True;Synchronous=Off"
dbDataSet.Locale = CultureInfo.InvariantCulture
dbConnection.Open()
dbDataSet.Reset()
End If
SQLStmt = "ATTACH '" & sUpdateDBPath & "' AS UPD;"
Dim dbcommand As SQLiteCommand = dbConnection.CreateCommand
With dbcommand
.CommandText = SQLStmt
.ExecuteNonQuery()
.Dispose()
End With
SQLStmt = "INSERT INTO UPD.Items (" _
& "[Category]," _
& "[Supplier]," _
& "[Product Code]," _
& "[Short Description]," _
& "[Long Description]," _
& "[Unit Price]," _
& "[Maximum Sample Quantity]," _
& "[Box Price]," _
& "[Box Quantity]," _
& "[Deleted]," _
& "[Item Image File Path]," _
& "[Last Update]" _
& ") VALUES (" _
& "'AAA'," _
& "'BBB'," _
& "'XXX'," _
& "'DDD'," _
& "'EEE'," _
& "100," _
& "200," _
& "300," _
& "400," _
& "'NO'," _
& "'C:\XYZ.jpg'," _
& "''" _
& ")"
Dim dbcommand2 As SQLiteCommand = dbConnection.CreateCommand
With dbcommand2
.CommandText = SQLStmt
.ExecuteNonQuery()
.Dispose()
End With
Return True
Catch ex As Exception
MessageBox.Show(ex.Message)
Return False
End Try


On 13 August 2015 at 12:09, Simon Slavin  wrote:

>
> On 13 Aug 2015, at 10:16am, Chris Parsonson  wrote:
>
> > the ATTACH gives no error in either my first
> > attempt or this second one.
>
> Then it's probably working.
>
> >SQLStmt = "ATTACH '" & sUpdateDBPath & "' AS UPD;"
>
> So after you've done that, write some code to insert a new row into a
> table in UPD and then try to read the row back and see if it's there.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Chris Parsonson
083 777 9261

[sqlite] Database sybchronisation

[Jean-Christophe Deschamps]

Hi Simon,

>Method 2
>
>
>Ignore Method 1.  In each copy of your database keep a log of all 
>INSERT and UPDATE commands executed since the last 'synchronize':
>
>CREATE TABLE commandsSinceLastSynch (theCommand TEXT)
>
>To synchronise the two copies, play back the log for copy A to copy B 
>and the log for copy B to copy A.

Things are not that simple: you need to handle the case where a row has 
been updated in both DBs. Comparing update commit (important!) 
timestamps in both logs helps determine which one got first/last. Of 
course if triggers changed more and timestamps are close enough then 
FUD enters the room.

[sqlite] Database sybchronisation

[Simon Slavin]

On 13 Aug 2015, at 11:34am, Chris Parsonson  wrote:

> Has anybody ever used this ATTTACH command?
> Still doesn't work

The code shown does not read the row back again once it is written.  What makes 
you think your new row is not being stored ?  

What does it do when it doesn't work ?

Download the SQLite shell tool from



and try the same command.  Tell us what you get.

I suspect that your program is opening a new database file somewhere other than 
where you think it is.  Possibly in a temporary folder somewhere.

Simon.

[sqlite] Database sybchronisation

[Dominique Devienne]

On Thu, Aug 13, 2015 at 11:12 AM,  wrote:

> On Thu Aug 13, 2015 at 10:06:44AM +0200, Dominique Devienne wrote:
> > The new RBU [1] extension, coupled with the new sqldiff utility, might
> be of interest too.--DD
>
> Is there a table of contents or index of SQLite extensions somewhere on
> the main website?  The only page I can find is the /contrib one which
> doesn't include the above rbu, or FTS3/4, etc.


Not directly. But going to https://www.sqlite.org/sitemap.html and
searching for "extension" in the "Permuted Index" is a close approximation.
--DD

[sqlite] Database sybchronisation

[Chris Parsonson]

In fact I found the database in the Documents folder.
"C:\Users\Chris\Documents\UpdateWolfpro.db3"
but it made no difference. But the ATTACH gives no error in either my first
attempt or this second one.

On 13 August 2015 at 10:45, R.Smith  wrote:

>
>
> On 2015-08-13 10:26 AM, Chris Parsonson wrote:
>
>> OK here's my code which is just test code, not intended to do anything
>> useful. It runs without error, but the DELETE just doesn't do anything
>>  Dim dbConnection As New SQLiteConnection
>>  Dim dbDataSet As New DataSet
>>  Dim SQLStmt As String
>>  Dim sDBPath As String = Application.StartupPath & "\wolfpro.db3"
>>  Dim sUpdateDBPath As String = Application.StartupPath &
>> "\UpdateWolfpro.db3"
>>  Try
>>  If dbConnection.State = ConnectionState.Closed Then
>>  dbConnection.ConnectionString = "Data Source=" & sDBPath
>> &
>> ";New=True;Compress=True;Synchronous=Off"
>>  dbDataSet.Locale = CultureInfo.InvariantCulture
>>  dbConnection.Open()
>>  dbDataSet.Reset()
>>  End If
>>  SQLStmt = "ATTACH '" & sUpdateDBPath & "' AS UPD;"
>>  Dim dbcommand As SQLiteCommand = dbConnection.CreateCommand
>>  With dbcommand
>>  .CommandText = SQLStmt
>>  .ExecuteNonQuery()
>>  .Dispose()
>>  End With
>>  SQLStmt = "DELETE FROM UPD.Items;"
>>  Dim dbcommand2 As SQLiteCommand = dbConnection.CreateCommand
>>  With dbcommand2
>>  .CommandText = SQLStmt
>>  .ExecuteNonQuery()
>>  .Dispose()
>>  End With
>>  Return True
>>  Catch ex As Exception
>>  MessageBox.Show(ex.Message)
>>  Return False
>>  End Try
>>
>
> Afraid I'm not too familiar with VB, but from the looks of it I see a
> first problem seeming like you attach a database that sits in your
> application startup path... this won't work on any newer Windows if the
> user has UAC turned on since it doesn't allow anyone/anything to mess with
> files in the App folder (typically c:\ProgramFiles\ or c:\ProgramFiles
> (X86)\).
>
> The UAC will typically virtualize that folder into your AppPath somewhere,
> just search for that DB file in subfolders of "c:\Users\(your
> User)\AppData\" to see where it ended up.
>
> To verify this is not the problem, simply put that UpdateWolfpro DB in the
> Documents folder or AppData folder and adjust the code to suit.
> The correct place to put data files in Windows will be in the
> "c:\Users\(username)\AppData\Roaming\(YourAppName)\" (more or less) but
> that is another discussion.
>
> Let us know if that works.
>
>
>
>>
>> On 13 August 2015 at 10:12, R.Smith  wrote:
>>
>> To Attach a second Database is fairly straightforward and shouldn't fail
>>> unless there is a physical problem with either of the files.
>>>
>>> The Attach command example is like this (on a WinX machine):
>>>
>>> ATTACH DATABASE 'C:\Documents\OtherDatabase.db' AS "DB2";
>>>
>>> Execute that as a standard SQL statement. If that file does not exist, it
>>> will be created - but the path needs to be valid.
>>> If that statement does not work for you, please let us know what the
>>> error
>>> message is.
>>>
>>> Once the statement did work, the second database can be accessed by
>>> prepending any reference with "DB2", so assuming you have a table in the
>>> current database called "MyTable" that needs to add rows that doesn't
>>> exist
>>> yet in the same named (with the same schema) table in the attached
>>> database
>>> on a Primary key called "RecID", this SQL might work:
>>>
>>> INSERT INTO  DB2.MyTable   SELECT * FROM  MyTable  WHERE MyTable.RecID
>>> NOT
>>> IN (SELECT RecID FROM DB2.MyTable);
>>>
>>> More information here:
>>> http://www.sqlite.org/lang_attach.html
>>>
>>> On 2015-08-13 07:13 AM, Chris Parsonson wrote:
>>>
>>> Now we get down to the first real problem that of the ATTACH. I have
 never
 been able to get that to work. If I could have got the ATTACH to work I
 probably would never have had to ask the synchronisation question.
 Although
 I have never had to do it before in SQLite, I have worked a lot with SQL
 Server doing synchronisation there


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



-- 
Chris Parsonson
083 777 9261

[sqlite] Database sybchronisation

[no...@null.net]

On Thu Aug 13, 2015 at 10:06:44AM +0200, Dominique Devienne wrote:
> 
> The new RBU [1] extension, coupled with the new sqldiff utility,
> might be of interest too.--DD
> 
> [1] http://www.sqlite.org/rbu.html

Is there a table of contents or index of SQLite extensions somewhere on
the main website?  The only page I can find is the /contrib one which
doesn't include the above rbu, or FTS3/4, etc.

-- 
Mark Lawrence

[sqlite] Database sybchronisation

[Simon Slavin]

On 13 Aug 2015, at 10:16am, Chris Parsonson  wrote:

> the ATTACH gives no error in either my first
> attempt or this second one.

Then it's probably working.

>SQLStmt = "ATTACH '" & sUpdateDBPath & "' AS UPD;"

So after you've done that, write some code to insert a new row into a table in 
UPD and then try to read the row back and see if it's there.

Simon.

[sqlite] Database sybchronisation

[Kevin Benson]

On Thu, Aug 13, 2015 at 4:05 AM, Chris Parsonson  wrote:

> So can someone give me a working example of two database ATTACHed and a
> simple select using columns from a table in each database


C:\SQLite3>sqlite3 C:\SQLite3\UpdateWolfpro.db3
SQLite version 3.8.11.1 2015-07-29 20:00:57
Enter ".help" for usage hints.
sqlite> CREATE TABLE Items (
[Category] VARCHAR(50) NOT NULL,
[Supplier] VARCHAR(50) DEFAULT 'Default',
[Product Code] VARCHAR(20) NOT NULL,
[Short Description] VARCHAR(50),
[Long Description] VARCHAR(1000),
[Unit Price] INTEGER,
[Maximum Sample Quantity] INT,
[Box Price] INTEGER,
[Box Quantity] INT,
[Deleted] VARCHAR(5) DEFAULT 'No',
[Item Image File Path] VARCHAR(100),
[Last Update] TIMESTAMP, CONSTRAINT
[sqlite_autoindex_Items_1] PRIMARY KEY ([Product Code]));
sqlite> .quit

C:\SQLite3>sqlite3 C:\SQLite3\Wolfpro.db3
SQLite version 3.8.11.1 2015-07-29 20:00:57
Enter ".help" for usage hints.
sqlite> CREATE TABLE Items (
[Category] VARCHAR(50) NOT NULL,
[Supplier] VARCHAR(50) DEFAULT 'Default',
[Product Code] VARCHAR(20) NOT NULL,
[Short Description] VARCHAR(50),
[Long Description] VARCHAR(1000),
[Unit Price] INTEGER,
[Maximum Sample Quantity] INT,
[Box Price] INTEGER,
[Box Quantity] INT,
[Deleted] VARCHAR(5) DEFAULT 'No',
[Item Image File Path] VARCHAR(100),
[Last Update] TIMESTAMP, CONSTRAINT
[sqlite_autoindex_Items_1] PRIMARY KEY ([Product Code]));
sqlite> attach 'C:\SQLite3\UpdateWolfpro.db3' as UPD;
sqlite> INSERT INTO main.Items (
[Category], [Product Code]) VALUES("resistor", "300ohm");
sqlite> INSERT INTO UPD.Items (
[Category], [Product Code]) VALUES("capacitor", "47uf");
sqlite> SELECT * FROM UPD.Items;
capacitor|Default|47uf|||No||
sqlite> SELECT * FROM main.Items;
resistor|Default|300ohm|||No||
sqlite> DELETE FROM UPD.Items;
sqlite> SELECT * FROM UPD.Items;
sqlite> SELECT * FROM main.Items;
resistor|Default|300ohm|||No||
sqlite> .quit

C:\SQLite3>

--
   --
  --
 --???--
K e V i N

[sqlite] Database sybchronisation

[R.Smith]

On 2015-08-13 10:26 AM, Chris Parsonson wrote:
> OK here's my code which is just test code, not intended to do anything
> useful. It runs without error, but the DELETE just doesn't do anything
>  Dim dbConnection As New SQLiteConnection
>  Dim dbDataSet As New DataSet
>  Dim SQLStmt As String
>  Dim sDBPath As String = Application.StartupPath & "\wolfpro.db3"
>  Dim sUpdateDBPath As String = Application.StartupPath &
> "\UpdateWolfpro.db3"
>  Try
>  If dbConnection.State = ConnectionState.Closed Then
>  dbConnection.ConnectionString = "Data Source=" & sDBPath &
> ";New=True;Compress=True;Synchronous=Off"
>  dbDataSet.Locale = CultureInfo.InvariantCulture
>  dbConnection.Open()
>  dbDataSet.Reset()
>  End If
>  SQLStmt = "ATTACH '" & sUpdateDBPath & "' AS UPD;"
>  Dim dbcommand As SQLiteCommand = dbConnection.CreateCommand
>  With dbcommand
>  .CommandText = SQLStmt
>  .ExecuteNonQuery()
>  .Dispose()
>  End With
>  SQLStmt = "DELETE FROM UPD.Items;"
>  Dim dbcommand2 As SQLiteCommand = dbConnection.CreateCommand
>  With dbcommand2
>  .CommandText = SQLStmt
>  .ExecuteNonQuery()
>  .Dispose()
>  End With
>  Return True
>  Catch ex As Exception
>  MessageBox.Show(ex.Message)
>  Return False
>  End Try

Afraid I'm not too familiar with VB, but from the looks of it I see a 
first problem seeming like you attach a database that sits in your 
application startup path... this won't work on any newer Windows if the 
user has UAC turned on since it doesn't allow anyone/anything to mess 
with files in the App folder (typically c:\ProgramFiles\ or 
c:\ProgramFiles (X86)\).

The UAC will typically virtualize that folder into your AppPath 
somewhere, just search for that DB file in subfolders of "c:\Users\(your 
User)\AppData\" to see where it ended up.

To verify this is not the problem, simply put that UpdateWolfpro DB in 
the Documents folder or AppData folder and adjust the code to suit.
The correct place to put data files in Windows will be in the 
"c:\Users\(username)\AppData\Roaming\(YourAppName)\" (more or less) but 
that is another discussion.

Let us know if that works.

>
>
> On 13 August 2015 at 10:12, R.Smith  wrote:
>
>> To Attach a second Database is fairly straightforward and shouldn't fail
>> unless there is a physical problem with either of the files.
>>
>> The Attach command example is like this (on a WinX machine):
>>
>> ATTACH DATABASE 'C:\Documents\OtherDatabase.db' AS "DB2";
>>
>> Execute that as a standard SQL statement. If that file does not exist, it
>> will be created - but the path needs to be valid.
>> If that statement does not work for you, please let us know what the error
>> message is.
>>
>> Once the statement did work, the second database can be accessed by
>> prepending any reference with "DB2", so assuming you have a table in the
>> current database called "MyTable" that needs to add rows that doesn't exist
>> yet in the same named (with the same schema) table in the attached database
>> on a Primary key called "RecID", this SQL might work:
>>
>> INSERT INTO  DB2.MyTable   SELECT * FROM  MyTable  WHERE MyTable.RecID NOT
>> IN (SELECT RecID FROM DB2.MyTable);
>>
>> More information here:
>> http://www.sqlite.org/lang_attach.html
>>
>> On 2015-08-13 07:13 AM, Chris Parsonson wrote:
>>
>>> Now we get down to the first real problem that of the ATTACH. I have never
>>> been able to get that to work. If I could have got the ATTACH to work I
>>> probably would never have had to ask the synchronisation question.
>>> Although
>>> I have never had to do it before in SQLite, I have worked a lot with SQL
>>> Server doing synchronisation there
>>>
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>

[sqlite] Database sybchronisation

[Chris Parsonson]

OK here's my code which is just test code, not intended to do anything
useful. It runs without error, but the DELETE just doesn't do anything
Dim dbConnection As New SQLiteConnection
Dim dbDataSet As New DataSet
Dim SQLStmt As String
Dim sDBPath As String = Application.StartupPath & "\wolfpro.db3"
Dim sUpdateDBPath As String = Application.StartupPath &
"\UpdateWolfpro.db3"
Try
If dbConnection.State = ConnectionState.Closed Then
dbConnection.ConnectionString = "Data Source=" & sDBPath &
";New=True;Compress=True;Synchronous=Off"
dbDataSet.Locale = CultureInfo.InvariantCulture
dbConnection.Open()
dbDataSet.Reset()
End If
SQLStmt = "ATTACH '" & sUpdateDBPath & "' AS UPD;"
Dim dbcommand As SQLiteCommand = dbConnection.CreateCommand
With dbcommand
.CommandText = SQLStmt
.ExecuteNonQuery()
.Dispose()
End With
SQLStmt = "DELETE FROM UPD.Items;"
Dim dbcommand2 As SQLiteCommand = dbConnection.CreateCommand
With dbcommand2
.CommandText = SQLStmt
.ExecuteNonQuery()
.Dispose()
End With
Return True
Catch ex As Exception
MessageBox.Show(ex.Message)
Return False
End Try


On 13 August 2015 at 10:12, R.Smith  wrote:

> To Attach a second Database is fairly straightforward and shouldn't fail
> unless there is a physical problem with either of the files.
>
> The Attach command example is like this (on a WinX machine):
>
> ATTACH DATABASE 'C:\Documents\OtherDatabase.db' AS "DB2";
>
> Execute that as a standard SQL statement. If that file does not exist, it
> will be created - but the path needs to be valid.
> If that statement does not work for you, please let us know what the error
> message is.
>
> Once the statement did work, the second database can be accessed by
> prepending any reference with "DB2", so assuming you have a table in the
> current database called "MyTable" that needs to add rows that doesn't exist
> yet in the same named (with the same schema) table in the attached database
> on a Primary key called "RecID", this SQL might work:
>
> INSERT INTO  DB2.MyTable   SELECT * FROM  MyTable  WHERE MyTable.RecID NOT
> IN (SELECT RecID FROM DB2.MyTable);
>
> More information here:
> http://www.sqlite.org/lang_attach.html
>
> On 2015-08-13 07:13 AM, Chris Parsonson wrote:
>
>> Now we get down to the first real problem that of the ATTACH. I have never
>> been able to get that to work. If I could have got the ATTACH to work I
>> probably would never have had to ask the synchronisation question.
>> Although
>> I have never had to do it before in SQLite, I have worked a lot with SQL
>> Server doing synchronisation there
>>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Chris Parsonson
083 777 9261

[sqlite] Database sybchronisation

[Tim Streater]

On 13 Aug 2015 at 09:05, Chris Parsonson  wrote: 

> So can someone give me a working example of two database ATTACHed and a
> simple select using columns from a table in each database

Why not try the sequence you want using the sqlite3 CLI program, on a simple 
example that mimics the essence of your production case. That way you can be 
sure you've got the SQL/SQLite part right before trying to incorporate it in 
your program.

--
Cheers  --  Tim

[sqlite] Database sybchronisation

[R.Smith]

To Attach a second Database is fairly straightforward and shouldn't fail 
unless there is a physical problem with either of the files.

The Attach command example is like this (on a WinX machine):

ATTACH DATABASE 'C:\Documents\OtherDatabase.db' AS "DB2";

Execute that as a standard SQL statement. If that file does not exist, 
it will be created - but the path needs to be valid.
If that statement does not work for you, please let us know what the 
error message is.

Once the statement did work, the second database can be accessed by 
prepending any reference with "DB2", so assuming you have a table in the 
current database called "MyTable" that needs to add rows that doesn't 
exist yet in the same named (with the same schema) table in the attached 
database on a Primary key called "RecID", this SQL might work:

INSERT INTO  DB2.MyTable   SELECT * FROM  MyTable  WHERE MyTable.RecID 
NOT IN (SELECT RecID FROM DB2.MyTable);

More information here:
http://www.sqlite.org/lang_attach.html

On 2015-08-13 07:13 AM, Chris Parsonson wrote:
> Now we get down to the first real problem that of the ATTACH. I have never
> been able to get that to work. If I could have got the ATTACH to work I
> probably would never have had to ask the synchronisation question. Although
> I have never had to do it before in SQLite, I have worked a lot with SQL
> Server doing synchronisation there

[sqlite] Database sybchronisation

[Dominique Devienne]

On Thu, Aug 13, 2015 at 4:52 AM, Chris Parsonson  wrote:

> I need to be able to synchronise some fairly simple table in two databases.
> Can someone help please
>

The new RBU [1] extension, coupled with the new sqldiff utility, might be
of interest too.--DD

[1] http://www.sqlite.org/rbu.html

[sqlite] Database sybchronisation

[Chris Parsonson]

So can someone give me a working example of two database ATTACHed and a
simple select using columns from a table in each database

On 13 August 2015 at 07:13, Chris Parsonson  wrote:

> Now we get down to the first real problem that of the ATTACH. I have never
> been able to get that to work. If I could have got the ATTACH to work I
> probably would never have had to ask the synchronisation question. Although
> I have never had to do it before in SQLite, I have worked a lot with SQL
> Server doing synchronisation there
>
> On 13 August 2015 at 07:08, Simon Slavin  wrote:
>
>>
>> On 13 Aug 2015, at 5:55am, Chris Parsonson  wrote:
>>
>> > The tables are very simple. They have a primary key, but no relationship
>> > between tables in the sense that you mean. Synchronisation will be add
>> new
>> > rows, and update some rows, no deletions
>>
>> To access two different databases with one database connection, use the
>> ATTACH command.
>>
>> Method 1
>> 
>>
>> To find rows which are in one table but not in the other, use EXCEPT
>>
>> SELECT primaryKeyColumn FROM databasea.myTable EXCEPT SELECT
>> primaryKeyColumn FROM myTable
>>
>> In cases where a row has been updated with a new value in one column, how
>> do you propose to decide which value is the 'right' one to put in both
>> databases ?
>>
>> Method 2
>> 
>>
>> Ignore Method 1.  In each copy of your database keep a log of all INSERT
>> and UPDATE commands executed since the last 'synchronize':
>>
>> CREATE TABLE commandsSinceLastSynch (theCommand TEXT)
>>
>> To synchronise the two copies, play back the log for copy A to copy B and
>> the log for copy B to copy A.
>>
>> Simon.
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
>
> --
> Chris Parsonson
> 083 777 9261
>



-- 
Chris Parsonson
083 777 9261

[sqlite] Database sybchronisation

[Chris Parsonson]

Now we get down to the first real problem that of the ATTACH. I have never
been able to get that to work. If I could have got the ATTACH to work I
probably would never have had to ask the synchronisation question. Although
I have never had to do it before in SQLite, I have worked a lot with SQL
Server doing synchronisation there

On 13 August 2015 at 07:08, Simon Slavin  wrote:

>
> On 13 Aug 2015, at 5:55am, Chris Parsonson  wrote:
>
> > The tables are very simple. They have a primary key, but no relationship
> > between tables in the sense that you mean. Synchronisation will be add
> new
> > rows, and update some rows, no deletions
>
> To access two different databases with one database connection, use the
> ATTACH command.
>
> Method 1
> 
>
> To find rows which are in one table but not in the other, use EXCEPT
>
> SELECT primaryKeyColumn FROM databasea.myTable EXCEPT SELECT
> primaryKeyColumn FROM myTable
>
> In cases where a row has been updated with a new value in one column, how
> do you propose to decide which value is the 'right' one to put in both
> databases ?
>
> Method 2
> 
>
> Ignore Method 1.  In each copy of your database keep a log of all INSERT
> and UPDATE commands executed since the last 'synchronize':
>
> CREATE TABLE commandsSinceLastSynch (theCommand TEXT)
>
> To synchronise the two copies, play back the log for copy A to copy B and
> the log for copy B to copy A.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Chris Parsonson
083 777 9261

[sqlite] Database sybchronisation

[Chris Parsonson]

The tables are very simple. They have a primary key, but no relationship
between tables in the sense that you mean. Synchronisation will be add new
rows, and update some rows, no deletions

On 13 August 2015 at 05:53, Simon Slavin  wrote:

>
> On 13 Aug 2015, at 3:52am, Chris Parsonson  wrote:
>
> > I need to be able to synchronise some fairly simple table in two
> databases.
>
> Are both copies of a table being changed between synchronisations ?  Are
> the changes just the adding of new rows, or do you sometimes delete or
> update rows ?
>
> Is the database schema relational ?  In other words, do you have a column
> of one table as the primary key of another table ?
>
> If the answer to all the above questions is 'yes' then it is very
> difficult to synchronise two copies of one database.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Chris Parsonson
083 777 9261

[sqlite] Database sybchronisation

[Simon Slavin]

On 13 Aug 2015, at 5:55am, Chris Parsonson  wrote:

> The tables are very simple. They have a primary key, but no relationship
> between tables in the sense that you mean. Synchronisation will be add new
> rows, and update some rows, no deletions

To access two different databases with one database connection, use the ATTACH 
command.

Method 1


To find rows which are in one table but not in the other, use EXCEPT

SELECT primaryKeyColumn FROM databasea.myTable EXCEPT SELECT primaryKeyColumn 
FROM myTable

In cases where a row has been updated with a new value in one column, how do 
you propose to decide which value is the 'right' one to put in both databases ?

Method 2


Ignore Method 1.  In each copy of your database keep a log of all INSERT and 
UPDATE commands executed since the last 'synchronize':

CREATE TABLE commandsSinceLastSynch (theCommand TEXT)

To synchronise the two copies, play back the log for copy A to copy B and the 
log for copy B to copy A.

Simon.

[sqlite] Database sybchronisation

[Simon Slavin]

On 13 Aug 2015, at 3:52am, Chris Parsonson  wrote:

> I need to be able to synchronise some fairly simple table in two databases.

Are both copies of a table being changed between synchronisations ?  Are the 
changes just the adding of new rows, or do you sometimes delete or update rows ?

Is the database schema relational ?  In other words, do you have a column of 
one table as the primary key of another table ?

If the answer to all the above questions is 'yes' then it is very difficult to 
synchronise two copies of one database.

Simon.

[sqlite] Database sybchronisation

[Chris Parsonson]

Hi SQLite people,
I need to be able to synchronise some fairly simple table in two databases.
Can someone help please

Regards,

Chris

-- 
Chris Parsonson
083 777 9261