mardi 29 septembre 2015

Save Items in ListView in to the Database

I'm developing an android application using SQLite Database. I have a itemList which user can enter food items into the List. I want to save the items of each listview seperately (one listview data in a line). And i'm using an Arraylist to store data which user enters into the System. Can someone help me to do this. Below i've posted my XML page and Java Class. Thanks in advance!! :)

manualListScr.xml

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://ift.tt/nIICcg"
android:orientation="vertical" android:layout_width="match_parent"
android:layout_height="match_parent">

<EditText
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:id="@+id/itmName"
    android:hint="Enter Item"
    android:textSize="24dp"
    android:layout_alignParentTop="true"
    android:layout_centerHorizontal="true"/>

<Button
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:id="@+id/addItems"
    android:text="Add Item"
    android:layout_below="@+id/itmName"
    android:layout_alignParentLeft="true"/>

<TextView
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:text="Manual Item List"
    android:id="@+id/itmHeader"
    android:layout_below="@+id/addItems"
    android:background="#5e5e5e"
    android:textColor="#FFFFFF"
    android:textSize="14dp"/>

<ListView
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:id="@+id/itmList"
    android:layout_below="@+id/itmHeader"
    android:layout_centerHorizontal="true">

</ListView>


manualList.java

public class manualInput extends Activity implements View.OnClickListener{
private Button addButton;
private EditText editText;
private ListView listView;
ArrayList<String> itmList = new ArrayList<String>();
ArrayAdapter<String> adapter;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.manualinputscr);
    addButton = (Button) findViewById(R.id.addItems);
    addButton.setOnClickListener(this);
    editText = (EditText) findViewById(R.id.itmName);
    adapter = new ArrayAdapter<String>(this, android.R.layout.simple_expandable_list_item_1,itmList);
    listView = (ListView) findViewById(R.id.itmList);
    listView.setAdapter(adapter);
    registerForContextMenu(listView);
}

@Override
public void onCreateContextMenu(ContextMenu menu, View v, ContextMenu.ContextMenuInfo menuInfo) {
    super.onCreateContextMenu(menu, v, menuInfo);
    MenuInflater inflater = getMenuInflater();
    inflater.inflate(R.menu.context_menu, menu);
}

@Override
public boolean onContextItemSelected(MenuItem item) {
    //return super.onContextItemSelected(item);
    AdapterView.AdapterContextMenuInfo info = (AdapterView.AdapterContextMenuInfo)item.getMenuInfo();
    switch (item.getItemId()){
        case R.id.editItem:
            //
            return true;
        case R.id.deleteItem:
            adapter.remove(adapter.getItem(info.position));
            Toast.makeText(this, "Item Deleted", Toast.LENGTH_SHORT).show();
            return true;
        default:
            return super.onContextItemSelected(item);
    }
}

public void onClick(View view){
    String input = editText.getText().toString();
    if(input.length() > 0){
        adapter.add(input);
    }
}
}

How get row counts of a table sqlite in android

I tried this code but it will always go error : java.lang.NullPointerException

I dont know why. I have records in my table Goals.

This is my code.

public long getGoalsCount() {
        SQLiteDatabase db=dbhandler.getWritableDatabase();
        long numRows = DatabaseUtils.queryNumEntries(db, MyDBHandler.TABLE_GOALS);
        return numRows;
    }

When i called out the function

public void displayAllGoals(){
        long goal_counts = dbhandler.getGoalsCount(); // The error points her that it means it doesnt return any value. And that it's null
        MessageTo.message(ViewGoalsActivity.this,Long.toString(goal_counts)); //To display the value

    }

OnCreate Function

protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_view_goals);

        displayAllGoals();

    }

I can pinpoint what's wrong in my code. I tried other sources but its still the same error. I'm a newbie in Android.

java.lang.RuntimeException: Unable to start activity ComponentInfo{com.citu.organizer/com.citu.organizer.ViewGoalsActivity}: java.lang.NullPointerException

at com.citu.organizer.ViewGoalsActivity.displayAllGoals(ViewGoalsActivity.java:57)
            at com.citu.organizer.ViewGoalsActivity.onCreate(ViewGoalsActivity.java:38)

unable to generate random words from SQLite database in android

Background: Every time the user starts a new game with another player, he is given 4 words that are taken from the SQLite database, which remain in the current game activity until the game is finished. If the user has multiple games, each of those games will have a different set of words.

problem: I am able to generate a random set of words, but the problem is that my app generates the set of words only once, and this set is used in all the games of the current user, and any other user I log in as.

Here is my code:

  public class WordsListDatabaseHelper extends SQLiteOpenHelper {

protected static Set<String> mSelectedWords;
private static final String DB_NAME = "GAME_TABLE";
private static final int DB_VERSION = 1;
protected static LinkedList<String> mCopy;
public static int gameNumber = 0;


WordsListDatabaseHelper(Context context) {
    super(context, DB_NAME, null, DB_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL("CREATE TABLE GAME ("
                    + "_id INTEGER PRIMARY KEY AUTOINCREMENT, "
                    + "SELECTED_WORDS TEXT, "
                    + "GAME_NUMBER INTEGER);"
    );



    Collection<String> wordList = new LinkedList<String>();
    mSelectedWords = new LinkedHashSet<String>();

    wordList.add("ant");
    wordList.add("almond");
      //lots more words

    mCopy = new LinkedList<String>(wordList);

    Gson gson = new Gson();
    String wordsContainer = gson.toJson(mCopy);
    insertWordList(db, wordsContainer, gameNumber);

}

private static void insertWordList(SQLiteDatabase db, String wordsContainer, int gameNumber) {
    ContentValues wordValues = new ContentValues();

    Gson gson = new Gson();
    Collections.shuffle(mCopy);
    mSelectedWords.addAll(mCopy.subList(1,7));
    wordsContainer = gson.toJson(mSelectedWords);

    wordValues.put("SELECTED_WORDS", wordsContainer);
    wordValues.put("GAME_NUMBER", gameNumber);
    db.insert("GAME", null, wordValues);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}

}

Here is the relevant code from FindingOpponentsActivity, which randomly matches the user with an opponent. When the match is created, the user is taken to StartGameActivity.

  Intent intentRand = new Intent(FindingOpponentActivity.this, StartGameActivity. .class);
                    int gameNum = WordsListDatabaseHelper.gameNumber;
                    intentRand.putExtra(
                            StartGameActivity.EXTRA_RAND_OPPONENT,
                            mOpponent.getObjectId());

                    intentRand.putExtra(
                            StartGameActivity.EXTRA_GAME_NUMBER,
                            gameNum);

                    sendPushNotification();
                    startActivity(intentRand);
                }

And now, this is the code in the onCreate() method of StartGameActivity:

  Intent intent = getIntent();
    mGameNum = intent.getIntExtra(EXTRA_GAME_NUMBER, 0);


    //create a cursor

    try {
        SQLiteOpenHelper wordsListDatabaseHelper = new WordsListDatabaseHelper(this);
        SQLiteDatabase db = wordsListDatabaseHelper.getReadableDatabase();
        Cursor cursor = db.query("GAME",
                new String[] {"SELECTED_WORDS"},
                "GAME_NUMBER =? ",
                new String[]{Integer.toString(mGameNum)},
                null, null, null);

        //move to the first record in the Cursor

        if (cursor.moveToFirst()) {
            //get wordList
            String savedWordList  = cursor.getString(0);

            Gson gson = new Gson();
            Type type = new TypeToken<ArrayList<String>>() {}.getType();
            ArrayList<String> finalWordList = gson.fromJson(savedWordList, type);
            mCopy = new LinkedList<String>();
            mCopy.addAll(finalWordList);


            word1 = (TextView) findViewById(R.id.word1);
            word1.setText(mCopy.get(1));

            word2 = (TextView) findViewById(R.id.word2);
            word2.setText(mCopy.get(2));

What I want is that every time a new game begins, the table has a new entry: a new GAME_NUMBER and wordsContainer (which contains the random words). Thus, each game of the user has a unique identifier (GAME_NUMBER). I think what I am doing wrong is not creating a new entry every time a new game begins, and this is what's causing the same list to appear, but I am totally lost as to how to implement the solution.

Logical expressions in full text search in SQLite executed with PHP PDO

The logical expressions does not work as expected while full text search in SQLite database. Tested on XAMPP-PORTABLE 1.8.3 [PHP: 5.5.6].

Example data

There is table clubs with columns

  • name for the football club
  • city for the city
  • nations for the abbreviations of players' nationalities

What is important, the cities with be used for OR statements (example, London or Liverpool). The nationalities will be used for AND statements.

The PHP script that creates such table

<?php
$db = new  PDO('sqlite:mydb.sqlite3');
$sql = "CREATE VIRTUAL TABLE clubs USING fts4 " .
       "(name, city, nations)";
$db->exec($sql);

$sql = "DELETE FROM clubs";
$db->exec($sql);

$teams = array(
  array(
    "name" => "FC Chelsea", 
    "city" => "London",
    "nations" => "ENG BEL BIH FRA SEN ESP GHA SRB NIG COL" 
  ),
  array(
    "name" => "FC Arsenal", 
    "city" => "London",
    "nations" => "ENG COL CZE FRA GER BRA ESP WAL CHI CRC" 
  ),
  array(
    "name" => "Tottenham Hotspur", 
    "city" => "London",
    "nations" => "ENG FRA NED BEL ARG AUT WAL ALG DEN CAM KOR" 
  ),
  array(
    "name" => "West Ham United", 
    "city" => "London",
    "nations" => "ENG ESP IRL SUI NZL WAL ITA CAN SCO CAM SEN FRA ARG ECU" 
  ),
  array(
    "name" => "Manchester City", 
    "city" => "Manchester",
    "nations" => "ENG ARG BEL FRA SRB BRA CIV ESP NIG" 
  ),
  array(
    "name" => "Manchester United", 
    "city" => "Manchester",
    "nations" => "ENG ESP ARG NED ITA ECU FRA BEL GER BRA" 
  ),
  array(
    "name" => "FC Everton", 
    "city" => "Liverpool",
    "nations" => "ENG ESP USA ARG CRC IRL BIH RSA BEL SCO URU" 
  ),
  array(
    "name" => "FC Liverpool", 
    "city" => "Liverpool",
    "nations" => "ENG BRA HUN FRA CRO ESP SVK CIV GER WAL POR BEL" 
  ),
);

for($i=0; $i<count($teams); $i++) {
  $sql = "INSERT INTO clubs VALUES (?, ?, ?)";
  $stmt = $db->prepare($sql);
  $stmt->execute(
    array(
      $teams[$i]["name"], $teams[$i]["city"], $teams[$i]["nations"]
    )     
  );
}

Examples of logical expressions

The following function wil be used for the display of the results

function doQuery($db, $sql, $desc) {
  $arr = array();
  $q = $db->query($sql);
  $rows = $q->fetchAll(PDO::FETCH_ASSOC);
  echo "<tt>" . $sql . "</tt><br/>";
  echo "<b> " . $desc . " (" . count($rows) . "</b>): " ;
  foreach($rows as $row) {
    $arr[] = $row['name'];
  }
  $results = implode(", ", $arr);
  echo $results . "<br/>";  
}

In the following examples the results will be displayed with italic to distinguish them from the other text.

One logical expression statement

1.1. Search clubs with Spanish players

$sql = <<<SQL
SELECT * FROM clubs WHERE clubs MATCH ('nations:ESP')
SQL;
doQuery($db, $sql, "ESP");

There are found all teams except Tottenham

ESP (7): FC Chelsea, FC Arsenal, West Ham United, Manchester City, Manchester United, FC Everton, FC Liverpool

1.2. Search clubs with Argentine players

$sql = <<<SQL
SELECT * FROM clubs WHERE clubs MATCH ('nations:ARG')
SQL;
doQuery($db, $sql, "ARG");

Results are also correct.

ARG (5): Tottenham Hotspur, West Ham United, Manchester City, Manchester United, FC Everton

Two logical expressions statement

2.1. Search clubs from London OR Liverpool

$sql = <<<SQL
SELECT * FROM clubs WHERE clubs MATCH ('city:London OR city:Liverpool')
SQL;
doQuery($db, $sql, "London or Liverpool");

No problem.

London or Liverpool (6): FC Chelsea, FC Arsenal, Tottenham Hotspur, West Ham United, FC Everton, FC Liverpool

2.2. Search clubs with Spanish players AND Argentine players

$sql = <<<SQL
SELECT * FROM clubs WHERE clubs MATCH ('nations:ARG nations:ESP')
SQL;
doQuery($db, $sql, "ARG ESP");

Implicit AND operator works fine.

ARG ESP (4): West Ham United, Manchester City, Manchester United, FC Everton

but when go for explicit AND

2.3. Search clubs with Spanish players AND Argentine players (explicit)

$sql = <<<SQL
SELECT * FROM clubs WHERE clubs MATCH ('nations:ARG AND nations:ESP')
SQL;
doQuery($db, $sql, "ARG and ESP");

No club is found.

ARG and ESP (0):

The explicit AND does not seem to work.

Three logical expressions statement

Now the results are unexpected

3.1. Search clubs from London OR clubs from Liverpool with Argentine players

$sql = <<<SQL
SELECT * FROM clubs WHERE clubs MATCH ('city:London OR city:Liverpool nations:ARG')
SQL;
doQuery($db, $sql, "London or Liverpool and ARG");

We would have expected all London teams selected, but instead we get only those with Argentine players

London or Liverpool and ARG (3): Tottenham Hotspur, West Ham United, FC Everton

The same result is obtained for query

SELECT * FROM clubs WHERE clubs MATCH ('city:Liverpool OR city:London nations:ARG')

It looks like OR is executed before AND: (city:Liverpool OR city:London) AND nations:ARG. But what happens when the brackets are really used around OR-statement.

3.2. Search clubs (from London OR clubs from Liverpool) with Argentine players (brackets)

$sql = <<<SQL
SELECT * FROM clubs WHERE clubs MATCH ('(city:Liverpool OR city:London) nations:ARG')
SQL;
doQuery($db, $sql, "(London or Liverpool) and ARG");

No club is found.

(London or Liverpool) and ARG (0):

What if the sequence of logical expressions changes

3.3 Search clubs from Liverpool with Argentine players or clubs from London.

$sql = <<<SQL
SELECT * FROM clubs WHERE clubs MATCH ('city:Liverpool nations:ARG OR city:London')
SQL;
doQuery($db, $sql, "Liverpool and ARG or London");

Only team from Liverpool with Argentine players is found.

Liverpool and ARG or London (1): FC Everton

3.4. Same search with switched nations:ARG and city:Liverpool.

$sql = <<<SQL
SELECT * FROM clubs WHERE clubs MATCH ('nations:ARG city:Liverpool OR city:London')
SQL;
doQuery($db, $sql, "ARG and Liverpool or London");

The same result as in the 3.1. example.

ARG and Liverpool or London (3): Tottenham Hotspur, West Ham United, FC Everton

Question

I got no clue how logical expressions are interpreted. Can anybody explain?

SQLite not loading data upon activity starting in android

Problem: The user is matched with another user, and the game begins. The user is given some words, which are taken from the SQLite database. The problem is, when the activity starts, the area where the words should be is empty. The log does not show any errors. Here is the relevant code.

The database class where all the words are kept:

public class WordsListDatabaseHelper extends SQLiteOpenHelper {

protected static Set<String> mSelectedWords;
private static final String DB_NAME = "GAME_TABLE";
private static final int DB_VERSION = 1;
protected static LinkedList<String> mCopy;
public static int gameNumber = 0;


WordsListDatabaseHelper(Context context) {
    super(context, DB_NAME, null, DB_VERSION);
}

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL("CREATE TABLE GAME ("
                    + "_id INTEGER PRIMARY KEY AUTOINCREMENT, "
                    + "SELECTED_WORDS TEXT, "
                    + "GAME_NUMBER INTEGER);"
    );



    Collection<String> wordList = new LinkedList<String>();
    mSelectedWords = new LinkedHashSet<String>();

    wordList.add("ant");
    wordList.add("almond");
      //lots more words

    mCopy = new LinkedList<String>(wordList);

    Gson gson = new Gson();
    String wordsContainer = gson.toJson(mCopy);
    insertWordList(db, wordsContainer, gameNumber);

}

private static void insertWordList(SQLiteDatabase db, String wordsContainer, int gameNumber) {
    ContentValues wordValues = new ContentValues();

    Gson gson = new Gson();
    Collections.shuffle(mCopy);
    mSelectedWords.addAll(mCopy.subList(1,7));
    wordsContainer = gson.toJson(mSelectedWords);

    wordValues.put("SELECTED_WORDS", wordsContainer);
    wordValues.put("GAME_NUMBER", gameNumber);
    db.insert("GAME", null, wordValues);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}

}

Here is the relevant code from FindingOpponentsActivity, which randomly matches the user with an opponent. When the match is created, the user is taken to StartGameActivity.

  Intent intentRand = new Intent(FindingOpponentActivity.this, StartGameActivity. .class);
                    int gameNum = WordsListDatabaseHelper.gameNumber;
                    intentRand.putExtra(
                            StartGameActivity.EXTRA_RAND_OPPONENT,
                            mOpponent.getObjectId());

                    intentRand.putExtra(
                            StartGameActivity.EXTRA_GAME_NUMBER,
                            gameNum);

                    sendPushNotification();
                    startActivity(intentRand);
                }

And now, this is the code in the onCreate() method of StartGameActivity:

  Intent intent = getIntent();
    mGameNum = intent.getIntExtra(EXTRA_GAME_NUMBER, 0);


    //create a cursor

    try {
        SQLiteOpenHelper wordsListDatabaseHelper = new WordsListDatabaseHelper(this);
        SQLiteDatabase db = wordsListDatabaseHelper.getReadableDatabase();
        Cursor cursor = db.query("GAME",
                new String[] {"SELECTED_WORDS"},
                "GAME_NUMBER =? ",
                new String[]{Integer.toString(mGameNum)},
                null, null, null);

        //move to the first record in the Cursor

        if (cursor.moveToFirst()) {
            //get wordList
            String savedWordList  = cursor.getString(0);

            Gson gson = new Gson();
            Type type = new TypeToken<ArrayList<String>>() {}.getType();
            ArrayList<String> finalWordList = gson.fromJson(savedWordList, type);
            mCopy = new LinkedList<String>();
            mCopy.addAll(finalWordList);


            word1 = (TextView) findViewById(R.id.word1);
            word1.setText(mCopy.get(1));

            word2 = (TextView) findViewById(R.id.word2);
            word2.setText(mCopy.get(2));

Please let me know if there is any more code that I need to post.

EDIT I forgot to mention that I was getting that the log was registering this error message:

 09-29 13:48:42.572  13689-13720/? E/SQLiteLog﹕ (1) no such table: mmsconfig

I checked over and over again for the problem, but I am unable to find where I am going wrong.

Android dev: Sync database

This is my first time writing an Android app that utilizes a database.

I have created a test database in the assets folder. I then have the app copy the database to the database folder: /data/data/myapplication1/databases/myDB.db

I realize now that the assets folders are read only, but how can I sync my database so I can verify, using SQLite browser, the changes I have made?

Should I just move my test DB from assets to a different location? Is there something better?

Or is there a way I can view the DB in the databases folder? (which I believe is protected so it is not visible to other programs)

How to do class-level queries for recursive @hybrid_property in SQLAlchemy

I have two classes in SQLAlchemy such as:

class Local(Base):
    id = Column(Unicode, primary_key=True)
    denom = Column(String)
    parent = Column(String)
    children = relationship("Local", backref=backref('parent', remote_side=[id]))

    @hybrid_property
    def anp(self):
        if self.denom.startswith(u"ANP_"):
            return self.denom[:6]
        elif self.parent:
            return self.parent.anp
        return None


class Equipament(Base):
    id = Column(Unicode, primary_key=True)
    local = relationship("Local", backref=backref('eqps', order_by=id))

    @hybrid_property
    def anp(self):
        if self.local is None:
            return None
        else:
            return self.local.anp

This works when checking for the anp property such as when equipment_instance.anp, but how do I build a proper query when trying to list all available equipments which meet a certain criteria for the property?

For example, I may try:

>>> loc = db.session.query(db.Local).first()
>>> loc.anp
u'ANP_MF'

But I can't do:

>>> loc = db.session.query(db.Local).filter(db.Local.anp==u'ANP_MF').first()
Traceback (most recent call last):
...
TypeError: Boolean value of this clause is not defined

Alternatively, I might use:

>>> db.session.query(db.Local).filter(db.Local.denom.like(u"ANP_%")).first()

But then I'd have to recurse into its children and join results manually.

So, is it possible to fix this so a simpler query might be used? I'm using SQLAlchemy 1.0.5 with SQLite.