[Tutor] Is it possible to archive subsets of data from an SQLite db and restore it later?

[boB Stepp]

My wife had an interesting request tonight: Would it be possible to
have two dbs, one that is the current working db, and the other an
archival db for students who have left the school? If yes, then the
archival db would need to be able to serve two functions:

1) If the teacher gets a request for certain types of reports, then
she should be able to generate them from the archival db just as she
would be able to from her working db. She would want the archival db
to retain the same structure and functionality as the current working
db.

2) If a student returns to the school as a student, it should be
possible to reintegrate all of that student's data from the archival
db back into the current working db.

I see this more as an interesting problem right now as I think it
would take a long time to fill up SQLite's 2 TB max size. It seems to
me like it should be doable, but might be quite complicated. OTH, I
don't know much about SQL yet, and it might actually be more trivial
than I can currently imagine. So I thought I would ask the group to
see which category of problem this truly is.

TIA!

P.S.: After doing a bunch of reading on unit testing yesterday and
today, I fired up Vim to a nice blank screen and was actually going to
start writing docstrings and my first test for the class I have not
yet written, but have been thinking about, when I realized that the
method I was going to write a test for would not be an easy thing to
do. Which made me realize that the method I was hoping to start
coding tonight was ill-conceived! ARGH! To bed I now go ... Is TDD
the programmer's version of writer's block? ~(:>)

[Zachary Ware]

Post by boB Stepp
My wife had an interesting request tonight: Would it be possible to
have two dbs, one that is the current working db, and the other an
archival db for students who have left the school? If yes, then the
1) If the teacher gets a request for certain types of reports, then
she should be able to generate them from the archival db just as she
would be able to from her working db. She would want the archival db
to retain the same structure and functionality as the current working
db.
2) If a student returns to the school as a student, it should be
possible to reintegrate all of that student's data from the archival
db back into the current working db.
I see this more as an interesting problem right now as I think it
would take a long time to fill up SQLite's 2 TB max size. It seems to
me like it should be doable, but might be quite complicated. OTH, I
don't know much about SQL yet, and it might actually be more trivial
than I can currently imagine. So I thought I would ask the group to
see which category of problem this truly is.

i think rather than try to move data between two DBs, I'd just add a
Boolean 'currently_enrolled' column to the Students table. When you
care about whether the student is currently enrolled or not, add
'WHERE currently_enrolled = true' (or however that needs to be
spelled) to your query. When the student graduates/moves/re-enrolls,
just update that value.

I'm not hugely experienced with databases/SQL myself either, though.

[Alan Gauld]

Post by Zachary Ware

Post by boB Stepp
My wife had an interesting request tonight: Would it be possible to
have two dbs, one that is the current working db, and the other an
archival db for students who have left the school?

i think rather than try to move data between two DBs, I'd just add a
Boolean 'currently_enrolled' column to the Students table.

Yep, that's exactly what i was going to suggest.
A flag column that can be used to include/exclude students
from reports.

Another refinement is a master student table including the flag
and two views derived from the master. One with the flag on
and the other with the flag off. Updates still need to be
on the master table but since they will likely be using the
ID that shouldn't be a problem.
--
Alan G
Author of the Learn to Program web site
http://www.alan-g.me.uk/
http://www.amazon.com/author/alan_gauld
Follow my photo-blog on Flickr at:
http://www.flickr.com/photos/alangauldphotos


_______________________________________________
Tutor maillist - ***@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman/listinfo/tutor

[Ben Finney]

A flag column that can be used to include/exclude students from
reports.

Boolean flags very commonly indicate something that can be different at
different times. When the name is of the form “currently_foo”, that's
almost certainly something that is different at different points in
time.

So better than a boolean in the database, would be a timestamp (or just
a date) indicating *when* the status changes. Either an ‘enrolled’
timestamp, or a ‘departed’ timestamp, or both.

Then you can interrogate the database about past, present, or future, by
comparing that value with whatever point in time is of interest.
--
\ “Faith is the determination to remain ignorant in the face of |
`\ all evidence that you are ignorant.” —Shaun Mason |
_o__) |
Ben Finney

_______________________________________________
Tutor maillist - ***@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailma

[Alan Gauld]

Post by Ben Finney

A flag column that can be used to include/exclude students from
reports.

So better than a boolean in the database, would be a timestamp (or just
a date) indicating *when* the status changes. Either an ‘enrolled’
timestamp, or a ‘departed’ timestamp, or both.
Then you can interrogate the database about past, present, or future, by
comparing that value with whatever point in time is of interest.

A date might be useful but its much harder to incorporate
into other reports. And given that virtually every query
potentially has to include this test (unless we go with
two views) that's a lot of overhead.

I'd go with a status flag (isEnrolled or somesuch) and if dates are
needed for specific reports add that as additional columns against
the flag. (enrollmentDate, leavingDate, etc)

But I'd also consider the YAGNI(*) principle. It's easy to add lots
of fields to databases that add to the admin load but are rarely,
if ever, used.

(*)You Aren't Going to Need It
--
Alan G
Author of the Learn to Program web site
http://www.alan-g.me.uk/
http://www.amazon.com/author/alan_gauld
Follow my photo-blog on Flickr at:
http://www.flickr.com/photos/alangauldphotos


_______________________________________________
Tutor maillist - ***@python.org
To unsubscribe or change subscription options:
https://

[Ben Finney]

My wife had an interesting request tonight: Would it be possible to
have two dbs, one that is the current working db, and the other an
archival db for students who have left the school?

(Note that this isn't anything to do with Python per se, and would be
better discussed on an SQLite-specific forum.)

A relational database (such as implemented by SQLite) is designed to
contain all the relevant truths, and is not designed to have truths
split across different databases.

So you will be better served by *adding* the distinction (“date when
this student left the school”) to the database, and use that distinction
in queries.

To make your job easier, you can store the distinguishing field and
prepare “views” which show only subsets (“students who have left the
school”, “students who have not left the school”, “all students”)
<URL: https://www.sqlite.org/lang_createview.html> against which you can
perform further queries.

I realized that the method I was going to write a test for would not
be an easy thing to do. Which made me realize that the method I was
hoping to start coding tonight was ill-conceived! ARGH! To bed I now
go ... Is TDD the programmer's version of writer's block? ~(:>)

No, it's the programmer's equivalent of saving time by having that
realisation come *before* committing wasted time on a poor design :-)
--
\ “If you don't fail at least 90 percent of the time, you're not |
`\ aiming high enough.” —Alan Kay |
_o__) |
Ben Finney

_______________________________________________
Tutor maillist - ***@python.org
To unsubscribe or change subscription options:
https://mail.python.org/mailman