Migrations and two applications sharing parts of database on different machines

user_5603470 0
Viewed 678

Better description - rephrasing the problem now in new section.

Question: how to organise ./manage migrate on client in following setup:

  • I have to create django web site on client machine, with local user to login there and do some stuff locally.
  • There is also server with huge django application, which have its users doing things on the server.
  • And there is "just python" application on client machine, which gets data transferred from server, stored locally and work with the local copy with direct SQL queries (lot of server table-connections are not used there), modifying only two local tables, which structure (but not data) is copied from the server for consistency.

The server django application and the client "just python" applications have already established protocol for connecting and transfer data, purely from server to client. Not all tables are transferred (as not all are needed), sometimes only a structure of table is enforced on the client, sometimes also data.

The tables are transferred just as content of "normal Mysql tables" via some XML/ssh/whatever, losing its django connections, mainly no auth.* / admin.* / session.* / content.* tables are transferred (as irrelevant), while a lot of django model have "modified_by_user" foreign key to (nontransferred) User table. This does not matter more on client.

No I have to create local django web on the client, with

  • its own set of Users, some profiles (OnToOne connected to the local users)
  • its own local tables which are read/write for local uses, but do not collide with the tables from server
  • shared tables (copied from server, purely read-only on client, overwritten sometimes by the sharing protocol)

I am writing the client side django part from scratch, I have access to source of the server part (so I can copy models.py for example), but I need to add

  • local authentications
  • manage content of "local only"tables
  • be able to expand them by migration, but do not change by migrate the existing "from server copied Mysql tables"
  • use some data for shown only from such "server copy tables" (some would be linked by foreign key from those local tables.

There is hard promise, that those copied tables would not do anything wild (will just grow in length and existing records would at maximum have some boolean columns modified, maybe later there would be added more fields, but none deleted or changed - either by structure or by meaning))

Question is how I should construct my "client django application" to be maintainable, coexisted (and work with) the server copies of tables.

I can create my app with declaring some models, migrate, copy the "server models" migrate with --fake this part and then continue as usual, but it would be installed on more clients, and the structure of the shared tables may later add something, that I would need to read - so another --fake migration

Is there a way, to make some migrations "--fake" by default, so it would not really migrate at all, but would not be offered as unfullfilled (if those models would change)? Or I just have to name all of such migrations "0007_PLEASE_FAKE_ME' and do it by hands each time on each client? Or does it need totally different access?

There is not data sending from client to server and serve can send any data/structure changes it wants any time down. It should not make problem to my application anyway and if some structural changes should be reflected, I would be warned before and it could be done days/weeks after the transmission really happened.

I even can send my requests for model changes to the "server" staff to incorporate it in their models.py and push it down as "structure-only update"

I can incorporate their models.py to mine also, if it changes.

Still I am not sure, how my OneToOne profile to User would work on server, where is a lot of already established Users and none Profile model ...

the Djago version is the same on server and client (1.10), all tables are MyISAM.

Thanks for all suggestions

Edits (for @Thushar):

Clients usually run 24/7/365, tables are transferred in logical order by dependencies, first those, which end leaves, then those, which reference them, core referencing all others last - so the the database model is every time consistent (just maybe not complete in transfer, anything used have there prerequisities already transferred)

'Created_by' is not used (de-referenced) on clients. So far the clients run just some python code, which uses enumerate subset of columns a tables. this works for like 5+ years.

  1. Modifications goes first to server (which fills values to tables), than tables are transferred to clients, then clients get SW update, which uses the new features (so it works each time by old way or by new way with complete new data) Server uses django, clients use plain SQL select name,filename, volume from themes where active=1

  2. database scheme changes are done first on server, then transferred to client, then client sw is update to use the new columns (like SQL ... AND plaing_allowed=1. Changes are back compatible, so even client with 10 versions obsolete SW is able work totally actual data, just missing some new features.

  3. the API for database transfer is pretty stable (even if little slow), transfers some form of TXT/XML data over SSH (many times no other connection is available to client) - with table descriptions first, then each changed line in order - and the order of transfers is structured so, that could be interrupted at any moment without affecting client ability to work. SW on client is updated after database, so there is (old client, old data - works) (old client, new data - works as previous version together)(new client, new data - works as new version)

old client simple ignores (does not know about) new tables/fields and is missing some new functions (also missing way to call such functions) so it works, like any changes was not done at all.

About new records (lines) - first are transferred dependencies (so there is no way to use them), then more core tables(which then are sure, that all relations they uses are already in place) - the dependencies are tree-like, so it is possible (and done) order it this way, that leave are transferred first, nodes then, root last - (so if transfer is interrupted, it is possible, that some leaves/subtrees are "stray"/unaccessible, but anything accessible have all dependencies in place) again, this already works 5+ years and tens or hundreds versions long. The client is so far more "device" than "computer" even if it runs full OS on PC platform - plans are made on server, clients just follows orders and do necessary computations, when needed.

Again the old way (django with mysql on server, python with mysql on clients) works reliably long years.

Not every time is client-server connection possible, then clients are suppose to work "locally" on old data, until the connection is available again (sometimes after hours, sometimes after months).

But "it just works".

My task is create new django application, which is to be release on some (possibly all) clients, which adds some "local features" to existing schema, basically

  • have some new tables to place some plans and data into
  • have some web interface so users can log by web browser directly to client (which itself is little server with apache+django+mysql) and plan some local events there - those events are NOT backpropagated on main server and does not modify "official tables", just reads, what is available and make "local notes"
  • those "local notes" then use already existing API on the client to notice it on more separate tasks, which needs be done "just now and here" - such API exist alredy and is used locally by some obsolete application (running on users PC/NB), which is to be replaced by web interface to that new django application on client.
  • the target is to be able expand the web interface to use some special "local tables" as well as read those "officially distributed" and expand the local API to allow more features, the local API for this (on client) just read data from tables and acts on them, not modifying anything in database. (New version of the local API should read on local tables too for more functionality)

My problem is, how to develop the client django application, with respect to that some database schema updates "just happens on background occasionally".

New version of the new django application can really on that all new "background changes on global tables" are already in place, when it is released and only changes of local tables are needed.

The already existing system (optimized for different criteria) is much larger, than the new part, so re-writing it from scratch is not option. Also there are some "unupdatable parts" such, that the old way of communication must be supported anyway in new version, as some computer totally out of my reach depends on it. (But the communication protocol is designed to be expanded, so there is not big problem add some more information there)

Better description

  • There is master computer M and identical slave computers S (1..x)
  • Master M runs Myslql + Django + Apache and have 1 API (M-S)
  • Master have tables TA (1..), TB (1..), TC (1..)
    1. M-S API transfer table schema (CREATE TABLE like) for all TA and TB tables (either full or changes)
    2. M-S then transfers data changes for TA tables
    3. TC tables are not transferred at all
  • Slave S now runs Mysql + Old program and have 2 APIS
    1. M-S API (for master over net)
    2. S-local (XML based for old local communication by OldApp)
  • Master (not regularly, but often enough, like 1-10x daily) contact each Slave and over M-S forces database updates on it
  • Slave does not update tables on Master (well there is some status reporting and such, but not mandatory and not interesting here)
  • Slave makes its own work (mainly playing music based on some hints from tables TA) independently (planning permutations ans such on its own)
  • when noted by XML from OldApp over S-local API the Slave stops what is doing and plays appropriate message, then continues
  • the S-local API is manipulated by some obsolete closed source application OldApp, which should be replaced by NewApp (see later)
    • OldApp runs on users computers (totally different from Master and Slaves) under Windows and have hardcoded XML strings to send to Slave S
  • lots information from Master M is irrelevant for Slave S (mainly all authorisation and users, as decisions on slave are based on different criteria and TA tables are de-facto read-only used here)
  • all of this works a lot of years and over lot of version changes an is too big to be rewritten in sane time.

New approach is to create and run Slave S based NewApp django application (+Apache) to offer better and new functionality, than the OldApp over web pages (hosted by Slave S)

To do so it needs:

  • authorisation (different from Master, bur all Master auth.* tables are in TC group and not transferred at all, so it can be locally reused on Slave)
    • different for every Slave, not reported to Master
  • it reads some tables of TA group (but just read, does not modify content)
  • its own set of tables TD (1..x) to save - configurations and sounds and relations - local to the particular Slave
    • different for every Slave, not reported to Master
  • use S-local API (and expand it little - not a problem)

Questioned solution:

  • Copy models of TD tables on Master and let them to be transferred with TB tables to Slaves (just structure)
  • transfer auth.* tables on Master the same way (with TB group, only structures, not data) to Slaves
  • install new version on NewApp this way:
    1. update TD on Master to synchronise with new version of TD in NewApp and migrate on Master
    2. make transfer on Slaves (so new version is there forced)
    3. update NewApp on Slaves and use just ./manage.py migrate --fake to stop complains

There are not problems with M-S API and its transfers (it just works, and default values in database are applied to new fields of existing records/rows), creating views and templates, (AFAIK if table for model contains more fields than model, such fields are just ignored)

This way Master would not use TD tables/model in any way (no interface, no admin, just being there empty) and Slave would be updated externally in sync with Master. Models.py on both would be the same in data part (maybe Master missing function of TD and slave missing methods on some TA/TB tables)

Questions

  1. could it work this way (fake migrations)
  2. are there some "system" tables needed (and automatically managed) by django on Master, which needs to be transferred (eventually with data) to to Slaves (probably something about what migrations are done, or about structure of tables needed to meet models.py requirement) 2.1. is such tables are transferred, are even fake migrations on Slave needed? (IMHO there would be none, as all migrations are mentioned in those tables and the models fits)
django-models django python
1 Answers

I hope that by the client machine, you mean some local server, that runs 24/7, and not some client's laptop/desktop.

  1. Please understand that any solution involving copying of database tables may be brittle, especially when there are foreign keys like created_by that point to a user, and in your local setup, they may have to point to some local user (or one dummy user).

  2. Moreover, there might be other database changes as well in the server application, and unless you change corresponding models in your client Django application also, you can't use the same at the client side, and if you are adding columns using some other tool, they may no longer be usable from Django. Any table created using django models, and changed otherwise, will push you into a hell lot of issues.

  3. So one thing is for sure, you will need to make changes to both applications separately, whenever you want to make any changes. Anyways, even if you manage to make schema changes locally, and manage to keep it "backward compatible", how will your client application know what to do with those new tables & columns unless it's code is changed?

  4. So it's better to avoid tables copying and try to use some more standard way like using APIs to connect the 2 systems so that you have complete control over how the data is copied.

  5. You can expose selected tables on server application through an API, and on client side you can use a system like django-celery-beat to run a cron that hits the API, and fetches the latest data from server application, in form of JSON, and save it in desired format in local DB.

  6. Whenever there is some change in server application, and you need that change in local application. You add that data to respective API's response. And do changes in client application to fetch and save/utilise that data.

  7. Example, if there's a model purchases in the remote application:

    class Purchase(models.Model): customer_id = models.ForeignKey(CustomerTable, related_name='purchased_by') sales_agent = models.ForeignKey(User, related_name='purchased_by') item = models.CharField(max_length=50) price = models.PositiveIntegerField() date = models.DateTimeField(auto_now_add=True)

And you just to use need, customer_id, item & price in the local application, Create an API http://server_app.com/purchase, that sends you a response like this. [{"customer_id": 4, "item": "soap", "price": 20}, {"customer_id": 5, "item": "toothbrush", "price": 10}, ...]. This response can be used by client application, and can be saved in a local model that may look like below. You can also make the system to send back a CSV file as a download, in place of JSON response, if data is huge.

class CustomerTrend(models.Model):
    customer_id = models.ForeignKey(CustomerTable, related_name='purchased_by')
    item = models.CharField(max_length=50)
    price = models.PositiveIntegerField()

The API acts as a contract between server and client-side applications, and you make changes to both sides only when some new functionality is needed on the client side, either in existing APIs, or by creating new ones. Even if some database change on the server, and the client doesn't bother about it, as far as your API keeps sending same response, client application doesn't care. Just make a celery job to hit that API as frequently as needed, to fetch the required data. And create new models whenever schema-only updates are needed. Anyways, automatic schema sync will be useless unless your client app's code is changed to know what to do with new schema.

Update

Okay, after your edit, one thing is sure that as it's a huge and legacy system, we are talking about, restructuring is not an option. As I understand, you want to plug in a Django based UI on the client side, in the existing system. Let's see, what other options are there:

  1. You can apply fake migrations, or you can choose not to apply any migrations at all. Let the current system replicate data/schema on your local system.
  2. Replicate the required models in your client-side Django application in code.
  3. Be careful about any new models or columns that you add on either side, they must have a sensible default value, and if possible allow Null(None). What this will do, is to maintain backward compatibility across both systems, and you won't run into Integrity Errors on client application, in case you are working with only a few columns. It reminds me of another legacy system I worked on (written in C & PHP, some 15 years ago. I worked on it when it was already 12 years old). We used to follow a similar strategy, with a database shared by multiple systems, all running different versions of clients, expecting different data. You can just do the one-time replication of authentication tables if you are using Django's custom authentication, and then disable replication of this table(as I understand you anyway don't migrate data on these tables). If you are using default auth, just migrate them separately during your first deployment on local setup.
  4. As I suggested earlier, running fake migrations is okay, or may be not running them at all. It's a call only you can make based on specific use case, that you might understand better. But if you don't apply (fake) migrations locally, you can't apply any migration in future(As django's internal migration table won't have any data). If you see a possibility of having client specific tables in future, then do fake migrations.
  5. Bottom line is, in no way, let 2 systems (DB-replicator & Django) make schema changes on same client database, or it will get ugly. As DB replcation is mandatory, client side appication code should be ensuring that it can manage to run with the relicated database (using columns with correct defaults and nullability). The code changes done in models for both server & client applications should be same. (code reviews should focus on this, as this may actually be your single point of failure in future).
user_91344870
Related