Re: [sqlite] Problem opening a new SQLite3 database file

[Nuno Lucas]

On 8/23/07, Dennis Achá <[EMAIL PROTECTED]> wrote:
> I cannot open a new SQLite3 database file through the command prompt.  In
> the
> windows "run" window, I type "SQLite3 mydatabase.db3" and I get the
> following error
> message:
> **
> "Cannot find the file 'SQLite3' (or one of its components). Make
> sure the path and filename are correct and that all required libraries are
> available."
> ***
> The only thing I can do is open the SQLite3.exe file and work directly off
> of it, but I
> cannot save anything.
>
> I have the following files in a folder located on the C drive:
>
> 1. sqlite3.exeapplication
> 2. sqlite3_analyzer   application
> 3. sqlite3.dll
> 4. tclsqlite3.dll
> 5. sqlite3.def
> 6. fts2.def
> 7. fts2.dll
>
> This happens on my two (Win XP, Win 98SE) home computers and my work
> computer (Win 2000).  Can anybody help me figure this out so I can start
> creating my database?  I'd
> greatly appreciate it.  Thanks

My advice to you, as not many people still know how to work well with
the windows command line,  is to get and use one of the sqlite GUIs
that let you use sqlite databases using a graphical manager.

Just select one in the
http://www.sqlite.org/cvstrac/wiki?p=ManagementTools page, preferably
an open source one (I'm sure there are good ones that deserve the
money one pays for them, but it's stupid to pay before you know the
limitations of the free alternatives).

The reason I don't explain why your way doesn't work is because if you
don't know what a PATH is then it's hard to say how to open a  command
line and start typing DOS commands.

Regards,
~Nuno Lucas

> Dennis Achá

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

Re: [sqlite] Looking for a cryptographic library

[John Mason Jr]

John


Raymond Hurst wrote:
> Search for the following on the web:
> BeeCrypt, CryptoPP, OpenSSL
> Ray Hurst
> 
> [EMAIL PROTECTED] wrote:
>> Hi all:
>> I'm writing an application that uses SQLite to store user's data, and
>> need a library to crypt some stuff, including passwords and data. The
>> goal is to crypt before insert and decript after extract tha data, so
>> this last can't be seen by others who gain access to the SQLite
>> dataBase. The application don't need military security level :-)
>> I have been reading about Blowfish, but it seem that it encrypts data
>> in 8-byte blocks, and I suppose that it need pad the data to 8-byte
>> round, who might cause some headache.
>> The ideal is some freeware library although commercial products can
>> also be considered. Of course the final product must be commercially
>> distributable without patent issues.
>> Any advice in this matter would be grateful
>> A.J.Millan
>>
>>
>>
> 
> -
> 
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 
> 
> 
> 



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

Re: [sqlite] Enumerating rows in a view

[T]

Hi Darren,

If that works, then try using a subquery in the view definition  
instead.


 create view Enumerated
 as
 select rowid as Sequence, Name from (
   select Name from Planets order by Name
 )

Sort of like that.


Thanks for the suggestion, but, unless I'm missing something, it  
doesn't work. Since rowid doesn't exist in the inner query, the outer  
query assigns Sequence with a null value, so the whole result is:


SequenceName
--  --
.   Earth
.   Jupiter
.   Mars
.   Mercury
.   Venus

(. = null)

If we instead change it to include rowid in the subquery:

create view Enumerated
as
select rowid as Sequence, Name from (
   select rowid, Name from Planets order by Name
)
;

we get the original rowids (but we instead want a numerical sequence  
1, 2, 3, 4, 5):


SequenceName
--  --
3   Earth
5   Jupiter
4   Mars
1   Mercury
2   Venus

Thanks,
Tom


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

Re: [sqlite] Enumerating rows in a view

[Darren Duncan]

At 1:08 AM +1000 8/26/07, T wrote:

When I create a view, is there any way to enumerate the output rows?


Another method would I've developed/discovered is to create a 
temporary table, fill it with the data from the view, then use the 
automatically created rowid column as the enumeration. This works, 
but is not ideal since the creation of a temporary table can't be 
included in a view itself.


If that works, then try using a subquery in the view definition instead.

  create view Enumerated
  as
  select rowid as Sequence, Name from (
select Name from Planets order by Name
  )

Sort of like that.

-- Darren Duncan

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

Re: [sqlite] Sparse matrix

[T]

Hi All,

Simon answered:

Here's a less gruesome version - no cases. I've given no thought to  
performance comparisons.


Thanks for the two great solutions you posted.


Upon further investigation, those solutions assume that we want all  
like occurrences together, effectively sorting records in way that  
overrides the original view or table.


The best solutions I've come up with so far require first enumerating  
the rows in the view (or table). In my separate email thread  
"Enumerating rows in a view", I mention two methods:


1. Deconstructing the "order by", replacing it with a series of  
inequality operators. Then counting how many other records are less  
than each current record.


or:

2. Creating a temporary table, filling with the rows from the view,  
using the automatic rowid as the enumeration.


So, for the example "Timetable" in this thread, where I said:


and a view that sorts the data is:

create view "Timetable Sorted"
as
select rowid, Day, Subject, Room, Teacher, Period
from Timetable
order by Day, Period
;


enumerating the rows via each method would be:

1. Deconstructing order into inequalities:

create view Enumerated
as
select count(*) as Sequence, *
from Timetable as Current
left join Timetable as Others
where Current.Day > Others.Day or ( Current.Day = Others.Day and  
Current.Period >= Others.Period )

group by Current.Day, Current.Period
;

or:

2. Creating a temporary table:

create temporary table Enumerated1
as
select * from "Timetable Sorted"
;
create temporary view Enumerated
as
select rowid as Sequence, * from Enumerated1
;

Note, I've also used a temporary view just to provide a Sequence  
column containing the enumeration. This gives the same column name as  
method 1, so the actual Sparsing (below) can use the same syntax (ie  
Sequence instead of rowid).


Each method gives the sorted view/table Enumerated as:

SequenceDay Period  Teacher RoomSubject
--  --  --  --  --  --
1   Monday  1   Ng  A1  English
2   Monday  2   Peters  A2  Maths
3   Monday  3   Peters  A2  Computing
4   Monday  4   KentH1  Sport
5   Tuesday 1   Peters  A2  Maths
6   Tuesday 2   Ng  A1  History
7   Tuesday 3   Ng  A1  English
8   Tuesday 4   Ng  A1  History
9   Wednesday   1   Peters  A2  Maths
10  Wednesday   2   KentH1  Sport
11  Wednesday   3   Who S2  Science
12  Wednesday   4   Smith   S2  Science

Now, on to replacing repeated values with null (ie showing a "sparse  
matrix") and counting the repetitions. Now that I have enumerated  
sorted rows, I can use the following method. I compare each Current  
value with the Previous value (ie the value that is enumerated as one  
less than the Current value). If it's the same as the previous, then  
it's a repetition which I replace with null. If it's different to the  
previous, then I show the value and the count of the same values from  
the current to the next change, or the bottom of the table.


For the Day column, for example, I calculate the Day (Current.Day or  
null) and DayCount (count until the next change, or null) like this:


select
  case
  when Previous.Day is null or Current.Day != Previous.Day
  then Current.Day
  else null
  end
   as Day,
  case
  when Previous.Day is null or Current.Day != Previous.Day
  then
 coalesce(
(
   select Sequence from Enumerated as Others
   where Current.Day != Others.Day and Others.Sequence >=  
Current.Sequence limit 1

),
Bottom
 ) - Current.Sequence
  else null
  end
   as DayCount
from Enumerated as Current
	left join Enumerated as Previous on Current.Sequence - 1 =  
Previous.Sequence

left join ( select max( Sequence ) + 1 as Bottom from Enumerated )
;

which gives the desired:

Day DayCount
--  --
Monday  4
.   .
.   .
.   .
Tuesday 4
.   .
.   .
.   .
Wednesday   4
.   .
.   .
.   .

(I've used . to show nulls)

To build the complete matrix, ie for all columns, I duplicated the  
expressions above for Day and DayCount to make Room and RoomCount  
(replacing Day with Room), Subject and SubjectCount etc. The result  
looks like the desired:


Day DC   Room  RC   Subject SC   Teacher TC   Period
--  ---    ---  --  ---  --  ---  --
Monday  4A11English 1Ng  11
.   .A22Maths   1

Re: [sqlite] Enumerating rows in a view

[T]

Hi All,

Again following up:


When I create a table, SQLite enumerates the rows in the rowid column.

When I create a view, is there any way to enumerate the output rows?


Another method would I've developed/discovered is to create a  
temporary table, fill it with the data from the view, then use the  
automatically created rowid column as the enumeration. This works, but  
is not ideal since the creation of a temporary table can't be included  
in a view itself.


So, to solve the previous example:

create view "Planets Sorted" as select Name from Planets order by  
Name;


we could do something like:

begin immediate
;
create temporary table Enumerated
as select * from "Planets Sorted"
;
select rowid as Sequence, * from Enumerated
;
commit
;

which gives the desired:


1 Earth
2 Jupiter
3 Mars
4 Mercury
5 Venus


This "create temporary table" method is the same syntax for any view,  
unlike the "deconstruct the order into comparisons" method that I  
posted before which requires adding custom comparison operators. But  
the temporary table can't be included in a view, whereas the  
deconstruct method can. So neither is ideal but the best I've got so  
far.


Any better alternatives welcome :-)

Tom


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

Re: [sqlite] Enumerating rows in a view

[T]

When I create a table, SQLite enumerates the rows in the rowid column.

When I create a view, is there any way to enumerate the output rows?

For example, say I have a table:

create table Planets( Name text collate nocase );
insert into Planets values( 'Mercury' );
insert into Planets values( 'Venus' );
insert into Planets values( 'Earth' );
insert into Planets values( 'Mars' );
insert into Planets values( 'Jupiter' );



How could I give those rows with enumeration:

1 Earth
2 Jupiter
3 Mars
4 Mercury
5 Venus


In the absence of any other replies, the best I've come up with so far  
is:


create view Enumerated
as
select count(*) as Sequence, Current.Name as Name
from Planets as Current
left join Planets as Others
where Current.Name >= Others.Name
group by Current.Name
;

It works by basically counting how many Other records are less than or  
equal to the Current record.


If there was some view, say "Filtered", that needed to be enumerated,  
I could replace "planets" in the above view with "Filtered" but I'd  
still have to re-write the "order by" clause as a comparison, which  
ignores the work already done by the "order by".


Is there a better way?

Thanks,
Tom


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

Re: [sqlite] Client/Server approach

[John Stanton]

In that case you are best to implement a server on one machine and have 
the others connect to it.  Since you only have 5 clients you do not need 
to use the shared cache approach.


When we use networked clients we use a server with HTTP protocol so that 
penetrating firewalls is no problem.


Sreedhar.a wrote:

 I am not using 5 clients from the same machine.
In a network 5 clients and 1 server will be present.


Regards,
A.Sreedhar.
 


-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED] 
Sent: Friday, August 24, 2007 6:17 PM

To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Client/Server approach

You do not need a server to manage 5 clients on the same machine, only if
they are on a network.

Sreedhar.a wrote:



Hi,

I am working in sqlite 3.3.6.
I want 5 clients to browse at the same time with good performance.
I enabled threadsafe and defined SQLITE_ENABLE_MEMORY_MANAGEMENT .
I think by using test_server.c  we can do 5 clients browsing with 
single server.

Can any one please help me by providing some patch for implementing this.

My doubts are:
1.Do I need  to create a thread for each client.If so it has to be 
created before Sqlite3_server_start().


(Or)
Some thing like below has to be followed.

Main()
{

 sqlite3_client_open()
  sqlite3_client_prepare()
  sqlite3_client_step()
  sqlite3_client_reset()
  sqlite3_client_finalize()
  sqlite3_client_close()

Sqlite3_server_start(); For starting the server.

sqlite3_server_stop();  For closing the server.



}

Kindly help me to solve this.
Thanks in advance

Best Regards,
A.Sreedhar.




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

--
---






-
To unsubscribe, send email to [EMAIL PROTECTED]

-




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




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

Re: [sqlite] Client/Server approach

[Zarko Popovski]

I found one open source api for making network interface for any embedded
rdbms. The problem was concurrency,for that reason i develop a small thread
pool inside the open source network interface and now  one rdmbs connection
is shared between threads or applications in network. I implement that for
SQLite in Java, i hope you can develope some similar net interface. Sending
query to interface and and receiveing results from interface in application
is builded with tcp sockets and streams.

On 8/24/07, Sreedhar.a <[EMAIL PROTECTED]> wrote:
>
> I am not using 5 clients from the same machine.
> In a network 5 clients and 1 server will be present.
>
>
> Regards,
> A.Sreedhar.
>
>
> -Original Message-
> From: John Stanton [mailto:[EMAIL PROTECTED]
> Sent: Friday, August 24, 2007 6:17 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Client/Server approach
>
> You do not need a server to manage 5 clients on the same machine, only if
> they are on a network.
>
> Sreedhar.a wrote:
> >
> > Hi,
> >
> > I am working in sqlite 3.3.6.
> > I want 5 clients to browse at the same time with good performance.
> > I enabled threadsafe and defined SQLITE_ENABLE_MEMORY_MANAGEMENT .
> > I think by using test_server.c  we can do 5 clients browsing with
> > single server.
> > Can any one please help me by providing some patch for implementing
> this.
> >
> > My doubts are:
> > 1.Do I need  to create a thread for each client.If so it has to be
> > created before Sqlite3_server_start().
> >
> > (Or)
> > Some thing like below has to be followed.
> >
> > Main()
> > {
> >
> >sqlite3_client_open()
> >sqlite3_client_prepare()
> >sqlite3_client_step()
> >sqlite3_client_reset()
> >sqlite3_client_finalize()
> >sqlite3_client_close()
> >
> > Sqlite3_server_start(); For starting the server.
> >
> > sqlite3_server_stop();For closing the server.
> >
> >
> >
> > }
> >
> > Kindly help me to solve this.
> > Thanks in advance
> >
> > Best Regards,
> > A.Sreedhar.
> >
> >
> >
> >
> > --
> > --- To unsubscribe, send email to
> > [EMAIL PROTECTED]
> > --
> > ---
> >
>
>
>
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> 
> -
>
>
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>

RE: [sqlite] Client/Server approach

[Sreedhar.a]

 I am not using 5 clients from the same machine.
In a network 5 clients and 1 server will be present.


Regards,
A.Sreedhar.
 

-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED] 
Sent: Friday, August 24, 2007 6:17 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Client/Server approach

You do not need a server to manage 5 clients on the same machine, only if
they are on a network.

Sreedhar.a wrote:
>  
> Hi,
> 
> I am working in sqlite 3.3.6.
> I want 5 clients to browse at the same time with good performance.
> I enabled threadsafe and defined SQLITE_ENABLE_MEMORY_MANAGEMENT .
> I think by using test_server.c  we can do 5 clients browsing with 
> single server.
> Can any one please help me by providing some patch for implementing this.
> 
> My doubts are:
> 1.Do I need  to create a thread for each client.If so it has to be 
> created before Sqlite3_server_start().
> 
> (Or)
> Some thing like below has to be followed.
> 
> Main()
> {
> 
>sqlite3_client_open()
>sqlite3_client_prepare()
>sqlite3_client_step()
>sqlite3_client_reset()
>sqlite3_client_finalize()
>sqlite3_client_close()
> 
> Sqlite3_server_start(); For starting the server.
> 
> sqlite3_server_stop();For closing the server.
> 
> 
> 
> }
> 
> Kindly help me to solve this.
> Thanks in advance
> 
> Best Regards,
> A.Sreedhar.
>  
> 
> 
> 
> --
> --- To unsubscribe, send email to 
> [EMAIL PROTECTED]
> --
> ---
> 



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




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