Need to do a Test.

classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Need to do a Test.

Richard-118
Need to create a test2.db
with following fields A B C
the data is mostly numerical, however it should be okay
with VARCHAR field? ( this is only a test of speed for importing and
searching )

Next Need to make Field "A" index able.
Then do a import of a text file sqtest.csv
into this new database.

What is the command for importing this in?


--

I will not be pushed, filed, stamped,
indexed, briefed, debriefed, or numbered!
My life is my own - No. 6
Reply | Threaded
Open this post in threaded view
|

Re: Need to do a Test.

D. Richard Hipp
Richard Nagle <[hidden email]> wrote:

> Need to create a test2.db
> with following fields A B C
> the data is mostly numerical, however it should be okay
> with VARCHAR field? ( this is only a test of speed for importing and
> searching )
>
> Next Need to make Field "A" index able.
> Then do a import of a text file sqtest.csv
> into this new database.
>
> What is the command for importing this in?
>

Whoever answers this question: please be sure to tell
Mr. Nagle about BEGIN...COMMIT.  Otherwise he is going
to run his speed tests and think SQLite only does 25
inserts/second instead of 50,000 inserts/second.
--
D. Richard Hipp <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: Need to do a Test.

Dennis Cote
In reply to this post by Richard-118
Richard Nagle wrote:

> Need to create a test2.db
> with following fields A B C
> the data is mostly numerical, however it should be okay
> with VARCHAR field? ( this is only a test of speed for importing and
> searching )
>
> Next Need to make Field "A" index able.
> Then do a import of a text file sqtest.csv
> into this new database.
>
> What is the command for importing this in?
>
>
Richard,

At a command prompt type:

sqlite3 test2.db

At he sqlite3 prompt type:

create table T(A, B, C);
create index on T(A);
.separator ,
.import 'sqtest.cvs'

You should now a table with your data. Be warned that there are problems
importing CSV data with quote delimiters on the fields (the quotes are
included in the table as part of the field values). You can try a select
to dump your table.

select * from T;

HTH
Dennis Cote

Reply | Threaded
Open this post in threaded view
|

Getting a Unique column from a query

njhinder
is it possible to use a Distinct or Unique on a column such as:

SELECT school.schoolID AS ID,
CASE WHEN  class.classtype= 2 THEN class.classtype ELSE teacher.teacherID
END AS unique(new_id) FROM Region


SELECT school.schoolID AS ID,
CASE WHEN  class.classtype= 2 THEN class.classtype ELSE teacher.teacherID
END AS distinct(new_id) FROM Region


I do not want any duplicates of the new_id.

Thanks!
Nicole Hinderman

Reply | Threaded
Open this post in threaded view
|

Re: Need to do a Test.

Dennis Cote
In reply to this post by D. Richard Hipp
[hidden email] wrote:

>Richard Nagle <[hidden email]> wrote:
>  
>
>>Need to create a test2.db
>>with following fields A B C
>>the data is mostly numerical, however it should be okay
>>with VARCHAR field? ( this is only a test of speed for importing and
>>searching )
>>
>>Next Need to make Field "A" index able.
>>Then do a import of a text file sqtest.csv
>>into this new database.
>>
>>What is the command for importing this in?
>>
>>    
>>
>
>Whoever answers this question: please be sure to tell
>Mr. Nagle about BEGIN...COMMIT.  Otherwise he is going
>to run his speed tests and think SQLite only does 25
>inserts/second instead of 50,000 inserts/second.
>--
>D. Richard Hipp <[hidden email]>
>
>
>  
>
Well its a little too late for that now, isn't it. :-)

But seriously, shouldn't the .import meta command in the sqlite shell do
this already?

Dennis Cote
Reply | Threaded
Open this post in threaded view
|

Re: Need to do a Test.

Dennis Cote
On 9/30/05, Dennis Cote <[hidden email]> wrote:
>
>
> But seriously, shouldn't the .import meta command in the sqlite shell do
> this already?


Sorry for the self reply, but I just checked the source, the .import command
does do a BEGIN and COMMIT around the insert statements it executes. So
Richard should be good to go with his speed test importing from a CSV file.
Reply | Threaded
Open this post in threaded view
|

Re: Getting a Unique column from a query

Jay Sprenkle
In reply to this post by njhinder
Won't you get duplicates rows of school_id, 2
when classtype = 2?

On 9/30/05, [hidden email] <[hidden email]>
wrote:

>
> is it possible to use a Distinct or Unique on a column such as:
>
> SELECT school.schoolID AS ID,
> CASE WHEN class.classtype= 2 THEN class.classtype ELSE teacher.teacherID
> END AS unique(new_id) FROM Region
>
>
> SELECT school.schoolID AS ID,
> CASE WHEN class.classtype= 2 THEN class.classtype ELSE teacher.teacherID
> END AS distinct(new_id) FROM Region
>
>
> I do not want any duplicates of the new_id.
>
> Thanks!
> Nicole Hinderman
>
>


--
---
The Castles of Dereth Calendar: a tour of the art and architecture of
Asheron's Call
http://www.lulu.com/content/77264
Reply | Threaded
Open this post in threaded view
|

Re: Need to do a Test.

Richard-118
In reply to this post by Dennis Cote
Woah,

Stop in time,
what this command? how does one add this?
I want to see the real state of sqlite, at its max speed.

Regards to all.
Richard Nagle


Dennis Cote wrote:

> [hidden email] wrote:
>
>> Richard Nagle <[hidden email]> wrote:
>>  
>>
>>> Need to create a test2.db
>>> with following fields A B C
>>> the data is mostly numerical, however it should be okay
>>> with VARCHAR field? ( this is only a test of speed for importing and
>>> searching )
>>>
>>> Next Need to make Field "A" index able.
>>> Then do a import of a text file sqtest.csv
>>> into this new database.
>>>
>>> What is the command for importing this in?
>>>
>>>  
>>
>> Whoever answers this question: please be sure to tell
>> Mr. Nagle about BEGIN...COMMIT.  Otherwise he is going
>> to run his speed tests and think SQLite only does 25
>> inserts/second instead of 50,000 inserts/second.
>> --
>> D. Richard Hipp <[hidden email]>
>>
>>
>>  
>>
> Well its a little too late for that now, isn't it. :-)
>
> But seriously, shouldn't the .import meta command in the sqlite shell do
> this already?
>
> Dennis Cote
>

--

I will not be pushed, filed, stamped,
indexed, briefed, debriefed, or numbered!
My life is my own - No. 7
Reply | Threaded
Open this post in threaded view
|

Re: Getting a Unique column from a query

njhinder
In reply to this post by Jay Sprenkle
It would be ok to have duplicates of school_id.  I just want to make sure
that new_id is unique.




                                                                           
             Jay Sprenkle                                                  
             <jsprenkle@gmail.                                            
             com>                                                       To
                                       [hidden email]            
             09/30/2005 01:03                                           cc
             PM                                                            
                                                                   Subject
                                       Re: [sqlite] Getting a Unique      
             Please respond to         column from a query                
             sqlite-users@sqli                                            
                  te.org                                                  
                                                                           
                                                                           
                                                                           
                                                                           





Won't you get duplicates rows of school_id, 2
when classtype = 2?

On 9/30/05, [hidden email] <[hidden email]>
wrote:

>
> is it possible to use a Distinct or Unique on a column such as:
>
> SELECT school.schoolID AS ID,
> CASE WHEN class.classtype= 2 THEN class.classtype ELSE teacher.teacherID
> END AS unique(new_id) FROM Region
>
>
> SELECT school.schoolID AS ID,
> CASE WHEN class.classtype= 2 THEN class.classtype ELSE teacher.teacherID
> END AS distinct(new_id) FROM Region
>
>
> I do not want any duplicates of the new_id.
>
> Thanks!
> Nicole Hinderman
>
>


--
---
The Castles of Dereth Calendar: a tour of the art and architecture of
Asheron's Call
http://www.lulu.com/content/77264


Reply | Threaded
Open this post in threaded view
|

Re: Getting a Unique column from a query

Dennis Cote
In reply to this post by njhinder
[hidden email] wrote:

>is it possible to use a Distinct or Unique on a column such as:
>
>SELECT school.schoolID AS ID,
>CASE WHEN  class.classtype= 2 THEN class.classtype ELSE teacher.teacherID
>END AS unique(new_id) FROM Region
>
>
>SELECT school.schoolID AS ID,
>CASE WHEN  class.classtype= 2 THEN class.classtype ELSE teacher.teacherID
>END AS distinct(new_id) FROM Region
>
>
>I do not want any duplicates of the new_id.
>
>Thanks!
>Nicole Hinderman
>
>
>  
>
I'm not sure what you are trying to do here.

Distinct applies to rows in a result set. A statement like "select
distinct ..." ensures that each row in the result set is distinct. That
means that there is a difference in at least one column from every other
row in the result.

Unique is a constraint that applies to a column, or columns, in a table.
It ensures that every row in the table has a unique value in that
column, or combined set of columns.

To help answer your question we will need more information about the
tables you are using. It looks like you have four, a region table, a
school table, a class table, and a teacher table. Is that correct?

The query you gave above does not make any sense as it stands. Could you
explain what you are trying to do in more detail.

Dennis Cote