Jump to content

Module proposal: DB migration assistant


Recommended Posts

It occurs to me that a module that records all alterations to DB schema would be useful for the migration of DBs from a staging/development environment to live or vice-versa.

I was thinking that, at present, if I work on my site locally (LAMP stack on my laptop etc) and then need to push changes live that it is no problem from a code point of view (I use git for source control, comfortable with that process) but that DB schema changes may be a bit more of a pain.

I can, of course, just recreate the fields and templates manually on the live site but it's not exactly convenient or intuitive!!

I have not checked, but if there are hooks into the DB parts of the core that make changes to the schema for new fields, template structure etc then it shouldn't be too hard to record those changes into a log that will therefore be a series of SQL commands that can be run to update the DB schema on another installation.

I'm slightly lacking the time to do this myself, but I suspect that there are talented module devs here who could do this in no time, assuming the hooks are in place.

An ex-colleague wrote a plugin for another CMS which did the same thing and it was very helpful indeed.

  • Like 1
Link to post
Share on other sites

Hey Rob, that would be awesome - I strongly feel that there really is a hole, when it comes to database syncing. While I probably won't pick this up (I'm not one of those talented developers), is there any possibility of linking us (or the one that will pick this up) to that ex-colleagues code? (in case it's open source, or he is willing to share it with PW community, if you're in contact).

  • Like 1
Link to post
Share on other sites

Hey Rob, that would be awesome - I strongly feel that there really is a hole, when it comes to database syncing. While I probably won't pick this up (I'm not one of those talented developers), is there any possibility of linking us (or the one that will pick this up) to that ex-colleagues code? (in case it's open source, or he is willing to share it with PW community, if you're in contact).

Hi Adam.

I think the code is on github somewhere and I could probably find it, but it's so specific to the CMS in question that it really wouldn't be relevant here. What we need to figure out is whether the hooks in question are available in the core and then tap into them to basically log every DB schema change.

I know from previous talks with Ryan that basically any function in the core can be made hook-able, but that there are performance implications in many cases so it has to be carefully considered.

That said, in this case it has nothing to do with page dispatch/render and is purely linked to back-end activity so it shouldn't affect site performance.

I'm sure he'll chime into this discussion when he gets the chance!

Link to post
Share on other sites

I think that I can add the necessary hooks to support this. Overhead with high traffic functions is always a concern with hooks, but did recently figure out a way to do it in a way that avoids any overhead unless/until a hook is actually active. So it should be possible to make something like $db->query() hookable. I will take a closer look at this.

  • Like 1
Link to post
Share on other sites

Ryan, haven't you previously mentioned that you made something like versioning before?

I would really love to have something like this:

You have local/live version; you make a snapshot of a live version, and push the changes to local version. You work on the local, disregarding the live version. When you're finished, you'll take new snapshot of the live and merge it into local version. Now you can push your new local dump live and you're in sync.

I think this comes into play not only with external editors (who can't work with your local version, obviously), but also to other automated things out of your control: I for instance have visit tracking code on section of website, because I later recalculate lists with this data. Also, when you have new comments and such on the website, you'll like to merge this with your local (possibly outdated version).

Only other way to do this (as far as I know) is to do all the dev on your local version, do live->local db dump, freeze live, do local changes and push local->live. And while it might sound simple when written, it really feels cumbersome...

  • Like 1
Link to post
Share on other sites

Web sites aren't just databases, but files too (which may be version tied to the database), so I don't see anything that operates just on SQL queries as being realistic. Versioning is one thing (which can be done), but migrating in the manner you mentioned is something else. I don't think you should ever consider "live" as something that can be frozen while you go to work on dev, though I guess that depends on the case. I know I couldn't freeze any of my live sites. Migrating file-based assets (templates, js, css, etc.) is a pretty simple matter. But migrating databases gets more complicated. Future tools for migrating data in ProcessWire will likely take a web-service approach (two sites talking and sharing JSON) rather than one involving sharing of database queries.

Link to post
Share on other sites

Ryan,

the freeze isn't the full time while you work on dev, I mean rather something like preparing everything you need locally and then:

  • discard local db changes
  • live->local db push
  • recreate needed db changes (as in merge local+live)
  • local->live

This could be done in matter of minutes, if well prepared (for instance, every new 'local' page is created through one script, for instance) and I think freezing anything for few minutes is possible (even on ecommerce sites, 10 minutes can't hurt your sales that much short of work day hours).

Of course, this involves rather unnecessary logistics, and something more elegant should be in PW :)

Link to post
Share on other sites
  • 7 months later...

What if every time PW made a change to the database schema, afterwards it asks if you want to record that change. Then PW could ask you to add this change to a 'batch' or create a new batch to add to.

while each batch would represent all the changes made in the new site version, on the server each 'batch' could be a folder containing the php scripts which implement the same database schema changes, in the same order (by date/time).

run those scripts on the production site and you should be caught up.

[edit: well, I guess the content won't be caught up. But at least things like fields and modules and stuff. Page content would still need to be added to the live site. maybe a 'save and record' button when editing a page? ]

would something like that be implementable?

Link to post
Share on other sites

I think it sounds good in theory. But it also might be problematic in reality. DB schema changes are often the result of other things going on in the system that have some other representation whether by modules, template files, page files, etc. We couldn't just record DB schema changes and then play them back somewhere else because the source of those DB schema changes might not exist on the other system. I will keep thinking about this though, as it's definitely interesting.

Link to post
Share on other sites
  • 1 year later...
  • 4 months later...

Hey all,

I wanted to see if there was any progress on this feature. I have recently had to use Wordpress on a project or two and while I can't really stand WP, there is an awesome plugin for syncing local and remote DB's (really any DB to another):

"Migrate DB Pro" - https://deliciousbrains.com/wp-migrate-db-pro

Something like this would be AWESOME to have for ProcessWire and really help with continuous integration workflows (Git does a great job of file sync, but DB is still a pain). Any thoughts on next steps for a module like this?

Thanks!

Link to post
Share on other sites

https://github.com/adrianbj/ProcessMigrator

and since you are coming from Wordpress, you might enjoy this plugin module for taking WP XML exports and importing them into PW:

https://github.com/NicoKnoll/MigratorWordpress

They are still possibly not quite release worthy yet, but with a little more testing, hint hint :) we might be ready to add them to the modules directory.

  • Like 3
Link to post
Share on other sites
  • 1 year later...

Hey all,

I wanted to see if there was any progress on this feature. I have recently had to use Wordpress on a project or two and while I can't really stand WP, there is an awesome plugin for syncing local and remote DB's (really any DB to another):

"Migrate DB Pro" - https://deliciousbrains.com/wp-migrate-db-pro

Something like this would be AWESOME to have for ProcessWire and really help with continuous integration workflows (Git does a great job of file sync, but DB is still a pain). Any thoughts on next steps for a module like this?

Thanks!

The WP Migrate DB Pro plugin doesn't do versioning (like Ruby on Rails DB Migrations) or content deployment (merging), but rather a complete database replacement of one database to another, which would be problematic on sites that are already live and whereby the live site and staging site have different content.  The RAMP plugin is what does selective content deployment.

The current ProcessWire equivalents are a bit manual:

  • Can export and then import fields (native PW feature)
  • Can export and then import templates (native PW feature)
  • Can export and then import HannaCode (native module feature)
  • Can export and then import FormBuilder forms (native module feature)
  • The ProcessMigrator module does both field and template export/import along with the ability to export and import actual content.  So in my eyes, it's the currently the analogous plugin to RAMP.

It would be very slick ProcessMigrator could "connect" a staging site and a live site so that content deployment could be one click.  Would be a great premium feature!

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By kongondo
      FieldtypeRuntimeMarkup and InputfieldRuntimeMarkup
       
      Modules Directory: http://modules.processwire.com/modules/fieldtype-runtime-markup/
      GitHub: https://github.com/kongondo/FieldtypeRuntimeMarkup
      As of 11 May 2019 ProcessWire versions earlier than 3.x are not supported
      This module allows for custom markup to be dynamically (PHP) generated and output within a page's edit screen (in Admin).
       
      The value for the fieldtype is generated at runtime. No data is saved in the database. The accompanying InputfieldRuntimeMarkup is only used to render/display the markup in the page edit screen.
       
      The field's value is accessible from the ProcessWire API in the frontend like any other field, i.e. it has access to $page and $pages.
       
      The module was commissioned/sponsored by @Valan. Although there's certainly other ways to achieve what this module does, it offers a dynamic and flexible alternative to generating your own markup in a page's edit screen whilst also allowing access to that markup in the frontend. Thanks Valan!
       
      Warning/Consideration
      Although access to ProcessWire's Fields' admin pages is only available to Superusers, this Fieldtype will evaluate and run the custom PHP Code entered and saved in the field's settings (Details tab). Utmost care should therefore be taken in making sure your code does not perform any CRUD operations!! (unless of course that's intentional) The value for this fieldtype is generated at runtime and thus no data is stored in the database. This means that you cannot directly query a RuntimeMarkup field from $pages->find(). Usage and API
       
      Backend
      Enter your custom PHP snippet in the Details tab of your field (it is RECOMMENDED though that you use wireRenderFile() instead. See example below). Your code can be as simple or as complicated as you want as long as in the end you return a value that is not an array or an object or anything other than a string/integer.
       
      FieldtypeRuntimeMarkup has access to $page (the current page being edited/viewed) and $pages. 
       
      A very simple example.
      return 'Hello'; Simple example.
      return $page->title; Simple example with markup.
      return '<h2>' . $page->title . '</h2>'; Another simple example with markup.
      $out = '<h1>hello '; $out .= $page->title; $out .= '</h1>'; return $out; A more advanced example.
      $p = $pages->get('/about-us/')->child('sort=random'); return '<p>' . $p->title . '</p>'; An even more complex example.
      $str =''; if($page->name == 'about-us') { $p = $page->children->last(); $str = "<h2><a href='{$p->url}'>{$p->title}</a></h2>"; } else { $str = "<h2><a href='{$page->url}'>{$page->title}</a></h2>"; } return $str; Rather than type your code directly in the Details tab of the field, it is highly recommended that you placed all your code in an external file and call that file using the core wireRenderFile() method. Taking this approach means you will be able to edit your code in your favourite text editor. It also means you will be able to type more text without having to scroll. Editing the file is also easier than editing the field. To use this approach, simply do:
      return wireRenderFile('name-of-file');// file will be in /site/templates/ If using ProcessWire 3.x, you will need to use namespace as follows:
      return ProcessWire\wireRenderFile('name-of-file'); How to access the value of RuntimeMarkup in the frontend (our field is called 'runtime_markup')
       
      Access the field on the current page (just like any other field)
      echo $page->runtime_markup; Access the field on another page
      echo $pages->get('/about-us/')->runtime_markup; Screenshots
       
      Backend
       

       

       
      Frontend
       

    • By kongondo
      Visual Page Selector
      Released 31 March 2016
      https://processwireshop.pw/plugins/visual-page-selector/
        As of 04 January 2018 ProcessWire versions earlier than 3.x are not supported   *******************************************************   ORIGINAL POST   ******************************************************* Introducing VPS, a commercial visual page field selector. 
      This is a pre-sale closed-beta version. This post is WIP and will be updated now and then.
      ############################
      Many ProcessWire users use the 'one image per page' principle to manage and reuse images across their sites. This works fine. However, for site editors who mainly work with images, especially for larger sites, it is sometimes difficult to remember the pages where particular images reside. This module helps to solve this challenge.
      Harnessing the awesomeness  that is ProcessWire, VPS provides a rich editing experience, enabling editors to search for, view, select, add, remove and delete page-images easily, in an easy to use and friendly interface. ProcessWire Lister is the workhorse behind the lightning-fast searches. Editors will be able to search for images by their descriptions, names, partial names, page names, templates, etc. 
      Current Features
      Single-image mode Full search Batch add/Remove/Delete Image/Delete Page in page fields Image Browser Selectable pages as per page field settings + Lister filters Grid and List View Draggable sorting Responsive (almost fully ..iframes!) Planned Features
      Multi-image mode (there are times you want to group similar images in multi-image field in one page; e.g. the back, front and side of a car photo) Configurable CSS on the fly resizing vs real image resizing (image resizing can quickly hog memory) Other as per feedback from beta testing FAQs
      When will this be available? 
      Soon.
      How much will it cost?
      Reasonably priced. Announcement soon.
      Where will I be able to buy this from?
      At all fine stores that stock quality ProcessWire products
      Do we really need another page field/inputfield select?
      See links below.
      What type of licenses will be available?
      Soon to be announced.
      Can I beta test this?
      Thanks for the interest but all available slots have been taken.
      Video (excuse the video quality please - too many takes....)
       
      Screens






      Previous Discussions
      https://processwire.com/talk/topic/10927-wishlist-select-pages-by-thumbnail/
      https://processwire.com/talk/topic/4330-get-image-from-other-pages-via-images-field/ https://processwire.com/talk/topic/417-extending-image-field/?p=6982 https://processwire.com/talk/topic/7073-profield-table-and-gallery/ https://processwire.com/talk/topic/3200-image-management-concerns-is-processwire-suitable-for-me/ https://processwire.com/talk/topic/425-file-manager/ https://processwire.com/talk/topic/10763-asset-manager-asset-selector/
    • By kongondo
      FieldtypeMatrix and InputfieldMatrix
      Modules Directory: http://modules.processwire.com/modules/fieldtype-matrix/
      GitHub: https://github.com/kongondo/FieldtypeMatrix 
      The module Matrix enables you to save data from a 2D-Matrix table. The rows and columns of the matrix table are made up of pages retrieved via a ProcessWire selector or via a page field selection of parent pages. The matrix values are made up of the data input in the matrix cells, i.e. the 'intersection of rows and columns'.
       
      Example Usage
      You have Products whose prices vary depending on colour, size, material, etc. Using the Fieldtype, you can create a table with rows made up of colours and columns made up of sizes the combination of each making up their respective values (in this case price). So rather than creating multiple text fields to do the following:
      Colour Size Price Red Small £10 Red Medium £20 Red Large £30 Red X-large £35 Green Small £9 Green Medium £15 Etc... You can instead have the following in one field:
      Small Medium Large X-Large Red £10 £20 £30 £35 Green £9 £15 Blue Etc... Yellow Purple If you set a selector in the Field's settings, to retrieve pages to build your matrix's rows and columns, it follows that all pages using the template the Fieldtype is attached to will have identical rows and columns. In some cases, this could be the intention. For instance, you might have 'Car' pages, e.g. Audi, Volvo, Ford, Citroen, Mazda, BWM, etc., each of which uses a 'Cars' template that has a single FiedltypeMatrix called 'car_attributes'. If you set a selector to build the Fieldtype's rows and columns, your users can easily compare the cars based on a combination of different values. The following matrix table best illustrates this:
      Type Engine Size Fuel Efficiency Carbon Emissions Warranty Road Tax Price 1994 Audi brand 1 values, etc. 2000 Audi brand 2 2006 Audi brand 3 2012 Audi brand 4 Each of your car pages would have similar matrices. 
      This allows you to make easy but powerful queries. Such a setup allows you to compare within and across car brands. Say you wanted to find out which car(s) offered the best value for money given certain parameters such as warranty, emissions etc. You can easily make such comparisons (see code below). You can also compare within one car type, e.g. which brand of BMWs does best in what area...The possibilities are endless.

      In the database, Rows and column pages data are stored as their respective page->id. Matrix-values store any data (varchar(255)).
       
      If instead you wanted a template's pages to each have a matrix built of different rows and columns, you would have to name a Multiple Page Field (attached to the same template as the as your matrix field) in the matrix field's settings. When editing those pages, your matrix table's rows and columns will be built using the published children pages of the 2 pages you select in the Multiple page field..
       
      The module allows the creation of matrix tables of any sizes (rows x columns). The rows and columns dynamically grow/shrink depending on the addition of row/column pages that match what you set in the matrix field's settings (see its 'Details Tab'). Please note that, if such pages are deleted/trashed/hidden/unpublished, their data (and presence) in the matrix are also deleted.
      Entering values in the matrix
      You have three choices:
      Manually entry Uploading a comma delimited (CSV) file. This can be delimited by other characters (tab, pipe, etc); not just commas Copy-pasting CSV values. (Tip: you can copy paste directly from an Excel spreadsheet. Such values will be 'tab-delimited'). In addition, if your server supports it, in the field's settings, you can enable the use of MySQL's fast LOAD DATA INFILE to read and save your submitted CSV values.
      Note that for large tables, you may have to increase your PHP's max_input_vars from the default 1000 otherwise PHP will timeout/return an error and your values will not be saved. I have successfully tested the module with up to ~3000+ values (10x350 table), the Fieldtype is not really optimised (nor was it intended) to handle mega large matrix tables. For such, you might want to consider other strategies. 
       
      Install
      Install as any other module.
       
      API + Output
      A typical output case for this module would work like this:
       
      The matrix's rows, columns and values are subfields of your matrix's field. So, if you created a field called 'products' of the type FieldtypeMatrix, you can access as:

      product.row, product.column and product.value respectively
       
      foreach($page->matrix as $m) { echo " <p> Colour: $m->row<br /> Size: $m->column<br /> Price: $m->value </p> "; } Of if you want to output a matrix table in the frontend: 
       
      //create array to build matrix $products = array(); foreach($page->matrix as $m) $products[$m->row][$m->column] = $m->value; $tbody ='';//matrix rows $thcols = '';//matrix table column headers $i = 0;//set counter not to output extraneous column label headers $c = true;//set odd/even rows class foreach ($products as $row => $cols) { //matrix table row headers (first column) $rowHeader = $pages->get($row)->title; $tbody .= "<tr" . (($c = !$c) ? " class='even' " : '') . "><td class='MatrixRowHeader'>" . $rowHeader . "</td>"; $count = count($cols);//help to stop output of extra/duplicate column headers foreach ($cols as $col => $value) { //matrix table column headers $columnHeader = $pages->get($col)->title; //avoid outputting extra duplicate columns if ($i < $count) $thcols .= "<th class='MatrixColumnHeader'>" . $columnHeader . "</th>"; //output matrix values $currency = $value > 0 ? '£' : ''; $tbody .= "<td>" . $currency . $value . "</td>"; $i++; } $tbody .= "</tr>"; } //final matrix table for output $tableOut = "<table class='Matrix'> <thead> <tr class=''> <th></th> $thcols </tr> </thead> <tbody> $tbody </tbody> </table>"; echo $tableOut; The module provides a default rendering capability as well, so that you can also do this (below) and get a similar result as the first example above (without the captions). 
      echo $page->matrix; Or this
      foreach($page->matrix as $m) { echo $m; } Finding matrix items
      The fieldtype includes indexed row, column and value fields. This enables you to find matrix items by either row types (e.g. colours) or columns (e.g. sizes) or their values (e.g. price) or a combination of some/all of these. For instance:
      //find all pages that have a matrix value of less than 1000 $results = $pages->find("products.value<1000"); //find some results in the matrix (called products) of this page $results = $page->products->find("column=$country, value=Singapore");//or $page->products->find("column=$age, value>=25"); //$country and $age would be IDs of two of your column pages Other more complex queries are possible, e.g. find all products that are either red or purple in colour, come in x-large size and are priced less than $50.

      Credits
      @Ryan on whose Fieldtype/InptufieldEvents this is largely based
      @charger and @sakkoulas for their matrix ideas
       
      Screens
       
      Field Details Tab

       
      Inputfield

      Larger matrix table

       
      Example output

    • By Pravin
      How to set the image quality as per desired..
      I tried using the above code but I get the original image size..
      <!--Content with Image left--> <?php $i = 2; foreach($page->page_content as $each) { if( $i%2 == 0 ){ ?> <section class="imageblock about-1"> <div class="imageblock__content col-md-6 col-sm-4 pos-left animated fadeInLeft"> <div class="background-image-holder"> <?php $option1 = array( 'quality' => 60, 'upscaling' => true, 'cropping' => true, ); $pravin= $each->single_image->size(790,650,$option1); ?> <img alt="image" src="<?php echo $pravin->url; ?>" /> </div> </div> <div class="container container-body"> <div class="row"> <div class="col-md-5 col-md-push-7 col-sm-8 col-sm-push-4 animated fadeInUp"> <?php echo $each->body; ?> </div> </div> <!--end of row--> </div> <!--end of container--> </section> <!--Content with Image on left--> <?php } else { ?> <!--Content with Image on right--> <section class="imageblock about-1"> <div class="imageblock__content col-md-6 col-sm-4 pos-right animated fadeInRight"> <div class="background-image-holder"> <?php $pravin= $each->single_image->size(790,650,$option1); ?> <img alt="image" src="<?php echo $pravin->url; ?>" /> </div> </div> <div class="container container-body"> <div class="row"> <div class="col-md-5 col-sm-8 animated fadeInUp"> <?php echo $each->body; ?> </div> </div> <!--end of row--> </div> <!--end of container--> </section> <?php } ++$i; } ?> <!--Content with Image on right-->  
    • By [Code] Specialist
      Hi Folks,
      I like to develop a Backend Module with extra tabs and nested fields. Is there any Example Code for my Idea ? 
      The Module should create new fields, a new Tab and several fields on it. AND... Last but not least the fields depend on each other by rules like "if checkbox $a then unfold area with Textfield 1+2 "
      If someone have a snippet for me to learn ... it would be soooo great. Thanks. 
      Michael
×
×
  • Create New...