Grails › Grails - user

Deleting rows

Classic

List

Threaded

8 messages Options

Quantumuniverses

Deleting rows

Is there a quick way to delete all the rows of all the tables?

I though to use the SQL command "truncate" but in that case I'd need a list
of all my 200 tables and I don't know how to get them automatically in
Grails..

Any suggest?

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

houbie

Re: Deleting rows

You could try something like this:

grailsApplication.getArtefacts("Domain")*.clazz.each{it.executeUpdate("delete ${it.simpleName}")}

houbie

On 15 Jun 2012, at 09:37, QuantumUniverses wrote:

> Is there a quick way to delete all the rows of all the tables?
>
> I though to use the SQL command "truncate" but in that case I'd need a list of all my 200 tables and I don't know how to get them automatically in Grails..
>
> Any suggest?
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe from this list, please visit:
>
> http://xircles.codehaus.org/manage_email
>
>

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

ideasculptor

Re: Deleting rows

In reply to this post by Quantumuniverses

On Fri, Jun 15, 2012 at 12:37 AM, QuantumUniverses <[hidden email]> wrote:

Is there a quick way to delete all the rows of all the tables?

I though to use the SQL command "truncate" but in that case I'd need a list of all my 200 tables and I don't know how to get them automatically in Grails..

Your database likely has catalog 'tables' you can query which would allow you to discover the names of all non-system tables in the database. Its actually part of the sql standard, though there's lots of room in the specification for db-specific implementation details, I believe. Google up your database name combined with the words "information schema" or "catalog" and you'll likely find the info you are looking for. Then just run the query needed to return the name of all tables and iterate over them, truncating them. Or write a stored proc to do that and then just call it whenever you need it.

But one has to ask why you need this functionality. Odds are very good that if you really need to do this often enough to automate it, you are probably better off putting your energy into automatically creating a fresh database from scratch, including whatever bootstrap data is needed - whether via the database migration plugin or some other mechanism. Then you can simply drop the database, re-create it empty, and launch your app with the db bootstrapping code set to execute. It may be more work to bootstrap a database from scratch, but it is likely to be more useful for you in the long run, as it makes setting up new test and developer systems very easy, and if you actually do it frequently, ensures that your scripts for creating a database from scratch actually create a database that is compatible with your latest codebase - something that is all too easy to get out of sync if you aren't careful. Most of us maintain scripts for migrating from one release to another, but many systems for doing that don't deal well with making larger jumps than one release. The dbm plugin does, if used correctly, but even that can be broken if you aren't careful. But if you setup dbm changesets which will bring a db from completely empty to up-to-date, then you can launch your app against a completely barren database whenever you like.

--sam

Any suggest?

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

Quantumuniverses

Re: Deleting rows

Let me explain what I need..

I’m developing integration tests and some test methods need to start from an empty DB. The way now I do this is everytime launching my app with dataSource.dbCreate=create in config.properties file. But it’s so tedious and I’d like to do it from my code, inserting it into the beginning of the test methods need empty DB.

So, is there a way to “command” the dataSource.dbCreate=create from the code?

Otherwise I have to follow other ways, as to delete all the rows and then re-create a minimal set of default data (by the way, can I can something like new BootStrap.init() from my code?)

"Samuel Gendler" <[hidden email]> ha scritto nel messaggio news:CAEV0TzAxrRmqR+R2RO=[hidden email]...

On Fri, Jun 15, 2012 at 12:37 AM, QuantumUniverses <[hidden email]> wrote:

Is there a quick way to delete all the rows of all the tables?

I though to use the SQL command "truncate" but in that case I'd need a list of all my 200 tables and I don't know how to get them automatically in Grails..

--sam

Any suggest?

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

alxndrsn

Re: Deleting rows

We drop data before each functional test like so:

def sql = Sql.newInstance('jdbc:h2:mem:testDb', 'sa',
'', 'org.h2.Driver')
sql.execute "SET REFERENTIAL_INTEGRITY FALSE"
sql.eachRow("SHOW TABLES") { table ->
sql.execute('DELETE FROM ' + table.TABLE_NAME) }
sql.execute "SET REFERENTIAL_INTEGRITY TRUE"

On 15 June 2012 13:35, QuantumUniverses <[hidden email]> wrote:

> Let me explain what I need..
>
> I’m developing integration tests and some test methods need to start from an
> empty DB. The way now I do this is everytime launching my app with
> dataSource.dbCreate=create in config.properties file. But it’s so tedious
> and I’d like to do it from my code, inserting it into the beginning of the
> test methods need empty DB.
>
> So, is there a way to “command” the dataSource.dbCreate=create from the
> code?
>
> Otherwise I have to follow other ways, as to delete all the rows and then
> re-create a minimal set of default data (by the way, can I can something
> like new BootStrap.init() from my code?)
>
>
>
> "Samuel Gendler" <[hidden email]> ha scritto nel messaggio
> news:CAEV0TzAxrRmqR+R2RO=[hidden email]...
>
>
> On Fri, Jun 15, 2012 at 12:37 AM, QuantumUniverses
> <[hidden email]> wrote:
>>
>> Is there a quick way to delete all the rows of all the tables?
>>
>> I though to use the SQL command "truncate" but in that case I'd need a
>> list of all my 200 tables and I don't know how to get them automatically in
>> Grails..
>>
>
> Your database likely has catalog 'tables' you can query which would allow
> you to discover the names of all non-system tables in the database. Its
> actually part of the sql standard, though there's lots of room in the
> specification for db-specific implementation details, I believe. Google up
> your database name combined with the words "information schema" or "catalog"
> and you'll likely find the info you are looking for. Then just run the
> query needed to return the name of all tables and iterate over them,
> truncating them. Or write a stored proc to do that and then just call it
> whenever you need it.
>
> But one has to ask why you need this functionality. Odds are very good that
> if you really need to do this often enough to automate it, you are probably
> better off putting your energy into automatically creating a fresh database
> from scratch, including whatever bootstrap data is needed - whether via the
> database migration plugin or some other mechanism. Then you can simply drop
> the database, re-create it empty, and launch your app with the db
> bootstrapping code set to execute. It may be more work to bootstrap a
> database from scratch, but it is likely to be more useful for you in the
> long run, as it makes setting up new test and developer systems very easy,
> and if you actually do it frequently, ensures that your scripts for creating
> a database from scratch actually create a database that is compatible with
> your latest codebase - something that is all too easy to get out of sync if
> you aren't careful. Most of us maintain scripts for migrating from one
> release to another, but many systems for doing that don't deal well with
> making larger jumps than one release. The dbm plugin does, if used
> correctly, but even that can be broken if you aren't careful. But if you
> setup dbm changesets which will bring a db from completely empty to
> up-to-date, then you can launch your app against a completely barren
> database whenever you like.
>
> --sam
>
>
>
>>
>> Any suggest?
>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe from this list, please visit:
>>
>> http://xircles.codehaus.org/manage_email
>>
>>
>

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

Quantumuniverses

Re: Deleting rows

.. changed into:

def sql = Sql.newInstance('jdbc:oracle:thin:@localhost:1521:XE',
'myproject', 'myproject00', 'oracle.jdbc.driver.OracleDriver')
sql.execute "SET REFERENTIAL_INTEGRITY FALSE"
sql.eachRow("SHOW TABLES") { table -> sql.execute('DELETE FROM ' +
table.TABLE_NAME) }
sql.execute "SET REFERENTIAL_INTEGRITY TRUE"

it fails on the 2nd row because of:

ORA-00922: opzione mancante o non valida

java.sql.SQLSyntaxErrorException: ORA-00922: ... (missing option or invalid
option)

at
oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
at
oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:942)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
at
oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1770)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1739)
at
oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:299)
...

I'm afraid Oracle doesn't support that SQL command.. :(

"Alex Anderson" ha scritto nel messaggio
news:[hidden email]...

We drop data before each functional test like so:

def sql = Sql.newInstance('jdbc:h2:mem:testDb', 'sa',
'', 'org.h2.Driver')
sql.execute "SET REFERENTIAL_INTEGRITY FALSE"
sql.eachRow("SHOW TABLES") { table ->
sql.execute('DELETE FROM ' + table.TABLE_NAME) }
sql.execute "SET REFERENTIAL_INTEGRITY TRUE"

On 15 June 2012 13:35, QuantumUniverses <[hidden email]> wrote:

> Let me explain what I need..
>
> I’m developing integration tests and some test methods need to start from
> an
> empty DB. The way now I do this is everytime launching my app with
> dataSource.dbCreate=create in config.properties file. But it’s so tedious
> and I’d like to do it from my code, inserting it into the beginning of the
> test methods need empty DB.
>
> So, is there a way to “command” the dataSource.dbCreate=create from the
> code?
>
> Otherwise I have to follow other ways, as to delete all the rows and then
> re-create a minimal set of default data (by the way, can I can something
> like new BootStrap.init() from my code?)
>
>
>
> "Samuel Gendler" <[hidden email]> ha scritto nel messaggio
> news:CAEV0TzAxrRmqR+R2RO=[hidden email]...
>
>
> On Fri, Jun 15, 2012 at 12:37 AM, QuantumUniverses
> <[hidden email]> wrote:
>>
>> Is there a quick way to delete all the rows of all the tables?
>>
>> I though to use the SQL command "truncate" but in that case I'd need a
>> list of all my 200 tables and I don't know how to get them automatically
>> in
>> Grails..
>>
>
> Your database likely has catalog 'tables' you can query which would allow
> you to discover the names of all non-system tables in the database. Its
> actually part of the sql standard, though there's lots of room in the
> specification for db-specific implementation details, I believe. Google
> up
> your database name combined with the words "information schema" or
> "catalog"
> and you'll likely find the info you are looking for. Then just run the
> query needed to return the name of all tables and iterate over them,
> truncating them. Or write a stored proc to do that and then just call it
> whenever you need it.
>
> But one has to ask why you need this functionality. Odds are very good
> that
> if you really need to do this often enough to automate it, you are
> probably
> better off putting your energy into automatically creating a fresh
> database
> from scratch, including whatever bootstrap data is needed - whether via
> the
> database migration plugin or some other mechanism. Then you can simply
> drop
> the database, re-create it empty, and launch your app with the db
> bootstrapping code set to execute. It may be more work to bootstrap a
> database from scratch, but it is likely to be more useful for you in the
> long run, as it makes setting up new test and developer systems very easy,
> and if you actually do it frequently, ensures that your scripts for
> creating
> a database from scratch actually create a database that is compatible with
> your latest codebase - something that is all too easy to get out of sync
> if
> you aren't careful. Most of us maintain scripts for migrating from one
> release to another, but many systems for doing that don't deal well with
> making larger jumps than one release. The dbm plugin does, if used
> correctly, but even that can be broken if you aren't careful. But if you
> setup dbm changesets which will bring a db from completely empty to
> up-to-date, then you can launch your app against a completely barren
> database whenever you like.
>
> --sam
>
>
>
>>
>> Any suggest?
>>
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe from this list, please visit:
>>
>> http://xircles.codehaus.org/manage_email
>>
>>
>

Quantumuniverses

Re: Deleting rows

Commenting the 2nd row an error of the 3rd row appears:

java.sql.SQLSyntaxErrorException: ORA-00900: ... (SQL command invalid)

at
oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
at
oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:942)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
at
oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1377)
at
oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:387)

"QuantumUniverses" ha scritto nel messaggio
news:jrfd77$361$[hidden email]...

.. changed into:

def sql = Sql.newInstance('jdbc:oracle:thin:@localhost:1521:XE',
'myproject', 'myproject00', 'oracle.jdbc.driver.OracleDriver')
sql.execute "SET REFERENTIAL_INTEGRITY FALSE"
sql.eachRow("SHOW TABLES") { table -> sql.execute('DELETE FROM ' +
table.TABLE_NAME) }
sql.execute "SET REFERENTIAL_INTEGRITY TRUE"

it fails on the 2nd row because of:

ORA-00922: opzione mancante o non valida

java.sql.SQLSyntaxErrorException: ORA-00922: ... (missing option or invalid
option)

at
oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
at
oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:942)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
at
oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1770)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1739)
at
oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:299)
...

I'm afraid Oracle doesn't support that SQL command.. :(

"Alex Anderson" ha scritto nel messaggio
news:[hidden email]...

We drop data before each functional test like so:

def sql = Sql.newInstance('jdbc:h2:mem:testDb', 'sa',
'', 'org.h2.Driver')
sql.execute "SET REFERENTIAL_INTEGRITY FALSE"
sql.eachRow("SHOW TABLES") { table ->
sql.execute('DELETE FROM ' + table.TABLE_NAME) }
sql.execute "SET REFERENTIAL_INTEGRITY TRUE"

On 15 June 2012 13:35, QuantumUniverses <[hidden email]> wrote:

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

alxndrsn

Re: Deleting rows

I'm not familiar with Oracle, but the purpose of line 2 is to enable
deleting of rows with foreign key constraints. Perhaps Oracle has an
alternative command. Line 4 re-enables constraint enforcement so
again you'll need an Oracle equivalent.

On 15 June 2012 16:29, QuantumUniverses <[hidden email]> wrote:

> Commenting the 2nd row an error of the 3rd row appears:
>
> java.sql.SQLSyntaxErrorException: ORA-00900: ... (SQL command invalid)
>
>
> at
> oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
> at
> oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
> at
> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
> at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
> at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183)
> at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:942)
> at
> oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
> at
> oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1377)
> at
> oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:387)
>
>
> "QuantumUniverses" ha scritto nel messaggio
> news:jrfd77$361$[hidden email]...
>
>
> .. changed into:
>
> def sql = Sql.newInstance('jdbc:oracle:thin:@localhost:1521:XE',
> 'myproject', 'myproject00', 'oracle.jdbc.driver.OracleDriver')
> sql.execute "SET REFERENTIAL_INTEGRITY FALSE"
> sql.eachRow("SHOW TABLES") { table -> sql.execute('DELETE FROM ' +
> table.TABLE_NAME) }
> sql.execute "SET REFERENTIAL_INTEGRITY TRUE"
>
> it fails on the 2nd row because of:
>
> ORA-00922: opzione mancante o non valida
>
> java.sql.SQLSyntaxErrorException: ORA-00922: ... (missing option or invalid
> option)
>
> at
> oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
> at
> oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
> at
> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
> at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
> at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183)
> at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:942)
> at
> oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
> at
> oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1770)
> at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1739)
> at
> oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:299)
> ...
>
> I'm afraid Oracle doesn't support that SQL command.. :(
>
>
>
> "Alex Anderson" ha scritto nel messaggio
> news:[hidden email]...
>
> We drop data before each functional test like so:
>
> def sql = Sql.newInstance('jdbc:h2:mem:testDb', 'sa',
> '', 'org.h2.Driver')
> sql.execute "SET REFERENTIAL_INTEGRITY FALSE"
> sql.eachRow("SHOW TABLES") { table ->
> sql.execute('DELETE FROM ' + table.TABLE_NAME) }
> sql.execute "SET REFERENTIAL_INTEGRITY TRUE"
>
>
> On 15 June 2012 13:35, QuantumUniverses <[hidden email]> wrote:
>>
>> Let me explain what I need..
>>
>> I’m developing integration tests and some test methods need to start from
>> an
>> empty DB. The way now I do this is everytime launching my app with
>> dataSource.dbCreate=create in config.properties file. But it’s so tedious
>> and I’d like to do it from my code, inserting it into the beginning of the
>> test methods need empty DB.
>>
>> So, is there a way to “command” the dataSource.dbCreate=create from the
>> code?
>>
>> Otherwise I have to follow other ways, as to delete all the rows and then
>> re-create a minimal set of default data (by the way, can I can something
>> like new BootStrap.init() from my code?)
>>
>>
>>
>> "Samuel Gendler" <[hidden email]> ha scritto nel messaggio
>> news:CAEV0TzAxrRmqR+R2RO=[hidden email]...
>>
>>
>> On Fri, Jun 15, 2012 at 12:37 AM, QuantumUniverses
>> <[hidden email]> wrote:
>>>
>>>
>>> Is there a quick way to delete all the rows of all the tables?
>>>
>>> I though to use the SQL command "truncate" but in that case I'd need a
>>> list of all my 200 tables and I don't know how to get them automatically
>>> in
>>> Grails..
>>>
>>
>> Your database likely has catalog 'tables' you can query which would allow
>> you to discover the names of all non-system tables in the database. Its
>> actually part of the sql standard, though there's lots of room in the
>> specification for db-specific implementation details, I believe. Google
>> up
>> your database name combined with the words "information schema" or
>> "catalog"
>> and you'll likely find the info you are looking for. Then just run the
>> query needed to return the name of all tables and iterate over them,
>> truncating them. Or write a stored proc to do that and then just call it
>> whenever you need it.
>>
>> But one has to ask why you need this functionality. Odds are very good
>> that
>> if you really need to do this often enough to automate it, you are
>> probably
>> better off putting your energy into automatically creating a fresh
>> database
>> from scratch, including whatever bootstrap data is needed - whether via
>> the
>> database migration plugin or some other mechanism. Then you can simply
>> drop
>> the database, re-create it empty, and launch your app with the db
>> bootstrapping code set to execute. It may be more work to bootstrap a
>> database from scratch, but it is likely to be more useful for you in the
>> long run, as it makes setting up new test and developer systems very easy,
>> and if you actually do it frequently, ensures that your scripts for
>> creating
>> a database from scratch actually create a database that is compatible with
>> your latest codebase - something that is all too easy to get out of sync
>> if
>> you aren't careful. Most of us maintain scripts for migrating from one
>> release to another, but many systems for doing that don't deal well with
>> making larger jumps than one release. The dbm plugin does, if used
>> correctly, but even that can be broken if you aren't careful. But if you
>> setup dbm changesets which will bring a db from completely empty to
>> up-to-date, then you can launch your app against a completely barren
>> database whenever you like.
>>
>> --sam
>>
>>
>>
>>>
>>> Any suggest?
>>>
>>>
>>>
>>> ---------------------------------------------------------------------
>>> To unsubscribe from this list, please visit:
>>>
>>> http://xircles.codehaus.org/manage_email
>>>
>>>
>>
>
> ---------------------------------------------------------------------
> To unsubscribe from this list, please visit:
>
> http://xircles.codehaus.org/manage_email
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe from this list, please visit:
>
> http://xircles.codehaus.org/manage_email
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe from this list, please visit:
>
> http://xircles.codehaus.org/manage_email
>
>

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email