//-------------------------------------------------------------------------------------
bool DBInterfaceMysql::getTableNames(std::vector<std::string>& tableNames, const char * pattern)
{
if(pMysql_ == NULL)
{
ERROR_MSG("DBInterfaceMysql::query: has no attach(db).\n");
return false;
}
tableNames.clear();
MYSQL_RES * pResult = mysql_list_tables(pMysql_, pattern);
if(pResult)
{
tableNames.reserve((unsigned int)mysql_num_rows(pResult));
MYSQL_ROW row;
while((row = mysql_fetch_row(pResult)) != NULL)
{
unsigned long *lengths = mysql_fetch_lengths(pResult);
tableNames.push_back(std::string(row[0], lengths[0]));
}
mysql_free_result(pResult);
}
return true;
}
void kGUIDbTablesListWindowObj::NewTable2(kGUIString *s,int closebutton)
{
if(s->GetLen())
{
kGUIDbCommand *c;
MYSQL_RES *res_set;
MYSQL_ROW row;
c=new kGUIDbCommand(m_dbobj,"CREATE TABLE %s (id INT);",s->GetString());
m_table.DeleteChildren();
/* repopulate the table */
res_set=mysql_list_tables(m_dbobj->GetConn(), 0);
/* populate table */
while ((row = mysql_fetch_row(res_set)) != NULL)
{
kGUIDbTablesListRowObj *robj=new kGUIDbTablesListRowObj(m_dbobj);
robj->SetName(row[0]);
m_table.AddRow(robj);
}
mysql_free_result(res_set);
/* automatically calculate */
m_table.CalculateColWidths();
}
}
static GtCstrTable* gt_rdb_mysql_get_tables(GtRDB *rdb, GtError *err)
{
GtRDBMySQL *rdbm;
MYSQL_RES *res;
MYSQL_ROW row;
GtCstrTable *tab;
gt_assert(rdb);
gt_error_check(err);
rdbm = gt_rdb_mysql_cast(rdb);
gt_assert(&rdbm->conn);
res = mysql_list_tables(&rdbm->conn, NULL); /* NULL means 'all tables' */
if (!res) {
gt_error_set(err, "error trying to list tables: %s",
mysql_error(&rdbm->conn));
return NULL;
}
tab = gt_cstr_table_new();
while ((row = mysql_fetch_row(res))) {
char buf[BUFSIZ];
GtUword *lengths;
memset(buf, 0, BUFSIZ);
lengths = mysql_fetch_lengths(res);
(void) snprintf(buf, MIN(BUFSIZ, lengths[0])*sizeof (char), "%s",
(char*) row[0] ? (char*) row[0] : "NULL");
gt_cstr_table_add(tab, buf);
}
mysql_free_result(res);
return tab;
}
/**
* get all tableNames in database that matches with the wildcard "h%"
**/
int getTables(IpfixDbReader* ipfixDbReader)
{
DbData* dbData = ipfixDbReader->dbReader->dbData;
int i = 0;
char* wild = "h%";
MYSQL_RES* dbResult = NULL;
MYSQL_ROW dbRow = NULL;
dbResult = mysql_list_tables(ipfixDbReader->conn, wild);
if(dbResult == 0) {
msg(MSG_FATAL,"There are no flow tables in database %s", ipfixDbReader->dbName);
return 1;
}
if(mysql_num_rows(dbResult) > MAX_TABLES) {
msg(MSG_ERROR,"There are too many flow tables in the database. Only the first MAX_TABLES=%i tables can be read.", MAX_TABLES);
}
while(( dbRow = mysql_fetch_row(dbResult)) && i < MAX_TABLES) {
char *table = (char*)malloc(sizeof(char) * TABLE_WIDTH);
strcpy(table,dbRow[0]);
dbData->tableNames[i] = table;
dbData->tableCount++;
i++;
}
mysql_free_result(dbResult);
return 0;
}
bool MysqlDatabase::exists(void) {
bool ret = false;
if ( conn == NULL || mysql_ping(conn) )
{
CLog::Log(LOGERROR, "Not connected to database, test of existence is not possible.");
return ret;
}
MYSQL_RES* result = mysql_list_dbs(conn, db.c_str());
if (result == NULL)
{
CLog::Log(LOGERROR,"Database is not present, does the user has CREATE DATABASE permission");
return false;
}
ret = (mysql_num_rows(result) > 0);
mysql_free_result(result);
// Check if there is some tables ( to permit user with no create database rights
if (ret)
{
result = mysql_list_tables(conn, NULL);
if (result != NULL)
ret = (mysql_num_rows(result) > 0);
mysql_free_result(result);
}
return ret;
}
void kGUIDb::Connect(const char *servername,const char *dbname,const char *username,const char *password)
{
m_servername.SetString(servername);
m_dbname.SetString(dbname);
m_username.SetString(username);
m_password.SetString(password);
m_conn = mysql_init(NULL);
if(!mysql_real_connect(m_conn,servername,username,password,dbname,0,NULL,0))
{
m_isconnected=false;
// char errorstring[256];
// sprintf(errorstring,"Failed to connect to database: Error: %s\n",mysql_error(m_conn));
// printf("%s",errorstring);
// return;
}
else
{
MYSQL_RES *res;
int z=0;
const char **row;
m_isconnected=true;
/* add all tablenames to a hashtable with a locked count of 0 */
res=mysql_list_tables(m_conn,0); /* get list of tablenames */
/* populate table */
while ((row = (const char **)mysql_fetch_row(res)) != NULL)
m_lockedtables.Add(row[0],&z); /* add tablename to locked list */
mysql_free_result(res);
}
}
kGUIDbTablesListWindowObj::kGUIDbTablesListWindowObj(kGUIDb *dbobj)
{
MYSQL *conn;
MYSQL_RES *res_set;
MYSQL_ROW row;
m_dbobj=dbobj;
SetEventHandler(this,CALLBACKNAME(WindowEvent));
SetTitle("Database tables");
SetPos(50,50);
SetSize(600,500);
m_def.SetPos(25,10);
m_def.SetSize(150,20);
m_def.SetString("Design View");
m_def.SetEventHandler(this,CALLBACKNAME(Design));
AddObject(&m_def);
m_data.SetPos(200,10);
m_data.SetSize(150,20);
m_data.SetString("Data View");
m_data.SetEventHandler(this,CALLBACKNAME(Data));
AddObject(&m_data);
m_newtable.SetPos(400,10);
m_newtable.SetSize(150,20);
m_newtable.SetString("New Table");
m_newtable.SetEventHandler(this,CALLBACKNAME(NewTable));
AddObject(&m_newtable);
m_table.SetPos(0,50);
m_table.SetSize(400,400);
m_table.SetNumCols(1);
m_table.SetColTitle(0,"Name");
m_table.SetColWidth(0,200);
m_table.SetEventHandler(this,&CALLBACKNAME(WindowEvent));
m_table.SetAllowAddNewRow(true);
AddObject(&m_table);
conn=dbobj->GetConn();
res_set=mysql_list_tables(conn, 0);
/* populate table */
while ((row = mysql_fetch_row(res_set)) != NULL)
{
kGUIDbTablesListRowObj *robj=new kGUIDbTablesListRowObj(dbobj);
robj->SetName(row[0]);
m_table.AddRow(robj);
}
mysql_free_result(res_set);
/* automatically calculate */
//m_table.CalculateColWidths();
kGUI::AddWindow(this);
}
static JSVAL list_tables(JSARGS args) {
HandleScope scope;
MYSQL *handle = (MYSQL *) args[0]->IntegerValue();
const char *wild = NULL;
if (args.Length() > 1) {
String::Utf8Value pat(args[1]->ToString());
wild = *pat;
}
return scope.Close(Integer::New((unsigned long)mysql_list_tables(handle, wild)));
}
static double apop_mysql_table_exists(char *table, int delme){
MYSQL_RES *res_set = mysql_list_tables(mysql_db, table);
if (!mysql_list_tables(mysql_db, table)){
print_error (mysql_db, "show tables query failed.");
return GSL_NAN;
}
int is_found = mysql_num_rows(res_set);
mysql_free_result(res_set);
if (!is_found)
return 0;
if (delme){
int len = 100+strlen(table);
char *a_query = malloc(len);
snprintf(a_query, len, "drop table %s", table);
if (mysql_query (mysql_db, a_query))
print_error (mysql_db, "table exists, but table dropping failed");
}
return 1;
}
// MYSQL_RES *mysql_list_tables(MYSQL *mysql, const char *wild)
static IDL_VPTR IDL_mg_mysql_list_tables(int argc, IDL_VPTR *argv) {
char *wildcard = NULL;
if (argc > 1) {
if (argv[1]->type == IDL_TYP_STRING) {
wildcard = IDL_VarGetString(argv[1]);
}
}
MYSQL_RES *result = mysql_list_tables((MYSQL *)argv[0]->value.ptrint,
wildcard);
return IDL_GettmpMEMINT((IDL_MEMINT) result);
}
void do_storetemp(int cfd,MYSQL *con,char **arg)
{
MYSQL_RES * res;
MYSQL_ROW row;
printf("do_storetemp is execed!\n");
unsigned char databuf[200] = {0};
unsigned char sql[50] = "insert into ";
int count = 0;
char timebuf[10];
unsigned short value = 0;
mysql_select_db(con,"temp");
while(1)
{
cgetdate(timebuf);
res = mysql_list_tables(con,timebuf);
row = mysql_fetch_row(res);
if(!row)
{
char csql[50] = "create table ";
strcat(csql,timebuf);
strcat(csql,"(time time,value int)");
mysql_query(con,csql);
}
recv(cfd,databuf,7,0);
count = databuf[6];
count <<= 8;
count |= databuf[5];
printf("count:%d\n",count);
recv(cfd,databuf,200,0);
printf("recv!\n");
for(int ii = 0; ii < count*2; ii+=2)
{
value = databuf[ii+1];
value <<= 8;
value |= databuf[ii];
value %= 100;
printf("pos:%d\n",ii);
printf("value:%d\n",value);
strcat(sql,timebuf);
strcat(sql," values(now(), ");
sql[32] = value %10+0x30;
sql[31] = value /10+0x30;
sql[33] = ')';
sql[34] = '\0';
printf("sql:%s\n",sql);
mysql_query(con,sql);
}
printf("\n\n\n");
}
}
static Variant HHVM_FUNCTION(mysql_list_tables, const String& database,
const Variant& link_identifier /* = null */) {
MYSQL *conn = MySQL::GetConn(link_identifier);
if (!conn) return false;
if (mysql_select_db(conn, database.data())) {
return false;
}
MYSQL_RES *res = mysql_list_tables(conn, nullptr);
if (!res) {
raise_warning("Unable to save MySQL query result");
return false;
}
return Variant(makeSmartPtr<MySQLResult>(res));
}
ResultSet* myconn_listTablesMatching(Connection *conn, char *pattern){
int i;
MYSQL_FIELD *field;
ResultSet *rset;
rset = myrset_createDefault(conn, mysql_list_tables(conn->stream, pattern));
for(i=0,field = mysql_fetch_field(rset->res);field != NULL;i++,field = mysql_fetch_field(rset->res)){
rset->column_names[i] = estrdup(field->name);
rset->column_types[i] = field->type;
}
return rset;
}
Variant f_mysql_list_tables(const String& database,
CVarRef link_identifier /* = null */) {
MYSQL *conn = MySQL::GetConn(link_identifier);
if (!conn) return false;
if (mysql_select_db(conn, database.data())) {
return false;
}
MYSQL_RES *res = mysql_list_tables(conn, NULL);
if (!res) {
raise_warning("Unable to save MySQL query result");
return false;
}
return Resource(NEWOBJ(MySQLResult)(res));
}
//判断数据表是否存在
bool DbMysql::table_exits(const char* table_name)
{
if(table_name == nullptr)
return false;
MYSQL_ROW row = nullptr;
MYSQL_RES *res = mysql_list_tables(conn_, nullptr);
if(res){
while((row = mysql_fetch_row(res)) != nullptr){
if(strcmp(table_name, row[0]) == 0)
return true;
}
mysql_free_result(res);
}
return false;
}
int Mysql::Obtener_tablas(wxChoice tablas[]){
if((resultado = mysql_list_tables(sock,NULL))){
tablas->Clear();//Limpiamos todos los item del choice para agrgar nuevos
c = (int) mysql_num_fields(resultado);
while((fila = mysql_fetch_row(resultado))){
for(int j = 0 ; j < c ; j++){
(fila[j]==NULL) ? tablas->Append(wxT("NULL")) : tablas->Append(wxString::FromAscii(fila[j]));
}
}
mysql_free_result(resultado); // Liberar el resultado de la consulta
return 1;
}
else{
return 0;
}
}
bool TableManager::reloadAllTables(MYSQL *mysql_conn)
{
MYSQL_RES *table_res = mysql_list_tables( mysql_conn,NULL );
if( !table_res )
{
Global::logger->debug( "mysql_list_table fail" );
return false;
}
MYSQL_ROW row;
deleteAll();
while((row=mysql_fetch_row(table_res)))
{
addNewTable(mysql_conn,row[0]);
}
mysql_free_result(table_res);
return true;
}
int main(int argc, char **argv)
{
int count, num;
MYSQL mysql,*sock;
MYSQL_RES *res;
char qbuf[160];
if (argc != 2)
{
fprintf(stderr,"usage : select_test <dbname>\n\n");
exit(1);
}
if (!(sock = mysql_connect(&mysql,NULL,0,0)))
{
fprintf(stderr,"Couldn't connect to engine!\n%s\n\n",mysql_error(&mysql));
perror("");
exit(1);
}
mysql.reconnect= 1;
if (mysql_select_db(sock,argv[1]) < 0)
{
fprintf(stderr,"Couldn't select database %s!\n%s\n",argv[1],
mysql_error(sock));
exit(1);
}
if (!(res=mysql_list_dbs(sock,NULL)))
{
fprintf(stderr,"Couldn't list dbs!\n%s\n",mysql_error(sock));
exit(1);
}
mysql_free_result(res);
if (!(res=mysql_list_tables(sock,NULL)))
{
fprintf(stderr,"Couldn't list tables!\n%s\n",mysql_error(sock));
exit(1);
}
mysql_free_result(res);
mysql_close(sock);
exit(0);
return 0;
}
bool wxMySQL::EnumTables(wxArrayString & array, wxString wildchar)
{
MYSQL_RES * res = mysql_list_tables(m_pDB, wildchar);
MYSQL_ROW row;
bool success(true);
array.Clear();
do
{
if(!res)
{
success = false;
break;
}
while(row = mysql_fetch_row(res)) array.Add(row[0]);
mysql_free_result(res);
}
while(false);
m_LastErrorMessage = mysql_error(m_pDB);
return success;
}
/* list_tables(table=nil) */
static VALUE list_tables(int argc, VALUE* argv, VALUE obj)
{
VALUE table;
MYSQL* m = GetHandler(obj);
MYSQL_RES* res;
unsigned int i, n;
VALUE ret;
rb_scan_args(argc, argv, "01", &table);
res = mysql_list_tables(m, NILorSTRING(table));
if (res == NULL)
mysql_raise(m);
n = mysql_num_rows(res);
ret = rb_ary_new2(n);
for (i=0; i<n; i++)
rb_ary_store(ret, i, rb_tainted_str_new2(mysql_fetch_row(res)[0]));
mysql_free_result(res);
return ret;
}
/**
* get all tables in database that matches with the wildcard "h\_%"
**/
int IpfixDbReader::getTables()
{
const char* wild = "h\\_%";
MYSQL_RES* dbResult = NULL;
MYSQL_ROW dbRow = NULL;
dbResult = mysql_list_tables(conn, wild);
if(dbResult == 0) {
msg(MSG_FATAL,"IpfixDbReader: There are no flow tables in database");
} else {
while((dbRow = mysql_fetch_row(dbResult))) {
tables.push_back(string(dbRow[0]));
msg(MSG_VDEBUG, "IpfixDbReader: table %s", tables.back().c_str());
}
}
mysql_free_result(dbResult);
return 0;
}
QStringList QMYSQLDriver::tables( const QString& typeName ) const
{
QStringList tl;
if ( !isOpen() )
return tl;
if ( !typeName.isEmpty() && !(typeName.toInt() & (int)QSql::Tables) )
return tl;
MYSQL_RES* tableRes = mysql_list_tables( d->mysql, NULL );
MYSQL_ROW row;
int i = 0;
while ( tableRes && TRUE ) {
mysql_data_seek( tableRes, i );
row = mysql_fetch_row( tableRes );
if ( !row )
break;
tl.append( QString(row[0]) );
i++;
}
mysql_free_result( tableRes );
return tl;
}
/* read tables and return array of names */
int kGUIDb::ReadTables(kGUIString **names)
{
MYSQL_RES *res_set;
int i,num;
MYSQL_ROW row;
res_set=mysql_list_tables(GetConn(), 0);
num=(int)mysql_num_rows(res_set);
if(!num)
names[0]=0;
else
{
names[0]=new kGUIString[num];
/* populate table */
for(i=0; i<num; ++i)
{
row=mysql_fetch_row(res_set);
names[0][i].SetString(row[0]);
}
}
mysql_free_result(res_set);
return(num);
}
//---------------------------------------------------------------------------
bool __fastcall TForm1::get_tables(String db_name)
{
MYSQL_RES *res;
AnsiString s_cmd;
TableView->Items->Clear();
s_cmd = "use ";
s_cmd+= db_name.c_str();
if (mysql_query(MySQL, s_cmd.c_str()) ||
!(res=mysql_list_tables(MySQL,"%")))
return false;
tables_node = TableView->Items->Add(NULL, db_name.c_str());
tables_node->ImageIndex = 1;
tables_node->SelectedIndex = 1;
deque<string> rows = fill_rows(res);
mysql_free_result(res);
fill_tree(rows,tables_tree,tables_node,TableView,2);
return true;
}
bool AMySQLServer::getTables(VECTOR_AString& sv, AString& error)
{
if (!isInitialized())
{
error.assign("Database has not been initialized;");
return false;
}
sv.clear();
MYSQL_RES *pmyresult = mysql_list_tables(mp_mydata, NULL);
if (pmyresult)
{
MYSQL_ROW myrow;
int iSize = (int)mysql_num_rows(pmyresult);
for (int i=0; i < iSize; ++i)
{
myrow = mysql_fetch_row(pmyresult);
if (myrow)
{
sv.push_back(myrow[0]);
}
}
mysql_free_result(pmyresult);
}
else
{
error = "Error(";
error += mysql_error(mp_mydata);
error += ") looking for tables;";
return false;
}
return true;
}
int
main( int argc, char * argv[] )
{
char szSQL[ 200 ], aszFlds[ 25 ][ 25 ], szDB[ 50 ] ;
const char * pszT;
int i, j, k, l, x ;
MYSQL * myData ;
MYSQL_RES * res ;
MYSQL_FIELD * fd ;
MYSQL_ROW row ;
//....just curious....
printf( "sizeof( MYSQL ) == %d\n", sizeof( MYSQL ) ) ;
if ( argc == 2 )
{
strcpy( szDB, argv[ 1 ] ) ;
strcpy( szSQL, DEFALT_SQL_STMT ) ;
if (!strcmp(szDB,"--debug"))
{
strcpy( szDB, "mysql" ) ;
printf("Some mysql struct information (size and offset):\n");
printf("net:\t%3d %3d\n",sizeof(myData->net),offsetof(MYSQL,net));
printf("host:\t%3d %3d\n",sizeof(myData->host),offsetof(MYSQL,host));
printf("port:\t%3d %3d\n",sizeof(myData->port),offsetof(MYSQL,port));
printf("protocol_version:\t%3d %3d\n",sizeof(myData->protocol_version),
offsetof(MYSQL,protocol_version));
printf("thread_id:\t%3d %3d\n",sizeof(myData->thread_id),
offsetof(MYSQL,thread_id));
printf("affected_rows:\t%3d %3d\n",sizeof(myData->affected_rows),
offsetof(MYSQL,affected_rows));
printf("packet_length:\t%3d %3d\n",sizeof(myData->packet_length),
offsetof(MYSQL,packet_length));
printf("status:\t%3d %3d\n",sizeof(myData->status),
offsetof(MYSQL,status));
printf("fields:\t%3d %3d\n",sizeof(myData->fields),
offsetof(MYSQL,fields));
printf("field_alloc:\t%3d %3d\n",sizeof(myData->field_alloc),
offsetof(MYSQL,field_alloc));
printf("free_me:\t%3d %3d\n",sizeof(myData->free_me),
offsetof(MYSQL,free_me));
printf("options:\t%3d %3d\n",sizeof(myData->options),
offsetof(MYSQL,options));
puts("");
}
}
else if ( argc > 2 ) {
strcpy( szDB, argv[ 1 ] ) ;
strcpy( szSQL, argv[ 2 ] ) ;
}
else {
strcpy( szDB, "mysql" ) ;
strcpy( szSQL, DEFALT_SQL_STMT ) ;
}
//....
if ( (myData = mysql_init((MYSQL*) 0)) &&
mysql_real_connect( myData, NULL, NULL, NULL, NULL, MYSQL_PORT,
NULL, 0 ) )
{
if ( mysql_select_db( myData, szDB ) < 0 ) {
printf( "Can't select the %s database !\n", szDB ) ;
mysql_close( myData ) ;
return 2 ;
}
}
else {
printf( "Can't connect to the mysql server on port %d !\n",
MYSQL_PORT ) ;
mysql_close( myData ) ;
return 1 ;
}
//....
if ( ! mysql_query( myData, szSQL ) ) {
res = mysql_store_result( myData ) ;
i = (int) mysql_num_rows( res ) ; l = 1 ;
printf( "Query: %s\nNumber of records found: %ld\n", szSQL, i ) ;
//....we can get the field-specific characteristics here....
for ( x = 0 ; fd = mysql_fetch_field( res ) ; x++ )
strcpy( aszFlds[ x ], fd->name ) ;
//....
while ( row = mysql_fetch_row( res ) ) {
j = mysql_num_fields( res ) ;
printf( "Record #%ld:-\n", l++ ) ;
for ( k = 0 ; k < j ; k++ )
printf( " Fld #%d (%s): %s\n", k + 1, aszFlds[ k ],
(((row[k]==NULL)||(!strlen(row[k])))?"NULL":row[k])) ;
puts( "==============================\n" ) ;
}
mysql_free_result( res ) ;
}
else printf( "Couldn't execute %s on the server !\n", szSQL ) ;
//....
puts( "==== Diagnostic info ====" ) ;
pszT = mysql_get_client_info() ;
printf( "Client info: %s\n", pszT ) ;
//....
pszT = mysql_get_host_info( myData ) ;
printf( "Host info: %s\n", pszT ) ;
//....
pszT = mysql_get_server_info( myData ) ;
printf( "Server info: %s\n", pszT ) ;
//....
res = mysql_list_processes( myData ) ; l = 1 ;
if (res)
{
for ( x = 0 ; fd = mysql_fetch_field( res ) ; x++ )
strcpy( aszFlds[ x ], fd->name ) ;
while ( row = mysql_fetch_row( res ) ) {
j = mysql_num_fields( res ) ;
printf( "Process #%ld:-\n", l++ ) ;
for ( k = 0 ; k < j ; k++ )
printf( " Fld #%d (%s): %s\n", k + 1, aszFlds[ k ],
(((row[k]==NULL)||(!strlen(row[k])))?"NULL":row[k])) ;
puts( "==============================\n" ) ;
}
}
else
{
printf("Got error %s when retreiving processlist\n",mysql_error(myData));
}
//....
res = mysql_list_tables( myData, "%" ) ; l = 1 ;
for ( x = 0 ; fd = mysql_fetch_field( res ) ; x++ )
strcpy( aszFlds[ x ], fd->name ) ;
while ( row = mysql_fetch_row( res ) ) {
j = mysql_num_fields( res ) ;
printf( "Table #%ld:-\n", l++ ) ;
for ( k = 0 ; k < j ; k++ )
printf( " Fld #%d (%s): %s\n", k + 1, aszFlds[ k ],
(((row[k]==NULL)||(!strlen(row[k])))?"NULL":row[k])) ;
puts( "==============================\n" ) ;
}
//....
pszT = mysql_stat( myData ) ;
puts( pszT ) ;
//....
mysql_close( myData ) ;
return 0 ;
}
int main(int argc, char* argv[])
{
char sPGhost[26], sPGport[26], sPGdb[26], sPGuser[26], sPGpass[26];
printf("Postgres connection settings\n Host>");
scanf("%s", sPGhost);
printf(" Port>");
scanf("%s", sPGport);
printf(" Base>");
scanf("%s", sPGdb);
printf(" User>");
scanf("%s", sPGuser);
printf(" Pass>");
scanf("%s", sPGpass);
///////////////////////////////
///////PGSQL Connect///////////
///////////////////////////////
PGconn* mPGconn = NULL;
mPGconn = PQsetdbLogin(sPGhost, sPGport, NULL, NULL, sPGdb, sPGuser, sPGpass);
if (PQstatus(mPGconn) != CONNECTION_OK)
{
printf("Could not connect to Postgre database at [%s]: \n %s\n", sPGhost, PQerrorMessage(mPGconn));
PQfinish(mPGconn);
return 1;
}
else
{
printf("Connected to Postgre database at [%s]\n", sPGhost);
printf(" PostgreSQL server ver: [%d]\n\n", PQserverVersion(mPGconn));
}
/// Set dummy notice processor
PQsetNoticeProcessor(mPGconn, pg_notice, mPGconn);
///////////////////////////////
///////MySQL Connect///////////
///////////////////////////////
MYSQL* mysqlInit;
mysqlInit = mysql_init(NULL);
if (!mysqlInit)
{
printf("Could not initialize Mysql connection\n");
return 1;
}
char sMYhost[26], sMYdb[26], sMYuser[26], sMYpass[26];
int iMYport;
printf("Mysql connection settings \n Host>");
scanf("%s", sMYhost);
printf(" Port>");
scanf("%d", &iMYport);
printf(" Base>");
scanf("%s", sMYdb);
printf(" User>");
scanf("%s", sMYuser);
printf(" Pass>");
scanf("%s", sMYpass);
mysql_options(mysqlInit, MYSQL_SET_CHARSET_NAME, "utf8");
MYSQL* mMysql;
mMysql = mysql_real_connect(mysqlInit, sMYhost, sMYuser, sMYpass, sMYdb, iMYport, NULL, 0);
if (mMysql)
{
printf("Connected to MySQL database at [%s] \n", sMYhost);
printf(" MySQL client library: [%s] \n", mysql_get_client_info());
printf(" MySQL server ver: [%s] \n\n", mysql_get_server_info(mMysql));
}
else
{
printf("Could not connect to MySQL database at [%s]:\n %s\n", sMYhost , mysql_error(mysqlInit));
mysql_close(mysqlInit);
return 1;
}
//////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////////
MYSQL_RES* result = NULL;
MYSQL_ROW row;
MYSQL_FIELD* fields = NULL;
uint64 rowCount = 0;
uint32 fieldCount = 0;
result = mysql_list_tables(mMysql , NULL);
rowCount = mysql_num_rows(result);
/***********************/
/* get list of tables */
/***********************/
T_TableList mTableList;
mTableList.reserve((size_t)rowCount);
while ((row = mysql_fetch_row(result)) != NULL)
{
for (uint32 i = 0; i < mysql_num_fields(result); i++)
{
mTableList.push_back(row[i]);
}
}
mysql_free_result(result);
/****************************************/
/* convert filed type and default type */
/****************************************/
T_Table m_Table;
TDataBase m_DataBase_Map;
m_DataBase_Map.clear();
for (uint32 j = 0; j < mTableList.size(); ++j)
{
result = mysql_list_fields(mMysql, mTableList[j].c_str(), NULL);
fieldCount = mysql_num_fields(result);
fields = mysql_fetch_fields(result);
for (uint32 i = 0; i < fieldCount; ++i)
{
sField mfield;
mfield.name = fields[i].name;
if (!fields[i].def)
{
mfield.def = "NULL";
}
else if (!strcmp(fields[i].def, "0000-00-00 00:00:00"))
{
/// Convert MySQL Default timestamp to PGSQL Default timestamp
mfield.def.append("'1970-01-01 00:00:00'");
}
else
{
/// Append '
mfield.def.append("'");
mfield.def.append(fields[i].def);;
mfield.def.append("'");
}
mfield.type = ConvertNativeType(fields[i].type, fields[i].length);
mfield.flags = fields[i].flags;
m_Table.push_back(mfield);
}
m_DataBase_Map[mTableList[j]] = m_Table;
m_Table.clear();
mysql_free_result(result);
}
/******************************************/
/* Conversion of the layout of the tables */
/******************************************/
uint32 count = 0;
TDataBase::const_iterator citr;
for (citr = m_DataBase_Map.begin(); citr != m_DataBase_Map.end(); ++citr)
{
ostringstream sql_str;
sql_str << "DROP TABLE IF EXISTS " << (*citr).first.c_str() << ";\n";
sql_str << "CREATE TABLE " << (*citr).first.c_str() << "(\n";
T_Table::const_iterator v_iter;
ostringstream prim_key_str;
ostringstream index_str;
for (v_iter = (*citr).second.begin();
v_iter != (*citr).second.end();
++v_iter)
{
sql_str << " " << (*v_iter).name;
if (((*v_iter).flags & AUTO_INCREMENT_FLAG) != 0)
{
/// AUTO_INCREMENT fields not have "default" data
sql_str << " bigserial";
}
else
{
sql_str << " " << (*v_iter).type;
sql_str << " default " << (*v_iter).def;
}
/// IF column have PRIMARY KEY flag then use column in PRIMARY KEY
if (IS_PRI_KEY((*v_iter).flags) != 0)
{
if (prim_key_str.str().size())
prim_key_str << ", ";
else
{
prim_key_str << "ALTER TABLE ";
prim_key_str << (*citr).first.c_str();
prim_key_str << " ADD CONSTRAINT pk_";
prim_key_str << (*citr).first.c_str();
prim_key_str << "_";
prim_key_str << (*v_iter).name;
prim_key_str << " PRIMARY KEY (";
}
prim_key_str << (*v_iter).name;
}
else if (((*v_iter).flags & MULTIPLE_KEY_FLAG) != 0)
{
/// IF column have INDEX flag then create INDEX
index_str << "CREATE INDEX idx_";
index_str << (*citr).first.c_str();
index_str << "_";
index_str << (*v_iter).name;
index_str << " ON ";
index_str << (*citr).first.c_str();
index_str << " USING btree (";
index_str << (*v_iter).name;
index_str << ");\n";
}
else if (((*v_iter).flags & UNIQUE_KEY_FLAG) != 0)
{
/// IF column have UNIQUE INDEX flag then create INDEX
index_str << "CREATE UNIQUE INDEX uidx_";
index_str << (*citr).first.c_str();
index_str << "_";
index_str << (*v_iter).name;
index_str << " ON ";
index_str << (*citr).first.c_str();
index_str << " USING btree (";
index_str << (*v_iter).name;
index_str << ");\n";
}
/// don't output "," for last column
if (v_iter + 1 != (*citr).second.end())
sql_str << ",\n";
else
sql_str << "\n";
}
sql_str << ")\n";
/// Out Table structure
PG_Exec_str(sql_str.str(), mPGconn);
/// out PRIMARY KEY
if (prim_key_str.str().size())
{
prim_key_str << ")";
PG_Exec_str(prim_key_str.str(), mPGconn);
}
/// out INDEX's
if (index_str.str().size())
PG_Exec_str(index_str.str(), mPGconn);
++count;
printf("Convert [%d] tables...\r", count);
}
printf("Completed the conversion of [%d] tables!\n", count);
/****************/
/* Copying data */
/****************/
count = 0;
for (uint32 j = 0; j < mTableList.size(); ++j)
{
ostringstream sql_str;
sql_str << "SELECT * FROM ";
sql_str << mTableList[j].c_str();
if (mysql_query(mysqlInit, sql_str.str().c_str()))
continue;
if (!(result = mysql_store_result(mysqlInit)))
continue;
while ((row = mysql_fetch_row(result)) != NULL)
{
ostringstream insert_str;
insert_str << "INSERT INTO ";
insert_str << mTableList[j].c_str();
insert_str << " VALUES (";
fieldCount = mysql_num_fields(result);
fields = mysql_fetch_fields(result);
for (uint32 i = 0 ; i < fieldCount ; ++i)
{
if (!row[i])
insert_str << "NULL";
else
{
if (IsNeeedEscapeString(fields[i].type))
{
string field_str = row[i];
PG_Escape_Str(field_str);
insert_str << "E'";
insert_str << field_str.c_str();
insert_str << "'";
}
else if (!strcmp(row[i], "0000-00-00 00:00:00"))
{
/// Convert MySQL timestamp to PGSQL timestamp
insert_str << "'1970-01-01 00:00:00'";
}
else
{
insert_str << "'";
insert_str << row[i];
insert_str << "'";
}
}
/// don't output "," for last column
if (i + 1 != fieldCount)
insert_str << ",";
else
insert_str << ")\n";
}
PG_Exec_str(insert_str.str(), mPGconn);
}
mysql_free_result(result);
++count;
printf("Copied data from [%d] tables...\r", count);
}
printf("Finished copying the data from [%d] tables!\n", count);
mTableList.clear();
m_DataBase_Map.clear();
/// Close connections
mysql_close(mMysql);
PQfinish(mPGconn);
printf("end\n");
return 0;
}
int main() {
MYSQL mysql;
mysql_init(&mysql);
//read options from file
mysql_options(&mysql, MYSQL_READ_DEFAULT_FILE, SQLCONFILE); //SQLCONFILE defined in sqloptions.h
mysql_options(&mysql, MYSQL_READ_DEFAULT_GROUP, SQLGROUP);
// connect
if(!mysql_real_connect(&mysql, NULL, NULL, NULL, NULL, 0, NULL, 0)) {
printf("Connection Failure: %s\n", mysql_error(&mysql));
printf("\nThe database specified in TarBrowserConnectOptions.cnf must exist.\n");
mysql_close(&mysql);
return 1;
}
// table strings
char* archivetable = "ArchiveList"; // all tables follow: name, creation string, existence flag
char* createarchivetable = "CREATE TABLE ArchiveList (ArchiveID INT AUTO_INCREMENT, ArchiveName VARCHAR(255), ArchivePath VARCHAR(5000), Timestamp VARCHAR(100), PRIMARY KEY(ArchiveID), UNIQUE(ArchiveName)) ENGINE=InnoDB";
int archivetable_exists = 0;
char* basetar = "UncompTar";
char* createbasetar = "CREATE TABLE UncompTar (FileID INT AUTO_INCREMENT, ArchiveID INT, ArchiveName VARCHAR(255), MemberName VARCHAR(300), MemberPath VARCHAR(5000), GBoffset INT, BYTEoffset BIGINT, MemberLength BIGINT, LinkFlag CHAR(1), DirFlag CHAR(1), Mode INT, Uid INT, Gid INT, LinkTarget VARCHAR(5000), PRIMARY KEY (FileID), FOREIGN KEY(ArchiveID) REFERENCES ArchiveList(ArchiveID)) ENGINE=InnoDB";
int basetar_exists = 0;
char* bzip2_files = "Bzip2_files";
char* create_bzip2_files = "CREATE TABLE Bzip2_files (FileID INT AUTO_INCREMENT, ArchiveID INT, ArchiveName VARCHAR(255), MemberName VARCHAR(300), MemberPath VARCHAR(5000), Blocknumber INT, BlockOffset BIGINT, InsideOffset BIGINT, MemberLength BIGINT, LinkFlag CHAR(1), DirFlag CHAR(1), Mode INT, Uid INT, Gid INT, LinkTarget VARCHAR(5000), PRIMARY KEY (FileID), FOREIGN KEY(ArchiveID) REFERENCES ArchiveList(ArchiveID)) ENGINE=InnoDB";
int bzip2_files_exists = 0;
char* bzip2_blocks = "Bzip2_blocks";
char* create_bzip2_blocks = "CREATE TABLE Bzip2_blocks (ArchiveID INT, ArchiveName VARCHAR(255), Blocknumber INT, BlockOffset BIGINT, BlockSize BIGINT, PRIMARY KEY (ArchiveID, Blocknumber), FOREIGN KEY(ArchiveID) REFERENCES ArchiveList(ArchiveID)) ENGINE=InnoDB";
int bzip2_blocks_exists = 0;
char* compxz = "CompXZ";
char* createcompxz = "CREATE TABLE CompXZ (FileID INT AUTO_INCREMENT, ArchiveID INT, ArchiveName VARCHAR(255), MemberName VARCHAR(300), MemberPath VARCHAR(5000), Blocknumber INT, BlockOffset BIGINT, InsideOffset BIGINT, MemberLength BIGINT, LinkFlag CHAR(1), DirFlag CHAR(1), Mode INT, Uid INT, Gid INT, LinkTarget VARCHAR(5000), PRIMARY KEY (FileID), FOREIGN KEY(ArchiveID) REFERENCES ArchiveList(ArchiveID)) ENGINE=InnoDB";
int compxz_exists = 0;
char* compxz_blocks = "CompXZ_blocks";
char* create_compxz_blocks = "CREATE TABLE CompXZ_blocks (ArchiveID INT, ArchiveName VARCHAR(255), Blocknumber INT, BlockOffset BIGINT, BlockSize BIGINT, PRIMARY KEY (ArchiveID, Blocknumber), FOREIGN KEY(ArchiveID) REFERENCES ArchiveList(ArchiveID)) ENGINE=InnoDB";
int compxz_blocks_exists = 0;
int connection = 2; //2=connected to Tarfiledb, 0=no connection
// if connection is not 0 "mysql" is now connected to Tarfiledb
if(connection) {
printf("Connection Successful to Tarfiledb\n");
// check that required tables exist
MYSQL_RES* response = mysql_list_tables(&mysql, "%");
if(response != NULL) {
MYSQL_ROW row;
while(row = mysql_fetch_row(response)) {
if(strcmp(row[0], archivetable) == 0) archivetable_exists = 1;
if(strcmp(row[0], basetar) == 0) basetar_exists = 1;
if(strcmp(row[0], bzip2_files) == 0) bzip2_files_exists = 1;
if(strcmp(row[0], bzip2_blocks) == 0) bzip2_blocks_exists = 1;
if(strcmp(row[0], compxz) == 0) compxz_exists = 1;
if(strcmp(row[0], compxz_blocks) == 0) compxz_blocks_exists = 1;
}
mysql_free_result(response);
}
else {
printf("Error listing tables");
}
// create non-existant tables
if(!archivetable_exists) {
printf("ArchiveList does not exist, creating\n");
if(mysql_query(&mysql, createarchivetable)) {
printf("Error: %s\n", mysql_error(&mysql));
}
}
if(!basetar_exists) {
printf("UncompTar does not exist, creating\n");
if(mysql_query(&mysql, createbasetar)) {
printf("Error: %s\n", mysql_error(&mysql));
}
}
if(!bzip2_files_exists) {
printf("Bzip2_files does not exist, creating\n");
if(mysql_query(&mysql, create_bzip2_files)) {
printf("Error: %s\n", mysql_error(&mysql));
}
}
if(!bzip2_blocks_exists) {
printf("Bzip2_blocks does not exist, creating\n");
if(mysql_query(&mysql, create_bzip2_blocks)) {
printf("Error: %s\n", mysql_error(&mysql));
}
}
if(!compxz_exists) {
printf("CompXZ does not exist, creating\n");
if(mysql_query(&mysql, createcompxz)) {
printf("Error: %s\n", mysql_error(&mysql));
}
}
if(!compxz_blocks_exists) {
printf("Compxz_blocks does not exist, creating\n");
if(mysql_query(&mysql, create_compxz_blocks)) {
printf("Error: %s\n", mysql_error(&mysql));
}
}
mysql_close(&mysql);
}
}
unsigned int wxMySQL::GetTableCount()
{
MYSQL_RES * res = mysql_list_tables(m_pDB, NULL);
return mysql_num_rows(res);
}
