CREATE TABLE android_metadata (locale TEXT);
CREATE TABLE tbl_lib_filters(UUID TEXT NOT NULL PRIMARY KEY, template
TEXT NOT NULL, library TEXT NOT NULL, rules TEXT);
CREATE TABLE tbl_library(UUID TEXT NOT NULL PRIMARY KEY, TITLE TEXT,
ICON_URI TEXT, DESCCRIPTION TEXT, REMOVED INTEGER NOT NULL,
sortTemplateUUID TEXT, groupTemplateUUID TEXT, sortDirection INTEGER,
templateVersion INTEGER, googleDocId TEXT, googleDocWorksheet TEXT,
googleDocNeedSync INTEGER, googleDocTemplateVersion INTEGER,
picasaAlbumId TEXT, public_id TEXT, lib_alias TEXT, allowed_users TEXT,
private_lib INTEGER, need_update_pub_template INTEGER, type INTEGER,
group_id INTEGER, google_sync_time INTEGER, public_time INTEGER,
protected INTEGER, encripted INTEGER, lib_order INTEGER, tile_columns
INTEGER, tile_color INTEGER, edit_time INTEGER, filter_uuid TEXT,
tile_text_color INTEGER, cloud_storage TEXT, cloud_folder_id TEXT,
lock_edit INTEGER, template_gd_file_id TEXT, entry_pages TEXT,
sort_options TEXT);
CREATE TABLE tbl_library_item(UUID TEXT NOT NULL PRIMARY KEY, LIB_UUID
TEXT, REMOVED INTEGER NOT NULL, creation_date INTEGER, REMOVED_TIME
INTEGER, VIEW_TIME INTEGER, FAVORITE INTEGER, EDIT_TIME INTEGER, FTS3_ID
INTEGER);
CREATE TABLE tbl_flex_content2(id INTEGER PRIMARY KEY AUTOINCREMENT,
stringContent TEXT, realContent REAL, intContent INTEGER, ownerUUID TEXT
NOT NULL, templateUUID TEXT NOT NULL);
CREATE TABLE tbl_filter_profiles(UUID TEXT NOT NULL PRIMARY KEY, library
TEXT NOT NULL, filter_name TEXT);
CREATE TABLE tbl_flex_template(UUID TEXT NOT NULL PRIMARY KEY, title
TEXT, type_code TEXT NOT NULL, usage INTEGER NOT NULL, number INTEGER
NOT NULL, encripted INTEGER, req INTEGER, LIB_UUID TEXT, stats TEXT,
hint TEXT, depends TEXT, display_title INTEGER);
CREATE TABLE tbl_gdocs_item_handlers(item_uuid TEXT NOT NULL PRIMARY
KEY, lib_uuid TEXT NOT NULL, gdocs_record_id TEXT NOT NULL, remove_flag
INTEGER, etag TEXT NOT NULL, edited INTEGER);
CREATE TABLE tbl_picasa_images(picasa_url TEXT NOT NULL, local_uri TEXT
NOT NULL, lib_uuid TEXT NOT NULL);
CREATE TABLE tbl_pub_item_handlers(item_uuid TEXT NOT NULL PRIMARY KEY,
lib_uuid TEXT NOT NULL, pub_id TEXT NOT NULL, remove_flag INTEGER,
version INTEGER, edited INTEGER);
CREATE TABLE tbl_groups(id INTEGER NOT NULL PRIMARY KEY, title TEXT NOT
NULL, sys_code TEXT);
CREATE TABLE tbl_master_hash (pass_hash TEXT NOT NULL PRIMARY KEY);
CREATE TABLE tbl_icon_storage(id INTEGER PRIMARY KEY AUTOINCREMENT, icon
BLOB);
CREATE TABLE tbl_field_email_format_2 (template_uuid TEXT NOT NULL,
lib_uuid TEXT NOT NULL, new_line INTEGER, ex_options TEXT,
colon_after_name INTEGER, email_template INTEGER, send_by_email INTEGER);
CREATE TABLE tbl_reminders3(id INTEGER PRIMARY KEY AUTOINCREMENT,
rem_minutes INTEGER, rem_time INTEGER);
CREATE TABLE tbl_email_templ (id INTEGER PRIMARY KEY
AUTOINCREMENT,lib_uuid TEXT NOT NULL,title TEXT);
CREATE TABLE tbl_cloud_files(cloud_url TEXT NOT NULL, local_uri TEXT NOT
NULL, cloud_id TEXT NOT NULL, lib_uuid TEXT NOT NULL);
CREATE VIRTUAL TABLE library_fts3 USING fts3();
CREATE TABLE 'library_fts3_content'(docid INTEGER PRIMARY KEY, 'c0content');
CREATE TABLE 'library_fts3_segments'(blockid INTEGER PRIMARY KEY, block
BLOB);
CREATE TABLE 'library_fts3_segdir'(level INTEGER,idx INTEGER,start_block
INTEGER,leaves_end_block INTEGER,end_block INTEGER,root BLOB,PRIMARY
KEY(level, idx));
CREATE TABLE category_alias (id integer PRIMARY KEY, category_id
integer, name string, Unique (name));
CREATE TABLE disks_bez (id integer PRIMARY KEY, name string, bez string,
Unique (name));
CREATE TABLE item_ref(id integer PRIMARY KEY,item1_id integer,
item1_type_id integer, item2_id integer, item2_type_id integer, unique
(item1_id, item1_type_id, item2_id, item2_type_id));
CREATE TABLE links(id integer PRIMARY KEY,name text,mediatype
text,source text,mirror text,dateAdded datetime,zone integer default 3,
pid integer, fid integer, votes integer default 0 not null, rating
integer default 0 not null, unique (source));
CREATE TABLE zone_bez (id integer PRIMARY KEY, zone integer, bez string,
Unique (zone));
CREATE TABLE zones (id integer PRIMARY KEY, zones integer, zone integer,
Unique (zones,zone));
CREATE TABLE category_item(id integer PRIMARY KEY,category_id integer
not null,item_id integer not null,item_type_id integer not null,UNIQUE
(category_id,item_id,item_type_id));
CREATE TABLE history(item_id integer, item_type_id integer, search_term
text, categories text, ex_categories text, zones integer, timestamp
datetime, command text, parameters text, output text);
CREATE TABLE category(id integer PRIMARY KEY,name text,parent integer,
zone integer,UNIQUE (name));
CREATE TABLE files(id integer PRIMARY KEY,name text,mediatype
text,disksource text,inode integer,bdisksource text,binode
integer,source text,zone integer default 3, votes integer default 0 not
null, rating integer default 0 not null, UNIQUE (disksource,inode));
CREATE TABLE UUID_infosystem(UUID TEXT NOT NULL PRIMARY KEY,item_id
integer not null,item_type_id integer not null);
CREATE TABLE infos(id integer PRIMARY KEY,name text,text text,zone
integer default 3, dateAdded datetime, lastModified datetime, votes
integer default 0 not null, rating integer default 0 not null,
expiration datetime, exp_action integer, date dateTime);
CREATE TABLE tbl_charts(id INTEGER PRIMARY KEY AUTOINCREMENT, title
TEXT, libraryUUID TEXT, options TEXT, type TEXT, filterUUID TEXT);
CREATE INDEX idx_library_removed ON tbl_library ( REMOVED );
CREATE INDEX idx_library_item_lib ON tbl_library_item ( LIB_UUID );
CREATE INDEX idx_library_item_removed ON tbl_library_item ( REMOVED );
CREATE INDEX idx_library_item_fts3 ON tbl_library_item ( FTS3_ID );
CREATE INDEX idx_flex_content_owner ON tbl_flex_content2 ( ownerUUID );
CREATE INDEX idx_flex_content_temp ON tbl_flex_content2 ( templateUUID );
CREATE INDEX idx_filters2_library ON tbl_filter_profiles ( library );
CREATE INDEX idx_flex_template_lib ON tbl_flex_template ( LIB_UUID );
CREATE INDEX idx_picasa_lib_uuid ON tbl_picasa_images ( lib_uuid );
CREATE INDEX idx_cloud_files_lib_uuid ON tbl_cloud_files ( lib_uuid );
CREATE INDEX idx_cloud_files_local_uri ON tbl_cloud_files ( local_uri );
CREATE VIEW category_link as select category_id, item_id from
category_item where item_type_id=2;
CREATE VIEW category_info as select category_id, item_id from
category_item where item_type_id=1;
CREATE VIEW v_infos as select * from infos;
CREATE VIEW category_file as select category_id, item_id from
category_item where item_type_id=3;
CREATE VIEW v_links as select *,dateAdded as date from links;
CREATE VIEW mv_name AS
select FTS3_ID,stringContent as name from
tbl_flex_content2,tbl_library_item,tbl_flex_template where
ownerUUID=tbl_library_item.UUID and tbl_flex_template.UUID=templateUUID
and title='name';
CREATE VIEW mv_text AS
select FTS3_ID,stringContent as text from
tbl_flex_content2,tbl_library_item,tbl_flex_template where
ownerUUID=tbl_library_item.UUID and tbl_flex_template.UUID=templateUUID
and title='text';
CREATE VIEW mv_zone AS
select FTS3_ID,intContent as zone from
tbl_flex_content2,tbl_library_item,tbl_flex_template where
ownerUUID=tbl_library_item.UUID and tbl_flex_template.UUID=templateUUID
and title='zone';
CREATE VIEW mv_dateAdded AS
select FTS3_ID, datetime(substr(stringContent,1,10), 'unixepoch',
'localtime') as dateAdded from
tbl_flex_content2,tbl_library_item,tbl_flex_template where
ownerUUID=tbl_library_item.UUID and tbl_flex_template.UUID=templateUUID
and title='dateAdded';
CREATE VIEW mv_date AS
select FTS3_ID, datetime(substr(stringContent,1,10), 'unixepoch',
'localtime') as date from
tbl_flex_content2,tbl_library_item,tbl_flex_template where
ownerUUID=tbl_library_item.UUID and tbl_flex_template.UUID=templateUUID
and title='date';
CREATE VIEW mv_expiration AS
select FTS3_ID, datetime(substr(stringContent,1,10), 'unixepoch',
'localtime') as expiration from
tbl_flex_content2,tbl_library_item,tbl_flex_template where
ownerUUID=tbl_library_item.UUID and tbl_flex_template.UUID=templateUUID
and title='expiration';
CREATE VIEW mv_exp_action AS
select FTS3_ID, intContent as exp_action from
tbl_flex_content2,tbl_library_item,tbl_flex_template where
ownerUUID=tbl_library_item.UUID and tbl_flex_template.UUID=templateUUID
and title='exp_action';
CREATE VIEW mv_lastModified AS
select FTS3_ID, datetime(substr(EDIT_TIME,1,10), 'unixepoch',
'localtime') as lastModified from tbl_library_item;
CREATE VIEW mv_votes AS
select FTS3_ID,intContent as votes from
tbl_flex_content2,tbl_library_item,tbl_flex_template where
ownerUUID=tbl_library_item.UUID and tbl_flex_template.UUID=templateUUID
and title='votes';
CREATE VIEW mv_rating AS
select FTS3_ID,intContent as rating from
tbl_flex_content2,tbl_library_item,tbl_flex_template where
ownerUUID=tbl_library_item.UUID and tbl_flex_template.UUID=templateUUID
and title='rating';
> Gesendet: Dienstag, 29. Juli 2014 um 15:12 Uhr
> Von: Michael <ruther1...@gmx-topmail.de>
> An: sqlite-users@sqlite.org
> Betreff: Re: [sqlite] Long execution time since sqlite 3.8
>
> Ok that's the output of sqlite_stat1:
>
> tbl|idx|stat
> android_metadata||1
> tbl_flex_template|idx_flex_template_lib|10 10
> tbl_flex_template|sqlite_autoindex_tbl_flex_template_1|10 1
> zones|sqlite_autoindex_zones_1|84 6 1
> history||5870
> tbl_flex_content2|idx_flex_content_temp|20010 2001
> tbl_flex_content2|idx_flex_content_owner|20010 10
> tbl_library|idx_library_removed|1 1
> tbl_library|sqlite_autoindex_tbl_library_1|1 1
> tbl_lib_filters|sqlite_autoindex_tbl_lib_filters_1|4 1
> tbl_filter_profiles|idx_filters2_library|4 4
> tbl_filter_profiles|sqlite_autoindex_tbl_filter_profiles_1|4 1
> category_item|sqlite_autoindex_category_item_1|6827 20 2 1
> library_fts3_segdir|sqlite_autoindex_library_fts3_segdir_1|28 10 1
> library_fts3_segments||254
> infos||1293
> tbl_groups||1
> disks_bez|sqlite_autoindex_disks_bez_1|11 1
> tbl_library_item|idx_library_item_fts3|2000 1
> tbl_library_item|idx_library_item_removed|2000 2000
> tbl_library_item|idx_library_item_lib|2000 2000
> tbl_library_item|sqlite_autoindex_tbl_library_item_1|2000 1
> category|sqlite_autoindex_category_1|416 1
> library_fts3_content||2000
> zone_bez|sqlite_autoindex_zone_bez_1|4 1
> category_alias|sqlite_autoindex_category_alias_1|114 1
> item_ref|sqlite_autoindex_item_ref_1|93 2 2 1 1
> links|sqlite_autoindex_links_1|2762 2
>
> and the schema attached
>
>
> > Gesendet: Dienstag, 29. Juli 2014 um 12:52 Uhr
> > Von: "Richard Hipp" <d...@sqlite.org>
> > An: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> > Betreff: Re: [sqlite] Long execution time since sqlite 3.8
> >
> > On Tue, Jul 29, 2014 at 4:55 AM, Michael <ruther1...@gmx-topmail.de>
> wrote:
> >
> > >
> > > Should I post the EXPLAIN?
> > >
> >
> > No. You should post the database schema and the content of the
> > sqlite_stat1, sqlite_stat3, and/or sqlite_stat4 tables if such tables
> exist.
> >
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > _______________________________________________
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
