ソフトウェアエンジニアの技術ブログ：Software engineer tech blog

DELETE FROM DELETE FROM pets WHERE id = ;

package com.example.android.pets;

import android.content.Intent;
import android.os.Bundle;
import android.support.design.widget.FloatingActionButton;
import android.support.v7.app.AppCompactActivity;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;

public class CatalogActivity extends AppCompactActivity {

	@Override
	protected void onCreate(Budle savedInstanceState){
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_catalog);

		// Setup FAB to open EditorActivity
		FloatingActionButton fab = (FloatingActionButton) findViewById(R.id.fab);
		fab.setOnClickListener(new View.OnClickListener(){
			@Override
			public void onClick(View view){
				Intent intent = new Intent(CatalogActivity.this, EditorActivity.class);
				startActivity(intent);
			}
		});
	}

	@Override
	public boolean onCreateOptionsMenu(Menu menu){
		// Inflate the menu options from the  res/menu/menu_catalog.xml file.
		// This adds menu items to the app bar.
		getMenuInflater().inflate(R.menu.menu_catalog, menu);
		return true;
	}

	@Override
	public boolean onOptionsItemSelected(MenuItem item){
		// User clicked on a menu option in the app bar overflow menu
		switch (item.getItemId()){
			case R.id.action_insert_dummy_data:

				return true;
			case R.id.action_delete_all_entries:

				return true;
		}
		return super.onOptionsItemSelected(item);
	}
}

package com.example.android.pets;

import android.os.Bundle;
import android.support.v4.app.NavUtils;
import android.support.v7.app.AppCompactActivity;
import android.text.TextUtils;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.EditText;
import android.widget.Spinner;

public class EditorActivity extends AppCompactActivity {
	private EditText mNameEditText;

	private EditText mBreedEditText;

	private EditText mWeightEditText;

	private Spinner mGenderSpinner;

	private int mGender = 0;

	@Override
	protected void onCreate(Bundle savedInstanceState){
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_editor);

		mNameEditText = (EditText) findViewById(R.id.edit_pet_name);
		mBreedEditText = (EditText) findViewById(R.id.edit_pet_breed);
		mWeightEditText = (EditText) findViewById(R.id.edit_pet_weight);
		mGenderSpinner = (Spinner) findById(R.id.spinner_gender);

		setupSpinner();
	}

}	

private void setupSpinner(){

	ArrayAdapter genderSpinnerAdapter = ArrayAdapter.createFromResource(this,
		R.array.array_gender_options, android.R.layout.simple_spinner_item);

	genderSpinnerAdapter.setDropDownViewResource(android.R.layout.simple_dropdown_item_1line);

	mGenderSpinner.setAdapter(genderSpinnerAdapter);

	mGenderSpinner.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener(){
		@Override
		public void onItemSelected(AdapterView<?> parent, View view, int position, long id){
			String selection = (String) parent.getItemAtPosition(position);
			if (!TextUtils.isEmpty(selection)){
				if (selection.equals(getString(R.string.gender_male))){
					mGender = 1;
				} else if (selection.equals(getSTring(R.string.gender_female))){
					mGender = 2;
				} else {
					mGender = 0;
				}
			}
		}

		@Override
		public void onNothingSelected(AdapterView<?> parent){
			mGender = 0;
		}
	});
}

	@Override
	public boolean onCreateOptionsMenu(Menu menu){
		getMenuInflater().inflate(R.menu.menu_editor, menu);
		return true;
	}

	@Override
	public boolean onOptionItemSelected(MenuItem item){
		switch (item.getItemId()){
			case R.id.action_save:
				return true;
			case R.id.action_delete:
				return true;
			case android.R.id.home:
				NavUtils.navigateUpFromSameTask(this);
				return true;
		}
		return super.onOptionsItemSelected(item);
	}

sqlite>   CREATE TABLE pets (
   ...>        _id INTEGER PRIMARY KEY AUTOINCREMENT,
   ...>        name TEXT NOT NULL,
   ...>        breed TEXT,
   ...>        gender INTEGER NOT NULL,
   ...>        weight INTEGER NOT NULL DEFAULT 0);
sqlite>   INSERT INTO pets ( name, breed, gender, weight) VALUES ( "Tommy", "Pomeranian", 1, 4);
sqlite>   INSERT INTO pets (name, breed, gender, weight) VALUES ("Garfield", "Tabby", 1, 14);
sqlite>   INSERT INTO pets (name, breed, gender, weight) VALUES ("Binx", "Bombay", 1, 6);
sqlite>   INSERT INTO pets (name, breed, gender, weight)  VALUES ( "Lady", "Cocker Spaniel", 2, 14);
sqlite>   INSERT INTO pets (name, breed, gender, weight) VALUES ("Duke", "Unknown", 1, 70);
sqlite>   INSERT INTO pets (name, breed, gender, weight) VALUES ("Cat", "Tabby", 0, 7);
sqlite>   INSERT INTO pets (name, breed, gender, weight) VALUES ("Baxter", "Border Terrier", 1, 8);
sqlite>   INSERT INTO pets (name, gender, weight) VALUES ("Arlene", 2, 5);
sqlite> UPDATE pets SET weight = 20 WHERE _id == 2;
sqlite> SELECT _id, name, weight FROM pets;
_id         name        weight
----------  ----------  ----------
1           Tommy       4
2           Garfield    20
3           Binx        6
4           Lady        14
5           Duke        70
6           Cat         7
7           Baxter      8
8           Arlene      5
sqlite> UPDATE pets SET breed = "Pomeranian Terrier" WHERE id==1;
Error: no such column: id
sqlite> UPDATE pets SET weight = 0;

PRIMARY KEY, AUTO INCREMENT, NOT NULL, DEFAULT

CREATE TABLE headphones (_id INTEGER, name TEXT, price INTEGER,
	style INTEGER, in_stock INTEGER, description TEXT);

PRIMARY KEY: Ensure uniqueness. There can only be one primary key per table
AUTO INCREMENT: Automatically calculates new integer when row is added. Useful for IDs

CREATE TABLE headphones (_id INTEGER PRIMARY KEY AUTOINCREMENT,
	name TEXT, 
	price INTEGER,
	style INTEGER, 
	in_stock INTEGER, 
	description TEXT);

CREATE TABLE headphones (_id INTEGER PRIMARY KEY AUTOINCREMENT,
	name TEXT, 
	price INTEGER,
	style INTEGER, 
	in_stock INTEGER, 
	description TEXT);

DEFAULT – When inserting a new row, if no value is defined, the default value given will be used

[slq]
CREATE TABLE headphones (_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
price INTEGER,
style INTEGER,
in_stock INTEGER NOT NULL DEFAULT 0,
description TEXT);
[/sql]

SELECT FROM ;
->
SELECT * FROM pets;
* indicates “all rows and all columns”
“pets” specifies we want data from the pets table

INSERT INTO pets (_id, name, bread, gender, weight)
	VALUES(1, "TOMMY", "Pomeranian", 1, 4);

sqlite> .tables
pets   pets2
sqlite> SELECT * FROM pets;
sqlite> INSERT INTO pets (_id, name, breed, gender, weight)
   ...>         VALUES(1, "TOMMY", "Pomeranian", 1, 4);
Error: table pets has no column named breed
sqlite> INSERT INTO pets (_id, name, bread, gender, weight)
   ...>         VALUES(1, "TOMMY", "Pomeranian", 1, 4);
sqlite> INSERT INTO pets (_id, name, bread, gender, weight)
   ...>         VALUES(2, "Garfield", "Tabby", 1, 8);
sqlite> SELECT * FROM pets;
1|TOMMY|Pomeranian|1|4
2|Garfield|Tabby|1|8

Create, Read, Update, Delete

In order to open and see the list of all the pets in the shelter taking read actions on the table.
Tapping on the entry in the CatalogActivity in order to see information about specific pet require Read.
From the EditorActivity, changing the weight of a specific pet requires taking Update actions on the table.
Using the option from the overflow menu that removes incorrectly entered pets requires Delete.
Selecting the “Insert Pet” option from the overflow menu to insert a list of default information requires taking Create actions on the table.

storage class, data types
NULL, INTEGER, REAL, TEXT, BLOB

No booleans, use INTEGER instead
Ex:
false = 0
true = 1

attribute
Name TEXT
Price INTEGER
Style INTEGER
In Stock INTEGER
Description TEXT

CREATE TABLE headphones (_id INTEGER, name TEXT,
	price INTEGER,
	style INTEGER,
	in_stock INTEGER,
	description TEXT);

CREATE TABLE pets (_id INTEGER, 
	name TEXT,
	bread TEXT,
	gender INTEGER,
	weight INTEGER);

Temporary Storage:Short term
-can be used for calculations or displaying data on screen
-quick to access
-short-lived
e.g.
-computer RAM

Permanent Storage:Long term
-can be used for storing user data
-slower to save and access data
-stick around forever(until deleted)
e.g.
-Hard disk, Flash drives

Different Data Storage Options
Files: good for saving large media files
SharedPreferences: Good for in-app user preferences, key and value, Unique String and primitive types and strings
SQLight Databases: Good for organizing a lot of related and structured data for easy access: Row, Column
commonly used for text data, easily grows in size and easily searchable

PS C:\Users\xxx> sqlite3 -version
3.15.0 2016-10-14 10:20:30 707875582fcba352b4906a595ad89198d84711d8

PS C:\Users\xxx> sqlite3
SQLite version 3.15.0 2016-10-14 10:20:30
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open pets.db
sqlite>