4

I am currently trying to design an interface for a WebService that can access several MySQL databases. There will be 4 operations available, Add(), Read(), Update() and Delete().

The WebService just needs to assemble an SQL statement based on the called method and execute it on the target database. But now I am struggling to find a good method signature that provides all needed information to the WebService and is simple to use. So if I want to assemble an INSERT statement when the Add() method was called, I don't know how I can pass all the column information with the corresponding values and types. For example a .NET DateTime should be stored with the MySQL data type for date/time. So I'd need some kind of wrapper object that check the properties via reflection to get the type.

Here are some thoughts:

Add(String db, String table, Dictionary<String, object> dataSet);
Read(String db, String table, String column, filter?);
Update(String db, String table, String idToReplace, Dictionary<String, object> dataSet);
Delete(String db, String table, String idToDelete);

I am not sure how to specify a filter possibility for the SELECT statement in the Read() method. In addition I don't know how to pass parameter information in the Add() and Update() methods. I don't want to save only string values in the db, I'd like to have passed some type information so that the WebService can match them to the corresponding MySQL data types.

  • 1
    How does the WebService know which database to perform the request on? – superM May 6 '15 at 9:17
  • first parameter – xmashallax May 6 '15 at 9:19
  • 1
    What language are you working with? Also this does not look like a web service, more like class methods (maybe used by that web service?). You may want to look into the Active Record Pattern and maybe Object Relational Mapping. Then your requests would maybe look more like Connection.new(db).from(table).add(dataset) (this just a very rough idea to get a start). also Connection.new(db).from(table).where({field1: value, field2: another_value}) – thorsten müller May 6 '15 at 9:30
  • Technology is .NET and WCF. The patterns are too much overhead. I want it to be more simple. – xmashallax May 6 '15 at 11:55
1

I believe you are making fundamental mistake. WebService is created to fulfill some clear purpose, not just operate on very generic data.

Now you have some kind of simple methods and are trying to abstract them so that they would be able to do all the work. This will not scale well. Tomorrow you will want them (since they are really VERY abstracted) to do some more work and will face a real challenge to incorporate that work into your abstracted abstraction.

Believe me, adding more methods here does not complicate matters. By making some generic generics, you are violating SRP principle.

Well, if you insist... Then look into ADO.NET method signatures. I believe they qualify for your task.

  • So you say instead of... Add(String db, String table, List<Object> data) I should do something like... AddToDB1(String table, List<Object> data) AddToDB2(String table, List<Object> data) ? – xmashallax May 13 '15 at 16:04
  • @xmashallax Well... If this is a specific service to manipulate databases, then, yes. But I guess this service of yours has some specific purpose. So, it's methods should be called after the changes they made in database. for example, changeUserData(), createLogEntry etc. – Vladislav Rastrusny May 13 '15 at 16:12
  • Woha no...that would be a huge bunch of methods if there is a method for every possible action. We have around 80 tables to deal with. That's why I wanted to use a more generic approach. The purpose of the service is just to access databases (at the moment 2). – xmashallax May 13 '15 at 16:18
  • 1
    @xmashallax Well, I see. Then I guess you should go with some metadata listing methods (DESCRIBE TABLE, SHOW TABLES etc) + some SQL-like methods (SELECT, INSERT, DELETE) – Vladislav Rastrusny May 14 '15 at 10:34
0

Filter:

In order to implement a filter, you could pass a simple string that is equivalent to the "WHERE " part of the select statement that gets executed, but you wouldn't want to just directly append what gets passed in to the actual query that gets executed since it creates an exploit that can be used against the DB. So instead of using the string directly, you could have some process that builds up the filter string, based on the input string.

For example: Parse and tokenize the string. Group over each AND/OR token. Match the values and comparison operators to a predefined list, then build up the filter string using the elements from the predefined list that you matched. Finally concatenate the AND/OR groups. This could get complicated if you want to implement all possible filter expressions.

For a list of what to implement, see http://dev.mysql.com/doc/refman/5.6/en/expressions.html and http://dev.mysql.com/doc/refman/5.6/en/functions.html.

Data Types:

As for typing the objects in Add and Update, the types are already predefined by the MySQL table. Therefore, you may compile a list of tables beforehand with their associated column types, then use that mapping to cast the Dictionary of objects once the WebService receives the request. The client side would be completely unaware of this process, and would continue to pass data as Dictionary<String, object> dataSet. If you encounter a new table, you can also create a new mapping entry on the fly by executing "DESCRIBE table" and storing the table information along with the other ones you have already defined.

  • I thought about something like that for the filter and implemented that basically without security checks. But as you mention the db can easily be hacked...so due to complexity reasons we'll drop this approach. – xmashallax May 13 '15 at 16:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.