[Lxr-commits] CVS: lxr/templates/initdb unique-user-sequences.sql, NONE, 1.1 initdb-m-template.sql,
Brought to you by:
ajlittoz
Menu
▾
▴
[Lxr-commits] CVS: lxr/templates/initdb unique-user-sequences.sql, NONE, 1.1 initdb-m-template.sql, 1.5, 1.6 initdb-p-template.sql, 1.5, 1.6 initdb-s-template.sql, 1.4, 1.5
|
From: Andre-Littoz <ajl...@us...> - 2013-11-17 15:33:58
|
Update of /cvsroot/lxr/lxr/templates/initdb In directory sfp-cvs-1.v30.ch3.sourceforge.com:/tmp/cvs-serv8585/templates/initdb Modified Files: initdb-m-template.sql initdb-p-template.sql initdb-s-template.sql Added Files: unique-user-sequences.sql Log Message: templates/initdb/initdb-*-template.sql, unique-user-sequences.sql: implement manually managed unique record ids in tables New template unique-server-sequences.sql contain storage description for the counters. --- NEW FILE: unique-user-sequences.sql --- /*- -*- tab-width: 4 -*- */ /* * SQL template for user management of unique record numbers * (C) 2013-2013 A. Littoz * $Id: unique-user-sequences.sql,v 1.1 2013/11/17 15:33:55 ajlittoz Exp $ * * This template is intended to be included in other SQL templates * and further customised by Perl script initdb-config.pl. * It creates the tables for replacement of built-in auto increment * fields as experiments showed a substantial performance boost. * Explanation: * LXR writes into the database only at genxref time and * does this with massive insertion in a short period and * nobody else than genxref ever writes to the DB (*). * Consequently, playing with COMMIT frequency results in * fewer I/O and higher processing speed. * (*) If somebody fancies to try to write while genxref is active, * this assertion is broken and the DB will become an unusable * mess. * * This strategy is incompatible with multi-threading if it is * ever reconsidered. * * The specific DB managers in Index/ must be adapted to the * presence of these extra tables. */ /* ************************************************************** * This program is free software; you can redistribute it and/or modify * it under the terms of the GNU General Public License as published by * the Free Software Foundation; either version 2 of the License, or * (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU General Public License for more details. * * You should have received a copy of the GNU General Public License * along with this program. If not, see <http://www.gnu.org/licences/>. * ************************************************************** -*/ /*--*/ /*--*/ drop table if exists %DB_tbl_prefix%filenum; drop table if exists %DB_tbl_prefix%symnum; drop table if exists %DB_tbl_prefix%typenum; create table %DB_tbl_prefix%filenum ( rcd int primary key , fid int ); insert into %DB_tbl_prefix%filenum (rcd, fid) VALUES (0, 0); create table %DB_tbl_prefix%symnum ( rcd int primary key , sid int ); insert into %DB_tbl_prefix%symnum (rcd, sid) VALUES (0, 0); create table %DB_tbl_prefix%typenum ( rcd int primary key , tid int ); insert into %DB_tbl_prefix%typenum (rcd, tid) VALUES (0, 0); Index: initdb-m-template.sql =================================================================== RCS file: /cvsroot/lxr/lxr/templates/initdb/initdb-m-template.sql,v retrieving revision 1.5 retrieving revision 1.6 diff -u -d -r1.5 -r1.6 --- initdb-m-template.sql 17 Nov 2013 09:28:36 -0000 1.5 +++ initdb-m-template.sql 17 Nov 2013 15:33:55 -0000 1.6 @@ -1,7 +1,7 @@ /*- -*- tab-width: 4 -*- */ /*- * SQL template for creating MySQL tables - * (C) 2012 A. Littoz + * (C) 2012-2013 A. Littoz * $Id$ * * This template is intended to be customised by Perl script @@ -126,12 +126,27 @@ /*--*/ use %DB_name%; +/*@IF 0 */ +/*@ DEFINE autoinc='auto_increment'*/ +/*@ELSE*/ +/*- Unique record id user management (initially developed for SQLite) -*/ +/*@ DEFINE autoinc=' '*/ +/*@ADD initdb/unique-user-sequences.sql*/ +alter table %DB_tbl_prefix%filenum + engine = MyISAM; +alter table %DB_tbl_prefix%symnum + engine = MyISAM; +alter table %DB_tbl_prefix%typenum + engine = MyISAM; + +/*@ENDIF*/ + /* Base version of files */ /* revision: a VCS generated unique id for this version of the file */ create table %DB_tbl_prefix%files - ( fileid int auto_increment not null primary key + ( fileid int %autoinc% not null primary key , filename varbinary(255) not null , revision varbinary(255) not null , constraint %DB_tbl_prefix%uk_files @@ -223,7 +238,7 @@ /* declaration: provided by generic.conf */ create table %DB_tbl_prefix%langtypes - ( typeid smallint not null auto_increment + ( typeid smallint not null %autoinc% , langid tinyint unsigned not null , declaration varchar(255) not null , constraint %DB_tbl_prefix%pk_langtypes @@ -237,7 +252,7 @@ * symname: symbol name */ create table %DB_tbl_prefix%symbols - ( symid int not null auto_increment primary key + ( symid int not null %autoinc% primary key , symcount int , symname varbinary(255) not null unique ) @@ -332,6 +347,19 @@ begin set @old_check = @@session.foreign_key_checks; set session foreign_key_checks = OFF; +/*@IF 0 */ +/*@ELSE*/ +/*- Unique record id user management -*/ + truncate table %DB_tbl_prefix%filenum; + truncate table %DB_tbl_prefix%symnum; + truncate table %DB_tbl_prefix%typenum; + insert into %DB_tbl_prefix%filenum + (rcd, fid) VALUES (0, 0); + insert into %DB_tbl_prefix%symnum + (rcd, sid) VALUES (0, 0); + insert into %DB_tbl_prefix%typenum + (rcd, tid) VALUES (0, 0); +/*@ENDIF*/ truncate table %DB_tbl_prefix%definitions; truncate table %DB_tbl_prefix%usages; truncate table %DB_tbl_prefix%langtypes; Index: initdb-p-template.sql =================================================================== RCS file: /cvsroot/lxr/lxr/templates/initdb/initdb-p-template.sql,v retrieving revision 1.5 retrieving revision 1.6 diff -u -d -r1.5 -r1.6 --- initdb-p-template.sql 17 Nov 2013 15:17:24 -0000 1.5 +++ initdb-p-template.sql 17 Nov 2013 15:33:55 -0000 1.6 @@ -140,9 +140,30 @@ -*//*- to activate place "- * /" at end of line (without spaces) /*@XQT psql -q -U postgres %DB_name% <<END_OF_TABLES*/ /*- end of disable/enable comment -*/ +/*- NOTE: a substantial performance gain resulted in + - not using SERIAL autoincrementing fields, numbering + - them with unique ids obtained from SEQUENCEs. + - A further marginal gain was possible replacing the + - sequences with user managed numbering. + - The reasons was in the drastic decrease in COMMIT statements. + - CAUTION! Since sequence number update is committed to the DB with + - low frequency, this optimisation is not compatible with + - multiple DB writes, i.e. multi-threading or concurrent + - table loading. + -*/ +/*@IF 0 */ +/*- Built-in unique record id management -*/ drop sequence if exists %DB_tbl_prefix%filenum; drop sequence if exists %DB_tbl_prefix%symnum; drop sequence if exists %DB_tbl_prefix%typenum; +create sequence %DB_tbl_prefix%filenum; +create sequence %DB_tbl_prefix%symnum; +create sequence %DB_tbl_prefix%typenum; +/*@ELSE*/ +/*- The following is a replacement (initially developed for SQLite) -*/ +/*@ADD initdb/unique-user-sequences.sql*/ +/*@ENDIF*/ + drop table if exists %DB_tbl_prefix%files cascade; drop table if exists %DB_tbl_prefix%symbols cascade; drop table if exists %DB_tbl_prefix%definitions cascade; @@ -151,10 +172,6 @@ drop table if exists %DB_tbl_prefix%status cascade; drop table if exists %DB_tbl_prefix%langtypes cascade; -create sequence %DB_tbl_prefix%filenum cache 500; -create sequence %DB_tbl_prefix%symnum cache 500; -create sequence %DB_tbl_prefix%typenum cache 10; - /* Base version of files */ /* revision: a VCS generated unique id for this version @@ -233,7 +250,7 @@ ( fileid int not null , releaseid bytea not null , constraint %DB_tbl_prefix%pk_releases - primary key (fileid,releaseid) + primary key (fileid, releaseid) , constraint %DB_tbl_prefix%fk_rls_fileid foreign key (fileid) references %DB_tbl_prefix%files(fileid) @@ -502,6 +519,9 @@ for each row execute procedure %DB_tbl_prefix%decusage(); +/* + * + */ grant select on %DB_tbl_prefix%files to public; grant select on %DB_tbl_prefix%symbols to public; grant select on %DB_tbl_prefix%definitions to public; Index: initdb-s-template.sql =================================================================== RCS file: /cvsroot/lxr/lxr/templates/initdb/initdb-s-template.sql,v retrieving revision 1.4 retrieving revision 1.5 diff -u -d -r1.4 -r1.5 --- initdb-s-template.sql 17 Nov 2013 11:12:07 -0000 1.4 +++ initdb-s-template.sql 17 Nov 2013 15:33:55 -0000 1.5 @@ -30,9 +30,6 @@ /*--*/ /*@XQT echo "*** SQLite - Configuring tables %DB_tbl_prefix% in database %DB_name%"*/ /*@XQT sqlite3 %DB_name% <<END_OF_TABLES*/ -drop table if exists %DB_tbl_prefix%filenum; -drop table if exists %DB_tbl_prefix%symnum; -drop table if exists %DB_tbl_prefix%typenum; drop table if exists %DB_tbl_prefix%files; drop table if exists %DB_tbl_prefix%symbols; drop table if exists %DB_tbl_prefix%definitions; @@ -41,26 +38,8 @@ drop table if exists %DB_tbl_prefix%status; drop table if exists %DB_tbl_prefix%langtypes; -create table %DB_tbl_prefix%filenum - ( rcd int primary key - , fid int - ); -insert into %DB_tbl_prefix%filenum - (rcd, fid) VALUES (0, 0); - -create table %DB_tbl_prefix%symnum - ( rcd int primary key - , sid int - ); -insert into %DB_tbl_prefix%symnum - (rcd, sid) VALUES (0, 0); - -create table %DB_tbl_prefix%typenum - ( rcd int primary key - , tid int - ); -insert into %DB_tbl_prefix%typenum - (rcd, tid) VALUES (0, 0); +/*- Tables for unique ids management -*/ +/*@ADD initdb/unique-user-sequences.sql*/ /* Base version of files */ /* revision: a VCS generated unique id for this version |
