Ticket #1727 (closed Feature Requests: wontfix)

Opened 6 years ago

Last modified 5 years ago

sqlite3 support is missing, sqlite2 should be deprecated

Reported by: diego_iastrubni Assigned to: diego_iastrubni
Priority: major Milestone: 2.5
Component: Sqlite3 Version:
Keywords: sqlite Cc:
Confirmation: Confirmed Distro:
Backend Engine: All Distro Ver:
Backend Ver: SVN Revision (if applicable):

Description

I would like to remove completely the support for sqlite2, and integrate sqlite3 support.

I would like to use the same new-install.sql file for MySQL and SQLite3. I would also like to remove the code duplication found in various function, and use the lower common SQL language supported by the two SQL drivers. Even if it means working harder in PHP.

Started working on this in trunk, see changeset:3662 (or 3661 on your CLI). I would like to add support for sqlite3 in the branch, and remove sqlite2 in the trunk. I would like to start the merging of the newinstall.sql files in the trunk. On the branch, having 3 newinstall.sql files is bad, but it's the best solution for this situation.

I would like to get your opinion about this subject.

Attachments

newinstall.sql (13.5 kB) - added by diego_iastrubni on 03/03/07 21:33:52.
Proposed newinstall.sql file
newinstall-sqlite3.patch (1.8 kB) - added by diego_iastrubni on 03/03/07 22:09:53.
Patch for current SVN trunk, with all proposed fixes.

Change History

01/28/07 09:44:40 changed by diego_iastrubni

With the supplied patch, on the 2.2 branch, the following modules do not install propely:

callback cidlookup disa findmefollow ivr recordings speeddial

All these modules will have to be patched, in order to work properly under sqlite3. If possible, even in the branch.

02/12/07 21:51:33 changed by diego_iastrubni

  • status changed from new to assigned.

The announcement module was fixed in version 0.7.0 to work properly with SQLite3. Confirmed.

02/12/07 22:41:06 changed by diego_iastrubni

misc destinations module is broken. see ticket:1773

02/12/07 23:01:14 changed by diego_iastrubni

time conditions module is broken. see ticket:1774

02/13/07 00:08:45 changed by diego_iastrubni

miscapps modules is broken. see ticket:1775

02/13/07 00:34:58 changed by diego_iastrubni

manager module is broken. see ticket:1776

02/13/07 01:03:02 changed by diego_iastrubni

inventorydb module is broken, see ticket:1777

02/14/07 02:25:03 changed by diego_iastrubni

pinsets module is broken. see ticket:1778

02/14/07 03:39:32 changed by diego_iastrubni

phpagiconf module is broken. see ticket:1779

02/14/07 22:48:18 changed by diego_iastrubni

  • summary changed from sqlite3 is missing, sqlite2 should be deprecated to sqlite3 support is missing, sqlite2 should be deprecated.

02/15/07 01:10:38 changed by diego_iastrubni

disa module is broken. see ticket:1784

02/17/07 22:49:00 changed by diego_iastrubni

findmefollow module is broken. see ticket:1791

02/18/07 00:14:12 changed by diego_iastrubni

ivr module is broken. see ticket:1972

02/18/07 00:15:59 changed by diego_iastrubni

vr module is broken. see ticket:1792 [ oops :) ]

02/18/07 01:02:19 changed by diego_iastrubni

callback module is broken. see ticket:1793

02/18/07 01:27:19 changed by diego_iastrubni

recordings module is broken. see ticket:1794

02/18/07 04:12:32 changed by diego_iastrubni

ringgroups module is broken. see ticket:1795

02/18/07 04:41:47 changed by diego_iastrubni

cidlookup module is broken. see ticket:1786

02/19/07 22:10:19 changed by diego_iastrubni

I am getting old and blind it seems... the correct ticket for the cidlookup is ticket:1796

03/03/07 21:29:42 changed by diego_iastrubni

The newinstall.sql should work for sqlite and mysql. This is a BIG problem:

  • AUTO_INCREMENT is supported only under mysql (4 and 5).
  • AUTOINCREMENT is supported under sqlite3 and mysql5 (not mysq4)
  • We currently use int(11) for indexes. We should use only INTEGER.
  • Only primary key is supported. The key keyword is not supported. This is used currently by tables freepbx_log and featurecodes. Maybe we can ommit this definition?
  • Multiple insert commands (like the one used to fill in the globals table) are not avialable under sqlite3. Easy to fix, I will do this on svn (it will not brake something new).

So as far as I am concerned, the only problem is the auto increment feature, which there is no easy solution. My suggestion is to use this syntax: 

`ID` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT'''

and when creating the sqlite3 tables, we can pass the newinstall.sql trough SED and replace the AUTO_INCREMENT to AUTOINCREMENT on the fly. This means we still have mysql support (and by default nothing will be broken), and people using sqlite3 will have to make an extra stage. I can live with this :)

I am attaching 2 files to this bug:

  1. a patch for newinstall.sql on the trunk
  2. the full newinstall.sql

Please review and comment.

03/03/07 21:33:52 changed by diego_iastrubni

  • attachment newinstall.sql added.

Proposed newinstall.sql file

03/03/07 22:09:53 changed by diego_iastrubni

  • attachment newinstall-sqlite3.patch added.

Patch for current SVN trunk, with all proposed fixes.

03/04/07 07:30:18 changed by gregmac

#1809 is a blocker to this

04/16/07 23:10:45 changed by diego_iastrubni

  • status changed from assigned to new.

gregmac,

I closed ticket:1809, as this is not our proble (but the distributions). Please comment, and re-open that ticket if still thinkg it's valid.

04/18/07 02:07:52 changed by diego_iastrubni

  • status changed from new to assigned.

05/06/07 00:29:39 changed by diego_iastrubni

Another merge of the patch has been introduced in changeset:3961 and changeset:3962.

This one deals with SUBSTRING in SQL code. This is not supported under SQLite3, and the only way I found is to do the trimming in PHP. The question is:

should be "ban" SUBSTRING from freePBX and do the trimming on PHP even for MySQL/PgSQL ? This will clean up the code a lot. If we decide to do this - this must be done in the trunk.

06/25/07 06:07:25 changed by p_lindheimer

  • version changed from 2.2 to 2.3-branch.

06/30/07 12:44:24 changed by p_lindheimer

  • status changed from assigned to new.
  • component changed from Core to Sqlite3.

07/26/07 11:12:43 changed by p_lindheimer

  • version changed from 2.3-branch to SVN-HEAD.

moving to post 2.3 (unless someone wants to get these in soon)

07/26/07 11:13:01 changed by p_lindheimer

  • milestone changed from 2.3 to 3.0.

09/20/07 04:13:18 changed by diego_iastrubni

Started working on a separate branch NullEngineSqlite?3Branch. In this branch the core module is "kinda" working.

Meanwhile, sqlite2 has been killed off in trunk. Only SQLite3 is available, and I recommend above 3.3, still not tested 3.4 nor 3.5.

11/28/07 02:31:11 changed by p_lindheimer

  • confirmation set to Confirmed.
  • version deleted.
  • milestone changed from 2.4 to 3.0.

deferring from lack of progress

06/23/08 23:51:46 changed by p_lindheimer

  • type changed from Bugs to Feature Requests.

07/06/08 16:49:19 changed by mbrevda

  • status changed from new to closed.
  • resolution set to wontfix.

closing due to a lack of feedback and considering that a. all the referenced tickets are closed, and b. this issue is likely to be revisited soon