install with sqlite errror

Question

install with sqlite errror

Opened this issue 4 years ago · 6 comments

guizmoau commented 4 years ago

when trying to install with composer require texxasrulez/calendar

I receive the following error :

Installing texxasrulez/calendar (0.0.7): Downloading (100%)
Do you want to activate the plugin calendar? [N|y] y
Creating plugin config file
Running database initialization script for calendar
ERROR: [1] no such function: provider (SQL Query: CREATE TABLE IF NOT EXISTS "calendar_oauth_states" (

"provider" TEXT NOT NULL,

"client_config_id" TEXT NOT NULL,

"user_id" TEXT NOT NULL,

"scope" TEXT NOT NULL,

"issue_time" INTEGER NOT NULL,

"state" TEXT NOT NULL,

UNIQUE ("provider"(50), "client_config_id"(50), "user_id"(50), "scope"(50)),

PRIMARY KEY ("state")

))
ERROR: Error in DDL schema /usr/local/lib/roundcubemail/plugins/calendar/SQL/sqlite.initial.sql: [1] no such function: provider
[FAILED]

I am using sqlite, how can I solve this ?

Thanks a lot

Answer 1 · 2020-10-01T19:26:54.000Z

I get something similar with mysql


Do you want to activate the plugin calendar? [N|y] y
Updated local config at /var/www/rc/config/config.inc.php
Creating plugin config file
Running database initialization script for calendar
ERROR: [1050] Table 'calendar_oauth_states' already exists (SQL Query: /*

 * Roundcube Database, CalDav & iCal Calendar Schema

 *

 * @author Gene Hawkins <texxasrulez@yahoo.com>

 *

 * @licence GNU AGPL

 */

CREATE TABLE `calendar_oauth_states` (

  `provider` varchar(255) NOT NULL,

  `client_config_id` varchar(255) NOT NULL,

  `user_id` varchar(255) NOT NULL,

  `scope` varchar(255) NOT NULL,

  `issue_time` INTEGER NOT NULL,

  `state` varchar(255) NOT NULL,

  UNIQUE (`provider`(50), `client_config_id`(50), `user_id`(50), `scope`(50)),

  PRIMARY KEY (`state`)

) /*!40000 ENGINE=INNODB */ /*!40101 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */)
ERROR: Error in DDL schema /var/www/rc/plugins/calendar/SQL/mysql.initial.sql: [1050] Table 'calendar_oauth_states' already exists
[FAILED]

Answer 2 · 2020-10-01T19:49:23.000Z

Fixed with adding CREATE TABLE WHERE NOT EXSITS

Answer 3 · 2020-10-06T14:02:05.000Z

I tryed to fix it by modifying the init SQL script to adapt

this is the WIP :

`
PRAGMA journal_mode = MEMORY;
PRAGMA synchronous = OFF;
PRAGMA foreign_keys = OFF;
PRAGMA ignore_check_constraints = OFF;
PRAGMA auto_vacuum = NONE;
PRAGMA secure_delete = OFF;
BEGIN TRANSACTION;

CREATE TABLE calendar_oauth_states(
provider TEXT NOT NULL UNIQUE,
client_config_id TEXT NOT NULL UNIQUE,
user_id TEXT NOT NULL UNIQUE,
scope TEXT NOT NULL UNIQUE,
issue_time INTEGER NOT NULL,
state TEXT NOT NULL PRIMARY KEY
);

CREATE TABLE calendar_oauth_access_tokens (
provider TEXT NOT NULL UNIQUE,
client_config_id TEXT NOT NULL UNIQUE,
user_id TEXT NOT NULL UNIQUE,
scope TEXT NOT NULL UNIQUE,
issue_time INTEGER NOT NULL,
access_token TEXT NOT NULL,
token_type TEXT NOT NULL,
expires_in INTEGER DEFAULT NULL
);

CREATE TABLE calendar_oauth_refresh_tokens (
provider TEXT NOT NULL UNIQUE,
client_config_id TEXT NOT NULL UNIQUE,
user_id TEXT NOT NULL UNIQUE,
scope TEXT NOT NULL UNIQUE,
issue_time INTEGER NOT NULL,
refresh_token TEXT DEFAULT NULL
);

CREATE TABLE caldav_calendars (
calendar_id INTEGER NOT NULL ,
user_id INTEGER NOT NULL DEFAULT '0',
name TEXT NOT NULL,
color TEXT NOT NULL,
showalarms tinyINTEGER NOT NULL DEFAULT '1',
caldav_url TEXT DEFAULT NULL,
caldav_tag TEXT NULL DEFAULT 'COLLATE',
caldav_user TEXT DEFAULT NULL,
caldav_pass TEXT DEFAULT NULL,
caldav_oauth_provider varbinary(200) DEFAULT NULL,
readonly int NOT NULL DEFAULT '0',
caldav_last_change timestamp without time zone DEFAULT (datetime('now', 'localtime')) NOT NULL,
PRIMARY KEY(calendar_id),
FOREIGN KEY (user_id)
REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE INDEX caldav_user_name_idx ON caldav_calendars(user_id, name);

CREATE TABLE caldav_events (
event_id INTEGER NOT NULL ,
calendar_id INTEGER NOT NULL DEFAULT '0',
recurrence_id INTEGER NOT NULL DEFAULT '0',
uid TEXT NOT NULL DEFAULT '',
instance TEXT NOT NULL DEFAULT '',
isexception tinyINTEGER NOT NULL DEFAULT '0',
created datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
changed datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
sequence INTEGER NOT NULL DEFAULT '0',
start datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
end datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
recurrence TEXT DEFAULT NULL,
title varbinary(128) NOT NULL,
description varbinary(2048) NOT NULL,
location varbinary(128) NOT NULL DEFAULT '',
categories varbinary(128) NOT NULL DEFAULT '',
url TEXT NOT NULL DEFAULT '',
all_day tinyINTEGER NOT NULL DEFAULT '0',
free_busy tinyINTEGER NOT NULL DEFAULT '0',
priority tinyINTEGER NOT NULL DEFAULT '0',
sensitivity tinyINTEGER NOT NULL DEFAULT '0',
status TEXT NOT NULL DEFAULT '',
alarms text NULL DEFAULT NULL,
attendees text DEFAULT NULL,
notifyat datetime DEFAULT NULL,
caldav_url TEXT NOT NULL,
caldav_tag TEXT NULL DEFAULT 'COLLATE',
caldav_last_change timestamp NOT NULL ,
PRIMARY KEY(event_id),
FOREIGN KEY (calendar_id)
REFERENCES caldav_calendars(calendar_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE INDEX caldav_uid_idx ON caldav_events(uid);
CREATE INDEX caldav_recurrence_idx ON caldav_events(recurrence_id);
CREATE INDEX caldav_calendar_notify_idx ON caldav_events(calendar_id,notifyat);

CREATE TABLE caldav_attachments (
attachment_id INTEGER NOT NULL ,
event_id INTEGER NOT NULL DEFAULT '0',
filename TEXT NOT NULL DEFAULT '',
mimetype TEXT NOT NULL DEFAULT '',
size INTEGER NOT NULL DEFAULT '0',
data MEDIUMBLOB,
PRIMARY KEY(attachment_id),
FOREIGN KEY (event_id)
REFERENCES caldav_events(event_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE database_calendars (
calendar_id INTEGER NOT NULL ,
user_id INTEGER NOT NULL DEFAULT '0',
name TEXT NOT NULL,
color TEXT NOT NULL,
showalarms tinyINTEGER NOT NULL DEFAULT '1',
PRIMARY KEY(calendar_id),
FOREIGN KEY (user_id)
REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE INDEX user_name_idx ON database_calendars(user_id, name);

CREATE TABLE database_events (
event_id INTEGER NOT NULL ,
calendar_id INTEGER NOT NULL DEFAULT '0',
recurrence_id INTEGER NOT NULL DEFAULT '0',
uid TEXT NOT NULL DEFAULT '',
instance TEXT NOT NULL DEFAULT '',
isexception tinyINTEGER NOT NULL DEFAULT '0',
created datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
changed datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
sequence INTEGER NOT NULL DEFAULT '0',
start datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
end datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
recurrence TEXT DEFAULT NULL,
title TEXT NOT NULL,
description text NOT NULL,
location TEXT NOT NULL DEFAULT '',
categories TEXT NOT NULL DEFAULT '',
url TEXT NOT NULL DEFAULT '',
all_day tinyINTEGER NOT NULL DEFAULT '0',
free_busy tinyINTEGER NOT NULL DEFAULT '0',
priority tinyINTEGER NOT NULL DEFAULT '0',
sensitivity tinyINTEGER NOT NULL DEFAULT '0',
status TEXT NOT NULL DEFAULT '',
alarms text DEFAULT NULL,
attendees text DEFAULT NULL,
notifyat datetime DEFAULT NULL,
PRIMARY KEY(event_id),
FOREIGN KEY (calendar_id)
REFERENCES database_calendars(calendar_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE UNIQUE INDEX uid_idx ON database_events(uid);
CREATE INDEX recurrence_idx ON database_events(recurrence_id);
CREATE INDEX calendar_notify_idx ON database_events(calendar_id,notifyat);

CREATE TABLE database_attachments (
attachment_id INTEGER NOT NULL ,
event_id INTEGER NOT NULL DEFAULT '0',
filename TEXT NOT NULL DEFAULT '',
mimetype TEXT NOT NULL DEFAULT '',
size INTEGER NOT NULL DEFAULT '0',
data TEXT NOT NULL,
PRIMARY KEY(attachment_id),
FOREIGN KEY (event_id)
REFERENCES database_events(event_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE itipinvitations (
token TEXT NOT NULL,
event_uid TEXT NOT NULL,
user_id INTEGER NOT NULL DEFAULT '0',
event TEXT NOT NULL,
expires DATETIME DEFAULT NULL,
cancelled TINYINTEGER NOT NULL DEFAULT '0',
PRIMARY KEY(token),
FOREIGN KEY (user_id)
REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE INDEX uid_itipinvitations_idx ON itipinvitations(user_id,event_uid);

CREATE TABLE ical_calendars (
calendar_id INTEGER NOT NULL ,
user_id INTEGER NOT NULL DEFAULT '0',
name TEXT NOT NULL,
color TEXT NOT NULL,
showalarms tinyINTEGER NOT NULL DEFAULT '1',
ical_url TEXT NOT NULL,
ical_user TEXT DEFAULT NULL,
ical_pass TEXT DEFAULT NULL,
ical_last_change timestamp NOT NULL ,
PRIMARY KEY(calendar_id),
FOREIGN KEY (user_id)
REFERENCES users(user_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE INDEX ical_user_name_idx ON ical_calendars(user_id,name);

CREATE TABLE ical_events (
event_id INTEGER NOT NULL ,
calendar_id INTEGER NOT NULL DEFAULT '0',
recurrence_id INTEGER NOT NULL DEFAULT '0',
uid TEXT NOT NULL DEFAULT '',
instance TEXT NOT NULL DEFAULT '',
isexception tinyINTEGER NOT NULL DEFAULT '0',
created datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
changed datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
sequence INTEGER NOT NULL DEFAULT '0',
start datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
end datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
recurrence TEXT DEFAULT NULL,
title varbinary(128) NOT NULL,
description varbinary(2048) NOT NULL,
location varbinary(128) NOT NULL DEFAULT '',
categories varbinary(128) NOT NULL DEFAULT '',
url TEXT NOT NULL DEFAULT '',
all_day tinyINTEGER NOT NULL DEFAULT '0',
free_busy tinyINTEGER NOT NULL DEFAULT '0',
priority tinyINTEGER NOT NULL DEFAULT '0',
sensitivity tinyINTEGER NOT NULL DEFAULT '0',
status TEXT NOT NULL DEFAULT '',
alarms text NULL DEFAULT NULL,
attendees text DEFAULT NULL,
notifyat datetime DEFAULT NULL,
ical_url TEXT NOT NULL,
ical_last_change timestamp NOT NULL ,
PRIMARY KEY(event_id),
FOREIGN KEY (calendar_id)
REFERENCES ical_calendars(calendar_id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE INDEX ical_uid_idx ON ical_events(uid);
CREATE INDEX ical_recurrence_idx ON ical_events(recurrence_id);
CREATE INDEX ical_calendar_notify_idx ON ical_events(calendar_id,notifyat);

CREATE TABLE ical_attachments (
attachment_id INTEGER NOT NULL ,
event_id INTEGER NOT NULL DEFAULT '0',
filename TEXT NOT NULL DEFAULT '',
mimetype TEXT NOT NULL DEFAULT '',
size INTEGER NOT NULL DEFAULT '0',
data TEXT NOT NULL,
PRIMARY KEY(attachment_id),
FOREIGN KEY (event_id)
REFERENCES ical_events(event_id) ON DELETE CASCADE ON UPDATE CASCADE
);
REPLACE INTO system (name, value) VALUES ('calendar-version', '2020081200');

COMMIT;
PRAGMA ignore_check_constraints = ON;
PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;`

Answer 4 · 2020-10-06T14:10:07.000Z

I had to rewrite the INDEX commands like this as example

CREATE INDEX ical_uid_idx ON ical_events(uid);

but I had issue with the uid_idx index since it is used in two differents tables but with the same name...
it must be problematic even it pass the init...

Answer 5 · 2020-10-06T14:13:31.000Z

now when I connect in roundcube on the calendar section, the default calendar is duplicated by 3 every time I go back to calendar...

Answer 6 · 2020-10-06T15:39:38.000Z

I use the command below with the roundcube script to init db

/bin/initdb.sh --dir=/my/dir/SQL