Model validation

[ondras]

From geompse@gmail.com on March 18, 2010 17:18:55

(new feature)

I know it is not the purpose of WWW SQL Designer, tough it would be cool to
have a quick validation done over the schema.

Simple rules like :

• [warn] table as no primary key
• [warn] foreign key index is missing
• [info] primary key is not numeric
• [info] table name contains other chars than ^[0-9A-Za-z_]*$

And maybe more rules...

Do you want some code towards this ?

Original issue: http://code.google.com/p/wwwsqldesigner/issues/detail?id=87

[ondras]

From geompse@gmail.com on March 18, 2010 09:49:45

• [warn] foreign key constraint does not match with a primary key

[ondras]

From ondrej.zara on March 18, 2010 23:57:35

Do you think this feature should be

1. implemented on client side,

2. target-db specific?

[ondras]

From geompse@gmail.com on March 19, 2010 03:57:26

I think of a client side implementation, these are common warnings that are relevant
no matter what DB kind you use.

Maybe it should be implemented twice, giving db-specific validation, but I'm not fond
of this.

---

Pour la petite histoire, I had to work with an Oracle DBA. He checked our team's
model (a model designed to be db-agnostic because we needed portability), and say :

• names are case sensitive, BAD
• dates and times are stored as integer (numeric), BAD
• some data are stored in JSON, BAD

These are typical db-specific warnings, but to us it makes no sense at all, since :

• names are by default lower-case in postgresql and had to be lower-case too in
  Oracle, where names are upper-case by default.
• dates and times column formats are really different from one SGDB to another
• the JSON data are computed afterwards by our program, since there is no sql-search
  on it, it saves times by not creating more tables, setting more relations,
  complexifying queries...

The point is that "good practices" must always be rethink afterwards. Sometimes they
are no good for your project.

[ondras]

From ondrej.zara on March 19, 2010 04:14:13

Okay, makes sense. Some small set of client-side DB-agnostic checks sounds like a
reasonable idea.

Status: Accepted
Labels: -Type-Defect Type-Enhancement