Author Topic: An ideea for an RDBMS project using C::B. Would be this a challange or what?  (Read 8173 times)

Offline eugen_mihailescu

  • Single posting newcomer
  • *
  • Posts: 7
    Hello everyone,

    I hope my question is not too stupid for this forum :)

Premise:
I am newcomer to C::B, I study a bit (1wk) the functionality, I even built the C::B from nightly build (it is such a cool thing that I have not enough words to express myself), so I am very pleased about the whole work that you have done guys, and also I am very optimist for the future of some RDBMS projects idea (RDBMS have nothing in common with C++, you'll think, right?)

What I am going to try:
Usually I (but I think that there are thousands like me) am working with large RDBMS projects, such project usually have over 1000 tables, 1000 stored procedures, 1000 functions, 1000 triggers, and so on.

You can imagine this project as a structure of folders and files, as following:

  • DATABASE
    • 1st_MODULE_NAME
      • TABLES
        • My1stTable
          • Triggers
            • Trigger1
            • ...
          • Constraints
            • Contraint1
            • ...
            (other table dependency)
        • ...(other tables)
      • STORED PROCEDURES
      • My1StoredProcedure
      • ...
      • VIEWS
      • My1stView
      • ...
      • FUNCTIONS
      • My1stFunction
      • ...
  • 2nd_MODULE_NAME

Bold = project structure as folders
Italic = files with .sql extension

So, actually a RDBMS project is a kind of file structure on disk, folders+files. The file types an RDBMS project will have are:
  • .sql = SQL object script file
  • .xml = SQL object property file

.sql file will store something like "CREATE <obj_type> BEGIN ....END"
.xml file will store the object's properties, in the case of a table will store the table name, the field's list, for each field will store the name/type/size/etc.

CB already is capable to handle this kind of thing (virtual folders and source files).
CB already have an editor with syntax highlighting. Also I saw in the nightly build the lexer (used for syntax highlighting) and something called "sqcompiler.cpp". I don't know yet where it is used (in C::B I mean) but I have read the source code (.cpp/.h) and it seems to be a "syntax checker" based on the ? language grammar. Based on this ? compiler (if it is not already something built in C::B yet) I will write a plug-in that simply will call(would be the easy way) the "? compiler" to check the .sql file syntax.
If there is not any compiler that I can use to check SQL syntax this will be the first challenge.
In this way I can use CB for RDBMS source management. OK, this can be done (sort of) using a lot of free tools, why reinventing the wheel?

Actually the IDE have already a lot of functionality that can be (re)used no matter what source file you will have + the ability to integrate with custom plug-ins that is extending its functionality. This is the big difference.

The final C:B should help any "RDBMS/C++" programmer to achieve the following:
  • to call a plug-in that will connect a RDBMS, analyze the structure, create inside CB the virtual folder structure, in each folder create the necessary .sql/.xml files based on the RDBMS objects
  • the IDE already have almost everything necessary for source editing and syntax checking (or will have)
  • to call a plug-in that will allow to connect a RDBMS and based on the virtual folder+xml+sql files to create from scratch the database structure, objects, so on; this plug-in will be something like "RDBMS deployment" if you like
  • if this will be possible then I will go further, for example to manage the source changes and to create incremental deployment scripts
  • I have more ideas but for the moment I will be glad if you (the experimented users preferable) can confirm if CB + CB SDK could be a framework for such a project.
The thing is that RDBMS project is not different than any other programing language project, except that there are no such tools available freely as open source projects, or at least I never heard of.

At M$ they have such a project but you don't want to know how expensive is just one license (see http://blogs.msdn.com/gertd/archive/2007/11/21/visual-studio-team-system-2008-database-edition.aspx).

So,what do you think? Am I just crazy, do I missed the forum where I should write this problem, or what?
(ignore the following tag)[/list]

Update:
I understand now what is about sqcompiler (see my obscure point above), it might be a solution for what I have asked: http://en.wikipedia.org/wiki/Squirrel_%28programming_language%29
« Last Edit: April 29, 2010, 08:28:15 pm by eugen_mihailescu »

Offline stahta01

  • Lives here!
  • ****
  • Posts: 6589
    • My Best Post
I do NOT think Code::Blocks would be worth the effort to add SQL additions.
I suggest finding an open source SQL Tool and use your efferts to improve it.

Examples
SQL Query Agent http://sourceforge.net/projects/sqlqueryagent/
pgAdmin http://www.pgadmin.org/

See also:
http://en.wikipedia.org/wiki/Comparison_of_database_tools

Tim S.
C Programmer working to learn more about C++ and Git.
On Windows 7 64 bit and Windows 10 32 bit.
On Debian Stretch, compiling CB Trunk against wxWidgets 3.0.
--
When in doubt, read the CB WiKi FAQ. http://wiki.codeblocks.org

Offline JGM

  • Lives here!
  • ****
  • Posts: 518
  • Got to practice :)
but many programmers use sql in combination with their programming language I for example use php with sqlite, and firebird with C++ I think never is a bad idea to extend as much as possible an IDE, is true that an IDE will never never cover everything every particular user would like but hey even visual studio has sql support for many databases.

Code::blocks could become much powerful with such a feature if some one implements it!

My 2,3,4,5,6 cents  :)

Offline stahta01

  • Lives here!
  • ****
  • Posts: 6589
    • My Best Post
The nice thing about Code::Blocks is a plug-in environment; so, feel free to try to do it.

Note, pgAdmin uses wxWidgets so you might be able to borrow ideas and code from it.

I will be willing to do some testing and add suggestions. But, I still not sure that an IDE that primary does C/C++ is the best thing to add the effort to make an SQL tool.

Things that might apply.

1A. Use Targets as different server/databases for execution of SQL commands.
1B. Use Targets as different users for execution of SQL commands.
2. Would projects match up with databases? This seems a good match.
3. Would workspace match up to Servers? Not sure this is a good match.

Not certain how the xml files describing tables will be used.

Former MS SQL 2000 developer for a small custom client/server App, stopped in mid 2006.


Tim S.
C Programmer working to learn more about C++ and Git.
On Windows 7 64 bit and Windows 10 32 bit.
On Debian Stretch, compiling CB Trunk against wxWidgets 3.0.
--
When in doubt, read the CB WiKi FAQ. http://wiki.codeblocks.org

Offline eugen_mihailescu

  • Single posting newcomer
  • *
  • Posts: 7
I do NOT think Code::Blocks would be worth the effort to add SQL additions.
I suggest finding an open source SQL Tool and use your efferts to improve it.

Examples
SQL Query Agent http://sourceforge.net/projects/sqlqueryagent/
pgAdmin http://www.pgadmin.org/

See also:
http://en.wikipedia.org/wiki/Comparison_of_database_tools

Tim S.

Hi Tim,

I think you have got me wrong, or I was not able (I've learned English from TV/Internet) to synthesize my idea in order to make me very clear. I am not looking to create another RDBMS administration tool (pgAdmin, M$ Management Studio, Toad, etc). These kind of software have nothing in common with my idea, except that both revolve around RDBMS.

The idea is much closer to http://blogs.msdn.com/gertd/archive/2007/11/21/visual-studio-team-system-2008-database-edition.aspx than Toad/pgAdmin/you_name_it.
Toad/pgAdmin/etc are more likely useful for database administrators than database programmers, I will not go further because is too much to discuss about this subject.

Thank you for your reply, I will consider it together with all other replies that I hope it will comes next days  :D
Best wishes,
Eugen

Offline eugen_mihailescu

  • Single posting newcomer
  • *
  • Posts: 7
Code::blocks could become much powerful with such a feature if some one implements it!

That was the idea, the base framework of C::B is such powerful that is shame to not use it at the maximum potential.

Offline eugen_mihailescu

  • Single posting newcomer
  • *
  • Posts: 7
The nice thing about Code::Blocks is a plug-in environment; so, feel free to try to do it.

Note, pgAdmin uses wxWidgets so you might be able to borrow ideas and code from it.

Hi Tim (again  :D ),

Yes, C:B is a plug-in environment more than a C++ environment, at least that is what I saw from the angle from which I have been looked to the C::B. Yes, the C::B team have done all the efforts to transform it in a veritable IDE for C++ programming that they have succeed to make it an IDE for any kind of programming. The base version has few limitations but they could be easily overcome with some effort.
Right now I just want to collect as much feedback as possible about the whole idea, so any suggestion/criticize/help will be very appreciated and very useful.

Thanks again.
Eugen

Offline thomas

  • Administrator
  • Lives here!
  • *****
  • Posts: 3979
I think you will have to implement most of the functionality yourself, since the project management pane is not made to browse anything more complicated than files (for example, you wouldn't be able to navigate into a schema). On the other hand, you could implement a database browser as editor component in much the same way wxSmith does it. Then one could add a database file to the project and when clicked on, it would open in an editor, showing tables etc.
"We should forget about small efficiencies, say about 97% of the time: Premature quotation is the root of public humiliation."

Offline eugen_mihailescu

  • Single posting newcomer
  • *
  • Posts: 7
(the answer is so large because I want to make clear the whole idea, it seems that because of my poor English, people think that I am talking about kind of database administration tool, which is not my intention.)

The idea is not to create another database tool, such as Toad/M$ Enterprise Manager (or its new child SQL Management Studio), or any other tool like database browser.

This tool is more about:
  • how to create a database project from the source code
  • how to share the same sources between different projects, the same way we use #include in C++
  • how to install/deploy the whole project to a destination database server from scratch, or in the case that the database server is a production server, how to deploy incremental update without altering the existent data (or your users/customer will shoot you instantly)
  • in the case of source refactoring, deploy the database structure changes by preserving existent data
  • because database projects means, like in C++ projects, to test intensive the product (before deployment) using some kind of "test case scenario", you can write (in C++) a plug-in that will run using the the project database server data whatever test you include in your C++ code; ex: to monitor the code execution performances, bottlenecks, limits, any last but not the least to test if the output of test result is according to the input data; I have to mention that these plug-ins have nothing to do with C::B, they should be custom plug-in related to each project, so probably some of them (or all) will never be published to C::B SVN repository, except the case they make sense for other people projects, too
  • in the database projects, in order to make it possible to run an test scenarios, you need some initial data; so you need to write a series of custom programs/plug-in (in C++) that will help you to generate random data; I have to mention that these plug-ins have nothing to do with C::B, the should be custom plug-in related to each project, so probably some of them (or all) will be never published to C::B svn, except the case they make sense for other people projects, too

At the end of the day, a database programmer (which usually need both, a database specific language AND also some powerful middle-high-level programming language, as C++) will have the following tool:
  • an environment where he has all the database sources (remember that a table source is just a schema, that can be easily represented in a XML language; the table script can be generated from using the xml schema; a stored procedure/function/trigger is nothing more than a code stored in a .sql source file, like yours .cpp/.h; and so on)
  • an source editor where he can edit very easy the sources of its database project (.xml files, .sql files, etc)
  • an environment which allows him to creates his own plug-ins for his particular needs; plug-ins meant to assist him or to automate some development task.
  • using some sort of SQL syntax checker (if you want the SQL compiler that you will use for this project) he will be able to check every file's syntax before deploying on the database (otherwise in the case of error, the database will say "invalid syntax near SELECT keyword" or something like that :)
  • using another kind of C::B plug-in, written special for this kind of task,he will be able to deploy (from the file's source code) the entire database project to the database server, meaning: to create database on the server, to create objects to the server (such as tables, procedures, functions, etc), he could have some scripts for pre-deployment and post-deployment (as we already have in C++ the pre-build steps) which he can use it for populating with some initial data the resulted database.
  • further, if you want, another plug-in could take care of source changes, meaning that each time that a table structure is changed, a stored procedure/function/trigger code is changed, these changes must be managed somewhere and then they must be packed  and deployed them on the destination server; this is kind of incremental deployment; but of course, the deployment script should take care not only "how the new database will look" but also about preservation of old data

You should never think about a RDBMS as a "bunch of TXT/XLS tables needed to store your data somewhere" because the RDBMS means not only the data structures (tables/views) but also thousands of stored procedure, functions, triggers, security, etc (written in a specific language depending on a database engine).
If you have the same database project installed to more than 100 customers, than you cannot connect manually to each database and make manually the same changes (how you are doing today with pgAdmin, Toad, etc, for example). Also, the project itself sometimes includes some customization for each customer which mean you need a sort of project's file source (where you can keep the source hierarchically) with some kind of "#include" capability.

You see, when you think to a C++ project and when you create a small "EXE" for your customer, you think that the deployment is all about "to overwrite the old "EXE" with the new "EXE" built from the source code (or just deploying the binary)". Well, this is also true for RDBMS, but you should also keep in mind that generally you have to take care of the old data and this can not be done with pgAdmin, Toad, etc. because it involves steps, data processing, automation. Before you spread your new version to your customers, you must test your entire project and make sure that it meets the performance criteria, hence you need to fill up your database with random data, to test it with various scenarios, and ONLY if they meet your expectation that this project become "ready for deployment". Every change you make to a file inside your project can damage other functionality, but if you have a platform which allows you to run  "custom random data", "custom test scenario" then you can automate those task which are for PCs, they are not for the human  :D

Finally: yes, the database browser could help (even any C++ programmer that already use C::B) and this can be implemented as a plug-in component for C::B using the wxSmith which is a great GUI toolkit.