Welcome, Guest
Username: Password: Remember me
  • Page:
  • 1
  • 2

TOPIC: Limesurvey 2.0+ with sqlite

Limesurvey 2.0+ with sqlite 1 year 7 months ago #93377

  • krefik
  • krefik's Avatar
  • OFFLINE
  • Fresh Lemon
  • Posts: 3
  • Thank you received: 1
  • Karma: 0
Hi,

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:
	public function 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->columns as $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 :)
The administrator has disabled public write access.
The following user(s) said Thank You: DenisChenu

Limesurvey 2.0+ with sqlite 1 year 7 months ago #93413

  • DenisChenu
  • DenisChenu's Avatar
  • OFFLINE
  • Moderator Lime
  • Posts: 6435
  • Thank you received: 840
  • Karma: 249
An SQlite possibility for LS can be a great idea !

Maybe you can put this in our bug report : bugs.limesurvey.org

Choose developpement or feature request
The administrator has disabled public write access.

Limesurvey 2.0+ with sqlite 1 year 7 months ago #93454

  • krefik
  • krefik's Avatar
  • OFFLINE
  • Fresh Lemon
  • Posts: 3
  • Thank you received: 1
  • Karma: 0
Ok, all initial tests looks quite good - at the moment I have to clean up the code, I should be done about monday afternoon.

Anyone know if those RIGHT JOINs need to be RIGHT? In what circumstances does it matter?
The administrator has disabled public write access.

Limesurvey 2.0+ with sqlite 1 year 7 months ago #93461

  • DenisChenu
  • DenisChenu's Avatar
  • OFFLINE
  • Moderator Lime
  • Posts: 6435
  • Thank you received: 840
  • Karma: 249
For right join, maybe, can be:
- A bad database structure ?
- Multilingual ?

I look further.

For the " yii framework" patch, did you have a forum post in Yii forum ?
Maybe we can do a
LsAlterColumn function with this patch.

If you have a github account, maybe you can do a fork and leave the link here.

Denis
The administrator has disabled public write access.

Limesurvey 2.0+ with sqlite 1 year 7 months ago #93536

  • Mazi
  • Mazi's Avatar
  • OFFLINE
  • LimeSurvey Team
  • Posts: 5331
  • Thank you received: 296
  • Karma: 249
krefik wrote:
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?!

Best regards/Beste Grüße,
Dr. Marcel Minke
(Limesurvey Head of Support)
Need Help? We offer professional Limesurvey support
Contact: marcel.minke(at)limesurvey.org'"
The administrator has disabled public write access.

Limesurvey 2.0+ with sqlite 1 year 7 months ago #93541

  • DenisChenu
  • DenisChenu's Avatar
  • OFFLINE
  • Moderator Lime
  • Posts: 6435
  • Thank you received: 840
  • Karma: 249
Mazi wrote:
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?!
With SQLlite: no need another server: included in PHP.

Then we need only Lighttpd and PHP : seem to be done on a iphone.

Denis
The administrator has disabled public write access.

Limesurvey 2.0+ with sqlite 1 year 7 months ago #93543

  • Mazi
  • Mazi's Avatar
  • OFFLINE
  • LimeSurvey Team
  • Posts: 5331
  • Thank you received: 296
  • Karma: 249
Yeah, I know the advantage of using SQLite, just wanted to know if krefik has already tested this in a certain project.

Denis, I think creating a tutorial of how to use Limesurvey on a smartphone or tablet would be very interesting for our users. Do we want to give that a try once krefik has send a pull request?

There is a large user base planning to do surveys on tablets, but so far most of them used a netbook with local XAMPP for this.
SQLite + Lighttpd would be a huge step forward.

Best regards/Beste Grüße,
Dr. Marcel Minke
(Limesurvey Head of Support)
Need Help? We offer professional Limesurvey support
Contact: marcel.minke(at)limesurvey.org'"
The administrator has disabled public write access.

Limesurvey 2.0+ with sqlite 1 year 7 months ago #93545

  • holch
  • holch's Avatar
  • NOW ONLINE
  • LimeSurvey Team
  • Posts: 2851
  • Thank you received: 358
  • Karma: 124
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.
Have a look at the manual! It is a really valuable source for information. Here some helpful links:
Manual (EN) | Question Types | Question Attributes | Workarounds

If you found this answer helpful and it saved you some time please consider a donation to the project to keep Limesurvey going!
Last Edit: 1 year 7 months ago by holch.
The administrator has disabled public write access.

Limesurvey 2.0+ with sqlite 1 year 7 months ago #93551

  • DenisChenu
  • DenisChenu's Avatar
  • OFFLINE
  • Moderator Lime
  • Posts: 6435
  • Thank you received: 840
  • Karma: 249
holch wrote:
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.
With Android, you can have AAMP (with MySQL) (and some AAMP in play.google *), found this for iphone: geekfault.org/2009/05/27/lighttpd-php-iphone/
Denis, I think creating a tutorial of how to use Limesurvey on a smartphone or tablet would be very interesting for our users. Do we want to give that a try once krefik has send a pull request?
Maybe i con help, WHEN i have a smartphone ;) or a tablet.

* : play.google.com/store/apps/details?id=ru.kslabs.ksweb&hl=fr is an example
The administrator has disabled public write access.
The following user(s) said Thank You: Ben_V

Limesurvey 2.0+ with sqlite 1 year 7 months ago #93555

  • holch
  • holch's Avatar
  • NOW ONLINE
  • LimeSurvey Team
  • Posts: 2851
  • Thank you received: 358
  • Karma: 124
I have seen ksweb, and I think it is even installed on the computer, but I think the test period was so short, that I didn't have time to play around.

Problem is, that you have to buy that for each tablet, especially when they are rented. Or am I seen something wrong?

in the play store I couldn't find anything under AAMP.
Have a look at the manual! It is a really valuable source for information. Here some helpful links:
Manual (EN) | Question Types | Question Attributes | Workarounds

If you found this answer helpful and it saved you some time please consider a donation to the project to keep Limesurvey going!
The administrator has disabled public write access.
  • Page:
  • 1
  • 2
Moderators: ITEd
Time to create page: 0.190 seconds
Donation Image