Re: [sqlite] adding/dropping foreign key to existing table

On 23 Mar 2011, at 2:55am, BareFeetWare wrote: > begin immediate; > create temp table "My Table Backup" as select * from "My Table"; > drop table "My Table"; > create table "My Table" (); > insert into "My Table" select * from "My Table Backup"; > drop table "My Table Backup"; > commit; > > Unfo

Re: [sqlite] adding/dropping foreign key to existing table

On 23/03/2011, at 1:17 PM, Sam Carleton wrote: > I don't see any examples on http://www.sqlite.org/foreignkeys.html how to > either add or drop a foreign key to an existing table. What might that > syntax look like exactly? You have to drop the old table and create a new one with the changed f

[sqlite] adding/dropping foreign key to existing table

I don't see any examples on http://www.sqlite.org/foreignkeys.html how to either add or drop a foreign key to an existing table. What might that syntax look like exactly? Also, from a performance perspective, is there an advantage to using a foreign key in SQLite verses just an index? (aka, is it

[sqlite] System.Data.Sqlite.Linq

Hello all, Anyone know where I can download the latest System.Data.Sqlite.Linq.dll? I tried compiling it from what I found at System.Data.Sqlite.org, but it gave me errors because the SQL Generation folder was blank. I also tried SourceForge, but the Setup file said "A network error occurre

Re: [sqlite] Create table if not exists and insert seed data

On 22/03/2011, at 9:04 AM, Erich93063 wrote: > I am trying to create a SQLite database if it doesn't exist, which I > know I can use 'CREATE TABLE IF NOT EXISTS", but more importantly, I > need to initially populate the database with seed data if it doesn't > exist. If I use CREATE TABLE IF NOT EX

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

You can store any big-number representation you like as TEXT or BLOB values. The main issue is that you'll lose syntactic sugar: SQLite3 won't be able to treat those as numeric values, therefore it won't be able to compare numerically nor use arithmetic with such values. You can get some of that

Re: [sqlite] SQLite IDE's

On Tue, Mar 22, 2011 at 4:02 PM, Ben wrote: > You don't mention which platform you're on, but for OS X there's a good > comparison table of SQLite editors here: > > http://www.barefeetware.com/sqlite/compare/?ml Ben, I have a Mac, but I am currently targetting Windows. _

Re: [sqlite] SQLite IDE's

On Tue, Mar 22, 2011 at 3:26 PM, Jonathan Allin wrote: > Would the diff have to do more than compare (in some nice graphical way) the > two sqlite_master tables? What I am looking for is this: I have one version of the DB out in the field, I have made changed to it in development, I want to see

Re: [sqlite] SQLite IDE's

You don't mention which platform you're on, but for OS X there's a good comparison table of SQLite editors here: http://www.barefeetware.com/sqlite/compare/?ml - Ben On 22 Mar 2011, at 18:46, Sam Carleton wrote: > I am looking for a good SQLite IDE, SQLite Maestro looks like a good > candidat

Re: [sqlite] SQLite IDE's

Would the diff have to do more than compare (in some nice graphical way) the two sqlite_master tables? From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Petite Abeille Sent: 22 March 2011 19:01 To: General Discussion of SQLite Database Subject: Re: [sql

Re: [sqlite] SQLite IDE's

On Mar 22, 2011, at 7:46 PM, Sam Carleton wrote: > The one feature I don't see is a tool that can do a diff on the DDL of two > SQLite db's. Ah, yes, something along the lines of Oracle's DBMS_METADATA_DIFF.COMPARE_ALTER [1] would be handy. Alternatively, what about a simple DIFF(1) between t

[sqlite] SQLite IDE's

I am looking for a good SQLite IDE, SQLite Maestro looks like a good candidate with most all the features I need. The price is good, too. The one feature I don't see is a tool that can do a diff on the DDL of two SQLite db's. Does anyone know of any other SQLite IDE's that have that ability? Sam

Re: [sqlite] Best approach for xBestIndex/xFilter effectiveness

On 03/23/2011 01:07 AM, Jay A. Kreibich wrote: > On Tue, Mar 22, 2011 at 06:25:04PM +0700, Dan Kennedy scratched on the wall: > >> SQLite assumes that the result of each expression in the WHERE >> clause depends only on its inputs. If the input arguments are >> the same, the output should be do. Si

Re: [sqlite] Best approach for xBestIndex/xFilter effectiveness

On Tue, Mar 22, 2011 at 06:25:04PM +0700, Dan Kennedy scratched on the wall: > SQLite assumes that the result of each expression in the WHERE > clause depends only on its inputs. If the input arguments are > the same, the output should be do. Since random() has no inputs, > SQLite figures that it

Re: [sqlite] Create table if not exists and insert seed data

On 11-03-22 10:40 AM, Simon Slavin wrote: > > Or just do a 'SELECT id FROM whatever LIMIT 1'. If you get any error, it > doesn't exist, so create it and fill it. > > Or look in sqlite_master for an entry for the TABLE. > > Simon. Could also use INSERT OR IGNORE statements for the seed data if th

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

On 22 Mar 2011, at 1:12pm, LacaK wrote: >> You still don't say what you're planning on doing with these number...just >> displaying them? > > Yes may be ... > I am working on modification of database component for accessing SQLite3 > databases for FreePascal project. > We map declared column's

Re: [sqlite] Create table if not exists and insert seed data

On 22 Mar 2011, at 1:38pm, Pavel Ivanov wrote: > You can use a simple CREATE TABLE (without IF NOT EXISTS clause). If > it succeeds then you populate table with data (remember to do that in > the same transaction where you created the table). If CREATE TABLE > fails then you don't insert your dat

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

On 3/22/2011 12:50 PM, Jonathan Allin wrote: > Igor, > > Is there another way of looking at the problem by considering how Java and > other libraries handle big integers and big decimals? They have data types for them, and the library to support them. > Can you store the numeric value across suff

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

Igor, Is there another way of looking at the problem by considering how Java and other libraries handle big integers and big decimals? Can you store the numeric value across sufficient cells necessary to achieve the required precision? ¬Jonathan From: sqlite-users-boun...@sqlite.or

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

LacaK wrote, On 03/22/2011 08:53 AM: >>> / Problem will be solved if SQLite will store such values as text ... so Laco -- Problem will be solved when you teach SQLite to store such values as text. This library might help your project: http://speleotrove.com/decimal/ -- kjh _

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

On 3/22/2011 9:53 AM, LacaK wrote: > Base idea is store as TEXT when : > 1. column value is supplied as TEXT (only in case sqlite3_bind_text) > 2. conversion to REAL or INTEGER leads to loose of precision (digits) > > I do not know details how to implement it ;-) > May be, 1. strip out leading and

Re: [sqlite] Create table if not exists and insert seed data

Quoth Philip Graham Willoughby , on 2011-03-22 10:18:08 +: > Yes, I had this problem - if sqlite3_open_v2 had an equivalent to > O_EXCL it would make this a lot easier: you would only try to run > your schema/prepopulating SQL if the exclusive open worked. If it > failed you would retry a non-

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

/ Problem will be solved if SQLite will store such values as text ... so />>/ will behave like this: />>/ 1. is supplied value in TEXT (sqlite3_bind_text) />>/ 2. if yes then try convert this text value into INTEGER or REAL />>/ 3. convert back to text and compare with original value />>/ 4. if e

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

LacaK wrote: >> Problem will be solved if SQLite will store such values as text ... so >> will behave like this: >> 1. is supplied value in TEXT (sqlite3_bind_text) >> 2. if yes then try convert this text value into INTEGER or REAL >> 3. convert back to text and compare with original value >> 4. i

Re: [sqlite] Create table if not exists and insert seed data

You can use a simple CREATE TABLE (without IF NOT EXISTS clause). If it succeeds then you populate table with data (remember to do that in the same transaction where you created the table). If CREATE TABLE fails then you don't insert your data. Pavel On Mon, Mar 21, 2011 at 6:04 PM, Erich93063

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

If you already have an arbitrary precision number, just encode it to text, save it in sqlite and then decode on the way out. Yes it is possible, but such values (and databases) will not be readable by other database connectors (like for example in PHP etc.) Problem will be solved if SQLite wil

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

> Is there way how to store numeric values, which are out of REAL range ? SQLite has no way of storing numbers other than REAL or INTEGER. If you want the exact number to be stored your only option is to store it as TEXT (and don't work with it as a number on SQL level). Pavel On Tue, Mar 22, 2

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

On Mar 22, 2011, at 9:12 AM, LacaK wrote: >> You still don't say what you're planning on doing with these number...just >> displaying them? > > Yes may be ... > I am working on modification of database component for accessing SQLite3 > databases for FreePascal project. > We map declared column

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

You still don't say what you're planning on doing with these number...just displaying them? Yes may be ... I am working on modification of database component for accessing SQLite3 databases for FreePascal project. We map declared column's types to native freepascal internal field types. So DEC

Re: [sqlite] Best approach for xBestIndex/xFilter effectiveness

On Tue, Mar 22, 2011 at 2:25 PM, Dan Kennedy wrote: > On 03/22/2011 04:26 PM, Max Vlasov wrote: > > Hi, > > > > recently I finally started experimenting with virtual tables and there's > at > > least one thing I can not understand. > > > > As I see xBestIndex/xFilter were developed to allow fast

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

LacaK wrote: >> So once if I define column as DECIMAL,NUMERIC then there is no chance store >> in such column numeric values out of range of 64bit >> integers or 64bit floating point values, right ? Well, no chance to store them losslessly, preserving the precision. Where does this precision co

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

Hmmm...the docs do say that...but how do you get that value back out? Retreiving it as text doesn't work. You still don't say what you're planning on doing with these number...just displaying them? I think the docs may be misleading...here is the comment in sqlite3.c /* ** Try to convert a valu

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

/ So only solution is use TEXT columns (with TEXT affinity) ? There is no />>/ way how to use DECIMAL columns (with NUMERIC affinity) ? />>/ My goal is store numeric values with big precision (as declared per />>/ column DECIMAL(30,7)). / SQLite happily ignores those numbers in parentheses. Ther

[sqlite] I made a sqlite widget

A Web Sql widget really, here is a screen shot link http://bettereconomics.blogspot.com/2011/03/xml-commander.html It is all XML htp get, web sql and xml in javascript, working on opera browsers. The idea is to get the publisher simple access to the combination of SQL in his document and ad hoc XM

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

LacaK wrote: >> So only solution is use TEXT columns (with TEXT affinity) ? There is no >> way how to use DECIMAL columns (with NUMERIC affinity) ? >> My goal is store numeric values with big precision (as declared per >> column DECIMAL(30,7)). SQLite happily ignores those numbers in parentheses.

Re: [sqlite] How to get Unicode Value of any Character in string/text

ashish yadav wrote: > I want to know Unicode vale of Character given in string ( Like chines , > Japanese etc) . > > If there is any API which can help in this ? Retrive the string as UTF-16 with sqite3_column_text16 (SQLite automatically converts between UTF-8 and UTF-16 as necessary). You'll

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

Hi Michael, thank you for response. So only solution is use TEXT columns (with TEXT affinity) ? There is no way how to use DECIMAL columns (with NUMERIC affinity) ? My goal is store numeric values with big precision (as declared per column DECIMAL(30,7)). I do not want any conversion to floating

Re: [sqlite] Best approach for xBestIndex/xFilter effectiveness

On 03/22/2011 04:26 PM, Max Vlasov wrote: > Hi, > > recently I finally started experimenting with virtual tables and there's at > least one thing I can not understand. > > As I see xBestIndex/xFilter were developed to allow fast searching if the > implementation is able to do this. But there's also

Re: [sqlite] In-memory database with persistent storage

> through a version of dijkstra's routing algorithm Just out of interest, what data is this working on? RBS On Tue, Mar 22, 2011 at 7:25 AM, Amit Chaudhuri wrote: > [Not at all expert in sqlite but here's a practical example of speed up > using ":memory:" and perhaps a slightly different strate

Re: [sqlite] storing big numbers into NUMERIC, DECIMAL columns

I hope you know what you're doing with trying to preserve that much significance. Ths first time you stick it in a double or long double variable you'll lose it. You can use the HPAlib to get 32 digits http://www.nongnu.org/hpalib/ // Example showing digit loss -- doesn't matter double or lo

Re: [sqlite] Bulk loading large dataset; looking for speedups.

But I thought he said he dropped the indexes (meaning they aren't there during inserts). That should make sorting irrelevant. 3 Things. #1 Test with :memory: database and see what the speed is. That tells you if it's SQLite or disk I/O as the bottleneck. #2 Try WAL mode "pragma journal_mode=WA

Re: [sqlite] Create table if not exists and insert seed data

On Tue, Mar 22, 2011 at 1:04 AM, Erich93063 wrote: > I am trying to create a SQLite database if it doesn't exist, which I > know I can use 'CREATE TABLE IF NOT EXISTS", but more importantly, I > need to initially populate the database with seed data if it doesn't > exist. If I use CREATE TABLE IF

Re: [sqlite] Create table if not exists and insert seed data

On 21 Mar 2011, at 22:04, Erich93063 wrote: > I am trying to create a SQLite database if it doesn't exist, which I > know I can use 'CREATE TABLE IF NOT EXISTS", but more importantly, I > need to initially populate the database with seed data if it doesn't > exist. If I use CREATE TABLE IF NOT EXI

Re: [sqlite] How to get Unicode Value of any Character in string/text

Hi Philip, Can you please provide some example for Same ie How to use iconv() to get Unicode of Character ? Actually , i want to classify Japanese characters as either kanji or Katakana or Hiragana . so for that , i want to know Unicode of Character in string/text . Base on Unicode value , i can

Re: [sqlite] In-memory database with persistent storage

[Not at all expert in sqlite but here's a practical example of speed up using ":memory:" and perhaps a slightly different strategy for getting at the persistent data.] I use sqlite3 with Qt4 / C++ for an application which reads in an undirected graph and then chunks through a version of dijkstra's

[sqlite] storing big numbers into NUMERIC, DECIMAL columns

Hi, I have table like this: CREATE TABLE tab1 ( a INTEGER, c DECIMAL(30,7), ... ); When I am trying insert values like: INSERT INTO tab1 (a,c) VALUES(1, 123456789123456789.123456); INSERT INTO tab1 (a,c) VALUES(2, '123456789123456789.123456'); values for c column are always rounded or cast to

Re: [sqlite] Newbie question

Thanks, Igor & Michael - your help is much appreciated. Cheers Kai On Mon, Mar 21, 2011 at 4:47 AM, Igor Tandetnik wrote: > Kai Peters wrote: >> given a table with two columns (SaleDate, SaleVolume) is it possible in one >> query to obtain >> the following three column result set: >> >> SalesC

[sqlite] Create table if not exists and insert seed data

I am trying to create a SQLite database if it doesn't exist, which I know I can use 'CREATE TABLE IF NOT EXISTS", but more importantly, I need to initially populate the database with seed data if it doesn't exist. If I use CREATE TABLE IF NOT EXISTS, it will obviously create the table if it doesn't

Re: [sqlite] Determining deepest descendents of parent records from one table

Hi Enrico, Thanks a lot for the help, it was very useful. I have a bit more testing to do to make sure that I'm always getting the correct records, but your methods seem to be working (and performing much faster) so far! The main "trick" that I didn't think of was selecting records from the same

[sqlite] Best approach for xBestIndex/xFilter effectiveness

Hi, recently I finally started experimenting with virtual tables and there's at least one thing I can not understand. As I see xBestIndex/xFilter were developed to allow fast searching if the implementation is able to do this. But there's also sql language that allows very exotic queries. Some of

Re: [sqlite] How to get Unicode Value of any Character in string/text

On 22 Mar 2011, at 07:25, ashish yadav wrote: > Hi , > > To be more specific that : > 1. Database is UTF-8. > 2. Programming Language is C or C++. > > Database may contain Chines / Japanese character of strings. > > So , if there is any way /APIs to know Unicode of Character ? iconv() Best

Re: [sqlite] How to get Unicode Value of any Character in string/text

Hi , To be more specific that : 1. Database is UTF-8. 2. Programming Language is C or C++. Database may contain Chines / Japanese character of strings. So , if there is any way /APIs to know Unicode of Character ? Thanks & Regards Ashish On Tue, Mar 22, 2011 at 12:45 PM, ashish yadav

[sqlite] How to get Unicode Value of any Character in string/text

Hi , I want to know Unicode vale of Character given in string ( Like chines , Japanese etc) . If there is any API which can help in this ? Thanks & Regards Ashish ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bi

[sqlite] storing big numbers into NUMERIC, DECIMAL columns

Hi, I have table like this: CREATE TABLE tab1 ( a INTEGER, c DECIMAL(30,7), ... ); When I am trying insert values like: INSERT INTO tab1 (a,c) VALUES(1, 123456789123456789.123456); INSERT INTO tab1 (a,c) VALUES(2, '123456789123456789.123456'); values for c column are always rounded or cast to :