Re: [UDD] How to properly maintain code in sql dir
On 17/05/13 at 09:19 +0200, Andreas Tille wrote:
> Hi,
>
> because I have created some confusion in the past by simply adding *.sql
> scripts in the dir sql/ in Git I would like to ask here again about the
> proper way to handle this. Currently we have
>
> setup.sql and
> upgrade.sql
>
> and it does not become clear to me in how far these are different.
> Guessing from the name you could think setup.sql is for some initial
> setup and upgrade.sql should be edited and rerun in case of some
> changes. However, the fact that some "INSERT INTO" statements made it
> into this script will conflict with the approach to rerun upgrade.sql.
> So what is the proper way to update some values in UDD.
>
> Currently I want to inject two things. One would be something like
>
> $ git diff
> diff --git a/sql/upgrade.sql b/sql/upgrade.sql
> index 271cf35..9bf596a 100644
> --- a/sql/upgrade.sql
> +++ b/sql/upgrade.sql
> @@ -87,9 +87,15 @@ INSERT INTO releases VALUES ( 'squeeze-proposed-updates', NULL, 602 );
> INSERT INTO releases VALUES ( 'wheezy', NULL, 700 );
> INSERT INTO releases VALUES ( 'wheezy-security', NULL, 701 );
> INSERT INTO releases VALUES ( 'wheezy-proposed-updates', NULL, 702 );
> +INSERT INTO releases VALUES ( 'jessie', NULL, 800 );
> +INSERT INTO releases VALUES ( 'jessie-security', NULL, 801 );
> +INSERT INTO releases VALUES ( 'jessie-proposed-updates', NULL, 802 );
> INSERT INTO releases VALUES ( 'sid', NULL, 100000 );
> INSERT INTO releases VALUES ( 'experimental', NULL, 0 ); /* this pseudo releases does not fit any order and it is not higher than unstable */
>
> +UPDATE releases SET releasedate = '2011-02-06' WHERE release like 'squeeze%' ;
> +UPDATE releases SET releasedate = '2013-05-05' WHERE release like 'wheezy%' ;
> +
> GRANT SELECT ON releases TO PUBLIC ;
>
>
> but as I said you can not simply rerun update.sql - so this change is
> useless in this script. While I could perfectly run the statements
> manually without doing some harm I would like to aks here to fully
> understand the concept. The reason why I would like to understand is
> the second change I would like to do to provide the data to fix bug
> #703402 in three additional tables. The code was developed in the
> separate script sql/blends.sql (I used this file as sandbox for testing)
> and is ready for adoption into UDD now. The question is: In what
> file should I put this additional sql code.
>
> Besides that I did not really understood the concept behind setup.sql
> and upgrade.sql I wonder whether we should start using some sql.d/
> like mechanism were you can drop sql code like
>
> sql.d/01_create_new_table.sql
> sql.d/02_create_some_function.sql
> sql.d/03_another_new_table.sql
> sql.d/04_update_some_data.sql
> etc.
>
> IMHO this would make development of new stuff more transparent.
What would be the support tool that would understand this structure? How
does it know which files were already executed?
The current way is:
setup.sql contains the code to generate a pristine UDD db from scratch.
upgrade.sql lists the command needed to upgrade a running UDD db. You
can't run it, but you can copy-paste from it.
Under the current scheme, there should be no other files, so all your
SQL code should go into setup.sql and upgrade.sql.
Lucas
Reply to: