Re: [sqlite] Version 3.1.3
On Sat, Feb 19, 2005 at 09:30:26PM -0500, D. Richard Hipp wrote: > As always, please let me know if you find any > problem. gmake && gmake test on OpenBSD 3.6/amd64 results in the following warnings and test errors. Looks like it is all because of 64bit architecture. If you want me to test anything I'll be happy to do so. These warnings are mentioned in ticket #848: src/table.c: In function `sqlite3_get_table': src/table.c:146: warning: cast to pointer from integer of different size src/table.c: In function `sqlite3_free_table': src/table.c:191: warning: cast from pointer to integer of different size src/vdbeaux.c: In function `displayP3': src/vdbeaux.c:389: warning: cast from pointer to integer of different size These warnings aren't mentioned anywhere else: src/test1.c: In function `test_collate_func': src/test1.c:1086: warning: cast from pointer to integer of different size src/test1.c: In function `test_collate_needed_cb': src/test1.c:1168: warning: cast to pointer from integer of different size These tests failed: printf-8.1... Error: integer value too large to represent printf-8.2... Error: integer value too large to represent 2 errors out of 19732 tests Failures on these tests: printf-8.1 printf-8.2 enjoy, -jeremy -- Jeremy Hinegardner [EMAIL PROTECTED]
[sqlite] Version 3.1.3
SQLite version 3.1.3 is now available on the website. http://www.sqlite.org/ This release fixes a few minor problems that were found in 3.1.2. This release is also designed to be forwards compatible with version 3.2. Version 3.1.2 might not be able to read and write some database files generated by version 3.2 - specifically databases on which ALTER TABLE has been used but have not yet been VACUUMed. But we believe that version 3.1.3 will also be able to read and write any 3.2 database file. As always, please let me know if you find any problem. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] ORDER BY / HAVING clauses and aliased field names
Hi!
I'm porting some MySQL code to SQLite 2.8.16.
I'm having problems with a few multi-tables queries used by my application:
The problems seems to be related with inability of including "calculated
field" names in ORDER BY and/or HAVING clauses, although I might be
wrong (and most probably I am) .
The simplest of the queries:
SELECT countries.*,
coalesce(cd1.country_name, cd2.country_name) AS country_name,
coalesce(cd1.language_id, cd2.language_id) AS language_id
FROM countries
LEFT JOIN countries_description AS cd1 ON
(countries.country_id=cd1.country_id AND cd1.language_id=1)
LEFT JOIN countries_description AS cd2 ON
(countries.country_id=cd2.country_id AND cd2.language_id=0)
WHERE 1
GROUP BY countries.country_id
HAVING 1
ORDER BY country_name ASC;
returns error:
[nativecode=ambiguous column name: country_name]
Firstly let me explain given query:
I'm using fallback language substitution feature. I have one 'countries'
table with country_id as primary key, and 'countries_description' table
holding country names in different languages (two field primary key) -
schema below:
CREATE TABLE 'countries' (
'country_id' char(2) NOT NULL default '',
PRIMARY KEY ('country_id')
);
CREATE TABLE 'countries_description' (
'country_id' char(2) NOT NULL default '',
'language_id' tinyint(4) NOT NULL default '0',
'country_name' varchar(20) NOT NULL default '',
PRIMARY KEY ('country_id','language_id')
);
With presented query I'd like to retrieve all country_ids with assigned
name in language_id=1, and if there is no entry for that country in
language_id=1, get translated entry in language_id = 0 (fallback
language). Therefore i'm using two JOINS and coalesce() function.
Worth noting - i'm using PRAGMA short_column_names = 1; for
compatibility in multi-table SELECT results.
The code works perfectly in MySQL. SQLite complains about ambiguity of
'country_name' field in ORDER BY clause.
Using cd1.country_name or cd2.country_name helps, BUT it's not what i
expect of this query, i'd like to order by appropriate (fallback
translated or not) entries - therefore i wanted to use calculated field
'country_name' alias.
When I change the alias name to anything else (i.e. not to existing
field name)- it works OK, but is it possible to use alias name that is
not causing conflict with field names WITHOUT having to rewrite code
retreiving results (because of column name change)?
The same situation occurs with HAVING clause - using alias name of
calculated field throws ambiguity error because i'm joining tables with
fields named like alias - although they never appear as result fields
directly (they are used only in expression of alias field).
If you could help me with this mater, I would be very thankful. Any
tricks, hacks...
Thanks in advance,
With regards,
Krzysztof Kotowicz
Re: [sqlite] Re: AUTOINCREMENT Functionality by default required in 3.0.8 (Is it possible?)
Hi John You are completely correct, it was just a hasty example to show that triggers may solve teh problem. If you want a unique variable you probably have to use a 1 row table as an index counter. % cat test.sql -- example showing how to use an increment unique value with sqlite .header on create table test ( num integer, num2integer, infotext, primary key (num) ); -- one value index database for unique indexnumber create table myindex ( id integer, num integer, primary key (id) ); -- trigger which increases num2 with a unique index create trigger increment_sum_in_test after insert on test begin update myindex set num = num+1 where id=0; update test set num2 = ( select num from myindex where id=0 ) where num=new.num; end; -- first set up myindex so the trigger wont complain insert into myindex (id,num) values (0,0); insert into test (num,info) values (null,'number one'); insert into test (num,info) values (null,'number two'); insert into test (num,info) values (null,'number three'); delete from test where info='number three'; delete from test where info='number one'; insert into test (num,info) values (null,'number four'); insert into test (num,info) values (null,'number five'); select num,info,num2 from test; $ sqlite3 text.db < test.sql num|info|num2 2|number two|2 3|number four|4 4|number five|5 -- MortenB --- John LeSueur <[EMAIL PROTECTED]> skrev: > what happens If you do: > > delete from test where num = 2; > insert into test (num, info) values(null, 'number > four'); > > Wouldn't you have: > > num|info|num2 > 1|number one|1 > 3|number three|3 > 3|number four|3 > > John LeSueur > >
Re: [sqlite] Is it bug?
It has to be text I guess. It can't be a double, as the IEEE format SQLite uses doesn't support rounding to exact numbers of decimal places. --- Witold Czarnecki <[EMAIL PROTECTED]> wrote: > 3.0.8 > > - Original Message - > From: "Ulrik Petersen" <[EMAIL PROTECTED]> > To: > Sent: Saturday, February 19, 2005 11:57 AM > Subject: Re: [sqlite] Is it bug? > > > > Witold Czarnecki wrote: > > > >> sqlite> select typeof(round(1)); > >> text > >> > >> Is it bug? > > > > What version? > > > > Ulrik Petersen > > > > > __ Do you Yahoo!? Yahoo! Mail - Easier than ever with enhanced search. Learn more. http://info.mail.yahoo.com/mail_250
[sqlite] Updated simple Delphi wrapper
I've updated the wrapper at: http://www.itwriting.com/sqlitesimple.php for Sqlite 3.1.2. Sqlite 3.1 changes the way column names are returned, so I've added a call to set the Pragma full_column_names on. I've also amended the field type detection to use the actual type when the declared type is not available, and added the utility function TableExists. The test application now includes a demonstration of one way to load, save and display images in a Sqlite 3 database. Tim
[sqlite] strcasecmp build error in VC++
I got a linker error when building sqlite3.exe with Visual Studio .NET 2003, sqlite version 3.1.2. The linker could not find strcasecmp (shell.c line 659). I replaced strcasecmp with _stricmp to get it to build. Tim
Re: [sqlite] Is it bug?
3.0.8 - Original Message - From: "Ulrik Petersen" <[EMAIL PROTECTED]> To: Sent: Saturday, February 19, 2005 11:57 AM Subject: Re: [sqlite] Is it bug? Witold Czarnecki wrote: sqlite> select typeof(round(1)); text Is it bug? What version? Ulrik Petersen
Re: [sqlite] Is it bug?
Witold Czarnecki wrote: sqlite> select typeof(round(1)); text Is it bug? What version? Ulrik Petersen
[sqlite] Is it bug?
sqlite> select typeof(round(1)); text Is it bug? Best regards, Witold
