Re: [sqlite] Page 2 is never used
> How big is the database file in the filesystem after you do this? > Can you post it (the database file)? Have you defined any preprocessor > macros when compiling, for example SQLITE_DEFAULT_AUTOVACUUM or one > of the OMIT symbols? Hi Dan, how you can see in my other mail, starting with a new (and so empty) database sqlite> create table tbl1(one varchar(10), two smallint); sqlite> PRAGMA integrity_check; *** in database main *** Page 2 is never used sqlite> .dump BEGIN TRANSACTION; CREATE TABLE tbl1(one varchar(10), two smallint); COMMIT; I have defined this macro: # define TEMP_STORE 3 and -DTHREADSAFE=0 -DSQLITE_OMIT_AUTHORIZATION=1 but somtimes I try other macro combination like these: -DSQLITE_NO_SYNC -DSQLITE_DISABLE_DIRSYNC -DSQLITE_OMIT_FLOATING_POINT -DSQLITE_DISABLE_LFS -DSQLITE_OMIT_CURSOR with the same result thank you for your attention to my problem, Andrea Federico -- ___ Play 100s of games for FREE! http://games.mail.com/
Re: [sqlite] Page 2 is never used
> When you say that you have no problem, does that mean you > have run SQLite version 3.3.1 on vxWorks and it worked, or > that you have run some other SQLite version 3.x.x? There > were big changes in the OS layer going from 3.2.8 to 3.3.0. > Something might have broken, at least in respect to vxWorks. > > I don't have access to vxWorks so there is no way for me to > test this myself. > -- I'm used all 3.x.x versions of sqlite and the function sqlite3OsFileSize() return the correct size 2048 bytes. The size 2048 is the size I obtain creating a new database with command the "create table tbl1(one varchar(10), two smallint);" Thanks Again for your reply Andrea Federico -- ___ Play 100s of games for FREE! http://games.mail.com/
Re: [sqlite] Page 2 is never used
--- Andrea Federico <[EMAIL PROTECTED]> wrote: > Hi sqliteUsers, > > I have a simple question: > > can you tell me why I obtain an error creating a simple table > in a new database ? > > if I insert this simple command: > > sqlite> create table tbl1(one varchar(10), two smallint); > sqlite> PRAGMA integrity_check; > *** in database main *** > Page 2 is never used > > I obtain "Page 2 is never used" , I don't understand this strange > behaviuor and if I dump the database: How big is the database file in the filesystem after you do this? Can you post it (the database file)? Have you defined any preprocessor macros when compiling, for example SQLITE_DEFAULT_AUTOVACUUM or one of the OMIT symbols? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] SQLite 3.3.1
Indeed. VC2005 brings up much more than 10 warnings with the x64 compat warnings on, all due to lack of proper casting. The current code may work, but these warnings may mask a future warning that should be heeded. I've been meaning to work on a patch but havn't been able to find the time. On 1/17/06, Ken & Deb Allen <[EMAIL PROTECTED]> wrote: > Hmmm. The number of warnings compiling the 3.3.1 source is up to 10 > from 6 in the 3.3.0 source. All of them appear to be related to > inappropriate sign comparisons/assignments. > > -ken > -- Cory Nelson http://www.int64.org
Re: [sqlite] SQLite 3.3.1
Ken & Deb Allen wrote: Hmmm. The number of warnings compiling the 3.3.1 source is up to 10 from 6 in the 3.3.0 source. All of them appear to be related to inappropriate sign comparisons/assignments. -ken So your compiler is blowing smoke. GCC 4.02 which I use under Linux coughs up warnings for every compile of almost all my favorite software such as PHP 6. The software still works fine. Bob
[sqlite] SQLite 3.3.1
Hmmm. The number of warnings compiling the 3.3.1 source is up to 10 from 6 in the 3.3.0 source. All of them appear to be related to inappropriate sign comparisons/assignments. -ken
Re: [sqlite] Functions
> Was looking on the functions code. Wondered if their's any way I can > do something like > > for select from table > > do something here > > loop > > the field have a array of data may be single row but only single column Hi Vishal -- I'm not sure I understand your syntax. Maybe a more complete example of what you are hoping to do would help? If my guess is right about what you mean, it should be easy to do using a user-defined function and a sub-select. In general, if you can form the loop as part of a SQL statement, the loop part should be easy. And for "do something here", you can define an aggregate function to do what you want. But an actual example would help. --nate
[sqlite] Functions
Dear All , Was looking on the functions code. Wondered if their's any way I can do something like for select from table do something here loop the field have a array of data may be single row but only single column -- With Best Regards, Vishal Kashyap. http://vishalkashyap.tk
Re: [sqlite] Slow sqlite3_step - more details.
> What language and/or wrapper library are you using to drive SQLite? > > Also, how large are the records in your database? You said earlier that > they have 45 fields, but not what type of data was stored in any of > them. Since you are doing string searches, I assume that at least one > contains a string. Is it a large string? > > Actually it would be best if you could provide the actual schema of the > table, including any indexes and triggers that are defined as well. > Dennis, Thanks for your response. Since my last email I've done some more testing and have narrowed the problem down considerably. Your "What language" question is quite perceptive and seems to be at the core of the problem. I'm using Delphi, but basically talking direct to SQLite3.DLL. After further testing yesterday I accidently discovered that the problem was ONLY happening when I ran my program from the Delphi IDE. I've never noticed any slowdown like this before, but don't call very many external DLL's from my programs. The code running in the IDE (with slowdown) didn't have any breakpoints set, and the code that I run outside of the IDE (without problem) is the same compile as the code that I ran inside the IDE. I discovered the nature of the problem accidently when I recompiled the source for SQLite V3.3.1 under Visual Studio to try and see if I could find the slowdown point. I'm *very* new to Visual Studio but managed to get the debugger to break inside the DLL after having it being called from my App - as long as my App was not being executed in the Delphi IDE - and in the process discovered that the problem was no longer present! Ah well, I guess I'll just have to check the SQLite performance of my App outside the Delphi IDE. Regards, Carl.
Re: [sqlite] Page 2 is never used
"Andrea Federico" <[EMAIL PROTECTED]> wrote: > > > My first guess is that the implementation of > > > sqlite3OsFileSize() (actually implemented as > > > "unixFileSize" on line of os_unix.c) is > > > not working correctly. > > > > in my case I have no problem, with debbuger I see the size is correct, > > 2048 Byte. > > > > Following a suggestion from an old mail in this mailinglist > > I have replaced the type "dev_t" present in os_unix.c with (in my case) > > "unsigned long" in this way the Device number is copied correctly. > > I want to add this information: > I have no problem with sqlite version 2.8.17. > When you say that you have no problem, does that mean you have run SQLite version 3.3.1 on vxWorks and it worked, or that you have run some other SQLite version 3.x.x? There were big changes in the OS layer going from 3.2.8 to 3.3.0. Something might have broken, at least in respect to vxWorks. I don't have access to vxWorks so there is no way for me to test this myself. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] [RBL] [sqlite] Randomly ordering results
"Steve O'Hara" <[EMAIL PROTECTED]> wrote: > The standard SQLite function random() is actually seeded by > the current time and date every time you open a database. > FWIW, the random() function in SQLite uses a cryptographic PRNG and (at least on Unix) is seeded using /dev/urandom if that device is available. So it should give you very good randomness. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Page 2 is never used
> > My first guess is that the implementation of > > sqlite3OsFileSize() (actually implemented as > > "unixFileSize" on line of os_unix.c) is > > not working correctly. > > in my case I have no problem, with debbuger I see the size is correct, > 2048 Byte. > > Following a suggestion from an old mail in this mailinglist > I have replaced the type "dev_t" present in os_unix.c with (in my case) > "unsigned long" in this way the Device number is copied correctly. I want to add this information: I have no problem with sqlite version 2.8.17. Best Regards Andrea Federico -- ___ Play 100s of games for FREE! http://games.mail.com/
Re: [sqlite] Slow sqlite3_step - more details.
Carl Jacobs wrote: I'm using Windows XP Sp2, SQLite3.DLL V3.2.7. One of the tests which follows was repeated with bleeding edge SQLite3.DLL V3.3.1 - and gave the same result. If I do the following query 'SELECT count(*) FROM MyTable' then when I time the process I get the following results: (All times in milliseconds) (For ~17000 records) 87818684852311132114211381137 As mentioned in my previous email - I can narrow it down even further. The ONLY sqlite3_step command that has a problem is the final one that returns SQLITE_DONE. The only thing that seems to make it quick again is terminating and restarting the process - i.e. shutting down and restarting my program. It seems that no matter what I do it's quick for about 5 queries, and then seems to fall in a heap. Carl, Sorry for the repost, I hit send too soon last time. What language and/or wrapper library are you using to drive SQLite? Also, how large are the records in your database? You said earlier that they have 45 fields, but not what type of data was stored in any of them. Since you are doing string searches, I assume that at least one contains a string. Is it a large string? Actually it would be best if you could provide the actual schema of the table, including any indexes and triggers that are defined as well. Dennis Cote
Re: [sqlite] Slow sqlite3_step - more details.
Carl Jacobs wrote: I'm using Windows XP Sp2, SQLite3.DLL V3.2.7. One of the tests which follows was repeated with bleeding edge SQLite3.DLL V3.3.1 - and gave the same result. If I do the following query 'SELECT count(*) FROM MyTable' then when I time the process I get the following results: (All times in milliseconds) (For ~17000 records) 87818684852311132114211381137 ... As mentioned in my previous email - I can narrow it down even further. The ONLY sqlite3_step command that has a problem is the final one that returns SQLITE_DONE. The only thing that seems to make it quick again is terminating and restarting the process - i.e. shutting down and restarting my program. It seems that no matter what I do it's quick for about 5 queries, and then seems to fall in a heap. Carl, What language are you using to drive SQLite?
Re: [sqlite] RE: [RBL] Re: [sqlite] Randomly ordering results
On 1/17/06, Steve O'Hara <[EMAIL PROTECTED]> wrote: > Jay, If you're going to offer advice, at least do some basic research to > make sure it's accurate. Was it your intent to be insulting or are you just drunk? > > Firstly, having a column that has a random number in it and sorting by > it produces the same output each time you query on it - obviously > unsatisfactory and I would have thought, manor from heaven for card > counters everywhere!! If you don't reset the column to new random values on subsequent sorts you're correct. I thought that obvious enough it didn't need comment. In any case, he didn't specify that he needed to do this repeatedly for his application. In an online exam I would doubt they present the questions twice. > > Secondly, the standard SQLite function random() is actually seeded by > the current time and date every time you open a database. Thanks for that useful bit of trivia. Automatically seeding the random function is a bad idea and I'm surprised an otherwise well thought out package does it. It makes regression testing of the engine and your programs very difficult. Further, you assume that he's using the sqlite random function and not the function from whatever language he's using. My response was intended to explain the concept of the algorithm not present a concrete solution. The algorithm was what he asked about. If you had something positive to add perhaps you should have written something positive to the original poster instead of insulting asides to others.
[sqlite] RE: [RBL] Re: [sqlite] Randomly ordering results
Jay, If you're going to offer advice, at least do some basic research to make sure it's accurate. Firstly, having a column that has a random number in it and sorting by it produces the same output each time you query on it - obviously unsatisfactory and I would have thought, manor from heaven for card counters everywhere!! Secondly, the standard SQLite function random() is actually seeded by the current time and date every time you open a database. Steve p.s. Sorry, been down the pub. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] rg] On Behalf Of Jay Sprenkle Sent: 17 January 2006 14:09 To: sqlite-users@sqlite.org; Clark Christensen Subject: [RBL] Re: [sqlite] Randomly ordering results On 1/16/06, Clark Christensen <[EMAIL PROTECTED]> wrote: > > So, I have to ask, is this the right way to implement such functionality? Is there a better, or more efficient way? The standard method of ordering a list randomly that doesn't return duplicates is to add a column for each of the items. Assign a random number to the column. The return the list sorted by the random number. It's used extensively for shuffling cards, etc. You should be aware that the standard random number functions of most languages return the same sequence of results for the same 'seed' value. So you generally have to do something like set the seed using the current clock so you get a different set of random numbers for each run of your program.
Re: [sqlite] CE locking -- review the code
- Original Message - From: "Simon Posnjak" <[EMAIL PROTECTED]> Robert Simpson wrote: For your convenience, I've updated the code at the previous link I supplied with the latest os_win.c that I am using against the 3.3.1 alpha. Thank you. It works super with my application on WinCE 4.10. (The Lucas Nuno port had some issues with locking - it dead locked my app). Will this code be in the next official release? I sure hope so :) SQLite and embedded platforms go together like peas and carrots (use a Forrest Gump accent for the full effect). A respectable percentage of users of my ADO.NET 2.0 provider are using it on Windows CE. I'm guessing it'll only be a matter of time before someone writes a merge replication module for it and then it'll become the SQL Mobile killer it should be. Robert
Re: [sqlite] Randomly ordering results
On 1/16/06, Clark Christensen <[EMAIL PROTECTED]> wrote: > > So, I have to ask, is this the right way to implement such functionality? Is > there a better, or more efficient way? The standard method of ordering a list randomly that doesn't return duplicates is to add a column for each of the items. Assign a random number to the column. The return the list sorted by the random number. It's used extensively for shuffling cards, etc. You should be aware that the standard random number functions of most languages return the same sequence of results for the same 'seed' value. So you generally have to do something like set the seed using the current clock so you get a different set of random numbers for each run of your program.
Re: [sqlite] Slow sqlite3_step - more details.
On 1/16/06, Carl Jacobs <[EMAIL PROTECTED]> wrote: > The only thing that seems to make it quick again is terminating and > restarting the process - i.e. shutting down and restarting my program. It > seems that no matter what I do it's quick for about 5 queries, and then > seems to fall in a heap. This sounds like memory exhaustion or a memory leak.
Re: [sqlite] Page 2 is never used
> My first guess is that the implementation of > sqlite3OsFileSize() (actually implemented as > "unixFileSize" on line of os_unix.c) is > not working correctly. in my case I have no problem, with debbuger I see the size is correct, 2048 Byte. Following a suggestion from an old mail in this mailinglist I have replaced the type "dev_t" present in os_unix.c with (in my case) "unsigned long" in this way the Device number is copied correctly. Thanks Again Andrea Federico -- ___ Play 100s of games for FREE! http://games.mail.com/
Re: [sqlite] CE locking -- review the code
Robert Simpson wrote: - Original Message - From: "Simon Posnjak" <[EMAIL PROTECTED]> Robert Simpson wrote: I incorporated the locks into the latest CVS version of os_win.c and it's available here: http://sqlite.phxsoftware.com/os_win.c Would i be possible to post the whole src? (Taking the 3.3.1 src and your file sadly does not work). Ticket #1600 contains the latest patch that works against os_win.c and adds locking code. Dr. H did some initial code cleanup and I followed that up with additional code cleanup to suit his coding style, but I suspect he has been too busy as of late to review it. For your convenience, I've updated the code at the previous link I supplied with the latest os_win.c that I am using against the 3.3.1 alpha. Thank you. It works super with my application on WinCE 4.10. (The Lucas Nuno port had some issues with locking - it dead locked my app). Will this code be in the next official release? Regards Simon
[sqlite] bug/misbehavior of the ALTER TABLE statement
I have observed a "bug" in sqlite (3.2.8). when a table is created, and then renamed via the ALTER TABLE statement, the create statement gets adjusted (which is correct), but the new name appears in single quotes. according to sql syntax, table and field names are quoted by double quotes, so this creates subsequent problems for parsing tools and such. can you verify and fix, pls ? to test: create table x(id) select * from sqlite_master (observe the create statememt) alter table x rename to x1 select * from sqlite_master (observe the create statememt again, the name is now 'x1' in quotes)
Re: [sqlite] Page 2 is never used
"Andrea Federico" <[EMAIL PROTECTED]> wrote: > > What operating system are you running? What version > > of SQLite are you using? Did you compile it yourself > > or did you obtain a precompiled version? > > Excuse me I have forgot to insert these important information: > > Sqlite version = 3.3.1 > OS = vxWorks > CPU= powerPC > build from source. > > vxWorks is in pratical like a unix operating system. > My first guess is that the implementation of sqlite3OsFileSize() (actually implemented as "unixFileSize" on line of os_unix.c) is not working correctly. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Page 2 is never used
"Andrea Federico" <[EMAIL PROTECTED]> wrote: > > What operating system are you running? What version > > of SQLite are you using? Did you compile it yourself > > or did you obtain a precompiled version? > > Excuse me I have forgot to insert these important information: > > Sqlite version = 3.3.1 > OS = vxWorks > CPU= powerPC > build from source. > > vxWorks is in pratical like a unix operating system. > Well, 3.3.1 works on unix, so there would appear to be at least some differences. ;-) Perhaps you can help us debug this... . -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Page 2 is never used
> What operating system are you running? What version > of SQLite are you using? Did you compile it yourself > or did you obtain a precompiled version? Excuse me I have forgot to insert these important information: Sqlite version = 3.3.1 OS = vxWorks CPU= powerPC build from source. vxWorks is in pratical like a unix operating system. thank you andrea Federico -- ___ Play 100s of games for FREE! http://games.mail.com/
Re: [sqlite] Page 2 is never used
"Andrea Federico" <[EMAIL PROTECTED]> wrote: > > can you tell me why I obtain an error creating a simple table > in a new database ? > Something is clearly broken. What operating system are you running? What version of SQLite are you using? Did you compile it yourself or did you obtain a precompiled version? -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] Page 2 is never used
Hi sqliteUsers, I have a simple question: can you tell me why I obtain an error creating a simple table in a new database ? if I insert this simple command: sqlite> create table tbl1(one varchar(10), two smallint); sqlite> PRAGMA integrity_check; *** in database main *** Page 2 is never used I obtain "Page 2 is never used" , I don't understand this strange behaviuor and if I dump the database: sqlite> .dump BEGIN TRANSACTION; CREATE TABLE tbl1(one varchar(10), two smallint); COMMIT; I dont' see where is the the problem. If i try to insert some vales into the table I have an error again: sqlite> insert into tbl1 values('hello!',10); SQL error: database disk image is malformed have you some idea to solve the problem? Best Regards Andrea Federico -- ___ Play 100s of games for FREE! http://games.mail.com/
Re: [sqlite] Database design and SQLite
michael munson schrieb: Type refers to an int value that represents an in-server datatype. The main difference is that I realized that I wouldn't necessarily need a row for EVERY property, only if a property is different than the value stored on the parent object. If its the same, it can have no entry and the code will just look to its parent (and its parent's parent, and so on) until it finds the correct value to inheriet. I think that will reduce the number of rows significently, if add a couple of CPU cycles but I think it will make up for it in the querying. Look for typical use cases and try to count the SQL commands you need to execute to solve these use cases. For my application (an interactive GUI based client server application) I have found, that I at most have to get ALL needed data in 3 - 6 queries (where the upper end is critical ...). With SQLite I would expect (local database), that 6 is not criical at all. If you do need too many queries, "bring the data to the queries": change your layout until its ok. Otherwise the interaction of the GUI is not good and the user will not be happy. Organizing the application in tab pages also helps you - checking from one page to another means activity for the user and you can use this time for your SQL queries. If you have tree structure go the Explorer way of Windows - show initially, that the object have children - only if the user checks them, solve the reverenfences and update the GUI. My customer were not happy about this until I showed them, that Windows does it the same way. Then suddenly the customer were quite - even though they did not believe me until they saw it in Windows. Marten