[sqlite] SQLite Suitability for Shopping Cart
I am building a shopping cart application that calls a web service which provides lists of documents and the documents themselves. The user purchases document images. The application will load lists into a database, then build pages from the stored lists. The database will also keep track of application state and store the retrieved documents for a month. It must be capable of supporting 100 simultaneous users. The maximum list size is 8000 documents which takes about 5 minutes to load from the web service (in 5 second transactions). There will be more than one front end server. >From this description, do you think SQLite is a valid choice as the database in this scenario? -- View this message in context: http://www.nabble.com/SQLite-Suitability-for-Shopping-Cart-tp25530589p25530589.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] cygwin and sqlite
Jim Showalter wrote: > I thought you were exaggerating, but this weekend I wrote a script for > work that used cygwin. It's unbelievably slow compared to the DOS > script it replaced, and it performs mostly the same operations as the > DOS script. Which is weird, because they wouldn't have to spawn off > separate Windows processes at all--they could interpret the commands > and call methods on a shared DLL, all in the same process. > > It's absurd that you were blacklisted for pointing out that naked > emperor over there behind the bushes. Well, it was probably late at night and tact went to bed before I did. ;-) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date comparisons
Barton Torbert wrote:
> Even though this is a bit more complicated, it does seem to work
> correctly.
>
> SELECT t1.realdate1,
> t2.realdate2
> from test_table_1 t1 left outer join test_table_2 t2 where
> strftime ('%s', t2.realdate2) between strftime ('%s',
> datetime(t1.realdate1, '-1 minutes') ) and strftime ('%s',
> datetime(t1.realdate1, '+1 minutes') )
This would lose any benefit of the index on realdate2, in case you have
one.
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date comparisons
Barton Torbert wrote:
> What you suggested sort of worked. The problem was that the two
> dates I were comparing were only 1 minute apart, but I had to put in
> '-1000 minutes' and '+1000 minutes' to get the test to work.
>
> My two data values are;
> 2009-04-13T12:19:00.000
> and
> 2009-04-13T12:18:00.000
datetime() produces the value without 'T' in the middle - like this:
2009-04-13 12:09:00. So, as long as the date part is the same, the value
with a space would alphabetically precede the one with 'T'.
In place of datetime, try
strftime('%Y-%m-%dT%H:%M:%f', t1.dateitem1, '-10 minutes')
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] cygwin and sqlite
I thought you were exaggerating, but this weekend I wrote a script for work that used cygwin. It's unbelievably slow compared to the DOS script it replaced, and it performs mostly the same operations as the DOS script. Which is weird, because they wouldn't have to spawn off separate Windows processes at all--they could interpret the commands and call methods on a shared DLL, all in the same process. It's absurd that you were blacklisted for pointing out that naked emperor over there behind the bushes. - Original Message - From: "John" To: "General Discussion of SQLite Database" Sent: Monday, September 21, 2009 7:29 PM Subject: Re: [sqlite] cygwin and sqlite > Pavel Ivanov wrote: >>> MacBook Mac OS X 10.5.8 >>> 2 GHz Intel Core Duo >>> 1 GB memory: >>> 17 minutes 46 seconds. >>> >>> IBM ThinkPad >>> Windows XP (latest patches) >>> 1.70 GHz, 512 MB memory: >>> 6 hours 25 minutes 57 seconds >> >> Windows is very slow in starting new processes if compared to any >> Unix >> system (especially if compared Windows + 512 MB and Unix + 1 GB). >> In >> cygwin starting new processes even slower because for some reason >> emulating fork() involves starting 2 processes one of which dies >> immediately. And bash scripts use processes a lot especially with >> Unix >> paradigm when for each small action you start new program (like >> sed, >> awk, test, true and whole lot of others). Thus bash scripts on >> cygwin >> will be slow unavoidably. >> But I'm digressing. This is subject for some other mailing list. :) > > I'm blacklisted apparently on the cygwin mailing list for when a > couple of years ago I complained rather unflatteringly about how > slow > it was/is when I was writing a simple expenses program (that works > in > seconds on my Mac). I forgot about that. A badge of honor in my > opinion. > > But after all these years I wonder why they don't fix the fork > problem? > MacOS runs on Intel processors. Windows runs on Intel processors. > Surely > they could learn how it *should* be done by studying things like the > Open Source Java code? > > It looks like I won't be able to distribute my stopgap cygwin code > on > Windows. I need to start speed reading my Java/Swing books I guess > in > my quest for my program to write once, run anywhere. > >> Pavel >> >> On Fri, Sep 18, 2009 at 3:26 AM, John wrote: >>> Pavel Ivanov wrote: > At least I think that is what you suggest, and think it just > may work! But I could be wrong! Yes, that's exactly what I suggest. Pavel >>> It worked! Fortunately I had already parameterized SQLITE3 as a >>> preference variable so I could have the same scripts run easily on >>> Mac >>> OS and Windows. There are dozens of sqlite3 calls throughout the >>> scripts. >>> >>> My whole set of scripts that process raw data and load the >>> database by >>> reading text files seem to work. >>> >>> cygwin is as slow as I recall, however. I was writing expense >>> scripts a >>> few years ago and abandoned it for MacOS Unix. I moved 100% to Mac >>> OS. >>> (except for this project which I want to work on Mac, linux, and >>> Windows; my next goal is recoding it in Java with its Swing GUI, >>> but I'm >>> just learning Java and Swing, but I'm on my way...). >>> >>> Observed elapsed times on my two notebook computers for the same >>> scripts >>> to load the database (using sqlite3 calls and lots of sed and awk >>> processing of thousands of lines of input data): >>> >>> MacBook Mac OS X 10.5.8 >>> 2 GHz Intel Core Duo >>> 1 GB memory: >>> 17 minutes 46 seconds. >>> >>> IBM ThinkPad >>> Windows XP (latest patches) >>> 1.70 GHz, 512 MB memory: >>> 6 hours 25 minutes 57 seconds >>> >>> Fortunately, sqlite .dump and restoring from the resultant sql >>> will be >>> able to be used for most of the heavy lifting when I'm done. >>> Changes to >>> the data will come in small increments over time from then on. My >>> dumpit >>> and restoreit scripts each take only seconds on both platforms for >>> the >>> full set of current data. >>> >>> Thanks! >>> On Thu, Sep 17, 2009 at 1:18 PM, John wrote: > Pavel Ivanov wrote: >>> I'd rather avoid building sqlite3 under cygwin. I would like >>> to keep as much as possible in native code, compromising only >>> on cygwin to run my scripts. >> And this is root of your problem. Using mix of cygwin-native >> applications with windows-native applications will always have >> such >> problem. >> >>> When installing cygwin, you it offers you the choice to switch >>> to default text file type to DOS (\r\n). Should I try that? >> Don't do that. This mode of operation is not supported much and >> not >> recommended by cygwin developers and it reportedly will >> significantly >> slow down cygwin's operation. >> >>> So I guess my question here is, do any sqlite users here >>> have experience fixing this on Windows for Unix cygwin >>
Re: [sqlite] cygwin and sqlite
Pavel Ivanov wrote: >> MacBook Mac OS X 10.5.8 >> 2 GHz Intel Core Duo >> 1 GB memory: >> 17 minutes 46 seconds. >> >> IBM ThinkPad >> Windows XP (latest patches) >> 1.70 GHz, 512 MB memory: >> 6 hours 25 minutes 57 seconds > > Windows is very slow in starting new processes if compared to any Unix > system (especially if compared Windows + 512 MB and Unix + 1 GB). In > cygwin starting new processes even slower because for some reason > emulating fork() involves starting 2 processes one of which dies > immediately. And bash scripts use processes a lot especially with Unix > paradigm when for each small action you start new program (like sed, > awk, test, true and whole lot of others). Thus bash scripts on cygwin > will be slow unavoidably. > But I'm digressing. This is subject for some other mailing list. :) I'm blacklisted apparently on the cygwin mailing list for when a couple of years ago I complained rather unflatteringly about how slow it was/is when I was writing a simple expenses program (that works in seconds on my Mac). I forgot about that. A badge of honor in my opinion. But after all these years I wonder why they don't fix the fork problem? MacOS runs on Intel processors. Windows runs on Intel processors. Surely they could learn how it *should* be done by studying things like the Open Source Java code? It looks like I won't be able to distribute my stopgap cygwin code on Windows. I need to start speed reading my Java/Swing books I guess in my quest for my program to write once, run anywhere. > Pavel > > On Fri, Sep 18, 2009 at 3:26 AM, John wrote: >> Pavel Ivanov wrote: At least I think that is what you suggest, and think it just may work! But I could be wrong! >>> Yes, that's exactly what I suggest. >>> >>> Pavel >> It worked! Fortunately I had already parameterized SQLITE3 as a >> preference variable so I could have the same scripts run easily on Mac >> OS and Windows. There are dozens of sqlite3 calls throughout the scripts. >> >> My whole set of scripts that process raw data and load the database by >> reading text files seem to work. >> >> cygwin is as slow as I recall, however. I was writing expense scripts a >> few years ago and abandoned it for MacOS Unix. I moved 100% to Mac OS. >> (except for this project which I want to work on Mac, linux, and >> Windows; my next goal is recoding it in Java with its Swing GUI, but I'm >> just learning Java and Swing, but I'm on my way...). >> >> Observed elapsed times on my two notebook computers for the same scripts >> to load the database (using sqlite3 calls and lots of sed and awk >> processing of thousands of lines of input data): >> >> MacBook Mac OS X 10.5.8 >> 2 GHz Intel Core Duo >> 1 GB memory: >> 17 minutes 46 seconds. >> >> IBM ThinkPad >> Windows XP (latest patches) >> 1.70 GHz, 512 MB memory: >> 6 hours 25 minutes 57 seconds >> >> Fortunately, sqlite .dump and restoring from the resultant sql will be >> able to be used for most of the heavy lifting when I'm done. Changes to >> the data will come in small increments over time from then on. My dumpit >> and restoreit scripts each take only seconds on both platforms for the >> full set of current data. >> >> Thanks! >> >>> On Thu, Sep 17, 2009 at 1:18 PM, John wrote: Pavel Ivanov wrote: >> I'd rather avoid building sqlite3 under cygwin. I would like >> to keep as much as possible in native code, compromising only >> on cygwin to run my scripts. > And this is root of your problem. Using mix of cygwin-native > applications with windows-native applications will always have such > problem. > >> When installing cygwin, you it offers you the choice to switch >> to default text file type to DOS (\r\n). Should I try that? > Don't do that. This mode of operation is not supported much and not > recommended by cygwin developers and it reportedly will significantly > slow down cygwin's operation. > >> So I guess my question here is, do any sqlite users here >> have experience fixing this on Windows for Unix cygwin >> script calls? > The major suggestion here: write some "windows native code launcher" > that will be used for running all non-cygwin applications (this can be > just function in the script). It will do nothing on unix platforms > (select your own preferred way of distinguishing it) and it will > always strip off '\r' from output of running application on windows > (you can use sed for that). And there's nothing else you can do about > it. This sounds like a great idea. I can have all sqlite3.exe calls "intercepted" by another script call like: NumPar=`WINDOWSCALL Program Arguments` WINDOWSCALL is the launcher that calls Program sqlite3.exe with its arguments and strips off any trailing \r's and returns that string to the caller through stdout, as to NumPar here. WINDOWSCALL can do nothing on Unix/MacOS, and fix the string on Wind
Re: [sqlite] SQLite database on a certain high-performance "SSD"
SSDs aren't necessarily faster than disks: http://en.wikipedia.org/wiki/Solid-state_drive#Disadvantages - Original Message - From: "Mark" To: Sent: Monday, September 21, 2009 1:53 PM Subject: [sqlite] SQLite database on a certain high-performance "SSD" > I've currently got a loaner high-performance flash-based "SSD" > (let's > just say it doesn't connect to any disk controllers) that I'm > testing > for performance. I've run my application against it, and I believe > that > I should see numbers MUCH higher than I do. When I run my test app > on a > normal SATA 7200 RPM disk, I get a certain performance, and on the > "SSD" > I get about 1/10th that speed. On an array of SAS disks I get > numbers > that are about 5x faster than my SATA disk, so my software itself > isn't > (I believe) the bottleneck. > > I'm wondering if anyone has any tips for "optimizing" for this sort > of > storage solution. > > Also, if anyone has any quick-and-dirty test setups they'd like me > to > run through on this rig, just let me know :) > > ___ > 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] List of active savepoints names
I half get his point--if it's hard to program, then only
really good programmers will do it.
But that argument has been made against every advance in software
development since the profession began. Patchcord-pluggers made fun of
switch-flippers. Switch-flippers made fun of microcoders. Microcoders
made fun of assembly-language programmers. Assembly-language
programmers made fun of high-level-language programmers.
High-level-language programmers made fun of modelers. And everyone
makes fun of ruby programmers.
But this argument is basically bullshit, because it misses a key
truth: software has improved in power, usability, and ubiquity. When
only a handful of people could program a computer, then only a handful
of useful things were doable with computers. Was that desirable? Or is
it better that we have zillions of powerful applications written by
all manner of programmers available all over the place? If the masses
can program, then you get masses of programs, and you can let market
forces weed out the bad ones.
Linus' argument basically boils down to real men don't use numerically
controlled machine tools. Back when men were men, they built cars by
hand, the way god intended cars to be built. The way his grandpappy
made cars, dammit, snarls manly Linus as he rocks on his porch,
whittlin' and spittin' chaw.
- Original Message -
From: "Roger Binns"
To: "General Discussion of SQLite Database"
Sent: Monday, September 21, 2009 10:42 AM
Subject: Re: [sqlite] List of active savepoints names
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Lukas Gebauer wrote:
>> I am not creating and releasing savepoints!
>
> Yes you are. The SQLite library is not magically doing them on a
> whim.
> Your code is asking for them.
>
>>> You can create a custom wrapper for savepoints which may have
>>> the stack of calls.
>>
>> Why I must duplicate code what is inside Sqlite too?
>
> There is no code inside SQLite that returns the list of savepoints
> so there
> is nothing to duplicate. There is a data structure of savepoints.
>
> Adding this code and API seems simple enough and superficially it
> is.
> However once added it cannot be removed (dynamic linking would
> break), has
> to be documented and tested, and adds some bloat to SQLite. It
> would also
> constrain future changes to underlying data structures. For example
> if the
> public API returns a flat list, it would complicate things if the
> internal
> data structure would be better off as a tree.
>
> Although it may not seem like it, one of the most important jobs of
> project
> maintainers is saying "no". Here is Linus Torvalds on the subject:
>
> http://linuxmafia.com/faq/Kernel/linus-im-a-bastard-speech.html
>
>> Sqlite have savepoint stack inside (AFAIK). I really not know why
>> is
>> a problem to list this stack through some API. (But maybe it really
>> is a problem, I don't know, but nobody tell this yet...)
>
> This code will print out the names to stderr. You should be able to
> do the
> equivalent from your debugger.
>
> void print_savepoints(sqlite3 *db)
> {
> int i;
> for(i=0;inSavepoint;i++)
>fprintf(stderr, "%s\n", db->pSavepoint[i]->zName);
> }
>
> They are also in a tree like structure so it isn't quite that
> simple.
>
>> Yes, I can create my own wrapper, what can work with my code only,
>
> It is your code that needs debugging :-)
>
>> Just because I must duplicate
>> lot of code for decide if savepoint is or not is created,
>
> If you have random code generating random SQL then it is harder, but
> that is
> a bad design anyway. Generally you'll have a function that when
> called
> generates savepoints and another for rolling them back. But even
> with
> random code, the authorizer interface can tell you when savepoint
> SQL is
> prepared.
>
>> And now compare it with simple API funtion what just list existing
>> savepoint stack inside Sqlite. It is simple, trivial, useful... and
>> working right at any case.
>
> And constrains data structures, requires documentation and testing,
> can
> never be removed and adds bloat. And in most cases still isn't
> useful by
> itself - you would still need to add yet another wrapper around that
> function depending on what you want to happen (print it indented as
> a tree,
> convert to utf16 or ascii, combine names separated by commas, stdout
> or
> stderr, gui dialog etc).
>
>> This is why I am asking for. Thanks!
>
> A simple rule of thumb is to look at how useful features would be to
> other
> SQLite users. Note that you are the only one to have asked for this
> feature
> and not one other person has agreed with you on its need! The
> responses
> have generally been along the lines of suggesting you organize your
> code so
> that you do not need it. But even if it was added as a debugging
> aid, do
> you expect people to compile up two versions of SQLite - one with
> debugging
> aids on and another with them off? This feature would impose
> decisi
Re: [sqlite] Date comparisons
Even though this is a bit more complicated, it does seem to work correctly.
SELECT t1.realdate1,
t2.realdate2
from test_table_1 t1 left outer join test_table_2 t2 where
strftime ('%s', t2.realdate2) between strftime ('%s',
datetime(t1.realdate1, '-1 minutes') ) and strftime ('%s',
datetime(t1.realdate1, '+1 minutes') )
I guess there are parts of SQLite that work better in different usages.
Bart
From: sqlite-users-boun...@sqlite.org on behalf of Barton Torbert
Sent: Mon 9/21/2009 6:59 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Date comparisons
Richard,
What you suggested sort of worked. The problem was that the two dates I were
comparing were only 1 minute apart, but I had to put in '-1000 minutes' and
'+1000 minutes' to get the test to work.
My two data values are;
2009-04-13T12:19:00.000
and
2009-04-13T12:18:00.000
Bart
From: sqlite-users-boun...@sqlite.org on behalf of D. Richard Hipp
Sent: Mon 9/21/2009 6:17 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Date comparisons
On Sep 21, 2009, at 8:00 PM, Barton Torbert wrote:
> My data are dates in the format;
> -MM-DD HH:MM:SS.SSS
>
> I am trying to use the function strftime () to convert the data to
> seconds since 1970-01-01. I was hoping that within a WHERE clause
> or the ON condition in an OUTER JOIN.
>
> What I wanted to do was something like this;
>
> SELECT t1.dateitem1,
> t2.dateitem2
> FROM table1 t1 LEFT OUTER JOIN table2 t2 ON
> strftime ('%s', t1.dateitem1) BETWEEN strftime
> (t2.dateitem2) - 600 AND
> strftime (t2.dateitem2) + 600
>
> Where columns dateitem1 and dateitem 2 are both declared as DateTime
> fields.
>
> Can this ( or something like it ) work?
You left out the '%s' on the second two strftime() calls
I think the following will likely run faster if you have an index on
table2.dateitem2:
SELECT ... FROM table1 AS t1 LEFT JOIN table2 AS t2
WHERE t2.dateitem2 BETWEEN datetime(t1.dateitem1, '-10 minutes') AND
datetime(t1.dateitem1,'+10 minutes');
D. Richard Hipp
d...@hwaci.com
___
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] Date comparisons
Richard,
What you suggested sort of worked. The problem was that the two dates I were
comparing were only 1 minute apart, but I had to put in '-1000 minutes' and
'+1000 minutes' to get the test to work.
My two data values are;
2009-04-13T12:19:00.000
and
2009-04-13T12:18:00.000
Bart
From: sqlite-users-boun...@sqlite.org on behalf of D. Richard Hipp
Sent: Mon 9/21/2009 6:17 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Date comparisons
On Sep 21, 2009, at 8:00 PM, Barton Torbert wrote:
> My data are dates in the format;
> -MM-DD HH:MM:SS.SSS
>
> I am trying to use the function strftime () to convert the data to
> seconds since 1970-01-01. I was hoping that within a WHERE clause
> or the ON condition in an OUTER JOIN.
>
> What I wanted to do was something like this;
>
> SELECT t1.dateitem1,
> t2.dateitem2
> FROM table1 t1 LEFT OUTER JOIN table2 t2 ON
> strftime ('%s', t1.dateitem1) BETWEEN strftime
> (t2.dateitem2) - 600 AND
> strftime (t2.dateitem2) + 600
>
> Where columns dateitem1 and dateitem 2 are both declared as DateTime
> fields.
>
> Can this ( or something like it ) work?
You left out the '%s' on the second two strftime() calls
I think the following will likely run faster if you have an index on
table2.dateitem2:
SELECT ... FROM table1 AS t1 LEFT JOIN table2 AS t2
WHERE t2.dateitem2 BETWEEN datetime(t1.dateitem1, '-10 minutes') AND
datetime(t1.dateitem1,'+10 minutes');
D. Richard Hipp
d...@hwaci.com
___
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] Date comparisons
On Sep 21, 2009, at 8:00 PM, Barton Torbert wrote:
> My data are dates in the format;
> -MM-DD HH:MM:SS.SSS
>
> I am trying to use the function strftime () to convert the data to
> seconds since 1970-01-01. I was hoping that within a WHERE clause
> or the ON condition in an OUTER JOIN.
>
> What I wanted to do was something like this;
>
> SELECT t1.dateitem1,
> t2.dateitem2
> FROM table1 t1 LEFT OUTER JOIN table2 t2 ON
> strftime ('%s', t1.dateitem1) BETWEEN strftime
> (t2.dateitem2) - 600 AND
> strftime (t2.dateitem2) + 600
>
> Where columns dateitem1 and dateitem 2 are both declared as DateTime
> fields.
>
> Can this ( or something like it ) work?
You left out the '%s' on the second two strftime() calls
I think the following will likely run faster if you have an index on
table2.dateitem2:
SELECT ... FROM table1 AS t1 LEFT JOIN table2 AS t2
WHERE t2.dateitem2 BETWEEN datetime(t1.dateitem1, '-10 minutes') AND
datetime(t1.dateitem1,'+10 minutes');
D. Richard Hipp
d...@hwaci.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite?: Stored procedures
On 20/09/2009, at 6:57 AM, Simon Slavin wrote: > > Ah. Okay, so in SQLite3 you can emulate stored procedures using > triggers. Just define a trigger to operate on something that > doesn't matter to you. For instance inserting a record in a table > that you never bother reading. Every so often you delete all rows > in the table just to keep it from taking up pointless space. Unfortunately triggers can't fill the need of stored procedures. A trigger can only perform certain SQL tasks, specifically update, insert, delete, select raise(error). For full procedures, we need the full SQL syntax, such as create temp table, pragma, create temp index, attach etc. Currently, I store procedures as text in a "Procedures" table in my database. But to execute them, I have to use SQL to copy out the text to my code, reinject it into SQL, pull out any results back into my code, potentially reinject into another SQL statement etc. Proper procedures would facilitate one call to SQLite, syntax checking of the procedure, precompiled optimizations etc. Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite?: Access to database structure
I would like to see some more access to the database structure via SQL calls. For instance, we can currently get the list of tables, views, indexes and/or triggers via queries to SQLite_Master. For instance, to get the name and SQL of all triggers that are initiated by MyTable, we can query: select Name, SQL from SQLite_Master were Tbl_Name = 'MyTable' and Type = 'trigger'; And we can get the name and declared type of all columns of a table or view via: pragma table_info(MyTable); But that's about all we can do. I'd like to see similar SQL syntax available to get properties such as: 1. The constraints of a table. 2. The default value, and constraints of a table. Perhaps as an extension of the current pragma table_info() function, but preferably as a select query on a dynamic table (perhaps SQLite_Tables) so that we can feed the output into a larger query within SQL. 3. The members of a constraint, one row for each. 4. Improve the declared_type result of a column to show a declared type for an expression in a view. The declared_type C call and the pragma table_info() SQL call currently return the type of a column in a view if the column is simply referring to a column in a table. But if the column in the view is an expression, it returns null. I would like to see it return the declared type of the outer function of the expression, since most functions have a known result type. For instance, if a column in a view is defined as cast(mycomplexfunction() as integer), then the declared type is known to be an integer. If the function is abs(), the type is real. If the function is length(), the type is integer. Obviously a few functions such as coalesce() have varied types so they would still have to show a declared_type of null, but the cast() function gives the SQL author the chance to explicitly declare the type of such a column. 5. The components of a create view select statement, that would provide access to the parsed parameters: distinct/all, column expressions and names, from, where, group by, having, order by, etc. 6. Parsing of triggers into before/after/instead of, delete/insert/ update/update of, when, steps etc. I have written routines to do all this in my own code, but it would be far more accessible, especially for new SQLite programmers, to have access to this via SQL. It also seems to me that the SQLite code must already be parsing out these parameters internally so it would be relatively simple to make them available, and far more consistent than us re-inventing the wheel. Thanks, Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Date comparisons
My data are dates in the format;
-MM-DD HH:MM:SS.SSS
I am trying to use the function strftime () to convert the data to seconds
since 1970-01-01. I was hoping that within a WHERE clause or the ON condition
in an OUTER JOIN.
What I wanted to do was something like this;
SELECT t1.dateitem1,
t2.dateitem2
FROM table1 t1 LEFT OUTER JOIN table2 t2 ON
strftime ('%s', t1.dateitem1) BETWEEN strftime (t2.dateitem2) - 600
AND
strftime (t2.dateitem2) + 600
Where columns dateitem1 and dateitem 2 are both declared as DateTime fields.
Can this ( or something like it ) work?
Bart
From: sqlite-users-boun...@sqlite.org on behalf of Simon Davies
Sent: Mon 9/21/2009 4:21 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Date comparisons
2009/9/21 Barton Torbert :
> Hello,
>
> I am having trouble doing a rather odd data comparison.
>
> I have two table, each with a DateTime field. The timestamps in these fields
> do not match exactly. I want to find the row in the second table that is
> within a specific time period around the time period to the DateTime in the
> first table.
Note that SQLite has no native DateTime type. A DateTime could be a
text string, or a real Julian day. What is your data?
>
> I have tried various combinations of reformatting the data to do a
> comparison. None of these works. In particular I had hoped that the
> strftime function (converting using the '%s' format to seconds since
> 1970-01-01 would work but it does not. The comparison with = is okay, but I
> need to use some combination of < and > or a BETWEEN. Nothing seems to work.
I can't tell what is wrong because you have provided no examples.
>
> Does anybody have a suggestion?
(Re)read http://www.sqlite.org/lang_datefunc.html
Provide more information on what you are doing...
>
> Bart
>
Regards,
Simon
___
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 database on a certain high-performance "SSD"
On Mon, Sep 21, 2009 at 1:53 PM, Mark wrote: > I've currently got a loaner high-performance flash-based "SSD" (let's > just say it doesn't connect to any disk controllers) that I'm testing > for performance. I've run my application against it, and I believe that > I should see numbers MUCH higher than I do. When I run my test app on a > normal SATA 7200 RPM disk, I get a certain performance, and on the "SSD" > I get about 1/10th that speed. On an array of SAS disks I get numbers > that are about 5x faster than my SATA disk, so my software itself isn't > (I believe) the bottleneck. > > I'm wondering if anyone has any tips for "optimizing" for this sort of > storage solution. > > Also, if anyone has any quick-and-dirty test setups they'd like me to > run through on this rig, just let me know :) > SSDs get their speed by striping together multiple slower NAND controllers. If you're getting 1/10th the speed, it seems plausible that you are only accessing a single stripe at once. Optimize your app to spread access across multiple stripes. Using a larger page size and/or performing concurrent reads might help. And of course, make sure you're using transactions to group operations. -- Cory Nelson http://int64.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
On 21 Sep 2009, at 9:36pm, Fred Williams wrote: > would SQLite not work as the "back end" > for a client server (wrapper) implementation that did the multi > process (or > whatever) lock management up one level so to speak? Great question. I don't know. It requires either in-depth knowledge of how SQLite works, or specialised understanding of how such database engines work and the common faults with them. I don't have either. Don't forget: if you're going to use SQLite as a back end system you can implement transactions-across-TCP/IP and many of those other things people want too. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite database on a certain high-performance "SSD"
Hi Mark I've had a little experience working with flash-based filesystems - I'd recommend playing with the page_size and temp_store PRAGMAs (and of course make sure you are using transactions to minimise the number of file writes) to improve performance. Cheers, Dave. -Original Message- From: Mark [mailto:godef...@gmail.com] Sent: 21 September 2009 13:54 To: sqlite-users@sqlite.org Subject: [sqlite] SQLite database on a certain high-performance "SSD" I've currently got a loaner high-performance flash-based "SSD" (let's just say it doesn't connect to any disk controllers) that I'm testing for performance. I've run my application against it, and I believe that I should see numbers MUCH higher than I do. When I run my test app on a normal SATA 7200 RPM disk, I get a certain performance, and on the "SSD" I get about 1/10th that speed. On an array of SAS disks I get numbers that are about 5x faster than my SATA disk, so my software itself isn't (I believe) the bottleneck. I'm wondering if anyone has any tips for "optimizing" for this sort of storage solution. Also, if anyone has any quick-and-dirty test setups they'd like me to run through on this rig, just let me know :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why are allowed to keep a text of 3 characters in a field that has been set to 2?
Ok.
Simon and P Kishor.
Thank very much for your response
BestRegards
GVS
Simon Davies escribió:
> 2009/9/21 Guillermo Varona Silupú :
>
>> Hi
>> In these SQL commands:
>>
>> CREATE TABLE "test" ("code" char(2));
>> INSERT INTO test (code) VALUES("123")
>>
>> Why are allowed to keep a text of 3 characters in a field that has been
>> set to 2?
>> Is a bug?
>>
>
> No - I suggest that you have a look at http://www.sqlite.org/datatype3.html
>
>
>> TIA
>> BestRegards
>> GVS
>>
>>
>
> Regards,
> Simon
> ___
> 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] Date comparisons
2009/9/21 Barton Torbert : > Hello, > > I am having trouble doing a rather odd data comparison. > > I have two table, each with a DateTime field. The timestamps in these fields > do not match exactly. I want to find the row in the second table that is > within a specific time period around the time period to the DateTime in the > first table. Note that SQLite has no native DateTime type. A DateTime could be a text string, or a real Julian day. What is your data? > > I have tried various combinations of reformatting the data to do a > comparison. None of these works. In particular I had hoped that the > strftime function (converting using the '%s' format to seconds since > 1970-01-01 would work but it does not. The comparison with = is okay, but I > need to use some combination of < and > or a BETWEEN. Nothing seems to work. I can't tell what is wrong because you have provided no examples. > > Does anybody have a suggestion? (Re)read http://www.sqlite.org/lang_datefunc.html Provide more information on what you are doing... > > Bart > Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why are allowed to keep a text of 3 characters in a field that has been set to 2?
On Mon, Sep 21, 2009 at 5:10 PM, Guillermo Varona Silupú
wrote:
> Hi
> In these SQL commands:
>
> CREATE TABLE "test" ("code" char(2));
> INSERT INTO test (code) VALUES("123")
>
> Why are allowed to keep a text of 3 characters in a field that has been
> set to 2?
> Is a bug?
nope. It is by design. Read up on datatypes in sqlite. If you are
concerned about what goes where, you have to put the checks in your
application.
>
> TIA
> BestRegards
> GVS
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Madison, WI, United States
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why are allowed to keep a text of 3 characters in a field that has been set to 2?
2009/9/21 Guillermo Varona Silupú :
> Hi
> In these SQL commands:
>
> CREATE TABLE "test" ("code" char(2));
> INSERT INTO test (code) VALUES("123")
>
> Why are allowed to keep a text of 3 characters in a field that has been
> set to 2?
> Is a bug?
No - I suggest that you have a look at http://www.sqlite.org/datatype3.html
>
> TIA
> BestRegards
> GVS
>
Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Why are allowed to keep a text of 3 characters in a field that has been set to 2?
Hi
In these SQL commands:
CREATE TABLE "test" ("code" char(2));
INSERT INTO test (code) VALUES("123")
Why are allowed to keep a text of 3 characters in a field that has been
set to 2?
Is a bug?
TIA
BestRegards
GVS
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Date comparisons
Hello, I am having trouble doing a rather odd data comparison. I have two table, each with a DateTime field. The timestamps in these fields do not match exactly. I want to find the row in the second table that is within a specific time period around the time period to the DateTime in the first table. I have tried various combinations of reformatting the data to do a comparison. None of these works. In particular I had hoped that the strftime function (converting using the '%s' format to seconds since 1970-01-01 would work but it does not. The comparison with = is okay, but I need to use some combination of < and > or a BETWEEN. Nothing seems to work. Does anybody have a suggestion? Bart ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Simon Slavin wrote: > >> I definitely don't agree here as we're talking about these additional >> locks existing _only_ in memory, not on disk. > > Which requires client/server architecture. Which SQLite3 doesn't > have. Once you require concurrent access features in your DBMS (i.e. > multi-user, lots of locking) the things you nned to implement start to > be easier with a client/server architecture, whether it's a standalone > client application that must be launched manually or just a unix-style > daemon running in the background which is launched automatically when > needed and quits when nothing has used it in a while. Uhh, no it doesn't. Unless your definition of client/server is completely different than mine in the context of what we're talking about here. In this context, the 'client and server' would share the same address space (they're the same process!), hence there is no client/server separation. It would mean the exact same amount of process separation as SQLite currently employs. It would just have additional code to optimize for concurrent writes by multiple threads just as sqlite3_enable_shared_cache() does for reads: http://sqlite.org/c3ref/enable_shared_cache.html http://sqlite.org/sharedcache.html What I'm suggesting would be an extension of that shared cache, but for managing access for writes. We're just talking finer-grained locks here ... we're not talking some elaborate scheme which requires IPC and client/server communication. It doesn't need to spawn off any other daemon process here, that would be just plain stupid to do within the context of what I'm talking about. I'm just wondering if you're confused on the difference between a thread and a process One of these days I just need to write a patch to do this and see if it gets accepted. Unfortunately, my spare time these days is around zilch. -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite database on a certain high-performance "SSD"
I've currently got a loaner high-performance flash-based "SSD" (let's just say it doesn't connect to any disk controllers) that I'm testing for performance. I've run my application against it, and I believe that I should see numbers MUCH higher than I do. When I run my test app on a normal SATA 7200 RPM disk, I get a certain performance, and on the "SSD" I get about 1/10th that speed. On an array of SAS disks I get numbers that are about 5x faster than my SATA disk, so my software itself isn't (I believe) the bottleneck. I'm wondering if anyone has any tips for "optimizing" for this sort of storage solution. Also, if anyone has any quick-and-dirty test setups they'd like me to run through on this rig, just let me know :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]on Behalf Of Simon Slavin Sent: Monday, September 21, 2009 3:05 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Most wanted features of SQLite ? On 21 Sep 2009, at 6:53pm, Brad House wrote: > I definitely don't agree here as we're talking about these additional > locks existing _only_ in memory, not on disk. Which requires client/server architecture. Which SQLite3 doesn't have. Once you require concurrent access features in your DBMS (i.e. multi-user, lots of locking) the things you nned to implement start to be easier with a client/server architecture, whether it's a standalone client application that must be launched manually or just a unix-style daemon running in the background which is launched automatically when needed and quits when nothing has used it in a while. On 21 Sep 2009, at 6:44pm, Pavel Ivanov wrote: > Interesting point, Simon. Are you saying that all developers of big > database engines that implemented row-level locks are just idiots > because there's no benefit from it at all? They had to implement just > database-level locks and all users would be a lot happier because > they'd received a significant performance boost? Nope. They chose to implement a big database engine and that meant they chose client/server architecture, which makes locking (and all other things that require centralised control) less difficult. It's just that this is not how SQLite works. Simon. With "optional" fine grained locking would SQLite not work as the "back end" for a client server (wrapper) implementation that did the multi process (or whatever) lock management up one level so to speak? Sybase bought a small DB company a while back. The company was known as "Advantage DB" prior to the acquisition. They, and Sybase still does I believe, offered a "Local" and "Server" implementation of their DB. That is what I'm really thinking of here, with SQLite.dll used as the "Local" and SQLite.dll + as the "Server." Fact is Advantage was what I was using for my "Local" DB implementations before discovering SQLite. Fred ___ 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] Most wanted features of SQLite ?
On 21 Sep 2009, at 6:53pm, Brad House wrote: > I definitely don't agree here as we're talking about these additional > locks existing _only_ in memory, not on disk. Which requires client/server architecture. Which SQLite3 doesn't have. Once you require concurrent access features in your DBMS (i.e. multi-user, lots of locking) the things you nned to implement start to be easier with a client/server architecture, whether it's a standalone client application that must be launched manually or just a unix-style daemon running in the background which is launched automatically when needed and quits when nothing has used it in a while. On 21 Sep 2009, at 6:44pm, Pavel Ivanov wrote: > Interesting point, Simon. Are you saying that all developers of big > database engines that implemented row-level locks are just idiots > because there's no benefit from it at all? They had to implement just > database-level locks and all users would be a lot happier because > they'd received a significant performance boost? Nope. They chose to implement a big database engine and that meant they chose client/server architecture, which makes locking (and all other things that require centralised control) less difficult. It's just that this is not how SQLite works. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Faster inserts in SQlite ...
On Mon, Sep 21, 2009 at 03:37:02PM -0400, Pavel Ivanov wrote: > > Have you any IO operations? As result you have dependence of page > > size. > > Though your performance most probably will not depend on these > operations because they will be executed at some random times by OS. > And they will be collected to have multiple blocks in one operation > anyway... > I don't have good knowledge of how disk cache works in kernel to say > if it will be beneficiary to send data there in chunks equal to blocks > on disk as opposed to chunks of any arbitrary size... It is well-known that matching DB page size and filesystem record size is important for improving performance. SQLite3 supports power-of-2 page sizes from 512 to 32KB. Most filesystems use 512, 4096 or 8192 byte blocks. SQLite3's default pagesize is 1024 bytes. In the case of ZFS the fs recordsize is variable up to 128KB, and can be tuned per-dataset. You should set the recordsize to 32KB for ZFS datasets containing SQLite3 DBs, and you should set the SQLite3 pagesize to 32KB. Nico -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Not really... Natural joins require that the column names be equivalent on each table. In my current database schema, the common practice is to use as the column name of the FK. (i.e. "CarType" to reference the "Type" column of a "Cars" table) But yea, what I'm looking for is a form of NATURAL JOIN just..more natural :) -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: Monday, September 21, 2009 3:25 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Most wanted features of SQLite ? Shaun Seckman (Firaxis) wrote: > * I really would like some SQL syntax sugar that will > automatically include rows referenced by foreign keys in a table. I > haven't seen any other SQL database do this as all expect you to > explicitly include the conditions and joins which can become quite the > hassle. Does NATURAL JOIN help? > * My second awesome feature request would be support for storing > prepared statements into the database file to be used at later times. http://www.mail-archive.com/sqlite-users@sqlite.org/msg30073.html Igor Tandetnik ___ 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] Faster inserts in SQlite ...
> Have you any IO operations? As result you have dependence of page > size. Though your performance most probably will not depend on these operations because they will be executed at some random times by OS. And they will be collected to have multiple blocks in one operation anyway... I don't have good knowledge of how disk cache works in kernel to say if it will be beneficiary to send data there in chunks equal to blocks on disk as opposed to chunks of any arbitrary size... Pavel On Mon, Sep 21, 2009 at 3:29 PM, Alexey Pechnikov wrote: > Hello! > > On Monday 21 September 2009 23:11:57 Pavel Ivanov wrote: >> > Most modern FS have the same block size. >> >> Though I don't think that in case of synchronous = OFF there's any >> benefit of using pages of the exactly same size as block in file >> system. Correct me if I'm wrong. > > Have you any IO operations? As result you have dependence of page > size. > > Best regards, Alexey Pechnikov. > http://pechnikov.tel/ > ___ > 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] Faster inserts in SQlite ...
Hello! On Monday 21 September 2009 23:11:57 Pavel Ivanov wrote: > > Most modern FS have the same block size. > > Though I don't think that in case of synchronous = OFF there's any > benefit of using pages of the exactly same size as block in file > system. Correct me if I'm wrong. Have you any IO operations? As result you have dependence of page size. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Shaun Seckman (Firaxis) wrote: > * I really would like some SQL syntax sugar that will > automatically include rows referenced by foreign keys in a table. I > haven't seen any other SQL database do this as all expect you to > explicitly include the conditions and joins which can become quite the > hassle. Does NATURAL JOIN help? > * My second awesome feature request would be support for storing > prepared statements into the database file to be used at later times. http://www.mail-archive.com/sqlite-users@sqlite.org/msg30073.html Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Here are my most wanted features and they don't even require locking or threading fu ;) * I really would like some SQL syntax sugar that will automatically include rows referenced by foreign keys in a table. I haven't seen any other SQL database do this as all expect you to explicitly include the conditions and joins which can become quite the hassle. * My second awesome feature request would be support for storing prepared statements into the database file to be used at later times. It's been documented that the creation of statements is a performance hit so it would be nice if my application doesn't have to recreate these statements every time it's executed. This would also cut down on the amount of hard-coded SQL I have scattered throughout my C++ application as I could instead use some sort of preprocessing tool to generate the statements and merely reference those statements by name or some other identifier in my code. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Brad House Sent: Monday, September 21, 2009 1:53 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Most wanted features of SQLite ? >> It could probably benefit a large number of integrations to >> have finer grained locking even if it could not be implemented for all >> integration types. > > It makes the system a great deal slower, since you need to keep > checking all the levels of lock you have implemented. For instance, > > UPDATE props SET colour='black',condition='poor' WHERE > description='fake sword' > > needs to check for locks on the file, the record, and three fields. > That's five operations before you can even start to modify the data. > Could easily double the amount of time it takes to perform the > update. And if you implement column locks there are even more. And > implementing fine-grain locks leads to lock-contention: if someone > locks a record and you try to lock a field in that record, what should > happen ? Now before trying to modify data and having locks interfere, > you're trying to modify locks and having lock-interaction interfere. I definitely don't agree here as we're talking about these additional locks existing _only_ in memory, not on disk. There'd be no reason to implement on-disk locking or even notifying the OS of sections of the file which are locked since we're only talking about multiple threads in the same process. Any other process would hit the OS file lock and be forced to wait. The overhead of in-memory locking going to be extremely minimal, and only affect those who specifically enable this fine-grained locking. That said, I do think the on-disk journal file format might need to change to accomplish even this, and I think that is probably the biggest show stopper. I'm not suggesting that this would be easy to implement either, and yes, you'd need to figure out if SQLite will block on a lock, or return BUSY, but by limiting the implementation scope to multithreaded applications, it at least makes the implementation feasible, and would provide great benefit to many users of SQLite. In our own synthetic benchmark of our application, which is extremely write-heavy, we see roughly 15 txns/sec with SQLite, but 1000 txns/sec with multiple connections to MySQL. Biggest difference here is MySQL allows multiple writers. (That said, I need to actually try to benchmark MySQL with only 1 connection to be able to normalize those numbers a bit). Typically though, those with large transaction volumes are going to go to some other database besides SQLite for other features of a server-based engine, like replication. Not complaining here though, SQLite definitely fits the bill for the default database of our application. What it does, it does well! -Brad ___ 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] Faster inserts in SQlite ...
> Most modern FS have the same block size. Though I don't think that in case of synchronous = OFF there's any benefit of using pages of the exactly same size as block in file system. Correct me if I'm wrong. Pavel On Mon, Sep 21, 2009 at 2:53 PM, Alexey Pechnikov wrote: > Hello! > > On Monday 21 September 2009 19:50:37 Pavel Ivanov wrote: >> > You must set pragma page_size=4096; >> >> Can I ask why "must" and this number? Why not the maximum 32768? > > $ sudo tune2fs -l /dev/sdb1|grep 4096 > Block size: 4096 > > Most modern FS have the same block size. > > Best regards, Alexey Pechnikov. > http://pechnikov.tel/ > ___ > 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] Faster inserts in SQlite ...
Hello! On Monday 21 September 2009 19:50:37 Pavel Ivanov wrote: > > You must set pragma page_size=4096; > > Can I ask why "must" and this number? Why not the maximum 32768? $ sudo tune2fs -l /dev/sdb1|grep 4096 Block size: 4096 Most modern FS have the same block size. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
>> It could probably benefit a large number of integrations to >> have finer grained locking even if it could not be implemented for all >> integration types. > > It makes the system a great deal slower, since you need to keep > checking all the levels of lock you have implemented. For instance, > > UPDATE props SET colour='black',condition='poor' WHERE > description='fake sword' > > needs to check for locks on the file, the record, and three fields. > That's five operations before you can even start to modify the data. > Could easily double the amount of time it takes to perform the > update. And if you implement column locks there are even more. And > implementing fine-grain locks leads to lock-contention: if someone > locks a record and you try to lock a field in that record, what should > happen ? Now before trying to modify data and having locks interfere, > you're trying to modify locks and having lock-interaction interfere. I definitely don't agree here as we're talking about these additional locks existing _only_ in memory, not on disk. There'd be no reason to implement on-disk locking or even notifying the OS of sections of the file which are locked since we're only talking about multiple threads in the same process. Any other process would hit the OS file lock and be forced to wait. The overhead of in-memory locking going to be extremely minimal, and only affect those who specifically enable this fine-grained locking. That said, I do think the on-disk journal file format might need to change to accomplish even this, and I think that is probably the biggest show stopper. I'm not suggesting that this would be easy to implement either, and yes, you'd need to figure out if SQLite will block on a lock, or return BUSY, but by limiting the implementation scope to multithreaded applications, it at least makes the implementation feasible, and would provide great benefit to many users of SQLite. In our own synthetic benchmark of our application, which is extremely write-heavy, we see roughly 15 txns/sec with SQLite, but 1000 txns/sec with multiple connections to MySQL. Biggest difference here is MySQL allows multiple writers. (That said, I need to actually try to benchmark MySQL with only 1 connection to be able to normalize those numbers a bit). Typically though, those with large transaction volumes are going to go to some other database besides SQLite for other features of a server-based engine, like replication. Not complaining here though, SQLite definitely fits the bill for the default database of our application. What it does, it does well! -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Interesting point, Simon. Are you saying that all developers of big database engines that implemented row-level locks are just idiots because there's no benefit from it at all? They had to implement just database-level locks and all users would be a lot happier because they'd received a significant performance boost? Pavel On Mon, Sep 21, 2009 at 1:23 PM, Simon Slavin wrote: > > On 21 Sep 2009, at 5:38pm, Brad House wrote: > >> It could probably benefit a large number of integrations to >> have finer grained locking even if it could not be implemented for all >> integration types. > > It makes the system a great deal slower, since you need to keep > checking all the levels of lock you have implemented. For instance, > > UPDATE props SET colour='black',condition='poor' WHERE > description='fake sword' > > needs to check for locks on the file, the record, and three fields. > That's five operations before you can even start to modify the data. > Could easily double the amount of time it takes to perform the > update. And if you implement column locks there are even more. And > implementing fine-grain locks leads to lock-contention: if someone > locks a record and you try to lock a field in that record, what should > happen ? Now before trying to modify data and having locks interfere, > you're trying to modify locks and having lock-interaction interfere. > > Simon. > ___ > 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] List of active savepoints names
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Lukas Gebauer wrote:
> I am not creating and releasing savepoints!
Yes you are. The SQLite library is not magically doing them on a whim.
Your code is asking for them.
>> You can create a custom wrapper for savepoints which may have
>> the stack of calls.
>
> Why I must duplicate code what is inside Sqlite too?
There is no code inside SQLite that returns the list of savepoints so there
is nothing to duplicate. There is a data structure of savepoints.
Adding this code and API seems simple enough and superficially it is.
However once added it cannot be removed (dynamic linking would break), has
to be documented and tested, and adds some bloat to SQLite. It would also
constrain future changes to underlying data structures. For example if the
public API returns a flat list, it would complicate things if the internal
data structure would be better off as a tree.
Although it may not seem like it, one of the most important jobs of project
maintainers is saying "no". Here is Linus Torvalds on the subject:
http://linuxmafia.com/faq/Kernel/linus-im-a-bastard-speech.html
> Sqlite have savepoint stack inside (AFAIK). I really not know why is
> a problem to list this stack through some API. (But maybe it really
> is a problem, I don't know, but nobody tell this yet...)
This code will print out the names to stderr. You should be able to do the
equivalent from your debugger.
void print_savepoints(sqlite3 *db)
{
int i;
for(i=0;inSavepoint;i++)
fprintf(stderr, "%s\n", db->pSavepoint[i]->zName);
}
They are also in a tree like structure so it isn't quite that simple.
> Yes, I can create my own wrapper, what can work with my code only,
It is your code that needs debugging :-)
> Just because I must duplicate
> lot of code for decide if savepoint is or not is created,
If you have random code generating random SQL then it is harder, but that is
a bad design anyway. Generally you'll have a function that when called
generates savepoints and another for rolling them back. But even with
random code, the authorizer interface can tell you when savepoint SQL is
prepared.
> And now compare it with simple API funtion what just list existing
> savepoint stack inside Sqlite. It is simple, trivial, useful... and
> working right at any case.
And constrains data structures, requires documentation and testing, can
never be removed and adds bloat. And in most cases still isn't useful by
itself - you would still need to add yet another wrapper around that
function depending on what you want to happen (print it indented as a tree,
convert to utf16 or ascii, combine names separated by commas, stdout or
stderr, gui dialog etc).
> This is why I am asking for. Thanks!
A simple rule of thumb is to look at how useful features would be to other
SQLite users. Note that you are the only one to have asked for this feature
and not one other person has agreed with you on its need! The responses
have generally been along the lines of suggesting you organize your code so
that you do not need it. But even if it was added as a debugging aid, do
you expect people to compile up two versions of SQLite - one with debugging
aids on and another with them off? This feature would impose decisions like
that on others in addition to the future maintenance costs.
Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAkq3uvIACgkQmOOfHg372QQI/QCcDDg5dthEmqBJj+FZL5NhatN2
JusAoMSHlyXRJsLHMhCF+S3vQgKFUX7E
=+RxM
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
On 21 Sep 2009, at 5:38pm, Brad House wrote: > It could probably benefit a large number of integrations to > have finer grained locking even if it could not be implemented for all > integration types. It makes the system a great deal slower, since you need to keep checking all the levels of lock you have implemented. For instance, UPDATE props SET colour='black',condition='poor' WHERE description='fake sword' needs to check for locks on the file, the record, and three fields. That's five operations before you can even start to modify the data. Could easily double the amount of time it takes to perform the update. And if you implement column locks there are even more. And implementing fine-grain locks leads to lock-contention: if someone locks a record and you try to lock a field in that record, what should happen ? Now before trying to modify data and having locks interfere, you're trying to modify locks and having lock-interaction interfere. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Prepare Error from sqlite
Thank you, that fixed the problem. Kavita - Original Message - From: "Pavel Ivanov" To: "General Discussion of SQLite Database" Sent: Monday, September 21, 2009 11:33:35 AM GMT -06:00 US/Canada Central Subject: Re: [sqlite] Prepare Error from sqlite > "CREATE TABLE EntityTbl (ID INTEGER PRIMARY KEY AUTOINCREMENT,AttrName > VARCHAR(50) COLLATE NOCASE,AttrEnum INTEGERAttrType INTEGER,AttrValue > VARCHAR(128) COLLATE NOCASE,ReadWrite VARCHAR(10),Entity_id INTEGER" In this sql string you're missing closing parenthesis at the end and apparently you also miss comma before AttrType. Pavel On Mon, Sep 21, 2009 at 12:22 PM, Kavita Raghunathan wrote: > > I just put in my sqlite wrappers and started testing. > > I get the following error and when I stepped into the sqlite code, its when > sqlite3VdbeFinalize is called: > "prepare error: near ",": syntax error" > > > I was trying to do a "sqlite3_prepare_v2" and was using the following > sqlstring and the following wrapper > "CREATE TABLE EntityTbl (ID INTEGER PRIMARY KEY AUTOINCREMENT,AttrName > VARCHAR(50) COLLATE NOCASE,AttrEnum INTEGERAttrType INTEGER,AttrValue > VARCHAR(128) COLLATE NOCASE,ReadWrite VARCHAR(10),Entity_id INTEGER" > > > Anyway I can get a more descriptive error ? > Thanks! > Kavita > ___ > 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
Re: [sqlite] Most wanted features of SQLite ?
>> Fine for me. It seems to be everybody else that wants their favorite >> feature imbedded in the core :-) >> >> Fine grained locking would be a great "asset" I feel. Notice I did >> not >> request a "feature." > > > On my to-do list is to write a paper that explains why fine-grain > locking is not practical without either (1) a dedicated server process > to manage the locks or (2) enhancements to OS locking primitives that > are not currently available on any OS that I am aware of. There is a > widely held belief that since OSes provide byte-level locking of files > it should be a simple matter to provide row-level locking in a > serverless database engine. The proposed paper will explain why that > belief is incorrect. It could probably benefit a large number of integrations to have finer grained locking even if it could not be implemented for all integration types. I could see the implementation of a per-process lock (full DB lock), but with finer-grained row-level locking on a per-thread basis within the same application. Probably enabled through the use of sqlite3_enable_shared_cache(). This would actually be of great benefit to our use of SQLite which is strictly from a single multi-threaded process, no other application or process would touch the database simultaneously, just multiple threads from a single process would interact through multiple 'connections'. -Brad ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Prepare Error from sqlite
> "CREATE TABLE EntityTbl (ID INTEGER PRIMARY KEY AUTOINCREMENT,AttrName > VARCHAR(50) COLLATE NOCASE,AttrEnum INTEGERAttrType INTEGER,AttrValue > VARCHAR(128) COLLATE NOCASE,ReadWrite VARCHAR(10),Entity_id INTEGER" In this sql string you're missing closing parenthesis at the end and apparently you also miss comma before AttrType. Pavel On Mon, Sep 21, 2009 at 12:22 PM, Kavita Raghunathan wrote: > > I just put in my sqlite wrappers and started testing. > > I get the following error and when I stepped into the sqlite code, its when > sqlite3VdbeFinalize is called: > "prepare error: near ",": syntax error" > > > I was trying to do a "sqlite3_prepare_v2" and was using the following > sqlstring and the following wrapper > "CREATE TABLE EntityTbl (ID INTEGER PRIMARY KEY AUTOINCREMENT,AttrName > VARCHAR(50) COLLATE NOCASE,AttrEnum INTEGERAttrType INTEGER,AttrValue > VARCHAR(128) COLLATE NOCASE,ReadWrite VARCHAR(10),Entity_id INTEGER" > > > Anyway I can get a more descriptive error ? > Thanks! > Kavita > ___ > 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] Index usage
Matthew, Regarding: "There's no way to optimize your query to be fast in both situations." I do *not* know if this would be of any help, but the newest 3.1.18 sqlite release which includes the SQLITE_ENABLE_STAT2 feature may possibly be of interest: (and excuse me if you've mentioned this already) http://sqlite.org/compile.html#enable_stat2 SQLITE_ENABLE_STAT2 This option adds additional logic to the ANALYZE command and to the query planner that can help SQLite to chose a better query plan under certain situations. The ANALYZE command is enhanced to collect a 10-sample histogram of the data in each index and store that histogram in the sqlite_stat2 table. The query planner will then use the histogram data to help it estimate how many rows will be selected by a range constraint in a WHERE clause. Regards, Donald Griggs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Prepare Error from sqlite
Correction: The error seems to be set in sqlite3VdbeSetSql because that was the last executed statement. - Original Message - From: "Kavita Raghunathan" To: "sqlite-users" Sent: Monday, September 21, 2009 11:22:15 AM GMT -06:00 US/Canada Central Subject: [sqlite] Prepare Error from sqlite I just put in my sqlite wrappers and started testing. I get the following error and when I stepped into the sqlite code, its when sqlite3VdbeFinalize is called: "prepare error: near ",": syntax error" I was trying to do a "sqlite3_prepare_v2" and was using the following sqlstring and the following wrapper "CREATE TABLE EntityTbl (ID INTEGER PRIMARY KEY AUTOINCREMENT,AttrName VARCHAR(50) COLLATE NOCASE,AttrEnum INTEGERAttrType INTEGER,AttrValue VARCHAR(128) COLLATE NOCASE,ReadWrite VARCHAR(10),Entity_id INTEGER" Anyway I can get a more descriptive error ? Thanks! Kavita ___ 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] Prepare Error from sqlite
I just put in my sqlite wrappers and started testing. I get the following error and when I stepped into the sqlite code, its when sqlite3VdbeFinalize is called: "prepare error: near ",": syntax error" I was trying to do a "sqlite3_prepare_v2" and was using the following sqlstring and the following wrapper "CREATE TABLE EntityTbl (ID INTEGER PRIMARY KEY AUTOINCREMENT,AttrName VARCHAR(50) COLLATE NOCASE,AttrEnum INTEGERAttrType INTEGER,AttrValue VARCHAR(128) COLLATE NOCASE,ReadWrite VARCHAR(10),Entity_id INTEGER" Anyway I can get a more descriptive error ? Thanks! Kavita ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Index usage
On Mon, Sep 21, 2009 at 8:27 AM, Pavel Ivanov wrote: > > There's no way to optimize your query to be fast in both situations. > LIMIT clause is pretty hard to optimize. Maybe just to have a closer > look at the application structure - maybe it's not so necessary to do > ORDER BY or maybe LIMIT can be moved to inner query... > But for this particular case I think it's pretty reasonable to use > INDEXED BY clause despite what documentation says (it discourages > usage for common cases). > Yeah, that's what I was afraid of. :) I guess I'll end up just tracking the number of val_table entries which match each path, then totaling up the # of matching entries first to get a count of how many rows my real query is going to match. Using that, and the LIMIT BY items, I can maybe heuristically guess which indexing method will be faster for when I do the real query. Seems like a pain for this relatively simple scenario, but I can see how it'd be deceptively easy-looking to optimize. Thanks for the response -- Matthew L. Creech ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Lite date / time functions
Alexey Pechnikov
wrote:
> On Monday 21 September 2009 15:59:47 Igor Tandetnik wrote:
>>> Please speak is now correct to store 'start of month' dates in
>>> julianday format?
>>
>> Was it at some point "incorrect" to do so? Yes, you can store any
>> date in julian day format, start of month or otherwise.
>
> Are correct selects like as
> SELECT * from tbl_name where date = julianday('now','start of month');
I see no reason why not. Note that the condition will only hold when
"date" column represents midnight on the corresponding day.
> Equal condition for dates is work now but is it safety?
In general, comparing double values for exact equality is risky.
However, fractions that are powers of two are represented exactly in a
double, so the comparison would work for values that represent, for
example, 3, 6, 9 and 12 o'clock (both AM and PM).
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Faster inserts in SQlite ...
> You must set pragma page_size=4096; Can I ask why "must" and this number? Why not the maximum 32768? Pavel On Mon, Sep 21, 2009 at 11:45 AM, Alexey Pechnikov wrote: > Hello! > > On Monday 21 September 2009 15:05:04 Atul_Vaidya wrote: >> 5. PRAGMA cache_size = 12000; > > I use value 128 000 on servers. > > You must set pragma page_size=4096; > If database is not empty is needed to vacuum it. >> >> i also avoid the update calls, and i do all the insertion in between Begin >> Transaction and End Transaction.I was planning to call the insertion calls >> to SQLite in a separate thread.I am using the SQLite Version 3.3.13.When i >> dig in through documentation, the latest version was 3.3.18.FAQ says that >> the SQlite is Thread safe. Please suggest me if I should work in this >> direction. > > Last version is 3.6.18 and is more better to use this. But your version is > thread-safe too. > > Best regards, Alexey Pechnikov. > http://pechnikov.tel/ > ___ > 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] Faster inserts in SQlite ...
Hello! On Monday 21 September 2009 15:05:04 Atul_Vaidya wrote: > 5. PRAGMA cache_size = 12000; I use value 128 000 on servers. You must set pragma page_size=4096; If database is not empty is needed to vacuum it. > > i also avoid the update calls, and i do all the insertion in between Begin > Transaction and End Transaction.I was planning to call the insertion calls > to SQLite in a separate thread.I am using the SQLite Version 3.3.13.When i > dig in through documentation, the latest version was 3.3.18.FAQ says that > the SQlite is Thread safe. Please suggest me if I should work in this > direction. Last version is 3.6.18 and is more better to use this. But your version is thread-safe too. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Lite date / time functions
Hello!
On Monday 21 September 2009 15:59:47 Igor Tandetnik wrote:
> > Please speak is now correct to store 'start of month' dates in
> > julianday format?
>
> Was it at some point "incorrect" to do so? Yes, you can store any date
> in julian day format, start of month or otherwise.
Are correct selects like as
SELECT * from tbl_name where date = julianday('now','start of month');
Equal condition for dates is work now but is it safety?
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Code problem - apostrophes
On Mon, Sep 21, 2009 at 9:35 AM, Jimmy Verner wrote: > I did realize a couple of points that might be helpful to others: > > 1. When you run "find and replace," it won't always pick up all the > apostrophes (or single close-quotes). Apparently, different programs > encode this symbol differently. I've encountered this problem before > when using more than one program to prepare word-processed documents. > There is no easy solution to it that I know of. At times, in formal > documents, I have been reduced to careful proofing to make sure all > the apostrophes are in the same typeface. > > 2. In the particular app I am building, search results are displayed > in html. Replacing apostrophes with ’ ( see > http://tlt.its.psu.edu/suggestions/international/web/codehtml.html#punc > ) solves the problem. Although with this particular database, I > must find and replace all the apostrophes (I'm far enough along with > this database that find-and-replace will be faster than starting > over), in the next one I will replace apostrophes with ’ while > editing in html to finesse the problem. You shouldn't even be bothering with all of the above if you are creating an application yourself, which, I am assuming you are since you are on this mailing list. You should be using bind values. > > Jimmy Verner > www.vernerlegal.com > >> >> On Sep 20, 2009, at 6:47 PM, Simon Slavin wrote: >> >>> >>> On 21 Sep 2009, at 12:30am, Jimmy Verner wrote: >>> person's >>> >>> You're using single quotes to surround the text, and there's an >>> apostrophe in the middle of the text. I make this mistake a lot with >>> PHP and it's really annoying. >>> >>> Simon. >>> ___ >>> 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 > -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Code problem - apostrophes
I did realize a couple of points that might be helpful to others: 1. When you run "find and replace," it won't always pick up all the apostrophes (or single close-quotes). Apparently, different programs encode this symbol differently. I've encountered this problem before when using more than one program to prepare word-processed documents. There is no easy solution to it that I know of. At times, in formal documents, I have been reduced to careful proofing to make sure all the apostrophes are in the same typeface. 2. In the particular app I am building, search results are displayed in html. Replacing apostrophes with ’ ( see http://tlt.its.psu.edu/suggestions/international/web/codehtml.html#punc ) solves the problem. Although with this particular database, I must find and replace all the apostrophes (I'm far enough along with this database that find-and-replace will be faster than starting over), in the next one I will replace apostrophes with ’ while editing in html to finesse the problem. Jimmy Verner www.vernerlegal.com > > On Sep 20, 2009, at 6:47 PM, Simon Slavin wrote: > >> >> On 21 Sep 2009, at 12:30am, Jimmy Verner wrote: >> >>> person's >> >> You're using single quotes to surround the text, and there's an >> apostrophe in the middle of the text. I make this mistake a lot with >> PHP and it's really annoying. >> >> Simon. >> ___ >> 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
Re: [sqlite] blocking when locking
> I see. Thanks for your explaination. > May I ask if SQLite can explicitly start a write lock transaction ? Yes. It's BEGIN IMMEDIATE or BEGIN EXCLUSIVE. The difference is that IMMEDIATE still allows readers to read until actual writing to the database file occurs (before that everything is collected in memory buffers) and EXCLUSIVE doesn't allow even readers to step in. Pavel On Mon, Sep 21, 2009 at 9:19 AM, Wenbo Zhao wrote: > 2009/9/21 Pavel Ivanov > >> > What I want to say is in this example, there should be only one step, >> > because the transaction knows it will do 'write'. >> > Then the txn should start a write lock before the select. >> > >> > And this is not a good example to explain dead lock, I think. >> >> And again, you're talking about application using SQLite. Maybe it >> knows that it will write (also not necessarily so - depends on the >> structure of application) and so it should take write lock right away. >> But maybe it wants to improve contention and in majority of cases it >> doesn't need to write after select... In any way SQLite doesn't know >> anything about application's intentions, so it can't take write lock >> before the select and dead lock is still possible. >> > I see. Thanks for your explaination. > May I ask if SQLite can explicitly start a write lock transaction ? > Sorry to bother you with such a basic question. I'm still new in SQLite. > > >> >> Pavel >> >> On Sun, Sep 20, 2009 at 2:04 AM, Wenbo Zhao wrote: >> > I was talking about this example by >> > 2009/9/19 Igor Tandetnik >> > "Imagine the >> > classic example, where a transaction first verifies that the balance in >> > a bank account is sufficient, then performs a withdrawal. If it >> > relinquishes all locks between these two steps, then somebody else may >> > record a withdrawal from that account, so that the write operation would >> > then make the balance negative, thus violating an invariant." >> > >> > What I want to say is in this example, there should be only one step, >> > because the transaction knows it will do 'write'. >> > Then the txn should start a write lock before the select. >> > >> > And this is not a good example to explain dead lock, I think. >> > >> > >> > 2009/9/19 Pavel Ivanov >> > >> >> Wenbo, are you talking about what do you want to see in DBMS or are >> >> you trying to explain how SQLite works? >> >> If the latter then you're wrong. In SQLite 'read lock' is designed for >> >> transaction that _made_ any reads, 'write lock' - for transaction that >> >> _made_ any writes. >> >> >> >> Pavel >> >> >> >> On Sat, Sep 19, 2009 at 12:18 AM, Wenbo Zhao wrote: >> >> > This is not a good example i think. >> >> > If a transaction is intent to update after the select, it should start >> >> > a write lock before the select. >> >> > And as described in previous 'dead lock' example, the update in this >> >> > example could fail due to 'dead lock' >> >> > I believe the 'read lock' is designed for a 'read only' transaction, >> >> > and the 'write lock' is for a transaction that 'may write something'. >> >> > >> >> > 2009/9/19 Igor Tandetnik >> >> > >> >> >> Angus March wrote: >> >> >> > Yes, I see. So what is key to the problem is that someone tries >> to >> >> >> > change their read lock to a write lock. I guess I just thought that >> >> >> > the kernel that manages fcntl() would have a way of dealing with >> >> >> > this. Can this situation not be averted if at step 3, transaction A >> >> >> > releases its read lock before requesting a write lock? >> >> >> >> >> >> Then it wouldn't be much of a transaction, now would it? Imagine the >> >> >> classic example, where a transaction first verifies that the balance >> in >> >> >> a bank account is sufficient, then performs a withdrawal. If it >> >> >> relinquishes all locks between these two steps, then somebody else >> may >> >> >> record a withdrawal from that account, so that the write operation >> would >> >> >> then make the balance negative, thus violating an invariant. >> >> >> >> >> >> Of course, if that's what the application wants, it can simply >> perform >> >> >> the read and the write operations in two separate transactions. >> >> >> >> >> >> Igor Tandetnik >> >> >> >> >> >> >> >> >> >> >> >> ___ >> >> >> sqlite-users mailing list >> >> >> sqlite-users@sqlite.org >> >> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> >> >> >> > >> >> > >> >> > >> >> > -- >> >> > >> >> > Best Regards, >> >> > ZHAO, Wenbo >> >> > >> >> > === >> >> > ___ >> >> > 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 >> >> >> > >> > >> > >> > -- >> > >> > Best
Re: [sqlite] Most wanted features of SQLite ?
Dr. Hip, As I stated before, I do not have a detailed understanding of the core source. Therefore I do not know how difficult or even possible adding a pragma controlled(?) finer grained locking "hook" or related API calls for those who might wish to "wrap" a server layer around the core. SQLite has proven to be a very fast, small, reliable, and bullet proof DB engine, for a single instance. I prefer to keep it true to its heritage. My real motive for the locking solution and API's would be to take the multi user pressure off the core. Leave us that want it to remain small, fast, and tight, a core that can either be used as is or embedded in a bigger picture. Fred -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]on Behalf Of D. Richard Hipp Sent: Monday, September 21, 2009 8:14 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Most wanted features of SQLite ? On Sep 21, 2009, at 8:58 AM, Fred Williams wrote: > > Fine for me. It seems to be everybody else that wants their favorite > feature imbedded in the core :-) > > Fine grained locking would be a great "asset" I feel. Notice I did > not > request a "feature." On my to-do list is to write a paper that explains why fine-grain locking is not practical without either (1) a dedicated server process to manage the locks or (2) enhancements to OS locking primitives that are not currently available on any OS that I am aware of. There is a widely held belief that since OSes provide byte-level locking of files it should be a simple matter to provide row-level locking in a serverless database engine. The proposed paper will explain why that belief is incorrect. D. Richard Hipp d...@hwaci.com ___ 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] blocking when locking
2009/9/21 Pavel Ivanov > > What I want to say is in this example, there should be only one step, > > because the transaction knows it will do 'write'. > > Then the txn should start a write lock before the select. > > > > And this is not a good example to explain dead lock, I think. > > And again, you're talking about application using SQLite. Maybe it > knows that it will write (also not necessarily so - depends on the > structure of application) and so it should take write lock right away. > But maybe it wants to improve contention and in majority of cases it > doesn't need to write after select... In any way SQLite doesn't know > anything about application's intentions, so it can't take write lock > before the select and dead lock is still possible. > I see. Thanks for your explaination. May I ask if SQLite can explicitly start a write lock transaction ? Sorry to bother you with such a basic question. I'm still new in SQLite. > > Pavel > > On Sun, Sep 20, 2009 at 2:04 AM, Wenbo Zhao wrote: > > I was talking about this example by > > 2009/9/19 Igor Tandetnik > > "Imagine the > > classic example, where a transaction first verifies that the balance in > > a bank account is sufficient, then performs a withdrawal. If it > > relinquishes all locks between these two steps, then somebody else may > > record a withdrawal from that account, so that the write operation would > > then make the balance negative, thus violating an invariant." > > > > What I want to say is in this example, there should be only one step, > > because the transaction knows it will do 'write'. > > Then the txn should start a write lock before the select. > > > > And this is not a good example to explain dead lock, I think. > > > > > > 2009/9/19 Pavel Ivanov > > > >> Wenbo, are you talking about what do you want to see in DBMS or are > >> you trying to explain how SQLite works? > >> If the latter then you're wrong. In SQLite 'read lock' is designed for > >> transaction that _made_ any reads, 'write lock' - for transaction that > >> _made_ any writes. > >> > >> Pavel > >> > >> On Sat, Sep 19, 2009 at 12:18 AM, Wenbo Zhao wrote: > >> > This is not a good example i think. > >> > If a transaction is intent to update after the select, it should start > >> > a write lock before the select. > >> > And as described in previous 'dead lock' example, the update in this > >> > example could fail due to 'dead lock' > >> > I believe the 'read lock' is designed for a 'read only' transaction, > >> > and the 'write lock' is for a transaction that 'may write something'. > >> > > >> > 2009/9/19 Igor Tandetnik > >> > > >> >> Angus March wrote: > >> >> >Yes, I see. So what is key to the problem is that someone tries > to > >> >> > change their read lock to a write lock. I guess I just thought that > >> >> > the kernel that manages fcntl() would have a way of dealing with > >> >> > this. Can this situation not be averted if at step 3, transaction A > >> >> > releases its read lock before requesting a write lock? > >> >> > >> >> Then it wouldn't be much of a transaction, now would it? Imagine the > >> >> classic example, where a transaction first verifies that the balance > in > >> >> a bank account is sufficient, then performs a withdrawal. If it > >> >> relinquishes all locks between these two steps, then somebody else > may > >> >> record a withdrawal from that account, so that the write operation > would > >> >> then make the balance negative, thus violating an invariant. > >> >> > >> >> Of course, if that's what the application wants, it can simply > perform > >> >> the read and the write operations in two separate transactions. > >> >> > >> >> Igor Tandetnik > >> >> > >> >> > >> >> > >> >> ___ > >> >> sqlite-users mailing list > >> >> sqlite-users@sqlite.org > >> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >> >> > >> > > >> > > >> > > >> > -- > >> > > >> > Best Regards, > >> > ZHAO, Wenbo > >> > > >> > === > >> > ___ > >> > 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 > >> > > > > > > > > -- > > > > Best Regards, > > ZHAO, Wenbo > > > > === > > ___ > > 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 > -- Best Regards, ZHAO, Wenbo === ___ sqlite-users mailing list sqlite-users@sqlite.o
Re: [sqlite] Most wanted features of SQLite ?
On Sep 21, 2009, at 8:58 AM, Fred Williams wrote: > > Fine for me. It seems to be everybody else that wants their favorite > feature imbedded in the core :-) > > Fine grained locking would be a great "asset" I feel. Notice I did > not > request a "feature." On my to-do list is to write a paper that explains why fine-grain locking is not practical without either (1) a dedicated server process to manage the locks or (2) enhancements to OS locking primitives that are not currently available on any OS that I am aware of. There is a widely held belief that since OSes provide byte-level locking of files it should be a simple matter to provide row-level locking in a serverless database engine. The proposed paper will explain why that belief is incorrect. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Fine for me. It seems to be everybody else that wants their favorite feature imbedded in the core :-) Fine grained locking would be a great "asset" I feel. Notice I did not request a "feature." Fred -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]on Behalf Of Alexey Pechnikov Sent: Monday, September 21, 2009 1:54 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Most wanted features of SQLite ? Hello! On Monday 21 September 2009 01:45:07 Fred Williams wrote: > With the background of the never ending drumbeat of "feature, feature, > feature..." on this list as a reason, I wonder if the structure of > SQLite could be "enhanced" to better support the "plug-in" concept, aka: > as with Firefox, IGoogle, and the like for instance. SQLite does have the best extensibility of known to me DBMS. You can bind function or collation from any programming lang. And you can easy write extension with a new functions and collations. You can use SQLite functions in any programming lang and create wrappers. Is it not enought for you?! Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ 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] Index usage
> My question: how can I optimize this kind of query so that it utilizes > both indexes, to grab the first [b] rows (ordered by time) which also > match [a]? Or am I just going to have to guess at which way will be > faster, and use "INDEXED BY" to force it? (The documentation says I > shouldn't have to do this) There's no way to optimize your query to be fast in both situations. LIMIT clause is pretty hard to optimize. Maybe just to have a closer look at the application structure - maybe it's not so necessary to do ORDER BY or maybe LIMIT can be moved to inner query... But for this particular case I think it's pretty reasonable to use INDEXED BY clause despite what documentation says (it discourages usage for common cases). Pavel On Fri, Sep 18, 2009 at 5:55 PM, Matthew L. Creech wrote: > Hi, > > I'm trying to optimize a query for 2 different scenarios, and I'm > having trouble getting something that works good in general. I want > to be sure I'm not missing something. Here are the tables and indexes > used in my database: > > sqlite> CREATE TABLE path_table (idx INTEGER PRIMARY KEY, path TEXT UNIQUE); > sqlite> CREATE TABLE val_table (idx INTEGER, val REAL, time INTEGER); > > sqlite> CREATE INDEX time_idx ON val_table (time ASC); > sqlite> CREATE INDEX path_idx ON val_table (idx ASC); > > 'path_table' contains unique string path names, while 'val_table' > records any number of values associated with each path, and the time > at which the value occurred. > > My query looks something like: > > sqlite> SELECT val FROM val_table WHERE idx IN (SELECT idx FROM > path_table WHERE path GLOB '[a]') ORDER BY time ASC LIMIT [b]; > > where [a] and [b] are provided by my code's caller. My problem occurs > when the idx value(s) selected from 'path_table' match a large number > of records in the database - say, 100,000 out of 1,000,000 records. > In that case, the query takes several minutes to complete even when > [b] is small. Presumably it's first looking up all 100,000 rows where > 'idx' matches, then applying the ORDER BY clause to those results > without indexing. EXPLAIN QUERY PLAN confirms: > > 0|0|TABLE val_table WITH INDEX path_idx > > I tried adding "INDEXED BY time_idx", which greatly improved this > particular case, because statistically 1/10 rows will match 'idx' and > therefore we find [b] of them very quickly when [b] is small. But > this hurts performance in other cases, since if there are only a few > rows with a matching 'idx', the query ends up manually walking through > most of the table. > > My question: how can I optimize this kind of query so that it utilizes > both indexes, to grab the first [b] rows (ordered by time) which also > match [a]? Or am I just going to have to guess at which way will be > faster, and use "INDEXED BY" to force it? (The documentation says I > shouldn't have to do this) > > Thanks for the help! > > -- > Matthew L. Creech > ___ > 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] Newbie needing help to get set up for C++ in Windows
> I am using NetBeans 6.5.1 as my development environment and Cygwin (GNU) > C++. What does this mean? Do you program in C++ inside NetBeans? > If the DLL is all that I need, could someone give me a hint as to how to > link to it in NetBeans? > All my NetBeans work to date has been in Java and I just create a library > that points to a jar file. This is much clearer. You cannot link DLL into java right away. You have to create some JNI wrapper for that. But maybe it's much better for you to get JDBC driver for SQLite or you can use any other approach for java presented here: http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers. Pavel On Sat, Sep 19, 2009 at 4:49 PM, burferd wrote: > > I am brand new to SQLite and need to set up a Windows environment for use > with C++. > I am using NetBeans 6.5.1 as my development environment and Cygwin (GNU) > C++. > > I have seen a lot of documentation on the interface, but I have not found > much in the way of getting set up. > > What, exactly do I need to do in order to get a library that I can link to > my application? > I've seen a DLL. Do I just need to download that DLL and put it in my > system directory? > Or is there another library type that I need to download? > > If the DLL is all that I need, could someone give me a hint as to how to > link to it in NetBeans? > All my NetBeans work to date has been in Java and I just create a library > that points to a jar file. > > Or if there is a good "Getting Started" document source that explains this, > I would appreciate a link. > > Thanks. > -- > View this message in context: > http://www.nabble.com/Newbie-needing-help-to-get-set-up-for-C%2B%2B-in-Windows-tp25525319p25525319.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 > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL Lite date / time functions
Alexey Pechnikov wrote: > Please speak is now correct to store 'start of month' dates in > julianday format? Was it at some point "incorrect" to do so? Yes, you can store any date in julian day format, start of month or otherwise. > Does SQLite now uses internal reprezentation of > microseconds for the julianday and store it as long integer or float? Julian day format is a double, with date represented by the whole part and time by the fractional part. A double has 53 bits of mantissa, IIRC, of which some 22 bits are needed to represent dates in recent past and future, leaving 31 bits for time. This gives a resolution of about 40 microseconds - so no, the format is incapable of representing times accurate down to a microsecond. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] blocking when locking
> What I want to say is in this example, there should be only one step, > because the transaction knows it will do 'write'. > Then the txn should start a write lock before the select. > > And this is not a good example to explain dead lock, I think. And again, you're talking about application using SQLite. Maybe it knows that it will write (also not necessarily so - depends on the structure of application) and so it should take write lock right away. But maybe it wants to improve contention and in majority of cases it doesn't need to write after select... In any way SQLite doesn't know anything about application's intentions, so it can't take write lock before the select and dead lock is still possible. Pavel On Sun, Sep 20, 2009 at 2:04 AM, Wenbo Zhao wrote: > I was talking about this example by > 2009/9/19 Igor Tandetnik > "Imagine the > classic example, where a transaction first verifies that the balance in > a bank account is sufficient, then performs a withdrawal. If it > relinquishes all locks between these two steps, then somebody else may > record a withdrawal from that account, so that the write operation would > then make the balance negative, thus violating an invariant." > > What I want to say is in this example, there should be only one step, > because the transaction knows it will do 'write'. > Then the txn should start a write lock before the select. > > And this is not a good example to explain dead lock, I think. > > > 2009/9/19 Pavel Ivanov > >> Wenbo, are you talking about what do you want to see in DBMS or are >> you trying to explain how SQLite works? >> If the latter then you're wrong. In SQLite 'read lock' is designed for >> transaction that _made_ any reads, 'write lock' - for transaction that >> _made_ any writes. >> >> Pavel >> >> On Sat, Sep 19, 2009 at 12:18 AM, Wenbo Zhao wrote: >> > This is not a good example i think. >> > If a transaction is intent to update after the select, it should start >> > a write lock before the select. >> > And as described in previous 'dead lock' example, the update in this >> > example could fail due to 'dead lock' >> > I believe the 'read lock' is designed for a 'read only' transaction, >> > and the 'write lock' is for a transaction that 'may write something'. >> > >> > 2009/9/19 Igor Tandetnik >> > >> >> Angus March wrote: >> >> > Yes, I see. So what is key to the problem is that someone tries to >> >> > change their read lock to a write lock. I guess I just thought that >> >> > the kernel that manages fcntl() would have a way of dealing with >> >> > this. Can this situation not be averted if at step 3, transaction A >> >> > releases its read lock before requesting a write lock? >> >> >> >> Then it wouldn't be much of a transaction, now would it? Imagine the >> >> classic example, where a transaction first verifies that the balance in >> >> a bank account is sufficient, then performs a withdrawal. If it >> >> relinquishes all locks between these two steps, then somebody else may >> >> record a withdrawal from that account, so that the write operation would >> >> then make the balance negative, thus violating an invariant. >> >> >> >> Of course, if that's what the application wants, it can simply perform >> >> the read and the write operations in two separate transactions. >> >> >> >> Igor Tandetnik >> >> >> >> >> >> >> >> ___ >> >> sqlite-users mailing list >> >> sqlite-users@sqlite.org >> >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> >> > >> > >> > >> > -- >> > >> > Best Regards, >> > ZHAO, Wenbo >> > >> > === >> > ___ >> > 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 >> > > > > -- > > Best Regards, > ZHAO, Wenbo > > === > ___ > 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] sqlite command-line system escape
Is there an escape character or sequence that can be used from the sqlite3 command-line to run system commands ? For example sqlite> ! dir Using sqlite3 version 3.5.9-6 under Debian Linux I thought I remembered doing this in earlier versions from a few years back, but my memory may be yet again playing tricks on me Quickly scanning the docs and google-izing has not floated up a solution -- Stanley C. Kitching Human Being Phoenix, Arizona ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] List of active savepoints names
> And now compare it with simple API funtion what just list existing > savepoint stack inside Sqlite. It is simple, trivial, useful... and > working right at any case. So, just take SQLite code, implement this trivial function and use it as you like. It will be a separate function, you will not change any existing code, so you will not have any problems merging your patch into sources of any future SQLite release. Pavel On Mon, Sep 21, 2009 at 6:51 AM, Lukas Gebauer wrote: >> > > > I am searching some API function for get list of active >> > > > savepoint names. Is this possible? Thank you! > >> > > No, this is not possible. Why do you need it in the first place? > >> > It will be great for debugging, for example. > >> You can create a custom wrapper for savepoints which may have >> the stack of calls. > > Why I must duplicate code what is inside Sqlite too? Ad-absurdum - it > is like you suggest me to implement my own Sqlite clone. *grin* > > Sqlite have savepoint stack inside (AFAIK). I really not know why is > a problem to list this stack through some API. (But maybe it really > is a problem, I don't know, but nobody tell this yet...) > > Yes, I can create my own wrapper, what can work with my code only, > however with third-party code not. I can catch all savepoint and > transaction commands and try to simulate what happen inside Sqlite. > Maybe I made same decision as Sqlite. With each Sqlite version I must > check, if handling in Sqlite was not changed. Etc. Well, I not see > this as effective and reliable solution anymore. > > And now compare it with simple API funtion what just list existing > savepoint stack inside Sqlite. It is simple, trivial, useful... and > working right at any case. > > This is why I am asking for. Thanks! > > > > -- > Lukas Gebauer. > > E-mail: gebau...@mlp.cz > http://synapse.ararat.cz/ - Ararat Synapse - TCP/IP Lib. > > ___ > 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] Most wanted features of SQLite ?
Hello! On Monday 21 September 2009 14:33:30 Grzegorz Wierzchowski wrote: > BTW while we are at subject of SQLite extensions. > I'm very new on this e-mail list but already saw here and there in mails > several links to places around the web with some extensions. There are a lot of extensions in defferent places. As example the compress extension functions are publicated by DRH in mail list, see comments in the sources. Yes, it is. Now you can see my archive http://mobigroup.ru/files/sqlite-ext/ Of cource I will glad to know about other extensions repositories. > I think it could be quite helpfull "first check place" if anybody is looking > for something like virtual table which stores data in csv files, or so, or > opposite - have wrote something general, and want to share. Please see VirtualText extension from Spatialite project http://mobigroup.ru/files/sqlite-ext/virtualtext/ I did pack this as single extension. NB: in multitheaded env is more better to make copy of date in SQLite with the extension and after operate with the copy. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Faster inserts in SQlite ...
Hi List, I have been using SQlite to store the information from my application.However,when i did a time analysis, I found that the sqlite contributes to Half of the total time.The data that i am storing in the Database using SQlite is retrieved on a per entity basis. In order, to enhance the speed, i had set pragmas, 1. PRAGMA journal_mode = MEMORY; 2. PRAGMA synchronous = OFF; 3. PRAGMA temp_store = MEMORY; 4. PRAGMA count_changes = OFF; 5. PRAGMA cache_size = 12000; i also avoid the update calls, and i do all the insertion in between Begin Transaction and End Transaction.I was planning to call the insertion calls to SQLite in a separate thread.I am using the SQLite Version 3.3.13.When i dig in through documentation, the latest version was 3.3.18.FAQ says that the SQlite is Thread safe. Please suggest me if I should work in this direction. Thanks in Advance, Atul -- View this message in context: http://www.nabble.com/Faster-inserts-in-SQlite-...-tp25530282p25530282.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] List of active savepoints names
> > > > I am searching some API function for get list of active > > > > savepoint names. Is this possible? Thank you! > > > No, this is not possible. Why do you need it in the first place? > > It will be great for debugging, for example. > You can create a custom wrapper for savepoints which may have > the stack of calls. Why I must duplicate code what is inside Sqlite too? Ad-absurdum - it is like you suggest me to implement my own Sqlite clone. *grin* Sqlite have savepoint stack inside (AFAIK). I really not know why is a problem to list this stack through some API. (But maybe it really is a problem, I don't know, but nobody tell this yet...) Yes, I can create my own wrapper, what can work with my code only, however with third-party code not. I can catch all savepoint and transaction commands and try to simulate what happen inside Sqlite. Maybe I made same decision as Sqlite. With each Sqlite version I must check, if handling in Sqlite was not changed. Etc. Well, I not see this as effective and reliable solution anymore. And now compare it with simple API funtion what just list existing savepoint stack inside Sqlite. It is simple, trivial, useful... and working right at any case. This is why I am asking for. Thanks! -- Lukas Gebauer. E-mail: gebau...@mlp.cz http://synapse.ararat.cz/ - Ararat Synapse - TCP/IP Lib. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
You mean like http://www.sqlite.org/contrib ? I agree though there's much to improve in that area... Itamar. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Grzegorz Wierzchowski Sent: Monday, September 21, 2009 1:34 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Most wanted features of SQLite ? BTW while we are at subject of SQLite extensions. I'm very new on this e-mail list but already saw here and there in mails several links to places around the web with some extensions. What would you say guys for creating some centalized list of (known/recommended ...) extensions somewhere on official Sqlite wiki. I found: http://www.sqlite.org/cvstrac/wiki?p=ManagementTools , but this seems to be mainly about external programs like GUI or Management tools. I mean similar list but with links to general purposes code (open source licensed) for things like: - virtual tables modules - some usefull expression functions not implemented in mainline - special collating functions usable for wider audience, etc. I think it could be quite helpfull "first check place" if anybody is looking for something like virtual table which stores data in csv files, or so, or opposite - have wrote something general, and want to share. Best Regards, Grzegorz W. Monday 21 of September 2009 09:36:15 Roger Binns napisał(a): > Alexey Pechnikov wrote: > > SQLite does have the best extensibility of known to me DBMS. > > Also not mentioned is that it is available under a public domain > license and hence anyone has the right to use it in any way they deem > fit, make changes, distribute changes, charge anything they want, keep > everything public, private or anything else. Some of the alternatives > are open source but more restrictive (GPL). I've never read the > Oracle license agreement but there are many claims on the Internet > that its license agreement forbids publishing of benchmarks! > > Many of the feature requests are ultimately asking (or hoping :-) that > someone else will do the work. If something is that important then it > isn't unreasonable to pay DRH and team to do it. They also provide > support: > > http://www.sqlite.org/support.html > > Note "modest fee"! The extensions are also very cheap and liberally > licensed. No matter how you look at it, SQLite is a bargain. > > Roger > ___ > 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
Re: [sqlite] Most wanted features of SQLite ?
BTW while we are at subject of SQLite extensions. I'm very new on this e-mail list but already saw here and there in mails several links to places around the web with some extensions. What would you say guys for creating some centalized list of (known/recommended ...) extensions somewhere on official Sqlite wiki. I found: http://www.sqlite.org/cvstrac/wiki?p=ManagementTools , but this seems to be mainly about external programs like GUI or Management tools. I mean similar list but with links to general purposes code (open source licensed) for things like: - virtual tables modules - some usefull expression functions not implemented in mainline - special collating functions usable for wider audience, etc. I think it could be quite helpfull "first check place" if anybody is looking for something like virtual table which stores data in csv files, or so, or opposite - have wrote something general, and want to share. Best Regards, Grzegorz W. Monday 21 of September 2009 09:36:15 Roger Binns napisał(a): > Alexey Pechnikov wrote: > > SQLite does have the best extensibility of known to me DBMS. > > Also not mentioned is that it is available under a public domain license > and hence anyone has the right to use it in any way they deem fit, make > changes, distribute changes, charge anything they want, keep everything > public, private or anything else. Some of the alternatives are open source > but more restrictive (GPL). I've never read the Oracle license agreement > but there are many claims on the Internet that its license agreement > forbids publishing of benchmarks! > > Many of the feature requests are ultimately asking (or hoping :-) that > someone else will do the work. If something is that important then it > isn't unreasonable to pay DRH and team to do it. They also provide > support: > > http://www.sqlite.org/support.html > > Note "modest fee"! The extensions are also very cheap and liberally > licensed. No matter how you look at it, SQLite is a bargain. > > Roger > ___ > 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] perfomance optimisations on tmpfs file system
Ah, great "first post" :( and now I'm going to compound my crimes by replying to self with a real subject heading - Sorry about that. > Hi, (SQLite v3.6.17, on ARM with Linux 2.6.18) > > I'm currently investigating the applicability of SQLite in an embedded > application. Part of this would be to store configuration data, for > which SQLite seems like a natural fit. > > I also have a requirement for exchange of realtime data between > processes. The data is only stored in RAM (probably on a tmpfs > filesystem in Linux, so it can be shared) and, while atomic commits are > valuable, it is not important to be robust in the face of reboots. > > Testing shows that I can commit writes at about 400 transactions per > second and read tables at about 1400 queries per second. I have tried > changing PRAGMA settings for journal_mode to OFF, synchronous OFF and > temp_store to 2. These seem to have a small impact (to about 410 > commits/second). > > PRAGMA journal_mode; gives no output though which makes me suspicious as > to whether it is doing anything. > > The performance so far is perhaps adequate, but since it takes up almost > 100% CPU to do it, it will of course tail off when I start to do > anything with the data. > > Are there any other configuration options (per DB) that I should be > considering, or other tips that might be relevant? > > Thanks, > Nick. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (no subject)
Hi, (SQLite v3.6.17, on ARM with Linux 2.6.18) I'm currently investigating the applicability of SQLite in an embedded application. Part of this would be to store configuration data, for which SQLite seems like a natural fit. I also have a requirement for exchange of realtime data between processes. The data is only stored in RAM (probably on a tmpfs filesystem in Linux, so it can be shared) and, while atomic commits are valuable, it is not important to be robust in the face of reboots. Testing shows that I can commit writes at about 400 transactions per second and read tables at about 1400 queries per second. I have tried changing PRAGMA settings for journal_mode to OFF, synchronous OFF and temp_store to 2. These seem to have a small impact (to about 410 commits/second). PRAGMA journal_mode; gives no output though which makes me suspicious as to whether it is doing anything. The performance so far is perhaps adequate, but since it takes up almost 100% CPU to do it, it will of course tail off when I start to do anything with the data. Are there any other configuration options (per DB) that I should be considering, or other tips that might be relevant? Thanks, Nick. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] List of active savepoints names
Hello! On Monday 21 September 2009 11:48:37 Lukas Gebauer wrote: > > > I am searching some API function for get list of active savepoint > > > names. Is this possible? Thank you! > > > > No, this is not possible. Why do you need it in the first place? > > It will be great for debugging, for example. > You can create a custom wrapper for savepoints which may have the stack of calls. Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
Hello! On Monday 21 September 2009 11:36:15 Roger Binns wrote: > Also not mentioned is that it is available under a public domain license and > hence anyone has the right to use it in any way they deem fit, make changes, > distribute changes, charge anything they want, keep everything public, > private or anything else. And last few years every month are released new important features. About five years ago I did start to use SQLite as RDBMS for PDA and as helper storage for server but now I have some projects translated to SQLite from PostgreSQL and Oracle. That's great! Best regards, Alexey Pechnikov. http://pechnikov.tel/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Viewer for blobs in hex?
Yan Bertrand wrote: > Hi all, > > > > I would like to display the contents of blobs in my table as > hexadecimal. I have not found any easy way of doing this. I tried : > > - wxSQLitePlus, but it does not display blobs contents (or I > could not make it do so) > > - SQLiteManager plugin (for Mozilla Firefox), but it does not > do this by default. The website says it can but the explaination for it > is still < to be done >. > > - SQLiteStudio does not display anything (it says < NULL > in > italic, but it is not a NULL content.) > > - SQLite2009 Pro but it does not dosplay blobs either > > - I could dump the table but it really gets tedious. > > > > Note: I tried exporting to other formats but the blobs are replaced by a > string (either < NULL > or < NONE >). I know my blobs are not empty > because C-code can read them. > > > > Any idea? SQLiteSpy displays blobs in hex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Viewer for blobs in hex?
Yan, >I would like to display the contents of blobs in my table as >hexadecimal. I have not found any easy way of doing this. I tried : Give SQLite Expert a try. There are both free and Pro version available at http://www.sqliteexpert.com/index.html Free version has no hassle license, no expiry, full of features and is as current as the payware release. I'm just an happy user of this application. Have a nice day. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Viewer for blobs in hex?
Hello Yan, you could use my SQLiteManager application: http://www.sqlabs.com/sqlitemanager.php Regards, -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ On Sep 21, 2009, at 9:43 AM, Yan Bertrand wrote: >Hi all, > > > > I would like to display the contents of blobs in my table as > hexadecimal. I have not found any easy way of doing this. I tried : > > - wxSQLitePlus, but it does not display blobs contents (or I > could not make it do so) > > - SQLiteManager plugin (for Mozilla Firefox), but it does not > do this by default. The website says it can but the explaination for > it > is still < to be done >. > > - SQLiteStudio does not display anything (it says < NULL > in > italic, but it is not a NULL content.) > > - SQLite2009 Pro but it does not dosplay blobs either > > - I could dump the table but it really gets tedious. > > > > Note: I tried exporting to other formats but the blobs are replaced > by a > string (either < NULL > or < NONE >). I know my blobs are not empty > because C-code can read them. > > > > Any idea? > > > > Thank you for your support and merry continued use of SQLite! > > > > Yan > > ___ > 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] Viewer for blobs in hex?
2009/9/21 Yan Bertrand : > Hi all, > > > > I would like to display the contents of blobs in my table as > hexadecimal. I have not found any easy way of doing this. I tried : > > - wxSQLitePlus, but it does not display blobs contents (or I > could not make it do so) > > - SQLiteManager plugin (for Mozilla Firefox), but it does not > do this by default. The website says it can but the explaination for it > is still < to be done >. > > - SQLiteStudio does not display anything (it says < NULL > in > italic, but it is not a NULL content.) > > - SQLite2009 Pro but it does not dosplay blobs either > > - I could dump the table but it really gets tedious. > > > > Note: I tried exporting to other formats but the blobs are replaced by a > string (either < NULL > or < NONE >). I know my blobs are not empty > because C-code can read them. > > > > Any idea? > >From sqlite shell command line (or equivalent in you GUI of choice) execute select hex( yourBlobColumnName ) from yourTable; > > Thank you for your support and merry continued use of SQLite! > > Yan > Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] List of active savepoints names
> > I am searching some API function for get list of active savepoint > > names. Is this possible? Thank you! > > No, this is not possible. Why do you need it in the first place? It will be great for debugging, for example. -- Lukas Gebauer. E-mail: gebau...@mlp.cz http://synapse.ararat.cz/ - Ararat Synapse - TCP/IP Lib. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] List of active savepoints names
> > I am searching some API function for get list of active savepoint > > names. Is this possible? Thank you! > > Since your code is creating and releasing savepoints, why not just > record them in your code? I am not creating and releasing savepoints! They are created and released by Sqlite, I just calling commands. ;-) So, Sqlite must know what savepoints exists at any time. Would be easy allow to read this set of savepoint names, AFAIK. Doing it in my code is little silly. Just because I must duplicate lot of code for decide if savepoint is or not is created, if is or not is released, etc. Additionally, when my application callign some third-party code for accessing sqlite, then I cannot do this easily. However list of active savepoints would be great for debugging, for enhanced debug info for exception dialogs, etc. > Also IIRC you can also have duplicate names. Never mind. It not break anything. When I got list of savepoints names sorted in same order as is inside SQlite, then I got exactly what I need. -- Lukas Gebauer. E-mail: gebau...@mlp.cz http://synapse.ararat.cz/ - Ararat Synapse - TCP/IP Lib. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Viewer for blobs in hex?
Hi all, I would like to display the contents of blobs in my table as hexadecimal. I have not found any easy way of doing this. I tried : - wxSQLitePlus, but it does not display blobs contents (or I could not make it do so) - SQLiteManager plugin (for Mozilla Firefox), but it does not do this by default. The website says it can but the explaination for it is still < to be done >. - SQLiteStudio does not display anything (it says < NULL > in italic, but it is not a NULL content.) - SQLite2009 Pro but it does not dosplay blobs either - I could dump the table but it really gets tedious. Note: I tried exporting to other formats but the blobs are replaced by a string (either < NULL > or < NONE >). I know my blobs are not empty because C-code can read them. Any idea? Thank you for your support and merry continued use of SQLite! Yan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Most wanted features of SQLite ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Alexey Pechnikov wrote: > SQLite does have the best extensibility of known to me DBMS. Also not mentioned is that it is available under a public domain license and hence anyone has the right to use it in any way they deem fit, make changes, distribute changes, charge anything they want, keep everything public, private or anything else. Some of the alternatives are open source but more restrictive (GPL). I've never read the Oracle license agreement but there are many claims on the Internet that its license agreement forbids publishing of benchmarks! Many of the feature requests are ultimately asking (or hoping :-) that someone else will do the work. If something is that important then it isn't unreasonable to pay DRH and team to do it. They also provide support: http://www.sqlite.org/support.html Note "modest fee"! The extensions are also very cheap and liberally licensed. No matter how you look at it, SQLite is a bargain. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkq3LOsACgkQmOOfHg372QRrWgCgrgqcqMEIMs6GgQpWv8WUzcCb XEUAnRqpChAc1qSPocYYxUdlE7ni08Oo =OGd1 -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reading "packed" data
Simon Slavin wrote: > On 20 Sep 2009, at 8:06pm, Mohit Sindhwani wrote: > > >> I'm just trying to see if there's a way to move more data per request. >> But it seems not (for now). >> > > By all means write your own routine that calls _step lots of times. > But it won't be faster or more efficient. Other database engines have > a large overhead for each call to their libraries so fewer calls > results in improvements in efficiency and speed. This overhead can be > caused by having to do authentication (username/password) or access a > server running on another computer for each function call. SQLite > does not have to do these things and has almost no overhead for each > call to _step, so there's no opportunity for savings. > Hi Simon Thanks for the message. Yes, I can see why other databases may need it. It was one of the things that we were using in the other database for performance - I was just trying to check if there's any thing in SQLite that's equivalent to the same. However, I can see why that does not apply to SQLite. Thanks, Mohit. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
