Re: [sqlite] How do you combine two SQLite databases?
On Sat, 17 Feb 2007 15:27:25 -0500, you wrote: >Hello, > >I have two SQLite database files, stuff1.db and stuff2.db. Each has three >tables within. I want to combine the two so I have one database file, >stuff.db, with 6 tables. How could I combine the databases? I am aware of >the "attach" command, but this just seemed to create a file with two >sub-databases, each of which has its own tables. (Maybe I used it wrong.) Is >there a specific solution? With the command line program: sqlite3 database1 .dump >fileall.sql sqlite3 database2 .dump >>fileall.sql sqlite3 database3 http://www.sqlite.org/sqlite.html >Thanks! Hope this helps. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problem with .import
On Sat, 10 Feb 2007 12:09:12 -0800 (PST), you wrote:
>On Thu, 28 Sep 2006, Dennis Cote wrote:
>
>> I suspect you may have trailing spaces at the ends of your lines. The
>> .import command isn't very smart about things like that. Your separator is
>> set to one space, not arbitrary whitespace. It there is another separator
>> after the last field it assumes there is another field there (which might
>> be an empty string) as well.
>
> Well, I'm back with the same problem, but a trailing space at the end of a
>record is not the problem.
>
> I wrote data from a table using 'insert' mode so I could drop and recreate
>the table without having to manually re-enter the data. That part worked
>just fine: the new table schema matches the number and type of fields in the
>data file.
>
> However, when I try to import the data into the table I see this error
>message:
>
>sqlite> .import variable.sql variable
>variable.sql line 1: expected 14 columns of data but found 16
>
> The editor is configured like this:
>
>sqlite> .show
> echo: off
> explain: off
> headers: off
> mode: insert
>nullvalue: ""
>output: stdout
>separator: ","
> width:
>
>and the first line of data is:
>
>INSERT INTO variable VALUES('Vegetation','Amounts, types, and uses of plant
>cover.','Habitats','','External','x
>100','Centroid',0,100,0.2,'Strong','Fuzzy Space','Minimum','Min-max');
>
> There are 14 fields and no extra space trailing the final ';'. The schema
>is attached for reference.
>
> What have I gotten wrong this time, please?
This is not a comma delimited values file like .import could
process, but an SQL script. You can execute it like:
sqlite3 databasefilename .read variable.sql
>Rich
--
( Kees Nuyt
)
c[_]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] How do I get MATCH and REGEXP
On Fri, 9 Feb 2007 21:11:31 +0100, Rick wrote: >The manual mentions the support for the MATCH and the REGEXP operators. But >if I use it, it doesn't work. Do I have to do something special to install >those operators? http://www.sqlite.org/lang_expr.html tells: "The REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message. If a user-defined function named "regexp" is added at run-time, that function will be called in order to implement the REGEXP operator. The MATCH operator is a special syntax for the match() user function. The default match() function implementation raises and exception and is not really useful for anything. But extensions can override the match() function with more helpful logic." >Regards, >Rick van der Lans HTH -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?
On Wed, 7 Feb 2007 13:03:09 -0500, you wrote: >I can't get SQLiteExplorer to work with my databases. It always gives >me an "unknown file format" error. I believe it's been quite a while >since it was updated. > >RobR Did you download sqlite3explorer or sqliteexplorer? The latter is meant for sqlite v2.x.x databases, and requires v2.x.x of sqlite.dll and has not been maintained for a long time. Make sure you use sqlite3explorer.exe v2.0 (which is built for sqlite 3.x.x) and put your sqlite3.dll in the same directory. If it fails to work on an existing database, try to create a new one from sqlite3explorer. I use it in combination with sqlite3.dll, version 3.3.12, without any problem. I downloaded it 2006-03-13 and it still works well. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?
On Tue, 6 Feb 2007 15:13:55 -0500, Donald Griggs wrote: >Regarding: "If I want to change data ." > >Sqlite3Explorer is free software, and works as you describe. >I imagine there are several others. I second this wholeheartedly. >http://www.singular.gr/sqlite/ (Not sure if there >will be future releases of this or not) -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Function question
Hi Jim, On Thu, 1 Feb 2007 09:08:44 -0500, you wrote: >From the CLP (i.e., w/o user-defined functions) is there any way to get >the equivalent of the sybase ltrim, rtrim functions? (ltrim/rtrim trim >spaces from the right/left end of a column value.) As far as I can tell load_extension(X,Y) can be used in the command line program. I usually postprocess output by piping it through awk to solve these kinds of problems. sqlite3 databasefile outfile >jim HTH -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Equivalent syntax?
On Wed, 31 Jan 2007 17:30:29 -0500, you wrote: >BTW, what is the concatenation operator? Standard SQL: string || string -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Equivalent syntax?
On Wed, 31 Jan 2007 18:31:20 -0500, you wrote: >Is cast documented on the sqlite website? I couldn't find it. http://www.sqlite.org/lang_expr.html -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UNIQUE constraint on column
Hi Shane, On Wed, 31 Jan 2007 09:29:24 -0500, you wrote: >On 1/31/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: >> "Shane Harrelson" <[EMAIL PROTECTED]> wrote: >> > when i try to insert a row into a table that has a UNIQUE constraint >> > on a column, and I get the SQLITE_CONSTRAINT result code because i'm >> > inserting a duplicate value, is there anyway to determine the rowid of >> > the conflict? >> > >> > looking at the internals of the VDBE, i found that the rowid of the >> > conflicting row is pushed on top of the VDBE stack. >> > >> > if i'm willing to violate the interface, i can dereference the rowid >> > from the internals of the VDBE struct. i'd rather not do this... is >> > there a more formal mechanism for getting this value without having do >> > to do a separate "select" query? something like >> > sqlite3_last_insert_rowid() -- ie. sqlite3_last_conflict_rowid()? >> > >> >> The official way to find the conflicting entry is to do a query. >> >> SELECT rowid FROM table WHERE uniquecolumn=?; >> >> -- >> D. Richard Hipp <[EMAIL PROTECTED]> >> > >Thank you for the reply. I assumed this was most likely the case, and >as I said in my original email, I was hoping to avoid having to do a >seperate select query for the sake of speed... especially since the >value I needed was so tantalizing close in the VDBE struct. The SELECT will be quite fast, chances are the required pages of the unique index will still be in the cache. In my opinion it is always better to write portable code, so I would prefer to rely on generic SQL than on yet another implementation specific API. Just my 2 cents. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Optimizing operations
Hi Alberto, On Fri, 26 Jan 2007 19:58:20 +, you wrote: >Hi > >I am trying to create indexes on some tables. I know the database is >just being used by me, and I have memory to optimize things. I am >trying to optimize things doing > >PRAGMA temp_store = MEMORY >PRAGMA cache_size = 100 >PRAGMA synchrinous = OFF >PRAGMA count_changes = 0 > >Is there any other pragma I can use for efficiency? PRAGMA page_size = 4096; Helps to store longish rows without having to overflow to another page. This pragma should be the first statement to execute when a new database is created. Experiment with various sizes for the best result. PRAGMA auto_vacuum = 0; This is the default, but I'm used to set important options explicitly all the time. To avoid surprises and to remind myself what I'm doing. This pragma should be issued before the first table is created. PRAGMA default_cache_size = 100; Will make the cache size stick to the database, so you don't have to repeat it every time it is opened. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: How to optimize a select that gets 17 rows from a 700k row DB (via perl using DBI)?
sing the page such as 3 am) that transfers the data from the >current DB to the historical, leaving only the most recent observation for >each machine. > >Jay: > >The closer to real-time, the better. The most often a cron job can run >under Linux is minutely, and minutely is pretty good. I guess I could >have the summary process occur at the end of the script that polls the >machines. It could generate static HTML, which would presumably make the >page load super fast. However, under the current regime, the process of >creating that summary is going to take at least 10 seconds. 40 seconds >for polling + 10 seconds for summarizing=50 seconds, and that number is >only going to get bigger! So I'll have to figure out a better table >structure anyway. > >Additional thoughts: > >In general, I think splitting the tables up is the way to go. Any further >comments/suggestions appreciated! > >Jonathan -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Using sqlite.exe
Hi Michael, On Sat, 30 Dec 2006 15:22:13 -, you wrote: > Thank you Kees - this works! I'm glad it does. >I have been running sqlite3.exe simply by clicking on its icon in My >Computer and I wrongly assumed that doing this would automatically make the >current directory the one where sqlite3.exe is found. When I come to use it >from within my Delphi program I shall ensure that I "cd" to the appropriate >directory first. > >Just a suggestion for the programmers, but it would be helpful if >sqlite3.exe came back with a response like "Database opened" or >"Database created in directory " - it would give those of us >who don't often use command line programs a bit of a clue what's going on. >I have forgotten a lot since my DOS days! Though it is a nice suggestion I doubt anyone would care to write the patch, because it only would be helpful the first time one uses the sqlite commandline shell. Once you've set up your development environment, you'll never have to think about it again. If anyone wants to make an effort, just make sure to only output those messages in the same way as the version information: suppress if redirection is active. >Thanks once again - I can get on with my work now! You're welcome, good luck, have fun. >Michael Hooker > >-Original Message- >From: Kees Nuyt [mailto:[EMAIL PROTECTED] >Sent: 30 December 2006 12:38 >To: sqlite-users@sqlite.org >Subject: Re: [sqlite] Using sqlite.exe > >Make that > C:\sqlite3\sqlite3.exe C:\sqlite3\BaseStation.sqb >or you will end up creating the database in what happens to be >the current directory, > >and then execute > .tables >or > .schema >to check what's in there. >-- > ( Kees Nuyt -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Using sqlite.exe
On Fri, 29 Dec 2006 18:46:58 -0600, Michael Hooker wrote: >ya, I've found getting to stuff from within the shell program tricky >at times. Here is the easiest way, esp. since your db is in the same >directory as the .exe (assuming that directory to be C:\sqlite3... >replace as needed) > >C:\sqlite3\sqlite3.exe BaseStation.sqb >.. >that will launch sqlite3 as well as open up your database all in one >command. Make that C:\sqlite3\sqlite3.exe C:\sqlite3\BaseStation.sqb or you will end up creating the database in what happens to be the current directory, and then execute .tables or .schema to check what's in there. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Creating a database from inside a program
On Fri, 29 Dec 2006 12:33:46 -0500, you wrote: > Sqlite3 newdatabase.db .read schemafile.txt > > But, when I issue this command from the DOS prompt, > it gives me an error message saying that there > is no command named "read". Try input redirection: Sqlite3 newdatabase.db
Re: [sqlite] VB wrappers
tOn Sat, 23 Dec 2006 20:55:16 -, you wrote: >Have come to the conclusion that the only thing that is >really important in speeding up SQLite inserts is to wrap >it in a transaction. Yes, it is. >Things like: >PRAGMA synchronous = OFF; >PRAGMA encoding='UTF-8'; >PRAGMA page_size=4096 or whatever number >Don't seem to make a difference. page_size is important when you have many bytes per row, think of blobs or long texts. Also when you have very many rows, as the maximum number of pages is not endless. >Are there any other things that could speed up inserts? > >RBS PRAGMA cache_size PRAGMA default_cache_size especially when you are building large indexes. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] delayed (batch) transactions
On Tue, 19 Dec 2006 13:52:19 -0500, you wrote: >I've started to use SQLite and it works fine - except for performance. The >application gets requests (possibly from many users) and does a few >transactions on the database. (eg. create a context for the request; later >it may be updated and when the user releases the record is deleted.) > >I tried all three sync methods and with FULL, NORMAL and OFF. For 100 >transactions (request / check / release) I see 800 open/close (in 1200msec), >5800 lock/unlock (22 msec) and 600 (FULL - 17,000 msec), 400 (NORMAL - >12,000 msec) or 0 (OFF) file flush operations. The latter is really slow but >I understand it. (Have to wait for the actual disk operation to complete). > >For this particular application it would NOT be a problem to lose like 2-5 >seconds of transactions. I wonder if it is possible to tell SQLite to "hold >off" the transactions, ACCUMMULATE them until a certain time (or if cache >memory is exhausted - which is not yet the case as we have a modest >database), then make a BIG COMMIT (ie. all previous transactions committed >or none). That way it's still transactional (ie. no currupted database - I >really don't want to use sync = OFF) but the I/O performance wouldnt slow >down serving requests. > >Anybody has done that already? If yes, where can I find such modified SQLite >source? >I saw that all file I/O related calls are in two files: pager.c and >vdbeaux.c so they are already well isolated and relatively easy to >understand. > >Any help for such "delayed" transaction method in SQLite? >(I tried to find any relevant message in the email archives but came up >empty.) > >Thanks, > >Laszlo Elteto >CISSP, System Architect >SafeNet, Inc. I wonder if shared cache would help you? http://www.sqlite.org/sharedcache.html In general, in a high concurrency environment sqlite might not be a suitable solution. http://www.sqlite.org/whentouse.html -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Transpose table
On Thu, 14 Dec 2006 09:53:22 - (GMT), you wrote: >Hi Denis, > >Yes, it is a one-off action and the only purpose is to present the data >into and Excel sheet in a more readable way. >I had done your suggestion in VBA, but I thought it was a bit slow >and wondered if there was a better way. If Excel is the target anyway I guess the fasted way to do this is to use the transpose option of the paste-special function in Excel itself. >I have just found a possible way to do this and maybe it is faster. >Say I have a table with an ID column and 3 other columns. >The data in these other columns need to be grouped by ID number, so > >ID col1 col2 col3 > >would become: > >ID col1_1 col2_1 col_1 col1_2 col2_2 col3_2 col1_3 col2_3 col3_3 > >etc. where the maximum number of fields will be determined by the >maximum number of records for one ID number > >Now I found that if I do: >select >ID, >col1, >col2, >col3 >from >table >group by >ID > >Then it will always pick up the row that comes last in the group of >ID numbers. This might actually be faster than doing a subquery with MAX. > >Now if I run the above and move the data to a new table, say table2 and >then run a query like this: > >select >t1.ID, >t1.col1, >t1.col2, >t1.col3 >from >table1 t1 inner join table2 t2 on >(t1.ID = t2.ID) >where >t1.col1 < t2.col1 >group by >t1.ID > >Then I will get the rows (if there was a row left)in the ID group >that comes second from last, so > >ID >1 >1 >1 >1 < will get this one >1 > >If I keep repeating this in a VBA loop and then join the tables I would >get my output. Not sure it is faster, but I think it might. >Will see. > > >RBS > >> Hi RBS! >> >> If I understood you correctly you need a tool to transform these data >> just once? >> So there is a pseudocode describing one of possible approaches. To >> convenient transformation SQLite is not enough for me, I suggest to use >> any script language like Lua, Ptython, etc. >> >> 1) With a statement >> SELECT COUNT(ID) AS counter FROM old GROUP BY ID ORDER BY counter DESC >> LIMIT 1 >> Determine max number of a values >> >> 2) construct create table statement >> CREATE TABLE new( >> ID INTEGER NOT NULL UNIQUE >> for n=1, maxVal >> ", value TEXT" >> end >> ); >> and execute it >> >> 3) then navigate through 'old' table, create statements for insert data >> to 'new' >> >> >> >> But please be sure that you need exactly such transformation. It is a >> _denormalization_, almost anytime people try to perform conversion >> exactly as you describe but in reverse direction :) >> >> With a 'new' table many operation, such as adding another one value for >> ID = 1, will lead to ALTER TABLE ADD COLUMN, etc. You will come away >> from SQL logic. >> >> Regards, Denis >> >> -Original Message- >> From: RB Smissaert [mailto:[EMAIL PROTECTED] >> Sent: Thursday, December 14, 2006 10:39 AM >> To: sqlite-users@sqlite.org >> Subject: RE: [sqlite] Transpose table >> >> >> The example I gave shows exactly what I need to do. >> I have a column of ID numbers with duplicates. I have to make this >> column hold only unique ID numbers by moving the values to the first row >> where that ID number appears, with that increasing the number of >> columns. Hope this makes it a clearer. >> >> RBS >> >> -Original Message- >> From: Darren Duncan [mailto:[EMAIL PROTECTED] >> Sent: 14 December 2006 06:59 >> To: sqlite-users@sqlite.org >> Subject: Re: [sqlite] Transpose table >> >> Can you please provide a use case for your example, so we know what >> you're trying to accomplish? That should help us to help you better. >> -- Darren Duncan >> >> At 12:08 AM + 12/14/06, RB Smissaert wrote: >>>I am moving my code away from VBA and transferring it to SQL. There is >>>one particular routine where I haven't found a good replacement >> for >>>and that is to transpose a table from a vertical layout to a horizontal >> one, >> -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] GROUP BY in SQLite
Hi Bart, First of all, you clearly try to start a new subject here, so please don't reply to something completely different, but start a new thread. TIA. On Thu, 14 Dec 2006 12:15:57 - (GMT), you wrote: >Just wondering about the implementation of GROUP BY in SQLite. >It looks I can do: > >select >field1, >field2, >field3 >from >table >group by >field1 > >and I will get the last row in the field1 group. In SQL there isn't something like a "last row", unless you specify ORDER BY. You get "some row", next time you run the query you might get another one. >This is fine and I can use that, but I thought that the SQL standard >was that all non-aggregate fields should be in the GROUP BY clause. That's right. So if you value your application you can't use it, even though sqlite doesn't throw an error at the moment. >I just tried it in Interbase and the above construction >indeed doesn't work with the error: > >SQL error code = -104, invalid column reference > >Runs fine though in SQLite. > >Is this a known feature? I wouldn't call it a feature, and you'd better not rely on this behaviour. >RBS HTH -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqllite ddb from win to linux
On Wed, 13 Dec 2006 20:33:29 +0100, you wrote: >The page where this is documented : > http://www.sqlite.org/sqlite.html >is quite hard to find, the only link i found is on the > http://www.sqlite.org/download.html >page, next to the "Precompiled Binaries For Windows" >sqlite-3_3_8.zip link. Which is very appropriate, but a bit >modest ;) Oops, there's another link on http://www.sqlite.org/quickstart.html as well: "Additional documentation is available here " -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: File Syste
On Wed, 13 Dec 2006 13:02:37 -0400, you wrote: > I mean the SQLite Core API, something like Berkeley DB. > I'd like to use SQLite B+tree API. It has quite recently been discussed, you may want to try to search the mailing list archives. The conclusion was: the B+tree API is usable but it is easy to shoot yourself in the foot. http://www.sqlite.org/arch.html might be a good starting point for further research. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqllite ddb from win to linux
On Tue, 12 Dec 2006 19:25:16 +0100, you wrote: >ok - I was trying to do somthing like this, but I could not find any sample? sqlite3 olddatabasefile .dump >transportfile [transfer transportfile in binary mode] sqlite3 newdatabasefile On 12/12/06, jose isaias cabrera <[EMAIL PROTECTED]> wrote: >> >> "Rob Coenen" wrote, >> >> >> > hello all, >> > >> > I have been using TRAC for Windows and I need to restore the TRAC wiki >> on >> > a >> > Linux machine. >> > >> > The integrated Trac WIKI is stored in a sqlite file, it seems as sqlite >> > version 3 (judging by opening the .db file with a HEX editor, the header >> > mentions sqlite 3). >> > >> > I can read the file on Window with sqlite but I cannot read the same >> file >> > when I copy it to my Linux machine. It says that the file is not a >> > database >> > or that it is encrypted. >> > >> > Any help here? >> > >> The first thing that comes to mind is to dump the database to text and >> then, >> open a new one in unix and import it. >> >> just thinking... >> >> >> >> - >> To unsubscribe, send email to [EMAIL PROTECTED] >> >> - >> >> -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Operation is not allowed when the object is closed
On Sat, 9 Dec 2006 23:38:47 -, you wrote: > On Error Resume Next > Do While SQLiteConn.State = slStateClosed > SQLiteConn.Open > Loop I has been quite some time since I wrote VB, but I'll give it a try. You seem to assume that SQLiteConn.Open raises an error when it fails. If it doesn't raise an VBerror, you don't detect something is wrong. SQLiteConn.State will not equal slStateClosed but the error status, so the loops ends and you stop trying to open, without having opened the database. HTH -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Operation is not allowed when the object is closed
On Sat, 9 Dec 2006 22:24:59 -, you wrote: > Keep getting this error when trying to create a table > in SQLite. There is a valid connection object and it > is open and I have no idea what is causing this: > > Operation is not allowed when the object is closed. > Error number: 3704 Please show us some code. Did you check for any errors on the connection statement? >Thanks for any advice > >RBS > > > >- >To unsubscribe, send email to [EMAIL PROTECTED] >----- -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Unicode Help
On Fri, 8 Dec 2006 15:54:45 +, you wrote:
> How do you set Notepad to Ecnoding = Unicode.
> I cant see an option for that ?
Perhaps it listens to a BOM?
http://unicode.org/unicode/faq/utf_bom.html#22
It would mean you have to initialize your textfile before
editing with some utility like awk:
BOF file initutf.cmd linewrapped by mail !!
@echo off
echo Build a few common BOM prefixed UTF files
echo BOM for UTF-8
awk "BEGIN{printf(\"\xEF\xBB\xBFUTF-8\"); exit 0}" >utf8.txt
echo BOM for UTF-16 Little Endian
awk
"BEGIN{printf(\"\xFF\xFE\x55\x00\x54\x00\x46\x00\x2D\x00\x31\x00\x36\x00\x4C\x00\x45\x00\");
exit 0}" >utf16LE.txt
echo BOM for UTF-16 Big Endian
awk
"BEGIN{printf(\"\xFE\xFF\x00\x55\x00\x54\x00\x46\x00\x2D\x00\x31\x00\x36\x00\x42\x00\x45\");
exit 0}" >utf16BE.txt
EOF file initutf.cmd ----
(tested, works with notepad.exe v5.1.2600.2180 Dutch)
HTH
--
( Kees Nuyt
)
c[_]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] semi corrupt db
On Tue, 5 Dec 2006 08:06:41 -0700 (MST), you wrote: >what would be the best method to dump this db into another one to recover? sqlite olddb .dump | sqlite newdb I have no idea what could cause the segfault, except perhaps a version difference between the sqlite command program and the sqlite library. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Saving tables
On Mon, 27 Nov 2006 12:43:24 -0800 (PST), you wrote: > >I typed exactly what you typed there and i get >SQL error: no such table: bar >my command window doesnt have : "sqlite3 foo.sqlite" like yours If you don't include a database name after the sqlite3 command, sqlite uses the "memory" database, which ceases to exist after you exit the program. So, use the following commamnd to create and open your database: sqlite3 mydatabase.db3 and then issue the SQL commands at the prompt to create and populate the table. Every time you want to reuse that database, you have to open it in the same way: sqlite3 mydatabase.db3 HTH -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] select from commandprompt with output to file
Hi RBS, On Thu, 16 Nov 2006 19:56:36 -, you wrote: >How do I do this: >From the command prompt issue a simple select query to a specified database >and direct the output to a file. >I can see there is the .output FILENAME option, but nothing seems to happen. >.output stdout works fine with output to the screen. >Must be overlooking something simple here. > >RBS .output FILENAME doesn't do anything by itself, it only sets the name of the file any follwing commands will write output to. Once you execute a query afterwards the file should be created. If you want the file in another directory than the current one, use slashes in the path, not backslashes. The following code works for me: C:\DATA\opt\test>sqlite3 tmp/test.db3 SQLite version 3.3.8 Enter ".help" for instructions sqlite> .mode list sqlite> .headers off sqlite> .output tmp/master.html sqlite> SELECT 'Database schema'; sqlite> SELECT 'Report on database schema TESTtables'; sqlite> .mode html sqlite> .headers on sqlite> select * from sqlite_master order by type,name; sqlite> .mode list sqlite> .headers off sqlite> SELECT ''; sqlite> .q C:\DATA\opt\test> -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Importing text file via .bat file
On Wed, 15 Nov 2006 19:46:32 -, you wrote: >Kees, > >Just one other thing needed. >In the .sql file is there a way to notify VB that the text import is >finished? I run the .bat file now from VBS, so it won't be visible. >I couldn't see anything suitable in the dot commands to tell VB. > >RBS It is done when the .bat returns to VBS, I guess. You could try to execute like this (just guessing, i never use VBS): start /w ReadCode.bat Or add this to the sql script: at the top: if exist signal.txt del signal.txt at the bottom: .output signal.txt select SUBJECT_TYPE from ReadCode limit 1; and detect the presence of the file signal.txt in your VBS script. Or do everything in your .bat (or better, .cmd). -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Importing text file via .bat file
On Wed, 15 Nov 2006 19:15:38 -, you wrote: >Hi Kees, > >Thanks a lot for that. All working perfect now. >I think all this should be on the SQLite site as >it took me a long time to get this fixed. http://www.sqlite.org/sqlite.html and the .help command taught me the commands, http://www.sqlite.org/lang.html the SQL as understood by sqlite; the rest is straight shell scripting, redirecting and piping. Which is nasty at first, but quite powerful once you get used to it. Worth to learn, really. But not really on topic on the sqlite site. But here are some hints: On windows NT4, 2000, XP and 2003 you're better off with writing .cmd scripts instead of .bat. .cmd is interpreted by CMD.EXE, which is much more powerful than command.exe, which is (usually) interpreted by the legacy command.exe command shell. Add some tools like make (for process management) and gawk (for data cleaning, input filtering and transformations), and there's your database factory ;) CMD.EXE even allows you to use forward slashes, you just have to put paths and filenames between double quotes as in "disk:/path/filename.extension". For easy table browsing and testing I use the excellent Sqlite3Explorer by Mike Cariotoglou (see http://www.sqlite.org/cvstrac/wiki?p=ManagementTools for a pointer), which even includes a query editor and a report generator. >Nogmaals bedankt. Veel plezier! >RBS -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Importing text file via .bat file
On Tue, 14 Nov 2006 23:44:12 -, you wrote: >Have figure out now what the quickest way is to move data from Interbase to >a SQLite db file: >IB to ADO recordset >Recordset to text >Import the text file with the .import command. > >Now I am trying to figure out how to automate the last step with a .bat >file. >What I got sofar is: >Have a SQL file with: > >create table ReadCode > ( >SUBJECT_TYPE varchar(5), >READ_CODE varchar(5), >TERM30 varchar(30), >TERM60 varchar(60) > ); > >Run a .bat file with this: > >cd C:\SQLite >del ReadCode.db >type ReadCode.sql | sqlite3 ReadCode.db > >Then run from the command prompt: > >Cd C:\SQLite (press return) >SQLite3 ReadCode.db (press return) >.mode csv(press return) >.import ReadCode.txt ReadCode (press return) > >This runs nice and quick, but how would I combine all this in one .bat file >or how could I run this all from VB? I know very little about .bat files, >but I would think that somehow it must be possible. >Thanks for any assistance. > > >RBS Input scripts for the sqlite command line utility aren't restricted to SQL, you can also put 'dot commands' in it. Try this (some day you may be working on an existing database instead of deleting it first, so I would drop the table first): --- file ReadCode.sql begin --- drop table if exists ReadCode; create table ReadCode ( SUBJECT_TYPE varchar(5), READ_CODEvarchar(5), TERM30 varchar(30), TERM60 varchar(60) ); .mode csv .import ReadCode.txt ReadCode --- file ReadCode.sql end --- And this is your shell script: --- file ReadCode.bat begin --- cd C:\SQLite del ReadCode.db SQLite3 ReadCode.db
Re: [sqlite] LEFT OUTER JOIN + INNER JOIN problem
On Tue, 14 Nov 2006 13:17:17 +0100, you wrote: >Gábor Farkas wrote: > >> i think it should only output the first row ( "5|admin|1|5|1|john" ) > >tried what you describe and get the answer you expect, not the one you got: [...] >using debian 3.1, sqlite3 3.3.8, >the same also on Mac OS X, sqlite3 3.3.7... Same here, MS Windows XP Pro, sqlite 3.3.8 -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] command-line shell handling of errors.
Hi Richard, On Thu, 26 Oct 2006 17:23:17 +, you wrote: > So the question: > Who will be adversely effected by the new error behavior > in the sqlite command-line shell? Not really. I prefer the new behaviour. At the moment I have to jump through hoops and scan my make logs to detect errors and signal them. > Who is using the sqlite command-line shell in scripts in > such a way that the script will no longer work with the > new behaviors? It might break some of my scripts, but rightly so. I'll be glad to repair them. > Do I need to change the behavior back to the way it was > and provide a command-line option to provoke the new > (more rational) behavior? Not really needed, but it would be nice to have a choice to explicitly suppress errors or explicitly provoke the new behaviour, either way. As my makefiles use a macro $(SQLITE) instead of the program name itself it is easy to add any switch anyway. Your approach in the remarks of said ticket is right, in my view. Thank you for any abort-on-error solution and the beautiful, consistent product sqlite is! -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] hexadecimal
Hi Lloyd, On Wed, 25 Oct 2006 20:11:49 +0530, you wrote: > Hi list, > can I insert a hexadecimal value to an integer field? Yes. > if yes How can do that? Convert it to an integer in your host language first. The X'hexstring' syntax is only for BLOBs. > Thanks, > Lloyd -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How do i do this UPDATE in SQLite?
On Mon, 16 Oct 2006 09:53:19 +0800, you wrote:
>update i set val=qty*q.price
>from salesrecord i
>inner join historicalprice q on i.itemid=q.itemid
I would use a correlated subquery which returns one column,
instead of the join. I don't think you can use aliasses there.
update salesrecord set val = qty * (select price from
historicalprice where historicalprice.itemid ==
salesrecord.itemid); {untested}
It depends on the version if you can use this.
>thanks.
>
>Radzi.
--
( Kees Nuyt
)
c[_]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] [sqlite]: How does a transaction work
On 13 Oct 2006 13:01:28 -, you wrote: >Hi, >I wanted to know how does a transaction work.. > >In the following example shown below, where is the transaction updated. Is it >in the memory or some temp file or is it directly written to the database >file. Please give more internal details on this. > >sqlite_exec_printf(pDBHandle,"begin transaction;", 0, 0, NULL); > >sqlite_exec_printf(pDBHandle, "update table1 set c1 = 1",0,0,0,NULL); >sqlite_exec_printf(pDBHandle, "update table2 set c1 = 1",0,0,0,NULL); >sqlite_exec_printf(pDBHandle, "update table3 set c1 = 1",0,0,0,NULL); >sqlite_exec_printf(pDBHandle, "update table4 set c1 = 1",0,0,0,NULL); > >sqlite_exec_printf(pDBHandle,"COMMIT;", 0, 0, NULL); These pages might give you the insight you need: http://www.sqlite.org/arch.html (Page Cache) http://www.sqlite.org/opcode.html (Transaction) http://www.sqlite.org/lockingv3.html (Rollback Journal) There are many more docs available in: http://www.sqlite.org/docs.html Enjoy! -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Helloo...
On Thu, 12 Oct 2006 15:24:22 -0400, you wrote: > PD: I think it could be implement in SQLite to but > I dont know if it support UNION in selects... It does. http://www.sqlite.org/lang_select.html -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] PK and rowid
On Wed, 11 Oct 2006 00:19:28 -0700 (PDT), you wrote: >Hi, > > If I declare my column as, "uniqId integer primary key", > now if I say something like, > select * from tbl1 where uniqId=x; > > Will the uniqId be same as rowid, making my table look ups > faster as I am using row id only. If not whats the way to > assign my uniqId to the row id so that my lookups would be > faster. http://www.sqlite.org/lang_createtable.html by drh> "Specifying a PRIMARY KEY normally just creates a UNIQUE index on the corresponding columns. However, if primary key is on a single column that has datatype INTEGER, then that column is used internally as the actual key of the B-Tree for the table. This means that the column may only hold unique integer values. (Except for this one case, SQLite ignores the datatype specification of columns and allows any kind of data to be put in a column regardless of its declared datatype.) If a table does not have an INTEGER PRIMARY KEY column, then the B-Tree key will be a automatically generated integer. The B-Tree key for a row can always be accessed using one of the special names "ROWID", "OID", or "_ROWID_". This is true regardless of whether or not there is an INTEGER PRIMARY KEY. An INTEGER PRIMARY KEY column can also include the keyword AUTOINCREMENT. The AUTOINCREMENT keyword modified the way that B-Tree keys are automatically generated. Additional detail on automatic B-Tree key generation is available separately." > ... > Chetana. I hope this helps. -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Memory mapped db
On Thu, 28 Sep 2006 15:32:03 +0200, you wrote: > >Has anyone tested an sqlite which memory-maps the db-file into ram? Is this >an old (maybe bad idea :-) ? I've looked over the source and it seems that >read and write operations are used through a singled interface, so it maybe >possible to implement it without too much trouble... > >Any comments are welcome :-) > > Michael SQLite already does that, it is called caching, and you can set the both the page size and the number of pages in cache with the PRAGMA statement. http://www.sqlite.org/pragma.html#modify Read about SQLite internals for a more detailed explanation. http://www.sqlite.org/arch.html The page cache can be shared by mutiple daatabase connections: http://www.sqlite.org/sharedcache.html -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to get version of an SQLIte file
On Thu, 28 Sep 2006 09:35:54 +0200, you wrote: > Le Thu, 28 Sep 2006 00:01:08 +0200, > Kees Nuyt <[EMAIL PROTECTED]> a écrit : > >> The first 15 bytes of the sqlite database file tell you which >> file format it is. The file format doesn't change that often, so >> it can be compatible with several software versions. > > Thanks for your answer. Do you know the different available > values ofthis 15 bytes? No, except for the software version I currently use 3.3.7, the the file signature is the C string "SQLite format 3". BTW, I wonder why do you ask? It is much faster to test that yourself than ask someone else -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to get version of an SQLIte file
On Wed, 27 Sep 2006 14:06:50 +0200, you wrote: >Hello >I'm trying to develop an application with C# Mono and SQLite and I have >a problem to know wich SQLIte version is the file I want open. > >Do you know a way to get this version before I open the file? The first 15 bytes of the sqlite database file tell you which file format it is. The file format doesn't change that often, so it can be compatible with several software versions. >LordPhoenix > >PS : Sorry for my English I'm french and don't speak this >language very often :) Many US citizens write worse English than you do ;) -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite index Idea for DBF
On Fri, 25 Aug 2006 13:58:09 +0600, you wrote: > Hi All, > > I want to create index in “dbf” using SQLite index idea. > > I will give a little detail of my project. > I am using SQLite for one of my project. I have dbf files > with 30 million or 50 million records. And at the first > stage I tried to covert the data in “db” and then > apply different operations. > > But because of the slow performance in case of converting > data into dbf to db I changed the design and now I did > some modifications and I am directly reading the data > from dbf files. > > So now I want to create index in “dbf” using SQLite index Idea. > > Can anyone guide me how to start. What type of database is "dbf" ? What type of database is "db" ? If "dbf" is dBase, FoxBase or something like that it doesn't make much sense to have data in that database indexed by SQLite. You'd better use the index of that given database, or speed up the conversion process. >Regards, > >Manzoor Ilahi -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Query Execution speed.
On Wed, 9 Aug 2006 18:51:30 +0600, you wrote: >hi All, > >I have to use SQLite for one of my project as ":memory:" db. [snip] >which PRAGMA statements can improve the query speed. >the main queries are, INSERT and SELECT with joins. > >I have just tried the following PRAGMA > >sqlite3_exec(db, "PRAGMA temp_store=2", NULL, NULL, NULL); > sqlite3_exec(db, "PRAGMA synchronous=0", NULL, NULL, NULL); > sqlite3_exec(db, "PRAGMA count_changes=OFF", NULL, NULL, NULL); > sqlite3_exec(db, "pragma default_cache_size =65536", NULL, NULL,NULL); That seems Ok to me. > sqlite3_exec(db, "pragma cache_size = 8192", NULL, NULL, NULL); That is weird, why would you set a large default_cache_size but a much smaller cache_size for the current connection? For a memory database that wouldn't make any difference though. >can someone guide me which PRAGMA statements are useful for >speed improvement and what values I need to set for those. >like "pragma default_cache_size =?" It might help to PRAGMA the page_size as well. You have only one chance to do that: at databasefile creation time, just before you CREATE your first table, and it should reflect the optimal size for your platform. On windows, it should be the same as the actual cluster size of your filesystem. For a memory database that wouldn't make much difference. >I have a 4G Physical Memory. That's a lot to play with. >//-- >one last thing, I have tested the code written by "Dennis Cote" >Wed, 03 May 2006 " performance v2 and V3 " > >I found that Version 2.8 was much Faster than 3.6 using ":memory:", 30 >Seconds and 60 seconds in case of 2.8 and 3.6 respectively. >can I use 2.8 in my project when i have a huge amount of data to handle. > >Thanks >Regards, > >Manzoor Ilahi Tamimy The biggest gain will be in your database structure and handling: - optimize your schema - don't store anything you don't really need - define indexes for every column you will join on - be very critical on your joins - experiment with table order in joins - sometimes a union of two or more inner joins is better than one outer join - experiment - read the page about index usage - use EXPLAIN Many of these techniques are discussed on the sqlite site, it really pays off to try to read all of it. -- ( Kees Nuyt ) c[_]
Re: [sqlite] date data types
On Fri, 04 Aug 2006 14:42:01 +0200, you wrote: >[EMAIL PROTECTED] wrote: [...] >>And it would go against the >>basic philosophy of SQLite. >> >> >in which way? > >but as far as I am concerned, I'll be happy if I can get the original >type declaration for the column by querying the database. is there a >way to do so? I've looked into the doc (for example, >"information_schema") but I found nothing which was working for me... Will PRAGMA table_info(tablename); do? >thanks and regards, >MF -- ( Kees Nuyt ) c[_]
Re: [sqlite] Reading the same table from two threads
On Wed, 26 Jul 2006 16:02:00 -0500, Dennis Jenkins wrote: >[EMAIL PROTECTED] wrote: >> It has been suggested that I add a mutex to every SQLite >> database connection. This would cause access to a database >> connection to automatically serialize even when two or more >> threads try to use that connection at once, thus preventing >> problems such as the above. >> >> The downside is the (minimal) additional overhead of acquiring >> and releasing a mutex on each API call. There will also be >> some changes to the OS-layer backend which will break >> private ports to unusual OSes. >> >> Thoughts? >> >> -- >> D. Richard Hipp <[EMAIL PROTECTED]> >IMHO: > >Don't do it. Those of us that want to use threads should create our own >threaded sqlite wrappers. If i understand Richard well, there would be a mutex for serializing calls to the same database connection. So nothing would change if each thread opened its own instance. As it doesn't affect my applications in any way I'm not particularly in favour or against it, but i can imagine that life would be easier for many other people if such a feature were available. In order to keep sqlite lean and mean where needed I think it should be a compile time option. -- ( Kees Nuyt ) c[_]
Re: [sqlite] temp_store=1 performance on Mac OS X vs. Windows
On Wed, 26 Jul 2006 20:56:16 +0200, you wrote: >Because sqlite has to deal with so many platforms, it shouldn't >make as few assumptions as possible about the 'correctness' ... Oops, that should of course read: Because sqlite has to deal with so many platforms, it should make as few assumptions as possible about the 'correctness' ... -- ( Kees Nuyt ) c[_]
Re: [sqlite] temp_store=1 performance on Mac OS X vs. Windows
On Tue, 25 Jul 2006 23:13:31 +0200, you wrote: >Hi, > >we just found that when using file-based temporary storage (compile >time macro definition TEMP_STORE=1) vs. memory-based temporary >storage (TEMP_STORE=2), on Mac OS X, the performance almost doesn't >degrade at all, whereas on Windows, we're getting a huge performance >penalty when using file-based temporary storage. > >We are compiling sqlite 3.3.6 ourselves using pretty much the stock >compile time options, except for TEMP_STORE and THREAD_SAFE=1. >We also found that apparently, database performance with TEMP_STORE=1 >is especially slow when running on Windows XP (SP2). > >OS Versions where Mac OS X 10.4.4 and Windows XP (SP2). >Both machines were equipped with > 1 GB of RAM, but since we're using >file temp storage, memory usage is not really a limit here. Hard disk >performance should be about equal on both machines. You don't mention the settings / optimisations of the filesystems, nor the amount of memory the OS allows the filesystem for caching. At least in Windows, server and desktop versions have different optimisations. In my opinion this might make a significant difference. -- ( Kees Nuyt ) c[_]
Re: [sqlite] temp_store=1 performance on Mac OS X vs. Windows
On Wed, 26 Jul 2006 15:23:04 +0100 (BST), you wrote: >Looking at this reminded me. What is the point of deleting the rollback >log? Why not just truncate it when committing a transaction, and deleting >it only when closing the database? My guess: Because sqlite has to deal with so many platforms, it shouldn't make as few assumptions as possible about the 'correctness' of any filesystem. Deleting the journal file would have a bigger chance to actually cause a directory update on harddisk than a mere reset of filesize. Mind powerfailures and system panics. To be sure, in your construction a simple truncate wouldn't be enough, additionaly the contents of the journal should be invalidated in some way, with a guaranteed flush to the filesystem. As the journal is our last resort for database consistency (thus the ACID nature of sqlite), it is better to be safe than sorry. >The benefits could be: >- Improved performance: > - No delete of the rollback log to cause synchronous IO on the > directory. There is the consistency culprit. > - Only need to create the rollback log when it doesn't exist. Else, > just the existing empty one. > >- Would play better with Virus scanners on Windows. No deleting of the > rollback log would cause no file locked errors until we're closing the > database. IMHO we'd better make the virusscanners behave. >So long as the rollback code handled the case of the rollback journal not >being present, this should be backward compatible with existing versions. True. >Christian -- ( Kees Nuyt ) c[_]
Re: [sqlite] finding the groups which have some sort of mising transaction
On Thu, 20 Jul 2006 15:44:32 +1000, you wrote: >HI, all > >The following 2 queries (have the same result) are about to find some groups >which have some sort of missing transactions. Please advise which one would >have better performance. Thank you in advance! Why not measure both cases with: time sqlite yourdatabase SELECT docketno >FROM cntt_sales2 >GROUP BY docketno >HAVING MAX(CASE WHEN datacmd = 'RCP' THEN 1 ELSE 0 END) = 0 >ORDER BY docketno > > >SELECT docketno FROM >(SELECT docketno, MAX(case when datacmd = 'RCP' then 1 else 0 end)AS opendoc >FROM cntt_sales2 GROUP BY docketno) >WHERE opendoc = 0 What is the table structure? >Jack -- ( Kees Nuyt ) c[_]
Re: [sqlite] How do you find out the names of the fields within a table?
On Wed, 12 Jul 2006 19:05:51 +0100, you wrote:
> Hi, how can I find out the names of the
> fields within a given table?
>
> I've tried "pragma table_info(test);"
> but this brings back too much info,
> I just require the names as I'll be
> storing them in an array within my application.
Your application could select the 'name' column in your result
set.
In what language is your application written?
PHP for example has a nice wrapper to achieve what you want:
sqlite_array_query()
Here is some sample PHP code (wrapped by mailclient):
if ($dbhandle = sqlite_open($dbfilename, 0666,
$sqliteerror)){
// query
$result = sqlite_array_query($dbhandle, 'SELECT * FROM
tablename', SQLITE_ASSOC);
// display
printf('');
$firsttime = TRUE;
foreach ($aResult as $aRow){
if ($firsttime){
printf('');
foreach ($aRow as $col => $val){
printf('%s',$col);
}
reset($aRow);
printf("\n");
$firsttime = FALSE;
}
printf('');
foreach ($aRow as $col => $val){
printf('%s',$val);
}
printf("\n");
}
printf('');
sqlite_close($dbhandle);
} else {
die($sqliteerror);
}
>Many thanks
>
>John
Hope this helps.
--
kees
