Turn your Android phone into a network SQL database (MyMobileSQLServer - Utesov)

Recently, I started doing another project for mobile devices. Its by-product was an application with which you can deploy a network database on your favorite Android phone, tablet, etc ... The proposed solution is a network provider SQLite database.

The application is multi-user, each user has its own database, which is not available to other users. Code open license Apache 2.0.

To demonstrate the stated capabilities, a simple application is written that allows you to make SQL queries to the server and get the results of their execution, consider its code here. The development time is 10 minutes. One device is enough for testing (if you use two or more, then you need a WiFi connection). Go!
')

Shot from the k \ f "Funny guys" (modified)

First of all, install the application (server) MyMobileSQLServer - Utesov from PlayMarket, Yandex.Store or from the repository . Next, you need to create a user to access the data and the database itself. To do this, on the main screen, select “edit user” -> “Add user”. Fill out the form fields. Then go back to the main screen of the application and launch it using the “on” button. Pay attention to the port and IP address to connect.



Let us proceed to the direct creation of an application for accessing a network database.

1 - Create a new project. Copy the library file “ myMobileSQLClient.jar ” to the library storage directory and take the necessary steps to make it available for the project.

2 - Configure the project manifest, add a directive to enable the use of a network connection:

<uses-permission android: name = "android.permission.INTERNET" />

3 - We throw on the form:
five input fields: IP address, port number, text for sql query, login, password ;
three buttons: connect, disconnect, send a request ;
field to display information .
You should have something like this:



Copy the following code with the necessary adjustments:

import ru.gc986.SQLClient.MainMobileSQLClient; import ru.gc986.SQLClient.parse.DATA_request; import android.app.Activity; import android.os.Bundle; import android.view.View; import android.widget.Button; import android.widget.EditText; import android.widget.TextView; public class MainActivity extends Activity { MainMobileSQLClient mainMobileSQLClient; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); //    Button bt_connect = (Button) findViewById(R.id.button_connect); bt_connect.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View v) { EditText et_ip = (EditText) findViewById(R.id.editText_ip); EditText et_port = (EditText) findViewById(R.id.editText_port); mainMobileSQLClient = new MainMobileSQLClient(et_ip.getText().toString(),Integer.parseInt(et_port.getText().toString())){ /**    */ @Override public void onMessage(String id, DATA_parser data){ super.onMessage(id, data); TextView tv_log = (TextView) findViewById(R.id.textView_log); String message = ""; for(int i = 0 ; i < data.recordsCount ; i ++){ for(int i1 = 0 ; i1 < data.structure.length ; i1++) { message = message + "\n" + data.structure[i1] + " : " + data.listData.get(i)[i1]; } message = message + "\n" + "-----"; } message = "Status - " + data.status + "\n" + message + "\n" + "======" + "\n"; tv_log.setText(message + tv_log.getText().toString()); } /**    */ @Override public void onConnection(){ super.onConnection(); } /**  */ @Override public void onConnect(){ super.onConnect(); //  EditText et_login = (EditText) findViewById(R.id.editText_login); EditText et_pass = (EditText) findViewById(R.id.editText_pass); onAuthentication(et_login.getText().toString(), et_pass.getText().toString()); } /**    */ @Override public void onDisconnect(){ showAllView(); } /**   */ @Override public void onErrAuthentication(){ showAllView(); } /**  */ @Override public void onCompleteAuthentication(){ } }; mainMobileSQLClient.startClient(); } }); //    Button bt_disconnect = (Button) findViewById(R.id.button_disconnect); bt_disconnect.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View v) { if(mainMobileSQLClient!=null) mainMobileSQLClient.stopClient(); } }); //   Button bt_request = (Button) findViewById(R.id.button_request); bt_request.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View v) { EditText et_request = (EditText) findViewById(R.id.editText_sql_request); if(mainMobileSQLClient!=null) mainMobileSQLClient.sendSQLRequest(et_request.getText().toString()); } }); } } 

Now let's see what happens.
This object is responsible for working with a remote database:
MainMobileSQLClient mainMobileSQLClient;
It overrides the following methods:
onMessage - a message came from the server;
onConnection - the client connects to the server;
onConnect - the client is connected to the server;
onDisconnect - the client has disconnected from the server;
onErrAuthentication - authentication error;
onCompleteAuthentication - authentication was successful;

When creating the main object, the IP address of the server and the port for the connection are specified. The client is started using the startClient () method. Starting the connection is followed by the onConnection method. If a connection error occurs, the onDisconnect method is called, which is also called when the client disconnects from the server (for example, if the connection is lost or the server is disconnected). If everything happened in normal mode, then the completion of the connection process will mark the onConnect method.

Next, you need to register the user on the server, it is best to do this after the end of the connection, directly in the onConnect method:

 onAuthentication(''user_name'', ''pass_pass''); 

If authentication succeeds, the onCompleteAuthentication method will be called, otherwise onErrAuthentication. If authentication has not occurred, then most likely your login and password do not match those registered on the server.

Now it is up to the small thing - to make requests and handle the response to incoming data.
To initiate a SQL query, you must call the method (example):
String id_request = mainMobileSQLClient.sendSQLRequest ('' select * from test; '');
It, in turn, will return the request ID as a text string. This is important because it is a network application, it works asynchronously. When a new response comes from the server, the onMessage (String id, DATA_parser data) method is called, which passes the request identifier (id) to which the response and the data itself came. The data is provided as a DATA_request object, which contains the following objects:
status - String - the status of the result of the data request, if “ok”, then the query is processed successfully, otherwise “err” (in the future, statuses may be replenished);
recordsCount - int— the number of records received from the server that are the result of the query;
structure - String [] - text array, which is an enumeration of the names of all fields of the response request;
listData - ArrayList <String []> - a list of data arrays. The number of items in the list corresponds to the number of entries from the server. Each element contains a text array, with the number of elements equal to the number of elements in the structure array.

The correctness of processing each request so far falls on the shoulders of the programmer, i.e. you will have to track each response from the server and compare it with the identifiers in the query tables that need to be maintained. But this solution gives the developer complete freedom and control in processing all the results coming from the server.

All is ready! Now proceed to testing, send any SQL queries to the server (creating tables, inserting / modifying data, deleting records, etc.) and get the corresponding execution results.

Repository with server, client, library and sample application - https://github.com/gc986/MyMobileSQLServer_Utesov
(Currently, the very first version of the project prepared in the ADT Eclipse format is in the repository. The next step will be to transfer the project to the AndroidStudio format)

Question to the audience: where can I apply this solution?