Linux_TCP_SQLite3: Implementation of a small embedded system including receiving and saving data.

1. Background

The system is designed to implement the function of receiving data by TCP/IP from the broadcast under the mechanism of multi-threaded in LINUX. Then store the data to the local database,

2. General introduction 

SQLite3: SQLite is a relational database system contained in a C programing library. In contraset to many other database management systems, SQLite is not a client-server database engine. Rather, it is embedded into the end program. This system will call the APIs from SQLite3. Several APIs are listed as follows.

a. Open the sqlite3

Use the function to start the database operation. Need to pass two parameters, one of them is the database file name, such as E:/test.db. The file name does not need to exist, and if the file does not exist, sqlite will automatically create it. If it exists, it will open as a database file. Sqilte3** is the data structure mentioned before.

The return values are listed as follows:

int sqlite3_open(
  const char *filename, /* Database filename */
  sqlite3 **ppDb /*OUT:SQLite db handle*/
)

b. Close the sqlite3

ppDb is the database key opened before.

int sqlite3_close(sqlite3 **ppDb)

c. Execute

This API is quite important

function prototype:

int sqlite3_exec{

sqlite3* ppDb, /*An open database*/

const char *sql, /*SQL to be evaluated*/

int (*callback)(void*,int,char**,char**), /*Callback function*/

void *, /*1st argument to callback*/

char **errmsg /*Error msg written here*/

}
This is a function that executes a sql statement.

The first argument is no longer to talk about, is the pointer opened before.

The second one const char* sql, ending with \ o.

The third parameter sqlite3_callback is the callback function which the SQLite3 will provide the calling API when it is excuted.

The fourth parameter void* is the pointer you provide, The parameter will finally return back to the callback function. You can just fill NULL.

The fifth parameter is to warn the error.

Usually, sqlite3_callback and its behind void* can be fiiled in both locations. Fill in NULL so this means you do not need a callback. For example, the operation of insert, delete, it's no need to call the callback function. But when you call the function select, you need to use the callback which SQLite3 will check the data and tell you the information via the callback.

Multi-thread: 

We apply multi-thread system in LINUX to receive and save data. The flowchart will be given after. But the specific APIs information will not be provided here which can be easily found on the Internet.

Mention the Synchronization and mutual exclusion mechanism. (In this prototype system, the importance of the synchronization appears not so obious.

TCP/IP:  

a. We call APIs such as setsockopt() to set the properties of the socket.

b. The cooperation of C/S system and the operations of the multi-thread and SQLite3 operations is quite key to the robustness.

c. The APIs will not be provided here.

3. Flowchar.

4. Codes

Main file

/* 
 * File      : Recv_sqlite3.c 
 * Change Logs: 
 * Date           Author       Notes 
 * 2016-6-28     Kyrie      the first version 
 * 2014-6-29     Kyrie      make code cleanup.  
 */  
#include "head.h"
#include <signal.h>
#include <memory.h>
#include <pthread.h>
#include <sys/time.h>
#include <sys/ipc.h>
#include <sys/msg.h>
#include <stdio.h>
#include <sqlite3.h>
#include <stdlib.h>
#include <net/if_arp.h>
#include <arpa/inet.h>
#include <errno.h>
#include <sys/socket.h>
#include <sys/ioctl.h>
#include <netinet/in.h>
#include <net/if.h>
#include <sqlite3.h>
#define ETH_NAME	"eth0"
int flag;
char *zErrMsg = 0;
int rc;
sqlite3 *db = 0;
char *ip;
char *IP;
char **result;
void *thrd_fuc(void *arg)
{
  int sockfd1,a,res;
  int f_isbcast;
  char BUF[BUFF_SIZE];
  struct sockaddr_in bcastaddr;
  sprintf(BUF,"\nIP %s \n %s %s",IP,result[0],result[1]);
 
	if (-1 == (sockfd1 = socket(AF_INET, SOCK_DGRAM, 0)))
	{
		perror("socket");
		exit(EXIT_FAILURE);
	}

	f_isbcast = 1;
	if(-1==setsockopt(sockfd1, SOL_SOCKET, SO_BROADCAST, &f_isbcast, sizeof(f_isbcast)))
	{
		perror("setsockopt");
		exit(EXIT_FAILURE);
	}

  bcastaddr.sin_family = AF_INET;
  bcastaddr.sin_port = htons(50000);
  bcastaddr.sin_addr.s_addr = inet_addr(ip);
  
  printf("flag=%d\n",flag); 

  printf("ss\n");
    if(-1 == sendto(sockfd1, BUF, strlen(BUF), 0, (struct sockaddr *)&bcastaddr, sizeof(bcastaddr)))
    {
		perror("setsockopt");
		exit(EXIT_FAILURE);
    }
    
	printf("send success %s\n",BUF);
   
    flag=1;
  
	printf("over");
	pthread_exit(NULL);
} 


int main()
{
 int sock;
 int sockfd,res;
 int i = 0 ;
 void * thrd_ret;
 pthread_t thread;
 int row = 0, column = 0;
 char **azResult; 
 struct sockaddr_in peeraddr,
                   bcast,
				bcastaddr;
 char buff[BUFF_SIZE];
 char buf[BUFF_SIZE];
 int nbytes;
 char sql[100]; 
 struct sockaddr_in sin;
 struct ifreq ifr;
 sock = socket(AF_INET, SOCK_DGRAM, 0);
 memset(sql,0,100);
 
 rc = sqlite3_open("Openmqtt.db", &db); 
 
	if(rc!=SQLITE_OK)//#SQLITE_OK in C/C++ means 0
	{
        fprintf(stderr,"Can't open database: %s\n",sqlite3_errmsg(db));
        sqlite3_close(db);
        exit(1);
	}
    else 
	{
		printf("You have opened a sqlite3 database! \n");
	}

    #ifdef _DEBUG_ 
    #endif 

      
	socklen_t addrlen = sizeof(struct sockaddr);//address length

	if(-1 == (sockfd=socket(AF_INET, SOCK_DGRAM, 0)))//
	{
		perror("socket");
		exit(EXIT_FAILURE);
	}
 
	bcast.sin_family = AF_INET;
	bcast.sin_port = htons(BCAST_PORT);
	bcast.sin_addr.s_addr = inet_addr(BCAST_ADDR);

	if(-1==bind(sockfd, (struct sockaddr *)&bcast, sizeof(bcast)))
	{
		perror("bind");
		exit(EXIT_FAILURE);
	}
 
	while(1)
	{
		memset(buff, 0, BUFF_SIZE);
		if(0>=(nbytes=recvfrom(sockfd,buff,BUFF_SIZE, 0, (struct sockaddr *)&peeraddr, &addrlen)))
		{
			perror("recvfrom");
			exit(EXIT_FAILURE);
		}
		printf("IP is %s\n",inet_ntoa(peeraddr.sin_addr));
		printf("[%d]%s\n",nbytes, buff);
		ip=inet_ntoa(peeraddr.sin_addr);
		printf("ip[] = %s\n",ip);
		flag=0;

		strncpy(ifr.ifr_name, ETH_NAME, IFNAMSIZ);
	
		ifr.ifr_name[IFNAMSIZ - 1] = 0;

		if (ioctl(sock, SIOCGIFADDR, &ifr) < 0)
		{
			perror("ioctl");
			return -1;
		}

		memcpy(&sin, &ifr.ifr_addr, sizeof(sin));
		fprintf(stdout, "eth0: %s\n", inet_ntoa(sin.sin_addr));
        IP=inet_ntoa(sin.sin_addr);
    
		strcpy(sql,"select * from Openmqtt");
		sqlite3_get_table(db,sql,&result,&row,&column,&zErrMsg);
		int i=0; 
		printf("row:%d column:%d\n",row,column);    
   
		printf("result[%d]=%s\n",i,result[i]);

		if((res=pthread_create(&thread, NULL, thrd_fuc, NULL))!=0)
		{
		printf("create failed\nerror number is = %d\n",res);
		exit(1); 
		}
		
		printf("thread being created!\n");

		sqlite3_free_table(result);
		sprintf(buf,"IP %s","hahaha");

    #ifdef _DEBUG_ 
    #endif
  
	}
  close(sockfd);
 
  return 0;
}

Header

/* 
 * File      : head.h
 * Change Logs: 
 * Date           Author       Notes 
 * 2014-6-28     Kyrie      make code cleanup.  
 */ 
#ifndef _HEAD_H_
#define _HEAD_H_

#include <sys/socket.h>
#include <sys/types.h>
#include <arpa/inet.h>

#include <netinet/in.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <unistd.h>

#define BCAST_PORT 50001
#define BCAST_ADDR "192.168.l.255"

#define BUFF_SIZE 1024


#endif 

Author: Kyrie