Re: [sqlite] About ticket #3452
On Fri, Oct 24, 2008 at 03:10:30PM -0700, Roger Binns wrote: > Nicolas Williams wrote: > > I've not run Evolution with SQLite3, so I don't know if it shares the > > cache. I'll ask the user in question to check. > > You'll need to ask the developer :-) According to the user Evolution has been fixed already. But still he sees a significant improvement in performance from using page sizes that match the host filesystem's preferred page size. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to limit the size of the database files?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tobias Müller wrote: >> For an even smaller footprint change, you can write your own >> xFileControl routine and then use sqlite3_file_control to >> query/set values. > > By "write your own xFileControl routine" did you mean that we should > change the SQLite source to support our query? Sending a file control is just standard SQLite 3 api http://sqlite.org/c3ref/file_control.html To receive the file control, you have to implement the xFileControl method in your VFS file http://sqlite.org/c3ref/io_methods.html For example you could have an opcode that returns the current file size and then keep calling sqlite3_file_control when you want to find that out. This would essentially be a reactive way of solving the problem. Using xWrite variants that i/o error would be proactive. You could also combine the approaches using a file control to get the current size (so you can have nice user interface) and another to set the maximum size which xWrite would take note of. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkkCQzAACgkQmOOfHg372QQI2gCgjg7iaeotdCjYlEZeQMDU1aqd 9C4AnR9MGs3N02P5P3RhRK95y1FsUSnU =uBPu -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] About ticket #3452
On Thu, Oct 23, 2008 at 04:04:58PM -0700, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Nicolas Williams wrote: > > (FYI, Evolution has/had a separate performance problem in that it opens > > a SQLite3 DB handle for the same DB every time you open a mail folder, > > whereas it could just re-use an existing open DB handle.) > > Why is that a problem. If shared cache is on, it shouldn't make any > difference in the big scheme of things. I've not run Evolution with SQLite3, so I don't know if it shares the cache. I'll ask the user in question to check. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Formatting to
On Fri, 24 Oct 2008 08:24:16 -0400 "Igor Tandetnik" <[EMAIL PROTECTED]> dijo: > > Yes - whatever happened to Title Case ? > > Still wouldn't work so well with, say, "The Definitive Guide To Sqlite" Yes, Igor has a very good point. The problem is the stupidity of English typesetting rules. Many have tried to code title case for English and few have succeeded. The problem is that "unimportant" words are to be left all lowercase. "Unimportant" means some conjunctions, most prepositions, and the articles a, an and the. While there is a short list of such words, there are additional exceptions. For example, if the title ends in "to" then "to" must be capitalized because the rule says to capitalize the first and last words regardless. There is also the problem of acronyms and abbreviations. And the first word following a colon is to be capitalized as well. Now, code can be written to accomplish most of the above without too much strain. But then you run into style manuals. Are you doing APA, Chicago, MLA, London Times, or whose style manual are you to follow? Every one of them has a slight variation on the rules. Title case in English is not trivial. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to bind a tinyint so that actually uses 1 byte?
> As long as your values are within the range of -128 to +127, your > integers will only take one byte of storage (plus common overhead). > There's an extra byte of meta data for each column value in each row due to manifest typing, so an int will take at least 2 bytes of storage per column per row. http://www.sqlite.org/fileformat.html#record_format The exceptions are the special values 0 and 1 when using pragma legacy_file_format=0; then only 1 byte is required to store these values. -- View this message in context: http://www.nabble.com/How-to-bind-a-tinyint-so-that-actually-uses-1-byte--tp20147265p20152977.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to generate a scheme
I'm trying to do the exact same thing via a C++ app right now. I don't know if a "schema" can be create other way than via the command line .s . But it may help you to know that there's a table called sqlite_master that contain infos on each tables and indexes of your database. I'm currently iterating through it to get what I call "MyDB_Structure". I can then validate each table/column of a DB. I can output the info of combine sqlite_master + PRAGMA table_info (tableName) in an XML file when I know my MyDB_Structure is ok and whenever I need to validate table/column of the DB, I load the XML file and try to match Structure with sqlite_master + PRAGMA table_info (tableName). Hope that gives you a hint. :) Antoine Caron -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Karl Lautman Sent: October 23, 2008 4:18 PM To: sqlite-users@sqlite.org Subject: [sqlite] How to generate a scheme I'm still feeling my way around sqlite. One thing that would be handy would be the ability to generate a scheme (schema?) for a particular database, to confirm it has the tables I think it has, and those tables have the characteristics I think they have (as well as to refresh my memory). How is this done with sqlite? I'm partial to python, so if there's a python module to do it, so much the better. Thanks. ___ 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
Re: [sqlite] SQLITE_CORE use for ??
D. Richard Hipp <[EMAIL PROTECTED]> writes: > > A developer using SQLite in their product should never have to mess > with SQLITE_CORE. The SQLITE_CORE macro is for internal use only. If > you find a case where you think you have to set SQLITE_CORE manually > in order to compile SQLite, that is bug - either in SQLite itself or > in your use of SQLite. > > D. Richard Hipp > [EMAIL PROTECTED] > Wondering if there was a way to prevent the FTS3 source getting built, I looked at the source to find that it would *not* get built if SQLITE_CORE was defined. >> /* ** The code in this file is only compiled if: ** ** * The FTS3 module is being built as an extension ** (in which case SQLITE_CORE is not defined), or ** ** * The FTS3 module is being built into the core of ** SQLite (in which case SQLITE_ENABLE_FTS3 is defined). */ #if !defined(SQLITE_CORE) || defined(SQLITE_ENABLE_FTS3) << The only way to set this would appear to be at compile time, using -DSQLITE_CORE in the CFLAGS, but this in not currently in my build file. Hence to remove the FTS3 code from my binaries I surely need to -DSQLITE_CORE during compilation. So this must be a case where I need to set it manually !? Regards, MikeW ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Formatting to
"MikeW" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Igor Tandetnik <[EMAIL PROTECTED]> writes: > >> >> Are you sure you would be happy with "Uncle tom's cabin", or >> "Adventures of tom sawyer", or "Oliver twist" ? >> > > Yes - whatever happened to Title Case ? Still wouldn't work so well with, say, "The Definitive Guide To Sqlite" Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Formatting to
Igor Tandetnik <[EMAIL PROTECTED]> writes: > > Are you sure you would be happy with "Uncle tom's cabin", or "Adventures > of tom sawyer", or "Oliver twist" ? > > Igor Tandetnik > Yes - whatever happened to Title Case ? MikeW ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to bind a tinyint so that actually uses 1 byte?
On Fri, Oct 24, 2008 at 02:49:14AM -0700, dbikash scratched on the wall: > > Hi, > > I am using tinyint in my schema, but while doing paramterized insertion, I > find that there is no specific bind API to insert a tinyint. So I used int > sqlite3_bind_int() instead. > > However, the size of my database suggests that SQLite might actually be > using 4 bytes instead of 1. Is it? How can I insert tinyint's in a > parameterized query so that it takes only 1 byte of space? As with strings, SQLite automatically adjusts the size of the stored integer to match the magnitude of the value. All integers are signed, and SQLite supports sizes of 1-4, 6, or 8 bytes. As long as your values are within the range of -128 to +127, your integers will only take one byte of storage (plus common overhead). -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Formatting to "Sentence case"?
"Gilles Ganault" <[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
> A column holds book titles which are all in capital letters ("MY BOOK
> TITLE"), and I'd like to reformat them as "My book title".
Are you sure you would be happy with "Uncle tom's cabin", or "Adventures
of tom sawyer", or "Oliver twist" ?
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to limit the size of the database files?
Roger Binns wrote: > You can write your own VFS that gives an I/O error on writes that make > the file larger than you want. This won't be much code since you can > just point to existing VFS routines (eg unix/winTruncate, unix/winLock > etc) for most of the code. After a first look, this really looks not too hard to do. Thanks! > For an even smaller footprint change, you can write your own > xFileControl routine and then use sqlite3_file_control to > query/set values. By "write your own xFileControl routine" did you mean that we should change the SQLite source to support our query? Or is it possible to register a new xFileControl function using some API call? Tobias ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to bind a tinyint so that actually uses 1 byte?
On Fri, Oct 24, 2008 at 2:49 AM, dbikash <[EMAIL PROTECTED]> wrote: > > Hi, > > I am using tinyint in my schema, but while doing paramterized insertion, I > find that there is no specific bind API to insert a tinyint. So I used int > sqlite3_bind_int() instead. > > However, the size of my database suggests that SQLite might actually be > using 4 bytes instead of 1. Is it? How can I insert tinyint's in a > parameterized query so that it takes only 1 byte of space? > sqlite only has a single integer type - there is no tinyint. sqlite stores integers in a variable-length encoding. 1 byte stores 7 bits, 2 stores 14 bits, etc. 9 bytes maximum. -- Cory Nelson http://www.int64.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to bind a tinyint so that actually uses 1 byte?
Hi, I am using tinyint in my schema, but while doing paramterized insertion, I find that there is no specific bind API to insert a tinyint. So I used int sqlite3_bind_int() instead. However, the size of my database suggests that SQLite might actually be using 4 bytes instead of 1. Is it? How can I insert tinyint's in a parameterized query so that it takes only 1 byte of space? Thanks, dbikash -- View this message in context: http://www.nabble.com/How-to-bind-a-tinyint-so-that-actually-uses-1-byte--tp20147265p20147265.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance Problems with joining and subqueries
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Da Martian wrote: > Regarding using "Case" I didnt know sqlite supported this. In fact looking > at core functions on the web there isnt any mention of a case statement, nor > in aggregate functions. Case is not a function but rather an expression. You can see the doc at http://www.sqlite.org/lang_expr.html which also covers a lot of other things, although it would be nice if the page included a case example. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkkBmRwACgkQmOOfHg372QSCFACeNOY/PPpARA3BT+HUbteOR3+n 7aUAn3n7kczRGEhVWPqVF1PMgSuES2IW =dXC6 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance Problems with joining and subqueries
Hi I do appologise. In my hast, I did copy bits of a larger query. The queries are correct they just have extra bits around the edges. I will post the corrections below. Regarding using "Case" I didnt know sqlite supported this. In fact looking at core functions on the web there isnt any mention of a case statement, nor in aggregate functions. Knowing this I can restructure as you have suggested. My first attempt at running the query as provided showed 10+ minutes performance similiar to the two subqueries joined together meaning the temp table option still wins hands down. So I still have the question about why a temp table is quicker than a subquery (derived table)? >For this query, an index on (Customer, PrincipalContractNo, SASContract, >BusinessArea, ProductGroup, Site), or any prefix of this list, might >help. Regarding this, I did create this index as mentioned in the post before, however it performed worse than the PK index. The PK index query takes around 1 minute. Creating the above index it balloons to over 10+ minutes which I found strange as in theory a index matching the group by should help collect the records together in the correct order. Thanks again for your help, and sorry about the shody "copy paste" job. Corrected Subqueires: Corrections: 1st subquery: SELECT cl.Customer as Customer, cl.PrincipalContractNo as PrincipalContractNo, SASContract as SASContractNo, cl.BusinessArea, cl.ProductGroup, cl.Site, rr.period_id as period, sum(cl.NCRAmt) as NCRAmt, sum(rr.revenue_amount) as RevenueAmt FROM ROT_DIM_CONTRACT_LINE cl join ROT_FACT_REV_ROLLOUT rr on (cl.ContractNo = rr.ContractNo and cl.ContractLine = rr.ContractLine) where rr.period_id > 200809 group by cl.Customer, PrincipalContractNo, SASContract, cl.BusinessArea, cl.ProductGroup, cl.Site, rr.period_id 2nd Subquery: SELECT cl.Customer as Customer, cl.PrincipalContractNo as PrincipalContractNo, SASContract as SASContractNo, cl.BusinessArea, cl.ProductGroup, cl.Site, sum(cl.NCRAmt) as NCRAmt FROM ROT_DIM_CONTRACT_LINE cl join ROT_FACT_REV_ROLLOUT rr on (cl.ContractNo = rr.ContractNo and cl.ContractLine = rr.ContractLine) where rr.period_id <= 200809 group by cl.Customer, PrincipalContractNo, SASContract, cl.BusinessArea, cl.ProductGroup, cl.Site On Thu, Oct 23, 2008 at 4:52 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > Da Martian <[EMAIL PROTECTED]> wrote: > > I have two tables as follows: > > > > CREATE TABLE ROT_DIM_CONTRACT_LINE (ContractNo Integer,ContractLine > > Integer,StartDate Integer,EndDate Integer,NCRAmt Float,Item > > Integer,BusinessArea Text,ProductGroup Text,ProductStyle Text,Site > > Text,Customer Integer,PrincipalContractNo Text,SASContract > > Text,ContractMonths Float,StartMonths Float,FullMonths Float,EndMonths > > Float,MonthlyAmortAmt Float,FirstAmortAmt Float,LastAmort > > Float,BalancingAmortAmt Float,RolloutToContractDiff Float, PRIMARY KEY > > (ContractNo ASC,ContractLine ASC)) > > -CREATE UNIQUE INDEX IDX_ROT_DIM_CONTRACT_LINE_1 ON > > ROT_DIM_CONTRACT_LINE (ContractNo ASC,ContractLine ASC) > > You don't need this index. PRIMARY KEY clause has already created the > same index implicitly. > > > CREATE TABLE ROT_FACT_REV_ROLLOUT (Period_ID Integer,ContractNo > > Integer,ContractLine Integer,Revenue_Amount Float, PRIMARY KEY > > (Period_ID > > ASC,ContractNo ASC,ContractLine ASC)) > > CREATE UNIQUE INDEX IDX_ROT_FACT_REV_ROLLOUT_1 ON ROT_FACT_REV_ROLLOUT > > (Period_ID ASC,ContractNo ASC,ContractLine ASC) > > Same here. Drop the index. > > > ROT_DIM_CONTRACT_LINE has 131,747 records > > ROT_FACT_REV_ROLLOUT has 3,971,369 records > > > > The process I am doing is two fold: > > 1) Joining the data for complete list > > 2) Splitting the data by date (Period_ID) into two catagories based > > on a > > threshold date. > >so if my threshold is Sep 2008 (200809) I want all records after > > Sep 2008 to be displayed AND > >I want a total column for all records prior to Sep 2008. > > > No case statements in SQLite so two subqueries does the job: > > > >> SEP 2008 > > select * from > > (SELECT cl.Customer as Customer, cl.PrincipalContractNo as > > PrincipalContractNo, SASContract as SASContractNo, cl.BusinessArea, > > cl.ProductGroup, cl.Site, rr.period_id as period, sum(cl.NCRAmt) as > > NCRAmt, sum(rr.revenue_amount) as RevenueAmt > > FROM > > ROT_DIM_CONTRACT_LINE cl join > > ROT_FACT_REV_ROLLOUT rr on > >(cl.ContractNo = rr.ContractNo and > > cl.ContractLine = rr.ContractLine) > > where > >rr.period_id > 200809 > > group by > > cl.Customer, PrincipalContractNo, SASContract, cl.BusinessArea, > > cl.ProductGroup, cl.Site, rr.period_id > > You have fewer closing parens here than you have opening ones. Why are > you doing "select from select"? Why an extra level of indirection? > > For this query, an index on (Customer, PrincipalContractNo, SASContract, > BusinessArea, ProductGroup, Site), or any prefix of this list, might > help.
Re: [sqlite] Formatting to "Sentence case"?
Perhaps by doing an UPDATE with:
set booktitle = upper(substr(booktitle,1,1)) || lower(substr(booktitle,2))
Regards,
-Toby
>Hello
>
>A column holds book titles which are all in capital letters ("MY BOOK
>TITLE"), and I'd like to reformat them as "My book title".
>
>Is there a function in SQLite that does this, or should I use some
>external language to do this?
>
>Thank you.
>
>___
>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] Formatting to "Sentence case"?
Hello
A column holds book titles which are all in capital letters ("MY BOOK
TITLE"), and I'd like to reformat them as "My book title".
Is there a function in SQLite that does this, or should I use some
external language to do this?
Thank you.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
