Re: [sqlite] Datatype for prices (1,500)

[Miha Vrhovnik]

On 2016-11-30 16:39, Werner Kleiner wrote:

<< All you need to store in the database table is the number. How it is
formatted for viewing or printing is controlled by the user interface.

yes thats right, but it would be nice to have same behavior between
MySQL and sqlite
I think to use a char datatype is not a good solution.

No using a char datatype is not a solution AS it is not a solution for 
SQLite to implement everything in a way that's compatible with database 
X.


You were given a good recommendation save everything in "cents". Which 
might also be a good solution depending on the underlying language you 
use. as you can't store money in a float!


BR,
Miha
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposed new sqlite3_open_v3() in terface - offt

[Miha Vrhovnik]

"Andy Gibbs" <andyg1...@hotmail.co.uk> wrote on 1.7.2010 21:12:57:
>Miha Vrhovnik wrote on 1/7/2010:
>> >Content analysis details:   (10.3 points, 7.0 required)
>> >
>> > pts rule name description
>> > -- 
>> >--
>> > 2.4 DNS_FROM_OPENWHOIS RBL: Envelope sender listed in bl.open-whois.org.
>> >-0.0 SPF_HELO_PASS SPF: HELO matches SPF record
>> >-0.0 SPF_PASS SPF: sender matches SPF record
>> > 1.5 MSGID_FROM_MTA_HEADER Message-Id was added by a relay
>> > 2.2 FAKE_REPLY_C FAKE_REPLY_C
>> > 4.2 FORGED_MUA_OUTLOOK Forged mail pretending to be from MS Outlook
>> 
>> Andy please change your mail client... also your previous message had around 
>> 8 points...
>
>I'm sorry, I was not aware that I needed a certain number of points to post!? 
>:o)
>
>More seriously though... I think your spam blocker may need updating. "Forged 
>mail
>pretending to be from MS Outlook" -- well, actually it wasn't pretending or 
>least only
>to the level that MS software *may* pretend! But since I have a hotmail 
>account, maybe
>the hotmail SMTP server is causing the problem?
>
>Does this mail come with a better rating? Sent direct from the hotmail website 
>this
>time!

Yep, Only 2.4 (2.4 DNS_FROM_OPENWHOIS RBL: Envelope sender listed in 
bl.open-whois.org)

Regards,
Miha
-- 
It's time to get rid of your current e-mail client ...
... and start using si.Mail.

It's small & free. ( http://www.simail.si/ )
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Proposed new sqlite3_open_v3() in terface - offt

[Miha Vrhovnik]

"Andy Gibbs"  wrote on 1.7.2010 15:05:01:

>Content analysis details:   (10.3 points, 7.0 required)
>
> pts rule name  description
> -- --
> 2.4 DNS_FROM_OPENWHOIS RBL: Envelope sender listed in bl.open-whois.org.
>-0.0 SPF_HELO_PASS  SPF: HELO matches SPF record
>-0.0 SPF_PASS   SPF: sender matches SPF record
> 1.5 MSGID_FROM_MTA_HEADER  Message-Id was added by a relay
> 2.2 FAKE_REPLY_C   FAKE_REPLY_C
> 4.2 FORGED_MUA_OUTLOOK Forged mail pretending to be from MS Outlook

Andy please change your mail client... also your previous message had around 8 
points...

Regards,
Miha
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sorting records in random order

[Miha Vrhovnik]

I think he's trying to do sth. like that

SELECT quote FROM quotes ORDER BY RAND() LIMIT 1

On 5/8/2008, "John Stanton" <[EMAIL PROTECTED]> wrote:

>Sorting in random order is a definite contradiction in terms.  What are
>you actually trying to do?
>
>Barbara Weinberg wrote:
>> Hi
>> I was wondering whether anyone had tried sorting records in random order
>> using sqlite3. I tried sorting by random() and randomblob() but it was very
>> slow and chewed up lots of resources. Any suggestions?
>> Barbara
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>___
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Unicode searches

[Miha Vrhovnik]

Someone sent a sqlite3_unicode.c file to this mailing list in the last week of 
December, 1st week of January which implemented upper/lower and some other 
functions. File was released as public domain if I  remember correctly and used 
data from Unicode 5.1 standard.

As ICU brings a lot of bulk into the mix and that file brought I believe about 
80k to final library size. It would be nice if that get incorporated into the 
tree, of course with defines for those who don't write Unicode data to db.

Regards,
Miha

"Dan" <[EMAIL PROTECTED]> wrote on 07.04.2008 8:18:50:
>
>On Apr 6, 2008, at 5:10 AM, Keith Stemmer wrote:
>
>> Yes, I can add a custom collation which works for ASCII chars LOL.
>> If you don't understand the problem, just don't reply.
>>
>> By the way, you can read on the SQLite website that the developer  
>> describes
>> my problem as a BUG which is nice to read. At least he doesn't call  
>> it a
>> feature.
>
>The answer you were provided with is correct and canonical in my  
>opinion.
>
>The SQLite source archive (.tar.gz, not sure about the preprocessed
>versions) contains source code for an sqlite extension that binds
>the ICU library to SQLite using the custom collation sequence interface
>(and others). With this extension, SQLite uses the upper/lower case
>tables that are part of unicode.
>
>See ext/icu/README in the source distro for details.
>
>Dan.
>
>
>
>> Keith.
>>
 Sort order is highly dependent on locale.  You can add custom
 collations to do this.
>>
>> On Sat, Apr 5, 2008 at 11:58 PM, Cory Nelson <[EMAIL PROTECTED]>  
>> wrote:
>>
>>> They are one and the same.  Look up collations.
>>>
>>> On Sat, Apr 5, 2008 at 2:55 PM, Keith Stemmer
>>> <[EMAIL PROTECTED]> wrote:
 That was not was I was talking about. I was not talking about  
 Sort Order
>>> but
  about Searches.
  Keith



  On Sat, Apr 5, 2008 at 11:42 PM, Cory Nelson <[EMAIL PROTECTED]>  
 wrote:

> Sort order is highly dependent on locale.  You can add custom
> collations to do this.
>
> On Sat, Apr 5, 2008 at 10:41 AM, Keith Stemmer
> <[EMAIL PROTECTED]> wrote:
>> Hello!
>>
>>  I found SQLite quite amazing, but I think there is one  
>> showstopper
>>> for
> me.
>>  It seems that searches for Unicode strings are case sensitive and
>>> there
> is
>>  no (easy) way around that.
>>  Could you please confirm or deny this?
>>
>>  Your explanation...
>>
>>  (A bug: SQLite only understands upper/lower case for 7-bit Latin
> characters.
>>  Hence the LIKE operator is case sensitive for 8-bit iso8859
>>> characters
> or
>>  UTF-8 characters. For example, the expression 'a' LIKE 'A' is  
>> TRUE
>>> but
> 'æ'
>>  LIKE 'Æ' is FALSE.).
>>
>>  seems to destroy all my hopes.
>>
>>  Thank you very much!
>>>
>>> --
>>> Cory Nelson
>>> http://www.int64.org


-- 
It's time to get rid of your current e-mail client ...
... and start using si.Mail.

It's small & free. ( http://www.simail.si/ )
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3Explorer Version 3.0 is available

[Miha Vrhovnik]

Hi Mike,

Thanks for updated version, but still no drag n drop support for opening 
database files :(


Regards,
Miha

"Cariotoglou Mike" <[EMAIL PROTECTED]> wrote on 29.11.2007 9:09:59:
>New version with a lot of enhancements to the user interface, also
>updated to be compatible with
>the latest sqlite dll.
>
>information, change list and download here :
>
>http://www.singular.gr/sqlite
>
>(New features are described in the link near the top).
>
>Documentation, such as it is,  is out of date :)
>
>
>-
>To unsubscribe, send email to [EMAIL PROTECTED]
>-
>
>


--
It's time to get rid of your current e-mail client ...
... and start using si.Mail.

It's small & free. ( http://simail.sourceforge.net/ )

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: Re: [sqlite] How is the windows binary built?

[Miha Vrhovnik]

>  strip sqlite3.exe
is not ok at least for DLL, because it strips everything even reallocation info.
strip --strip-unneeded sqlite3.dll

Regards,
Miha

"Joe Wilson" <[EMAIL PROTECTED]> wrote on 21.4.2007 5:48:00:
>--- [EMAIL PROTECTED] wrote:
>> I'm using cygwin under windows XP.
>> gcc version 3.4.4
>>
>> I unzipped the sqlite-3.3.16.tar.gz to the directory sqlite-3.3.16.
>>
>> Executed the following:
>>
>> cd sqlite-3.3.16
>> mkdir build
>> cd build
>> ./configure
>> make
>>
>> The resulting sqlite3.exe is 4 times bigger than the windows release in 
>> sqlite-3.3.16.zip.
>>
>> Can anyone explain the size difference?
>
>The debug symbols.
>
>  strip sqlite3.exe
>
>The sqlite3.exe posted on the website is built with a MinGW gcc cross
>compiler hosted on Linux. It is not dependent on cygwin DLLs, unlike the
>cygwin version:
>
># cygwin
>$ cygcheck ./sqlite3.exe
>.\sqlite3.exe
>  c:\cygwin\bin\cygwin1.dll
>C:\WINNT\system32\ADVAPI32.DLL
>  C:\WINNT\system32\NTDLL.DLL
>  C:\WINNT\system32\KERNEL32.DLL
>  C:\WINNT\system32\RPCRT4.DLL
>  c:\cygwin\bin\cygreadline6.dll
>c:\cygwin\bin\cygncurses-8.dll
>C:\WINNT\system32\USER32.dll
>  C:\WINNT\system32\GDI32.DLL
>
># MinGW
>$ cygcheck ./sqlite3.exe
>.\sqlite3.exe
>  C:\WINNT\system32\KERNEL32.dll
>C:\WINNT\system32\NTDLL.DLL
>  C:\WINNT\system32\msvcrt.dll
>
>DLLs aside, the cygwin version has the advantage of playing nice with
>rxvt and xterm.
>
>__
>Do You Yahoo!?
>Tired of spam?  Yahoo! Mail has the best spam protection around
>http://mail.yahoo.com
>
>-
>To unsubscribe, send email to [EMAIL PROTECTED]
>-
>
>


--
It's time to get rid of your current e-mail client ...
... and start using si.Mail.

It's small & free. ( http://simail.sourceforge.net/ )

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: Re: [sqlite] Re: How to optimize a select that gets 17 rows from a 700k row DB (via perl using DBI)?

[Miha Vrhovnik]

I'm gonna cut all the content and say just one thing.
If Sqlite supported table partitioning this would be piece of cake without any 
complications.

http://www.postgresql.org/docs/8.2/static/ddl-partitioning.html

--
It's time to get rid of your current e-mail client ...
... and start using si.Mail.

It's small & free. ( http://simail.sourceforge.net/ )

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] How to create DLL for windows?

[Miha Vrhovnik]

Why don't you use mingw + msys enviroment?
Google for more.

Regards,
Miha

"Yuvaraj Athur Raghuvir" <[EMAIL PROTECTED]> je ob 3.10.2006 3:19:21 napisal(a):


>Hello,
>
>I have sync'ed and created the exe using Cygwin on Windows. I also have the
>libtool available.
>
>Now, I donot see the dll that I can use in Windows environment.
>
>How to create a dll?
>
>Regards,
>Yuva
>



--
It's time to get rid of your current e-mail client ...
... and start using si.Mail.

It's small & free. ( http://simail.sourceforge.net/ )

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] new file / existing table testing.

[Miha Vrhovnik]

"Tony Harris" <[EMAIL PROTECTED]> je ob 18.6.2006 8:03:03 napisal(a):

>Or is there a way within the api that I haven't found to test for existance of 
>tables?
SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;
SELECT name FROM sqlite_master WHERE type='index' ORDER BY name;

Regards,
Miha

Re: Re: [sqlite] WHERE clause syntax error

[Miha Vrhovnik]

>Something like:
>
>SELECT A.ID
>FROM A
>LEFT JOIN B
>ON A.ID = B.ID
>WHERE B.ID IS NULL
>
>Might be more efficient?
Nope. I rewrote such query to SELECT ID FROM A WHERE ID NOT IN (SELECT ID FROM 
B);

SELECT COUNT(*) AS unread FROM mboxes WHERE
 idMbox = %d AND idMsg IN (SELECT idMsg FROM
 msgStatus WHERE idStatus = %d);

Is almost instant
Where as
SELECT COUNT(*) AS unread FROM msgStatus AS b
  JOIN mboxes AS a ON b.idMsg = a.idMsg WHERE a.idMbox = %d
  AND b.idStatus = %d;

Would take about 9 seconds.
mboxes has 18.000 rows, msgStatus 23.000
So if one would like to argue, I can provide additional details.

Regards,
Miha

Re: [sqlite] questions about performance

[Miha Vrhovnik]

>This problem has been coming up with more and more frequency.
>I need to either figure out a solution or at least write up
>some official documentation on it.
I think that table partitioning as I purposed about a month ago would be
a solution if data is of nature, that it can be partitioned by any
column.

Regards,
Miha

[sqlite] support for table partitioning?

[Miha Vrhovnik]

Hi drh and others,

Regarding the issues they appear on ML with very large tables and knowing
that sqlite now enforces constraint checks on tables, is there any
chances of  suporting table partitoning?


Regards,
Miha

Re: [sqlite] support for table partitioning?

[Miha Vrhovnik]

On 3/21/2006, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote:

>"Miha Vrhovnik" <[EMAIL PROTECTED]> wrote:
>> Hi drh and others,
>> 
>> Regarding the issues they appear on ML with very large tables and knowing
>> that sqlite now enforces constraint checks on tables, is there any
>> chances of  suporting table partitoning?
>> 
>
>Put each table in a separate database file then ATTACH
>the databases.
>--
>D. Richard Hipp   <[EMAIL PROTECTED]>
>
That's not the same. You still need to rewrite queries, where in real
table partitioning you don't need to do that. The select's and inserts
are faster because db knows where to put/search for them.

Regards,
Miha

[sqlite] bug in NOT EXISTS?

[Miha Vrhovnik]

using sqlite 3.2.8.

CREATE TABLE movies (id INTEGER PRIMARY KEY, catalogIDNumeric INTEGER);
INSERT INTO movies (catalogIDNumeric) VALUES(1);
INSERT INTO movies (catalogIDNumeric) VALUES(2);
INSERT INTO movies (catalogIDNumeric) VALUES(3);
INSERT INTO movies (catalogIDNumeric) VALUES(5);
INSERT INTO movies (catalogIDNumeric) VALUES(7);
INSERT INTO movies (catalogIDNumeric) VALUES(8);

CREATE TEMP TABLE tmpCatalogID (catalogIDNumeric INTEGER);
INSERT INTO tmpCatalogID VALUES(1);
INSERT INTO tmpCatalogID VALUES(2);
INSERT INTO tmpCatalogID VALUES(3);
INSERT INTO tmpCatalogID VALUES(4);
INSERT INTO tmpCatalogID VALUES(5);
INSERT INTO tmpCatalogID VALUES(6);
INSERT INTO tmpCatalogID VALUES(7);
INSERT INTO tmpCatalogID VALUES(8);

query returns empty resultset instead of number 4:
SELECT MIN(catalogIDNumeric) FROM tmpCatalogID WHERE NOT EXISTS (SELECT 
catalogIDNumeric FROM movies);

thus
SELECT MIN(catalogIDNumeric) FROM tmpCatalogID WHERE EXISTS (SELECT 
catalogIDNumeric FROM movies);

returns 1.

For the time beeing I rowrote query so it uses IN keyword.

Regards,
Miha

Re: [sqlite] sqlite3_create_function in delphi

[Miha Vrhovnik]

juan perez <[EMAIL PROTECTED]> je ob 3.12.2005 17:16:00 napisal(a):

>There is a "nocase" collation for that purpose.
>
>jp
Which works only on ASCII characters I need unicode version of Lower function.

Regards,
Miha

[sqlite] sqlite3_create_function in delphi

[Miha Vrhovnik]

Does anybody know how to add custom function to sqlite3 in Delphi? Cariotoglou 
Mike?

I'd like to add function 'Lower' so I can match case insenisitive text columns 
in table.

Regards,
Miha

Re: Re: [sqlite] Rewriting a query

[Miha Vrhovnik]

[EMAIL PROTECTED] je ob 30.9.2005 12:22:47 napisal(a):

>(Side note: I am experimenting with a new Mail User Agent that
>uses SQLite to store all its email messages.  I appologize in advance
>if this message is misformatted or otherwise garbled.)

Am. Who stole that idea from me?

--
It's time to get rid of your current e-mail client ...
... and start using si.Mail.

http://simail.sourceforge.net/

[sqlite] Re: bug in SELECT DISTINCT ?

[Miha Vrhovnik]

"Igor Tandetnik" <[EMAIL PROTECTED]> je ob 17.9.2005 16:20:03 napisal(a):

>Why did you expect the latter? DISTINCT in SELECT DISTINCT detects
>identical rows, not identical first columns. The three first rows with
>an id of 1 differ in the columns you chose not to show (specifically in
>the map.d0 column), that's why all three are returned.
>
>The second query is equivalent to
>
>SELECT * FROM t1, map WHERE t1.id = map.idT1 AND id IN (1, 2, 3);
>
>Of course it returns all rows.
>

So How can I select DISTINCT ON 1st column OR any other.
as sqlite does not support DISTINCT ON (column1, column2, )

Regards,
Miha

[sqlite] bug in SELECT DISTINCT ?

[Miha Vrhovnik]

Hi!

I think there is a bug in SELECT DISTINC. I'm using sqlite 3.2.5 dll

CREATE TABLE t1(id INTEGER PRIMARY KEY, d0 INTEGER, d1 TEXT);
CREATE TABLE map(idT1 INTEGER , d0 INTEGER);

INSERT INTO t1 VALUES(1, 1, 'dd');
INSERT INTO t1 VALUES(2, 1, 'dd');
INSERT INTO t1 VALUES(3, 1, 'dd');

INSERT INTO map VALUES(1, 2);
INSERT INTO map VALUES(1, 3);
INSERT INTO map VALUES(1, 4);
INSERT INTO map VALUES(2, 2);
INSERT INTO map VALUES(3, 2);

both
SELECT DISTINCT id, * FROM t1, map WHERE t1.id = map.idT1;
and
SELECT * FROM t1, map WHERE t1.id = map.idT1 AND id IN (SELECT DISTINCT idT1 AS 
id FROM map WHERE d0 = 2);

return
1, ...
1, ...
1, ...
2, ...
3, ...

instead of
1, ...
2, ...
3, ...

Regards,
Miha

Re: [sqlite] Problems with threadsafe opt correction #2623

[Miha Vrhovnik]

"D. Richard Hipp" <[EMAIL PROTECTED]> je ob 5.9.2005 18:45:42 napisal(a):

>SQLite has *never* supported the ability of a handle to be used
>by more than one thread.  By luck, such use would sometimes work on
>some operating systems.  But it would fail on others.  Such a
>situation is very dangerous since if a developer is working on
>a system where the misuse of SQLite just happened to work, they
>might not detect their design error and then ship non-working code
>to a customer where it would fail.  A minor change in check-in [2517]
>detects the misuse of SQLite on all systems and prevents it from
>working even by chance.  This allows the problem to be detected
>early and corrected before it reaches a customer.
>
>This is not a design change.  This is not an API change.
>It does not break backwards compatibility.  All that changed
>is that certain errors in the way SQLite is used are now
>detected sooner rather than later.
>
So what does that mean.

On windows I'm protecting database handle with MREWS.
Should I rewrite about 10.000 lines of code. And Check other 30.000. To fix 
that?

Regards,
Miha

RE: [sqlite] bug in sqlite 3.2.5 compila tion and ANALYZE

[Miha Vrhovnik]

"Cariotoglou Mike" <[EMAIL PROTECTED]> je ob 5.9.2005 14:49:54 napisal(a):

>ok.
>I use sqlite3Explorer (which should not matter), I create a new
>database, then I run :
>
>create table test1(id integer);
>analyze
>
>this crashes immediately, with dll 3.2.5. it works fine from the
>command-line.
>
I can confirm that. But it seems that the problem is in Sqlite explorer or your 
sqlite3.dll wrapper implementation.
When I try that with my own Delphi wrapper implementation it works. But opening 
database with sqlite explorer results in crash.

Regards,
Miha

[sqlite] bug in ORDER BY ?

[Miha Vrhovnik]

Hi,

sqlite dll is 3.2.1

I have the folowing query:

SELECT * FROM Mactor WHERE id < 9223372036854775807 ORDER BY id DESC LIMIT 1;

where 9223372036854775807 is Maximum value of signed Int64.

Table is defined as:

CREATE TABLE Mactor (
id INTEGER PRIMARY KEY,
name TEXT,
birthName TEXT,
birthday DATE,
gender INTEGER,
idCountry INTEGER,
idProvince INTEGER,
imdb TEXT,
url TEXT,
otherWork TEXT,
biographiy TEXT,
comment TEXT,
pictures TEXT,
custom TEXT);

There are two records in table one with id = 1 and other with id = 2

The query retuns an empty "dataset" BUT it works as soon as I remove ORDER BY 
clause.

P.S. If anybody has better Idea of how to get the last/first row (the one with 
highest/lowest ID) then comments are welcome.

Regards,
Miha

+*+
It's time to get rid of your current e-mail client ...
... and start using si.Mail.

It's small & free. ( http://simail.sourceforge.net/ )
+*+