Abhishek Dasgupta wrote:

Thanks for the useful script! But shouldn't this line: known_files[entry] =3D (repo, package) be known_files[entry].append((repo, package))

That is a nice suggestion! Using lists as hash values could make the tool useful for purposes other than checking packages built from unsupported. The program does quite a bit of I/O. I'm tempted to convert those file lists into a sqlite database, since it would really improve efficiency. -- Chris

2009/4/5 Daenyth Blank :

That's not a bad idea, and it's one that's been recommended to me for pkgfile as well. Is there a simple (read: bash-usable) interface I could work with to speed up pkgfile?

I was trying out the command line sqlite3 tool to manage a database comprised of the following fields: key pkgname file The problem is that insertion into the database is *slow*. It takes over an hour to generate the databases for the official repositories. Thus, if we're to use sqlite then only the differences should be updated. Otherwise these filelist databases could be generated on some other server and then downloaded by pkgfile. The advantage of sqlite is of course that searching is nearly instanteneous. -- Abhishek

2009/4/8 Daenyth Blank :

That sounds promising... If we can find some method to construct only the differences, then it would be doable. Have a method for the user to do initial generation, update an existing db, and in addition, to download a database from a known source (I could generate a db in a cronjob and then host it on twilightlair I think). I have no experience with this at all, what would you recommend for doing an incremental update?

The initial generation is taking really long! It's over two hours and still generating... As for differences, we could generate a text containing all files with the corresponding pkgnames after each update. Then by doing a diff -u with the previous text we can delete and add accordingly. This step won't take too much time, hopefully. Also, there has to be some way of escaping the filename so that sqlite does not give errors like this: SQL error: near "N": syntax error SQL error: near "n": syntax error SQL error: near "s_Eye": syntax error Of course if the databases could be stored on some server, then the user is spared the problem of generating the database and updating it. -- Abhishek

Abhishek Dasgupta wrote:

The initial generation is taking really long! It's over two hours and still generating... *SNIP* Also, there has to be some way of escaping the filename so that sqlite does not give errors like this: SQL error: near "N": syntax error

I made a fork of pkgtools on github. Have a look at the sqlite branch of http://github.com/CMB/pkgtools I wrote a script which incrementally updates an SQLite database from a REPO.files.tar.gz tarball. It is in pkgtools/other/repofile2db.py The code is terribly raw! I'm afraid to try it, until I've done some more desk-checking. -- Chris

2009/4/9 Abhishek Dasgupta :

I made a sqlite-bash branch on github [1] with the create_db function which I'm using right now. It's not incremental yet but adding a separate table with package versions like you've done would solve the problem.

Just added incremental update support to sqlite-bash branch. I tested it out with the databases I already had [1] and it worked fine. Updating the filelists was far quicker and finished within a few minutes. For anyone wishing to try out, download the databases and drop them into /var/cache/pkgtools/lists after gunzipping. Use the pkgfile from the git branch and run `sudo pkgfile -uv`. The new database versions.db contains the package names, versions and repository names and has the following format: pkgname TEXT, pkgver TEXT, repo TEXT At the moment, /var/cache/pkgtools/lists/{reponame} is not deleted, though it's not really used (except for the listfiles() function which I haven't modified to use the sqlite db yet). Also the --binaries switch which allows one to search for files in bin/ or sbin/ might not work properly. [1]: http://abhidg.mine.nu/arch/package-databases/ -- Abhishek

Daenyth Blank wrote:

How is the filesize of the REPO.db? CMB was reporting that community was taking up more than 75mb with his version. Regardless of how the sqlite features would be implemented, I will also keep the flat file version available, and configurable. How are the searching and updating speeds compared to before?

Here are the rest of my results from yesterday. Inserting all of core, extra, and community took less than 6 minutes, on a machine with a single 2.5 GHz Pentium 4 CPU and 512 MB of RAM. The database weighed in at a whopping 240 megabytes. I haven't tried an incremental update yet. Here is what I know about search speeds. A query like "select * from files where filename = 'usr/bin/getmail'" is practically instantaneous, as one would expect. "select * from files where filename like '%/getmail'" takes 5 seconds or so, because it has to iterate through all the filenames in the table. If sqlite were used for pkgfile, that second query would need to be quite a bit faster than it is now. -- Chris

Chris Brannon wrote:

"select * from files where filename like '%/getmail'" takes 5 seconds or so, because it has to iterate through all the filenames in the table.

If sqlite were used for pkgfile, that second query would need to be quite a bit faster than it is now.

-- Chris

Couldn't we simply introduce a new column, store the filename there and create an index on it?

On Thu, Apr 9, 2009 at 13:50, Abhishek Dasgupta wrote:

2009/4/9 Evangelos Foutras :

...

Couldn't we simply introduce a new column, store the filename there and create an index on it?

That's a nice idea. I'll try out and see how much the db size increases. -- Abhishek

Can you pull from my sqlite-bash branch? I have some cleanup fixes that will be easier to apply before proceeding, though the function is still pretty much the same,

2009/4/9 Daenyth Blank :

How is the filesize of the REPO.db? CMB was reporting that community was taking up more than 75mb with his version. Regardless of how the sqlite features would be implemented, I will also keep the flat file version available, and configurable. How are the searching and updating speeds compared to before?

Filesizes: core.db 2.1M (440K) extra.db 42M (7.2M) community.db 39M(5.7M) Filesizes in brackets are after gzip -9. Searching (after) $ time pkgfile search getmail getmail usr/bin/getmail real 0m4.458s user 0m2.816s sys 0m0.297s Searching (before) extra/getmail real 0m12.403s user 0m0.450s sys 0m1.380s Updating speed is slower than previous since the previous method just extracted the files. However incremental update finishes within 5 mins. -- Abhishek