Where I can find example of SQLite database file or dump of it?

Sqlite

Sqlite Problem Overview

I really need a sqlite database file for studies. I'm creating an application in C# and I need a database with many tables, field, views, indexes, constraints and so on. And I don't want to create it myself using sqlite command line tool.

So I suppose may be someone can say where I can find such file, may be different applications or examples, or even can send me their own database file. I will appreciate any help.

Sqlite Solutions

Solution 1 - Sqlite

There is a nice sample database called Chinook. It's trying to be the modern example to replace NorthWind. They have versions for different database servers including SQLite.

Also, check this sample in the SQLite .NET client forums (attached to first post)

Maybe a GUI tool to create database stuff will make starting easier, check this one, free for personal use

Personally, I create SQLite databases for testing NHibernate mappings. Usually I create my classes and mappings then use the mappings to generate schema to a new SQLite file (or in memory database, more often) and use that. Most NHibernate introduction articles do that as well.

Solution 2 - Sqlite

SQLite is whidely used by many applications so I am pretty sure very lot examples can be found on your computer. For example, on my Win10 PC if I search in "c:\Users\Konstantin" (my profile) for files with:

  • file name mask: *.sqlite;*.db
  • text inside: SQLite format 3

I am currently getting 785 results. Most of them, which have that text in the beginning - it is 99% SQLite database files. Particularly I see that it is used by skype, viber, dropbox, office and firefox.

Solution 3 - Sqlite

I used sqlightCrud operation

First creat database class. package com.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.widget.Toast;

public class DataBaseSampleActivity {
	
    /** for database */	
	static final String DataBaseName = "EmployeDB";
	
	 /** for employee table */
	static final String EmployeTable = "Employees";
	static final String ColEmpID = "EmpId";
	static final String ColEmpName = "EmpName";
	static final String ColEmpAge = "EmpAge";
	static final String ColDept = "Dept";
	
	 /** for department table */
	static final String DeptTable = "Department";
	static final String ColDeptID = "DeptId";
	static final String ColDeptName = "DeptName";
	
	public static final int DATABASE_VERSION = 2;
	
	//private static final String KEY_ROWID = "_id";
	
	private static final String EMPLOYEE_TABLE_CREATE ="Create table " + EmployeTable +
	//"(_id INTEGER UNIQUE," + [old code]
	"("+ColEmpID 	 + " INTEGER PRIMARY KEY AUTOINCREMENT," +
	ColEmpName 	 + " VARCHAR(15) ," +
	ColEmpAge 	 + " INT(15) ," +
	ColDept 	 + " VARCHAR(15)) ";
	
	private final Context context;
	private DatabaseHelper DBHelper;
	private SQLiteDatabase db;
	
	public DataBaseSampleActivity(Context ctx){
		Log.i("test****", "**test***");
		this.context = ctx;
		DBHelper = new DatabaseHelper(context);
	}
	private static class DatabaseHelper extends SQLiteOpenHelper{
		public DatabaseHelper(Context context){
			super(context, DataBaseName , null, DATABASE_VERSION);
			Log.i("context","context");
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			// TODO Auto-generated method stub
			db.execSQL(EMPLOYEE_TABLE_CREATE);
			Log.i("************", "table created");
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// TODO Auto-generated method stub
			Log.w("tag", "Upgrading database from version " + oldVersion + " to "+ newVersion + ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS " + EmployeTable);
			onCreate(db);
		}
		
	};
	
	public DataBaseSampleActivity open() throws SQLException{
		db = DBHelper.getWritableDatabase();
		Log.i("open", "message");
		return this;
	}
	public void close(){
		DBHelper.close();
	}
	
	//public long insert(Integer empid, String empname, Integer empage, String empdept) {
	public long insert(String empname, Integer empage, String empdept) {
		Log.i("**** suruchitest **** ","*** test ***");
		ContentValues initialValues = new ContentValues();
		
		//initialValues.put(ColEmpID, empid);
		initialValues.put(ColEmpName, empname);
		initialValues.put(ColEmpAge, empage);
		initialValues.put(ColDept, empdept);
		
		return db.insert(EmployeTable, null, initialValues);
	}
	
	public Cursor getEmpValues(){
		Cursor mCursor = db.query(EmployeTable, null, null, null, null, null, null);
		return mCursor;
	}

	public boolean deleteEmpList(long rowId){
		Toast.makeText(context, "deleted", 2000).show();
		return db.delete(EmployeTable, ColEmpID +" = " + rowId, null) > 0;
	}
	public boolean updateEmplist(String empname, Integer empage, String empdept, Integer rowid){

		ContentValues initialValues = new ContentValues();
		Log.i("#####  "+rowid,""+empname+" "+empage+" "+empdept);

		//initialValues.put(ColEmpID, rowid);
		initialValues.put(ColEmpName,empname);
		initialValues.put(ColEmpAge,empage);
		initialValues.put(ColDept,empdept);
				
		try{
			int b = db.update(EmployeTable, initialValues,  ColEmpID+ " = " + rowid, null);
			Log.i("update", "up "+rowid+"  ddd   "+b);
			return true;
		}catch (Exception e){
			Log.d("asdfasdfsadfasdf", "_--___--__--_=-_");
			return false;
		}
	}
}




2. create Main Activity


package com.db;

import android.app.Activity;
import android.content.Intent;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

public class MainActivity extends Activity implements OnClickListener {
	
	Button buttonsubmit;
	EditText empid,empname,empage,empdept;
	
	String emp_name, emp_dept;
	//Integer emp_id,emp_age;
	Integer emp_age;
	@Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        
       
        buttonsubmit = (Button) findViewById(R.id.btnSubmit);
        buttonsubmit.setOnClickListener(this);
       
       // empid =(EditText) findViewById(R.id.empid);
        empname =(EditText) findViewById(R.id.empname);
        empage =(EditText) findViewById(R.id.empage);
        empdept =(EditText) findViewById(R.id.empdpt);
        

	}

	@Override
	public void onClick(View v) {
		// TODO Auto-generated method stub
		 DataBaseSampleActivity dbObj = new DataBaseSampleActivity(getApplicationContext());
		 
	       // String Emp_ids = empid.getText().toString();        
	       // emp_id = Integer.parseInt(Emp_ids);
	        //emp_id = empid.getText().toString();  

	        String Emp_ages = empage.getText().toString();
	        emp_age = Integer.parseInt(Emp_ages);

	        //emp_age = empage.getText().toString();
	        
	        emp_name = empname.getText().toString();
	        emp_dept = empdept.getText().toString();
		 
		 
		 try {
	        	Log.i("try", "message");
	        	dbObj.open();
	        	//long temp = dbObj.insert(emp_id, emp_name, emp_age, emp_dept);
	        	long temp = dbObj.insert(emp_name, emp_age, emp_dept);
	        	//Toast.makeText(getApplicationContext(), "temp"+temp, 3000).show();
	        	dbObj.close();
	        	
	        	Intent intent = new Intent(this,ShowListView.class);
	        	startActivity(intent);
			} catch (Exception e) {
				// TODO: handle exception
				Log.i("catch", "message");
			}
	}
}





2. Create listclass to show tha data


package com.db;

import java.lang.reflect.Array;
import java.util.ArrayList;

import android.R.integer;
import android.app.Activity;
import android.content.Context;
import android.content.Intent;
import android.database.Cursor;
import android.os.Bundle;
import android.util.Log;
import android.view.LayoutInflater;
import android.view.View;
import android.view.View.OnClickListener;
import android.view.ViewGroup;
import android.widget.BaseAdapter;
import android.widget.Button;
import android.widget.CheckBox;
import android.widget.CompoundButton;
import android.widget.CompoundButton.OnCheckedChangeListener;
import android.widget.ImageView;
import android.widget.ListView;
import android.widget.TextView;
import android.widget.Toast;

public class ShowListView extends Activity {
	
	ArrayList<String> arrname = new ArrayList<String>();
	ArrayList<String> arrage = new ArrayList<String>();
	ArrayList<String> arrdept = new ArrayList<String>();
	ArrayList<Integer> arrRowId = new ArrayList<Integer>();
	ArrayList<Integer> arrDelId = new ArrayList<Integer>();
	Array[] arr;
	Button deleteBtn;
	Button btnadd;
	int index = 0;
	
	public int pos;
	 
	@Override
	public void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.emplist);
			
		//Toast.makeText(getApplicationContext(), "LIST VIEW", 5000).show();
		
		ToGetCursorValues();
		
	}
	
	public void ToGetCursorValues(){
		DataBaseSampleActivity db = new DataBaseSampleActivity(getApplicationContext());
		db.open();
		try {
			Cursor cur = db.getEmpValues();
			cur.moveToFirst();
			arrRowId.clear();
			arrname.clear();
			arrage.clear();
			arrdept.clear();
			while (!cur.isAfterLast()) {
				arrRowId.add(cur.getInt(cur.getColumnIndex(db.ColEmpID)));
				arrname.add(cur.getString(cur.getColumnIndex(db.ColEmpName)));
				arrage.add(cur.getString(cur.getColumnIndex(db.ColEmpAge)));
				arrdept.add(cur.getString(cur.getColumnIndex(db.ColDept)));
				cur.moveToNext();
			}
			//Log.i("#####","col "+arrlist.size());
			//Toast.makeText(getApplicationContext(), "* "+arrname.size()+","+arrage.size()+","+arrdept.size(), 5000).show();
			
			//Toast.makeText(getApplicationContext(), "***** "+arrRowId.get(0), 2000).show();
			
		} catch (Exception e) {
			// TODO: handle exception
		}
		
		
		ListView lst = (ListView) findViewById(R.id.mylist);
        lst.setAdapter(new ListAdapter(getApplicationContext()));
		
		db.close();
	}
	
	public class ListAdapter extends BaseAdapter implements OnCheckedChangeListener,OnClickListener{
		private LayoutInflater inflater = null;
		
		public ListAdapter(Context c){
			Log.i("Context","Context");
			inflater = LayoutInflater.from(c);
		}
		@Override
		public int getCount() {
			// TODO Auto-generated method stub
			//return 0;
			return arrname.size();
		}

		@Override
		public Object getItem(int position) {
			// TODO Auto-generated method stub
			return null;
		}

		@Override
		public long getItemId(int position) {
			// TODO Auto-generated method stub
			return 0;
		}
		class ViewHolder{
			TextView empnameview;
			TextView empageview;
			TextView empdeptview;
			CheckBox empchkbox;
		}
	    
		 // create a new ImageView for each item referenced by the Adapter
        public View getView(final int position, View convertView, ViewGroup parent) {
        	
        	
        	Log.i("*view","view*");
       	    ViewHolder vh;
            //ImageView imageView;
            if (convertView == null) {  // if it's not recycled, initialize some attributes
           	 	
            	Log.i("*null1*","*null1*");
            	
            	vh = new ViewHolder();
				convertView = inflater.inflate(R.layout.customlist, null);
				
				Log.i("*null2*","*null2*");
				pos = position;
				vh.empnameview = (TextView) convertView.findViewById(R.id.ename);
				vh.empageview = (TextView) convertView.findViewById(R.id.eage);
				vh.empdeptview = (TextView) convertView.findViewById(R.id.edept);
				vh.empchkbox = (CheckBox) convertView.findViewById(R.id.ckekDelete);
				
				Log.i("*null3*","*null3*");

				vh.empnameview.setText(arrname.get(position));
				
				vh.empnameview.setOnClickListener(new OnClickListener() {
					
					@Override
					public void onClick(View v) {
						// TODO Auto-generated method stub
						Intent intent = new Intent(ShowListView.this,UpdateDB.class);
						String name = arrname.get(position);
						int age = Integer.parseInt(arrage.get(position));
						String dept = arrdept.get(position);
						int rowid = arrRowId.get(position);
						
						intent.putExtra("KeyName" , name);
						intent.putExtra("Keyage" , age);
						intent.putExtra("Keydept" , dept);
						intent.putExtra("Rowid", rowid);

						startActivity(intent);
					}
				});
				
			    vh.empageview.setText(arrage.get(position));
			    vh.empdeptview.setText(arrdept.get(position));
			    vh.empchkbox.setOnCheckedChangeListener(new OnCheckedChangeListener() {
					
					@Override
					public void onCheckedChanged(CompoundButton buttonView, boolean isChecked) {
						// TODO Auto-generated method stub
						//Toast.makeText(getApplicationContext(), "OnChecked"+position, 2000).show();
						if(buttonView.isChecked()){
							arrDelId.add(arrRowId.get(position));
							//Toast.makeText(getApplicationContext(), "OnChecked"+position, 2000).show();
							
//							DataBaseSampleActivity db = new DataBaseSampleActivity(getApplicationContext());
//							db.open();
//							db.deleteEmpList(arrRowId.get(position));
//							Toast.makeText(getApplicationContext(), "delet", 3000).show();
//							db.close();
//									
						}
						else{
							for(int i=0;i<arrDelId.size();i++){
								if(arrRowId.get(position) == arrDelId.get(i)){
									arrDelId.remove(i);
								}
							}
						}
					}
				});
			    
			    Log.i("******", "complete");
			 
            } else {
            	Log.i("*not*","*not*");
            	vh = (ViewHolder) convertView.getTag();
            }
            
            deleteBtn = (Button) findViewById(R.id.delBtn);
            deleteBtn.setOnClickListener(this);
            
            btnadd = (Button) findViewById(R.id.addBtn);
            btnadd.setOnClickListener(new OnClickListener() {
				
				@Override
				public void onClick(View v) {
					// TODO Auto-generated method stub
					Intent inte = new Intent(ShowListView.this, MainActivity.class);
					startActivity(inte);
				}
			});

           // imageView.setImageResource(thumbarr[position]);
           return convertView;
           
        }
        
        public View getView1(int position, View convertView, ViewGroup parent) {
 			// TODO Auto-generated method stub
 			return null;
 		}
		
		@Override
		public void onClick(View v) {
			// TODO Auto-generated method stub
			
			for(int i=0;i<arrDelId.size();i++){
				//Toast.makeText(getApplicationContext(), "OnDeleteClick  "+i, 2000).show();
				DataBaseSampleActivity db = new DataBaseSampleActivity(getApplicationContext());
				db.open();
				db.deleteEmpList(arrDelId.get(i));
				//Toast.makeText(getApplicationContext(), "delet", 3000).show();
				db.close();
			}
			
			ToGetCursorValues();
			
		}
		@Override
		public void onCheckedChanged(CompoundButton buttonView,
				boolean isChecked) {
			// TODO Auto-generated method stub
			
		}
		
		
	}

}


3. for update 

package com.db;

import android.app.Activity;
import android.content.Intent;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

public class UpdateDB extends Activity implements OnClickListener{
	
	Intent intnt;
	EditText editname,editage,editdept;
	Button updateBtn;
	int row_id;

	@Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);

        editname = (EditText) findViewById(R.id.empname);
        editage = (EditText) findViewById(R.id.empage);
        editdept = (EditText) findViewById(R.id.empdpt);
        
        updateBtn = (Button) findViewById(R.id.btnSubmit);
        updateBtn.setText("Update");

        intnt = getIntent();
        
        editname.setText(intnt.getStringExtra("KeyName"));
        editage.setText(""+intnt.getIntExtra("Keyage",0));
        editdept.setText(intnt.getStringExtra("Keydept"));
        
        row_id = intnt.getIntExtra("Rowid", 0);
        
        updateBtn.setOnClickListener(new OnClickListener() {
			
			@Override
			public void onClick(View v) {
				// TODO Auto-generated method stub
				Toast.makeText(getApplicationContext(), "update", 3000).show();
				DataBaseSampleActivity db = new DataBaseSampleActivity(getApplicationContext());
				db.open();
				String empname = editname.getText().toString();
				int empage = Integer.parseInt(editage.getText().toString());
				String empdept = editdept.getText().toString();
				//db.deleteEmpList(row_id);
				db.updateEmplist(empname, empage, empdept,row_id);
				//Toast.makeText(getApplicationContext(), "delet", 3000).show();
				db.close();
				Intent list = new Intent(UpdateDB.this,ShowListView.class);
				startActivity(list);
			}
		});
        
       // Toast.makeText(getApplicationContext(), "update "+intnt.getIntExtra("Keyage",0), 3000).show();
	}

	@Override
	public void onClick(View v) {
		// TODO Auto-generated method stub
		
	}

}
Categories
Recommended Sqlite Solutions

Previous Solution:

Is there any case where a return of a RValue Reference (&&) is useful?

C++C++11Move SemanticsRvalue Reference

Next Solution:

Why would I put code in __init__.py files?

PythonModule

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionDmitriyView Question on Stackoverflow
Solution 1 - SqliteMeligyView Answer on Stackoverflow
Solution 2 - SqlitesarhView Answer on Stackoverflow
Solution 3 - Sqlitesachin surjanView Answer on Stackoverflow