Database upgrades and New company Templates

[Phil Daintree-3]

Nicholas came up with a bright idea to upgrade the database
automatically on version upgrade and I have spec'd up how I think it
should work. Appreciate any feedback or pitfalls obvious to you with
this approach.

See http://www.weberp.org/AutomateDatabaseUpgrades

He also had a nice idea for having SQL template files for creating new
companies. In particular companies operating in different countries will
have differing charts of accounts and tax configurations.

How then to accumulate a good set of charts of accounts and
country/industry sql files to form the templates?

I could create a script to "harvest" the set up of your company but
allow you to edit out the general ledger accounts you don't wish to give
back to the project - eg. a chart of accounts might include company
specific accounts for "Loans to Phil" or "Investments in Lehman Bros"
etc. The script would then create an sql file to create a new chart of
accounts that would be specific to your business/country's requirements.

I think only a few tables are required to get a good tax setup specific
to your region - and without compromising your data.

Running this script would create a new template without any of the
company specific private information in it.

The big question though, would any business be prepared to run it and
donate a template for their country/industry??

If they would then I could modify the installation script to allow
selection of one of the templates to form the basis of their new company
set up with a preconfigured tax set up and chart of accounts relevant to
their industry/business. The same logic could also be applied to the
Z_MakeNewCompany.php script. I am thinking that rather than duplicate
the entire weberp-new.sql script - that the templates run the normal
weberp-new.sql script then run the template.sql script that loads the
chart, updates the default currency and country of operation against the
weberpdemo company, and loads the taxauthorities, taxgroups etc to
provide the tax configuration for the locale.

What do you think people is this a worthwhile project?

Phil

------------------------------------------------------------------------------
Come build with us! The BlackBerry® Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9-12, 2009. Register now!
http://p.sf.net/sfu/devconf
_______________________________________________
Web-erp-developers mailing list
Web-erp-developers@...
https://lists.sourceforge.net/lists/listinfo/web-erp-developers

[Javier de Lorenzo-Cáceres]

> Nicholas came up with a bright idea to upgrade the database
> automatically on version upgrade and I have spec'd up how I think it
> should work. Appreciate any feedback or pitfalls obvious to you with
> this approach.
>
> See http://www.weberp.org/AutomateDatabaseUpgrades
>

I think that upgrades is a subject for admins, not users. In many cases
admins are just a piece of meat with a couple of eyes but they have the
privileges and the reponsibility that users don't have. $dbuser should have
just the minimum permissions to use the application, i.e., should not have
the privileges to upgrade the database, i.e., I would not tell admins to do
something like

mysql> GRANT ALL PRIVILEGES ON *.* TO weberp_db_user@localhost IDENTIFIED BY
'some_pass' WITH GRANT OPTION;

exactly what is written in INSTALL.txt where I can't see any reference to
the install directory and /install/index.php either.

Admins should make the upgrades, the database backups, the users'
permissions, etc. and of course, for automatic upgrade to work, admin has
upgraded files first, they have edited config.php

Admins have to make two things: upgrade files and upgrade the database and
usually they will make one thing first. It doesn't matter what they do
first,  until both things are finished users should not login and it will be
good that login prevents users from using an inconsistent installation but I
don't think this should be taken further.

> He also had a nice idea for having SQL template files for creating new
> companies. In particular companies operating in different countries will
> have differing charts of accounts and tax configurations.
>
> How then to accumulate a good set of charts of accounts and
> country/industry sql files to form the templates?
>
> I could create a script to "harvest" the set up of your company but
> allow you to edit out the general ledger accounts you don't wish to give
> back to the project - eg. a chart of accounts might include company
> specific accounts for "Loans to Phil" or "Investments in Lehman Bros"
> etc. The script would then create an sql file to create a new chart of
> accounts that would be specific to your business/country's requirements.
>
> I think only a few tables are required to get a good tax setup specific
> to your region - and without compromising your data.
>
> Running this script would create a new template without any of the
> company specific private information in it.
>
> The big question though, would any business be prepared to run it and
> donate a template for their country/industry??
>

This would be fantastic.
Who knows, there are all kind of people. I would ask before doing that hard
work.

> If they would then I could modify the installation script to allow
> selection of one of the templates to form the basis of their new company
> set up with a preconfigured tax set up and chart of accounts relevant to
> their industry/business. The same logic could also be applied to the
> Z_MakeNewCompany.php script. I am thinking that rather than duplicate
> the entire weberp-new.sql script - that the templates run the normal
> weberp-new.sql script then run the template.sql script that loads the
> chart, updates the default currency and country of operation against the
> weberpdemo company, and loads the taxauthorities, taxgroups etc to
> provide the tax configuration for the locale.
>
> What do you think people is this a worthwhile project?

With the collaboration of companies, I think it would be really great. Also,
I'm now searching webERP documentation for a way to load/import a chart of
accounts, I have it in csv/spreadsheet.

javier

> Phil
>
> ------------------------------------------------------------------------------
> Come build with us! The BlackBerry® Developer Conference in SF, CA
> is the only developer event you need to attend this year. Jumpstart your
> developing skills, take BlackBerry mobile applications to market and stay
> ahead of the curve. Join us from November 9-12, 2009. Register
> now!
> http://p.sf.net/sfu/devconf
> _______________________________________________
> Web-erp-developers mailing list
> Web-erp-developers@...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers 

------------------------------------------------------------------------------
Come build with us! The BlackBerry® Developer Conference in SF, CA
is the only developer event you need to attend this year. Jumpstart your
developing skills, take BlackBerry mobile applications to market and stay
ahead of the curve. Join us from November 9-12, 2009. Register now!
http://p.sf.net/sfu/devconf
_______________________________________________
Web-erp-developers mailing list
Web-erp-developers@...
https://lists.sourceforge.net/lists/listinfo/web-erp-developers

[Nicholas Lee]

On Mon, Sep 21, 2009 at 9:05 PM, Phil Daintree <phil@...> wrote:

Nicholas came up with a bright idea to upgrade the database
automatically on version upgrade and I have spec'd up how I think it
should work. Appreciate any feedback or pitfalls obvious to you with
this approach.

See http://www.weberp.org/AutomateDatabaseUpgrades

My idea was more like Rails migrations that the automated application of "upgradeX.XX-Y.YY.sql" you have suggested.

I was suggesting a single XXX.sql file for each atomic change to the DB.  This also gets around your problem. The upgrade application script should care about the DB version. It just processes each XXX and XXX+1 sql script in order until the version reference in the database is the same as the reference defined by the release manager. Note in the trunk version this on file system db version ref might be 99999999999... so all sql updates get applied automatically.

N

Nicholas

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
Web-erp-developers mailing list
Web-erp-developers@...
https://lists.sourceforge.net/lists/listinfo/web-erp-developers

[Bogdan Stanciu]

when changing a database which contains critical information, extereme
care should be taken. while some new tables couldn't harm too much, a
change in existing structures could. So thorough testing is needed.
therefore, i think user's decision should be involved.


Of course, we should have a tool for doing it easily, efficiently, but
just letting the system do it for you, it seems too risky to me.


What we could also take into consideration, is a general update tool,
which checks the server for new versions, informs and can perform updates.



Nicholas Lee wrote:

>
>
> On Mon, Sep 21, 2009 at 9:05 PM, Phil Daintree <phil@...
> <mailto:phil@...>> wrote:
>
>     Nicholas came up with a bright idea to upgrade the database
>     automatically on version upgrade and I have spec'd up how I think it
>     should work. Appreciate any feedback or pitfalls obvious to you with
>     this approach.
>
>     See http://www.weberp.org/AutomateDatabaseUpgrades
>
>
> My idea was more like Rails migrations that the automated application
> of "upgradeX.XX-Y.YY.sql" you have suggested.
>
> I was suggesting a single XXX.sql file for each atomic change to the
> DB.  This also gets around your problem. The upgrade application
> script should care about the DB version. It just processes each XXX
> and XXX+1 sql script in order until the version reference in the
> database is the same as the reference defined by the release manager.
> Note in the trunk version this on file system db version ref might be
> 99999999999... so all sql updates get applied automatically.
>
> N
>
> Nicholas
> ------------------------------------------------------------------------
>
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
> trial. Simplify your report design, integration and deployment - and focus on
> what you do best, core application coding. Discover what's new with
> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> ------------------------------------------------------------------------
>
> _______________________________________________
> Web-erp-developers mailing list
> Web-erp-developers@...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>  

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
Web-erp-developers mailing list
Web-erp-developers@...
https://lists.sourceforge.net/lists/listinfo/web-erp-developers

[Tim Schofield]

One problem with any auto updating tool is that people often use
customised webERP implementations.

I like Nicholas' idea but I'm not sure about a separate file for each
update. This could end up with huge volume of files in a few years
time. I still think my idea of checking whether updates have been done
before doing it has some mileage, just my implementation of it was
rubbish.

Tim


2009/11/5 Bogdan Stanciu <bogdanovidiu.stanciu@...>:

> when changing a database which contains critical information, extereme
> care should be taken. while some new tables couldn't harm too much, a
> change in existing structures could. So thorough testing is needed.
> therefore, i think user's decision should be involved.
>
>
> Of course, we should have a tool for doing it easily, efficiently, but
> just letting the system do it for you, it seems too risky to me.
>
>
> What we could also take into consideration, is a general update tool,
> which checks the server for new versions, informs and can perform updates.
>
>
>
> Nicholas Lee wrote:
>
>>
>>
>> On Mon, Sep 21, 2009 at 9:05 PM, Phil Daintree <phil@...
>> <mailto:phil@...>> wrote:
>>
>>     Nicholas came up with a bright idea to upgrade the database
>>     automatically on version upgrade and I have spec'd up how I think it
>>     should work. Appreciate any feedback or pitfalls obvious to you with
>>     this approach.
>>
>>     See http://www.weberp.org/AutomateDatabaseUpgrades
>>
>>
>> My idea was more like Rails migrations that the automated application
>> of "upgradeX.XX-Y.YY.sql" you have suggested.
>>
>> I was suggesting a single XXX.sql file for each atomic change to the
>> DB.  This also gets around your problem. The upgrade application
>> script should care about the DB version. It just processes each XXX
>> and XXX+1 sql script in order until the version reference in the
>> database is the same as the reference defined by the release manager.
>> Note in the trunk version this on file system db version ref might be
>> 99999999999... so all sql updates get applied automatically.
>>
>> N
>>
>> Nicholas
>> ------------------------------------------------------------------------
>>
>> ------------------------------------------------------------------------------
>> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
>> trial. Simplify your report design, integration and deployment - and focus on
>> what you do best, core application coding. Discover what's new with
>> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
>> ------------------------------------------------------------------------
>>
>> _______________________________________________
>> Web-erp-developers mailing list
>> Web-erp-developers@...
>> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>>
>
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
> trial. Simplify your report design, integration and deployment - and focus on
> what you do best, core application coding. Discover what's new with
> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> _______________________________________________
> Web-erp-developers mailing list
> Web-erp-developers@...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
Web-erp-developers mailing list
Web-erp-developers@...
https://lists.sourceforge.net/lists/listinfo/web-erp-developers

[Nicholas Lee]

Consolidate the sql files at each update.

So 01 .. 0.9 .sql  becomes just 09.sql.

Clever schema might be:

• 311NNN.sql reduce to 311999.sql when 3.12 is released
  
• 312NNN.sql reduce to 312999.sql when 3.13 is released
  
• 313NNN.sql
  

This obviously depends on the file system glob ordering to work, but it is simple and seems to fix the persistent problem weberp has with database upgrades.

Plus it forces the developers to order their db updates in a functional and consistent way.

Nicholas

On Sat, Nov 7, 2009 at 12:32 AM, Tim Schofield <tim.schofield1960@...> wrote:

One problem with any auto updating tool is that people often use
customised webERP implementations.

I like Nicholas' idea but I'm not sure about a separate file for each
update. This could end up with huge volume of files in a few years
time. I still think my idea of checking whether updates have been done
before doing it has some mileage, just my implementation of it was
rubbish.

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
Web-erp-developers mailing list
Web-erp-developers@...
https://lists.sourceforge.net/lists/listinfo/web-erp-developers

[Tim Schofield]

Hi,

I have been giving some thought to this problem. I have a working
solution following Nicholas' idea of numbered updates. However it uses
mysql procedures that are only in version 5.something of mysql. Is
this a problem? I know that people who have control of their own
servers are ok, but what about hosted solutions? Do people have hosted
solutions with mysql still at a version less than 5?

Thanks
Tim


2009/11/9 Nicholas Lee <emptysands@...>:

>
> Consolidate the sql files at each update.
>
> So 01 .. 0.9 .sql  becomes just 09.sql.
>
> Clever schema might be:
>
> 311NNN.sql reduce to 311999.sql when 3.12 is released
> 312NNN.sql reduce to 312999.sql when 3.13 is released
> 313NNN.sql
>
> This obviously depends on the file system glob ordering to work, but it is
> simple and seems to fix the persistent problem weberp has with database
> upgrades.
>
> Plus it forces the developers to order their db updates in a functional and
> consistent way.
>
> Nicholas
>
> On Sat, Nov 7, 2009 at 12:32 AM, Tim Schofield
> <tim.schofield1960@...> wrote:
>>
>> One problem with any auto updating tool is that people often use
>> customised webERP implementations.
>>
>> I like Nicholas' idea but I'm not sure about a separate file for each
>> update. This could end up with huge volume of files in a few years
>> time. I still think my idea of checking whether updates have been done
>> before doing it has some mileage, just my implementation of it was
>> rubbish.
>>
>
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
> trial. Simplify your report design, integration and deployment - and focus
> on
> what you do best, core application coding. Discover what's new with
> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> _______________________________________________
> Web-erp-developers mailing list
> Web-erp-developers@...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>
>

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
Web-erp-developers mailing list
Web-erp-developers@...
https://lists.sourceforge.net/lists/listinfo/web-erp-developers

[Phil Daintree-3]

Are we ready to commit completely to mysql now Oracle owns it?
In my view it would be nice to have a solution that is not RDBMS
dependent - with the code in PHP rather than as a stored procedure.

Phil

Tim Schofield wrote:

> Hi,
>
> I have been giving some thought to this problem. I have a working
> solution following Nicholas' idea of numbered updates. However it uses
> mysql procedures that are only in version 5.something of mysql. Is
> this a problem? I know that people who have control of their own
> servers are ok, but what about hosted solutions? Do people have hosted
> solutions with mysql still at a version less than 5?
>
> Thanks
> Tim
>
>
> 2009/11/9 Nicholas Lee <emptysands@...>:
>> Consolidate the sql files at each update.
>>
>> So 01 .. 0.9 .sql  becomes just 09.sql.
>>
>> Clever schema might be:
>>
>> 311NNN.sql reduce to 311999.sql when 3.12 is released
>> 312NNN.sql reduce to 312999.sql when 3.13 is released
>> 313NNN.sql
>>
>> This obviously depends on the file system glob ordering to work, but it is
>> simple and seems to fix the persistent problem weberp has with database
>> upgrades.
>>
>> Plus it forces the developers to order their db updates in a functional and
>> consistent way.
>>
>> Nicholas
>>
>> On Sat, Nov 7, 2009 at 12:32 AM, Tim Schofield
>> <tim.schofield1960@...> wrote:
>>> One problem with any auto updating tool is that people often use
>>> customised webERP implementations.
>>>
>>> I like Nicholas' idea but I'm not sure about a separate file for each
>>> update. This could end up with huge volume of files in a few years
>>> time. I still think my idea of checking whether updates have been done
>>> before doing it has some mileage, just my implementation of it was
>>> rubbish.
>>>
>> ------------------------------------------------------------------------------
>> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
>> trial. Simplify your report design, integration and deployment - and focus
>> on
>> what you do best, core application coding. Discover what's new with
>> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
>> _______________________________________________
>> Web-erp-developers mailing list
>> Web-erp-developers@...
>> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>>
>>
>
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
> trial. Simplify your report design, integration and deployment - and focus on
> what you do best, core application coding. Discover what's new with
> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> _______________________________________________
> Web-erp-developers mailing list
> Web-erp-developers@...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
Web-erp-developers mailing list
Web-erp-developers@...
https://lists.sourceforge.net/lists/listinfo/web-erp-developers

[David Lamotte]

Hi All,

I don't really think that there is much to fear from who owns MySQL, but
I do think that we keep the database abstracted from the code to allow
any database to be supported in future.

If we need some idea on how to do this, then Moodle (http://moodle.org)
is a good example. It currently supports MySQL, PostgreSQL, Oracle and
Microsoft SQL Server.

HTH,
David

Phil Daintree wrote:
> Are we ready to commit completely to mysql now Oracle owns it?
> In my view it would be nice to have a solution that is not RDBMS
> dependent - with the code in PHP rather than as a stored procedure.
>
> Phil

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
Web-erp-developers mailing list
Web-erp-developers@...
https://lists.sourceforge.net/lists/listinfo/web-erp-developers

[phil9x]

The Oracle Sun deal has not yet been completed.
EU still objecting a hearing is to be held on Nov 22, Have a feeling that might just be when we find out what is to happen to MySQL
There is already a fork called Maria DB which has the backing of one of the original members of the project.
http://www.eeoc.gov/gina_supplement.pdf
http://www.theregister.co.uk/2009/11/13/eu_optimistic_mysql/
Philipp

Phil Daintree wrote:

Are we ready to commit completely to mysql now Oracle owns it?
In my view it would be nice to have a solution that is not RDBMS 
dependent - with the code in PHP rather than as a stored procedure.

Phil

Tim Schofield wrote:
  

Hi,

I have been giving some thought to this problem. I have a working
solution following Nicholas' idea of numbered updates. However it uses
mysql procedures that are only in version 5.something of mysql. Is
this a problem? I know that people who have control of their own
servers are ok, but what about hosted solutions? Do people have hosted
solutions with mysql still at a version less than 5?

Thanks
Tim


2009/11/9 Nicholas Lee emptysands@...:
    

Consolidate the sql files at each update.

So 01 .. 0.9 .sql  becomes just 09.sql.

Clever schema might be:

311NNN.sql reduce to 311999.sql when 3.12 is released
312NNN.sql reduce to 312999.sql when 3.13 is released
313NNN.sql

This obviously depends on the file system glob ordering to work, but it is
simple and seems to fix the persistent problem weberp has with database
upgrades.

Plus it forces the developers to order their db updates in a functional and
consistent way.

Nicholas

On Sat, Nov 7, 2009 at 12:32 AM, Tim Schofield
tim.schofield1960@... wrote:
      

One problem with any auto updating tool is that people often use
customised webERP implementations.

I like Nicholas' idea but I'm not sure about a separate file for each
update. This could end up with huge volume of files in a few years
time. I still think my idea of checking whether updates have been done
before doing it has some mileage, just my implementation of it was
rubbish.

        

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus
on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
Web-erp-developers mailing list
Web-erp-developers@...
https://lists.sourceforge.net/lists/listinfo/web-erp-developers


      

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day 
trial. Simplify your report design, integration and deployment - and focus on 
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
Web-erp-developers mailing list
Web-erp-developers@...
https://lists.sourceforge.net/lists/listinfo/web-erp-developers

    

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day 
trial. Simplify your report design, integration and deployment - and focus on 
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
Web-erp-developers mailing list
Web-erp-developers@...
https://lists.sourceforge.net/lists/listinfo/web-erp-developers

  

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
Web-erp-developers mailing list
Web-erp-developers@...
https://lists.sourceforge.net/lists/listinfo/web-erp-developers

[Tim Schofield]

Hi Phil,

Well I thought a lot on whether it should be done in sql or php. My
attempt at adding the comments in the upgrade3.10-3.11.sql script were
a method of upgrading via php, as the php upgrade script used these
comments to report on the upgrading. This fell down because some
people prefer to upgrade using the sql script directly.

My plan was to do as Nicholas suggested and record in the database the
latest update that had been applied. However if you do this in the php
script then the people who use the sql directly dont get that info
recorded. This could be a problem if the next time the person
upgrading decided to use the php script.

This was why I looked for a solution that used only sql. The problem
is that I don't see a way of doing it without using
functions/procedures.

Thanks
Tim


2009/11/16 Phil Daintree <phil@...>:

> Are we ready to commit completely to mysql now Oracle owns it?
> In my view it would be nice to have a solution that is not RDBMS
> dependent - with the code in PHP rather than as a stored procedure.
>
> Phil
>
> Tim Schofield wrote:
>> Hi,
>>
>> I have been giving some thought to this problem. I have a working
>> solution following Nicholas' idea of numbered updates. However it uses
>> mysql procedures that are only in version 5.something of mysql. Is
>> this a problem? I know that people who have control of their own
>> servers are ok, but what about hosted solutions? Do people have hosted
>> solutions with mysql still at a version less than 5?
>>
>> Thanks
>> Tim
>>
>>
>> 2009/11/9 Nicholas Lee <emptysands@...>:
>>> Consolidate the sql files at each update.
>>>
>>> So 01 .. 0.9 .sql  becomes just 09.sql.
>>>
>>> Clever schema might be:
>>>
>>> 311NNN.sql reduce to 311999.sql when 3.12 is released
>>> 312NNN.sql reduce to 312999.sql when 3.13 is released
>>> 313NNN.sql
>>>
>>> This obviously depends on the file system glob ordering to work, but it is
>>> simple and seems to fix the persistent problem weberp has with database
>>> upgrades.
>>>
>>> Plus it forces the developers to order their db updates in a functional and
>>> consistent way.
>>>
>>> Nicholas
>>>
>>> On Sat, Nov 7, 2009 at 12:32 AM, Tim Schofield
>>> <tim.schofield1960@...> wrote:
>>>> One problem with any auto updating tool is that people often use
>>>> customised webERP implementations.
>>>>
>>>> I like Nicholas' idea but I'm not sure about a separate file for each
>>>> update. This could end up with huge volume of files in a few years
>>>> time. I still think my idea of checking whether updates have been done
>>>> before doing it has some mileage, just my implementation of it was
>>>> rubbish.
>>>>
>>> ------------------------------------------------------------------------------
>>> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
>>> trial. Simplify your report design, integration and deployment - and focus
>>> on
>>> what you do best, core application coding. Discover what's new with
>>> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
>>> _______________________________________________
>>> Web-erp-developers mailing list
>>> Web-erp-developers@...
>>> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>>>
>>>
>>
>> ------------------------------------------------------------------------------
>> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
>> trial. Simplify your report design, integration and deployment - and focus on
>> what you do best, core application coding. Discover what's new with
>> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
>> _______________________________________________
>> Web-erp-developers mailing list
>> Web-erp-developers@...
>> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>>
>
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
> trial. Simplify your report design, integration and deployment - and focus on
> what you do best, core application coding. Discover what's new with
> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> _______________________________________________
> Web-erp-developers mailing list
> Web-erp-developers@...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
Web-erp-developers mailing list
Web-erp-developers@...
https://lists.sourceforge.net/lists/listinfo/web-erp-developers

[Phil Daintree-3]

Yes I did see that Monty the driving force of mysql has forked the code
to this Maria SQL - which does provide some comfort. I think it is
unlikely that the run can be pulled from under us with mysql really, but
still it is nice not to depend completely on the backend RDBMS.

Phil

Phil Daintree wrote:

> Are we ready to commit completely to mysql now Oracle owns it?
> In my view it would be nice to have a solution that is not RDBMS
> dependent - with the code in PHP rather than as a stored procedure.
>
> Phil
>
> Tim Schofield wrote:
>> Hi,
>>
>> I have been giving some thought to this problem. I have a working
>> solution following Nicholas' idea of numbered updates. However it uses
>> mysql procedures that are only in version 5.something of mysql. Is
>> this a problem? I know that people who have control of their own
>> servers are ok, but what about hosted solutions? Do people have hosted
>> solutions with mysql still at a version less than 5?
>>
>> Thanks
>> Tim
>>
>>
>> 2009/11/9 Nicholas Lee <emptysands@...>:
>>> Consolidate the sql files at each update.
>>>
>>> So 01 .. 0.9 .sql  becomes just 09.sql.
>>>
>>> Clever schema might be:
>>>
>>> 311NNN.sql reduce to 311999.sql when 3.12 is released
>>> 312NNN.sql reduce to 312999.sql when 3.13 is released
>>> 313NNN.sql
>>>
>>> This obviously depends on the file system glob ordering to work, but it is
>>> simple and seems to fix the persistent problem weberp has with database
>>> upgrades.
>>>
>>> Plus it forces the developers to order their db updates in a functional and
>>> consistent way.
>>>
>>> Nicholas
>>>
>>> On Sat, Nov 7, 2009 at 12:32 AM, Tim Schofield
>>> <tim.schofield1960@...> wrote:
>>>> One problem with any auto updating tool is that people often use
>>>> customised webERP implementations.
>>>>
>>>> I like Nicholas' idea but I'm not sure about a separate file for each
>>>> update. This could end up with huge volume of files in a few years
>>>> time. I still think my idea of checking whether updates have been done
>>>> before doing it has some mileage, just my implementation of it was
>>>> rubbish.
>>>>
>>> ------------------------------------------------------------------------------
>>> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
>>> trial. Simplify your report design, integration and deployment - and focus
>>> on
>>> what you do best, core application coding. Discover what's new with
>>> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
>>> _______________________________________________
>>> Web-erp-developers mailing list
>>> Web-erp-developers@...
>>> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>>>
>>>
>> ------------------------------------------------------------------------------
>> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
>> trial. Simplify your report design, integration and deployment - and focus on
>> what you do best, core application coding. Discover what's new with
>> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
>> _______________________________________________
>> Web-erp-developers mailing list
>> Web-erp-developers@...
>> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>>
>
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
> trial. Simplify your report design, integration and deployment - and focus on
> what you do best, core application coding. Discover what's new with
> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> _______________________________________________
> Web-erp-developers mailing list
> Web-erp-developers@...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
Web-erp-developers mailing list
Web-erp-developers@...
https://lists.sourceforge.net/lists/listinfo/web-erp-developers

[Tim Schofield]

Hi Phil,

I am fully behind your comments about not depending solely on one
RDBMS. However most RDBMs have the ability to use stored procedures in
one form or another. Certainly my procedure would run - almost -
unaltered on DB2 and Oracle. Not sure about postgre, its been a while.

Thanks
Tim


2009/11/17 Phil Daintree <phil@...>:

> Yes I did see that Monty the driving force of mysql has forked the code
> to this Maria SQL - which does provide some comfort. I think it is
> unlikely that the run can be pulled from under us with mysql really, but
> still it is nice not to depend completely on the backend RDBMS.
>
> Phil
>
> Phil Daintree wrote:
>> Are we ready to commit completely to mysql now Oracle owns it?
>> In my view it would be nice to have a solution that is not RDBMS
>> dependent - with the code in PHP rather than as a stored procedure.
>>
>> Phil
>>
>> Tim Schofield wrote:
>>> Hi,
>>>
>>> I have been giving some thought to this problem. I have a working
>>> solution following Nicholas' idea of numbered updates. However it uses
>>> mysql procedures that are only in version 5.something of mysql. Is
>>> this a problem? I know that people who have control of their own
>>> servers are ok, but what about hosted solutions? Do people have hosted
>>> solutions with mysql still at a version less than 5?
>>>
>>> Thanks
>>> Tim
>>>
>>>
>>> 2009/11/9 Nicholas Lee <emptysands@...>:
>>>> Consolidate the sql files at each update.
>>>>
>>>> So 01 .. 0.9 .sql  becomes just 09.sql.
>>>>
>>>> Clever schema might be:
>>>>
>>>> 311NNN.sql reduce to 311999.sql when 3.12 is released
>>>> 312NNN.sql reduce to 312999.sql when 3.13 is released
>>>> 313NNN.sql
>>>>
>>>> This obviously depends on the file system glob ordering to work, but it is
>>>> simple and seems to fix the persistent problem weberp has with database
>>>> upgrades.
>>>>
>>>> Plus it forces the developers to order their db updates in a functional and
>>>> consistent way.
>>>>
>>>> Nicholas
>>>>
>>>> On Sat, Nov 7, 2009 at 12:32 AM, Tim Schofield
>>>> <tim.schofield1960@...> wrote:
>>>>> One problem with any auto updating tool is that people often use
>>>>> customised webERP implementations.
>>>>>
>>>>> I like Nicholas' idea but I'm not sure about a separate file for each
>>>>> update. This could end up with huge volume of files in a few years
>>>>> time. I still think my idea of checking whether updates have been done
>>>>> before doing it has some mileage, just my implementation of it was
>>>>> rubbish.
>>>>>
>>>> ------------------------------------------------------------------------------
>>>> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
>>>> trial. Simplify your report design, integration and deployment - and focus
>>>> on
>>>> what you do best, core application coding. Discover what's new with
>>>> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
>>>> _______________________________________________
>>>> Web-erp-developers mailing list
>>>> Web-erp-developers@...
>>>> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>>>>
>>>>
>>> ------------------------------------------------------------------------------
>>> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
>>> trial. Simplify your report design, integration and deployment - and focus on
>>> what you do best, core application coding. Discover what's new with
>>> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
>>> _______________________________________________
>>> Web-erp-developers mailing list
>>> Web-erp-developers@...
>>> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>>>
>>
>> ------------------------------------------------------------------------------
>> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
>> trial. Simplify your report design, integration and deployment - and focus on
>> what you do best, core application coding. Discover what's new with
>> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
>> _______________________________________________
>> Web-erp-developers mailing list
>> Web-erp-developers@...
>> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>>
>
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
> trial. Simplify your report design, integration and deployment - and focus on
> what you do best, core application coding. Discover what's new with
> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> _______________________________________________
> Web-erp-developers mailing list
> Web-erp-developers@...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
Web-erp-developers mailing list
Web-erp-developers@...
https://lists.sourceforge.net/lists/listinfo/web-erp-developers

[Lindsay Harris]

OK,  how about this.  [Note that I am in the habit of running the
upgrade SQL scripts - out of habit mostly,  and perhaps because I
didn't realise it could be done via the web interface.]

What concerns me about upgrades is that some regular user comes along
and starts using webERP when it's somewhere in the middle of being
upgraded.   So, I propose the following.

1/ No more upgrade scripts as such.  And a README in the directory to
explain the following method.

2/ If a regular user (i.e. NOT an administrator) logs in when the DB
and code are out of step,  they are told the problem and are blocked.

3/ When the admin logs in,  they are presented with an 'upgrade
needed' screen,  and one or more links to the upgrade scripts
required to bring the DB up to date.   [These would be PHP scripts,
with the SQL as an embedded 'heredoc']

4/ When step 3 completes successfully, the admin has the choice of
unblocking regular user access.   While this COULD be automatic at
the end of step 3,  a more cautious admin (e.g. me) might want to
verify that at least some things work as before.  Then unblock user
access.

Lindsay

On Tue, 17 Nov 2009 19:41:56 Tim Schofield wrote:

> Hi Phil,
>
> Well I thought a lot on whether it should be done in sql or php. My
> attempt at adding the comments in the upgrade3.10-3.11.sql script
> were a method of upgrading via php, as the php upgrade script used
> these comments to report on the upgrading. This fell down because
> some people prefer to upgrade using the sql script directly.
>
> My plan was to do as Nicholas suggested and record in the database
> the latest update that had been applied. However if you do this in
> the php script then the people who use the sql directly dont get
> that info recorded. This could be a problem if the next time the
> person upgrading decided to use the php script.
>
> This was why I looked for a solution that used only sql. The
> problem is that I don't see a way of doing it without using
> functions/procedures.
>
> Thanks
> Tim
>
> 2009/11/16 Phil Daintree <phil@...>:
> > Are we ready to commit completely to mysql now Oracle owns it?
> > In my view it would be nice to have a solution that is not RDBMS
> > dependent - with the code in PHP rather than as a stored
> > procedure.
> >
> > Phil
> >
> > Tim Schofield wrote:
> >> Hi,
> >>
> >> I have been giving some thought to this problem. I have a
> >> working solution following Nicholas' idea of numbered updates.
> >> However it uses mysql procedures that are only in version
> >> 5.something of mysql. Is this a problem? I know that people who
> >> have control of their own servers are ok, but what about hosted
> >> solutions? Do people have hosted solutions with mysql still at a
> >> version less than 5?
> >>
> >> Thanks
> >> Tim
> >>
> >> 2009/11/9 Nicholas Lee <emptysands@...>:
> >>> Consolidate the sql files at each update.
> >>>
> >>> So 01 .. 0.9 .sql  becomes just 09.sql.
> >>>
> >>> Clever schema might be:
> >>>
> >>> 311NNN.sql reduce to 311999.sql when 3.12 is released
> >>> 312NNN.sql reduce to 312999.sql when 3.13 is released
> >>> 313NNN.sql
> >>>
> >>> This obviously depends on the file system glob ordering to
> >>> work, but it is simple and seems to fix the persistent problem
> >>> weberp has with database upgrades.
> >>>
> >>> Plus it forces the developers to order their db updates in a
> >>> functional and consistent way.
> >>>
> >>> Nicholas
> >>>
> >>> On Sat, Nov 7, 2009 at 12:32 AM, Tim Schofield
> >>>
> >>> <tim.schofield1960@...> wrote:
> >>>> One problem with any auto updating tool is that people often
> >>>> use customised webERP implementations.
> >>>>
> >>>> I like Nicholas' idea but I'm not sure about a separate file
> >>>> for each update. This could end up with huge volume of files
> >>>> in a few years time. I still think my idea of checking whether
> >>>> updates have been done before doing it has some mileage, just
> >>>> my implementation of it was rubbish.
> >>>
> >>> ---------------------------------------------------------------
> >>>--------------- Let Crystal Reports handle the reporting - Free
> >>> Crystal Reports 2008 30-Day trial. Simplify your report design,
> >>> integration and deployment - and focus on
> >>> what you do best, core application coding. Discover what's new
> >>> with Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> >>> _______________________________________________
> >>> Web-erp-developers mailing list
> >>> Web-erp-developers@...
> >>> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
> >>
> >> ----------------------------------------------------------------
> >>-------------- Let Crystal Reports handle the reporting - Free
> >> Crystal Reports 2008 30-Day trial. Simplify your report design,
> >> integration and deployment - and focus on what you do best, core
> >> application coding. Discover what's new with Crystal Reports
> >> now.  http://p.sf.net/sfu/bobj-july
> >> _______________________________________________
> >> Web-erp-developers mailing list
> >> Web-erp-developers@...
> >> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
> >
> > -----------------------------------------------------------------
> >------------- Let Crystal Reports handle the reporting - Free
> > Crystal Reports 2008 30-Day trial. Simplify your report design,
> > integration and deployment - and focus on what you do best, core
> > application coding. Discover what's new with Crystal Reports now.
> >  http://p.sf.net/sfu/bobj-july
> > _______________________________________________
> > Web-erp-developers mailing list
> > Web-erp-developers@...
> > https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>
> -------------------------------------------------------------------
>----------- Let Crystal Reports handle the reporting - Free Crystal
> Reports 2008 30-Day trial. Simplify your report design, integration
> and deployment - and focus on what you do best, core application
> coding. Discover what's new with Crystal Reports now.
> http://p.sf.net/sfu/bobj-july
> _______________________________________________
> Web-erp-developers mailing list
> Web-erp-developers@...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
Web-erp-developers mailing list
Web-erp-developers@...
https://lists.sourceforge.net/lists/listinfo/web-erp-developers

[David Lamotte]

Hi Lindsay,

This sounds a little complex.

The way that Zen-Cart handles this is that an administrator can select a
'Maintenance Mode' which prevents normal users from logging in.

David

Lindsay Harris wrote:

> OK,  how about this.  [Note that I am in the habit of running the
> upgrade SQL scripts - out of habit mostly,  and perhaps because I
> didn't realise it could be done via the web interface.]
>
> What concerns me about upgrades is that some regular user comes along
> and starts using webERP when it's somewhere in the middle of being
> upgraded.   So, I propose the following.
>
> 1/ No more upgrade scripts as such.  And a README in the directory to
> explain the following method.
>
> 2/ If a regular user (i.e. NOT an administrator) logs in when the DB
> and code are out of step,  they are told the problem and are blocked.
>
> 3/ When the admin logs in,  they are presented with an 'upgrade
> needed' screen,  and one or more links to the upgrade scripts
> required to bring the DB up to date.   [These would be PHP scripts,
> with the SQL as an embedded 'heredoc']
>
> 4/ When step 3 completes successfully, the admin has the choice of
> unblocking regular user access.   While this COULD be automatic at
> the end of step 3,  a more cautious admin (e.g. me) might want to
> verify that at least some things work as before.  Then unblock user
> access.
>
> Lindsay
>
> On Tue, 17 Nov 2009 19:41:56 Tim Schofield wrote:
>> Hi Phil,
>>
>> Well I thought a lot on whether it should be done in sql or php. My
>> attempt at adding the comments in the upgrade3.10-3.11.sql script
>> were a method of upgrading via php, as the php upgrade script used
>> these comments to report on the upgrading. This fell down because
>> some people prefer to upgrade using the sql script directly.
>>
>> My plan was to do as Nicholas suggested and record in the database
>> the latest update that had been applied. However if you do this in
>> the php script then the people who use the sql directly dont get
>> that info recorded. This could be a problem if the next time the
>> person upgrading decided to use the php script.
>>
>> This was why I looked for a solution that used only sql. The
>> problem is that I don't see a way of doing it without using
>> functions/procedures.
>>
>> Thanks
>> Tim
>>
>> 2009/11/16 Phil Daintree <phil@...>:
>>> Are we ready to commit completely to mysql now Oracle owns it?
>>> In my view it would be nice to have a solution that is not RDBMS
>>> dependent - with the code in PHP rather than as a stored
>>> procedure.
>>>
>>> Phil
>>>
>>> Tim Schofield wrote:
>>>> Hi,
>>>>
>>>> I have been giving some thought to this problem. I have a
>>>> working solution following Nicholas' idea of numbered updates.
>>>> However it uses mysql procedures that are only in version
>>>> 5.something of mysql. Is this a problem? I know that people who
>>>> have control of their own servers are ok, but what about hosted
>>>> solutions? Do people have hosted solutions with mysql still at a
>>>> version less than 5?
>>>>
>>>> Thanks
>>>> Tim
>>>>
>>>> 2009/11/9 Nicholas Lee <emptysands@...>:
>>>>> Consolidate the sql files at each update.
>>>>>
>>>>> So 01 .. 0.9 .sql  becomes just 09.sql.
>>>>>
>>>>> Clever schema might be:
>>>>>
>>>>> 311NNN.sql reduce to 311999.sql when 3.12 is released
>>>>> 312NNN.sql reduce to 312999.sql when 3.13 is released
>>>>> 313NNN.sql
>>>>>
>>>>> This obviously depends on the file system glob ordering to
>>>>> work, but it is simple and seems to fix the persistent problem
>>>>> weberp has with database upgrades.
>>>>>
>>>>> Plus it forces the developers to order their db updates in a
>>>>> functional and consistent way.
>>>>>
>>>>> Nicholas
>>>>>
>>>>> On Sat, Nov 7, 2009 at 12:32 AM, Tim Schofield
>>>>>
>>>>> <tim.schofield1960@...> wrote:
>>>>>> One problem with any auto updating tool is that people often
>>>>>> use customised webERP implementations.
>>>>>>
>>>>>> I like Nicholas' idea but I'm not sure about a separate file
>>>>>> for each update. This could end up with huge volume of files
>>>>>> in a few years time. I still think my idea of checking whether
>>>>>> updates have been done before doing it has some mileage, just
>>>>>> my implementation of it was rubbish.
>>>>> ---------------------------------------------------------------
>>>>> --------------- Let Crystal Reports handle the reporting - Free
>>>>> Crystal Reports 2008 30-Day trial. Simplify your report design,
>>>>> integration and deployment - and focus on
>>>>> what you do best, core application coding. Discover what's new
>>>>> with Crystal Reports now.  http://p.sf.net/sfu/bobj-july
>>>>> _______________________________________________
>>>>> Web-erp-developers mailing list
>>>>> Web-erp-developers@...
>>>>> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>>>> ----------------------------------------------------------------
>>>> -------------- Let Crystal Reports handle the reporting - Free
>>>> Crystal Reports 2008 30-Day trial. Simplify your report design,
>>>> integration and deployment - and focus on what you do best, core
>>>> application coding. Discover what's new with Crystal Reports
>>>> now.  http://p.sf.net/sfu/bobj-july
>>>> _______________________________________________
>>>> Web-erp-developers mailing list
>>>> Web-erp-developers@...
>>>> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>>> -----------------------------------------------------------------
>>> ------------- Let Crystal Reports handle the reporting - Free
>>> Crystal Reports 2008 30-Day trial. Simplify your report design,
>>> integration and deployment - and focus on what you do best, core
>>> application coding. Discover what's new with Crystal Reports now.
>>>  http://p.sf.net/sfu/bobj-july
>>> _______________________________________________
>>> Web-erp-developers mailing list
>>> Web-erp-developers@...
>>> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>> -------------------------------------------------------------------
>> ----------- Let Crystal Reports handle the reporting - Free Crystal
>> Reports 2008 30-Day trial. Simplify your report design, integration
>> and deployment - and focus on what you do best, core application
>> coding. Discover what's new with Crystal Reports now.
>> http://p.sf.net/sfu/bobj-july
>> _______________________________________________
>> Web-erp-developers mailing list
>> Web-erp-developers@...
>> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>
>
>
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
> trial. Simplify your report design, integration and deployment - and focus on
> what you do best, core application coding. Discover what's new with
> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> _______________________________________________
> Web-erp-developers mailing list
> Web-erp-developers@...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>
>
> ------------------------------------------------------------------------
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.425 / Virus Database: 270.14.68/2507 - Release Date: 11/16/09 19:53:00
>

--
Regards,

David Lamotte
Managing Consultant

Rams-Pro Technology Pty Ltd
ABN: 43 126 822 026

Phone: [phone]
Fax:   [phone]
Mobile: [phone]
Int'l: [phone]
        [phone]
Web: http://rams-pro.com.au       

Disclaimer: This email and any attached files are intended solely for
the named addressee, are confidential and may contain legally privileged
information. The copying or distribution of them or any information they
contain, by anyone other than the addressee, is prohibited. If you have
received this email in error, please advise the sender by telephone or
return the email before destroying all copies. Thank you.

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
Web-erp-developers mailing list
Web-erp-developers@...
https://lists.sourceforge.net/lists/listinfo/web-erp-developers

[Tim Schofield]

Hi Lindsay, this is similar to what we have been looking at. However
users could all be at different update stages, depending on what
updates have already been applied. Also we need to store somewhere
what the latest update that has been applied is.

Thanks
Tim


2009/11/17 Lindsay Harris <lindsay@...>:

> OK,  how about this.  [Note that I am in the habit of running the
> upgrade SQL scripts - out of habit mostly,  and perhaps because I
> didn't realise it could be done via the web interface.]
>
> What concerns me about upgrades is that some regular user comes along
> and starts using webERP when it's somewhere in the middle of being
> upgraded.   So, I propose the following.
>
> 1/ No more upgrade scripts as such.  And a README in the directory to
> explain the following method.
>
> 2/ If a regular user (i.e. NOT an administrator) logs in when the DB
> and code are out of step,  they are told the problem and are blocked.
>
> 3/ When the admin logs in,  they are presented with an 'upgrade
> needed' screen,  and one or more links to the upgrade scripts
> required to bring the DB up to date.   [These would be PHP scripts,
> with the SQL as an embedded 'heredoc']
>
> 4/ When step 3 completes successfully, the admin has the choice of
> unblocking regular user access.   While this COULD be automatic at
> the end of step 3,  a more cautious admin (e.g. me) might want to
> verify that at least some things work as before.  Then unblock user
> access.
>
> Lindsay
>
> On Tue, 17 Nov 2009 19:41:56 Tim Schofield wrote:
>> Hi Phil,
>>
>> Well I thought a lot on whether it should be done in sql or php. My
>> attempt at adding the comments in the upgrade3.10-3.11.sql script
>> were a method of upgrading via php, as the php upgrade script used
>> these comments to report on the upgrading. This fell down because
>> some people prefer to upgrade using the sql script directly.
>>
>> My plan was to do as Nicholas suggested and record in the database
>> the latest update that had been applied. However if you do this in
>> the php script then the people who use the sql directly dont get
>> that info recorded. This could be a problem if the next time the
>> person upgrading decided to use the php script.
>>
>> This was why I looked for a solution that used only sql. The
>> problem is that I don't see a way of doing it without using
>> functions/procedures.
>>
>> Thanks
>> Tim
>>
>> 2009/11/16 Phil Daintree <phil@...>:
>> > Are we ready to commit completely to mysql now Oracle owns it?
>> > In my view it would be nice to have a solution that is not RDBMS
>> > dependent - with the code in PHP rather than as a stored
>> > procedure.
>> >
>> > Phil
>> >
>> > Tim Schofield wrote:
>> >> Hi,
>> >>
>> >> I have been giving some thought to this problem. I have a
>> >> working solution following Nicholas' idea of numbered updates.
>> >> However it uses mysql procedures that are only in version
>> >> 5.something of mysql. Is this a problem? I know that people who
>> >> have control of their own servers are ok, but what about hosted
>> >> solutions? Do people have hosted solutions with mysql still at a
>> >> version less than 5?
>> >>
>> >> Thanks
>> >> Tim
>> >>
>> >> 2009/11/9 Nicholas Lee <emptysands@...>:
>> >>> Consolidate the sql files at each update.
>> >>>
>> >>> So 01 .. 0.9 .sql  becomes just 09.sql.
>> >>>
>> >>> Clever schema might be:
>> >>>
>> >>> 311NNN.sql reduce to 311999.sql when 3.12 is released
>> >>> 312NNN.sql reduce to 312999.sql when 3.13 is released
>> >>> 313NNN.sql
>> >>>
>> >>> This obviously depends on the file system glob ordering to
>> >>> work, but it is simple and seems to fix the persistent problem
>> >>> weberp has with database upgrades.
>> >>>
>> >>> Plus it forces the developers to order their db updates in a
>> >>> functional and consistent way.
>> >>>
>> >>> Nicholas
>> >>>
>> >>> On Sat, Nov 7, 2009 at 12:32 AM, Tim Schofield
>> >>>
>> >>> <tim.schofield1960@...> wrote:
>> >>>> One problem with any auto updating tool is that people often
>> >>>> use customised webERP implementations.
>> >>>>
>> >>>> I like Nicholas' idea but I'm not sure about a separate file
>> >>>> for each update. This could end up with huge volume of files
>> >>>> in a few years time. I still think my idea of checking whether
>> >>>> updates have been done before doing it has some mileage, just
>> >>>> my implementation of it was rubbish.
>> >>>
>> >>> ---------------------------------------------------------------
>> >>>--------------- Let Crystal Reports handle the reporting - Free
>> >>> Crystal Reports 2008 30-Day trial. Simplify your report design,
>> >>> integration and deployment - and focus on
>> >>> what you do best, core application coding. Discover what's new
>> >>> with Crystal Reports now.  http://p.sf.net/sfu/bobj-july
>> >>> _______________________________________________
>> >>> Web-erp-developers mailing list
>> >>> Web-erp-developers@...
>> >>> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>> >>
>> >> ----------------------------------------------------------------
>> >>-------------- Let Crystal Reports handle the reporting - Free
>> >> Crystal Reports 2008 30-Day trial. Simplify your report design,
>> >> integration and deployment - and focus on what you do best, core
>> >> application coding. Discover what's new with Crystal Reports
>> >> now.  http://p.sf.net/sfu/bobj-july
>> >> _______________________________________________
>> >> Web-erp-developers mailing list
>> >> Web-erp-developers@...
>> >> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>> >
>> > -----------------------------------------------------------------
>> >------------- Let Crystal Reports handle the reporting - Free
>> > Crystal Reports 2008 30-Day trial. Simplify your report design,
>> > integration and deployment - and focus on what you do best, core
>> > application coding. Discover what's new with Crystal Reports now.
>> >  http://p.sf.net/sfu/bobj-july
>> > _______________________________________________
>> > Web-erp-developers mailing list
>> > Web-erp-developers@...
>> > https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>>
>> -------------------------------------------------------------------
>>----------- Let Crystal Reports handle the reporting - Free Crystal
>> Reports 2008 30-Day trial. Simplify your report design, integration
>> and deployment - and focus on what you do best, core application
>> coding. Discover what's new with Crystal Reports now.
>> http://p.sf.net/sfu/bobj-july
>> _______________________________________________
>> Web-erp-developers mailing list
>> Web-erp-developers@...
>> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>
>
>
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
> trial. Simplify your report design, integration and deployment - and focus on
> what you do best, core application coding. Discover what's new with
> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> _______________________________________________
> Web-erp-developers mailing list
> Web-erp-developers@...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
Web-erp-developers mailing list
Web-erp-developers@...
https://lists.sourceforge.net/lists/listinfo/web-erp-developers

[Lindsay Harris]

Tim,
The current schema ID should be stored in the "config" table in the
DB.  As that table is (I'm guessing) loaded in very early in
operations (e.g. checking HTTPS connection), there would be no issues
with extracting the DB schema ID and comparing it to the one wired
into webERP's code.

Also implied by the above is distinguishing the DB schema ID (or
version) from the version of webERP in use.  These two version
numbers need not be both the same.   Among other things, this would
allow developers to add incremental changes to the DB schema between
releases, and the appropriate changes would be made for developers
too.  The whole collection would be applied to the next release.  The
only requirement would be for the DB version to be monotonically
increasing.

Tim, I'm not sure what you mean by "users could all be at different
update stages".  Does this refer to multiple companies on the one web
server?   If so,  I don't see it as a problem.  Each company is on a
different database.  If they are using the same copy of webERP on the
server,  then all the code is updated at one time.  So each company
would be locked out of the database until their admin updates the DB.  
But this is what must happen.  But I run just one company, and it's
quite likely I'm missing some obvious piece of reality :-(

And David's idea of a "maintenance mode" option is a good one that we
should have.  I would still like to have discrepancies between code
version and DB version automatically block user access - that is a
serious issue.

Lindsay

On Tue, 17 Nov 2009 21:02:48 Tim Schofield wrote:

> Hi Lindsay, this is similar to what we have been looking at.
> However users could all be at different update stages, depending on
> what updates have already been applied. Also we need to store
> somewhere what the latest update that has been applied is.
>
> Thanks
> Tim
>
> 2009/11/17 Lindsay Harris <lindsay@...>:
> > OK,  how about this.  [Note that I am in the habit of running the
> > upgrade SQL scripts - out of habit mostly,  and perhaps because I
> > didn't realise it could be done via the web interface.]
> >
> > What concerns me about upgrades is that some regular user comes
> > along and starts using webERP when it's somewhere in the middle
> > of being upgraded.   So, I propose the following.
> >
> > 1/ No more upgrade scripts as such.  And a README in the
> > directory to explain the following method.
> >
> > 2/ If a regular user (i.e. NOT an administrator) logs in when the
> > DB and code are out of step,  they are told the problem and are
> > blocked.
> >
> > 3/ When the admin logs in,  they are presented with an 'upgrade
> > needed' screen,  and one or more links to the upgrade scripts
> > required to bring the DB up to date.   [These would be PHP
> > scripts, with the SQL as an embedded 'heredoc']
> >
> > 4/ When step 3 completes successfully, the admin has the choice
> > of unblocking regular user access.   While this COULD be
> > automatic at the end of step 3,  a more cautious admin (e.g. me)
> > might want to verify that at least some things work as before.
> >  Then unblock user access.
> >
> > Lindsay
> >
> > On Tue, 17 Nov 2009 19:41:56 Tim Schofield wrote:
> >> Hi Phil,
> >>
> >> Well I thought a lot on whether it should be done in sql or php.
> >> My attempt at adding the comments in the upgrade3.10-3.11.sql
> >> script were a method of upgrading via php, as the php upgrade
> >> script used these comments to report on the upgrading. This fell
> >> down because some people prefer to upgrade using the sql script
> >> directly.
> >>
> >> My plan was to do as Nicholas suggested and record in the
> >> database the latest update that had been applied. However if you
> >> do this in the php script then the people who use the sql
> >> directly dont get that info recorded. This could be a problem if
> >> the next time the person upgrading decided to use the php
> >> script.
> >>
> >> This was why I looked for a solution that used only sql. The
> >> problem is that I don't see a way of doing it without using
> >> functions/procedures.
> >>
> >> Thanks
> >> Tim
> >>
> >> 2009/11/16 Phil Daintree <phil@...>:
> >> > Are we ready to commit completely to mysql now Oracle owns it?
> >> > In my view it would be nice to have a solution that is not
> >> > RDBMS dependent - with the code in PHP rather than as a stored
> >> > procedure.
> >> >
> >> > Phil
> >> >
> >> > Tim Schofield wrote:
> >> >> Hi,
> >> >>
> >> >> I have been giving some thought to this problem. I have a
> >> >> working solution following Nicholas' idea of numbered
> >> >> updates. However it uses mysql procedures that are only in
> >> >> version 5.something of mysql. Is this a problem? I know that
> >> >> people who have control of their own servers are ok, but what
> >> >> about hosted solutions? Do people have hosted solutions with
> >> >> mysql still at a version less than 5?
> >> >>
> >> >> Thanks
> >> >> Tim
> >> >>
> >> >> 2009/11/9 Nicholas Lee <emptysands@...>:
> >> >>> Consolidate the sql files at each update.
> >> >>>
> >> >>> So 01 .. 0.9 .sql  becomes just 09.sql.
> >> >>>
> >> >>> Clever schema might be:
> >> >>>
> >> >>> 311NNN.sql reduce to 311999.sql when 3.12 is released
> >> >>> 312NNN.sql reduce to 312999.sql when 3.13 is released
> >> >>> 313NNN.sql
> >> >>>
> >> >>> This obviously depends on the file system glob ordering to
> >> >>> work, but it is simple and seems to fix the persistent
> >> >>> problem weberp has with database upgrades.
> >> >>>
> >> >>> Plus it forces the developers to order their db updates in a
> >> >>> functional and consistent way.
> >> >>>
> >> >>> Nicholas
> >> >>>
> >> >>> On Sat, Nov 7, 2009 at 12:32 AM, Tim Schofield
> >> >>>
> >> >>> <tim.schofield1960@...> wrote:
> >> >>>> One problem with any auto updating tool is that people
> >> >>>> often use customised webERP implementations.
> >> >>>>
> >> >>>> I like Nicholas' idea but I'm not sure about a separate
> >> >>>> file for each update. This could end up with huge volume of
> >> >>>> files in a few years time. I still think my idea of
> >> >>>> checking whether updates have been done before doing it has
> >> >>>> some mileage, just my implementation of it was rubbish.
> >> >>>
> >> >>> ------------------------------------------------------------
> >> >>>--- --------------- Let Crystal Reports handle the reporting
> >> >>> - Free Crystal Reports 2008 30-Day trial. Simplify your
> >> >>> report design, integration and deployment - and focus on
> >> >>> what you do best, core application coding. Discover what's
> >> >>> new with Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> >> >>> _______________________________________________
> >> >>> Web-erp-developers mailing list
> >> >>> Web-erp-developers@...
> >> >>> https://lists.sourceforge.net/lists/listinfo/web-erp-develop
> >> >>>ers
> >> >>
> >> >> -------------------------------------------------------------
> >> >>--- -------------- Let Crystal Reports handle the reporting -
> >> >> Free Crystal Reports 2008 30-Day trial. Simplify your report
> >> >> design, integration and deployment - and focus on what you do
> >> >> best, core application coding. Discover what's new with
> >> >> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> >> >> _______________________________________________
> >> >> Web-erp-developers mailing list
> >> >> Web-erp-developers@...
> >> >> https://lists.sourceforge.net/lists/listinfo/web-erp-develope
> >> >>rs
> >> >
> >> > --------------------------------------------------------------
> >> >--- ------------- Let Crystal Reports handle the reporting -
> >> > Free Crystal Reports 2008 30-Day trial. Simplify your report
> >> > design, integration and deployment - and focus on what you do
> >> > best, core application coding. Discover what's new with
> >> > Crystal Reports now. http://p.sf.net/sfu/bobj-july
> >> > _______________________________________________
> >> > Web-erp-developers mailing list
> >> > Web-erp-developers@...
> >> > https://lists.sourceforge.net/lists/listinfo/web-erp-developer
> >> >s
> >>
> >> ----------------------------------------------------------------
> >>--- ----------- Let Crystal Reports handle the reporting - Free
> >> Crystal Reports 2008 30-Day trial. Simplify your report design,
> >> integration and deployment - and focus on what you do best, core
> >> application coding. Discover what's new with Crystal Reports
> >> now.
> >> http://p.sf.net/sfu/bobj-july
> >> _______________________________________________
> >> Web-erp-developers mailing list
> >> Web-erp-developers@...
> >> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
> >
> > -----------------------------------------------------------------
> >------------- Let Crystal Reports handle the reporting - Free
> > Crystal Reports 2008 30-Day trial. Simplify your report design,
> > integration and deployment - and focus on what you do best, core
> > application coding. Discover what's new with Crystal Reports now.
> >  http://p.sf.net/sfu/bobj-july
> > _______________________________________________
> > Web-erp-developers mailing list
> > Web-erp-developers@...
> > https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>
> -------------------------------------------------------------------
>----------- Let Crystal Reports handle the reporting - Free Crystal
> Reports 2008 30-Day trial. Simplify your report design, integration
> and deployment - and focus on what you do best, core application
> coding. Discover what's new with Crystal Reports now.
> http://p.sf.net/sfu/bobj-july
> _______________________________________________
> Web-erp-developers mailing list
> Web-erp-developers@...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
Web-erp-developers mailing list
Web-erp-developers@...
https://lists.sourceforge.net/lists/listinfo/web-erp-developers

[Tim Schofield]

Hi Lindsay,

I think we are both thinking on the same lines here. Your description
is pretty much exactly what I was looking at.

My point about different update stages was badly written as I was in a
hurry. Each upgrade script contains many separate updates. Depending
on the time that the last code was downloaded  different updates may
have been applied. As you say this info could be kept in the config
table and this was what I was looking at doing. What I then came up
against was how to achieve this in practice, and still give people the
option of running the sql directly through the cli or phpmyadmin. This
was why I was discussing stored procedures. What I came up with was
the following sql:

DELIMITER //

DROP PROCEDURE  IF EXISTS SimpleUpdate;
CREATE PROCEDURE SimpleUpdate()
  BEGIN
    DECLARE s INT(7);
        SET s=(SELECT confvalue  FROM config WHERE confname='LastDBUpdate');
        CASE s
                WHEN s is NULL THEN INSERT INTO config VALUES ('LastDBUpdate', 0);
                WHEN s<1 THEN CREATE TABLE IF NOT EXISTS `fixedassetlocations` (
`locationid` char(6) NOT NULL default '', `locationdescription`
char(20) NOT NULL default '',  PRIMARY KEY  (`locationid`) )
ENGINE=InnoDB DEFAULT CHARSET=latin1;
                                                                UPDATE config SET confvalue=1 WHERE confname='LastDBUpdate';
                WHEN s<2 THEN DROP TABLE `assetmanager`;
                                                                UPDATE config SET confvalue=2 WHERE confname='LastDBUpdate';
                WHEN s<3 THEN CREATE TABLE IF NOT EXISTS `assetmanager` (  `id`
int(11) NOT NULL auto_increment, `stockid` varchar(20) NOT NULL
default '',  `serialno` varchar(30) NOT NULL default '', `location`
varchar(15) NOT NULL default '',  `cost` double NOT NULL default '0',
`depn` double NOT NULL default '0',  `datepurchased` date NOT NULL
default '0000-00-00',  PRIMARY KEY  (`id`) ) ENGINE=InnoDB DEFAULT
CHARSET=latin1;
                                                                UPDATE config SET confvalue=3 WHERE confname='LastDBUpdate';
      ELSE
        BEGIN
        END;
        END CASE;
  END //
DELIMITER ;

CALL SimpleUpdate();


Thanks
Tim

2009/11/17 Lindsay Harris <lindsay@...>:

> Tim,
> The current schema ID should be stored in the "config" table in the
> DB.  As that table is (I'm guessing) loaded in very early in
> operations (e.g. checking HTTPS connection), there would be no issues
> with extracting the DB schema ID and comparing it to the one wired
> into webERP's code.
>
> Also implied by the above is distinguishing the DB schema ID (or
> version) from the version of webERP in use.  These two version
> numbers need not be both the same.   Among other things, this would
> allow developers to add incremental changes to the DB schema between
> releases, and the appropriate changes would be made for developers
> too.  The whole collection would be applied to the next release.  The
> only requirement would be for the DB version to be monotonically
> increasing.
>
> Tim, I'm not sure what you mean by "users could all be at different
> update stages".  Does this refer to multiple companies on the one web
> server?   If so,  I don't see it as a problem.  Each company is on a
> different database.  If they are using the same copy of webERP on the
> server,  then all the code is updated at one time.  So each company
> would be locked out of the database until their admin updates the DB.
> But this is what must happen.  But I run just one company, and it's
> quite likely I'm missing some obvious piece of reality :-(
>
> And David's idea of a "maintenance mode" option is a good one that we
> should have.  I would still like to have discrepancies between code
> version and DB version automatically block user access - that is a
> serious issue.
>
> Lindsay
>
> On Tue, 17 Nov 2009 21:02:48 Tim Schofield wrote:
>> Hi Lindsay, this is similar to what we have been looking at.
>> However users could all be at different update stages, depending on
>> what updates have already been applied. Also we need to store
>> somewhere what the latest update that has been applied is.
>>
>> Thanks
>> Tim
>>
>> 2009/11/17 Lindsay Harris <lindsay@...>:
>> > OK,  how about this.  [Note that I am in the habit of running the
>> > upgrade SQL scripts - out of habit mostly,  and perhaps because I
>> > didn't realise it could be done via the web interface.]
>> >
>> > What concerns me about upgrades is that some regular user comes
>> > along and starts using webERP when it's somewhere in the middle
>> > of being upgraded.   So, I propose the following.
>> >
>> > 1/ No more upgrade scripts as such.  And a README in the
>> > directory to explain the following method.
>> >
>> > 2/ If a regular user (i.e. NOT an administrator) logs in when the
>> > DB and code are out of step,  they are told the problem and are
>> > blocked.
>> >
>> > 3/ When the admin logs in,  they are presented with an 'upgrade
>> > needed' screen,  and one or more links to the upgrade scripts
>> > required to bring the DB up to date.   [These would be PHP
>> > scripts, with the SQL as an embedded 'heredoc']
>> >
>> > 4/ When step 3 completes successfully, the admin has the choice
>> > of unblocking regular user access.   While this COULD be
>> > automatic at the end of step 3,  a more cautious admin (e.g. me)
>> > might want to verify that at least some things work as before.
>> >  Then unblock user access.
>> >
>> > Lindsay
>> >
>> > On Tue, 17 Nov 2009 19:41:56 Tim Schofield wrote:
>> >> Hi Phil,
>> >>
>> >> Well I thought a lot on whether it should be done in sql or php.
>> >> My attempt at adding the comments in the upgrade3.10-3.11.sql
>> >> script were a method of upgrading via php, as the php upgrade
>> >> script used these comments to report on the upgrading. This fell
>> >> down because some people prefer to upgrade using the sql script
>> >> directly.
>> >>
>> >> My plan was to do as Nicholas suggested and record in the
>> >> database the latest update that had been applied. However if you
>> >> do this in the php script then the people who use the sql
>> >> directly dont get that info recorded. This could be a problem if
>> >> the next time the person upgrading decided to use the php
>> >> script.
>> >>
>> >> This was why I looked for a solution that used only sql. The
>> >> problem is that I don't see a way of doing it without using
>> >> functions/procedures.
>> >>
>> >> Thanks
>> >> Tim
>> >>
>> >> 2009/11/16 Phil Daintree <phil@...>:
>> >> > Are we ready to commit completely to mysql now Oracle owns it?
>> >> > In my view it would be nice to have a solution that is not
>> >> > RDBMS dependent - with the code in PHP rather than as a stored
>> >> > procedure.
>> >> >
>> >> > Phil
>> >> >
>> >> > Tim Schofield wrote:
>> >> >> Hi,
>> >> >>
>> >> >> I have been giving some thought to this problem. I have a
>> >> >> working solution following Nicholas' idea of numbered
>> >> >> updates. However it uses mysql procedures that are only in
>> >> >> version 5.something of mysql. Is this a problem? I know that
>> >> >> people who have control of their own servers are ok, but what
>> >> >> about hosted solutions? Do people have hosted solutions with
>> >> >> mysql still at a version less than 5?
>> >> >>
>> >> >> Thanks
>> >> >> Tim
>> >> >>
>> >> >> 2009/11/9 Nicholas Lee <emptysands@...>:
>> >> >>> Consolidate the sql files at each update.
>> >> >>>
>> >> >>> So 01 .. 0.9 .sql  becomes just 09.sql.
>> >> >>>
>> >> >>> Clever schema might be:
>> >> >>>
>> >> >>> 311NNN.sql reduce to 311999.sql when 3.12 is released
>> >> >>> 312NNN.sql reduce to 312999.sql when 3.13 is released
>> >> >>> 313NNN.sql
>> >> >>>
>> >> >>> This obviously depends on the file system glob ordering to
>> >> >>> work, but it is simple and seems to fix the persistent
>> >> >>> problem weberp has with database upgrades.
>> >> >>>
>> >> >>> Plus it forces the developers to order their db updates in a
>> >> >>> functional and consistent way.
>> >> >>>
>> >> >>> Nicholas
>> >> >>>
>> >> >>> On Sat, Nov 7, 2009 at 12:32 AM, Tim Schofield
>> >> >>>
>> >> >>> <tim.schofield1960@...> wrote:
>> >> >>>> One problem with any auto updating tool is that people
>> >> >>>> often use customised webERP implementations.
>> >> >>>>
>> >> >>>> I like Nicholas' idea but I'm not sure about a separate
>> >> >>>> file for each update. This could end up with huge volume of
>> >> >>>> files in a few years time. I still think my idea of
>> >> >>>> checking whether updates have been done before doing it has
>> >> >>>> some mileage, just my implementation of it was rubbish.
>> >> >>>
>> >> >>> ------------------------------------------------------------
>> >> >>>--- --------------- Let Crystal Reports handle the reporting
>> >> >>> - Free Crystal Reports 2008 30-Day trial. Simplify your
>> >> >>> report design, integration and deployment - and focus on
>> >> >>> what you do best, core application coding. Discover what's
>> >> >>> new with Crystal Reports now.  http://p.sf.net/sfu/bobj-july
>> >> >>> _______________________________________________
>> >> >>> Web-erp-developers mailing list
>> >> >>> Web-erp-developers@...
>> >> >>> https://lists.sourceforge.net/lists/listinfo/web-erp-develop
>> >> >>>ers
>> >> >>
>> >> >> -------------------------------------------------------------
>> >> >>--- -------------- Let Crystal Reports handle the reporting -
>> >> >> Free Crystal Reports 2008 30-Day trial. Simplify your report
>> >> >> design, integration and deployment - and focus on what you do
>> >> >> best, core application coding. Discover what's new with
>> >> >> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
>> >> >> _______________________________________________
>> >> >> Web-erp-developers mailing list
>> >> >> Web-erp-developers@...
>> >> >> https://lists.sourceforge.net/lists/listinfo/web-erp-develope
>> >> >>rs
>> >> >
>> >> > --------------------------------------------------------------
>> >> >--- ------------- Let Crystal Reports handle the reporting -
>> >> > Free Crystal Reports 2008 30-Day trial. Simplify your report
>> >> > design, integration and deployment - and focus on what you do
>> >> > best, core application coding. Discover what's new with
>> >> > Crystal Reports now. http://p.sf.net/sfu/bobj-july
>> >> > _______________________________________________
>> >> > Web-erp-developers mailing list
>> >> > Web-erp-developers@...
>> >> > https://lists.sourceforge.net/lists/listinfo/web-erp-developer
>> >> >s
>> >>
>> >> ----------------------------------------------------------------
>> >>--- ----------- Let Crystal Reports handle the reporting - Free
>> >> Crystal Reports 2008 30-Day trial. Simplify your report design,
>> >> integration and deployment - and focus on what you do best, core
>> >> application coding. Discover what's new with Crystal Reports
>> >> now.
>> >> http://p.sf.net/sfu/bobj-july
>> >> _______________________________________________
>> >> Web-erp-developers mailing list
>> >> Web-erp-developers@...
>> >> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>> >
>> > -----------------------------------------------------------------
>> >------------- Let Crystal Reports handle the reporting - Free
>> > Crystal Reports 2008 30-Day trial. Simplify your report design,
>> > integration and deployment - and focus on what you do best, core
>> > application coding. Discover what's new with Crystal Reports now.
>> >  http://p.sf.net/sfu/bobj-july
>> > _______________________________________________
>> > Web-erp-developers mailing list
>> > Web-erp-developers@...
>> > https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>>
>> -------------------------------------------------------------------
>>----------- Let Crystal Reports handle the reporting - Free Crystal
>> Reports 2008 30-Day trial. Simplify your report design, integration
>> and deployment - and focus on what you do best, core application
>> coding. Discover what's new with Crystal Reports now.
>> http://p.sf.net/sfu/bobj-july
>> _______________________________________________
>> Web-erp-developers mailing list
>> Web-erp-developers@...
>> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>
>
>
> ------------------------------------------------------------------------------
> Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
> trial. Simplify your report design, integration and deployment - and focus on
> what you do best, core application coding. Discover what's new with
> Crystal Reports now.  http://p.sf.net/sfu/bobj-july
> _______________________________________________
> Web-erp-developers mailing list
> Web-erp-developers@...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
Web-erp-developers mailing list
Web-erp-developers@...
https://lists.sourceforge.net/lists/listinfo/web-erp-developers

[Lindsay Harris]

Tim,
Stored procedures are outside my SQL knowledge and experience.  BUT,
it's pretty clear what's going on, though some of the steps
(especially the ELSE/BEGIN/END; bit at the end) are not obvious to
me.  Don't bother explaining it,  I'll take your word for it being
required (to me, the ELSE seems to not be part of anything).

My SQL book is from before stored procedures were part of the
standard,  so that was no help.  When I eventually found it :(

Seems to be a reasonable way to proceed.   But I wonder whether
putting the changes for many DB versions in one script would be
better than multiple, per version scripts?   I can see benefits and
downsides of both.

Now it's time to get back to the API side of life.

Lindsay

On Tue, 17 Nov 2009 22:19:01 Tim Schofield wrote:

> Hi Lindsay,
>
> I think we are both thinking on the same lines here. Your
> description is pretty much exactly what I was looking at.
>
> My point about different update stages was badly written as I was
> in a hurry. Each upgrade script contains many separate updates.
> Depending on the time that the last code was downloaded  different
> updates may have been applied. As you say this info could be kept
> in the config table and this was what I was looking at doing. What
> I then came up against was how to achieve this in practice, and
> still give people the option of running the sql directly through
> the cli or phpmyadmin. This was why I was discussing stored
> procedures. What I came up with was the following sql:
>
> DELIMITER //
>
> DROP PROCEDURE  IF EXISTS SimpleUpdate;
> CREATE PROCEDURE SimpleUpdate()
>   BEGIN
>     DECLARE s INT(7);
> SET s=(SELECT confvalue  FROM config WHERE
> confname='LastDBUpdate'); CASE s
> WHEN s is NULL THEN INSERT INTO config VALUES ('LastDBUpdate',
> 0); WHEN s<1 THEN CREATE TABLE IF NOT EXISTS `fixedassetlocations`
> ( `locationid` char(6) NOT NULL default '', `locationdescription`
> char(20) NOT NULL default '',  PRIMARY KEY  (`locationid`) )
> ENGINE=InnoDB DEFAULT CHARSET=latin1;
> UPDATE config SET confvalue=1 WHERE
> confname='LastDBUpdate'; WHEN s<2 THEN DROP TABLE `assetmanager`;
> UPDATE config SET confvalue=2 WHERE
> confname='LastDBUpdate'; WHEN s<3 THEN CREATE TABLE IF NOT EXISTS
> `assetmanager` (  `id` int(11) NOT NULL auto_increment, `stockid`
> varchar(20) NOT NULL default '',  `serialno` varchar(30) NOT NULL
> default '', `location` varchar(15) NOT NULL default '',  `cost`
> double NOT NULL default '0', `depn` double NOT NULL default '0',
> `datepurchased` date NOT NULL default '0000-00-00',  PRIMARY KEY
> (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
> UPDATE config SET confvalue=3 WHERE
> confname='LastDBUpdate'; ELSE
>         BEGIN
>         END;
> END CASE;
>   END //
> DELIMITER ;
>
> CALL SimpleUpdate();
>
>
> Thanks
> Tim
>
> 2009/11/17 Lindsay Harris <lindsay@...>:
> > Tim,
> > The current schema ID should be stored in the "config" table in
> > the DB.  As that table is (I'm guessing) loaded in very early in
> > operations (e.g. checking HTTPS connection), there would be no
> > issues with extracting the DB schema ID and comparing it to the
> > one wired into webERP's code.
> >
> > Also implied by the above is distinguishing the DB schema ID (or
> > version) from the version of webERP in use.  These two version
> > numbers need not be both the same.   Among other things, this
> > would allow developers to add incremental changes to the DB
> > schema between releases, and the appropriate changes would be
> > made for developers too.  The whole collection would be applied
> > to the next release.  The only requirement would be for the DB
> > version to be monotonically increasing.
> >
> > Tim, I'm not sure what you mean by "users could all be at
> > different update stages".  Does this refer to multiple companies
> > on the one web server?   If so,  I don't see it as a problem.
> >  Each company is on a different database.  If they are using the
> > same copy of webERP on the server,  then all the code is updated
> > at one time.  So each company would be locked out of the database
> > until their admin updates the DB. But this is what must happen.
> >  But I run just one company, and it's quite likely I'm missing
> > some obvious piece of reality :-(
> >
> > And David's idea of a "maintenance mode" option is a good one
> > that we should have.  I would still like to have discrepancies
> > between code version and DB version automatically block user
> > access - that is a serious issue.
> >
> > Lindsay
> >
> > On Tue, 17 Nov 2009 21:02:48 Tim Schofield wrote:
> >> Hi Lindsay, this is similar to what we have been looking at.
> >> However users could all be at different update stages, depending
> >> on what updates have already been applied. Also we need to store
> >> somewhere what the latest update that has been applied is.
> >>
> >> Thanks
> >> Tim
> >>
> >> 2009/11/17 Lindsay Harris <lindsay@...>:
> >> > OK,  how about this.  [Note that I am in the habit of running
> >> > the upgrade SQL scripts - out of habit mostly,  and perhaps
> >> > because I didn't realise it could be done via the web
> >> > interface.]
> >> >
> >> > What concerns me about upgrades is that some regular user
> >> > comes along and starts using webERP when it's somewhere in the
> >> > middle of being upgraded.   So, I propose the following.
> >> >
> >> > 1/ No more upgrade scripts as such.  And a README in the
> >> > directory to explain the following method.
> >> >
> >> > 2/ If a regular user (i.e. NOT an administrator) logs in when
> >> > the DB and code are out of step,  they are told the problem
> >> > and are blocked.
> >> >
> >> > 3/ When the admin logs in,  they are presented with an
> >> > 'upgrade needed' screen,  and one or more links to the upgrade
> >> > scripts required to bring the DB up to date.   [These would be
> >> > PHP scripts, with the SQL as an embedded 'heredoc']
> >> >
> >> > 4/ When step 3 completes successfully, the admin has the
> >> > choice of unblocking regular user access.   While this COULD
> >> > be automatic at the end of step 3,  a more cautious admin
> >> > (e.g. me) might want to verify that at least some things work
> >> > as before. Then unblock user access.
> >> >
> >> > Lindsay
> >> >
> >> > On Tue, 17 Nov 2009 19:41:56 Tim Schofield wrote:
> >> >> Hi Phil,
> >> >>
> >> >> Well I thought a lot on whether it should be done in sql or
> >> >> php. My attempt at adding the comments in the
> >> >> upgrade3.10-3.11.sql script were a method of upgrading via
> >> >> php, as the php upgrade script used these comments to report
> >> >> on the upgrading. This fell down because some people prefer
> >> >> to upgrade using the sql script directly.
> >> >>
> >> >> My plan was to do as Nicholas suggested and record in the
> >> >> database the latest update that had been applied. However if
> >> >> you do this in the php script then the people who use the sql
> >> >> directly dont get that info recorded. This could be a problem
> >> >> if the next time the person upgrading decided to use the php
> >> >> script.
> >> >>
> >> >> This was why I looked for a solution that used only sql. The
> >> >> problem is that I don't see a way of doing it without using
> >> >> functions/procedures.
> >> >>
> >> >> Thanks
> >> >> Tim
> >> >>
> >> >> 2009/11/16 Phil Daintree <phil@...>:
> >> >> > Are we ready to commit completely to mysql now Oracle owns
> >> >> > it? In my view it would be nice to have a solution that is
> >> >> > not RDBMS dependent - with the code in PHP rather than as a
> >> >> > stored procedure.
> >> >> >
> >> >> > Phil
> >> >> >
> >> >> > Tim Schofield wrote:
> >> >> >> Hi,
> >> >> >>
> >> >> >> I have been giving some thought to this problem. I have a
> >> >> >> working solution following Nicholas' idea of numbered
> >> >> >> updates. However it uses mysql procedures that are only in
> >> >> >> version 5.something of mysql. Is this a problem? I know
> >> >> >> that people who have control of their own servers are ok,
> >> >> >> but what about hosted solutions? Do people have hosted
> >> >> >> solutions with mysql still at a version less than 5?
> >> >> >>
> >> >> >> Thanks
> >> >> >> Tim
> >> >> >>
> >> >> >> 2009/11/9 Nicholas Lee <emptysands@...>:
> >> >> >>> Consolidate the sql files at each update.
> >> >> >>>
> >> >> >>> So 01 .. 0.9 .sql  becomes just 09.sql.
> >> >> >>>
> >> >> >>> Clever schema might be:
> >> >> >>>
> >> >> >>> 311NNN.sql reduce to 311999.sql when 3.12 is released
> >> >> >>> 312NNN.sql reduce to 312999.sql when 3.13 is released
> >> >> >>> 313NNN.sql
> >> >> >>>
> >> >> >>> This obviously depends on the file system glob ordering
> >> >> >>> to work, but it is simple and seems to fix the persistent
> >> >> >>> problem weberp has with database upgrades.
> >> >> >>>
> >> >> >>> Plus it forces the developers to order their db updates
> >> >> >>> in a functional and consistent way.
> >> >> >>>
> >> >> >>> Nicholas
> >> >> >>>
> >> >> >>> On Sat, Nov 7, 2009 at 12:32 AM, Tim Schofield
> >> >> >>>
> >> >> >>> <tim.schofield1960@...> wrote:
> >> >> >>>> One problem with any auto updating tool is that people
> >> >> >>>> often use customised webERP implementations.
> >> >> >>>>
> >> >> >>>> I like Nicholas' idea but I'm not sure about a separate
> >> >> >>>> file for each update. This could end up with huge volume
> >> >> >>>> of files in a few years time. I still think my idea of
> >> >> >>>> checking whether updates have been done before doing it
> >> >> >>>> has some mileage, just my implementation of it was
> >> >> >>>> rubbish.
> >> >> >>>
> >> >> >>> ---------------------------------------------------------
> >> >> >>>--- --- --------------- Let Crystal Reports handle the
> >> >> >>> reporting - Free Crystal Reports 2008 30-Day trial.
> >> >> >>> Simplify your report design, integration and deployment -
> >> >> >>> and focus on what you do best, core application coding.
> >> >> >>> Discover what's new with Crystal Reports now.
> >> >> >>>  http://p.sf.net/sfu/bobj-july
> >> >> >>> _______________________________________________
> >> >> >>> Web-erp-developers mailing list
> >> >> >>> Web-erp-developers@...
> >> >> >>> https://lists.sourceforge.net/lists/listinfo/web-erp-deve
> >> >> >>>lop ers
> >> >> >>
> >> >> >> ----------------------------------------------------------
> >> >> >>--- --- -------------- Let Crystal Reports handle the
> >> >> >> reporting - Free Crystal Reports 2008 30-Day trial.
> >> >> >> Simplify your report design, integration and deployment -
> >> >> >> and focus on what you do best, core application coding.
> >> >> >> Discover what's new with Crystal Reports now.
> >> >> >>  http://p.sf.net/sfu/bobj-july
> >> >> >> _______________________________________________
> >> >> >> Web-erp-developers mailing list
> >> >> >> Web-erp-developers@...
> >> >> >> https://lists.sourceforge.net/lists/listinfo/web-erp-devel
> >> >> >>ope rs
> >> >> >
> >> >> > -----------------------------------------------------------
> >> >> >--- --- ------------- Let Crystal Reports handle the
> >> >> > reporting - Free Crystal Reports 2008 30-Day trial.
> >> >> > Simplify your report design, integration and deployment -
> >> >> > and focus on what you do best, core application coding.
> >> >> > Discover what's new with Crystal Reports now.
> >> >> > http://p.sf.net/sfu/bobj-july
> >> >> > _______________________________________________
> >> >> > Web-erp-developers mailing list
> >> >> > Web-erp-developers@...
> >> >> > https://lists.sourceforge.net/lists/listinfo/web-erp-develo
> >> >> >per s
> >> >>
> >> >> -------------------------------------------------------------
> >> >>--- --- ----------- Let Crystal Reports handle the reporting -
> >> >> Free Crystal Reports 2008 30-Day trial. Simplify your report
> >> >> design, integration and deployment - and focus on what you do
> >> >> best, core application coding. Discover what's new with
> >> >> Crystal Reports now.
> >> >> http://p.sf.net/sfu/bobj-july
> >> >> _______________________________________________
> >> >> Web-erp-developers mailing list
> >> >> Web-erp-developers@...
> >> >> https://lists.sourceforge.net/lists/listinfo/web-erp-develope
> >> >>rs
> >> >
> >> > --------------------------------------------------------------
> >> >--- ------------- Let Crystal Reports handle the reporting -
> >> > Free Crystal Reports 2008 30-Day trial. Simplify your report
> >> > design, integration and deployment - and focus on what you do
> >> > best, core application coding. Discover what's new with
> >> > Crystal Reports now. http://p.sf.net/sfu/bobj-july
> >> > _______________________________________________
> >> > Web-erp-developers mailing list
> >> > Web-erp-developers@...
> >> > https://lists.sourceforge.net/lists/listinfo/web-erp-developer
> >> >s
> >>
> >> ----------------------------------------------------------------
> >>--- ----------- Let Crystal Reports handle the reporting - Free
> >> Crystal Reports 2008 30-Day trial. Simplify your report design,
> >> integration and deployment - and focus on what you do best, core
> >> application coding. Discover what's new with Crystal Reports
> >> now.
> >> http://p.sf.net/sfu/bobj-july
> >> _______________________________________________
> >> Web-erp-developers mailing list
> >> Web-erp-developers@...
> >> https://lists.sourceforge.net/lists/listinfo/web-erp-developers
> >
> > -----------------------------------------------------------------
> >------------- Let Crystal Reports handle the reporting - Free
> > Crystal Reports 2008 30-Day trial. Simplify your report design,
> > integration and deployment - and focus on what you do best, core
> > application coding. Discover what's new with Crystal Reports now.
> >  http://p.sf.net/sfu/bobj-july
> > _______________________________________________
> > Web-erp-developers mailing list
> > Web-erp-developers@...
> > https://lists.sourceforge.net/lists/listinfo/web-erp-developers
>
> -------------------------------------------------------------------
>----------- Let Crystal Reports handle the reporting - Free Crystal
> Reports 2008 30-Day trial. Simplify your report design, integration
> and deployment - and focus on what you do best, core application
> coding. Discover what's new with Crystal Reports now.
> http://p.sf.net/sfu/bobj-july
> _______________________________________________
> Web-erp-developers mailing list
> Web-erp-developers@...
> https://lists.sourceforge.net/lists/listinfo/web-erp-developers

------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day
trial. Simplify your report design, integration and deployment - and focus on
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july
_______________________________________________
Web-erp-developers mailing list
Web-erp-developers@...
https://lists.sourceforge.net/lists/listinfo/web-erp-developers