[TIP] Easy (semi) automated way of updating your sequences

Share business templates, ideas, experiences, etc with fellow Servoy developers here

[TIP] Easy (semi) automated way of updating your sequences

Postby ROCLASI » Sat Jun 18, 2011 9:43 pm

I posted a tip on ServoyCamp about how to easily update all your database sequences in your PostgreSQL database in a (semi) automated fashion :).

http://www.servoycamp.com/topics/tips/1 ... ences.html

Enjoy!
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: [TIP] Easy (semi) automated way of updating your sequenc

Postby Adelo Herrero » Sun Jun 19, 2011 9:42 am

Hi:

We can automate it with an stored procedure in plpgsql (view code) we call :
pgadmin/psql
Code: Select all
select update_seq();


Servoy
Code: Select all
databaseManager.getDataSetByQuery("serverName","select update_seq()");


Code of function.
Code: Select all
-- Function: update_seq()

-- DROP FUNCTION update_seq();

CREATE OR REPLACE FUNCTION update_seq()
  RETURNS void AS
$BODY$-- Update sequences in database.
DECLARE
   sql_result information_schema.tables%rowtype;
   query text;
BEGIN
   query:="SELECT 'SELECT setval('''||_schema||'.'||_seq||''', (SELECT MAX('||_column||') FROM '||_schema||'.'||_table||')+1); ' AS updatestring
      FROM (SELECT n.nspname AS _schema,
          c.relname AS _table,
          a.attname AS _column,
          SUBSTRING(d.adsrc FROM E'nextval\\(''([^'')]+)''') AS _seq
      FROM pg_attrdef       AS d
          JOIN pg_attribute AS a ON a.attrelid = d.adrelid AND a.attnum = d.adnum
          JOIN pg_class     AS c ON c.oid = d.adrelid
          JOIN pg_namespace AS n ON n.oid = c.relnamespace
      WHERE adsrc LIKE 'nextval(''%') t;";

    FOR sql_result in EXECUTE(query) LOOP
      EXECUTE(sql_result);   
    END LOOP;
      
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION update_seq() OWNER TO postgres;
COMMENT ON FUNCTION update_seq() IS 'Update sequences in database.';
Adelo Herrero
Adelo Herrero
 
Posts: 103
Joined: Sat Jul 25, 2009 9:24 pm
Location: Requena (Valencia) España

[TIP] Easy automated way of updating your sequences

Postby ROCLASI » Sun Jun 19, 2011 1:17 pm

Very nice! :D
Just load this into the template1 database and it will be available in every database you create after that.

But this doesn't take into account the very reason why I made it a semi-automatic solution. Namely the same sequence can be used on multiple columns.
I guess 99% of the Servoy users probably won't use a sequence for more than 1 column but for that 1% the result won't be satisfactory.
When would you reuse a sequence object? For example in some accounting solution you might want to have a unique serial number covering multiple tables so you reuse a sequence object for this.
Anyway , this is something Michael Fuhr (the guy I reference in the ServoyCamp post) also pointed out.
To solve this you would have to check if a sequence name is returned more than once and act accordingly like so:

Code: Select all
SELECT COALESCE(MAX(id), 0)+1 AS maxall FROM (
  SELECT MAX(tableOneColumn) AS id FROM tableOne
  UNION
  SELECT MAX(tableTwoColumn) AS id FROM tableTwo
) AS s;

So your actual update SQL will be like this:

Code: Select all
SELECT setval('sequencename',SELECT COALESCE(MAX(id), 0)+1 AS maxall FROM (SELECT MAX(tableOneColumn) AS id FROM tableOne UNION SELECT MAX(tableTwoColumn) AS id FROM tableTwo) AS s);


So I guess you can script this with a shell script, PL/pgSQL or even in a Servoy method like so:

Code: Select all
var _sServer = "myConnectionName",
    _sQuery = "SELECT n.nspname as _schema, \
                    c.relname as _table, \
                    a.attname as _column, \
                    SUBSTRING(d.adsrc FROM E'nextval\\\\(''([^'')]+)''') AS _seq \
                FROM pg_attrdef   AS d \
                    JOIN pg_attribute AS a ON a.attrelid = d.adrelid AND a.attnum = d.adnum \
                    JOIN pg_class     AS c ON c.oid = d.adrelid \
                    JOIN pg_namespace AS n ON n.oid = c.relnamespace \
                WHERE adsrc LIKE 'nextval(''%' \
                ORDER BY _seq",
    _ds = databaseManager.getDataSetByQuery(_sServer, _sQuery, null, -1),
    _aGetMax = new Array(),
    _sUpdateSQL,
    _oException;
if (_ds.getException()) {
    application.output("Error:" + _ds.getException().getMessage(), LOGGINGLEVEL.ERROR);
} else {
    for (var i = 1; i <= _ds.getMaxRowIndex(); i++) {
        _aGetMax.push("SELECT MAX(" + _ds.getValue(i, 3) + ") AS maxid FROM " + _ds.getValue(i, 1) + "." + _ds.getValue(i, 2));
        if (i == _ds.getMaxRowIndex() || _ds.getValue(i + 1, 4) != _ds.getValue(i, 4)) {
            _sUpdateSQL = "SELECT setval('" + _ds.getValue(i, 4) + "', (SELECT COALESCE(MAX(maxid), 0)+1 AS maxall FROM (" + _aGetMax.join(" UNION ") + ") AS t ));";
            _oException = databaseManager.getDataSetByQuery(_sServer, _sUpdateSQL, null, 1).getException();
            if (_oException) {
                application.output("Error:" + _oException.getMessage(), LOGGINGLEVEL.ERROR);
                application.output(_sUpdateSQL, LOGGINGLEVEL.ERROR);
            }
            _aGetMax = new Array();
        }
    }
}


UPDATE: I removed the use of the rawSQL plugin in the method. Since we use a SELECT this can be done with a regular getDataSetByQuery.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: [TIP] Easy (semi) automated way of updating your sequenc

Postby Adelo Herrero » Sun Jun 19, 2011 6:06 pm

Hi:
Not really thought about it, I always use unique secuence per column, so it is good to learn from the best ;-)

Best regards.
Adelo Herrero
Adelo Herrero
 
Posts: 103
Joined: Sat Jul 25, 2009 9:24 pm
Location: Requena (Valencia) España

Re: [TIP] Easy (semi) automated way of updating your sequenc

Postby Karel Broer » Mon Jun 20, 2011 11:09 am

Adelo Herrero wrote:We can automate it with an stored procedure in plpgsql

Nice job Adelo! Thanks for your tip!
Karel Broer
ServoyCamp - http://www.servoycamp.com
User avatar
Karel Broer
 
Posts: 779
Joined: Mon May 03, 2004 12:49 am
Location: Doetinchem


Return to Sharing Central

Who is online

Users browsing this forum: No registered users and 4 guests