User Tools

Site Tools


Sidebar

Privacy Policy

News

Version 2.50 is out since Jan 1st 2022


Frequently Asked Questions


Namespaces

Note for contributors

If you wish to create a new page in the DroidScript wiki, please click on the most appropriate namespace above and follow the notes for contributors there.

Because of spam, it has been necessary to add a CAPTCHA to the registration form and the save option for editing pages. You will not need to prove you are human if you are logged in, so please register.

Please feel free to improve any existing page, as well as adding new pages to increase the sum of public knowledge about DroidScript.

Formatting Syntax

sample_code:database_navigate

This is an old revision of the document!


Application: view Droidscript databases

I created this simple application to learn and play Databases and Dialogs

  • Identify the sqlite databases in the default folder used by Droidscript.
  • List the tables
  • List Columns of tables
  • Dialog contents of columns
  • And could also delete a database

On My local Version, I have also 3 icons in subdirectory Img<br> I leave text, the code for icons is kepts inside … see the 3 tCol

enjoy

AdminDB.js
// thierry . 2015 04 15
// purpose: 
//  1. view contents of databases from application droidscripts
//  2. test sqlite commandes
// verifier si base de donnee ouverte ou fermee
var opened=0;
 
// pour chercher les images: var target="/Sys/Img";
// pour chercher les databases sqlite de droidscripts
var target="/data/data/com.smartphoneremote.androidscriptfree/databases/";
 
var selectedDb='';
var selectedTable='';
var selectedColumn='';
 
 
//Called when application is started.
function OnStart()
{
    //Loading other files into the app
    //app.ShowProgress("...Loading Scripts...");
    //app.LoadScript("Misc/Folderpicker.js");
    //app.HideProgress();
 
 //Create a layout with objects vertically centered.
 lay = app.CreateLayout( "linear", "VTop,FillXY" );  
 
 // je cherchais aussi le langage
 var country= app.GetCountry() ;
 var countrycode= app.GetCountryCode() ;
 var language= app.GetLanguage() ;
 var languagecode= app.GetLanguageCode() ;
 
 // affiche mon titre
 titre = app.CreateText( "Yep Database View", 1 );
  titre.SetTextSize(20);
  titre.SetTextColor( "#8855ff" ); 
  titre.SetTextShadow( 10, 5,10, "#ff88ff" ); 
  titre.SetOnTouchUp(about);
  titre.SetOnTouchDown(longabout);
  lay.AddChild( titre );
 
 lh0=app.CreateLayout("linear","horizontal");
 lh1=app.CreateLayout("linear","horizontal");
 lh2=app.CreateLayout("linear","horizontal");
 lh3=app.CreateLayout("linear","horizontal");
  lay.AddChild( lh0 );
  lay.AddChild( lh1 );
  lay.AddChild( lh2 );
  lay.AddChild( lh3 );
  lh0.SetBackGradient( "#444444", "#888888" );
  lh1.SetBackGradient( "#888888", "#444444" );
 
 remindDb=app.CreateText("Db:",0.33);
  lh0.AddChild(remindDb);
 remindTable=app.CreateText("T:",0.33);
  lh0.AddChild(remindTable);
 remindColumn=app.CreateText("C:",0.33);
  lh0.AddChild(remindColumn);
 var colorTitle="#77BBFF";
  remindDb.SetTextColor( colorTitle );
  remindTable.SetTextColor( colorTitle );
  remindColumn.SetTextColor( colorTitle );
 
 tColGauche=app.CreateText("db files",0.33);
 //tColGauche= app.CreateImage("Img/database.png",0.07); 
  lh1.AddChild(tColGauche);
  //tColGauche.SetMargins( 0.1,0.003,0,0.004 ); 
  tColGauche.SetOnTouch( btnD_OnTouch );
 
 tColCentre=app.CreateText("tables",0.33);
 //tColCentre=app.CreateImage("Img/table.png",0.07);
  lh1.AddChild(tColCentre);
  //tColCentre.SetMargins( 0.29,0.003,0.29,0.004 );
  tColCentre.SetOnTouch( btnT_OnTouch );
 
 tColDroite=app.CreateText("column",0.33);
 //tColDroite=app.CreateImage("Img/column.png",0.07);
  lh1.AddChild(tColDroite);
  //tColDroite.SetMargins( 0,0.003,0.1,0.004 ); 
  tColDroite.SetOnTouch( btnF_OnTouch );
 
  var colorTitle="#0033dd";
  tColGauche.SetTextColor( colorTitle );
  tColCentre.SetTextColor( colorTitle );
  tColDroite.SetTextColor( colorTitle );
 
 // faire des listes
 lstLeft = app.CreateList( "", 0.7, 0.6 ); 
  loadLeftList();
  lh2.AddChild( lstLeft );
  lstLeft.SetOnTouch( lstLeft_OnTouch );
  lstLeft.SetOnLongTouch( lstLeft_OnLongTouch );
 
 lstCentre = app.CreateList( "", 0.15, 0.6 );
  lh2.AddChild( lstCentre );
  lstCentre.SetOnTouch( lstCentre_OnTouch );
 
 lstRight= app.CreateList( "", 0.15, 0.6 );
  lh2.AddChild( lstRight );
  lstRight.SetOnTouch( lstRight_OnTouch );
  lstRight.SetOnLongTouch( lstRight_OnLongTouch );
 
  //Create buttons width columns.
 btnD = app.CreateButton( "Db", 0.3 ,-1,"alumn");
   lh3.AddChild( btnD );
   btnD.SetOnTouch( btnD_OnTouch );
 btnT = app.CreateButton( "Table", 0.3 ,-1,"alumn");
   lh3.AddChild( btnT );
   btnT.SetOnTouch( btnT_OnTouch );
 btnF = app.CreateButton( "Column", 0.3 ,-1,"alumn");
   lh3.AddChild( btnF );
   btnF.SetOnTouch( btnF_OnTouch );
 
 //Add layout to app.    
 app.AddLayout( lay );
}
 
 
function about() { app.ShowPopup( target );}
function longabout() { app.ShowPopup( "Thierry 2015 04 15. bis40@free.fr" );}
 
function loadLeftList() {
 var F = app.ListFolder( target );
 for (var i in F ) {
  var text=F[i];var rien;
  if( text.indexOf("journal") >-1) rien++ 
  else lstLeft.AddItem( text );
 }
}
 
//Called when user touches our button
function btnD_OnTouch(){resizeListes(0.8,0.1,0.1);}
function btnT_OnTouch(){resizeListes(0.1,0.8,0.1);}
function btnF_OnTouch(){resizeListes(0.1,0.1,0.8);}
function resizeListes(a,b,c){
 lstLeft.SetSize(a);
 lstCentre.SetSize(b);
 lstRight.SetSize(c);
}
 
 
function emptyList(list) {
 // pas certain des separateurs: virgule ou CR ou autre?
 var itemString=list.GetList(",");
 var itemArray=itemString.split(",");
 for (var i in itemArray ) {
   list.RemoveItem(itemArray[i] );
 } 
 var itemString=list.GetList("\n");
 var itemArray=itemString.split("\n");
 for (var i in itemArray ) {
   list.RemoveItem(itemArray[i] );
 }
}
 
 
function lstLeft_OnTouch( database ){
  if (opened) {  db.Close(); }
  selectedDb = database ;
  db=app.OpenDatabase( database ); 
  opened=1; 
  emptyList(lstCentre); 
  emptyList( lstRight );
  remindDb.SetText( database );
  resizeListes(0.15,0.7,0.15);
  db.ExecuteSql("SELECT * FROM sqlite_master where type='table' ",[],OnFoundTables);  
}  
 
function OnFoundTables(results)    
{
 var len = results.rows.length;   
 for(var i = 0; i < len; i++ )    
    {   
     var item = results.rows.item(i)   ;
     lstCentre.AddItem(item.tbl_name); 
    }   
}   
 
 
 
function lstLeft_OnLongTouch( item )
{
 //Create dialog window.
 dlgTxt=app.CreateDialog("confirm delete database?");
 var txtSize = 12 ;
 //Create a layout for dialog gui
 layDlg = app.CreateLayout( "linear", "vertical,fillxy,left" );
   //layDlg.SetPadding( 0.02, 0, 0.02, 0.02 );
   dlgTxt.AddLayout( layDlg );
 descItem=app.CreateText("confirm delete database "+item, 1, 0.06);
   layDlg.AddChild(descItem);
   descItem.SetTextSize( txtSize );
   //Create btns
 btnYDlg = app.CreateButton( "delete "+item, 1, 0.06 );
 btnNDlg = app.CreateButton( "no", 1,0.1);
 
   btnYDlg.SetTextColor( "#ff2222" );
   btnNDlg.SetTextColor( "#22FF22" );
   btnYDlg.SetTextSize( txtSize );
   btnNDlg.SetTextSize( txtSize );
 
   btnYDlg.SetOnTouch( btnY_OnTouch );
   btnNDlg.SetOnTouch( btnN_OnTouch );
   layDlg.AddChild( btnYDlg );
   layDlg.AddChild( btnNDlg );
  //Show dialog. }
  dlgTxt.Show();
}
 
function btnY_OnTouch(item) {
  dlgTxt.Hide();
  var database=descItem.GetText();
  //app.ShowPopup( "ok faut effacer " + database ); 
  db = app.OpenDatabase( database );   
  db.Delete();
  //dlgTxt.Dismiss();
  emptyList(lstLeft);
  loadLeftList();
}
function btnN_OnTouch() {
 dlgTxt.Hide();
}
 
 
function lstCentre_OnTouch( table )
{ 
 emptyList( lstRight );
 selectedTable = table ;
 remindTable.SetText(table);
 //app.ShowPopup( "ItemCentre = " + table, "Short" );
 db.ExecuteSql("SELECT sql FROM sqlite_master where type='table' and tbl_name='"+table+"'",[], OnFoundColumns );
 resizeListes(0.15,0.15,0.7);
}
 
function OnFoundColumns01(results)    
{ 
  var len = results.rows.length;   
  for(var i = 0; i < len; i++ )    
    {   
     var item = results.rows.item(i)   ;
     lstRight.AddItem(JSON.stringify(item,null,3)); 
    }   
}   
function OnFoundColumns(results)    
{
 var objet = results.rows.item(0)  ;
 emptyList( lstRight );
 var arraytext= parseSqliteMasterSql( objet['sql']);
 for(var column in arraytext ) 
 {   
  lstRight.AddItem( arraytext[column] ); 
 }   
}
 
function parseSqliteMasterSql(val) {
 var arrayOne=val.split('(');
 var arrayTwo=arrayOne[1].split(',');
 var last=arrayTwo.pop();
 var clean = last.replace(")","");
 arrayTwo.push( clean );
 return arrayTwo;
}
 
function parseSqliteMasterSql02(val) {
 var arrayOne=val.split("\W");
 var toRemove=arrayOne.shift();
 return arrayOne;
}
 
function lstRight_OnLongTouch(column){
 var array=column.split(" ");
 selectedColumn = array[0] ; 
 remindColumn.SetText(selectedColumn);
 //app.ShowPopup(selectedTable +" _ " + selectedColumn , "Short" );
 db.ExecuteSql("SELECT count(DISTINCT "+ selectedColumn +" ) FROM "+selectedTable,[], OnFoundRecord );
} 
function OnFoundRecord(q) {
 var len = q.rows.length;   
 var output='L='+len+" ";
 for(var i = 0; i < len; i++ )    
 {   
  var item = q.rows.item(i)   ;
  //lstRight.AddItem(JSON.stringify(item,null,3)); 
  output+= JSON.stringify(item,null,3);
 }  
 app.ShowPopup( output, "long");
}
 
function lstRight_OnTouch(column){
 var array=column.split(" ");
 selectedColumn = array[0] ; 
 //app.ShowPopup( JSON.stringify(selectedColumn,null,3) );
 db.ExecuteSql("SELECT "+ selectedColumn +"  FROM "+selectedTable,[], OnFoundRecord02 );
} 
function OnFoundRecord02(results) {
 //Create dialog window.
 dlgContent=app.CreateDialog("Content from "+ selectedDb+' '+ selectedTable+ ' ' +selectedColumn );
 var txtSize = 10 ;
 //Create a layout for dialog gui
 layDlgContent = app.CreateLayout( "linear", "vertical,fillxy,left" );
   dlgContent.AddLayout( layDlgContent );
 lstDlg = app.CreateList( '', 0.8, 0.3 );
   lstDlg.SetTextSize( txtSize );
   layDlgContent.AddChild( lstDlg );
 btnOkvu = app.CreateButton( "ok ", 1 );
   layDlgContent.AddChild( btnOkvu );
   btnOkvu.SetOnTouch(  closeDialog02 );  
 
 var len = results.rows.length;   
 //app.ShowPopup( JSON.stringify(results,null,3) );
 var s = "";  
 for(var i = 0; i < len; i++ )   
 {  
  var item = results.rows.item(i)  
  //s += JSON.stringify( item ) + "\n";
  lstDlg.AddItem( JSON.stringify( item ) ) ;
 }  
 //   app.ShowPopup( s );  
 dlgContent.Show(); 
}
function closeDialog02(){dlgContent.Hide()}

enjoyed? — thierry DERICK 2015/04/17 00:40

sample_code/database_navigate.1429232020.txt.gz · Last modified: 2015/04/17 08:53 (external edit)