TOPIC: Limesurvey 2.0+ with sqlite

I'm using limesurvey as offline data input component, and sqlite works quite well (after few code modifications) in these circumstances. Although, I'm not entirely sure, if my modifications don't break something important, that I cannot find.

Main problem is (apart from limited ALTER TABLE support in sqlite, which I worked around in quite unelegant way, quoted below) sqlite's lack of RIGHT JOIN support. RIGHT JOIN appears in LS2 code four times (once in activate_helper.php, twice in frontend_helper.php, and once in user.php), but from database structure and query syntax I cannot understand if this need to be right join neccesarily. At the moment I simply substituted RIGHT JOIN with LEFT OUTER JOIN, tested everything from installation to import of new survey to data input to limesurvey update, and don't encountered (apart from when debug=2) any strange nor undesirable behavior.

If anyone is interested in going with this direction, I can prepare diff from Build 130305 for working sqlite support after some cleaning.

The workaround code for yii framework is:

publicfunction alterColumn($table,$column,$type){//// This is dangerous as hell and should never be used in production - better// approach would be creating new table and moving all data.//$table=$this->loadTable($table);$columns=Array();foreach($table->columnsas$col){$columns[$col->name]=$col->dbType;}$cmd="PRAGMA writable_schema = 1;\n";$tablename=$this->getDBConnection()->tablePrefix.str_replace(array('{{','}}'),'',$table->name);$columns[$column]=$type;$cmd.="UPDATE SQLITE_MASTER SET SQL = ".$this->getDBConnection()->quoteValue($this->createTable($tablename,$columns))." WHERE type = [table] and name = ".$this->getDBConnection()->quoteTableName($tablename).";\n";$cmd.="PRAGMA writable_schema = 1;\n";$cmd.="VACUUM;\n";return$cmd;// throw new CDbException(Yii::t('yii', 'Altering a DB column is not supported by SQLite.'));}

and some minor column name and table name quoting fixes. I fully understand, that this approach is dangerous, and shouldn't be used at all - yet it works

but from database structure and query syntax I cannot understand if this need to be right join neccesarily. At the moment I simply substituted RIGHT JOIN with LEFT OUTER JOIN, tested everything from installation to import of new survey to data input to limesurvey update, and don't encountered (apart from when debug=2) any strange nor undesirable behavior.

These certain JOPIn operations are used for more efficient DB queries. You should be able to replace all of them with a UNION JOIN or rewrite them to use a LEFT join.
It helps to do the query directly on the DB using tools like phpmyadmin to then compare the maybe different results.

Making Limesurvey use SQLite would for sure be a nice thing, so please send us a pull request when you have tested everything carefully.

Can you explain how you use Limesurvey offline a little more so we can evaluate to which extend our users might make use of SQLite?!

Well, I am not an expert on this, but from what I have seen so far, Lighthttpd only runs on rooted Android devices. I assume that Android would be the way to go, because it should be most widespread.

However, I think most users want something easy to install (an app) and not root their phone or tablet and then install a webserver on it.

Don't get me wrong, I think it would be very interesting for Limesurvey to go into this direction (there are quite a few survey tools out there that offer online and offline surveys with the same system, once you are online again, the data is sent to the server). This is great because you can use one system for different approaches (online, telephone, tablet, etc.). I think all of those approaches were based on ASP. So I am not sure if something similar is possible with PHP based Limesurvey.

I am not sure if with the new features of HTML5 it would be possible to let LS run without the webserver until the internet connection is back.

For development the easiest way would be to have a package with limesurvey, webserver and database running on Android. However, I don't know about performance and it would have to be very easy to install.

Just to give you an idea how this usually works: The tablets are usually rented, because they are only needed for specific projects and often in big quantities.
So what is important is easy installation and of course easy data merger. Often the interviewer will have to send the data to the project leader so the files can be merged. If this is necessary, it must be very simple because a lot of interviewers have no idea about IT and computers. There is nothing worse than having the interviews completed, but not being able to receive the data.