Author: dmacvicar Date: Thu Apr 19 17:05:49 2007 New Revision: 5414 URL: http://svn.opensuse.org/viewcvs/zypp?rev=5414&view=rev Log: - desnormalize a capabilities table, and reduce loading to 1s Modified: trunk/libzypp/zypp2/cache/CacheStore.cpp trunk/libzypp/zypp2/cache/CacheStore.h trunk/libzypp/zypp2/cache/schema/schema.sql trunk/libzypp/zypp2/source/cached/CachedSourceImpl.cc Modified: trunk/libzypp/zypp2/cache/CacheStore.cpp URL: http://svn.opensuse.org/viewcvs/zypp/trunk/libzypp/zypp2/cache/CacheStore.cpp?rev=5414&r1=5413&r2=5414&view=diff ============================================================================== --- trunk/libzypp/zypp2/cache/CacheStore.cpp (original) +++ trunk/libzypp/zypp2/cache/CacheStore.cpp Thu Apr 19 17:05:49 2007 @@ -58,7 +58,7 @@ sqlite3_command_ptr select_file_cmd; sqlite3_command_ptr insert_file_cmd; - sqlite3_command_ptr insert_dependency_entry_cmd; + //sqlite3_command_ptr insert_dependency_entry_cmd; sqlite3_command_ptr append_file_dependency_cmd; sqlite3_command_ptr append_named_dependency_cmd; @@ -110,9 +110,9 @@ _pimpl->select_file_cmd.reset( new sqlite3_command( _pimpl->con, "select id from files where dir_name_id=:dir_name_id and file_name_id=:file_name_id;" )); _pimpl->insert_file_cmd.reset( new sqlite3_command( _pimpl->con, "insert into files (dir_name_id,file_name_id) values (:dir_name_id,:file_name_id);" )); - _pimpl->insert_dependency_entry_cmd.reset( new sqlite3_command( _pimpl->con, "insert into capabilities ( resolvable_id, dependency_type, refers_kind ) values ( :resolvable_id, :dependency_type, :refers_kind );" )); - _pimpl->append_file_dependency_cmd.reset( new sqlite3_command( _pimpl->con, "insert into file_capabilities ( capability_id, file_id ) values ( :capability_id, :file_id );" )); - _pimpl->append_named_dependency_cmd.reset( new sqlite3_command( _pimpl->con, "insert into named_capabilities ( capability_id, name_id, version, release, epoch, relation ) values ( :capability_id, :name_id, :version, :release, :epoch, :relation );" )); + //_pimpl->insert_dependency_entry_cmd.reset( new sqlite3_command( _pimpl->con, "insert into capabilities ( resolvable_id, dependency_type, refers_kind ) values ( :resolvable_id, :dependency_type, :refers_kind );" )); + _pimpl->append_file_dependency_cmd.reset( new sqlite3_command( _pimpl->con, "insert into file_capabilities ( resolvable_id, dependency_type, refers_kind, file_id ) values ( :resolvable_id, :dependency_type, :refers_kind, :file_id );" )); + _pimpl->append_named_dependency_cmd.reset( new sqlite3_command( _pimpl->con, "insert into named_capabilities ( resolvable_id, dependency_type, refers_kind, name_id, version, release, epoch, relation ) values ( :resolvable_id, :dependency_type, :refers_kind, :name_id, :version, :release, :epoch, :relation );" )); _pimpl->append_resolvable_cmd.reset( new sqlite3_command( _pimpl->con, "insert into resolvables ( name, version, release, epoch, arch, kind, catalog_id ) values ( :name, :version, :release, :epoch, :arch, :kind, :catalog_id );" )); @@ -237,10 +237,14 @@ ZYPP_THROW(Exception("bad versioned dep")); //DBG << "versioned : " << cap << endl; - data::RecordId capability_id = appendDependencyEntry( resolvable_id, deptype, cap->refers() ); + //data::RecordId capability_id = appendDependencyEntry( resolvable_id, deptype, cap->refers() ); data::RecordId name_id = lookupOrAppendName(cap->name()); - _pimpl->append_named_dependency_cmd->bind( ":capability_id", capability_id); + _pimpl->append_named_dependency_cmd->bind( ":resolvable_id", resolvable_id ); + _pimpl->append_named_dependency_cmd->bind( ":dependency_type", zypp_deptype2db_deptype(deptype) ); + _pimpl->append_named_dependency_cmd->bind( ":refers_kind", zypp_kind2db_kind(cap->refers()) ); + + //_pimpl->append_named_dependency_cmd->bind( ":capability_id", capability_id); _pimpl->append_named_dependency_cmd->bind( ":name_id", name_id); _pimpl->append_named_dependency_cmd->bind( ":version", cap->edition().version() ); _pimpl->append_named_dependency_cmd->bind( ":release", cap->edition().release() ); @@ -256,35 +260,39 @@ if ( !cap ) ZYPP_THROW(Exception("bad file cap")); - data::RecordId capability_id = appendDependencyEntry( resolvable_id, deptype, cap->refers() ); + //data::RecordId capability_id = appendDependencyEntry( resolvable_id, deptype, cap->refers() ); data::RecordId file_id = lookupOrAppendFile(cap->filename()); - _pimpl->append_file_dependency_cmd->bind( ":capability_id", capability_id); + _pimpl->append_file_dependency_cmd->bind( ":resolvable_id", resolvable_id ); + _pimpl->append_file_dependency_cmd->bind( ":dependency_type", zypp_deptype2db_deptype(deptype) ); + _pimpl->append_file_dependency_cmd->bind( ":refers_kind", zypp_kind2db_kind(cap->refers()) ); + + //_pimpl->append_file_dependency_cmd->bind( ":capability_id", capability_id); _pimpl->append_file_dependency_cmd->bind( ":file_id", file_id); _pimpl->append_file_dependency_cmd->executenonquery(); //delete cmd; } -data::RecordId CacheStore::appendDependencyEntry( const data::RecordId &resolvable_id, zypp::Dep deptype, const Resolvable::Kind &refers ) -{ - //DBG << "rid: " << resolvable_id << " deptype: " << deptype << " " << "refers: " << refers << endl; - _pimpl->insert_dependency_entry_cmd->bind( ":resolvable_id", resolvable_id ); - - db::DependencyType dt = zypp_deptype2db_deptype(deptype); - if ( dt == db::DEP_TYPE_UNKNOWN ) - { - ZYPP_THROW(Exception("Unknown depenency type")); - } - - _pimpl->insert_dependency_entry_cmd->bind( ":dependency_type", zypp_deptype2db_deptype(deptype) ); - _pimpl->insert_dependency_entry_cmd->bind( ":refers_kind", zypp_kind2db_kind(refers) ); - - _pimpl->insert_dependency_entry_cmd->executenonquery(); - //delete cmd; - long long id = _pimpl->con.insertid(); - return static_castdata::RecordId(id); -} +// data::RecordId CacheStore::appendDependencyEntry( const data::RecordId &resolvable_id, zypp::Dep deptype, const Resolvable::Kind &refers ) +// { +// //DBG << "rid: " << resolvable_id << " deptype: " << deptype << " " << "refers: " << refers << endl; +// _pimpl->insert_dependency_entry_cmd->bind( ":resolvable_id", resolvable_id ); +// +// db::DependencyType dt = zypp_deptype2db_deptype(deptype); +// if ( dt == db::DEP_TYPE_UNKNOWN ) +// { +// ZYPP_THROW(Exception("Unknown depenency type")); +// } +// +// _pimpl->insert_dependency_entry_cmd->bind( ":dependency_type", zypp_deptype2db_deptype(deptype) ); +// _pimpl->insert_dependency_entry_cmd->bind( ":refers_kind", zypp_kind2db_kind(refers) ); +// +// _pimpl->insert_dependency_entry_cmd->executenonquery(); +// //delete cmd; +// long long id = _pimpl->con.insertid(); +// return static_castdata::RecordId(id); +// } data::RecordId CacheStore::lookupOrAppendFile( const Pathname &path ) { Modified: trunk/libzypp/zypp2/cache/CacheStore.h URL: http://svn.opensuse.org/viewcvs/zypp/trunk/libzypp/zypp2/cache/CacheStore.h?rev=5414&r1=5413&r2=5414&view=diff ============================================================================== --- trunk/libzypp/zypp2/cache/CacheStore.h (original) +++ trunk/libzypp/zypp2/cache/CacheStore.h Thu Apr 19 17:05:49 2007 @@ -238,8 +238,8 @@ * the capabilities table for a specific capability * entry. */ - data::RecordId appendDependencyEntry( const data::RecordId &, - zypp::Dep, const Resolvable::Kind & ); +// data::RecordId appendDependencyEntry( const data::RecordId &, +// zypp::Dep, const Resolvable::Kind & ); private: /** Implementation. */ Modified: trunk/libzypp/zypp2/cache/schema/schema.sql URL: http://svn.opensuse.org/viewcvs/zypp/trunk/libzypp/zypp2/cache/schema/schema.sql?rev=5414&r1=5413&r2=5414&view=diff ============================================================================== --- trunk/libzypp/zypp2/cache/schema/schema.sql (original) +++ trunk/libzypp/zypp2/cache/schema/schema.sql Thu Apr 19 17:05:49 2007 @@ -309,36 +309,32 @@ -- Capabilities ------------------------------------------------ -CREATE TABLE capabilities ( - id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL +CREATE TABLE named_capabilities ( + id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , resolvable_id INTEGER REFERENCES resolvables(id) , dependency_type INTEGER , refers_kind INTEGER -); -CREATE INDEX capability_resolvable ON capabilities (resolvable_id); - -CREATE TABLE named_capabilities ( - id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL - , capability_id INTEGER REFERENCES capabilities (id) , name_id INTEGER REFERENCES names(id) , version TEXT , release TEXT , epoch INTEGER , relation INTEGER ); -CREATE INDEX named_capabilities_capability_id ON named_capabilities (capability_id); CREATE INDEX named_capabilities_name ON named_capabilities(name_id); CREATE TABLE file_capabilities ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL - , capability_id INTEGER REFERENCES capabilities (id) + , resolvable_id INTEGER REFERENCES resolvables(id) + , dependency_type INTEGER + , refers_kind INTEGER , file_id INTEGER REFERENCES files(id) ); -CREATE INDEX file_capabilities_capability_id ON file_capabilities (capability_id); CREATE TABLE split_capabilities ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL - , capability_id INTEGER REFERENCES capabilities (id) + , resolvable_id INTEGER REFERENCES resolvables(id) + , dependency_type INTEGER + , refers_kind INTEGER , name_id INTEGER REFERENCES names(id) , file_id INTEGER REFERENCES files(id) ); Modified: trunk/libzypp/zypp2/source/cached/CachedSourceImpl.cc URL: http://svn.opensuse.org/viewcvs/zypp/trunk/libzypp/zypp2/source/cached/CachedSourceImpl.cc?rev=5414&r1=5413&r2=5414&view=diff ============================================================================== --- trunk/libzypp/zypp2/source/cached/CachedSourceImpl.cc (original) +++ trunk/libzypp/zypp2/source/cached/CachedSourceImpl.cc Thu Apr 19 17:05:49 2007 @@ -132,8 +132,8 @@ // // } // } - sqlite3_command select_named_cmd( con, "select c.refers_kind, n.name, v.version, v.release, v.epoch, v.relation, c.dependency_type, c.resolvable_id from names n , named_capabilities v, capabilities c where v.name_id=n.id and c.id=v.capability_id ;"); - sqlite3_command select_file_cmd( con, "select c.refers_kind, dn.name, fn.name, c.dependency_type, c.resolvable_id from file_capabilities fc, capabilities c, files f, dir_names dn, file_names fn where f.id=fc.file_id and f.dir_name_id=dn.id and f.file_name_id=fn.id and c.id=fc.capability_id;"); + sqlite3_command select_named_cmd( con, "select v.refers_kind, n.name, v.version, v.release, v.epoch, v.relation, v.dependency_type, v.resolvable_id from names n , named_capabilities v where v.name_id=n.id;"); + sqlite3_command select_file_cmd( con, "select fc.refers_kind, dn.name, fn.name, v.dependency_type, v.resolvable_id from file_capabilities fc, files f, dir_names dn, file_names fn where f.id=fc.file_id and f.dir_name_id=dn.id and f.file_name_id=fn.id;"); { debug::Measure mnc("read named capabilities"); -- To unsubscribe, e-mail: zypp-commit+unsubscribe@opensuse.org For additional commands, e-mail: zypp-commit+help@opensuse.org