Database Projects

[Andrew Fuller]

Hey Miguel et al,

I figured I should chime in here as I have a bit of related experience.

This sounds like a WishList item, or have you already started on
something, Miguel?  I like the proposal.  In fact I like it so much
that I wanted something similar some time ago and wrote my own to suit
my needs.  So if you haven't already started, I'll mention what parts
I've got working and you can consider if you want to use mine as a
start point or even just to pull pieces from.

I should first mention that my program (dubbed DBSchemer) is
unpolished and currently only targets PostgreSQL.  It has been written
just because I needed a tool to do the job and didn't find an existing
one.  I intended (and still do) to fix some rough edges and make it
public.  Sounds like this may be a good time to push it out there
either for use or for parts.

Mine is a stand-alone application and not part of KDevelop, though I
intended to (in the future) make use of code completion technologies
from KDevelop.  Being directly part of KDevelop is a good idea but
with one consideration: there should be a CLI that is not tied to
KDevelop.  This obviously would not have the full capabilities as the
UI but would be invaluable for scripting.  Consider a headless server
with a git repository and running a database image where you want to
test your DB changes.  KDevelop is not needed on the machine, but a
scripting interface for the database project would be required.  So I
think it should have a base library, the KDevelop plugin that uses
said library, but also a CLI program with a limited interface that
also uses the library.  One or both interfaces can exist for any
installation.

I'll compare your feature list to DBSchemer's current capabilities so
you can decide if you want to have a look.

>        - Reverse engineer from already existing databases

DBSchemer does this for recent PostgreSQL databases, but not 100% (ie.
I don't load anything to do with users).

>        - Tree view for the database objects(tables, views, stored procedures, functions, etc)

I don't display them as a tree and I don't load views, but essentially yes.

>        - The database objects would be stored as .sql files

I do like the idea of separate files (whether .sql or other).  I
didn't do that with DBSchemer but looking back it probably would have
been beneficial to do so.  Two things to consider:
1. If you use .sql files, which dialect do you use?  (assuming support
for multiple databases).  Or perhaps auto-detect the dialect and
support every type?
2. You'll wish to store an identifier for every object.  In any given
database schema.tablename will be unique.  But when a user is editing
/ creating objects there could be a name collision.  If you save/load
your project, then are your FKs referencing the correct tables?  If
using .sql files this could be as comments so they remain valid .sql.

>        - Language support for SQL
>        - Code completion for SQL development, collecting the symbols from the .sql files
>        - Code validation for SQL

Kate gives us syntax highlighting, but beyond that this is definitely
the realm of KDevelop awesomeness which DBSchemer has not yet tapped
into.

>        - Ability to easily create unit tests for functions and stored procedures
>        - Data generation for testing

DBSchemer is of no help in its current state for this, I'm afraid.

>        - Deployment of the local .sql files into one or more databases(DEV, TEST, PROD, etc)
>        - Diff between databases and local .sql files
>        - Generate a diff script to deploy on a database

DBSchemer does this.  And don't forget this part would be very useful
to be scriptable on a headless server.

>        - Possibility to run tests against the local .sql scripts instead of the database server
>        - Debug functions and stored procedures

Are you thinking of doing this outside the DB?  Or are you thinking of
controlling an actual DB and have the plugin feed it the data and read
back the output?  The latter sounds like a better test mechanism to
me.

>        - Support for MySQL. Other servers would also be nice.

DBSchemer has no support for MySQL.  Multiple servers would be great.

One thing that you don't mention that I found to be the primary reason
for starting DBSchemer is that I wanted a *visual* database editor.
The current solutions out there seem to be table-based, but MySQL,
PostgreSQL, etc are all focused on the relations, not the tables
themselves.  (I can't speak for NoSQL servers).  Based on what I've
seen of the KDevelop code, I don't believe it would be a problem to
load a different widget than Kate in the view tabs so we can view the
relationships.  Were you wanting this, or were you wanting table-based
and just integrated into KDevelop?

I'm happy to help out with it, but on condition that there is a visual
view of the relationships within the DB structure (as mentioned
above).  Otherwise it holds little value for me personally.

Cheers,
-Andrew