• Project ideas

  1. Creating an initial project repository at https://gitlab.mi.hdm-stuttgart.de.

    Tip

    Include your lecturer into your team providing at least read access.

  2. Committing a Readme.md file describing the project's goals.

    This includes a precise description of the prototype's desired functionality likely to be extended along with your project's progress.

  1. Identify individual tasks like e.g.:

    • Creating sample data.

    • Test server provisioning

    • Setting up test scenarios.

    • Selecting a documentation tool set.

  2. Assign team members to tasks.

  1. Implementation resulting in:

    • Version controlled source code (Gitlab, Github, ...)

    • End-user deployment description / CI/CD pipeline

      See the section called “Project documentation”

    • End-user documentation.

    • Internal software documentation. (Architecture, design principles, frameworks ...)

      Tip

      Don't start documenting too late. The »Real programmers don't document, the code is obvious« myth no longer works!

  • Command line argument handling

  • Database access API's, e.g. JDBC™.

  • Project ideas
    • ➟ Extending UNIX grep to databases 
> grep --color ❶ -i ❷ fraction App.java
package de.hdm_stuttgart.mi.sd1.fraction;
 * Playing with fraction objects.
    final Fraction threeSeven = 
        new Fraction(3, 7);
    final Fraction

  • Database queries are more complex than matching text.

  • Support for Nosql databases, e.g. Mongodb.

  • Customer demands:

    • Restrict input record sets.

    • Filter / page output.

  • Connection profile handling

  • Search level specification:

    • Whole database

    • Table(s)

    • Table column(s)

    • Recordsets

  • Output formatting, limiting/filtering and paging

Command File ~/.dbgrep/Profiles/postgresTest.cfg 
dbgrep --profile postgresTest ...
 
host=myserver.companynet.com
port=5432
database=main_test_data
user=testuser
password=secret
driver=Driver/postgresql-42.6.0.jar
 
dbgrep ... ❶
dbgrep ... --table User --table Stocks ❷...
dbgrep ... --column User.userId ❸ ...
dbgrep ... --table Stocks --column User.userId ... ❹
dbgrep ... --equal 237 ❶
dbgrep ... --greater 4.43 ❷
dbgrep ... --like 'Smit%' ❸
dbgrep ... --like 'Smit%' --and --greater 4 ❹
dbgrep ... --range [-3:17] ❺

❶

Search for integer values equal to 237. Return either of:

  • All records from all tables containing an integer type value of 237.

  • A list of all tables among with column names of integer type containing at least one value of 237 but not the records themselves. This resembles the grep -l ... aka --files-with-matches argument.

❷

Search for numeric values being greater than 4.43.

❸

Texts starting with Smit.

❹

Conjunction: Records containing text starting with Smit and at least one numerical value being greater than 4.

❺

Search for integer values between and including -3 and 17.

  • Project ideas
    • ➟ Database copy tool

Consider two potentially differing database systems e.g. Postgresql™ and Mysql™.

Source database:

Hosting an active instance i.e. a set of tables containing data records and optionally views.

Destination database

Yet empty or containing non-conflicting table and view names.

We assume full JDBC™ read access to our source database and full read/write access to the destination database.

  1. Source to destination copy addressing vendor specific SQL syntax rules.

  2. Transfer as many integrity constraints as possible:

    • Differing data types.

    • null / not null constraints.

    • Column default values.

    • Primary / candidate key constraints.

    • Foreign key constraints.

    • Check constraints.

  • null / not null defaults may differ on both database systems.

  • The destination database may be a non-SQL database like Mongodb supporting a limited subset of schema constraints. A copy tool thereby supports database migration.

Tip

Useful technologies:

  • Project ideas
    • ➟ Creating data samples

  • Precondition: Existing database schema.

  • Populate a corresponding database with test data.

  • Allow for configuration, e.g. table dependent record set sizes.

  • Project ideas
    • ➟ Database schema evolution tool

Consider an RDBMS with a given set of tables, data records and constraints. Software evolution requires schema evolution:

  • Adding new tables and views.

  • Adding / replacing data columns.

  • Changing types.

  • Adding / removing / changing integrity constraints.

Upgrading may involve:

  • Dumping the existing data to a series of JSON or XML files among with a database schema export.

  • Post modifying both exported data and schema to meet the desired version's schema.

    Note

    In practice a database's size may effectively prohibit validation due to memory / performance limits.

  • Import data and integrity constraint to upgraded database.