RE: [sqlite] import
dos2unix, unix2dos tools will do the conversion -Original Message- From: Randall [mailto:[EMAIL PROTECTED] Sent: Monday, February 20, 2006 1:33 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] import PS I can do vbscript readline/ writeline as a workaround, not too slow... But I would prefer not. Randall > Hi, > I think it is asc=10, so "LF" rather than "CRLF", but I don't want to have > to re-open , say a 80Mb or 0.5gig text file with a text editor, nor > re-process the lines! > Surely there is an option, or this can be modified in the ".output" > command..? > It is already OK in the ".dump" for instance?.. > Thanks, Randall >> Hello,
Re: [sqlite] import
PS I can do vbscript readline/ writeline as a workaround, not too slow... But I would prefer not. Randall Hi, I think it is asc=10, so "LF" rather than "CRLF", but I don't want to have to re-open , say a 80Mb or 0.5gig text file with a text editor, nor re-process the lines! Surely there is an option, or this can be modified in the ".output" command..? It is already OK in the ".dump" for instance?.. Thanks, Randall Hello,
[sqlite] SQLite 3 file format changed?
Hi, I use PHP PDO_SQLITE extension with SQLite 3.2.8 library, I try to open a database version 3 which I use SQLiteSpy 1.5.2 with SQLite 3.3.4 library, then PDO said error message like "unsupported file format". I tried to use sqlite3 console which is version 3.2.6 for open it, same error message given. I suspected there was file format has been changed again, but I can't found any log on http://sqlite.org/changes.html told about that. What's wrong exactly? -- Firman Wandayandi Never Dreamt Before: http://firman.dotgeek.org/ Wishlist: http://www.amazon.com/gp/registry/1AAN8NZBHW2W9
Re: [sqlite] Managing trees in the database
On Sat, Feb 18, 2006 at 05:34:45PM -0800, Jim Dodgen wrote: > I really miss the oracle "connect by" operator. I first used it for a > postal application back in 1992. I am surprised that this feature has > not made it into to the standard or any other RDBMS. Maybe we should Although useful, Oracle's connect by feature is widely considered to be a flawed design, which is part of the reason few other databases support it. The SQL standard specifies a different approach, which is said to be similar to DB2's "recursive SQL". Hm, here are a bunch of links on "Hierarchical data in RDBMSs", which I just stumbled across: http://troels.arvin.dk/db/rdbms/links/#hierarchical -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/
Re: [sqlite] using sqlite3 in php
You can use the PDO extension. http://php.net/pdo http://www.php.net/manual/en/ref.pdo-sqlite.php Maurício M. Maia www.semjanelas.net jack wu wrote: apparently sqlite3 is not available in php5. i am wondering if there is anyway to work around it. i have a c++ program which works with sqlite3, but i would also like my php script to use the same database. jack.
Re: [sqlite] Managing trees in the database
Jim C. Nasby wrote: On Thu, Feb 16, 2006 at 08:23:43PM +0100, Philipp Kn?sel wrote: SanjayK schrieb: Since SQLite is perfect for use in single-user desktop utility applications and since such applications typically store hierarchial data (tree) in a single table, it would be nice to have support for special features like connect by of oracle. See: http://www.adp-gmbh.ch/ora/sql/connect_by.html -- View this message in context: http://www.nabble.com/Managing-trees-in-the-database-t113.html#a2974277 Sent from the SQLite forum at Nabble.com. Depending on your goals, this concept might give you another solution: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html Yet again, examples of mis-information from MySQL... "The first common task when dealing with hierarchical data is the display of the entire tree, usually with some form of indentation. The most common way of doing this is in pure SQL is through the use of a self-join:" Self-join maybe, but certainly not in the way they're suggesting. Databases that support WITH make this easy (and it's technically a self-join). The problem with what he's proposing is that it silently limits you to (in this case) 4 levels of nesting. If you have more levels than that you'll end up missing data from either the top or bottom of the tree. Of course, if you think "Feb 31" is a valid date, maybe that's OK... Their information about using a nested set model seems accurate, though. Another option is to use 'ltree'. There used to be an implimentation for PostgreSQL, but it looks like it's been removed. http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/ltree/README.ltree?rev=1.7 has more info. I really miss the oracle "connect by" operator. I first used it for a postal application back in 1992. I am surprised that this feature has not made it into to the standard or any other RDBMS. Maybe we should see about adding the extension.
[sqlite] Older sources
Hi there. I would need the sources of an older version (3.1.2) of SQLite. Are they still available for download? Best regards, Manfred ___ Telefonate ohne weitere Kosten vom PC zum PC: http://messenger.yahoo.de
Re: [sqlite] import
Hi, I think it is asc=10, so "LF" rather than "CRLF", but I don't want to have to re-open , say a 80Mb or 0.5gig text file with a text editor, nor re-process the lines! Surely there is an option, or this can be modified in the ".output" command..? It is already OK in the ".dump" for instance?.. Thanks, Randall Hello, this may sound stupid, but is it possible that you get a file with UNIX line endings instead of Windows ones? If that's the case, any decent text editor or a really simple program can do a conversion... cu, Thomas
[sqlite] using sqlite3 in php
apparently sqlite3 is not available in php5. i am wondering if there is anyway to work around it. i have a c++ program which works with sqlite3, but i would also like my php script to use the same database. jack.
Re: [sqlite] import
On Mon, 20 Feb 2006, Randall wrote: [...] I cannot get ".output" to work reliably to give me a correct "EOL", so, say, notepad sees the "EOL". [...] Hello, this may sound stupid, but is it possible that you get a file with UNIX line endings instead of Windows ones? If that's the case, any decent text editor or a really simple program can do a conversion... cu, Thomas
Re: [sqlite] import
Hi, please open this again; I cannot get ".output" to work reliably to give me a correct "EOL", so, say, notepad sees the "EOL". Can anyone help? [Wrapper was not the problem, I believe] Thanks, Randall Sorry; Ignore this for now; I see ".output" works OK; my wrapper was stripping something, I think; Best, Randall
[sqlite] 3.3.4 build with MinGW/MSYS
A brief report on building sqlite-3.3.4 with MinGW/MSYS and tcl84 on WinXP... 1. After configure, a small edit was necessary to the Makefile; for some reason libtool doesn't put .lo objects into .libs subdirectory anymore; there was also an issue reported on the mailing list with stripping the DLL; so I changed the dll target to: dll: sqlite3.dll REAL_LIBOBJ = $(LIBOBJ:%.lo=%.o) sqlite3.dll: $(LIBOBJ) $(TOP)/sqlite3.def dllwrap --dllname sqlite3.dll --def $(TOP)/sqlite3.def $(REAL_LIBOBJ) strip --strip-unneeded sqlite3.dll 2. gcc 3.4.5 blew up trying to compile tclsqlite.c I was able to work around the problem by adding these lines in tclsqlite.c: #undef EXTERN #define EXTERN extern Without these lines, EXTERN is translated to __declspec((dllimport)) which is not what we want, and was giving gcc fits (ICE) for some reason. I also wonder about the purpose of #ifdef BUILD_sqlite #undef TCL_STORAGE_CLASS #define TCL_STORAGE_CLASS DLLEXPORT #endif /* BUILD_sqlite */ since TCL_STORAGE_CLASS doesn't see to be used anywhere in the file. Regards, e
Re: [sqlite] import
Sorry; Ignore this for now; I see ".output" works OK; my wrapper was stripping something, I think; Best, Randall
Re: [sqlite] Disable index?
one simple trick I first started using with oracle and also use with sqlite is to do the following on the where clause for numerics where filda = fldb+0 for strings where flda = fldb||"" this would cause a index on fldb to be ignored during optimization Jim Dennis Cote wrote: On 2/18/06, SanjayK <[EMAIL PROTECTED]> wrote: I am using a virtual tree control to display columns dynamically when needed from the sqlite database. While it works ok for display purposes, in certain other operations, I need to improve the speed. I found that the new sqlite random access (even with prepared/transaction) approach is about 15 times slower than my earlier design where I was using a direct access file with read, seek, etc on Windows. In spite of this, sqlite has several advantages and I am staying with it. I am looking for speed improvement suggestions. Somewhere in a thread I read "disable indexing." I can't find any reference to how to do it in the docs or in this group. How do I disable indexing? I will also appreciate any other suggestions for speed improvement too. Basically, these random access routines are very simple: * Use a prepared read statement to get one column value directly. * I am using a generic read statement that reads all columns desired but I directly get only one column value after the Step. I tried to prepare a single column read statement but that didn't help much over the generic read. Sanjay, The idea of disabling indexing to improve speed only applies to writing the database. SQLite must update each index for every record that is inserted, so if you delete the indexes you reduce the amount of work (and I/O) that must be done to add a record. Removing indexes won't speed up queries, and it might greatly slow them down if you delete an index that is being used to accelerate your query. HTH Dennis Cote
Re: [sqlite] import
Am 19.2.06 um 15:06 schrieb Randall: Hi, Sorry for the basic queries here... If I ".output" with .mode "tabs" , "lines" etc, the resulting text file or csv file cannot be ".imported" by import, as the EOL is not read? Can I fix this easily? Thanks, Randall You didn't tell what you want the re-import for, but if it's just for backup / transfer purposes, you're better off using the ".dump" command to produce an SQL script that you can ".read" again. HTH,
[sqlite] import
Hi, Sorry for the basic queries here... If I ".output" with .mode "tabs" , "lines" etc, the resulting text file or csv file cannot be ".imported" by import, as the EOL is not read? Can I fix this easily? Thanks, Randall
Re: [sqlite] Disable index?
Op zondag 19 februari 2006 13:23, schreef Nemanja Corlija: > On 2/19/06, SanjayK <[EMAIL PROTECTED]> wrote: > > I am using a virtual tree control to display columns dynamically when > > needed from the sqlite database. While it works ok for display purposes, > > in certain other operations, I need to improve the speed. I found that > > the new sqlite random access (even with prepared/transaction) approach is > > about 15 times slower than my earlier design where I was using a direct > > access file with read, seek, etc on Windows. > > > > In spite of this, sqlite has several advantages and I am staying with it. > > I am looking for speed improvement suggestions. Somewhere in a thread I > > read "disable indexing." I can't find any reference to how to do it in > > the docs or in this group. How do I disable indexing? I will also > > appreciate any other suggestions for speed improvement too. > > Disabling index might come in handy in some cases. For example, I was > able to speedup one query a lot by disabling index for sorting. For > some reason SQLite 2 was going back to disk to sort by index even > though it had all data needed in result set already. You can disable > the index like this: > SELECT * FROM foo ORDER BY +bar; I used a lot of the opcode from 2.8.x (forgot exact which one) It did not use an index on sorting, but it sorted the result-set > > where bar is your indexed column. > > If that still doesn't help, you'll have much better chance of getting > some help here on the list if you post more info. Such as SQLite > version used, schema of tables involved in query and query it self. > EXPLAIN's output for that query wouldn't hurt also. If you're using > some wrapper it would be good to run that query from sqlite shell and > see how fast that goes. > > -- > Nemanja Corlija <[EMAIL PROTECTED]> -- Met vriendelijke groet Bert Verhees ROSA Software
Re: [sqlite] Disable index?
On 2/19/06, SanjayK <[EMAIL PROTECTED]> wrote: > > I am using a virtual tree control to display columns dynamically when needed > from the sqlite database. While it works ok for display purposes, in certain > other operations, I need to improve the speed. I found that the new sqlite > random access (even with prepared/transaction) approach is about 15 times > slower than my earlier design where I was using a direct access file with > read, seek, etc on Windows. > > In spite of this, sqlite has several advantages and I am staying with it. I > am looking for speed improvement suggestions. Somewhere in a thread I read > "disable indexing." I can't find any reference to how to do it in the docs > or in this group. How do I disable indexing? I will also appreciate any > other suggestions for speed improvement too. Disabling index might come in handy in some cases. For example, I was able to speedup one query a lot by disabling index for sorting. For some reason SQLite 2 was going back to disk to sort by index even though it had all data needed in result set already. You can disable the index like this: SELECT * FROM foo ORDER BY +bar; where bar is your indexed column. If that still doesn't help, you'll have much better chance of getting some help here on the list if you post more info. Such as SQLite version used, schema of tables involved in query and query it self. EXPLAIN's output for that query wouldn't hurt also. If you're using some wrapper it would be good to run that query from sqlite shell and see how fast that goes. -- Nemanja Corlija <[EMAIL PROTECTED]>
[sqlite] sqlite, odbc, any gotchas?
If read a bit already and see limitations and benefits in SQlite. Our office still wants to use the GUI present in MS-Access. I like the simplicity of maintaining SQLite plus it's rollback features etc. Later, there are aspirations for a webbrowser interface to same database. Anyone have recommendations or suggestions to this scenario: Windows(MS-Access)<---office network--->(sqliteodbc-sqlite)linux Thanks
