Re: [sqlite] [EXTERNAL] Persistent snapshots and rollbacks

[Daniel Kraft]

Hi!

On 2018-10-08 08:11, Hick Gunter wrote:
> 1) Include an "inserted at" timestamp column in each table
> 2) create a history table for each real table
> 3) use before triggers to copy the old record to the history table and set 
> the "inserted at" timestamp

Using triggers is an interesting approach that I hadn't thought of
before.  That makes "manual" handling of the history transparent to the
code that performs operations.  (And avoids the need to instrument /
change every line of code that does a change to the database.)

However, that still means that I need to build the system for a
particular database schema (set of tables and fields in them), right?
In my situation, I would ideally like to apply a method that works "out
of the box" independent of the underlying database schema (because I'm
building an application *platform* rather than a particular
application).  The sessions extension seems to provide this, so I'll try
that one first to see if it fits my requirements.

Yours,
Daniel

-- 
https://www.domob.eu/
OpenPGP: 1142 850E 6DFF 65BA 63D6  88A8 B249 2AC4 A733 0737
Namecoin: id/domob -> https://nameid.org/?name=domob
--
3.6.0: Bar-Pri-Ran-Rog-Sam-Val-Wiz
To go: Arc-Cav-Hea-Kni-Mon-Tou



signature.asc
Description: OpenPGP digital signature
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Persistent snapshots and rollbacks

[Hick Gunter]

1) Include an "inserted at" timestamp column in each table
2) create a history table for each real table
3) use before triggers to copy the old record to the history table and set the 
"inserted at" timestamp

If you use temp triggers, you can even keep the history tables in a separate 
database from the live tables and even switch to a new history database every 
week or month or whatever schedule fits your application.

Reading the old state is just a group by primary key query over the live and 
history table. And restoring a historic state just means saving the result set 
of the old state query into the live table.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Daniel Kraft
Gesendet: Freitag, 05. Oktober 2018 17:40
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Persistent snapshots and rollbacks

Hi!

I need the ability to make multiple changes / commits to my SQLite database but 
keep snapshots of previous states and potentially roll back to those states 
later on.  All of that needs to be persistent, i.e.
survive closing the database and restarting the process.  After some time, I 
can get rid of old snapshots (my process determines by itself when and which 
snapshots can get discarded, it is not based on some fixed TTL or something 
like that).

Is it possible to do all that with SQLite?

From reading the docs, it seems to me that (persistent) WAL mode basically does 
*exactly that* internally:  Changes are recorded in the logs so that previous 
versions are retained.  Rollbacks would be possible by "simply" discarding the 
WAL entries after the desired snapshot.  And discarding of very old snapshots 
corresponds to checkpointing.

However, I'm not sure if all of that functionality is (officially) exposed to 
me as a user.  There are in particular two points where I think that my 
requirements differ from the functionality that WAL mode
exposes:

1) Handles to snapshots can be obtained and stored, but they are read-only.  It 
seems to be not possible to tell SQLite to restore the WAL to a previous 
version and then continue modifying from that version.
 (Which basically means truncating the WAL file at a certain point.)

2) From what I have seen, checkpointing can only be triggered for the full WAL 
(or whatever is possible with existing readers) and not selectively up to a 
desired point.  Of course I could work around that by creating a reader at the 
point I want to keep.  But then I wonder if it is a problem if the WAL can 
never be *fully* checkpointed (as in my requirement).  Would that mean that it 
keeps on growing forever, or is checkpointing able to remove parts from the 
beginning of the WAL?

Is my understanding here correct?  And is there some way in which I could 
achieve my requirements using WAL mode (or somehow else)?

Thank you very much!

Yours,
Daniel

--
https://www.domob.eu/
OpenPGP: 1142 850E 6DFF 65BA 63D6  88A8 B249 2AC4 A733 0737
Namecoin: id/domob -> https://nameid.org/?name=domob
--
3.6.0: Bar-Pri-Ran-Rog-Sam-Val-Wiz
To go: Arc-Cav-Hea-Kni-Mon-Tou



___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Windows GUI alternative to Excel?

[Kees Nuyt]

On Sun, 07 Oct 2018 15:25:26 -0600, "Keith Medcalf"
 wrote:

> Many people do not "do" web forums.  I am one of them. 

So am I. But:

> If there is not a mailing list then it does not exist.

The fossil forum sends notification mails, with the full text of
the forum posts, complete with references headers, so it threads
correctly, just like a mailing list.
For lurking mode, there is nothing else you have to do.

Once you decide you want to follow-up on a posting, the
procedure is simple: Each message contains a hyperlink to that
exact posting in the webforum. If you don't want to be
anonymous, you only have to login. 
Click the reply button to reply. That's all.
No further navigation or browsing required.

For me, this is a very acceptable alternative for a mailing
list. YMMV, I know you post  more often than I do.

-- 
Regards,
Kees Nuyt

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

Re: [sqlite] SQLite Windows GUI alternative to Excel?

[Keith Medcalf]

Many people do not "do" web forums.  I am one of them.  If there is not a 
mailing list then it does not exist.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.


>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Warren Young
>Sent: Sunday, 7 October, 2018 11:13
>To: SQLite mailing list
>Subject: Re: [sqlite] SQLite Windows GUI alternative to Excel?
>
>On Oct 6, 2018, at 10:23 AM, Warren Young  wrote:
>>
>> What we want is a SQLite-based program along the lines of Access or
>FileMaker, preferably with some kind of cloud capability.
>
>I’ve come up with a plan to do this within Fossil, or as a fork of
>it:
>
>https://fossil-scm.org/forum/forumpost/0f9f9f4733
>
>Incidentally, those on this mailing list might want to visit that
>forum to preview what is likely to replace this mailing list at some
>point.  The formatting that was easily done for that post is just one
>of the many features we get from this new web forum feature in
>Fossil.
>___
>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] Ajqvue v2.10 Released

[Ned Fleming]

On 2018-10-07 2:34 PM, dmp wrote:

Ajqvue Version 2.10 Released

The Ajqvue project is pleased to release v2.10 to the public. The release


Looks good.

Would pronouncing ajqvue -- achoo? -- send Mr. Mxyzptlk back to the 5th 
dimension for 90 days?


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

Re: [sqlite] SQLite Windows GUI alternative to Excel?

[Peter da Silva]

There's a couple of Tcl/Tk SQLITE database managers that could be more easily 
turned into something like the Access GUI than starting from scratch. And they 
wouldn't be limited to Windows.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Ajqvue v2.10 Released

[dmp]

Ajqvue Version 2.10 Released

The Ajqvue project is pleased to release v2.10 to the public. The release
is minor in nature with a new feature to allow opening local file databases
in the Login Frame via a file chooser. Work was also completed to support
the DB_to_FileMemoryDB plugin, package utilities.db, and update the
QueryBuilder plugin.

Dana M. Proctor
Ajqvue Project Manager
http://ajqvue.com
https://github.com/danap/ajqvue


Ajqvue provides an easy to use Java based user interface front-end
for viewing, adding, editing, or deleting entries in several mainstream
databases. A query frame allows the building of complex SQL statements
and a SQL Query Bucket for saving such. The application allows easy
sorting, searching, and import/export of table data. A plug-in framework
has allowed the inclusion of tools to visually build queries, profile and
plot data for analysis.

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

Re: [sqlite] SQLite Windows GUI alternative to Excel?

[Warren Young]

On Oct 7, 2018, at 12:28 PM, Luuk  wrote:
> 
> The 'software development department' should forbid 'software
> developmentprojects' which iare not done by them.

Yes, and corporate laptops should be absolutely locked down, so that people 
can’t install software not approved by IT, which list will usually not include 
SQLite, there being no CIO lunches put on by SQLite, Inc.

Mainframe computers were replaced by weak little home computers, and those 
desktop computers were replaced by laptops, and those laptops are now being 
replaced by smartphones because end users want and deserve some control over 
their computing environment.  Central authorities that try to prevent this have 
always lost in the end, and they will continue to lose.

Any organization that demands complete centralized control over all things IT 
will fail to outcompete those that allow their users some freedom, as long as 
there are no competition breakdowns due to regulatory capture, monopolies, etc.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Windows GUI alternative to Excel?

[Luuk]

On 7-10-2018 01:18, Warren Young wrote:
> On Oct 6, 2018, at 2:21 PM, Simon Slavin  wrote:
>>
>> Excel ate the financial business world because companies use Excel to solve 
>> a simple problem, then add a feature, then add another feature, and keep 
>> going until they have some crawling creeping horror that needs to return to 
>> R'lyeh.  There is never any point in this process when a manager looks at 
>> what's being done with Excel and says "Okay we need to hire a programmer to 
>> turn this into a proper App.”.
> Up to a certain point, there’s nothing wrong with that process.

Yes, there is!
>
> One of the tasks smart management should be doing is keeping an eye on these 
> guerrilla software development projects and step in when it becomes clear 
> they’ve got a valuable business tool that needs to be rewritten on a stronger 
> foundation to allow its continued growth and increasing value to the business.
The 'software development department' should forbid 'software
developmentprojects' which iare not done by them.
>
> If your business is big enough to have an IT staff with at least one 
> professional programmer, turning Excel prototypes into professional business 
> tools is mainly a matter of scheduling.  (Again, a function of management.)
"turning Excel prototypes into..." is a NO-GO.  It simply means that IT
staff did not do its own work 'the correct way'!.
>
> The main need for a personal database is in organizations too small to have 
> full-time programming staff.  (That includes most home use as well.)
>
> Maybe that’s the biggest reason this sort of software is rare and expensive: 
> big business doesn’t need it, so it only gets funded by small businesses and 
> home users, who are notoriously tight when it comes to software licensing.
>
> Contrast the elements of a traditional office software suite, which are 
> needed clear through the Fortune uint8_t.
> ___
>

At the office where i work we have an Excel sheet which takes >10
minutes to load, and i do not beleive its because of the absolute size
of the excel sheet
This Excel sheet contains (or tries to contain) )functionality which
should have been implemented a long time ago in our ERP-application.




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

Re: [sqlite] SQLite Windows GUI alternative to Excel?

[Warren Young]

On Oct 6, 2018, at 10:23 AM, Warren Young  wrote:
> 
> What we want is a SQLite-based program along the lines of Access or 
> FileMaker, preferably with some kind of cloud capability.

I’ve come up with a plan to do this within Fossil, or as a fork of it:

https://fossil-scm.org/forum/forumpost/0f9f9f4733

Incidentally, those on this mailing list might want to visit that forum to 
preview what is likely to replace this mailing list at some point.  The 
formatting that was easily done for that post is just one of the many features 
we get from this new web forum feature in Fossil.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Persistent snapshots and rollbacks

[Jean-Luc Hainaut]

This suggestion refers to temporal DB.  To those interested by this 
approach, this tutorial could help (implementation coded in SQLite):


https://staff.info.unamur.be/dbm/Documents/Tutorials/SQLfast/SQLfast-Case09-Temporal-DB(1).pdf 



J-L Hainaut


On 05/10/2018 20:27, Richard Damon wrote:

On 10/5/18 2:19 PM, James K. Lowden wrote:

On Fri, 5 Oct 2018 17:39:57 +0200
Daniel Kraft  wrote:


I need the ability to make multiple changes / commits to my SQLite
database but keep snapshots of previous states and potentially roll
back to those states later on.  All of that needs to be persistent,
i.e. survive closing the database and restarting the process.  After
some time, I can get rid of old snapshots (my process determines by
itself when and which snapshots can get discarded, it is not based on
some fixed TTL or something like that).

"The totality of data in a data bank may be viewed
as a collection of time-varying relations."
-- E.F. Codd in
"A Relational Model of Data for Large Shared Data Banks"

You're not the first.  Data change over time.  SQL doesn't support data
versions as a language feature, but you can implement it yourself
in your database design.

Add a "version" column to your table.  Create views that (using a
self-join) show only the latest version. Periodically purge old
versions.  Roll back by deleting new versions.

One design that I have used is to add two timestamps to every record
(with sufficient precision for your versioning, it could be a version
number too), one is the starting time for the record, and the second for
the ending time (NULL if to 'now'). To update a record, you get the
current time stamp (or next version number), alter the existing record
to have that as its end and create a new record with it as the start
time and NULL for the end. To get 'current' data, you condition selects
with ISNULL(endtime), to get a historical record you select such that
start is less than or equal to the time, and the end is greater than the
time or NULL.

You can purge old records based on the end time being old enough, or
total roll back by deleting records with start greater than the time,
and changing end date greater to NULL.



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

Re: [sqlite] SQLite Windows GUI alternative to Excel?

[Tim Streater]

On 07 Oct 2018, at 00:18, Warren Young  wrote:

> On Oct 6, 2018, at 2:21 PM, Simon Slavin  wrote:
>> 
>>  Excel ate the financial business world because companies use Excel to solve
>> a simple problem, then add a feature, then add another feature, and keep
>> going until they have some crawling creeping horror that needs to return to
>> R'lyeh.  There is never any point in this process when a manager looks at
>> what's being done with Excel and says "Okay we need to hire a programmer to
>> turn this into a proper App.”.
>
> Up to a certain point, there’s nothing wrong with that process.
>
> One of the tasks smart management should be doing is keeping an eye on these
> guerrilla software development projects and step in when it becomes clear
> they’ve got a valuable business tool that needs to be rewritten on a stronger
> foundation to allow its continued growth and increasing value to the business.

But they don't IME. Someone hacks something together as a proof of concept, and 
management thinks that it's job done. The hack, which requires some manpower to 
use, and which is not integrated with other systems, is then handed over to 
Operations.



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