Android SQLite Test

Android SQLite Test

Most of the secret of implementing SQLite Test with JUnit unit tests, is in the use of the assert methods in the class org.junit.Assert. In this text I will take a closer look at what assert methods are available in this class.

The most easy way is to use InstrumentationRegistry in android test package

mDataSource = new RateDataSource(InstrumentationRegistry.getTargetContext());

We can start with a normal SQLite class with CRUD (Create, Read, Update and Delete).

public class RateDataSource {
    // Database fields
    private SQLiteDatabase mDatabase;
    private MySQLiteHelper mDbHelper;
    private String[] mAllColumns = {MySQLiteHelper.COLUMN_ID,
            MySQLiteHelper.COLUMN_COIN, MySQLiteHelper.COLUMN_VALUE};

    public RateDataSource(Context context) {
        mDbHelper = new MySQLiteHelper(context);
    }

    public void open() throws SQLException {
        mDatabase = mDbHelper.getWritableDatabase();
    }

    public void close() {
        mDbHelper.close();
    }

    public Rate createRate(String coin, double value) {
        ContentValues values = new ContentValues();
        values.put(MySQLiteHelper.COLUMN_COIN, coin);
        values.put(MySQLiteHelper.COLUMN_VALUE, value);
        long insertId = mDatabase.insert(MySQLiteHelper.TABLE_RATE, null,
                values);
        Cursor cursor = mDatabase.query(MySQLiteHelper.TABLE_RATE,
                mAllColumns, MySQLiteHelper.COLUMN_ID + " = " + insertId, null,
                null, null, null);
        cursor.moveToFirst();
        Rate newComment = cursorToRate(cursor);
        cursor.close();
        return newComment;
    }

    public void deleteRate(Rate comment) {
        long id = comment.getId();
        System.out.println("Rate deleted with id: " + id);
        mDatabase.delete(MySQLiteHelper.TABLE_RATE, MySQLiteHelper.COLUMN_ID
                + " = " + id, null);
    }

    public List<Rate> getAllRates() {
        List<Rate> rates = new ArrayList<Rate>();

        Cursor cursor = mDatabase.query(MySQLiteHelper.TABLE_RATE,
                mAllColumns, null, null, null, null, null);

        cursor.moveToFirst();
        while (!cursor.isAfterLast()) {
            Rate rate = cursorToRate(cursor);
            rates.add(rate);
            cursor.moveToNext();
        }
        // make sure to close the cursor
        cursor.close();
        return rates;
    }

    private Rate cursorToRate(Cursor cursor) {
        Rate rate = new Rate();
        rate.setId(cursor.getLong(0));
        rate.setCoin(cursor.getString(1));
        rate.setValue(cursor.getDouble(2));
        return rate;
    }

    public void deleteAll() {

        mDatabase.delete(MySQLiteHelper.TABLE_RATE, null, null);
    }
}

After that you can start with your unit test, don’t forget to open and close database:

import android.support.test.InstrumentationRegistry;
import android.support.test.runner.AndroidJUnit4;
import android.test.suitebuilder.annotation.LargeTest;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;

import java.util.List;

import static junit.framework.Assert.assertNotNull;
import static junit.framework.Assert.assertTrue;
import static org.hamcrest.CoreMatchers.is;
import static org.hamcrest.MatcherAssert.assertThat;

@RunWith(AndroidJUnit4.class)
@LargeTest
public class SQLiteTest {

    private RateDataSource mDataSource;

    @Before
    public void setUp(){
        mDataSource = new RateDataSource(InstrumentationRegistry.getTargetContext());
        mDataSource.open();
    }

    @After
    public void finish() {
        mDataSource.close();
    }

    @Test
    public void testPreConditions() {
        assertNotNull(mDataSource);
    }

    @Test
    public void testShouldAddExpenseType() throws Exception {
        mDataSource.createRate("AUD", 1.2);
        List<Rate> rate = mDataSource.getAllRates();

        assertThat(rate.size(), is(1));
        assertTrue(rate.get(0).toString().equals("AUD"));
        assertTrue(rate.get(0).getValue().equals(1.2));
    }

    @Test
    public void testDeleteAll() {
        mDataSource.deleteAll();
        List<Rate> rate = mDataSource.getAllRates();

        assertThat(rate.size(), is(0));
    }

    @Test
    public void testDeleteOnlyOne() {
        mDataSource.createRate("AUD", 1.2);
        List<Rate> rate = mDataSource.getAllRates();

        assertThat(rate.size(), is(1));

        mDataSource.deleteRate(rate.get(0));
        rate = mDataSource.getAllRates();

        assertThat(rate.size(), is(0));
    }

    @Test
    public void testAddAndDelete() {
        mDataSource.deleteAll();
        mDataSource.createRate("AUD", 1.2);
        mDataSource.createRate("JPY", 1.993);
        mDataSource.createRate("BGN", 1.66);

        List<Rate> rate = mDataSource.getAllRates();
        assertThat(rate.size(), is(3));

        mDataSource.deleteRate(rate.get(0));
        mDataSource.deleteRate(rate.get(1));

        rate = mDataSource.getAllRates();
        assertThat(rate.size(), is(1));
    }
}

you can see the complete code in GitHub

Send data from activity to fragment on android II

Send data from activity to fragment on android II
When it was instanceted of fragment, we can’t to pass data with parameter, The only way is to call fragment’s method from activity.

We need:
– Activity
– Fragment
We instance a fragment from activity. After that We will call a fragment’s method from the activity.

Complete code in github

You can check the activity.

package com.thedeveloperworldisyours.l;

import android.graphics.Color;
import android.support.v4.app.FragmentManager;
import android.support.v4.view.MenuItemCompat;
import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.support.v7.widget.SearchView;
import android.util.Log;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;

public class MainActivity extends AppCompatActivity {

    private static final String TAG = "MainActivity";
    private MenuItem mSearchMenuItem;

    private SearchView mSearchView;
    private String mSearchString;
    private static final String SEARCH_KEY = "search";

    FirstFragment mFirstFragment;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        // if you saved something on outState you can recover them here
        if (savedInstanceState != null) {
            mSearchString = savedInstanceState.getString(SEARCH_KEY);
        }
        mFirstFragment = new FirstFragment().newInstance();
        replaceFragment();
    }

    // This is called before the activity is destroyed
    @Override
    protected void onSaveInstanceState(Bundle outState) {
        super.onSaveInstanceState(outState);
        mSearchString = mSearchView.getQuery().toString();
        outState.putString(SEARCH_KEY, mSearchString);
    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        getMenuInflater().inflate(R.menu.menu_main, menu);

        mSearchMenuItem = menu.findItem(R.id.menu_main_action_search);

        mSearchView = (SearchView) MenuItemCompat.getActionView(mSearchMenuItem);

        customSearView();

        mSearchView.setOnQueryTextListener(new SearchView.OnQueryTextListener() {
            @Override
            public boolean onQueryTextSubmit(String query) {
                return true;
            }

            @Override
            public boolean onQueryTextChange(String newText) {
                mFirstFragment.refreshString(newText);
                return false;
            }
        });

        return super.onCreateOptionsMenu(menu);
    }

    public void customSearView() {
        SearchView.SearchAutoComplete searchAutoComplete = (SearchView.SearchAutoComplete) mSearchView.findViewById(android.support.v7.appcompat.R.id.search_src_text);
        searchAutoComplete.setHintTextColor(Color.WHITE);
        searchAutoComplete.setTextColor(Color.WHITE);

        View searchPlate = mSearchView.findViewById(android.support.v7.appcompat.R.id.search_plate);
        searchPlate.setBackgroundResource(R.drawable.background_search);

        mSearchView.setIconifiedByDefault(false);
    }


    public void replaceFragment() {

        try {

            FragmentManager fragmentManager = getSupportFragmentManager();
            fragmentManager.beginTransaction().replace(R.id.content_main, mFirstFragment, "tag").commit();

        } catch (Exception e) {
            Log.d(TAG, e.toString());
        }

    }


}

Complete code in github

How we solved Magento core_url issues once and for all

website speedToday, we tell you the story of how we solved our Magento core_url issues, which might be very familiar and similar to you, since you’ve landed here.

If you have been working with Magento for long enough, chances are that sooner than later you’d have learnt that there are a two fundamental steps to follow if you are having issues:

  • It’s always the cache! Whenever something doesn’t work, clear the cache, and I mean, all of them! And the problem will (or not) go away, it’s like rebooting in Windows!
  • If that doesn’t work, run a full reindex, and repeat the above step 🙂

That’s why it’s is not only recommended, but also it’s very common to run reindexes daily, and most likely even more often, whenever we want to immediately reflect some changes in the front-end, such a new stock, new products, or new category-products assignments.

Reindexing often shouldn’t be a problem for small stores, but the more products you have, the more probable is that reindexing is going to cause you trouble due the long time that it takes, the amount of resources that it needs, and so on, as well as the fact that they need to run overnight to avoid slowing down your website dramatically.

Whenever a full site reindex is taking a long time, 99% of the cases “catalog_url” reindex is the one spending most of the time, specially if you have a large catalog of products and or if you have a multi-store setup.

Unfortunately or otherwise, that was our case at our bikes shop. We had several thousand products and, even though a big part of them are currently disabled, the reindex was taking far too long, and the table core_url_rewrite was growing exponentially, causing other problems such as slowing down the backup process, or preventing the sitemap generation from running, due big SQL queries taking far too long to run.

Like in most existing online stores, all of these could have been easily avoided if it had been addressed properly from the beginning, but you know… could’ve, would’ve, should’ve… but didn’t!

So, there we were, with a core_url_rewrite table with over 210k rows, and expanding beyond maintainability. We had only two chances:

  • Upgrade the servers and ignore/delay the problem until we reach to this same stage in a couple of months time.
  • Tackle the problem and invest the time needed to find out what’s going on, and sort it out properly

We love challenges, and the first option wasn’t even considered, as it wasn’t a solution really, so we got our hands on it and started to look into the possible causes.

We identified two major problems looking into core_url_rewrite table:

  • There were several thousand rows with URLs about products that were disabled or not visible individually. We had already installed Dn’D module that skips the reindex of disabled and not visible products, speeding up the reindex by keeping them out of the table, but there “leftovers” from the past, before the module was installed. If you don’t have Dn’D Patch Index Url module installed, please take a few minutes and install it right know, it’s a must have for any Magento store.
  • We noticed a very interesting issue: Every time we run a catalog_url reindex, the number of rows of the core_url_rewrite table was increased, even when there were no recent changes on the catalog. So, basically, the table was growing and growing each time without any obvious reason.

In order to address the first problem, we built two queries to identify the rows corresponding to:

Disabled products:

SELECT count(*) FROM core_url_rewrite WHERE product_id IN 
(SELECT entity_id FROM catalog_product_entity_int WHERE attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code ='status') AND VALUE = 2 AND entity_type_id = 4);

Roughly 2k rows.

Then, we did the same to identify the products with Visibility set to Not Visible Individually:

SELECT count(*) FROM core_url_rewrite WHERE product_id IN 
(SELECT entity_id FROM catalog_product_entity_int WHERE attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code ='visibility') AND VALUE = 1 AND entity_type_id = 4);

Nearly 8k rows.

Since the above queries return rows that correspond to products not being displayed on the front-end (because they are disabled of not visible in the catalogue), they can safely be removed without any impact on the front-end/SEO.

The infinite growing “duplicated” redirects on core_url_rewrite

This is a very interesting issue which is widely known among Magento developers. There is a good thread on StackExchange discussing it, where you can find responses of all kinds. In the above link, people have done a great job analysing the problem, and some even propose (partial) decent solutions to it, but given the complexity, many people have also given up to find a fix. In fact, Alan Storm himself, one of the most popular Magento developers advises so, and his answer is the second most voted, which by the way is pretty disappointing to say the least.

In our case, it was unacceptable to leave it as it was, because we were aware of the issue, and we had been delaying it for a while. Eventually, it was causing us serious issues, and it was having a pretty bad impact in the overall performance of our store, so it had to be addressed.

As we mentioned previously, every time we reindex the “catalog_url”, the amount of rows on the table core_url_rewrite was increasing, and we were trying to figure out why. It seems that the problem is caused when there are two or more products/categories with the same URL key. During the reindex, Magento checks whether the url key is unique, if it’s not, it appends the entity_id of the product to the URL to make it unique. The problem is that next time the reindex runs, it will do the same process, but the new URL (url_key + entity_id) already exists, so Magento tries increasing the numeric part of the url key (product’s entity id) until it finds a unused URL, and it keeps creating crazy redirects on each run.

One way to avoid/overcame this issue is making sure that the url_keys of the products are unique. We tested that approach in first place, running a query that modified the url_key (attribute of the product) of each product that has duplicates, appending the entity_id followed by a dash. Ie. product with id 123 and url_key “my_product” would become “my_product-123”. This way when the reindex runs, all URLs would be unique, and Magento wouldn’t have to do “his thing”. However, this would be messing up with existing URL redirects in core_rewrite_url and potentially would lead to broken links. Also, it wouldn’t fully resolve the issue, as nothing would be preventing new products from being created/imported with duplicated URL keys in the future, so we had to find a better solution.

Since many people had been investigating and debugging this problem, we decided to have a look at the proposed solutions on StackExchange and tested some of them. The solution proposed by @Simon was the one that made more sense for us, and it seemed to do the job. After applying the code changes, reindexing wouldn’t increase the number of rows of the table. However, the proposed solution was overriding the core, which is something we never do unless it’s something unavoidable. It’s a nice solution, wrapped  as an “unofficial Magento patch”, but we didn’t want to take the risk of having future upgrade/patching issues. So, we decided to create a new Magento extension, which would override the model Mage/Catalog/Model/Url.php, and we put Simon’s fix there, leaving the core untouched. It all seemed to work fine, and the table core_url_rewrite stabilised so the first step was done, now it was time for the tricky part, the remaining “leftovers”.

We had 210k rows (well, actually 200k after the previous clean-up of disabled/not visible products) on the core_url_rewrite table, but as we mentioned previously, we have only a couple of thousand products enabled in our catalog, so presumably many rows were not really “needed”, and most likely the “oversize” of the table was related to the infinite rewrites/redirects.

Looking at the latest entries of the core_url_rewrite, there was a clear pattern on the redirects:

Product_id Request_path Target Path
1234 product-url-key-1234.html product-url-key-1235.html
12 product-url-key-13.html product-url-key-14.html

Both, request_path and target_path look the same except for the number prepended to the url_key. So, we came up with this query to identify all the redirects:

SELECT count(*)  FROM core_url_rewrite WHERE product_id IS NOT NULL AND LEFT(request_path, length(`request_path`) - 5 - length(product_id)) = LEFT(target_path, length(`target_path`) - 5 - length(product_id)) AND
is_system = 0

Result: 195k rows!!! Unbelievable, we thought those rewrites were bad, but we didn’t expect that they would be THAT BAD!

Right, so we wanted to get rid of all that “junk” redirects, but we couldn’t just delete them. Well, technically we could, but that could have a very bad SEO impact, it would generate many 404 errors for products already indexed in the search engines, and there would be existing links to the products in newsletters, social media, etc. So we needed an strategy to fix it.

After a lot of testing and back and forth, eventually we did the following:

  • Create a table core_url_rewrite_tmp which contains only the redirects (can be done with one query, using create table as select):
    SELECT category_id, product_id, store_id, request_path, target_path  FROM core_url_rewrite WHERE product_id IS NOT NULL AND LEFT(request_path, length(`request_path`) - 5 - length(product_id)) = LEFT(target_path, length(`target_path`) - 5 - length(product_id));
  • Override Mage_Cms_IndexController::noRouteAction to capture 404 events
  • Then, whenever a 404 happens, check if the request matches the below pattern: '/\\/(.*)-\\d+.html/'
    • If it doesn’t, let Magento handle the 404 normally
    • If it does, query our new table and search for matches on the current store, fetching the product_id or category_id.
      • If there are no matches, let Magento handle the 404 normally
      • If there are matches, redirect the user to the corresponding product/category (301 – permanent redirect) URL

After having this kind of fallback mechanism, the redirect entries can be removed from the core_url_rewrite table.

Then, the core_url_rewrite table is completely clean and light, which, being one of the critical and most used and joined tables of Magento,  has a shocking impact in the overall performance of the site, not to mention the massive speed increase on heavy tasks such as sitemap generation, core url reindex, and so on.

In our case, as a result of this whole clean-up, the core_url_rewrite now has 5k rows. We still have to keep the “core_url_rewrite_tmp”, but that table is queried in very rare occasions (vs the frequency of core_url_rewrite), when an “old URL” is requested.

Last but not least, since we are sending all the requests to the right destination with a 301 redirect, within a couple of months, all the search engines would have updated their indexes and the table can be “safely” removed.

Note that there might still be old links in emails, social media, etc., but the fallback can then be amended to redirect users either to the catalog search (searching for the requested URI), or it can do a best-match on the core_url_rewrite and redirect the user to  the closest match. This won’t be 100% accurate as the products with the duplicated URL keys would have similar url keys/names. However, on this fast moving age, after 6 months those links are highly unlikely to be hit.

We deleted our table after 3 months, and two weeks later we haven’t found any related 404 in our reports.

Finally, our zipped database (excluding orders) dropped from 80Mb to 5Mb, all our sitemap generation and reindex issues were resolved, and our website overall speed/performance improved noticeably, so we couldn’t be happier 🙂

Communicating between fragment and activity

Communicating between fragment and activity


The easiest way to communicate between your activity and fragments is using interfaces or parameters.

There are different ways to communicate:

*From activity to Fragment:

1. When it is instantiated, We can pass a parameter (Send data from activity to fragment in android)
2. When it was instantiated, We can call a fragment’s method from activity.(Send data from activity to fragment on android II)
3. When we use a TabBar with some fragments, We use a interface in our Adapter.(Communicating activity with fragments in TabBar)

*From fragment to activity:

1. When we need to communicate with activity from fragment, we use a interface. The idea is basically to define an interface a given fragment with onAttach(Activity activity) and onDetach(), and let the activity implement that interface.(Listener from fragment to activity)

Communicating activity with fragments in TabBar

Communicating activity with fragments in TabBar

We need
– Interface.
– Actvity.
– ManagerFragment.
– Adapter
– FirstFragment.
– SecondFragment.


Complete code in github

First Step

In our Activity we will call a Manager fragment

mManagerFragment.update(newText);

You can check the complete activity

public class MainActivity extends AppCompatActivity {

    private static final String TAG = "MainActivity";
    private MenuItem mSearchMenuItem;

    private SearchView mSearchView;

    ManagerFragment mManagerFragment;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
       
        mManagerFragment = new ManagerFragment().newInstance();
        replaceFragment();
    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        getMenuInflater().inflate(R.menu.menu_main, menu);

        mSearchMenuItem = menu.findItem(R.id.menu_main_action_search);

        mSearchView = (SearchView) MenuItemCompat.getActionView(mSearchMenuItem);

        mSearchView.setOnQueryTextListener(new SearchView.OnQueryTextListener() {
            @Override
            public boolean onQueryTextSubmit(String query) {
                return true;
            }

            @Override
            public boolean onQueryTextChange(String newText) {
                //sent to ManagerAdapter
                mManagerFragment.update(newText);
                return false;
            }
        });

        return super.onCreateOptionsMenu(menu);
    }


    public void replaceFragment() {

        try {

            FragmentManager fragmentManager = getSupportFragmentManager();
            fragmentManager.beginTransaction().replace(R.id.content_main, mManagerFragment, "tag").commit();

        } catch (Exception e) {
            Log.d(TAG, e.toString());
        }

    }
    
}

Now in Our ManagerFragment we received the string in this method

public void update(String string)

In Our Fragment we send the string from Manager To Adapter

public void update(String string){
        mAdapter.update(string);
    }

This is ManagerFragment

public class ManagerFragment extends Fragment {

    private ViewPager mViewPager;
    private TabsAdapter mAdapter;

    public ManagerFragment() {
        // Required empty public constructor
    }

    public static ManagerFragment newInstance() {
        ManagerFragment fragment = new ManagerFragment();
        return fragment;
    }

    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
    }

    @Override
    public View onCreateView(LayoutInflater inflater, ViewGroup container,
                             Bundle savedInstanceState) {
        // Inflate the layout for this fragment
        View view = inflater.inflate(R.layout.fragment_manager, container, false);

        TabLayout tabLayout = (TabLayout) view.findViewById(R.id.fragment_manager_tab_bar_tab_layout);
        mViewPager = (ViewPager) view.findViewById(R.id.fragment_manager_tab_bar_pager);

        mAdapter = new TabsAdapter(getFragmentManager(), getActivity());

        mViewPager.setAdapter(mAdapter);
        tabLayout.setupWithViewPager(mViewPager);

        return view;
    }

    //received from Activity
    public void update(String string){
        //sent to Adapter
        mAdapter.update(string);
    }

}

Second Step

This is the most importa step
In Our adapter We receive from ManagerFragment

//received from ManagerFragment
    public void update(String string) {
        mGeneralString = string;
        //updated
        notifyDataSetChanged();
    }

And we sent to FirstFragment and SecondFragment

@Override
    public int getItemPosition(Object object) {
        if (object instanceof UpdateableFragmentListener) {
            //sent to FirstFragment and SecondFragment
            ((UpdateableFragmentListener) object).update(mGeneralString);
        }
        return super.getItemPosition(object);
    }

You can see the adapter

public class TabsAdapter extends FragmentStatePagerAdapter {

    public static final int TOTAL_TABS = 2;
    public String mGeneralString;
    public Context mContext;

    public TabsAdapter(FragmentManager fm, Context context) {
        super(fm);
        mContext = context;
    }

    @Override
    public Fragment getItem(int position) {
        switch (position) {
            case 0:
                return new FirstFragment().newInstance();

            case 1:
                return new SecondFragment().newInstance();
        }
        return null;
    }

    //received from ManagerFragment
    public void update(String string) {
        mGeneralString = string;
        //updated
        notifyDataSetChanged();
    }

    @Override
    public int getItemPosition(Object object) {
        if (object instanceof UpdateableFragmentListener) {
            //sent to FirstFragment and SecondFragment
            ((UpdateableFragmentListener) object).update(mGeneralString);
        }
        return super.getItemPosition(object);
    }

    @Override
    public int getCount() {
        return TOTAL_TABS;
    }

    @Override
    public CharSequence getPageTitle(int position) {
        switch (position) {
            case 0:
                return mContext.getString(R.string.fragment_first_title);
            case 1:
                return mContext.getString(R.string.fragment_second_title);
            default:
                return mContext.getString(R.string.fragment_first_title);
        }
    }
}

And our listener is a simple interface

 
public interface UpdateableFragmentListener {
    public void update(String string);
}

Third Step

In FirstFragment and SecondFragment we receive the string with our Listener,
We implement the listener

public class FirstFragment extends Fragment implements UpdateableFragmentListener{
//received from Adapter with our Listener
    @Override
    public void update(String string) {
        mTextView.setText(string);
    }

you can see the simple Fragment

public class FirstFragment extends Fragment implements UpdateableFragmentListener{

    TextView mTextView;

    public FirstFragment() {
        // Required empty public constructor
    }

    public static FirstFragment newInstance() {
        FirstFragment fragment = new FirstFragment();
        return fragment;
    }

    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
    }

    @Override
    public View onCreateView(LayoutInflater inflater, ViewGroup container,
                             Bundle savedInstanceState) {
        // Inflate the layout for this fragment
        View view = inflater.inflate(R.layout.fragment_first, container, false);
        mTextView = (TextView) view.findViewById(R.id.fragment_first_text);
        return view;
    }

    //received from Adapter with our Listener
    @Override
    public void update(String string) {
        mTextView.setText(string);
    }
}

Complete code in github