[sqlite] Where to look for SQLite coder?

Hi - I need a small amount of coding done, and am wondering if someone can recommend a good place to post for an SQLite coder? Cheers ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Ghost index?

On 12 Nov 2013, at 4:00am, SongbookDB wrote: > Ahh - ok. I was typing sqlite3 db.3sdb IN sqlite3, not command prompt. > > I can load and search the db now. > > So to drop the index 'index1' for the table 'table1', do I just type DROP > INDEX IF EXISTS 'index1'; ?

Re: [sqlite] Ghost index?

I've notices if I make a mistake, the prompt turns to ...>, and then legitimate commands no longer work. Is there a way to get back to the normal prompt? On Tue, Nov 12, 2013 at 2:42 PM, Simon Slavin wrote: > > On 12 Nov 2013, at 2:49am, SongbookDB

[sqlite] Never mind Simon - Re: Ghost index?

Please disregard my last email - I was loading an incorrect copy of the database into SQLite Administrator. On Tue, Nov 12, 2013 at 2:42 PM, Simon Slavin wrote: > > On 12 Nov 2013, at 2:49am, SongbookDB > wrote: > > > I've named it db.s3db,

Re: [sqlite] Ghost index?

Ahh - ok. I was typing sqlite3 db.3sdb IN sqlite3, not command prompt. I can load and search the db now. So to drop the index 'index1' for the table 'table1', do I just type DROP INDEX IF EXISTS 'index1'; ? Doing so still shows the index when I load the database in SQLite Administrator. On

Re: [sqlite] Ghost index?

On 12 Nov 2013, at 2:49am, SongbookDB wrote: > I've named it db.s3db, run the shell, type sqlite3 db, then select * from > songs; and it gives the error "Error: near "sqlite3": syntax error. as a command to your operating system sqlite3 db.s3db then once you're

Re: [sqlite] Ghost index?

On 12 Nov 2013, at 2:46am, SongbookDB wrote: > Does the shell expect a certain filename extension on the database file? I > can't get mine to open. It will open whatever file you tell it to. You must type the full name including the extension, but you can use

Re: [sqlite] Ghost index?

I've named it db.s3db, run the shell, type sqlite3 db, then select * from songs; and it gives the error "Error: near "sqlite3": syntax error. On Tue, Nov 12, 2013 at 1:26 PM, Simon Slavin wrote: > > On 12 Nov 2013, at 1:50am, SongbookDB >

Re: [sqlite] Ghost index?

Does the shell expect a certain filename extension on the database file? I can't get mine to open. Thanks Simon. On Tue, Nov 12, 2013 at 1:26 PM, Simon Slavin wrote: > > On 12 Nov 2013, at 1:50am, SongbookDB > wrote: > > > I've downloaded

Re: [sqlite] How to cancel the operation?

On 11/11/2013 9:23 PM, Igor Korot wrote: I downloaded and installed the command line SQLite3 tool on my Windows box. What I don't understand now is to how to cancel the long operation. Suppose I need to do this: sqlite3> CREATE TRIGGER > mytabe_insert AFTER INSERT ON > now at

Re: [sqlite] Ghost index?

On 12 Nov 2013, at 1:50am, SongbookDB wrote: > I've downloaded and run the shell tool - re opening a database, where do > base dir paths start from? I have the sqlite3.exe in a dir called test in > my downloads folder, and the test database is called db.sbdb -

[sqlite] How to cancel the operation?

Hi, ALL, I downloaded and installed the command line SQLite3 tool on my Windows box. What I don't understand now is to how to cancel the long operation. Suppose I need to do this: sqlite3> CREATE TRIGGER > mytabe_insert AFTER INSERT ON > now at this point I need to cancel the

Re: [sqlite] Ghost index?

Thanks Simon I'm not sure how to set up logging when using SQLite with as3 AIR - I'll check in with the Adobe community. I've downloaded and run the shell tool - re opening a database, where do base dir paths start from? I have the sqlite3.exe in a dir called test in my downloads folder, and the

Re: [sqlite] Ghost index?

On 12 Nov 2013, at 1:07am, Simon Slavin wrote: > On 12 Nov 2013, at 12:51am, SongbookDB wrote: > >> The process works fine at first - first time around, the index is dropped >> (this takes about 20 seconds on my test database). On subsequent

Re: [sqlite] Ghost index?

On 12 Nov 2013, at 12:51am, SongbookDB wrote: > The process works fine at first - first time around, the index is dropped > (this takes about 20 seconds on my test database). On subsequent loads, no > dropping occurs. Are you logging the result code returned from

[sqlite] Ghost index?

Hi I have a desktop app that loads an SQLite database into a datagrid. Before loading data, it goes through series of checks to make sure that the user's database has the correct indexes set up. The very last check is to test for the existence of the index I originally used on the database a

Re: [sqlite] Combining two databases into one

Simon, Many thanks for the clarification. Dave Ward Analytics Ltd - information in motion Tel: +44 (0) 118 9740191 Fax: +44 (0) 118 9740192 www: http://www.ward-analytics.com Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, United Kingdom, GU1 3SR Registered company

Re: [sqlite] Combining two databases into one

HI Ulrich, Sorry I should have made that clearer. I want to save all the data in one database, not just see it. Thanks for the idea though. Cheers, Dave Ward Analytics Ltd - information in motion Tel: +44 (0) 118 9740191 Fax: +44 (0) 118 9740192 www: http://www.ward-analytics.com Registered

Re: [sqlite] Feature request: Better support for database file moving

On Mon, Nov 11, 2013 at 5:31 PM, Stephen Chrzanowski wrote: > a Linux developer (Unless you count BASH scripting) but I deal with the OS > enough (My job for 6 years) to know enough low level stuff that at the file > system level, programs attach to files at the INODE level,

Re: [sqlite] executing queries on normalized database

I agree with Simon, but I also twist things up a bit. Databases are just data storage containers, at the most low level, raw definition. A text file could be a "database" for all intents and purposes. There should be things in there to protect the data within, but there also needs to be some

Re: [sqlite] Feature request: Better support for database file moving

Two questions; - Outside of SQLite, how would you handle "better checking" if a file randomly moves stuff around when a transaction is happening? - Why would a user move a database file while the program is in use? You can plan for every kind of problem, but you can't prevent every problem.

Re: [sqlite] Feature request: Better support for database file moving

On 11 Nov 2013 at 15:36, Simon Slavin wrote: > On 11 Nov 2013, at 2:01pm, L. Wood wrote: > >> * A user could move the file during a transaction, i.e., between >> _prepare_v2(); and _finalize(); >> >> It seems that SQLite needs support for this situation.

Re: [sqlite] sqlite db getting corrupt on power outage scenarios

Consumer-grade flash drives are notoriously unreliable. I suggest that (at a minimum) you test the drive with the software described at http://sosfakeflash.wordpress.com/2008/09/02/h2testw-14-gold-standard-in-detecting-usb-counterfeit-drives/ Have you what happens to your flash drive (and its

Re: [sqlite] Feature request: Better support for database file moving

On 11 Nov 2013, at 2:01pm, L. Wood wrote: > * A user could move the file during a transaction, i.e., between > _prepare_v2(); and _finalize(); > > It seems that SQLite needs support for this situation. Would you agree? > > If so, the question is how. Maybe through callback

Re: [sqlite] executing queries on normalized database

On 11 Nov 2013, at 2:35pm, dd wrote: > Got it. > > Can I conclude this way: Foreign keys works pretty well when > application deals with parent keys only. But, application may need to > execute more queries when dealing with child key/tables. My feeling is that a lot of

[sqlite] sqlite 3.8.X query planner bug with r_tree joins(r_tree utilization not working on joins & cross join has no affect on plan with r_tree joins)?

Hello, We are facing performance regression on queries over r_tree tables with any new 3.8.X sqlite version and we are sure it is connected with new versions of sqlite especially with new query planner and r_tree module. We have distincted by now two bugs: First one: We are convienced that new

Re: [sqlite] Row Level Locking as in InnoDB

Ok. Thank you for your explanation everyone and for being patient with me. I look forward to Sqlite4 in whatever features you implement :) On Mon, Nov 11, 2013 at 8:17 PM, Richard Hipp wrote: > On Mon, Nov 11, 2013 at 1:40 AM, Raheel Gupta wrote: > > > >

Re: [sqlite] Row Level Locking as in InnoDB

On Mon, Nov 11, 2013 at 1:40 AM, Raheel Gupta wrote: > > I guess a Row level locking could be difficult but a Page Level locking > could be not that difficult. > In an anomaly-free system, page level locking is not difficult. The difficulty comes when you have to recover

Re: [sqlite] executing queries on normalized database

Got it. Can I conclude this way: Foreign keys works pretty well when application deals with parent keys only. But, application may need to execute more queries when dealing with child key/tables. Is it? Thanks, dd On Mon, Nov 11, 2013 at 5:35 PM, Simon Slavin wrote: > >

Re: [sqlite] Combining two databases into one

On 10 Nov 2013, at 8:27pm, David Wellman wrote: > Can I just check something. When you say " Don't forget to DELETE FROM > before you insert your new rows " I assume that you're saying to make sure > that the target table is empty before I do the insert. Yes?

Re: [sqlite] Row Level Locking as in InnoDB

On 2013/11/11 08:40, Raheel Gupta wrote: I guess a Row level locking could be difficult but a Page Level locking could be not that difficult. ATM "db level locking" : If DB locked throw busy error In not locked lock db, let the writer do its thing For Page level locking (I think you could

Re: [sqlite] Feature request: Better support for database file moving

On Mon, Nov 11, 2013 at 3:01 PM, L. Wood wrote: > * The program runs in sandbox mode (a requirement for all programs on > Apple's Mac App Store), meaning that if a user moves a package, the program > will gain read/write access to the new path but LOSE read/write access to >

Re: [sqlite] Can i change the primary key?

On 11/10/2013 8:12 PM, David M. Cotter wrote: what i did before SQL was to just tell the new song (which may have updated / corrected meta data) to have the old song ID (and tell the old one to have the new one), then i'd just delete all the "new" song ids (since they had been swapped, i'd be

[sqlite] Feature request: Better support for database file moving

Here is my situation and why SQLite doesn't seem to satisfy it. It would be great to hear what some of the SQLite developers think about this. * My Mac OS X program has a document file format of packages (which are nothing but glorified directories). My database file is always inside of a

[sqlite] Can i change the primary key?

i've got say a music database the unique "song ID" is the "integer primary key", used to look up the song and all it's data sometimes an update of the meta data comes along, so i want to update to the new list, but preserve the old song IDs (so playlists that refer to them still link up)

Re: [sqlite] Combining two databases into one

Hi Simon, Thanks for that. Can I just check something. When you say " Don't forget to DELETE FROM before you insert your new rows " I assume that you're saying to make sure that the target table is empty before I do the insert. Yes? Cheers, Dave Ward Analytics Ltd - information in motion Tel:

Re: [sqlite] executing queries on normalized database

On 11 Nov 2013, at 12:38pm, dd wrote: > Scenario: > > sqlite> pragma foreign_keys = on; > sqlite> CREATE TABLE artist(artistidINTEGER PRIMARY KEY > AUTOINCREMENT, artistname TEXT); > sqlite> CREATE TABLE track(trackid INTEGER PRIMARY KEY > AUTOINCREMENT,

Re: [sqlite] executing queries on normalized database

I understand parent table to child table modifications with ON UPDATE/ON DELETE CASCADS. It's very good. Now, I stuck with child to parent tables query optimization after normalization. Scenario: sqlite> pragma foreign_keys = on; sqlite> CREATE TABLE artist(artistidINTEGER PRIMARY KEY

Re: [sqlite] executing queries on normalized database

Am 11.11.2013 12:57, schrieb Simon Slavin: > > On 11 Nov 2013, at 11:08am, dd wrote: > >> How do I insert trackname as "That's Amore" and artistname as >> "Dean Martin" with single query in artist and track tables? > > Sorry, I don't know a way to do that. I hope

Re: [sqlite] executing queries on normalized database

On 11 Nov 2013, at 11:08am, dd wrote: > How do I insert trackname as "That's Amore" and artistname as "Dean > Martin" with single query in artist and track tables? Sorry, I don't know a way to do that. I hope someone else has a way but it's normal to have your software

Re: [sqlite] executing queries on normalized database

Hi, Thanks Simon. sqlite> pragma foreign_keys = on; sqlite> CREATE TABLE artist(artistidINTEGER PRIMARY KEY AUTOINCREMENT, artistname TEXT); sqlite> CREATE TABLE track(trackid INTEGER PRIMARY KEY AUTOINCREMENT, trackname TEXT, trackartist INTEGER REFERENCES artist(artistid) ON UPDATE

Re: [sqlite] report bugs-update

Where did you get Windows version? Did you compile it using SQLITE_ENABLE_UPDATE_DELETE_LIMIT? From Compilation Options For SQLite : "*SQLITE_ENABLE_UPDATE_DELETE_LIMIT* This option enables an optional ORDER BY and LIMIT clause on