From 86a3b23b1ddb40262c5c3f168cd45c33978d8f8d Mon Sep 17 00:00:00 2001 From: helge Date: Mon, 21 Feb 2005 00:40:56 +0000 Subject: [PATCH] added SQLite scripts git-svn-id: http://svn.opengroupware.org/SOGo/trunk@595 d1b88da0-ebda-0310-925b-ed51d893ca5b --- OGoContentStore/sql/folderinfo-create.sqlite | 62 ++++++++++ .../sql/foldertablecreate-helge-privcal.psql | 1 - .../foldertablecreate-helge-privcal.sqlite | 33 ++++++ ...enerate-folderinfo-sql-for-users-sqlite.sh | 111 ++++++++++++++++++ 4 files changed, 206 insertions(+), 1 deletion(-) create mode 100644 OGoContentStore/sql/folderinfo-create.sqlite create mode 100644 OGoContentStore/sql/foldertablecreate-helge-privcal.sqlite create mode 100755 OGoContentStore/sql/generate-folderinfo-sql-for-users-sqlite.sh diff --git a/OGoContentStore/sql/folderinfo-create.sqlite b/OGoContentStore/sql/folderinfo-create.sqlite new file mode 100644 index 00000000..c96e09ae --- /dev/null +++ b/OGoContentStore/sql/folderinfo-create.sqlite @@ -0,0 +1,62 @@ +-- +-- (C) 2005 SKYRIX Software AG +-- +-- TODO: +-- add a unique constraints on path + +DROP TABLE SOGo_folder_info; + +CREATE TABLE SOGo_folder_info ( + c_folder_id INTEGER PRIMARY KEY, + c_path VARCHAR(255) NOT NULL, /* the full path to the folder */ + c_path1 VARCHAR(255) NOT NULL, /* parts (for fast queries) */ + c_path2 VARCHAR(255) NULL, /* parts (for fast queries) */ + c_path3 VARCHAR(255) NULL, /* parts (for fast queries) */ + c_path4 VARCHAR(255) NULL, /* parts (for fast queries) */ + c_foldername VARCHAR(255) NOT NULL, /* last path component */ + c_location VARCHAR(2048) NOT NULL, /* URL to folder */ + c_quick_location VARCHAR(2048) NULL, /* URL to quicktable of folder */ + c_folder_type VARCHAR(255) NOT NULL /* the folder type ... */ +); + +INSERT INTO SOGo_folder_info + ( c_path, c_path1, c_path2, c_path3, c_path4, c_foldername, + c_location, c_quick_location, c_folder_type ) +VALUES + ( '/Users', + 'Users', + NULL, + NULL, + NULL, + 'Users', + 'sqlite://localhost/tmp/sogo-registry.sqlite/SOGo_user_folder_blob', + 'sqlite://localhost/tmp/sogo-registry.sqlite/SOGo_user_folder_quick', + 'Container' ); + +INSERT INTO SOGo_folder_info + ( c_path, c_path1, c_path2, c_path3, c_path4, c_foldername, + c_location, c_quick_location, c_folder_type ) +VALUES + ( '/Users/helge', + 'Users', + 'helge', + NULL, + NULL, + 'helge', + 'sqlite://localhost/tmp/sogo-registry.sqlite/SOGo_user_folder_blob', + 'sqlite://localhost/tmp/sogo-registry.sqlite/SOGo_user_folder_quick', + 'Container' ); + +INSERT INTO SOGo_folder_info + ( c_path, c_path1, c_path2, c_path3, c_path4, c_foldername, + c_location, c_quick_location, c_folder_type ) +VALUES + ( '/Users/helge/Calendar', + 'Users', + 'helge', + 'Calendar', + NULL, + 'Calendar', + 'sqlite://localhost/tmp/sogo-registry.sqlite/SOGo_helge_privcal_blob', + 'sqlite://localhost/tmp/sogo-registry.sqlite/SOGo_helge_privcal_quick', + 'Appointment' ); diff --git a/OGoContentStore/sql/foldertablecreate-helge-privcal.psql b/OGoContentStore/sql/foldertablecreate-helge-privcal.psql index 558a714a..710c6587 100644 --- a/OGoContentStore/sql/foldertablecreate-helge-privcal.psql +++ b/OGoContentStore/sql/foldertablecreate-helge-privcal.psql @@ -1,4 +1,3 @@ --- $Id$ -- -- (C) 2004 SKYRIX Software AG -- diff --git a/OGoContentStore/sql/foldertablecreate-helge-privcal.sqlite b/OGoContentStore/sql/foldertablecreate-helge-privcal.sqlite new file mode 100644 index 00000000..af9cf747 --- /dev/null +++ b/OGoContentStore/sql/foldertablecreate-helge-privcal.sqlite @@ -0,0 +1,33 @@ +/* + (C) 2005 SKYRIX Software AG +*/ + +DROP TABLE SOGo_helge_privcal_quick; +DROP TABLE SOGo_helge_privcal; + +CREATE TABLE SOGo_helge_privcal_quick ( + c_name VARCHAR(256) NOT NULL PRIMARY KEY, /* the filename */ + uid VARCHAR(256) NOT NULL, + startdate INT NOT NULL, + enddate INT NOT NULL, + title VARCHAR(1000) NOT NULL, + participants VARCHAR(100000) NOT NULL, /* the CNs of the participants */ + isallday INT NULL, + iscycle INT NULL, /* client needs to fetch to resolve */ + ispublic INT NOT NULL, + status INT NOT NULL, + isopaque INT NULL, + location VARCHAR(256) NULL, + orgmail VARCHAR(256) NULL, + partmails VARCHAR(100000) NOT NULL, /* the emails of the participants */ + partstates VARCHAR(256) NOT NULL, /* the status of each participant */ + sequence INT NULL /* the iCal sequence */ +); + +CREATE TABLE SOGo_helge_privcal_blob ( + c_name VARCHAR(256) NOT NULL PRIMARY KEY, /* the filename */ + c_content VARCHAR(100000) NOT NULL, /* the BLOB */ + c_creationdate INT NOT NULL, /* creation date */ + c_lastmodified INT NOT NULL, /* last modification (UPDATE) */ + c_version INT NOT NULL /* version counter */ +); diff --git a/OGoContentStore/sql/generate-folderinfo-sql-for-users-sqlite.sh b/OGoContentStore/sql/generate-folderinfo-sql-for-users-sqlite.sh new file mode 100755 index 00000000..22b4b3df --- /dev/null +++ b/OGoContentStore/sql/generate-folderinfo-sql-for-users-sqlite.sh @@ -0,0 +1,111 @@ +#!/bin/bash +# +# Usage: generate-folderinfo-sql-for-users user1 [user2] [user3] [...] +# + +DB="/tmp/sogo-registry.sqlite" + +while [ "$1" != "" ]; do +USER_ID=$1 +USER_TABLE=`echo $USER_ID | tr -s [:punct:] _` +cat << EOF +DELETE FROM SOGo_folder_info WHERE c_path2 = '${USER_ID}'; + +INSERT INTO SOGo_folder_info + ( c_path, c_path1, c_path2, c_path3, c_path4, c_foldername, + c_location, c_quick_location, c_folder_type ) +VALUES + ( '/Users/${USER_ID}', + 'Users', + '${USER_ID}', + NULL, + NULL, + '${USER_ID}', + 'sqlite://localhost${DB}/SOGo_user_folder_blob', + 'sqlite://localhost${DB}/SOGo_user_folder_quick', + 'Container' ); + +INSERT INTO SOGo_folder_info + ( c_path, c_path1, c_path2, c_path3, c_path4, c_foldername, + c_location, c_quick_location, c_folder_type ) +VALUES + ( '/Users/${USER_ID}/Calendar', + 'Users', + '${USER_ID}', + 'Calendar', + NULL, + 'Calendar', + 'sqlite://localhost${DB}/SOGo_${USER_TABLE}_privcal_blob', + 'sqlite://localhost${DB}/SOGo_${USER_TABLE}_privcal_quick', + 'Appointment' ); + +INSERT INTO SOGo_folder_info + ( c_path, c_path1, c_path2, c_path3, c_path4, c_foldername, + c_location, c_quick_location, c_folder_type ) +VALUES + ( '/Users/${USER_ID}/Contacts', + 'Users', + '${USER_ID}', + 'Contacts', + NULL, + 'Contacts', + 'sqlite://localhost${DB}/SOGo_${USER_TABLE}_contacts_blob', + 'sqlite://localhost${DB}/SOGo_${USER_TABLE}_contacts_quick', + 'Contact' ); + +DROP TABLE SOGo_${USER_TABLE}_privcal_quick; +DROP TABLE SOGo_${USER_TABLE}_privcal_blob; + +CREATE TABLE SOGo_${USER_TABLE}_privcal_quick ( + c_name VARCHAR(256) NOT NULL PRIMARY KEY, /* the filename */ + uid VARCHAR(256) NOT NULL, + startdate INT NOT NULL, + enddate INT NOT NULL, + title VARCHAR(1000) NOT NULL, + participants VARCHAR(100000) NOT NULL, /* the CNs of the participants */ + isallday INT NULL, + iscycle INT NULL, /* client needs to fetch to resolve */ + ispublic INT NOT NULL, + status INT NOT NULL, + isopaque INT NULL, + location VARCHAR(256) NULL, + orgmail VARCHAR(256) NULL, + partmails VARCHAR(100000) NOT NULL, /* the emails of the participants */ + partstates VARCHAR(256) NOT NULL, /* the status of each participant */ + sequence INT NULL /* the iCal sequence */ +); + +CREATE TABLE SOGo_${USER_TABLE}_privcal_blob ( + c_name VARCHAR(256) NOT NULL PRIMARY KEY, /* the filename */ + c_content VARCHAR(100000) NOT NULL, /* the BLOB */ + c_creationdate INT NOT NULL, /* creation date */ + c_lastmodified INT NOT NULL, /* last modification (UPDATE) */ + c_version INT NOT NULL /* version counter */ +); + +DROP TABLE SOGo_${USER_TABLE}_contacts_quick; +DROP TABLE SOGo_${USER_TABLE}_contacts_blob; + +CREATE TABLE SOGo_${USER_TABLE}_contacts_quick ( + c_name VARCHAR(256) NOT NULL PRIMARY KEY, /* the filename */ + givenname VARCHAR(256), + cn VARCHAR(256), + sn VARCHAR(256), + l VARCHAR(256), + mail VARCHAR(256), + o VARCHAR(256), + ou VARCHAR(256), + telephonenumber VARCHAR(256) +); + +CREATE TABLE SOGo_${USER_TABLE}_contacts_blob ( + c_name VARCHAR(256) NOT NULL PRIMARY KEY, /* the filename */ + c_content VARCHAR(100000) NOT NULL, /* the BLOB */ + c_creationdate INT NOT NULL, /* creation date */ + c_lastmodified INT NOT NULL, /* last modification (UPDATE) */ + c_version INT NOT NULL /* version counter */ +); + +EOF +shift +done -- 2.39.5