android - Update app and update existing table with sqlite in a new app version -
i have application has table dates. in 1.0 this, when list appear. version 1.0
i uploaded version goole play store. made new version of app, called 1.1 , now.
i had change date format. problem if downloaded 1.0 play store, , update app, app crash when tap refresh button in app(the big red button, want convert old data new format). want make change in background. otherwise if download new version first, app running without error.
class dbhelper extends sqliteopenhelper { private static final int database_version = 2; private static final string database_name = "driveractivity.db"; private static final string table_worktime = "driver_work_time"; //worktime per day columns private static final string column_id = "_id"; private static final string column_overall_worktime = "worktime"; private static final string column_actual_day = "date"; private static final string activity_table_driver_activity = "driver_activity"; //activitys columns private static final string activity_column_id = "_id"; private static final string activity_column_type = "activity_type"; private static final string activity_column_overall_tract = "tract_time"; private static final string activity_column_actual_day = "date"; private static final string activity_column_actual_time = "activity_timestamp"; private string allworktime; private string driveandotherworktime; private string standbyworktime; private string driveonlytime; private string otheronlytime; private string payeddaytime; private context context; dbhelper(context context, string name, sqlitedatabase.cursorfactory factory, int version) { super(context, database_name, factory, database_version); this.context = context; } @override public void oncreate(sqlitedatabase db) { string query = "create table "+ table_worktime +"("+ column_id +" integer primary key autoincrement, "+ column_overall_worktime +" text, "+ column_actual_day +" text "+ ");"; string query1 = "create table "+ activity_table_driver_activity +"("+ activity_column_id +" integer primary key, "+ activity_column_type +" varchar(100), "+ activity_column_overall_tract +" text, "+ activity_column_actual_day +" text, "+ activity_column_actual_time +" text "+ ");"; try { db.execsql(query); db.execsql(query1); } catch (sqlexception e) { throw e; } } @override public void onupgrade(sqlitedatabase db, int oldversion, int newversion) { try { string alter_query1="alter table "+ activity_table_driver_activity +" rename temp;"; string alter_query2="create table "+ activity_table_driver_activity +"("+ activity_column_id +" integer primary key, "+ activity_column_type +" varchar(100), "+ activity_column_overall_tract +" text, "+ activity_column_actual_day +" text, "+ activity_column_actual_time +" text "+ ");"; string alter_query3="insert into"+ activity_table_driver_activity +"select *, 0 temp;"; string alter_query4="drop table temp;"; db.execsql(alter_query1); db.execsql(alter_query2); db.execsql(alter_query3); db.execsql(alter_query4); } catch (sqlexception e) { throw e; } } public cursor getdatabasealltables() { sqlitedatabase db = getwritabledatabase(); cursor c = db.rawquery("select * "+ activity_table_driver_activity +" 1=1",null); return c; } //this method update old table's values new format public void updateolddatabase() { cursor cursor = getdatabasealltables(); if (cursor.movetofirst()) { { contentvalues values = new contentvalues(); values.put(activity_column_type, cursor.getstring(cursor.getcolumnindex(dbhelper.activity_column_type))); values.put(activity_column_overall_tract, cursor.getstring(cursor.getcolumnindex(dbhelper.activity_column_overall_tract))); values.put(activity_column_actual_day, datecorrectioner(cursor.getstring(cursor.getcolumnindex(dbhelper.activity_column_actual_day)))); values.put(activity_column_actual_time, cursor.getstring(cursor.getcolumnindex(dbhelper.activity_column_actual_time))); sqlitedatabase db = getwritabledatabase(); db.update(activity_table_driver_activity,values,""+ activity_column_id +" = "+ cursor.getint(cursor.getcolumnindex(dbhelper.activity_column_id)),null); } while (cursor.movetonext()); } cursor.close(); } //if has older version of date value, dates in format of 2017-1-1, change //the new format 2017-01-01 public string datecorrectioner(string date) { string[] temp = date.split("-"); string correcteddate; string concatmonth; string concatday; string year = temp[0]; int month = integer.parseint(temp[1]); int day = integer.parseint(temp[2]); if (month<10) concatmonth = "0"+(month); else concatmonth = string.valueof(month); if (day<10) concatday = "0"+day; else concatday = string.valueof(day); correcteddate = year + "-" + concatmonth + "-" + concatday; return correcteddate; } //this method invoke when user tap refresh button(big red button) public arraylist<driveractivity> getallactivity(string fromdate,string todate) { arraylist<driveractivity> datalist; calendar c = calendar.getinstance(); c.add(calendar.date, 0); calendar ctomorrow = calendar.getinstance(); ctomorrow.add(calendar.date,1); simpledateformat format1 = new simpledateformat("yyyy-mm-dd"); string = format1.format(c.gettime()); string tomorrow = format1.format(ctomorrow.gettime()); string from; string to; //here convertion in background, before user query tables wrong date format updateolddatabase(); if(!fromdate.equals("") && !todate.equals("") ) { if(fromdate.equals(todate)) { = fromdate; = (todate.substring(0, todate.length() - 1) + "" + (integer.parseint(todate.substring(todate.length() - 1)) + 1)); } else { = fromdate; = todate; } } else { = now; = tomorrow; } try { sqlitedatabase db = getwritabledatabase(); string query = "select * "+ activity_table_driver_activity + " "+ activity_column_actual_day + " >= '"+ +"' , " + ""+ activity_column_actual_day +" <= '"+ +"' " + "order "+ activity_column_actual_day +" desc,"+ activity_column_actual_time +" desc"; cursor cursor = db.rawquery(query, null); datalist = new arraylist<>(); if (cursor.movetofirst()) { { driveractivity driverac = new driveractivity(); driverac.set_id(cursor.getint(cursor.getcolumnindex(dbhelper.activity_column_id))); driverac.set_tract(cursor.getstring(cursor.getcolumnindex(dbhelper.activity_column_overall_tract))); driverac.set_selectedspinneritem(cursor.getstring(cursor.getcolumnindex(dbhelper.activity_column_type))); driverac.set_todaydate(datecorrectioner(cursor.getstring(cursor.getcolumnindex(dbhelper.activity_column_actual_day)))); driverac.set_actualtime(cursor.getstring(cursor.getcolumnindex(dbhelper.activity_column_actual_time))); datalist.add(driverac); } while (cursor.movetonext()); } db.close(); cursor.close(); } catch (sqliteexception e){throw e;} //i catch here datalist arraylist put out later times , work types //and assign these datas statistic fields. in case can make query one. stringtointsumthenstring(datalist); return datalist; } private void stringtointsumthenstring(arraylist<driveractivity> datas) { arraylist<string> standby = new arraylist<>(); arraylist<string> drive = new arraylist<>(); arraylist<string> other = new arraylist<>(); arraylist<string> payday = new arraylist<>(); string[] overalltracttime = new string[datas.size()]; string[] arrayoftypes = context.getresources().getstringarray(r.array.spinner_array); this.allworktime = ""; this.standbyworktime = ""; this.driveandotherworktime = ""; this.driveonlytime = ""; this.otheronlytime = ""; this.payeddaytime = ""; int overallhourtemp = 0; int overallminutestemp = 0; int overallstandbytimehourtemp = 0; int overallstandbytimeminutetemp = 0; int overalldrivetimehourtemp = 0; int overalldrivetimeminutestemp = 0; int overallonlydrivetimehourtemp = 0; int overallonlydrivetimeminutetemp = 0; int overallonlyothertimehourtemp = 0; int overallonlyothertimeminutetemp = 0; int overallonlypayeddaytimehourtemp = 0; int overallonlypayeddaytimeminutetemp = 0; //selection (int i=0;i<datas.size();i++) { overalltracttime[i] = datas.get(i).get_tract(); if(datas.get(i).get_spinneritemselected().equals(arrayoftypes[1])) { standby.add(datas.get(i).get_tract()); } else if(datas.get(i).get_spinneritemselected().equals(arrayoftypes[0])) { drive.add(datas.get(i).get_tract()); } else if(datas.get(i).get_spinneritemselected().equals(arrayoftypes[2])) { other.add(datas.get(i).get_tract()); } else if(datas.get(i).get_spinneritemselected().equals(arrayoftypes[3])) { payday.add(datas.get(i).get_tract()); } } //convert string overall times integer (string anoveralltracttime : overalltracttime) { string[] separatedarray = anoveralltracttime.split(":"); //i assign temp hour , minute overall temps overallhourtemp += integer.parseint(separatedarray[0]); if (separatedarray.length == 1) { overallminutestemp += 0; } else { overallminutestemp += integer.parseint(separatedarray[1]); } } //convert string standby times integer for(int k=0;k<standby.size();k++) { string[] separatedarray = standby.get(k).split(":"); //i assign temp hour , minute overall temps overallstandbytimehourtemp += integer.parseint(separatedarray[0]); if (separatedarray.length == 1) { overallstandbytimeminutetemp += 0; } else { overallstandbytimeminutetemp += integer.parseint(separatedarray[1]); } } //convert string drive times integer for(int k=0;k<drive.size();k++) { string[] separatedarray = drive.get(k).split(":"); //i assign temp hour , minute overall temps overallonlydrivetimehourtemp += integer.parseint(separatedarray[0]); if (separatedarray.length == 1) { overallonlydrivetimeminutetemp += 0; } else { overallonlydrivetimeminutetemp += integer.parseint(separatedarray[1]); } } //convert string other work times integer for(int k=0;k<other.size();k++) { string[] separatedarray = other.get(k).split(":"); //i assign temp hour , minute overall temps overallonlyothertimehourtemp += integer.parseint(separatedarray[0]); if (separatedarray.length == 1) { overallonlyothertimeminutetemp += 0; } else { overallonlyothertimeminutetemp += integer.parseint(separatedarray[1]); } } //convert string payed day times integer for(int k=0;k<payday.size();k++) { string[] separatedarray = payday.get(k).split(":"); //i assign temp hour , minute overall temps overallonlypayeddaytimehourtemp += integer.parseint(separatedarray[0]); if (separatedarray.length == 1) { overallonlypayeddaytimeminutetemp += 0; } else { overallonlypayeddaytimeminutetemp += integer.parseint(separatedarray[1]); } } //i divide overallminute temp 60 , add result overallhour temp //later concatenate these numbert final overalltime , appeare on screen. //overall time counting overallhourtemp += (overallminutestemp / 60); overallminutestemp = (overallminutestemp % 60); this.allworktime = string.valueof(overallhourtemp) +":"+ string.valueof(overallminutestemp); //standby time counting overallstandbytimehourtemp += (overallstandbytimeminutetemp / 60); overallstandbytimeminutetemp = (overallstandbytimeminutetemp % 60); this.standbyworktime = string.valueof(overallstandbytimehourtemp) +" "+ context.getresources().getstring(r.string.hour) +" "; this.standbyworktime += string.valueof(overallstandbytimeminutetemp) +" "+ context.getresources().getstring(r.string.minute); //drive time counting overalldrivetimehourtemp += ((((overallonlydrivetimehourtemp*60) + overallonlydrivetimeminutetemp) + ((overallonlyothertimehourtemp*60) + overallonlyothertimeminutetemp)) / 60); overalldrivetimeminutestemp = ((((overallonlydrivetimehourtemp*60) + overallonlydrivetimeminutetemp) + ((overallonlyothertimehourtemp*60) + overallonlyothertimeminutetemp)) % 60); this.driveandotherworktime = string.valueof(overalldrivetimehourtemp) +" "+ context.getresources().getstring(r.string.hour) +" "; this.driveandotherworktime += string.valueof(overalldrivetimeminutestemp) +" "+ context.getresources().getstring(r.string.minute); //only drive time counting overallonlydrivetimehourtemp += (overallonlydrivetimeminutetemp / 60); overallonlydrivetimeminutetemp = (overallonlydrivetimeminutetemp % 60); this.driveonlytime = string.valueof(overallonlydrivetimehourtemp) +" "+ context.getresources().getstring(r.string.hour) +" "; this.driveonlytime += string.valueof(overallonlydrivetimeminutetemp) +" "+ context.getresources().getstring(r.string.minute); //only other time counting overallonlyothertimehourtemp += (overallonlyothertimeminutetemp / 60); overallonlyothertimeminutetemp = (overallonlyothertimeminutetemp % 60); this.otheronlytime = string.valueof(overallonlyothertimehourtemp) +" "+ context.getresources().getstring(r.string.hour) +" "; this.otheronlytime += string.valueof(overallonlyothertimeminutetemp) +" "+ context.getresources().getstring(r.string.minute); //only payed free day time counting overallonlypayeddaytimehourtemp += (overallonlypayeddaytimeminutetemp / 60); overallonlypayeddaytimeminutetemp = (overallonlypayeddaytimeminutetemp % 60); this.payeddaytime = string.valueof(overallonlypayeddaytimehourtemp) +" "+ context.getresources().getstring(r.string.hour) +" "; this.payeddaytime += string.valueof(overallonlypayeddaytimeminutetemp) +" "+ context.getresources().getstring(r.string.minute); } and here part of filter fragment quered datas appear
//refresh button button _btnfilterapply = (button) view.findviewbyid(r.id.btnfilterapply); _btnfilterapply.setonclicklistener(new view.onclicklistener() { @override public void onclick(view v) { pattern p = pattern.compile(_dateregex); pattern pa = pattern.compile(_dateregex); matcher ma = p.matcher(_fromdate.gettext().tostring()); matcher mb = pa.matcher(_todate.gettext().tostring()); if( (_fromdate.gettext().tostring().equals("")) && (_todate.gettext().tostring().equals(""))) { toast.maketext(getcontext(),getresources().getstring(r.string.today_list),toast.length_long).show(); } else { if (!ma.matches() && !mb.matches()) { toast.maketext(getcontext(),getresources().getstring(r.string.wrong_date_format),toast.length_long).show(); } } adapter = new customlistadapter(getcontext(), db.getallactivity( _fromdate.gettext().tostring(), _todate.gettext().tostring())); _list.setadapter(adapter); adapter.notifydatasetchanged(); registerforcontextmenu(_list); //here give mainactivity.java result of work time count needed work time in dialog datapasser.getqueryallworktimeresult(db.getallworktime()); _drivetime = db.getdriveandotherworktime(); _standbytime = db.getstandbyworktime(); _onlydrive = db.getdriveonlytime(); _onlyother = db.getotheronlytime(); _onlypayedfreeday = db.getpayeddaytime(); _driveresult.settext(getresources().getstring(r.string.drive_result_time) +" "+ _drivetime); _standbyresult.settext(getresources().getstring(r.string.standby_result) +" "+ _standbytime); _onlydrivetime.settext(getresources().getstring(r.string.only_drive_result_time) +" "+ _onlydrive); _onlyothertime.settext(getresources().getstring(r.string.only_other_result_time) +" "+ _onlyother); _onlypayedfreedaytime.settext(getresources().getstring(r.string.payed_free_day_result) +" "+ _onlypayedfreeday); db.close(); } }); i know not best code you've ever seen, want improve skill, that's why ask me if can :) thank you.
Comments
Post a Comment