Re: [sqlite] copy one row to another
Hello! On Thursday 08 April 2010 01:58:09 P Kishor wrote: > The above is not going to work because I already have 649 in table t. CREATE TEMP TABLE temp_t AS SELECT * FROM t WHERE id = 651; UPDATE temp_t SET id=649; -- may be id=id-2 for set of records DELETE FROM t WHERE id = 649; INSERT INTO t SELECT * FROM temp_t; 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] Concurrency for in-memory scenarios
BODY { font-family:Arial, Helvetica, sans-serif;font-size:12px;
}Thanks to all respondents.
A RAM disk is a good idea, but would require me to jump through too
many hoops to get it approved. Corporate environment, you see :(
For now, I've gone with a disk-based database with a larger cache and
page size. Concurrent reads appear to be working, so I guess the
shared cache is disabled by default . . . ?
Best,
Kent
On Wed 07/04/10 6:47 PM , Alexey Pechnikov pechni...@mobigroup.ru
sent:
Hello!
On Tuesday 06 April 2010 17:43:47 Kent Boogaart wrote:
> What I'm wondering is whether SQLite flat out doesn't support
> concurrent access to an in-memory database, or perhaps whether I'm
> just doing something wrong. Can anyone confirm whether concurrent
> access to an in-memory database is supported?
Use database on tmpfs (ram disk) for this task. It's not needed to
add
this functionality at SQLite internals.
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] Concurrency for in-memory scenarios
On Apr 8, 2010, at 3:38 PM, Kent Boogaart wrote:
> BODY { font-family:Arial, Helvetica, sans-serif;font-size:12px;
> }Thanks to all respondents.
> A RAM disk is a good idea, but would require me to jump through too
> many hoops to get it approved. Corporate environment, you see :(
> For now, I've gone with a disk-based database with a larger cache and
> page size. Concurrent reads appear to be working, so I guess the
> shared cache is disabled by default . . . ?
It is indeed disabled by default.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Concurrency for in-memory scenarios
Hello! On Thursday 08 April 2010 12:38:57 you wrote: > A RAM disk is a good idea, but would require me to jump through too > many hoops to get it approved. Corporate environment, you see :( > For now, I've gone with a disk-based database with a larger cache and > page size. You can set PRAGMA synchronous=0 and writes will be very fast too. But will be possible database corruption. 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] non utf-8 database names
Hi, I'm not sure if this is the right place to ask or has been covered already (haven't been able to find any reference to this issue), but does sqlite3_open_v2 have to take a UTF-8 formatted string for the filename? SQLITE_API int sqlite3_open_v2( const char *filename, /* Database filename (UTF-8) */ sqlite3 **ppDb, /* OUT: SQLite db handle */ int flags, /* Flags */ const char *zVfs/* Name of VFS module to use */ ); I'm using sqlite through Qt and the problem I'm having is that in non utf locales (e.g. ja_JP.PCK on solaris) when given a shift-jis encoded to utf-8 string to open, the file being created is using the utf-8 characters, i.e. when the file is ls'd the characters are garbled. If a full path containing any non-ascii characters is given the file fails to be found given the different encodings. I've been going through the sqlite code shipped with qt (it's the amalgamated source so a bit of light reading ;) ) - but does anyone know off hand if there will be untoward issues with sqlite if I modified the qt call to sqlite so that a locale encoded string instead of utf-8 is passed across? I'm quite happy to modify the code as necessary but I am not sure as to what things might break or what problems to look out for. Regards, Alex Kides ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] non utf-8 database names
On 8 Apr 2010, at 11:35am, Alex Kides wrote: > I'm using sqlite through Qt and the problem I'm having is that in non > utf locales (e.g. ja_JP.PCK on solaris) when given a shift-jis encoded > to utf-8 string to open, the file being created is using the utf-8 > characters, i.e. when the file is ls'd the characters are garbled. If a > full path containing any non-ascii characters is given the file fails to > be found given the different encodings. > > > > I've been going through the sqlite code shipped with qt (it's the > amalgamated source so a bit of light reading ;) ) - but does anyone know > off hand if there will be untoward issues with sqlite if I modified the > qt call to sqlite so that a locale encoded string instead of utf-8 is > passed across? It would be non-standard, and you'd have to keep modifying it each time you switched to a later version of sqlite. A better solution would be to create your own function, with same arguments as sqlite3_open(), which took a filename of any character standard and converted it to UTF-8, then called the normal sqlite3_open(). This assumes it's possible to detect that something is shift-jis encoded. If it's not, then just make a mysqlite_open_jis() which always does the conversion from ^jis to UTF-8. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite on Windows 2003
First of all, I want to thank everyone that has replied to this email thread. I still do not have a successful opening of the SQLite connection in my ASP.NET C# application on Windows 2003. I have done the following: The connection opens successfully with the SQLite command tool. It opens successfully using a C# console application. I have entered the following in my web.config: I have installed SQLite on the server. I have entered the following in my web.config and machine.config: I have re-cycled IIS many times. I have done everything suggested in the following link: http://sqlite.phxsoftware.com/forums/p/1391/6025.aspx What am I missing? Your patience and help is appreciated. Joseph Burnett | Principal Software Engineer | FIDELITY INSTITUTIONAL TECHNOLOGY 2 Contra Way Merrimack, NH 03054 603.791.5113 CELL: 603.289.0481 -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Roger Binns Sent: Wednesday, April 07, 2010 2:27 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite on Windows 2003 >> Alternatively use Process Monitor from sysinternals.com which will show >> arguments and return codes without any need to run a debugger. > > Even better, use FILEMON (from sysinternals.com) for clues. It's wonderful. Err, no. You are 4 years out of date. The functionality of filemon, regmon etc was all wrapped up into Process Monitor. http://technet.microsoft.com/en-us/sysinternals/bb896642.aspx 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] SQLite on Windows 2003
> First of all, I want to thank everyone that has replied to this email > thread. > > I still do not have a successful opening of the SQLite connection in my > ASP.NET C# application on > Windows 2003. I have done the following: > > The connection opens successfully with the SQLite command tool. > It opens successfully using a C# console application. > I have entered the following in my web.config: > > I have installed SQLite on the server. > I have entered the following in my web.config and machine.config: > > > > description=".Net Framework Data Provider for > SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" /> > > > I have re-cycled IIS many times. > I have done everything suggested in the following link: > http://sqlite.phxsoftware.com/forums/p/1391/6025.aspx > Just a wild guess : You're sure you're not getting bitten by the fact that IIS under more recent Windows version is (extremely) restricted in the sense in what it can do on the filesytem ? Igmar ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite on Windows 2003
On 8 Apr 2010, at 1:12pm, Burnett, Joe wrote: > I still do not have a successful opening of the SQLite connection in my > ASP.NET C# application on > Windows 2003. I have done the following: > > The connection opens successfully with the SQLite command tool. > It opens successfully using a C# console application. Your problem appears to be with the tools supplied with ASP.NET. If the other two methods of opening work fine then the problem is definitely not with SQLite. I can only suggest you look for a mailing list about ASP.NET and ask why it has this problem. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite on Windows 2003
Hi Igmar, Yeah, I think it is related to that. I am not new to ASP.NET nor to Windows 2003. I am new to SQLite, however. But I think you are on to something. I even changed the file name extension to .txt and it still doesn't work, although it does work in the console application. Thanks, Joe Joseph Burnett | Principal Software Engineer | FIDELITY INSTITUTIONAL TECHNOLOGY 2 Contra Way Merrimack, NH 03054 603.791.5113 CELL: 603.289.0481 -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igmar Palsenberg Sent: Thursday, April 08, 2010 8:18 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite on Windows 2003 > First of all, I want to thank everyone that has replied to this email > thread. > > I still do not have a successful opening of the SQLite connection in my > ASP.NET C# application on > Windows 2003. I have done the following: > > The connection opens successfully with the SQLite command tool. > It opens successfully using a C# console application. > I have entered the following in my web.config: > > I have installed SQLite on the server. > I have entered the following in my web.config and machine.config: > > > > description=".Net Framework Data Provider for > SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" /> > > > I have re-cycled IIS many times. > I have done everything suggested in the following link: > http://sqlite.phxsoftware.com/forums/p/1391/6025.aspx > Just a wild guess : You're sure you're not getting bitten by the fact that IIS under more recent Windows version is (extremely) restricted in the sense in what it can do on the filesytem ? Igmar ___ 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 on Windows 2003
> Yeah, I think it is related to that. I am not new to ASP.NET nor to > Windows 2003. I am new to SQLite, however. > But I think you are on to something. I even changed the file name > extension to .txt and it still doesn't work, > although it does work in the console application. > To test : create a user with admin rights, and let IIS run under that user. See if that helps. If it does, you've got a permission problem. If it doesn'r, it's more complicated that a simple permission issue. Igmar ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite on Windows 2003
> -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Igmar Palsenberg > Sent: Thursday, April 08, 2010 6:18 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] SQLite on Windows 2003 > > > > First of all, I want to thank everyone that has replied to this email > > thread. > > > > I still do not have a successful opening of the SQLite connection in > my > > ASP.NET C# application on > > Windows 2003. I have done the following: > > > > The connection opens successfully with the SQLite command tool. > > It opens successfully using a C# console application. > > I have entered the following in my web.config: > > > > I have installed SQLite on the server. > > I have entered the following in my web.config and machine.config: > > > > > > > > invariant="System.Data.SQLite" > > description=".Net Framework DataProvider for > > SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" > /> > > > > > > I have re-cycled IIS many times. > > I have done everything suggested in the following link: > > http://sqlite.phxsoftware.com/forums/p/1391/6025.aspx > > > Just a wild guess : You're sure you're not getting bitten by the fact > that IIS under more recent Windows version is (extremely) restricted in > the sense in what it can do on the filesytem ? > > > Igmar I will second this guess. Have you tried placing your DB into the "bin" directory and tried opening it from there using a relative path? -Allan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite on Windows 2003
No, actually, not bin. I have tried it from the root directory and the App_Data directory using absolute paths. I will try your approach. Thanks Allan, Joe Joe Burnett | Principal Software Engineer | FIDELITY INSTITUTIONAL TECHNOLOGY 2 Contra Way Merrimack, NH 03054 603.791.5113 cell: 603.289.0481 -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Schrum, Allan Sent: Thursday, April 08, 2010 9:57 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite on Windows 2003 > -Original Message- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Igmar Palsenberg > Sent: Thursday, April 08, 2010 6:18 AM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] SQLite on Windows 2003 > > > > First of all, I want to thank everyone that has replied to this email > > thread. > > > > I still do not have a successful opening of the SQLite connection in > my > > ASP.NET C# application on > > Windows 2003. I have done the following: > > > > The connection opens successfully with the SQLite command tool. > > It opens successfully using a C# console application. > > I have entered the following in my web.config: > > > > I have installed SQLite on the server. > > I have entered the following in my web.config and machine.config: > > > > > > > > invariant="System.Data.SQLite" > > description=".Net Framework DataProvider for > > SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite" > /> > > > > > > I have re-cycled IIS many times. > > I have done everything suggested in the following link: > > http://sqlite.phxsoftware.com/forums/p/1391/6025.aspx > > > Just a wild guess : You're sure you're not getting bitten by the fact > that IIS under more recent Windows version is (extremely) restricted in > the sense in what it can do on the filesytem ? > > > Igmar I will second this guess. Have you tried placing your DB into the "bin" directory and tried opening it from there using a relative path? -Allan ___ 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] Lemon parser
On Tue, Apr 6, 2010 at 10:40 PM, Dan Kennedy wrote: > > On Apr 7, 2010, at 3:23 AM, Chris verBurg wrote: > > > Hehe, okay, here I go. :) > > > > > > I'm trying to replace an existing flex/bison parser with an re2c/lemon > > parser, but I'm running into a methodological problem. I have a > > hypothetical grammar like this: > > > > file ::= FOO str . > > file ::= BAR str . > > See the use of the "%fallback" directive in the parse.y > file that is part of SQLite sources. I think you want > something like this: > > %fallback str BAR. > > That does almost exactly what I want. Thank you thank you thank you! :) Do you know why that isn't documented? (Or, am I looking at the wrong documentation?) -Chris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Poor query planning for virtual tables
Hello! The SQLite library is builded from trunk af3b72d94a1b6513f02402af3ada5fb5dd390151 1. With ordinary tables - fast explain query plan select role.rowid as rowid, role.* from role_exist, role where role_exist.rowid=role.rowid; 0|0|TABLE role_exist 1|1|TABLE role USING PRIMARY KEY select role.rowid as rowid, role.* from role_exist, role where role_exist.rowid=role.rowid; ... CPU Time: user 0.00 sys 0.00 2. With ordinary tables and virtual table - very slow explain query plan select role_exist.rowid as rowid, role.* from role_exist, role, role_fts where role_exist.rowid=role.rowid and role_exist.rowid=role_fts.rowid; 0|2|TABLE role_fts VIRTUAL TABLE INDEX 0: 1|0|TABLE role_exist USING PRIMARY KEY 2|1|TABLE role USING PRIMARY KEY select role_exist.rowid as rowid, role.* from role_exist, role, role_fts where role_exist.rowid=role.rowid and role_exist.rowid=role_fts.rowid; ^CCPU Time: user 34.058128 sys 1.720108 Error: interrupted 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] Poor query planning for virtual tables
On Thu, Apr 08, 2010 at 11:52:13PM +0400, Alexey Pechnikov scratched on the wall: > > 1. With ordinary tables - fast > 2. With ordinary tables and virtual table - very slow > > select role_exist.rowid as rowid, role.* from role_exist, role, role_fts > where role_exist.rowid=role.rowid and role_exist.rowid=role_fts.rowid; > ^CCPU Time: user 34.058128 sys 1.720108 > Error: interrupted Is there a point you're trying to make, or a question you're asking? All you've done is posted two times that aren't that unexpected. Virtual Tables are completely abstract to the query planner. The only way to do any type of indexed or high efficiency lookup is if the virtual table allows that to happen. Otherwise, there is *nothing* the query planner can do, except perform a full table scan -- even on the ROWID column. The issue isn't with the SQLite core, it isn't with the query planner-- it is with the virtual table module you're using. Most modules are designed to be used in specific ways. If you're using it in some other way, it isn't a surprise it doesn't work very well. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Poor query planning for virtual tables
Hello! On Friday 09 April 2010 00:11:56 Jay A. Kreibich wrote: > Virtual Tables are completely abstract to the query planner. The > only way to do any type of indexed or high efficiency lookup is if > the virtual table allows that to happen. Otherwise, there is > nothing the query planner can do, except perform a full table scan > -- even on the ROWID column. The issue isn't with the SQLite core, > it isn't with the query planner-- it is with the virtual table module > you're using. Most modules are designed to be used in specific ways. > If you're using it in some other way, it isn't a surprise it doesn't > work very well. May be is needed to show this more informatively. The record "TABLE role_fts VIRTUAL TABLE INDEX 0:" is not useful I think. And is not helpful than planner using full table scan without any alert. For FTS3 the workaround is in using _content table insted of virtual table but now we must search for bad-planned queries manually. So vurtual tables are similar to regular tables but we have a lot of hidden planner problems. 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] Poor query planning for virtual tables
On Fri, Apr 09, 2010 at 12:50:56AM +0400, Alexey Pechnikov scratched on the wall: > Hello! > > May be is needed to show this more informatively. The record > "TABLE role_fts VIRTUAL TABLE INDEX 0:" is not useful I think. http://sqlite.org/fts3.html 1.3 Simple FTS3 Queries [...] FTS3 tables can be queried efficiently using SELECT statements of two different forms: - Query by rowid [...] the form "rowid = ?" [...] - Full Text query [...] MATCH [...] If neither of the two query strategies enumerated above can be used, all queries on FTS3 tables are implemented using a linear scan of the entire table. Although you've got rowid= type queries, they're JOINs, not searches for specific values, so the first point doesn't really count. I suppose there is some chance the query planner is assuming any column named "rowid" is a cheap lookup, and somehow favoring the FTS virtual table in the JOIN sequence, but that seems like a bit of a long-shot. > And is not helpful than planner using full table scan without any > alert. Exactly like every other query the database makes? Why would this be different? > So vurtual tables are similar to regular tables but we have a lot of > hidden planner problems. Virtual tables are *not* similar to regular tables. They're highly specialized, and each type of virtual table is specialized in a different manner. If you treat them like normal tables you're going to have lots of problems. If you want something that looks and acts like a normal table... use a normal table. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
