Update your Magento SQLite3 database with Vala

  • SumoMe

 

Let’s update our magento SQLite3 database

Remember when we were trying to help Magento’s autoload with a SQLite3 database?

I mentioned that I was going to show you how to use Vala to help read and write to that database and offload the operation to the system instead of PHP.

Make sure that you have the dependencies to compile the code, in Ubuntu:

sudo apt-get install libsqlite

Here is the code:

using GLib;
using Sqlite;
void main (string[] args) {
    Database db;
    Statement stmt;
    Statement upd;
    int rc = 0;
    int col, cols;
    var db_path = "classes.db"; //better if we pass a setting or as an argument
    //This is a function in my class
    if ((rc = Database.open (db_path, out db)) == 1) {
        printerr ("Can't open database: %s\n", db.errmsg ());
        return;
    }
    //this is a function in the error checking class
    var select = "select filename from classes";
    if ((rc = db.prepare_v2 (select, -1, out stmt, null)) ==  Sqlite.ERROR) {
        printerr ("SQL error: %d, %s\n", rc, db.errmsg ());
        return;
    }
    cols = stmt.column_count();
    //This is a function in my class
    string[] paths = new string[4];
    paths[0]    =   "app/code/local/";
    paths[1]    =   "app/code/community";
    paths[2]    =   "app/code/core/";
    paths[3]    =   "lib/";
    do {
        rc = stmt.step();
        switch (rc) {
        case Sqlite.ROW:
            for (col = 0; col < cols; col++) { //needs to be written with a foreach
                string txt = stmt.column_text(col);
                int update_file_exists = 0;
                //This is a function in my class
                foreach (string path in paths){
                     string file = path + txt;
                      if (!FileUtils.test (file, FileTest.IS_REGULAR)){
                         update_file_exists = 0;
                      } else{
                        update_file_exists = 1;
                      }
                }
                //This is a function in my class
                int r = 0;
                string update = "UPDATE classes set file_exists = %d where filename like '%s';".printf(update_file_exists,txt);
                if ((r = db.prepare_v2 (update, -1, out upd, null)) == Sqlite.ERROR) {
                    printerr ("SQL error: %d, %s\n", rc, db.errmsg ());
                    return;
                }
                print ("%s \n", update);
                r = upd.step();
                if (r == Sqlite.OK)
                {
                    break;
                }
            }
            break;
        default:
        case Sqlite.DONE:
            break;
        }
    } while (rc == Sqlite.ROW);
}

If you’re interested in Vala and SQLite3 databases I recommend you read SQLite VAPI or use this wrapper.

For the sake of simplicity I just shoved all the code together in the main function. Locally, I have everything in different classes and functions. Look at some of the comments for more info.

Now let’s compile it:

valac --pkg sqlite3 -o sqlite sqlite.vala

Now we can run it like this:

./sqlite

Note that the database should be in the same directory. If you don’t want this behavior, be sure to update the database path.

Now all we need is to set up the cron job:

crontab -e

Add this to it * */4 * * * path_to_command. Keep in mind that in my case path_to_command was ~/vala/sqlite and you should change yours appropriately. This cron will run every 4 hours and confirm your files. So PHP only needs to get them from the SQLite3 database. One problem that you may find is that I didn’t implement any update functionality in the autoload class and that may yield some bad results.

What’s next with our SQLite3 databases experiments?

We can start scheduling when a file should be checked and use the last checked column for that purpose. There are a bunch of “small” ideas being thrown out there but nothing is confirmed yet.

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Author: Luis Tineo

Husband, Father, performance improvement junkie, biker and video gamer, Linux user and in my day job I'm a Software Engineer at BuyerQuest.

Share This Post On
  • Pingback: use sqlite3 with magento()

  • http://www.mindinventory.com/hire-magento-developers.php magento developer

    Magento has used the SQLlite3 database for update the
    database query. SQLlite3 is very useful for the Magento web development.

    VA:F [1.9.22_1171]
    Rating: 0.0/5 (0 votes cast)
    VA:F [1.9.22_1171]
    Rating: 0 (from 0 votes)
    • http://www.kingletas.com Luis Tineo

      Woah so glad I am not alone :-D, thanks for validating that I am not mad!!!

      VN:F [1.9.22_1171]
      Rating: 0.0/5 (0 votes cast)
      VN:F [1.9.22_1171]
      Rating: 0 (from 0 votes)