Re: [sqlite] Date Selection

[Federico Granata]

On Thu, Jun 12, 2008 at 5:50 AM, Harold Wood <[EMAIL PROTECTED]> wrote:

> Hello Igor
> the create table statement:
>
> CREATE TABLE Items
>  (
>  ID INT NOT NULL PRIMARY KEY ASC,
>  SubCatId  INT NOT NULL,
>  Description VARCHAR(60) NOT NULL,
>  LastUnitPrice NUMERIC(6,2) DEFAULT 0.0,
>  AvgUnitPrice NUMERIC(6,2) DEFAULT 0.0,
>  MinUnitPrice NUMERIC(6,2) DEFAULT 0.0,
>  LastPurchaseDate DATETIME,
>  LastQtyPurchase  NUMERIC(6,2) DEFAULT 0.0,
>  PurchaseUnit VARCHAR(20),
>  NumTimesPurchased INT DEFAULT 0,
>  NeedIt  BIT DEFAULT 0,
>  FOREIGN KEY (SubCatId) REFERENCES Subcategory(ID)
>  );
>
where is DateCol ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL question

[Federico Granata]

sqlite> create table t1(n,c);
sqlite> insert into t1 values("a",3);
sqlite> insert into t1 values("a",5);
sqlite> insert into t1 values("b",7);
sqlite> insert into t1 values("b",2);
sqlite> select * from t1;
a|3
a|5
b|7
b|2
sqlite> select n,max(c) from t1 group by n;
a|5
b|7


--
[image: Just A Little Bit Of
Geekness]
Le tre grandi virtù di un programmatore: pigrizia, impazienza e arroganza.
(Larry Wall).

On Fri, Jun 6, 2008 at 11:03 AM, Andrea Galligani <
[EMAIL PROTECTED]> wrote:

> Hi to all,
>
> I'm a novice in SQL and SQLite so I apologize if this question has an
> obvious solution
>
> I have a table formed in this way.
>
> NAME   SCORE   INFO1   INFO2   etc.
> Andrew   5 aaa bbb ...
> Andrew   8 ddd eee ...
> Paul 4 xxx yyy ...
> Paul 6 aaa fff ...
>
> I need a query to get from any name the row with the max score. So from
> the above sample, I would like to obtain the following rows.
>
> NAME   SCORE   INFO1   INFO2   etc.
> Andrew   8 ddd eee ...
> Paul 6 aaa fff ...
>
> Can I get this result using a complex query or should I filter the rows
> using any different tool from SQL (C/C++)?
>
> Thanks in advance
> Andrea
>
> --
> --
> Andrea Galligani
>
> Macs Tech s.r.l.
> Via San Paolo 11, 56125
> Pisa - Italy
>
> Phone...: +39 050 40915
> e-mail..: [EMAIL PROTECTED]
> --
>
> ___
> 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] How to speed up my queries?

[Federico Granata]

can you post those rows with
.mode insert
so I can do a fast try ?
Tnx.

--
[image: Just A Little Bit Of
Geekness]
Le tre grandi virtù di un programmatore: pigrizia, impazienza e arroganza.
(Larry Wall).

2008/6/4 Christophe Leske <[EMAIL PROTECTED]>:

>
> > Can you give me some row of your db (also fake data are ok) so I try to
> > populate a db with 840k row and test your query on my machine ...
> >
> You can either take these rows here:
>
> Pietraporzio|5|-1|7.032936|44.345913
> Sambuco|5|-1|7.081367|44.33763
> Le Pra|6|-1|6.88|44.316667
> Bagni|6|-1|7.08|44.3
> Argentera|6|-1|6.937569|44.396168
> Bersezio|6|-1|6.970739|44.377898
> Saint-Dalmas-le-Selvage|6|-1|6.867705|44.285194
> Ferrere|6|-1|6.950052|44.354508
> San Bernolfo|6|-1|7.039278|44.263371
> Murenz|6|-1|6.998868|44.348969
> Bagni di Vinadio|6|-1|7.074884|44.290033
>
> or you can download a 170 MB big database similar to the one i am using,
> but populatet with free data from geonames.org from
>
> http://www.multimedial.de/earth/DB/free.7z
>
> That one is 1.4 Gb unpacked, and has yet no indices (i think, i prepared
> it some time ago).
>
> It should yield to similar results.
>
> Thanks for your time,
>
> --
> Christophe Leske
>
> www.multimedial.de - [EMAIL PROTECTED]
> http://www.linkedin.com/in/multimedial
> Lessingstr. 5 - 40227 Duesseldorf - Germany
> 0211 261 32 12 - 0177 249 70 31
>
>
> ___
> 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] How to speed up my queries?

[Federico Granata]

>
> Can someone tell me what kind of performance one is to expect from a
> 40Mb Sqlite database like the one I have?
>
if you put it on a floppy and throw  it out of the window it fall at 9.8 m/s
...

Can you give me some row of your db (also fake data are ok) so I try to
populate a db with 840k row and test your query on my machine ...
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Speed-Lost on using expression with combined "AND" and "OR"!

[Federico Granata]

2008/6/4 Markus Wolters <[EMAIL PROTECTED]>:

> Well why not? They've just integrated the ADO.NET provider into the DLL.
> It IS SQLITE...
>
I have just quote what they write: "It is a complete drop-in replacement for
the original sqlite3.dll" I don't know what they rewrite and how ...
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Speed-Lost on using expression with combined "AND" and "OR"!

[Federico Granata]

"*System.Data.SQLite *is an enhanced version of the original SQLite
database engine.  It is a complete drop-in
replacement for the original sqlite3.dll"

If you don't use original sqlite I think you can't get any help here ...

--
[image: Just A Little Bit Of
Geekness]
Le tre grandi virtù di un programmatore: pigrizia, impazienza e arroganza.
(Larry Wall).

2008/6/4 Markus Wolters <[EMAIL PROTECTED]>:

> Hi,
>
>
>
> I've got a problem in combining expressions. On combining multiple ANDs
> with
> an OR within a SELECT WHERE clause, SQLite takes years to respond.
> (Database
> has a size of only about 1 MB).
>
> Using two SELECTs and combining them by UNION works like a charm! What
> could
> be the problem here?
>
>
>
> This statement takes about 10 seconds after result can be read:
>
>
>
> SELECT <.> FROM Scenes AS s, Movies AS m, Categories AS c WHERE m.Id =
> s.MovieId AND ((s.MovieId = c.MovieId AND s.SceneNo = c.SceneNo AND
> c.CategorieId IN (60)) OR Rating IN (2))
>
>
>
> Working statement (immediate response):
>
>
>
> SELECT <.> FROM Scenes AS s, Movies AS m, Categories AS c WHERE m.Id =
> s.MovieId AND s.MovieId = c.MovieId AND s.SceneNo = c.SceneNo AND
> c.CategorieId IN (410)
>
> UNION
>
> SELECT <.> FROM Scenes AS s, Movies AS m WHERE m.Id = s.MovieId AND
> s.Rating
> IN(1)
>
>
>
> I am using System.Data.SQLite from http://sqlite.phxsoftware.com/
>
>
>
> Thanks for any response!
>
>
>
> Regards,
>
> Markus
>
>
>
>
>
>
>
> ___
> 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] transaction recovery question

[Federico Granata]

2008/6/3 Darko Filipovic <[EMAIL PROTECTED]>:

> But, what happen if journal file is deleted before starting B process?
>
what if a UFO stole your pc ? :-D

try to delete journal file and see what happens ...
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite allows "RowID" to be the name of a column

[Federico Granata]

>
> The normal proper way to do what you said is to declare a table like this:
>
>   CREATE TABLE person (
> person_id INT PRIMARY KEY,
> name TEXT,
> birthdate DATE
>   )
>
> In my example, you are using only the normal data, which is the 3 columns
> specified, and you are not referring to a column you didn't declare
> ("rowid" or whatever), but by a column you did declare, "person_id".
>
> By contrast, defining a table like this is inferior:
>
>   CREATE TABLE person (
> name TEXT,
> birthdate DATE
>   )
>
> In that example, the rowid would be generated and you can't use something
> externally meaningful (such as SSN) to distinguish one Joe from another.

I can use the generated rowid to link this table to another containing
meaningful data or I don't need other data besides the ones in this table.

I can use an explicitly defined rowid or the automatically generated one ...


> My point still stands.  Or my other point of adding a LIMIT clause to
> UPDATE also stands if you want to create tables the second way.

you can use LIMIT in UPDATE only if data isn't meaningful (in this case you
can habe only one row), otherwise ignoring the generated rowid would break
the db
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite on RAM

[Federico Granata]

>
> > > For windows the easiest thing to do would be setting up the ram disk
> and
> > > store database there.
> >
> > easier than using :memory: ???
>
>   Not easier, but sometimes more useful.  Using a RAM disk means going
>  through the OSes file manager, which adds some overhead.  On the
>  other hand, you can copy the database file to (or from) a more
>  traditional storage-backed filesystem at any time... something you
>  can't do with a :memory: database.
>
I can dump a :memory: db ... but I get your point
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite allows "RowID" to be the name of a column

[Federico Granata]

>
> Your example doesn't counter my suggestion at all, which is to use the data
> only and not a special rowid.  So you put 2 identical rows in a table.
> Since rows in a table are unordered, there isn't even an ordinal position
> to distinguish the 2 occurrences of that same row.  Since they are
> identical, they are redundant, and so they are equivalent to just 1 such
> row.  So updating both copies is perfectly fine.  Though better yet is to
> not store a second copy in the first place.
>
LOL
English isn't my first language but I think you are joking ...

If I want to make a table with a list of people (name and age) I can have
two or more row with the same name and age and they aren't redundant and the
implicit rowid is different.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite on RAM

[Federico Granata]

>
> For windows the easiest thing to do would be setting up the ram disk and
> store database there.

easier than using :memory: ???
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite allows "RowID" to be the name of a column

[Federico Granata]

>
> But thinking more about hijacking "RowID" I am glad this is now a separate
> thread. Lack of a reseverd "RowID" column name to guarantee unambiguous
> record operations by general SQLite tools is a potential thread to data
> security IMO.
>
> I would very much appreciate if this could be addressed in a future version
> of SQLite!
>
have you seen here http://www.sqlite.org/autoinc.html ?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite remote management tools?

[Federico Granata]

>
> I was hoping there might be a client/server management tool out there.
> I would need the source code, since the server part would need to be
> ported to my embedded device.
>
Maybe you haven't yet read this http://www.sqlite.org/serverless.html
There isn't a sqlite server so you can't have a sqlite client, local or
remote.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] development vs production

[Federico Granata]

2008/5/10 sebastian stephenson <[EMAIL PROTECTED]>:

>
> I see that sqlite is great for development but for production would
> that be a bright idea?
> see ya
>
it you develop a single user embedded sw than sqlite is a good solution, if
you develop a db for a really BIG with many concurrent write access then
sqlite is a bad solution

P.S. sorry for my english
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] splite database under version control (subversion)?

[Federico Granata]

If you use svn only to sync over many pc maybe you can put your project on a
usb device (or usb hd) so you don't need to "sync" with svn.

If you WANT to use svn and you are under linux you can create an alias for
commit that make a dump of your db then commit it and one alias that update
and read the dump in db.

Maybe in windows you can make a batch, I don't know windows very well.

--
[image: Just A Little Bit Of
Geekness]
Le tre grandi virtù di un programmatore: pigrizia, impazienza e arroganza.
(Larry Wall).

On Sun, May 4, 2008 at 10:36 PM, M. Emal Alekozai <[EMAIL PROTECTED]> wrote:

> Hi,
> > In the BIG db I have worked on there is a table that log every
> insert/update
> > on specific and important tables and a log of every sql statement
> execute
> > but I haven't ever see a db under version control with svn (or csv or
> git or
> > any other).
> >
> I'm (mis)using subversion more as a synchronization tool than as a
> version control system. I have a small software project and I have to
> work on this project on different computers (desktop, laptop, ...). To
> synchronize the project on all computers I use subversion. Before
> starting to work on one computer I make an "svn update" for the project
> and after finishing I do a "svn commit". This approach works quite well
> for my case, because in general only text files (source code, latex
> documentation,...) change. The problem with this project is that I
> shifted all the "content" (labels, translation etc.) from the source
> code into a small sqlite database. This clean separation of "logic" and
> "content" makes the update and modification of the project much easier.
> But the drawback is that the sqlite database is in a binary format and
> putting it into subversion is not optimal ;-).
>
> A possible solution would be to export the sqlite database as a list of
> sql commands. This sql commands could be stored in a text file and be
> put under version control. Before starting to work on the project I
> could build from the text file the sqlite database. This approach means
> some extra work but in principle it should work.
>
> Are there any other solutions /approaches for sqlite available?
>
> Thanks
> Emal
>
>
>
> ___
> 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] splite database under version control (subversion)?

[Federico Granata]

In the BIG db I have worked on there is a table that log every insert/update
on specific and important tables and a log of every sql statement execute
but I haven't ever see a db under version control with svn (or csv or git or
any other).

--
[image: Just A Little Bit Of
Geekness]
Le tre grandi virtù di un programmatore: pigrizia, impazienza e arroganza.
(Larry Wall).

On Wed, Apr 30, 2008 at 10:59 PM, <[EMAIL PROTECTED]> wrote:

> Dear Sqlite users,
>
> I want to put a sqlite database under version control (in my case
> subversion).
> As far as I know the sqlite database is a binary file.
> Always exporting and importing the database to a sql file is quite
> laborious .
> What is the standard approach for this ?
> Is there any "filter" available for subversion which does the conversion
> automatically?
>
> Thanks
> Emal
>
> --
> GMX startet ShortView.de. Hier findest Du Leute mit Deinen Interessen!
> Jetzt dabei sein: http://www.shortview.de/[EMAIL PROTECTED]
> ___
> 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] Insert date

[Federico Granata]

On Linux I get

SQLite version 3.5.6
Enter ".help" for instructions
sqlite> CREATE TABLE Sighting (
   ...>  SightingIdinteger PRIMARY KEY AUTOINCREMENT NOT NULL,
   ...>  SpeciesId integer,
   ...>  LocationIdinteger,
   ...>  SightingDate  date,
   ...>  Note  nvarchar(100)
   ...> );
sqlite> INSERT INTO Sighting (SpeciesID,LocationID,SightingDate,Note) VALUES
(3005,22,'2/26/2008','New Note');
sqlite> select * from Sighting;
1|3005|22|2/26/2008|New Note


--
[image: Just A Little Bit Of
Geekness]
Le tre grandi virtù di un programmatore: pigrizia, impazienza e arroganza.
(Larry Wall).

On Fri, Apr 25, 2008 at 6:20 PM, Simon Davies <
[EMAIL PROTECTED]> wrote:

> 2008/4/25 lrjanzen <[EMAIL PROTECTED]>:
> >
> > I have the following table
> ...
> > and the following insert
> > INSERT INTO Sighting (SpeciesID,LocationID,SightingDate,Note)
> > VALUES (3005,22,'2/26/2008','New Note')
> >
> > the insert works EXCEPT the date keeps coming in as NULL! What am I
> doing
> > wrong?
> >
> > Thanks
> > Lonnie
> >
>
> On XP I get
>
> SQLite version 3.4.2
> Enter ".help" for instructions
> sqlite>
> sqlite> CREATE TABLE Sighting (
>...>  SightingIdinteger PRIMARY KEY AUTOINCREMENT NOT NULL,
>   ...>  SpeciesId integer,
>   ...>  LocationIdinteger,
>   ...>  SightingDate  date,
>   ...>  Note  nvarchar(100)
>...> );
> sqlite>
> sqlite> INSERT INTO Sighting (SpeciesID,LocationID,SightingDate,Note)
>   ...> VALUES (3005,22,'2/26/2008','New Note');
> sqlite> select * from sighting;
> 1|3005|22|2/26/2008|New Note
> sqlite>
>
> What operating system, version, wrapper?
>
> Rgds,
> Simon
> ___
> 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] What is faster?

[Federico Granata]

If you are under linux you can use "time" command to execute sqlite with
various query and see which one is faster.

--
[image: Just A Little Bit Of
Geekness]
Le tre grandi virtù di un programmatore: pigrizia, impazienza e arroganza.
(Larry Wall).

On Fri, Apr 25, 2008 at 10:04 AM, Alexander Batyrshin <[EMAIL PROTECTED]>
wrote:

>  Hello people,
>
> I have two SQL commands doing the same thing:
> 1.
> SELECT id FROM foo WHERE expr1
> EXCEPT
> SELECT id FROM bar WHERE expr2
>
> 2.
> SELECT id FROM foo WHERE expr1 AND id not IN (SELECT id FTOM bar WHERE
> expr2)
>
>
> Can you say which one is faster? I prefer second option because I can
> use extra condition like LIMIT.
>
> --
> Alexander Batyrshin aka bash
> bash = Biomechanica Artificial Sabotage Humanoid
> ___
> 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] Trigger on an attached db.

[Federico Granata]

Doing it in my app means polling the other db instead of receive an
"interrupt" via trigger ... I can do it but it's the "polling vs interrupt"
...
Obviously I prefer to sit and wait for data instead of looping looking for
data but if it's the only way ...

--
[image: Just A Little Bit Of
Geekness]<http://feeds.feedburner.com/%7Er/JustALittleBitOfGeekness/%7E6/1>
Le tre grandi virtù di un programmatore: pigrizia, impazienza e arroganza.
(Larry Wall).

On Wed, Apr 23, 2008 at 3:01 PM, P Kishor <[EMAIL PROTECTED]> wrote:

> On 4/23/08, Federico Granata <[EMAIL PROTECTED]> wrote:
> > On Wed, Apr 23, 2008 at 12:22 AM, Igor Tandetnik <[EMAIL PROTECTED]>
> >  wrote:
> >
> >
> >  > Each connection has its own independent temp database. You've created
> a
> >  > temporary trigger which exists in the temp database for your
> connection.
> >  > The trigger simply doesn't exist on the other connection.
> >  >
> >
> > damn ...
> >
> >
> >  Yes, if that other session attaches the appropriate database and
> creates
> >  > an appropriate trigger. No, you cannot magically alter the behavior
> of
> >  > another application you have no control over.
> >  >
> >
> > I don't want to alter the behavior of the other app nor I want to alter
> his
> >  db.
> >  If I create mine table and trigger in the B db everything run smooth
> but I
> >  try to "hook" on some event without disturbing the original behavior.
> >  It's possible at all ?
> >
> ..
>
> do it in your application rather than at the db level.
> ___
> 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] Trigger on an attached db.

[Federico Granata]

On Wed, Apr 23, 2008 at 12:22 AM, Igor Tandetnik <[EMAIL PROTECTED]>
wrote:

> Each connection has its own independent temp database. You've created a
> temporary trigger which exists in the temp database for your connection.
> The trigger simply doesn't exist on the other connection.
>
damn ...

Yes, if that other session attaches the appropriate database and creates
> an appropriate trigger. No, you cannot magically alter the behavior of
> another application you have no control over.
>
I don't want to alter the behavior of the other app nor I want to alter his
db.
If I create mine table and trigger in the B db everything run smooth but I
try to "hook" on some event without disturbing the original behavior.
It's possible at all ?

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

[sqlite] Trigger on an attached db.

[Federico Granata]

Hi, I hope this question isn't a noob one like my last one ...

I have two db, the main one is used from mine sw (call this db A), the other
is used from another sw (call this db B).
I open A, attach B, create a temp trigger in A triggered by insert into a
table in B and writing in a table in A

If I insert into the table in B the trigger is triggered but if the sw
(working on B) insert the same thing in the same table the trigger do
nothing.

db A
CREATE TABLE original(id integer primary key, data text);

db B
attach database 'A.db' as A;
CREATE TABLE sync(id integer primary key, original_id integer, flag
integer);
CREATE TRIGGER to_sync after insert on A.original
begin
  insert into sync (original_id,flag) values(new.id,1);
end;

running from B
sqlite> insert into original(data) values("test");
sqlite> select * from original;
1|test
sqlite> select * from sync;
1|1|1

if I open another instance of sqlite3 for A running another insert
sqlite> insert into original (data) values("test2");

in B I see
sqlite> select * from original;
1|test
2|test2
sqlite> select * from sync;
1|1|1

It's possible to get the trigger run when insert is lunched from another
session ?

--
[image: Just A Little Bit Of
Geekness]
Le tre grandi virtù di un programmatore: pigrizia, impazienza e arroganza.
(Larry Wall).
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] trigger and attached db

[Federico Granata]

Thanks a lot, you are VERY fast ;-)
A temporary trigger it's ok for my purpose.

--
[image: Just A Little Bit Of
Geekness]<http://feeds.feedburner.com/%7Er/JustALittleBitOfGeekness/%7E6/1>
Le tre grandi virtù di un programmatore: pigrizia, impazienza e arroganza.
(Larry Wall).

On Mon, Apr 21, 2008 at 11:50 PM, Scott Hess <[EMAIL PROTECTED]> wrote:

> You cannot create a trigger which crosses between databases because
> the system cannot know that the database will be attached when the
> trigger fires.  Triggers are part of the consistency guarantee, so
> this is bad.
>
> It would be interesting to have something like "CREATE TEMPORARY
> TRIGGER ... " which would only exist so long as the referenced
> databases we attached.  Then you could just create your temporary
> triggers in the same code which attaches your databases.  Likewise for
> views, perhaps.
>
> -scott
>
>
>
>
> On Mon, Apr 21, 2008 at 2:41 PM, Federico Granata
> <[EMAIL PROTECTED]> wrote:
> > Hi, I'm trying to create in the main db a trigger on "after insert on
> > ATTACHED_DB.TABLE" but I get an error
> > "SQL error: trigger TEST_TRIGGER cannot reference objects in database
> > ATTACHED_DB"
> >
> > Can I obtain TABLE_1 in the attached db and TABLE_2 and TRIGGER (who
> read
> > from TABLE_1 and write to TABLE_2) in the main db ?
> >
> > Tanks a lot and sorry for my bad english.
> > ___
> > 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

[sqlite] trigger and attached db

[Federico Granata]

Hi, I'm trying to create in the main db a trigger on "after insert on
ATTACHED_DB.TABLE" but I get an error
"SQL error: trigger TEST_TRIGGER cannot reference objects in database
ATTACHED_DB"

Can I obtain TABLE_1 in the attached db and TABLE_2 and TRIGGER (who read
from TABLE_1 and write to TABLE_2) in the main db ?

Tanks a lot and sorry for my bad english.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Ruby - SQLite 3.0.7

[Federico Granata]

Hi I'm looking for a sqlite 3.0.7 wrapper for ruby (nothin found with wiki).

Can you help me ?
 

 

 --

 Email.it, the professional e-mail, gratis per te: http://www.email.it/f

 

 Sponsor:

 Biscotti perfetti? Metti la pasta dentro allo Sparabiscotti e...click click... 
biscotti pronti per essere infornati!

 Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=2745&d=29-9

[sqlite] Index test on 3.0.7

[Federico Granata]

Hi, I'm testing index on sqlite ver 3.0.7.
I've create a really simple database 
CREATE TABLE tbl1(rowid integer primary key,fld1 text,fld2 integer);
and fill it with 100 row like this
insert into tbl1(fld1,fld2) values("blablablablablablabla",759928);

Then I copy this test.db to test-index.db and add a index (on test-index.db) 
CREATE INDEX ind1 on tbl1(fld2);

When I do something like 
time sqlite3 test.db "select * from tbl1 where fld2=12345;" > /dev/null

real0m3.879s
user0m3.188s
sys 0m0.650s

and
time sqlite3 test-index.db "select * from tbl1 where fld2=12345;" > /dev/null

real0m0.019s
user0m0.006s
sys 0m0.006s

I'm happy... index work very well...

but...
time sqlite3 test.db "select * from tbl1 where fld2>12345;" > /dev/null

real0m21.452s
user0m20.503s
sys 0m0.809s

and
time sqlite3 test-index.db "select * from tbl1 where fld2>12345;" > /dev/null

real1m15.840s
user0m54.799s
sys 0m16.131s

make me very sad :(

It's a bug or mine mistake ?

P.S. Sorry for my english.
 

 

 --

 Email.it, the professional e-mail, gratis per te: http://www.email.it/f

 

 Sponsor:

 Dai più energia al tuo sport! Rigenera il tuo corpo in maniera naturale

* Grazie agli integratori sport che trovi solo su Erboristeria.com

 Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=2379&d=25-9

Re: [sqlite] New SQL Function.

[Federico Granata]

Alle 20:04, mercoledì 18 agosto 2004, Doug Currie ha scritto:
>
> You must link with a math library that includes sqrt.
>
> Perhaps adding -lm (assuming you have libm.a and it has sqrt) to the
> end of the TCC or LTLINK lines in the Makefile will help.

Thanks, now it's ok.




 --

 Email.it, the professional e-mail, gratis per te: http://www.email.it/f



 Sponsor:

 Digitalpix stampa le tue migliori foto digitali

* su vera carta fotografica professionale.

 Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid32&d-8

[sqlite] New SQL Function.

[Federico Granata]

Sorry for my english.

I try to add a new SQL function (sqrt) as described at 
http://www.hwaci.com/sw/sqlite/c_interface.html#cfunc

If I try (for understanding pourpose only) to return the same value I give to 
the function it's all ok, then (for a little experiment) I try to return the 
abs and it's ok again, but when I try to return the sqrt I obtain a compile 
error:

./libtool --mode=link gcc -g -O2 -DTHREADSAFE=1 -DOS_UNIX=1 -DOS_WIN=0 
-DHAVE_USLEEP=1 -I. -I../sqlite/src -DHAVE_READLINE=0  -o 
sqlite ../sqlite/src/shell.c \
libsqlite.la
gcc -g -O2 -DTHREADSAFE=1 -DOS_UNIX=1 -DOS_WIN=0 -DHAVE_USLEEP=1 -I. 
-I../sqlite/src -DHAVE_READLINE=0 
-o .libs/sqlite ../sqlite/src/shell.c  ./.libs/libsqlite.so
./.libs/libsqlite.so: undefined reference to `sqrt'
collect2: ld returned 1 exit status
make: *** [sqlite] Error 1

I have add this :
/*
** Implementation of sqrt by Edo
*/
static void sqrtFunc(sqlite_func *context, int argc, const char **argv){
  assert( argc==1 );
  sqlite_set_result_double(context,sqrt(sqliteAtoF(argv[0],0)));
}

and ...
  } aFuncs[] = {
{ "sqrt",   1, SQLITE_NUMERIC, 0, sqrtFunc   }, // ... this row
{ "min",   -1, SQLITE_ARGS,0, minmaxFunc },

Someone can help me ? (sorry again for my english).
 

 

 --

 Email.it, the professional e-mail, gratis per te: http://www.email.it/f

 

 Sponsor:

 Da 1.500 a 10.000 Euro per realizzare i tuoi desideri con sicurezza e semplicità, 
clicca e richiedi il tuo prestito

 Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=1964&d=18-8

[sqlite] ODBC Driver

[Federico Granata]

Hi,
I try to find a ODBC driver to use on linux with OOo.
Can you help me ?

P.S. Sorry for my english.

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]