Database Projects

[Miguel Tadeu]

On Friday 13 January 2012 19:47:56 Andrew Fuller wrote:
> 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.
> 
Hi Andrew. This is still a proposal open for discussion for requirements. I'm still on the planing phase here and all help is welcome.
All available code will be analysed(in my possibilities and of those that wish to help), so I'd be happy to analyse our code too and see how it fits the planning.

> 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.
> 
It it a good time :) even unpolished.

> 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 agree to have a CLI with the basic funcionality and the UI to show the results to the user. With this, there can be other apps based on it.
But, what do you mean by scripting interface?

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

> >        - 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 tree view looks like the most organised way to display the objects, but still open for suggestions

> >        - 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?
Well I'd have to use the dialect of the current connection. To support multiple server types, multiple dialects will have to be developed.


> 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.
> 
This will be resolved when planning the implementation. But I'm figuring that when deploying each script, the database it will complain about it.

> >        - 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.
> 
KDevelop is....Probably with a new project type to ease the development of unit tests.

> >        - 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.
> 
You mean, using a CLI? Just to make sure about what you mean about being scriptable.

> >        - 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.
> 
At first both occured to me. But in kdevelop we'll only have the structure of the database(or a representation of it), so it won't be possible to have data on it.
Saying so, testing against the server is the only option I find reasonable.

> >        - Support for MySQL. Other servers would also be nice.
> 
> DBSchemer has no support for MySQL.  Multiple servers would be great.
> 
I'll start with MySQL support, since I think it's the most used among open source projects. The idea is to make this plug-in driven, so further server types can be supported.

> 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?
> 
This is not a mandatory feature for database development, but I find it a good feature. The "visual" parts will have to have the ability to handle(create, change and delete) the .sql scripts.

> 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.
> 
Then feel welcome to join. If you don't mind I'd like to take a look at DBSchemer, so I can think of an architecture for all this.

> Cheers,
> -Andrew
> 
Cheers,
Miguel Tadeu