SQL as slim and easy possible Topic is solved

[Jorg]

Hi,

I want to (for the fun of it) play a bit with databases. But I do not feel like setting up ODBC, seperate SQL engine, etc. I know about SQL lite, a single DB backend for apps.

Can someone point me into the proper direction for using a simple SQL server with wxWidgets ?

Regards,
- Jorgen

[geon]

I have worked with MySQL and SQLite under PHP. I found SQLite to be at least as good as MySQL for my own needs.

One great thing about SQLite is the file-based database. Especially when debugging, it is useful to be able to easily back-up or reset a database quickly.

But more importantly, you have noo need to configure passwords and usernames, just the filename. Much more comfortable (especially when your web host doesn't automatically provide MySQL.). It sounds a bit less secure at first, but to get unauthorized access to the database, you also would have access to the scripts/config files, so it doesn't really matter.

[Jorg]

Thanks Geon,

How about using wxODBC but with a slim back-end, is something like that possible? Using a SQL lite kind of DB interface with wxODBC?

What I am aiming at is that I am not tying my own hands together with one solution, and might end up later on to use a more full blown DB solution anyway, and rewriting parts of my code.

Also, do you have any experience with how to implement the best 3 tier solution under wxWidgets ?

I want to avoid mixing the SQL commands in my data classes, but I do also want to avoid changing my whole app layer when I change my database scheme to something else.

With regards,
- Jorgen

[ddaeschl]

Hi Jorg,

In the book Patterns of Enterprise Application Architecture the authors define patterns that help you to separate the data access code from the class logic. One of the patterns that the authors develop is the Table data gateway ( http://www.martinfowler.com/eaaCatalog/ ... teway.html )

This allows you to hide the actual database code from the rest of your application, enabling you to swap in other kinds of gateways for different databases as an example.

After looking at the class diagrams you may just find yourself saying "oh yeah, duh!" as I did

[RogerIrwin]

Interested in collaboration ?

I have a lot of experience with using databases, but no experience with wxWidgets.

My first wxWidgets project does include an SQLite peer to peer server. I researched available solutions before starting this project and found none. Allthougth I did find people who had tried wrapping the SQLite API to allow remote access, I felt this was a very kludgey solution as the API is designed for local access.

Instead I have made a wrapper that allows pure SQL queries to be issued and results recieved (which I have done in an odd way, as the obvious ways have 'gotcha's). Therefore this solution **cannot** be used as a drop in replacement to make existing SQLite apps client server, but it used 'from scratch' then it does make a neat solution.

I have the basic server just about running, I am perfecting the server interface before developing the client as I want something solid to test the client against rather than end up developing 2 apps simultaneously.

As nothing like this currently exists I had planned to post my solution when finished. I have no experience of administering open development (using CVS, source forge, stuff like that) my hope would be that somebody else is interested enougth to do this and perhaps others will be interested in enhancing the concept with things that they need like ODBC drivers etc.

If you wish to wait a few days I will post a message here when I have a basic package complete.

ALTERNITEVLY, perhaps you are interested in developing client software, something I have not done but should be quite simple. If you (or others) are interested in this, then you could email me at:

roger
AT
techfell
com

[Jorg]

To ddaeschl,

Thanks this sounds like something useful! To abstract the main app from the database layer a wxConfig like layer is needed, where the classes can be restored using paths or key combinations. The idea of implementing SQL like statements in the data layer which are propagated is interesting.

Myself I was thinking about something like pointing out unique key combinations for classes, and on demand restore.

I do know there are things like record sets in .net. abstract SQL commands can be used to retrieve record sets. This should be easily portable as well right ?

To RogerIrwin,

I wish I had the time to collaborate. I have many projects going, and there is no tellign when I will be able to finish this to say the least.

It would be nice if indeed there is one global solution like record sets to be used. The problem with SQL is that every data element needs to be specified in a select statement. I am not experienced enough to know how things work the best (thus my question about the best 3 tier solution).

I will have to investigate this furter and perhaps choose a more 'dirty' solution first, and see how easy that goes.

Thanks all,
- Jorgen

[RogerIrwin]

To RogerIrwin,

It would be nice if indeed there is one global solution like record sets to be used. The problem with SQL is that every data element needs to be specified in a select statement. I am not experienced enough to know how things work the best (thus my question about the best 3 tier solution).

Your comments reflect my views a few years ago!

A programmer coming to use databases for the first time tends to look for the easiest way to link the database tables to some kind of data object that they can manipulate using the procedural languages they know so well. As experience grows with databases you begin to realise that the database is a far more appropriate tool for doing many of the tasks they would have done in code.

SQL is a language, but unlike procedural languges you are carrying out operations that on whole dataset in one go. When I first started using databses I used recordsets that where linked to a table, no SQL whatsover. Nowdays I would not dream of accessing a database without SQL.

Simple examples:

GUI toolkits generally allow you to link a recordset to a grid, and they will automatically generate titles and format the table. But suppose your table contains the following columns:

Article_Name
Article_Cost
Profit_Markup

and you want a table that has the columns:

Item (=Article_Name)
ListPrice (=Article_Cost + Profit_Markup)
YourPrice (=Article_Cost+(Profit_Markup *0.9)

The programmers approach would either to read the table and write code to generate the grid manually or (where hooks are available), to put callbacks that modify the data as it comes in. Using SQL you could just ask for a recordset that has your required result:

Select Article_Name as Item, ListPrics as (Article_Cost + Profit_Markup), YourPrice as Article_Cost+(Profit_Markup *0.9) as YourPrice from MyTable

That is a **very** simple example from a single table. Just imagine if the discount (*0.9) had been a variable in a customer table, SQL could still do that as simple query:

Select Article_Name as Item, ListPrics as (Article_Cost + Profit_Markup), YourPrice as Article_Cost+(Profit_Markup * CustTable.discount) as YourPrice from MyTable, CustTable where CustTable.UserNum=1003;

As experience with databases grows I find more and more ways I can replace reams of code just by good database design. The facilities offered by transactions and triggers and other database facilites can be very awkward to do in code.

Note that the design of the dabase (tables, schema etc) is very important to any database use.

Before making any decisions about what database tools to use, I would strongly recommend you work througth a good introductory book such as The Big Dummies guide to SQL.

BTW, allthougth MySQL is a good database, it does not support a lot of common database facilities (leastways it didn't, I know the latest versions now offer subqueries, triggers, views atc, but AFAIK it is still Alpha/Beta and perhaps not appropriate for learners....is it me or is it a bug!). FireFox and Postgres are open databases that implement a good standard SQL, or for maximum simplicity you could use the sqlite console program that is included in sqlite.

[toxicBunny]

I wrote an application several years ago that was client/server in nature using sockets and a MySQL database backend. I am now in the process of modifying it to add a networked version for local networks and a single-user version. The networked version will use a Firebird database while the single-user version will use SQLite.

I have abstracted the data access by creating what I refer to as a DataConnector class. This class has virtual methods for all the functions I need to access from my application such as connecting, disconnecting, and retrieving or updating a data object. I then create derived classes to implement the different functionality. I create a LocalDataConnector for the single-user version and implement all the functions using either the DBStep library or the DatabaseLayer library from wxCode. This gives me access to SQLite, Firebird, Postgres, or MySQL natively. DBStep also includes ODBC access. For the NetworkDataConnector, I simply override the connection functionality in the LocalDataConnector class to connect to a Firebird database instead of an SQLite database. The rest of the code works the same for both databases. On the server side of the client/server version I do something similar to connect to a MySQL database. In this sense, I write the database code once and simply change the connection functions to connect to the appropriate type of database.

I can then use the client for accessing any of the dabases by simply creating the appropriate type of DataConnector class. The client/server version is a little different because the calls are being passed between the client and server using sockets, but the actual data access code on the server is the same as for the single-user version. I use events to notify the client that the action in the DataConnector failed or succeeded.

This may not be the best way to do things, but it seems to work well so far. Of course, the project is only partially completed at the moment since I only work on it in my spare time. Just thought I'd throw that out as an option...

Scott

[Jorg]

Thanks for this paradigm shift in my thoughts I must say!!

I have always been a strong believer of data containment and objects that know how to restore themselves. I rather point an object I created to a datasource and let it restore himself.

That has worked for me, but I came to realise that the data model is actually the DATABASE itself! This actually came to mind when larger data structures needed to be restored from a file all the time. It was always needed to keep all those objects alive.

So maybe instead I should simply retrieve the data itself always from the database when it needs to be viewed, and only for performance issues such as operations and lengthy calculations, represent them in a C++ class.

My application is very simple. It has the project item it currently works with in memory, and every 'x' minutes I issue a program that does something. The database effort is very minimal, but just for that case I decided to choose this project to start with it. If it is too much work, it's a steeper learning curve.

Thanks for the book tip, luckily I work at a company where we have a wide range of SQL books ..

With regards,
- Jorgen

[RogerIrwin]

Are you trying to say I create a C++ object that contains my data, I manipulate it and it knows how to put my data back?

Works great if there is only one object and one thread that can alter the data;-)

Having said that, many people are using SQLite for single user embedded applications just because they want a full SQL capability rather than just the ability to get and restore tables.

An example, this forum. It can send an email notification for new posts. I assume each post with it's details gets shoved into a database. I could read the posts into one object, read user info into another, and then work througth to see if any new posts are to threads were there are other posters who have requested notification.

On the other hand I could do that globally at a database level, I send a query to the database and it replies with a recordset that contains a list of all the users which need to be notified along with the other details needed to generate the email. Much simpler, and far less bug prone.

But I could go further, I could put a trigger on the 'posts' table so that every time a new record is placed in the 'posts' a query is executed that inserts the username and thread into a notifications table. All my program needs to do is to open a recordset of the notifications table cross linked to the users table so each record contains the email address and the thread number for the notification. As the email is sent I execute a delete for the record that contains the username and thread.

This is 2 tier database use. 3 tier systems deal with abstracting the operations but, IMHO most (not all) 3 tier stuff is bloatware/vapourware. If SQL were 'perfect', 3 tier would be irrelevant Just check out 3 tier middleware websites, they are full of 'corporate visions' and 'new futures'. Where's the meat?

[Jorg]

I guess you are right!

Thank you for the insight, I can read you have good experience with SQL and databases. I have done some work on it, but it has not been much, and the framework was already there, and we use Delphi (and COM) which makes life a bit easier.

I guess I will have to treat the database layer as the "object" model and figure out "views" on the data, which MAY contain data from one of the tables and play with it, but it IS not a complete restored object.

Thanks again for the extensive explanation, now to start playing around

I will award you with the accepted post points (wxAwards) and the others with assisted points as they helped me greatly too!

- Jorgen

[ddaeschl]

I would agree with RogerIrwin that alot of middleware is bloatware that doesn't increse productivity or flexability at all. However, we develop middleware here that deals with things like client disconnected operation (restaurant cash registers for example), database failures, instant messaging, remote file services, and other areas that a relational database can't touch, or would have problems with. Sometimes a rich object model, application server, and a database back end (3 tier) really is the right way to go. It just depends on your situation.

[Jorg]

Yeah .. well I have to step down of the 1:1 relation of tables in the DB though. I will however use classes that I can restore with DB data, but they are not sacred in the way that the DB cannot be consulted for quick queries ertc.

We use AdoConnection / AdoPeer model. This goes a bit like this;

Code: Select all

// create a peer class that knows how to restore MyFoo
MyPersistentFooPeer peer(sql_connection);

// create an empty MyFoo container
MyFoo *ptr = new MyFoo();

// set the ID (now this will be tricky)
ptr->SetId(theId)

// restore the object using ID.
ptr->Restore(peer);

This object model has as benefit that unique ID's can be used to recreate the objects. The drawback is that the ID's must be known and the database must be able to issue unique ID's that do not change like the lausy way access creates a followup ID.

My only problem now is how to fill the MyFoo object the most efficiently with the MyPersistentFooPeer object. It needs to be some kind of friends with the original object to reach it's internal properties without having to use Get/Set methods.

- Jorgen