Keeping python code and database in sync
Hi all Now that I have bitten the bullet and published my repository, I am forced to change my working practices (which is a good thing!). The project is inherently database-driven. The python code expects to find certain tables and columns in the database. As I develop new features, I sometimes need to modify the database structure. In the bad old days (like yesterday) I would just make the modifications and carry on. Now I have to be aware that others may have downloaded the project, so I have to consider how to ensure that their database is kept up to date. It is a simple matter to write a program that updates the database automatically. The question is, what should trigger such an update? My first thought is to use a version number - store a version number in the working directory, and have a matching number in the code. If someone downloads the latest version, the numbers will no longer match, and I can run the upgrade program. The problem with that is that version numbers are usually reserved for tagged releases, but this could happen as the result of any commit in the current development cycle. Any suggestions? Frank Millman -- https://mail.python.org/mailman/listinfo/python-list
Re: Keeping python code and database in sync
On Friday, August 29, 2014 6:12:06 PM UTC+5:30, Frank Millman wrote: > Hi all > Now that I have bitten the bullet and published my repository, I am forced > to change my working practices (which is a good thing!). > The project is inherently database-driven. The python code expects to find > certain tables and columns in the database. As I develop new features, I > sometimes need to modify the database structure. In the bad old days (like > yesterday) I would just make the modifications and carry on. Now I have to > be aware that others may have downloaded the project, so I have to consider > how to ensure that their database is kept up to date. There are tools like this http://alembic.readthedocs.org/en/latest/ It may help to read that to avoid reinvention > It is a simple matter to write a program that updates the database > automatically. The question is, what should trigger such an update? My first > thought is to use a version number - store a version number in the working > directory, and have a matching number in the code. If someone downloads the > latest version, the numbers will no longer match, and I can run the upgrade > program. > The problem with that is that version numbers are usually reserved for > tagged releases, but this could happen as the result of any commit in the > current development cycle. I dont think alembic can solve that. Still it may help to study it -- https://mail.python.org/mailman/listinfo/python-list
Re: Keeping python code and database in sync
On Fri, Aug 29, 2014 at 10:42 PM, Frank Millman wrote:
> It is a simple matter to write a program that updates the database
> automatically. The question is, what should trigger such an update? My first
> thought is to use a version number - store a version number in the working
> directory, and have a matching number in the code. If someone downloads the
> latest version, the numbers will no longer match, and I can run the upgrade
> program.
This is a well-known problem, and there's no really perfect solution.
The first thing to consider is: What happens if someone back-levels
the program? If you can afford to say "never back-level past a schema
change", then you can simply version the schema, independently of the
code. A simple incrementing integer will do - you don't need a
multipart version number. Then you just have code like this:
# Get the current schema version, or 0 if there's nothing yet
version = db.query("select schema_version from config")
if version < 1:
# Brand new database
db.query("create table blah blah")
db.query("create table spam")
# etc
if version < 2:
db.query("alter table spam add whatever")
# Add new patch levels here
db.query("update config set schema_version = 2")
else:
throw_really_noisy_error("YOU BACKLEVELLED!")
To add a new patch level, you add a new condition with the next
number, add its code, and change the update statement at the end. So
it'd look like this:
if version < 2:
db.query("alter table spam add whatever")
+if version < 3:
+db.query("create table brand_new_table")
# Add new patch levels here
-db.query("update config set schema_version = 2")
+db.query("update config set schema_version = 3")
else:
throw_really_noisy_error("YOU BACKLEVELLED!")
It's fairly straight-forward and readable. You'll sometimes need to go
back and defang old patch code (if you simplify or break stuff), and
you might prefer to keep your patch 0 handling up-to-date (so it
doesn't then have to do all the rest of the patches - have that one
immediately set version and bail out), but that's a basic structure
that's been proven in real-world usage. (Note that the exact code
above might be buggy. I'm recreating from memory and porting to Python
at the same time. But the design intent is there.)
Ideally, you want to minimize churn. Don't do heaps of schema changes
in a short period of time. But this can handle plenty of changes
fairly easily, and it'll handle either incremental changes or big
blocks of them just the same way (if you upgrade from patch level 10
to patch level 35 all at once, it'll just grind through all those
changes one after another).
ChrisA
--
https://mail.python.org/mailman/listinfo/python-list
Re: Keeping python code and database in sync
"Rustom Mody" wrote in message news:1cdf6e52-e09b-40f1-8db1-db6cbbee9...@googlegroups.com... > On Friday, August 29, 2014 6:12:06 PM UTC+5:30, Frank Millman wrote: >> Hi all > >> Now that I have bitten the bullet and published my repository, I am >> forced >> to change my working practices (which is a good thing!). > >> The project is inherently database-driven. The python code expects to >> find >> certain tables and columns in the database. As I develop new features, I >> sometimes need to modify the database structure. In the bad old days >> (like >> yesterday) I would just make the modifications and carry on. Now I have >> to >> be aware that others may have downloaded the project, so I have to >> consider >> how to ensure that their database is kept up to date. > > > There are tools like this > http://alembic.readthedocs.org/en/latest/ > > It may help to read that to avoid reinvention > Thanks for the link, Rustom. I glanced at it, and it looks very powerful, but I will have to find the time to study it at leisure. In the meantime, Chris' suggestion is simple to implement and adequate for my present needs, so I will run with that for now. Frank -- https://mail.python.org/mailman/listinfo/python-list
Re: Keeping python code and database in sync
"Chris Angelico" wrote in message news:CAPTjJmrJBciRuterUKWP=qtqxd8xyqum4nx+ofd-twm5oos...@mail.gmail.com... > On Fri, Aug 29, 2014 at 10:42 PM, Frank Millman > wrote: >> It is a simple matter to write a program that updates the database >> automatically. The question is, what should trigger such an update? My >> first >> thought is to use a version number - store a version number in the >> working >> directory, and have a matching number in the code. If someone downloads >> the >> latest version, the numbers will no longer match, and I can run the >> upgrade >> program. > > This is a well-known problem, and there's no really perfect solution. > The first thing to consider is: What happens if someone back-levels > the program? If you can afford to say "never back-level past a schema > change", then you can simply version the schema, independently of the > code. A simple incrementing integer will do - you don't need a > multipart version number. Thanks, Chris, this sounds ideal for my current requirements. You mentioned keeping schema changes to a minimum, which I agree with, but that is not actually my main problem. Right now I am writing a tool to allow users to view and modify menu definitions. The tool is effectively a form definition, which in my system is expressed in xml and stored in the database in the 'sys_form_defns' table. The raw xml will be uploaded as part of the commit, and will be downloaded when someone pulls the latest version, but it still has to be inserted into the database before it is accessible. It is not a problem - I can use the same mechanism that you described - but it will be happening quite a lot until the system settles down. Frank -- https://mail.python.org/mailman/listinfo/python-list
Re: Keeping python code and database in sync
On Fri, Aug 29, 2014 at 11:31 PM, Frank Millman wrote: > Right now I am writing a tool to allow users to view and modify menu > definitions. The tool is effectively a form definition, which in my system > is expressed in xml and stored in the database in the 'sys_form_defns' > table. The raw xml will be uploaded as part of the commit, and will be > downloaded when someone pulls the latest version, but it still has to be > inserted into the database before it is accessible. > > It is not a problem - I can use the same mechanism that you described - but > it will be happening quite a lot until the system settles down. The system I described is intended for cases where each edit builds on the previous ones - for instance, if patch 4 creates a table and patch 7 adds a column to it, you have to have done them in that order. But for that XML data, it sounds as if each update completely overwrites all previous ones - is this right? In that case, I'd simplify it: after doing all the other patches (anything more complicated than "update this one column" can still go through a patching scheme like I described), you just hard code an overwrite that sets the appropriate field to the current XML. You don't need to update it through all the previous states just to get to this one. Basically, take the simplest approach that will do what you want :) ChrisA -- https://mail.python.org/mailman/listinfo/python-list
Re: Keeping python code and database in sync
"Chris Angelico" wrote in message news:captjjmp68kh5zcxq50pi0yeaaapnqotxybg1+f58mv__xd9...@mail.gmail.com... > On Fri, Aug 29, 2014 at 11:31 PM, Frank Millman > wrote: >> Right now I am writing a tool to allow users to view and modify menu >> definitions. The tool is effectively a form definition, which in my >> system >> is expressed in xml and stored in the database in the 'sys_form_defns' >> table. The raw xml will be uploaded as part of the commit, and will be >> downloaded when someone pulls the latest version, but it still has to be >> inserted into the database before it is accessible. >> >> It is not a problem - I can use the same mechanism that you described - >> but >> it will be happening quite a lot until the system settles down. > > The system I described is intended for cases where each edit builds on > the previous ones - for instance, if patch 4 creates a table and patch > 7 adds a column to it, you have to have done them in that order. But > for that XML data, it sounds as if each update completely overwrites > all previous ones - is this right? In that case, I'd simplify it: > after doing all the other patches (anything more complicated than > "update this one column" can still go through a patching scheme like I > described), you just hard code an overwrite that sets the appropriate > field to the current XML. You don't need to update it through all the > previous states just to get to this one. > > Basically, take the simplest approach that will do what you want :) > I *think* I understand, but let me restate it in my terms just to be sure. When I have got this menu definition tool working, I will commit. The files involved will probably be one or more 'py' files, and one 'xml' file. Once downloaded, the 'py' files are automatically 'live', because that is how python works. But the xml file will just be sitting in a directory. *Something* has to trigger running a program that reads the xml file and inserts it into the database. My idea is to put the 'version number check' in the main program 'start.py'. If it detects that an upgrade is required, it will display a warning message to the user and then run the upgrade program. The same upgrade program can handle both 'schema' changes and (for want of a word) 'meta-data' changes such as the menu definition. Frank -- https://mail.python.org/mailman/listinfo/python-list
Re: Keeping python code and database in sync
On Sat, Aug 30, 2014 at 12:12 AM, Frank Millman wrote: > Once downloaded, the 'py' files are automatically 'live', because that is > how python works. But the xml file will just be sitting in a directory. > *Something* has to trigger running a program that reads the xml file and > inserts it into the database. I'm not 100% certain of this distinction, but it depends on how your code gets invoked. > My idea is to put the 'version number check' in the main program 'start.py'. > If it detects that an upgrade is required, it will display a warning message > to the user and then run the upgrade program. The same upgrade program can > handle both 'schema' changes and (for want of a word) 'meta-data' changes > such as the menu definition. Yes... or start.py can do it directly and automatically, just check and do whatever changes are needed. That's how I'd do it. ChrisA -- https://mail.python.org/mailman/listinfo/python-list
Re: Keeping python code and database in sync
In article , "Frank Millman" wrote: > The project is inherently database-driven. The python code expects to find > certain tables and columns in the database. As I develop new features, I > sometimes need to modify the database structure. In the bad old days (like > yesterday) I would just make the modifications and carry on. Now I have to > be aware that others may have downloaded the project, so I have to consider > how to ensure that their database is kept up to date. Yeah, schema migration is an ugly problem. There's a number of tools to help here, most of which reduce the suckitude, but don't eliminate it completely. Some things you might want to look at: * SQLAlchemy Migrate * South (django-specific) * yoyo-migrations * alembic Google for "python schema migration tools" and you'll probably find others. -- https://mail.python.org/mailman/listinfo/python-list
Re: Keeping python code and database in sync
- Original Message - > From: "Roy Smith" > > Yeah, schema migration is an ugly problem. There's a number of tools > to > help here, most of which reduce the suckitude, but don't eliminate it > completely. Some things you might want to look at: > > * SQLAlchemy Migrate > * South (django-specific) > * yoyo-migrations > * alembic > > Google for "python schema migration tools" and you'll probably find > others. Note that South is now fully integrated in django 1.7 (It's still ugly but it has been polished by the django team). The OP may want to consider moving to such framework, they tend to provide working concepts on critical issues when it comes to webapp/database. JM -- IMPORTANT NOTICE: The contents of this email and any attachments are confidential and may also be privileged. If you are not the intended recipient, please notify the sender immediately and do not disclose the contents to any other person, use it for any purpose, or store or copy the information in any medium. Thank you. -- https://mail.python.org/mailman/listinfo/python-list
Re: Keeping python code and database in sync
On Fri, Aug 29, 2014 at 9:54 AM, Roy Smith wrote: > Yeah, schema migration is an ugly problem. It's not really any worse than any other sort of complex data structure change, is it? If your persistent data lived in a pickle file, it would likely be as bad or worse. > ... suckitude ... Nice word. Let's use it more so my polly app will see it as a common word and maybe offer it to me in a potential XKCD 936 password. :-) suckitude suckitude suckitude suckitude suckitude suckitude suckitude :-) Skip -- https://mail.python.org/mailman/listinfo/python-list
Re: Keeping python code and database in sync
Roy Smith writes: > Yeah, schema migration is an ugly problem. There's a number of tools > to help here, most of which reduce the suckitude, but don't eliminate > it completely. Some things you might want to look at: > > * SQLAlchemy Migrate > * alembic I can strongly recommend SQLAlchemy. It has several levels of working with the RDBMS, and they all work well together; you can code primarily to one API and occasionally use a different part, and it all works together. I've never used Alembic, but it is a migration tool built on SQLAlchemy. > * South (django-specific) It's worth noting the South is no longer developed as a separate library: Please note that South is now end of lifed in favour of the new migrations framework in Django 1.7, which is based on South but with significant design improvements. South will not work with Django 1.7; it supports only versions 1.4, 1.5 and 1.6. http://south.aeracode.org/> -- \ “I think Western civilization is more enlightened precisely | `\ because we have learned how to ignore our religious leaders.” | _o__)—Bill Maher, 2003 | Ben Finney -- https://mail.python.org/mailman/listinfo/python-list
Re: Keeping python code and database in sync
On Sat, Aug 30, 2014 at 3:04 AM, Skip Montanaro wrote: > On Fri, Aug 29, 2014 at 9:54 AM, Roy Smith wrote: >> Yeah, schema migration is an ugly problem. > > It's not really any worse than any other sort of complex data > structure change, is it? If your persistent data lived in a pickle > file, it would likely be as bad or worse. Well, correct. The problem isn't because it's in a database; the problem is a consequence of persistent structured data that can get out of sync. It's easy to solve in a simple way that breaks on any sort of confusion. It takes a bit more complexity (like the scheme I suggested) to handle a few more cases. It takes a lot more complexity (like the migration tools Roy listed) to cope with lots of awkward cases (I suspect at least some of them will handle back-levelling, which my scheme doesn't). And I doubt any of them is absolutely perfect. >> ... suckitude ... > > Nice word. Let's use it more so my polly app will see it as a common > word and maybe offer it to me in a potential XKCD 936 password. :-) > > suckitude suckitude suckitude suckitude suckitude suckitude suckitude > Yeah, it's a great word. As a general rule, suckitude increases with the square of design complexity and superlinearly with number of bugs. I'm not sure how suckitude is affected by bugs, exactly; possibly O(N log N), because each bug has a small probability of affecting another bug. ChrisA -- https://mail.python.org/mailman/listinfo/python-list
Re: Keeping python code and database in sync
On Aug 29, 2014 5:34 PM, "Chris Angelico" wrote: > I'm not sure how suckitude is affected by bugs, exactly; possibly O(N > log N), because each bug has a small probability of affecting another > bug. OTOH, bug fixes often have a fairly high probability of adding more bugs to the system, especially if your test suite isn't up to snuff. Skip -- https://mail.python.org/mailman/listinfo/python-list
Re: Keeping python code and database in sync
On 08/29/2014 04:47 PM, Skip Montanaro wrote: On Aug 29, 2014 5:34 PM, "Chris Angelico" wrote: I'm not sure how suckitude is affected by bugs, exactly; possibly O(N log N), because each bug has a small probability of affecting another bug. OTOH, bug fixes often have a fairly high probability of adding more bugs to the system, especially if your test suite Major suckitude !! must be O(N**2) at least! [Thus endeth my attempts to train Skip's Polly. But I am curious -- if 'suckitude' is in immediate contact with punctuation such as just now, or at the end of a sentence, does it not count? That would be suckitude indeed! ;) ] -- ~Ethan~ -- https://mail.python.org/mailman/listinfo/python-list
Re: Keeping python code and database in sync
On Fri, Aug 29, 2014 at 6:54 PM, Ethan Furman wrote: > Thus endeth my attempts to train Skip's Polly. But I am curious -- if > 'suckitude' is in immediate contact with punctuation such as just now, or at > the end of a sentence, does it not count? That would be suckitude indeed! ;) Thank you all, "suckitude" made it into the corpus, or cruft, or dustbin, whatever. :-) ? dict /usr/share/dict/words accessing accounts adapted adding addressed adds adjusted adjusting advantages advertised aired akumbo algorithms alister allen allowed ... subscribed suckitude suffered suggested suggestions suggests suited ... /usr/share/dict/words really isn't a very good dictionary. Note all the words that are valid but which get flagged, mostly because they have common suffixes applied to words. I'll fix that shortly. Yes, "words" are skipped if they contain anything other than lower case alphabetic characters. Really simple words = text.split(), then discard words not meeting the criteria. Skip -- https://mail.python.org/mailman/listinfo/python-list
Re: Keeping python code and database in sync
On Sat, Aug 30, 2014 at 12:14 PM, Skip Montanaro wrote: > Yes, "words" are skipped if they contain anything other than lower > case alphabetic characters. Really simple words = text.split(), then > discard words not meeting the criteria. Easy way to catch a few more: Just .strip() off a few common items of punctuation (quotes (all types), full stop, comma, brackets (all types), etc). If there are any inside the word, discard the word, but those at one end or other aren't a problem. ChrisA -- https://mail.python.org/mailman/listinfo/python-list
Re: Keeping python code and database in sync
"Frank Millman" writes: > ... > It is a simple matter to write a program that updates the database > automatically. The question is, what should trigger such an update? My first > thought is to use a version number - store a version number in the working > directory, and have a matching number in the code. If someone downloads the > latest version, the numbers will no longer match, and I can run the upgrade > program. > > The problem with that is that version numbers are usually reserved for > tagged releases, but this could happen as the result of any commit in the > current development cycle. I use "data model version number"s for this. They identify the data model version and are idenpendent of any program version number. Such a version number is changed whenever the data model changes (and there is the need for a model upgrade). -- https://mail.python.org/mailman/listinfo/python-list
suckitude classifications [was Re: Keeping python code and database in sync]
On 08/29/2014 10:04 AM, Skip Montanaro wrote: On Fri, Aug 29, 2014 at 9:54 AM, Roy Smith wrote: Yeah, schema migration is an ugly problem. It's not really any worse than any other sort of complex data structure change, is it? If your persistent data lived in a pickle file, it would likely be as bad or worse. ... suckitude ... Nice word. Let's use it more so my polly app will see it as a common word and maybe offer it to me in a potential XKCD 936 password. :-) suckitude suckitude suckitude suckitude suckitude suckitude suckitude Speaking of suckitude, we could classify technologies that way: xml: major suckitude rpc: no suckitude python: negative suckitude oh, and suckitude is neither cruft nor corpus ! Polly want a cracker? ;) -- ~Ethan~ -- https://mail.python.org/mailman/listinfo/python-list
Re: suckitude classifications [was Re: Keeping python code and database in sync]
On Sat, Aug 30, 2014 at 5:02 AM, Ethan Furman wrote: > Speaking of suckitude, we could classify technologies that way: > > xml: major suckitude > > rpc: no suckitude > > python: negative suckitude I disagree with your last two qualifications. RPC still sucks, just not as much as some things do. And your last statement implies that Python actually fixes other problems, which violates one of the fundamental laws of physics: the Law of Conservation of Suckitude and Frustration. It's possible to reduce suckitude in one system by shifting it to another system, and it's possible to overall reduce suckitude in the universe by engaging in a very frustrating job, but merely combining two entities cannot actively reduce suckitude. Of course, sometimes you can wrap the suckitude up in another layer, thus reducing *apparent* suckitude, and this can make a system safer to use; but maintenance on the wrapper layer will reveal that nothing has been destroyed. Hello, Polly! ChrisA -- https://mail.python.org/mailman/listinfo/python-list
Re: suckitude classifications [was Re: Keeping python code and database in sync]
In article , Chris Angelico wrote: > On Sat, Aug 30, 2014 at 5:02 AM, Ethan Furman wrote: > > Speaking of suckitude, we could classify technologies that way: > > > > xml: major suckitude > > > > rpc: no suckitude > > > > python: negative suckitude > > I disagree with your last two qualifications. RPC still sucks, just > not as much as some things do. Are we talking the generic concept of Remote Procedure Calls, or the specific implementation of Sun RPC? I'm surprised at the reaction to the word, as if it were something new. I thought the term has been in common parlance for many years. Contrast with winitude. Anyway, I think it is unfair to describe xml as major suckitude. True, it is somewhat outdated, with more modern alternatives such as JSON, YAML, protobuffers, etc. In its day, however, it was a breakthrough technology, even if only an incremental outgrowth of SGML. -- https://mail.python.org/mailman/listinfo/python-list
Re: suckitude classifications [was Re: Keeping python code and database in sync]
On Sat, Aug 30, 2014 at 9:19 AM, Roy Smith wrote:
> In article ,
> Chris Angelico wrote:
>
>> On Sat, Aug 30, 2014 at 5:02 AM, Ethan Furman wrote:
>> > Speaking of suckitude, we could classify technologies that way:
>> >
>> > xml: major suckitude
>> >
>> > rpc: no suckitude
>> >
>> > python: negative suckitude
>>
>> I disagree with your last two qualifications. RPC still sucks, just
>> not as much as some things do.
>
> Are we talking the generic concept of Remote Procedure Calls, or the
> specific implementation of Sun RPC?
I was thinking of XML-RPC, a particular protocol. Although the generic
concept of remotely calling something isn't entirely free of suckitude
- it's all very well in its way, but like everything else, it has its
flaws. In systems that completely hide the details and make it look
identical to local procedure calls, you have the same problem as
creating a Python property that does a lot of work ("wait, that call
actually goes out over the network??"), and in systems that make it
more clear that this is a network transaction, it's not really a
remote procedure call any more, it's a network action like any other.
But these are small nitpicks. Enough that I'd say "minor suckitude" or
"slight suckitude", but not "no suckitude".
> I'm surprised at the reaction to the word, as if it were something new.
> I thought the term has been in common parlance for many years. Contrast
> with winitude.
It has. It's just that Skip's Polly has never heard the word before,
so we're teaching her.
> Anyway, I think it is unfair to describe xml as major suckitude. True,
> it is somewhat outdated, with more modern alternatives such as JSON,
> YAML, protobuffers, etc. In its day, however, it was a breakthrough
> technology, even if only an incremental outgrowth of SGML.
I've never used XML for anything that wasn't mandated by some other
end, and in every single one of those cases, JSON would have been a
much better fit for the data structure. XML is frequently shoehorned
into carrying array data like this:
footextmore
textblah
baronly one element
In JSON, this would be:
{"entry":[
{"heading":"foo","subentry":["text","more text","blah"]},
{"heading":"bar","subentry":["only one element"]}
]}
But in XML, there's no way to represent lists/arrays at all, and the
usual way of fitting them in doesn't allow you to distinguish
one-element lists from text strings (compare the heading entry).
So what's the value of XML for information storage? What's it being
compared against that makes it look good? And above all, why do people
still use it when JSON and other formats are available and well known?
ChrisA
--
https://mail.python.org/mailman/listinfo/python-list
