Database Projects

[Miguel Tadeu]

On Wednesday 11 January 2012 13:01:12 Milian Wolff wrote:
> Miguel Tadeu, 10.01.2012:
> > Hi,
> > I'm making a proposal for a new project type. This project type is to
> > create facilities to develop for databases. Since there's the need for
> > some applications to use a database, it would be useful to integrate the
> > database development into the same session as the application.
> 
> Note: Quanta has a first-shot at this with its SQL plugin. If you want to work 
> on it, I can push it into it's own repository for easier access.
> 
> > Some of the features I'm thinking of would be:
> > 	- Reverse engineer from already existing databases
> 
> what does this mean?

Reverse engineer means importing objects(tables, SPs, functions, users, etc) from an existing database into a .sql file script.

> 
> > 	- Tree view for the database objects(tables, views, stored procedures,
> > functions, etc)
> 
> if the above mentioned plugin cannot do this already, it is of course a good 
> idea to add such functionality.

I'll check it out...
> 
> > - The database objects would be stored as .sql files
> 
> again, what does this mean? if users write database stuff in .sql files, so be 
> it. But what should KDevelop do here?

To begin with, we'll have a nice editor :) Having the application and the database development in the same IDE makes integration of the development tighter.

> 
> > 	- Language support for SQL
> 
> define "SQL". MySQL? SQLite? PostgreSQL? ...? You'll need to concentrate on 
> one "dialect" first and then might work on others later on. I agree that this 
> would be a nice thing btw.

In deed, I would start with mysql first.

> 
> For this you'll need a parser and lexer for your dialect. Best would be to 
> reuse such code from the official server, if possible. I.e. by copying their 
> grammar/lexer files.
> 
> > 	- Code completion for SQL development, collecting the symbols from the
> > .sql files
> 
> -> see above
> 
> > - Code validation for SQL
> 
> -> see above
> 
> > 	- Ability to easily create unit tests for functions and stored procedures
> 
> is there no existing SQL-test framework? I'm reluctant to create one on our 
> own. If there really is none yet, I would not suggest writing it in a way that 
> is heavily dependend on KDevelop. Rather create it as a separate application 
> and embed/integrate it into KDevelop. This way more people can use it and it 
> will get better coverage.

I was thinking on a procject template that supplies the infrastructure to communicate with the DB. The user would only have to write the unit tests. MySQL++ sounds like a nice add to this.

> 
> > 	- Data generation for testing
> 
> -> see above

Data generation would be something like random data to populate tables or from another database.

> 
> > 	- With .sql files generated in a structured tree in the file system, it
> > would be easy to include the database in a source control system
> 
> again, I don't get it. A database (binary blob, potentially large!) should 
> *not* be tracked by a VCS. If a user really wants to do it, he should do that 
> manually. This is *not* something KDevelop should do automatically.

Sorry, I was not clear about this point. When I say .sql scripts I mean the scripts that generate the structure of the database, not the data. Something like
CREATE TABLE XPTO......

> 
> Re file system structure: Again, this should be left to the user if he wants 
> that or not. KDevelop does not / should not impose such rules.

I didn't get this point...

> 
> > - Deployment of the local .sql files into one or more databases(DEV, TEST,
> > PROD, etc)
> 
> the above mentioned SQL plugin could be extended with such functionality
> 

Niko reminded that kate also has such a plugin. I'll investigate it too...

> > - Diff between databases and local .sql files
> 
> Again, what do you mean by a "database .sql file"? Maybe you mean a dump from 
> a database?

A dump of the structure, not the data.

> 
> > 	- Generate a diff script to deploy on a database
> 
> ? what do you mean here?

Generate a .sql script with the differences between 2 schemas. Thw result would be what to add and what to remove from the target database.

> 
> > 	- Possibility to run tests against the local .sql scripts instead of the
> > database server
> 
> see above regarding testing
> 
> > - Debug functions and stored procedures
> 
> see above regarding testing
> 
> > 	- Support for MySQL. Other servers would also be nice.
> 
> Yep, MySQL language support would be nice.
> 
> bye
> 
> 

regards