Re: [sqlite] Search all collumns with LIKE at once
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 fangles wrote: > I'm currently searching through all columns in a table to see if any match > the search text and the query is rather cumbersome. Have you considered http://www.sqlite.org/cvstrac/wiki?p=FtsTwo Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF2VL+mOOfHg372QQRArtdAKDUap+FIYHN/z7j7c3MkPGRhAd/WwCff9yr dwzO5IkKuh2VpQREyq7oJO8= =YpKB -END PGP SIGNATURE- - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] What is wrong with this SELECT CASE statement?
Thanks. Yes, it will return the errors, but this fell away in my wrapper function. Understand now how this works and will fix it and see how it compares to the Other methods. RBS -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: 19 February 2007 01:42 To: sqlite-users@sqlite.org Subject: Re: [sqlite] What is wrong with this SELECT CASE statement? --- RB Smissaert <[EMAIL PROTECTED]> wrote: > Trying to update my mmdd integers to months with a SELECT CASE > statement: > > SELECT CASE > (CAST(DATE_OF_BIRTH / 100 AS INTEGER) - CAST(DATE_OF_BIRTH / 1 AS > INTEGER) * 100) > WHEN 1 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'January' > WHEN 2 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'February' > WHEN 3 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'March' > WHEN 4 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'April' > WHEN 5 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'May' > WHEN 6 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'June' > WHEN 7 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'July' > WHEN 8 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'August' > WHEN 9 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'September' > WHEN 10 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'October' > WHEN 11 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'November' > WHEN 12 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'December' > END > FROM A2IDC21_J > > But no updates take place. SQL error: near "UPDATE": syntax error Does the environment you run in return error codes? You can't perform an UPDATE in a WHEN sub-clause. UPDATE TABLE1 SET FOO = (CASE ...whatever... END) > Thanks for any advice. > > RBS Be a PS3 game guru. Get your game face on with the latest PS3 news and previews at Yahoo! Games. http://videogames.yahoo.com/platform?platform=120121 - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: What is wrong with this SELECT CASE statement?
Thanks, I understand now. Will fix it and see how it compares the other methods. RBS -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 19 February 2007 04:54 To: SQLite Subject: [sqlite] Re: What is wrong with this SELECT CASE statement? RB Smissaert <[EMAIL PROTECTED]> wrote: > Trying to update my mmdd integers to months with a SELECT CASE > statement: > > SELECT CASE > (CAST(DATE_OF_BIRTH / 100 AS INTEGER) - CAST(DATE_OF_BIRTH / 1 AS > INTEGER) * 100) > WHEN 1 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'January' > WHEN 2 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'February' > WHEN 3 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'March' > WHEN 4 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'April' > WHEN 5 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'May' > WHEN 6 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'June' > WHEN 7 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'July' > WHEN 8 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'August' > WHEN 9 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'September' > WHEN 10 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'October' > WHEN 11 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'November' > WHEN 12 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'December' > END > FROM A2IDC21_J > > But no updates take place. UPDATE is a statement, not an expression. It cannot appear nested in another statement. You want UPDATE A2IDC21_J SET DATE_OF_BIRTH = CASE (CAST(DATE_OF_BIRTH / 100 AS INTEGER) - CAST(DATE_OF_BIRTH / 1 AS INTEGER) * 100) WHEN 1 THEN 'January' WHEN 2 THEN 'February' ... END Also, the expression in the CASE can be simplified to CAST(DATE_OF_BIRTH AS INTEGER) / 100 % 100 Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Search all collumns with LIKE at once
fangles wrote: I'm currently searching through all columns in a table to see if any match the search text and the query is rather cumbersome. Is there a way to use a loop to go through all available columns by some means? Maybe a loop by querying the schema? If you had a PK on that table and used a view to concatenate the columns create view v as select pk, first||...||deleted as cols from addr; you could reduce the select to select * from addr where pk = (select pk from v where cols like srch); Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Search all collumns with LIKE at once
fangles wrote: I'm currently searching through all columns in a table to see if any match the search text and the query is rather cumbersome. Is there a way to use a loop to go through all available columns by some means? Maybe a loop by querying the schema? SELECT * FROM addresses WHERE title LIKE '% + searchtext + %' OR first LIKE '% + searchtext + "%' OR middle LIKE '% + searchtext + "%' OR last LIKE '% + searchtext + "%' OR street LIKE '% + searchtext + "%' OR suburb LIKE '% + searchtext + "%' OR city LIKE '% + searchtext + "%' OR postcode LIKE '% + searchtext + "%' OR state LIKE '% + searchtext + "%' OR country LIKE '%+ searchtext + "%' OR work LIKE '% + searchtext + "%' OR home LIKE '% + searchtext + "%' OR mobile LIKE '% + searchtext + "%' OR company LIKE '%+ searchtext + "%' OR misc1 LIKE '% + searchtext + "%' OR misc2 LIKE '% + searchtext + "%' OR email LIKE '% + searchtext + "%' OR note LIKE '% + searchtext + "%' OR category LIKE '% + searchtext + "%' OR displayas LIKE '% + searchtext + "%' OR firstentry LIKE '% + searchtext + "%' OR lastedit LIKE '% + searchtext + "%' OR deleted LIKE '%+ searchtext + "%' ORDER BY displayas This is a big one!! Could you not join all the columns and search that? select * from addresses where first||middle...deleted like searchtext; Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Search all collumns with LIKE at once
I'm currently searching through all columns in a table to see if any match the search text and the query is rather cumbersome. Is there a way to use a loop to go through all available columns by some means? Maybe a loop by querying the schema? SELECT * FROM addresses WHERE title LIKE '% + searchtext + %' OR first LIKE '% + searchtext + "%' OR middle LIKE '% + searchtext + "%' OR last LIKE '% + searchtext + "%' OR street LIKE '% + searchtext + "%' OR suburb LIKE '% + searchtext + "%' OR city LIKE '% + searchtext + "%' OR postcode LIKE '% + searchtext + "%' OR state LIKE '% + searchtext + "%' OR country LIKE '%+ searchtext + "%' OR work LIKE '% + searchtext + "%' OR home LIKE '% + searchtext + "%' OR mobile LIKE '% + searchtext + "%' OR company LIKE '%+ searchtext + "%' OR misc1 LIKE '% + searchtext + "%' OR misc2 LIKE '% + searchtext + "%' OR email LIKE '% + searchtext + "%' OR note LIKE '% + searchtext + "%' OR category LIKE '% + searchtext + "%' OR displayas LIKE '% + searchtext + "%' OR firstentry LIKE '% + searchtext + "%' OR lastedit LIKE '% + searchtext + "%' OR deleted LIKE '%+ searchtext + "%' ORDER BY displayas This is a big one!! -- View this message in context: http://www.nabble.com/Search-all-collumns-with-LIKE-at-once-tf3251161.html#a9037784 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Binary data in SQLite3
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 jose isaias cabrera wrote: > Can SQLite3 save binary data? http://sqlite.org/datatype3.html Look for blobs. > I would like to save zip files on it. Is > this possible? Yes. There is a limit of 2GB per item and there is no random access api to retrieve part of a blob - you have to get the whole thing in one go. In general better practise is to store the zip files in the filesystem and store the name of the file in SQLite. > If so, is there a place where I can read or get some > examples about it? Blobs are just another SQLite datatype so you manipulate them in exactly the same way as you do other types. http://sqlite.org/docs.html The topic also comes up fairly frequently on this list. You can examine the list archives for previous discussion. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF2Uj0mOOfHg372QQRAhPJAKDY/M/aash7JD7xlOGp0m/xwwHbjgCeJXj9 kiUUCu+a/POfb1PzP4Nf3Yw= =Tb74 -END PGP SIGNATURE- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Month string from yyyy-mm-dd
RB Smissaert wrote: Analyzing the lookup table knocks the time down from 0.36 to 0.31 secs, something I didn't expect. That shows the importance of testing. I ported the SQLite benchmarks to Python and was surprised to see some of the tests taking minutes to run versus a few (or a few tens of) seconds for the SQLite shell reading SQL from a file or the same wrapper executing the statements individually. For a 100k line bulk insert (e.g., restoring from a .dump to a disk file) that could mean a slow down from about 25 seconds to over 5500 seconds, or 92 minutes... Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Binary data in SQLite3
On 2/19/07, jose isaias cabrera <[EMAIL PROTECTED]> wrote: Greetings! Can SQLite3 save binary data? I would like to save zip files on it. Is this possible? If so, is there a place where I can read or get some examples about it? blobs http://www.sqlite.org/datatype3.html -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] compare open table and attached database table
Igor Tandetnik wrote: > > fangles <[EMAIL PROTECTED]> wrote: >> Hello, I am trying to compare a currently opened database table with >> a table from an attached database. Both tables have identical >> structures but the attached table has an extra record. >> >> The first lists records from the internal table NOT CONTAINED IN the >> attached table >> The second lists records NOT CONTAINED IN the internal table >> >> Select a.displayas AS displayas FROM addresses a INNER JOIN >> RemoteDb.addresses b ON a.displayas <> b.displayas > > This query doesn't do what you think it does. Once you get past the > little syntax problem, you'll get displayas from every record in > addresses table, each repeated multiple times. > > Make it > > select displayas from main.addresses > where displayas not in (select displayas from RemoteDb.addresses) > > Igor Tandetnik > > Thank you Igor, that's fantastic. I'm reading lots of SqLite tutorials but a lot of the SQL is so far out of my brain's reach that it doesn't make sense to me. And I love to play:):):) Have a great day. -- View this message in context: http://www.nabble.com/compare-open-table-and-attached-database-table-tf3250700.html#a9037663 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Binary data in SQLite3
Greetings! Can SQLite3 save binary data? I would like to save zip files on it. Is this possible? If so, is there a place where I can read or get some examples about it? Thanks. josé - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Month string from yyyy-mm-dd
RB Smissaert wrote: Yes, it looks it isn't there. I guess it isn't really SQLite's place to know how to spell this month's name in your locale. It's a presentation issue at the end of the day and a lookup table is a nice easy solution. to update the table and 25000 records takes about a third of a second > ... Will see if doing it with a substr function is any faster. Would that work on integer numbers? Dunno, but 80,000 updates a second seems quite good to me. How often do you need to run this? Premature/excessive optimisation and all that. ;) Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: What is wrong with this SELECT CASE statement?
RB Smissaert <[EMAIL PROTECTED]> wrote: Trying to update my mmdd integers to months with a SELECT CASE statement: SELECT CASE (CAST(DATE_OF_BIRTH / 100 AS INTEGER) - CAST(DATE_OF_BIRTH / 1 AS INTEGER) * 100) WHEN 1 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'January' WHEN 2 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'February' WHEN 3 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'March' WHEN 4 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'April' WHEN 5 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'May' WHEN 6 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'June' WHEN 7 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'July' WHEN 8 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'August' WHEN 9 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'September' WHEN 10 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'October' WHEN 11 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'November' WHEN 12 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'December' END FROM A2IDC21_J But no updates take place. UPDATE is a statement, not an expression. It cannot appear nested in another statement. You want UPDATE A2IDC21_J SET DATE_OF_BIRTH = CASE (CAST(DATE_OF_BIRTH / 100 AS INTEGER) - CAST(DATE_OF_BIRTH / 1 AS INTEGER) * 100) WHEN 1 THEN 'January' WHEN 2 THEN 'February' ... END Also, the expression in the CASE can be simplified to CAST(DATE_OF_BIRTH AS INTEGER) / 100 % 100 Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: compare open table and attached database table
fangles <[EMAIL PROTECTED]> wrote: Hello, I am trying to compare a currently opened database table with a table from an attached database. Both tables have identical structures but the attached table has an extra record. The first lists records from the internal table NOT CONTAINED IN the attached table The second lists records NOT CONTAINED IN the internal table Select a.displayas AS displayas FROM addresses a INNER JOIN RemoteDb.addresses b ON a.displayas <> b.displayas This query doesn't do what you think it does. Once you get past the little syntax problem, you'll get displayas from every record in addresses table, each repeated multiple times. Make it select displayas from main.addresses where displayas not in (select displayas from RemoteDb.addresses) Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] compare open table and attached database table
Hello, I am trying to compare a currently opened database table with a table from an attached database. Both tables have identical structures but the attached table has an extra record. I wanted to do two queries. The first lists records from the internal table NOT CONTAINED IN the attached table The second lists records NOT CONTAINED IN the internal table I am using a field called "displayas" as the comparison for record existence and the attached database was called RemoteDb. The query below is just what I am playing with and predictably doesn't work and I am getting an ambiguous column reference error for "a.displayas". Could anyone help me please? I am fairly new at this. Select a.displayas AS displayas FROM addresses a INNER JOIN RemoteDb.addresses b ON a.displayas <> b.displayas -- View this message in context: http://www.nabble.com/compare-open-table-and-attached-database-table-tf3250700.html#a9036602 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite: current_time is off
--- P Kishor <[EMAIL PROTECTED]> wrote:
> On 2/18/07, Neil McLeod <[EMAIL PROTECTED]> wrote:
> > I just started using SQLite, and it seems my timestamps are all 5 hours
> > ahead of time (i.e. "select current_time" gives me 0:47 when it is actually
> > 19:47 here). I guess this is GMT. Is there a way to adjust the time? I'm on
> > Windows XP, and the time is listed correctly in my taskbar.
>
> by default, time is in UTC. Use 'localtime' modifier for your time.
>
> select time('now', 'localtime');
Is there any way to force current_time to display localtime (other than
hacking the sqlite source code)? Some environment setting or PRAGMA
or something?
Finding fabulous fares is fun.
Let Yahoo! FareChase search your favorite travel sites to find flight and hotel
bargains.
http://farechase.yahoo.com/promo-generic-14795097
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] What is wrong with this SELECT CASE statement?
--- RB Smissaert <[EMAIL PROTECTED]> wrote: > Trying to update my mmdd integers to months with a SELECT CASE > statement: > > SELECT CASE > (CAST(DATE_OF_BIRTH / 100 AS INTEGER) - CAST(DATE_OF_BIRTH / 1 AS > INTEGER) * 100) > WHEN 1 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'January' > WHEN 2 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'February' > WHEN 3 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'March' > WHEN 4 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'April' > WHEN 5 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'May' > WHEN 6 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'June' > WHEN 7 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'July' > WHEN 8 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'August' > WHEN 9 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'September' > WHEN 10 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'October' > WHEN 11 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'November' > WHEN 12 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'December' > END > FROM A2IDC21_J > > But no updates take place. SQL error: near "UPDATE": syntax error Does the environment you run in return error codes? You can't perform an UPDATE in a WHEN sub-clause. UPDATE TABLE1 SET FOO = (CASE ...whatever... END) > Thanks for any advice. > > RBS Be a PS3 game guru. Get your game face on with the latest PS3 news and previews at Yahoo! Games. http://videogames.yahoo.com/platform?platform=120121 - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] What is wrong with this SELECT CASE statement?
Trying to update my mmdd integers to months with a SELECT CASE statement: SELECT CASE (CAST(DATE_OF_BIRTH / 100 AS INTEGER) - CAST(DATE_OF_BIRTH / 1 AS INTEGER) * 100) WHEN 1 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'January' WHEN 2 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'February' WHEN 3 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'March' WHEN 4 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'April' WHEN 5 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'May' WHEN 6 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'June' WHEN 7 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'July' WHEN 8 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'August' WHEN 9 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'September' WHEN 10 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'October' WHEN 11 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'November' WHEN 12 THEN UPDATE A2IDC21_J SET DATE_OF_BIRTH = 'December' END FROM A2IDC21_J But no updates take place. Thanks for any advice. RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite: current_time is off
On 2/18/07, Neil McLeod <[EMAIL PROTECTED]> wrote:
Hello,
I just started using SQLite, and it seems my timestamps are all 5 hours
ahead of time (i.e. "select current_time" gives me 0:47 when it is actually
19:47 here). I guess this is GMT. Is there a way to adjust the time? I'm on
Windows XP, and the time is listed correctly in my taskbar.
by default, time is in UTC. Use 'localtime' modifier for your time.
select time('now', 'localtime');
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] How do you combine two SQLite databases?
Thanks! That fixed it. Neil On 2/17/07, Kees Nuyt <[EMAIL PROTECTED]> wrote: 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] - -- Ubuntu Linux 6.06 (Dapper Drake)
[sqlite] sqlite: current_time is off
Hello, I just started using SQLite, and it seems my timestamps are all 5 hours ahead of time (i.e. "select current_time" gives me 0:47 when it is actually 19:47 here). I guess this is GMT. Is there a way to adjust the time? I'm on Windows XP, and the time is listed correctly in my taskbar. Thanks! Neil
Re: [sqlite] migrating from mysql
Frederick Grim wrote:
Howdy all,
So I am wondering if anyone has run into this problem. I am trying
to move from mysql to sqlite and the unique constraint from primary keys
is not allowing me to insert my sql that I dumped from the mysql
database. I have a bunch of join tables so I really can't set the id
field to null and let sqlite renumber the keys. I guess I could dump
each table into a separate .csv file or something and use the import
pragma but I am hoping there is just so pragma I can give it for it not
to enforce key constraints through the import. Can I do this?
I have run into this, or it into me, but not cross-server and not with
the complexity of joined tables.
I think the principles are the same.
My guess is that you have existing records in the target table with keys
that are identical to those you are trying to import (you said "insert
my sql that I dumped from the mysql database").
Since you rely on these keys to join other tables, I can imagine that
there's a possibility that anything you do will mangle the associations
that exist.
Someone here might have a quick and easy way and I'd love to read it.
If you can easily re-build the joins, one option would be to use an
INSERT INTO [table]( {with a field list that excludes your key field})
VALUES(..) or UPDATE [table] SET {with a field list that excludes your
key field} as applicable. A little more work than just importing.
Another option is to build a process that reads the data from the source
and populates it according to the existing constraints. You might also
consider nulling all your master table source record key fields and
import these (new keys will be assigned), and then use a process to
re-build the joins with the other tables.
If it were my project, I'd see a few hours of programming in the near term.
Knowing me, I'd also back up everything.
/m
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] migrating from mysql
Howdy all, So I am wondering if anyone has run into this problem. I am trying to move from mysql to sqlite and the unique constraint from primary keys is not allowing me to insert my sql that I dumped from the mysql database. I have a bunch of join tables so I really can't set the id field to null and let sqlite renumber the keys. I guess I could dump each table into a separate .csv file or something and use the import pragma but I am hoping there is just so pragma I can give it for it not to enforce key constraints through the import. Can I do this? Fred - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] new algorithm for handling INSERT, and .import patch
It does not appear that the recent INSERT checkin speeds up bulk inserts on already populated tables with many indexes: [3643] Add a new algorithm for handling INSERT which reduces fragmentation on a VACUUM http://www.sqlite.org/cvstrac/chngview?cn=3643 http://www.sqlite.org/cvstrac/tktview?tn=2075 Regarding the newly optimized code for the statement: INSERT INTO table1 SELECT * FROM table2; I created a patch (attached) in the hope of speeding up the sqlite3 shell .import command using the same INSERT INTO construct. It uses a staging table in an exact schema copy of the table and indexes (with code borrowed from vacuum.c) into an attached :memory: database table which bulk inserts the data into the real table, batch by batch. The supplied patch stages in 10,000 row batches. Unfortunately, its results are not very promising. Although checkin [3643] greatly improves VACUUM speed and index locality after a VACUUM, it does not help in situations where you're trying to insert into already populated large tables with numerous indexes. In such cases, simply using a high value for cache_size pragma cache_size=20; yields substantially times faster .import times than the supplied patch .import with a default cache_size. Only if you increase the patch's IMPORT_BATCH_SIZE value to a number greater than the number of rows being imported (say 10) - and the table being into imported into is empty prior to .import - do you see any speed improvement. Even with these ideal conditions the patched .import is still 2 times slower than the non-patched .import command with a very high pragma cache_size. The test scenario is below. 59,049 rows are to be .imported into table foo which has numerous indexes. Try it with the latest CVS sqlite3, as well as the latest CVS plus the .import patch. .import patch cache_size IMPORT_BATCH_SIZE time (s) - -- - no 2000 n/a 1201 no 20 n/a22 yes 2000 1 1665 yes 2000 3 966 yes20 378 yes 20001051 Perhaps SQLite insert speed could benefit from having blocks of contiguous pages for exclusively use by each index. There seems to be a great deal of disk activity when the cache_size is low. #!/bin/bash rm -f foo.db t59049.csv sqlite3 foo.dbseparator); @@ -1124,9 +1162,30 @@ static int do_meta_command(char *zLine, } sqlite3_finalize(pStmt); if( nCol==0 ) return 0; -zSql = malloc( nByte + 20 + nCol*2 ); -if( zSql==0 ) return 0; -sqlite3_snprintf(nByte+20, zSql, "INSERT INTO '%q' VALUES(?", zTable); + +rc = execSql(p->db, "attach database ':memory:' as import_db"); +zSql = sqlite3_mprintf( +"SELECT 'CREATE TABLE import_db.' || substr(sql,14,1) " +"FROM sqlite_master WHERE tbl_name='%q' AND type='table' " +"UNION ALL " +"SELECT 'CREATE INDEX import_db.' ||
RE: [sqlite] Month string from yyyy-mm-dd
Analyzing the lookup table knocks the time down from 0.36 to 0.31 secs, something I didn't expect. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 18 February 2007 19:59 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Month string from -mm-dd Thanks, I thought that would be the reason it wasn't included. Doing a join with a lookup table is very much fast enough, so there is no problem there. Just tried it with substr instead of integer division (starting with mmdd) and they are equally fast. Will now try a big CASE statement, but I somehow guess it will be slower. RBS -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 18 February 2007 19:37 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Month string from -mm-dd "RB Smissaert" <[EMAIL PROTECTED]> wrote: > Is it possible with the date-time functions to get the month as a string, so > January etc. from the date in the format -mm-dd? This is difficult to internationalize so I omitted it in my implementation of the date/time functions. You can, of course, grab the source to the date/time functions, make what modifications you want, and use the modified date/time functions in your code. But text month names are not supported by the core distribution of SQLite. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Month string from yyyy-mm-dd
Thanks, I thought that would be the reason it wasn't included. Doing a join with a lookup table is very much fast enough, so there is no problem there. Just tried it with substr instead of integer division (starting with mmdd) and they are equally fast. Will now try a big CASE statement, but I somehow guess it will be slower. RBS -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 18 February 2007 19:37 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Month string from -mm-dd "RB Smissaert" <[EMAIL PROTECTED]> wrote: > Is it possible with the date-time functions to get the month as a string, so > January etc. from the date in the format -mm-dd? This is difficult to internationalize so I omitted it in my implementation of the date/time functions. You can, of course, grab the source to the date/time functions, make what modifications you want, and use the modified date/time functions in your code. But text month names are not supported by the core distribution of SQLite. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Month string from yyyy-mm-dd
"RB Smissaert" <[EMAIL PROTECTED]> wrote: > Is it possible with the date-time functions to get the month as a string, so > January etc. from the date in the format -mm-dd? This is difficult to internationalize so I omitted it in my implementation of the date/time functions. You can, of course, grab the source to the date/time functions, make what modifications you want, and use the modified date/time functions in your code. But text month names are not supported by the core distribution of SQLite. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Does SQLITE support RESEEDing of AUTOINCREMENT fields
Tom Olson <[EMAIL PROTECTED]> wrote:
> Hello,
>
> Is there a function or PRAGMA that tells the database engine to recalculate
> the next sequence number for an AUTOINCREMENT column of a table (i.e. PRAGMA
> reseed('tablename')?
>
You can UPDATE the sqlite_sequence table to do this.
--
D. Richard Hipp <[EMAIL PROTECTED]>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Month string from yyyy-mm-dd
Yes, it looks it isn't there. It is no problem though to update the table and 25000 records takes about a third of a second when I do it from the integer mmdd format like this: UPDATE A2IDB3F_J SET DATE_OF_BIRTH = (SELECT MONTH_TEXT FROM MONTH_LOOKUP WHERE (SELECT CAST(DATE_OF_BIRTH / 100 AS INTEGER) - CAST(DATE_OF_BIRTH / 1 AS INTEGER) * 100) = MONTH_NUMBER) Will see if doing it with a substr function is any faster. Would that work on integer numbers? RBS -Original Message- From: Martin Jenkins [mailto:[EMAIL PROTECTED] Sent: 18 February 2007 18:53 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Month string from -mm-dd RB Smissaert wrote: > Is it possible with the date-time functions to get the month as a string, so > January etc. from the date in the format -mm-dd? Doesn't look like it. Nothing in the wiki and I couldn't see anything in the source either. I suppose you could use a big case statement if you wanted to avoid joining with a month table. Martin - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] ANN: SQLite ODBC Driver 0.73
Hi all, release 0.73 of the SQLite ODBC driver is available for download from http://www.ch-werner.de/sqliteodbc >From the change log: * update to SQLite 3.3.13 * SQLGetInfo(SQL_OWNER_TERM) now is empty string * fixed bug in Win32 version concerning SQLDriverConnect() * added -L/-l/-I/-i switches to sqlite+tcc.c Cheers, Christian - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Month string from yyyy-mm-dd
RB Smissaert wrote: Is it possible with the date-time functions to get the month as a string, so January etc. from the date in the format -mm-dd? Doesn't look like it. Nothing in the wiki and I couldn't see anything in the source either. I suppose you could use a big case statement if you wanted to avoid joining with a month table. Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Does SQLITE support RESEEDing of AUTOINCREMENT fields
Hello,
Is there a function or PRAGMA that tells the database engine to recalculate
the next sequence number for an AUTOINCREMENT column of a table (i.e. PRAGMA
reseed('tablename')?
Regards,
Tom
--
View this message in context:
http://www.nabble.com/Does-SQLITE-support-RESEEDing-of-AUTOINCREMENT-fields-tf3249262.html#a9032368
Sent from the SQLite mailing list archive at Nabble.com.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: FW: [sqlite] retrieval speedup help requested
On 2/17/07, Anderson, James H (IT) <[EMAIL PROTECTED]> wrote: Thanks. AFAIK it's not available in sybase, and that's my only sql background. I guessed as much. What ANALYZE does (informal explanation follows) is that it updates all the internal statistics and indexes that SQLite needs for its queries, kinda like kick-starting the indexing process. Nevertheless, there is probably something funky with your query itself. Looking at it in detail, you have CREATE TABLE city_loc ( loc_id INTEGER PRIMARY KEY, cc TEXT, region TEXT, city TEXT, postalCode TEXT, lat REAL, lon REAL, areaCode TEXT ); CREATE TABLE city_block ( start INTEGER UNSIGNED NOT NULL, end INTEGER UNSIGNED NOT NULL, loc_id INTEGER NOT NULL ); SELECT cc, region, city, postalCode, lat, lon, areaCode FROM city_block NATURAL JOIN city_loc WHERE $ipnum BETWEEN start AND end; so, I didn't know what a NATURAL JOIN was. Went, looked it up **. Seems like it joins on like named and typed columns. Ok. So, in other words, you are doing SELECT cb.cc AS cc, cb.region AS region, cb.city AS city, cb.postalCode AS postalCode, cb.lat AS lat, cb.lon AS lon, cb.areaCode AS areaCode FROM city_block cb JOIN city_loc cl ON cb.loc_id = cl.loc_id WHERE $ipnum BETWEEN cl.start AND cl.end and you have made a composite index on cl.start and cl.end. Try an index on cl.loc_id as well so that the JOIN is done efficiently. ** by the way, asktom's advice with NATURAL JOINs... "it's ambiguous at best and leaves you open to problems if columns get added or renamed, no more than two tables can be joined using this method, and it gives you little control over the specifics of a join if columns join across the tables in an unusual way. Tom's advice with NATURAL JOINs- forget that they exist." I would agree... it just seems a better practice to spell things out explicitly in programs, not just for the benefit for others but perhaps for one's own benefit when you come back to look at your own code 6 months later. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P Kishor Sent: Saturday, February 17, 2007 8:56 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] retrieval speedup help requested On 2/17/07, Anderson, James H (IT) <[EMAIL PROTECTED]> wrote: > Where does one get "analyze"? > man, its pretty much a standard SQL command (at least all the dbs I've used thus far). Check out http://www.sqlite.org/lang.html. Most questions answered. > -Original Message- > From: RB Smissaert [mailto:[EMAIL PROTECTED] > Sent: Saturday, February 17, 2007 5:25 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] retrieval speedup help requested > > Try running: analyze city_loc > after adding the index. > > RBS > > > -Original Message- > From: Tom Shaw [mailto:[EMAIL PROTECTED] > Sent: 17 February 2007 22:16 > To: sqlite-users@sqlite.org > Subject: [sqlite] retrieval speedup help requested > > In sqlite 3 I have two tables. city_loc has 156865 entries and > city_block has 1874352 entries: > > CREATE TABLE city_loc (loc_id INTEGER PRIMARY KEY, cc TEXT, region > TEXT, city TEXT, postalCode TEXT, lat REAL, lon REAL, areaCode TEXT); > CREATE TABLE city_block (start INTEGER UNSIGNED NOT NULL, end INTEGER > UNSIGNED NOT NULL, loc_id INTEGER NOT NULL); > > And my retrieval is but it is slow (6 seconds!): > SELECT cc, region, city, postalCode, lat, lon, areaCode FROM > city_block NATURAL JOIN city_loc WHERE $ipnum BETWEEN start AND end; > > I tried using: > CREATE INDEX city_block_idx ON city_block (start,end); > > but it did not appear to speedup anything but it did use up a lot of > space. > > Any suggestions on how to speedup retrievals? All help is appreciated. > > TIA > > Tom > > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] - NOTICE: If received in error, please destroy and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error. -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = - To unsubscribe, send
[sqlite] Month string from yyyy-mm-dd
Is it possible with the date-time functions to get the month as a string, so
January etc. from the date in the format -mm-dd?
I can get the month as a number like this:
select strftime('%m', '2007-02-17')
and I could do a table update by joining to a month lookup table, but I
wonder if there is a simpler way to do this.
Thanks for any advice.
RBS
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] retrieval speedup help requested
Tom Shaw <[EMAIL PROTECTED]> wrote: > In sqlite 3 I have two tables. city_loc has 156865 entries and > city_block has 1874352 entries: > > CREATE TABLE city_loc (loc_id INTEGER PRIMARY KEY, cc TEXT, region > TEXT, city TEXT, postalCode TEXT, lat REAL, lon REAL, areaCode TEXT); > CREATE TABLE city_block (start INTEGER UNSIGNED NOT NULL, end INTEGER > UNSIGNED NOT NULL, loc_id INTEGER NOT NULL); > > And my retrieval is but it is slow (6 seconds!): > SELECT cc, region, city, postalCode, lat, lon, areaCode FROM > city_block NATURAL JOIN city_loc WHERE $ipnum BETWEEN start AND end; > > I tried using: > CREATE INDEX city_block_idx ON city_block (start,end); > Try CREATE INDEX city_block_idx2 ON city_block(loc_id, start); -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
