Meta issue for `clean` method · Issue #5 · c410-f3r/oapth · GitHub

Description

This issue gathers all remaining missing aspects of clean and also serves as a general tutorial.

The clean method tries to clean all objects of a database, including user created schemas, but is very tricky to implement for all databases. To make life easier, the current implementation of flyway is going to be used as a development reference (hope the flyway guys don't mind).

1. Choose a database and a missing target

For example, let's pick PostgreSQL to see what flyway does and what oapth currently can do:

https://github.com/flyway/flyway/blob/e2ffad5790f484b434a761f23e431ba8e3e232f8/flyway-core/src/main/java/org/flywaydb/core/internal/database/postgresql/PostgreSQLSchema.java#L81

By looking into both projects, it is possible to see that oapth doesn't remove sequences, so let's implement it.

2. Implementation

Let's create the function that lists all sequences (don't forget to link and credit the original flyway source):

// https://github.com/flyway/flyway/blob/master/flyway-core/src/main/java/org/flywaydb/core/internal/database/postgresql/PostgreSQLSchema.java
#[oapth_macros::dev_tools_]
#[inline]
pub async fn sequences<B>(back_end: &mut B, schema: &str) -> crate::Result<Vec<String>>
where
  B: crate::BackEnd
{
  let mut buffer = ArrayString::<[u8; 128]>::new();
  buffer.write_fmt(format_args!(
    "SELECT sequence_name AS generic_column FROM information_schema.sequences WHERE sequence_schema = '{schema}'",
    schema = schema
  ))?;
  Ok(back_end.query_string(&buffer).await?)
}

And insert the drop statement within the clean function:

for sequence in sequences(back_end, "public").await? {
  buffer.write_fmt(format_args!("DROP SEQUENCE {};", domain))?;
}

3. Testing

Since this is a specific behavior of PostgreSQL, we are going to include some code into the already existing clean_drops_all_objs test:

...

c.back_end.execute("CREATE SEQUENCE serial START 101;").await.unwrap();

...

assert_eq!(crate::fixed_sql_commands::pg::sequences(&mut c.back_end).await.unwrap().len(), 1);

...

c.clean().await.unwrap();

...

assert_eq!(crate::fixed_sql_commands::pg::sequences(&mut c.back_end).await.unwrap().len(), 0);

...

Now it is only a matter of running the test. Go to the project root directory and type ./scripts/integration-tests-pg.sh to run your shinny new test through the diesel, tokio_postgres and sqlx database bridges.

Check out https://github.com/c410-f3r/oapth/blob/master/CONTRIBUTING.md for more information.

Issues

MS-SQL

Mysql

PostgreSQL

SQLite